1, Common data types

Database – > data table – > row (record): information used to describe an object
Column (field): an attribute used to describe an object

Common data types:	effect
int	integer
float	Single precision floating point (4 bytes, 32 bits)
double	Double precision floating point (8 bytes, 64 bits)
char	Fixed length character type
varchar	Variable length character type
text	text
image	picture
decimal(5,2)	5 effective length digits with 2 digits after the decimal point

2, View database structure

Use the command to enter the database

1) View the database in the current server

SHOW DATABASES;				##Case insensitive, semicolon ";" Indicates the end

2) View the tables contained in the database

USE Database name;
SHOW TABLES; 

3) . view the structure (field) of the table

SE Database name;
DESCRIBE [Database name.]Table name;
Shrinkable writing:DESC Table name;

3, SQL statement

SQL statements are used to maintain and manage the database, including data query, data update, access control, object management and other functions.

3.1. SQL language classification:

language	function
DDL	Data definition language is used to create database objects, such as libraries, tables, indexes, etc
DML	Data manipulation language is used to manage the data in the table
DQL	Data query language is used to find qualified data records from data tables
DCL	Data control language is used to set or change database user or role permissions

4, DDL (data definition language)

4.1. Create a new database

CREATE DATABASE Database name;

4.2. Create a new table

CREATE TABLE Table name(Field 1 data type,Field 2 data type[,...][,PRIMARY KEY (Primary key name)]);
#Primary keys generally select fields that can represent uniqueness. Null values are not allowed. A table can only have one primary key.

4.3. Delete the specified data table

DROP TABLE [ Database name.]Table name;			#If you do not USE to enter the database, you need to add the database name

4.4. Delete the specified database

DROP DATABASE Database name;

5, DML (data operation language)

5.1 data records in management table

1) . insert a new data record into the data table

INSERT INTO Table name(Field 1,Field 2[,...]) VALUES(Value of field 1,Value of field 2,...);

Example:
create database school;
use school;

create table class2 (
id int not null,
name char(20) not null,
sex char(2) not null,
primary key (id));

insert into class2 (id,name,sex) values(1,'zhangsan','male');

2) . query data records

SELECT Field name 1,Field name 2[,...] FROM Table name [WHERE Conditional expression];

Example:
select * from class2;
select name,sex from class2 where id=1;

3) , modify and update the data records in the data table

UPDATE Table name SET Field name 1=Field value 1[,Field name 2=Field value 2] [WHERE Conditional expression];

Example:
insert into class2 (id,name,sex) values(2,'lisi','female');
insert into class2 (id,name,sex) values(3,'wangwu','male');
select * from class2;

update class2 set id=4 where name='zhangsan';
select * from class2;

update class2 set name='zhaoliu',sex='male' where id=2;
select * from class2;

4) . delete the specified data record in the data table

DELETE FROM Table name [WHERE Conditional expression];

Example: delete from class2 where id=4;
select * from class2;

5.2. Modify table name and table structure

1) . modify table name

ALTER TABLE Old table name RENAME New table name;

Example: alter table class2 rename class3;
show tables;
select * from class3;

2) . expand table structure (add field)

ALTER TABLE Table name ADD address varchar(50) default 'Address unknown';
#Default "unknown address": indicates that the default value of this field is unknown; Can be used with NOT NULL

Example:
alter table class3 add address varchar(50) default 'Address unknown';

3) Modify the field (column) name and add a unique key

ALTER TABLE Table name CHANGE Old column name new column name data type [unique key];

Example:
alter table class3 change name student_name varchar(20) unique key;
select * from class3;

insert into class3 (id,student_name,sex) values (1,'zhaoliu','male');
select * from class3;

4) , delete field

ALTER TABLE Table name DROP Field name;

Example: alter table class3 drop address;

5) , expansion

use school;
create table if not exists info (
id int(4) zerofill primary key auto_increment,				#The second way to specify the primary key
name varchar(10) not null,
cardid int(18) not null unique key,
hobby varchar(50));

---------------Command interpretation--------------------------------
#if not exists: it means to check whether the table to be created already exists. If not, continue to create it
#int(4) zerofill: indicates that if the value is less than 4 digits, it is filled with "0" in front, for example 0001
#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 duplicated; Self growing field must be 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: indicates 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

6, Advanced operation of data table

6.1. Clone the table, which means that the data records of the data table are generated into a new table

Method 1: like

create table test1 like info;  #Using the LIKE method, copy the KY08 table structure to generate the test01 table
insert into test1 select * from info;

Method 2: direct cloning

CREATE TABLE test2 (SELECT * from info);

show create table test2\G;					#Get the table structure, index and other information of the data table
SELECT * from test2;

Defect of direct cloning: some special attributes cannot be cloned

6.2. Clear the table and delete all data in the table

Method 1: record ID is not deleted

delete from test1;
#DELETE after clearing the table, the returned result contains deleted record entries; DELETE deletes record data line by line during operation; If there is a self increment field in the table, after deleting all records with DELETE FROM, the newly added record will continue to be self increment written to the record from the original maximum record ID.

Method 2: delete record ID

truncate table test2;
#TRUNCATE does not return deleted entries after clearing the table; TRUNCATE rebuilds the table structure as it is, so TRUNCATE is faster than DELETE to clear the table; After using TRUNCATE TABLE to clear the data in the table, the ID will be re recorded from 1.

6.3. Create a temporary table

After the temporary table is created successfully, use sHOWTABLES The command cannot see the created temporary table. 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, such as using DROPTABLE Statement to manually and directly delete the temporary table.

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

Example:
create temporary table test3 (
id int(4) zerofill primary key auto_increment,
name varchar(10) not null,
sex char(2) not null);

insert into test3 values(1,'zhangsan','male');

select * from test03;
show tables;

6.4. Create foreign key constraints to ensure the integrity and consistency of data

• 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

• Understanding of primary key table and foreign key table:

  • Tables with public keywords as primary keys are primary key tables (parent and primary)
  • Tables with public keywords as foreign keys are foreign key tables (from table and appearance)

Note: the fields of the sub table associated with the foreign key must be set as the primary key. The foreign key fields of the main table and the fields of the sub table are required to have the same data type, character length and constraints.

#Create main table test4
create table test4 (hobid int(4),hobname varchar(50));
#Create from table test5
create table test5 (id int(4) primary key auto_increment,name varchar(10),age int(3),hobid int(4));

#Add a primary key constraint to the main table test4. It is recommended that the primary key name be "PK_" start
alter table test4 add constraint PK_hobid primary key (hobid);
#To add a foreign key from the test5 table and establish a foreign key association between the hobbid field of the test5 table and the hobbid field of the test4 table, it is recommended that the foreign key name be "FK_" start
alter table test5 add constraint FK_hob foreign key (hobid) references test4 (hobid);

desc test5;

When inserting a new data record, you must first the master table and then the slave table

insert into test4 values(1,'read');
insert into test5 values(1,'lisi',10,1);

When deleting data records, you must first delete the primary table from the primary table, that is, when deleting the primary key table, you must first delete other tables associated with it

drop tables test5;
drop tables test4;

Viewing and deleting foreign key constraints

show create table test5\G;
alter table test5 drop foreign key FK_hob;
alter table test5 drop key FK_hob;
desc test5;

Six common constraints in MySQL

Primary key constraint( primary key)
Foreign key constraint( foreign key)
Non NULL constraint( not null)
Uniqueness constraint( unique [key|index])
Default value constraint( default)
Self increasing constraint( auto_increment)

7, Database user management

7.1. Create a new user

CREATE USER 'user name'@'Source address' [IDENTIFIED BY [PASSWORD] 'password'];
#----------------------Interpretation part-----------------------------------------
'user name': Specifies the user name that will be created
'Source address': Specify the hosts on which the newly created user can log in, and use the IP The form of address, network segment and host name,
          Available to local users localhost，Allow any host to log in. Wildcards are available%
'password': If plaintext password is used, enter it directly'password'，Inserted into the database by Mysql Automatic encryption;
       If you use an encrypted password, you need to use it first SELECT PASSWORD('password'); Get the ciphertext and add it to the statement PASSWORD 'ciphertext';
       If omitted“ IDENTIFIED BY"Part, the user's password will be empty (not recommended)
#----------------------------------------------------------------------
For example:
create user 'test1'@'localhost' IDENTIFIED BY '123456';

select password('123456');
create user 'test2'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

7.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;

7.3. Rename user

rename user 'test1'@'localhost' to 'zhangsan'@'localhost';

7.4. Delete user

drop user 'test2'@'localhost';

7.5. Modify the current login user password

set PASSWORD = PASSWORD('abc123');

7.6. Modify other user passwords

set PASSWORD for 'zhangsan'@'localhost' = PASSWORD('abc123');

7.7. And solutions for forgetting the root password

Modify / etc / my CNF configuration file, login 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.service

mysql								#Direct login

8, Database user authorization

8.1 authorization

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

GRANT Permission list ON Database name.Table name TO 'user name'@'Source address' [IDENTIFIED BY 'password'];
#-------------------------------Parameter interpretation---------------------------------------------------------------------------
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:  Use to specify the names of databases and tables that authorize operations, where wildcards can be used“*". For example, use“ test.*"The object representing the authorization operation is test 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 domain name IP Address, you can also use“%"Wildcard, indicating all addresses in a region or network segment, such as“%.test.com","192.168.163.%"Etc.

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.

For example:
#The user zhangsan is allowed to query the data records of all tables in the school database locally, but it is prohibited to query the records of tables in other databases.
GRANT select ON school.* TO 'zhangsan'@'localhost' IDENTIFIED BY 'abc123';

flush privileges;
quit

mysql -u zhangsan -pabc123
use school;
show tables;
select * from info;

Other authorization examples:
#Allow user zhangsan to remotely connect to mysql at all terminals and have all permissions.
GRANT ALL [PRIVILEGES] ON *.* TO 'zhangsan'@'%' IDENTIFIED BY 'abc123';

8.2. View permission

SHOW GRANTS FOR user name@Source address;

For example:
SHOW GRANTS FOR 'zhangsan'@'localhost';

8.3 revocation of authority

REVOKE Permission list ON Database name.Table name FROM user name@Source address;
For example:
REVOKE SELECT ON "school".* FROM 'zhangsan'@'localhost';
SHOW GRANTS FOR 'zhangsan'@'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;
REVOKE ALL ON *.* FROM 'zhangsan'@'localhost';