字符集
-
要求在一次SQL查询过程中,至少 保证 character_set_client、character_set_database、character_set_connection、character_set_results 字符集一致。 才不会出现中文乱码。
-
echo $LANG 查看 Linux 系统使用的字符集
-
推荐使用 utf8 字符编码。
-
show variables like ‘char%’ 查看 MySQL系统使用的字符集。
-
set names utf8: character_set_client、character_set_connection、character_set_results
-
set character_set_server = utf8 修改 character_set_database 字符集。
-
以上均为临时设置,数据库重启时效。 保证永久有效,修改MySQL 配置文件。/etc/mysql/msyql.conf.d
【结论】:创建数据库时,确认使用的字符编码。创建表时,也指定字符编码。
补充:一些关于查看和修改字符集的MySQL知识:
查看mysql的字符集:show variables where Variable_name like ‘%char%’;
查看某一个数据库字符集:show create database enterprises;(注:enterprises为数据库)
查看某一个数据表字符集:show create table employees;(注:employees为数据表)
修改mysql的字符集:
mysql> set character_set_client=utf8;
mysql> set character_set_connection=utf8;
mysql> set character_set_database=utf8;
mysql> set character_set_results=utf8;
mysql> set character_set_server=utf8;
mysql> set character_set_system=utf8;
mysql> set collation_connection=utf8;
mysql> set collation_database=utf8;
mysql> set collation_server=utf8;
修改数据库enterprises的字符集:
alter database enterprises character set utf8
修改数据表employees的字符集:
alter table employees character set utf8
修改字段的字符集
alter table employees change name name char(10) character set utf-8;
列类型
整型
tinyint | 1 | -128 ~ 127 | 0-255 |
---|---|---|---|
smallint | 2 | -32768~32767 | 0~65535 |
int | 4 | -2^31 - 2^31-1 | 0- 2^32 -1 |
mysql> create table t1(
-> age tinyint unsigned,
-> num smallint unsigned,
-> sal bigint unsigned
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> desc t1;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| age | tinyint(3) unsigned | YES | | NULL | |
| num | smallint(5) unsigned | YES | | NULL | |
| sal | bigint(20) unsigned | YES | | NULL | |
+-------+----------------------+------+-----+---------+-------+
-
显示宽度:
int(5):代表 插入数据的 显示宽度。
- 不足 5 位数时:默认 按实际位数显示。 添加 zerofill 填充 前导 0
- 超过 5 位数时:按数据值,原样输出。
显示宽度 和 数据类型的 上限值 不是一个概念。
mysql> create table t2(
-> id int(5),
-> age int(5) zerofill
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t2 values(123, 1234);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t2;
+------+-------+
| id | age |
+------+-------+
| 123 | 01234 |
+------+-------+
1 row in set (0.00 sec)
mysql> insert into t2 values(123456, 123456);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t2;
+--------+--------+
| id | age |
+--------+--------+
| 123 | 01234 |
| 123456 | 123456 |
+--------+--------+
2 rows in set (0.00 sec)
浮点型
- float(总位数,小数位数)-3.4E+38~3.4E+38
- 小数部分(精度部分)超出定义范围。4舍5入。
- 整数部分(总位数-小数位数)超出定义范围。报错。
- double(总位数,小数位数)-1.8E+308~1.8E+308
- 小数部分(精度部分)超出定义范围。4舍5入。
- 整数部分(总位数-小数位数)超出定义范围。报错。
浮点数,是会有精度丢失的。如果不能容忍,改用 定点数存储。
mysql> create table t4(num2 double(20,19) );
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> desc t4;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| num2 | double(20,19) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into t4 values(1.1234567890123456789);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t4;
+-----------------------+
| num2 |
+-----------------------+
| 1.1234567890123457000 |
+-----------------------+
1 row in set (0.00 sec)
定点数
- decimal(总位数,小数位数) 没有取值范围。
- 特性,将 整数位 和 小数位 分开存储,以保证数据的完整性。 额外消耗存储空间。
mysql> create table t5(num2 decimal(20,19) );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t5 values(1.1234567890123456789);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t5;
+-----------------------+
| num2 |
+-----------------------+
| 1.1234567890123456789 |
+-----------------------+
字符串/文本类型
-
char : 定长字符串
- 创建表时,指定的 字符串空间,在实际存储时,如果有盈余,不回收。 执行效率高,浪费存储。
- char(10):10 代表存储的 最大字符数。 实际存储,不能超过该值。
- 取值范围绝对最大上限。
-
varchar:变长字符串类型
- 创建表时,指定的 字符串空间,在实际存储时,如果有盈余,回收剩余空间。节省存储。效率低。
- varchar(10):10 代表存储的 最大字符数。 实际存储,不能超过该值。
- varchar类型的取值范围受:
- varchar 本身 取值范围 65535 字节。
- 字符编码影响: utf8(一个字符 3 字节) gbk(一个字符 2 字节)
- MySQL数据库中的一条记录。不允许超过 65535
-
文本类型:
tinytext、text、mediumtext、longtext。
实际存储时,文本类型的字段中,存储的 是 实际文本的 地址值。因此不会打破 MySQL数据库中的一条记录。不允许超过 65535原则。
.assets/文本类型.png)]
枚举
- enum(‘枚举名’,‘枚举名’,‘枚举名’,…)
- 枚举名对应 枚举值,采用数字计数法。从 1 开始。1 2 3 4 5 6 … 65535
- 优点:
- 节省空间
- 运算速度快,效率高
- 限制用户输入,保证数据完整性。
mysql> create table t8( name varchar(20), sex enum('male', 'female', 'unknown'));
mysql> select sex+0 from t8;
+-------+
| sex+0 |
+-------+
| 1 |
| 3 |
+-------+
2 rows in set (0.00 sec)
枚举的取值范围 为 :2 ^ 16 = 65536. 枚举的个数 65535
集合
set(‘列举名’,‘列举名’,‘列举名’,…)
- 列举名对应 列举值,采用数字计数法。从 2^0 开始。 2^1 2^2 2^3 2^4 …
- 多个 列举项,作为一个 字段值存入。保存在 一个 ‘ ’ 中
优点:
- 节省空间
- 运算速度快,效率高
- 限制用户输入,保证数据完整性。
mysql> create table t9(
-> id int,
-> hobby set('抽烟','喝酒','烫头','开车')
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc t9;
+-------+------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| hobby | set('抽烟','喝酒','烫头','开车') | YES | | NULL | |
+-------+------------------------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select hobby+0 from t9;
+---------+
| hobby+0 |
+---------+
| 9 |
| 11 |
| 1 |
+---------+
3 rows in set (0.00 sec)
日期和时间
- datetime
- 年-月-日 时:分:秒。 年:1~9999
- MySQL系统,自动识别闰年。
- 日期和时间采用 字符串 表现形式。‘ ’
- date
- 年-月-日 年:1~9999
- timestap:时间戳
- 字段采用时间戳计时, default 值为 当前时间。
- 取值范围:1970-1-1 00:00:00 ~ 2038-1-19 11:14:07
- time:
- 时:分:秒. 小时部分可以超出 24.
- 取值范围:-838:59:59~838:59:59
- 按天插入 ‘10 10:24:59’ —— 250:24:59
列属性
not null、default、comment 也是列属性。
自动增长
特性:
1. 计数从 1, 每次自动增1。
- 使用 无符号 整型。
- 自动增长 auto_increment 列,必须是 primary key主键。 反过来,主键,不一定是自动增长。
- 具备自动增长属性的列,插入 null 值,自动填充自动增长值。(显示插入)
- 删除的自动增长值,不会被重复使用。
主键
不能重复,不能为空(not null)。一张表,只能有一个主键。
创建表指定主键
方法一:
create table 表名 (
字段名1 字段类型 primary key,
字段名2 字段类型,
....
);
方法二:
create table 表名 (
字段名1 字段类型,
字段名2 字段类型,
....,
primary key(字段名1)
);
组合主键:
create table 表名 (
字段名1 字段类型,
字段名2 字段类型,
....,
primary key(字段名1, 字段名2)
);
修改表指定主键
方法1:
create table t19( id int, name varchar(20) );
alter table t19 add primary key(id) -- 添加主键属性
方法2:
create table t19( id int, name varchar(20) );
alter table t19 change id id int auto_increment primary key; -- 添加自动增长、主键属性
删除主键
-
删除带有自动增长属性的 主键 。
-
去除自动增长属性
alter table 表名 change 字段名 字段名 类型名; -- 去除 auto_increment 属性
-
删除主键
alter table 表名 drop primary key;
-
-
删除没有自动增长属性的 主键。
alter table 表名 drop primary key;
-
主键的作用:
- 保证数据的完整性。
- 加快查询速度。(跟 index有关)
-
如何选择主键:
- 尽量选择 最少的字段做主键
- 尽量选择 改动较少的 字段做主键
- 尽量选择 数字类型做主键。
唯一键
唯一,可以为空。 一张表可以有多个唯一键。
创建表指定唯一键
方法一:
create table 表名 (
字段名1 字段类型 unique,
字段名2 字段类型,
....
);
方法2:
create table 表名 (
字段名1 字段类型,
字段名2 字段类型,
....
unique(字段名1),
unique(字段名2),
....
);
修改表指定唯一键
create table 表名( id int, name varchar(20) );
alter table 表名 add unique(字段名1), add unique(字段名2) -- 给多个字段添加唯一键
获取唯一键键名
show create table 表名;
| t22 | CREATE TABLE `t22` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
UNIQUE KEY `id` (`id`), -- `id` 唯一键键名
UNIQUE KEY `email` (`email`),
UNIQUE KEY `UQ_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
自定义唯一键键名
alter table 表名 add unique 唯一键键名(字段名1)
alter table t22 add unique UQ_name(name)
删除唯一键
alter table 表名 drop index 唯一键键名 -- index 关键字 不能省略。
对比主键、唯一键:
主键: 非空、唯一、一张表只有一个。
唯一键:唯一、可以null、一张表可以有多个。
SQL注释:
- 单行注释:
- #:
- –空格:
- 多行注释: /* */
外键约束
数据完整性:
实体完整型。域完整性。引用完整性。
引用完整性:
借助外键约束(主表、从表),保证数据的引用完整性。
1. 主表中没有的数据,从表不允许插入。
- 从表中包含的数据,主表不允许删除。
3. 不能修改主表中的 数据,导致从表数据孤立存在。
4. 先删从表,再删主表。
创建外键约束
- 创建表指定外键约束
mysql> create table stuinfo(
-> stuno char(4) primary key,
-> name varchar(20) not null
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> create table stumarks(
-> stuid char(4) primary key,
-> score tinyint unsigned
-> foreign key(从表公共字段名) references 主表名(主表公共字段名)
-> );
- 修改表指定外键约束
mysql> create table stuinfo(
-> stuno char(4) primary key,
-> name varchar(20) not null
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> create table stumarks(
-> stuid char(4) primary key,
-> score tinyint unsigned
-> );
Query OK, 0 rows affected (0.03 sec)
# 添加外键约束
alter table 从表名 add foreign key (从表公共字段名) references 主表名(主表公共字段名);
alter table stumarks add foreign key(stuid) references stuinfo(stuno);
删除外键
-
获取外键名:
show create table stumarks; | stumarks | CREATE TABLE `stumarks` ( `stuid` int(5) NOT NULL, `score` int(5) DEFAULT NULL, PRIMARY KEY (`stuid`), CONSTRAINT `stumarks_ibfk_1` FOREIGN KEY (`stuid`) REFERENCES `stuinfo` (`stuno`) -- `stumarks_ibfk_1` 外键名 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
-
删除外键
语法: alter table 表名 drop foreign key 外键名
alter table stumarks drop foreign key `stumarks_ibfk_1`;
特别注意:
外键约束,需要使用 Innodb 引擎。 myisam 引擎的表,不支持外键约束。
数据库设计:
实体间关系
-
1对多:
- 主键 和 非主键建立关系。 一父多子。歌星 —— 专辑。
-
1对1:
- 主键 和 主键建立关系。 一夫一妻。 账号 —— 密码
-
多对多: 非主键 和 非主键建立关系。 —— 【错】!!!
- 需要第三张表。描述实体间关系。 体检科室 —— 体检人员。 公交车 —— 乘客。