mysql入门系列（一）

前言：sql语言入门

sql语言分为五个部分

• 数据查询语言（Data Query Language, DQL）
  1、主要是select操作；
• 数据操作语言（Data Manipulation Language, DML）
  1、insert操作；
  2、update操作；
  3、delete操作；
• 数据定义语言（Data Definition Language, DDL）
  1、create操作；
  2、alter操作；
  3、drop操作；
• 数据控制语言（Data Control Language, DCL）
  1、grant操作（用户授权）；
  2、revoke操作(权限回收)；
• 事务控制语言（Transaction Query Language, TCL）
  1、start transaction(开启事务);
  2、commit；
  3、rollback；
  4、set tranaction(设置事务级别);

一、创建数据库和表

说明：本文以一个完整例子描述，版本是mysql8

-- 1.1 创建数据库
create database test_db;
-- 1.2 查看数据库
show databses;
-- 1.3 切换数据库
use test_db;
-- 1.4 创建学生表
create table t_student(
	sno int(5),
	sname varchar(6),
	sex char(1),
	age int(3),
	class_name varchar(15),
	enter_date date,
	email varchar(32)
);
-- 1.5 查询学生表
select * from t_student limit 5;
-- 1.6 查看学生表建表语句
show create table t_student;
-- 1.7 查看学生表详情
desc t_student;

二、修改数据

/*
2.1 插入操作
*/
insert into t_student values (1,'张三','男',23,'行政一班','2022-01-01','zs@163.com');
insert into t_student values (2,'李四','男',23,'行政一班','2022-01-01','ls@163.com');
insert into t_student values (3,'王五','男',23,'行政一班','2022-01-01','ww@163.com');
/*
2.2 更新操作
*/
update t_student set sex = '女' where sno = 2;
/*
2.3 删除操作
*/
delete from t_student where sno = 2;

三、修改表结构

/*
3.1 重命名表
*/
alter table t_student rename to t_student2;
alter table t_student2 rename to t_student;
/*
3.2 删除表
*/
-- drop table t_student;
/*
3.3 新增列
*/
-- 向学生表结尾插入一列
alter table t_student drop addr;
-- 向学生表开头插入一列
alter table t_student add addr varchar(255) first;
-- 向学生表sex列后插入一列
alter table t_student add addr2 varchar(255) after sex;
/*
3.4 更改列
*/
-- 更改学生表列属性
alter table t_student modify addr2 varchar(123);
-- 更改学生表列名称
alter table t_student change addr2 addr3 varchar(123);
/*
3.5 删除列
*/
alter table t_student drop addr;

四、非外键约束

/*
4.0 清除历史
*/
drop table if exists t_student;
/*
4.1 新建带非外键约束的学生表
*/
create table t_student(
	sno int(5) primary key auto_increment,
	sname varchar(6) not null,
	sex char(1) default '男' check(sex = '男' || sex = '女'),
	age int(3) check(age >= 18 and age <= 60),
	class_name varchar(15),
	enter_date date,
	email varchar(32) unique
);
-- 4.2 测试email唯一性生效情况
insert into t_student values (1,'张三','男',21,'java01班','2023-9-1','zs@126.com');
insert into t_student values (null,'李四','男',21,'java01班','2023-9-1','zs@126.com');

/*
4.3 对已存在表设置约束
*/
drop table t_student;
-- 新建普通表
create table t_student(
    sno int(5),
    sname varchar(6),
    sex char(1),
    age int(3),
    class_name varchar(15),
    enter_date date,
    email varchar(32)
);
alter table t_student add constraint pk_stu primary key(sno);
alter table t_student modify sno int(5) auto_increment;
alter table t_student add constraint uq_email unique(email);
alter table t_student add constraint ck_sex check(sex = '男' || sex = '女');
alter table t_student add constraint ck_age check(age >= 18 and age <= 60);

五、外键约束

/*
5.1 关联表准备
*/
-- 新建员工宿舍表
create table t_dept(
	dept_no int(5) primary key auto_increment,
	dept_name varchar(255) unique
)
-- 新建员工表
drop table if exists t_employee;
show tables;
create table t_employee(
	emp_no int(5) primary key auto_increment,
	emp_name varchar(10) not null,
	emp_mail varchar(20) unique,
	dept_id int(5),
	constraint fk_dept foreign key (dept_id) references t_dept(dept_no)
);
/*
5.2 数据初始化：新增宿舍表记录
*/
insert into t_dept values 
(null, '一号宿舍楼'), (null, '三号宿舍楼'), (null, '八号宿舍楼');
/*
5.3 测试插入员工记录测试
*/
-- 预期会报错
insert into t_employee values (null,'张三','zs@126.com',4);
-- 新增员工记录
insert into t_employee values (null,'张三','zs@126.com',1);
insert into t_employee values (null,'李四','ls@126.com',2),(null,'小明','xm@126.com',3);

/*
5.4 对已有表设置外键约束
*/
alter table t_employee drop foreign key fk_dept;
/*
外键策略
	1、cascade 2、set null
	说明：在update和delete外检设置中可以混合使用
*/
alter table t_employee add constraint foreign key fk_dept (dept_id) 
references t_dept(dept_no) on update cascade on delete cascade;

/*
5.5 测试：更新宿舍表，员工表中的记录会关联更新
*/
update t_dept set dept_no = 5 where dept_no = 2;
-- 查看员工表的级联更新效果
select * from t_employee;