1. 创建表的完整语法
SQL语句 不区别大小写。
创建表的完整语法 :
create table 表名称 (
字段名 1 数据类型 ( 宽度 ) 约束条件 1 ,
字段名 2 数据类型 ( 宽度 ) 约束条件 1 ,
字段名 3 数据类型 ( 宽度 ) 约束条件 1
)
1.1 语法规定
1. 在同一张表中字段名不能重复。
2. 宽带和约束条件是可选的 , 而字段名和字段类型是必须写的 , 约束条件可以写多个。
3. 最后一行不需要逗号。
create table t2( id int , id char ( 4 ) ) ;
create table t1( id) ;
create table t1( id int , name char , ) ;
1.2 宽度
宽度一帮情况下指的是对存储数据的限制。
* 整数类型括号内的数据是显示范围不是宽度 .
create table t5( name char ) ;
desc t5;
+ ------- + --------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + --------- + ------ + ----- + --------- + ------- +
| name | char ( 1 ) | YES | | NULL | |
+ ------- + --------- + ------ + ----- + --------- + ------- +
insert into t5 values ( 'kid21' ) ;
insert into t5 values ( null ) ;
select * from t5;
+ ------ +
| name |
+ ------ +
| k |
| NULL |
+ ------ +
针对不同的版本会出现不同的效果:
5.6 版本默认没有开启严格模式 , 超出部分会被舍弃 , 从左往右截取有效部分存储。
5.7 版本及以上 ( 默认开启了严格模式 ) 一旦超出范围立刻报错。
( 使用数据库的准则:遵循尽少让数据库干活 , 不添加额外压力。 )
1.3 修改表名
修改表名
alter table 表名 rename 新表名 ;
1.4 增加字段
增加字段
alter table 表名 add 字段名 字段类型(宽度) 约束条件 ; 默认在末尾加。
alter table 表名 add 字段名 字段类型(宽度) 约束条件 first ; 第一位。
alter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名 ; 跟在xx字段后面。
1.5 删除字段
删除字段
alter table 表名 drop 字段名 ; 对应数据全部一起删除。
1.6 修改字段
修改字段
alter table 表名 modify 字段名 字段类型(宽度)约束条件 ; 修改字段信息
alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件 ; 字段替换
1.7起别名
起别名
as 可以给字段起别名,也可以省略不写。
select 字段名 as 'xxx1' , 字段名 as 'xxx2' from 表名称 as 'xxx3' ; 推荐写法。
select 字段名 'xxx1' , 字段名 'xxx2' from 表名称 'xxx3' ;
1.8 修改数据
修改数据
updata 表名 set 字段 = 新值 where 字段 = 某值
2.约束条件
宽度与约束 :
宽带限制数据的存储
约束条件是在宽度的基础上增加了额外的约束。
3.非空
not null 设置禁止插入空。
在指定字段传值的时候 :
没有设置禁止写入空 , 在传值的时候没有给字段传值 , 默认用NULL边补 .
设置之后 , 必须给该字段传给值 .
顺序传值 , 的情况下不传就报错了 , 但是可以传入null .
3.1不设非空
create table t0( id int , name char ) ;
desc t0;
↓ 看字段 对应 Null 的关系
+ ------- + --------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + --------- + ------ + ----- + --------- + ------- +
| id | int ( 11 ) | YES | | NULL | |
| name | char ( 1 ) | YES | | NULL | |
+ ------- + --------- + ------ + ----- + --------- + ------- +
insert into t0( id) values ( 1 ) ;
insert into t0 values ( 2 , null ) ;
select * from t0;
+ ------ + ------ +
| id | name |
+ ------ + ------ +
| 1 | NULL |
| 2 | NULL |
+ ------ + ------ +
3.2设置非空
create table t6( id int , name char not null ) ;
desc t6;
↓ 看字段 对应 Null 的关系
+ ------- + --------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + --------- + ------ + ----- + --------- + ------- +
| id | int ( 11 ) | YES | | NULL | |
| name | char ( 1 ) | NO | | NULL | |
+ ------- + --------- + ------ + ----- + --------- + ------- +
insert into t6 values ( 1 , null ) ;
ERROR 1048 ( 23000 ) : Column 'name' cannot be null
insert into t6 values ( 1 , '' ) ;
4.唯一
unique 限制字段唯一
单列唯一:一个字段唯一。
联合唯一:单个都可以重复 , 但是加在一起必须唯一。
4.1单列唯一
creatE table t4(
id int unique ,
name varchar ( 16 )
) ;
desc t4;
# ↓ 唯一 Key UNI
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| id | int ( 11 ) | YES | UNI | NULL | |
| name | varchar ( 16 ) | YES | | NULL | |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
insert into t4 values ( 1 , 'kid' ) ;
insert into t4 values ( 1 , 'qz' ) ;
ERROR 1062 ( 23000 ) : Duplicate entry '1' for key 'id'
4.2联合唯一
create table t5(
id int ,
ip varchar ( 16 ) ,
port int ,
unique ( ip, port)
) ;
insert into t5 values ( 1 , '127.0.0.1' , 3306 ) ;
insert into t5 values ( 2 , '127.0.0.1' , 3305 ) ;
insert into t5 values ( 3 , '127.0.0.2' , 3306 ) ;
insert into t5 values ( 4 , '127.0.0.1' , 3306 ) ;
ERROR 1062 ( 23000 ) : Duplicate entry '127.0.0.1-3306' for key 'ip'
select * from t5;
+ ------ + ----------- + ------ +
| id | ip | port |
+ ------ + ----------- + ------ +
| 1 | 127.0 .0 .1 | 3306 |
| 2 | 127.0 .0 .1 | 3305 |
| 3 | 127.0 .0 .2 | 3306 |
+ ------ + ----------- + ------ +
5.主键
primary key 主键
1. 单从效果上来看 primary key 等价于 not null + unique 非空且唯一。
2. Innodb 存储引擎在创建表的时候必须要有primary key , 它是这个引擎组织的依据。
(类似书的目录 , 能提高查询的效率并且也是建表的依据)
2.1 一张表中有且只有一个主键 , 如果你没有设置主键 ,
那么会从上至下搜索直到遇到一个非空且唯一的字段将它自动升级为主键。
2.2 如果表中没有主键也没有非空且唯一的字段 ,
那么Innodb会采用自己内部提供的一个隐藏字段作为主键。
(隐藏意味着你无法使用它 , 就无法提升查询速度)
2.3 单个字段主键:一张表中通常应该有一个主键字段并且通常将表头编号id字段作为主键。
2.4 多个字段联合主键:多个字段联合起来作为变的主键本质还是一个主键。
5.1主键
create table t6( id int primary key ) ;
desc t6;
# Key PRI 主键的标识
+ ------- + --------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + --------- + ------ + ----- + --------- + ------- +
| id | int ( 11 ) | NO | PRI | NULL | |
+ ------- + --------- + ------ + ----- + --------- + ------- +
insert into t6 values ( null ) ;
ERROR 1048 ( 23000 ) : Column 'id' cannot be null
insert into t6 values ( 1 ) , ( 1 ) ;
ERROR 1062 ( 23000 ) : Duplicate entry '1' for key 'PRIMARY'
5.2自动升级为主键
create table t7(
id int not null unique ,
name varchar ( 16 ) ,
age int ,
addr varchar ( 32 ) not null unique
) ;
desc t7;
# 没有设置primary key 第一个非空且唯一 升级为主键
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| id | int ( 11 ) | NO | PRI | NULL | |
| name | varchar ( 16 ) | YES | | NULL | |
| age | int ( 11 ) | YES | | NULL | |
| addr | varchar ( 32 ) | YES | UNI | NULL | |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
5.3联合组建
create table t9(
ip varchar ( 16 ) ,
port int ,
primary key ( ip, port)
) ;
desc t9;
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| ip | varchar ( 16 ) | NO | PRI | | |
| port | int ( 11 ) | NO | PRI | 0 | |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
6.自增
auto_inctrment 自增
一帮配合primary key 主键使用 , 不能给普通字段加。
6.1配合主键使用
create table t1( id int auto_increment , name char ) ;
ERROR 1075 ( 42000 ) : Incorrect table definition; there can be only one auto column and it must
create table t1(
id int primary key auto_increment ,
name varchar ( 16 )
) ;
desc t1;
+ ------- + ------------- + ------ + ----- + --------- + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + ------------- + ------ + ----- + --------- + ---------------- +
| id | int ( 11 ) | NO | PRI | NULL | auto_increment |
| name | varchar ( 16 ) | YES | | NULL | |
+ ------- + ------------- + ------ + ----- + --------- + ---------------- +
insert into t1 ( name) values ( 'kid' ) , ( 'qz' ) , ( 'xxx' ) ;
select * from t1;
+ ---- + ------ +
| id | name |
+ ---- + ------ +
| 1 | kid |
| 2 | qz |
| 3 | xxx |
+ ---- + ------ +
6.2 自曾计算器
delete from 表名 ; 在删除表中的数据时 , 主键的自增计算不会重置。
truncate 表名 ; 清空表数据并且重置主键。
delete from t1;
select * from t1;
Empty set ( 0.00 sec)
insert into t1 ( name) values ( 'A' ) , ( 'B' ) , ( 'C' ) ;
select * from t1;
# 计算器从上次的位置开始自增
+ ---- + ------ +
| id | name |
+ ---- + ------ +
| 4 | A |
| 5 | B |
| 6 | C |
+ ---- + ------ +
truncate t1;
insert into t1 ( name) values ( 'A' ) , ( 'B' ) , ( 'C' ) ;
select * from t1;
# 计算器从 1 开始
+ ---- + ------ +
| id | name |
+ ---- + ------ +
| 1 | A |
| 2 | B |
| 3 | C |
+ ---- + ------ +
如果自己打乱自增的顺序 , 给之间传值 , id会在上次自定义的值后面继续自增 ,
在中间插入值 , 不会回退到自定义的值 , 在计算 , 而是在最大的id值基础上自增 .
insert into t1 values ( 10 , 'D' ) ;
insert into t1 ( name) values ( 'E' ) , ( 'F' ) ;
select * from t1;
+
| id | name |
+
| 1 | A |
| 2 | B |
| 3 | C |
| 10 | D |
| 11 | E |
| 12 | F |
+
insert into t1 values ( 6 , 'G' ) ;
insert into t1 ( name) values ( 'H' ) , ( 'I' ) ;
select * from t1;
+
| id | name |
+
| 1 | A |
| 2 | B |
| 3 | C |
| 6 | G |
| 10 | D |
| 11 | E |
| 12 | F |
| 13 | H |
| 14 | I |
+
7.字段注释
comment 给字段加注释
格式 :
comment = '字段的注释'
show create table 表名 ; 查看字段的注释
create table t11 ( id int comment '序号' , name char comment '姓名' ) ;
show create table t11;
+ ------- + ---------------------------------------------------------------------------------- +
| Table | Create Table |
+ ------- + ---------------------------------------------------------------------------------- +
| t11 | CREATE TABLE ` t11 ` ( ` id ` int ( 11 ) DEFAULT NULL COMMENT '序号' , |
| | ` name ` char ( 1 ) DEFAULT ` name ` char ( 1 ) DEFAULT NULL COMMENT '姓名' ) |
| | ENGINE = InnoDB DEFAULT CHARSET = utf8 |
+ ------- + ---------------------------------------------------------------------------------- +