表的创建
-
创建一个数据库
create database QAQ default character set utf8;#创建了一个名为QAQ的数据库并将其默认字符集设置为utf8
利用show databases来查看创建的数据库
show databases;
![1adc5d31abf5c68a13252b8f9b6a67c7.png](https://i-blog.csdnimg.cn/blog_migrate/72eebf1b4d86fc00ea2325e19718efd7.png)
数据库创建成功
用use qaq来进入该数据库并创建表
use qaq;#进入该数据库
#创建了一个名为Student的表,其中有两个字段,学号和长度为20个字符的名字。
#这里说一下char(10),char会固定占用10个字符的空间,而varchar(10),则不会固定占用10个字符#的空间,如果名字只有3个字符则只会占用3个字符和一个结束字符
create table Student(
Sid int primary key auto_increment comment '学号',
Sname char(20) not null comment '姓名',#comment为字段注释方便理解代码
Sex enum ('男','女'),#enum单选
Slike set ('唱','跳','rap') #set枚举类型,多选
)
#创建了一个学生成绩表,其中的学号引用了学生表的学号并用了级联删除(on delete cascade)和级联修改(on update cascade)这两个的作用是当在父表做修改或删除时子表也会自动修改
create table Grades(
Sid int primary key,
Score decimal(5,2) comment '学生成绩',#decimal(5,2),表示总共5位保留两位小数
foreign key (Sid) references Student(Sid) on delete cascade on update cascade
)
表结构的修改
-
首先来查看表的结构,利用show create table 表名;
show create table student;
#以下是查询结果,其中会补上写的时候省略的内容
CREATE TABLE `student` (
`Sid` int NOT NULL AUTO_INCREMENT COMMENT '学号',
`Sname` char(20) NOT NULL COMMENT '姓名',
`Sex` enum('男','女') DEFAULT NULL,
`Slike` set('唱','跳','rap') DEFAULT NULL,
PRIMARY KEY (`Sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 #引擎为InnoDB,字符型为utf8mb3, 若没有写字符型将和数据库一样
show create table Grades;
#以下是查询结果,其中会补上写的时候省略的内容
CREATE TABLE `grades` (
`Sid` int NOT NULL,
`Score` decimal(5,2) DEFAULT NULL COMMENT '学生成绩',
PRIMARY KEY (`Sid`),
CONSTRAINT `grades_ibfk_1` FOREIGN KEY (`Sid`) REFERENCES `student` (`Sid`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
#`grades_ibfk_1`为外键的名字,创建表时没写的话系统会自动生成,可以按上面的格式来创建有名字的外键约束
-
表名的修改
#alter table 表名 rename to 新表名
#rename table 表明 to 新表名
两个都可以
-
字段的修改
-
添加字段
#alter table 表名 add 字段及其相关属性,如下
alter table student add ss int not null default 0;
-
删除字段
alter table 表名 drop 删除的字段名
#注意:如果要删除的字段与其它表有联系则无法删除,如student表中的sid被grades表中的sid引用所以无法删除,要先删除外键约束(alter table 表名 drop 外键名)或取消外键约束(set foreign_key_checks = 0 等于1则恢复外键约束)
-
字段修改
alter table 表名 change 原字段名 新字段名 新数据类型 #当只想修改数据类型或字段名时不变的地方照抄就好了
alter table 表名 modify 字段名 新数据类型 #用法同上但不能修改字段名
#修改字段的排序位置
alter table 表名 change 字段名1 字段名1 数据类型 first|after 字段名2
-
约束的修改
-
删除约束
alter table 表名 drop 约束名
#删除外键约束时,当父表的约束被删除了。那么子表和父表的联系就断了,因此要将子表的约束也一起删掉
#删除唯一约束
alter table 表名 drop index 唯一约束名(如创建表时没写则系统自动生成)
#删除默认约束值,以下两个都可以
alter table 表名 change 原字段名 原字段名 原数据类型 default null;
alter table 表名 modify 原字段名 原数据类型 default null;
#删除非空约束,以下两个都可以
alter table 表名 change 原字段名 原字段名 原数据类型 null;
alter table 表名 madify 原字段名 原数据类型 null;
-
增加约束
alter table 表名 add 要添加的约束
#添加主键约束
alter table 表名 add primary key (列名)
#添加外键约束
alter table 表名 add constraint 外键名 foreign key (字表字段名) references aa (父表字段名)
#添加唯一约束
alter table 表名 add constraint 唯一约束名 unique(列名)
#添加默认约束
alter table 表名 change 字段名 字段名 数据类型 default 默认值
alter table 表名 modify 字段名 数据类型 default 默认值
#添加非空约束
alter table 表名 change 字段名 字段名 数据类型 not null
alter table 表名 modify 字段名 数据类型 not null
-
选项的修改
#修改存储引擎
alter table 表名 engine = 新的引擎类型
#修改默认字符集
alter table 表名 default charset = 新的字符集
#修改索引关键字压缩方式的语法规则
alter table 表名 pack_keys = 新的压缩类型
#修改自增字段的初始值
alter table 表名 auto_increment = 新的初始值
-
其他操作
#查看当前数据库中的所有表
show tables;
#查看指定表的表结构
desc 表名
#查看指定表的详细信息
show create table 表名
#复制表
#将原表的结构复制到新表中,不包含数据
create table 新表名 like 原表名
#将原表的数据和结构复制到新表中
create table 新表名 select * from 原表名
#删除表
#要注意如果要删除的表是某个表的父表时,要先解除外键约束或删除外键约束
drop 表名
数据的处理
-
插入数据
#插入完整的一条数据
insert into 表名 (字段名1,字段名2...) values (字段一的值,字段二的值...);
#以上方的学生表为例
#插入不完整的数据要写下字段并插入的值要按照字段顺序来
insert into student (Sid,Sanme) values (123,'张山');
#插入整条数据字段可以不写,但顺序必须按照表中的字段顺序来添加
insert into student values (123,'张三','男','唱,跳,rap');
#插入多条数据,每条数据间用逗号隔开
insert into student values
(456,'阿莹','女','唱'),
(789,'阿晴','女','跳,rap');
#插入查询结果(这里不多解释,等到查询的时候就可以看懂了)
#只插入原表的数据
insert into 新表名 (新表字段名1,新表字段名2...) select (旧表字段名1,旧表字段名2...) from 原表名
#将原表的数据和结构插入到新表中
create table 新表名 select * from 原表名
#用replace来插入数据(这里只写语法),要注意replace是先将原有的数据删除了再插入新的数据,所以若原先的字段与其他的字段有联系(外键),则要先解除联系,若原先没有数据则直接插入
replace into 表名 (字段名1,字段名2...) values (字段1的值,字段2的值...);
replace into 目标表名 (目标表字段名1,目标表字段名2...) select (旧表字段名1,旧表字段名2...) from 旧表名 where 条件
replace into 表名 set 字段名1 = 字段1的值,字段名2 = 字段2的值...;
-
修改数据
#修改特定数据
update 表名 set 字段名1 = 字段1的值,字段名2 = 字段2的值... where 限制条件
#修改所有数据(因为是所有数据所以没有限制条件)
update 表名 set 字段名1 = 字段1的值,字段名2 = 字段2的值...;
-
删除数据
#删除特定数据
delete from 表名 where 限制条件
#删除所有数据
delete from 表名;
#采用truncate语句清空数据记录
truncate 表名;
数据查询
-
selsct语句的概述
select 字段列表
from 表名
where 表的限制条件
group by 字段列表 having 对分组结果的限制条件
order by 字段名 [asc|desc]
limit 开始位 显示个数
先来讲解一下上面的代码
select后面是要查询的表中的字段,如果要查询全部字段则可以用*来代替
where是对表中的限制
group by 是分组查询的内容,而havign是对分组结果的限制条件
order by是对查询结果的升序显示(asc)或降序显示(desc)
limit 是显示查询结果的某一段结果,如我的查询结果有30条,但我只想显示第5条到第10条的内容就可以写为limit 4 5;(前面的4为第5条结果的下标,5为往下的个数)
-
单表查询
#查询特定的字段
select 字段1 as 别名1,字段2 as 别名2... from 表名 #(其中的as可以省略)
select sid 学号,sname 学生姓名 from student;
#查询表中所有数据
select * from 表名;
#以上的查询结果会存在一定的重复性,如我要查询所有学生所选的课,而学生1选了课1和课2,学生2选了课2和课3,那么查询结果就会是课1,课2,课2,课3,其中课2是存在重复的,此时就可以用 distinct
select distinct 字段列表 from 表名;
#限制显示记录数
select * from 表名 limit 5; #若前一位不写则默认从下标为0的第一个开始
#where 条件查询
#如查询学生表中女生的信息
select * from student where sex = '女'; #where后后跟的是一个条件表达式,因此可以用and or && || > >= 等相关符号;
#确定集合的查询
#查询在集合中的值
select 字段列表 from 表名 where 字段 in (值1,值2...); #如果在集合中就写出来
#查询不在集合中的值
select 字段列表 from 表名 where 字段 not in (值1,值2...);
#模糊查询
#查询名字中含荧的学生
select * from student where sname like '%荧%'; #其中%表示0个后无穷个字符
#查询姓王的三个字的学生
select * from student where sname like '王__'; #这里有两个下划线,只是显示的不是很清楚
#转意字符\,可以将\后面的含有特殊意义的单个字符变成普通字符
#查询名字中含有%的学生
select * from student where sname like '%\%%'; #其中前后两个%含有特殊含义,而\后面的%就只是一个%
#查询名字中含有\的学生,因为\原先是有特殊含义的,而且又不能用它本身来转义,所以要暂时指定另一个字符为转义字符
select * from student where sname like '%@\%' escape '@'; #这里将@符号指定为转义字符了;
#空值的查询
select * from student where sname is null; #查询非空值只需将is null改为not null 就好了
#对查询结果进行排序
select * from student order by [asc|desc];
#聚合查询 (以下都可加distinct,均为去掉重复值是可以省略不写的)
count([distinct] *) #返回行数,不管是否为NULL,列名就是字段名
count(列名) #返回指定列中非NULL值的个数
sum(列名) #返回指定列中非NULL值的之和
avg(列名) #返回指定列中非NULL值的平均值
max(列名) #返回指定列中非NULL值的最大值
min(列名) #返回指定列中非NULL值的最小值
###聚合函数通常是用在select后,不能出现在where之后
#分组查询(group by)
#查询男女学生的人数,那么结果就是一个二维表。
select sex,count(*) from student group by sex; #其中count(*)是用来返回行数的
#查询选修了3门以上课程的学生学号
select sid,count(*) from register group by sid having count(*) > 3; #having是对分组查询的结果进行限制输出
#这里提一下分组查询的一个错误
#错误代码
SELECT book.*, lend.LendDate, lend.BackDate, COUNT(lend.BookID) AS BorrowCount
FROM book LEFT OUTER JOIN lend
ON book.BookID = lend.BookID
GROUP BY book.BookID;
#其中是基于book表对lend表中的lend.bookid进行分组查询,但lend.LendDate, lend.BackDate也在lend表中,违反了ONLY_FULL_GROUP_BY模式,该模式要求在SELECT列表中的所有列都必须在GROUP BY子句中列出,或者在SELECT列表中使用聚合函数进行计算。
所以改进的代码如下
SELECT book.*, lend.LendDate, lend.BackDate, COUNT(lend.BookID) AS BorrowCount
FROM book LEFT OUTER JOIN lend
ON book.BookID = lend.BookID
GROUP BY book.BookID, lend.LendDate, lend.BackDate;
#但它的查询结果有一个缺点,会将图书按book.BookID, lend.LendDate, lend.BackDate;进行分组显然是有重复且不合适的。
#再改进的代码
SELECT book.*, L.lend_count, L.LendDate, L.BackDate
FROM book
LEFT JOIN (
SELECT BookID, COUNT(BookID) AS lend_count, MIN(LendDate) AS LendDate, MAX(BackDate) AS BackDate
FROM lend GROUP BY BookID
) AS L ON book.BookID = L.BookID;
#这回没有重复项了,但又出现问题了,就是如果这本书被借阅了两次或更多,它的借阅日期仅最早借阅日期以及最晚归还日期。(可能是我要查询的信息出了逻辑上的问题。。。QAQ~~~)
###查询的代码灵活性还是比较高的只要不违背select概述里的大概顺序就可以,更多的组合可以自行了解,这里不过多讲述
-
多表查询
这里要先引入表做例子
#多表查询是用于要查询的结果在多个表中的情况,因此连接条件(主、外键)至少是表数减一
#内连接查询(可以在where的末尾外添加分组查询)
#格式一
select [distinct] 字段列表 from 表名1,表名2... where 连接条件和一些限制条件;
#格式二(这里仅作了解,感兴趣的可自行深入)
select [distinct] 字段列表 from 表名1 join 表名2 on 连接条件
join 表名3 on 连接条件 ... where 表的一些限制条件
#外连接:具体参内连接,只是添加了一个left/right/full
select 字段列表 from 表1 left/right/full 表名2 on 连接条件 where 表的一些限制条件;
#其中表1为左表,表2为有右表,而left是除了满足连接条件的结果外还包括左表中不满足连接条件的结果,right则反之。
#联合查询(union[all],其中all表示将所有行显示出来,不加all则去掉重复的行),主要是将两个查询结果合并增加其行数,如查询计算机专业的一二班的学生,其中一二班的表的格式是一样的(而且需要显示的列数也要一样),所有可以用联合查询。
select * from 专业表 where 专业名 = '计算机' and 班级 = '一班' union
select * from 专业表 where 专业名 = '计算机' and 班级 = '二班'
-
子查询(重点!!!)即查询的嵌套
-
不相关子查询:子查询和父查询是不相关的,因此查询是由内而外的层层进行
#以下代码仅作为讲解列子
#查询选了计算机专业的学生信息
select * from 学生表 where 学号 in (select 学号 from 专业表 where 专业名 = '计算机')
#由内而外的查询,先在子查询中从专业表中找到选了计算机这个专业的学生的学号,而因为专业表中没有学生信息,所以再从学生表中去查询学生信息。由于子查询的结果是一个集合,所以用in;
-
相关子查询:子查询的查询条件是依赖于父查询的
#同一个表的两次查询时,要取别名来区分
#查询成绩比该课程平均成绩高的学生成绩表,包括学号,课程号,成绩
#注册表包含学号,课程号,成绩和选课时间
select 学号,课程号,成绩 from 注册表 r1 where 成绩 > (select avg(成绩) from 注册表
r2 where 课程号 = r1.cid)
#浅浅分析一下,首先在子查询中,课程号 = r1.cid,是r2表中的课程号等于r1的课程号,在表1中选取一个学生,用这个学生的课程号去匹配表2,找到表2中该课程号的平均成绩(avg(成绩)),子查询就返回这个平均成绩,然后到父查询中用表1中该学生的学号去匹配它的成绩,并与平均成绩进行比较,若符合则输出该学生的信息,否则进行下一位学生的查询,如此循环直到表1中的学生都查询完毕。
-
这块的内容相对比较简单,就不过多解释了,只要尝试去用一下就能理解了。。。
![36b97ea3c4efe3b1192ebb9678fb0514.jpeg](https://i-blog.csdnimg.cn/blog_migrate/6573698e50a16febcc2ea04a84a1ad0b.jpeg)
后面补的一些内容
#这里添加点东西哈
update lend,reader set lend.BackDate = '2018-1-1' where reader.ReaderName = '赵青青' and lend.BookID = 'B004' and lend.ReaderID = reader.ReaderID
#这段代码表明,连接还可以用再修改上,因为修改的信息跨越了两张表,所以用连接。那么再一次体现了代码的灵活性,还是得多写写才理解啊QwQ~~~