Database notes
Database foundation
1. Start MySql service on the terminal:
net start mysql;
2. Connect to MySQL: (add one, not - proot; oh)
mysql -h localhost -p 3306 -u root -proot (h Database links, p Database port number, u Database user name -p Database password, p (no space between and password)
3. View database:
`show databases;`
4. Create database:
`create database test;(test Database name)`；
5. Enter the database:
use test;
6. View the tables in the database:
show tables;
7. Modify the database code:
bank database name
alter database bank character set utf8;
8. Modify the coding format of specific table:
user is the table name
alter table user CONVERT TO CHARACTER SET utf8
9. View the encoding format of the database:
show variables like 'Database name'
10. View the coding format of the data table:
show create table Table name'
7. Establish data sheet:
create table pet ( --(pet Table name)
name VARCHAR(20), --(name Key, varchar char String data type, data Date related type)
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE);
8. View the created data table structure:
describe pet;
9. Exit the database server:
exit;
10. How to view data records in a data table:
select*from pet;
11. How to add data to a data table:
insert into pet
values('pullball','diane','hamster','f','1990-03-03',NULL);
12. How to delete data from tables in the database:
delete form pet where name='Zhou Xingxing';
13. How to modify data:
updata pet set name='Zhou Xing' where owner='bei'; (-- updata to update pet Table name set Setting change name Pass for Zhou Xing owner=bei To lock a piece of data in the table.
Database Constraints
14.mysql table creation constraints: (primary key constraint, foreign key constraint, auto increment constraint, unique constraint, non empty constraint, default constraint)
15. Primary key constraint (it can uniquely determine a piece of data in a table, that is, by adding constraints to a field, we can make the field not duplicate and not empty).
create table user(id int primary key, name varchar(20)); (-- id It is a primary key and cannot be repeated)
Federated primary key:
create table user2 (id int,name varchar(20),password varchar(20),primary key(id,name)); (id,name It is a primary key. Any primary key of a union primary key cannot be empty. A union primary key is as long as the two primary keys do not duplicate) insert into user2 values(1,'Zhang San','123456'); insert into user2 values(1,'Zhang San','123456');(--report errors) insert into user2 values(2,'Zhang San','123456');(--No error reporting)
16. Auto increment constraint: primary key auto_increment;
creste table user(id int primary key auto_increment,name varchar(20));
insert into user3 (name) values ('Zhang San');
17. Add a primary key to a table:
(1) Add directly alter table user4 add primary key(id); (2) adopt modify add to alter table user4 modify name varchar primary key;
18. Delete the primary key of a table:
alter table user4 drop primary key;
19. Unique constraint: the value of the constraint modification field cannot be repeated
Add unique constraint: (1)ALTER table user5 add unique(name); (2)create table user6 (id int, name varchar, unique(name));
20. Delete unique constraint:
alter table user6 drop index name;
21. Non empty constraint: the modified field cannot be empty
create table user7 (id int, name varchar(20) not null);
22. Default constraint: when the inserted field value is, it will be set as the default value if no value is transferred
create table user7 (id int,name varchar(20),age int default 10);
23. Add default constraint:
alter table user3 ALTER name set DEFAULT 'zhangsansan';
24. Foreign key constraint: involves two tables, parent table and child table (primary table and secondary table)
Data values that do not exist in the main table cannot be used in the sub table.
The data values used by sub tables in the main table cannot be deleted.
For example, when creating a child table, there are foreign keys in the child table, but the foreign keys in the parent table do not have this number, which will fail to create a child table.
--Class table create table classes ( id int primary key , name varchar(20)); --Student list create table students ( id int primary key , name varchar(20), calss_id int,(--Associated class table id , establish the relationship between the two tables) foreign key(class_id) references classes(id);(--binding id，Name class_id From classes Medium id)
25. Three design paradigms of database: (whether it is satisfied or not depends on the actual development)
1. The first normal form: non separable normal form: the field is the smallest cell and cannot be split in.
((detachable) create table adress( id int, name varchar(20),adress varchar(50) ); insert into adress values(1,'Zhang San','Xinjiang county, Yuncheng City, Shanxi Province, China') (Inseparable) create table adress1( id int,name varchar(20),cuntry varchar(50),privice varchar(20), city varchar(20),details varchar(20) ); insert into adress2 values(1,'Zhang San','China','Shanxi Province','Yuncheng City','Xinjiang county');
2.Second normal form: on the basis of meeting the first normal form, every column except the primary key must be completely dependent on the primary key. If incomplete dependency occurs, it can only occur in the case of joint primary keys.
3.Third normal form: on the premise of meeting the second normal form, there can be no transfer dependency between columns except the primary key column.
Database query
1. Query all records in the table:
select * from student;(student Table name)
2. Specified field records in the query table:
select ssname,ssex,class from student;
where is followed by query conditions and statistical conditions
3. Data range under a field in the query table: (between... and...)
select * from score where degree between '60' and '80';(--''Or not) select * from score where degree >70 and degree<80;
4. Several specific values under the query field:
select * from score where degree in (60,88,78);
5. Query data records with different fields: (where or condition)
Records of class 95031 or gender female in the query table:
select * from student where class='95031' or ssex='female';
Add ascending and descending conditions after order by
7. Ascending and descending (asc generally defaults to ascending, desc):
Insert all records in the student table in ascending class order:
select * from student order by class;(--The default is ascending) select * from student order by class asc;(--Ascending order) select * from student order by class desc;(--Descending order)
8. Ascending by a field descending by a field:
In the ascending order of student number, the student number with the lowest score:
select * from score order by cno asc,degree desc;
9. Statistics: (select count(*) from student where condition)
Query the number of students in class "95031":
select count(*) from student where class="95031";
10. Sub query or sorting:
Query the student number and course number with the highest score in score: steps
(1) Find the highest score:
select max(degree) from score;
(2) . find sno and cno with the highest scores:
select sno,cno from score where degree=(select max(degree) from score);
select sno,cno from score where degree=(select max(degree) from score);
The method of sorting to find the highest score (generally not used if there are defects):
limit starts from the first number to the number of digits.
select sno,cno,degree from score order by degree
desc limit 0,1;
11. Query the average score of each course:
avg(); average
group by; group (group each course first and then average)
select avg(degree) from score where cno='3-105';
select cno ,avg(degree) from score group by cno;
12. Statistical count
Average avg
What starts with like (fuzzy query)
%Is a wildcard
count(*) the statistical quantity can be displayed
After grouping, aggregate data is filtered
Query the average score of courses starting with 3 for at least 2 students in the score table.
First, group the cno courses and compare the cno courses greater than 2
select cno from score group by cno having count(cno)>=2 and cno like '3%';
13.select sname,cno,degree from student,score where student.sno=score.sno;
Query sname CNO degree (two table joint query)
14. Three table joint query (find out the common table, and the corresponding columns in the common table will be replaced)
15. Display the corresponding column (student.sno)
16. Alias as (student.sno as stu)
17. Inquire about the average score of students in class 95031 without courses
(the first step is to take out the students belonging to 95031, and then take them as conditions to screen the scores in the score table, and then group them by courses, so as to get the average score of no courses)
SELECT AVG(degree) FROM score WHERE sno IN(SELECT sno FROM student WHERE class='95031');
--Query student no. of class 95031; SELECT sno FROM student WHERE class='95031'; --Find out the corresponding grades according to the student number.in Relationship between or; SELECT * FROM score WHERE sno IN(SELECT sno FROM student WHERE class='95031'); --Group the course numbers and find the average score SELECT cno,AVG(degree) FROM score WHERE sno IN(SELECT sno FROM student WHERE class='95031') GROUP BY cno;
18. Query the records of all students whose grades of elective course 3-105 are higher than those of student 109:
1. First, it involves the student number, course and achievement.
2. First find out the grades of students with student No. 109 in course 3-105.
3. Find out the data of 3-105 courses for students whose grades are higher than student number 109.
4. After screening out the results of pseudo 3-105 in the course.
20.year acquisition:
SELECT YEAR(sbirthday) FROM student WHERE sno IN (101,108);
Query students with the same year and student number 101102
20. Inquire about the results of the students taught by teacher Zhang Xu;
21. Query the name of the teacher who takes a course with more than 5 students;
Query courses greater than 5:
SELECT tname FROM teacher WHERE tno IN (SELECT tno FROM course WHERE cno IN (SELECT cno FROM score GROUP BY cno HAVING COUNT(*)>5));
22.not in
union Union; (join tables together)
23. At least, greater than at least one of them, any (greater than one of them)
Query the cno degree whose elective grade 3-105 is at least greater than 3-245
select * from score where cno='3-1056' and degree>any(select degree from score where cno='3-245' order by degree desc); 24.And? all(Greater than all (any one) Query elective 3-105 Score and at least 3-245 Achievement cno degree ```sql select * from score where cno='3-1056' and degree>all(select degree from score where cno='3-245' order by degree desc);(--Greater than all in parentheses)
25. Query the names and birthdays of teachers and all students (two tables can be connected by union, and as can be aliased. After the first table is aliased, the second table will be automatically modified to a good alias)
26. Check the scores of students whose scores b are lower than the average score of the course.
27. Check the names of all the teachers who teach the course, (teaching means arranging courses in the curriculum)
33. Query the class number of at least two boys (first check the class number, take the class number as the group, and then check the boys)
Adding conditions after having (conditions to be followed after grouping)
select class from student where ssex='male' group by class having count(*)>1
34. Find out the students whose names are not Li in the table: (first, find out the students whose names are Li, that is, like% is used in the fuzzy query. If they are not Li, that is not)
SELECT *FROM student WHERE sname NOT LIKE 'Lee%';
35 query the name and age of each student:
Current year: select year(now())
SELECT sname,YEAR(NOW())-YEAR(sbirthday) AS 'Age' FROM student;
36. Query the largest and lowest year in the student table: (function max(),min()) only compares the size of the number.
SELECT MAX(sbirthday) AS 'maximum',MIN(sbirthday) AS 'minimum' FROM student;
37. Query table records by shift number and age from big to small: the first thought should be sorting:
select * from student order by class desc,sbirthday;
38. Inquire about the courses taught by male teachers.
SELECT * FROM course WHERE tno IN( SELECT tno FROM teacher WHERE tsex='male');
39. Query the highest score student to get cno: (use the max function)
Note: the max function is usually after select
SELECT * FROM score WHERE degree=(SELECT MAX(degree) FROM score );
39.
SELECT degree FROM score WHERE cno IN (SELECT cno FROM course WHERE cname='Digital electricity') AND sno IN (SELECT sno FROM student WHERE ssex='male');
40. Query grade:
Construction grade table:
Four join queries for SQL
1, Internal connection (filter the same data)
Inner join or join (inner join is the same as join, and the subsequent on represents the condition, and the join table represents the on condition)
2, External connection (connecting multiple tables)
1. Left outer join or left outer join (mainly on the left)
All the data in the left table will be taken out, while the data in the right table will be displayed if they are equal, and NULL will be filled if they are not
2. right join or right outer join (right side is dominant)
All the data in the right table will be taken out, while the data in the left table will be displayed if they are equal, and NULL will be filled if they are not
3. Complete external connection full join or full outer join (all data are displayed if they are the same, and null if they are not)
For MySQL to realize all external connection, the left and right external connections should be connected by union:
3, To create two tables
Error encountered while creating Table: Table 'card' already exists (because this Table already exists)
No foreign keys were created
1- inner join query: (inline query is to query the related data in two tables through a relative field)
2.left join (all the data in the left table will be taken out, while the data in the right table will be displayed if they are equal, and null will be filled if none)
3. Full join all external connections (full join mysql does not support all external connections) (connect with the left external union and the right external union)
MySQL transaction(
In MySQL, transaction is actually the smallest and indivisible unit of work. Transaction can ensure the integrity of a business.
For example, there is such a requirement: transfer. There are two sql statements. One cannot succeed and the other fails, which leads to data asymmetry. Therefore, sometimes multiple statements are required to succeed at the same time and fail at the same time. At this time, transactions appear.
How to control transactions in MySQL:
1.MySQL enables transactions by default (auto commit).
The function of starting a transaction by default is that when we execute an sql statement, the effect will be reflected immediately and cannot be rolled back.
– if you want to rollback, set the automatic submission of MySQL to false, (set autocommit=0);
– rollback refers to revoking the execution effect of sql,
(turn off auto commit set autocommit=0) (turn on auto commit set autocommit=1) (rollback statement)
(query whether to submit automatically)
2. Transactions are divided into automatic commit, manual commit and transaction rollback.
Auto commit is auto commit by default in MySQL. It cannot be rolled back.
Manual commit refers to manual commit. It cannot be rolled back.
Transaction rollback: undo the rollback of statement execution effect.
Zhang Sanxiang aa transfers 2 yuan
3. Manually start the transaction and roll back
The transaction indicates that it can be rolled back,
begin; --perhaps start transaction;--Can help us manually start a transaction (rollback);
4. Four characteristics of affairs
(1) Atomicity: transactions are inseparable from the smallest unit.
(2) Consistency: the transaction requires that sql statements in the same transaction must succeed or fail at the same time, otherwise it will cause information asymmetry.
(3) Isolation: transaction 1 and transaction 2 are isolated.
(4) Persistence: once the transaction is commit ted, it cannot be returned (rolled back).
Transaction on: (rollback can be rolled back)
1. Modify the default submission: set autocommit=0;
2.begin；
3. start transcation;
**Transaction manual commit**
commit；
Transaction manual rollback
rollback；
Transaction isolation
Check the isolation level of the transaction: MySQL 5. X statement: select @@global.tx_isolation;
Modify transaction isolation level: set transaction isolation level read committed;
MySQL's default isolation level is repeatable read;
mysql 8 version: (can be set)
System level isolation: select @ @ global. Transaction_isolation;
Session level isolation: select @@transaction_isolation;
Modify the isolation level to serialization: set global transaction isolation level serializable;
Dirty read: refers to that one transaction reads uncommitted data from another transaction.
There are four types: (isolation level can be set for all four types)
1. Read uncommitted: read uncommitted
2. Read submitted: read committed; (non repeatable reading may occur, and the data of the previous minute and the next minute may be different)
3. Repeatable read: repeatable read; (phantom read may occur, that is, two transactions a and B operate the same table in different places. After one transaction commit s, the other transaction will not read. This is phantom read)
4. Serialization: serializable (b can see a at this time, but when a transaction is in progress, two transactions must wait until the transaction is commit ted).
——Read uncommitted: for example, there are two transactions a and b. transaction a is saved in the virtual table but has not been committed. At this time, transaction b can see transaction a, which is read uncommitted.
——Read committed: