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_name

14.将计算机考试成绩按从高到低进行排序

select stu_id,name,grade from score where c_name = '计算机'  order by grade desc