最近准备数据库的上机考试,所以在这里做一些基础的语法总结。
资料链接:https://pan.baidu.com/s/1xJrwGpCREvItNZGi3iDx_g
提取码:bgz6
说明
这里给出所使用的练习数据库的链接,以下是该数据库的五张表(按顺序依次为book、borrow、category、major、student),可能看不清,可以下载数据库看。本文是基于这个数据库进行一系列sql操作进行一些基础练习,最后综合练习。如有不正确的地方,还望指正。
Part 1:DDL(Data Definition Language)
DDL指用来定义数据库结构的语句,如Create、drop等。
1. 创建数据库,指定字符集和排序规则
create database if not exists practice
default character set utf8mb4
default collate utf8mb4_unicode_d;
2. 创建一张表
所要关注的几个问题:数据类型、主键、外键的表示、外键修改时的操作
2.1 一般数据类型
- charactor(n)/char(n):定长字符串
- varchar(n):变长字符串
- int/integer
- numeric(p,d):确定精度的定点数,位数(p-d)+d
- 等
2.2 行内定义主键、外键
create table aaa(
attr1 character(20) primary key,
attr2 varchar(20) references student(stuid),
attr3 integer);
- 创建完成后,可以通过 show tables; 查看当前数据库中已有的表。
- 主键和外键既可以通过行内创建,也可以在行外创建
2.3 行外定义主键、外键
create table eee(
aa1 char(20),
aa2 char(20),
aa3 varchar(255),
primary key(aa1,aa2),
constraint fk_0 foreign key(aa3) references bbb(attr1) on delete cascade on update cascade);
- fk_0是自定义的外键名,非必须,系统会自动生成
- 定义外键比较容易报错,主要有以下几个原因:要关联的字段类型不一致;要关联的表的编码不一致;两张表需要关联的字段都已经有记录,记录的内容不满足外键要求等等,相关问题推荐链接
- 自己写外键的时候,遇到的问题比较奇葩,单个字段时,没加括号导致报错,浪费了很多时间啊
- 外键字段更改时,应该采取什么操作?即update、delete是关联的字段该怎么办呢?主要有下面几种做法:
(1)no action & restrict:阻止这样的update、delete操作
(2)cascade:级联操作,关联的字段值一并修改
(3)set null:关联的字段值设为空
2.4 定义表时其他一些声明
- not null:要求不为空
- default <value>:默认为某值
- check(check的条件):例如,A表中price real check(price>=5),若更新或插入的price为4则会被拒绝。
3. 删除操作
- drop database <数据库名>;
- drop table <表名>; 等
4. 修改操作
alter可以做很多,修改表名、修改字段名、字段类型、增减/删除字段、添加约束等等。自己在课程中倒是很少用到,就不举例了,应该做工程开发应该才会很常用吧。
Part 2:DML(Data Manipulation Language)
指是一个查询、更新数据的操作:insert、delete、update、select-from-where
1. select - from - where
就从实例说明吧~顺序是从简单到复杂,这部分练习罗列了基础常用的一些查询应用,答案在链接的资源中,题目是自己根据知识点瞎编的,答案也是自己写的,因为数据量很小,个人检查过是ok的;另外也许有多种方法实现查询目的的方法,给出的不一定是最优的;如果有歧义或错误还望指出。
- 查询student表
- 查询student表,仅返回stuname字段
- 【distinct】查询major表,返回所有的学院,要求去重
- 【rename】查询student表,返回stuname、degree字段并将 stuname 重命名为 name
- 查询book表,返回bookname、price字段并将price打八折,price重命名为discount
- 查询book表,返回price和catid均相同的书对的bookname
- 查询book表,返回price不超过40的书的bookname和price
- 【all】查询book表,返回price最高的书的bookname和price
- 【any】查询book表,返回price不是最低的书的bookname和price
- 【%】查询student表,返回姓“王”的学生的stuid和stuname
- 【%】查询student表,返回名字中含有“飞”字的学生的stuid和stuname
- 【desc】查询book表,返回bookname、price字段,按照price降序
- 【asc】查询book表,返回bookname字段,按照price升序
- 【natural jion】查询book、category表,返回bookname和所对应的分类名catname,要求使用natural jion
- 查询book、category表,按照price降序,返回price小于50的 bookname 和 所对应的分类名catname
- 【count】查询student、major表,返回“计算机与控制工程学院”的学生数
- 【avg】查询book、category表,返回“经济”类书的price的平均值(聚合操作还有min、max、sum等,就不再举例了)
- 【group by】查询book表,返回每个类别书的平均price
- 【exists】查询student表,返回专业中只有一个学生的stuname(即不存在多个学生学习同一专业)
- 【group by + having】查询book、category表,返回数目总数大于1的类别名catname
接下来的就不说出应该查哪个表啦
- 返回所借书的price小于50的学生的stuname和degree
- 【子查询】返回所有借阅《算法导论》的学生的专业majorname
- 【子查询】返回“计算机技术”类且price和《货币银行学》相等的书bookname
- 【子查询】返回所借书的price小于50的学生的stuname和degree(要求from后仅有一个关系)
- 【group by】返回 2010-10-13 00:00:00 以后借书的每一个学生stuname的个人借书量
以上主要是sql语句的内容(再次说明,仅是基础的练习,应对考试的话是不够的)后续还有两部分内容:
① 数据库大作业(微信小程序+php实现,包含事务操作、存储过程、触发器控制等)
② 数据库设计部分的知识点小结(考试复习)