MySQL Table basic Query

Source: Internet
Author: User
Tags create database

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.