- 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