Mysql的基本操作和级联操作

MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

一、数据库常用操作

1、创建数据库

create database school;

2、使用数据库

use database school;

3、显示所有数据库

show databases;

4、删除数据库

drop database school;

二、数据表常用操作

1、创建数据表

创建班级表CLASS_ID为主键；创建学生表STUDENT_ID为主键；
创建教师表TEACHER_ID 为主键；

# 创建班级表
CREATE TABLE class (

CLASS_ID INT(6) NOT NULL PRIMARY KEY AUTO_INCREMENT,
NAME  CHAR(6)

)
# 创建学生表
CREATE TABLE student (

STUDENT_ID INT(6) NOT NULL PRIMARY KEY AUTO_INCREMENT,
AGE INT(3),
NAME CHAR(4) NOT NULL ,
S_CLASS_ID INT(4),
SEX CHAR(2)

)
# 创建老师表
CREATE TABLE teacher (

TEACHER_ID INT(6) NOT NULL PRIMARY KEY AUTO_INCREMENT,
NAME CHAR(4) NOT NULL ,
T_CLASS_ID INT(4),
SEX CHAR(2)

)

2、增加数据

INSERT INTO class (CLASS_ID,NAME) VALUES (1,'一班');
INSERT INTO class (CLASS_ID,NAME) VALUES (2,'二班');
INSERT INTO class (CLASS_ID,NAME) VALUES (3,'三班');

INSERT INTO student (STUDENT_ID,AGE,NAME,S_CLASS_ID,SEX) VALUES (1,8,'张三',1,'男');
INSERT INTO student (STUDENT_ID,AGE,NAME,S_CLASS_ID,SEX) VALUES (2,10,'李四',1,'男');
INSERT INTO student (STUDENT_ID,AGE,NAME,S_CLASS_ID,SEX) VALUES (3,9,'牛三',1,'女');
INSERT INTO student (STUDENT_ID,AGE,NAME,S_CLASS_ID,SEX) VALUES (4,8,'王五',2,'男');
INSERT INTO student (STUDENT_ID,AGE,NAME,S_CLASS_ID,SEX) VALUES (5,8,'赵六',3,'男');

INSERT INTO teacher (TEACHER_ID,NAME,T_CLASS_ID,SEX) VALUES (1,'王蒙',1,'女');
INSERT INTO teacher (TEACHER_ID,NAME,T_CLASS_ID,SEX) VALUES (2,'韩梅梅',2,'女');
INSERT INTO teacher (TEACHER_ID,NAME,T_CLASS_ID,SEX) VALUES (3,'李虎',3,'男');

3、查询数据

SELECT * FROM class;

SELECT * FROM student;

SELECT * from teacher;

4、修改数据

UPDATE student SET SEX = '女' WHERE NAME = '李四';

SELECT * FROM student WHERE NAME = '李四';

5、删除数据

删除数据有三种方式：delete、truncate、drop
执行速度：drop > truncate >> delete
区别：delete删除的数据可以恢复；truncate 清空表，保留表的结构，数据不可以恢复；
drop 删除整个表，表不存在，数据不可恢复;

DELETE FROM student where NAME = '牛三';
SELECT * FROM student;

三、设置级联操作

-- 1. 添加级联操作
	语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称
		FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) 
		ON UPDATE CASCADE 
		ON DELETE CASCADE;
-- 2. 分类：
	1. 级联更新：ON UPDATE CASCADE
	2. 级联删除：ON DELETE CASCADE

在学生表里设置班级表的级联删除和更新，学生表的外键（S_CLASS_ID）连接班级表的（CLASS_ID）;
在教师表里设置班级表的级联删除和更新，教师表的外键（T_CLASS_ID）连接班级表的（CLASS_ID）;

ALTER TABLE student ADD CONSTRAINT s_c_classid
		FOREIGN KEY (S_CLASS_ID) REFERENCES class(CLASS_ID) 
		ON UPDATE CASCADE 
		ON DELETE CASCADE;
		
ALTER TABLE teacher ADD CONSTRAINT t_c_classid
		FOREIGN KEY (T_CLASS_ID) REFERENCES class(CLASS_ID) 
		ON UPDATE CASCADE 
		ON DELETE CASCADE;

查看效果

1、级联删除操作

eg:删除班表里三班的数据，学生表里三班的学生也跟着删除，教师表里三班的老师也跟着删除

SELECT * from student;
SELECT * from teacher;

DELETE from class where CLASS_ID=3;
SELECT * from student;
SELECT * from teacher;

2、级联更新操作

eg:将班级表里一班的id修改为6，学生表和老师表里S_CLASS_ID和T_CLASS_ID也会修改为6

UPDATE class set CLASS_ID = 6 where CLASS_ID=1;
SELECT * from student;
SELECT * from teacher;