MySQl 表基本查询操作

MySQl 表基本查询操作

一、语法

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