SQL语句练习

                        Student表

         Scores表

 courses表

select * from demo_user where name = "黑熊精"
select name asd,age cc  from demo_user where name = "黑熊精"

select name,age from demo_user where age<30 and sex="女"
select name,age from demo_user where age<30 or sex="女"
select name,age from demo_user where age in(300,10,18)
select name,age from demo_user where not age in(300,10,18)

select name,age from demo_user where name like "黑%"
select name,age from demo_user where name like "%王"
select name,age from demo_user where name like "%大%"

select name,age from demo_user where name like "__大_"

select * from demo_user where age between 20 and 50
select * from demo_user where age>=20 and age<=50

select * from demo_user order by age limit 2
select * from demo_user limit 2,5

select * from emp where date_format(hiredate,'%Y-%m-%d')<'2015-01-01';
select * from emp where year(hiredate)<2015

select date_format(hiredate,'%Y-%m-%d') "日期",ename from emp where year(hiredate)<2015
#2015-2019

select ename,date_format(hiredate,'%Y-%m-%y') "日期" from emp where year(hiredate)>2015 and year(hiredate)<2019

select count(empno) "个数" from emp

select max(sal) "工资",deptno from emp group by deptno

select empno,deptno,avg(sal)from emp group by deptno having avg(sal)<8000

#多表联查
#查询李军的学习的课程

select cno,cname from courses where cno in (select cno from scores where sno = (select sno from students where sname="李军")) group by cno

select * from students s,scores c where s.sno = c.sno and sname = "李军"
select s.sname,c.cno from students s,scores c where s.sno = c.sno and sname = "李军"

select * from students natural join scores;#自然连接

select * from students s inner join scores c where s.sno = c.sno and sname = "李军" #内连接

select * from students s left join scores c on s.sno = c.sno where sname = "李军" #左连接
select * from students s right join scores c on s.sno = c.sno where sname = "李军" #右连接

#三表连查
select * from students s,scores c,courses f where s.sno = c.sno and c.cno = f.cno and s.sname = "李军"
select s.sno,s.sname,c.cno,f.cname from (students s left join scores c on s.sno = c.sno) left join courses f on  c.cno = f.cno where s.sname = "李军"

#按照年龄大小排序
select sname,year(sbirthday) from students order by sbirthday desc
select sname,date_format(sbirthday,"%Y-%m-%d") "出生日期" from students order by sbirthday desc

 

#查看两门成绩都超过80分的名字

//思路 先按照成绩降序排序,然后在查看大于80的成绩,在按照name分组,查看出现的次数

select * from cj where chengji > 80 order by chengji desc

select name,count(*) from (select * from cj where chengji > 80 order by chengji desc) as total group by name

sql的执行顺序
    先选择表,表连接条件,连接表,where条件,分组,聚合函数,having条件分组过滤,Distinct 字段去重,排序,分页

笛卡儿积,
自然连接(与内连接的结果是完全一样的,区别是自然连接将重复的属性只保留一份),
内连接:
左连接将左表的记录全部表示出来,右表只会显示符合搜索条件的记录,右表记录不足的未null,其实就是将右边的表的第一项根据条件分别和左边的每一行数据进行对比，如果符合条件，就拼成一条记录。
右连接:

SQL优化
1 尽量不用使用*,使用具体字段
2 使用where条件时,不要使用or,应该分开两条语句,避免索引失效,
3 使用varchar代替char varchar为可变长度字符
4 尽量使用数值代替字符串类型.比如男女(0.1),状态0 上架,1 下降,2删除等
5 查询尽量避免返回大量数据 采用分页
6 优化like,使用"1%",不要使用"%1","%1%",因为索引是最左匹配原则
7 避免where中对字段进行表达式操作
8 先过滤在分组
9 三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小

distinct 给结果去重 distinct job
常见聚合函数;常见函数 MAX MIN SUM AVG count（求个数）
例如：注意 where 里中不能使用聚合函数
          having 使用的过滤条件必须是查到过的结果
          1）select deptno,round(avg(sal)) '平均薪资'from emp_1 group by deptno
          2）select count(1) '入职人数', year(hiredate) from emp_1  where year(hiredate) >100 group by year(hiredate)
          3）select job,max(sal) from emp_1 group by job having max(sal) >2000 order by max(sal) desc
          4） select year(hiredate),count(1) from emp_1 where year(hiredate) > 2000 group by year(hiredate)
当查询结果出现了聚合列和非聚合列时需要分组
通常按照非聚合列分组

事务
    事务指单个逻辑工作单元执行的一系列操作，要么全成功，要么全失败
    事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则就会恢复到这堆SQL执行之前的状态。
    例如 转账
事务4个特性ACID
    原子性：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间的某一环节。事务在执行过程中如果发生错误，会被  回滚  到事务开始前的状态，就像这个事务从来没有执行过一样
    一致性：在事务开始之前和事务结束之后，数据库的完整性没有被破坏，这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确性，串联性已经后续数据库可以自发性的完成预定工作
    隔离性：数据库运行多个并发事务同时对其数据进行读写与修改功能，隔离性可以防止多个事务执行时由于交叉执行而导致数据的不一致
    持久性：事务处理后，对数据的修改就是永久的，即使系统故障也不会丢失
隔离级别： 读未提交，读已提交，可重复读，串行化
                 从左到右 安全性越强，效率越低，MYSQL用的是可重复读

开始事务 start transaction（开始事务）start  （增删改，才可以对数据库产生影响）
              commint（提交事务）或rollback（回滚）回滚对数据库没有任何影响，返回到事务开始状态
    就是使用的是commit方式结束事务，但是如果中间出现错误，也会发生回滚
    注意：如果只执行start命令没有commit命令，获取在当前窗口存入了数据，但没有持久性，在另一个窗口无法成功查看，
    只要同时使用了start 与 commit 才可以是一个完整的事务

外键约束：foreign key：子表添加记录时，id必须在取自主表
             删除主表记录时，必须没有被子表使用者
             子表中有外键的sql，后面关联的是主表
    foreign key(user_id) REFERENCES tb_user(id)//将自己的主键与想要关联的表相联系
    例如：create table tb_use(id int primary key auto_increment,name varchar(10),sex varchar(10))
              create table tb_user_address(user_id int primary key auto_increment,adderss varchar(20),foreign key (user_id)references tb_use(id))
默认约束：sex varchar(10) default '男'
检查约束：check(age>0 and age<200)

单值索引：一个索引包含一个列
唯一索引：
符合索引：最左特性：查时只能12，123，否则可以会失去索引效果（查的慢）

单值索引：create index 索引名字 on 表名(字段名);
唯一索引：alter table dept add unique(loc) #创建唯一索引--索引列的值必须唯一
符合索引：alter table emp_1 add index fuhe_index1(sal,deptno)

使用索引：select * from emp_1 where job = 'java';//背后使用job索引
                select * from emp where empno = 100 //背后会使用主键索引
    使用时就普通查询，看不出是否使用了索引

explain：用来观察sql的执行顺序，只要看是否使用了索引
    explain
    select * from emp_1 where job = 'java'
为了提高数据库的查询效率，可以使用索引
当查询需要量比较大，字段的值比较大的时候.. 使用索引
使用索引：好处 大大提高了SQL的查询效率
                坏处：索引本身就是一张表，重复了包内容，浪费内存

笛卡儿积：
    select* from dapt,emp_1 where dapt.deptno = emp_1.deptno and emp_1.ename = 'hy1' // 两张表的关联关系

三种链接join
    1 内连接 inner join
    例 select* from emp_1  inner join dapt on emp_1.deptno =dapt.deptno  where emp_1.ename = 'hy'
    2 左（外）连接 left  join
    3 右（外）链接 rigth join

inner join、left join、right join的区别？
    //常用左连接：常用小表驱动大表，，左边为小表，全部查询，右边为大表，只查询满足需要的
    1.inner join 查询的是两边都对应的记录才展示，其他去掉
    例如：select* from emp_1  inner join dapt on emp_1.deptno =dapt.deptno  where emp_1.ename = 'hy'    
    2.left join 左边表的数据都出现，右边没有数据以null填充
    例如：select* from emp_1  left join dapt on emp_1.deptno =dapt.deptno     
    3.right join RIGHT JOIN右边表中的数据都出现，左边没有数据以NULL填充
    例如：select empno,ename,sal from emp_1 right join dapt on emp_1.deptno =dapt.deptno  
子查询（嵌套查询）：把上次的查询结果作为条件再次查询
    1 例如select* from emp_1 where deptno = (  select deptno from dapt where loc = '高新国际')

视图：可以把SQL的查询结果缓存起来，存入视图中 视图就是一张特殊的表，最好只做查询
好处：简化了SQL的编写
坏处：没法做SQL的优化，占用空间

创建视图 (select year(sbirthday),count(1) from students group by year(sbirthday))
使用视图 select * from studemts_view

SQL 优化
1 尽量使用字段名代替*
2 做表设计时能用varchar 不用char
3 字段里的值，最好用数字代替字符串
4 尽量把过滤条件精细，能用and不用or
5 索引的设计，最多5个，不能太多
6 模糊查询，尽量要确定开始元素，让索引生效
7 数据库对数字没有严格的要求，name = 123 索引不生效，name = ‘123’索引不生效
8 无用索引最好及时删除