Mysql database learning (III): crud operations, integrity constraints, select single-table queries, select multi-table queries bitsCN.com
I. table crud operations
It refers to the addition (Create), query (Retrieve) (obtain data again), Update (Update), and Delete (Delete). // The select query will be discussed later.
SQL Code 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Create table t_emp (empno int, ename varchar (20), esex char (2 ));
Alter table t_emp modify ename varchar (30 );
Alter table t_emp drop esex;
Alter table t_emp add esex char (2 );
Insert into t_emp (empno, ename, esex) values (1000, 'Tom ', 'M ');
Insert into t_emp values (1000, 'mage', 'F');/* the primary key has not been set, so empno can be the same */
Insert into t_emp (empno, ename) values (1002, 'John ');
Insert into t_emp (empno, ename, esex) values (1003, null, 'M ');
Insert into t_emp values (1004, 'Zhang San', 'mal ');
Show variables like 'character _ set % ';/* View character set settings */
Set character_set_database = utf8;/* You can also set the configuration file */
Set names gbk;/* supports inserting Chinese characters */
Update t_emp set empno = 1001 where ename = 'Maggi ';
Delete from t_emp where esex is null;
Delete from t_emp;/* the table structure is still in */
Drop table t_emp;/* delete an entire table */
II. integrity constraintsTable integrity constraints
User-defined integrity constraints (check)
SQL Code 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
Create table t_emp (empno int not null primary key, ename varchar (20), esex char (2);/* set primary key at creation */
Create table t_emp (empno int, ename varchar (20), esex char (2), primary key (empno ));
Create table t_emp (empno int, ename varchar (20), esex char (2), constraint PK_EMPNO primary key (empno);/* Set primary key */
Create table t_emp (empno int, ename varchar (20), esex char (2 ));
Alter table t_emp add constraint PK_EMPNO primary key (empno);/* you can set the primary key in this way */
Insert into t_emp values (1000, 'John', 'M ');
Insert into t_emp values (1000, 'Lily', 'F');/* error, empno cannot be equal */
Insert into t_emp values (null, 'Lily', 'F');/* error, primary key cannot be blank */
Create table t_emp (empno int, deptno int, ename varchar (20), esex char (2 ));
Alter table t_emp add constraint PK_EMPNO primary key (empno );
Create table t_dept (deptno int, dname varchar (20 ));
Alter table t_dept add constraint PK_DEPTNO primary key (deptno );
Alter table t_emp add constraint FK_DEPTNO foreign key (deptno) references t_dept (deptno);/* Set the foreign key of t_emp to the primary key of t_dept */
Set names gbk;
Insert into t_dept values (2001, 'personnel authorization ');
Insert into t_dept values (2002, 'technology department ');
Insert into t_emp values (1001,200 1, 'John', 'M ');
Insert into t_emp values (1003,200 3, 'John', 'M ');
Create table t_test1 (id int auto_increment primary key, name varchar (30), age int default 20 );
Insert into t_test1 values (null, 'AAA ');
Insert into t_test1 values (null, 'AAA', null );
Insert into t_test1 (name) values ('BBB ');
Create table t_test2 (id int, name varchar (30), age int );
Alter table t_test2 add constraint CC_AGE check (age> = 18 and age <= 60);/* currently mysql does not support check restrictions */
Alter table t_test2 add constraint CC_AGE check (length (name)> 2 );
III. select query
Import data before exercise: create database scott; use scott; source C:/scott. SQL scott. SQL click here to download
Select single table query:
SQL Code 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Select empno, ename, job from emp;
Select * from emp;
SELECT empno as 'employee id ', ename 'name' FROM emp;/* as is followed by an alias */
SELECT empno, 'jinan University 'FROM emp;/* constant column query */
SELECT empno, concat (ename, '#') FROM emp;/* concat connection */
SELECT empno, ename | '#' FROM emp;/* oracle can use | used as a connector */
SELECT empno, ename, job FROM emp WHERE ename = 'Smith'
SELECT empno, ename, job FROM emp WHERE ename <> 'Smith '/* can also be used! = */
SELECT empno, ename, sal FROM emp WHERE sal >=1500
SELECT * FROM emp WHERE deptno = 30 and sal> 1500;/* and */
SELECT * FROM emp WHERE job = 'manager' or job = 'Salesman'/* or */
SELECT * FROM emp where sal BETWEEN 800 and 1500;
SELECT * FROM emp where sal> = 800 and sal <= 1500;
SELECT empno, ename, sal, comm FROM emp WHERE comm is null
SELECT empno, ename, sal, comm FROM emp WHERE comm is not null/* not */
SELECT * FROM emp where sal not BETWEEN 800 and 1500;/* */
SELECT * FROM emp where ename in ('Smith ', 'king');/* in */
SELECT * FROM emp where ename like's % ';/* fuzzy query wildcard:' % '(0 multiple characters); wildcard:' _ '(single character )*/
SELECT * FROM emp where ename like's _ ITH ';
SELECT * FROM emp order by ename desc;/* order by defaults to ascending asc */
SELECT empno, ename, job FROM emp order by 2 desc;
SELECT * FROM emp order by job asc, sal desc;
SQL Code 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Select * from emp order by sal limit 5;/* limit can be used for paging query */
Select * from emp order by sal limit;/* 0 indicates offet, and 5 indicates five records starting from 0 */
Select * from emp order by sal limit 5, 5;
Select * from emp order by sal limit 10, 5;
Select job, deptno from emp;
Select all job, deptno from emp;/* The default value is all */
Select distinct job, deptno from emp;/* Remove duplicate records */
Select * from dept where deptno in (select distinct deptno from emp);/* query department information with employees */
/* UNION (UNION without duplicates): When UNION is executed, duplicate rows in the result set are automatically removed and sorted in ascending order based on the results in the first column. */
Select empno, ename, job from emp where job = 'Salesman'
Union/* union indicates union query */
Select empno, ename, job from emp where job = 'manager ';
Select empno, ename, job from emp where job = 'Salesman' or job = 'manager'/* comparison result */
/* Union all (with duplicate UNION sets): Duplicate rows are not removed and the result set is not sorted. */
Select job, sal from emp where empno = 7902
Union all
Select job, sal from emp where empno = 7788;
Select job, sal from emp where empno = 7902
Union
Select job, sal from emp where empno = 7788;
Select multi-table query: multi-table query
Cross-connection internal connection external connection
Left outer join
Outer right connection
Full connection
Natural connection
A cross join is a multi-table query without a WHERE clause. it returns the Cartesian product of all data rows in the two joined tables. The number of rows returned to the result set is equal to the number of rows that meet the query conditions in the first table multiplied by the number of rows that meet the query conditions in the second table.
Internal join (equivalent join): Use the equal sign (=) operator in the connection condition to compare the values of the joined columns. all columns in the connected table are listed in the query results, including duplicate columns.
Inner join (unequal join): Use a comparison operator other than the equal operator to compare the values of the joined columns. These operators include >,>=, <=, <,!> ,! <和<>
Internal connection (self-connection)
Outer join (left join): returns records that include all records in the left table and join fields in the right table. that is, the left outer join adds unmatched data in the main table based on the equijoin.
Outer join (right join): returns records that include all records in the right table and join fields in the left table are equal. that is, the outer right join adds unmatched data in the connected table on the basis of the equijoin.
Outer join (full join): the outer join adds unmatched data in the left and right tables on the basis of the equivalent join.
Natural join: Use the equal to (=) operator in the join condition to compare the column values of the connected column. However, it uses the selection list to indicate the columns included in the query result set, delete duplicate columns in the connection table.
SQL Code 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Select * from emp, dept/* Cross join */
SELECT * FROM emp inner join dept ON emp. deptno = dept. deptno;/* internal JOIN (equivalent JOIN )*/
Select * from emp, dept where emp. deptno = dept. deptno;
Select * from emp inner join dept on emp. deptno> dept. deptno;/* inner join (unequal JOIN )*/
Select * from emp, dept where emp. deptno> dept. deptno;
Select A. ename employee, B. ename leads from emp A, emp B where A. mgr = B. empno;/* internal connection (self connection )*/
SELECT * FROM emp inner join dept ON emp. deptno = dept. deptno;
Select * from emp left join dept on emp. deptno = dept. deptno/* outer join (left join )*/
/* Scott. SQL does not set the foreign key of the emp table to deptno, so the deptno value that does not exist in the dept table can be inserted here */
/* Mainly to demonstrate the difference between left and right connections */
Insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
Values (9999, 'xxxx ', 'cler', 7782, '2017-01-23', 1982, null, 90 );
Select * from emp right outer join dept on emp. deptno = dept. deptno/* outer join (right join )*/
Select * from emp left join dept on emp. deptno = dept. deptno
/* The natural connection will merge deptno, but the external connection will not */
SELECT * FROM emp natural join dept;
SELECT * FROM emp natural left join dept;
SELECT * FROM emp natural right join dept;
Refer:
Introduction to Database Systems
Mysql 5.1 Reference Manual
BitsCN.com
