SELECt [ALL | DISTINCT]
{ * | table.* | [ table.field1 [ as alias1] [, table.field2 [as alias2]][, …]] }
FROM table_name [ as table_ alias ]
[ left|out|inner join table_name2 ] #联合查询
[ WHERe … ] #指定结果需满足的条件
[ GROUP BY … ] #指定结果按照哪几个字段来分组
[ HAVINg … ] #过滤分组的记录必须满足的次要条件
[ ORDER BY… ] #指定查询记录按一个或者多个条件排序
[ LIMIT { [ offset,] row_count | row_count OFFSET offset } ] ;
#指定查询的记录从哪条至哪条
[] 括号代表可选的；
{} 括号代表必须的；
# MySQL语句中的注释符，也可以用
下面案例所用数据库：
CREATE DATABASE IF NOT EXISTS `myStudent`;
use myStudent;
CREATE TABLE stu (
sid CHAr(6),
sname VARCHAr(50),
age INT,
gender VARCHAr(50)
);
INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);
CREATE TABLE emp(
empno INT,
ename VARCHAr(50),
job VARCHAr(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT
) ;
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
CREATE TABLE dept(
deptno INT,
dname VARCHAr(14),
loc VARCHAr(13)
);
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES(40, 'OPERATIONS', 'BOSTON');
二、基础查询
-- 查询stu表中的所有字段 select `sid`,`sname`,`age`,`gender` from `stu`; select * from `stu`; -- 查询学生表中的指定列 select `sid`, `sname` from `stu`;三、条件查询
-- 查询某某记录=查询莫一条数据的所有字段
-- 查询性别为女，并且年龄大于50的记录
select * from `stu` where `gender`='female' and age > 50;
-- 查询性别为女，并且年龄大于50的员工编号和姓名
select `sid`,`sname`from `stu` where `gender`='female' and `age` > 50;
-- 查询学号为S_1001, 或者姓名为lisi的记录
SELECt * FROM `stu` WHERe `sid`='S_1001' OR `sname`='lisi';
-- 查询学号为S_1001, S_1002, S_1003的记录
select * from `stu` where `sid`='S_1001' or `sid`='S_1002' or `sid`='S_1003';
select * from `stu` where `sid` in ('S_1001', 'S_1002', 'S_1003');
-- 查询学号不是S_1001, S_1002, S_1003的记录
select * from `stu` where `sid` not in ('S_1001', 'S_1002', 'S_1003');
select * from `stu` where `sid`!='S_1001' and `sid`!='S_1002' and `sid`!='S_1003';
-- 查询年龄为null的记录
select * from stu where age is null;
-- 查询年龄在20到40之间的学生记录
select * from stu where age>=20 and age<=40;
select * from stu where age between 20 and 40;
-- 查询性别非男的学生记录
select * from stu where gender = 'female';
select * from stu where gender != 'male';
select * from stu where gender <> 'male';
select * from stu where not gender = 'male';
-- 查询姓名不为null的学生记录
select * from stu where sname is not null;
select * from stu where not sname is null;
四、字段控制查询
-- 去除重复记录 DISTINCT select sal from emp; select distinct sal from emp; -- 查看雇员的月薪与佣金之和 注意：一个人数与NULL进行运算, 结果为NULL select *, sal + comm from emp; select *, sal + IFNULL(comm,0) from emp; -- 给列名添加别名 AS , AS关键字可以省略不写 select *, sal + IFNULL(comm,0) as '月薪和佣金之和' from emp; select *, sal + ifnull(comm,0) '月薪和佣金之和' from emp; -- 排序 ORDER BY ASC升序 (默认排序) DESC降序 SELECt * FROM stu; -- 查询所有学生记录，按年龄升序排序 SELECt * FROM stu ORDER BY age ASC; SELECt * FROM stu ORDER BY age; -- 查询所有学生记录，按年龄降序排序 SELECt * FROM stu ORDER BY age DESC; -- 查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序 SELECt * FROM emp ORDER BY sal DESC, empno ASC; -- 查询工资高于1600的员工记录，并按照薪资进行降序排序 SELECt * FROM emp WHERe sal>1600 ORDER BY sal;五、聚合函数
聚合函数是用来做纵向运算的函数。
-- COUNT():统计指定列不为NULL的记录行数; SELECt COUNT (comm) AS '有 佣金的人数' FROM emp; SELECt COUNT (mgr) AS '有领导的员工数目' FROM emp; SELECt COUNT(*) AS '数据数目' FROM emp; -- MAX():计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算; SELECt MAX(sal) AS '最高工资' FROM emp; -- MIN():计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运 SELECt MIN(sal) AS '最低工资' FROM emp; -- SUM():计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0; SELECt SUM(sal) AS '员工工资总和' FROM emp; -- AVG():计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0; SELECt AVG(sal) AS '员工平均工资' FROM emp; -- 查询emp表中月薪大于2500的人数 SELECt COUNT(*) '月薪大于2500的人数' FROM emp WHERe sal>2500; -- 统计月薪与佣金之和大于2500元的人数 SELECt COUNT(*) '月薪与佣金之和大于2500元的人数' FROM emp WHERe sal + IFNULL (comm, 0) >2500; -- 查询所有雇员月薪+佣金和 SELECt SUM(sal + IFNULL (comm, 0)) FROM emp;
六、练习
https://www.jianshu.com/p/dffd40d06e3c?u_atoken=07fb4814-9f89-40ad-b23e-897c216bee9e&u_asession=01UP_Wi73p_BVLYsCfTCSEdbYw_v5blNLAeSQhWUMmOr-PZIvbpk7S8XGy_huvO1NyX0KNBwm7Lovlpxjd_P_q4JsKWYrT3W_NKPr8w6oU7K-0XUgKz4MZ0JDbGmbp_SSePpcarp92QKzyJKyYjREPlmBkFo3NEHBv0PZUm6pbxQU&u_asig=05AuiogwoKKcxKt5qAOOwfnnIAHQuXQ39RkgNJOe3QBB0e88VyI5f8y7GNTAxG3W15VYN4HuB2C4d91e67E2hAdKZ-RK20KxGbSzkURVb_11u0eLAY58ygUaCYYvNivpRFSARnIk7VMzhosg5I_bnYeWQnjeeYQqHIwtb1Ds0YIz_9JS7q8ZD7Xtz2Ly-b0kmuyAKRFSVJkkdwVUnyHAIJzeNEQHAPE-xmjkhwgqKOBU3u-hUkfsLe0CyJdKl0zbgIWPRPQyB_SKrj-61LB_f61u3h9VXwMyh6PgyDIVSG1W_wSf37HFzc2m4rYsXdFoAUZdiyqXEaeAXsOo3ZA-nUomCAi-zR8Dtn0cBrAFmoysNZTviOXeWI3PvuRFuRirormWspDxyAEEo4kbsryBKb9Q&u_aref=%2FaE1di2CyxBC9Kbmib25UK%2FA54M%3D
https://www.cnblogs.com/zhangrui0328/p/8985919.html
