MySQL 03 高级查询(一)
文章目录
一、学习目标
- 掌握使用SQL语句修改、删除表的方法
- 掌握使用SQL语句实现对表添加/删除约束的方法
- 掌握使用SQL语句对表进行增删改操作的方法
- 掌握简单子查询的用法
二、调整数据库的表结构
尽管项目设计阶段,做了详尽周密的设计,但软件开发过程中,总会出现需求变更的情况,甚至会对已经创建的数据库的表结构的操作
- 如:添加、删除字段等
如何根据变更的需求调整数据库的表结构?
- 使用SQL语句
三、修改表
语法:
- 修改表名
ALTER TABLE <旧表名> RENAME [TO] <新表名>;
- 添加字段
ALTER TABLE 表名 ADD 字段名 数据类型 [属性];
- 修改字段
ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 [属性];
- 删除表中的字段
ALTER TABLE 表名 DROP 字段名;
示例:
假设hospital数据库中有日志表(log)结构如下:
序号 字段名称 字段说明 数据类型 长度 约束 说明 1 id 日志编号 int 4 非空 2 time 操作时间 date 非空 3 action 操作记录 varchar 50 非空 4 comment 备注 varchar 70 新增字段 创日志表(log)表脚本:
USE hospital; #创建日志表 DROP TABLE IF EXISTS log; CREATE TABLE IF NOT EXISTS log( id INT(4) NOT NULL COMMENT '日志编号', time DATE NOT NULL COMMENT '操作时间', action VARCHAR(50) NOT NULL COMMENT '操作记录' )COMMENT='日志表'; SHOW tables;
使用SQL语句实现以下功能:
- 将log表改名为action_log
- 添加备注(comment)字段,数据类型为varchar(70)
- time字段名改为actionTime,数据类型改为DATETIME
- 删除备注(comment)字段
#修改表名 ALTER TABLE log RENAME action_log; #添加字段 ALTER TABLE action_log ADD `comment` VARCHAR(70); #修改字段 ALTER TABLE action_log CHANGE time actionTime DATETIME NOT NULL COMMENT '操作时间'; #删除字段 ALTER TABLE action_log DROP `comment`;
四、设置主外键约束
问题:
- 如何将action_log表中id字段设置为主键?
添加主键语法:
ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY 表名(主键字段);
添加主键示例:
alter table action_log add constraint pk_action_log primary key (id);
添加外键语法:
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键字段) REFERENCES 关联表名(关联字段);
添加外键示例:
alter table action_log add constraint pk_action_log primary key (id);
注意事项:
MySQL中,MyISAM存储类型的表不支持外键。因此,可以通过建立逻辑关联的方式保证数据的完整性和一致性。
五、添加约束 (练习)
数据库结构:
#创建数据库 create database t147hospital; #选择hospital数据库 USE t147hospital; #1、创建Patient表 CREATE TABLE IF NOT EXISTS patient( patientID INT(4) NOT NULL COMMENT '病人编号' PRIMARY KEY AUTO_INCREMENT, #非空,主键,自增 password VARCHAR(20) NOT NULL COMMENT '登录密码', birthDate DATE COMMENT '出生日期', gender VARCHAR(4) DEFAULT '男' NOT NULL COMMENT '性别',#非空,默认值“男” patientName VARCHAR(50) NOT NULL COMMENT '病人姓名', phoneNum VARCHAR(50) COMMENT '联系电话', email VARCHAR(70) COMMENT '邮箱', identityNum VARCHAR(20) UNIQUE KEY COMMENT '身份证号', #唯一 address VARCHAR(255) DEFAULT '地址不详' COMMENT '地址' )COMMENT='病人表'; #表注释“病人表” #2、创建科室表: CREATE TABLE IF NOT EXISTS `department` ( `depID` int(0) NOT NULL AUTO_INCREMENT COMMENT '科室编号', `depName` varchar(50) NOT NULL COMMENT '科室名称', PRIMARY KEY (`depID`) ) COMMENT = '科室表'; #3、创建科室检查项目关系表 CREATE TABLE IF NOT EXISTS `department_checkitem` ( `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '关系编号', `depID` int(0) NOT NULL COMMENT '科室编号', `checkItemID` int(0) NOT NULL COMMENT '检查项目编号', PRIMARY KEY (`id`) ) COMMENT = '科室可开检查项目关系表' ; #4、创建体检项目表 CREATE TABLE IF NOT EXISTS `checkitem` ( `checkItemID` int(0) NOT NULL AUTO_INCREMENT COMMENT '检查项目编号', `checkItemName` varchar(50) NOT NULL COMMENT '检查项目名称', `checkItemCost` float NOT NULL COMMENT '检查项目价格', PRIMARY KEY (`checkItemID`) ) COMMENT = '检查项目表'; #5、创建处方表 CREATE TABLE IF NOT EXISTS `prescription` ( `examID` int(0) NOT NULL AUTO_INCREMENT COMMENT '检查编号', `patientID` int(0) NOT NULL COMMENT '病人编号', `depID` int(0) NOT NULL COMMENT '开处方的科室编号', `checkResult` varchar(500) NULL DEFAULT NULL COMMENT '检查结果', `checkItemID` int(0) NOT NULL COMMENT '检查项目编号', `examDate` datetime(0) NOT NULL COMMENT '检查日期' ) COMMENT = '处方表' ;
添加数据:
#为病人表(patient)添加数据 #为病人表(patient)添加数据 INSERT INTO `patient` VALUES (1, '123456', '1985-06-07', '女', '夏颖', '13800000001', 'ying.xia@qq.com', '110000198506071100', '厦门市'); INSERT INTO `patient` VALUES (2, '234567', '1985-06-08', '男', '李政', '13800000002', 'lizheng@163.com', '210000198506082100', '长春市'); INSERT INTO `patient` VALUES (3, '345678', '2010-03-02', '女', '李沁', '13800000003', 'liqin@sohu.com', '120000201003021200', '合肥市'); INSERT INTO `patient` VALUES (4, '456789', '1999-01-02', '女', '李思雨', '13800000004', 'siyu.li@hotmail.com', '150000199901021500', '北京市'); INSERT INTO `patient` VALUES (5, '567890', '2008-10-17', '男', '夏天', '13800000005', 'tian.xia@qq.com', '210000200810172100', '长春市'); #批量新增数据(使用一条INSERT语句将表格里所有病人记录插入到病人表) INSERT INTO `patient` VALUES (6, '678901', '1999-03-08', '男', '刘占波', '13800000006', 'zhanbo@163.com', '210000199903082101', '惠州市') ,(7, '789012', '1987-05-02', '女', '廖慧颖', '13800000007', 'huiying@qq.com', '220000198705022200', '广州市') ,(8, '890123', '1975-03-02', '男', '李伟忠', '13800000008', 'wz@qq.com', '230000197503022300', '沈阳市') ,(9, '901234', '1986-10-11', '男', '姚维新', '13800000009', 'ywx@hotmail.com', '310000198610113100', '北京市') ,(10, '012345', '1975-03-04', '男', '陈建', '13800000010', 'cz@qq.com', '320000197503043200', '北京市') , (11, '098765', '1992-01-01', '女', '林永清', '13800000011', 'yongqing@qq.com', '330000199201013300', '长春市') ,(12, '987654', '1993-03-02', '女', '李亚', '13800000012', 'liya@qq.com', '340000199303023400', '保定市') ,(13, '909000', '1995-02-12', '女', '李菲', '13887676500', 'fei.zhang@qq.com', '610000199502126100', '北京市'); #为科室表(department)添加数据 INSERT INTO department(depName) VALUES ('急诊科'),('呼吸科'),('内科'); #为科室可开检查项目关系表(department_checkitem)添加关系数据 INSERT INTO department_checkitem(depID, checkItemID) VALUES (1,1),(1,2),(2,1),(2,5),(3,1),(3,2),(3,3),(3,4); #为处方表(prescription)添加数据 INSERT INTO prescription(patientID, depID, checkResult, checkItemID, examDate) VALUES (1,1,'正常',1,'2020-01-02'), (1,1,'正常',2,'2020-01-02'), (3,2,'肺炎支原体阳性',5,'2020-04-05'), (1,1,'正常',1,'2020-02-06'), (8,3,'正常',4,'2020-03-02'), (8,3,'血糖偏高',3,'2020-03-02'), (8,3,'正常',1,'2020-03-02'), (10,3,'正常',3,'2020-03-02'), (1,1,'白细胞数量偏高',1,'2020-07-08'); #录入体检项目数据 INSERT INTO `checkitem` VALUES (1, '血常规', 28); INSERT INTO `checkitem` VALUES (2, '尿常规', 20); INSERT INTO `checkitem` VALUES (3, '血脂、血糖检查', 25); INSERT INTO `checkitem` VALUES (4, '凝血五项', 50); INSERT INTO `checkitem` VALUES (5, '肺炎支、衣原体(快速)', 66); INSERT INTO `checkitem` VALUES (6, 'CT', 110);
需求说明
- 为prescription表添加主键约束
- 病人编号、开处方的科室编号、检查项目编号、检查时间构成组合主键
- 为department_checkitem表添加外键约束
- 主表checkitem和从表department_checkitem通过checkItemID字段建立主外键关联
- 主表department和从表department_checkitem通过depID字段建立主外键关联
#1.为prescription表添加主键约束 alter table prescription add constraint pk_prescription primary key (examID);
如果项设置复合主键,复合主键的特点是同时创建、同时删除,所以需要把主键删除,如果表中设置了自增,需要先把自增删除,才可以删除主键,如果不先删除自增,而直接删除主键的话会报错。
#2.病人编号、开处方的科室编号、检查项目编号、检查时间构成组合主键 #删除语法: alter table prescription drop primary key; #设置组合主键 alter table prescription add constraint pk_prescriptions primary key (patientID,depID,checkItemID,examDate);
#3.为department_checkitem表添加外键约束 #主表checkitem和从表department_checkitem通过checkItemID字段建立主外键关联 alter table department_checkitem add constraint fk_checkItemID foreign key(checkItemID) references checkitem (checkItemID); #主表department和从表department_checkitem通过depID字段建立主外键关联 alter table department_checkitem add constraint fk_depID foreign key(depID) references department (depID); #查看表结构 desc department_checkitem;
六、数据操纵语言
6.1.DML(Data Manipulation Language)
DML是对数据库中表记录的执行哪些操作?
- 插入(INSERT)
- 插入单行数据
- 插入多行数据
- 将查询结果插入到新表
- 更新(UPDATE)
- 删除(DELETE)
软件开发人员日常使用最频繁的操作
6.2.DML语句——插入单条数据
语法:一次向表中插入一条记录
INSERT INTO 表名 [(字段名列表)] VALUES (值列表);
注意:
- 字段名是可选的,如省略,则依次插入所有字段
- 多个列表和多个值之间使用逗号分隔
- 值列表和字段名列表一一对应
- 如果插入表中的部分字段数据,则字段名列表必填
示例:
insert into patient (`password`, birthdate, gender, patientName, phoneNum, email, identityNum, address) values ('123456', '1995-06-07', '男', '艾编程', '13500000000','abc@163.com', '110000198506071100', '长沙市');
6.3.DML语句——插入多条数据
语法:一次向表中插入多条记录
INSERT INTO 表名 [(字段名列表)] VALUES (值列表1), (值列表2), ……, (值列表n);
示例: 向patient表中插入多条病人记录
insert into patient (`password`, birthDate, gender, patientName,phoneNum, email, identityNum, address) values ('234567','1985-06-08','男','李政', '13800000002','lizheng@163.com','210000198506082100','长春市'), ('345678','2010-03-02','女','李沁','13800000003','liqin@sohu.com','120000201003021200','合肥市'), ('456789','1999-01-02','女','李思雨', '13800000004','siyu.li@hotmail.com','150000199901021500','北京市'), ('567890','2008-10-17','男','夏天', '13800000005','tian.xia@qq.com','210000200810172100','长春市');
6.4.DML语句——将查询结果插入到新表
示例
- 将patient表中patientName和address字段的数据保存到新表patient_address中
- 注意:如果新表已存在,将会报错!
create table patient_address (select patientName, address from patient);
6.5.DML语句——更新数据
语法:
更新数据记录
UPDATE 表名 SET 字段1='值1', 字段2='值2', …, 字段n='值n' [WHERE 条件];
示例:
修改patient表中姓名为“夏颖”的病人的地址和邮箱
update patient set email='ying.xia@qq.com', address='厦门市' where patientName='夏颖';
#将姓名为刘占波的病人的密码修改为8765 UPDATE patient SET password="8765" WHERE patientName="刘占波"; #将血常规检查的价格减少2元钱 UPDATE checkitem SET checkItemCost= checkItemCost-2 WHERE checkItemName="血常规";
6.6.DML语句——删除数据
语法:
删除数据记录
DELETE FROM 表名 [WHERE条件];
TRUNCATE TABLE 表名;
示例:
1.从patient_address表中删除“夏天”的记录
delete from patient_address where patientName='夏天';
2.删除patient_address表中的所有行
truncate table patient_address;
注意:
使用 truncate 语句删除表中记录后,将重置自增列,表结构及其字段、约束、索引保持不变,执行速度比delete语句快
6.7.比较DROP、DELETE与TRUNCATE语句
执行速度:
- 一般来说,DROP>TRUNCATE>DELETE.
- 使用DROP、TRUNCATE语句时,要慎重.
- 如果使用DELETE语句删除表中部分数据,要带上WHERE子句,且要注意表空间要足够大.
经验:
- 如果要删除表,使用DROP语句
- 如果要保留表但删除表中所有数据,如果与事务无关,可以使用TRUNCATE语句;
- 如果与事务有关,则使用DELETE语句
DROP DELETE TRUNCATE 类型 DDL 会隐式提交,不能回滚 DML 每次从表中删除一行数据的同时将改行的删除操作记录在redo和undo表空间中,以便进行回滚和重做操作 需要手动提交操作才能生效,可通过ROLLBACK撤销操作 DDL 会隐式提交,不会记录日志,不能回滚 功能 删除表结构及所有数据,并将标所占用的空间全部释放 可根据条件删除表中满足条件的数据,如果不指定WHERE子句,则删除表中所有记录 删除表中所有记录,并将重建表结构
七、数据查询语言
Data Query Language
- 用于查询数据库的表中数据
- 是数据库中最为核心的语言,使用频率最高
语法:
SELECT <字段名列表> FROM <表名或视图> [WHERE <查询条件>] [GROUP BY <分组的字段名>] [HAVING <条件>] [ORDER BY <排序的字段名> [ASC 或 DESC]]
/* 字段名列表为要查询的字段名,各字段之间使用逗号分隔,若查询表中所有字段使用“*”号表示。 FROM后的表名为要查询的数据的来源,可以单个或多个。 WHERE子句为可选项,指定查询的条件。 GROUP BY子句表明查询出来的数据按指定字段进行分组。 HAVING子句用于筛选组 ORDER BY子句指定按什么顺序显示查询出来的数据,什么字段的升序(ASC)或降序(DESC)。 */
经验:
为了提高语句的执行效率,建议:编写查询语句时,采用SELECT 字段列表 FROM 表名 WHERE 条件表达式;
示例:
- 查询所有性别为女的病人信息,按病人编号降序显示查询结果
SELECT patientID, patientName, gender, birthDate, phoneNum, email, identityNum, address FROM patient WHERE gender='女' ORDER BY patientID DESC;
运行结果:
八、常用函数
SQL中将一些常用的数据处理操作封装起来,作为函数提供给程序员使用,可以提高程序员开发效率
MySQL支持的常用函数
- 字符串函数
- 时间日期函数
- 聚合函数
- 数学函数
8.1.字符串函数
8.2.日期函数
8.3.聚合函数
8.4.数学函数
九、分页查询
在SQL Server中使用top关键字实现指定返回查询结果集里指定的记录?
与SQLServer不同,MySQL中使用LIMIT子句限制结果集
语法:
SELECT <字段名列表> FROM <表名或视图> [WHERE <查询条件>] [GROUP BY <分组的字段名>] [HAVING <条件>] [ORDER BY <排序的字段名> [ASC 或 DESC]] [LIMIT [位置偏移量,]行数];
注意:
- 第1条记录的位置偏移量是0,第2条记录的位置偏移量是1……
- LIMIT子句经常和ORDER BY子句一起使用,即先对查询结果进行排序,再根据LIMIT子句的参数返回指定的数据
偏移量的计算公式
偏 移 量 = ( 当 前 页 索 引 − 1 ) ∗ 每 页 显 示 的 行 数 偏移量 = (当前页索引 -1) * 每页显示的行数 偏移量=(当前页索引−1)∗每页显示的行数
示例:#查询病人表信息,要求每页显示10条数据,分别查询第1页、第2页、第3页的数据 #查询第1页数据: select patientID, patientName, gender from patient order by patientID asc limit 0,10; #——>【偏移量=(1-1)*10=0】 #查询第2页数据: select patientID, patientName, gender from patient order by patientID asc limit 10,10;#——>【偏移量=(2-1)*10=10】 #查询第3页数据: select patientID, patientName, gender from patient order by patientID asc limit 20,10;#——>【偏移量=(3-1)*10=20】 #........
十、查询病人信息
#查询2020年2月以前做过血常规检查的病人编号、检查结果和检查时间 select patientID, checkResult, examdate from prescription where checkItemID = 1 and examDate <= "2020-02-01"; #查询所有年龄大于20岁的病人,并按照按年龄从小到大排序,显示从第3条到第5条记录的病人的姓名、性别、年龄 select patientName, gender, floor(datediff(curdate(),birthDate)/365) as age from patient where floor(datediff(curdate(),birthDate)/365) > 20 order by floor(datediff(curdate(),birthDate)/365) asc limit 2,3; #查询所有病人的最大年龄,最小年龄和平均年龄 SELECT max( floor( datediff( curdate(), birthDate )/ 365 )) AS maxAge, min( floor( datediff( curdate(), birthDate )/ 365 )) AS minAge, avg( floor( datediff( curdate(), birthDate )/ 365 )) AS avgAge FROM patient;
十一、子查询
问题:
- 如图所示,要求查询所有年龄比姚维新大的病人的信息
分析:
实现思路
- 查找出“姚维新”的出生日期
- 使用WHERE子句筛选出所有年龄比“姚维新”大的病人信息
- 即:出生日期比“姚维新”小的病人信息
实现方法一:使用SQL语句分两步完成
1.查找出“姚维新”的出生日期
#查找出“姚维新”的出生日期 SELECT birthDate FROM patient WHERE patientName='姚维新'; #查询结果:1986-10-11
2.使用WHERE子句筛选出所有出生日期比“1986年10月11日”小的病人信息
#使用WHERE子句筛选出年龄比姚维新大的病人信息 SELECT patientName, gender, birthDate, address FROM patient WHERE birthDate < '1986-10-11' ;
运行结果:
实现方法二:采用子查询实现
SELECT patientName, gender, birthDate, address FROM patient WHERE birthDate < (SELECT birthDate FROM patient WHERE patientName='姚维新');
运行结果:
什么是子查询?
- 子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询
- 先执行子查询,返回所有来自子查询的结果,再执行外围的父查询,返回查询的最终结果
子查询在WHERE语句中的一般用法
语法:
SELECT …… FROM 表1 WHERE 字段1 比较运算符 (子查询);
注意:
- 将子查询和比较运算符联合使用,必须保证子查询返回的值不能多于一个
- 先执行子查询,返回所有来自子查询的结果,再执行外围的父查询,返回查询的最终结果
十二、SQL语句中指定别名的方法
使用AS关键字,符合ANSI标准
SELECT 字段列表 FROM 表名 AS 表的别名
使用空格,简便的方法
SELECT 字段列表 FROM 表名 表的别名;
可以为表、字段、查询结果指定别名
select 列名 as 列别名 from 表名;
select 列名 列别名 from 表名;
经验:
为某个表命名了别名后,在SELECT语句中出现该表的字段需要指定表名时,就必须统一使用该表的别名;否则将产生语法错误
十三、子查询应用案例(一 )
将子查询中函数运算结果作为父查询的条件
问题:
使用子查询语句查询满意下面条件的数据
- 所有年龄大于20岁的病人
- 按照按年龄从小到大排序
- 显示病人的姓名、性别、年龄
分析:
实现步骤
表中没有病人的年龄,需使用函数先计算每个病人的年龄
FLOOR(DATEDIFF(CURDATE(),birthDate)/365)
查询每个病人的姓名、性别和年龄
查询所有年龄大于20岁的病人信息,按要求显示
示例:
1、查询每个病人的姓名、性别和年龄
SELECT patientName, gender, FLOOR( DATEDIFF( CURDATE(), birthDate )/ 365 ) AS age FROM patient;
2、查询所有年龄大于20岁的病人信息,按要求输出查询结果
#将子查询中函数运算结果作为父查询的条件 SELECT patientName,gender, t.age FROM( SELECT patientName, gender, FLOOR( DATEDIFF( CURDATE(), birthDate )/ 365 ) AS age FROM patient ) t WHERE t.age > 20 ORDER BY age DESC;
运行结果:
十四、子查询应用案例(二)
需求说明
- 查询姓名为“夏颖”的病人
- 第一次做血常规检查
- 最后一次做血常规检查的时间及检查结果
实现思路
- 查询获得血常规检查的检查编号
- 查询获得“夏颖”的病人编号
- 使用(1)和(2)的查询结果获得所有“夏颖”做血常规检查的记录信息
- 根据检查时间排序(3)的结果,正序排列的第一条是第一次检查记录,倒叙排列的第一条是最近一次检查记录
- 使用LIMIT子句获取第一次和最近一条检查记录
示例:
#查询姓名为“夏颖”的病人,第一次做血常规检查做血常规检查的时间及检查结果 SELECT examID, patientID, depID, checkResult, checkItemID, examDate FROM prescription WHERE patientID=(SELECT patientID FROM patient WHERE patientName="夏颖") AND checkItemID = (SELECT checkItemID FROM checkitem WHERE checkItemName="血常规") ORDER BY examDate ASC LIMIT 1 #查询姓名为“夏颖”的病人,最后一次做血常规检查做血常规检查的时间及检查结果 SELECT examID, patientID, depID, checkResult, checkItemID, examDate FROM prescription WHERE patientID=(SELECT patientID FROM patient WHERE patientName="夏颖") AND checkItemID = (SELECT checkItemID FROM checkitem WHERE checkItemName="血常规") ORDER BY examDate DESC LIMIT 1