02-MySQL库和表的操作

一、mysql注释

• 注释写法1：

## 注释内容
1

• 注释写法2：

-- 注释内容            注意：-- 后必须跟上一个空格才能正常使用
1

• 注释写法3：

/*注释内容*/
1

注意：mysql中不区分大小写，但是建议关键字大写，表名和字段名使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。

二、库操作

-- 1.查询库
show databases;

-- 2.创建库
create database [if not exists] 数据库名 [default] character set utf8;
-- 说明：[if not exists] 为可选参数，MySQL 不允许在同一系统下创建两个相同名称的数据库
-- 加上后如果不存在才会创建，避免报错。

--简便写法
create database 数据库名 character set utf8;

-- 3.删除库
drop database 数据库名;

-- 4.进入/切换 数据库
use 数据库名;

-- 5.查询所有库
show databases;

-- 6.查询建库语句
show create database 数据库名;

-- 7.修改数据参数，例如 字符集
alter database 数据库名 character set 字符集;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

三、表操作

3.1 建表

语法：

create table 表名(
    列名 数据类型 [约束或默认值],
    列名 数据类型 [约束或默认值],
    列名 数据类型 [约束或默认值],
    ...
)character set utf8;

-- 一般在库层面设置过字符集后，建表时可以不加，默认使用库的字符集。
1
2
3
4
5
6
7
8

其他命令：

-- 1.查询当前库中有哪些表
show tables;

-- 2.查询表结构
desc 表名;
-- 参数说明如下：
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| 字段名 | 数据类型| 为空  |主键  | 默认值   | 附加项|
+-------+---------+------+-----+---------+-------+

-- 3.查询建表语句
show create table 表名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3.2 删除表

drop table 表名;
1

3.3 修改表

-- 1.修改表名
alter table 旧表名 rename to 新表名;

-- 2.修改表中字段和属性,整体语法遵循：alter table 表名 <修改项>;
-- 2.1 增加列(在末尾添加)
alter table 表名 add 新列名 数据类型;
-- 2.2 增加列(在开头添加)
alter table 表名 add 新列名 数据类型 first;
-- 2.3 增加列(在指定的字段后添加)
alter table 表名 add 新列名 数据类型 after 现有列名;
-- 2.4 仅修改字段属性
alter table 表名 modify 字段名 数据类型 [约束 默认值];
-- 2.5 修改字段名称和属性
alter table 表名 change 旧列名 新列名 新列的数据类型;
-- 2.6 删除列
alter table 表名 drop 列名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

3.4 插入数据

-- 全表插入
insert into 表名 values(值1,值2,...);

-- 根据字段插入
insert into 表名(字段名1,字段名2,字段名3) values(值1,值2,值3);
1
2
3
4
5

3.5 查询数据

-- 无条件全表查询， * 通配符
select * from 表名;

-- 有条件全字段查询
select * from 表名 where 条件;

-- 有条件查询指定字段
select 字段名1,字段名2 from 表名 where 条件;
1
2
3
4
5
6
7
8

3.6 修改数据

update 表名 set 字段1=值,字段2=值 where 条件;
1

3.7 删除数据

-- 删除指定数据的语法：
delete from 表名 where 条件;


-- 清空整个表
delete from 表名;
truncate table 表名;

-- 区别：
1. truncate效率高于delete，前者整体删除，后者逐条删除
2. truncate不会触发触发器(trigger)
3. truncate会重置表中的自增列(回到1)，delete不会重置
4. truncate不会记录服务器log，delete会
1
2
3
4
5
6
7
8
9
10
11
12
13

四、字段默认值和约束

4.1 默认值

创建表时可以使用 DEFAULT 关键字设置默认值，具体的语法格式如下：

create table 表名(
	字段名 数据类型 default 默认值;
	...
);
--如果是字符类型的，要用单引号括起来，指定默认值后如果插入数据时没有指定值就使用默认值作为值。
1
2
3
4
5

4.2 约束

4.2.1 主键约束

主键约束 - primary key：主键是表的一个特殊字段，要求非空且唯一，该字段能唯一标识该表中的每条信息。例如学员id

-- 建表时添加主键约束：primary key ， auto_increment 自增
create table 表名(
	id int primary key auto_increment;
    ...
);

-- 建表后添加
alter table 表名 add primary key(字段名);

-- 删除主键
alter table 表名 drop primary key;
1
2
3
4
5
6
7
8
9
10
11

4.2.2 外键约束

外键约束 - foreign key：外键是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。例如：部门表和员工表之间的关系。

语法规则：

-- 在建表时添加
create table 从表名(
	...,
    constraint 外键名 foreign key(从表的外键字段) references 主表名(主表的主键字段)
);

-- 在建表后添加
alter table 从表名 add constraint 外键名 foreign key(从表的外键字段) references 主表名(主表的主键字段);

-- 删除外键
alter table 表名 drop foreign key 外键名;
1
2
3
4
5
6
7
8
9
10
11

案例：

-- 部门表(主表)
create table dept(
	id INT primary key auto_increment,
	name VARCHAR(50)
);
insert into dept values(1,'开发部');
insert into dept values(2,'设计部');
insert into dept values(3,'售后部');

-- 员工表(从表)
create table emp(
	id INT primary key auto_increment,
    name VARCHAR(50),
    dept_id INT,
    constraint pk_emp_dept foreign key(dept_id) references dept(id)
);
insert into emp values(1,'张三',1);
insert into emp values(2,'李四',2);
insert into emp values(3,'王二',4); /*插入失败，因为外键参照主表，主表没有的是无法插入的*/

delete from dept where id = 1; /*删除失败,因为其还有子表中的数据在关联他，想删除必须先删除子表的关联数据*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

4.2.3 唯一约束

唯一约束 - unique：为字段设置唯一约束后，该字段所有记录中值不能重复。

create table 表名(
	id int primary key auto_increment,
    name varchar(50) unique
);
1
2
3
4

4.2.4 非空约束

非空约束 - not null，它用于约束字段的值不能为空。

-- 建表时指定非空
create table emp1(
	id int,
    name varchar(50) not null  -- name不能为空
);
1
2
3
4
5

4.2.5 检查约束

检查约束 - check，MySQL 8.0.16及以上才支持。
用于约束插入数据时的值。

--案例1：id只能插入大于100才能插入
create table emp1(
	id int,
    name varchar(50),
    check(id>100)
);

--案例2：sex的值只能是'男'或者'女'才能插入
create table emp2(
	id int,
    name varchar(50),
    sex varchar(1),
    check(sex in('男','女'))
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14