约束条件
-
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 最小值
sum 求和
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)
1253
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
