MySQL约束条件及外键的关系

2022-10-25 13:34:37

MySQL约束条件及外键的关系

约束条件

unsigned

设置无符号， 针对整形，这样一设置比如说 tinyint本来是(-127--128),设置之后存储范围就变成了255

mysql> create table t1 (id int unsigned);
Query OK, 0 rows affected (0.07 sec)

mysql> desc t1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int unsigned | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> insert into t1 values(-1);  # 这里不能插入负数
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)

zerofill

用0来填充

mysql> create table t1 (id int(10) zerofill);# 注意这里的10不是指存储范围或则长度， 整形的存储范围之和他的类型名有关系，10代表的是最大显示宽度
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> insert into t1 values (11);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------------+
| id         |
+------------+
| 0000000011 |
+------------+
1 row in set (0.00 sec)

mysql> desc t1;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id    | int(10) unsigned zerofill | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)


int(M) M指示最大显示宽度。最大有效显示宽度是255。显示宽度与存储大小或类型包含的值的范围无关

not null

非空

mysql> create table t1 (id int, name varchar(16));
Query OK, 0 rows affected (0.04 sec)

mysql> create table t2 (id int, name varchar(16) not null);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t1 values (1, 'egon');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1(id) values (1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1(id, name) values (1, ''); # 注意这里的 '' 和 空是不一样的
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | egon |
|    1 | NULL |
|    1 |      |
+------+------+
3 rows in set (0.00 sec)

mysql> insert into t2(id, name) values(1, 'egon');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2(id, name) values(1, '');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2(id) values(1);  # 报错，因为给name这个字段设置了非空

mysql> select * from t2;
+------+------+
| id   | name |
+------+------+
|    1 | egon |
|    1 |      |
+------+------+
2 rows in set (0.00 sec)

mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(16) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

 # 注意这里的 '' 和 空是不一样的

default

设置默认值

mysql> create table t1 (id int, name varchar(32) default 'egon');
Query OK, 0 rows affected (0.04 sec)

mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(32) | YES  |     | egon    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

默认值可传可不传

unique

唯一：
	单列唯一：
        mysql> create table t1 (id int, name varchar(32) unique);
        Query OK, 0 rows affected (0.06 sec)

        mysql> desc t1;
        +-------+-------------+------+-----+---------+-------+
        | Field | Type        | Null | Key | Default | Extra |
        +-------+-------------+------+-----+---------+-------+
        | id    | int         | YES  |     | NULL    |       |
        | name  | varchar(32) | YES  | UNI | NULL    |       |
        +-------+-------------+------+-----+---------+-------+
        2 rows in set (0.00 sec)

        mysql> insert into t1 values(1, 'qwe');
        Query OK, 1 row affected (0.01 sec)

        mysql> insert into t1 values(1, 'qwe');  # qwe上面已经传过一次了；
        ERROR 1062 (23000): Duplicate entry 'qwe' for key 't1.name'
        mysql>	
    多列唯一：
    	    mysql> create table t1 (id int, host varchar(16), port int, unique(host, port));
    	    # 这个就是host和port加起来唯一，单个可以重复
            Query OK, 0 rows affected (0.07 sec)

            mysql> insert into t1 values (1, '127.0.0.1', 3306);
            Query OK, 1 row affected (0.01 sec)

            mysql> insert into t1 values (1, '127.0.0.1', 3306);
            ERROR 1062 (23000): Duplicate entry '127.0.0.1-3306' for key 't1.host'
            mysql> insert into t1 values (1, '127.0.0.2', 3306);
            Query OK, 1 row affected (0.01 sec)

            mysql> insert into t1 values (1, '127.0.0.2', 3302);
            Query OK, 1 row affected (0.00 sec)

primary key

# 1. 从限制角度来说， 主键相当于非空且唯一
	id int primary key <==> id int not null unique
# 2. InnoDB存储引擎规定表中必须要有一个主键
	在之前创建的表中，主键给我们隐藏了，隐藏意味着看不到，也不能用
# 主键的功能
	查询速度快， 主键本身也是一种索引

auto_increment

每次自增1

create table t1 (id int primary key auto_increment)  # id字段 创建的标准语法

foreign key
```
这个是外键关系
```

清空表数据

# 第一种：
	delete from t1;  # 删除了，下次插入数据，主键不是从1开始
# 第二种
	truncate table t1;  # 推荐

binlog  恢复数据用的，，我们写的sql语句和数据都在这个里面

外键关系的判断

表呢有三种关系：
	一对一
    一对多
    多对多
    
###############一对一##################
eg: 
    作者表和作者详情表
    一个作者对应一个作者详情
    一个作者详情对应一个作者
    # 这样的关系就是一对一， 建立外键的时候，推荐建立在查询频率高的一方

###############一对多##################
eg:
    书籍表和出版社表
    一个书籍只能有一个出版社
    一个出版社可以有多个书籍
    # 这样的关系就是一对多，外键建立在多的一方
  
###############多对多##################
 eg:
    作者表和书籍表
    一个作者可以写多本书
    一本书可以有多个作者
    # 这样的关系就是多对多， 这个建立一张第三方的关系表

代码实现上述需求

###############一对一##################

create table author (
	id int primary key auto_increment,
    name varchar(16),
    author_detail_id int unique,
    foreign key (author_detail_id) references author_detail(id)
    on update cascade # 级联更新
    on delete cascade # 级联删除
)

create table author_detail(
	id int primary key auto_increment,
    phone bigint,
    email varchar(16),
    gender enum('male', 'female') default 'male'
)

###############一对多##################
 
create table book(
    id int primary key auto_increment,
	title varchar(16),
    price decimal,
    publish_id int,
    foreign key (publish_id) references publish(id)
    on update cascade
    on delete cascade
)

create table publish(
	id int primary key auto_increment,
    name varchar(16),
    addr varchar(16)
)

###############对多对##################
    
create table author(
	id int primary key auto_increment,
    name varchar(16)
)

create table book(
	id int primary key auto_increment,
    title varchar(16),
    price decimal
)

create table author_book(
	id int primary key auto_increment,
    book_id int,
    author_id int,
    foreign key (book_id) references author(id)
    on update cascade
    on delete cascade,
    
    foreign key (author_id) references book(id)
    on update cascade
    on delete cascade
)

聚合函数

max  最大值
min  最小值
sun  求和
count 计数
avg   平均值

模糊查询--like

like

关键符合:
    %: 匹配任意个数的任意字符
    _: 匹配单个个数的任意字符
    # 注意： 模糊查询第一个%不走索引，如果想要走索引就把数据同步到esc(elasticsearch)中

查询前期准备数据

create table emp(
  id int primary key auto_increment,
  name varchar(20) not null,
  sex enum('male','female') not null default 'male', #大部分是男的
  age int(3) unsigned not null default 28,
  hire_date date not null,
  post varchar(50),
  post_comment varchar(100),
  salary double(15,2),
  office int, #一个部门一个屋子
  depart_id int
);

#插入记录
#三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);

查询关键字之where

# 1. 查询id大于等于3小于等于6的数据
	select * from emp where id >=3 and id <= 6;
    select * from emp where id between 3 and 6;
    """
    between and 在什么之间
    """
# 2.查询薪资是20000或者18000或者17000的数据
	select * from emp where salary =20000 or salary =18000 or salary = 17000;
     select * from emp where salary in (20000, 17000, 18000);
     """
     in 后面跟一个集合 在这个集合里面
     """
# 3. 查询姓名中带有字母o的员工姓名和薪资
	select name, salary from emp where name like '%o%';\
    
# 4. 查询姓名由四个字符组成的员工姓名和薪资
	select * from emp where name like '____';
    select * from emp where char_length(name) = 4;
    """
    char_length()  # 计算长度
    """
# 5.查询id小于3或者大于6的数据
	select * from emp where id < 3 or id > 6;
    select * from emp where not id between 3 and 6;
    """
    not 取反
    """
# 6.查询薪资不在20000，18000，17000范围的数据
	select * from emp where salary not in (20000, 18000, 17000);
    """
    not in 不走索引
    """
# 7.查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is
	 select name, post from emp where post_comment is null;
    """
    针对null不能用等号，只能用is
    """

查询关键字之group by 分组

分组：

将单个单个的个体按照指定的条件分成一个整体

分组之后默认只能直接取到分组的依据，其他字段无法直接获取(可以间接获取)

# 严格模式
	set global sql_mode='STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH,only_full_group_by'

# 1. 每个部门的最高薪资
	select post, max(salary) from emp  group by post;
# 2.每个部门的最低薪资
	select post, min(salary) from emp group by post;
# 3. 每个部门的平均薪资
	select post, avg(salary) from emp group by post;
# 4.每个部门的人数
	 select post, count(salary) from emp group by post;
# 5.每个部门的月工资总和
	select post, sum(salary) from emp group by post;
    
    
# as 可以给字段起别名
	select post, sum(salary) as sum_salary from emp group by post;
    
# 6. 查询分组之后的部门名称和每个部门下所有的员工姓名
	"""
	group_concat()   获取分组以外的字段数据  并且支持拼接操作
	concat()   未分组之前使用的拼接功能
	concat_ws()  
	"""
    
mysql> select group_concat(name, ':', salary) from emp group by post;
+--------------------------------------------------------------------------------------------------+
| group_concat(name, ':', salary)                                                                  |
+--------------------------------------------------------------------------------------------------+
| 僧龙:10000.13,程咬金:20000.00,程咬银:19000.00,程咬铜:18000.00,程咬铁:17000.00                    |
| 哈哈:3000.13,呵呵:2000.35,西西:1000.37,乐乐:3000.29,拉拉:4000.33                                 |
| tom:1000000.31,kevin:8300.00,tony:3500.00,owen:2100.00,jack:9000.00,jenny:30000.00,sank:10000.00 |
+--------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> select concat(name, ':', salary) from emp where id <3;;
+---------------------------+
| concat(name, ':', salary) |
+---------------------------+
| tom:1000000.31            |
| kevin:8300.00             |
+---------------------------+
2 rows in set (0.00 sec)

ERROR:
No query specified

mysql> select concat_ws(':', name, salary, post) from emp;
+------------------------------------+
| concat_ws(':', name, salary, post) |
+------------------------------------+
| tom:1000000.31:teacher             |
| kevin:8300.00:teacher              |
| tony:3500.00:teacher               |
| owen:2100.00:teacher               |
| jack:9000.00:teacher               |
| jenny:30000.00:teacher             |
| sank:10000.00:teacher              |
| 哈哈:3000.13:sale                  |
| 呵呵:2000.35:sale                  |
| 西西:1000.37:sale                  |
| 乐乐:3000.29:sale                  |
| 拉拉:4000.33:sale                  |
| 僧龙:10000.13:operation            |
| 程咬金:20000.00:operation          |
| 程咬银:19000.00:operation          |
| 程咬铜:18000.00:operation          |
| 程咬铁:17000.00:operation          |
+------------------------------------+
17 rows in set (0.00 sec)

码农公寓

MySQL约束条件及外键的关系

约束条件

清空表数据

外键关系的判断

代码实现上述需求

聚合函数

模糊查询--like

查询前期准备数据

查询关键字之where

查询关键字之group by 分组

相关文章