1, MySQL database management

1.1 warehouse and table

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

Column (field): an attribute used to describe an object

1.2 common data types

int	integer
float	Single precision floating point 4-byte 32-bit
double	Double precision floating point 8-byte 64 bit
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

2.1. View the database in the current server

show databases;#Case insensitive, semicolon ";" Indicates the end

2.2. View the tables contained in the database

use mysql;
show tables;

2.3. View table structure (field)

use Database name;
describe [Database name.]Table name;
Can be reduced to: desc Table name;
perhaps
desc Database name.Data table name;

3, SQL statement

Function: SQL statement is used to maintain and manage database, including data query, data update, access control, object management and other functions

3.1. SQL statement classification:

sentence	Meaning of representative
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, used to set or change database user or role permissions

4, Create and delete databases and tables

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 keys generally select fields that can represent uniqueness. NULL values are not allowed. A table can only have one primary key.

Example:

create database db;
use db;
create table test1 (id int(4) not null,name char(10),age int(3),primary key(id)) ;

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, Manage data records in tables

5.1. Insert a new data record into the data sheet

Syntax description:

insert into Table name(Field 1,Field 2[,...]) VALUES(Value of field 1,Value of field 2,...);
insert into Table name values (Value of field 1,Value of field 2,...);

Example:

insert into test1 (id,name,score,passwd) values (1,'zhangsan','70.1',password('111111'));
#PASSWORD('111111 '): when querying data records, the password string is displayed in encrypted form; If PASSWORD() is not used, it will be displayed in clear text during query.
insert into test1 values(2,'lisi',90.1,'22222222');　　

5.2. Query data records

select  Field name 1,Field name 2[,...] from Table name [where Conditional expression];

Example:

SELECT * FROM test;
SELECT ``id``,name,score FROM ab WHERE ``id``=2;` `
select` `name from ab\G 
``#Display vertically as a list``select` `* from ab limit 2;  ``#Only the first 2 rows are displayed``select` `* from ab limit 2,3; ``#Displays the first 3 lines after line 2

5.2. 1. Query all data

Syntax description:

select * from test1;

5.2. 2 query specified data

Syntax description:

select id,name from test1 where id=2;

5.2. 3 vertical display in list mode

select * from test1;

5.2. 4 display the contents of the specified line

Syntax description:

select * from test1 limit 2;
select * from test1 limit 2,2;
select * from test1 limit 3,2;
#limit 2 indicates that the first two lines are displayed
#limit 2,2 indicates that the two lines after the second line are displayed, that is, lines 3 and 4
#limit 3,2 indicates that the two lines after the third line are displayed, that is, lines 4 and 5

5.3. Modify and update the data records in the data sheet

Syntax description:

update Table name set Field name 1=Field value 1[,Field name 2=Field value 2] [where Conditional expression];

Example:

update test1 set score='100.0' where id=6;

5.4 delete the specified data record in the data table

delete from Table name [where Conditional expression]; 

Example:

delete from test1 where id=6;

6, Modify table name and table structure

6.1. Modify table name

rename table Old table name to New table name;
perhaps
alter table Old table name rename New table name;

Example:

rename table test1 to test;
perhaps
alter table test rename test1;

6.2. Expand table structure (add field)

alter table Table name add address varchar(50) default 'The address is unknown';
#Default 'unknown address': indicates that the default address of this field setting is unknown; Can be used with NOT NULL

Example:

alter table test1 add address varchar(50) default 'The address is unknown';

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

Syntax description:

alter table Table name change Old column name new column name data type [unique key] ;

Example:

alter table test1 change name username varchar(10) unique key ;#change can modify all items such as field name, data type and constraint

6.4. Delete field

Syntax description:

alter table Table name drop field;

Example:

alter table test1 drop address;

6.5. Use if judgment to create tables and test autoincrement and filling

case

use db;
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));　　

analysis:

#if not exists: indicates whether the table to be created already exists. If it does not exist, continue to create it
#int(4) zerofill: if the value is less than 4 digits, fill it 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 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: indicates the unique key constraint of this field, and the data in 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

Test: create data test, automatic replenishment and self growth

insert into info (id,name,cardid,hobby) values(1,'Zhang San',18,'Basketball');

Note: however, if a row of records is deleted in the middle, you can only add one row of records with a specific id

7, Data table advanced operations

7.1. Clone the table and generate the data records of the data table into a new table

Method 1: copy the table first and then import the data

Syntax description:

#Only its table structure can be cloned, and the data records in it cannot be cloned

#Create a table with the same structure as the old table
create table New table name like Old table name;
#View records in table
select * from New table name ;
#View table structure
desc New table name ;
#Insert records from the old table into the new table
insert into New table name select * from Old table name;
select * from New table name;

Method 2: import data while copying tables

Syntax description:

create table New table name (select * from Old table name);
select * from New table name;

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

Method 1: delete

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

select * from stu4;
delete from stu4;
select * from stu4;
insert into stu4 (name,sex) values ('Old three','male');
select * from stu4;

Method 2: truncate delete

explain:

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

select * from Table name;
truncate table Table name;
#The difference between truncate and delete is that if the id is self incremented, delete is used to delete the record and then add the record. It is calculated from the next bit of the previous maximum id, and
insert into TEST03 (name,cardid) values ('wangwu','33333');
select * from TEST03;

7.3. Create a temporary table

explain:

#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.
Syntax description:

Example:

create temporary table test3 (id int(4) zerofill primary key auto_increment,name char(10) not null,age int(3));
insert into test3 values (1,'Zhang San',19);
create table test4(select * from test3);

7.4. Create foreign key constraints to ensure data integrity and consistency

explain:

Understanding of primary key table and foreign key table:
(1)Tables with public keywords as primary keys are primary key tables(Parent table, primary table)
(2)Tables with public keywords as foreign keys are foreign key tables(From the appearance)

Note: the fields of child tables associated with foreign keys must be set as primary keys. 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.

To create a foreign key constraint:

7.4. 1 create primary key table and slave table

#Create the main table live and define the attributes of each field
create table live(id int,city char(10));
desc live;
#Create det from table
create table det(id int,name char(10),sex char(3),age int(3));
desc det;

7.4. 2 add a primary key constraint to the primary key table. It is recommended to use "PK_" start.

alter table live add constraint PK_id primary key(id);
desc live;

7.4. 3 to add a foreign key from the table, it is recommended that the foreign key name be "FK_" And establish an association with the primary key table

explain:

#Add a foreign key to the det table and associate the id field of the det table with the id field of the live table. "FK_" is recommended for foreign key names start.

Syntax description:

alter table det add constraint FK_id foreign key(id) references live (id);

7.4. 4 when inserting a new data record, you must first the master table and then the slave table (data cannot be added if it is not in this order)

#Add data to main table first
insert into  live values(1,'Nanjing');
#Add data from table
insert into det values (1,'Zhang San','male',23);

Note: data can only be added to the slave table after adding data to the master table. There is only one data in the master table, and the second data cannot be added to the slave table

7.4. 5 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

#Delete the data from the table first
delete from det where id=3;
#Then delete the data of the main table
delete from live where id=3;

7.4. 6 view and release foreign key constraints and delete foreign keys

show create table det;
#Delete foreign key constraint
alter table det drop foreign key FK_id;
#Delete foreign key
alter table det drop  key FK_id;

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

7.4. 8 six common constraints in MySQL

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

Summary:

• The order of adding data to the master-slave table: first add data to the master table, and then add data to the slave table. The fields of foreign keys must exist in the master table, and the number of records in the slave table cannot exceed the number of records in the master table
• When deleting data, first delete the records in the slave table, and then delete the records in the master table
• If you don't follow the above, there's no big problem, just an error will be reported