Common statements:

--show database
SHOW DATABASES;

#Create a table score in stuinfo, including column id,name, and grade scores

--Select database
USE stuinfo;
--Create table
CREATE TABLE score(
   id INT,    -- auto_increment Automatic growth
   NAME VARCHAR(20),-- varchar() Variable length string
   grade INT);

#Query table

Syntax: select Query list from Table name;

#The query list can be fields, constants, expressions and functions in the table (equivalent to methods in java)
#The result of the query is just a virtual table

SELECT * FROM score;
SELECT id FROM score;
SELECT 100;
SELECT SUM() FROM score;

#Insert data

Syntax:
insert into Table name (Column name 1,Column name 2)values(Value 1,Value 2);
insert into Table name values(data);

INSERT INTO score (NAME,grade) VALUES ('Zhang San',40);    
INSERT INTO score VALUES(2,'Li Si',80);
INSERT INTO score VALUES(1,'Wang Wu',90);

#Modify statement

Syntax: update Table name set Column name 1=Value 1,Column name 2=Value 2 where condition;

UPDATE score SET id = 1 WHERE NAME='Zhang San';

#Delete data

Syntax:
delete from Table name where condition; 
drop table Table name;
truncate table Table name;

The difference between delete, drop and truncate:
delete deletes the data in the table without deleting the table structure
drop deletes the table structure, and the execution speed is the fastest
truncate deleting data does not delete the table structure, but will free up space

--Select database
USE myschool;
--If there is a table to create, delete it
DROP TABLE IF EXISTS student;
--Create table
CREATE TABLE student(
  studentNo INT(4) NOT NULL PRIMARY KEY COMMENT 'Student number',
  loginPwd VARCHAR(20) NOT NULL COMMENT 'password',
  studentName VARCHAR(50) NOT NULL COMMENT 'Student name',
  sex CHAR(2) NOT NULL DEFAULT 'male' COMMENT 'Gender',
  gradeId INT(4) UNSIGNED COMMENT 'Grade number',
  phone VARCHAR(50) COMMENT 'Telephone',
  address VARCHAR(255) DEFAULT 'Address unknown' COMMENT 'address',
  bornDate DATETIME COMMENT 'date of birth',
  email VARCHAR(50) COMMENT 'Mailbox number',
  identityCard VARCHAR(18) UNIQUE KEY COMMENT 'ID number'
);

If an Error occurs, it indicates an Error
Find near after Error Check which one is wrong and solve it
Note: all symbols must be in English

--View the current default storage engine
SHOW VARIABLES LIKE 'storage_engine%';

#Set the storage engine for the table

Syntax:
CREATE TABLE Table name(
   Column name data type..
)ENGINE = Storage engine(MYISAM/INNODB)

#Advanced query
#Modify the table and operate on the created table

--Modify table name
 Syntax: alter table Old table name rename to New table name;

#Change the name of the stu table to teacher

ALTER TABLE stu RENAME TO teacher;
SELECT * FROM teacher;

--Add field syntax:
alter table Table name add Field name data type [attribute|notes];

ALTER TABLE teacher ADD id INT;

--Delete field
 Syntax: alter table Table name drop Field name;

ALTER TABLE teacher DROP id;

--Modify field syntax:
alter table Table name change Original field name new field name data type;

ALTER TABLE teacher CHANGE nums mumber INT;

Small exercise:
1. Create a chart of accounts subject, column name subjectno (course number),
Subjectname, classhour, gradeid
2. Change chart of accounts subject to course,
3. Change the field subjectNo to courseNo,
Change subjectName to courseName
4. Add field classNum,
5. Delete field classHour

Primary key: a record that is uniquely identified in a table. There can be no duplicate and cannot be empty

Comparison between primary key and unique constraint
Ensure uniqueness # whether null is allowed # how many in a table
Primary key constraint: Yes No. there can only be one at most
The only constraint ， is that ， can have multiple

Foreign key: it is the primary key of another table. The foreign key can be duplicate or null. It is used to establish contact with other tables.
Foreign key: an association between two tables
A table can be associated with multiple tables
Function: limit the insertion of table data. Only when there is data in the associated table can data be added to the table where the foreign key is located

matters needing attention:
1. The foreign key relationship is designed on the slave table to limit the value of the slave table
2. The foreign key column type of the slave table is the same as or compatible with the associated column of the primary key
3. The associated column of the primary table must be a primary key or unique
4. When inserting a table, first insert the primary table (foreign key associated table) and then insert the secondary table (table with foreign key)
When deleting data, first delete the secondary table, and then delete the primary table

--Add primary key
 Syntax: alter table Table name add constraint Primary key name primary key Table name(Primary key field)

ALTER TABLE teacher ADD CONSTRAINT `pk_teacher` PRIMARY KEY teacher(mumber);

--Select database
USE stuinfo;

--1,First build a professional table to store all professional information
DROP TABLE IF EXISTS major;
CREATE TABLE major(
   id INT COMMENT 'major id number',
   mjname VARCHAR(20) COMMENT 'Professional name'
);
INSERT INTO major VALUES (101,'UI');

SELECT * FROM major;

--Add primary key to specialty table
ALTER TABLE major ADD PRIMARY KEY(id);

--2,Create another student table
DROP TABLE IF EXISTS student;
CREATE TABLE student(
   stuid INT COMMENT 'student ID',
   stuName VARCHAR(20) COMMENT 'Student name',
   mj_id INT COMMENT 'Student's major',
   FOREIGN KEY(mj_id) REFERENCES major(id)
)
INSERT INTO student VALUES (1,'Peng Kai',101);
INSERT INTO student VALUES (2,'Wang Jiaqi',101);
INSERT INTO student VALUES (2,'Wang Jiaqi',100000);

SELECT * FROM student;
--Insert the main table before inserting the table(Table associated with foreign key)，Re insert from table(Table where the foreign key is located)
INSERT INTO major VALUES (100,'java');
SELECT * FROM student;
SELECT * FROM major;

Small exercise:
1. Create an employee table. The employee field has
id integer type (primary key), name character type, gender character type, birthday date type,
entry_date date type, job integer type (foreign key)
2. Create a type of work table. The fields of jobs are
job_id integer type (primary key), salary decimal type
'2020-11-23'
3. Requirements: create the main foreign key and insert two pieces of data

--Add foreign key syntax:
ALTER TABLE Table name ADD CONSTRAINT Foreign key FOREIGN KEY(Foreign key field) REFERENCES Associated table name(Associated field)

--Create employee table 
CREATE TABLE employee(
   id INT PRIMARY KEY,
   NAME VARCHAR(20),
   gender VARCHAR(20),
   birthday DATETIME,
   entry_date DATETIME,
   job VARCHAR(10) ,
   FOREIGN KEY(job) REFERENCES jobs(job_id)
   -- Add foreign keys directly when creating
)
--Create work type table
CREATE TABLE jobs(
   job_id INT PRIMARY KEY,
   salary DOUBLE
)
--After creating the table, add the foreign key separately
ALTER TABLE employee ADD CONSTRAINT pk_job 
FOREIGN KEY(job)
REFERENCES jobs(job_id);

Small exercise:
1. Create a personal information table PersonInfo containing the field stu_name, sex, age, age, phone number, add address
2. Insert 3 rows of data with the syntax of inserting multiple data
3. Write SQL statements to extract the data of name and mobile phone number from the personal information table and store them in the phoneList of the address book table
4. Query the data of the new table phoneList
 

#PERIOD_DIFF(201910, 201903); Returns the month difference between two times. The result is 7

SELECT PERIOD_DIFF(CURDATE(hiredate)，CURDATE(NOW())) FROM employees;

SELECT PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(hiredate ,'%Y%m')) FROM employees;