Data table advanced operations

Preparation: install MySQL database

create database CLASS;
use CLASS;

create table TEST (id int not null,name char(20) 	not null,cardid varchar(18) not null unique 	key,primary key (id));

insert into TEST(id,name,cardid) values (1,'zhangsan','123123');

insert into TEST(id,name,cardid) values (2,'lisi','1231231');

insert into TEST(id,name,cardid) values (3,'wangwu','12312312');
select * from TEST;

1, Another way to specify a primary key

use lucien;

create table if not exists ljm (
id int(4) zerofill primary key auto_increment,     
student_name varchar(20) not null,
cardid varchar(18) not null unique key,
hobby varchar(50));

if not exists	Indicates whether the table to be created exists. If it does not exist, the table will be created
int(4) zerofill	Indicates that if the value is less than 4 digits, it is filled with "0" in front
auto_increment	Indicates that this field is a self increasing field, that is, each record is automatically incremented by 1, which starts from 1 by default; Self growth field data cannot be duplicate; The self growing field must be a primary key; If the added record data does not specify the value of this field and the addition fails, it will be automatically incremented once
unique key	Represents the unique key constraint of this field, and the data of this field cannot be repeated; There can only be one primary key in a table, but there can be multiple unique keys in a table
not null	Indicates that this field is not allowed to be NULL

2, Clone table

Copy the data records of the data table to the new table

1. Method 1

create table New table name like Replicated table name;  #Copy format: you can copy the format of a table to a new table, but there is no content
insert into New table name select * from Replicated table name;  #Copy the contents of the original table to the new table

example:create table TEST01 like TEST;
select * from TEST01;

desc TEST01;
insert into TEST01 select * from TEST;
select * from TEST01;

2. Method 2

create table New table name (select * from Replicated table name)

example:create table TEST02 (select * from TEST);
select * from TEST02;

• CREATE TABLE new table (SELECT * FROM old table)
• This method will copy all the contents of the oldtable, but one of the worst aspects of this method is that the new table does not have the primary key, Extra (auto_increment) and other attributes of the old table.

3, Clear the table and delete all data in the table

1. Method 1

delete from Table name;

#After the table is cleared by delete, there are deleted record entries in the returned result;
delete Delete record data line by line during operation;
If there is a self growing field in the table, use delete from After deleting all records, the newly added records will be changed from the original largest records ID Continue to write records automatically later

Example: create table if not exists TEST03 (id int primary 	key auto_increment,name varchar(20) not null,cardid varchar(18) not null unique key);
show tables;

insert into TEST03 (name,cardid) values ('zhangsan','11111');		
select * from TEST03;
delete from TEST03;

insert into TEST03 (name,cardid) values ('lisi','22222');
select * from TEST03;

2. Method 2

truncate table Table name;

Example: select * from TEST03;
truncate table TEST03;
insert into TEST03 (name,cardid) values ('wangwu','33333');
select * from TEST03;


#TRUNCATE does not return deleted entries after clearing the table;
TRUNCATE When working, the table structure is re established as it is, so in terms of speed TRUNCATE Will compare DELETE Empty the watch fast;
use TRUNCATE TABLE After clearing the data in the table, ID It will be re recorded from 1.

3. Method 3

drop table [Database name.]Table name;

#drop is a direct deletion table, which is violent

4. Comparison of three deletion methods

In terms of deletion speed:

• drop is to delete the table directly
• truncate refactoring for direct destruction
• delete is an item by item deletion
• So drop > truncate > delete

In terms of safety:

• Both drop and truncate do not keep records, which is not convenient for data recovery
• Delete has every step of the delete operation, so when it is deleted by mistake, it can be restored according to the log
  Therefore, delete has the best security

4, Create temporary table

• After the temporary table is created successfully, the temporary table created cannot be seen by using the SHOW TABLES command, and the temporary table will be destroyed after the connection exits.
• Before exiting the connection, you can also perform operations such as adding, deleting, modifying, and querying. For example, use the DROP TABLE statement to manually and directly delete the temporary table.
• At the same time, temporary tables cannot create foreign keys.

CREATE TEMPORARY TABLE Table name (Field 1 data type,Field 2 data type[,...][,PRIMARY KEY (Primary key name)]);

Example: create temporary table TEST04 (id int not null,name varchar(20) not null,cardid varchar(18) not null unique key,primary key (id));
show tables;

insert into TEST04 values (1,'haha','12345');	
select * from TEST04;

5, Six common constraints in MySQL

Primary key constraint	primary key
Foreign key constraint	foreign key
Non NULL constraint	not null
Unique constraint	unique [key
Default value constraint	default
Self increasing constraint	auto_increment

6, Create foreign key constraints

1. Definition of foreign key

• Definition of foreign key: if the same attribute field x is the primary key in Table 1 but not in Table 2, then field x is called the foreign key in Table 2.

2. Function

• The function of creating foreign key constraints: to ensure the integrity and consistency of data

3. Primary key table and foreign key table

• 1. Tables with public keywords as primary keys are primary key tables (parent tables and primary tables)
• 2. Tables with public keywords as foreign keys are foreign key tables (from table and appearance)
• Note: the fields of the master table associated with the foreign key must be set as the primary key. It is required that the slave table cannot be a temporary table. The fields of the master and slave tables have the same data type, character length and constraints

4. Operation principle and demonstration of master table and slave table

(1) First, create two tables, and the fields of one table are the same as the primary key of the other table

Example: create table TEST04 (hobid int(4),hobname varchar(50));
create table TEST05 (id int(4) primary key auto_increment,name varchar(50),age int(4),hobid int(4));

alter table TEST04 add constraint PK_hobid primary key(hobid);
alter table TEST05 add constraint FK_hobid foreign key(hobid) references TEST04(hobid);

(2) Add data content to the table

Example: add data record
insert into TEST05 values (1,'zhangsan','20',1);
insert into TEST04 values (1,'sleep');
insert into TEST05 values (1,'zhangsan',20,1);

(3) Add primary key constraints to the primary table and foreign keys to the secondary table

example:drop table TEST04;
drop table TEST05;
drop table TEST04;

(4) View structure and display foreign key associations

(5) Insert data (master table first, then slave table)

(6) Delete data record (from table to main table)

(7) If you want to delete the foreign key constraint field (delete the foreign key constraint first, and then delete the foreign key name)

Note: if you want to delete the foreign key constraint field
 Delete the foreign key constraint first, and then delete the foreign key name. It is not demonstrated here

show create table TEST05;
alter table TEST05 drop foreign key FK_hobid;
alter table TEST05 drop key FK_hobid;
desc TEST05;

7, Database user management

1. New user

CREATE USER 'user name'@'Source address' [IDENTIFIED BY [PASSWORD] 'password'];

• User name: Specifies the user name that will be created
• Source address: Specifies the hosts on which the newly created user can log in. It can be in the form of IP address, network segment and host name. Local users can use localhost to allow any host to log in
• Wildcards available%
• Wildcard% can be used
• Password ': if plaintext password is used, directly enter' password ', which will be automatically encrypted by Mysql when inserted into the database;
  • If you use an encrypted PASSWORD, you need to use SELECT PASSWORD('PASSWORD ') first; Obtain the ciphertext, and then add PASSWORD 'ciphertext' to the statement;
  • If the "IDENTIFIED BY" section is omitted, the user's password will be empty (not recommended)

Example: create user 'zhangsan'@'localhost' identified by '123123';
select password('123123');
create user 'lisi'@'localhost' identified by password '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1';

2. View user information

The created user is saved in the user table of mysql database

USE mysql;

SELECT User,authentication_string,Host from user;

3. Rename user

rename user 'lisi'@'localhost' to 'wangwu'@'localhost';

select User,authentication_string,Host from user;

4. Delete user

drop user 'wangwu'@'localhost';

select User,authentication_string,Host from user;

5. Modify the password of the current login user

set password = password('123456');
quit
mysql -u root -p

6. Change other user passwords

set password for 'zhangsan'@'localhost' = password('111111');
use mysql;
select User,authentication_string,Host from user;

7. Solution to forgetting the root password

(1) Modify / etc / my CNF configuration file, log in to mysql directly without password

vim /etc/my.cnf
[mysqld]
skip-grant-tables					#Add to log in to mysql without using the authorization table
systemctl restart mysqld
mysql								#Direct login

(2) Use update to change the root password and refresh the database

UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD('112233') where user='root';
FLUSH PRIVILEGES;
quit

Again /etc/my.cnf In the configuration file skip-grant-tables Delete and restart mysql Service.
mysql -u root -p
112233

8, Database user authorization

1. Grant permissions

GRANT Statement: specifically used to set the access rights of database users. When the specified user name does not exist, GRANT Statement will create a new user; When the specified user name exists, GRANT Statement is used to modify user information.

grant Right raising
grant Permission list on Database name.Table name to 'user name'@'Source address' [identified by 'password'];

- Permission list: used to list various database operations authorized to use, separated by commas, such as“ select,insert,update" . Use“ all" Indicates all permissions and can authorize any operation.
-  Database name.Table name: Specifies the name of the database and table that authorize the operation, where wildcards can be used * . lucien.* The object representing the authorization operation is lucien All tables in the database.
-  'user name@Source address': Used to specify the user name and the client address allowed to access, that is, who can connect and where to connect. The source address can be a domain name IP Address, you can also use“%" Wildcard, indicating all addresses in a region or network segment, such as“%.lucien.com" ,"192.168.184.%" Wait.
-  identified by: Used to set the password string used by users when connecting to the database. When creating a new user, if omitted“ identified by" Part, the user's password will be empty.

#The user wangwu is allowed to query the data records of all tables in the CLASS database locally, but it is prohibited to query the records of tables in other databases.

Example:
GRANT select ON CLASS.* TO 'wangwu'@'localhost' IDENTIFIED BY '123456';
quit;
mysql -u wangwu -p
123456
show databases;
use information_schema;
show tables;
select * from INNODB_SYS_TABLESTATS;

#The user wangwu is allowed to connect mysql locally and remotely, and has all permissions.

quit;
mysql -u root -p112233
GRANT ALL PRIVILEGES ON *.* TO 'wangwu'@'localhost' IDENTIFIED BY '123456';

flush privileges;
quit

mysql -u wangwu -p123456
create database SCHOOL;

2. View permissions

show grants for user name@Source address;

Example:
SHOW GRANTS FOR 'wangwu'@'localhost';

3. Revoke permissions

revoke Permission list on Database name.Table name from user name@Source address;

Example: quit;
mysql -u root -p112233
SHOW GRANTS FOR 'wangwu'@'localhost';
REVOKE SELECT ON "CLASS".* FROM 'wangwu'@'localhost';

SHOW GRANTS FOR 'wangwu'@'localhost';

#Use permission can only be used for database login and cannot perform any operation; The USAGE permission cannot be recycled, that is, REVOKE cannot delete a user.

flush privileges;