mysql——入门2

最新推荐文章于 2023-05-29 16:33:58 发布

vy#dyb

最新推荐文章于 2023-05-29 16:33:58 发布

阅读量120

点赞数

分类专栏：笔记

本文链接：https://blog.csdn.net/qq_44966948/article/details/100020232

版权

笔记专栏收录该内容

4 篇文章 0 订阅

订阅专栏

字符集

要求在一次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原则。

$\sada$

.assets/文本类型.png)]

枚举

enum(‘枚举名’，‘枚举名’，‘枚举名’，…)
- 枚举名对应枚举值，采用数字计数法。从 1 开始。1 2 3 4 5 6 … 65535
优点：
1. 节省空间
2. 运算速度快，效率高
3. 限制用户输入，保证数据完整性。

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;
```
主键的作用：
1. 保证数据的完整性。
2. 加快查询速度。（跟 index有关）
如何选择主键：
1. 尽量选择最少的字段做主键
2. 尽量选择改动较少的字段做主键
3. 尽量选择数字类型做主键。

唯一键

唯一，可以为空。一张表可以有多个唯一键。

创建表指定唯一键

方法一：

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：
- 主键和主键建立关系。一夫一妻。账号 —— 密码
多对多: 非主键和非主键建立关系。 —— 【错】！！！
- 需要第三张表。描述实体间关系。体检科室 —— 体检人员。公交车 —— 乘客。

vy#dyb

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql——入门2

字符集要求在一次SQL查询过程中，至少保证 character_set_client、character_set_database、character_set_connection、character_set_results 字符集一致。才不会出现中文乱码。echo $LANG 查看 Linux 系统使用的字符集推荐使用 utf8 字符编码。show varia...
复制链接

扫一扫

专栏目录