数据库SQL语句基础点总结
1、存储引擎
针对不同的数据格式需要对应不同的存储方式和处理机制。存储引擎就是不同的处理机制。
MySQL主要存储引擎：
- innodb：是MySQL5.5版本之后的默认存储引擎,存储数据更加安全
- myisam：是MySQL5.5版本之前的默认存储引擎，myisam比innodb速度要快，但是没它安全
- memory：内存引擎，数据全部存放在内存中，速度快，但是不能永久保存数据，断电就数据丢失
- blackhole：无论存什么都立刻消失
查看所有的存储引擎
show engines;
1.1 不同引擎创建表
用不同的引擎创建四个表
use db2;
create table t3(id int) engine=innodb; # 会产生两个文件，`frm`表结构和`ibd`表数据
create table t4(id int) engine=myisam; # 会产生三个文件，`fm`表结构 `myd`表数据 `myi`索引
create table t5(id int) engine=memory; # 会产生一个`frm`表结构的文件，数据在内存，无需文件存储
create table t6(id int) engine=blackhole; # 会产生一个`frm`表结构的文件，写进去就丢失，不会有表数据
2、创建表的完整语法
create table 表名(
字段名1 类型 (宽度) 约束条件,
字段名1 类型 (宽度) 约束条件,
...
)
- 宽度：一般情况下指的是对存储数据的限制,在5.6版本默认没有开启严格模式，规定只能存一个字符，字符存多了就自动截取，5.7版本及以上或开启了严格模式的话，那么规定存几个就不能超，一旦超出就报错。MySQL5.7版本之后默认开启
严格模式create table t1(name char)：默认宽度是1- 当默认宽度是1的时候，我们插入一个数据
insert info t1 values('abcdef') - 然后查看这张表
select * from t1会看到name的字段只有一个a
- 约束条件：比如创建表的时候加了结束
create table t2(id int, name char not null),然后插入的时候就不能插入nullinsert into t2(2, null);这样会报错
宽度和约束条件的区别：
- 宽度是用来限制数据的存储
- 约束条件是在宽度的基础上增加的额外的约束
注意事项：
- 同一张表中字段名不能重复
- 宽度和约束条件是可选的，字段名和字段类型是必选的，约束条件写的话也可以写多个
- 创建字段的时候最后一行不能有逗号
2.1 严格模式
查看是否严格模式，看sql_mode
show variables like '%mode';
-- +--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
-- | Variable_name | Value |
-- +--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
-- | block_encryption_mode | aes-128-ecb |
-- | gtid_mode | OFF |
-- | innodb_autoinc_lock_mode | 1 |
-- | innodb_strict_mode | ON |
-- | offline_mode | OFF |
-- | pseudo_slave_mode | OFF |
-- | rbr_exec_mode | STRICT |
-- | slave_exec_mode | STRICT |
-- | sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
-- +--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
-- 9 rows in set (0.00 sec)
- 模糊匹配 ‘mode’
like %mode,%匹配任意多个字符，_匹配任意单个字符
修改严格模式：
set session：只在当前窗口有效，退出了就失效了。set global：全局有效
修改为严格模式
set global sql_mode = 'STRICT_TRANS_TABLES';
设置完需要退出服务端重新进入一下。
3、数据类型
3.1 整型
分类：
int：4个字节，32位,从-2^31(-2,147,483,648)到2^31-1(2,147,483,647)的整型数据smallint：2个字节，16位,从-2^15(-32,768)到2^15-1(32,767)的整数数据bigint：8个字节，64位,从-2^63(-9223372036854775808)到2^63-1(9223372036854775807)的整型数据tinyint：1个字节，8位,无符号：从0到255的整数数据，有符号：-128~127
整型默认情况下都是带有符号的。
以tinyint为例
create table t9(id tinyint);
-- +-------+------------+------+-----+---------+-------+
-- | Field | Type | Null | Key | Default | Extra |
-- +-------+------------+------+-----+---------+-------+
-- | id | tinyint(4) | YES | | NULL | |
-- +-------+------------+------+-----+---------+-------+
insert into t9 values(-129),(256);
select * from t9;
-- tinyint默认是有符号的所以结果会只存到tinyint接受的最大范围`-128,127`，严格模式会直接报错，插不进去
-- 取消符号
create table t10 (id tinyint unsigned);
desc t10;
-- +-------+---------------------+------+-----+---------+-------+
-- | Field | Type | Null | Key | Default | Extra |
-- +-------+---------------------+------+-----+---------+-------+
-- | id | tinyint(3) unsigned | YES | | NULL | |
-- +-------+---------------------+------+-----+---------+-------+
针对整型，括号内的宽度问题
- int(8): 如果数字没有超出8位，那么默认用空格填充
- 如果数字超出了8位，那么有几位就存几位，遵循最大值
mysql> create table t12(id int(8));
-- Query OK, 0 rows affected (0.01 sec)
mysql> insert into t12 values(123456789);
-- Query OK, 1 row affected (0.00 sec)
mysql> select * from t12;
-- +-----------+
-- | id |
-- +-----------+
-- | 123456789 |
-- +-----------+
-- 1 row in set (0.00 sec)
会发现设置了8位，但是存9位存进去了。所以只有整型括号的数字不是表示限制位数，而是显示长度。
但是如果不想要默认的空格填充，可以加约束
-- 无符号。用0填充
create table t13 (id int unsigned zerofill)
所以针对整型字段，括号内无需指定宽度，默认的宽度足以足够显示所有数据。
3.2 浮点型
分类：
- float(255,30)：单精度，浮点数值,总共255位，小数部分占30位
- double(255,30)：双精度，浮点数值，总共255位，小数部分占30位
- decimal(65,30)：小数值，总共65位，小数部分占30位
精确度：float < double < decimal
3.3 字符类型
分类：
- char：定长，比如char(4)，存储数据的长度超过4个报错，不够4个空格补全
- varchar：变长，varchar(r)，不够四个字符有几个存几个，超过4个报错
char类型在硬盘上存的数据带有空格，显示的时候MySQL会自动将空格去掉了。不想让MySQL给去空格，可以修改sql_mode
set global sql_mode = 'STRICT_TRANS_TABLES, PAD_CHAR_TO_FULL_LENGTH'
需要注意的是，修改sql_mode不是修改操作，是重置操作，所以需要把之前修改的一并提交一次。
修改完可以通过char_length查看
select char_length(name) from t;
优缺点对比：
char：
- 缺点：浪费空间，因为定几个就存几个
- 优点：存取都简单，因为定好了长度，存取都按固定长度存取就好了
varchar：
- 优点：节省空间
- 缺点：存取较为麻烦，因为不定长度，所以在存的时候需要制作1bytes的报头，标识这组数据的长度，取的时候需要先读取报头，然后再取
3.4 时间类型
分类：
- date：年月日
- datetime：年月日时分秒
- time：时分秒
- Year：年
create table student(id int, name char(16), born_year year, study_time date);
insert into student (1, 'aaa', '2012', '2012-11-11')
3.5 枚举类型与集合类型
分类：
- 枚举(enum)：多选一，存数据的时候只能从枚举的项里面选择一个存储
- 集合(set)：多选多，集合可以只写一个，不能写没有列举的数据
使用：
create table user(
id ind,
name char(16),
gender enum('male', 'female', 'others')
)
insert into user values(1, 'feather', 'male')
-- 集合demo
create table teacher(
id int,
name char(16),
gender enum('male', 'female', 'others'),
hobby set('read', 'hecha', 'chi', 'happy')
)
insert into user values(1, 'feather', 'male', 'read,happy')
4、约束条件
前面有三个约束条件了zerofill、unsigned、not null
新增一些其他：
default：默认值
create table t12(
id int,
name char(16),
gender enum('male', 'female', 'others') default 'male'
)
-- +--------+--------------------------------+------+-----+---------+-------+
-- | Field | Type | Null | Key | Default | Extra |
-- +--------+--------------------------------+------+-----+---------+-------+
-- | id | int(11) | YES | | NULL | |
-- | name | char(16) | YES | | NULL | |
-- | gender | enum('male','female','others') | YES | | male | |
-- +--------+--------------------------------+------+-----+---------+-------+
-- 3 rows in set (0.01 sec)
insert into t12(name,id,gender) values('feather',2,'male');
-- +------+---------+--------+
-- | id | name | gender |
-- +------+---------+--------+
-- | 2 | feather | male |
-- +------+---------+--------+
-- 1 row in set (0.00 sec)
insert into t12(name,id) values('feather2',3);
-- +------+----------+--------+
-- | id | name | gender |
-- +------+----------+--------+
-- | 2 | feather | male |
-- | 3 | feather2 | male |
-- +------+----------+--------+
unique：唯一
- 单列唯一：设置了unique的一列的值唯一，例如
create table t13(id int unique, name char(16));，就只能id唯一不能重复 - 联合唯一：多列的值全部一样不行，例如设置了2列，只要其中有一项不一致就算不一致，
create table t14(id int, ip char(16), port int, unique(ip, port))
primary key：主键，非空且唯一，它除了有约束效果之外，还是Innodb存储引擎组织数据的依据，Innodb存储引擎在创建表的时候必须要有primary key，因为它类似于目录，能够帮助提升查询效率，并且也是建表的依据
- 一张表中有且只有一个主键，如果没有设置，那么会从上往下搜索直到遇到一个非空且唯一的字段将它自动升级为主键。
- 如果表中没有主键也没有其他非空且唯一的字段，那么Innodb会自己创建一个隐藏字段作为主键，隐藏意味着无法使用到它，就无法提升查询速度。
- 一张表中都应该有一个主键字段，通常将id设置为主键
- 主键也可以联合唯一
"""
primary key = not null + unique
设置主键的列约束：非空且唯一
"""
create table t5(id int primary key);
insert into t5 values(null);
insert into t5 values(1),(1);
# 联合主键
create table t14(id int, ip char(16), port int, primary key(ip, port))
auto_increment：自增，专门为主键服务的，每次主键自动增加，不用人力维护。不要给其他的项加，只能加到主键上。就算把之前的删掉了，自增主键也不会倒回去，只会接着自增。
create table t8(
id int primary key auto_increment,
name char(16)
);
insert into t8(name) values('name1'),('name2'),('name3');
--mysql> select * from t8;
-- +----+-------+
-- | id | name |
-- +----+-------+
-- | 1 | name1 |
-- | 2 | name2 |
-- | 3 | name3 |
-- +----+-------+
-- 3 rows in set (0.00 sec)
如果想清空表数据并且重置主键的话可以使用truncate
truncate t8;
5、外键 foreign key
外键是用来帮助建立表与表之间的关系的。
- 一对多表的关系，外键字段建在多的一方。
- 在创建表的时候一定要先建被关联表
- 在录入数据的时候也必须先录入被关联表
6、表关系
- 一对多关系
- 多对多关系
- 一对一关系
6.1 一对多关系
create database db3;
use db3;
-- 需要先建被关联表
create table dep(
id int primary key auto_increment,
name char(16),
dep_desc char(16)
);
create table emp(
id int primary key auto_increment,
name char(16),
gender enum('male', 'female', 'others') default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
);
-- mysql> desc emp;
-- +--------+--------------------------------+------+-----+---------+----------------+
-- | Field | Type | Null | Key | Default | Extra |
-- +--------+--------------------------------+------+-----+---------+----------------+
-- | id | int(11) | NO | PRI | NULL | auto_increment |
-- | name | char(16) | YES | | NULL | |
-- | gender | enum('male','female','others') | YES | | male | |
-- | dep_id | int(11) | YES | MUL | NULL | |
-- +--------+--------------------------------+------+-----+---------+----------------+
-- 4 rows in set (0.00 sec)
-- MUL 就是外键的意思
insert dep(name, dep_desc) values('bumen1', 'bumen1'),('bumen2','bumen2');
insert emp(name, dep_id) values('fea', 1),('ter',2);
如果想要直接删除dep里的数据会报错，需要先删除关联表的数据，然后把被关联的数据删掉，这样比较繁琐，可以使用级联更新/删除来操作
如果要使用级联更新、删除的话需要在建表的时候添加参数
create table dep(
id int primary key auto_increment,
name char(16),
dep_desc char(16)
);
create table emp(
id int primary key auto_increment,
name char(16),
gender enum('male', 'female', 'others') default 'male',
dep_id int,
foreign key(dep_id) references dep(id) on update cascade on delete cascade
);
添加了on update cascade on delete cascade配置就可以同步更新和删除了。
6.2 多对多关系
针对多对多关系的表，不能在两张表原有的表中创建外键，需要单独开设一张中间表
create table book (
id int primary key auto_increment,
title varchar(32),
price int
);
create table author(
id int primary key auto_increment,
name varchar(32),
age int
);
-- 中间表.对表中的字段进行一对多处理
create table book2author(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) references author(id) on update cascade on delete cascade,
foreign key(book_id) references book(id) on update cascade on delete cascade
);
insert into book(title, price) values('book1', 20), ('book2', 21), ('book3', 22);
insert into author(name, age) values('u1', 22), ('u2', 22), ('u3', 22);
-- 插入中间表，只需要插入book_id 和 author_id
insert into book2author(author_id, book_id) values(1,2),(2,1),(1,3),(2,3);
以上就创建好了多对多关系的表，下面进行测试他们是同步更新和删除的。
-- 先查看各个表的情况
-- mysql> select * from book;
-- +----+-------+-------+
-- | id | title | price |
-- +----+-------+-------+
-- | 1 | book1 | 20 |
-- | 2 | book2 | 21 |
-- | 3 | book3 | 22 |
-- +----+-------+-------+
-- 3 rows in set (0.00 sec)
-- mysql> select * from author;
-- +----+------+------+
-- | id | name | age |
-- +----+------+------+
-- | 1 | u1 | 22 |
-- | 2 | u2 | 22 |
-- | 3 | u3 | 22 |
-- +----+------+------+
-- 3 rows in set (0.00 sec)
-- mysql> select * from book2author;
-- +----+-----------+---------+
-- | id | author_id | book_id |
-- +----+-----------+---------+
-- | 1 | 1 | 2 |
-- | 2 | 2 | 1 |
-- | 3 | 1 | 3 |
-- | 4 | 2 | 3 |
-- +----+-----------+---------+
-- 4 rows in set (0.00 sec)
delete from author where id = 1;
-- mysql> select * from author;
-- +----+------+------+
-- | id | name | age |
-- +----+------+------+
-- | 2 | u2 | 22 |
-- | 3 | u3 | 22 |
-- +----+------+------+
-- 2 rows in set (0.00 sec)
-- mysql> select * from book2author;
-- +----+-----------+---------+
-- | id | author_id | book_id |
-- +----+-----------+---------+
-- | 2 | 2 | 1 |
-- | 4 | 2 | 3 |
-- +----+-----------+---------+
-- 2 rows in set (0.00 sec)
然后就发现author_id=1的数据没了，也没有报错。
6.3 一对一关系
假设一张表的字段特别多，每次查询又不是都需要，就可以进行拆表
假设有一个需求有id name age addr phone email....很多个字段数据，然后可以拆开成为用户表和详情表
- 用户表字段：
id name age - 用户详情表：
id addr phone email....
一个用户只有一个详情，一个详情只能描述一个用户，这种关系的表就是一对一关系。
一对一表关系的外键字段建在哪张表都可以，建议建在查询频率比较高的表中。
create table user(
id int primary key auto_increment,
name char(32),
age int,
userDetail_id int unique,
foreign key(userDetail_id) references detail(id) on update cascade on delete cascade
);
create table detail(
id int primary key auto_increment,
addr varchar(64),
phone int,
email varchar(64)
);
insert into detail(addr, phone, email) values('bjbjbj', 1582222, 'aaa@qq.com'), ('bjbjbj2', 22222, 'aaa2@qq.com'),('bjbjbj3', 333333, 'aaa3@qq.com');
insert into user(name, age ,userDetail_id) values('user1', 22, 1), ('user2',23,2);
这样就好了。
7、修改表
- 修改表名：
alter table 表名 rename 新表名; - 增加字段：
alter table 表名 add 字段名 字段类型(宽度) 约束条件 [first|after 字段名]; - 删除字段：
alter table 表名 drop 字段名; - 修改字段：
alter table 表名 modify 字段名 字段类型(宽度) 约束条件; - 改字段名：
alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;
8、复制表
sql语句查询的结果是一张虚拟表，存在内存中的表。
create table 新表名 select * from 旧表名;
注意点：
- 可以复制表，但是不能复制主键 外键等，只能复制表结构和数据。
- 如果复制了一些没有数据的内容，表还是会创建，但是是空表。