一,DML操作语言
DML:对表中的数据进行操作的语言
关键字:insert ,update, delete, select
假设我们使用创建表的语句创建了一个student表,然后对该表进行操作。
create table classroom(
cid int,
cname varchar(20),
des varchar(20)
)
1,插入数据
语法:insert into 表名 [(字段)] values(值);
例句:
给所有字段添加值,和表中字段顺序一致
insert into classroom values (1,'java','java class');
给cname字段添加值,和指定的字段顺序把必须保持一致
insert into classroom (cname) values ('python');
insert into classroom (cid,cname) values (3,'test');
添加三条记录,批量插入
insert into classroom
values
(4,'oracle','oracle class'),
(5,'c','c class'),
(4,'c#','c# class');
将classroom的值整体复制到classroom1。(前提是两个表的结构必须一样(字段类型,个数))
create table classroom1(
cid int,
cname varchar(20),
des varchar(20)
)
insert into classroom1 select * from classroom;
查看表的所有记录
select * from classroom;
2,删除数据
语法:delete form 表名 where 条件;
例句:
删除一条数据
delete from classroom where cname = 'python';
删除表中数据
delete form classroom;
清空表的数据
语法:truncate table 表名;
例句:
truncate table classroom;
delete 和 truncare 的区别是什么?
(1),delete是逐行删除,truncate是文件式的清空
(2),delete删除之后并不会改变表的结构,自增性会继续执行,不会重置,truncate删除之后,自增属性重置
3,修改数据
语法:update 表名 set 字段值 = 新值 [where 条件];
例句:
修改一条数据
update classroom set cname = 'test' where cid = 1;#cid主键列,可以提高检索速度,sql优化
update classroom set cname = 'test' where des = 'oracle class';
修改所有数据(不是把des改为班级信息,而是把des字段的值改为班级信息)
update classroom set des = '班级信息';
4,查询数据
语法:
SELECT 字段 |表达式
FROM 表名 |视图|结果集
[ WHERE 条件 ]
[ GROUP BY 分组 ]
[ HAVING 分组之后进行检索 ]
[ ORDER BY 排序 ]
[ LIMIT 限制结果]
例句:
查询所有信息
select * from classroom;
#查询部分信息
select cid, cname from classroom;
查询所有员工的姓名和工资
select ename,sal from emp;
员工工资提升5%之后的员工姓名和工资
select ename, sal + sal * 0.05 from emp;
查询工资大于2000的员工信息
select * from emp where sal > 2000;
查询工资在1000-2000之间的员工信息
select * from emp where sal between 1000 and 2000;#包括边界值[1000,2000]
select * from emp where sal >= 1000 and sal <= 2000;#包括边界值[1000,2000]
select * from emp where sal > 1000 and sal <2000;#不包括边界值(1000,2000)
查询员工编号为7521,7369,7788的员工信息
select * from emp where empno in (7521,7788,7369);
select * from emp where empno = 7521 or empno = 7788 or empno = 7369;
5、取别名
语法:
select ename, sal + sal * 0.05 as 提升之后的薪资 from emp;
select ename, sal + sal * 0.05 提升之后的薪资 from emp;#as可以省略不写
例句:
select ename, sal + sal * 0.05 as after_salary from emp;
select ename, sal + sal * 0.05 after_salary from emp;#as可以省略不写
6、去重查询 distinct
查询所有的职位信息 去重
select distinct job from emp;
7、模糊查询 like
%:匹配字符,匹配0个或者多个长度的任意字符
_:匹配字符,匹配一个长度的任意字符
查询以s开头的员工信息
select * from emp where ename like 's%';
查询名字中包含s的员工信息
select * from emp where ename like '%s%';
查询第二个字符为L的所有员工信息
select * from emp where ename like '_L%';
8、排序
语法:排序关键字是order by
,默认升序asc
,降序为desc
对所有员工的工资进行排序
select * from emp order by sal desc;
select * from emp order by sal;
对所有员工的工资进行降序排列,如果工资一致,则按照员工编号降序排列
select ename,sal from emp order by sal desc,empno desc;
9、限制结果集查询
语法:关键字是limit
,limit M,N
M:开始的位置,索引从0开始,N:取值的长度
例句:
查询在10号部门工资最高的员工
select * from emp where deptno = 10 and sal = (select max(sal) from emp);
-- 思路:查询在10号部门的员工信息;工资最高 排序 desc;只要第一个人的信息,limit M,N M:开始的位置,索引从0开始,N:取值的长度
select * from emp where deptno = 10;
select * from emp where deptno = 10 order by sal desc;
select * from emp where deptno = 10 order by sal desc limit 0,1;
10、为空或者非空的数据查询与操作
语法:为空 is null
, 非空 is not null
例句:
查询所有有奖金的员工信息
select * from emp where comm is not null;
select * from emp where comm > 0;
select * from emp where comm is null;#查询空置,不能用 = null,要用 is null
将奖金小于500的员工奖金加100
update emp set comm = comm + 100 where comm < 500 or comm is null;#这样子的查询是有问题的,他只会修改掉comm<500的员工奖金,对于comm为null的不做修改
-- 针对以上问题,我们需要把空值的列改为0
update emp set comm = 0 where comm is null;
-- 然后再添加奖金
update emp set comm = comm + 100 where comm < 500 or comm is null;
-- 两句sql处理过于麻烦,可以使用 ifnull(comm,0)函数,判断comm列是否为null,如果是null则赋值0
update emp set comm = ifnull(comm,0) + 100 where comm < 500 or comm is null;
二,sql运算符
1,算术运算符
+,-,*,/, %取结果的余数,div整除,只取整个结果的整数部分, mod取余
select 10/3 #3.33333
select 10div3 #3
select 10%3 #1
select 10%3 #1
2,比较运算符
1、 >, <, >=, <=, =, !=, is null, is not null, between and, in, not in
2、 结果是布尔值 true 、false
3,逻辑运算符 and,逻辑与,都真则真 or,逻辑或,一真为真 !,逻辑非,取反 select !(1<0); 结果为true
练习:
-- 查询员工提升100元后超过2000的所有员工信息
select * from emp where sal+100 >2000;
-- 查询工资在1000-2000之间的10号部门的最高工资的员工信息
select * from emp where sal between 1000 and 2000 and deptno = 10 order by sal asc limit 0,1;
-- 将所有工资低于2000的员工工资提高5%
update emp set sal = sal + sal * 0.05 where sal < 2000;
-- 查询名字包含s的并且在20号部门的员工信息
select * from emp where deptno = 20 and ename like '%s%';
-- 查询工资大于1000的10号部门的前5条记录
select * from emp where sal >10 and deptno = 10 order by sal asc limit 0,5;
-- 将奖金小于500的员工奖金加100
update emp set comm = ifnull(comm,0) +100 where comm < 500 or comm is null;
如果想要更多的练习SQL语句,可以点击下面的链接,百度文库中有很多题目可以练习。
员工部门表综合查询60题 - 百度文库 (baidu.com)