本文共 14605 字，大约阅读时间需要 48 分钟。
文章所有内容是对动力节点老杜的SQL教学视频和文档的再次整理，旨在方便查阅！
共三张表，分别是
emp是员工表：mysql> select * from emp; // 从emp表查询所有数据。 +-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+
dept是部门表：
mysql> select * from dept; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+
salgrade 是工资等级表：
mysql> select * from salgrade; +-------+-------+-------+ | GRADE | LOSAL | HISAL | +-------+-------+-------+ | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | +-------+-------+-------+
SQL语句分类
DQL： 数据查询语言（凡是带有select关键字的都是查询语句） DML： 数据操作语言（凡是对表当中的数据进行增删改的都是DML） insert delete update insert 增 delete 删 update 改 这个主要是操作表中的数据data。 DDL： 数据定义语言 凡是带有create、drop、alter的都是DDL。 DDL主要操作的是表的结构。不是表中的数据。 create：新建，等同于增 drop：删除 alter：修改 这个增删改和DML不同，这个主要是对表结构进行操作。 TCL： 是事务控制语言 包括：事务提交：commit; 事务回滚：rollback; DCL： 是数据控制语言。 例如：授权grant、撤销权限revoke....
退出mysql
mysql> exit
查看mysql中有哪些数据库
mysql> show databases; //注意：以分号结尾，分号是英文的分号。
选择使用其中某个数据库
mysql> use test;//表示正在使用一个名字叫做test的数据库。
创建数据库
mysql> create database bjpowernode;
查看某个数据库下有哪些表
mysql> show tables;
查看mysql数据库的版本号
mysql> select version();
查看当前使用的是哪个数据库
mysql> select database();
不看表中的数据，只看表的结构
mysql> desc 表名;//describe缩写为：desc
查看emp表里的所有数据
mysql> select * from emp; // 从emp表查询所有数据。
不看表中的数据，只看表的结构
//desc 表名; desc是describe的缩写，语句中用describe也可。mysql> desc emp;
查询某一个字段
mysql> select dname from dept;
查询两个或者多个字段
//使用逗号隔开“,”mysql> select deptno,dname from dept;
查询所有字段
//第一种方式：可以把每个字段都写上//mysql> select a,b,c,d,e,f... from tablename;//第二种方式：可以使用*mysql> select * from dept;//这种方式的缺点：1、效率低 2、可读性差。在实际开发中不建议，可以自己玩没问题。//你可以在DOS命令窗口中想快速的看一看全表数据可以采用这种方式。
给查询的列起别名
mysql> select deptno,dname as deptname from dept;//注意：只是将显示的查询结果列名显示为deptname，原表列名还是叫：dname//假设起别名的时候，别名里面有空格，怎么办？mysql> select deptno,dname as 'dept name' from dept; //加单引号
查询薪资等于800的员工姓名和编号
mysql> select empno,ename from emp where sal = 800;//此外还有：<>或!= 不等于 < 小于 <= 小于等于 > 大于 >= 大于等于
使用between … and …. 两个值之间, 等同于 >= and <=
mysql> select empno,ename,sal from emp where sal between 2450 and 3000;//使用between and的时候，必须遵循左小右大。between and是闭区间，包括两端的值。
查询哪些员工的津贴/补助为null？
mysql> select empno,ename,sal,comm from emp where comm is null;//is null 为 null（is not null 不为空）//注意：在数据库当中null不能使用等号进行衡量。需要使用is null// 因为数据库中的null代表什么也没有，它不是一个值，所以不能使用// 等号衡量。
查询工作岗位是MANAGER并且工资大于2500的员工信息？（AND的使用）
select empno,ename,job,sal from emp where job = 'MANAGER' and sal > 2500;//and优先级比or高。
查询工作岗位是MANAGER和SALESMAN的员工？（OR的使用）
select empno,ename,jobfromempwhere job = 'MANAGER' or job = 'SALESMAN';
查询工作岗位是MANAGER和SALESMAN的员工？（IN的使用，相当于多个or联用）
select empno,ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';select empno,ename,job from emp where job in('MANAGER', 'SALESMAN');// not in 表示不在这几个值当中的数据。select empno,ename,job from emp where job not in('MANAGER', 'SALESMAN'); like 模糊查询，支持%或下划线匹配
%：匹配任意多个字符 下划线：匹配任意一个字符。//找出名字中含有O的？select empno,ename,jobfromempwhere job = 'MANAGER' or job = 'SALESMAN';//找出名字以T结尾的？select ename from emp where ename like '%T';//找出名字以K开始的？select ename from emp where ename like 'K%';//找出第二个字每是A的？select ename from emp where ename like '_A%';//找出第三个字母是R的？select ename from emp where ename like '__R%';//找出名字中有下划线“_”的？mysql> select name from t_student where name like '%\_%'; // \转义字符。
简单的升序和降序排列
//查询所有员工薪资并升序排序？mysql> select ename,sal from emp order by sal; // 默认是升序！！！mysql> select ename,sal from emp order by sal asc;//查询所有员工薪资并降序排序？mysql> select ename,sal from emp order by sal desc;
多个字段排序
//查询员工名字和薪资，要求按照薪资升序，如果薪资一样的话，再按照名字升序排列。mysql> select ename,sal from emp order by al asc, ename asc; // sal在前，起主导，只有sal相等的时候，才会考虑启用ename排序。
综合案例：
找出工资在1250到3000之间的员工信息，要求按照薪资降序排列。mysql> select ename,sal from emp where sal between 1250 and 3000 order by sal desc;
**总结 **
关键字顺序不能变： select ... from ... where ... order by ... 以上语句的执行顺序必须掌握： 第一步：from 第二步：where 第三步：select 第四步：order by（排序总是在最后执行！）
大小写转换
mysql> select lower(ename) as ename from emp;mysql> select upper(name) as name from t_student;
substr 取子串（substr( 被截取的字符串, 起始下标,截取的长度)）
mysql> select substr(ename, 1, 3) as ename from emp;//注意：起始下标从1开始，没有0.//找出员工名字第一个字母是A的员工信息？//第一种方式：模糊查询mysql> select ename from emp where ename like 'A%';//第二种方式：substr函数mysql> select ename from emp where substr(ename,1,1) = 'A';
concat 字符串的拼接
mysql> select concat(empno,ename) from emp;
length 取长度
select length(ename) enamelength from emp;
trim 去空格
mysql> select * from emp where ename = trim(' KING'); str_to_date 将字符串转换成日期
date_format 格式化日期 format 设置千分位case…when…then…when…then…else…end
//当员工的工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALESMAN的时候，工资上调50%,其它正常。//注意：不修改数据库，只是将查询结果显示为工资上调select ename,job, sal as oldsal, (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal from emp;
round 四舍五入
mysql> select round(1236.567, 0) as result from emp; //保留整数位。mysql> select round(1236.567, 1) as result from emp; //保留1个小数mysql> select round(1236.567, 2) as result from emp; //保留2个小数mysql> select round(1236.567, -1) as result from emp; // 保留到十位。
distinct 去重
mysql> select distinct job from emp;
rand() 生成随机数
mysql> select round(rand()*100,0) from emp; // 100以内的随机数
ifnull 可以将 null 转换成一个具体值
//NULL只要参与运算，最终结果一定是NULL。为了避免这个现象，需要使用ifnull函数。//ifnull函数用法：ifnull(数据, 被当做哪个值)//补助为NULL的时候，将补助当做0再与薪资相加select ename, (sal + ifnull(comm, 0)) * 12 as yearsal from emp;
count 计数
sum 求和 avg 平均值 max 最大值 min 最小值mysql> select max(sal) from emp;mysql> select min(sal) from emp;mysql> select sum(sal) from emp;mysql> select avg(sal) from emp;mysql> select count(ename) from emp;
select ... from ... where ... group by ... having ... order by ...以上关键字的顺序不能颠倒，需要记忆。执行顺序是什么？ 1. from 2. where 3. group by 4. having 5. select 6. order by为什么分组函数不能直接使用在where后面？ select ename,sal from emp where sal > min(sal);//报错。 因为分组函数在使用的时候必须先分组之后才能使用。 where执行的时候，还没有分组。所以where后面不能出现分组函数。 select sum(sal) from emp; 这个没有分组，为啥sum()函数可以用呢？ 因为select在group by之后执行。
例子
//找出每个工作岗位的工资和mysql> select job,sum(sal) from emp group by job;//找出每个部门的最高薪资mysql> select ename,deptno,max(sal) from emp group by deptno;//找出“每个部门，不同工作岗位”的最高薪资mysql> select deptno, job, max(sal) from emp group by deptno, job;
having 对分完组之后的数据进一步过滤。
//having不能单独使用，having不能代替where，having必须和group by联合使用。//找出每个部门最高薪资，要求显示最高薪资大于3000的mysql> select deptno,max(sal) from emp group by deptno having max(sal) > 3000;//思考一个问题：以上的sql语句执行效率是不是低？//比较低，实际上可以这样考虑：先将大于3000的都找出来，然后再分组。mysql> select deptno,max(sal) from emp where sal > 3000 group by deptno;//优化策略：//where和having，优先选择where，where实在完成不了了，再选择having。
什么是连接查询？ 从一张表中单独查询，称为单表查询。 emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字。 这种跨表查询，多张表联合起来查询数据，被称为连接查询。 连接查询的分类？ 根据语法的年代分类： SQL92：1992年的时候出现的语法 SQL99：1999年的时候出现的语法 ,我们这里重点学习SQL99 根据表连接的方式分类： 内连接： 等值连接 、非等值连接、自连接 外连接：左外连接（左连接）、右外连接（右连接）、全连接
内连接之等值连接
//查询每个员工所在部门名称，显示员工名和部门名？ select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno;inner可以省略（带着inner可读性更好！！！一眼就能看出来是内连接）
内连接之非等值连接
//找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？ select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal; // 条件不是一个等量关系，称为非等值连接。
内连接之自连接
//查询员工的上级领导，要求显示员工名和对应的领导名？//技巧：一张表看成两张表:emp a 员工表 & emp b 领导表 select a.ename as '员工名', b.ename as '领导名' from emp a join emp b on a.mgr = b.empno; //员工的领导编号 = 领导的员工编号
外连接
//查询每个员工的上级领导，要求显示所有员工的名字和领导名？ select a.ename as '员工名', b.ename as '领导名' from emp a left outer join emp b on a.mgr = b.empno; // outer是可以省略的，带着可读性强。//内连接：（A和B连接，AB两张表没有主次关系。平等的。）//外连接：A和B连接，AB两张表有主次关系，通过right/left体现//right代表：表示将join右边的表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。//left代表：表示将join左边的表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询右边的表。//带有right的是右外连接，又叫做右连接。//带有left的是左外连接，又叫做左连接。//任何一个右连接都有左连接的写法。//任何一个左连接都有右连接的写法。//外连接的查询结果条数一定是 >= 内连接的查询结果条数
多表连接
//案例：找出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级？ select e.ename,e.sal,d.dname,s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal;//案例：找出每个员工的部门名称以及工资等级，还有上级领导，要求显示员工名、领导名、部门名、薪资、薪资等级？ select e.ename,e.sal,d.dname,s.grade,l.ename from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal left join emp l on e.mgr = l.empno;
where子句中的子查询
//找出比最低工资高的员工姓名和工资？mysql> select ename,sal from emp where sal > (select min(sal) from emp);
from子句中的子查询
//找出每个岗位的平均工资的薪资等级。//技巧：from后面的子查询，可以将子查询的查询结果当做一张临时表。select t.*, s.gradefrom (select job,avg(sal) as avgsal from emp group by job) tjoin salgrade son t.avgsal between s.losal and s.hisal;
select后面出现的子查询
//找出每个员工的部门名称，要求显示员工名，部门名？select e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
union 合并查询结果集
//查询工作岗位是MANAGER和SALESMAN的员工？//不使用union：mysql> select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';mysql> select ename,job from emp where job in('MANAGER','SALESMAN');//使用union：mysql> select ename,job from emp where job = 'MANAGER' union select ename,job from emp where job = 'SALESMAN'; limit
完整用法：limit startIndex, length startIndex是起始下标，length是长度。 起始下标从0开始。缺省用法：limit 5; 这是取前5.//按照薪资降序，取出排名在前5名的员工？mysql> select ename,sal from emp order by sal desc limit 0,5;//注意：mysql当中limit在order by之后执行！！！！！！取出工资排名在[3-5]名的员工？mysql> select ename,sal from emp order by sal desc limit 2, 3;
分页
每页显示3条记录 第1页：limit 0,3 [0 1 2] 第2页：limit 3,3 [3 4 5] 第3页：limit 6,3 [6 7 8] 第4页：limit 9,3 [9 10 11]每页显示pageSize条记录 第pageNo页：limit (pageNo - 1) * pageSize , pageSize//java程序样例 public static void main(String[] args){ // 用户提交过来一个页码，以及每页显示的记录条数 int pageNo = 5; //第5页 int pageSize = 10; //每页显示10条 int startIndex = (pageNo - 1) * pageSize; String sql = "select ...limit " + startIndex + ", " + pageSize; } 记公式： limit (pageNo-1)*pageSize , pageSize 语法顺序 select ... from ... where ... group by ... having ... order by ... limit ... 执行顺序 1.from 2.where 3.group by 4.having 5.select 6.order by 7.limit..
表的创建
语法格式： create table 表名(字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型); 关于mysql中的数据类型 varchar(最长255) 可变长度的字符串，比较智能，节省空间。会根据实际的数据长度动态分配空间。 优点：节省空间 缺点：需要动态分配空间，速度慢。 char(最长255) 定长字符串，不管实际的数据长度是多少。分配固定长度的空间去存储数据。 使用不恰当的时候，可能会导致空间的浪费。 优点：不需要动态分配空间，速度快。 缺点：使用不当可能会导致空间的浪费。 int(最长11) 数字中的整数型。等同于java的int。 bigint 数字中的长整型。等同于java中的long。 float 单精度浮点型数据 double 双精度浮点型数据 date 短日期类型 datetime 长日期类型 clob 字符大对象，最多可以存储4G的字符串。比如：存储一篇文章，存储一个说明。 超过255个字符的都要采用CLOB字符大对象来存储。 blob 二进制大对象，专门用来存储图片、声音、视频等流媒体数据。 往BLOB类型的字段上插入数据的时候，例如插入一个图片、视频等，你需要使用IO流才行。
//案例：创建一个学生表，字段包括学号、姓名、年龄、性别、邮箱地址mysql> create table t_student( no int, name varchar(32), sex char(1), age int(3), email varchar(255) );//案例：创建一个学生表并指定sex字段的默认值为'm'mysql> create table t_student( no int, name varchar(32), sex char(1) default 'm', age int(3), email varchar(255);删除表：mysql> drop table t_student; // 当这张表不存在的时候会报错！mysql> drop table if exists t_student; // 如果这张表存在的话，删除
增 insert
语法格式： insert into 表名(字段名1,字段名2,字段名3...) values(值1,值2,值3); //案例：增加一条表t_student的数据记录mysql> insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m',20,'zhangsan@123.com');mysql> insert into t_student(email,name,sex,age,no) values('lisi@123.com','lisi','f',20,2);//注意：insert语句中的“字段名”可以省略，省略的话，等于都写上了！所以值也要都写上！//例如：insert into t_student values(2, 'lisi', 'f', 20, 'lisi@123.com'); 增 insert——插入日期
str_to_date：将字符串varchar类型转换成date类型date_format：将date类型转换成具有一定格式的varchar字符串类型。语法格式： str_to_date('字符串日期', '日期格式') date_format(日期类型数据, '日期格式') mysql的日期格式： %Y 年 %m 月 %d 日 %h 时 %i 分 %s 秒 //案例mysql> insert into t_user(id,name,birth) values(1, 'zhangsan', str_to_date('01-10-1990','%d-%m-%Y'));//如果你提供的日期字符串是这个格式:%Y-%m-%d，str_to_date函数就不需要了！！！mysql> insert into t_user(id,name,birth) values(2, 'lisi', '1990-10-01'); +------+----------+------------+ | id | name | birth | +------+----------+------------+ | 1 | zhangsan | 1990-10-01 | | 2 | lisi | 1990-10-01 | +------+----------+------------+查询的时候可以使用 date_format 这个函数可以将日期类型转换成特定格式的字符串。//案例mysql> select id,name,date_format(birth, '%m/%d/%Y') as birth from t_user; +------+----------+------------+ | id | name | birth | +------+----------+------------+ | 1 | zhangsan | 10/01/1990 | | 2 | lisi | 10/01/1990 | +------+----------+------------+//mysql默认的日期格式：'%Y-%m-%d' date和datetime两个类型的区别？ date是短日期：只包括年月日信息。 date短日期默认格式：%Y-%m-%d datetime是长日期：包括年月日时分秒信息。 datetime长日期默认格式：%Y-%m-%d %h:%i:%s//案例mysql> insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-10-01','2020-03-18 15:49:50');
在mysql当中怎么获取系统当前时间？ now() 函数，并且获取的时间带有：时分秒信息！！！！是datetime类型的。//案例mysql> insert into t_user(id,name,birth,create_time) values(2,'lisi','1991-10-01',now());
改 update
语法格式： update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3... where 条件;注意：没有条件限制会导致整张表所有数据全部更新//案例mysql> update t_user set name = 'jack', birth = '2000-10-11' where id = 2;
删 delete
语法格式 delete from 表名 where 条件;注意：没有条件，整张表的数据会全部删除//案例mysql> delete from t_user where id = 2;mysql> delete from t_user; // 删除所有！
转载地址：http://zemji.baihongyu.com/