The database used in this paper is as follows:
CREATE DATABASE exam;
/*Creating Department Tables*/
CREATE TABLE dept(
deptno INT PRIMARY KEY,
dname VARCHAR(50),
loc VARCHAR(50)
);
/*Create an Employee Table*/
CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
COMM DECIMAL(7,2),
deptno INT,
CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno) ,
//CONSTRAINT fk_emp_dept FOREIGN KEY(deptno) REFERENCES dept(deptno);
//This foreign key constraint should have been added, but this constraint should not be added for the sake of future columns. With this constraint, the value of deptno in emp table must be a value of the primary key in dept table.
);
/*Create a Wage Scale*/
CREATE TABLE salgrade(
grade INT PRIMARY KEY,
losal INT,
hisal INT
);
/*Creating Student Table*/
CREATE TABLE stu(
sid INT PRIMARY KEY,
sname VARCHAR(50),
age INT,
gander VARCHAR(10),
province VARCHAR(50),
tuition INT
);
/*Insert dept table data*/
INSERT INTO dept VALUES (10, 'Ministry of Education and Research', 'Beijing');
INSERT INTO dept VALUES (20, 'Ministry of Education and Work', 'Shanghai');
INSERT INTO dept VALUES (30, 'Sales Department', 'Guangzhou');
INSERT INTO dept VALUES (40, 'Finance Department', 'Wuhan');
/*Insert emp table data*/
INSERT INTO emp VALUES (1009, 'Zeng A Niu', 'Chairman', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO emp VALUES (1004, 'Liu Bei', 'manager', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO emp VALUES (1006, 'Guan Yu', 'manager', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO emp VALUES (1007, 'Zhang Fei', 'manager', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO emp VALUES (1008, 'Zhuge Liang', 'analyst', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO emp VALUES (1013, 'Pang Tong', 'analyst', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO emp VALUES (1002, 'Daisy', 'Salesperson', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO emp VALUES (1003, 'Yin Tianzheng', 'Salesperson', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO emp VALUES (1005, 'Thankson', 'Salesperson', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO emp VALUES (1010, 'Xiangr', 'Salesperson', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO emp VALUES (1012, 'Cheng Pu', 'Clerk', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO emp VALUES (1014, 'Huang gai', 'Clerk', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO emp VALUES (1011, 'Zhou Tai', 'Clerk', 1008, '2007-05-23', 11000, NULL, 20);
INSERT INTO emp VALUES (1001, 'Gan Ning', 'Clerk', 1013, '2000-12-17', 8000, NULL, 20);
INSERT INTO emp VALUES (1015, 'Zhang San', 'Cleaner', 1009, '2001-09-01', 24500, 50000, 50); //Note that there are no 50 departments in the Department table at all.
/*Insert salgrade table data*/
INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);
/*Insert stu table data*/
INSERT INTO `stu` VALUES ('1', 'Wang Yong', '23', 'male', 'Beijing', '1500');
INSERT INTO `stu` VALUES ('2', 'Zhang Lei', '25', 'male', 'Liaoning', '2500');
INSERT INTO `stu` VALUES ('3', 'Li Qiang', '22', 'male', 'Beijing', '3500');
INSERT INTO `stu` VALUES ('4', 'Song Yonghe', '25', 'male', 'Beijing', '1500');
INSERT INTO `stu` VALUES ('5', 'Syria Beauty', '23', 'female', 'Beijing', '1000');
INSERT INTO `stu` VALUES ('6', 'Chen Ning', '22', 'female', 'Shandong', '2500');
INSERT INTO `stu` VALUES ('7', 'Wang Li', '21', 'female', 'Beijing', '1600');
INSERT INTO `stu` VALUES ('8', 'Li Yong', '23', 'male', 'Beijing', '3500');
INSERT INTO `stu` VALUES ('9', 'Zhang Ling', '23', 'female', 'Guangzhou', '2500');
INSERT INTO `stu` VALUES ('10', 'Ah calendar', '18', 'male', 'Shanxi', '3500');
INSERT INTO `stu` VALUES ('11', 'Wang Gang', '23', 'male', 'Hubei', '4500');
INSERT INTO `stu` VALUES ('12', 'Chen Yong', '24', 'male', 'Beijing', '1500');
INSERT INTO `stu` VALUES ('13', 'Li Lei', '24', 'male', 'Liaoning', '2500');
INSERT INTO `stu` VALUES ('14', 'Li Yan', '22', 'male', 'Beijing', '3500');
INSERT INTO `stu` VALUES ('15', 'Wang Xiaoming', '25', 'male', 'Beijing', '1500');
INSERT INTO `stu` VALUES ('16', 'Wang Xiaoli', '23', 'female', 'Beijing', '1000');
INSERT INTO `stu` VALUES ('17', 'Downing', '22', 'female', 'Shandong', '2500');
INSERT INTO `stu` VALUES ('18', 'Tang Li', '21', 'female', 'Beijing', '1600');
INSERT INTO `stu` VALUES ('19', 'Ah forever', '23', 'male', 'Beijing', '3500');
INSERT INTO `stu` VALUES ('20', 'Tang Ling', '23', 'female', 'Guangzhou', '2500');
INSERT INTO `stu` VALUES ('21', 'Sydan', '18', 'male', 'Shanxi', '3500');
INSERT INTO `stu` VALUES ('22', 'Wang tired', '23', 'male', 'Hubei', '4500');
INSERT INTO `stu` VALUES ('23', 'Zhao an', '23', 'male', 'Beijing', '1500');
INSERT INTO `stu` VALUES ('24', 'Guan Lei', '25', 'male', 'Liaoning', '2500');
INSERT INTO `stu` VALUES ('25', 'Li Zi', '22', 'male', 'Beijing', '3500');
INSERT INTO `stu` VALUES ('26', 'Xu An Guo', '25', 'male', 'Beijing', '1500');
INSERT INTO `stu` VALUES ('27', 'Chen Haonan', '23', 'female', 'Beijing', '1000');
INSERT INTO `stu` VALUES ('28', 'state', '22', 'female', 'Shandong', '2500');
INSERT INTO `stu` VALUES ('29', 'Sun Li', '21', 'female', 'Beijing', '1600');
INSERT INTO `stu` VALUES ('30', 'Li Zhiguo', '23', 'male', 'Beijing', '3500');
INSERT INTO `stu` VALUES ('31', 'Zhang Na', '23', 'female', 'Guangzhou', '2500');
INSERT INTO `stu` VALUES ('32', 'An Qiang', '18', 'male', 'Shanxi', '3500');
INSERT INTO `stu` VALUES ('33', 'Wang Huan', '23', 'male', 'Hubei', '4500');
INSERT INTO `stu` VALUES ('34', 'Zhou Tianle', '23', 'male', 'Beijing', '1500');
INSERT INTO `stu` VALUES ('35', 'Guan Lei', '25', 'male', 'Liaoning', '2500');
INSERT INTO `stu` VALUES ('36', 'Wu Qiang', '22', 'male', 'Beijing', '3500');
INSERT INTO `stu` VALUES ('37', 'Wu Heguo', '25', 'male', 'Beijing', '1500');
INSERT INTO `stu` VALUES ('38', 'Positive sum', '23', 'female', 'Beijing', '1000');
INSERT INTO `stu` VALUES ('39', 'Wu Li', '22', 'female', 'Shandong', '2500');
INSERT INTO `stu` VALUES ('40', 'Feng Han', '21', 'female', 'Beijing', '1600');
INSERT INTO `stu` VALUES ('41', 'Chen Dong', '23', 'male', 'Beijing', '3500');
INSERT INTO `stu` VALUES ('42', 'Guan Ling', '23', 'female', 'Guangzhou', '2500');
INSERT INTO `stu` VALUES ('43', 'Pauli', '18', 'male', 'Shanxi', '3500');
INSERT INTO `stu` VALUES ('44', 'Adata', '23', 'male', 'Hubei', '4500');
INSERT INTO `stu` VALUES ('45', 'Li Yong', '23', 'male', 'Beijing', '1500');
INSERT INTO `stu` VALUES ('46', 'Zhang Guanlei', '25', 'male', 'Liaoning', '2500');
INSERT INTO `stu` VALUES ('47', 'Send Xiaoqiang', '22', 'male', 'Beijing', '3500');
INSERT INTO `stu` VALUES ('48', 'Guan Zhonglin', '25', 'male', 'Beijing', '1500');
INSERT INTO `stu` VALUES ('49', 'Su Xiaoyao', '23', 'female', 'Beijing', '1000');
INSERT INTO `stu` VALUES ('50', 'Zhao Ning', '22', 'female', 'Shandong', '2500');
INSERT INTO `stu` VALUES ('51', 'Chen Li', '21', 'female', 'Beijing', '1600');
INSERT INTO `stu` VALUES ('52', 'Qian Xiaogang', '23', 'male', 'Beijing', '3500');
INSERT INTO `stu` VALUES ('53', 'Eileen', '23', 'female', 'Guangzhou', '2500');
INSERT INTO `stu` VALUES ('54', 'Guo Lin', '18', 'male', 'Shanxi', '3500');
INSERT INTO `stu` VALUES ('55', 'Zhou Zhiqiang', '23', 'male', 'Hubei', '4500');
Single Table Query and Multiple Table Query Columns
Single form query exercise
/*1\. Find out all employees whose department number is 30*/
SELECT *
FROM emp
WHERE deptno=30;
/*2\. Inquire the names, numbers and department numbers of all salesmen.*/
SELECT ename, empno, deptno
FROM emp
WHERE job='Salesperson';
/*3\. Find out employees whose bonuses are higher than their salaries.*/
SELECT *
FROM emp
WHERE comm > sal
/*4\. Find out employees whose bonuses are 60% higher than their salaries.*/
SELECT *
FROM emp
WHERE comm > sal*0.6;
/*5\. Find out the details of all managers in department number 10 and all salesmen in department number 20.*/
SELECT *
FROM emp
WHERE (deptno=10 AND job='manager') OR (deptno=20 AND job='Salesperson')
/*6\. Find out all the managers in the department number 10, all the salesmen in the department number 20, and the details of all the employees who are neither managers nor salesmen but whose salaries are large or equal to 20,000.*/
SELECT *
FROM emp
WHERE (deptno=10 AND job='manager') OR (deptno=20 AND job='Salesperson') OR (job NOT IN ('manager', 'Salesperson') AND sal >= 20000)
/*8\. Employees without bonuses or with bonuses below 1000.*/
SELECT *
FROM emp
WHERE comm IS NULL OR comm < 1000
/*9\. The query name consists of three words for the employee.*/
SELECT *
FROM emp
WHERE ename LIKE '___'
/*10.Look up the employees who entered the company in 2000.*/
SELECT *
FROM emp
WHERE hiredate LIKE '2000-%'
/*11\. Query all employee details and sort them in ascending order*/
SELECT *
FROM emp
ORDER BY empno
/*12\. Query all employee details and rank them in descending order with wages. If wages are the same, rank them in ascending order with entry dates.*/
SELECT *
FROM emp
ORDER BY sal DESC, hiredate ASC
/*13\. Query the average salary of each department*/
SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno
/*14\. Query the number of employees in each department.*/
SELECT deptno, COUNT(*)
FROM emp
GROUP BY deptno
/*15\. Query the maximum wage, minimum wage, number of people for each job*/
SELECT job, MAX(sal), MIN(sal), COUNT(*)
FROM emp
GROUP BY job
Multi-table Query Exercise
/* 1. Find out at least one employee's department. Display Department number, Department name, Department location and department number. Column: d.deptno, d.dname, d.loc, number of departments Table: dept d, emp e Conditions: e.deptno=d.deptno */ SELECT d.*, z1.cnt FROM dept d, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z1 WHERE d.deptno = z1.deptno /* 3. List the names of all employees and their immediate superiors. Column: Employee's name, superior's name Table: emp e, emp m Conditions: mgr of employees = empno of superiors */ SELECT e.ename, IFNULL(m.ename, 'BOSS') Leader FROM emp e LEFT OUTER JOIN emp m ON e.mgr=m.empno /* 4. List the number, name and department name of all employees whose employment date is earlier than that of their immediate superiors. Column: e.empno, e.ename, d.dname Table: emp e, emp m, dept d Conditions: E. hiredate < M. hiredate */ SELECT e.empno, e.ename, d.dname FROM emp e, emp m, dept d WHERE e.mgr=m.empno AND e.hiredate<m.hiredate AND e.deptno=d.deptno /* 5. List Department names and staff information for these departments, as well as those departments that do not have employees. Column:* Table: emp e, dept d Conditions: e.deptno=d.deptno */ SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno /* 7. List the jobs with a minimum salary of more than 15,000 and the number of employees working in them. Column: job, count(*) Table: emp e Conditions: min (sal) > 15000 Grouping: job */ SELECT job, COUNT(*) FROM emp e GROUP BY job HAVING MIN(sal) > 15000 /* 8. List the names of employees working in the sales department, assuming they don't know the department number of the sales department. Column: e.ename Table: emp Conditions: e.deptno=(select deptno from dept where dname='Sales') */ SELECT * FROM emp e WHERE e.deptno=(SELECT deptno FROM dept WHERE dname='Sales Department') /* 9. List all employees whose salary is higher than the average salary of the company, the name of their department, their superiors, and their salary levels. Column:* Table: emp e Conditions: Sal > (Find out the average salary of the company) */ SELECT e.*, d.dname, m.ename, s.grade FROM emp e, dept d, emp m, salgrade s WHERE e.sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr=m.empno AND e.sal BETWEEN s.losal AND s.hisal --------------- SELECT e.*, d.dname, m.ename, s.grade FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno LEFT OUTER JOIN emp m ON e.mgr=m.empno LEFT OUTER JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal WHERE e.sal>(SELECT AVG(sal) FROM emp) SELECT * FROM emp; SELECT * FROM dept; SELECT * FROM salgrade; /* 10.List the names of all employees and departments engaged in the same work as Pang Tong. Column: e.*, d.dname Table: emp e, dept d Conditions: job= (Query out a large number of jobs) */ SELECT e.*, d.dname FROM emp e, dept d WHERE e.deptno=d.deptno AND job=(SELECT job FROM emp WHERE ename='Pang Tong') /* 11.List the names and salaries of employees and departments whose salaries are higher than those of all employees working in Department 30. Column: e.ename, e.sal, d.dname Table: emp e, dept d Conditions; Sal > all (30 Department salary) */ SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno=d.deptno AND sal > ALL (SELECT sal FROM emp WHERE deptno=30) /* 13.Find out the year, profit, annual growth ratio */ SELECT y1.*, IFNULL(CONCAT((y1.zz-y2.zz)/y2.zz*100, '%'), '0%') Growth ratio FROM tb_year y1 LEFT OUTER JOIN tb_year y2 ON y1.year=y2.year+1;
Write in front: MySQL is the software of this article. About the basic application of MySQL, please Click here Study
What is SQL
SQL: Structured Query Language (Structured Query Language). The function is: the client uses SQL to operate the server.
SQL annotation: is formulated by the International Organization for Standardization (ISO), the unified operation of DBMS.
SQL Dialect: Every DBMS will have its own dialect in addition to following the standard. That is, their own special sentence or syntax.
SQL grammar:
- SQL statements can be written in single or multiple lines, ending with a semicolon, without a semicolon, and can change lines.
- Spaces or indentations can be used to enhance the readability of statements.
- MySQL is case-insensitive, but capitalization is recommended.
- All string types in the database need single quotation marks instead of double quotation marks.
Classification of SQL Statements
- DDL (Data Definition Language): Data Definition Language, which is used to manipulate the structure of a database or table. That is to create, delete, and modify library and table structures.
- DML (Data Manipulation Language): Data Operating Language, which is used to update the records of tables, that is, to add, delete and change the records of tables.
- DQL (Dat Query Language): Data Query Language, used to query the records of tables
- DCL (Dat Control Language): Data Control Language, which defines access permissions and security levels
Data types stored in Databases
click here Download Data Type Documents
DDL
DDL Operations on Libraries
- View all databases: SHOW DATABASES;
- Switch (select the database to operate): USE database name;
- Create a database: CREATE DATABASE mydb1 [CHARSET=utf8]// box number is optional. There is no box number in the statement
- Delete database: DROP DATABASE mydb1
- Modify database coding: ALTER DATABASE mydb1 CHARACTER SET utf8
DDL Operations on Tables
- Create tables
CREATE TABLE Table Name(
Column type,
Column type,
...
Column type
);
- View all table names in the current database: SHOW TABLES;
- View table structure: DESC table name;
- Delete table: DROP TABLE table name;
- Modify _add column:
ALTER TABLE Table Name
ADD (
Column type,
Column type,
...
);
- Modify column type ALTER TABLE table name MODIFY column type;
- Modify Modify column name: ALTER TABLE table name CHANGE original column name new column type;
- Modify _delete column: ALTER TABLE table name DROP column name;
- Modify the table name: ALTER TABLE original table name RENAME TO new table name;
DML
where conditional grammar
- The condition must be a boolean type value or expression
- where attribute judgment
- Conditional operators:
| operator | Illustration examples | operator | Illustration examples |
|---|---|---|---|
| = | Be equal to | != | Not equal to |
| <> | Greater than or less than | > | greater than |
| < | less than | >= | Greater than or equal to |
| <= | Less than or equal to WHERE age >= 18 AND age <= 80 | between..and | WHERE age BETWEEN 18 AND 80 |
| in(...) | WHERE name IN ('zhangSan','liSi') includes zhangSan and liSi | is null | WHERE age IS NULL; select age as NULL |
| not | wrong | or | perhaps |
| and | and |
WHERE age >= 18 AND age <= 80 The selected age is older than 18 and less than 80
WHERE age BETWEEN 18 AND 80
WHERE name ='zhangSan'OR name ='liSi' Checked name is zhanSan or liSi
WHERE name IN ('zhangSan','liSi'); the selected names are zhangSan and liSi
WHERE age IS NULL chooses the age value to be empty
WHERE age IS NOT NULL chooses an age value that is not empty
insert data
INTERT INTO table name (column name 1, column name 2,...) VALUES (column value 1, column value 2,...);
The order and number of values must correspond to the column specified above, without specifying the automatic insertion of null. If no column name is given, all columns are inserted
INSERT INTO stu( number, name, age, gender ) VALUES( '11111111', 'zhangSan', 28, 'male' );
Modify data
UPDATE table name SET column name 1 = column value 1, column name 2 = column value 2,... [WHERE condition]
A box number indicates that a condition can be specified, and there is no box number in the actual statement. If no conditions are applied, the values of the column names for all records are modified.
UPDATE stu SET number=55, name='liSi' WHERE age BETWEEN 18 AND 80 //Change the number of all students aged between 18 and 80 to 55 and the name to liSi
Delete data
DELETE FROM Table Name [WHERE Conditions]; Common Delete Statements
TRUNCATE TABLE Table Name: TRUNCATE is a DDL statement. It deletes the drop table first and then create s the table. And can't roll back!!!
DCL superuser root
Creating Users
CREATE USER User Name @IP Address IDENTIFIED BY'Password'; Users can only log in at the specified IP address
CREATE USER User Name @'%'IDENTIFIED BY'Password'; Users can log in at any IP address
localhost can be used for local IP address
User Authorization
GRANT permission 1,... The permission n ON database. * TO username@IP address assigns the specified permissions to the user on the specified database
GRANT ALL ON database. * TO username@IP address; assign users all permissions on the specified database
The permissions are: create, alter, drop, insert, update, delete, select
For example, GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost;
Withdrawal of authorization
REVOKE permission 1,... The permission n ON database. * FROM username@IP address; revokes the specified permission of the specified user on the specified database
For example, REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;
View permissions
SHOW GRANTS FOR User Name@IP Address to View the Privileges of a Specified User
delete user
DROP USER User Name@IP Address Delete User
DQL_Database Query Language
Basic query
Query all columns; select * from table name;
Query the specified column: select column 1, column 2,..., column n from table name;
If there are duplicate records, only one record is displayed: select distinct * from table name;
Column operation
Column arithmetic only operates on the display and does not modify the contents of the database.
- Add, subtract, multiply and divide: select column 1 + column 2 from table name; column can add, subtract, multiply and divide numbers. Strings are treated as 0. If there is a record of NULL in a column, it is necessary to convert null value to 0 with ifnull (column with null value, 0). Of course, null value can be converted to string XXX with ifnull (column with null value,'XXX').
- Give a column an alias, this alias is only valid for display, and does not modify the real column name in the database: SELECT column 1 original name AS column 1 alias, column 2 original name AS column 2 alias FROM table name; where as can be omitted
- Continuous operation of strings: select concat('I call', name,'My job is', job) from table name; where name and job are columns of table.
Conditional Query _where
Use where keyword as a condition, refer to where's Conditional operator
Fuzzy Query_like
- Query all employees whose name is Zhang in the emp column; select * from emp where ename is like'Zhang_'; and represent a vague arbitrary character with an underscore matching a character.
- Query all employees with only three words in the name ename column of emp in the employee table; select * from emp where ename is like'_';
- Query all employees in the name ename column of emp in the employee table: select * from emp where ename like'Zhang%'; where the% number matches multiple characters
- In the name ename column of emp, the last word is all employees of forest: select * from emp where ename like'% forest';
- Query the name ename column of emp in the employee table, all employees with small size: select * from emp where ename like'% small';
Sort_order by
Ascending order -- Sort all records by age: select * from where EMP order by age asc; age is the column of the table, where ASC can be omitted
Descending -- Sorting all records by salary: select * from where emp order by sal desc; desc cannot be omitted
Use multiple columns as a sorting condition - ascend all records by age, and descend some records by wage if they are of the same age: select * from where EMP order by age asc, sal desc;
Aggregation function
Aggregation functions are used to do longitudinal operations on a column
- Query row number: Query the number of rows in EMP table that all columns are not NULL records: select count(*) from emp;
- Look up the maximum / minimum: Look up the maximum salary: select max(sal) from emp;
- Query sum, NULL and string are calculated as 0: the sum of all salaries is queried: select sum (sal) from emp;
- Query average, NULL and string are calculated as 0: Query average wage of all people: select avg(sal) from emp;
To sum up: select count(*), sum(sal), max(sal) maximum wage, avg(sal) average wage from emp;
Group query
Group query means that records are grouped by a column, and then information is queried. The information searched is group information, not personal information. Group information is usually calculated by aggregation function.
- Use deptno grouping to query department number and the number of records per department: select deptno,count(*) from emp group by deptno;
- Use job grouping to query the maximum wage for each job: select job, Max (sal) from EMP group by job;
Group conditions: where before grouping and have after grouping - Use deptno grouping to query the number of records in each group, provided that the salary of the number of records is greater than 15000 (before grouping): select deptno, count (*) from EMP where Sal > 15000 group by deptno;
- Use deptno grouping to query the number of records in each group, provided that the record wage in each group is greater than 15000 (the condition before grouping), and the record number is greater than or equal to 2 (the condition after grouping): select deptno, count (*) from EMP where Sal > 15000 group by deptno having count (*) > = 2;
limit clause dialect
The limit clause is the dialect of mysql, which is the language peculiar to mysql. It is used to limit the number of initial rows and total rows of query results.
- The fifth line of query Initiation Behavior queries three rows of records: select * from emp limit 4,3;, where 4 represents the fifth line, the first line starts from 0, and 3 represents a total of three records queried.
This function is very convenient for paging queries.
Writing order of keywords
The order of keyword writing and execution for all keywords used is: select, from, where, group by, have, order by
constraint
Primary key constraints are related to them
When a column of a table is designated as the primary key, it cannot be null (non-null), duplicate (uniqueness), and can be referenced (foreign key).
Define the primary key PRIMARY KEY, the primary key self-growth AUTO_INCREMENT, a column of non-empty NOT NULL, uniquely constrained NOT NULL UNIQUE
CREATE TABLE stu( sid INT PRIMARY KEY AUTO_INCREMENT, //Define primary keys, self-growth sname VARCHAR(20) NOT NULL UNIQUE, //Define name uniqueness (no duplication) age INT NOT NULL, //Defining age is not empty gender VARCHAR(10) );
When modifying the table, specify the primary key: ALTER TABLE stu ADD PRIMARY KEY (sid);
Set primary key self-growth when modifying table: ALTER TABLE stu CHANGE Sid INT AUTO_INCREMENT;
When modifying tables, deleting primary keys increases: ALTER TABLE stu CHANGE Sid INT;
Delete primary key: ALTER TABLE stu DROP PRIMARY KEY;
Establish many-to-one foreign key constraints
Statement of Employees(
Employee Number, Primary Key
Employee's name,
. . . .
Employee Department Number (Value 10, 20, 30, null)
CONSTRAINT fk_Employee Form_Departmental Form FOREIGN KEY (Employee Departmental Number) REFERENCES Departmental Form (Departmental Number); //Add Foreign Key Constraints
);
Department chart(
Department Number, Primary Key
Department name
)；As shown in the above code, the relationship between employees and their departments is many-to-one. The section in the staff table is the external key of the staff table.
A foreign key must be the primary key value of a table (either another table or this table) that refers to the primary key. Foreign key content can be duplicated and empty. A table can have multiple foreign keys.
The value of the foreign key must be within the range of the primary key value of the Department table. The value that the primary key of the Department table does not have can not appear, otherwise the error will be reported.
Add foreign key constraints when modifying the table: ALTER TABLE employee table ADD CONSTRAINT fk_employee table_department table FOREIGN KEY (employee Department number) REFERENCES department table (department number);
Delete foreign key constraints when modifying tables: ALTER TABLE employee table DROP FOREIGN KEY fk_employee table_department table;
Establishing one-to-one foreign key constraints
The primary key of one of the tables is the primary key and the foreign key.
Husband's List(
Husband number, primary key
Husband's name,
);
Wife List(
Wife Number, Primary Key + Foreign Key
Wife's name
CONSTRAINT fk_Wife Table_Husband Table FOREIGN KEY (Wife Number) REFERENCES Husband Table (Husband Number); //Add Foreign Key Constraints
)；As shown in the code above, husband and wife are one-to-one relationships. The wife number in the wife table is both the primary key and the foreign key.
Establishment of multiple-to-multiple foreign key constraints
You need to use three tables, two foreign keys in the middle table, and refer to the primary keys of the other two tables.
Student table(
Student Number, Primary Key
Name of student
);
Teacher list(
Teacher Number, Primary Key
Name of Teacher
);
Intermediate table(
Intermediate table student number,
Intermediate table teacher number
CONSTRAINT fk_student table_teacher table_add student foreign key FOREIGN KEY (middle table student number) REFERENCES student table (student number), //add foreign key constraint
CONSTRAINT fk_student table_teacher table_add teacher foreign key FOREIGN KEY( middle table teacher number)REFERENCES teacher table(teacher number)//add foreign key constraint
);As in the above code, the relationship between students and teachers is many-to-many. Each record in the middle table illustrates the relationship between students and teachers.
multi-table query
join query
Internal connection
/* Dialect, unique to MySQL Search for employee's name, salary and department's name */ SELECT e.ename,e.sal,d.dname FROM emp AS e,dept AS d WHERE e.deptno=d.deptno; /* Standards are recommended for future use Search for employee's name, salary and department's name */ SELECT e.ename,e.sal,d.dname FROM emp AS e INNER JOIN dept AS d ON e.deptno=d.deptno;
As can be seen from the above execution, there is no Zhang San in the name of the person inquired, because Zhang San's department is 50, and there is no 50 department in the Department table. If you want to display Zhang San and NULL for the name of the corresponding department, you need an external connection.
External connection
/* Left link, first query the left table (mainly left table), and then query the right table, the right table to meet the conditions of the display, not satisfied with the conditions of the display NULL */ SELECT e.ename,e.sal,d.dname FROM emp AS e LEFT OUTER JOIN dept d ON e.deptno=d.deptno ; /* Right link, first query the right table (mainly the right table), then query the left table, the left table to meet the conditions of display, not meet the conditions of display NULL */ SELECT e.ename,e.sal,d.dname FROM emp AS e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno ; /* External connection SELECT e.ename,e.sal,d.dname FROM emp AS e FULL OUTER JOIN dept d ON e.deptno=d.deptno ; But MySQL database does not support it and can be solved by UNION ALL. */ SELECT e.ename,e.sal,d.dname FROM emp AS e LEFT OUTER JOIN dept d ON e.deptno=d.deptno UNION ALL SELECT e.ename,e.sal,d.dname FROM emp AS e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno ;
As shown in the code above, there are left and right links, first query the left table (mainly left table), then query the right table, the right table to meet the conditions of the display NULL, for example, Zhang San in the staff, first query Zhang San, then the right table (department table) does not have 50 departments, so display NULL. Right link, first query the right table (mainly the right table), then query the left table, the left table to meet the conditions of the display, not satisfied with the conditions of the display NULL, such as department table has a 40 financial department, first query out, but the left table (employees) there is no 40 Department staff, so the left side is shown as NULL.
Subquery
Subqueries are nested queries, and select contains multiple selections. The location of the sub-query appears after where, as a part of the condition; after from, as a table to be queried. When the sub-query appears after where as a condition, you can use any, all keywords.
The results set of the sub-query is in the form of single row, single row, multi-column, multi-row and multi-column.
/* Subquery Case 1: Employees whose salary is higher than Guanyu's Subqueries are used as conditions. Form is single row, single column The sub-condition is Guan Yu's salary. */ SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename="Guan Yu") ; /* Subquery Case 2: Employee Information with Salaries Over 30 Departments Subqueries are used as conditions. In the form of multiple rows and single columns The sub-condition is the salary of the owner of 30 departments. */ SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30) ; /* Subquery Case 3: Employees with exactly the same job and salary as Yin Tianzheng Subqueries are used as conditions. In form of single row and multiple columns The sub-condition is Yin Tianzheng's work and salary. */ SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename="Yin Tianzheng"); /* Subquery Case 4: Query Employee Name, Department Name, Employee Wage and Department Address with Employee Number 1006 Subqueries act as tables. In the form of multiple rows and columns The sub-condition is the name and address of the Department in the Department table. As a condition of removing Cartesian product, the department number must also be queried. */ SELECT e.ename,e.sal,d.dname,d.loc FROM emp AS e , (SELECT dname ,loc ,deptno FROM dept) AS d WHERE e.deptno=d.deptno AND e.empno=1006 ;