SQL statement Basics

data type
DQL language
DML language
DDL language
DCL language
TCL language
Non foreign key constraint
Foreign key constraint
Foreign key policy
DDL and DML add a little
Multi table query syntax
Subquery
affair
Transaction concurrency problem
Transaction isolation level
View + stored procedure
JDBC+sql injection + other

Installation: uninstall all mysql and mariadb versions before installation

wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

Use the above command to download the installation directly Yum Repository，About 25 KB Then you can directly yum Installed.
[root@localhost ~]# yum -y install mysql57-community-release-el7-10.noarch.rpm
 Then start the installation MySQL The server.
[root@localhost ~]# yum -y install mysql-community-server
 This step may take some time, and the previous will be overwritten after the installation is completed mariadb. 
thus MySQL The installation is complete, and then MySQL Some settings for.
Start first MySQL
[root@localhost ~]# systemctl start  mysqld.service
 see MySQL Operation status, as shown in the figure
[root@localhost ~]# systemctl status mysqld.service
 here MySQL It has started normal operation, but to enter MySQL We have to find out first root The user's password can be found in the log file through the following command:
[root@localhost ~]# grep "password" /var/log/mysqld.log
 Enter the database with the following command:
[root@localhost ~]# mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'QAZwsx123.';

use mysql
select host from user where user='root';
update user set host = '%' where user ='root';
flush privileges; #Effective immediately

Can't use navicat To connect to the database, the firewall may not be closed:
systemctl stop firewalld.service            #Stop firewall
systemctl disable firewalld.service        #Disable firewall startup

Horizontal: record. Portrait: Fields

Summary of basic statements: (the full text is based on sql99 syntax)

sql statement keywords, table names (only valid in windows system, case sensitive in linux system), field names, etc. are not case sensitive. The default content is also case insensitive. When using the where clause, pay attention to multiple records with different case that may affect the same value at the same time, such as where sname = "Bob" and where sname = "Bob"

Insert: INSERT INTO [Table name] VALUES('value1','value2')  
     INSERT INTO [Table name]([Field 1],[Field 2]) VALUES('value1','value2')  
     INSERT INTO [Table name]([Field 1],[Field 2]) SELECT [Field 1],[Field 2] FROM [Table name]
Copy: SELECT * INTO Target table name FROM Source table name WHERE 1=2 ([Copy table structure:Give Way WHERE The condition is not tenable)
     SELECT * INTO Target table name FROM Source table name ([copy table structure and data] requires that the target table does not exist, because it will be created automatically during insertion)
Delete table: DROP TABLE [Table name]
Delete data: DELETE FROM [Table name] WHERE Range  
Empty: TRUNCATE TABLE [Table name]
to update: UPDATE [Table name] SET [field]='value1' WHERE Range  
Replace: UPDATE [Table name] SET [field] = REPLACE([field], 'Content before replacement', 'Content after replacement');
choice: SELECT * FROM [Table name] WHERE Range  
Find: SELECT * FROM [Table name] WHERE [field] LIKE '%value1%'
Sort: SELECT * FROM [Table name] ORDER BY [Field 1] ASC,[Field 2] DESC 
total: SELECT COUNT(*) AS TotalCount FROM [Table name]  
Summation: SELECT SUM([field]) AS SumValue FROM [Table name]  
average: SELECT AVG([field]) AS AvgValue FROM [Table name]  
maximum: SELECT MAX([field]) AS MaxValue FROM [Table name]  
minimum: SELECT MIN([field]) AS MinValue FROM [Table name]

--Add primary key
ALTER TABLE Table name ADD CONSTRAINT Primary key name PRIMARY KEY(field);

--Modify field name (be careful: Changing any part of the object name can break scripts and stored procedures)
EXEC SP_RENAME 'Table name.Field name','new field name'

--Modify field type:
ALTER TABLE Table name ALTER COLUMN Field name INT NOT NULL

--Add field
ALTER TABLE Table name ADD Field name INT NOT NULL

--Delete field
ALTER TABLE Table name DROP COLUMN Field name;

--External connection   
A,left join:    
Left outer join (left join): the result set includes several matching rows of the join table and all rows of the left join table.   
B: right join:   
Right outer connection(Right connection): The result set includes both matching join rows of the join table and all rows of the right join table.   
C: join:    
All outer join: includes not only the matching rows of the symbolic join table, but also all records in the two join tables.

data type

General shaping:
INT: 4 bytes
BIGINT: 8 bytes
mysql supports the selection of the display width of the integer value specified in the brackets behind the keyword of this type. The display width does not limit the actual range of values saved in the column, nor does it limit the display of values exceeding the specified width of the column. For example, int (4) means preferably 4 bytes.

General decimal:
DOUBLE: 8 bytes
Note that different from the integer, the double type will not automatically expand the field width. For example, score double (4,1) means that the total length is 4 and the decimal part is 1, and will not be automatically expanded

String type:
char (m): a fixed length string with a maximum allowable length of M, occupying a space of M
varchar (m): variable length string with length of M. the occupied space is allocated according to the actual situation
blob: binary long text, which can store audio, video, etc
TEXT: long TEXT data

Date and time type:
DATE: YYYY-MM-DD
DATETIME: YYYY-MM-DD, HH:MM:SS
TIMESTAMP: TIMESTAMP, YYYY-MM-DD, HH:MM:SS. Unlike DATETIME, the TIMESTAMP range is 1970-2038. If it is not assigned or given a null value, mysql will automatically set the field value to the current system date and time.

DQL language

SELECT clause, from clause and where clause are combined to query one or more data

Create employee table, department table, salary grade table and bonus table

create table DEPT(
DEPTNO int(2) not null,
DNAME VARCHAR(14) ,
LOC VARCHAR(13)
)
alter table DEPT add constraint PK_DEPT primary key (DEPTNO);

create table EMP(
EMPNO int(4) primary key,
ENAME VARCHAR(10) ,
JOB VARCHAR(9),
MGR int(4),
HIREDATE DATE,
SAL double(7,2),
COMM double(7,2),
DEPTNO int(2)
);
alter table EMP add constraint FK_DEPTNO foreign key (DEPTNO) references DEPT (DEPTNO)
-- alter TABLE EMP drop foreign key FK_DEPTNO

create table SALGRADE(
GRADE int primary key,
LOSAL double(7,2),
HISAL double(7,2)
);

create table BONUS(
ENAME VARCHAR(10),
JOB VARCHAR(9),
SAL DOUBLE(7,2),
COMM DOUBLE(7,2)
)

insert into DEPT (DEPTNO, DNAME, LOC) values (10, 'ACCOUNTING', 'NEW YORK') ;
insert into DEPT (DEPTNO, DNAME, LOC) values (20, 'RESEARCH', 'DALLAS');
insert into DEPT (DEPTNO, DNAME, LOC) values (30, 'SALES', 'CHICAGO');
insert into DEPT (DEPTNO, DNAME, LOC) values (40, 'OPERATIONS', 'BOSTON');

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into EMP (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20); 
insert into EMP (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28 ',1250,1400, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01',2850, null, 30);
insert into EMP ( EMPNO,ENAME, JOB, MGR,HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into EMP ( EMPNO,ENAME, JOB, MGR,HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839,'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into EMP ( EMPNO,ENAME, JOB, MGR,HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0,30);
insert into EMP ( EMPNO,ENAME, JOB, MGR,HIREDATE, SAL, COMM, DEPTNO)
values (7875, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20) ;
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into EMP (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
insert into EMP ( EMPNO,ENAME, JOB, MGR,HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

insert into SALGRADE (GRADE, LOSAL, HISAL)
values (1, 700, 1200) ;
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (2, 1201, 1400);
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (3, 1401, 2000);
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (4, 2001, 3000);
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (5, 3001, 9999);

Basics

-- Partial row
select empno, ename,sal from EMP;
-- Show partial rows
select * from EMP  where sal > 2000
select empno,ename,job,mgr from EMP where sal > 2000

-- Alias
select empno Employee number, ename full name, sal wages from EMP
-- as = alias
select empno as 'Employee number', ename as "full name", sal wages from EMP

-- Arithmetic operator 
select empno, ename, sal, sal+1000, deptno from EMP where sal < 2500;
select empno, ename, sal, comm, sal+comm from EMP; --If sal and comm If one of them is empty, the result is empty. How to solve it?

-- duplicate removal
select job from EMP
select distinct job from EMP
select job, deptno from EMP
select distinct job, deptno from EMP

-- sort
select * from EMP order by sal -- Default ascending order
select * from EMP order by sal asc
select * from EMP order by sal desc
select * from EMP order by sal asc ,deptno desc -- sal Same time deptno In descending order

where

select * from EMP where deptno = 10 -- just so so< > <= >=, It doesn't mean it can be used <> Or!=
select * from EMP where job = 'clerk' -- Case insensitive
select * from EMP where binary job = 'clerk' -- add binary Case sensitive
select * from EMP where hiredate < '1981.12.25'

-- where clause+Logical operator and
select * from EMP where sal > 1500 and sal < 3000 -- (1500, 3000)
select * from EMP where sal > 1500 && sal < 3000
select * from EMP where sal > 1500 and sal < 3000 order by sal
select * from EMP where sal between 1500 and 3000 -- [1500, 3000]
-- or
select * from EMP where deptno = 10 or deptno = 20
select * from EMP where deptno = 10 || deptno = 20
select * from EMP where deptno in (10,20)
select * from EMP where job in ('clerk','manager','analyst')

-- Fuzzy query
select * from EMP where ename like '%A%' -- The percent sign represents any number of characters
select * from EMP where ename like '_A%' -- Any character

-- Air judgment
select * from EMP where comm is null
select * from EMP where comm is not null

-- Parentheses count first and Recalculate or
select * from EMP where job = 'SALESMAN' or job = 'CLERK' and sal >=1500
select * from EMP where job = 'SALESMAN' or (job = 'CLERK' and sal >=1500)
select * from EMP where (job = 'SALESMAN' or job = 'CLERK') and sal >=1500

Function classification

Single-Row Functions

select empno, ename, lower(ename), upper(ename), sal from EMP -- Single line function, a set of data returns a set of results
select max(sal), min(sal), count(sal), sum(sal), avg(sal) from EMP -- Multiline function, a group of data that returns only one result, also known as grouping function
-- Note that except for these five multiline functions, all other functions are single line functions
-- Numerical function
select ename, length(ename), substring(ename,2,3) from EMP -- Start with 2 and count back three
select abs(-5),ceil(5.3),floor(5.9),round(3.14) from dual -- dual It's actually a pseudo table
select abs(-5) absolute value,ceil(5.3) Round up,floor(5.9) Round down,round(3.14) rounding
select ceil(sal) from EMP
select 10/3, 10%3, mod(10,3)
-- Date and time functions
select * from EMP
select curdate(), curtime()
select now(), sysdate(), sleep(3), now(), sysdate() -- now()current time  sysdate()Time of execution to current position, mm / DD / yyyy, H / min / S

insert into EMP values (9999, 'lili', 'SALESMAN', 7698, now(), 1000, null, 30) -- now()It can represent year, month, day, hour, minute and second, but when inserting data, you still need to refer to the table structure
desc EMP;

-- Process function 
select empno,ename, sal, if(sal > 2500, "High salary",'Base salary') as 'pay grade ' from EMP -- Double branch
select empno,ename, sal, comm, sal+ifnull(comm, 0) from EMP -- Before settlement comm When empty, sal+comm Empty question, single branch
select nullif(1,1),nullif(1,2) from dual; -- If value1 be equal to value2， Then return null， Otherwise, it will return to Japan value1
-- case
select empno, ename, job, 
case job
when 'CLERK' then 'clerk'
when 'SALESMAN' then 'sale'
when 'MANAGER' then 'manager'
else 'other'
end 'post',
sal from EMP
select empno, ename, job, 
case 
when sal <=1000 then 'A'
when sal<=2000 then 'B'
when sal <=3000 then 'C'
else 'D'
end 'Wage scale',
deptno from EMP
-- Other functions
select database(), user(), version() -- Current database, current user, current version

Multiline function

select max(sal), min(sal), count(sal), sum(sal), avg(sal) from EMP -- Multiline function, a group of data that returns only one result, also known as grouping function
-- Multiline functions are ignored automatically null value
select max( comm) , min( comm) , count( comm) , sum(comm) , sum( comm)/ count(comm) , avg(comm) from EMP;
-- max() ,min(), count()For all types sum(),avg()Valid only for numeric types
select max(ename) ,min(ename) , count(ename) , sum(ename) ,avg(ename) from EMP;
-- count
select count(*) from EMP
select 1 from EMP
select count(1) from EMP

group by group

-- Average wage of each department
select deptno, avg(sal) from EMP;
select deptno, avg(sal) from EMP group by deptno -- Fields and multiline functions cannot be used at the same time unless the field belongs to a group
select deptno, avg(sal) from EMP group by deptno order by deptno desc
-- Average salary of each post
select job, lower(job), avg(sal) from EMP group by job

having, secondary screening is performed after grouping

-- The average salary of each department is counted, and only the groups with an average salary of more than 2000 are displayed. It will be used for secondary filtering in the future having
select deptno, avg(sal) from EMP group by deptno
select deptno, avg(sal) average wage from EMP group by deptno having avg(sal)>2000
select deptno, avg(sal) average wage from EMP group by deptno having avg(sal)>2000 order by deptno desc
-- Count the average salary of each post, except MANAGER post
-- Method 1: use where
select job from EMP where job != 'MANAGER'
select job from EMP where job != 'MANAGER' group by job
select job, avg(sal) from EMP where job != 'MANAGER' group by job
-- Method 2: use having
select job, avg(sal) from EMP group by job having job != 'MANAGER'
-- The difference between the two methods, where Filter before grouping, having Filter after grouping
-- List positions with a minimum salary of less than 2000
select job, min(sal) from EMP group by job having min(sal) < 2000
-- Combination of departments and job matching with an average salary greater than 1200
select deptno, job, avg(sal) from EMP group by deptno, job having avg(sal) > 1200 order by deptno
-- Average wage of departments with less than 4 employees
select deptno, count(1), avg(sal) from EMP group by deptno having count(1) < 4
-- Count the maximum wage of each department, and exclude the departments with the maximum wage less than 3000
select deptno, max(sal) from EMP group by deptno having max(sal) < 3000

Execution order of select statement:

from -> where -> group by -> select -> having -> order by

DML language

insert

insert into t_student VALUES (1,'bob','d',17,'2022-5-6','Software class','aa@qq.com');
insert into t_student VALUES (11110000,'bob','d',17,'2022-5-6','Software class','aa@qq.com');

matters needing attention:
1. int Width is the display width,If it exceeds, the width can be automatically increased, int The bottom layer is 4 bytes
2.There are many ways of time'1256-12-23' "1256/12/23" "1256.12.23"
3.Strings do not distinguish between single and double quotation marks
4.How to write the current time now(), sysdate() , CURRENT_DATE()
5. char varchar Is the number of characters, not bytes, which can be used binary, varbinary Indicates the number of bytes of fixed length and variable length.
Test the above:
insert into t_student VALUES (11110000,'bob','d',171,'2022.5.6','Software class','aa@qq.com');
insert into t_student VALUES (11110000,'bob','d',172,'2022/5/6','Software class','aa@qq.com');
insert into t_student VALUES (11110000,"bob",'d',173,'2022-5-6','Software class','aa@qq.com');
insert into t_student VALUES (11110000,'bob','d',174,now(),'Software class','aa@qq.com');
6.If you do not insert data in all fields, you need to add field names. Otherwise mysql I don't know how to correspond
insert into t_student (sno,sname,enterdate) VALUES (11110000,'bob',now());

update

--where String matching for clause is not case sensitive
update t_student set sex = 'male'; --Impact full table
update t_student set sex = 'male' where sno = 1;
UPDATE t_student set classname = "ok" WHERE sno = 11110000;
UPDATE t_student set age = 1 WHERE classname = "OK";

delete

DELETE FROM t_student; --Clear all table data
DELETE FROM t_student WHERE sno = 1;

DDL language

create，alter，drop. Create, modify, delete Library

Create table: when creating a new database, the character set must be selected as utf-8mb4, corresponding to utf-8 character set. Otherwise, there will be garbled code when inserting Chinese.

create table t_student(
sno int(6),  -- The display length is 6, but more than 6 can be saved
sname VARCHAR(5),--The number of characters is 5
sex char(1),--The number of characters is 1
age int(3),
enterdate date,
classname VARCHAR(10),
email VARCHAR(15)
);

-- View table structure,Show field details
desc t_student;

-- View data in table
select * from t_student

-- View the statement used when creating the table
show create table t_student
CREATE TABLE `t_student` (
  `sno` int(6) DEFAULT NULL,
  `sname` varchar(5) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `enterdate` date DEFAULT NULL,
  `classname` varchar(10) DEFAULT NULL,
  `email` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

alter drop

-- Add a column
ALTER table t_student add score DOUBLE(5,2);
UPDATE t_student set score = 123.55555 WHERE sno = 1; -- Note that the decimal point is automatically rounded to two digits

-- Delete a column
ALTER table t_student drop score;

-- Add a column to the front
ALTER TABLE t_student add score DOUBLE(5,2) FIRST;

-- Add a column after a field
ALTER TABLE t_student add score DOUBLE(5,2) AFTER sex;

-- Modify the data type of a column
ALTER TABLE t_student MODIFY score FLOAT(4,1);
-- Modify the data type and column name of a column at the same time
ALTER TABLE t_student CHANGE score scores DOUBLE(5,1);

-- Delete table
DROP TABLE t_student;

DCL language

grant，revoke. Grant, revoke permission

TCL language

Start transaction start transaction

Commit commit transaction

Rollback rollback transaction

set transaction sets transaction properties

Non foreign key constraint

-- Column level constraint
create table t_student(
sno int(6) PRIMARY KEY auto_increment,  -- If the primary key is not empty, it will increase automatically
sname VARCHAR(5) not NULL, -- Name is not empty
sex char(1) DEFAULT 'male' CHECK(sex='male' || sex='female'), -- Default value, check value
age int(3) CHECK(age>=18 and age<=50), -- Inspection scope
enterdate date, 
classname VARCHAR(10),
email VARCHAR(15) UNIQUE); -- Check unique
 Test:
INSERT INTO t_student VALUES(null, 'Xiao Ming',DEFAULT,10,'2020-1-1', 'jj','xmm@11.com');
-- If sql If an error is reported, the primary key may not be connected, and we do not require the primary key to be connected


-- Table level constraints
create table t_student(
sno int(6) auto_increment,
sname VARCHAR(5) NOT NULL,
sex char(1) ,
age int(3),
enterdate date,
classname VARCHAR(10),
email VARCHAR(15),
CONSTRAINT pk_stu PRIMARY KEY (sno);
CONSTRAINT ck_sex CHECK(sex='male' || sex='female'),
CONSTRAINT ck_age CHECK(age>=18 and age<=50),
CONSTRAINT ck_em UNIQUE(email);
);

-- Add constraints after creating tables
ALTER TABLE t_student add CONSTRAINT pk_stu PRIMARY KEY (sno);
ALTER TABLE t_student MODIFY sno int(6) auto_increment;
ALTER TABLE t_student add CONSTRAINT ck_sex CHECK(sex='male' || sex='female'),
ALTER TABLE t_student add CONSTRAINT ck_age CHECK(age>=18 and age<=50),
ALTER TABLE t_student add CONSTRAINT ck_em UNIQUE(email);

Foreign key constraint

Foreign key: a field in a table depends on a field in another table. The dependent field must have a primary key constraint or a unique constraint.
The dependent table is called the master table or parent table, and the table with foreign key constraints is called the child table or slave table.

-- View table structure,Show field details
desc t_student;
desc t_class;
-- View data in table
select * from t_student
select * from t_class

create table t_class(
cno int(4) primary key auto_increment,
cname varchar(10) not null,
room char(4)
)

-- Add class data
insert into t_class values(null, 'java' , 'a');
insert into t_class values(null, 'pyt' , 'b');
insert into t_class values(null, 'qt' , 'c');

create table t_student(
sno int(6) primary key auto_increment,
sname varchar(5) not null,
classno int(4),
constraint fk foreign key (classno) references t_class(cno)
)

CREATE TABLE t_student(
sno int(6) PRIMARY KEY auto_increment,
sname VARCHAR(5) not NULL,
classno int(4)
)
-- Add foreign key constraint after table creation
alter table t_student add constraint fk foreign key (classno) references t_class(cno);

insert into t_student values(null, 'Zhang', 1),(null, 'Lee', 2),(null, 'king', 3);

-- Test foreign key constraints
delete from t_class where cno = 1;

Foreign key policy

select * from t_student
select * from t_class
-- Strategy 1 no action Operation not allowed
-- By operation sql To complete
-- First, change the class corresponding to the students in the table to null
update t_student set classno = null where classno = 2
-- Then delete class 2
delete from t_class where cno = 2

-- Strategy 2: cascade Cascade operation, which affects the foreign key information of the slave table when operating the master table,
-- Delete the original foreign key constraint first
alter table t_student drop foreign key fk
-- Re add foreign key constraints
alter table t_student add constraint fk foreign key (classno) references t_class(cno) on update cascade on delete cascade
-- Try the update
update t_class set cno = 5 where cno =3
-- Try deleting
delete from t_class where cno = 5;

-- Strategy 3, set null Empty operation
-- Delete the previous foreign key constraint first
alter table t_student drop foreign key fk
-- Re add foreign key constraints
alter table t_student add CONSTRAINT fk  foreign key(classno) references t_class(cno) on update cascade on delete set null
-- Try the update
update t_class set cno = 9 where cno = 1
-- Try deleting
delete from t_class where cno = 9

-- Strategies 2 and 3 can be mixed

-- Application:
-- Delete the circle of friends, delete likes and leave messages at the same time
-- Dissolve the class and the corresponding students  set null

DDL and DML add a little

-- Copy one table to another
create table t_student2 as 
select * from t_student
-- Copy a table to another table, only copy the structure, not the data
create table t_student3 as 
select * from t_student where 1=2
-- Copy a table to another table, as long as some columns and some data
create table t_student3 as 
select sno,sname from t_student where sno = 4
-- Clear data in table
delete from t_student
truncate table t_student

The difference between delete and truncate:
1.delete is dml and truncate is ddl
2.delete deletes the data in the table one by one. truncate preserves the table structure and then recreates the table, so the status is equivalent to a new table, which is more efficient
3.delete can be rolled back. truncate causes implicit submission and cannot be rolled back
4. After deleting the record, add data to the table again. For the value of the field with self incrementing constraint, it will increase automatically from the maximum value of the field before deletion, and truncate will restart self incrementing from 1

Multi table query syntax

Cross connection, natural connection, inner connection

-- Query employee number, name and department number
select empno, ename, deptno from EMP

-- Query employee No., name, Department No., department name
select * from EMP
select * from DEPT

-- multi-table query
-- Cross connect cross join
select * from EMP cross join DEPT -- Cartesian product, 56, no practical significance, theoretical significance
select * from EMP join DEPT -- cross It can be omitted, mysql Yes, oracle Not in

-- Natural connection: natural join，Automatically match all columns with the same name
select * from EMP natural join DEPT
select empno, ename, sal, dname, loc from EMP natural join DEPT -- Disadvantages: the database table to which the field belongs is not specified when querying the field, which is inefficient
select EMP.empno, EMP.ename, EMP.sal, DEPT.dname, DEPT.loc from EMP natural join DEPT -- Resolution: specify the table name
select E.empno, E.ename, E.sal, D.dname, D.loc from EMP E natural join DEPT D -- What if it's too complicated? Solution: alias
-- Natural connection automatically matches all columns with the same name in the table. How to match only some columns with the same name?
select * from EMP E join DEPT D using(deptno)
-- using The disadvantage is that the associated field names in the two tables must be the same. Solution: inner connection on clause
select * from EMP E join DEPT D on (E.deptno = D.deptno)
-- Comprehensive view on Use more clauses

select * from EMP E join DEPT D on (E.deptno = D.deptno) where sal > 3500
-- Conditions:
-- Screening conditions where having
-- Connection conditions on using natural

External connection

-- inner join and on Clause displays all matching information
select * from EMP E inner join DEPT D on E.deptno = D.deptno
-- Existing problems: 1. Department No. 40 has no employees and is not displayed in the query results. 2.If an employee has no department, it will not be displayed in the query results
-- External connection: in addition to displaying matching data, you can also display unmatched data 
-- Left outer connection: left outer join ，You can see the effect even if the information in the table on the left does not match
select * from EMP E left outer join DEPT D on E.deptno = D.deptno
-- The right outer connection shows the effect even if the information of the table on the right does not match
select * from EMP E right outer join DEPT D on E.deptno = D.deptno
-- All external connections, unmatched data in the left and right tables can be displayed, but mysql Not supported in, workaround: union: The efficiency of union set (de duplication) is low
select * from EMP E left outer join DEPT D on E.deptno = D.deptno union select * from EMP E right outer join DEPT D on E.deptno = D.deptno
-- union: High efficiency of union set (without duplication)
select * from EMP E left outer join DEPT D on E.deptno = D.deptno union all select * from EMP E right outer join DEPT D on E.deptno = D.deptno
-- mysql The support for set operations in is relatively weak. It only supports Union, intersection and difference sets
-- outer It can be omitted

Take three table query as an example

-- Query employee number, name, salary, department number, department name and salary grade
select * from EMP
select * from DEPT
select * from SALGRADE
-- Associate three tables
select E.empno, E.ename, E.sal, D.deptno, D.dname from EMP E 
right join DEPT D on E.deptno = D.deptno 
join SALGRADE S on E.sal between S.losal and S.hisal
-- behind join Just go down

Self connection query:
Self association: self association

-- Query employee number, name, superior number and superior name
select e1.empno, e1.ename Employee name, e2.empno, e2.ename Employee leadership from EMP e1 join EMP e2 on e1.MGR = e2.empno
-- There is an employee king，He has no superior leader, but he also needs to show it, which can be realized through the left outer connection
select e1.empno, e1.ename Employee name, e2.empno, e2.ename Employee leadership from EMP e1 left join EMP e2 on e1.MGR = e2.empno

Subquery

One sql contains multiple select

Execution order: execute sub queries first, and then external queries
Unrelated subquery: a subquery can run independently and is called an unrelated subquery
Irrelevant sub query classification: according to the number of rows of sub query results, it can be divided into single row sub query (there is only one row of sub query results) and multi row sub query (there are multiple rows of sub query results)

Unrelated subquery:

-- Query all ratios clerk Information about high paid employees
-- Step 1: query clerk wages
select sal from EMP where ename = 'clark'
-- Step 2: query all ratios clerk High paid employee information
select * from EMP where sal > 2450
-- Two commands solve the problem. The second command relies on the first command and talks about the combination of the two commands
select * from EMP where sal > (select sal from EMP where ename = 'clark') -- Subqueries in parentheses can run independently and are called irrelevant subqueries

-- single-row subqueries 
-- Query the name and salary of employees whose salary is higher than the average salary
select ename, sal from EMP where sal > (select avg(sal) from EMP)
-- Query and clark Name and salary of employees in the same department who are paid lower than him
select ename, sal from EMP where deptno = (select deptno from EMP where ename = 'Clark') and sal < (select sal from EMP where ename = 'Clark')
-- Query job and scott Same, than scott Employee information with early employment
select * from EMP where job = (select job from EMP where ename = 'scott') and hiredate < (select hiredate from EMP where ename = 'scott')

-- Multiline subquery
-- Query the employee information of department 20 with the same job as the employee of department 10
-- Check the employee information in the Department 20 first
	select * from EMP where deptno = 20
-- Employee position of department 10 under investigation
select job from EMP where deptno = 10
select * from EMP where deptno = 20 and job in (select job from EMP where deptno = 10)
select * from EMP where deptno = 20 and job = any (select job from EMP where deptno = 10) -- equivalence
-- Query salary ratio of all salesman High employee number, name and salary
select empno, ename, sal from EMP
select sal from EMP where job = 'Salesman'
-- combination
select empno, ename, sal from EMP where sal > all(select sal from EMP where job = 'Salesman')
select empno, ename, sal from EMP where sal > (select max(sal) from EMP where job = 'Salesman')
-- Query salary lower than any one clerk Employee information for salary(Smaller than the largest)
select * from EMP
select sal from EMP where job = 'clerk'
select * from EMP where sal < any(select sal from EMP where job = 'clerk')
select * from EMP where sal < (select max(sal) from EMP where job = 'clerk') -- equivalence
select * from EMP where sal < (select max(sal) from EMP where job = 'clerk') and job != 'clerk'

Related sub query: sub queries cannot be run independently. Run external queries first, and then internal queries

-- Query the employee with the highest salary (irrelevant sub query)
select * from EMP where sal = (select max(sal) from EMP)
-- Query the highest paid employees in the Department
-- Method 1: irrelevant sub query
select * from EMP where deptno = 10 and sal = (select max(sal) from EMP where deptno = 10) -- Check 10 departments first
union
select * from EMP where deptno = 20 and sal = (select max(sal) from EMP where deptno = 20) -- Check 20 departments again
union
select * from EMP where deptno = 30 and sal = (select max(sal) from EMP where deptno = 30)
-- Disadvantages: too many statements
-- Method 2:Related sub query
select * from EMP e where sal = (select max(sal) from EMP where deptno = e.deptno) order by deptno
-- Query employees whose salary is higher than the average salary of their position
-- Unrelated subquery
select * from EMP where job = 'clerk' and sal > (select avg(sal) from EMP where job = 'clerk') -- Then check multiple departments separately and then union
select * from EMP e where sal > (select avg(sal) from EMP where job = e.job) -- In addition, it is good to include the situation that there is only one person in a department and the salary is exactly equal to the average salary

affair

Atomicity: a group of things either succeed or fail
Consistency: change the database from one consistency state to another. We must achieve our desired results. Consistency is guaranteed by atomicity.
Isolation: things do not affect or interfere with each other.
Persistence: any changes made to data are recorded in permanent memory.

create table account(
id int primary key auto_increment,
uname varchar(10) not null,
balance double
)

select * from account
insert into account values(null, 'Lili', 2000),(null, 'Xiao Gang', 2000)

-- Lili transferred 200 yuan to Xiaogang
update account set balance = balance - 200 where id = 1;
update account set balance = balance + 200 where id = 2;
-- Default one DML Statement is one thing, so the above operation performs two things
-- The above two operations must be controlled in one thing
-- Manually open things, and all the operations performed after opening are controlled in one thing
start transaction;
update account set balance = balance - 200 where id = 1;
update account set balance = balance + 200 where id = 2;
rollback -- Roll back manually, and all the operations just performed will be cancelled
commit -- Manual submission
-- Note that no matter what changes are made before rollback and commit, the data in the cache must be modified and written to the database after commit. After rollback and commit, things are over

Transaction concurrency problem

Dirty reading: wrong data is read and uncommitted data is used.
A transaction is accessing and modifying data, but has not been submitted to the database. Another thing also accesses the data and uses the modified data, but because the data has not been submitted and may be rolled back, it is dirty data.

Thing A	Thing B
Open transaction A
	Open thing B
The query balance is 100
	The balance increased to 150
The query balance is 150
	Transaction rollback

Non repeatable:
When one thing is executed, the result of reading the same data multiple times is different due to the influence of another thing.

Thing A	Thing B
Open thing A
	Open thing B
The query balance is 100
	The balance increased to 150
The query balance is 100
	Submit things
The query balance is 150

Unreal reading:
Similar to non repeatable reading, when a transaction is executed, several pieces of data are inserted into another transaction. In subsequent queries, the first transaction finds some more records that do not exist, just like an illusion.

Thing A	Thing B
Open transaction A
	Open transaction B
Query three records with ID < 3
	Insert a record, id=2
	Commit transaction
Query four transactions with ID < 3

The difference between non repeatable reading and unreal reading:
1. The former focuses on the modification of data, while the latter focuses on adding or deleting data.
2. To solve non repeatable reading, you need to lock rows, and to solve phantom reading, you need to lock tables

Transaction isolation level

The concurrency problem is solved by setting the isolation level of transactions.

Isolation level	Dirty reading	Non repeatable reading	Unreal reading
READ UNCOMMITED	√	√	√
READ COMMITED		√	√
REPEATABLE READ			√
SERIALIZABLE

In practice, repeatable reading is generally used

-- View the default transaction isolation level, which can be read repeatedly by default
select @@transaction_isolation
-- Set the isolation level of the transaction. Set the isolation level of the current session
set session transaction isolation level read UNCOMMITTED
set session transaction isolation level read COMMITTED
set session transaction isolation level repeatable read
set session transaction isolation level serializable
-- Test transaction isolation
-- Create a new transaction
start transaction
select * from account where id = 1
commit

-- Create another transaction to simulate concurrency
start transaction;
select * from account where id = 1;
update account set balance = balance + 100 where id = 1;
rollback;
commit;

view

A virtual table built from a single or multiple basic data tables or other views.

Only store definitions, not real data. It is essentially a query statement.
Benefits: simplify user operations, enable users to focus on data, and protect confidential data.

-- Create a single table view
create or replace view v1
as 
select empno, ename, job, deptno
from EMP
where deptno = 20
with check option
-- view a chart
select * from v1
-- Insert data in view
insert into v1 (empno, ename, job, deptno) values (9999, 'lili', 'clerk', 20)
insert into v1 (empno, ename, job, deptno) values (8829, 'nana', 'clerk', 30)

-- Create, replace multi table view
create or replace view v2
as 
select e.empno, e.ename, e.sal, d.deptno, d.dname 
from EMP e
join DEPT d
on e.deptno = d.deptno
where sal > 2000
-- test
select * from v2

-- Create statistics view
create or replace view v3
as 
select e.deptno, d.dname, avg(sal),min(sal),count(1)
from EMP e
join DEPT d
using(deptno)
group by e.deptno
-- test
select * from v3

-- Create a view based view
create or replace view v4
as
select * from v3 where deptno = 20
-- test
select * from v4

Stored procedure:
1. Improve execution performance. Ordinary sql statements will perform lexical analysis, compilation, execution, etc. stored procedures only perform syntax analysis, compilation and execution for the first time.
2. Reduce the network burden. You only need to pass the sql parameters from the client without passing the sql itself
3. Black box database processing, without considering the detailed processing of stored procedures, you only need to know which stored procedure to call

create procedure mp1(name varchar(10))
begin
	if name is null or name = "" then 
		select * from EMP
	else
		select * from EMP where ename like concat('%',name,'%');
	end if;
end

-- Define a stored procedure with no return value
-- Realize fuzzy query operation
select * from EMP where ename like '%A%'

create procedure mp1(aaa varchar(10))
begin
	if aaa is null or aaa = "" then 
		select * from EMP;
	else
		select * from EMP where ename like concat('%',aaa,'%');
	end if;
end;
-- Delete stored procedure
drop procedure mp1
-- Call stored procedure
call mp1(null);
call mp1('R');

-- Stored procedure with return value
-- Realize fuzzy query operation
-- in Previous parameters in It can be omitted, found_rows()yes mysql A function defined in returns the number of query results
create procedure mp2(in name varchar(10), out num int(3))
begin
if name is null or name = "" then
select * from EMP
else
select * from EMP where ename like concat('%',name,'%')
end if
select found_rows() into num
end
-- Call stored procedure
call mp2(null, @num);
select @num;
call mp2('R', @aa);
select @aa;

JDBC+sql injection

JDBC:
A series of operation specification interfaces for connecting to the database are formulated, which are stored in the core library of JDK, such as java.sql package, javax.sql package, etc.
When calling these interfaces, they will call the corresponding database driver. mysql has mysql driver and oracle has oracle driver. Therefore, it is necessary to import the connection driver of the used database separately.

There are some packaged bytecode files in the jar file

The main module of JDBC: drivermanager, which manages the following parts:
1.connection: equivalent to a path between java and mysql
2.driver: equivalent to the driver transporting goods on the road
3.statement: it is equivalent to the truck driven by the driver and is responsible for transporting goods, including sql statement and resultset result set

java entity class: used to store the data queried from the database. The attribute must be private. It is recommended that the attribute of the entity class be written as a wrapper class (Integer instead of int, date type is recommended to be written as java.util.date). The attribute needs to have get and set methods, and must have null parameter construction methods. The entity class should implement the serialization interface (related to mybatis and distributed), You need to implement a null parameter construction method

sql injection: build special inputs as parameters and pass them into the web application. Most of these inputs are sql syntax combinations. Execute sql statements to perform the operations required by the attacker. The reason is that user input is not filtered
For example, if the password entered is' sdfg 'or' a '=' a ', the result of querying the user's password will always be true

select * from account where username = 'sdfg' and password = 'sdfg' or 'a' = 'a'

preparedstatement can be used in JDBC to prevent sql injection attacks. (the principle is to escape the quotation marks in the syntax). In practice, the prepared statement is used instead of the statement

preparestatement advantages: the statement is compiled only once, which reduces the compilation times and improves the security performance

If precompiling is enabled, you need to enable precompiling and precompiling cache at the same time to bring some performance improvement. You need to add the following parameters after the url:

&useServerPrepStms = true & cachePrepStms =true;

Elements required for DAO mode: 1. Entity class, one-to-one correspondence with the tables in the database 2.DAO layer, which defines the interface and implementation class for the data to perform those operations 3.mybatis encapsulates the DAO layer code, and there will be other changes in the coding method

Connection pool: create a connection in advance and store it in a container. You can take it out directly when you use it. If the collection is empty when obtaining the connection, create a new connection and return it. If the number exceeds the specified upper limit set by the connection pool, release the excess connections. Common connection pools include:
C3p0，Druid，DBCP

Log framework log4j: compared with using IO stream to print e.printStackTrace, 1. It can be saved for a long time, 2. There are levels, 3. The format can be well customized, and 4. The coding is simple.
log4j log level:
FATAL: very serious error events occur, which may cause the program to abort
ERROR: Although there is an ERROR, the program is allowed to continue running
WARN: refers to potential hazards in the operating environment
INFO: reports information that highlights the process of the application at a coarse-grained level
DEBUG: fine-grained event, which is most useful for application debugging
Use of log4j framework: add the jar package, log4j-1.2.8.jar, and add log4j.properties under the property file src (only this name can be used)

log4j.properties configuration file:

1og4j.rootLogger=debug, stdout use stdout Mode output log
1og4j.rootLogger=debug, logfile use logfile Output logs in
1og4j.rootLogger=debug, stdout , logfile There are two ways to output logs

use stdout Mode output log
1og4j.appender.stdout=org. apache .log4j.ConsoleAppender
1og4j.appender.stdout.Target=System. err
1og4j.appender.stdout.layout=org. apache .1og4j .SimpleLayout

use logfile Mode output log
1og4j.appender.1ogfile=org. apache.log4j.FileAppender
1og4j.appender.1ogfile.File=d:/msb.1og
1og4j.appender.logfile.layout=org.apache.1og4j.PatternLayout
1og4j.appender.1ogfile.layout.ConversionPattern=%d{yyy-MM-dd HH:mm:ss} %l %F %p %m%n

Three paradigms: some empirical norms to ensure reasonable database design

Objective: reasonable structure, less redundancy, and try to avoid inserting, deleting and modifying exceptions

One paradigm: column atomicity (column cannot be divided) detailed address - > Province, city, district
Second normal form: the complete correlation between the data and the joint primary key. Each column is related to the primary key, not only a part of the primary key (mainly for the joint primary key)
Three paradigms: direct correlation between data and primary key (each column of data in the column is directly related to the primary key, not indirectly)

One to one relationship: one data in table A corresponds to another relationship in table B (Student ID card)
One to many: multiple records in table A correspond to one (student class) in table B. multiple student records correspond to one record in the class table
Many to many: Student schedule curriculum schedule

Tags: Database MySQL SQL db

Posted on Wed, 06 Oct 2021 17:13:50 -0400 by le007

Programmer Help