MySQL commonly used SQL and commands from entry to deleting database and running away

create table table name(
	Column name 1 Data type 1,
	Column name 2 Data type 2,
	....
	Column Name n Data Type n
	);

insert into user2 values ​​(1,'李四','123'); // add delete from pet where ower = 'disn'; // delete update pet set name = '后裔' where ower = 'dfn'; // modify

①> 、< 、<= 、>= 、= 、<>	
②BETWEEN...AND	
③ IN(collection)	
④LIKE fuzzy query	
⑤_A single arbitrary character ⑥%Multiple arbitrary characters ⑦IS NULL 
⑧and or &&
⑨or or|| 
⑩not or!
Examples of query conditions:
SELECT * FROM user WHERE age >= 18;
SELECT * FROM user WHERE age >= 18 AND age <=36;
SELECT * FROM user WHERE age BETWEEN 40 AND 70;
SELECT * FROM user WHERE age IN (6,18,37);
// About NULL
SELECT * FROM user WHERE height = NULL; Error, because null value cannot use = or (!=) Judgment SELECT * FROM user WHERE height IS NULL; (correct)
SELECT * FROM user WHERE height IS NOT NULL; (correct)
// Query who has the last name Chen? < like>
SELECT * FROM user WHERE NAME LIKE '陈%';
// Query people whose second character of the name is new SELECT * FROM user WHERE NAME LIKE "_新%";
// Query people whose names are three characters SELECT * FROM user WHERE NAME LIKE '___';
// Query people whose names contain dog SELECT * FROM user WHERE NAME LIKE '%狗%';

 create table user (
	id int primary key auto_increment, // When creating a table, add a primary key constraint and complete the primary key auto-increment	
	name varchar(20)
 );
-- Composite primary key: A primary key composed of multiple fields, as long as the sum of the composite primary keys is not repeated. Any field in the composite primary key cannot be empty.
create table user2 (
 	id int,
 	name varchar(20),
 	password varchar(20),
 	primary key(id, name)
);

 create table user1 (
 	id int primary key auto_increment,
 	phone_num varchar(20) unique
 	 );
 create table user2 (
 	id int primary key auto_increment,
 	name varchar(20),
 	unique(id, name) // means that the two fields are not repeated together );
 	 

create table user3 (
	id int primary key auto_increment,
	name varchar(20) not null
	);

create table user4(
	id int primary key auto_increment,
	age int default 18,
	name varchar(20) not null
	);

create table table name(
....
Foreign key column constraint Foreign key name foreign key (foreign key column name) references Primary table name (primary table column name)
);
//Class create table classes(
	id int primary key,
	name varchar(20)
	);	
// Student table create table student (
		id int primary key,
		name varchar(20),
		class_id int,
		foreign key(class_id) references classes(id)
		);
		

Query all records For example: Query all records in the student table.

select * from student;

Query the specified fields, for example: query sname, ssex, class in student.

select sname,ssex,class from student;

Query all units in the teacher table, that is, the distinct depart column. <Exclude duplicate distinct>

select distinct depart from teacher;

Query all records in the score table whose scores are between 60 and 80 <query interval between...and...>

select * from score where degree between 60 and 80;
select * from score where degree > 60 and degree < 80;

Query the records with scores of 85, 86 or 88 in the score table select * from score where degree in(85, 86, 88);

Query the student table for students in class '95031' or whose gender is 'female'. <or means either>

select *from student where class = '95031' or sex = '女';

Query all records in the student table in descending order of class <desc, asc, default ascending (omitted)>.

select * from student order by class desc;

Query all records in the score table in ascending order of cno and descending order of degree select * from score order by cno asc,degree desc;

Query the number of students in class "95031" <statistics count>

select count(*) from student where class = '95031';
Query the student ID and course ID of the student with the highest score in the score table (subquery)

select sno, cno from score where degree = (select max(degree) from score ); where: select max(degree) from score to find the highest score first.

select sno,cno degree from score order by degree desc limit 0,1; where: the first number of limit indicates the starting point, and the second number indicates the number of entries. When there are multiple identical highest scores, bugs are likely to occur, so this method of query is not recommended.

Query the average score of each course select cno, avg(degree) from score group by cno;

Query the average score of the courses in the score table that are taken by at least 2 students and start with 3.

select cno, avg(degree) from score group by cno having count(cno) >= 2 and cno like '3%';

Query the sno column with scores greater than 70 and less than 90.

select sno, degree from score where degree between 70 and 90;
Query the sname, cno and degree columns of all students.
select sname, cno, degree from student, score where student.sno = score.sno;
Query the sno, cname and degree columns of all students select sno, cname, degree from course , score where course.cno = score.cno;
Query the average score of each course for students in class "95031".
select cno, avg(degree) from score where sno in (select sno from student where class = '95031') group by cno;
Query the records of all students who have taken the elective course "3-105" and whose scores are higher than those of student No. "109" in "3-105".
select * from score where cno = '3-105' and degree > (select degree from score where sno = '109' and cno = '3-105');
Query all records whose scores are higher than those of the student with the student number "109" and the course number "3-105" select * from score where degree > (select degree from score where sno = '109' and cno = '3-105');
Query all sno, sname, sbirthday of students born in the same year as student number 108,101
select *from student where year(sbirthday) in (select year(sbirthday) from student where sno in(108, 101));
Query the grades of students taught by teacher "Zhang Xu" select * from score where cno = ( select cno from course where tno = (select tno from teacher where tname = "Zhang Xu"));
Query the names of teachers whose students taking a course are more than 5.
select tname from teacher where tno = (select tno from course where cno = (select cno from score group by cno having count(*) > 5));
Query the CNOs for courses with scores above 85
select cno, degree from score where degree > 85;
Query the score table of courses taught by teachers in the "Computer Science Department" select * from score where cno in (select cno from course where tno in (select tno from teacher where depart = "Computer Science Department"));
Query the CNO, SNO and degree of students who have taken the elective number "3-105" and whose grades are at least higher than those of the elective number "3-245", and sort them in descending order by degree.
any at least one.

select * from score where cno = '3-105' and degree > any(select degree from score where cno = '3-245') order by degree desc;

select * from score where cno = '3-105' and degree > all(select degree from score where cno = '3-245') order by degree desc;

select * from score a where degree < (select avg(degree) from score b where a.cno = b.cno);
Table a
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 91 |
| 102 | 3-105 | 92 |
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 104 | 3-105 | 81 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
12 rows in set (0.00 sec) 

Table b
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 91 |
| 102 | 3-105 | 92 |
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 104 | 3-105 | 81 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
12 rows in set (0.00 sec) 

Data preparation for analytical use cases:
mysql> select * from person;
+----+--------+--------+
| id | name | cardId |
+----+--------+--------+
| 1 | Zhang San | 1 |
| 2 | Li Si | 3 |
| 3 | Wang Wu | 6 |
+----+--------+--------+
3 rows in set (0.00 sec)
mysql> select * from card;
+------+-----------+
| id | name |
+------+-----------+
| 1 | Meal Card|
| 2 | CCB Card|
| 3 | Agricultural Bank Card|
| 4 | Business Card|
| 5 | Postal Card |
+------+-----------+
5 rows in set (0.00 sec)

select * from person inner join card on person.cardId = card.id;
+----+--------+--------+------+-----------+
| id | name | cardId | id | name |
+----+--------+--------+------+-----------+
| 1 | Zhang San| 1 | 1 | Meal Card|
| 2 | Li Si | 3 | 3 | Agricultural Bank Card |
+----+--------+--------+------+-----------+
2 rows in set (0.00 sec)

select * from person left join card on person.cardId = card.id;

+----+--------+--------+------+-----------+
| id | name | cardId | id | name |
+----+--------+--------+------+-----------+
| 1 | Zhang San| 1 | 1 | Meal Card|
| 2 | Li Si | 3 | 3 | Agricultural Bank Card |
| 3 | Wang Wu | 6 | NULL | NULL |
+----+--------+--------+------+-----------+
3 rows in set (0.00 sec)

select * from person right join card on person.cardId = card.id;

+------+--------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------+--------+------+-----------+
| 1 | Zhang San| 1 | 1 | Meal Card|
| 2 | Li Si | 3 | 3 | Agricultural Bank Card |
| NULL | NULL | NULL | 2 | CCB Card |
| NULL | NULL | NULL | 4 | Business Card |
| NULL | NULL | NULL | 5 | Postal Card |
+------+--------+--------+------+-----------+
5 rows in set (0.01 sec)

mysql> select * from person full join card on person.cardId= card.id;
ERROR 1054 (42S22): Unknown column 'person.cardId' in 'on clause'
**** How to solve the problem that MySQL does not support full join ****
 <Left connection + right connection>, that is, connect the left and right connections through union. <Left connection union right connection>.
eg:

select * from person left join card on person.cardId = card.id union select * from person right join card on person.cardId = card.id;

+------+--------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------+--------+------+-----------+
| 1 | Zhang San| 1 | 1 | Meal Card|
| 2 | Li Si | 3 | 3 | Agricultural Bank Card |
| 3 | Wang Wu | 6 | NULL | NULL |
| NULL | NULL | NULL | 2 | CCB Card |
| NULL | NULL | NULL | 4 | Business Card |
| NULL | NULL | NULL | 5 | Postal Card |
+------+--------+--------+------+-----------+
6 rows in set (0.01 sec)

MYSQL executes statements in a strict order and cannot change the select statement.
from
where
group by
having
order by

For example:
a --> -100
update user set money = money - 100 where name = 'a';
b --> +100
update user set money = money + 100 where name = 'b';
-- In the actual program, if only one SQL statement is executed successfully, but the other one is not executed successfully? Then the data before and after will be inconsistent.
update user set money = money - 100 where name = 'a';
update user set money = money + 100 where name = 'b';
In multiple SQL statements, there may be requirements for simultaneous success or simultaneous failure.

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
//Create table create database bank;
create table user (
	id int primary key,
 name varchar(20),
 money int
 );
// First insert a user data a into the table.
insert into user values ​​(1,'a',1000);
Query OK, 1 row affected (0.00 sec)
// Perform a rollback operation.
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
// After executing the rollback, check the data table information and find that even if rollback is called, the inserted data still exists. This means that rollback is not possible at the moment.
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+
1 row in set (0.00 sec)
// You can set msql's rollback auto-commit to false.
set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
// In other words, the above set autocommit = 0; operation turns off MySQL's automatic commit.
*******Insert data again:*******
insert into user values ​​(2,'b',1000);
Query OK, 1 row affected (0.00 sec)
// Check the table after inserting the data, user 2's data has been added successfully.
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
2 rows in set (0.00 sec)
// Perform a rollback operation.
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
// After rolling back, check the table again and find that the data just inserted has been deleted.
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+
1 row in set (0.01 sec)
**** For this scenario, if you want user b to successfully submit the data, you can use the commit; command to perform a manual submission operation. After manual submission, if you want to undo it again through rollback, it is not possible. In other words, once the transaction is submitted, the executed SQL statement cannot be undone, which means that once the transaction is submitted, the data will have a lasting effect.

Right now:
 The current default mode is auto-commit mode, and executing rollback is invalid at this time. Execute the following SQL statement:
 start transaction; (or begin;)
 update user set money = money - 100 where name = 'a';
 update user set money = money + 100 where name = 'b';
 After inserting user data a and b, execute rollback again and find that the transaction can be rolled back successfully. You can successfully switch to the mode of manually opening transactions. If you want the inserted data to take effect, you also need to manually execute commit to commit the operation.
 After the transaction is started, once it is committed, it cannot be rolled back, that is, the current transaction is over when it is committed.
 

mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ |
+--------------------------------+
1 row in set (0.00 sec)

For example:
create table myorder(
		product_id int,
		customer_id int,
		product_name varchar(20),
		customer_name varchar(20),
		primary key(product_id, customer_id
	);
	In the current table, columns other than the primary key only depend on some fields of the primary key. This does not satisfy the second normal form and usually requires table splitting.
create table myorder(
		order_id int primary key,
		product_id int,
		customer_id int
	);
create table product (
		id int primary key,
		name varchar(20)
	);
create table customer(
		id int primary key,
		name varchar(20)
		);
After being split into three tables, the second normal form is met.

create table student(
	sno varchar(20) primary key,
	sname varchar(20) not null,
	ssex varchar(20) not null,
	sbrithday datetime,
	class varchar(20)
	);

create table student(
	sno varchar(20) primary key,
	sname varchar(20) not null,
	ssex varchar(10) not null,
	sbirthday datetime,
	class varchar(20)
)

create table teacher(
	tno varchar(20) primary key,
	tname varchar(20) not null,
	tsex varchar(20) not null,
	tbirthday datetime,
	prof varchar(20) not null,
	depart varchar(20) not null
	);

create table course(
	cno varchar(20) primary key,
	cname varchar(20) not null,
	tno varchar(20) not null,
	foreign key(tno) references teacher(tno)
	);

create table score(
	sno varchar(20) not null,
	degree decimal,
	primary key (sno, cno),
	foreign key (sno) references student(sno),
	foreign key (cno) references course (cno)
	);

insert into student values ​​('101','Zeng Hua','Male','1977-09-01','95033');
insert into student values ​​('102','匡明','男','1975-10-02','95031');
insert into student values ​​('103','王丽','女','1976-01-23','95033');
insert into student values ​​('104','李军','男','1976-02-20','95033');
insert into student values ​​('105','王芳','女','1975-02-10','95031');
insert into student values ​​('106','陆君','男','1974-06-03','95031');
insert into student values ​​('107','王尼马','男','1976-02-20','95033');
insert into student values ​​('108','张全蛋','男','1975-02-10','95031');
insert into student values ​​('109','赵铁柱','男','1974-06-03','95031');

insert into teacher values ​​('804','李成','男','1958-12-02','副教授','计算机系');
insert into teacher values ​​('856','张旭','男','1969-03-12','讲座师','电子工程部');
insert into teacher values ​​('825','王萍','女','1972-05-05','教助手','计算机系');
insert into teacher values ​​('831','Liu Bing','Female','1977-08-14','Teaching Assistant','Department of Electronic Engineering');

insert into course values ​​('3-105','Introduction to Computer', '825');
insert into course values ​​('3-245','operating system', '804');
insert into course values ​​('6-166','Digital Circuit', '856');
insert into course values ​​('9-888','Advanced Mathematics', '831');
 
insert into score values('103','3-245','86');
insert into score values('105','3-245','75');
insert into score values('109','3-245','68');
insert into score values('103','3-105','92');
insert into score values('105','3-105','88');
insert into score values('109','3-105','76');
insert into score values('103','3-105','64');
insert into score values('105','6-166','79');
insert into score values('109','6-166','81');


create table person(
	id int primary key auto_increment,
	name varchar(20),
	cardId int
);

create table card (
	id int,
	name varchar(20)
);

insert into card values ​​(1,'Rice Card');
insert into card values ​​(2,'ＣＣ银行卡');
insert into card values ​​(3,'Agricultural Bank Card');
insert into card values ​​(4,'Business Card');
insert into card values ​​(5,'Postal Card');

insert into person values ​​(1,'张三',1);
insert into person values ​​(2,'李四',3);
insert into person values ​​(3,'王五',6);