mysql三次实验，所有代码

3.1创建数据库
create database medicine;

3.2.1创建医生信息doctor 表
CREATE TABLE doctor(
doctorno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT‘医生编号’，name VARCHAR(50)NOT NULL COMMENT‘医生姓名’，
sex CHAR(2)COMMENT‘医生性别’，
age VARCHAR(3)COMMENT ’年龄’，phone VARCHAR (20)COMMENT‘电话号’);

3.2.2创建患者信息patient 表
CREATE TABLE patient(
patientno INTT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT’患者编号’，name VARCHAR (50)NOT NULL.COMMENT ’患者姓名’，
sex CHAR(2)COMMENT ’患者性别’，
age VARCHAR(3)COMMENT’年龄’，phone VARCHAR(20)COMMENT‘电话号’);

3.2.3创建药品信息drug表
CREATE TABLE drug(
drugno INT NOT_NULL PRIMARY KEY AUTO_INCREMENT COMMENT’药品编号’，name VARCHAR(50)NOT NULL COMMENT ’药品名称’，
price int NOT NULL COMMENT’价格’，
inventory INT NOT NULL COMMENT’存货数量’) ;

3.2.4创建诊断信息diagnosis表
CREATE TABLE diagnosis(
diagnosisno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT’诊治编号’，doctorno INT COMMENT‘医生编号’
patientno INT COMMENT‘惠者编号，
situation CHAR(50) NOT NULL COMMENT ’诊断结果);

3.2.5创建处方信息prescription表
CREATE TABLE prescription(
pricription INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT’处方编号’，patientno INT COMMENT’患者编号’，
name VARCHAR(50)NOT NULL COMMENT’处方名’，time DATE NOT NULL COMMENT’开处方日期’
) ;

3.3.1在医生信息doctor表中插入语句
INSERT INTO doctor(doctorno, name,sex, age, phone)VALUES
(1，’赵钱’,’男’，’36’,’13134208640’),
(3，‘孙李,’女’,’26,’13134628530’),
(4，’周昊’,’男’，’46’,’13134616530’)，
(5,’郑王’，’男’，‘56’,’13134598530’);

3.3.2在患者信息patient表中插入语句
INSERT INTO patient (patientno, name,sex, age, phone)VALUES
(1，’夏商’，’男’,’36’,313134258640’)，
(3，’西周’，’女’,’26’,’13134624630’)，
(4，’两晋’，’男’，’46’,‘13134614830’),
(5,’南北朝’,’男’,’56’,’13134758530’) ;

3.3.3在药品信息drug表中插入语句
INSERT INTO drug(drugno, name, price,inventory)VALUES
(01,’唐’,1235,1651651)，
(02,’宋’，6523,6156216)，
(03,’元’,5821，1265121),
(04，’明’,6987,1915138) ;

3.3.4在诊断信息diagnosis表中插入语句
INSERT INTO diagnosis(diagnosisno, doctorno, patientno,situation)VALUES
(001,1,1,’身体健康’)，
(002,2,2,’身体健康’)，
(003,3,3,’身体健康’)，
(004,4,4,’身体健康’);

3.3.5在处方信息prescription表中插入语句
INSERT INTO prescription(pricription, patientno, name,time)VALUES
(001,1，’夏商’，’2021-01-21’)，
(002,2，’西周’，’2021-11-21’)，
(003,3，’两晋’，'2021-04-30’)，
(004,4,’南北朝’，’2021-08-153) ;

3.3.6在医生信息doctor表中插入数据
insert into doctor values(6,’汉武’﹐'男’,‘36’,'13134208640’) ;
select * from doctor ;

3.4.1查询医生信息doctor表
select *from doctor ;

3.4.2查询查询患者信息patient表
select * from patient;

3.4.3查询药品信息drug表
select *from drug;

3.4.4查询诊断信息diagnosis表
select * from diagnosis;

3.4.5查询处方信息prescription表
select from prescription;

3.5.1修改诊断信息diagnosis表数据
update diagnosis
set situation= ‘胆结石’
where diagnosisno=1;

select * from diagnosis;

3.5.2修改处方信息prescription表数据
update prescription
set name=’秦皇’where pricription=1;

select * from prescription;

3.6.1删除处方信息prescription表中数据
delete from prescription
where name=南北朝;

select * from prescription;
3.6.2删除处方信息prescription表中所有数据
select * from prescription;

delete from prescription;

3.6.2删除drug表
drop table drug;

1.基于医院管理系统的具体功能为表添加适当的数据进行数据完成至少的3个简单查询；
Select * from medicine;
select * from doctor where name like ‘赵%’;
select * from doctor where age = 36;

2.基于医院管理系统的具体功能为表添加适当的数据进行数据的至少2个条件查询，其中包含对GROUP BY子句，ORDER BY子句和LIMIT子句的使用；
select * from doctor where age =‘36’ group by name;
select * from doctor where age =‘36’ order by phone;
select * from doctor limit 2;

3.基于医院管理系统的具体功能为表添加适当的数据进行数据的至少3个高级查询（子查询/内连接/外连接）；
select * from doctor where age<(select age from doctor where name=‘赵钱’);$
select u.name,c.name from doctor u left join patient c on u.sex=c.sex;
select u.name,c.name from doctor u,patient c where u.sex=c.sex;

4.使用简单存储过程为核心表表添加十万条以上数据，为其他表添加适当数据，作为查询的基础数据。
DELIMITER $$CREATEPROCEDUREpatient()BEGINDECLAREiINT;SETi=0;WHILEi<100000DOSETi=i+1;INSERTINTOpatient(i,’清’,’男’,’100’,’19999999999’);ENDWHILE;END$$
5.了解索引的作用与分类，掌握索引的创建方法；基于医院管理系统建立适当的索引。
create index indexname on doctor(name);
show index from doctor\G;

6.理解数据完整性的概念及其分类，掌握各种数据完整性的实现方法；
一、实体完整性——表中记录唯一，包括唯一约束和主键约束
1、主键约束: 唯一识别每一条记录，不能重复，且不能为NULL值（具有唯一值的字段可设为主键字段）
注意:每张表只有一个主键，一般选择新增无业务含义的字段作为主键且选中字段勾选为自动增长
2、唯一约束:不能重复但可为NULL值
二:域完整性——指给定列输入的有效性，即保证指定列的数据具有正确的数据类型，格式和有效的数据范围
1.非空约束:勾选不能为NULL
2.默认值约束:大量重复字段可设默认值，如民族可设为汉族
三:参照完整性——定义一表主键与一表外键的引用规则来约束两表的联系，目的用关联表之间的制约机制互相参照，控制表间数据的一致性和完整性，实质不允许引用表中不存在的记录
从表——含有外键且参照其他表的表，也叫子表或参照表
主表——被其他表参照的表，也叫父表或被参照表
外键——参照别表主键的字段，被参照表称主表又叫父表，含有外键参照其他表的表称为子表也叫参照表。
注意:主表中的被参照列为主键或者唯一约束字段
7.设计SQL语句执行查询，来优化查询性能。
select ‘男’ from patient;
select * from patient where sex=‘男’;
select * from patient where age<20;
select * from patient where age>30;

1.基于医院管理系统业务逻辑创建，修改、删除视图；
创建视图
create view doctor_age_view as select * from doctor d where d.age>'30’ ;
select * from doctor_age_view;

修改视图
alter view doctor_age_view
as select d.name from doctor d where d.age>'20’;
select *from doctor age view;

删除视图
drop view doctor_age_view;

2.了解存储过程体中允许的SQL语句类型和参数的定义方法；
1）SHOW CREATE PROCEDURE 过程名;
2）SHOW PROCEDURE STATUS [LIKE 匹配模式];
3）CALL 数据库名.存储过程名称([实参列表]);
实参列表传递的参数需要与创建存储过程的形参相对应。
当形参被指定为IN时，则实参值可以为变量或是直接数据；
当形参被指定为OUT或INOUT时，调用存储过程传递的参数必须是一个变量，用于接收返回给调用者的数据。
4）ALTER PROCEDURE 存储过程名称 [特征]
特征指的是存储过程需要修改哪个部分。
ALTER PROCEDURE不能更改存储过程的参数或主体。
5）DECLARE 错误名称 CONDITION FOR [错误类型]
错误类型的可选值为mysql_error_code和SQLSTATE [VALUE] sqlstate_value。
前者是数值类型表示的错误代码，如1148。
后者是5个字符长度的错误代码，如SQLSTATE ‘42000’。
6）DECLARE 错误处理方式 HANDLER FOR 错误类型 [, 错误类型] …
程序语句段
MySQL支持的错误处理方式有两种，一个为CONTINUE（遇到错误不处理，继续执行），另一个为EXIT（遇到错误时马上退出）。
程序语句段表示遇到定义的错误时，需要执行的存储过程代码段。
3.基于医院管理系统业务逻辑定义存储过程并执行，其中应该包括游标的使用。
DELIMITERKaTeX parse error: Expected 'EOF', got '&' at position 289: …F cur_num >= 0 &̲& cur_num<= 400…

4.基于医院管理系统业务逻辑创建和使用触发器；
create TRIGGER sh_ patient
AFTER update on patientfor
each row
UPDATE doctor
SET doctor.phone=new.Phone where doctor.phone=old.Phone;
$$5.基于医院管理系统业务逻辑定义函数，并使用视图和调用函数。DELIMITER$$
CREATEFUNCTION PEOPLE (name varchar (20))RETURNS DATE
DETERMINISTICBEGIN
DECLARE age VERCAHR (3);
SELECT doctorno into age from doctor where name='赵钱’;
return age;
END
$$

6.为医院管理系统数据库创建不同用户并分配不同权限。
CREATE USER ’’ HCT’@’%’identified by '123456’;
$$
grant select on medicine.* to HCT;

7.完成数据库的备份与恢复。
用户备份
mysqldump -u username -ppassword --all-databases > bak.sql

将备份的数据导入到刚新建的 testDB 数据库中
[root@192 home]# mysql -u username -ppassword testDB < /home/bak.sql