三、数据类型和约束
2020-01-03 本文已影响0人
胖虎喜欢小红
1、数据类型
在MySQL数据库管理系统中，可以通过存储引擎来决定表的类型。同时，MySQL数据库管理系统也
提供了数据类型决定表存储数据的类型。
1.整型
作用：用于存储用户的年龄、游戏的Level、经验值等。
分类：tinyint smallint mediumint int bigint
常用的是int
显示宽度：类型后面小括号内的数字是显示宽度，不能限制插入数值的大小
比如：bigint(2) 2是显示宽度
结论：整形的宽度仅为显示宽度，不是限制。因此建议整形无须指定宽度。
=====================================================
2.浮点数类型 FLOAT DOUBLE
作用：用于存储用户的身高、体重、薪水等
float(5.3) #一共5位，小数占3位.做了限制
mysql> create table test4(float_test float(5,2)); #案例
宽度不算小数点
==================================================================================
定点数类型 DEC
定点数在MySQL内部以字符串形式存储，比浮点数更精确，适合用来表示货币等精度高的数据。
3.字符串类型
作用：用于存储用户的姓名、爱好、发布的文章等
字符类型 char varchar --存字符串
char(10) 根据10，占10个.
列的长度固定为创建表时声明的长度： 0 ~ 255
varchar(10) 根据实际字符串长度占空间，最多10个
列中的值为可变长字符串，长度： 0 ~ 65535
案例:
mysql> create table t8(c char(5),v varchar(12));
Query OK, 0 rows affected (0.42 sec)
mysql> insert into t8 values('abcde','abcdef');
Query OK, 1 row affected (0.38 sec)
mysql> insert into t8 values('abc','abcdef'); #char可以少于规定长度。
Query OK, 1 row affected (0.05 sec)
mysql> insert into t8 values('abc777','abcdef7'); #char不能大于规定的长度。
ERROR 1406 (22001): Data too long for column 'c' at row 1
mysql>
=====================================================================
1.经常变化的字段用varchar
2.知道固定长度的用char
3.尽量用varchar
4.超过255字符的只能用varchar或者text
5.能用varchar的地方不用text
text：文本格式
-----------------------------------------------------------------
4.枚举类型 enum
mysql> create table t101(name enum('tom','jim'));
只能从tom,jim两个里面2选其1
（enumeration）
有限制的时候用枚举
==================================================================
5.日期类型
===时间和日期类型测试：year、date、time、datetime、timestamp
作用：用于存储用户的注册时间，文章的发布时间，文章的更新时间，员工的入职时间等
注意事项:
==插入年份时，尽量使用4位值
==插入两位年份时，<=69，以20开头，比如65, 结果2065
>=70，以19开头，比如82，结果1982
案例:
mysql> create table test_time(d date,t time,dt datetime);
Query OK, 0 rows affected (0.03 sec)
mysql> desc test_time;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d | date | YES | | NULL | |
| t | time | YES | | NULL | |
| dt | datetime | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert into test_time values(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql> select * from test_time;
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2019-08-23 | 00:26:29 | 2019-08-23 00:26:29 |
+------------+----------+---------------------+
1 row in set (0.00 sec)
测试年：
mysql> create table t3(born_year year);
Query OK, 0 rows affected (0.40 sec)
mysql> desc t3;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| born_year | year(4) | YES | | NULL | |
+-----------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into t3 values (12),(80);
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t3;
+-----------+
| born_year |
+-----------+
| 2012 |
| 1980 |
+-----------+
2 rows in set (0.00 sec)
mysql> insert into t3 values (2019),(80);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t3;
+-----------+
| born_year |
+-----------+
| 2012 |
| 1980 |
| 2019 |
| 1980 |
+-----------+
4 rows in set (0.00 sec)
mysql>
2、约束
表完整性约束
作用：用于保证数据的完整性和一致性
约束条件 说明
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录，不可以为空 UNIQUE + NOT NULL
FOREIGN KEY (FK) 标识该字段为该表的外键，实现表与表（父表主键/子表1外键/子表2外键）之间的关联
NULL 标识是否允许为空，默认为NULL。
NOT NULL 标识该字段不能为空，可以修改。
UNIQUE KEY (UK) 标识该字段的值是唯一的，可以为空，一个表中可以有多个UNIQUE KEY
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT 为该字段设置默认值
UNSIGNED 无符号，正数
1.主键
每张表里只能有一个主键，不能为空，而且唯一。
定义两种方式：
mysql> create table t7(hostname char(20) primary key,ip char(150));
mysql> create table t9(hostname char(20),ip char(150),primary key(hostname));
删除主键
mysql> alter table t7 drop primary key;
index(key)每张表可以有很多列做index，必须起名
面试题:
导致SQL执行慢的原因：
1.硬件问题。如网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等。
2.没有索引或者索引失效.
3.数据过多（分库分表）
4.服务器调优及各个参数设置（调整my.cnf）
索引：当查询速度过慢可以通过建立优化查询速度，可以当作调优
创建索引：两种
mysql> create table t100(hostname char(20) primary key,ip char(150),index (ip));
mysql> create table t101(hostname char(20) primary key,ip char(150),index dizhi(ip));
#给ip做的索引，名字叫dizhi
auto_increment--------自增 （每张表只能有一个字段为自曾） （成了key才可以自动增长）
mysql> CREATE TABLE department3 (
-> dept_id INT PRIMARY KEY AUTO_INCREMENT,
-> dept_name VARCHAR(30),
-> comment VARCHAR(50)
-> );
设置唯一约束 UNIQUE
mysql> CREATE TABLE department2 (
-> dept_id INT,
-> dept_name VARCHAR(30) UNIQUE,
-> comment VARCHAR(50)
-> );
插入数据的时候id和comment字段相同可以插入数据，如果name不唯一,插入数据失败。
1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male' #只能选择maie和female，不允许为空，默认是male
age int unsigned NOT NULL default 20 #必须为正值（无符号） 不允许为空 默认是20
指定字符集:
修改字符集 ：在创建表的最后面指定一下： default charset=utf8 #可以指定中文
注意
如果报错进入server端服务器登陆mysql执行:
mysql> use mysql
mysql> update user set host = '%' where user = 'root';
mysql> flush privileges;
example(详解)：
默认约束default ,和not null
mysql> create database test;
mysql> create table test.t1(
-> id int not null,
-> name varchar(50) not null,
-> sex enum('m','f') default 'm' not null,
-> age int unsigned default 18 not null,
-> hobby set('music','disc','dance','book') default 'book'
-> );
mysql> desc test.t1;
+-------+------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(50) | NO | | NULL | |
| sex | enum('m','f') | NO | | m | |
| age | int(10) unsigned | NO | | 18 | |
| hobby | set('music','disc','dance','book') | YES | | book | |
+-------+------------------------------------+------+-----+---------+-------+
mysql> insert into test.t1 values(1,'jack','m',20,'book');
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> insert into t1 values(2,NULL,'m',19,'book');
ERROR 1048 (23000): Column 'name' cannot be null
设置唯一约束 unique
mysql> create table t2(
-> id int,
-> name varchar(30) unique,
-> comment varchar(50)
-> );
建议使用下面这种方式设置约束:
mysql> create table t2(
-> id int,
-> name varchar(30),
-> comment varchar(50),
-> unique(name)
-> );
mysql> desc t2;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(30) | YES | UNI | NULL | |
| comment | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
设置主键约束 PRIMARY KEY
primary key 字段的值是不允许重复，且不允许不 NULL（UNIQUE + NOT NULL）
单列做主键
多列做主键（复合主键）
单列主键：
mysql> create table t3(
-> id int auto_increment,
-> name varchar(30) not null,
-> sex enum('male','female') not null default 'male',
-> age int not null,
-> primary key(id)
-> );
mysql> desc t3;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | NO | | NULL | |
+-------+-----------------------+------+-----+---------+----------------+
mysql> insert into t3(name,sex,age) values
-> ('tom','male',19),
-> ('jack','male',20);
mysql> insert into t3(name,age) values ('hiry',19);
mysql> select * from t3;
+----+------+------+-----+
| id | name | sex | age |
+----+------+------+-----+
| 1 | tom | male | 19 |
| 2 | jack | male | 20 |
| 3 | hiry | male | 19 |
+----+------+------+-----+
复合主键
可能单一主键无法达到唯一的要求，这时候就可以设置符合主键
mysql> create table t4(
-> host_ip varchar(15) not null,
-> service_name varchar(10) not null,
-> port int(5) not null,
-> allow enum('y','n') default 'n',
-> primary key(host_ip,port)
-> );
mysql> desc t4;
+--------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| host_ip | varchar(15) | NO | PRI | NULL | |
| service_name | varchar(10) | NO | | NULL | |
| port | varchar(5) | NO | PRI | NULL | |
| allow | enum('y','n') | YES | | n | |
+--------------+---------------+------+-----+---------+-------+
mysql> insert into t4 values
-> ('10.3.134.2','ftp',20,'y'),
-> ('10.3.134.3','http',80,'y');
mysql> select * from t4;
+------------+--------------+------+-------+
| host_ip | service_name | port | allow |
+------------+--------------+------+-------+
| 10.3.134.2 | ftp | 20 | y |
| 10.3.134.3 | http | 80 | y |
+------------+--------------+------+-------+
外键约束
设置外键约束 foreign key
父表 company.employees
mysql> create table t5(
-> name varchar(50),
-> mail varchar(20),
-> primary key(name)
-> )engine=innodb default charset=utf8;
子表 company.payroll
mysql> create table t6(
-> id int auto_increment,
-> name varchar(50) not null,
-> payroll float(10,2) not null,
-> primary key(id),
-> foreign key(name) references t5(name) on update cascade on delete cascaade
-> )engine=innodb default charset=utf8;
#子表 name 外键，关联父表（t5 主键name），同步更新，同步删除
#两表name字段必须一致，表引擎和字符集也需一致
#删除表时，需要先删除外键表
mysql> desc t5;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(50) | NO | PRI | NULL | |
| mail | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> desc t6;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | MUL | NULL | |
| payroll | float(10,2) | NO | | NULL | |
+---------+-------------+------+-----+---------+----------------+
mysql> insert into t5 values('tom','123@163.com');
mysql> insert into t6(name,payroll) values('tom',11000);
mysql> select * from t5;
+------+-------------+
| name | mail |
+------+-------------+
| tom | 123@163.com |
+------+-------------+
mysql> select * from t6;
+----+------+----------+
| id | name | payroll |
+----+------+----------+
| 1 | tom | 11000.00 |
+----+------+----------+
mysql> update t5 set name='jack' where name='tom';
mysql> select * from t6;
+----+------+----------+
| id | name | payroll |
+----+------+----------+
| 1 | jack | 11000.00 |
+----+------+----------+
mysql> select * from t5;
+------+-------------+
| name | mail |
+------+-------------+
| jack | 123@163.com |
+------+-------------+
mysql> delete from t5 where name='jack';
mysql> select * from t5;
Empty set (0.00 sec)
mysql> select * from t6;
Empty set (0.00 sec)
结论
当父表中某个员工的记录修改时，子表也会同步修改
当父表中删除某个员工的记录，子表也会同步删除
3.查看修改字符集
查看当前安装的 MySQL 所支持的字符集。
mysql> show charset;
查看 MySQL 数据库服务器和数据库字符集。
mysql> show variables like '%char%';
+--------------------------------------+----------------------------+
| Variable_name | Value |
+--------------------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| validate_password_special_char_count | 1 |
+--------------------------------------+----------------------------+
修改表和字段的字符集
//修改数据库
mysql> alter database name character set utf8;
//修改表
alter table 表名 convert to character set gbk;
//修改字段
alter table 表名 modify column '字段名' varchar(30) character set gbk not null;
//添加表字段
alter table 表名 add column '字段名' varchar (20) character set gbk;
注:执行命令过程中字段名不加引号
