Mysql
brief introduction
What is a database? What is a database management system? What is SQL? What is the relationship between them?
Database:
The English word DataBase is called DB for short. A combination of files that store data in a format.
As the name suggests: a warehouse for storing data is actually a pile of files. Stored in these files
Data with a specific format.
Database management system:
DataBaseManagement, or DBMS for short.
The database management system is specially used to manage the data in the database. The database management system can add, delete, modify and query the data in the database.
Common database management systems:
MySQL, Oracle, MS SqlServer, DB2, sybase, etc
SQL: Structured Query Language
Programmers need to learn SQL statements. Programmers write SQL statements, and then DBMS is responsible for executing SQL statements, and finally complete the addition, deletion, modification and query of data in the database.
SQL is a set of standards. Programmers mainly learn SQL statements. This SQL can be used in mysql, Oracle and DB2.
The relationship between the three?
DBMS – execute – > sql – operate – > DB
First install the database management system MySQL, and then learn how to write SQL statements. After writing SQL statements, the DBMS executes the SQL statements, and finally completes the data management of the database.
View Mysql service
Computer – > right click – > management – > services and applications – > services – > find mysql services
MySQL service is in the "start" status by default. It can only be used after MySQL is started. By default, it is started automatically. Automatic startup means that the service will be started automatically the next time the operating system is restarted
Right click on the service:
start-up
Restart service
Out of Service
...
You can also change the default configuration of the service:
Right click the service, click properties, and then select the startup method:
Automatic (delayed start)
automatic
Manual
Disable
In the Windows operating system, use commands to start and shut down mysql services
Syntax:
net stop service name;
net start service name;
The above commands can be used for the start and stop of other services.
Client login to Mysql database
Use the mysql.exe command under the bin directory to connect to the MySQL database server
Local login (show the form of writing password):
C:\Users\Administrator>mysql -uroot -p123456 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.36 MySQL Community Server (GPL) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Local login (in the form of hidden password):
C:\Users\Administrator>mysql -uroot -p Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.36 MySQL Community Server (GPL) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
mysql common commands
1. Exit mysql: exit
2. Check which databases are in mysql?
show databases;
Note: it ends with a semicolon, which is a semicolon in English.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+
mysql comes with four databases by default.
How to choose to use a database?
mysql> use test; Database changed
Indicates that a name called test Database.
3. How to create a database?
mysql> create database bjpowernode; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | bjpowernode | | mysql | | performance_schema | | test | +--------------------+
4. Check which tables are under a database?
Select a database: MySQL > use MySQL;
Display the tables under the database: MySQL > show tables;
+---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | host | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | servers | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+
Note: the above commands are not case sensitive.
View the version number of mysql database
mysql> select version();
mysql> select version(); +-----------+ | version() | +-----------+ | 5.5.36 | +-----------+
View which database is currently in use
mysql> select database();
mysql> select database(); +-------------+ | database() | +-------------+ | bjpowernode | +-------------+
Note: mysql does not execute when it is missing ";"; "indicates the end!
Note: \ c is used to terminate the input of a command.
Tables: basic cells in a database
What is a table? Why use tables to store data?
full name Gender Age(Columns: Fields) --------------------------- Zhang San male 20 ------->Line (record) Li Si female 21 ------->Line (record) Wang Wu male 22 ------->Line (record)
The data in the database is represented in the form of tables. Because the table is more intuitive.
Any table has rows and columns:
row: called data / record.
column: called a field. For example, name field, gender field and age field.
Learn about:
Each field has attributes such as field name, data type and constraint.
The field name is understandable. It is an ordinary name. Just see the name and know the meaning.
Data type: string, number, date, etc. later.
Constraints: there are also many constraints, one of which is called uniqueness constraint. After this constraint is added, the data in this field cannot be repeated.
Classification of sql statements
There are many SQL statements. It's best to classify them, so it's easier to remember.
Divided into:
DQL:
Data query language (all queries with select keyword are query statements)
select...
DML:
Data operation language (DML is used to add, delete and modify data in the table)
insert,delete,update
insert increment
Delete delete
update change
This is mainly the data in the operation table.
DDL:
Data definition language
All with create, drop and alter are DDL.
DDL mainly operates on table structure. Not data in table.
create: new, equivalent to adding
drop: deleting
alter: modify
This addition, deletion and modification is different from DML. It mainly operates on the table structure.
TCL:
Transaction control language
Including:
Transaction commit: commit;
Transaction rollback: rollback;
DCL:
Is a data control language.
For example: grant authorization, revoke permission, revoke
Import of data
Bjpwernode.sql is the database table I prepared for you in advance.
How to import data from sql files?
mysql> source D:\course\03-MySQL\document\bjpowernode.sql
Note: do not have Chinese in the path!!!!
About imported tables
mysql> show tables; +-----------------------+ | Tables_in_bjpowernode | +-----------------------+ | dept | | emp | | salgrade | +-----------------------+
dept is the Department table
emp is the employee table
salgrade is the salary scale
How to view the data in the table?
select * from table name// Execute this SQL statement uniformly.
mysql> select *from emp; +-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
mysql> select *from dept; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+
mysql> select *from salgrade; +-------+-------+-------+ | GRADE | LOSAL | HISAL | +-------+-------+-------+ | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | +-------+-------+-------+
Don't look at the data in the table, just look at the structure of the table
desc table name;
mysql> desc emp; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | EMPNO | int(4) | NO | PRI | NULL | |Employee number | ENAME | varchar(10) | YES | | NULL | |Employee name | JOB | varchar(9) | YES | | NULL | |Employee position | MGR | int(4) | YES | | NULL | |Superior number | HIREDATE | date | YES | | NULL | |Entry date | SAL | double(7,2) | YES | | NULL | |wages | COMM | double(7,2) | YES | | NULL | |subsidy | DEPTNO | int(2) | YES | | NULL | |Department number +----------+-------------+------+-----+---------+-------+
mysql> desc dept; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | DEPTNO | int(2) | NO | PRI | NULL | |Department number | DNAME | varchar(14) | YES | | NULL | |Department name | LOC | varchar(13) | YES | | NULL | |geographical position
mysql> desc salgrade; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | GRADE | int(11) | YES | | NULL | |Wage scale | LOSAL | int(11) | YES | | NULL | |minimum wage | HISAL | int(11) | YES | | NULL | |Maximum wage +-------+---------+------+-----+---------+-------+
Simple query DQL
Query a field
select field name from table name;
Note:
select and from are both keywords.
Field names and table names are identifiers.
emphasize:
It is common for SQL statements. All SQL statements end with ";". In addition, SQL statements are case insensitive.
Query department name
mysql> select dname from dept; +------------+ | dname | +------------+ | ACCOUNTING | | RESEARCH | | SALES | | OPERATIONS | +------------+
Query multiple fields
Use commas to separate ","
Query department number and department name
mysql> select deptno,dname from dept; +--------+------------+ | deptno | dname | +--------+------------+ | 10 | ACCOUNTING | | 20 | RESEARCH | | 30 | SALES | | 40 | OPERATIONS | +--------+------------+
Query all fields
The first way: you can write every field
select a,b,c,d,e,f... from tablename;
The second way: you can use*
mysql> select * from dept; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+
Disadvantages of this approach:
1. Low efficiency
2. Poor readability.
It's not recommended in actual development. You can play by yourself, no problem.
You can take a quick look at the full table data in the DOS command window. This method can be used.
Alias query columns
mysql> select deptno,dname as deptname from dept;
Use the as keyword to alias
mysql> select deptno,dname as deptname from dept; +--------+------------+ | deptno | deptname | +--------+------------+ | 10 | ACCOUNTING | | 20 | RESEARCH | | 30 | SALES | | 40 | OPERATIONS | +--------+------------+
Note: only the column name of the displayed query result is displayed as deptname, and the original table column name is still called: dname
Remember: select statements will never be modified. (because it is only responsible for query)
Can the as keyword be omitted? tolerable
mysql> select deptno,dname deptname from dept;
Suppose there is a space in the alias when you start the alias. What should I do?
mysql> select deptno,dname dept name from dept;
When the DBMS sees such a statement, it compiles the SQL statement. If it does not conform to the syntax, it will report an error. How?
select deptno,dname ‘dept name’ from dept; // Charizing Operator
select deptno,dname “dept name” from dept; // quotation marks
mysql> select deptno,dname 'dept name' from dept; +--------+------------+ | deptno | dept name | +--------+------------+ | 10 | ACCOUNTING | | 20 | RESEARCH | | 30 | SALES | | 40 | OPERATIONS | +--------+------------+
Note: in all databases, strings are enclosed in single quotation marks. Single quotation marks are standard, and double quotation marks are not used in oracle database. But it can be used in mysql.
Again, the strings in the database are enclosed in single quotes. This is standard. Double quotation marks are not standard.
Calculate employee annual salary
sal * 12
1. Check the employee's name and salary
mysql> select ename,sal from emp; +--------+---------+ | ename | sal | +--------+---------+ | SMITH | 800.00 | | ALLEN | 1600.00 | | WARD | 1250.00 | | JONES | 2975.00 | | MARTIN | 1250.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | SCOTT | 3000.00 | | KING | 5000.00 | | TURNER | 1500.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | FORD | 3000.00 | | MILLER | 1300.00 | +--------+---------+
2. Calculate annual salary
mysql> select ename,sal*12 from emp; +--------+----------+ | ename | sal*12 | +--------+----------+ | SMITH | 9600.00 | | ALLEN | 19200.00 | | WARD | 15000.00 | | JONES | 35700.00 | | MARTIN | 15000.00 | | BLAKE | 34200.00 | | CLARK | 29400.00 | | SCOTT | 36000.00 | | KING | 60000.00 | | TURNER | 18000.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | FORD | 36000.00 | | MILLER | 15600.00 | +--------+----------+
Conclusion: fields can use mathematical expressions!
3. Starting from the annual salary
select ename,sal*12 as yearsal from emp; //Alias +--------+----------+ | ename | yearsal | +--------+----------+ | SMITH | 9600.00 | | ALLEN | 19200.00 | | WARD | 15000.00 | | JONES | 35700.00 | | MARTIN | 15000.00 | | BLAKE | 34200.00 | | CLARK | 29400.00 | | SCOTT | 36000.00 | | KING | 60000.00 | | TURNER | 18000.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | FORD | 36000.00 | | MILLER | 15600.00 | +--------+----------+ 14 rows in set (0.00 sec)
4. Chinese alias with single quotation marks
mysql> select ename,sal*12 as 'Annual salary' from emp; +--------+----------+ | ename | Annual salary | +--------+----------+ | SMITH | 9600.00 | | ALLEN | 19200.00 | | WARD | 15000.00 | | JONES | 35700.00 | | MARTIN | 15000.00 | | BLAKE | 34200.00 | | CLARK | 29400.00 | | SCOTT | 36000.00 | | KING | 60000.00 | | TURNER | 18000.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | FORD | 36000.00 | | MILLER | 15600.00 | +--------+----------+
Condition query
Not all the data in the table can be found. The query results are qualified.
Syntax format: select Field 1,Field 2,Field 3.... from Table name where condition;
Condition category
1. = equal sign
Query the name and number of an employee whose salary is equal to 800
mysql> select empno,ename from emp where sal = 800; +-------+-------+ | empno | ename | +-------+-------+ | 7369 | SMITH | +-------+-------+
Query SMITH number and salary?
select empno,sal from emp where ename = ‘SMITH’; // Use single quotes for Strings
mysql> select empno,sal from emp where ename = 'SMITH'; +-------+--------+ | empno | sal | +-------+--------+ | 7369 | 800.00 | +-------+--------+
2. < > or= Not equal to
Query the name and number of employees whose salary is not equal to 800
mysql> select empno,ename from emp where sal != 800; +-------+--------+ | empno | ename | +-------+--------+ | 7499 | ALLEN | | 7521 | WARD | | 7566 | JONES | | 7654 | MARTIN | | 7698 | BLAKE | | 7782 | CLARK | | 7788 | SCOTT | | 7839 | KING | | 7844 | TURNER | | 7876 | ADAMS | | 7900 | JAMES | | 7902 | FORD | | 7934 | MILLER | +-------+--------+
3. < less than
Query the name and number of employees whose salary is less than 2000
mysql> select empno,ename from emp where sal < 2000; +-------+--------+ | empno | ename | +-------+--------+ | 7369 | SMITH | | 7499 | ALLEN | | 7521 | WARD | | 7654 | MARTIN | | 7844 | TURNER | | 7876 | ADAMS | | 7900 | JAMES | | 7934 | MILLER | +-------+--------+
4. < = less than or equal to
Query the name and number of employees whose salary is less than or equal to 3000
mysql> select empno,ename,sal from emp where sal <= 3000; +-------+--------+---------+ | empno | ename | sal | +-------+--------+---------+ | 7369 | SMITH | 800.00 | | 7499 | ALLEN | 1600.00 | | 7521 | WARD | 1250.00 | | 7566 | JONES | 2975.00 | | 7654 | MARTIN | 1250.00 | | 7698 | BLAKE | 2850.00 | | 7782 | CLARK | 2450.00 | | 7788 | SCOTT | 3000.00 | | 7844 | TURNER | 1500.00 | | 7876 | ADAMS | 1100.00 | | 7900 | JAMES | 950.00 | | 7902 | FORD | 3000.00 | | 7934 | MILLER | 1300.00 |
5. > greater than
Query the name and number of employees whose salary is greater than 3000
select empno,ename,sal from emp where sal > 3000;
6. = greater than or equal to
Query the name and number of employees whose salary is greater than or equal to 3000?
select empno,ename,sal from emp where sal >= 3000;
7.between... And... Is equal to > = and<=
Query employee information with salary between 2450 and 3000? Including 2450 and 3000
The first way: > = and < = (and means and)
mysql> select empno,ename from emp where sal >= 1000 and sal <= 3000; +-------+--------+ | empno | ename | +-------+--------+ | 7499 | ALLEN | | 7521 | WARD | | 7566 | JONES | | 7654 | MARTIN | | 7698 | BLAKE | | 7782 | CLARK | | 7788 | SCOTT | | 7844 | TURNER | | 7876 | ADAMS | | 7902 | FORD | | 7934 | MILLER | +-------+--------+
The second way: between... and
select empno,ename,sal from emp where sal between 2450 and 3000;
Note:
When using between and, you must follow the principle of small left and large right.
between and is a closed interval, including the values at both ends.
mysql> select empno,ename,sal from emp where sal between 2450 and 3000; +-------+-------+---------+ | empno | ename | sal | +-------+-------+---------+ | 7566 | JONES | 2975.00 | | 7698 | BLAKE | 2850.00 | | 7782 | CLARK | 2450.00 | | 7788 | SCOTT | 3000.00 | | 7902 | FORD | 3000.00 | +-------+-------+---------+ 5 rows in set (0.00 sec)
8. Is not null is null (is not null is not null)
Query which employees' allowances / subsidies are null
mysql> select empno,ename,sal,comm from emp where comm = null; Empty set (0.00 sec)
mysql> select empno,ename,sal,comm from emp where comm is null; +-------+--------+---------+------+ | empno | ename | sal | comm | +-------+--------+---------+------+ | 7369 | SMITH | 800.00 | NULL | | 7566 | JONES | 2975.00 | NULL | | 7698 | BLAKE | 2850.00 | NULL | | 7782 | CLARK | 2450.00 | NULL | | 7788 | SCOTT | 3000.00 | NULL | | 7839 | KING | 5000.00 | NULL | | 7876 | ADAMS | 1100.00 | NULL | | 7900 | JAMES | 950.00 | NULL | | 7902 | FORD | 3000.00 | NULL | | 7934 | MILLER | 1300.00 | NULL | +-------+--------+---------+------+ 10 rows in set (0.00 sec)
Note: null cannot be measured with an equal sign in the database. You need to use is null, because null in the database represents nothing. It is not a value, so it cannot be measured by the equal sign.
Query which employees' allowances / subsidies are not null
mysql> select empno,ename,sal,comm from emp where comm is not null; +-------+--------+---------+---------+ | empno | ename | sal | comm | +-------+--------+---------+---------+ | 7499 | ALLEN | 1600.00 | 300.00 | | 7521 | WARD | 1250.00 | 500.00 | | 7654 | MARTIN | 1250.00 | 1400.00 | | 7844 | TURNER | 1500.00 | 0.00 | +-------+--------+---------+---------+
9.and
Query the information of employees whose position is MANAGER and whose salary is greater than 2500
select empno,ename,job,sal from emp where job = 'MANAGER' and sal > 2500;
mysql> select empno,ename,job,sal from emp where job = 'MANAGER' and sal > 2500; +-------+-------+---------+---------+ | empno | ename | job | sal | +-------+-------+---------+---------+ | 7566 | JONES | MANAGER | 2975.00 | | 7698 | BLAKE | MANAGER | 2850.00 | +-------+-------+---------+---------+
10.or
Query employees whose jobs are MANAGER and SALESMAN
mysql> select empno,ename,job from emp where job = 'MANAGER' or job = 'SALSEMAN'; +-------+-------+---------+ | empno | ename | job | +-------+-------+---------+ | 7566 | JONES | MANAGER | | 7698 | BLAKE | MANAGER | | 7782 | CLARK | MANAGER | +-------+-------+---------+
If and and or occur at the same time, is there a priority problem
Query employees whose salary is greater than 2500 and department number is 10 or 20
mysql> select * from emp where sal > 2500 and deptno = 10 or deptno = 20; +-------+-------+-----------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+-----------+------+------------+---------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | +-------+-------+-----------+------+------------+---------+------+--------+ 6 rows in set (0.00 sec)
Analyze the problems of the above statements
and has higher priority than or.
The above statement will execute and first, and then or.
What does the above statement mean?
Find out the employees whose salary is greater than 2500 and department number is 10, or all employees in department 20.
select * from emp where sal > 2500 and (deptno = 10 or deptno = 20);
mysql> select * from emp where sal > 2500 and (deptno = 10 or deptno = 20); +-------+-------+-----------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+-----------+------+------------+---------+------+--------+ | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | +-------+-------+-----------+------+------------+---------+------+--------+ 4 rows in set (0.00 sec)
And and or appear at the same time, and has higher priority. If you want or to execute first, you need to add "parentheses". Later, if you are not sure about the priority, you can add parentheses.
11.in incl
Equivalent to multiple or (not in not in this range)
Query employees whose jobs are MANAGER and SALESMAN
mysql> select empno,ename,job from emp where job in('MANAGER', 'SALESMAN');
+-------+--------+----------+
| empno | ename | job |
+-------+--------+----------+
| 7499 | ALLEN | SALESMAN |
| 7521 | WARD | SALESMAN |
| 7566 | JONES | MANAGER |
| 7654 | MARTIN | SALESMAN |
| 7698 | BLAKE | MANAGER |
| 7782 | CLARK | MANAGER |
| 7844 | TURNER | SALESMAN |
+-------+--------+----------+
Query employee information with salary of 800 and 5000
mysql> select * from emp where sal in (800,5000); +-------+-------+-----------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+-----------+------+------------+---------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | +-------+-------+-----------+------+------------+---------+------+--------+ 2 rows in set (0.00 sec)
not in indicates data that is not among these values.
mysql> select ename,sal from emp where sal not in(800, 5000, 3000); +--------+---------+ | ename | sal | +--------+---------+ | ALLEN | 1600.00 | | WARD | 1250.00 | | JONES | 2975.00 | | MARTIN | 1250.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | TURNER | 1500.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | MILLER | 1300.00 | +--------+---------+
not can take non, mainly used in is or in
is null
is not null
in
not in
12.like
It is called fuzzy query and supports% or underscore matching
% matches any number of characters
Underscore: any character.
(% is a special symbol, is also a special symbol)
Find the one with O in the name?
mysql> select ename from emp where ename like '%O%'; +-------+ | ename | +-------+ | JONES | | SCOTT | | FORD | +-------+
Find the one whose name ends in T?
select ename from emp where ename like '%T';
Find the one whose name starts with K?
select ename from emp where ename like 'K%';
Find out if the second word is A?
select ename from emp where ename like '_A%';
Find out that the third letter is R?
select ename from emp where ename like '__R%';
t_student Student list name field -------------------- zhangsan lisi wangwu zhaoliu jack_son
Find the one with "" in the name?
select name from t_student where name like '%%'; / / this won't work.
mysql> select name from t_student where name like '%\_%'; // \Escape character. +----------+ | name | +----------+ | jack_son | +----------+
sort
Query all employee salaries, sort
Ascending order
select ename,sal from emp order by sal; // The default is ascending!!
+--------+---------+ | ename | sal | +--------+---------+ | SMITH | 800.00 | | JAMES | 950.00 | | ADAMS | 1100.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | | MILLER | 1300.00 | | TURNER | 1500.00 | | ALLEN | 1600.00 | | CLARK | 2450.00 | | BLAKE | 2850.00 | | JONES | 2975.00 | | FORD | 3000.00 | | SCOTT | 3000.00 | | KING | 5000.00 | +--------+---------+ ### Descending order Specify descending order: select ename,sal from emp order by sal desc; ```sql +--------+---------+ | ename | sal | +--------+---------+ | KING | 5000.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | ALLEN | 1600.00 | | TURNER | 1500.00 | | MILLER | 1300.00 | | MARTIN | 1250.00 | | WARD | 1250.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | SMITH | 800.00 | +--------+---------+
Specify ascending order select ename,sal from emp order by sal asc;
+--------+---------+ | ename | sal | +--------+---------+ | SMITH | 800.00 | | JAMES | 950.00 | | ADAMS | 1100.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | | MILLER | 1300.00 | | TURNER | 1500.00 | | ALLEN | 1600.00 | | CLARK | 2450.00 | | BLAKE | 2850.00 | | JONES | 2975.00 | | FORD | 3000.00 | | SCOTT | 3000.00 | | KING | 5000.00 | +--------+---------+
Sorting of multiple fields
Query employee name and salary in ascending order. If the salary is the same,
Then arrange them in ascending order of names.
select ename,sal from emp order by sal asc, ename asc; // sal comes first and plays a leading role. Enable enable ename sorting only when sal is equal.
sal comes first and plays a leading role. Enable enable ename sorting only when sal is equal.
+--------+---------+ | ename | sal | +--------+---------+ | SMITH | 800.00 | | JAMES | 950.00 | | ADAMS | 1100.00 | | MARTIN | 1250.00 | | WARD | 1250.00 | | MILLER | 1300.00 | | TURNER | 1500.00 | | ALLEN | 1600.00 | | CLARK | 2450.00 | | BLAKE | 2850.00 | | JONES | 2975.00 | | FORD | 3000.00 | | SCOTT | 3000.00 | | KING | 5000.00 | +--------+---------+
The position of the field can also be sorted
Select name, Sal from EMP order by 2; / / 2 indicates the second column. The second column is sal
Sort by the second column sal of the query results.
Understand that it is not recommended to write this in development because it is not robust.
Because the column order is easy to change, 2 will be discarded after the column order is modified.
comprehensive
Find out the information of employees whose salary is between 1250 and 3000, and arrange them in descending order.
select ename,salfromempwheresal between 1250 and 3000order bysal desc; +--------+---------+ | ename | sal | +--------+---------+ | FORD | 3000.00 | | SCOTT | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | ALLEN | 1600.00 | | TURNER | 1500.00 | | MILLER | 1300.00 | | MARTIN | 1250.00 | | WARD | 1250.00 | +--------+---------+
Keyword order cannot be changed: select ... from ... where ... order by ...
The execution sequence of the above statements must be mastered: Step 1: from Step 2: where Step 3: select **Step 4: order by(Sorting is always performed last!)**
Data processing function
Data processing functions are also called single line processing functions
Characteristics of single line processing function: one input corresponds to one output.
The opposite of single line processing function is multi line processing function. (features of multi line processing function: multiple inputs, corresponding to one output!)
Common single line processing functions
lower to lowercase
mysql> select lower(ename) as ename from emp; +--------+ | ename | +--------+ | smith | | allen | | ward | | jones | | martin | | blake | | clark | | scott | | king | | turner | | adams | | james | | ford | | miller | +--------+
14 inputs, and finally 14 outputs. This is the characteristic of single line processing functions.
upper convert uppercase
mysql> select * from t_student; +----------+ | name | +----------+ | zhangsan | | lisi | | wangwu | | jack_son | +----------+
mysql> select upper(name) as name from t_student; +----------+ | name | +----------+ | ZHANGSAN | | LISI | | WANGWU | | JACK_SON | +----------+
substr substring
Substr (intercepted string, starting subscript, intercepted length)
select substr(ename, 1, 1) as ename from emp;
Note: the starting subscript starts with 1 and does not have 0
Find out the employee information whose first letter is A
The first method: fuzzy query select ename from emp where ename like 'A%'; The second way: substr function select ename from emp where substr(ename,1,1) = 'A';
Initial capitalization
select upper(substr(name,1,1)) from t_student; select substr(name,2,length(name) - 1) from t_student; select concat(upper(substr(name,1,1)),substr(name,2,length(name) - 1)) as result from t_student;
+----------+ | result | +----------+ | Zhangsan | | Lisi | | Wangwu | | Jack_son | +----------+
concat function to splice strings
select concat(empno,ename) from emp; +---------------------+ | concat(empno,ename) | +---------------------+ | 7369SMITH | | 7499ALLEN | | 7521WARD | | 7566JONES | | 7654MARTIN | | 7698BLAKE | | 7782CLARK | | 7788SCOTT | | 7839KING | | 7844TURNER | | 7876ADAMS | | 7900JAMES | | 7902FORD | | 7934MILLER | +---------------------+
Length takes the length
select length(ename) enamelength from emp; +-------------+ | enamelength | +-------------+ | 5 | | 5 | | 4 | | 5 | | 6 | | 5 | | 5 | | 5 | | 4 | | 6 | | 5 | | 5 | | 4 | | 6 | +-------------+
trim to space
mysql> select * from emp where ename = ' KING'; Empty set (0.00 sec)
mysql> select * from emp where ename = trim(' KING');
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
+-------+-------+-----------+------+------------+---------+------+--------+
str_to_date converts a string to a date
date_format format date
format sets the thousandth
case...when...then...when...then...else...end
When the employee's job is MANAGER, the salary will be increased by 10%. When the job is SALESMAN, the salary will be increased by 50%. Others are normal.
(Note: the database is not modified, but the query result is displayed as salary increase)
select ename, job, sal as oldsal, (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal from emp;
+--------+-----------+---------+---------+ | ename | job | oldsal | newsal | +--------+-----------+---------+---------+ | SMITH | CLERK | 800.00 | 800.00 | | ALLEN | SALESMAN | 1600.00 | 2400.00 | | WARD | SALESMAN | 1250.00 | 1875.00 | | JONES | MANAGER | 2975.00 | 3272.50 | | MARTIN | SALESMAN | 1250.00 | 1875.00 | | BLAKE | MANAGER | 2850.00 | 3135.00 | | CLARK | MANAGER | 2450.00 | 2695.00 | | SCOTT | ANALYST | 3000.00 | 3000.00 | | KING | PRESIDENT | 5000.00 | 5000.00 | | TURNER | SALESMAN | 1500.00 | 2250.00 | | ADAMS | CLERK | 1100.00 | 1100.00 | | JAMES | CLERK | 950.00 | 950.00 | | FORD | ANALYST | 3000.00 | 3000.00 | | MILLER | CLERK | 1300.00 | 1300.00 | +--------+-----------+---------+---------+
Round round
select field from table name;
select ename from emp;
Select 'abc' from emp; // select is directly followed by "literal value / literal value"
mysql> select 'abc' as bieming from emp; +---------+ | bieming | +---------+ | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | +---------+
mysql> select abc from emp; ERROR 1054 (42S22): Unknown column 'abc' in 'field list'
This will definitely report an error, because you will take abc as the name of a field and look for the abc field in the emp table.
select 1000 as num from emp; // 1000 is also treated as a literal / literal value. +------+ | num | +------+ | 1000 | | 1000 | | 1000 | | 1000 | | 1000 | | 1000 | | 1000 | | 1000 | | 1000 | | 1000 | | 1000 | | 1000 | | 1000 | | 1000 | +------+
Conclusion: select can be followed by the field name of a table (which can be regarded as variable name) or literal value / literal value (data).
select 21000 as num from dept; +-------+ | num | +-------+ | 21000 | | 21000 | | 21000 | | 21000 | +-------+
mysql> select round(1236.567, 0) as result from emp; //Keep integer digits. +--------+ | result | +--------+ | 1237 | | 1237 | | 1237 | | 1237 | | 1237 | | 1237 | | 1237 | | 1237 | | 1237 | | 1237 | | 1237 | | 1237 | | 1237 | | 1237 | +--------+
select round(1236.567, 1) as result from emp; // Keep 1 decimal
select round(1236.567, 2) as result from emp; // Keep 2 decimal places
select round(1236.567, -1) as result from emp; // Keep to ten.
+--------+ | result | +--------+ | 1240 | | 1240 | | 1240 | | 1240 | | 1240 | | 1240 | | 1240 | | 1240 | | 1240 | | 1240 | | 1240 | | 1240 | | 1240 | | 1240 | +--------+
select round(1236.567, -2) as result from emp; +--------+ | result | +--------+ | 1200 | | 1200 | | 1200 | | 1200 | | 1200 | | 1200 | | 1200 | | 1200 | | 1200 | | 1200 | | 1200 | | 1200 | | 1200 | | 1200 | +--------+
rand() generates a random number
mysql> select round(rand()*100,0) from emp; // Random number within 100 +---------------------+ | round(rand()*100,0) | +---------------------+ | 76 | | 29 | | 15 | | 88 | | 95 | | 9 | | 63 | | 89 | | 54 | | 3 | | 54 | | 61 | | 42 | | 28 | +---------------------+
ifnull can convert null to a concrete value
ifnull is an empty handler. Dedicated to empty.
In all databases, as long as there is a mathematical operation involving NULL, the final result is NULL.
mysql> select ename, sal + comm as salcomm from emp; +--------+---------+ | ename | salcomm | +--------+---------+ | SMITH | NULL | | ALLEN | 1900.00 | | WARD | 1750.00 | | JONES | NULL | | MARTIN | 2650.00 | | BLAKE | NULL | | CLARK | NULL | | SCOTT | NULL | | KING | NULL | | TURNER | 1500.00 | | ADAMS | NULL | | JAMES | NULL | | FORD | NULL | | MILLER | NULL | +--------+---------+
Calculate the annual salary of each employee
Annual salary = (monthly salary + monthly subsidy) * 12
select ename, (sal + comm) * 12 as yearsal from emp; +--------+----------+ | ename | yearsal | +--------+----------+ | SMITH | NULL | | ALLEN | 22800.00 | | WARD | 21000.00 | | JONES | NULL | | MARTIN | 31800.00 | | BLAKE | NULL | | CLARK | NULL | | SCOTT | NULL | | KING | NULL | | TURNER | 18000.00 | | ADAMS | NULL | | JAMES | NULL | | FORD | NULL | | MILLER | NULL | +--------+----------+
Note: as long as NULL participates in the operation, the final result must be NULL. To avoid this, you need to use the ifnull function.
Ifnull function usage: ifnull (data, which value is treated as)
If the "data" is NULL, which value should the data structure be treated as.
When the subsidy is NULL, the subsidy is treated as 0
select ename, (sal + ifnull(comm, 0)) * 12 as yearsal from emp; +--------+----------+ | ename | yearsal | +--------+----------+ | SMITH | 9600.00 | | ALLEN | 22800.00 | | WARD | 21000.00 | | JONES | 35700.00 | | MARTIN | 31800.00 | | BLAKE | 34200.00 | | CLARK | 29400.00 | | SCOTT | 36000.00 | | KING | 60000.00 | | TURNER | 18000.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | FORD | 36000.00 | | MILLER | 15600.00 | +--------+----------+
Grouping function (multiline data processing)
Characteristics of multi line processing function: input multiple lines and finally output one line.
Five functions
Count count
sum
avg average
max
min
be careful:
Grouping functions must be grouped before they can be used.
If you do not group the data, the whole table defaults to one group.
Find the maximum wage
mysql> select max(sal) from emp; +----------+ | max(sal) | +----------+ | 5000.00 | +----------+
Find the minimum wage
mysql> select min(sal) from emp; +----------+ | min(sal) | +----------+ | 800.00 | +----------+
Calculate wages and
mysql> select sum(sal) from emp; +----------+ | sum(sal) | +----------+ | 29025.00 | +----------+
Calculate average wage
mysql> select avg(sal) from emp; +-------------+ | avg(sal) | +-------------+ | 2073.214286 | +-------------+
Calculate the number of employees
mysql> select count(ename) from emp; +--------------+ | count(ename) | +--------------+ | 14 | +--------------+
What should we pay attention to when using grouping functions?
First point: the grouping function automatically ignores NULL. You don't need to deal with NULL in advance.
mysql> select sum(comm) from emp; +-----------+ | sum(comm) | +-----------+ | 2200.00 | +-----------+
mysql> select count(comm) from emp; +-------------+ | count(comm) | +-------------+ | 4 | +-------------+
mysql> select avg(comm) from emp; +------------+ | avg(comm) | +------------+ | 550.000000 | +------------+
Second point: what is the difference between count(*) and count (specific field) in grouping functions?
mysql> select count(*) from emp; +----------+ | count(*) | +----------+ | 14 | +----------+ mysql> select count(comm) from emp; +-------------+ | count(comm) | +-------------+ | 4 | +-------------+
Count (specific field): indicates the total number of non NULL elements in this field.
count(*): the total number of rows in the statistics table. (as long as there is a row of data count + +) because each row of records cannot be NULL, and one column in a row of data is not NULL, this row of data is valid.
Third point: grouping functions cannot be used directly in the where clause.
Find information about employees who are higher than the minimum wage.
select ename,sal from emp where sal > min(sal); No problem on the surface. Run it? ERROR 1111 (HY000): Invalid use of group function
Point 4: all grouping functions can be combined and used together.
mysql> select sum(sal),max(sal),min(sal),avg(sal),count(*) from emp; +----------+----------+----------+-------------+----------+ | sum(sal) | max(sal) | min(sal) | avg(sal) | count(*) | +----------+----------+----------+-------------+----------+ | 29025.00 | 5000.00 | 800.00 | 2073.214286 | 14 | +----------+----------+----------+-------------+----------+
Group query (important)
What is a group query
In practical applications, there may be such a need to group first, and then operate on each group of data.
At this time, we need to use group query. How to perform group query?
select ... from ... group by ...
Calculate the wages and salaries of each department
Calculate the average salary for each job
Find out the highest salary for each job
Execution order of keywords
select ... from ... where ... group by ... order by ...
The order of the above keywords cannot be reversed and needs to be remembered.
What is the order of execution?
1. from
2. where
3. group by
4. select
5. order by
Why can't grouping functions be used directly after where?
select ename,sal from emp where sal > min(sal);//report errors.
Because the grouping function can only be used after grouping. When where is executed, there is no grouping. Therefore, grouping functions cannot appear after where.
select sum(sal) from emp;
There is no grouping. Why can sum() function be used?
Because select is executed after group by.
Find out the salary and salary of each job
select job,sum(sal) from emp group by job;
mysql> select job,sum(sal) from emp group by job; +-----------+----------+ | job | sum(sal) | +-----------+----------+ | ANALYST | 6000.00 | | CLERK | 4150.00 | | MANAGER | 8275.00 | | PRESIDENT | 5000.00 | | SALESMAN | 5600.00 | +-----------+----------+
The execution order of the above statement?
First query the data from the emp table; Group according to the job field; Then sum(sal) the data of each group
select ename,job,sum(sal) from emp group by job; +-------+-----------+----------+ | ename | job | sum(sal) | +-------+-----------+----------+ | SCOTT | ANALYST | 6000.00 | | SMITH | CLERK | 4150.00 | | JONES | MANAGER | 8275.00 | | KING | PRESIDENT | 5000.00 | | ALLEN | SALESMAN | 5600.00 | +-------+-----------+----------+
The above statements can be executed in mysql, but they are meaningless.
The above statement reports an error when executed in oracle.
The syntax of oracle is stricter than that of mysql. (the syntax of mysql is relatively loose!)
Key conclusions:
In a select statement, if there is a group by statement, select can only be followed by the fields participating in grouping and grouping functions, and others cannot be followed.
Find out the highest salary for each department
mysql> select deptno,max(sal) from emp group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | | 20 | 3000.00 | | 30 | 2850.00 | +--------+----------+
Find out the maximum salary of "each department, different jobs"
mysql> select deptno,job,max(sal) from emp group by deptno,job; +--------+-----------+----------+ | deptno | job | max(sal) | +--------+-----------+----------+ | 10 | CLERK | 1300.00 | | 10 | MANAGER | 2450.00 | | 10 | PRESIDENT | 5000.00 | | 20 | ANALYST | 3000.00 | | 20 | CLERK | 1100.00 | | 20 | MANAGER | 2975.00 | | 30 | CLERK | 950.00 | | 30 | MANAGER | 2850.00 | | 30 | SALESMAN | 1600.00 | +--------+-----------+----------+
Use having to further filter the grouped data
Using having, you can further filter the data after grouping * *. Having cannot be used alone. Having cannot replace where. Having must be used in combination with group by**
Find out the highest salary of each department and display the information with the highest salary greater than 3000
Step 1: find out the highest salary for each department
Group according to the department number and find the maximum value of each group.
mysql> select deptno,max(sal) from emp group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | | 20 | 3000.00 | | 30 | 2850.00 | +--------+----------+
Step 2: it is required to display the maximum salary greater than 3000
select deptno,max(sal) from emp group by deptno having max(sal) > 3000;
mysql> select deptno,max(sal) from emp group by deptno having max(sal)>3000; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | +--------+----------+
Optimization strategy:
Where and having, give priority to where. Where can't be completed, and then choose having.
mysql> select deptno,max(sal) from emp where sal >3000 group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | +--------+----------+
where cannot be used
Find out the average salary of each department, and it is required to display those whose average salary is higher than 2500.
mysql> select deptno,avg(sal) from emp group by deptno having avg(sal) > 2500; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 10 | 2916.666667 | +--------+-------------+ 1 row in set (0.00 sec)
summary
select ... from ... where ... group by ... having ... order by ...
The above keywords can only be in this order and cannot be reversed.
Execution sequence?
1. from
2. where
3. group by
4. having
5. select
6. order by
To query data from a table, first filter out valuable data through where criteria. Group these valuable data. After grouping, you can use having to continue filtering, select to query, and finally sort the output!
Find out the average salary of each position. If the average salary is greater than 1500, it is required to be arranged in descending order according to the average salary except for the MANAGER position.
mysql> select job,avg(sal) from emp where job <> 'MANAGER' group by job having avg(sal) >1500 order by avg(sal) desc;
+-----------+-------------+
| job | avg(sal) |
+-----------+-------------+
| PRESIDENT | 5000.000000 |
| ANALYST | 3000.000000 |
+-----------+-------------+
mysql> select job,avg(sal) from emp where job not in ('MANAGER') group by job having avg(sal) >1500 order by avg(sal) desc;
+-----------+-------------+
| job | avg(sal) |
+-----------+-------------+
| PRESIDENT | 5000.000000 |
| ANALYST | 3000.000000 |
+-----------+-------------+
De duplication distinct
Note: the original table data will not be modified, but the query results will be de duplicated
Use of de duplication keyword: distinct
mysql> select distinct job from emp; +-----------+ | job | +-----------+ | CLERK | | SALESMAN | | MANAGER | | ANALYST | | PRESIDENT | +-----------+
This writing is wrong, syntax error.
distinct can only appear at the front of all fields.
mysql> select ename,distinct job from emp;
distinct appears before the job and deptno fields, indicating that the two fields are combined to remove duplication.
mysql> select distinct job,deptno from emp; +-----------+--------+ | job | deptno | +-----------+--------+ | CLERK | 20 | | SALESMAN | 30 | | MANAGER | 20 | | MANAGER | 30 | | MANAGER | 10 | | ANALYST | 20 | | PRESIDENT | 10 | | CLERK | 30 | | CLERK | 10 | +-----------+--------+
Count the number of jobs
mysql> select count(distinct job) from emp; +---------------------+ | count(distinct job) | +---------------------+ | 5 | +---------------------+
join query
What is a join query
A separate query from a table is called a single table query.
The emp table and the dept table are combined to query data. The employee name is taken from the emp table and the Department name is taken from the dept table; This cross table query, in which multiple tables join to query data, is called join query.
Classification of join queries
Classification according to grammatical age:
SQL92: syntax that appeared in 1992
SQL99: syntax in 1999
Let's focus on SQL99. (a simple example of SQL92 is demonstrated in this process)
Classification according to table connection mode:
Internal connection:
Equivalent connection
Non equivalent connection
Self connection
External connection:
Left outer connection (left connection)
Right outer connection (right connection)
Full connection (not speaking)
What happens when two tables are connected for query without any restrictions?
Case: query the Department name of each employee?
mysql> select ename,deptno from emp; +--------+--------+ | ename | deptno | +--------+--------+ | SMITH | 20 | | ALLEN | 30 | | WARD | 30 | | JONES | 20 | | MARTIN | 30 | | BLAKE | 30 | | CLARK | 10 | | SCOTT | 20 | | KING | 10 | | TURNER | 30 | | ADAMS | 20 | | JAMES | 30 | | FORD | 20 | | MILLER | 10 | +--------+--------+
Department table
mysql> select * from dept; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+
There are no restrictions on the connection of two tables:
select ename,dname from emp, dept;
mysql> select ename,dname from emp, dept; +--------+------------+ | ename | dname | +--------+------------+ | SMITH | ACCOUNTING | | SMITH | RESEARCH | | SMITH | SALES | | SMITH | OPERATIONS | | ALLEN | ACCOUNTING | | ALLEN | RESEARCH | | ALLEN | SALES | | ALLEN | OPERATIONS | | WARD | ACCOUNTING | | WARD | RESEARCH | | WARD | SALES | | WARD | OPERATIONS | | JONES | ACCOUNTING | | JONES | RESEARCH | | JONES | SALES | | JONES | OPERATIONS | ..... 56 rows in set (0.00 sec) 14 * 4 = 56
When two tables are connected for query without any restrictions, the number of final query results is the product of the number of two tables. This phenomenon is called Cartesian product phenomenon. (Descartes discovered that this is a mathematical phenomenon.)
How to avoid Cartesian product
Conditions are added during connection, and records that meet these conditions are filtered out
select ename,dname from emp, dept where emp.deptno = dept.deptno;
select emp.ename,dept.dname from emp, dept where emp.deptno = dept.deptno;
Thinking: the number of results of the final query is 14, but has the number of matches been reduced in the process of matching?
Or 56 times, just one out of four. The number of times did not decrease.
mysql> select emp.ename,dept.dname from emp,dept where emp.deptno = dept.deptno; +--------+------------+ | ename | dname | +--------+------------+ | CLARK | ACCOUNTING | | KING | ACCOUNTING | | MILLER | ACCOUNTING | | SMITH | RESEARCH | | JONES | RESEARCH | | SCOTT | RESEARCH | | ADAMS | RESEARCH | | FORD | RESEARCH | | ALLEN | SALES | | WARD | SALES | | MARTIN | SALES | | BLAKE | SALES | | TURNER | SALES | | JAMES | SALES | +--------+------------+ 14 rows in set (0.00 sec)
Alias is very important, efficiency is a problem
mysql> select e.ename, d.dname from emp e,dept d where e.deptno = d.deptno;//SQL92 syntax. +--------+------------+ | ename | dname | +--------+------------+ | CLARK | ACCOUNTING | | KING | ACCOUNTING | | MILLER | ACCOUNTING | | SMITH | RESEARCH | | JONES | RESEARCH | | SCOTT | RESEARCH | | ADAMS | RESEARCH | | FORD | RESEARCH | | ALLEN | SALES | | WARD | SALES | | MARTIN | SALES | | BLAKE | SALES | | TURNER | SALES | | JAMES | SALES | +--------+------------+ 14 rows in set (0.00 sec)
Note: according to the Cartesian product phenomenon, the more the connection times of the table, the lower the efficiency. Try to avoid the connection times of the table.
Equivalent connection of inner connection
Case: query the Department name of each employee, and display the employee name and department name
Join the emp e and dept d tables. The condition is: e.deptno = d.deptno
SQL92 syntax:
select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno;
Disadvantages of sql92: the structure is not clear. The connection conditions of the table and the conditions for further screening in the later stage are put behind where.
SQL99 syntax:
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
There is an inner in front of the join that can be omitted (better readability with inner!!! You can see that it is an inner connection at a glance)
select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno; // The condition is an equal quantity relationship, so it is called equivalent connection.
mysql> select
-> e.ename,d.dname
-> from
-> emp e
-> join
-> dept d
-> on
-> e.deptno = d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| CLARK | ACCOUNTING |
| KING | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH | RESEARCH |
| JONES | RESEARCH |
| SCOTT | RESEARCH |
| ADAMS | RESEARCH |
| FORD | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| MARTIN | SALES |
| BLAKE | SALES |
| TURNER | SALES |
| JAMES | SALES |
+--------+------------+
14 rows in set (0.01 sec)
Advantages of sql99: the conditions of table connection are independent. If further filtering is needed after connection, continue to add where later
SQL99 syntax:
select ... from a join b on a and b Connection conditions where Screening conditions
Non equivalent connection of inner connection
Case: find out the salary grade of each employee and display the employee name, salary and salary grade
mysql> select * from emp; +-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+
mysql> select * from salgrade; +-------+-------+-------+ | GRADE | LOSAL | HISAL | +-------+-------+-------+ | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | +-------+-------+-------+
select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal; // The condition is not an equal quantity relationship, which is called non equivalent connection.
mysql> select e.ename,e.sal,s.grade from emp e join salgrade s
-> on e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename | sal | grade |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+--------+---------+-------+
Self connection of internal connection
Query the superior leader of an employee. The employee name and corresponding leader name are required to be displayed
mysql> select ename,empno,mgr from emp; +--------+-------+------+ | ename | empno | mgr | +--------+-------+------+ | SMITH | 7369 | 7902 | | ALLEN | 7499 | 7698 | | WARD | 7521 | 7698 | | JONES | 7566 | 7839 | | MARTIN | 7654 | 7698 | | BLAKE | 7698 | 7839 | | CLARK | 7782 | 7839 | | SCOTT | 7788 | 7566 | | KING | 7839 | NULL | | TURNER | 7844 | 7698 | | ADAMS | 7876 | 7788 | | JAMES | 7900 | 7698 | | FORD | 7902 | 7566 | | MILLER | 7934 | 7782 | +--------+-------+------+
Tip: look at one table as two.
emp a employee table
+--------+-------+------+ | ename | empno | mgr | +--------+-------+------+ | SMITH | 7369 | 7902 | | ALLEN | 7499 | 7698 | | WARD | 7521 | 7698 | | JONES | 7566 | 7839 | | MARTIN | 7654 | 7698 | | BLAKE | 7698 | 7839 | | CLARK | 7782 | 7839 | | SCOTT | 7788 | 7566 | | KING | 7839 | NULL | | TURNER | 7844 | 7698 | | ADAMS | 7876 | 7788 | | JAMES | 7900 | 7698 | | FORD | 7902 | 7566 | | MILLER | 7934 | 7782 | +--------+-------+------+
emp b employee table
+--------+-------+------+ | ename | empno | mgr | +--------+-------+------+ | SMITH | 7369 | 7902 | | ALLEN | 7499 | 7698 | | WARD | 7521 | 7698 | | JONES | 7566 | 7839 | | MARTIN | 7654 | 7698 | | BLAKE | 7698 | 7839 | | CLARK | 7782 | 7839 | | SCOTT | 7788 | 7566 | | KING | 7839 | NULL | | TURNER | 7844 | 7698 | | ADAMS | 7876 | 7788 | | JAMES | 7900 | 7698 | | FORD | 7902 | 7566 | | MILLER | 7934 | 7782 | +--------+-------+------+
mysql> select a.ename,b.ename from emp a join emp b on a.mgr = b.empno; +--------+-------+ | ename | ename | +--------+-------+ | SMITH | FORD | | ALLEN | BLAKE | | WARD | BLAKE | | JONES | KING | | MARTIN | BLAKE | | BLAKE | KING | | CLARK | KING | | SCOTT | JONES | | TURNER | BLAKE | | ADAMS | SCOTT | | JAMES | BLAKE | | FORD | JONES | | MILLER | CLARK | +--------+-------+
The above is the inner connection: self connection, skill: one table is regarded as two tables.
There is no KING in 13 records because KING has no leader
External connection
Employee table
mysql> select * from emp; +-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+ 14 rows in set (0.00 sec)
Department table
mysql> select * from dept; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+
This is an internal connection
Features of internal connection: complete the data query that can match this condition.
mysql> select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno; +--------+------------+ | ename | dname | +--------+------------+ | CLARK | ACCOUNTING | | KING | ACCOUNTING | | MILLER | ACCOUNTING | | SMITH | RESEARCH | | JONES | RESEARCH | | SCOTT | RESEARCH | | ADAMS | RESEARCH | | FORD | RESEARCH | | ALLEN | SALES | | WARD | SALES | | MARTIN | SALES | | BLAKE | SALES | | TURNER | SALES | | JAMES | SALES | +--------+------------+
External connection (right external connection)
mysql> select e.ename,d.dname from emp e right join dept d on e.deptno = d.deptno; +--------+------------+ | ename | dname | +--------+------------+ | CLARK | ACCOUNTING | | KING | ACCOUNTING | | MILLER | ACCOUNTING | | SMITH | RESEARCH | | JONES | RESEARCH | | SCOTT | RESEARCH | | ADAMS | RESEARCH | | FORD | RESEARCH | | ALLEN | SALES | | WARD | SALES | | MARTIN | SALES | | BLAKE | SALES | | TURNER | SALES | | JAMES | SALES | | NULL | OPERATIONS | +--------+------------+
What right stands for: it means that the table on the right of the join keyword is regarded as the main table, mainly to query all the data of this table, with the table on the left of the associated query. In the external connection, two tables are connected, resulting in a primary secondary relationship.
External connection (left external connection)
External connection (left external connection): select e.ename,d.dname from dept d left join emp e on e.deptno = d.deptno;
outer can be omitted with strong readability.
select e.ename,d.dname from dept d left outer join emp e on e.deptno = d.deptno;
+--------+------------+ | ename | dname | +--------+------------+ | CLARK | ACCOUNTING | | KING | ACCOUNTING | | MILLER | ACCOUNTING | | SMITH | RESEARCH | | JONES | RESEARCH | | SCOTT | RESEARCH | | ADAMS | RESEARCH | | FORD | RESEARCH | | ALLEN | SALES | | WARD | SALES | | MARTIN | SALES | | BLAKE | SALES | | TURNER | SALES | | JAMES | SALES | | NULL | OPERATIONS | +--------+------------+
be careful:
With right is the right outer connection, also known as the right connection.
With left is the left outer connection, also known as the left connection.
Any right connection has a left connection.
Any left connection has a right connection.
Think: the number of query results of external connection must be > = the number of query results of internal connection?
correct.
Query the superior leader of each employee and display the names of all employees and leaders
mysql> select a.ename,b.ename from emp a left join emp b on a.mgr = b.empno; +--------+-------+ | ename | ename | +--------+-------+ | SMITH | FORD | | ALLEN | BLAKE | | WARD | BLAKE | | JONES | KING | | MARTIN | BLAKE | | BLAKE | KING | | CLARK | KING | | SCOTT | JONES | | KING | NULL | | TURNER | BLAKE | | ADAMS | SCOTT | | JAMES | BLAKE | | FORD | JONES | | MILLER | CLARK | +--------+-------+
Multiple table connection
Syntax:
select ... from a join b on a and b Connection conditions join c on a and c Connection conditions right join d on a and d Connection conditions
Internal and external connections in an SQL can be mixed. Can appear!
Find out the Department name and salary grade of each employee in the case. It is required to display the employee name, department name, salary and salary grade
mysql> select * from emp; +-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+
mysql> select * from salgrade; +-------+-------+-------+ | GRADE | LOSAL | HISAL | +-------+-------+-------+ | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | +-------+-------+-------+
mysql> select * from dept; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+
mysql> select e.ename,e.sal,d.dname,s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal; +--------+---------+------------+-------+ | ename | sal | dname | grade | +--------+---------+------------+-------+ | SMITH | 800.00 | RESEARCH | 1 | | ALLEN | 1600.00 | SALES | 3 | | WARD | 1250.00 | SALES | 2 | | JONES | 2975.00 | RESEARCH | 4 | | MARTIN | 1250.00 | SALES | 2 | | BLAKE | 2850.00 | SALES | 4 | | CLARK | 2450.00 | ACCOUNTING | 4 | | SCOTT | 3000.00 | RESEARCH | 4 | | KING | 5000.00 | ACCOUNTING | 5 | | TURNER | 1500.00 | SALES | 3 | | ADAMS | 1100.00 | RESEARCH | 1 | | JAMES | 950.00 | SALES | 1 | | FORD | 3000.00 | RESEARCH | 4 | | MILLER | 1300.00 | ACCOUNTING | 2 | +--------+---------+------------+-------+
Case: find out the Department name and salary grade of each employee, as well as the superior leaders. It is required to display the employee name, leader name, department name, salary and salary grade
mysql> select a.ename,b.ename,d.dname,a.sal,s.grade from emp a left join emp b on a.mgr = b.empno join dept d on a.deptno = d.deptno join salgrade s on a.sal between s.losal and s.hisal; +--------+-------+------------+---------+-------+ | ename | ename | dname | sal | grade | +--------+-------+------------+---------+-------+ | SMITH | FORD | RESEARCH | 800.00 | 1 | | ALLEN | BLAKE | SALES | 1600.00 | 3 | | WARD | BLAKE | SALES | 1250.00 | 2 | | JONES | KING | RESEARCH | 2975.00 | 4 | | MARTIN | BLAKE | SALES | 1250.00 | 2 | | BLAKE | KING | SALES | 2850.00 | 4 | | CLARK | KING | ACCOUNTING | 2450.00 | 4 | | SCOTT | JONES | RESEARCH | 3000.00 | 4 | | KING | NULL | ACCOUNTING | 5000.00 | 5 | | TURNER | BLAKE | SALES | 1500.00 | 3 | | ADAMS | SCOTT | RESEARCH | 1100.00 | 1 | | JAMES | BLAKE | SALES | 950.00 | 1 | | FORD | JONES | RESEARCH | 3000.00 | 4 | | MILLER | CLARK | ACCOUNTING | 1300.00 | 2 | +--------+-------+------------+---------+-------+ 14 rows in set (0.00 sec)
Subquery
Select statements are nested in select statements. Nested select statements are called subqueries.
Where can subqueries appear?
select ..(select). from ..(select). where ..(select).
Subquery in where clause
Case: find out the name and salary of employees who are higher than the minimum wage
ERROR 1111 (HY000): Invalid use of group function
Grouping functions cannot be used directly in the where clause.
Implementation idea:
Step 1: what is the minimum wage
mysql> select min(sal) from emp; +----------+ | min(sal) | +----------+ | 800.00 | +----------+
Step 2: find out more than 800
mysql> select sal from emp where sal > 800; +---------+ | sal | +---------+ | 1600.00 | | 1250.00 | | 2975.00 | | 1250.00 | | 2850.00 | | 2450.00 | | 3000.00 | | 5000.00 | | 1500.00 | | 1100.00 | | 950.00 | | 3000.00 | | 1300.00 | +---------+
Step 3 merge:
mysql> select ename,sal from emp where sal >(select min(sal) from emp); +--------+---------+ | ename | sal | +--------+---------+ | ALLEN | 1600.00 | | WARD | 1250.00 | | JONES | 2975.00 | | MARTIN | 1250.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | SCOTT | 3000.00 | | KING | 5000.00 | | TURNER | 1500.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | FORD | 3000.00 | | MILLER | 1300.00 | +--------+---------+
Subquery in from clause
Note: for the sub query after from, you can treat the query result of the sub query as a temporary table. (skill)
Case: find out the salary grade of the average salary of each position.
Step 1: find out the average salary of each job
mysql> select avg(sal) from emp group by job; +-------------+ | avg(sal) | +-------------+ | 3000.000000 | | 1037.500000 | | 2758.333333 | | 5000.000000 | | 1400.000000 | +-------------+
Step 2: overcome psychological barriers and treat the above query results as a real table t.
mysql> select s.grade,t.* from(select job,avg(sal) as avgsal from emp group by job) t join salgrade s on t.avgsal between s.losal and s.hisal; +-------+-----------+-------------+ | grade | job | avgsal | +-------+-----------+-------------+ | 1 | CLERK | 1037.500000 | | 2 | SALESMAN | 1400.000000 | | 4 | ANALYST | 3000.000000 | | 4 | MANAGER | 2758.333333 | | 5 | PRESIDENT | 5000.000000 | +-------+-----------+-------------+ 5 rows in set (0.00 sec)
The sub query after select (you don't need to master this content, just understand!!!)
Find out the Department name of each employee and display the employee name and department name
mysql> select
-> e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname
-> from
-> emp e;
+--------+--------+------------+
| ename | deptno | dname |
+--------+--------+------------+
| SMITH | 20 | RESEARCH |
| ALLEN | 30 | SALES |
| WARD | 30 | SALES |
| JONES | 20 | RESEARCH |
| MARTIN | 30 | SALES |
| BLAKE | 30 | SALES |
| CLARK | 10 | ACCOUNTING |
| SCOTT | 20 | RESEARCH |
| KING | 10 | ACCOUNTING |
| TURNER | 30 | SALES |
| ADAMS | 20 | RESEARCH |
| JAMES | 30 | SALES |
| FORD | 20 | RESEARCH |
| MILLER | 10 | ACCOUNTING |
+--------+--------+------------+
be careful:
//Error: ERROR 1242 (21000): Subquery returns more than 1 row
select e.ename,e.deptno,(select dname from dept) as dname from emp e;
Note: for the sub query after select, this sub query can only return one result at a time. If there is more than one result, an error will be reported.!
union merge query
Case: query employees whose jobs are MANAGER and SALESMAN
mysql> select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN'; +--------+----------+ | ename | job | +--------+----------+ | ALLEN | SALESMAN | | WARD | SALESMAN | | JONES | MANAGER | | MARTIN | SALESMAN | | BLAKE | MANAGER | | CLARK | MANAGER | | TURNER | SALESMAN | +--------+----------+ 7 rows in set (0.00 sec)
mysql> select ename,job from emp where job in('MANAGER','SALESMAN');
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)
mysql> select ename,job from emp where job = 'MANAGER' union select ename,job from emp where job = 'SALESMAN' ; +--------+----------+ | ename | job | +--------+----------+ | JONES | MANAGER | | BLAKE | MANAGER | | CLARK | MANAGER | | ALLEN | SALESMAN | | WARD | SALESMAN | | MARTIN | SALESMAN | | TURNER | SALESMAN | +--------+----------+ 7 rows in set (0.00 sec)
union is more efficient. For table connection, each time a new table is connected, the matching times meet the Cartesian product and double the flip
But union can reduce the number of matches. In the case of reducing the number of matches, the splicing of two result sets can also be completed.
For example:
a connection b connection c
a 10 records
b 10 records
c 10 records
Matching times: 1000
A connects b to a result: 10 * 10 -- > 100 times
A connects c to a result: 10 * 10 -- > 100 times
Using union: 100 times + 100 times = 200 times. (union turns multiplication into addition)
matters needing attention:
//Wrong: union requires the same number of columns in two result sets when merging result sets.
select ename,job from emp where job = 'MANAGER' union select ename from emp where job = 'SALESMAN'; ERROR 1222 (21000): The used SELECT statements have a different number of columns
//MYSQL is OK, oracle syntax is strict, no, and an error is reported. Requirement: the data types of columns and columns should also be consistent during result set consolidation.
select ename,job from emp where job = 'MANAGER' union select ename,sal from emp where job = 'SALESMAN';
mysql> select ename,job from emp where job = 'MANAGER'
-> union
-> select ename,sal from emp where job = 'SALESMAN';
+--------+---------+
| ename | job |
+--------+---------+
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| ALLEN | 1600 |
| WARD | 1250 |
| MARTIN | 1250 |
| TURNER | 1500 |
+--------+---------+
7 rows in set (0.00 sec)
limit
effect
Part of the query result set is taken out, which is usually used in paging queries.
Baidu default: 10 records are displayed on one page.
The function of paging is to improve the user experience, because it is found all at once, and the user experience is poor. You can turn page by page
How to use it?
Take out the top 5 employees in descending salary order
Default writing
mysql> select ename,sal from emp order by sal desc limit 5; +-------+---------+ | ename | sal | +-------+---------+ | KING | 5000.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | +-------+---------+ 5 rows in set (0.00 sec)
Full usage: limit startIndex, length
startIndex is the starting subscript and length is the length.
The starting subscript starts at 0.
Default usage: limit 5; This is the top 5
Complete writing:
mysql> select ename,sal from emp order by sal desc limit 0,5; +-------+---------+ | ename | sal | +-------+---------+ | KING | 5000.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | +-------+---------+ 5 rows in set (0.00 sec)
be careful
In mysql, limit is executed after order by!!!!!!
Take out the employees with salary ranking in [3-5]
mysql> select ename,sal from emp order by sal desc limit 2,3;//2 indicates the starting position and 3 indicates the length +-------+---------+ | ename | sal | +-------+---------+ | FORD | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | +-------+---------+ 3 rows in set (0.00 sec)
Take out the employees with salary ranking in [5-9]
mysql> select ename,sal from emp order by sal desc limit 4,5; +--------+---------+ | ename | sal | +--------+---------+ | BLAKE | 2850.00 | | CLARK | 2450.00 | | ALLEN | 1600.00 | | TURNER | 1500.00 | | MILLER | 1300.00 | +--------+---------+ 5 rows in set (0.00 sec)
paging
3 records per page
Page 1: limit 0,3 [0 1 2]
Page 2: limit 3,3 [3,4,5]
Page 3: limit 6,3 [6,7,8]
Page 4: limit 9,3 [9, 10, 11]
pageSize records per page
Page pageNo: limit (pageNo - 1) * PageSize, PageSize
public static void main(String[] args){
// The user submits a page number and the number of records displayed on each page
int pageNo = 5; //Page 5
int pageSize = 10; //10 per page
int startIndex = (pageNo - 1) * pageSize;
String sql = "select ...limit " + startIndex + ", " + pageSize;
}
Note the formula:
*limit (pageNo-1)pageSize , pageSize
Summary of DQL statements
select ... from ... where ... group by ... having ... order by ... limit ...
Execution sequence?
1. from
2. where
3. group by
4. having
5. select
6. order by
7. limit...
Table creation
Syntax format of table creation
Creating a table is a DDL statement. DDL includes create drop alter
create table name (field name 1 data type, field name 2 data type, field name 3 data type);
create table Table name( Field name 1 data type, Field name 2 data type, Field name 3 data type );
Table name: t is recommended_ Or tbl_ Start, readable. See the name and know the meaning.
Field name: see the meaning of the name.
Both table and field names are identifiers.
Data types in Mysql
For many data types, we only need to master some common data types.
Varchar (maximum 255)
Variable length string
It is intelligent and saves space.
Space is dynamically allocated according to the actual data length.
Advantages: space saving
Disadvantages: it needs to allocate space dynamically and the speed is slow.
Char (maximum 255)
Fixed length string
Regardless of the actual data length.
Allocate a fixed length of space to store data.
Improper use may lead to a waste of space.
Advantages: there is no need to dynamically allocate space and the speed is fast.
Disadvantages: improper use may lead to a waste of space.
How should we choose varchar and char?
What do you choose for the gender field? Because gender is a fixed length string, char is selected.
What do you choose for the name field? Everyone's name is different in length, so choose varchar.
Int (max. 11)
An integer in a number. Equivalent to java int.
bigint
A long integer in a number. Equivalent to long in java.
float
Single precision floating point data
double
Double precision floating point data
date
Short date type
datetime
Long date type
clob
Character large object
Up to 4G strings can be stored.
For example: store an article and a description.
CLOB character large objects shall be used to store more than 255 characters.
Character Large OBject:CLOB
blob
Binary large object
Binary Large OBject
It is specially used to store streaming media data such as pictures, sound and video.
When inserting data into a BLOB type field, such as inserting a picture, video, etc,
You need to use IO streams.
t_movie movie table (dedicated to storing movie information)
number name storyline Release date duration poster type no(bigint) name(varchar) history(clob) playtime(date) time(double) image(blob) type(char) ------------------------------------------------------------------------------------------------------------------ 10000 nezha .......... 2019-10-11 2.5 .... '1' 10001 Wukong biography .......... 2019-11-11 1.5 .... '2' ....
Create a student table
Student number, name, age, gender, email address
create table t_student( no int, name varchar(32), sex char(1), age int(3), email varchar(255) );
mysql> create table t_student(
-> no int,
-> name varchar(32),
-> sex char(1),
-> age int(3),
-> email varchar(255)
-> );
Query OK, 0 rows affected (0.01 sec)
Delete table:
drop table t_student; // An error will be reported when this table does not exist!
// If this table exists, delete it mysql> drop table if exists t_student; Query OK, 0 rows affected (0.00 sec)
insert data (DML)
Syntax format:
insert into table name (field name 1, field name 2, field name 3...) values (value 1, value 2, value 3);
**Note: field names and values should correspond to each other** What is one-to-one correspondence?
The quantity should correspond. The data type should correspond to.
mysql> insert into t_student(no,name,sex,age,email) values(1,'z','m',20,'11111@123.com'); Query OK, 1 row affected (0.00 sec)
Disrupt the input order, and the field names and values should correspond one by one
mysql> insert into t_student(email,name,sex,age,no) values('lisi@123.com','lisi','f',20,2);
Query OK, 1 row affected (0.00 sec)
Give only the value of one field
mysql> insert into t_student(no) values(3); Query OK, 1 row affected (0.00 sec)
mysql> select * from t_student; +------+------+------+------+---------------+ | no | name | sex | age | email | +------+------+------+------+---------------+ | 1 | z | m | 20 | 11111@123.com | | 2 | lisi | f | 20 | lisi@123.com | | 3 | NULL | NULL | NULL | NULL | +------+------+------+------+---------------+ 3 rows in set (0.00 sec)
Insert a name field in the
mysql> select * from t_student; +------+------+------+------+---------------+ | no | name | sex | age | email | +------+------+------+------+---------------+ | 1 | z | m | 20 | 11111@123.com | | 2 | lisi | f | 20 | lisi@123.com | | 3 | NULL | NULL | NULL | NULL | | NULL | s | NULL | NULL | NULL | +------+------+------+------+---------------+ 4 rows in set (0.00 sec)
Note: if the insert statement is executed successfully, there must be one more record. If no value is specified for other fields, the default value is NULL.
Set default values when creating tables
mysql> create table t_student(
-> no int,
-> name varchar(32),
-> sex char(1) default 'm',
-> age int(3),
-> email varchar(255)
-> );
Query OK, 0 rows affected (0.01 sec)
see
mysql> desc t_student
-> ;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| no | int(11) | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| sex | char(1) | YES | | m | |
| age | int(3) | YES | | NULL | |
| email | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
Insert a value
mysql> insert into t_student(no) values(1); Query OK, 1 row affected (0.00 sec) mysql> select * from t_student; +------+------+------+------+-------+ | no | name | sex | age | email | +------+------+------+------+-------+ | 1 | NULL | m | NULL | NULL | +------+------+------+------+-------+ 1 row in set (0.00 sec)
Omit field name
Can the "field name" in the insert statement be omitted? sure
insert into t_student values(2); //FALSE
//Note: if the preceding field name is omitted, it is written! So write down all the values!
insert into t_student values(2, 'lisi', 'f', 20, 'lisi@123.com');
Insert insert date
Number formatting:
mysql> select ename,sal from emp; +--------+---------+ | ename | sal | +--------+---------+ | SMITH | 800.00 | | ALLEN | 1600.00 | | WARD | 1250.00 | | JONES | 2975.00 | | MARTIN | 1250.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | SCOTT | 3000.00 | | KING | 5000.00 | | TURNER | 1500.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | FORD | 3000.00 | | MILLER | 1300.00 | +--------+---------+ 14 rows in set (0.00 sec)
Format number: format
mysql> select ename,format(sal, '$999,999') as sal from emp; +--------+-------+ | ename | sal | +--------+-------+ | SMITH | 800 | | ALLEN | 1,600 | | WARD | 1,250 | | JONES | 2,975 | | MARTIN | 1,250 | | BLAKE | 2,850 | | CLARK | 2,450 | | SCOTT | 3,000 | | KING | 5,000 | | TURNER | 1,500 | | ADAMS | 1,100 | | JAMES | 950 | | FORD | 3,000 | | MILLER | 1,300 | +--------+-------+
str_to_date: converts the string varchar type to date type
date_format: converts the date type to a varchar string type with a certain format.
drop table if exists t_user; create table t_user( id int, name varchar(32), birth date // You can also use the date date type for birthdays );
Note: there is a naming convention in the database:
All identifiers are all lowercase, and words are connected with underscores.
mysql> desc t_user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(32) | YES | | NULL | | | birth | date | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)
insert data
mysql> insert into t_user(id,name,birth) values(1,'zs','01-10-1990'); ERROR 1292 (22007): Incorrect date value: '01-10-1990' for column
Something went wrong: the reason was a type mismatch. The database birth is of type date. Here, a string varchar is given.
What should I do? You can use str_ to_ The date function performs type conversion.
tr_ to_ The date function converts a string to the date type date
Syntax format:
str_to_date('String date', 'Date format')
Date format of mysql
%Y year %m month %d day %h Time %i branch %s second
mysql> insert into t_user(id,name,birth) values(1,'zs',str_to_date('01-10-1990','%d-%m-%Y'));
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_user; +------+------+------------+ | id | name | birth | +------+------+------------+ | 1 | zs | 1990-10-01 | +------+------+------------+ 1 row in set (0.00 sec)
str_ to_ The date function can convert the string varchar into date type data. It is usually used in insert, because a date type data is required during insert, and the string needs to be converted into date through this function.
be careful:
If the date string you provide is in this format, str_ to_ The date function is not needed!!!
%Y-%m-%d
Can be inserted directly
mysql> insert into t_user(id,name,birth) values(2, 'lisi', '1990-10-01'); Query OK, 1 row affected (0.00 sec)
mysql> select * from t_user; +------+------+------------+ | id | name | birth | +------+------+------------+ | 1 | zs | 1990-10-01 | | 2 | lisi | 1990-10-01 | +------+------+------------+ 2 rows in set (0.00 sec)
When querying, it can be displayed in a specific date format
date_format, this function can convert the date type to a string in a specific format.
mysql> select id,name,date_format(birth,'%m/%d/%y') as birth from t_user; +------+------+----------+ | id | name | birth | +------+------+----------+ | 1 | zs | 10/01/90 | | 2 | lisi | 10/01/90 | +------+------+----------+ 2 rows in set (0.00 sec)
date_ How to use the format function
date_ Format (date type data, 'date format')
This function is usually used to set the date format of the display in terms of query date.
mysql> select id,name,birth from t_user; +------+----------+------------+ | id | name | birth | +------+----------+------------+ | 1 | zhangsan | 1990-10-01 | | 2 | lisi | 1990-10-01 | +------+----------+------------+
In fact, the above SQL statements are formatted by default, and automatically convert the date type in the database to varchar type. The default date format of mysql is'% Y-%m-%d '
Date format in java
yyyy-MM-dd HH:mm:ss SSS
The difference between date and datetime
Date is a short date: only the date information is included.
datetime is a long date: including the information of year, month, day, hour, minute and second.
Create a new t_user
mysql> create table t_user(
-> id int,
-> name varchar(32),
-> birth date,
-> create_time datetime
-> );
Query OK, 0 rows affected (0.01 sec)
id is an integer
name is a string
birth is a short date
create_time is the creation time of this record: long date type
mysql short date default format:% Y-%m-%d
mysql long date default format:% Y -% m -% d% H:% I:% s
mysql> insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-10-01','2021-09-14 15:49:50'); Query OK, 1 row affected (0.00 sec)
mysql> select * from t_user; +------+----------+------------+---------------------+ | id | name | birth | create_time | +------+----------+------------+---------------------+ | 1 | zhangsan | 1990-10-01 | 2021-09-14 15:49:50 | +------+----------+------------+---------------------+ 1 row in set (0.00 sec)
How to get the current system time in mysql
now() function, and the obtained time has: hour, minute and second information!!!! Is of type datetime.
mysql> insert into t_user(id,name,birth,create_time) values(2,'l','1991-01-02',now()); Query OK, 1 row affected (0.00 sec) mysql> select * from t_user; +------+----------+------------+---------------------+ | id | name | birth | create_time | +------+----------+------------+---------------------+ | 1 | zhangsan | 1990-10-01 | 2021-09-14 15:49:50 | | 2 | l | 1991-01-02 | 2021-09-14 14:51:57 | +------+----------+------------+---------------------+ 2 rows in set (0.00 sec)
Modify update (DML)
Syntax format:
update table name set field name 1 = value 1, field name 2 = value 2, field name 3 = value 3... where condition;
Note: no restrictions will cause all data to be updated.
Change name and date of birth
mysql> update t_user set name = 'jack',birth = '2000-10-11' where id = 2; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t_user; +------+----------+------------+---------------------+ | id | name | birth | create_time | +------+----------+------------+---------------------+ | 1 | zhangsan | 1990-10-01 | 2021-09-14 15:49:50 | | 2 | jack | 2000-10-11 | 2021-09-14 14:51:57 | +------+----------+------------+---------------------+ 2 rows in set (0.00 sec)
Modify creation date
mysql> update t_user set create_time = now() where id =2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select *from t_user; +------+----------+------------+---------------------+ | id | name | birth | create_time | +------+----------+------------+---------------------+ | 1 | zhangsan | 1990-10-01 | 2021-09-14 15:49:50 | | 2 | jack | 2000-10-11 | 2021-09-14 15:06:32 | +------+----------+------------+---------------------+ 2 rows in set (0.00 sec)
Update all
update t_user set name = 'abc';
Delete data delet e (DML)
Syntax format
delete from table name where condition;
Note: if there are no conditions, all the data in the whole table will be deleted!
delete from t_user where id = 2;
insert into t_user(id) values(2);
delete from t_user; // Delete all!
Insert can insert records for more than one day at a time
mysql> desc t_user; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(32) | YES | | NULL | | | birth | date | YES | | NULL | | | create_time | datetime | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)
mysql> insert into t_user(id,name,birth,create_time) values
-> (1,'zs','1980-10-11',now()),
-> (2,'lisi','1981-10-11',now()),
-> (3,'wangwu','1982-10-11',now());
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
You can insert more than one record at a time:
insert into t_user(id,name,birth,create_time) values (1,'zs','1980-10-11',now()), (2,'lisi','1981-10-11',now()), (3,'wangwu','1982-10-11',now());
Syntax: insert into t_user(Field name 1,Field name 2) values(),(),(),();
Quick create table
mysql> create table emp2 as select * from emp; Query OK, 14 rows affected (0.01 sec) Records: 14 Duplicates: 0 Warnings: 0
Principle:
Create a new query result as a table!!
This can complete the quick copy of the table!!
The table is created, and the data in the table also exists!
mysql> create table mytable as select empno,ename from emp where job = 'MANAGER'; Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0
insert the query results into a table
mysql> create table dept_bak as select * from dept; Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from dept_bak; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ 4 rows in set (0.00 sec)
Insert the query results into the table with insert, which is rarely used
mysql> insert into dept_bak select * from dept; Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from dept_bak; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ 8 rows in set (0.00 sec)
Quickly delete the data in the table [truncate is important and must be mastered]
Delete Dept_ Data in bak table
delete from dept_bak; // This method of deleting data is slow.
mysql> delete from dept_bak; Query OK, 8 rows affected (0.00 sec)
Delete statement principle of deleting data (delete belongs to DML statement!!!)
The data in the table has been deleted, but the real storage space of this data on the hard disk will not be released!!!
The disadvantage of this deletion is that the deletion efficiency is relatively low.
The advantage of this deletion is that it supports rollback, and you can recover the data if you regret!!!
Principle of truncate statement deleting data
This deletion is more efficient. The table is truncated once and physically deleted.
Disadvantage of this deletion: rollback is not supported.
The advantage of this deletion: fast.
Usage: MySQL > truncate table Dept_ bak; (this is a DDL operation.)
If you need to delete a large table
When deleting, use delete. It may take 1 hour to delete! Low efficiency.
You can choose to use truncate to delete the data in the table. The deletion ends in less than 1 second. High efficiency.
However, before using truncate, you must carefully ask the customer if you really want to delete it, and warn that it cannot be recovered after deletion!
truncate is to delete the data in the table, and the table is still there
Delete table operation
drop table name// This is not to delete the data in the table, it is to delete the table.
Addition, deletion and modification of table structure
What is a modification to a table structure
Add a field, delete a field, modify a field!!!
To modify the table structure, you need to use: alter belong to DDL sentence
DDL include: create drop alter
First: in the actual development, once the requirements are determined and the table is designed, the table structure is rarely modified. Because the cost of modifying the table structure is relatively high when development is in progress. Modifying the java code corresponding to the table structure requires a lot of modification. The cost is relatively high. This responsibility should be borne by the designer!
Second: there are few operations to modify the table structure, so we don't need to master it. If you want to modify the table structure one day, you can use tools!!!!
The operation of modifying the table structure does not need to be written into the java program. In fact, it is not the category of java programmers.
constraint
What are constraints
English word corresponding to constraint: constraint
When creating a table, we can add some constraints to the fields in the table to ensure the integrity and effectiveness of the data in the table!!!
The function of constraints is to ensure that the data in the table is valid!!
What are the constraints
Non NULL constraint: not null
Uniqueness constraint: unique
Primary key constraint: primary key (PK for short)
Foreign key constraint: foreign key (FK for short)
Check constraint: check (not supported by mysql, supported by oracle)
Here we focus on four constraints:
not null
unique
primary key
foreign key
Non NULL constraint: not null
The field of a non NULL constraint not null constraint cannot be null.
create table t_vip( id int, name varchar(255) not null // not null, only column level constraints, no table level constraints! );
mysql> insert into t_vip(id,name) values (1,'z'),(2,'as'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into t_vip(id) values(3); ERROR 1364 (HY000): Field 'name' doesn't have a default value
Episode:
xxxx.sql files are called SQL script files.
A large number of sql statements are written in the sql script file.
When we execute the sql script file, all sql statements in the file will be executed!
For batch execution of SQL statements, you can use SQL script files.
How to execute sql script in mysql
mysql> source D:\course\03-MySQL\document\vip.sql ( D:/java/Mysqlsource/t_vip.sql)
When you arrive at the company on the first day of your actual work, the project manager will give you a xxx.sql file,
If you execute this script file, you will have the database data on your computer!
Uniqueness constraint: unique
The fields of the uniqueness constraint unique constraint cannot be duplicate, but can be NULL.
mysql> create table t_vip(
-> id int,
-> name varchar(255) unique,
-> email varchar(255));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t_vip(id,name,email) values (1,'z','z@qq.com');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_vip(id,name,email) values (2,'a','a@qq.com');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_vip(id,name,email) values (3,'c','c@qq.com');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_vip(id,name,email) values (4,'a','a@qq.com');
ERROR 1062 (23000): Duplicate entry 'a' for key 'name'
Can be null
mysql> insert into t_vip(id) values(4); Query OK, 1 row affected (0.00 sec) mysql> insert into t_vip(id) values(5); Query OK, 1 row affected (0.00 sec) mysql> select * from t_vip; +------+------+----------+ | id | name | email | +------+------+----------+ | 1 | z | z@qq.com | | 2 | a | a@qq.com | | 3 | c | c@qq.com | | 4 | NULL | NULL | | 5 | NULL | NULL | +------+------+----------+ 5 rows in set (0.00 sec)
New requirement: name and email fields are unique!!!!
drop table if exists t_vip; create table t_vip( id int, name varchar(255) unique, // Constraints added directly after columns are called column level constraints. email varchar(255) unique );
The creation of this table does not meet my above "new requirements".
This means that name is unique and email is unique. Each is unique.
The following data is in line with my "new needs".
However, if you create a table in the above way, the creation will certainly fail because 'zhangsan' and 'zhangsan' are repeated.
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');
How to create such a table to meet the new requirements?
drop table if exists t_vip; create table t_vip( id int, name varchar(255), email varchar(255), unique(name,email) // The constraint is not added after the column. This constraint is called a table level constraint. );
mysql> insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com'); Query OK, 1 row affected (0.00 sec) mysql> insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com'); Query OK, 1 row affected (0.00 sec) mysql> select * from t_vip; +------+----------+-------------------+ | id | name | email | +------+----------+-------------------+ | 1 | zhangsan | zhangsan@123.com | | 2 | zhangsan | zhangsan@sina.com | +------+----------+-------------------+ 2 rows in set (0.00 sec)
The name and email fields are united to be unique!!!
insert into t_vip(id,name,email) values(3,'zhangsan','zhangsan@sina.com'); ERROR 1062 (23000): Duplicate entry 'zhangsan-zhangsan@sina.com' for key 'name'
When to use table level constraints
When you need to combine multiple fields to add a constraint, you need to use table level constraints.
unique and not null can be combined
mysql> create table t_vip(
-> id int,
-> name varchar(255) not null unique
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc t_vip; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(255) | NO | PRI | NULL | | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
In mysql, if a field is constrained by not null and unique at the same time, the field will automatically become a primary key field. (Note: it's different in oracle!)
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'zhangsan'); //Error: name cannot be repeated insert into t_vip(id) values(2); //Error: name cannot be NULL.
Primary key constraint (very important)
Terms related to primary key constraints
Primary key constraint: a constraint.
Primary key field: a primary key constraint is added to this field. Such a field is called a primary key field
Primary key value: each value in the primary key field is called a primary key value.
What is a primary key? What's the use?
The primary key value is the unique identification of each row of records.
The primary key value is the ID number of each row!!!
Remember: any table should have a primary key. Without a primary key, the table is invalid!!
Characteristics of primary key: not null + unique (the primary key value cannot be NULL and cannot be repeated!)
How to add a primary key constraint to a table?
mysql> create table t_vip(
-> id int primary key, //Column level constraint
-> name varchar(255));
Query OK, 0 rows affected (0.00 sec)
insert data
mysql> insert into t_vip(id,name) values(1,'z'); Query OK, 1 row affected (0.00 sec) mysql> insert into t_vip(id,name) values(2,'a'); Query OK, 1 row affected (0.00 sec) mysql> insert into t_vip(id,name) values(2,'s'); ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
//Error: cannot repeat
insert into t_vip(id,name) values(2,'wangwu');
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
//Error: cannot be NULL
insert into t_vip(name) values('zhaoliu');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
Use table level constraints to add primary keys
mysql> create table t_vip(
-> id int,
-> name varchar(255),
-> primary key(id)); //Table level constraints
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t_vip(id,name) values(1,'zhangsan'); Query OK, 1 row affected (0.00 sec)
//error insert into t_vip(id,name) values(1,'lisi'); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
Table level constraints are mainly used to add constraints to multiple fields
drop table if exists t_vip; // id and name are combined as primary keys: composite primary keys!!!! create table t_vip( id int, name varchar(255), email varchar(255), primary key(id,name) );
mysql> insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com'); Query OK, 1 row affected (0.00 sec) mysql> insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com'); Query OK, 1 row affected (0.00 sec)
//Error: cannot repeat insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com'); ERROR 1062 (23000): Duplicate entry '1-lisi' for key 'PRIMARY'
Composite primary key is not recommended in actual development. It is recommended to use a single primary key!
Because the significance of the primary key value is the ID number of the row, so long as the meaning is reached, a single primary key can be achieved.
Composite primary key is complex, not recommended!!!
Can I add two primary key constraints to a table
mysql> create table t_vip(
-> id int primary key,
-> name varchar(255) primary key);
ERROR 1068 (42000): Multiple primary key definedsql
Conclusion: only one primary key constraint can be added to a table. (there can only be 1 primary key.)
Recommended primary key values:
int
bigint
char and other types.
It is not recommended to use: varchar as the primary key. Primary key values are generally numbers and are generally fixed in length!
Primary keys: in addition to single primary keys and composite primary keys, they can also be classified in this way
Natural primary key: the primary key value is a natural number and has nothing to do with business.
Business primary key: the primary key value is closely related to the business. For example, take the bank card account number as the primary key value. This is the business primary key!
Do you use more business primary keys or more natural primary keys in actual development?
Naturally, primary keys are used more often, because as long as they are not repeated, they do not need to be meaningful.
The business primary key is not good because once the primary key is linked to the business, when the business changes,
It may affect the primary key value, so the business primary key is not recommended. Try to use natural primary keys.
In mysql, there is a mechanism to help us automatically maintain a primary key value
create table t_vip( id int primary key auto_increment, //auto_increment means self increment, starting from 1 and increasing by 1! name varchar(255) );
mysql> insert into t_vip(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_vip(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_vip(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_vip(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_vip(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_vip(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_vip(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_vip(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_vip;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | zhangsan |
| 3 | zhangsan |
| 4 | zhangsan |
| 5 | zhangsan |
| 6 | zhangsan |
| 7 | zhangsan |
| 8 | zhangsan |
+----+----------+
8 rows in set (0.00 sec)
foreign key constraint (FK) is very important
Terms related to foreign key constraints:
Foreign key constraint: a foreign key constraint
Foreign key field: a foreign key constraint is added to this field
Foreign key value: each value in the foreign key field.
Business background:
Please design a database table to describe the information of "class and student"
The first scheme: classes and students are stored in a table
t_student no(pk) name classno classname ---------------------------------------------------------------------------------- 1 jack 100 Class 1, senior 3, No. 2 middle school, Yizhuang Town, Daxing District, Beijing 2 lucy 100 Class 1, senior 3, No. 2 middle school, Yizhuang Town, Daxing District, Beijing 3 lilei 100 Class 1, senior 3, No. 2 middle school, Yizhuang Town, Daxing District, Beijing 4 hanmeimei 100 Class 1, senior 3, No. 2 middle school, Yizhuang Town, Daxing District, Beijing 5 zhangsan 101 Class 2, senior 3, No. 2 middle school, Yizhuang Town, Daxing District, Beijing 6 lisi 101 Class 2, senior 3, No. 2 middle school, Yizhuang Town, Daxing District, Beijing 7 wangwu 101 Class 2, senior 3, No. 2 middle school, Yizhuang Town, Daxing District, Beijing 8 zhaoliu 101 Class 2, senior 3, No. 2 middle school, Yizhuang Town, Daxing District, Beijing
Analyze the disadvantages of the above schemes:
Data redundancy, space waste!!!!
This design is a failure!
The second scheme: one table for class and one table for students
t_class Class table classno(pk) classname ------------------------------------------------------ 100 Class 1, senior 3, No. 2 middle school, Yizhuang Town, Daxing District, Beijing 101 Class 1, senior 3, No. 2 middle school, Yizhuang Town, Daxing District, Beijing
t_student Student list no(pk) name cno(FK quote t_class This table is classno) ---------------------------------------------------------------- 1 jack 100 2 lucy 100 3 lilei 100 4 hanmeimei 100 5 zhangsan 101 6 lisi 101 7 wangwu 101 8 zhaoliu 101
When the cno field has no constraints, the data may be invalid. A 102 may appear, but class 102 does not exist.
Therefore, in order to ensure that the values in the cno field are 100 and 101, you need to add a foreign key constraint to the cno field.
Then: the cno field is the foreign key field. Each value in the cno field is a foreign key value.
be careful:
t_class is the parent table
t_student is a child table
Delete table order?
Delete the son first, then the father.
Order in which tables are created?
Create a parent before creating a child.
Order of deleting data?
Delete the son first, then the father.
The order in which data is inserted?
Insert parent before child.
drop table if exists t_student;
drop table if exists t_class;
create table t_class(
classno int primary key,
classname varchar(255)
);
create table t_student(
no int primary key auto_increment,
name varchar(255),
cno int,
foreign key(cno) references t_class(classno)
);
insert into t_class(classno,classname) values(100,'Class 1, senior 3, No. 2 middle school, Yizhuang Town, Daxing District, Beijing');
insert into t_class(classno,classname) values(101,'Class 1, senior 3, No. 2 middle school, Yizhuang Town, Daxing District, Beijing');
insert into t_student(name,cno) values('jack',100);
insert into t_student(name,cno) values('lucy',100);
insert into t_student(name,cno) values('lilei',100);
insert into t_student(name,cno) values('hanmeimei',100);
insert into t_student(name,cno) values('zhangsan',101);
insert into t_student(name,cno) values('lisi',101);
insert into t_student(name,cno) values('wangwu',101);
insert into t_student(name,cno) values('zhaoliu',101);
Think: the foreign key in the child table refers to a field in the parent table. Must the referenced field be a primary key?
Not necessarily a primary key, but at least a unique constraint.
Test: can foreign keys be NULL?
Foreign key values can be NULL.
mysql> insert into t_student(name,cno) values('zhaoliu',NULL);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_student;
+----+-----------+------+
| no | name | cno |
+----+-----------+------+
| 1 | jack | 100 |
| 2 | lucy | 100 |
| 3 | lilei | 100 |
| 4 | hanmeimei | 100 |
| 5 | zhangsan | 101 |
| 6 | lisi | 101 |
| 7 | wangwu | 101 |
| 8 | zhaoliu | 101 |
| 10 | zhaoliu | NULL |
+----+-----------+------+
9 rows in set (0.00 sec)
Storage engine (understand content)
What is a storage engine and what is its use?
Storage engine is a special term in MySQL, which is not found in other databases. (in Oracle, but not by this name)
The name of storage engine is high-end and atmospheric.
In fact, the storage engine is a way of storing / organizing data in a table.
Different storage engines store data in different ways.
How to add / specify a "storage engine" to a table
mysql> show create table t_student; +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t_student | CREATE TABLE `t_student` ( `no` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `cno` int(11) DEFAULT NULL, PRIMARY KEY (`no`), KEY `cno` (`cno`), CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 | +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
When creating a table, it can be used to the right of the ")" in the last parenthesis:
ENGINE to specify the storage ENGINE.
CHARSET to specify the character encoding of this table.
Conclusion:
The default mysql storage engine is InnoDB
The default character encoding method of mysql is utf8
Specify the storage engine and character encoding method when creating a table
create table t_product( id int primary key, name varchar(255) )engine=InnoDB default charset=gbk;
mysql> desc t_product
-> ;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
How to view which storage engines mysql supports
Command: show engines \G
mysql> show engines \G
*************************** 1. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 9. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
9 rows in set (0.00 sec)
mysql supports nine storage engines, and currently 5.5.36 supports eight. Different versions support different situations.
Introduce the commonly used mysql storage engines
MyISAM storage engine
The tables it manages have the following characteristics:
Each table is represented by three files:
Format file - definition of storage table structure (mytable.frm)
Data file - stores the contents of table rows (mytable.MYD)
Index file - index on storage table (mytable.MYI): index is the directory of a book, a mechanism to narrow the scanning range and improve query efficiency.
Can be converted to compressed, read-only tables to save space
Here's a hint:
For a table, as long as it is a primary key or a field with unique constraints, an index will be automatically created.
MyISAM storage engine features:
Can be converted to compressed, read-only tables to save space
This is the advantage of this storage engine!!!!
MyISAM does not support transaction mechanism and has low security.
InnoDB storage engine
This is the default storage engine of mysql, and it is also a heavyweight storage engine.
InnoDB supports transaction and automatic recovery mechanism after database crash.
The main feature of InnoDB storage engine is that it is very secure.
The tables it manages have the following main characteristics:
– each InnoDB table is represented as a. frm file in the database directory
– InnoDB tablespace is used to store the contents of a table (a tablespace is a logical name. A tablespace stores data + indexes.)
– provide a set of log files used to record transactional activities
– support transactions with commit, SAVEPOINT, and rollback
– full ACID compatible
– provide automatic recovery after MySQL server crash
– multi Version (MVCC) and row level locking
– support the integrity of foreign keys and references, including cascading deletion and update
The biggest feature of InnoDB is that it supports transactions:
To ensure data security. The efficiency is not very high, and can not be compressed, can not be converted to read-only, and can not save storage space well.
MEMORY storage engine
A table using the MEMORY storage engine whose data is stored in MEMORY and whose row length is fixed,
These two features make the MEMORY storage engine very fast.
The tables managed by the MEMORY storage engine have the following characteristics:
– in the database directory, each table is represented as a file in. frm format.
– table data and indexes are stored in memory. (the purpose is fast, fast query!)
– table level locking mechanism.
– cannot contain TEXT or BLOB fields.
The MEMORY storage engine was formerly known as the HEAP engine.
MEMORY engine advantage: query efficiency is the highest. There is no need to interact with the hard disk.
MEMORY engine disadvantages: unsafe, data disappears after shutdown. Because the data and index are in MEMORY.
affair
What is a transaction
A transaction is actually a complete business logic. It is the smallest unit of work and cannot be divided.
What is a complete business logic?
Assuming transfer, transfer 10000 from account A to account B
Subtract 10000 from account A (update statement)
Add 10000 to account B (update statement)
This is a complete business logic.
The above operations are the smallest unit of work. They either succeed or fail at the same time. They can't be divided.
These two update statements must succeed or fail at the same time to ensure that the money is correct.
Only DML statements can have transactions. Other statements have nothing to do with transactions
insert
delete
update
Only the above three statements are related to transactions, and nothing else.
Because only the above three statements are added, deleted and modified by the data in the database table.
As long as your operation involves adding, deleting and changing data, you must consider security issues.
Data security first!!!
Assuming that all businesses can be completed with only one DML statement, is it necessary to have a transaction mechanism
It is precisely because when doing something, multiple DML statements need to be combined together to complete it, so transactions need to exist. If any complex thing can be solved by a DML statement, then the transaction has no value.
What is a business?
In the final analysis, in essence, a transaction is actually the simultaneous success or failure of multiple DML statements!
Transaction: batch DML statements succeed or fail at the same time!
How can a transaction succeed and fail multiple DML statements at the same time
InnoDB storage engine: provides a set of log files used to record transactional activities
Transaction started:
insert
insert
insert
delete
update
update
update
The business is over!
During transaction execution, each DML operation will be recorded in the "log file of transactional activities".
During the execution of a transaction, we can commit the transaction or roll back the transaction.
Commit transaction
Clear the log file of transactional activities and completely persist all data into the database table.
Committing a transaction marks the end of the transaction. And it's the end of all success.
Rollback transaction
Undo all previous DML operations and empty the log files of transactional activities
Rolling back a transaction marks the end of the transaction. And it's the end of all failure.
How to commit a transaction and how to rollback a transaction
Commit transaction: commit; sentence
Rollback transaction: rollback; Statement (rollback can always be rolled back to the last commit point!)
The English word for a transaction is: transaction
Test the default transaction behavior in mysql
mysql supports automatic transaction submission by default. (auto submit)
What is auto submit
Every time a DML statement is executed, it is submitted once!
In fact, this kind of automatic submission is not in line with our development habit, because a business usually needs multiple DML statements to be executed together. In order to ensure the security of data, it must be submitted after being successful at the same time, so we can't submit one after executing one.
How to turn off the automatic submission mechanism of mysql
Execute this command first: start transaction;
Rollback transaction
---------------------------------Rollback transaction---------------------------------------- mysql> use bjpowernode; Database changed mysql> select * from dept_bak; Empty set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into dept_bak values(10,'abc', 'tj'); Query OK, 1 row affected (0.00 sec) mysql> insert into dept_bak values(10,'abc', 'tj'); Query OK, 1 row affected (0.00 sec) mysql> select * from dept_bak; +--------+-------+------+ | DEPTNO | DNAME | LOC | +--------+-------+------+ | 10 | abc | tj | | 10 | abc | tj | +--------+-------+------+ 2 rows in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from dept_bak; Empty set (0.00 sec)
Commit transaction
mysql> select * from dept_bak; Empty set (0.00 sec) mysql> insert into dept_bak values(10,'abc','tj'); Query OK, 1 row affected (0.00 sec) mysql> insert into dept_bak values(20,'abc','tj'); Query OK, 1 row affected (0.00 sec) mysql> insert into dept_bak values(20,'abc','tj'); Query OK, 1 row affected (0.00 sec) mysql> insert into dept_bak values(20,'abc','tj'); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from dept_bak; +--------+-------+------+ | DEPTNO | DNAME | LOC | +--------+-------+------+ | 10 | abc | tj | | 20 | abc | tj | | 20 | abc | tj | | 20 | abc | tj | +--------+-------+------+ 4 rows in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from dept_bak; +--------+-------+------+ | DEPTNO | DNAME | LOC | +--------+-------+------+ | 10 | abc | tj | | 20 | abc | tj | | 20 | abc | tj | | 20 | abc | tj | +--------+-------+------+ 4 rows in set (0.00 sec)
Transactions include four features
A: Atomicity
Transaction is the smallest unit of work. No further division.
C: Consistency
All transactions require that in the same transaction, all operations must succeed or fail at the same time to ensure data consistency.
1: I solation
There is some isolation between A transaction and B transaction.
There is A wall between classroom A and classroom B, which is isolation.
When transaction A operates A table, another transaction B also operates the table???
D: Persistence
A guarantee of the final end of the transaction. Transaction commit is equivalent to saving data not saved to the hard disk!
Focus on the isolation of transactions
There is A wall between classroom A and classroom B, which can be very thick or thin. This is the isolation level of the transaction. The thicker the wall, the higher the isolation level.
What are the isolation levels between transactions and transactions? 4 levels
Read uncommitted: read uncommitted
What is read uncommitted?
Transaction A can read the uncommitted data of transaction B.
The problems with this isolation level are:
Dirty reading! (Dirty Read)
We said we read dirty data.
This isolation level is generally theoretical. Most database isolation levels start at the second level!
Read committed: read committed
What is read committed?
Transaction A can only read the data after transaction B commits.
What does this isolation level solve?
The phenomenon of dirty reading is solved.
What is the problem with this level of isolation?
Data cannot be read repeatedly.
What is unrepeatable data?
After the transaction is started, three pieces of data are read for the first time, but the current transaction has not ended. It is possible that four pieces of data are read for the second time, and 3 is not equal to 4, which is called non repeatable reading.
This isolation level is relatively real data, and the data read every time is absolutely real.
The default isolation level of oracle database is read committed
Repeatable read: repeatable read "you can't read it after submitting. What you always read is the data when the transaction was just started"
What is a repeatable read?
After transaction A is started, no matter how long, the data read in transaction A is consistent every time. Even if transaction B has modified the data and committed it, the data read by transaction A has not changed, which is called repeatable reading.
What problem does repeatable reading solve?
It solves the problem of non repeatable data reading.
What are the problems with repeatable reading?
Phantom reading can occur.
Every time the data is read, it is an illusion. Not real enough!
The transaction starts at 9:00 in the morning. As long as the transaction does not end, the data read will still be the same at 9:00 in the evening!
What I read is an illusion. Not absolutely true.
This is the default transaction isolation level in mysql!!!!!!!!!!!
Serialization / serialization: serializable (highest isolation level)
This is the highest isolation level and the lowest efficiency. Solved all the problems.
This isolation level indicates that transactions are queued and cannot be concurrent!
synchronized, thread synchronization (transaction synchronization)
The data read every time is the most real and the efficiency is the lowest.
Verify various isolation levels
View segregated sectors:
mysql> SELECT @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+
mysql default isolation level
Table t tested_ user
Validation: read uncommitted
mysql> set global transaction isolation level read uncommitted; affair A affair B -------------------------------------------------------------------------------- use bjpowernode; use bjpowernode; start transaction; select * from t_user; start transaction; u select * from t_user;
Validation: read committed
mysql> set global transaction isolation level read committed;
affair A affair B
--------------------------------------------------------------------------------
use bjpowernode;
use bjpowernode;
start transaction;
start transaction;
select * from t_user;
insert into t_user values('zhangsan');
select * from t_user;
commit;
select * from t_user;
Verification: repeatable read
mysql> set global transaction isolation level repeatable read;
affair A affair B
--------------------------------------------------------------------------------
use bjpowernode;
use bjpowernode;
start transaction;
start transaction;
select * from t_user;
insert into t_user values('lisi');
insert into t_user values('wangwu');
commit;
select * from t_user;
Validation: serializable
mysql> set global transaction isolation level serializable;
affair A affair B
--------------------------------------------------------------------------------
use bjpowernode;
use bjpowernode;
start transaction;
start transaction;
select * from t_user;
insert into t_user values('abc');
select * from t_user;
commit;
Indexes
What is an index
Indexes are added to the fields of database tables. They are a mechanism to improve query efficiency. An index can be added to one field of a table. Of course, indexes can also be added to multiple fields. The index is equivalent to the directory of a book. It is a mechanism to narrow the scanning range.
For a dictionary, there are two ways to find a Chinese character:
The first way: search page by page until it is found. This search method belongs to full dictionary scanning.
The efficiency is relatively low.
The second way: first locate an approximate location through the directory (index), and then locate it directly
Position, do local scanning, narrow the scanning range and find quickly. This search method belongs to through
Index retrieval, high efficiency.
t_user
id(idIndex) name(nameIndex) email(emailIndex) address (emailAddressIndex) ---------------------------------------------------------------------------------- 1 zhangsan... 2 lisi 3 wangwu 4 zhaoliu 5 hanmeimei 6 jack
select * from t_user where name = 'jack';
The above SQL statement will scan the name field. Why?
Because the query criteria are: name = "jack"
If no index (directory) is added to the name field, or no index is created to the name field, MySQL will perform a full scan and compare each value on the name field. The efficiency is relatively low.
MySQL has two main query methods:
The first method: full table scanning
The second way: search according to the index.
be careful:
In practice, the directories in front of Chinese dictionaries are sorted according to a b c d e f,
Why sort? Because only after sorting can there be interval search! (reduce scanning range)
In fact, it's just scanning an interval!)
In mysql database, the index also needs to be sorted, and the sorting of this index is the same as that of TreeSet
The data structure is the same. The bottom layer of TreeSet (TreeMap) is a self balanced binary tree! In mysql
The index is a B-Tree data structure.
Follow the principle of small on the left and large on the right. The middle order traversal method is used to get the data.
Implementation principle of index
Suppose there is a user table: t_user id(PK) name Each line of record has a physical storage number on the hard disk ---------------------------------------------------------------------------------- 100 zhangsan 0x1111 120 lisi 0x2222 99 wangwu 0x8888 88 zhaoliu 0x9999 101 jack 0x6666 55 lucy 0x5555 130 tom 0x7777
Reminder 1: in any database, the index object will be automatically added to the primary key, and there will be an index in the id field automatically, because the id is PK. In addition, in mysql, if there is a unique constraint on a field, the index object will also be created automatically.
Reminder 2: in any database, any record of any table has a physical storage number of the hard disk on the hard disk storage.
Reminder 3: in mysql, the index is a single object. Different storage engines exist in different forms. In MyISAM storage engine, the index is stored in a. MYI file. In the InnoDB storage engine, indexes are stored in a logical name called tablespace. In the MEMORY storage engine, the index is stored in MEMORY. No matter where the index is stored, the index exists in the form of a tree in MySQL. (self balanced binary tree: B-Tree)
In mysql, indexes will be automatically added to the primary key and unique fields!!!!
Under what conditions would we consider adding indexes to fields?
Condition 1: the amount of data is huge (how huge is it? This needs to be tested because each hardware environment is different)
Condition 2: this field often appears after where and exists in the form of condition, that is, this field is always scanned.
Condition 3: this field has few DML(insert delete update) operations. (the index needs to be reordered after DML.)
It is recommended not to add indexes at will, because indexes also need to be maintained. Too many indexes will reduce the performance of the system.
It is recommended to query through the primary key and unique constrained fields. The efficiency is relatively high.
How to create an index? How to delete? What is grammar?
Create index:
mysql> create index emp_ename_index on emp(ename);
Add an index to the ename field of the emp table and name it emp_ename_index
Delete index:
mysql> drop index emp_ename_index on emp;
Place emp on the emp table_ ename_ Index index object deleted.
In mysql, how to check whether an SQL statement uses an index for retrieval?
mysql> explain select * from emp where ename = 'KING'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
Scan 14 records: it indicates that the index is not used. type=ALL
mysql> explain select * from emp where ename = 'KING'; +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+ | 1 | SIMPLE | emp | ref | emp_ename_index | emp_ename_index | 33 | const | 1 | Using where | +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
When the index fails, when does the index fail?
Case 1 of failure:
select * from emp where ename like '%T';
Even if an index is added to ename, it will not go through the index. Why?
The reason is that the fuzzy matching starts with "%"!
Try to avoid fuzzy queries starting with '%'.
This is an optimization means / strategy.
mysql> explain select * from emp where ename like '%T'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
Case 2 of failure:
When or is used, it will fail. If or is used, the conditional fields on both sides of or are required to have indexes before indexing. If one field has no index, the index on the other field will also be realized. So that's why or is not recommended.
mysql> explain select * from emp where ename = 'KING' or job = 'MANAGER'; +----+-------------+-------+------+-----------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-----------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp | ALL | emp_ename_index | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+-----------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
Case 3 of failure:
When using a composite index, the left column is not used for searching, and the index becomes invalid
What is a composite index?
Two or more fields are combined to add an index, which is called a composite index.
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+ | 1 | SIMPLE | emp | ref | emp_job_sal_index | emp_job_sal_index | 30 | const | 3 | Using where | +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
mysql> explain select * from emp where sal = 800; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
Case 4 of failure:
In where, the index column participates in the operation, and the index is invalid.
mysql> explain select * from emp where sal+1 = 800; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
Case 5 of failure:
In where, the index column uses the function
mysql> explain select * from emp where lower(ename) = 'smith'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
Index is an important means to optimize various databases. When optimizing, the priority factor is index.
Indexes are divided into many categories in the database?
- Single index: adds an index to a field.
- Composite index: add an index to two or more fields.
- Primary key index: adds an index to the primary key.
- Unique index: adds an index to a field with a unique constraint.
...
Note: it is not useful to add indexes to fields with weak uniqueness.
view
What is a view?
view: look at the same data from different angles.
How to create a view object? How to delete a view object?
Table replication:
mysql> create table dept2 as select * from dept;
mysql> create table dept2 as select * from dept; Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from dept2; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ 4 rows in set (0.00 sec)sql
Create view objects
mysql> create view dept2_view as select * from dept2; Query OK, 0 rows affected (0.01 sec)
Delete view object
mysql> drop view dept2_view; Query OK, 0 rows affected (0.00 sec)
Note: only DQL statements can be created in the form of view.
create view view_name as the statement here must be a DQL statement;
What do you do with views
We can add, delete, modify and query the view object. Adding, deleting, modifying and querying the view object will cause the original table to be operated! (view features: the operation of the view will affect the original table data.)
//View oriented query mysql> select * from dept2_view; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ 4 rows in set (0.00 sec
// View oriented insertion insert into dept2_view(deptno,dname,loc) values(60,'SALES', 'BEIJING');
mysql> insert into dept2_view(deptno,dname,loc) values(60,'SALES','BEIJING');
Query OK, 1 row affected (0.00 sec)
//The data of the original table has also changed
mysql> select * from dept2;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 60 | SALES | BEIJING |
+--------+------------+----------+
5 rows in set (0.00 sec)
mysql> select * from dept2_view
-> ;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 60 | SALES | BEIJING |
+--------+------------+----------+
5 rows in set (0.00 sec)
//View oriented deletion mysql> delete from dept2_view; Query OK, 5 rows affected (0.00 sec)
//Query original table data mysql> select * from dept2; Empty set (0.00 sec)
mysql> select e.ename,e.sal,d.dname from emp e join dept d on e.deptno = d.deptno; +--------+---------+------------+ | ename | sal | dname | +--------+---------+------------+ | CLARK | 2450.00 | ACCOUNTING | | KING | 5000.00 | ACCOUNTING | | MILLER | 1300.00 | ACCOUNTING | | SMITH | 800.00 | RESEARCH | | JONES | 2975.00 | RESEARCH | | SCOTT | 3000.00 | RESEARCH | | ADAMS | 1100.00 | RESEARCH | | FORD | 3000.00 | RESEARCH | | ALLEN | 1600.00 | SALES | | WARD | 1250.00 | SALES | | MARTIN | 1250.00 | SALES | | BLAKE | 2850.00 | SALES | | TURNER | 1500.00 | SALES | | JAMES | 950.00 | SALES | +--------+---------+------------+ 14 rows in set (0.01 sec)
mysql> create view emp_dept_view as select e.ename,e.sal,d.dname from emp e join dept d on e.deptno = d.deptno; Query OK, 0 rows affected (0.01 sec)
//Query view mysql> select * from emp_dept_view; +--------+---------+------------+ | ename | sal | dname | +--------+---------+------------+ | CLARK | 2450.00 | ACCOUNTING | | KING | 5000.00 | ACCOUNTING | | MILLER | 1300.00 | ACCOUNTING | | SMITH | 800.00 | RESEARCH | | JONES | 2975.00 | RESEARCH | | SCOTT | 3000.00 | RESEARCH | | ADAMS | 1100.00 | RESEARCH | | FORD | 3000.00 | RESEARCH | | ALLEN | 1600.00 | SALES | | WARD | 1250.00 | SALES | | MARTIN | 1250.00 | SALES | | BLAKE | 2850.00 | SALES | | TURNER | 1500.00 | SALES | | JAMES | 950.00 | SALES | +--------+---------+------------+ 14 rows in set (0.00 sec)
//View oriented update mysql> update emp_dept_view set sal = 1000 where dname = 'ACCOUNTING'; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0
//Original table data update mysql> select * from emp; +-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 1000.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 1000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1000.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+ 14 rows in set (0.00 sec)
What is the use of view objects in practical development? Convenient, simplified development and easy maintenance
create view emp_dept_view as select e.ename,e.sal,d.dname from emp e join dept d on e.deptno = d.deptno;
Suppose there is a very complex SQL statement that needs to be used repeatedly in different locations. Every time you use this SQL statement, you need to rewrite it. It's very long and troublesome. What should I do?
This complex SQL statement can be created as a view object.
Using the view object directly where you need to write this SQL statement can greatly simplify development.
It is also conducive to later maintenance, because only one location needs to be modified, and only the SQL statement mapped by the view object needs to be modified.
When we develop for view in the future, we can use the view like using table. You can add, delete, modify and query views. The view is not in memory, and the view object is also stored on the hard disk and will not disappear.
Another reminder:
The statement corresponding to a view can only be a DQL statement.
However, after the view object is created, you can add, delete, modify and query the view.
Episode:
Addition, deletion, modification and query, also known as CRUD.
CRUD is a term for communication between programmers in the company. Generally, we seldom say adding, deleting, modifying and checking.
Generally speaking, CRUD.
C:Create((added) R:Retrive((search: search) U:Update((modified) D:Delete((deleted)
DBA common commands
Key points:
Import and export of data (backup of data)
Learn about other commands. (keep this training log file. If you forget it later, you can open it, copy and paste it.)
Data export
Note: in the dos command window of windows:
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123456
Can I export the specified table
mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123456
Data import
Note: you need to log in to the mysql database server first.
Then create the database: create database bjpwernode;
Use database: use bjpwernode
Then initialize the database: source D: \ bjpwernode.sql
Three paradigms of database design
What is the database design paradigm?
Design basis of database table. Teach you how to design database tables.
Database design paradigm common
Three.
The first paradigm: any table must have a primary key, and each field is atomic and can no longer be divided.
The second paradigm: Based on the first paradigm, all non primary key fields are required to completely rely on the primary key without partial dependence.
The third paradigm: Based on the second paradigm, all non primary key fields are required to directly rely on the primary key without transitive dependency.
Statement: three paradigms are often asked by interviewers, so you must remember them by heart!
When designing database tables, follow the above paradigm to avoid data redundancy and space waste in the tables.
First paradigm
The most core and important paradigm, the design of all tables needs to be met.
There must be a primary key, and each field is atomic and cannot be divided.
Student number Student name contact information ------------------------------------------ 1001 Zhang San zs@gmail.com,1359999999 1002 Li Si ls@gmail.com,13699999999 1001 Wang Wu ww@163.net,13488888888
The above is the student list. Does it meet the first paradigm
Not satisfied. First, there is no primary key. Second: contact information can be divided into email address and telephone number
Student number(pk) Student name e-mail address contact number --------------------------------------------------------- 1001 Zhang San zs@gmail.com 1359999999 1002 Li Si ls@gmail.com 13699999999 1003 Wang Wu ww@163.net 13488888888
Second paradigm
Based on the first paradigm, all non primary key fields must be completely dependent on the primary key without partial dependence.
Student number Student name teacher number teacher name ---------------------------------------------------- 1001 Zhang San 001 Miss Wang 1002 Li Si 002 Miss Zhao 1003 Wang Wu 001 Miss Wang 1001 Zhang San 002 Miss Zhao
This table describes the relationship between students and teachers: (one student may have multiple teachers, and one teacher may have multiple students)
This is very typical: many to many relationship!
Analyze whether the above table meets the first normal form
Does not satisfy the first paradigm.
How to satisfy the first paradigm? modify
Student number+Teacher number(pk) Student name Teacher name ---------------------------------------------------- 1001 001 Zhang San Miss Wang 1002 002 Li Si Miss Zhao 1003 001 Wang Wu Miss Wang 1001 002 Zhang San Miss Zhao
Student No. teacher No. the two fields are combined as primary key and compound primary key (PK: Student No. + teacher No.)
After modification, the above table meets the first paradigm. But does it satisfy the second paradigm?
Not satisfied, "Zhang San" relies on 1001 and "Mr. Wang" relies on 001, which obviously produces partial dependence.
What are the disadvantages of generating partial dependencies?
Data redundancy. Space is wasted. "Zhang San" repeated, "Mr. Wang" repeated.
In order for the above table to meet the second paradigm, you need to design it as follows:
Use three tables to represent many to many relationships!!!!
Student list Student number(pk) Student name ------------------------------------ 1001 Zhang San 1002 Li Si 1003 Wang Wu Teacher table Teacher number(pk) Teacher name -------------------------------------- 001 Miss Wang 002 Miss Zhao Student teacher relationship form id(pk) Student number(fk) Teacher number(fk) ------------------------------------------------------ 1 1001 001 2 1002 002 3 1003 001 4 1001 002
Recite the formula:
How to design many to many?
Many to many, three tables, relationship table, two foreign keys!!!!!!!!
Third paradigm
The third paradigm is based on the second paradigm, which requires that all non primary key dictionaries must directly rely on the primary key and do not produce transitive dependency.
Student number( PK) Student name Class number Class name --------------------------------------------------------- 1001 Zhang San 01 Once a year 1002 Li Si 02 Two classes a year 1003 Wang Wu 03 Three classes a year 1004 Zhao Liu 03 Three classes a year
The design of the above table is to describe the relationship between class and students. It is obviously a 1-to-many relationship!
There are multiple students in a classroom.
Analyze whether the above table meets the first paradigm?
It satisfies the first normal form and has a primary key.
Analyze whether the above table meets the second paradigm?
The second paradigm is satisfied because the primary key is not a composite primary key and does not generate partial dependencies. The primary key is a single primary key.
Analyze whether the above table meets the third paradigm?
The third paradigm requires: do not generate transitive dependency!
One shift a year depends on 01, and 01 depends on 1001, resulting in transmission dependency.
It does not meet the requirements of the third paradigm, resulting in data redundancy.
So how to design one to many?
Class schedule: 1 Class number(pk) Class name ---------------------------------------- 01 Once a year 02 Two classes a year 03 Three classes a year Student list: multiple Student number( PK) Student name Class number(fk) ------------------------------------------- 1001 Zhang San 01 1002 Li Si 02 1003 Wang Wu 03 1004 Zhao Liu 03
Recite the formula:
One to many, two tables, many tables plus foreign keys!!!!!!!!!!!!
Design of summary table
One to many:
One to many, two tables, many tables plus foreign keys!!!!!!!!!!!!
Many to many:
Many to many, three tables, relationship table, two foreign keys!!!!!!!!!!!!!!!
one-on-one:
Don't you just put one-on-one in one table? Why split the table?
In the actual development, there may be too many and too large fields in a table. At this time, the table should be split.
How to design one-on-one?
Before splitting a table: a table
t_user
id login_name login_pwd real_name email address........
------------------------------------------------------------------------------------------------------
1 zhangsan 123 Zhang San zhangsan@xxx
2 lisi 123 Li Si lisi@xxx
...
This huge table is recommended to be split into two: t_login Login information table id(pk) login_name login_pwd ------------------------------------------ 1 zhangsan 123 2 lisi 123 t_user User details table id(pk) real_name email address........ login_id(fk+unique) ----------------------------------------------------------------------------------------- 100 Zhang San zhangsan@xxx 1 200 Li Si lisi@xxx 2
Pithy formula: one-to-one, unique foreign key!!!!!!!!!!
One word:
The three paradigms of database design are theoretical.
Practice and theory sometimes deviate.
The ultimate goal is to meet the needs of customers. Sometimes redundancy will be exchanged for execution speed.
Because in sql, the more connections between tables, the lower the efficiency. (Cartesian product)
Sometimes there may be redundancy, but in order to reduce the number of table connections, this is also reasonable, and the difficulty of writing sql statements will be reduced for developers.
In the interview, put this sentence: he won't think you are a junior programmer!
N | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1000.00 | NULL | 10 |
±------±-------±----------±-----±-----------±--------±--------±-------+
14 rows in set (0.00 sec)
### What is the use of view objects in practical development? Convenient, simplified development and easy maintenance create view emp_dept_view as select e.ename,e.sal,d.dname from emp e join dept d on e.deptno = d.deptno; Suppose there is a very complex SQL sentence,**And this one SQL Statements need to be used repeatedly in different locations**. Every time you use this sql Statements need to be rewritten. They are very long and troublesome. What should I do? You can put this complex SQL Statement to create a new as a view object. Write this article as needed SQL The position of the statement directly uses the view object, which can greatly simplify the development. It is also conducive to later maintenance, because only one location needs to be modified when modifying, and only the location mapped by the view object needs to be modified SQL sentence. When we develop for view in the future, we can use view like table Same.**You can add, delete, modify and query views**. The view is not in memory, and the view object is also stored on the hard disk and will not disappear. Another reminder: **The statement corresponding to the view can only be DQL sentence.** However, after the view object is created, you can add, delete, modify and query the view. Episode: **Addition, deletion, modification and query, also known as: CRUD**. CRUD Is a term for communication between programmers in the company. Generally, we seldom say adding, deleting, modifying and checking. Generally speaking CRUD.
C:Create((added) R:Retrive((search: search) U:Update((modified) D:Delete((deleted)
## DBA common commands ### Key points: Import and export of data (backup of data) Learn about other commands. (keep this training log file. If you forget it later, you can open it, copy and paste it.) ### Data export Note: in windows of dos In the command window:
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123456
#### Can I export the specified table
mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123456
### Data import Note: you need to log in to mysql On the database server. Then create the database:`create database bjpowernode;` Use database:`use bjpowernode` Then initialize the database:`source D:\bjpowernode.sql` ## Three paradigms of database design ### What is the database design paradigm? Design basis of database table. Teach you how to design database tables. ### Database design paradigm common 3 One. The first paradigm: any table must have a primary key, and each field is atomic and can no longer be divided. The second paradigm: Based on the first paradigm, all non primary key fields are required to completely rely on the primary key without partial dependence. The third paradigm: Based on the second paradigm, all non primary key fields are required to directly rely on the primary key without transitive dependency. Statement: three paradigms are often asked by interviewers, so you must remember them by heart! When designing database tables, follow the above paradigm to avoid data redundancy and space waste in the tables. ### First paradigm The most core and important paradigm, the design of all tables needs to be met. There must be a primary key, and each field is atomic and cannot be divided. ```sql Student number Student name contact information ------------------------------------------ 1001 Zhang San zs@gmail.com,1359999999 1002 Li Si ls@gmail.com,13699999999 1001 Wang Wu ww@163.net,13488888888
The above is the student list. Does it meet the first paradigm
Not satisfied. First, there is no primary key. Second: contact information can be divided into email address and telephone number
Student number(pk) Student name e-mail address contact number --------------------------------------------------------- 1001 Zhang San zs@gmail.com 1359999999 1002 Li Si ls@gmail.com 13699999999 1003 Wang Wu ww@163.net 13488888888
Second paradigm
Based on the first paradigm, all non primary key fields must be completely dependent on the primary key without partial dependence.
Student number Student name teacher number teacher name ---------------------------------------------------- 1001 Zhang San 001 Miss Wang 1002 Li Si 002 Miss Zhao 1003 Wang Wu 001 Miss Wang 1001 Zhang San 002 Miss Zhao
This table describes the relationship between students and teachers: (one student may have multiple teachers, and one teacher may have multiple students)
This is very typical: many to many relationship!
Analyze whether the above table meets the first normal form
Does not satisfy the first paradigm.
How to satisfy the first paradigm? modify
Student number+Teacher number(pk) Student name Teacher name ---------------------------------------------------- 1001 001 Zhang San Miss Wang 1002 002 Li Si Miss Zhao 1003 001 Wang Wu Miss Wang 1001 002 Zhang San Miss Zhao
Student No. teacher No. the two fields are combined as primary key and compound primary key (PK: Student No. + teacher No.)
After modification, the above table meets the first paradigm. But does it satisfy the second paradigm?
Not satisfied, "Zhang San" relies on 1001 and "Mr. Wang" relies on 001, which obviously produces partial dependence.
What are the disadvantages of generating partial dependencies?
Data redundancy. Space is wasted. "Zhang San" repeated, "Mr. Wang" repeated.
In order for the above table to meet the second paradigm, you need to design it as follows:
Use three tables to represent many to many relationships!!!!
Student list Student number(pk) Student name ------------------------------------ 1001 Zhang San 1002 Li Si 1003 Wang Wu Teacher table Teacher number(pk) Teacher name -------------------------------------- 001 Miss Wang 002 Miss Zhao Student teacher relationship form id(pk) Student number(fk) Teacher number(fk) ------------------------------------------------------ 1 1001 001 2 1002 002 3 1003 001 4 1001 002
Recite the formula:
How to design many to many?
Many to many, three tables, relationship table, two foreign keys!!!!!!!!
Third paradigm
The third paradigm is based on the second paradigm, which requires that all non primary key dictionaries must directly rely on the primary key and do not produce transitive dependency.
Student number( PK) Student name Class number Class name --------------------------------------------------------- 1001 Zhang San 01 Once a year 1002 Li Si 02 Two classes a year 1003 Wang Wu 03 Three classes a year 1004 Zhao Liu 03 Three classes a year
The design of the above table is to describe the relationship between class and students. It is obviously a 1-to-many relationship!
There are multiple students in a classroom.
Analyze whether the above table meets the first paradigm?
It satisfies the first normal form and has a primary key.
Analyze whether the above table meets the second paradigm?
The second paradigm is satisfied because the primary key is not a composite primary key and does not generate partial dependencies. The primary key is a single primary key.
Analyze whether the above table meets the third paradigm?
The third paradigm requires: do not generate transitive dependency!
One shift a year depends on 01, and 01 depends on 1001, resulting in transmission dependency.
It does not meet the requirements of the third paradigm, resulting in data redundancy.
So how to design one to many?
Class schedule: 1 Class number(pk) Class name ---------------------------------------- 01 Once a year 02 Two classes a year 03 Three classes a year Student list: multiple Student number( PK) Student name Class number(fk) ------------------------------------------- 1001 Zhang San 01 1002 Li Si 02 1003 Wang Wu 03 1004 Zhao Liu 03
Recite the formula:
One to many, two tables, many tables plus foreign keys!!!!!!!!!!!!
Design of summary table
One to many:
One to many, two tables, many tables plus foreign keys!!!!!!!!!!!!
Many to many:
Many to many, three tables, relationship table, two foreign keys!!!!!!!!!!!!!!!
one-on-one:
Don't you just put one-on-one in one table? Why split the table?
In the actual development, there may be too many and too large fields in a table. Split the table at this time.
How to design one-on-one?
Before splitting a table: a table
t_user
id login_name login_pwd real_name email address........
------------------------------------------------------------------------------------------------------
1 zhangsan 123 Zhang San zhangsan@xxx
2 lisi 123 Li Si lisi@xxx
...
This huge table is recommended to be split into two: t_login Login information table id(pk) login_name login_pwd ------------------------------------------ 1 zhangsan 123 2 lisi 123 t_user User details table id(pk) real_name email address........ login_id(fk+unique) ----------------------------------------------------------------------------------------- 100 Zhang San zhangsan@xxx 1 200 Li Si lisi@xxx 2
Pithy formula: one-to-one, unique foreign key!!!!!!!!!!
One word:
The three paradigms of database design are theoretical.
Practice and theory sometimes deviate.
The ultimate goal is to meet the needs of customers. Sometimes redundancy will be exchanged for execution speed.
Because in sql, the more connections between tables, the lower the efficiency. (Cartesian product)
Sometimes there may be redundancy, but in order to reduce the number of table connections, this is also reasonable, and the difficulty of writing sql statements will be reduced for developers.
In the interview, put this sentence: he won't think you are a junior programmer!