[MySQL]三、MySQL字符集、字段类型和限制条件

最新推荐文章于 2023-10-24 20:56:18 发布

FanMY_71

最新推荐文章于 2023-10-24 20:56:18 发布

阅读量1.3k

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_48638643/article/details/127739859

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

字符集

定义：字符集指的是某个范围字符的编码规则

比如utf8字符集对于所有中文保字采用3个字节来表示(编码) ,所以我们称utf8为-种字符集。
- 这里的范围就是指所有的中文汉字
- 编码规则就是指都采用3个字节来表示-一个汉字
比如ASCII字符集对于所有英文字母采用1个字节来表示(编码)，所以我们称ASCII为一种字符集。
- 这里的范围就是指所有的英文字母

1.1、不同字符集消耗的空间是不一样的

我们可以使用“show character set;”来查看

root@fan 20:18  mysql>show character set;
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |

| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
 # 这里只是截取了其中的一部分数据。
 # utf8_general_ci，ci表示字符在默认排序的时候不区分大小写。

1.2、查看和筛选MySQL中存在的变量

root@fan 20:25  mysql>show variables;  # 查看MySQL中存在的变量
root@fan 20:26  mysql>show variables like "%character%";
 # 筛选
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

1.2.1、如何知道某个库使用的是什么字符集？

root@fan 20:40  mysql>show create database fan;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| fan      | CREATE DATABASE `fan` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+

# method2： 看配置文件
[root@localhost ~]# cat /etc/my.cnf
[mysqld_safe]

[client]
socket=/data/mysql/mysql.sock

[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8   # 这个就是设置MySQL服务器默认字符集的
#skip-grant-tables   # 跳过密码验证

[mysql]
auto-rehash
prompt=\u@\d \R:\m  mysql>

# method3
root@fan 20:26  mysql>show variables like "%character%";
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

1.3、字符集的继承问题

文本类型的数据，会牵涉到字符集。如，varchar、char、text。

继承顺序为：服务器（我们一般不设置）==》库==》表==》列

优先级问题：若是不指定，那么表和列都继承库所使用的字符集；所以有指定，那么就是指定的优先。

1.3.1、单独设置表中不同的字符集(列的字符集不一样)

CREATE TABLE t1(
    c1 CHAR(1) CHARACTER SET latin1,
    c2 CHAR(1) CHARACTER SET ascli
)

1.3.2、设置字符集（实践其中的继承关系问题）

root@fan 20:40  mysql>create database feng default charset=utf8mb4;
Query OK, 1 row affected (0.00 sec)

root@fan 20:50  mysql>show create database feng;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| feng     | CREATE DATABASE `feng` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
root@fan 20:50  mysql>use feng;
Database changed

root@feng 20:52  mysql>create table love(name varchar(20));
Query OK, 0 rows affected (0.00 sec)

root@feng 20:52  mysql>show create table love;
+-------+-------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                    |
+-------+-------------------------------------------------------------------------------------------------+
| love  | CREATE TABLE `love` (
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |  # 我们可以发现，我们继承了我们指定的字符集
+-------+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

字段类型和限制条件

root@feng 21:09  mysql>create table student(id int primary key, name varchar(20), sex char(1));
root@feng 21:20  mysql>create table student2(id int primary key, name varchar(20) not null, sex char(1));

主键：primary key。其实就是一个字段或者多个字段，这个字段或者多个字段，不允许为空，不允许重复。为了建立索引，方便查询。

2.1、列的数据类型

2.1.1、数值类

五种整型：

每个整型类型可以指定一个最小显示宽度。这个宽度并不表示存储的值有多大

定点类型

空间是根据长度来计算的，一个数字占一个字节。

浮点类型

位类型，用的不多

2.1.2、字符串类

（1）char和varchar

对于男女性别，适合使用char；若是对于一组数据长度相差比较大，适合使用varchar。

char和varchar的区别

（2）text

（3）blob

（4）enum

枚举类型

CREATE TABLE shirts (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
desc shirts;
+-------+----------------------------------------------------+------+-----+---------+-------+
| Field | Type                                               | Null | Key | Default | Extra |
+-------+----------------------------------------------------+------+-----+---------+-------+
| name  | varchar(40)                                        | YES  |     | NULL    |       |
| size  | enum('x-small','small','medium','large','x-large') | YES  |     | NULL    |       |
+-------+----------------------------------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
  ('polo shirt','small');
  
select * from shirts;
+-------------+--------+
| name        | size   |
+-------------+--------+
| dress shirt | large  |
| t-shirt     | medium |
| polo shirt  | small  |
+-------------+--------+
3 rows in set (0.00 sec)

INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),   ('polo shirt','small'),("handsome shirt", '4xl');
ERROR 1265 (01000): Data truncated for column 'size' at row 4

规定号每种数据可选项，若是插入的选项不属于规定的，会报错

（5）set 集合

mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
mysql> INSERT INTO myset (col) VALUES 
-> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
mysql> SELECT col FROM myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+
5 rows in set (0.04 sec)

插入一个set之外的值

mysql> INSERT INTO myset (col) VALUES ('a,d,d,s');
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'col' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.04 sec)

mysql> SELECT col FROM myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+
6 rows in set (0.01 sec)

2.1.3、日期和时间

timestamp（时间戳）

消耗四个字节

root@fan 23:37  mysql>select now();
+---------------------+
| now()               |
+---------------------+
| 2022-08-05 23:37:33 |
+---------------------+
1 row in set (0.00 sec)

date的使用

由此可见，这几种方法都可以用于插入date类型。

2.1.4、统计字符长度

使用length()和char_length()

root@fan 20:51  mysql>create table t1(id int,solary decimal(15,2), name varchar(30));
root@fan 20:56  mysql>insert into t1(id,solary,name) values(12, 20000.12, "张三丰");
root@fan 21:00  mysql>insert into t1(id,solary,name) values(12, 20000.12, "fanmy");
root@fan 22:50  mysql>select id,solary,name,length(name),char_length(name),length(solary), char_length(solary) from t1;
+------+----------+-----------+--------------+-------------------+----------------+---------------------+
| id   | solary   | name      | length(name) | char_length(name) | length(solary) | char_length(solary) |
+------+----------+-----------+--------------+-------------------+----------------+---------------------+
|   12 | 20000.12 | 张三丰    |            9 |                 3 |              8 |                   8 |
|   12 | 20000.12 | fanmy     |            5 |                 5 |              8 |                   8 |
+------+----------+-----------+--------------+-------------------+----------------+---------------------+
2 rows in set (0.00 sec)

# length()是用来记录消耗字节的多少，char_length()是用来查看字符的个数

2.2、字段属性

2.2.1、自增(auto_increment)

会在前一个数字的基础上加一

root@fan 15:55  mysql>create table city_name (seqno integer unsigned auto_increment not null primary key, name varchar(30) not null);
Query OK, 0 rows affected (0.01 sec)

root@fan 15:55  mysql>desc city_name;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| seqno | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)


root@fan 15:56  mysql>insert into city_name(name) values ('changsha');
Query OK, 1 row affected (0.00 sec)

root@fan 15:56  mysql>select * from city_name;
+-------+----------+
| seqno | name     |
+-------+----------+
|     1 | changsha |
+-------+----------+
1 row in set (0.00 sec)

root@fan 15:56  mysql>insert into city_name(name) values ('beijing');
Query OK, 1 row affected (0.00 sec)

root@fan 15:57  mysql>select * from city_name;
+-------+----------+
| seqno | name     |
+-------+----------+
|     1 | changsha |
|     2 | beijing  |
+-------+----------+
2 rows in set (0.00 sec)

root@fan 15:57  mysql>insert into city_name(seqno,name) values (9,'shanghai');
Query OK, 1 row affected (0.01 sec)

root@fan 15:59  mysql>select * from city_name;
+-------+----------+
| seqno | name     |
+-------+----------+
|     1 | changsha |
|     2 | beijing  |
|     9 | shanghai |
+-------+----------+
3 rows in set (0.00 sec)

root@fan 15:59  mysql>insert into city_name(name) values ('shangdong');
Query OK, 1 row affected (0.00 sec)

root@fan 15:59  mysql>select * from city_name;
+-------+-----------+
| seqno | name      |
+-------+-----------+
|     1 | changsha  |
|     2 | beijing   |
|     9 | shanghai  |
|    10 | shangdong |
+-------+-----------+
4 rows in set (0.00 sec)

2.2.2、设置自增的步长和起始值

默认的初始值是1，步长是1

root@fan 15:59  mysql>SET @@auto_increment_offset = 10,  -- 起始值
    ->       @@auto_increment_increment=10;  -- 每次加几
Query OK, 0 rows affected (0.00 sec)
root@fan 16:28  mysql>ins ert into city_name(name) values ('hainan');
Query OK, 1 row affected (0.00 sec)

root@fan 16:30  mysql>select * from city_name;
+-------+-----------+
| seqno | name      |
+-------+-----------+
|     1 | changsha  |
|     2 | beijing   |
|     9 | shanghai  |
|    10 | shangdong |   -- 因为我们在设置MySQL的初始值10的时候，已经存在了10这个编号。若是没有存在这个编号，那么只要是前面的数值小于10，那么设置之后插入的值一定是10.
|    20 | hainan    |
+-------+-----------+
5 rows in set (0.00 sec)
root@fan 16:30  mysql>insert into city_name(name) values (@sg);
Query OK, 1 row affected (0.00 sec)

root@fan 16:32  mysql>select * from city_name;
+-------+-----------+
| seqno | name      |
+-------+-----------+
|     1 | changsha  |
|     2 | beijing   |
|     9 | shanghai  |
|    10 | shangdong |
|    20 | hainan    |
|    30 | fmy       |
+-------+-----------+
6 rows in set (0.00 sec)

2.2.2、MySQL中的变量

@@，是系统变量，MySQL内部的变量；@是自定义变量

root@fan 16:28  mysql>set @sg='fmy';
Query OK, 0 rows affected (0.00 sec)

root@fan 16:28  mysql>select @sg;
+------+
| @sg  |
+------+
| fmy  |
+------+
1 row in set (0.00 sec)

2.2.3、zerofill

填充0，自动转化为unsigned（无符号整数）；signed（有符号整数）

2.2.4、unique（唯一性）

允许为null，即null可以出现很多次。但是空值只能出现一次，字符串为空" "。

2.2.5、null和空值的区别

空值，即单引号或者双引号力没有任何内容。但是null值，就是什么都没有。

root@fan 16:32  mysql>create table test(id int primary key, name varchar(20) unique);
Query OK, 0 rows affected (0.01 sec)

 root@fan 16:51  mysql>desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


root@fan 16:50  mysql>insert into test(id, name) values(1,"");
Query OK, 1 row affected (0.00 sec)

root@fan 16:51  mysql>insert into test(id, name) values(2,"fan");
Query OK, 1 row affected (0.00 sec)


root@fan 16:51  mysql>insert into test(id, name) values(3,null);
Query OK, 1 row affected (0.00 sec)

root@fan 16:51  mysql>select * from test;
+----+------+
| id | name |
+----+------+
|  3 | NULL |
|  1 |      |
|  2 | fan  |
+----+------+
3 rows in set (0.00 sec)
root@fan 16:52  mysql>insert into test(id, name) values(4,'');  --验证unique只能有一个空值
ERROR 1062 (23000): Duplicate entry '' for key 'name'
root@fan 16:56  mysql>insert into test(id, name) values(4,NUll);
Query OK, 1 row affected (0.01 sec)

root@fan 16:58  mysql>select * from test;
+----+------+
| id | name |
+----+------+
|  3 | NULL |
|  4 | NULL |
|  1 |      |
|  2 | fan  |
+----+------+
4 rows in set (0.00 sec)

2.2.6、primary key(主键)

等同于not null + unique，不允许为空，也不允许重复。

主键设置方法有两种：

我们可以定义某个字段的后面加上primary key。
我们也可以在定义完字段之后，在最后面使用primary key()。

注：若是primary key() ，后面接了两个字段，那么就创建了一个联合主键

示例：

root@fan 17:08  mysql>create table pk(id int , name varchar(20) , sex char(1),primary key(id,name));
Query OK, 0 rows affected (0.01 sec)
root@fan 17:09  mysql>desc pk;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   | PRI | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2.2.7、default

root@fan 17:00  mysql>create table test2(id int primary key, name varchar(20) unique, age int default 18);
Query OK, 0 rows affected (0.01 sec)

root@fan 17:00  mysql> desc test2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
| age   | int(11)     | YES  |     | 18      |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

root@fan 17:00  mysql>insert into test2(id, name) values(1,'wangzai');
Query OK, 1 row affected (0.00 sec)

root@fan 17:00  mysql>select * from test2;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | wangzai |   18 |
+----+---------+------+
1 row in set (0.00 sec)

2.2.8、comment

起注释作用

root@fan 17:03  mysql>show create table test3;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test3 | CREATE TABLE `test3` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT '18' COMMENT '年龄',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8     |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2.2.9、外键 foregin key ⭐

A表里的主键被引用到B表里做一个字段，这个字段就是B表里的外键。

外键的好处：直接引用别的表里的字段，自己的表里就不需要再创建相同的数据了，可以避免数据的冗余。

关于外键对内存和磁盘方面的影响：我们使用外键省去了存储在磁盘中的空间，但是在查询的时候要多消耗内存的空间。建议：在实际中运用起来，能够用一张表来存储的话就都用一张表存储。避免使用外键，因为使用外键是非常消耗内存的。

示例：

创建父表

create table dept(deptid integer,
    dname varchar(20), 
    primary key(deptid)
    );
root@fan 17:40  mysql>insert into dept(deptid, dname) values(10,"市场部");
Query OK, 1 row affected (0.00 sec)

root@fan 17:41  mysql>insert into dept(deptid, dname) values(20,"销售部");
Query OK, 1 row affected (0.00 sec)

root@fan 17:41  mysql>select * from dept;
+--------+-----------+
| deptid | dname     |
+--------+-----------+
|     10 | 市场部    |
|     20 | 销售部    |
+--------+-----------+
2 rows in set (0.00 sec)

创建子表

create table emp(
    id integer,
    name varchar(20),
    deptid integer,
    primary key(id),
    foreign key(deptid) references dept(deptid)
    );
Query OK, 0 rows affected (0.01 sec)
root@fan 17:41  mysql>insert into emp(id, name, deptid) values(1,'ftt',10);
Query OK, 1 row affected (0.00 sec)

root@fan 17:43  mysql>insert into emp(id, name, deptid) values(2,'fyy',20);
Query OK, 1 row affected (0.01 sec)

root@fan 17:43  mysql>select * from emp;
+----+------+--------+
| id | name | deptid |
+----+------+--------+
|  1 | ftt  |     10 |
|  2 | fyy  |     20 |
+----+------+--------+
2 rows in set (0.00 sec)

多表连接查询

# 新语法
root@fan 17:45  mysql>select id, name, deptid, dname from emp join dept using(deptid);
+----+------+--------+-----------+
| id | name | deptid | dname     |
+----+------+--------+-----------+
|  1 | ftt  |     10 | 市场部    |
|  2 | fyy  |     20 | 销售部    |
+----+------+--------+-----------+
2 rows in set (0.00 sec)

# 旧语法
root@fan 17:53  mysql>select id, name, emp.deptid, dname from emp, dept where emp.deptid=dept.deptid;
+----+------+--------+-----------+
| id | name | deptid | dname     |
+----+------+--------+-----------+
|  1 | ftt  |     10 | 市场部    |
|  2 | fyy  |     20 | 销售部    |
+----+------+--------+-----------+
2 rows in set (0.00 sec)

另外若是删外键的话，我们需要连续删除很多东西。那么我们可以在建立表的时候，就设置on delete cascade (级联删除：当删除父表中的行时，如果子表中有依赖于被删除父表的子行存在，那么连同子行一起删除)或者on delete set null (当删除父表中的行时，如果子表中有依赖于被删除父表的子行存在，那么将子行的外键列设为null)

2.3、根据已有的表来创建新表

语法1：CREATE TABLE new_tbl LIKE orig_tbl;

root@fan 13:32  mysql>create table test.pk like fan.pk;
Query OK, 0 rows affected (0.02 sec)

root@fan 13:33  mysql>use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
root@test 13:33  mysql>show tables;
+----------------+
| Tables_in_test |
+----------------+
| pk             |
+----------------+
1 row in set (0.00 sec)

root@test 13:33  mysql>desc pk;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   | PRI | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

root@test 13:33  mysql>select * from pk;  --这样复制过来的表，只是拥有表结构，没有复制表的内容
Empty set (0.00 sec)

语法2：

root@test 13:34  mysql>create table dept as select deptid,dname from fan.dept;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@test 13:38  mysql>select * from dept;  -- 这样复制过来，会复制之前表的所有内容。
+--------+-----------+
| deptid | dname     |
+--------+-----------+
|     10 | 市场部    |
|     20 | 销售部    |
+--------+-----------+