学校数据库课程

课程"/>

学校数据库课程

前言

因为一直sql语言都是自学的，很多知识点都似懂非懂，正好这学期开设了数据库这门课，希望通过系统的学习让自己的基础更加扎实。

---

文章目录

• 前言
• 基础知识
• • 操作数据库
  • 操作表
  • 行操作增删改查
  • 列操作（alter）
  • 视图
  • 拓展
  • 修改用户密码
  • root 用户远程登录
  • 添加约束
  • MySQL 连接的使用（join）
• 实验部分
• • 实验一 创建学生课程库、表
  • 实验二 基础查询
  • 实验三 连接查询
  • 实验四 嵌套查询
  • 实验五 存储过程
  • 实验六 断言

---

基础知识

操作数据库

创建数据库语句：

create database 数据库名;

删除数据库语句：

drop database <数据库名>;

---

操作表

创建：

create table table_name (column_name column_type);

例：创建一个名为student的表，字段为id和name

删除：

DROP TABLE table_name ;

---

行操作增删改查

---

增（insert）

插入单条数据：

向test表的id,name,pass字段插入值 5,xiaohua,123

插入多条数据的语法：

INSERT INTO table_name  (field1, field2,...fieldN)  VALUES  (valueA1,valueA2,...valueAN),(valueB1,valueB2,...valueBN),(valueC1,valueC2,...valueCN)......;

删（delete）

删除数据：

delect from 表名 where 字段名=值;

注：delect from 表名 等于删除整个表。

例：

删除前：

删除test表中id等于3的数据

---

改（update）

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

例：
将名字为xiaohua的id改为3：

update test set id=3 where name=xiaohua;

---

查（select）
最常用的方法，直接查表所有内容：

select * from test;

列操作（alter）

一，增添列

实例：给test表增加列address，类型为字符串char。

alter table test add column address char(50);

二，删除列

实例：删除test1表中的id列

alter table test1 drop column id;

---

视图

识图创建出来就相当于一个表，感觉安全是它最大的作用，不用对真实表造成影响。

---

创建视图

实例：创建一个test表的视图

create view test_view as select *from test;

查看

select *from test_view;

---

删除一个视图

实例：删除视图test_view

drop view test_view;

---

拓展

往同一列修改多行数据

目标：我们想批量往phone字段添加值，111,222,333。

查了查，知道了使用case这个方法，如下：

update test set phone=case when id=1 then 111when id=2 then 222when id=3 then 333else phone end;	

不过感觉这个办法依然麻烦，如果以后学到了新的方法，再来更新。

修改用户密码

alter user 'root'@'%' identified with mysql_native_password by 'root';

使用mysqladmin修改密码，例如将密码修改为Gr33kLibrary_pass1997!

mysqladmin -u root -p password Gr33kLibrary_pass1997!
Enter password:

root 用户远程登录

select Host,User from user;

显示root用户只允许本地登录，改为%（%号在sql中为通配符），即允许任意ip登录。

UPDATE user SET Host='%' WHERE User='root' AND Host='localhost' LIMIT 1;

添加约束

这部分转自【SQL】数据库中的五种约束，做数据库实验时就参照的这篇文章。

格式：alter table ### add constraint ##

1、添加主键约束（将UserId作为主键）

　　alter table UserIdadd constraint PK_UserId primary key (UserId)

这里的约束名PK_UserId可以去掉

2、添加唯一约束

　　alter table UserInfoadd constraint UQ_IDNumber unique(IdentityCardNumber)

3、添加默认约束（如果地址不填 默认为“地址不详”）

　　alter table UserInfoadd constraint DF_UserAddress default (‘地址不详’) for UserAddress

4、添加检查约束 （对年龄加以限定 20-40岁之间）

　　alter table UserInfoadd constraint CK_UserAge check (UserAge between 20 and 40)

　　alter table UserInfoadd constraint CK_UserSex check (UserSex=’男’ or UserSex=’女′)

5、添加外键约束 (主表UserInfo和从表UserOrder建立关系，关联字段UserId)

　　alter table UserOrderadd constraint FK_UserId_UserId foreign key(UserId)references UserInfo(UserId)

添加完约束之后可以show columns from Student查看：

MySQL 连接的使用（join）

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

使用inner join(也可以省略 INNER 使用 JOIN，效果一样)

下面为实例：
首先，我们将创建三个表：employees、departments 和 salaries。接下来，演示使用 INNER JOIN 和 LEFT JOIN 将这几个表连接起来。

1. 创建 employees 表：

CREATE TABLE employees (emp_id INT PRIMARY KEY,emp_name VARCHAR(50) NOT NULL,department_id INT
);

2. 创建 departments 表：

CREATE TABLE departments (department_id INT PRIMARY KEY,department_name VARCHAR(50) NOT NULL
);

3. 创建 salaries 表：

CREATE TABLE salaries (salary_id INT PRIMARY KEY,emp_id INT,salary_amount DECIMAL(10, 2) NOT NULL,FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);

向这三个表插入一些测试数据

# departments
INSERT INTO departments (department_id, department_name)
VALUES (1, 'HR'),(2, 'IT'),(3, 'Marketing'),(4, 'Finance');
# employees
INSERT INTO employees (emp_id, emp_name, department_id)
VALUES (101, 'Alice', 1),(102, 'Bob', 2),(103, 'Charlie', 2),(104, 'David', 3),(105, 'Eva', NULL);
# salaries
INSERT INTO salaries (salary_id, emp_id, salary_amount)
VALUES (201, 101, 5000.00),(202, 102, 6000.00),(203, 103, 5500.00),(204, 104, 4500.00);

接下来，展示如何使用 INNER JOIN 和 LEFT JOIN 连接表：

• 用 INNER JOIN 演示：

SELECT e.emp_id, e.emp_name, d.department_name, s.salary_amount
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN salaries s ON e.emp_id = s.emp_id;

• LEFT JOIN

SELECT e.emp_id, e.emp_name, d.department_name, s.salary_amount
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN salaries s ON e.emp_id = s.emp_id;

图示：

实验部分

实验一 创建学生课程库、表

数据库：

表：
course：

sc

student

实验二 基础查询

查询计算机科学系全体学生的名单

查询计算机科学系年龄在20岁以下的学生姓名

查询选修1号课程的学生的平均成绩

2号课程

全部数据：

实验三 连接查询

1）等值连接：查询每个学生及其选修课程的情况

mysql> select SC.Sno,Student.Sname,Ssex,Sage,Course.Cname,Course.Cno from SC,Student,Course where SC.Sno=Student.Sno and SC.Cno=Course.Cno;

2）自然连接：查询每个学生及其选修课程的情况

mysql> select SC.Sno,Student.Sname,Ssex,Sage,Course.Cname,Course.Cno from SC,Student,Course where SC.Sno=Student.Sno and SC.Cno=Course.Cno;

3）自身连接：查询每一门课的间接先修课（即先修课的先修课）

mysql> select first.Cno,second.Cpno from Course as first,Course as second where first.Cpno=second.Cno and second.Cpno is not null;

4）多表连接：查询每个学生的学号、姓名、选修的课程名及成绩

mysql> select Student.Sno,Sname,SC.Crade,Course.Cname from SC,Student,Course where Student.Sno=SC.sno and SC.Cno=Course.Cno;

5）选择和连接查询：查询选修2号课程且成绩在90分以上的所有学生的学号和姓名

select Student.Sno,Sname from SC,Student where Student.Sno=SC.sno and SC.Cno=2 and SC.Crade>90;

实验四 嵌套查询

1）查询选择课程号“2”的课程的学生姓名；

select Student.Sname from SC,Student where Student.Sno=SC.Sno and Cno=2;

2）查询选择“信息系统”课程的学生姓名；

mysql> select Student.sname from SC,Student,Course where SC.Sno=Student.Sno and SC.Cno=Course.Cno and Courseame='信息系统';

3）查询与“刘晨”同学同系的学生学号、姓名；

select Student.Sname,Student.Sno from SC,Student where Student.Sno=SC.Sno and Sdept in (select Sdept from Student where Sname='刘晨');

4）查询选择“数据处理”为先修课的学生学号；

select Student.Sno from SC,Student,Course where Student.Sno=SC.Sno and SCo=Courseo and Course.cpno in (select Cno from Course where cname='数据处理');

5）查询选择与“李勇”同学选择相同课程的学生学号及其成绩；

select Sno,Crade from SC where cno in (select Cno from SC where Sno=(select Sno from Studentwhere sname='李勇'));

实验五 存储过程

1、创建一存储过程，求l+2+3+…+n，并打印结果。

调用：

2、创建一存储过程Proc_Student，用于显示学号为“201215121”的学生基本信息（包括学号、姓名、性别、年龄和所在系）；

3、创建一存储过程Stu_grade，通过读取某门课的编号，求出选课学生的学号。（参考例8.9）

4、调用上面的存储过程Stu_grade，求出课程编号为“2”的选课学生学号。

实验六 断言

本次实验，基于学生-课程数据库，自行设计一个断言，并执行断言。

因为MYSQL目前为止并不支持ASSERTION断言操作，使用触发器解决目的要求
制作一个触发器让成绩crade不超过100分

这里before改为after后生效

插入100分以上的分数报错，不超过则通过