一.约束
作用: 保证数据的完整性和一致性
表的设计
1.not null 和 default
not null 是放在最后用来约束 前面 数据类型的
(在原有基础上本来可以主键后面可以为空,但是一旦在后面约束,则当输出的values()为空的时候会出错)
default 是放在后面约束 前面数据类型 如 default 1
(在原有基础上本来可以主键后面可以为空,一旦加上后面约束 ,则输出values()为空的时候,不会报错,会显示default后面的值)
在同时存在 not null 和 default 的时候 输出values()不会出错 ,显示fault的值
1 mysql> create table student2( 2 -> id int not null, 3 -> name varchar(50) not null, 4 -> age int(3) unsigned not null default 18, 5 -> sex enum('male','female') default 'male', 6 -> fav set('smoke','drink','tangtou') default 'drink,tangtou' 7 -> ); 8 Query OK, 0 rows affected (0.01 sec) 9 10 # 只插入了not null约束条件的字段对应的值 11 mysql> insert into student2(id,name) values(1,'mjj'); 12 Query OK, 1 row affected (0.00 sec) 13 14 # 查询结果如下 15 mysql> select * from student2; 16 +----+------+-----+------+---------------+ 17 | id | name | age | sex | fav | 18 +----+------+-----+------+---------------+ 19 | 1 | mjj | 18 | male | drink,tangtou | 20 +----+------+-----+------+---------------+ 21 row in set (0.00 sec)
2.unique 不同的
应用举例: 创建公司部门表 (每一个表都是唯一的)
单列唯一:
就是再原有基础上用来限制 每一个数据结构只能是惟一的
1 create table dep(id int not null,name varchar(20) unique); 2 insert into dep(id,name) values(1,'alex'); 3 insert into dep(id,name) values(2,'alex');
多列唯一:
就是在原有基础上用来限制多个数据结构
1 create table dep2(id int unique,name varchar(20) unique); 2 3 insert into dep2(id,name) values(1,'alex')
组合唯一 :
相当于or ,两个一样才算错
1 create table dep3( 2 id int, 3 name varchar(20), 4 unique(id,name) 5 ); 6 insert into dep3(id,name) values(1,'alex'); 7 insert into dep3(id,name) values(1,'alex2'); 8 insert into dep3(id,name) values(2,'alex'); 9 10 不能插入的格式 11 insert into dep3(id,name) values(1,'alex');
3.primary key
在进行约束后 key会为 PRI
一个表中可以:
单列做主键
多列做主键(复合主键)
约束:等价于 notnull unique,字段中的值不为空且唯一
存储引擎默认是（innodb）:对于innodb存储引擎来说，一张表必须有一个主键。
单列主键
1 # 创建t14表，为id字段设置主键，唯一的不同的记录 2 create table t14( 3 id int primary key, 4 name char(16) 5 ); 6 7 insert into t14 values 8 (1,'xiaoma'), 9 (2,'xiaohong'); 10 11 mysql> insert into t14 values(2,'wxxx'); 12 ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY' 13 14 15 # not null + unique的化学反应，相当于给id设置primary key 16 create table t15( 17 id int not null unique, 18 name char(16) 19 ); 20 mysql> create table t15( 21 -> id int not null unique, 22 -> name char(16) 23 -> ); 24 Query OK, 0 rows affected (0.01 sec) 25 26 mysql> desc t15; 27 +-------+----------+------+-----+---------+-------+ 28 | Field | Type | Null | Key | Default | Extra | 29 +-------+----------+------+-----+---------+-------+ 30 | id | int(11) | NO | PRI | NULL | | 31 | name | char(16) | YES | | NULL | | 32 +-------+----------+------+-----+---------+-------+ 33 rows in set (0.02 sec)
多列主键
1 create table t16( 2 ip char(15), 3 port int, 4 primary key(ip,port) 5 ); 6 7 insert into t16 values 8 ('1.1.1.2',80), 9 ('1.1.1.2',81); 10 11 验证复合主键的使用
4.auto_increment
约束:约束字段为自动增长,约束的字段必须同时被key约束
不指定id,则自动增长id
1 # 创建student 2 create table student( 3 id int primary key auto_increment, 4 name varchar(20), 5 sex enum('male','female') default 'male' 6 ); 7 8 mysql> desc student; 9 +-------+-----------------------+------+-----+---------+----------------+ 10 | Field | Type | Null | Key | Default | Extra | 11 +-------+-----------------------+------+-----+---------+----------------+ 12 | id | int(11) | NO | PRI | NULL | auto_increment | 13 | name | varchar(20) | YES | | NULL | | 14 | sex | enum('male','female') | YES | | male | | 15 +-------+-----------------------+------+-----+---------+----------------+ 16 rows in set (0.17 sec) 17 18 #插入记录 19 mysql> insert into student(name) values ('老白'),('小白'); 20 Query OK, 2 rows affected (0.01 sec) 21 Records: 2 Duplicates: 0 Warnings: 0 22 23 mysql> select * from student; 24 +----+--------+------+ 25 | id | name | sex | 26 +----+--------+------+ 27 | 1 | 老白 | male | 28 | 2 | 小白 | male | 29 +----+--------+------+ 30 rows in set (0.00 sec) 31 32 不指定id，则自动增长
也可以指定id
指定后如果没有指定id 则会继续增长id
1 mysql> insert into student values(4,'asb','female'); 2 Query OK, 1 row affected (0.00 sec) 3 4 mysql> insert into student values(7,'wsb','female'); 5 Query OK, 1 row affected (0.01 sec) 6 7 mysql> select * from student; 8 +----+--------+--------+ 9 | id | name | sex | 10 +----+--------+--------+ 11 | 1 | 老白 | male | 12 | 2 | 小白 | male | 13 | 4 | asb | female | 14 | 7 | wsb | female | 15 +----+--------+--------+ 16 rows in set (0.00 sec) 17 18 # 再次插入一条不指定id的记录，会在之前的最后一条记录继续增长 19 mysql> insert into student(name) values ('大白'); 20 Query OK, 1 row affected (0.00 sec) 21 22 mysql> select * from student; 23 +----+--------+--------+ 24 | id | name | sex | 25 +----+--------+--------+ 26 | 1 | 老白 | male | 27 | 2 | 小白 | male | 28 | 4 | asb | female | 29 | 7 | wsb | female | 30 | 8 | 大白 | male | 31 +----+--------+--------+ 32 rows in set (0.00 sec) 33 34 也可以指定id
删除后重头再来
1 mysql> delete from student; 2 Query OK, 5 rows affected (0.00 sec) 3 4 mysql> select * from student; 5 Empty set (0.00 sec) 6 7 mysql> select * from student; 8 Empty set (0.00 sec) 9 10 mysql> insert into student(name) values('ysb'); 11 Query OK, 1 row affected (0.01 sec) 12 13 mysql> select * from student; 14 +----+------+------+ 15 | id | name | sex | 16 +----+------+------+ 17 | 9 | ysb | male | 18 +----+------+------+ 19 row in set (0.00 sec) 20 21 #应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它 22 mysql> truncate student; 23 Query OK, 0 rows affected (0.03 sec) 24 25 mysql> insert into student(name) values('xiaobai'); 26 Query OK, 1 row affected (0.00 sec) 27 28 mysql> select * from student; 29 +----+---------+------+ 30 | id | name | sex | 31 +----+---------+------+ 32 | 1 | xiaobai | male | 33 +----+---------+------+ 34 row in set (0.00 sec) 35 36 mysql> 37 38 对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
二
1.sql的基本语法
对数据库:
create database db1
对表 :
create table t1(id int,name char(10));
show create table t1;
show tables :查看所有的表
desc t1;查看表的详细结构
对数据:
insert into t1(id,name) values(1,'alex'),(2,'武sir'); 插入
insert into t1 value(3,'日天');
select id from t1;
select id,name from t1;
select * from t1;
select * from db1.t1 where id = 2;
关系型数据库：mysql db2 oricle sqlite 表中存储
非关系型数据库：monogodb redis
key : value 基于model 模型 obj.insert
2.存储引擎
mysql 5.5版本之后默认为innodb存储引擎
另外还有mysiam、memory、blackhone
#memory，在重启mysql或者重启机器后，表内数据清空
#blackhole，往表内插入任何数据，都相当于丢入黑洞，表内永远不存记录
复制表结构和表数据
create table a1 select * from db2.t1;
只复制表结构
create table a2 select * from db2.t1 where 1>2;
create table a3 like db2.t1;
3.数据类型
#1. 数字：
整型：tinyint 作用:用于保存范围小的
有符号：
-128 ～ 127
无符号：
0 ～ 255
PS： MySQL中无布尔值，使用tinyint(1)构造。
int
有符号：
-2147483648 ～ 2147483647
无符号：
0 ～ 4294967295
bigint
大整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-9223372036854775808 ～ 9223372036854775807
无符号：
0 ～ 18446744073709551615
有符号和无符号tinyint
注意:unsigned 不允许为负数
int类型后面的存储是显示宽度，而不是存储宽度
1 mysql> create table t3(id int(1) unsigned); 2 3 #插入255555记录也是可以的 4 mysql> insert into t3 values(255555); 5 6 mysql> select * from t3; 7 +--------+ 8 | id | 9 +--------+ 10 | 255555 | 11 +--------+ 12 ps:以上操作还不能够验证，再来一张表验证用zerofill 用0填充 13 14 # zerofill 用0填充 15 mysql> create table t4(id int(5) unsigned zerofill); 16 17 18 mysql> insert into t4 value(1); 19 Query OK, 1 row affected (0.00 sec) 20 21 #插入的记录是1，但是显示的宽度是00001 22 mysql> select * from t4; 23 +-------+ 24 | id | 25 +-------+ 26 | 00001 | 27 +-------+ 28 row in set (0.00 sec)
#2.小数：
浮点型
浮点类型：FLOAT DOUBLE deimal
作用：存储薪资、身高、体重、体质参数等
范围:
1 -------------------------FLOAT------------------- 2 FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] 3 #参数解释：单精度浮点数（非准确小数值），M是全长，D是小数点后个数。M最大值为255，D最大值为30 4 5 #有符号： 6 -3.402823466E+38 to -1.175494351E-38, 7 1.175494351E-38 to 3.402823466E+38 8 9 #无符号： 10 1.175494351E-38 to 3.402823466E+38 11 #精确度： 12 **** 随着小数的增多，精度变得不准确 **** 13 14 15 -------------------------DOUBLE----------------------- 16 DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] 17 18 #参数解释： 双精度浮点数（非准确小数值），M是全长，D是小数点后个数。M最大值为255，D最大值为30 19 20 #有符号： 21 -1.7976931348623157E+308 to -2.2250738585072014E-308 22 2.2250738585072014E-308 to 1.7976931348623157E+308 23 24 #无符号： 25 2.2250738585072014E-308 to 1.7976931348623157E+308 26 27 #精确度： 28 ****随着小数的增多，精度比float要高，但也会变得不准确 **** 29 30 ====================================== 31 --------------------DECIMAL------------------------ 32 decimal[(m[,d])] [unsigned] [zerofill] 33 34 #参数解释：准确的小数值，M是整数部分总个数（负号不算），D是小数点后个数。 M最大值为65，D最大值为30。 35 36 37 #精确度： 38 **** 随着小数的增多，精度始终准确 **** 39 对于精确数值计算时需要用此类型 40 decaimal能够存储精确值的原因在于其内部按照字符串存储。
精度: deimal > double > float
1 # 分别对三张表插入相应的记录 2 mysql> insert into t5 values(1.1111111111111111111111111111111);#小数点后31个1 3 Query OK, 1 row affected (0.01 sec) 4 5 mysql> insert into t6 values(1.1111111111111111111111111111111); 6 Query OK, 1 row affected (0.01 sec) 7 8 mysql> insert into t7 values(1.1111111111111111111111111111111); 9 Query OK, 1 row affected, 1 warning (0.00 sec) 10 11 # 查询结果 12 mysql> select * from t5; #随着小数的增多，精度开始不准确 13 +----------------------------------+ 14 | x | 15 +----------------------------------+ 16 | 1.111111164093017600000000000000 | 17 +----------------------------------+ 18 row in set (0.00 sec) 19 20 mysql> select * from t6; #精度比float要准确点，但随着小数的增多，同样变得不准确 21 +----------------------------------+ 22 | x | 23 +----------------------------------+ 24 | 1.111111111111111200000000000000 | 25 +----------------------------------+ 26 row in set (0.00 sec) 27 28 mysql> select * from t7; #精度始终准确,d为30，于是只留了30位小数 29 +----------------------------------+ 30 | x | 31 +----------------------------------+ 32 | 1.111111111111111111111111111111 | 33 +----------------------------------+ 34 row in set (0.00 sec)
float ：在位数比较短的情况下不精准
double ：在位数比较长的情况下不精准
0.000001230123123123
存成：0.000001230000
decimal：（如果用小数，则用推荐使用decimal）
精准
内部原理是以字符串形式去存
#3. 时间类型： DATE TIME DATETIME TIMESTAMP YEAR
1 mysql> create table student( 2 -> id int, 3 -> name varchar(20), 4 -> born_year year, 5 -> birth date, 6 -> class_time time, 7 -> reg_time datetime 8 -> ); 9 Query OK, 0 rows affected (0.02 sec) 10 11 mysql> insert into student values 12 -> (1,'alex',"1995","1995-11-11","11:11:11","2017-11-11 11:11:11"), 13 -> (2,'egon',"1997","1997-12-12","12:12:12","2017-12-12 12:12:12"), 14 -> (3,'wsb',"1998","1998-01-01","13:13:13","2017-01-01 13:13:13"); 15 Query OK, 3 rows affected (0.00 sec) 16 Records: 3 Duplicates: 0 Warnings: 0 17 18 mysql> select * from student; 19 +------+------+-----------+------------+------------+---------------------+ 20 | id | name | born_year | birth | class_time | reg_time | 21 +------+------+-----------+------------+------------+---------------------+ 22 | 1 | alex | 1995 | 1995-11-11 | 11:11:11 | 2017-11-11 11:11:11 | 23 | 2 | egon | 1997 | 1997-12-12 | 12:12:12 | 2017-12-12 12:12:12 | 24 | 3 | wsb | 1998 | 1998-01-01 | 13:13:13 | 2017-01-01 13:13:13 | 25 +------+------+-----------+------------+------------+---------------------+ 26 rows in set (0.00 sec)
在使用事件数据结构的时候 ,加入values() 里面的数据一定是字符串的形式,需要加" "
最常用：datetime
#4. 枚举类型与集合类型
enum 和set
字段的值只能在给定范围中选择，如单选框，多选框
enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）
1 mysql> create table consumer( 2 -> id int, 3 -> name varchar(50), 4 -> sex enum('male','female','other'), 5 -> level enum('vip1','vip2','vip3','vip4'),#在指定范围内，多选一 6 -> fav set('play','music','read','study') #在指定范围内，多选多 7 -> ); 8 Query OK, 0 rows affected (0.03 sec) 9 10 11 mysql> insert into consumer values 12 -> (1,'赵云','male','vip2','read,study'), 13 -> (2,'赵云2','other','vip4','play'); 14 Query OK, 2 rows affected (0.00 sec) 15 Records: 2 Duplicates: 0 Warnings: 0 16 17 mysql> select * from consumer; 18 +------+---------+-------+-------+------------+ 19 | id | name | sex | level | fav | 20 +------+---------+-------+-------+------------+ 21 | 1 | 赵云 | male | vip2 | read,study | 22 | 2 | 赵云2 | other | vip4 | play | 23 +------+---------+-------+-------+------------+ 24 rows in set (0.00 sec)
#5. 字符串：
实际上varchar 比 char 快上不少
char（10）：简单粗暴，浪费空间，存取速度快
root存成root000000
varchar：精准，节省空间，存取速度慢
char填充空格来满足固定长度，但是在查询时却会很不要脸地删除尾部的空格（装作自己好像没有浪费过空间一样），然后修改sql_mode让其现出原形。
现出原形:
1 # 创建t1表,分别指明字段x为char类型，字段y为varchar类型 2 mysql> create table t1(x char(5),y varchar(4)); 3 Query OK, 0 rows affected (0.16 sec) 4 5 # char存放的是5个字符，而varchar存4个字符 6 mysql> insert into t1 values('你瞅啥 ','你瞅啥 '); 7 Query OK, 1 row affected (0.01 sec) 8 9 # 在检索时char很不要脸地将自己浪费的2个字符给删掉了，装的好像自己没浪费过空间一样，而varchar很老实，存了多少，就显示多少 10 mysql> select x,char_length(x),y,char_length(y) from t1; 11 +-----------+----------------+------------+----------------+ 12 | x | char_length(x) | y | char_length(y) | 13 +-----------+----------------+------------+----------------+ 14 | 你瞅啥 | 3 | 你瞅啥 | 4 | 15 +-----------+----------------+------------+----------------+ 16 row in set (0.02 sec) 17 18 #略施小计，让char现原形 19 mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'; 20 Query OK, 0 rows affected (0.00 sec) 21 22 #查看当前mysql的mode模式 23 mysql> select @@sql_mode; 24 +-------------------------+ 25 | @@sql_mode | 26 +-------------------------+ 27 | PAD_CHAR_TO_FULL_LENGTH | 28 +-------------------------+ 29 row in set (0.00 sec) 30 31 #原形毕露了吧。。。。 32 mysql> select x,char_length(x) y,char_length(y) from t1; 33 +-------------+------+----------------+ 34 | x | y | char_length(y) | 35 +-------------+------+----------------+ 36 | 你瞅啥 | 5 | 4 | 37 +-------------+------+----------------+ 38 row in set (0.00 sec) 39 40 # 查看字节数 41 #char类型：3个中文字符+2个空格=11Bytes 42 #varchar类型:3个中文字符+1个空格=10Bytes 43 mysql> select x,length(x),y,length(y) from t1; 44 +-------------+-----------+------------+-----------+ 45 | x | length(x) | y | length(y) | 46 +-------------+-----------+------------+-----------+ 47 | 你瞅啥 | 11 | 你瞅啥 | 10 | 48 +-------------+-----------+------------+-----------+ 49 row in set (0.02 sec)
sql优化：创建表时，定长的类型往前放，变长的往后放
比如性别 比如地址或描述信息
>255个字符，超了就把文件路径存放到数据库中。
比如图片，视频等找一个文件服务器，数据库中只存路径或url。