SQL statement
DQL (data query language): query statements. All select statements are DQL;
DML (data operation language): insert delete update to add, delete and modify the data in the table;
DDL (data definition language): create drop alter, adding, deleting and modifying the table structure;
TCL (transaction control language): commit the transaction and rollback the transaction.
DCL (data control language): grant authorization, revoke authority
Login database:
1. dos go to the bin directory of the database
mysql -uroot -p (password)
Table column / column row (records in the table)
Each field has: field name, field data type, field constraint and field length
Statement execution order:
select 5 .. from 1 .. (join on) 2 where .. group by 3 .. having 4 .. order by 6 .. limit 7 ..
Common commands:
2. View database
show databases;(Mysql (command)
3. Create database
create database power；(Mysql Command, database name (self naming)
4. Specify the current default database
use power;
4. 1. Initialization table
source D:\Mysql\power.sql
Overwrite the self built sql library file into the power library
5. View which tables are in the current database
show tables;
6. View tables in other databases (exam Library)
show tables from exam;
7. View the creation statement of the table (emp table)
show create table emp;
8. Other instructions
desc exam Display table structure( exam Library) select database(); View which database is currently in use select version(); see mysql Version number of \c End a statement exit quit \q (ctrl+c) sign out mysql
1. (any sql statement is assumed to be ";) (end)
2. (sql statements are not case sensitive)
3. (standard sql statements require strings to be enclosed in single quotes' China ')
1, Query statement DQL (select)
select field name 1, field name 2, field name 3... from table name;
select ename from emp;((find) select sal*12 from emp; (Fields can participate in mathematical operations) select sal*12 as yearsal from emp; (Field rename) select sal*12 as 'Annual salary' from emp; (Single quotation mark is required for Chinese) select * from emp；(Query all fields) (inefficient, not recommended)
1. Conditional query (where):
select field, field...... from table name where condition;
(execute in the order of select, from first)
Query employee name with salary = 5000:
select ename from emp where sal=5000;
Query smith's salary:
select sal from emp where ename='smith';
Find employees with salary > = 3000:
select ename,sal from emp where sal>=3000;
Find out the salary= 3000 employees:
select ename,sal from emp where sal<>3000; select ename,sal from emp where sal!=3000;
Identify employees with salaries between 1100 and 3000, including 1100 and 3000:
select ename,sal from emp where sal>=1100 and sal<=3000; select ename,sal from emp where sal between 1100 and 3000;
between and
(between... And... Is a closed interval. between and must be smaller on the left and larger on the right)
(between and can also be used in character strings in addition to numbers. Character strings are closed on the left and open on the right)
Find employees whose names are in a and c (excluding c)
select ename from emp where ename between 'a'and 'c';
is null/ is not null
Find out who has no allowance: NULL in the database is not a value, which means empty; (is null /is not null)
select ename，comm from emp where comm is null; select ename，comm from emp where comm is null or comm=0;
and/or
(and, or, or, and takes precedence over or)
(operator priority is uncertain, plus (parentheses))
Find out the employees whose jobs are manager and salesman
select ename,job from emp where job='manager' or job='salesman';
Find employees whose salary is greater than 1000 and department number is 20 or 30:
select ename,sal,deptno from emp where sal>1000 and (deptno=20 or deptno=30);
in equals or
In is equivalent to or (or) (not in) is not in enumeration
The value after in () is not an interval specific value
Identify employees whose jobs are manager or salesman
select ename,job from emp where job='salesman' or job='manager';
select ename,job from emp where job in('salesman','manager');
select ename,job from emp where job not in('salesman','manager');
2. Fuzzy query (like)
(in fuzzy query, two special symbols% represent any number of characters and represents one character)
Find a with o in the name:
select ename from emp where ename like '%o%';
Find the second letter in the name is A
select ename from emp where ename like '_a%';
Find the underlined (\ escape character) in the name
select ename from emp where ename like '%\_%'；
Find out that the last letter in the name is t
select ename from emp where ename like '%t';
order by
(default ascending order; asc ascending order, desc descending order)
select ename,sal from emp order by sal; select ename,sal from emp order by sal asc; select ename,sal from emp order by sal desc;
The salary is arranged in descending order. If the salary is the same, the salary is arranged in ascending order according to the name:
(the more front fields play a leading role. Only when the front fields cannot be sorted, the latter fields will be enabled)
select ename,sal from emp order by sal desc,ename; select ename,sal from emp order by sal desc,ename asc;
Sort by sixth column (default ascending)
select ename,sal from emp order by 6;
Find out the employees whose jobs are salesman and arrange them in descending order of salary:
select ename ,job,sal from emp where job='salesman' order by sal desc;
(1. Execute from first, 2. Execute where, 3. Execute select, 4. Execute order by last)
3. Function
Grouping function (multiline processing function)
Five grouping functions, count, max, min, sum and avg;
All grouping functions operate on a certain set of data
Features: input multiple lines and output one line
Grouping function automatically ignores null
Grouping functions cannot be used directly in the where clause, because grouping functions are executed after the execution of group by
Count count
Total number of people:
select count(*) from emp;
select count(ename) from emp;
Count the total number of records:
Count the total number of non null elements in the comm field:
select cout(comm) from emp;
sum
Total wages:
select sum(sal) from emp;
avg average
Average salary:
select avg(sal) from emp;
max
Maximum wage:
select max(sal) from emp;
min
Minimum wage:
select min(sal) from emp;
Grouping function combination
select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
Single line processing function
Input one line, output one line
It is specified in the database that as long as there is null (+ - * /) involved, the operation result must be null
Calculate the annual salary of each employee:
select ename,(sal+comm)*12 as yearsal from emp; select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;
ifnull() null handler
Syntax:
ifnull (data that may be null is treated as what)
select ename,comm,ifnull(comm,0) from emp;
4. Group by
group by groups according to a field or some fields
having filters (constrains) the grouped data again (group by is required)
Grouping functions are generally used in conjunction with group by, which is why they are called grouping functions
Any grouping function is executed after the group by statement is executed
When there is no group by in the sql statement, the data of the whole table will form a group by itself
When there is group by in the statement, select can only be followed by grouping functions and fields participating in grouping
Statement execution order
select 5
..
from 1
..
where 2
..
group by 3
..
having 4
..
order by 6
..
Find out the highest salary for each job
Find out the highest salary for each job
select max(sal) from emp group by job;
Find employees whose salary is higher than the average salary (statement nesting)
select ename,sal from emp where sal>(select avg(sal) from emp);
Error: when there is group by in the statement, select can only be followed by grouping functions and fields participating in grouping
select ename,job,max(sal) from emp group by job;(X)
Find the average salary per job
select job,avg(sal) from emp group by job;
Joint grouping of multiple fields
Find out the highest salary for different positions in each department
select deptno,job,max(sal) from emp group by deptno,job;
Find out the maximum salary of each department and display the data with salary greater than 2900
select deptno,max(sal) from emp group by deptno having max(sal)>2900;//Low efficiency select deptno,max(sal) from emp where sal>2900 group by deptno;//efficient
Find out the data that the average salary of each department is greater than 2000
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
5. Remove duplicate records (distinct)
The distinct statement does not delete the changed data, but the repeated statements are not displayed
The distinct keyword can only appear at the top of all fields
Find out which positions:
select distinct job from emp;
Count the number of Posts:
select count(distinct job) from emp;
6. Connection query
Cartesian product phenomenon (cross connection)
If the two tables are connected without any restrictions, the number of final query results is the product of the number of records in the two tables;
select ename,dname from emp,dept;
Alias table:
Advantages: high execution efficiency and good readability.
select e.ename,d.dname from emp e,dept d;
Avoiding Cartesian product will not reduce the number of records matching, but will display valid records
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;//sql92 syntax
Inner join
Two tables are connected. Internal connection is used. If the records on the two tables can be queried, the tables on both sides have corresponding data at the same time, that is, if there is missing data on either side, it will not be displayed
The two tables have no distinction between the main and the auxiliary, and are equal;
Equivalent connection
The condition is equal quantity relationship
Query the Department name of each employee, and the employee name and department name are required to be displayed
SQL92: (too old, no need)
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;//Equivalent connection
SQL99: (commonly used) (inner can be omitted, with inner purpose, good readability)
select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;
SQL99 syntax:
····· A inner join B on Connection conditions where ···
The syntax structure of SQL99 is clearer, and the connection condition of the table is separated from the subsequent where condition
Non equivalent connection
Features: the relationship in the connection condition is unequal
Find out the salary grade of each employee and ask to display the employee name
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
Self connection
Features: a table can be regarded as two tables, connecting itself
Find out the superior leader of each employee and display the employee name and corresponding leader name.
select a.ename as 'staff',b.ename as 'leader' from emp a inner join emp b on a.mgr=b.empno;
Outer join
Features: unconditional query of main table data
Two tables are connected by external connection. One table is the main table and the other is the sub table. It mainly queries the data in the main table and the sub table. When the data in the sub table does not match the main table, the sub table automatically simulates null to match it.
Left outer connection (left connection)
The table on the left is the main table
The left connection has the writing method of the right connection, and the right connection also has the writing method of the corresponding left connection
left outer join
(outer) external connection can be omitted
Query employees and their superiors
select a.ename 'staff',b.ename 'leader' from emp a left join emp b on a.mgr=b.empno;
Right outer connection (right connection)
The table on the right is the main table
select a.ename 'staff',b.ename 'leader' from emp b right join emp a on a.mgr=b.empno;
Query which department has no employees
select d.* from emp e right join dept d on e.deptno=d.deptno where e.empno is null;
Full connection
There are both left and right connections. Check both tables
Check the corresponding items in the two tables and those that cannot
Connection query of more than three tables
1. Find out the Department name and salary grade of each employee (internal connection)
select e.ename,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;
2. Find out the Department name, salary grade and superior leader (external connection) of each employee
select e.ename 'staff',d.dname 'department',s.grade 'pay grade ',e1.ename 'leader' 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 e1 on e.mgr=e1.empno;
7. Subquery
Select statements are nested in select statements, and the nested select statements are sub queries
select ..(select) from ..(select) where ..(select)
Use subqueries in the where clause
Find out the information of employees with higher than average salary
select * from emp where sal>(select avg(sal) from emp);
Use subqueries in the from clause
Find out the salary scale of the average salary of each department
1. Find the average salary of each department (group by department number and find the average value of sal)
select deptno,avg(sal) from emp group by deptno;
| deptno | avg(sal) |
|---|---|
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
2. Take the above results as a temporary table t, and connect the table t with the salgrade table,
Conditions:
t.avg(sal) between s.losal and s.hisal
select t.*,s.grade from t join salgrade s on t.avgsal between s.losal and s.hisal;
Summary: the previous condition is used as the sub query of from
select t.*,s.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal;
Find out the average salary grade of each department
1. Find out the salary grade of each employee
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
| ename | sal | grade |
|---|---|---|
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
2. Based on the above table, group according to deptno and calculate the average value of grade
select e.deptno,avg(s.grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno order by deptno;
Embed subquery after select
Find out the name of the Department where each employee belongs, and it is required to display the employee name and department name
(Commonly used) select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno; (select Subquery) select e.ename, (select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;
8. Union (add query results)
Identify employees whose jobs are salesman and manager
1,or
select ename, job from emp where job='salesman' or job='manager';
2,in
select
ename,job
from
emp
where
job in('salesman','manager');
3,union
select ename,job from emp where job='manager' union select ename,job from emp where job='salesman';
The data commonly used in two irrelevant tables are spliced together for display
(the data queried in the two tables must have the same number of columns)
select ename from emp union select dname from dept;
9. Limit (paging query)
1. limit is unique to mysql, but not in other databases. Not common (Oracle has the same mechanism called rownum)
2. Function: limit gets part of the data in the result set
3. Syntax:
limit startIndex length
startIndex represents the starting position, starting from 0, and 0 represents the first data
length indicates how many quantities are taken
Take out the top five employees (take the top five in descending order)
select ename,sal from emp order by sal desc limit 0,5;
Write only one number after limit, and start from 0 by default
select ename,sal from emp order by sal desc limit 5;
Statement execution order:
select 5 .. from 1 .. where (join on) 2 .. group by 3 .. having 4 .. order by 6 .. limit 7 ..
Find out the employees whose wages rank fourth to ninth
The third subscript is 3, 4-9, a total of 6 employees
select ename,sal from emp order by sal desc limit 3,6;
General standard paging sql
Three records are displayed on each page:
Page 1: 0, 3
Page 2: 3, 3
Page 3: 6, 3
Page 4: 9, 3
pageSize records are displayed on each page
Page: (pageNo-1) * pageSize,pageSize
pageNo indicates the page number
2, Definition Language DDL (addition, deletion and modification of table structure)
Create table
Syntax format of table building statement
create table Table name( Field name 1 data type, Field name 2 data type, Field name 3 data type, ...... ) create table t_class( cno int, cname varchar(255), primary key(cno) );
Common field data types in Myqsl:
int Integer type bigint Long integer float float char Fixed length string char(6),effective An error is reported when the character exceeds the specified length varchar Variable length string date Date type BLOB Binary large object (storing pictures, videos and other streaming media information) Binary Large OBject CLOB Character large object (storing large text, such as 4 G (string) Character Large OBject
char and varchar
The length of field data does not change. char (birthday, gender) is used for fixed length, and varchar (introduction, name) is used for uncertain length
Create student table
Student No.: bigint
Name: varchar
Gender: char
Class No.: varchar
Birthday: char
create table t_student( no bigint, name varchar(255), sex char(1), classno varchar(255), birth char(10) );
Delete table (drop)
drop table Table name//Error reporting when the table does not exist drop table if exists Table name;
Modify table (alter)
3, Operation language DML (data addition, deletion and modification)
insert data
If the insert statement is executed successfully, there must be more data in the table
Syntax format:
insert into Table name (field 1, field 2, field 3,...) values(Value 1, value 2, value 3,...)
Requirement: the number of fields and values should be the same, and the data type should be the same
insert into t_student(no,name,sex,classno,birth) values(001,'Li Jie','male','Soft worker 1 shift','1997-01-01');
When the contents of the inserted field are incomplete, the contents not inserted are null
drop table if exists t_student;//Delete when table exists
Default sets the default value
create table t_student( no bigint, name varchar(255), sex char(1) default 'male' classno varchar(255), birth char(10) );
When the parameter field is not connected after the table, the value of values must correspond to it
insert into t_student values(002,'jack','male','Soft work shift 1','1998-03-06');
Insert multiple rows of data at one time
insert into t_student values (003,'bruce','male','First shift soft worker','1998-06-01'), (004,'alex','male','Second shift of soft work','1999-06-01');
Replication of tables
Syntax:
1. Create query results as tables
create table Table name as select sentence;
create table emp2 as select ename,empno from emp;
2. Insert the query results into a table (the table needs to be created before)
insert into Table name select sentence;
insert into dept1 select * from dept;
Modify data (update)
Syntax format:
update Table name set Field name 1=Value 1,Field name 2=Value 2 where condition;
Note: (when there is no where condition, the data of the whole table will be updated)
Change the loc of department 10 to SHANGHAI. The Department name is modified to RENSHIBU
update dept set dname='RENSHIBU',loc='shanghai' where deptno=10;
Delete data
Physical space is not released, and deleting large tables is inefficient and takes a long time
Syntax format:
delete from Table name where Conditions:
Note: (delete all tables without conditions)
Delete data of department No. 10
delete from dept1 where deptno =10;
Delete all records in the table
delete from Table name;
Delete truncate
Delete the data in the table, and the table is still there. The table is truncated, cannot be rolled back, and is permanently lost
truncate table Table name;
4, Constraint
When creating a table, you can add corresponding constraints to the fields of the table to ensure the legitimacy, validity and integrity of the data in the table.
Common constraints:
Non NULL constraint: not null constraint field cannot be null
Unique constraint: unique constraint fields cannot be duplicate
Primary key constraint: the fields of the primary key (PK) constraint cannot be NULL or duplicate
Foreign key constraint: foreign key (FK)
Check constraints: check, Mysql does not, Oracle does
Non NULL constraint not null
Constraint field cannot be NULL
drop table if exists t_user; create table t_user( id int, username varchar(255) not null, password varchar(255) ); insert into t_user(id,username,password) values(1,'lee','456');
Uniqueness constraint
Constraint fields cannot be duplicate, but can be null
Column level constraint
drop table if exists t_user; create table t_user( id int unique, username varchar(255) unique);//Column level constraint insert into t_user values(1,'lee'),(2,'Liu');
Table level constraint
Unique (field 1, field 2) joint constraint. An error is reported only when both fields are repeated
create table t_user( id int, username varchar(255) unique(id username)//Table level constraint );
Primary key constraint
Constrained fields cannot be NULL or duplicate
create table t_user( id int primary key, username varchar(255) );
Primary key field: after adding a primary key to a field, it is called a primary key field
Primary key value: each value in this field is a primary key value
There are three paradigms in primary key design. The first paradigm requires that any table should have a primary key
Primary key function: the primary key value is the unique identification of this row of records in this table
Primary key classification:
According to the number of primary key fields:
Single primary key
primary key(id)
Composite primary key (multiple fields are combined to add a primary key constraint) (composite primary key is not recommended and violates the three normal forms)
primary key(id,username)
Divided according to the nature of primary key
Natural primary key: the primary key value should preferably be a natural number unrelated to business (recommended)
Business primary key: the primary key value is linked to the business of the system (not recommended)
There can only be one primary key constraint for a table
Auto_increment of primary key
drop table if exists t_user; create table t_user( id int primary key auto_increment,//The id field automatically maintains a self increasing number, starting from 1 and increasing by 1 username varchar(255) );
Foreign key constraint
foreign key(Field name) references Another table name(Fields in table name)
t_ The classno field in student refers to t_ cno field in class table, t_ The student table is called a sub table, t_ The class table is called the parent table
Sequence requirements:
When deleting data, delete the child table first, and then delete the parent table
When deleting a table, delete the child table first and then the parent table
When adding data, add the parent table first, and then add the child table
When creating a table, first create the parent table and then create the child table
drop table if exists t_student; drop table if exists t_class; create table t_class( cno int, cname varchar(255), primary key(cno) ); create table t_student( sno int, sname varchar(255), classno int, primary key(sno), foreign key(classno) references t_class(cno) ); insert into t_class values(101,'soft1ban'); insert into t_class values(102,'soft2ban'); insert into t_student values(1,'zhang',101); insert into t_student values(2,'liu',102); insert into t_student values(3,'lee',102); //Error, the child table cannot add records for the parent table insert into t_student values(4,'huang',103);
1. Foreign key can be null
2. When a foreign key field references a field of another table, the referenced field is not necessarily a primary key, but must be unique
5, Storage engine
Complete table creation statement:
create table `t_x`( `id` int(11) default null ) engine=innodb default charset=utf8;
In Mysql, but the identifier can be enclosed by a floating sign (`). It's better not to use it. It's not universal
When creating a table, you can specify either a storage engine or a character set
The default storage engine used by mysql is InnoDB
The default character set is UTF8
Storage engine: how tables are stored
View the storage engines currently supported by mysql
show engines \g
Common storage engines
1. MyISAM storage engine
MylSAM storage engine is the most commonly used engine for MySQL, but this engine is not the default and does not support transactions
It uses three files to represent each table
Format file storage table format (XXX.frm)
Data file storage table data (XXX.MYD)
Index on index file storage table (XXX.MYI)
Advantages: it can be compressed, save storage space, and can be converted into a read-only table to improve the retrieval efficiency
2. InnoDB storage engine
Advantages: support transactions, row level locks and foreign keys. High safety
The table structure is stored in the (xxx.frm) file
The data is stored in the tablespace table space and cannot be compressed or converted to read-only
Cascade and delete (parent-child support)
3. MEMORY storage engine
Disadvantages: things are not supported, all data and indexes are stored in memory, and data is easy to be lost
Advantages: the fastest query speed, suitable for query
6, Business
A transaction is a complete business logic unit and cannot be further divided
Multiple statements constitute a transaction. All the transaction statements must be executed successfully or fail at the same time
Only DML (insert delete update) statements are related to transactions and operate on data
When a transaction exists, in order to ensure data integrity and security
Usually, a transaction needs to be completed jointly by multiple DML statements
Open transaction mechanism (start) operation in cache
commit (save cache to file) or rollback (withdraw) the save operation to file to release the cache
Four characteristics of transaction
Atomicity: transactions are the smallest unit of work and cannot be further divided
Consistency: transactions must ensure that polymorphic DML statements succeed or fail at the same time
Isolation: there is isolation between transaction A and transaction B
Persistence: persistence means that the final data must be persisted to the hard disk file before the transaction can be concluded successfully
Isolation level
Set isolation level:
set global transaction isolation level level
(read uncommitted)
(read committed)
(repeatable read)
(serializable)
Level 1: read uncommitted
The other party's transaction has not been submitted yet. We can extract the uncommitted data of the other party in the current transaction
dirty read in uncommitted reads: indicates dirty data has been read
Level 2: read committed
We can only read the data after the opposite transaction is committed
Read committed problem: cannot read repeatedly
The problem of dirty reading is solved
Level 3: repeatable read
This isolation level solves the problem of non repeatable reads
Problem: the data read is an illusion (backup data or real data has been modified)
Level 4: serialized read / serialized read
Process one transaction before processing another
Solved all the problems
Low efficiency and need to queue things
The default isolation level of oracle database is read committed (second)
The default isolation level of mysql database is: repeatable read (the third)
Demonstrate things
mysql transactions are automatically committed by default (execute any DML statement to commit once)
Use transaction to start and close transactions automatically
1. Preparation table
drop table if exists t_user; create table t_user( id int primary key auto_increment, username varchar(255) );
2. Insert data
insert into t_user(username) values('zhang');
select * from t_user;
3. Rollback
rollback; select * from t_user;
4. Rollback failed, data has been submitted automatically
5. Start a transaction with start transaction
start transaction;
6. Insert data
insert into t_user(username) values('liu');
select * from t_user;
7. Rollback
rollback; select * from t_user;
8. Rollback succeeded and the inserted data was revoked
9. After the transaction is started, commit manually
insert into t_user(username) values('huang');
select * from t_user;
commit;
10. Submitted manually, rollback failed
rollback; select * from t_user;
7, Index
The index is equivalent to the directory of a book. After using the directory, you can quickly find the corresponding resources
In terms of data volume, there are two retrieval methods for query tables:
The first method: full table scanning
The second way: search according to the index (very efficient) (principle: narrow the scanning range)
The index cannot be added at will. The index is also an object in the database, which needs continuous maintenance of the database and has maintenance cost. If the data in the table is often modified, it is not suitable to add an index, because the data is modified, and the index needs to be reordered and maintained.
Suitable scenario for index creation
1. Huge amount of data
2. There are few DML operations on this field (the field is modified, and the index also needs to be maintained)
3. This field often appears in the where clause
View the execution of a statement
explain sentence
Create index
create index Index name on Table name(Field name);
Delete index
drop index Index name on Table name;
The underlying data structure of the index is B+Tree
Index implementation principle: narrow the scanning range through B Tree, sort and partition the underlying index, and the index will carry the "physical address" of the data in the table. Finally, after retrieving the data through the index, obtain the associated physical address, and locate the data in the table through the physical address. The efficiency is the highest
select ename from emp where ename='SMITH'; Convert to by index: select ename from emp where Physical address=0x3;
Classification of indexes
Single index: adds an index to a single field
Composite index: add one index to the union of multiple fields
Primary key index: the index will be automatically added to the primary key
Unique index: the index will be automatically added to the field with unique constraint
Index failure
select ename from emp where ename like '%a%';
Fuzzy query. When the first wildcard uses% the index fails
8, View
View: view data from different angles
Create view
When creating a view, you can only use DQL statements to create it as a view object
create view View name as condition; create view myview as select empno,ename from emp;
Delete view
After the view is created, you can CRUD the view with DDL (add query, modify delete)
drop view View name; drop view myview;
How to delete, modify and query the view will affect the original table data
create table emp_bak as select * from emp; Create view create view myview1 as select empno,ename,sal from emp_bak; Modify data in view update myview1 set ename='xuan',sal=100 where empno=7369; Delete original table data through view delete from myview1 where empno='7369';
Function of view
The view can hide the implementation details of the table. For systems with high confidentiality, the database only provides the corresponding view, and programmers generally only CRUD the view object.
9, DBA command
Data export
Execute in the dos command window of window:
mysqldump Database name >position -uroot -p password mysqldump power >D: newpower.sql -uroot -phang199881 Exports the specified table in the specified database mysqldump power emp>D: newpower.sql -uroot -phang199881
Data import
create database Database name; use Database name; source File path
Three paradigms of database design
Design paradigm: the basis for designing tables. Tables designed according to the three paradigms will not have data redundancy.
First paradigm
Any table should have a primary key, and the atomicity of each field cannot be further divided
Second paradigm
Based on the first paradigm, all non primary key fields completely depend on the primary key (one-to-one) and cannot produce partial dependence (many to many)
Many to many
Three tables, two data tables, and one relational table contain two foreign keys
Multiple teachers correspond to multiple students t_student Student list sno(pk) sname ------------------- 1 Zhang San 2 Li Si 3 Wang Wu t_teacher Teacher table tno(pk) tname ------------------- 1 Miss Zhang 2 Miss Li 3 Miss Wang t_student_teacher_relation Student lecturer relationship table id(pk) sno(fk) tno(fk) ------------------------------- 1 1 3 2 1 1 3 2 2 4 2 3 5 3 1 6 3 3
Third paradigm
Based on the second paradigm, all non primary key fields directly depend on the primary key field and cannot produce transitive dependency
One to many
Two tables, many tables plus foreign keys
(One class corresponds to multiple students) t_class class cno(pk) cname ----------------- 1 Class one 2 Class two 3 Class three t_student student sno(pk) sname classno(fk) ----------------------------------- 101 Zhang 1 102 Lee 2 103 yellow 1 104 shut 3
Tip: the actual development of president Zhang focuses on meeting the needs of customers. Sometimes he will exchange redundancy for execution speed
One to one scheme design
1. Primary key sharing
t_user_login User login form id(pk) username password ---------------------------- 1 zs 123 2 ls 456 t_user_detail User details table id(pk+fk) realname tel --------------------------- 1 Zhang San 1231514 2 Li Si 1246546
2. Foreign key unique
t_user_login User login form id(pk) username password ---------------------------- 1 zs 123 2 ls 456 t_user_detail User details table id(pk) realname tel userid(fk+unique) ------------------------------------------------ 1 Zhang San 1231514 1 2 Li Si 1246546 2
10, Lock
Exercises
1. Name of the person who gets the highest salary in each department
1. Get the highest salary in each department
select deptno,max(sal) as maxsal from emp group by deptno;
2. Connect the above results as temporary tables T, t and emp e, provided that t.deptno=e.deptno and t.maxsal=e.sal
select e.ename,t.* from (select deptno,max(sal) as maxsal from emp group by deptno) t join emp e on e.deptno=t.deptno and e.sal=t.maxsal;
Note: primary keys and fields with unique constraints are automatically indexed. Query based on the primary key is efficient. Try to retrieve based on the primary key.
2. Who is paid above the average salary of the Department
1. Find out the average salary of each department
select deptno,avg(sal) as avgsal from emp group by deptno;
2. The above query results are made into table t, which is connected with table emp
Conditions: e.deptno = t.deptno and e.sal > t.avgsal
select t.*,e.ename,e.sal from emp e join (select deptno,avg(sal) as avgsal from emp group by deptno) t on e.deptno=t.deptno and e.sal>t.avgsal;
3. Get the average salary grade of everyone in each department
1. Find out everyone's salary scale
select e.deptno,e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal
2. Based on the above results, group according to deptno and calculate the average value of grade
select e.deptno,avg(s.grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by deptno;
4. Get the highest salary among employees
1. sal descending order, limit 1
select ename, sal from emp order by sal desc limit 1;
2. Using the max function
select max(sal) from emp;
3. Self connection
3. 1. Find out the data whose salary is less than the maximum value (de duplication)
select distinct a.sal from emp a join emp b on a.sal<b.sal;
3. 2. In addition to the above data, the data in the original table is the maximum salary
select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal<b.sal);
5. Department number with the highest average salary
1. First in descending order
1. 1. Find the average salary of each department
select deptno,avg(sal) as avgsal from emp group by deptno;
1. 2. Select the first one in descending order
select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
2,max
2,1
select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t;
2,2,having
select deptno,avg(sal) as avgsal from emp group by deptno having avgsal=(select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);
6. Name of department with the highest average salary
select d.dname,avg(e.sal) as avgsal from emp e join dept d on d.deptno=e.deptno group by d.dname order by avgsal desc limit 1;
7. Name of the Department with the lowest average salary level
select avg