第七讲

单表练习题
1、查询1832班的成绩信息
select chinese,english,math,class from grade where class = 1832;

2，查询1833班，语文成绩大于80小于90的成绩信息
select chinese,english,math ,class from grade where class = 1833 and chinese > 80 and chinese <90;

3，查询学生表中5到10行的数据
select * from grade limit 4,6;

4，显示1832班英语成绩为98，数学成绩为77的姓名与学号,
select name,sid from grade where class = 1832 and english = 98 and math = 77;

5，查询出1832班成绩并且按语文成绩排序（降序）
select chinese,english,math,class from grade where class = 1832 order by chinese desc;

6，查询1833班与1832班，语文成绩与数学成绩都小于80的姓名。
select name from grade where class in(1833,1832) and chinese<80 and math<80;

select name from grade where (class = 1833 or class = 1832) and chinese<80 and math<80;

7，查询出没有参加语文考试的学生姓名和班级名称。
select name,class from grade where chinese is null;

8，求出班上语文成绩不及格的学生姓名
select name from grade where chinese<60 or chinese is null;

9，求出每个班的数学平均成绩
select avg(math),class from grade group by class;

10、求出每个班级语文成绩总分 --涉及到每个的时候都需要分组
select sum(chinese),class from grade group by class;

11、将语文成绩不及格的学生成绩改为60分
update grade set chinese = 60 where chinese < 60 or chinese is NULL;

12、三科分数都大于70分的人名和年纪
select name,age from grade where chinese>70 and math>70 and english>70;

13、求出英语分数高于70且其它任何一科目大于60分的人和班级
select name,class from grade where english >70 and (chinese >60 or math >60);

14、统计每个班的人数
select count(*),class from grade group by class;

15、求每个班数学成绩大于80的人数
select class,count(*) from grade where math>80 group by class;

16、求出每个班英语成绩最高的那个人的姓名和班级名称 --每个班英语成绩 最高
select name, class from grade where english =(select max(english)  from grade g2 where g2.class = grade.class);

select class,max(english) s from grade group by class;   ==>每个班的最高分  临时表  t  

select * from grade    ==>

select g.name,g.class from grade g,(select class,max(english) s from grade group by class)t  where g.class = t.class and
g.english = t.s;

17、给stu表增加3个字段（数据类型及长度自定义，建议要合理）

【数据库多表连接】
1、基本法    ==》2张表中有相同的字段值则可以连接成一张表 ，连接后结果显示相同字段值的数据
select* from 表1，表2  where  表1的相同=表2的相同字段

select * from aa,cc where aa.id = cc.s_id;

张三的成绩
select score from aa,cc where aa.id = cc.s_id and name = 'zhangsan';

2、内连接   ==》2张表中有相同的字段值则可以连接成一张表 ，连接后结果显示相同字段值的数据
select * from 表1 inner join 表2  on 表1的相同=表2的相同字段
select * from aa inner join cc on aa.id = cc.s_id;

张三的成绩
select score from aa inner join cc on aa.id = cc.s_id where name = 'zhangsan';

3、左连接  ==》以左表为主，显示左表的全部数据，右表没有的数据以null填充
select * from 表1 left join 表2  on 表1的相同=表2的相同字段

select  * from aa left join cc on aa.id = cc.s_id;

张三的成绩
select score from aa left join cc on aa.id = cc.s_id where name = 'zhangsan';

4、右连接  ==》以右表为主，显示右表的全部数据，左表没有的数据以null填充
select * from 表1 right join 表2  on 表1的相同=表2的相同字段

select  * from cc right join aa on aa.id = cc.s_id;

张三的成绩
select score from cc right join aa on aa.id = cc.s_id where name = 'zhangsan';

5、临时表
select  * from cc right join aa on aa.id = cc.s_id;   ==》临时表 t

张三的成绩

select score from (select  * from cc right join aa on aa.id = cc.s_id)t  where name = 'zhangsan';

6、硬链接  ==》2张表的字段数量一定要相同
select * from aa union select * from cc;

7、嵌套 =
张三的成绩
select id from aa where name = 'zhangsan';    ==>1001

select score from cc where s_id = (select id from aa where name = 'zhangsan');

8、嵌套 in
select id from aa where name = 'zhangsan';    ==>1001

select score from cc where s_id in (select id from aa where name = 'zhangsan');

求出没有参加考试的人名
左连接  
select name from aa left join cc on aa.id = cc.s_id where score is null;

左连接 + 临时表

select * from aa left join cc on aa.id = cc.s_id;
select name from (select * from aa left join cc on aa.id = cc.s_id)t where score is null;

右连接 + 临时表
select * from cc right join aa on aa.id = cc.s_id;
select name from (select * from cc right join aa on aa.id = cc.s_id)t where score is null;

嵌套法
select s_id from cc;    ==>1001

select name from aa where id not in (select s_id from cc);

【视图】
1.什么是视图

视图是一个虚拟的表，它不在数据库中以存储数据的
形式保存，是在使用视图的时候动态生成。

2.视图的特点

2.1视图是由基本表产生的虚表

2.2视图的更新和删除会影响基础表

2.3基础表的更新和删除也会影响到视图

3 视图的作用

它的作用可以对基本表的敏感信息进行保护；在实际工作中，出于安全考虑，
将用户常用的信息创建成视图给用户调用，避免了直接操作基本表！

select * from stu;
create view stu6 as (select id,sex,name,class from stu);   ==》创建一个视图stu6 和基础表stu表中的id sex name class一样的数据

show tables;     ==》查看视图

show create view stu6;    ==》查看成创建视图stu6结构（查看赋权）

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `stu6` AS
(select `stu`.`id` AS `id`,`stu`.`sex` AS `sex`,`stu`.`name` AS `name`,`stu`.`class` AS `class` from `stu`)

select* from stu6;   ==》查看视图的数据

update stu6 set sex = 0 where id = 1;      ==》修改视图表的数据，基础表也会改变

select * from stu;         ==》基础表也会改变

update  stu set sex = 1 where id = 1;     ==》更改基础表的数据，视图表也会改变

drop view stu6;      ==》删除视图

数据库中的DDL和DML
DDL：data definition language 数据定义语言    ==》对数据库的结构操作   alter create drop

DML：data mannipulation language  数据操作语言  ==》对数据库数据的操作   select  delete  UPDATE  

【外键约束】

数据库的约束有哪些
not NULL   ==》非空
primary key  ==》主键
default     ==》默认值
auto_increment  ==》自增长
foreign key   ==》外键约束    表与表之间关系

1、外键用来干什么
为了一张表记录的数据不要太过冗余。这和软件project的模块化思想类似，仅仅
只是在数据库中是对表关系进行解耦，尽量让表记录的数据单一化。
外键的主要作用是：保持数据的一致性、完整性

show create table stu;     ==》查看创建表的结构

CREATE TABLE `stu` (
  `id` int(4) NOT NULL,
  `age` int(8) DEFAULT NULL,
  `sex` int(4) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `class` int(4) DEFAULT NULL,
  `math` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

ENGINE=MyISAM  引擎 =存储引擎   ==》不能使用外键

ENGINE=innodb    是mysql是数据库提供外键的引擎

create table dcs1(id int(10) primary key ,name varchar(10))engine=innodb;   ==》当作主表

create table dcs2(sid int(10) primary key,sname varchar(10),constraint fk_sid foreign key(sid)
references dcs1(id))engine=innodb;

fk_sid：是外键名 --对应dcs2表中的：fk_sid
foreign key(子表的外键字段) --对应dcs2表中的：sid
references 父表的表名（父表的主键的字段名） ---父表名对应dcs1，父表的主键的
字段名对应dcs1中的id字段
外键之增加数据：
如果表1和表2之间有外键约束 且表1 dcs1是主表 表2 dcs2为子表 如果主表不存在的数据 在任何
一张子表是无法插入跟该数据相关的任何数据。

show create table dcs1;   ==》查看创建表的结构

CREATE TABLE `dcs1` (
  `id` int(10) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

show create table dcs2;     ==》查看创建表的结构

dcs2