MySql教程

MySQL约束条件及外键的关系

本文主要是介绍MySQL约束条件及外键的关系,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

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约束条件及外键的关系的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!