MySQL learning notes station B
DAY01
1. What is a database? What is a database management system? What is SQL? What is the relationship between them?
Database: English words DataBase，abbreviation DB. 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，abbreviation DBMS. 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 Statement, programmers write SQL Statement, and then DBMS Responsible for implementation SQL Statement to complete the addition, deletion, modification and query of data in the database. SQL Is a set of standards, programmers mainly learn is SQL Statement, this SQL stay mysql Can be used in, At the same time Oracle Can also be used in DB2 Can also be used in. The relationship between the three? DBMS--implement--> SQL --operation--> DB First install the database management system MySQL，Then learn SQL How to write and write statements SQL After the statement, DBMS yes SQL Statement to complete the data management of the database.
2. Install MySQL database management system.
Step 1: install first and select "classic version"
Step 2: MySQL database instance configuration is required.
Note: just take the next step along the way!!!!! Matters needing attention? Port number: Port number port Is any software/Applications will have, and the port number is the only representative of the application. The port number is usually the same as IP The address is together, IP Address is used to locate the port number of the computer port It is used to locate a service on the computer/Of an application! Port numbers cannot be duplicated on the same computer. Unique. mysql When the database is started, the default port number occupied by this service is 3306 This is what everyone knows. Remember. Character encoding method? set up mysql The character encoding method of the database is UTF8 Be sure to note: select the third radio button first, and then select utf8 Character set. Service name? The default is: MySQL Don't change it. Select the configuration environment variable path: What if there is no choice? You can configure it manually path=Other paths;C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin mysql The super administrator user name cannot be changed. It must be: root You need to set mysql The password of the database super administrator. We set it to 123456 It can be activated while setting the password root Account remote access. Active: indicates root The account can be logged in in other places. Inactive: indicates root The account can only be used on this computer. I choose to activate here!
3. Perfect uninstall of MySQL database!
Step 1: double click the installation package to uninstall and delete.
Step 2: delete directory:
Kill the MySQL directory under C: \ program data.
Kill the MySQL directory under C:\Program Files (x86).
So the uninstall is over!
4. Look at the services on the computer and find out where the MySQL service is?
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 "automatic" startup. Automatic startup means the next time the operating system is restarted
Automatically start the service.
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
5. How to use commands to start and shut down mysql services in windows operating system?
Syntax:
net stop service name;
net start service name;
The above commands can be used for the start and stop of other services.
6. mysql is installed and the service is started. How can I use the client to log in to the 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>
7. Common mysql commands:
sign out mysql : exit see mysql What databases are available in? 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 By default, it comes with 4 databases. How to choose to use a database? mysql> use test; Database changed Indicates that a name called test Database. 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 | +--------------------+ View which tables are under a database? mysql> show tables; Note: the above commands are not case sensitive. see mysql Version number of the database: mysql> select version(); +-----------+ | version() | +-----------+ | 5.5.36 | +-----------+ View which database is currently in use? mysql> select database(); +-------------+ | database() | +-------------+ | bjpowernode | +-------------+ mysql> show -> databases -> ; +--------------------+ | Database | +--------------------+ | information_schema | | bjpowernode | | mysql | | performance_schema | | test | +--------------------+ be careful: mysql It's gone“;"No execution“;"It means the end! mysql> show -> -> -> -> -> -> -> -> -> \c mysql> \c Used to terminate the input of a command.
8. The most basic unit in the database is the table: table
What is a table 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: OK( row): Called data/record. Column( column): Is called a field. 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.
9. About the classification of SQL statements?
SQL There are many sentences. It's best to classify them, so it's easier to remember. Divided into: DQL: Data query language (usually with select Keywords are all query statements) select... DML: Data operation language (it is used to add, delete and modify the data in the table DML) insert delete update insert increase delete Delete update change This is mainly the data in the operation table data. DDL: Data definition language Usually with create,drop,alter All of them DDL. DDL The main operation is the structure of the table, not the data in the table. create: New, equal to increase drop: delete alter: modify This addition, deletion and modification DML Different, this mainly operates on the table structure. TCL: Not ace TV. Transaction control language include: Transaction commit: commit; Transaction rollback: rollback; DCL: Is a data control language. For example: authorization grant,Revoke permissions revoke....
10. Import the data prepared in advance:
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!!!!
11. About the imported tables?
mysql> show tables; +-----------------------+ | Tables_in_bjpowernode | +-----------------------+ | dept | | emp | | salgrade | +-----------------------+
dept It's a department table emp Employee table salgrade It's a salary scale How to view the data in the table? select * from Table name; //Execute this SQL statement uniformly. mysql> select * from emp; // Query all data from the emp table. +-------+--------+-----------+------+------------+---------+---------+--------+ | 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 | +-------+-------+-------+
12. Instead of looking at the data in the table, just look at the table structure. There is a command:
desc table name;
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 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 | |post | 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 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 +-------+---------+------+-----+---------+-------+ describe Abbreviation: desc mysql> describe dept; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | DEPTNO | int(2) | NO | PRI | NULL | | | DNAME | varchar(14) | YES | | NULL | | | LOC | varchar(13) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+
13. Simple query
13.1. 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 | +------------+ 4 rows in set (0.00 sec) mysql> SELECT DNAME FROM DEPT; +------------+ | DNAME | +------------+ | ACCOUNTING | | RESEARCH | | SALES | | OPERATIONS | +------------+ 4 rows in set (0.00 sec)
13.2. How to query two or more fields?
Use commas to separate ","
Query department number and department name?
select deptno,dname from dept;
±-------±-----------+
| deptno | dname |
±-------±-----------+
| 10 | ACCOUNTING |
| 20 | RESEARCH |
| 30 | SALES |
| 40 | OPERATIONS |
±-------±-----------+
13.3. How to 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*
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.
13.4. Alias the queried column?
mysql> select deptno,dname as deptname from dept; +--------+------------+ | deptno | deptname | +--------+------------+ | 10 | ACCOUNTING | | 20 | RESEARCH | | 30 | SALES | | 40 | OPERATIONS | +--------+------------+
Alias with the as keyword.
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 +--------+------------+ | 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 in mysql
Can be used in.
Again, the strings in the database are enclosed in single quotes. This is standard.
Double quotation marks are not standard.
13.5. Calculate the annual salary of employees? sal * 12
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 | +--------+---------+ mysql> select ename,sal*12 from emp; // Conclusion: fields can use mathematical expressions! +--------+----------+ | 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 | +--------+----------+ mysql> 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 | +--------+----------+ mysql> select ename,sal*12 as 'Annual salary' from emp; //The alias is Chinese, enclosed in single quotation marks. +--------+----------+ | 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 | +--------+----------+
14. Condition query
14.1. What is conditional 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;
14.2 what are the conditions?
= be equal to
Query the name and number of employees whose salary is equal to 800?
select empno,ename from emp where sal = 800;
query SMITH Your number and salary?
select empno,sal from emp where ename = 'SMITH'; //Use single quotes for Strings
<>or!= Not equal to
Query the name and number of employees whose salary is not equal to 800?
select empno,ename from emp where sal != 800;
select empno,ename from emp where sal <> 800; // Unequal sign composed of less than sign and greater than sign
< less than
Query the name and number of employees whose salary is less than 2000?
mysql> select empno,ename,sal from emp where sal < 2000;
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7369 | SMITH | 800.00 |
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
| 7654 | MARTIN | 1250.00 |
| 7844 | TURNER | 1500.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7934 | MILLER | 1300.00 |
+-------+--------+---------+
<= Less than or equal to
Query the name and number of employees whose salary is less than or equal to 3000?
select empno,ename,sal from emp where sal <= 3000;
> greater than
Query the name and number of employees whose salary is greater than 3000?
select empno,ename,sal from emp where sal > 3000;
>= 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;
between ... and .... Between two values, Equivalent to >= and <=
Query employee information with salary between 2450 and 3000? Including 2450 and 3000
The first way:>= and <= (and Yes, and it means
select empno,ename,sal from emp where sal >= 2450 and sal <= 3000;
+-------+-------+---------+
| empno | ename | sal |
+-------+-------+---------+
| 7566 | JONES | 2975.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7902 | FORD | 3000.00 |
+-------+-------+---------+
The second way: between ... and ...
select
empno,ename,sal
from
emp
where
sal between 2450 and 3000;
be careful:
use between and When, we must follow the principle of "small on the left and big on the right".
between and Is a closed interval, including the values at both ends.
is null by null(is not null (not null)
Which employees' allowances are queried/Subsidy is 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: in the database null The equal sign cannot be used for measurement. Need to use is null
Because in the database null It represents nothing. It is not a value, so it cannot be used
Equal sign measurement.
Which employees' allowances are queried/The subsidy is not null？
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 |
+-------+--------+---------+---------+
and also
Query job position yes MANAGER Employee information with salary greater than 2500?
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 |
+-------+-------+---------+---------+
or perhaps
Query job position yes MANAGER and SALESMAN Employees?
select empno,ename,job from emp where job = 'MANAGER';
select empno,ename,job from emp where job = 'SALESMAN';
select
empno,ename,job
from
emp
where
job = 'MANAGER' or job = '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 |
+-------+--------+----------+
and and or If it happens at the same time, is there a priority problem?
Query employees whose salary is greater than 2500 and department number is 10 or 20?
select
*
from
emp
where
sal > 2500 and deptno = 10 or deptno = 20;
Analyze the problems of the above statements?
and Priority ratio or High.
The above statement will be executed first and，Then execute 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);
and and or At the same time, and Higher priority. If you want or Execute first and add "parentheses"
Later, in development, if you are not sure about the priority, you can add parentheses.
in Contains, equivalent to multiple or (not in (not in this range)
Query job position yes MANAGER and SALESMAN Employees?
select empno,ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
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 |
+-------+--------+----------+
be careful: in Not an interval. in Followed by the specific value.
Query employee information with salary of 800 and 5000?
select ename,sal from emp where sal = 800 or sal = 5000;
select ename,sal from emp where sal in(800, 5000); //This doesn't mean you can find 800 to 5000.
+-------+---------+
| ename | sal |
+-------+---------+
| SMITH | 800.00 |
| KING | 5000.00 |
+-------+---------+
select ename,sal from emp where sal in(800, 5000, 3000);
// not in indicates data that is not among these values.
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 in
is null
is not null
in
not in
like
Called fuzzy query, support%Or underline match
%Match any number of characters
Underscore: any character.
(%Is a special symbol,_ (also a special symbol)
Find out what the name contains O of
mysql> select ename from emp where ename like '%O%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD |
+-------+
Find the name to T End?
select ename from emp where ename like '%T';
Find the name to K Started?
select ename from emp where ename like 'K%';
Find out the second word each is A of
select ename from emp where ename like '_A%';
Find out what the third letter is R of
select ename from emp where ename like '__R%';
t_student Student list
name field
----------------------
zhangsan
lisi
wangwu
zhaoliu
jack_son
Find out what's in the name“_"of
select name from t_student where name like '%_%'; //That won't work.
mysql> select name from t_student where name like '%\_%'; // \Escape character.
+----------+
| name |
+----------+
| jack_son |
+----------+
15. Sort
15.1. Query the salary of all employees, and sort it?
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 | +--------+---------+
15.2. How to descending?
Specify descending order: select ename,sal from emp order by sal desc;
±-------±--------+
| 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 |
±-------±--------+
15.3 can I sort by two fields or by 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 the leading role. Ename sorting will be considered 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 | +--------+---------+
15.4. Understand: you can also sort according to the position of the field
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.
16. Comprehensive cases:
Find out the information of employees whose salary is between 1250 and 3000, and arrange them in descending order.
select
ename,sal
from
emp
where
sal between 1250 and 3000
order by
sal 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!)
17. Data processing function
17.1 data processing function is also called single line processing function
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!)
17.2 what are the common single line processing functions?
lower Convert 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 characteristic of single line processing functions.
upper Convert to 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 from 1 and there is no 0.
Find out what the first letter of the employee's name is A Employee information?
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 capital?
select name from t_student;
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 Take length
select length(ename) enamelength from emp;
+-------------+
| enamelength |
+-------------+
| 5 |
| 5 |
| 4 |
| 5 |
| 6 |
| 5 |
| 5 |
| 5 |
| 4 |
| 6 |
| 5 |
| 5 |
| 4 |
| 6 |
+-------------+
trim Go 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 Convert string to date
date_format format date
format Set thousands
case..when..then..when..then..else..end
When an employee's job is MANAGER The salary was raised by 10%，When the job is SALESMAN At the time of, wages were raised by 50%,Others are normal.
(Note: do not modify the database, but display the query result 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 rounding
select field from Table name;
select ename from emp;
select 'abc' from emp; // select is directly followed by "literal / literal"
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 must be wrong, because it will abc As the name of a field, go emp Find in table abc I went.
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 It can be followed by the field name of a table (which can be regarded as variable name) or literal quantity/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() Generate 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 null Convert to a specific value
ifnull Is an empty handler. Dedicated to empty.
In all databases, as long as there is NULL The final result of participating in the mathematical operation 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 = (a 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 |
+--------+----------+
be careful: NULL As long as you participate in the operation, the final result must be NULL. To avoid this phenomenon, you need to use ifnull Function.
ifnull Function usage: ifnull(data, What value is treated as)
If data is NULL What value should we treat this data structure as.
Subsidy is NULL When, treat the subsidy 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 |
+--------+----------+
18. Grouping function (multiline processing function)
5:
Count count
sum
avg average
max
min
Characteristics of multi line processing function: input multiple lines and finally output one line. 5 Number: count count sum Sum avg average value max Maximum min minimum value 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 | +----------+ Calculation of wages and salaries: mysql> select sum(sal) from emp; +----------+ | sum(sal) | +----------+ | 29025.00 | +----------+ Calculate average wage: mysql> select avg(sal) from emp; +-------------+ | avg(sal) | +-------------+ | 2073.214286 | +-------------+ 14 Add up all the wages and divide by 14. 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: grouping function is automatically ignored NULL，You don't need to be right in advance NULL Process. 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: in grouping function count(*)and count(Specific field)What's the difference? mysql> select count(*) from emp; +----------+ | count(*) | +----------+ | 14 | +----------+ mysql> select count(comm) from emp; +-------------+ | count(comm) | +-------------+ | 4 | +-------------+ count(Specific field): Indicates that all statistics under this field are not NULL The total number of elements. count(*): The total number of rows in the statistical table. (as long as there is one line of data) count be++) Because every line of records cannot be NULL，One column in a row of data is not NULL，Then this line of data is valid. The third point: grouping functions cannot be used directly in 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 ????????????????????????????????????????????????????????????????????? Group query(group by)Then I understand. Point 4: all grouping functions can be combined and used together. select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp; +----------+----------+----------+-------------+----------+ | sum(sal) | min(sal) | max(sal) | avg(sal) | count(*) | +----------+----------+----------+-------------+----------+ | 29025.00 | 800.00 | 5000.00 | 2073.214286 | 14 | +----------+----------+----------+-------------+----------+
19. Group query (very important: five stars * * * *)
19.1,What is 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? .... 19.2,Combine all the previous keywords together to see their execution order? 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 in where Back? select ename,sal from emp where sal > min(sal);//report errors. Because the grouping function can only be used after grouping. where At the time of execution, there is no grouping, so where Grouping functions cannot appear after. select sum(sal) from emp; This is not grouped. Why sum()Function can be used? because select stay group by Then execute. 19.3,Find out the salary and salary of each job? Implementation idea: group according to jobs, and then sum wages. 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 from emp Query data in the table. according to job Field. Then the data of each group is analyzed sum(sal) 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 statement is in mysql It can be executed, but it makes no sense. The above statement is in oracle An error is reported during execution. oracle Syntax ratio of mysql The grammar is strict( mysql The syntax of is relatively loose!) Key conclusions: In one select Statement, if any group by Statement, select Only fields participating in grouping and grouping functions can be followed. The others can't follow. 19.4,Find out the highest salary for each department What is the realization idea? Group according to the department number and find the maximum value of each group. select Add later ename The field has no meaning. In addition oracle Will report an error. mysql> select ename,deptno,max(sal) from emp group by deptno; +-------+--------+----------+ | ename | deptno | max(sal) | +-------+--------+----------+ | CLARK | 10 | 5000.00 | | SMITH | 20 | 3000.00 | | ALLEN | 30 | 2850.00 | +-------+--------+----------+ mysql> select deptno,max(sal) from emp group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | | 20 | 3000.00 | | 30 | 2850.00 | +--------+----------+ 19.5,Find out the maximum salary of "each department, different jobs"? +--------+-----------+---------+--------+ | ename | job | sal | deptno | +--------+-----------+---------+--------+ | MILLER | CLERK | 1300.00 | 10 | | KING | PRESIDENT | 5000.00 | 10 | | CLARK | MANAGER | 2450.00 | 10 | | FORD | ANALYST | 3000.00 | 20 | | ADAMS | CLERK | 1100.00 | 20 | | SCOTT | ANALYST | 3000.00 | 20 | | JONES | MANAGER | 2975.00 | 20 | | SMITH | CLERK | 800.00 | 20 | | BLAKE | MANAGER | 2850.00 | 30 | | MARTIN | SALESMAN | 1250.00 | 30 | | ALLEN | SALESMAN | 1600.00 | 30 | | TURNER | SALESMAN | 1500.00 | 30 | | WARD | SALESMAN | 1250.00 | 30 | | JAMES | CLERK | 950.00 | 30 | +--------+-----------+---------+--------+ Skill: combine two fields into one field. (combine two fields into a group) 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 | +--------+-----------+----------+ 19.6,use having You can further filter the data after grouping. having It cannot be used alone, having Cannot replace where，having must and group by Combined use. Find out the highest salary of each department and display 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. 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; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | +--------+----------+ Think about one question: the above sql Is the execution efficiency of statements low? It is relatively low. In fact, it can be considered as follows: find out all those greater than 3000, and then group them. select deptno,max(sal) from emp where sal > 3000 group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | +--------+----------+ Optimization strategy: where and having，Preference where，where I really can't finish it. Choose again having. 19.7,where No way???? Find out the average salary of each department, and it is required to display those whose average salary is higher than 2500. Step 1: find out the average salary of each department select deptno,avg(sal) from emp group by deptno; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 10 | 2916.666667 | | 20 | 2175.000000 | | 30 | 1566.666667 | +--------+-------------+ Step 2: it is required to display the average salary higher than 2500 select deptno,avg(sal) from emp group by deptno having avg(sal) > 2500; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 10 | 2916.666667 | +--------+-------------+
20. Big summary (single table query completed)
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 Query data from a table, First pass where Conditions filter out valuable data. Group these valuable data. After grouping, you can use having Continue filtering. select Find out. Last sort output! Find out the average salary of each position. It is required to display the average salary greater than 1500, except MANAGER Outside the post, It is required to be arranged in descending order of average salary. select job, avg(sal) as avgsal from emp where job <> 'MANAGER' group by job having avg(sal) > 1500 order by avgsal desc; +-----------+-------------+ | job | avgsal | +-----------+-------------+ | PRESIDENT | 5000.000000 | | ANALYST | 3000.000000 | +-----------+-------------+
DAY02
1. Remove duplicate records from query results [distinct]
Note: the original table data will not be modified, but the query results will be de duplicated.
Duplicate removal requires a 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? select count(distinct job) from emp; +---------------------+ | count(distinct job) | +---------------------+ | 5 | +---------------------+
2. Connection query
2.1. What is connection 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.
2.2. Classification of connection query?
According to the chronological classification of grammar: SQL92: 1992 Grammar appeared in SQL99: 1999 Grammar appeared in We focus on learning here SQL99.(A simple demonstration of this process SQL92 Examples of) 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)
2.3. What happens when two tables are connected and queried 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 | +--------+--------+ 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; +--------+------------+ | ename | dname | +--------+------------+ | SMITH | ACCOUNTING | | SMITH | RESEARCH | | SMITH | SALES | | SMITH | OPERATIONS | | ALLEN | ACCOUNTING | | ALLEN | RESEARCH | | ALLEN | SALES | | ALLEN | 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 entries in two tables is called Cartesian product. (Descartes discovered that this is A mathematical phenomenon.)
2.4. How to avoid Cartesian product?
Add a condition when connecting, and the records that meet this condition will be 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; // Table alias. Very important. Efficiency issues. 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 | +--------+------------+ 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. 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.
2.5. Equivalent connection of internal 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;
sql92 Disadvantages: the structure is not clear, and the connection conditions of the table and the conditions for further screening in the later stage are all placed in the table where Back.
SQL99 syntax:
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
//Inner 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.
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
Connection conditions of a and b
where
Screening conditions
2.6 non equivalent connection of internal connection
Case: find out the salary grade of each employee. Employee name, salary and salary grade are required to be displayed?
mysql> select * from emp; e +-------+--------+-----------+------+------------+---------+---------+--------+ | 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 | ....
mysql> select * from salgrade; s +-------+-------+-------+ | 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.
select e.ename, e.sal, s.grade from emp e inner 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 | +--------+---------+-------+
2.7 self connection of internal connection
Case: query the superior leader of an employee. The employee name and the corresponding leader name are required to be displayed?
mysql> select empno,ename,mgr from emp; +-------+--------+------+ | empno | ename | mgr | +-------+--------+------+ | 7369 | SMITH | 7902 | | 7499 | ALLEN | 7698 | | 7521 | WARD | 7698 | | 7566 | JONES | 7839 | | 7654 | MARTIN | 7698 | | 7698 | BLAKE | 7839 | | 7782 | CLARK | 7839 | | 7788 | SCOTT | 7566 | | 7839 | KING | NULL | | 7844 | TURNER | 7698 | | 7876 | ADAMS | 7788 | | 7900 | JAMES | 7698 | | 7902 | FORD | 7566 | | 7934 | MILLER | 7782 | +-------+--------+------+ Tip: look at one table as two. emp a Employee table +-------+--------+------+ | empno | ename | mgr | +-------+--------+------+ | 7369 | SMITH | 7902 | | 7499 | ALLEN | 7698 | | 7521 | WARD | 7698 | | 7566 | JONES | 7839 | | 7654 | MARTIN | 7698 | | 7698 | BLAKE | 7839 | | 7782 | CLARK | 7839 | | 7788 | SCOTT | 7566 | | 7839 | KING | NULL | | 7844 | TURNER | 7698 | | 7876 | ADAMS | 7788 | | 7900 | JAMES | 7698 | | 7902 | FORD | 7566 | | 7934 | MILLER | 7782 | +-------+--------+------+ emp b Leadership table +-------+--------+------+ | empno | ename | mgr | +-------+--------+------+ | 7369 | SMITH | 7902 | | 7499 | ALLEN | 7698 | | 7521 | WARD | 7698 | | 7566 | JONES | 7839 | | 7654 | MARTIN | 7698 | | 7698 | BLAKE | 7839 | | 7782 | CLARK | 7839 | | 7788 | SCOTT | 7566 | | 7839 | KING | NULL | | 7844 | TURNER | 7698 | | 7876 | ADAMS | 7788 | | 7900 | JAMES | 7698 | | 7902 | FORD | 7566 | | 7934 | MILLER | 7782 | +-------+--------+------+
select a.ename as 'Employee name', b.ename as 'Leader name' from emp a join emp b on a.mgr = b.empno; //Employee's leader number = leader's employee number +--------+--------+ | Employee name | Leader name| +--------+--------+ | 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 | +--------+--------+ 13 Records, no KING. <Internal connection The above is the inner connection: self connection, skill: one table is regarded as two tables.
2.8 external connection
mysql> select * from emp; e +-------+--------+-----------+------+------------+---------+---------+--------+ | 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; d +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+
Inner connection: (A and B are connected, and AB has no primary and secondary relationship. They are equal.)
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno; //Features of internal connection: complete the data query that can match this condition. +--------+------------+ | 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):
select e.ename,d.dname from emp e right join dept d on e.deptno = d.deptno; // outer can be omitted with strong readability. select e.ename,d.dname from emp e right outer join dept d on e.deptno = d.deptno; right What does it mean: it means to join The table on the right side of the keyword is regarded as the main table, which is mainly used to query all the data of this table and carry the table on the left side of the associated query. In the external connection, two tables are connected, resulting in a primary secondary relationship.
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; have right Is the right outer connection, also known as the right connection. have 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. +--------+------------+ | 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 | +--------+------------+ Thinking: the number of query results of external connection must be >= How many query results are connected within? correct. Case: query the superior leader of each employee. It is required to display the names of all employees and leaders? select a.ename as 'Employee name', b.ename as 'Leader name' from emp a left join emp b on a.mgr = b.empno;
+--------+--------+ | Employee name | Leader name | +--------+--------+ | 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 | +--------+--------+
2.9 how to connect three tables and four tables?
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
One SQL Inner and outer connections can be mixed. Can appear! Case: find out the Department name and salary grade of each employee. The employee name, department name, salary and salary grade are required to be displayed? 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, Employee name, leader name, department name, salary and salary grade are required to be displayed? select e.ename,e.sal,d.dname,s.grade,l.ename from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal left join emp l on e.mgr = l.empno; +--------+---------+------------+-------+-------+ | ename | sal | dname | grade | ename | +--------+---------+------------+-------+-------+ | SMITH | 800.00 | RESEARCH | 1 | FORD | | ALLEN | 1600.00 | SALES | 3 | BLAKE | | WARD | 1250.00 | SALES | 2 | BLAKE | | JONES | 2975.00 | RESEARCH | 4 | KING | | MARTIN | 1250.00 | SALES | 2 | BLAKE | | BLAKE | 2850.00 | SALES | 4 | KING | | CLARK | 2450.00 | ACCOUNTING | 4 | KING | | SCOTT | 3000.00 | RESEARCH | 4 | JONES | | KING | 5000.00 | ACCOUNTING | 5 | NULL | | TURNER | 1500.00 | SALES | 3 | BLAKE | | ADAMS | 1100.00 | RESEARCH | 1 | SCOTT | | JAMES | 950.00 | SALES | 1 | BLAKE | | FORD | 3000.00 | RESEARCH | 4 | JONES | | MILLER | 1300.00 | ACCOUNTING | 2 | CLARK | +--------+---------+------------+-------+-------+
3. Subquery?
3.1. What is a subquery?
Select statements are nested in select statements. Nested select statements are called subqueries.
3.2 where can subqueries appear?
select ..(select). from ..(select). where ..(select).
3.3 subquery in where clause
Case: find out the name and salary of employees who are higher than the minimum wage? select ename,sal from emp where sal > min(sal); ERROR 1111 (HY000): Invalid use of group function where Grouping functions cannot be used directly in clause. Implementation idea: Step 1: what is the minimum wage select min(sal) from emp; +----------+ | min(sal) | +----------+ | 800.00 | +----------+ Step 2: find out>800 of select ename,sal from emp where sal > 800; Step 3: Merge 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 | +--------+---------+
3.4 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 position (average by position grouping) select job,avg(sal) from emp group by job; +-----------+-------------+ | job | avgsal | +-----------+-------------+ | ANALYST | 3000.000000 | | CLERK | 1037.500000 | | MANAGER | 2758.333333 | | PRESIDENT | 5000.000000 | | SALESMAN | 1400.000000 | +-----------+-------------+t surface Step 2: overcome psychological barriers and treat the above query results as a real table t. mysql> select * from salgrade; s surface +-------+-------+-------+ | GRADE | LOSAL | HISAL | +-------+-------+-------+ | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | +-------+-------+-------+ t Table and s Table connection, condition: t surface avg(sal) between s.losal and s.hisal; select t.*, s.grade 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; +-----------+-------------+-------+ | job | avgsal | grade | +-----------+-------------+-------+ | CLERK | 1037.500000 | 1 | | SALESMAN | 1400.000000 | 2 | | ANALYST | 3000.000000 | 4 | | MANAGER | 2758.333333 | 4 | | PRESIDENT | 5000.000000 | 5 | +-----------+-------------+-------+
3.5. Sub query after select (you don't need to master this content, just understand!!!)
Case: find out the Department name of each employee. It is required to display the employee name and department name?
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 | +--------+--------+------------+ //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 select For the following sub query, this sub query can only return one result at a time. If there is more than one result, an error will be reported.!
4. union merge query result set
Case: query employees whose jobs are MANAGER and SALESMAN?
select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
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 |
+--------+----------+
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 |
±-------±---------+
union is more efficient. For table joins, each time a new table is joined,
Then the number of matches satisfies the Cartesian product and doubles...
But union can reduce the number of matches. In the case of reducing the number of matches,
You can also splice two result sets.
a connect b connect c a 10 Records b 10 Records c 10 Records Matching times: 1000 a connect b One result: 10 * 10 --> 100 second a connect c One result: 10 * 10 --> 100 second use union The words are: 100 times + 100 second = 200 Times. ( union Turn multiplication into addition)
Are there any precautions when using union?
//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'; // 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'; +--------+---------+ | ename | job | +--------+---------+ | JONES | MANAGER | | BLAKE | MANAGER | | CLARK | MANAGER | | ALLEN | 1600 | | WARD | 1250 | | MARTIN | 1250 | | TURNER | 1500 | +--------+---------+
5. limit (very important)
5.1. limit function: take out part of the query result set. It 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.
5.2 how to use limit?
Full usage: limit startIndex, length startIndex Is the starting subscript, length Is the length. The starting subscript starts at 0. Default usage: limit 5; This is the top 5. Take out the top 5 employees in descending salary order? select ename,sal from emp order by sal desc limit 5; //Top 5 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.3. Note: in mysql, limit is executed after order by!!!!!!
5.4. Take out the employees with salary ranking in [3-5]?
select ename,sal from emp order by sal desc limit 2, 3;
2 Indicates that the starting position starts from subscript 2, which is the third record. 3 Indicates the length. +-------+---------+ | ename | sal | +-------+---------+ | FORD | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | +-------+---------+
5.5. Take out the employees with salary ranking in [5-9]?
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.6 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
6. 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..
7. Table creation (table creation)
7.1. Syntax format of table creation: (table creation belongs to DDL statement, and DDL includes create drop alter)
create table 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: it is recommended to t_ perhaps 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.
7.2. About 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. varchar and char How should we choose? What do you choose for the gender field? Because gender is a fixed length string, select char. 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 of int. bigint A long integer in a number. Equivalent to java Medium long. 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 4 can be stored G String of. For example: store an article and a description. If it exceeds 255 characters, it shall be used CLOB Characters are stored as large objects. 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. to BLOB When inserting data into a field of type, such as inserting a picture, video, etc, You need to use IO Flow. 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 Lin Zhengying's mother ........... 2019-11-11 1.5 .... '2' ....
7.3. 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)
);
Delete table: drop table t_student; // An error will be reported when this table does not exist! // If this table exists, delete it drop table if exists t_student;
7.4. 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.
insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m',20,'zhangsan@123.com');
insert into t_student(email,name,sex,age,no) values('lisi@123.com','lisi','f',20,2);
insert into t_student(no) values(3);
+------+----------+------+------+------------------+
| no | name | sex | age | email |
+------+----------+------+------+------------------+
| 1 | zhangsan | m | 20 | zhangsan@123.com |
| 2 | lisi | f | 20 | lisi@123.com |
| 3 | NULL | NULL | NULL | NULL |
+------+----------+------+------+------------------+
insert into t_student(name) values('wangwu');
+------+----------+------+------+------------------+
| no | name | sex | age | email |
+------+----------+------+------+------------------+
| 1 | zhangsan | m | 20 | zhangsan@123.com |
| 2 | lisi | f | 20 | lisi@123.com |
| 3 | NULL | NULL | NULL | NULL |
| NULL | wangwu | NULL | NULL | NULL |
+------+----------+------+------+------------------+
be careful: insert If the statement is executed successfully, there must be one more record.
If no value is specified for other fields, the default value is NULL.
drop table if exists t_student;
create table t_student(
no int,
name varchar(32),
sex char(1) default 'm',
age int(3),
email varchar(255)
);
+-------+--------------+------+-----+---------+-------+
| 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 | |
+-------+--------------+------+-----+---------+-------+
insert into t_student(no) values(1);
mysql> select * from t_student;
+------+------+------+------+-------+
| no | name | sex | age | email |
+------+------+------+------+-------+
| 1 | NULL | m | NULL | NULL |
+------+------+------+------+-------+
insert Can the "field name" in the 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');
+------+------+------+------+--------------+
| no | name | sex | age | email |
+------+------+------+------+--------------+
| 1 | NULL | m | NULL | NULL |
| 2 | lisi | f | 20 | lisi@123.com |
+------+------+------+------+--------------+
7.5 insert date
Number formatting: format
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 |
+--------+---------+
Format number: format(number, 'format')
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: String varchar Type conversion to date type
date_format: take date Type to a formatted varchar String type.
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
);
create table t_user(
id int,
name varchar(32),
birth char(10) // Birthday can use string, no problem.
);
Birthday: 1990-10-11 (10 (characters)
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 | |
+-------+-------------+------+-----+---------+-------+
Insert data?
insert into t_user(id,name,birth) values(1, 'zhangsan', '01-10-1990'); // October 1, 1990
Something went wrong: the reason was a type mismatch. database birth yes date Type, a string is given here varchar.
What should I do? have access to str_to_date Function for type conversion.
str_to_date The function converts a string to a date type date？
Syntax format:
str_to_date('String date', 'Date format')
mysql Date format:
%Y year
%m month
%d day
%h Time
%i branch
%s second
insert into t_user(id,name,birth) values(1, 'zhangsan', str_to_date('01-10-1990','%d-%m-%Y'));
str_to_date Function can convert a string varchar Convert to date date Type data,
Usually used in insert insert Because a date type data is required when inserting,
You need to convert the string to date.
hot wire?
If the date string you provide is in this format, str_to_date Function is not needed!!!
%Y-%m-%d
insert into t_user(id,name,birth) values(2, 'lisi', '1990-10-01');
Can the query be displayed in a specific date format?
date_format
This function converts the date type to a string in a specific format.
select id,name,date_format(birth, '%m/%d/%Y') as birth from t_user;
+------+----------+------------+
| id | name | birth |
+------+----------+------------+
| 1 | zhangsan | 10/01/1990 |
| 2 | lisi | 10/01/1990 |
+------+----------+------------+
date_format How does the function work?
date_format(Date type data, 'Date format')
This function is usually used to query dates. Format the displayed date.
mysql> select id,name,birth from t_user;
+------+----------+------------+
| id | name | birth |
+------+----------+------------+
| 1 | zhangsan | 1990-10-01 |
| 2 | lisi | 1990-10-01 |
+------+----------+------------+
Above SQL The statement is actually formatted with the default date,
Automatically convert data in the database date Type conversion to varchar Type.
And the format is mysql Default date format:'%Y-%m-%d'
select id,name,date_format(birth,'%Y/%m/%d') as birth from t_user;
java Date format in?
yyyy-MM-dd HH:mm:ss SSS
7.6 what are the differences 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.
drop table if exists t_user; create table t_user( id int, name varchar(32), birth date, create_time datetime ); id Is an integer name Is a string birth It's 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 insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-10-01','2020-03-18 15:49:50'); stay mysql How to get the current time of the system? now() Function, and the obtained time has: hour, minute and second information!!!! yes datetime Type. insert into t_user(id,name,birth,create_time) values(2,'lisi','1991-10-01',now());
7.7. 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. update t_user set name = 'jack', birth = '2000-10-11' where id = 2; +------+----------+------------+---------------------+ | id | name | birth | create_time | +------+----------+------------+---------------------+ | 1 | zhangsan | 1990-10-01 | 2020-03-18 15:49:50 | | 2 | jack | 2000-10-11 | 2020-03-18 15:51:23 | +------+----------+------------+---------------------+ update t_user set name = 'jack', birth = '2000-10-11', create_time = now() where id = 2; Update all? update t_user set name = 'abc';
7.8. delete data (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!
DAY03
1. Query the Department name of each employee? The employee name and department name are required to be displayed.
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 | +--------+------------+----------+
Get ename from the emp table and dname from the dept table. There are no restrictions. What is the final query result?
ENAME DNAME SMITH ACCOUNTING Invalid record SMITH RESEARCH Effective record SMITH SALES Invalid record SMITH OPERATIONS Invalid record ALLEN ACCOUNTING ALLEN RESEARCH ALLEN SALES ALLEN OPERATIONS .....
56 records.
Adding a condition is not only to achieve one out of four, but also to the effectiveness of the data.
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno; Conditions are added only to avoid Cartesian product and to query effective combination records. The number of matches is not less than 56.
2. Can insert statements insert multiple records at a time? [Master]
Yes!
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 | | +-------------+-------------+------+-----+---------+-------+
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(),(),(),(); mysql> select * from t_user; +------+--------+------------+---------------------+ | id | name | birth | create_time | +------+--------+------------+---------------------+ | 1 | zs | 1980-10-11 | 2020-03-19 09:37:01 | | 2 | lisi | 1981-10-11 | 2020-03-19 09:37:01 | | 3 | wangwu | 1982-10-11 | 2020-03-19 09:37:01 | +------+--------+------------+---------------------+
3. Create table quickly? [understanding content]
mysql> create table emp2 as select * from emp; 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!!! create table mytable as select empno,ename from emp where job = 'MANAGER';
4. insert query results into a table? insert related!!! [understanding content]
create table dept_bak as select * from dept; mysql> select * from dept_bak; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+
insert into dept_bak select * from dept; //Rarely used! 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 | +--------+------------+----------+
5. Quickly delete 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> select * from dept_bak; Empty set (0.00 sec) delete How does the statement delete data? ( delete belong 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!!! truncate How does the statement delete 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: truncate table dept_bak; (This operation belongs to DDL Operation.) The big table is very large, with hundreds of millions of records???? When deleting, use delete，It may take 1 hour to delete! Low efficiency. You can choose to use truncate Delete the data in the table. The deletion ends in less than 1 second. High efficiency. But use truncate Before, you must carefully ask the customer if you really want to delete it, and warn that it cannot be recovered after deletion! truncate Delete the data in the table. The table is still there! Delete table? drop table Table name; // This is not to delete the data in the table, it is to delete the table.
6. Addition, deletion and modification of table structure?
What is the modification of 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, there are few changes Modify the table structure. Because the cost of modifying the table structure is relatively high when development is in progress. Modify the structure of the table and the corresponding java The code needs a lot of modification. The cost is relatively high. This responsibility should be borne by the designer! Second: since there are few operations to modify the table structure, we don't need to master it. If one day If you really want to modify the table structure, you can use tools!!!! To modify the table structure, you do not need to write java In the program. Not really java The category of programmers.
7. Constraints (very important, five stars * * * *)
7.1 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 consistency of the data in the table
Integrity and effectiveness!!!
The function of constraints is to ensure that the data in the table is valid!!
7.2 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
7.3. Non NULL constraint: not null
Non NULL constraint not null Constrained fields cannot be empty NULL. drop table if exists t_vip; create table t_vip( id int, name varchar(255) not null // not null, only column level constraints, no table level constraints! ); insert into t_vip(id,name) values(1,'zhangsan'); insert into t_vip(id,name) values(2,'lisi'); insert into t_vip(id) values(3); ERROR 1364 (HY000): Field 'name' doesn't have a default value Episode: xxxx.sql Such documents are called sql Script file. sql A large number of scripts are written in the script file sql sentence. We execute sql When the script file, all the sql All statements will be executed! Batch execution SQL Statement, you can use sql Script file. stay mysql How to implement it sql What about the script? mysql> source D:\course\03-MySQL\document\vip.sql When you arrive at the company on the first day of your actual work, the project manager will give you an opportunity xxx.sql Documents, If you execute this script file, you will have the database data on your computer!
7.4. Uniqueness constraint: unique
Uniqueness constraint unique Constrained fields cannot be duplicate, but can be NULL. drop table if exists t_vip; create table t_vip( id int, name varchar(255) unique, email varchar(255) ); insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com'); insert into t_vip(id,name,email) values(2,'lisi','lisi@123.com'); insert into t_vip(id,name,email) values(3,'wangwu','wangwu@123.com'); select * from t_vip; insert into t_vip(id,name,email) values(4,'wangwu','wangwu@sina.com'); ERROR 1062 (23000): Duplicate entry 'wangwu' for key 'name' insert into t_vip(id) values(4); insert into t_vip(id) values(5); +------+----------+------------------+ | id | name | email | +------+----------+------------------+ | 1 | zhangsan | zhangsan@123.com | | 2 | lisi | lisi@123.com | | 3 | wangwu | wangwu@123.com | | 4 | NULL | NULL | | 5 | NULL | NULL | +------+----------+------------------+ name Although the field is unique Constraints, but can be NULL. New requirements: name and email The combination of two fields is 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 creates a representation: name Unique, email 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'Repeat. 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. ); 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'); select * from t_vip; name and email The two fields are unique when combined!!! 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 we unite? drop table if exists t_vip; create table t_vip( id int, name varchar(255) not null unique ); mysql> desc t_vip; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(255) | NO | PRI | NULL | | +-------+--------------+------+-----+---------+-------+ stay mysql If a field is simultaneously not null and unique If bound, This field automatically becomes a primary key field. (Note: oracle (different in!) 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.
7.5. The primary key constraint (PK) is very important. There are five stars*****
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(Primary key value cannot be NULL，Also cannot be repeated!)
How to add a primary key constraint to a table?
drop table if exists t_vip;
// One field is called a single primary key
create table t_vip(
id int primary key, //Column level constraint
name varchar(255)
);
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi');
//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
Can I add a primary key like this and use table level constraints?
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255),
primary key(id) // Table level constraints
);
insert into t_vip(id,name) values(1,'zhangsan');
//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)
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');
//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?
drop table if exists t_vip;
create table t_vip(
id int primary key,
name varchar(255) primary key
);
ERROR 1068 (42000): Multiple primary key defined
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
Other types.
Not recommended: varchar To do the primary key. Primary key values are generally numbers and are generally fixed in length!
In addition to single primary keys and composite primary keys, you can also classify them 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.
stay mysql Among them, there is a mechanism that can help us automatically maintain a primary key value?
drop table if exists t_vip;
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)
);
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
select * from t_vip;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | zhangsan |
| 3 | zhangsan |
| 4 | zhangsan |
| 5 | zhangsan |
| 6 | zhangsan |
| 7 | zhangsan |
| 8 | zhangsan |
+----+----------+
7.6. foreign key constraint (FK) is very important. There are five stars*****
Terms related to foreign key constraints: Foreign key constraint: a constraint( foreign key) 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: the class 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 cno When the field has no constraints, the data may be invalid. A 102 may appear, but the 102 class does not exist. So to ensure cno The values in the field are 100 and 101, which need to be given cno Field to add a foreign key constraint. Then: cno Fields are foreign key fields. cno Each value in the 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. 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 unique Constraints. Test: foreign keys can be NULL Are you? Foreign key values can be NULL.
8. Storage engine (understand content)
8.1 what is a storage engine and what is its use?
Storage engine is a unique term in MySQL, which is not found in other databases (Oracle, but not 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.
8.2. How to add / specify a "storage engine" to a table?
show create table t_student;
You can assign a storage engine to a table when it is created. 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 When creating a table, it can be in the last parenthesis")"To the right of the: ENGINE To specify the storage engine. CHARSET To specify the character encoding of this table. Conclusion: mysql The default storage engine is: InnoDB mysql The default character encoding method 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;
8.3. How to view which storage engines mysql supports?
mysql> select version();
+-----------+ | version() | +-----------+ | 5.5.36 | +-----------+
Command: 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
mysql supports nine storage engines. Currently, 5.5.36 supports eight. Different versions support different situations.
8.4. 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 add unique Indexes are automatically created on the constrained fields. MyISAM Storage engine features: Can be converted to compressed, read-only tables to save space This is the advantage of this storage engine!!!! MyISAM Transaction mechanism is not supported and security is low.
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 in database directory.frm Format file representation – InnoDB Tablespace tablespace Used to store the contents of a table (a tablespace is a logical name. A tablespace stores data+(index.) – Provides a set of log files used to record transactional activities – use COMMIT(Submit),SAVEPOINT and ROLLBACK(RollBACK )Support transaction processing – Provide full ACID compatible – stay MySQL Provide automatic recovery after server crash – Multi version( MVCC)And row level locking – Support the integrity of foreign keys and references, including cascading deletion and update InnoDB The biggest feature is to support transactions: To ensure the security of data. The efficiency is not very high, and it cannot be compressed or converted to read-only, 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.
MEMORY Tables managed by the storage engine have the following characteristics: – Within the database directory, each table is.frm File representation in format. – Table data and indexes are stored in memory. (the purpose is fast, fast query!) – Table level locking mechanism. – Cannot contain TEXT or BLOB Field. MEMORY The storage engine was formerly known as HEAP Engine. MEMORY Engine advantages: 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.
9. Affairs (key points: five stars * * * * *, must be understood and mastered)
9.1. What is a transaction?
A transaction is actually a complete business logic. Is the smallest unit of work. No further division. What is a complete business logic? Hypothetical transfer, from A Account to B Transfer 10000 from account. take A The money in the account minus 10000( update Statement) take B The money in the account plus 10000( 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 items. update The statement requires both success and failure to ensure that the money is correct.
9.2. 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!!!
9.3. 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 to complete it,
Therefore, the existence of transactions is required. If any complex thing can be solved with a DML statement,
Then the transaction has no value.
What is a business? In the final analysis, in essence, a transaction is actually multiple DML Statements succeed or fail at the same time! Transaction: batch DML Statements succeed or fail at the same time!
9.4 how do transactions 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 the execution of the transaction, each DML All operations are 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? All previous DML All operations are undone and the log files of transactional activities are emptied Rolling back a transaction marks the end of the transaction. And it's the end of all failure.
9.5. How to commit a transaction and roll back a transaction?
Commit transaction: commit; sentence
Rollback transaction: rollback; Statement (rollback can always be rolled back to the last commit point!)
The English words corresponding to the transaction are: transaction Test it, in mysql What is the default transaction behavior in? mysql Automatic transaction submission is supported by default. (auto submit) What is auto submit? Each execution DML Statement, submit once! This automatic submission is actually not in line with our development habits, because it is a business Usually more than one is required DML Statements can be completed only when they are executed together, in order to ensure the data The security of must be submitted after successful at the same time, so one cannot be executed Just submit one. How will mysql How about turning off the automatic submission mechanism? Execute this command first: start transaction; Demo 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> use bjpowernode; Database changed mysql> select * from dept_bak; +--------+-------+------+ | DEPTNO | DNAME | LOC | +--------+-------+------+ | 10 | abc | bj | +--------+-------+------+ 1 row in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into dept_bak values(20,'abc Query OK, 1 row affected (0.00 sec) mysql> insert into dept_bak values(20,'abc Query OK, 1 row affected (0.00 sec) mysql> insert into dept_bak values(20,'abc Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.01 sec) mysql> select * from dept_bak; +--------+-------+------+ | DEPTNO | DNAME | LOC | +--------+-------+------+ | 10 | abc | bj | | 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 | bj | | 20 | abc | tj | | 20 | abc | tj | | 20 | abc | tj | +--------+-------+------+ 4 rows in set (0.00 sec)
9.6. Transaction includes four features:?
A: Atomicity atom Transaction is the smallest unit of work. No further division. C: uniformity consistency All transactions require that in the same transaction, all operations must succeed or fail at the same time, To ensure data consistency. I: Isolation isolate A Transactions and B There is some isolation between transactions. classroom A And classroom B There is a wall between them, which is isolation. A When a transaction operates a table, another transaction B What happens when you operate this table??? D: persistence duration A guarantee of the final end of the transaction. Transaction commit is equivalent to saving data not saved to the hard disk!
9.7. Focus on the isolation of transactions!!!
A Classroom and B There is a wall in the middle of the classroom. This wall can be very thick or very 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(The lowest isolation level is "not submitted" What is read uncommitted? affair A Transactions can be read B Uncommitted data. 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<You can't read it until you submit it What is read committed? affair A Only transactions can be read B Data after submission. 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, and the current transaction has not been started yet At the end of the second reading, the data read is 4, and 3 is not equal to 4 It is called non repeatable reading. This isolation level is relatively real data, and the data read every time is absolutely real. oracle The default isolation level of the database is: read committed Repeatable: repeatable read<You can't read it after committing. What you always read is the data when you just started the transaction What is a repeatable read? affair A After opening, no matter how long, every time in the transaction A Data read in Are consistent. Even transaction B The data has been modified and the transaction has been committed A The read data 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. mysql This is the default transaction isolation level in!!!!!!!!!!! serialize/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.
9.8 verify various isolation levels
View isolation level: 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;
insert into t_user values('zhangsan');
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;
DAY04
1. index
1.1 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'; This one above SQL Statement will go name Scan on field, why? Because the query criteria are: name='jack' If name There is no index (directory) added to the field, or there is no name Create an index for the field, MySQL Full scan will be performed and name Each value on the field is compared. The efficiency is relatively low. MySQL In terms of query, there are mainly two 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....Sort, Why sort? Because only after sorting can there be interval search! (narrowing the scanning range is actually scanning a certain interval!) stay mysql The indexes in the database also need to be sorted, and the sorting and TreeSet The data structure is the same. TreeSet(TreeMap)The bottom layer is a self balanced binary tree! stay 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.
1.2. 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, index objects will be automatically added to the primary key, id The field is automatically indexed because id yes PK. In addition mysql If there is any in one field unique If the constraint is, 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 Among them, the index is a single object, and different storage engines exist in different forms MyISAM In the storage engine, the index is stored in a.MYI File. stay InnoDB In the storage engine, indexes are stored in a logical name called tablespace Of course. stay MEMORY In the storage engine, the index is stored in memory. No matter where the index is stored, the index is mysql All of them are in the form of a tree Presence. (self balancing binary tree: B-Tree)
1.3. 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: there are few DML(insert delete update) operations for this field. (because 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 by primary key unique The query efficiency is relatively high.
1.4 how to create an index? How to delete it? What is the syntax?
Create index: mysql> create index emp_ename_index on emp(ename); to emp Tabular ename Add an index to the field, named: emp_ename_index Delete index: mysql> drop index emp_ename_index on emp; take emp On the table emp_ename_index Index object deletion.
1.5. 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 | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ Scan 14 records: it indicates that the index is not used. type=ALL mysql> create index emp_ename_index on emp(ename); 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.6 when does the index fail?
Case 1 of failure: select * from emp where ename like '%T'; ename Even if an index is added to the database, it will not go through the index. Why? The reason is that fuzzy matching“%"It's the beginning! Try to avoid fuzzy queries“%"Start. This is a means of optimization/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 | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ Case 2 of failure: use or It will fail when used or So ask or The condition fields on both sides must have indexes before indexing. If one field has no index, the index on the other field will also be implemented. Therefore, this is why it is not recommended or The reason for this. 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 | +----+-------------+-------+------+-----------------+------+---------+------+------+-------------+ 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. create index emp_job_sal_index on emp(job,sal); mysql> explain select * from emp where job = 'MANAGER'; +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+ | 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 | +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+ 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 | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ Case 4 of failure: stay where The index column participates in the operation, and the index is invalid. mysql> create index emp_sal_index on emp(sal); explain select * from emp where sal = 800; +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ | 1 | SIMPLE | emp | ref | emp_sal_index | emp_sal_index | 9 | const | 1 | Using where | +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ 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 | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ Case 5 of failure: stay where The index column uses the function 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 | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ Failed 6th... Failed 7th...
1.7. Index is an important means of optimizing various databases. Index is the priority factor in optimization.
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: with unique Adds an index to a constrained field. ..... Note: it is not useful to add indexes to fields with weak uniqueness.
2. View
2.1. What is a view?
view: look at the same data from different angles.
2.2. How to create a view object? How to delete a view object?
Table replication: mysql> create table dept2 as select * from dept; dept2 Data in table: mysql> select * from dept2; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ To create a view object: create view dept2_view as select * from dept2; To delete a view object: drop view dept2_view; Note: only DQL Statement can be view Created in the form of. create view view_name as The statement here must be DQL sentence;
2.3 what do you do with views?
We can add, delete, modify and query view objects. Adding, deleting, modifying and querying view objects will lead to The original table has been manipulated! (view features: the operation of the view will affect the original table data.) //View oriented query select * from dept2_view; // View oriented insertion insert into dept2_view(deptno,dname,loc) values(60,'SALES', 'BEIJING'); // Query original table data mysql> select * from dept2; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | | 60 | SALES | BEIJING | +--------+------------+----------+ // View oriented deletion mysql> delete from dept2_view; // Query original table data mysql> select * from dept2; Empty set (0.00 sec) // Create view objects 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 view object 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 | +--------+---------+------------+ // View oriented update update emp_dept_view set sal = 1000 where dname = 'ACCOUNTING'; // The original table data is updated 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 | +-------+--------+-----------+------+------------+---------+---------+--------+
2.4 what is the use of view objects in actual 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, which 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 when modifying
Modify the SQL statement mapped by the view object.
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, so is the view object
Those stored on the hard disk 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)
3. 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
4. Three paradigms of database design
4.1 what is the database design paradigm?
Design basis of database table. Teach you how to design database tables.
4.2. Common database design paradigms?
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.
4.3 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
4.4. The second paradigm
Based on the first paradigm,
It is required that all non primary key fields must completely depend on the primary key and do not generate 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 tables meet the first paradigm? 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 number+(teacher number) 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!!!!!!!!!!!!!!!
4.5 the third paradigm
The third paradigm is based on the second paradigm
It is required that all non primary key dictionaries must directly rely on the primary key and do not generate transfer 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!!!!!!!!!!!!
4.6 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!!!!!!!!!!
4.7. 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 Among them, 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 for developers, sql The difficulty of writing statements will also be reduced. In the interview, put this sentence: he won't think you are a junior programmer!