MySQL Table basic Query

Section I.

--or # single-line comment
/**
* Multi-line Comment
*/

--C CREATE DATABASE exam
CREATE DATABASE Exam;

Use exam;

/* Create a departmental table */
CREATE TABLE Dept (
Deptnoint PRIMARY KEY,
Dnamevarchar (50),
Loc VARCHAR (50)
);

/* Create an employee table */
CREATE TABLE EMP (
Empnoint PRIMARY KEY,
Enamevarchar (50),
Jobvarchar (50),
Mgrint,
Hiredatedate,
Saldecimal (7,2),
COMM DECIMAL (7,2),
Deptnoint,
CONSTRAINT fk_emp FOREIGN KEY (MGR) REFERENCES EMP (empno)
);

/* Create a salary scale table */
CREATE TABLE Salgrade (
Gradeint PRIMARY KEY,
Losalint,
Hisalint
);

/* Create student Table */
CREATE TABLE Stu (
Sidint PRIMARY KEY,
Snamevarchar (50),
Ageint,
Gandervarchar (10),
Provincevarchar (50),
Tuitionint
);

/* Insert Dept Table Data */
INSERT into Dept VALUES (10, ' Research department ', ' Beijing ');
INSERT into Dept VALUES (20, ' engineering Department ', ' Shanghai ');
INSERT into Dept VALUES (30, ' sales department ', ' Guangzhou ');
INSERT into Dept VALUES (40, ' finance department ', ' Wuhan ');

/*inserting the EMP table data*/
INSERT into EMP VALUES (1009, ' Zeng Aso ', ' 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, ' Diane ', ' Salesperson ', 1006, ' 2001-02-20 ', 16000, 3000, 30);
INSERT into EMP VALUES (1003, ' Yintian positive ', ' salesperson ', 1006, ' 2001-02-22 ', 12500, 5000, 30);
INSERT into EMP VALUES (1005, ' Sheson ', ' Salesperson ', 1006, ' 2001-09-28 ', 12500, 14000, 30);
INSERT into EMP VALUES (1010, ' wei-smile ', ' salesperson ', 1006, ' 2001-09-08 ', 15000, 0, 30);
INSERT into EMP VALUES (1012, ' Cheng ', ' 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, ' Jacky Chow ', ' clerk ', 1008, ' 2007-05-23 ', 11000, NULL, 20);

INSERT into EMP VALUES (1001, ' ganning ', ' clerk ', 1013, ' 2000-12-17 ', 8000, NULL, 20);

/* 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 ', ' 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 Yong ', ' 25 ', ' Male ', ' Beijing ', ' 1500 ');
INSERT into ' Stu ' VALUES (' 5 ', ' beautiful ', ' 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 ', ' tat ', ' 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 ', ' Lee along ', ' 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 ', ' Tangli ', ' 21 ', ' Female ', ' Beijing ', ' 1600 ');
INSERT into ' Stu ' VALUES (' 19 ', ' Ah Yong ', ' 23 ', ' Male ', ' Beijing ', ' 3500 ');
INSERT into ' Stu ' VALUES (' 20 ', ' Tangling ', ' 23 ', ' Female ', ' Guangzhou ', ' 2500 ');
INSERT into ' Stu ' VALUES (' 21 ', ' Syria ', ' 18 ', ' Male ', ' Shanxi ', ' 3500 ');
INSERT into ' Stu ' VALUES (' 22 ', ' King tired ', ' 23 ', ' Male ', ' Hubei ', ' 4500 ');
INSERT into ' Stu ' VALUES (' 23 ', ' Zhao An ', ' 23 ', ' Male ', ' Beijing ', ' 1500 ');
INSERT into ' Stu ' VALUES (' 24 ', ' Guanle ', ' 25 ', ' Male ', ' Liaoning ', ' 2500 ');
INSERT into ' Stu ' VALUES (' 25 ', ' Lee word ', ' 22 ', ' Male ', ' Beijing ', ' 3500 ');
INSERT into ' Stu ' VALUES (' 26 ', ' Syria ', ' 25 ', ' Male ', ' Beijing ', ' 1500 ');
INSERT into ' Stu ' VALUES (' 27 ', ' Chen Hao Hard ', ' 23 ', ' Female ', ' Beijing ', ' 1000 ');
INSERT into ' Stu ' VALUES (' 28 ', ' Chen ', ' 22 ', ' Female ', ' Shandong ', ' 2500 ');
INSERT into ' Stu ' VALUES (' 29 ', ' Sun-hee ', ' 21 ', ' Female ', ' Beijing ', ' 1600 ');
INSERT into ' Stu ' VALUES (' 30 ', ' Li State ', ' 23 ', ' Male ', ' Beijing ', ' 3500 ');
INSERT into ' Stu ' VALUES (' 31 ', ' Zhang Na ', ' 23 ', ' Female ', ' Guangzhou ', ' 2500 ');
INSERT into ' Stu ' VALUES (' 32 ', ' peacefully ', ' 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 ', ' Guanle ', ' 25 ', ' Male ', ' Liaoning ', ' 2500 ');
INSERT into ' Stu ' VALUES (' 36 ', ' Wu Qiang ', ' 22 ', ' Male ', ' Beijing ', ' 3500 ');
INSERT into ' Stu ' VALUES (' 37 ', ' Wu Hachou ', ' 25 ', ' Male ', ' Beijing ', ' 1500 ');
INSERT into ' Stu ' VALUES (' 38 ', ' just small and ', ' 23 ', ' Female ', ' Beijing ', ' 1000 ');
INSERT into ' Stu ' VALUES (' 39 ', ' eurodisplay ', ' 22 ', ' Female ', ' Shandong ', ' 2500 ');
INSERT into ' Stu ' VALUES (' 40 ', ' von contained ', ' 21 ', ' Female ', ' Beijing ', ' 1600 ');
INSERT into ' Stu ' VALUES (' 41 ', ' Chen ', ' 23 ', ' Male ', ' Beijing ', ' 3500 ');
INSERT into ' Stu ' VALUES (' 42 ', ' Guanling ', ' 23 ', ' Female ', ' Guangzhou ', ' 2500 ');
INSERT into ' Stu ' VALUES (' 43 ', ' pack Lee ', ' 18 ', ' Male ', ' Shanxi ', ' 3500 ');
INSERT into ' Stu ' VALUES (' 44 ', ' Wei Gang ', ' 23 ', ' Male ', ' Hubei ', ' 4500 ');
INSERT into ' Stu ' VALUES (' 45 ', ' tat ', ' 23 ', ' Male ', ' Beijing ', ' 1500 ');
INSERT into ' Stu ' VALUES (' 46 ', ' Zhang Guanlei ', ' 25 ', ' Male ', ' Liaoning ', ' 2500 ');
INSERT into ' Stu ' VALUES (' 47 ', ' Send Xiao Qiang ', ' 22 ', ' Male ', ' Beijing ', ' 3500 ');
INSERT into ' Stu ' VALUES (' 48 ', ' Closed forest ', ' 25 ', ' Male ', ' Beijing ', ' 1500 ');
INSERT into ' Stu ' VALUES (' 49 ', ' Sue Dumb ', ' 23 ', ' Female ', ' Beijing ', ' 1000 ');
INSERT into ' Stu ' VALUES (' 50 ', ' zhaoning ', ' 22 ', ' Female ', ' Shandong ', ' 2500 ');
INSERT into ' Stu ' VALUES (' 51 ', ' Li Chen ', ' 21 ', ' Female ', ' Beijing ', ' 1600 ');
INSERT into ' Stu ' VALUES (' 52 ', ' Qianxiaogang ', ' 23 ', ' Male ', ' Beijing ', ' 3500 ');
INSERT into ' Stu ' VALUES (' 53 ', ' Arryn ', ' 23 ', ' Female ', ' Guangzhou ', ' 2500 ');
INSERT into ' Stu ' VALUES (' 54 ', ' Guo Lin ', ' 18 ', ' Male ', ' Shanxi ', ' 3500 ');
INSERT into ' Stu ' VALUES (' 55 ', ' Week strong ', ' 23 ', ' Male ', ' Hubei ', ' 4500 ');

--1 Basic query
--1.1 Querying all columns

SELECT * from Stu;

--1.2 Querying the Stu table for the specified column SID, Sname, age

SELECT SID, Sname, age from Stu;

--2 Conditional query
--2.2 query gender for female, and age 50 record
SELECT * from Stu WHERE gender= ' female ' and ge<50;

--2.3 Inquiry number is 1, or the name is King's record
SELECT * from stu WHERE sid = ' 1 ' OR sname= ' king ';

--2.4 Records of the study number
SELECT * from Stu WHERE SID in (' 1 ', ' 2 ', ' 3 ');

--2.5 Inquiry number is not a record of
SELECT * from Tab_student WHERE s_number not in (' 1 ', ' 2 ', ' 3 ');

--2.6 Check the age of 22 records
SELECT * from Stu WHERE age is 22;

--2.7 Check the student record of age 20 to 24
SELECT * from Stu WHERE age>=20 and age<=24;
-OR
SELECT * from Stu WHERE age between and 24;
-OR
SELECT * from Stu WHERE age (20,21,22,23,24);

--2.8 gender-non-male student records
SELECT * from Stuwhere gender!= ' male ';
-OR
SELECT * from Stuwhere gender<> ' male ';
-OR
SELECT * from Stuwhere not gender= ' male ';
-OR
SELECT * from Stuwhere gender= ' female ';

--2.9 Check for student records whose names are not null
SELECT * from Stu WHERE isn't sname is NULL;
-OR
SELECT * from Stu WHERE sname are not NULL;

--3 Fuzzy query
--3.1 Check the student record of the name consisting of 5 letters
SELECT * from Stu WHERE sname like ' _____ ';

--3.2 Query names are made up of 5 letters, and the 5th letter is "I" Student records
SELECT * from Stu WHERE sname like ' ____i ';

--3.3 Check the student records whose names begin with "Zhang"
SELECT * from Stu WHERE sname like ' Zhang% ';

--3.4 A student record with the 2nd letter "small" in the name
SELECT * from Stu WHERE sname like ' _ Small% ';

--3.5 student records with "Lai" letters in their names
SELECT * from Stu WHERE sname like '% li% ';

--4 Field control query
--4.1 removal of duplicate records
SELECT DISTINCT sal from EMP;

--4.2 Viewing the sum of the employee's monthly salary and commission
SELECT *,sal+comm from EMP;

--The Comm column has many records with a value of NULL, because anything with null adds the result or null, so the settlement result may appear null. The following function ifnull is used to convert null to value 0:
SELECT *,sal+ifnull (comm,0) from EMP;

---4.3 add an alias to a column name
--The column named Sal+ifnull (comm,0) appears in the above query, which is very ugly, now we give this column an alias, for total:

SELECT *, Sal+ifnull (comm,0) as total from EMP;

--When you alias a column, you can omit the AS keyword:
SELECT *,sal+ifnull (comm,0) total from EMP;

--5 sort order BY
--5.1 Query All student records, sorted by age ascending
SELECT * from Stu ORDER by Sage ASC;
-OR
SELECT * from Stu ORDER by Sage;

--5.2 Query All student records, sorted by age descending
SELECT * from Stu ORDER by age DESC;

--5.3 Query all employees, in descending order of monthly salary, if the monthly salary is the same, in ascending order by number
SELECT * from Emporder by Sal Desc,empno ASC;

--6.1 COUNT
--Query the number of records in the EMP table:
SELECT COUNT (*) as CNT from EMP;

--Check the number of people who have commissions in the EMP table:
SELECT COUNT (comm) CNT from EMP;

--Query the number of people with a monthly wage greater than 2500 in the EMP table:
SELECT COUNT (*) from empwhere sal > 2500;

--The number of people with a monthly salary and commission of more than 2500 yuan:
SELECT COUNT (*) as CNT from EMP WHERE sal+ifnull (comm,0) > 2500;

--Check the number of people who have commissions and the number of leaders:
SELECT count (Comm), COUNT (Mgr) from EMP;

--6.2 Sum and AVG
--Check the monthly salary of all employees and:
SELECT SUM (SAL) from EMP;

--Query all employees monthly and, and all employee commissions and:
SELECT sum (SAL), SUM (comm) from EMP;

--Check all employees ' monthly salary + Commission and:
SELECT SUM (Sal+ifnull (comm,0)) from EMP;

--statistics on average salary of all employees:
SELECT SUM (SAL), COUNT (SAL) from EMP;
--or
SELECT AVG (SAL) from EMP;

--6.3 Max and Min
--Check the maximum wage and minimum wage:
SELECT MAX (SAL), MIN (SAL) from EMP;

--7.1 Packet Query
--Query the department number of each department and the salary of each department and:
SELECT Deptno, SUM (SAL) from the EMP GROUP by Deptno;

--Query the department number of each department and the number of each department:
SELECT Deptno,count (*) from the EMP GROUP by Deptno;

--Query the department number of each department and the number of people who pay more than 1500 per department:
SELECT Deptno,count (*) from the EMP WHERE sal>1500 GROUP by Deptno;

--7.2 HAVING clause
--Query the department number with the sum of wages greater than 9000 and the salary and:
SELECT deptno, sum (SAL) from the EMP GROUP by Deptno have SUM (SAL) > 9000;

--8.1 query 5 rows of records, starting line starting from 0
SELECT * from emp LIMIT 0, 5;

--8.2 Query 10 rows of records, starting from 3.
SELECT * from EMP LIMIT 3, 10;

8.3 Paging Query rules
(Number of pages-1) * Number of rows = The first display of the second page. Write a few lines from the beginning. (preceded by the number of start lines, followed by the number of rows displayed)
8.3 Paged Query
Query Statement Writing order: Select–from-where-group By-having-order By-limit
Query statement Execution order: From-where-group By-having-select-order By-limit

MySQL Table basic Query