三—01：MySQL01（DDL、DML、约束）

一、基础知识

1. 数据库基础知识

1.1数据库(database)

数据库(Database，简称DB)，就是存储数据的仓库。 其本质就是一个文件管理, 就是以文件的形式将数据保存到电脑的磁盘中 。

1.2 数据库的分类

1. 关系型数据库

关系型数据库是将复杂的数据结构用较为简单的二维表来表示，由二维表及其之间的联系所组成的一个数据组。

优点：

1. 易于维护：使用二维表的表结构，格式一致；

2. 使用方便：SQL语言通用，关系型数据库都可以使用SQL进行操作；

3. 可用于复杂操作：可用于一个表以及多个表之间非常复杂的查询。

缺点：

1. 读写性能比较差，尤其是海量数据的高效率读写；

2. 固定的表结构，灵活度稍欠；

3. 高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。

2. 非关系型数据库

非关系型数据库也称之为NoSQL（Not Only SQL）数据库，是一种数据结构化存储方法的集合，可以是文档或者键值对等。

优点：

1. 格式灵活：存储数据的格式可以是key, value形式, 文档形式, 图片形式等等.
2. 使用灵活：应用场景广泛，而关系型数据库则只支持基础的类型
3. 速度快：使用内存存储数据，而关系型数据库只能使用硬盘
4. 高扩展性

缺点：

1. 不提供sql支持，学习和使用成本较高
2. 数据结构相对复杂，复杂查询方面稍欠
3. 只适合存储一些较为简单的数据，对于需要进行较复杂查询的数据，关系型数据库显的更为合适
4. 不适合持久存储海量数据

2. SQL语言入门

1.SQL基本概念

SQL(Structured Query Language )是结构化查询语言的简称，是一种数据库查询和程序设计语言。

2. SQL作用

• 是所有关系型数据库的统一操作规范，不同关系型数据库都支持SQL

• 所有的关系型数据库都可以使用SQL

• 不同数据库之间的SQL有一些区别（方言）

二、SQL分类

1、DDL操作

数据定义语言（Data Definition Language，DDL）：

DDL主要用针对是数据库对象（数据库、表、索引、视图）进行创建, 修改和删除操作。 主要包括：

• CREATE: 创建

• ALTER: 修改

• DROP: 删除

• SHOW：查看

 1.1 操作数据库

-- 1.查看所有的库
show databases;

-- 2.创建新的库
/*
    create database 库名;
*/
create database zqwl;

create database zqwl1;

-- 3.删除库
/*
    drop database 库名;
*/
drop database zqwl1;
-- 4.选择某个库，表是属于库的，选择库之后再对该库中的表进行操作，不选择直接操作，提示 no database selected
use zqwl;


-- 查看所有表
show tables;

 1.2 操作数据库中的表

-- 先选择一个存在的库
use zqwl;
create table test1(
	uname varchar(5),
	age int
)

create table student(
	sid int,
	sname varchar(10),
	sex char(1),
	age int,
	enrollment_date date, -- 入学日期，date没有关键字颜色
	clazz varchar(15),
	email varchar(25)
)

-- 2.查看所有的表
show tables;

-- 3.查看表结构（查看表中字段等）
desc student;
 
-- 4.查看建表语句
show create table student;

-- 5.删除表
drop table test1;


-- 6.修改表信息
show tables;
desc stu;

	-- 1.修改表名
rename table student to stu;

	-- 2.修改表中字段的类型  modify
alter table stu modify sex varchar(5);

  -- 3.修改表中字段的名字  change
alter table stu change enrollment_date e_date date;


-- 7.添加新的字段
alter table stu add bir date;  -- 默认添加到最后位置
alter table stu add count int first;  -- 添加到第一个位置
alter table stu add test char(3) after sname; -- 添加到指定字段的后边

 1.3 SQL中常见数据类型

1.整数类型：
   int(长度限制)： 长度限制可指定也可不指定。 指定了长度限制，超过指定的限制，自动扩充。   
 2.浮点类型：
    double(m,n)：长度限制需要指定。定了长度限制，不可以超过指定的限制。m整体的长度，n小数的长度。   
 3.字符类型：
    char(长度限制)：长度限制需要指定。指定了长度限制，不可以超过指定的限制。直接分配指定   的空间。varchar(长度限制)，长度限制需要指定，指定了长度限制，不可以超过指定的限制，根据内容动态分配空间。
 4.日期与时间类型：

date：日期。 datetime：日期+时间。  timestamp：日期+时间 。  

2、DML操作

数据操作语言（Data Manipulation Language，DML）：

DML主要用于对数据 增加、修改和删除的操作。 主要包括：

• INSERT: 增加数据

• UPDATE: 修改数据

• DELETE: 删除数据

2.1 添加表数据

（新添加一行数据）

  格式：insert into 表名(字段名,字段名,...) values(值,值,...);
    注意：
        1.值的个数和字段个数相同
        2.值的类型和字段类型相同
        3.值的范围不能超过指定的范围
        4.字符类型，值可以使用 '' 或 ""
        5.日期类型可以使用字符类型，'yyyy-MM-dd' "yyyy-MM-dd"， 日期函数
        6.为所有的字段添加值，字段名可以省略不写
        7.添加部分字段，字段不可以省略，必须手动指定为哪些字段添加值

alter table stu drop bir;
desc stu;
-- 一次添加一条数据
--  Affected rows: 1 影响一行，添加成功一条数据
insert into stu(sid,sname,sex,age,e_date,clazz)
				 values(1,'张三','男',18,'1990-10-1','java-100');
				 
-- 1136 - Column count doesn't match value count at row 1  字段数和值数不一致
insert into stu(sid,sname,sex,age,e_date,clazz)
				 values(1,'张三','男',18,'1990-10-1');
				 
insert into stu values(2, '李四', '女', 17, '2000-1-1', 'java-101');

insert into stu(sid,sname) values(3,'王五');

insert into stu(sname,age,clazz) values('赵六', 20, 'java-101');

-- now() sysdate() 日期函数，获取当前系统的日期
insert into stu(sid, sname, e_date) values(5, '老七', now());
insert into stu(sid, sname, e_date) values(6, '老八', sysdate());

-- 一次添加多条数据
-- > Affected rows: 2 
insert into stu(sid, sname, e_date) values(7, '老九', now()),(8, '老十', now());

 2.2 修改表数据

（修改单个或多个字段的值）

格式：update 表名 set 字段名=新值, 字段名=新值, ... where 条件
    注意：
        1.修改时一定要添加条件，否则修改全部。
        2.条件
            字段名=值（常用）
            字段名>值
            字段名<值

update stu set sex='男';

-- > Affected rows: 1
update stu set sex='女' where sname='李四';

update stu set age=38 where e_date='2023-09-04';

2.3 删除表数据

（删除一行数据）

格式：delete from 表名 where 条件;
    注意：
        1.删除时一定加条件，否则删除全部数据。
    删除全部数据：
        1.delete from 表名; 一条一条删除
        2.truncate table 表名; 复制表结构，创建新表。原表直接删除。

delete from stu;

delete from stu where e_date='2023-09-04';
delete from stu where sname='赵六';

3、数据查询语言（Data Query Language，DQL）

DQL主要用于数据的查询，其基本结构是使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据。

• select

• from

• where

• order by

• having

4、数据控制语言（Data Control Language，DCL）

DCL用来授予或回收访问数据库的权限。

主要包括：

• GRANT: 授予用户权限

• REVOKE: 回收授予的权限 。

5、事务控制语言（Transaction Control Language，TCL）

TCL用于数据库的事务管理。

主要包括：

• START TRANSACTION: 开启事务

• COMMIT: 提交事务

• ROLLBACK: 回滚事务

注意：

数据操纵语言DML（insert、update、delete）针对表中的数据 ；

而数据定义语言DDL（create、alter、drop）针对数据库对象，比如数据库database、表table、索引index、视图view、存储过程procedure；

三、表的约束

1. 约束的作用和分类

可以对表中的数据做一些限制, 从而保证数据的正确性, 有效性, 和完整性。

违反约束的不正确数据是无法插入到表中的。

约束名	关键字	作用
主键约束	primary key	不可重复, 不能为空，即唯一 + 非空
唯一	unique	不可重复
非空	not null	不能为空
默认值	default	赋予默认值
检查(Mysql8提供)	check	取值范围
外键	foreign key	表与表之间的关系

2. 主键约束

2.1 添加主键约束

-- 语法格式：
字段名 字段类型 primary key

-- 案例1：创建一个带有主键的emp表 字段 eid int  ename varchar(10)  esex char(1)
-- 方式一
create table emp(
	eid int primary key,  -- 设置主键 非空唯一
	ename varchar(10),
	sex char(1)
);

-- 方式二
drop table emp; -- 删除emp表
create table emp(
	eid int,
	ename varchar(10),
	sex char(1),
	primary key(eid)  -- 指定主键为eid字段
);

-- 方式三
drop table emp; -- 删除emp表
create table emp(
	eid int primary key,
	ename varchar(10),
	sex char(1)
);
-- 创建表时不指定主键, 然后通过DDL语句进行设置
alter table emp add primary key(eid);

2.2 删除主键约束

-- 使用DDL 删除表中的主键约束
alter table emp drop primary key;

-- 查看表结构
desc emp;

-- 删除唯一约束(了解)
-- 添加了唯一约束为 eid int not null, 通过设置字段属性删除唯一约束 
alter table emp modify eid int;

2.3 主键自增

自己添加数据可能出现重复, 我们通常希望在每次插入新记录时, 数据库自动生成主键字段的值。

-- 语法格式: 
关键字 auto_increment	 表示自增长(字段类型必须为整数类型)

-- 案例：为emp表eid字段添加主键约束, 并设置为自增
drop table emp;
create table emp(
	eid int primary key auto_increment,
	ename varchar(10),
	sex char(1)
);
-- 测试主键自增
insert into emp values(null,'张三','男');
insert into emp values(default,'李四','男');
insert into emp(ename, esex) values('王五', '男');

3. 非空约束

-- 语法格式: 字段名 字段类型 not null

-- 案例：创建emp表，eid为主键约束,自增，ename为非空约束
-- 删除存在的emp表 
drop table emp;
create table emp(
	eid int primary key auto_increment,
	ename varchar(10) not null,
	sex char(1)
);
-- 测试非空约束
insert into emp values(default,null,'n');
-- Column 'ename' cannot be null  ename不能为空

4. 唯一约束

-- 语法格式： 
字段名 字段类型 unique

-- 案例：emp表 eid 主键约束,自增  ename 非空约束 esex唯一约束
drop table emp; -- 删除存在的emp表 
create table emp(
	eid int primary key auto_increment,
	ename varchar(10) not null,
	sex char(1) unique
);
-- 测试唯一约束 添加两个性别为男
insert into emp values(default,'zs','男');
insert into emp values(default,'lisi','男');
-- Duplicate entry 'n' for key 'emp.esex'  esex重复

5. 默认值约束

-- 语法格式： 字段名 字段类型 default '值'

-- 案例：emp表 eid 主键约束,自增  ename 非空约束 esex默认值'男'
-- 删除存在的emp表 
drop table emp;
create table emp(
	eid int primary key auto_increment,
	ename varchar(10) not null,
	sex char(1) default '男'
);
-- 查看emp表结构 
desc emp;
-- 测试默认值约束
insert into emp values(default,'zs',default);

-- 错误用法
insert into emp values(default,'zs',null); 

6 检查约束

字段名 字段类型 check(字段='值' or 字段='值')
字段名 字段类型 check(字段>n or 字段<m)
字段名 字段类型 check(字段>n and 字段<m)

7.外键约束

特点：使用在多表中（从表中的外键值必须在主表的主键值中存在）

1.操作从表 

• 向从表中添加数据时，外键值必须在主表的主键值中存在
• 变更从表中的外键值是，变更的新外键值必须在主表的主键值中存在

2.操作主表

• 删除主表中数据时，从表中有关联的外键值，设置为null | 直接删除关联数据
• 变更主表中主键值时，从表中有关联的外键值，设置为null | 直接删除关联数据 | 将从表中关联外键值同步为新主键值。

3.先创建主表，再创建从表。先删除从表，再删除主表
关键词：constrains foreign key references
  创建格式：1. create table 表名(
                      id int primary key,
                       ...,
                      constrains 外键名 foreign key(字段) references主表(主键字段)
                      )
                  2. alter table 表名 add constrains 外键名 foreign key(字段) references主表(主键字段);
                
4.外键约束设置：
     1.删除时
           RESTRICT(默认)  No action：作用类似，都会检查外键约束。
           setNull：删除主表中数据时，将关联从表中的外键值设置为null。
           cascade：删除主表中数据时，将关联从表中的数据删除。
       2.变更时
           RESTRICT(默认)  No action：作用类似，都会检查外键约束。
           setNull：变更主表中主键值时，将关联从表中的外键值设置为null。
           cascade：变更主表中主键值时，将关联从表中的外键值变为新的主键值。