mysql必知必会2
2018年7月10日笔记
1.创建数据表和插入数据
创建表commodityType
create table commodityType(
id int primary key,
name varchar(50)
)表commodityType插入数据
insert into commodityType (id,name) values (1,'玩具');
insert into commodityType values (2,'文具');
insert into commodityType values (3,'书籍'),(4,'食物'),(5,'衣物');创建表commodity
create table commodity(
id int primary key,
name varchar(20),
madein varchar(20),
c_type int references commodityType(id),
inprice int unsigned,
outprice int unsigned,
num int unsigned default 200);表commodity插入数据
insert into commodity(id,name,madein,c_type,inprice,outprice,num)values(1,'变形金刚','美国',1,100,200,default);
insert into commodity values(2,'红楼梦','中国',3,1000,1500,1000);
insert into commodity values(3,'西游记','中国',3,500,600,30);
insert into commodity values(4,'文具盒','日本',2,10,100,20);
insert into commodity values(5,'铅笔','中国',2,1,2,1000000);
insert into commodity values(6,'HADOOP权威指南','美国',3,149,199,default);
insert into commodity values(7,'多啦A梦','日本',1,600,900,100);
insert into commodity values(8,'Java编程思想','中国',3,100,null,1000);2.数据表的删除数据
语法：delete from {1} where {2} 第一对大括号替换为表名，第二对大括号替换为查询条件。 注意：删除语句一定要写删除条件，否则整张表删除。 例如:delete from commodity 这个SQL语句删除commodity表中的所有数据。 例如：delete from commodity where id = 5 这个SQL语句删除commodity表中的id=5的数据
3.数据表的清空表格数据
语法：truncate table {} 大括号替换为表名。 例如：truncate table commodity 这个SQL语句清空commodity表中的所有数据。 相同：都能删除数据，不删除表结构，但truncate速度更快 不同：使用truncate table 重新设置auto_increment计数器; 使用truncate table不会对事务有影响
4.数据库的修改数据
语法：update {1} set {2} = {3} where {4} = {5} 第1对大括号替换为要修改表的表名，第2对大括号替换为新字段的字段名，第3对大括号替换为新字段的值， 第4对大括号替换为限定条件的字段名，第5对大括号为限定条件的值。 例如：update commodity set name = '超人' where id = 8
课堂联系
图片.png-30.7kB
drop table if exists subject;
create table subject(
sub_id int primary key,
sub_name varchar(50),
sub_hour int,
sub_grand_id int
);
insert into subject values(1,'高等数学-1',120,1);
insert into subject values(2,'高等数学-2',110,2);
insert into subject values(3,'高等数学-3',100,3);
insert into subject values(4,'高等数学-4',130,4);5.数据库的单表查询
5.1选出限定的字段
select id,name,madein,c_type,inprice,outprice,num from commodity;
sql语句运行结果如下图所示:
图片.png-7.8kB
5.2 between关键字
select id,name,inprice from commodity where inprice between 100 and 101; select id,name,inprice from commodity where inprice <= 101 and inprce >= 100;
两种写法效果相同，sql语句运行结果如下图所示：
图片.png-2.7kB
5.3 is null关键字
is null关键字的条件查询语句示例：
select * from commodity where outprice is null;
5.4 is not null关键字
is not null 关键字的条件查询语句示例：
select * from commodity where outprice is not null;
5.5 in关键字
in关键字的条件查询语句示例:
select * from commodity where outprice in (200,600);
5.6 like关键字
like关键字的条件查询语句示例:
select * from commodity where madein like "%国";
sql语句运行结果如下图所示：
图片.png-6.3kB
查出name字段值长度为3的行的所有信息：
select * from commodity where name like '___';sql语句运行结果如下图所示：
图片.png-4kB
5.7 order by关键字
order by 关键字使用示例,desc降序从大到小，asc升序从小到大:
select * from commodity order by num desc;
sql语句运行结果如下图所示：
图片.png-7.5kB
5.8 limit关键字
limit关键字使用示例： 选出前5条记录
select * from commodity limit 5;
选出第6-8条记录:
select * from commodity limit 5,3;
练习
练习1 使用SQL命令重新创建myschool数据库并新增适量数据，完成如下需求： 1.查询出所有男生； 2.查询出住址不在宿舍的所有学员； 3.查询出所有张姓学员；
插入语句：
insert into students values("张三",'男',"宿舍"), ("赵六","女","校外"),("李四","男","宿舍"),("张五","男","校外");
查询所有男生：
select * from students where sex = "男";
查询住址不在宿舍的所有学员:
select * from students where address != "宿舍";
查询出所有张姓学员：
select * from students where name like "张%";
练习2 查询《XXX》课程成绩前10名且分数大于80的学生信息（学号，姓名，课程名，分数）
select * from stu where score > 80 limit 10;
6.聚合函数
聚合函数一般配合group by 关键字使用 聚合函数:count()、avg()、sum()、max()、min()
7.数据分组
通过group by关键字实现，可与having关键字配合使用继续进行筛选
8.多表连接查询
内连接，左连接，右连接三种连接方式查询的区别可以通过下面三张图总结： 内连接是根据条件取两个表的交集
image_1ci7l580fpuek5lij4n2756613.png-58.9kB
左连接是根据条件取两个表的交集+左表中没有被取进交集的部分
image_1ci7l4g5i1o3v1rpb1ifa1ucv123cm.png-73.2kB
右连接是根据条件取两个表的交集+右表中没有被取进交集的部分
image_1ci7l7abl12ss1jqj163v1f5311h31t.png-74.3kB
9.子查询
9.1概念：
1.子查询是一种常用计算机语言select-sql语言中嵌套查询下层的程序模块。当一个查询是另一个查询的条件时，称为子查询。 2.当需要一张表的数据作为条件去查询另一张表，我们需要用到子查询。
9.2子查询的使用
1.子查询必须被圆括号括起来。 2.子查询只能在有一列的select子句中。 3.order by不能再子查询中使用，在主查询中可以使用。group by 可以用来在子查询中如order by 相同的功能。 4.返回多于一个子查询只能用于多个值运算符，比如in 5.between 操作符不能与子查询使用，但是可以在子查询中使用。
实战
1.创建student表，结构如下
create table student(
id int(10) primary key not null auto_increment comment '学号',
name varchar(20) not null comment '姓名',
sex varchar(4) null comment '性别',
birth year null comment '出生年份',
department varchar(20) not null comment '院系',
address varchar(50) null comment '家庭住址'
);2.创建score表，结构如下
create table score(
id int(10) primary key not null auto_increment comment'编号',
stu_id int(10) not null comment'学号',
c_name varchar(20) null comment '课程名',
grade int(10) null comment '分数'
);3.给student表添加以下数据
insert into student values(901,'张老大','男',1985,'计算机系','北京市海淀区');
insert into student values(902,'张老二','男',1986,'中文系','北京市昌平区');
insert into student values(903,'张三','女',1990,'中文系','湖南省永州市');
insert into student values(904,'李四','男',1990,'英语系','辽宁省阜新市');
insert into student values(905,'王五','女',1991,'英语系','福建省厦门市');
insert into student values(906,'王六','男',1988,'计算机系','湖南省衡阳市');4.给score表添加以下数据
insert into score values(1,901,'英语',80);
insert into score values(2,902,'计算机',65);
insert into score values(3,902,'中文',88);
insert into score values(4,904,'中文',95);
insert into score values(5,904,'计算机',70);
insert into score values(6,904,'英语',92);
insert into score values(7,905,'英语',94);
insert into score values(8,906,'计算机',90);
insert into score values(9,906,'英语',85);5.查询student表的第2条到第4条记录
select * from student limit 1,3;6.从student表查询所有学生的学号、姓名和院系的信息
select id,name,department from student;7.从student表中查询计算机系和英语系的学生的信息
select * from student where department in ('计算机系','英语系');8.从student表中查询年龄18-22岁的学生信息
select id,name,sex,(2018-birth) as age,department,address from student where 2018-birth <= 22 and 2018 - birth >= 18;9.从student表中查询每个院系有多少人
select department,count(id) from student group by department;10.从score表中查询每个科目的最高分
select c_name,max(grade) from score group by c_name;11.用连接的方式所有学生的信息和考试信息
select student.id,name,sex,birth,department,address,c_name,grade
from student inner join score on student.id = stu_id;12.计算每个学生的总成绩
select student.id,name,sum(grade)
from student inner join score on student.id = stu_id group by student.id;13.计算每个考试科目的平均成绩
select c_name,avg(grade) from score group by c_name14.将计算机考试成绩按从高到低进行排序
select stu_id,name,grade from score where c_name = '计算机' order by grade desc