-- default默认值，在创建表的时候可以为字段设定一个默认值
-- ps：在创建表的规范时，最后一条建议字段设为非空，可以使用默认值进行填充。
-- 注意：在使用简略的插入数据的SQL语法(insert into 表名 values(值1, ...))时必须所有字段都有值，即使是设定了默认值的情况下。
mysql> create table t1(id int, sex enum('male', 'female', 'unkonwn') not null default 'unkonwn');
Query OK, 0 rows affected (0.03 sec)
mysql> desc t1;
+-------+---------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| sex | enum('male','female','unkonwn') | NO | | unkonwn | |
+-------+---------------------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
2-14 约束条件 - unique
-- unique 唯一约束条件(key)，插入数据唯一不能重复，比如说学生编号
mysql> create table t2(id int unique, name varchar(6));
Query OK, 0 rows affected (0.04 sec)
mysql> desc t2;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int | YES | UNI | NULL | |
| name | varchar(6) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into t2 values(1, 'lili');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t2 values(2, 'lili'); -- name字段重复不会报错
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values(2, 'lili'); -- id因为有了unique约束条件会提示用重复(Duplicate)的数据
ERROR 1062 (23000): Duplicate entry '2' for key 't2.id'
-- 可以使用联合唯一，几个字段连在一起时是唯一的
mysql> create table t3(id int, name varchar(5), unique(id, name)); -- id+name唯一
Query OK, 0 rows affected (0.04 sec)
mysql> desc t3;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int | YES | MUL | NULL | |
| name | varchar(5) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into t3 values(1, 'lili');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t3 values(2, 'lili'); -- name重复不会报错
Query OK, 1 row affected (0.01 sec)
mysql> insert into t3 values(1, 'qq'); -- id重复也不会报错
Query OK, 1 row affected (0.08 sec)
mysql> insert into t3 values(1, 'lili'); -- name+id重复就会报错提示重复了
ERROR 1062 (23000): Duplicate entry '1-lili' for key 't3.id'
2-15 约束条件 - primary key
-- primary key，key的一种，主键效果等同于not null+unique，即非空且唯一
mysql> create table t4(id int primary key, name varchar(6) unique);
Query OK, 0 rows affected (0.63 sec)
mysql> desc t4;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(6) | YES | UNI | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- 主键除了能够对字段进行约束之外，还是innodb存储引擎组织数据的依据，这个在详细介绍innodb时再详细介绍
-- 使用innodb存储引擎创建表时必须要有主键，并且可以通过主键提升查询效率
-- 在一张表中有且只有一个主键
mysql> create table t5(id int primary key, name char(6) primary key);
ERROR 1068 (42000): Multiple primary key defined
-- 如果没有设置主键，则按照创建表时的字段顺序第一个非空且唯一的字段将自动升级为主键。
mysql> create table t5(id int, name char(6) not null unique, age int not null unique);
Query OK, 0 rows affected (0.03 sec)
mysql> desc t5;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | char(6) | NO | PRI | NULL | |
| age | int | NO | UNI | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
-- 如果表中没有设置逐渐和非空且唯一的字段，则Innodb默认提供一个隐藏的主键，但是这个默认隐藏的主键无法提供快速查询的服务。
mysql> create table t6(id int, name char(6));
Query OK, 0 rows affected (0.03 sec)
mysql> desc t6;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | char(6) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- 一般，表中的主键应该设置为id字段，id字段一般在创建表的时候是必须指定的，并且是主键字段
-- 主键可以为一个字段，也可以是联合主键（多个字段联合起来作为表的主键，本质还是一个主键）
mysql> create table t7(id int, name varchar(5), primary key(id, name));
Query OK, 0 rows affected (0.03 sec)
mysql> desc t7;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(5) | NO | PRI | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
2-16 约束条件 - auto_increment
-- auto_increment 自增约束条件，通常是主键字段使用，普通字段不使用该约束条件，可以标识当前数据是是表中d第几条数据
-- 同一张表中只能有一个字段使用该约束条件。
mysql> create table t8(id int primary key auto_increment, cid int auto_increment);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key -- 提示只能有一个列是自增列
-- 向表中写入数据时，可以省略主键，因为主键是非空唯一且自增的
-- 主键的初始值是1，每向表中写入一条数据，主键值自动+1
mysql> create table t9(id int primary key auto_increment, name char(6));
Query OK, 0 rows affected (0.04 sec)
mysql> desc t9;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | char(6) | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> insert into t9 values('lili'); -- 当不手动写入主键字段时，不能使用简略的插入数据的sql语法
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into t9(name) values('lili');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t9;
+----+------+
| id | name |
+----+------+
| 1 | lili |
+----+------+
1 row in set (0.00 sec)
-- 小练习
mysql> create database info charset=utf8mb4; -- 创建库
mysql> create table student(
id int primary key auto_increment comment '主键',
name varchar(6) not null unique,
age int unsigned not null,
sex enum('male', 'female') not null default 'male',
hobby set('read', 'play', 'sleep') null,
create_time datetime default localtime()
) engine=innodb charset=utf8mb4; -- 创建表
mysql> insert into student(name, age, hobby) values('丽丽', 18, 'read,sleep');
Query OK, 1 row affected (0.02 sec)
mysql> select * from student;
+----+--------+-----+------+------------+---------------------+
| id | name | age | sex | hobby | create_time |
+----+--------+-----+------+------------+---------------------+
| 1 | 丽丽 | 18 | male | read,sleep | 2022-03-01 09:27:00 |
+----+--------+-----+------+------------+---------------------+
1 row in set (0.00 sec)
2-18 表增加字段
-- 表准备
mysql> create database test;
mysql> create table test.t1(id int primary key auto_increment);
-- 添加1个字段
alter table 表名 add 字段名 数据类型(宽度) 约束条件 comment '字段注释';
mysql> alter table t1 add name varchar(5);
mysql> desc t1;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(5) | YES | | NULL | |
+-------+------------+------+-----+---------+----------------+
-- 添加多个字段
alter table 表名 add (字段名1 数据类型(宽度) 约束条件 comment '字段注释', 字段名2 数据类型(宽度) 约束条件 comment '字段注释');
mysql> alter table t1 add (age int, sex enum('male', 'female') default 'male');
mysql> desc t1;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(5) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | enum('male','female') | YES | | male | |
+-------+-----------------------+------+-----+---------+----------------+
-- 向指定位置添加列(在第一个字段的位置写入新的字段，只能添加一个字段)
alter table 表名 add 字段名 字段类型(宽度) 约束条件 comment '字段注释' first;
mysql> alter table t1 add fir_name int unsigned first;
mysql> desc t1;
+----------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------------------+------+-----+---------+----------------+
| fir_name | int unsigned | YES | | NULL | |
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(5) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | enum('male','female') | YES | | male | |
+----------+-----------------------+------+-----+---------+----------------+
-- 向指定位置添加列(在某个字段之后写入新的字段)
alter table 表名 add 字段名 字段类型(宽度) 约束条件 comment '字段注释' after 字段名;
mysql> alter table t1 add after_name char(6) default 'sss' after age;
mysql> desc t1;
+------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------------------+------+-----+---------+----------------+
| fir_name | int unsigned | YES | | NULL | |
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(5) | YES | | NULL | |
| age | int | YES | | NULL | |
| after_name | char(6) | YES | | sss | |
| sex | enum('male','female') | YES | | male | |
+------------+-----------------------+------+-----+---------+----------------+
2-19 表的其他修改和删除操作
-- 表准备
create table t1(id int primary key auto_increment, name varchar(6) not null unique);
desc t1;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(6) | NO | UNI | NULL | |
+-------+------------+------+-----+---------+----------------+
-- 修改表名，不能修改库名但是可以修改表名
alter table 表名 rename 新表名;
alter table t1 rename t0;
show tables;
-- 修改字段数据类型、约束条件，需要将不需要修改的约束条件同时写上，否则约束条件可能会产生变化
alter table 表名 modify 字段名 字段类型(宽度) 约束条件；
mysql> alter table t1 modify name char(6); -- 只修改字段的数据类型，约束条件变化了
mysql> desc t1;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | char(6) | YES | UNI | NULL | |
+-------+---------+------+-----+---------+----------------+
-- 修改字段名，如果约束条件不变的话也是需要同时写上修改前的约束条件，否则约束条件可能会产生变化
alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;
mysql> alter table t1 change name new_name char(6);
mysql> desc t1;
+----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| new_name | char(6) | YES | UNI | NULL | |
+----------+---------+------+-----+---------+----------------+
-- 删除字段，删除字段后，如果表中有数据那么对应该字段的数据都会被删除。
alter table 表名 drop 字段名;
alter table t1 drop name;
desc t1;
-- 删除表
drop table 表名;
2-20 数据的修改
-- 表准备
create table t1(id int primary key auto_increment, name char(6), age int);
insert into t1(name, age) values
('lili', 18),
('nini', 19),
('nana', 20);
-- 数据的修改操作,如果不指明修改哪一行的话会将所有数据行的字段值都进行修改操作。
update 表名 set 字段名=新字段值, 字段名=新字段值,... where 条件;
2-21 数据的删除
-- 提示：删除操作非常危险，在实际开发中尽量不要执行删除操作
-- 删除数据，删除一条数据
delete from 表名 where 条件;
-- 删除数据，删除表中所有数据
drop table 表名; -- 删除表就表示表中所有的数据都被删除
delete from 表名; -- 清空表中所有数据
truncate table 表名; -- 清空表中所有数据
-- delete和truncate的区别
1.delete清空表之后向表中再写入数据主键的值是在已经删除的数据的基础上+1，而truncate清空表之后，再写入数据，主键值是从1开始的。
2.delete是DML操作也就是数据库修改操作, 是逻辑性质删除,逐行进行删除,速度慢.并未真正在磁盘上删除，磁盘空间不会立即释放。
3.truncate是DDL操作也就是数据库定义的操作,对表的数据进行清空,速度快.磁盘空间立即释放
-- 补充小知识
在实际开发场景中，通常使用一个字段来标识数据是否删除，而不是真正的将数据从我们的表中删除，比如说我们可以定义一个字段is_delete来表示该条数据是否被删除了，我们可以使用0表示数据被删除1表示数据没有被删除。
像这种只有两种状态的数据我们都可以使用布尔类型进行定义。
在MySQL中MySQL内置其实并没有布尔类型，但是MySQL支持在定义字段的时候将数据类型定义为布尔类型叫BOOLEAN或BOOL，布尔类型只有两种状态，一种是真一种是假。
在MySQL中0被认为是假(FALSE/False/false)，非零值被认为是真(true/True/TRUE)，当然也可以使用布尔文本也就是FALSE/False/false和true/True/TRUE表示真假，但是在显示数据的时候布尔文本只显示0和1这两种状态
alter table t1 add(is_delete bool default true);
alter table t1 add(is_delete1 boolean default 1);
mysql> desc t1;
+------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| new_name | char(6) | YES | UNI | NULL | |
| is_delete | tinyint(1) | YES | | 1 | |
| is_delete1 | tinyint(1) | YES | | 1 | |
+------------+------------+------+-----+---------+----------------+
mysql> insert into t1(new_name, is_delete) values('11', FALSE);
mysql> select * from t1;
+----+----------+-----------+------------+
| id | new_name | is_delete | is_delete1 |
+----+----------+-----------+------------+
| 1 | 11 | 0 | 1 |
+----+----------+-----------+------------+
我们可以通过查看创建表的完整SQL看一下MySQL的布尔类型到底是什么类型。可以发现定义字段时使用的布尔类型变成了tinyint(1)。
mysql> show create table t1 \G;
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int NOT NULL AUTO_INCREMENT,
`new_name` char(6) DEFAULT NULL,
`is_delete` tinyint(1) DEFAULT '1',
`is_delete1` tinyint(1) DEFAULT '1',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`new_name`),
UNIQUE KEY `name_2` (`new_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
2-22 库的操作总结
SQL：SQL语句对大小写不敏感，SQL语法大写小写都可以，但是库名、表名、字段名定义的时候是小写的话在SQL语句中也是小写。
创建数据库的规范：
库名不建议有大写字母
创建数据库时建议指定字符集
库名不能以数字开头
库名不能使用MySQL关键字命名
库名要和实际业务相关
增create - 创建数据库：
语法：create database 库名(charset=字符集);
查show：
show databases; -- 查看当前MySQL中所有的库
show create database 库名; -- 查看指定数据库的创建规则
改alter - 修改数据库的字符集，不支持直接修改库名：
alter database 库名 charset 字符集;
删drop：
drop database 库名;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
