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
课堂联系
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语句运行结果如下图所示:
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语句运行结果如下图所示:
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语句运行结果如下图所示:
查出name字段值长度为3的行的所有信息:
select * from commodity where name like '___';
sql语句运行结果如下图所示:
5.7 order by关键字
order by 关键字使用示例,desc降序从大到小,asc升序从小到大:
select * from commodity order by num desc;
sql语句运行结果如下图所示:
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.多表连接查询
内连接,左连接,右连接三种连接方式查询的区别可以通过下面三张图总结:
内连接是根据条件取两个表的交集
左连接是根据条件取两个表的交集+左表中没有被取进交集的部分
右连接是根据条件取两个表的交集+右表中没有被取进交集的部分
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