08.DQL语句基本操作

1.查询操作

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

查询所有数据的所有列
select * from stu;

查询所有数据的sid,sname列
select sid,sname from stu;

查询age>20的数据
select * from stu where age >20;

查询学号为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开头的学⽣信息。
select * from stu where sname like 'S%';

姓名中以s开头，并且只有5个字符的学⽣信息（方便查看_用空格隔开）
select * from stu where sname like 's_ _ _ _';

练习2

创建表emp

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

去重，sal不重复显示两个3000
select distinct sal from emp;

薪⽔上浮50%，查询员工工资（as可以省略）
select ename as 员工姓名,sal * 1.5 as 提薪后的薪水 from emp;
select ename, sal + IFNULL(comm,0) 总薪水 from emp;			//查询总薪水
select * from emp order by sal desc,ename asc;				//根据薪水降序，同薪水按名字正序（asc可省略）

2.函数的使用

聚合函数：max()、 min() 、count() 、sum() 、avg()

查询总工资
select sum(sal) from emp;

查询总人数，（1）中 1 可以随便写
select count(1) from emp;

平均工资
select avg(sal) from emp;

查所用工资的总数， IFNULL（comm,0） 是把comm中是NULL的数据改为0
select sum(sal + IFNULL(comm,0) )  from emp;

3.分组

按depton（部门）分组，显示最高工资
select deptno,max(sal) from emp group by deptno;

having是和group by一起出现的，相当于where
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

4.分页

数据库行数是从0开始的，下面表示从第5行开始一共打印6行
select * from emp limit 4,6;

5、SQL的顺序

1，书写顺序
select .... from 表名 where .... group by ... having .... order by ... limit ...

2） 执行顺序（执行引擎）
from 表名 where .... group by... having ... select ... order by ... limit....

6.多表查询

创建表dept

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 emp,dept;
所看到的结果就是笛卡尔集，14*4一共56条数据

内连接

左边的表 和 右边的表，如果条件能满足，就显示 左右两个表的数据，如果关联不上，就左表和右表都不显示

select * from emp inner join dept on emp.deptno = dept.deptno;

外连接

左连接，左外连接的意思是：以左表为主（左表的数据要全部显示出来），以右表为辅（能关联上的显示出来，关联不上的显示null）。
select * from emp left outer join dept on emp.deptno = dept.deptno;

右连接
select * from emp right outer join dept on emp.deptno = dept.deptno;

如果表名特别长，需要巧妙的使用别名

select * from emp e left join dept d on e.deptno=d.deptno;

！！！！！错误