Mysql learning notes

Posted by gmccague on Mon, 07 Mar 2022 19:37:05 +0100

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;
deptnoavg(sal)
202175.000000
301566.666667
102916.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;
enamesalgrade
SMITH800.001
ALLEN1600.003
WARD1250.002
JONES2975.004
MARTIN1250.002
BLAKE2850.004
CLARK2450.004
SCOTT3000.004
KING5000.005
TURNER1500.003
ADAMS1100.001
JAMES950.001
FORD3000.004
MILLER1300.002

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

Topics: SQL