1 DQL操作
DQL数据查询语言:数据库执行DQL语句不会对数据进行改变,而是让数据库发送结果集给客户端。查询返回的结果集是一张虚拟表。
查询关键字:SELECT
语法:SELECT 列名 FROM 表名 【WHERE-->GROUP BY-->HAVING-->ORDER BY-->LIMIT】
举例:创建学生表stu
#创建表stu
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);
创建员工表empp
#创建表empp
CREATE TABLE `empp`(
`empno` INT,
`ename` VARCHAR(50),
`job` VARCHAR(50),
`mgr` INT,
`hiredate` DATE,
`sal` DECIMAL(7,2),
`comm` DECIMAL(7,2),
`deptno` INT);
#插入数据
INSERT INTO empp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO empp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO empp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO empp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO empp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO empp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO empp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO empp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO empp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO empp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO empp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO empp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO empp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO empp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
2 基础查询
2.1 查询所有列
语法:SELECT * FROM 表名;*表示所有列
如查询stu表的所有列结果:SELECT * FROM stu;
2.2 查询指定列
语法:SELECT 列名1,列名2,... FROM 表名;
如查询stu表中的sname和age字段:SELECT `sname`,`age` FROM stu;
3 条件查询
条件查询就是在查询时给出WHERE子句,在WHERE子句中可以使用如下运算符及关键字。
运算符
=、!=、<>、<、<=、>、>=
关键字
BETWEEN...AND | 在。。。和。。。之间 |
IN(set) | 在。。。之中 |
IS NULL | 为空 |
AND | 与 |
OR | 或 |
NOT | 非 |
如:查询学号为S_1001,S_1002,S_1003的记录
SELECT * FROM stu WHERE sid IN('S_1001','S_1002','S_1003');
4 模糊查询
在WHERE子句中,使用关键字LIKE进行模糊查询。
通配符:
%,表示匹配0或任意多个字符
_,表示匹配单个字符
如:查询姓名由5个字母构成的学生记录
SELECT * FROM stu WHERE 'sname' LIKE '_____';#其中"_"匹配任意一个字母,5个“_”表示5个任意字母
如:查询姓名中第2个字母为“i”的学生记录
SELECT * FROM stu WHERE sname LIKE '_i%';#其中%匹配0~n个任意字母
5 字段控制查询
5.1 去除重复记录
DISTINCT关键字:去掉SELECT查询返回的记录结果中重复的记录。
如:查询empp表中所有员工薪资(去掉重复记录)。
SELECT DISTINCT sal FROM empp;
5.2 IFNULL()函数
任何东西和NULL进行算术运算,结果都为NULL。这种计算是没有意义的,可以使用IFNULL()函数过滤。
IFNULL(字段名,数据)函数:判断指定字段的字段值是否为null,如果是,将其转换为指定的数据,如果不为null,则不做转换,直接使用字段值。
如:查看雇员的月薪与佣金之和。
SELECT *,sal+IFNULL(comm,0) FROM empp;
5.3 AS子句
AS子句作用:给列名取一个新别名 ;给表取一个新别名;可把经计算或总结的结果用另外一个新名称来代替。
SELECT *,sal+IFNULL(comm,0) AS '月薪和佣金之和' FROM empp;
6 排序
排序关键字:ORDER BY 。ASC表示升序,DESC表示降序。
#使用order by 进行排序,升序ASC(升序是系统默认,可不写),降序DESC
#查询所有学生记录,按年龄升序排序
SELECT * FROM stu ORDER BY age ASC;
SELECT * FROM stu ORDER BY age;
#查询所有学生记录,按年龄降序排序
SELECT * FROM stu ORDER BY age DESC;
7 聚合函数(sum avg max min count)
聚合函数是用来做纵向运算的函数:
COUNT():统计指定列不为NULL的记录行数;
MAX(): 计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;
MIN():计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算;
SUM():计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;
AVG():计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0;
7.1 COUNT()函数
#查询empp表中记录数
SELECT COUNT(*) AS '该表中数据数量'FROM empp;
#查询emp表中有佣金的人数:
SELECT COUNT(comm) AS '有佣金的人数' FROM empp;
#查询emp表中月薪大于2500的人数
SELECT COUNT(sal) AS '月薪大于2500的人数' FROM empp WHERE sal>2500;
#统计月薪与佣金之和大于2500元的人数
SELECT COUNT(*) AS '月薪与佣金之和大于2500元的人数'FROM empp WHERE sal+IFNULL(comm,0)>2500;
#查询有佣金的人数,以及有领导的人数
SELECT COUNT(comm) AS '有佣金的人数',COUNT(mgr)AS'有领导的人数'FROM empp;
7.2 SUM()函数和AVG()函数
SELECT SUM(sal) AS'所有雇员月薪和'FROM empp;
#查询所有雇员月薪和,以及所有雇员佣金和
SELECT SUM(sal) AS'所有雇员月薪和',SUM(comm) AS'所有雇员佣金和'FROM empp;
#查询所有雇员月薪+佣金和
SELECT SUM(sal+IFNULL(comm,0)) AS '所有雇员月薪+佣金和'FROM empp;
#统计所有员工平均工资
SELECT AVG(sal) AS '所有员工平均工资'FROM empp;
7.3 MAX()函数和MIN()函数
SELECT MAX(sal) AS '最高工资'FROM empp;
SELECT MIN(sal) AS '最低工资' FROM empp;