数据库学习笔记二

五、DQL操作(查询)

数据库执行DQL语句不会对数据进行改变,而是数据库发送结果集个客户端,查询返回结果集是一盏虚拟表.
语法:

Select 列名 from表名[where --group by—having—order by];

解释：

1. Select 列名 要查询的列
2. From 表名 要查询的表
3. Where 条件 条件查询
4. Group by 分组 对结果分组
5. Having 条件 在分组后做条件
6. Order by 排序

学生表：stu
sid char(6) 学生学号
sname varchar(50) 学生姓名
age int 学生年龄
gender varchar(50) 学生性别

创建学生表并插入数据：

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);

雇员表：emp
empno int 员工编号
ename varchar(50) 员工姓名
job varchar(50) 员工工作
mgr int 领导编号
hiredate date 入职日期
sal decimal(7,2) 月薪
comm decimal(7,2) 奖金
deptno int 部门编号

创建员工表并插入数据：

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);

部门表：dept
deptno int 部门编码
dname varchar(50) 部门名称
loc varchar(50) 部门所在地点

创建部门表并插入数据：

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');

基础查询：

查询所有学生

SELECT * FROM stu;

查询所有学生的名字

SELECT sname FROM stu;

条件查询：查询的时候在WHERE后面跟一些子句

等于= 不等于：!= 和 <> ，小于：<，小于等于： <=，大于： >，大于等于： >=
Between…and 在a和b之间
,in 在几个范围之内，
Is null：空，
is not null：不为空
and，on，not…

查询性别为女并且年龄在小于50的记录

SELECT * 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 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');

查询年龄为NULL的记录

SELECT * FROM stu WHERE age='null';

查询年龄在20-40之间的记录

SELECT * FROM stu WHERE age >=20 AND age<=40;

查询性别为非男的记录

SELECT * FROM stu WHERE gender !='male';

查询姓名不为NULL的记录

SELECT * FROM stu WHERE sname!='null';

模糊查询：

当想查询什么中包含某字母的时候就需要模糊查询
通配符：
_:表示任意字符 ，%：表示0-n个字符

查循学生表中有5个字母构成的学生记录

SELECT * FROM stu WHERE sname LIKE '_____';

查询姓名为5个字母构成，并且第五个字母为i的记录

SELECT * FROM stu WHERE sname LIKE '___i';

查询姓名以z开头的学生记录

SELECT * FROM stu WHERE sname LIKE 'z%';

查询姓名中第二个字母为i的学生记录

SELECT * FROM stu WHERE sname LIKE '_i%';

查询姓名中包含a字母的学生记录

SELECT * FROM stu WHERE sname LIKE '%a%';

字段控制查询：

去除一些重复记录，
查看一些结果和 ，
给查询的结果起别名

查看部门表中的部门编号记录并将重复的数据去除

SELECT DISTINCT deptno FROM emp;

查看员工表中月薪和佣金之和

SELECT ename,sal+IFNULL(comm,0)FROM emp;

查看员工表中月薪和佣金之和并给结果起别名

SELECT ename,sal+IFNULL(comm,0) 实发工资 FROM emp;
SELECT ename,sal+IFNULL(comm,0) AS 实发工资 FROM emp;

排序：

ORDER BY ASC升序 DESC降序

查询所有学生记录，按年龄升序排序

SELECT * FROM stu ORDER BY age ASC;

查询所有学生记录，按年龄降序排序

SELECT * FROM stu ORDER BY age DESC;

查询所有员工按照月薪降序排列，如果月薪相同按员工编号升序排列

SELECT emp.*，sal FROM emp ORDER BY sal DESC , empno ASC;

聚合函数：

聚合函数是用来做纵向运算的函数
COUNT()统计指定列不为NULL的记录行数
MAX()计算指定列的最大值
MIN()计算指定列的最小值
SUM()指定列的数值和
AVG()平均值

查询emp表中有多少条记录

SELECT COUNT(*) FROM emp;

查询emp表中有佣金的人数

SELECT COUNT(comm) FROM emp;
SELECT COUNT(*) FROM emp WHERE comm IS NOT NULL;

查询emp表中月薪大于2500的人数

SELECT COUNT(*) FROM emp WHERE sal>2500;

统计月薪与佣金之和大于2500元的人数

SELECT COUNT(*) FROM emp WHERE sal+IFNULL(comm,0)>2500;

查询有佣金的人数，以及有领导的人数

SELECT COUNT(comm),COUNT(mgr) FROM emp;