sql语句练习

show databases
create database abc
drop database abc
create TABLE test1 (id int PRIMARY key auto_increment ,name VARCHAR(20) default '空白')
drop table test
desc test
alter table test add COLUMN (addr varchar(100))
alter table test add COLUMN (addrr varchar(100) unique not null )
select now() #当前年月日时分秒
select DATE(NOW())  #当前年月日把date换成time，是表示当前时分秒
select year(now())#当前年份，把year换成month day hour minute second 表示当前年月日时分秒;
insert into  test VALUES(null ,'jake' ,'青岛','市南' )
insert into  test VALUES(null ,'tom' ,'青岛','市北' )
insert into  test VALUES(null ,'rose' ,'青岛','崂山' )
UPDATE test set name = 'tomm' where name='tom'
update test set name ='jack' where name='jack'
select * from test #查表里的所有信息
select name,upper(name) from test#把name转为全大写，若upper换为lower则是转为全小写
select LENGTH(name) from test #查询各个name的长度
select name,CONCAT(name,123) FROM test #把name 后都拼接个123
select name,REPLACE(name,'j','666') FROM test#替换
select name,SUBSTR(name,2,2) from test#从第二个字符开始截取2个字符，若一个数字是截取第几个数字之后的全部字符
select distinct name from test#去name里重复的数字
insert into  test VALUES(null ,'jake' ,'青岛','李沧' )
select * from test where name is  not null
select * from test LIMIT 1,2 #表示从第二条信息开始取两条，若后是一个数字表示取几条数据
select * from test where id BETWEEN 2 and 4#前后都包括
select * from test where id>=2 and id<=3#与上句表达意思相等
select * from test WHERE name like '%e%'# %为占位符
select * from test ORDER BY name#排序默认升序，字段后加desc 表示降序
select DISTINCT name from test where addr is not null and name like '%e%' order by id desc LIMIT 1#综合
select MAX(id),min(id),round((AVG(id))),SUM(id),COUNT(1) from test#聚合函数求和
select avg(sal) from emp where job='员工'
select sal+ifnull(comm,0) from emp
select deptno,round(avg(sal+IFNULL(comm,0))) from emp GROUP BY deptno
select job,MIN(sal),MAX(sal) from emp group by job having job like '%总%'
begin;#事务
insert into test values (null ,'jakes','青岛','城阳');
insert into test values (null ,'jack','青岛','黄岛');
COMMIT;
select * from test
# 在创建表格的时候放括号内外键约束
# FOREIGN key (子表的字段名) REFERENCES 主表表名 (主表字段)
alter table test add index  a(name) #普通索引
alter table test add unique bbcc(addrr)#唯一索引
alter table test add index cc(name,addrr)#复合索引
EXPLAIN#检测有没有用到索引
select name from test
explain
select addrr from test where  name like '%e%'
CREATE view abb as SELECT *from test where name like'%e%'#create viwe 视图名 as+SQL 语句
select * from abb #把视图当做一张表来使用，缩小查询空间
#多表联查
select * from dept,emp where dept.deptno=emp.deptno#笛卡尔积
select * from dept JOIN emp on dept.deptno=emp.deptno where dname like '%e%'  limit 2#join连接
select ename from emp where deptno in(select deptno from dept where loc ='一区') #子查询
#二区所在部分的所有人的名字，三种表达方式
select ename  from emp a, dept b where a.deptno=b.deptno and b.loc ="二区"
select ename  from emp a join dept b on a.deptno=b.deptno where b.loc ="二区" 
select ename from emp where deptno in(select deptno from dept where loc ='二区')

# 列出research 部门里的所有员工信息
select a.* from emp a,dept b
where a.deptno=b.deptno
and b.dname='research'
SELECT a.* from emp a right JOIN dept b
on a.deptno=b.deptno
where b.dname='research'
#子查询 把第一次的查询结果作为第二次查询的条件（嵌套查询）
#SELECT deptno from dept where dname='research'
select * FROM emp where deptno=(SELECT deptno from dept where dname='research')
#练习二查tony 所在的部门信息
select * FROM dept where deptno =(SELECT deptno from emp where ename='tony' )
#查询二区员工的姓名
#SELECT deptno from dept where loc='二区'
select ename from emp where deptno in  #(有多个值时，不能用等于号只能用in（1,2）)
(SELECT deptno from dept where loc='二区')
#查询高于平均工资的员工姓名
SELECT ename from emp where sal>(select avg(sal)from emp )

#条件查询 distinct 去除
SELECT  loc  from dept;
SELECT DISTINCT  loc ,dname from dept;#去重
#  where 用来过滤数据
SELECT  loc  from dept 
SELECT  *  from dept 
#全表查-低效
SELECT  *  from dept where deptno=1;#只查一条--高效
SELECT * from dept WHERE loc="二区"#在二区的所有部门
#查询二区名字为research的部门
SELECT * FROM dept WHERE loc = "二区" AND dname = 'research' 
#查询二区名字为research的部门编号
SELECT deptno FROM dept WHERE loc = "二区" AND dname = 'research' 
#查询在一区的编号是1的部门名称，and比or稍微高效
SELECT dname FROM dept WHERE loc = '一区'AND deptno = 1 
#查询在一区的或者编号是3的部门
SELECT*FROM dept WHERE loc = '一区'    OR deptno = 3 
#like 模糊查询 %叫占位符
SELECT * FROM dept WHERE dname LIKE '%o%';#查询部分名称里包含o的部门
SELECT * FROM dept WHERE dname LIKE 'a%';#以a开头的部门  最高效
SELECT * FROM dept WHERE dname LIKE '%ch';#以ch结尾的部门
SELECT * FROM dept WHERE dname LIKE '%ting%'OR loc='一区';#查询在一区的部门或者包含ting的部门
select * FROM emp WHERE comm is null;#查询comm是空的员工信息
select * FROM emp WHERE comm is not null;#查询comm不为空的员工信息；        
select *,sal+IFNULL(comm,0) FROM emp ;#查询每个员工的月薪
#查询工资(5000,10000)的员工信息
select * from emp where sal>5000 and sal<10000
select * from emp where sal between 5000 and 10000 #都包含[5000,10000]
#分页 limit,限制数据的条数
select * from emp limit 2 #取前两条
select * from emp limit 0,2 #从0(第1条记录)开始,取两条
select * from emp limit 2,2 #从2(第3条记录)开始,取两条
select * from emp limit 1,3 #从1(第2条记录)开始,取三条
#查询工资>5000的前两条记录
select * from emp where sal>5000 limit 2
#order by排序 升序(默认的)、降序
select * from emp order by sal asc #升序,其中asc可省略
select * from emp order by sal desc #desc降序
#练习1：查询工资>5000的两个最高薪的员工信息
select * from emp where sal>5000 order by sal desc limit 2
#练习2：查询名字里包含o的老员工
select * from emp where ename like '%o%' order by hiredate limit 1
#练习3：查询名字里包含o的员工的入职年份
select *,year(hiredate),year(now())-year(hiredate) 
from emp where ename like '%o%' 
#练习4：2015年以前入职的老员工
select * from emp where year(hiredate)<2015
select * from emp where hiredate < '2015-1-1'
#练习5：查询每个员工入职了几年
select *,year(NOW())-year(hiredate) from emp 
#练习6：查询2015~2019年入职的员工
select * from emp where year(hiredate) >=2015 and year(hiredate) < 2019
select * from emp where year(hiredate)  between 2015 and 2019
#练习7：一年13薪，算年薪 
select *,sal*13+ifnull(comm,0)*13 from emp 
select *,sal*13+ifnull(comm,0)*13 as 年薪 from emp  #给列/字段设置别名
select *,sal*13+ifnull(comm,0)*13 年薪 from emp  #省略as

#统计名字里包含a的员工人数
select *,COUNT(1) from  emp  where ename like '%a%' 
select avg(sal+IFNULL(comm,0)) ,max(sal) ,min(sal)from emp where job='员工'
SELECT count(1)from emp where  year(hiredate)>=2019
select round(sum(sal+IFNULL(comm,0))*12) 一年开销 from emp where deptno=2

use cgb2105
#多表联查1 笛卡尔积 把多个表用逗号隔开
select * from emp,dept 
#用笛卡尔积加约束条件where dept.deptno=emp.deptno相联系的两个字段等于前边用表名.
select * from emp,dept WHERE dept.deptno=emp.deptno
#使用别名 加限制条件 取一区的数据
select * from dept a,emp b 
WHERE a.deptno=b.deptno  
and a.deptno=1 
#查询一号部门的员工姓名
select ename,a.deptno from dept a,emp b WHERE a.deptno=b.deptno and a.deptno=1 
select ename,loc from dept a,emp b WHERE a.deptno=b.deptno and ename='tony'
select ename,loc from dept join emp on dept.deptno=emp.deptno and ename='tony'
 select emp.* from dept dept,emp where dept.deptno=emp.deptno and dept.loc='二区'
select b.* from dept a,emp b WHERE a.deptno=b.deptno and dname='accounting'
#查二区员工的平均工资
select ROUND(AVG(b.sal+IFNULL(comm,0))),a.loc from dept a,emp b 
where a.deptno=b.deptno and a.loc='二区'
#多表联查2 表连接用join，把多个表用join连接
select *from dept join emp  
#把多个表用join连接 过滤一般用on，其余相同于笛卡尔积,（也可以用where）
select *from dept join emp  on dept.deptno=emp.deptno
#查询Jack的部门名称
select a.dname from dept a join emp b 
on a.deptno=b.deptno  # 描述表间的关系 用 on
where b.ename='jack'  # 过滤条件 用 where
#查询二区的所有员工名字
select b.ename from dept a join emp b 
on a.deptno=b.deptno
where a.loc='二区'
#查询二区的所有员工个数
select count(1)  from dept  a join emp b
on a.deptno=b.deptno 
where a.loc='二区'
#查二区里的最高薪
select b.ename,MAX(b.sal)from dept a join emp b
on a.deptno=b.deptno 
where a.loc='二区'
#查询二区的所有功能
#inner join：取左右两个表中，都满足了条件记录
#left join：取左表里的所有记录，右表中满足了条件的记录不满足的用null填充
#right join：取右表里的所有记录，左表中满足了条件的记录不满足的用null填充
select *from dept a join emp b
on a.deptno=b.deptno 
 LIMIT 1
select *from dept a  join emp b#取交集
select *from dept a left join emp b
#select *from dept a right join emp b
on a.deptno=b.deptno 
where a.loc='二区'

select * from dept a,emp b
where a.deptno=b.deptno
and a.loc = '二区'

select ename from dept a join emp b
where a.deptno=b.deptno and a.loc='一区'

SELECT deptno from emp where ename='jack'
SELECT loc from dept where deptno in (SELECT deptno from emp where ename='jack' or ename='tony')
  
select *from dept a  join emp b
on a.deptno=b.deptno