修改表字段
create table t1(id int);
desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
# add默认从最后一个字段增加
alter table t1 add name varchar(5) not null, add age int not null;
desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(5) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
# add + first在字段开头增加
alter table t1 add haha tinyint first;
desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| haha | tinyint(4) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | varchar(5) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
# add + after指定字段进行插入
alter table t1 add y varchar(3) after id;
desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| haha | tinyint(4) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| y | varchar(3) | YES | | NULL | |
| name | varchar(5) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
# alter + drop指定字段进行删除
alter table t1 drop y;
desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| haha | tinyint(4) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | varchar(5) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
# alter + modify指定字段进行属性修改
alter table t1 modify name varchar(10);
desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| haha | tinyint(4) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | int(11) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
# alter + change 修改表的字段名
alter table t1 change name new_name varchar(10) not null;
desc t1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| haha | tinyint(4) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| new_name | varchar(10) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
复制表
# \G标准输出(将字段按照每一行显示)
select * from mysql.user\G
# select默认会将输出结果拼接成一个表格形式
select host,user,password from mysql.user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| db01 | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| db01 | | |
+-----------+------+-------------------------------------------+
# 复制表+表数据(创建表,将表mysql.user的输出结果打印到t2表中)
create table t2 select host,user,password from mysql.user;
desc t2;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| host | char(60) | NO | | | |
| user | char(16) | NO | | | |
| password | char(41) | NO | | | |
+----------+----------+------+-----+---------+-------+
select * from t2;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| db01 | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| db01 | | |
+-----------+------+-------------------------------------------+
# 只复制表结构,where后面的条件为false,没有输出结果
select host,user,password from mysql.user where 1>10;
# create + select + where(条件为false)无结果输出
create table t3 select host,user,password from mysql.user where 1>10;
desc t3;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| host | char(60) | NO | | | |
| user | char(16) | NO | | | |
| password | char(41) | NO | | | |
+----------+----------+------+-----+---------+-------+
select * from t3;
Empty set (0.00 sec)
表中数据的增删改
# 插入数据的几种方式
create table t4(id int,name varchar(10),age int);
# 按照顺序插入数据
insert t4 values(1,"nana",18);
insert t4 values(1,"xixi",19);
# 按照字段插入数据
insert t4(name,id,age) values("haha",3,20);
# 批量插入数据
insert t4 values(2,"lala",16),(4,"dudu",19);
select * from t4;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | nana | 18 |
| 1 | xixi | 19 |
| 3 | haha | 20 |
| 2 | lala | 16 |
| 4 | dudu | 19 |
+------+------+------+
表中数据的迁移
create table test01(
id int primary key auto_increment,
name varchar(16) not null unique,
age int not null,
email varchar(16) not null,
born_year int not null,
reg_time timestamp
);
desc test01;
+-----------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(16) | NO | UNI | NULL | |
| age | int(11) | NO | | NULL | |
| email | varchar(16) | NO | | NULL | |
| born_year | int(11) | NO | | NULL | |
| reg_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-----------+-------------+------+-----+-------------------+-----------------------------+
insert into test01(name,age,email,born_year,reg_time) values
("nana",18,"12@qq.com",1999,now()),
("lala",19,"12@qq.com",1998,now()),
("haha",20,"12@qq.com",1997,now()),
("xixi",21,"12@qq.com",1993,now());
select * from test01;
+----+------+-----+-----------+-----------+---------------------+
| id | name | age | email | born_year | reg_time |
+----+------+-----+-----------+-----------+---------------------+
| 1 | nana | 18 | 12@qq.com | 1999 | 2021-07-02 10:24:25 |
| 2 | lala | 19 | 12@qq.com | 1998 | 2021-07-02 10:24:25 |
| 3 | haha | 20 | 12@qq.com | 1997 | 2021-07-02 10:24:25 |
| 4 | xixi | 21 | 12@qq.com | 1993 | 2021-07-02 10:24:25 |
+----+------+-----+-----------+-----------+---------------------+
create table test02(
id int primary key auto_increment,
name varchar(16) not null unique,
email varchar(16) not null,
reg_time timestamp
);
select id,name,email,reg_time from test01;
+----+------+-----------+---------------------+
| id | name | email | reg_time |
+----+------+-----------+---------------------+
| 1 | nana | 12@qq.com | 2021-07-02 10:24:25 |
| 2 | lala | 12@qq.com | 2021-07-02 10:24:25 |
| 3 | haha | 12@qq.com | 2021-07-02 10:24:25 |
| 4 | xixi | 12@qq.com | 2021-07-02 10:24:25 |
+----+------+-----------+---------------------+
# 指定test01表中的字段,将指定的字段数据复制到test02表中
insert test02(id,name,email,reg_time) select id,name,email,reg_time from test01;
select * from test02;
+----+------+-----------+---------------------+
| id | name | email | reg_time |
+----+------+-----------+---------------------+
| 1 | nana | 12@qq.com | 2021-07-02 10:24:25 |
| 2 | lala | 12@qq.com | 2021-07-02 10:24:25 |
| 3 | haha | 12@qq.com | 2021-07-02 10:24:25 |
| 4 | xixi | 12@qq.com | 2021-07-02 10:24:25 |
+----+------+-----------+---------------------+
单表查询
注意事项：
- 使用select查询数据，默认会在内存生成一张虚拟表，并将虚拟表的结果显示到屏幕。
select distinct 字段1,字段2,... from 库.表;
distinct 去重
where 过滤条件(分组前过滤,针对表进行筛选)
group by 分组字段
having 过滤条件(分组后过滤,针对组进行筛选)
order by 排序字段
limit 条数
原理:先去硬盘中找到对应的库和表(from),过滤出符合条件的字段数据(where),从硬盘读入内存中,并生成一个虚拟表(select)。
关键字的执行优先级
关键字的执行优先级:
from => where => group by => having => select => distinct => order by => limit
简单查询
# 创建表
create table employee(
id int not null unique 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
);
desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(3) unsigned | NO | | 28 | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | YES | | NULL | |
| post_comment | varchar(100) | YES | | NULL | |
| salary | double(15,2) | YES | | NULL | |
| office | int(11) | YES | | NULL | |
| depart_id | int(11) | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+----------------+
# 插入数据
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
("sun","male",18,"20170301","猥琐欲为",1000.22,401,1), # 以下是教学部
("haha","male",78,"20150302","teacher",10000.31,401,1),
("xixi","male",81,"20130305","teacher",8300.31,401,1),
("dudu","male",72,"20150312","teacher",3100,401,1),
("biubiu","male",28,"20140318","teacher",5000,401,1),
("lala","female",58,"20210323","teacher",1020,401,1),
("bubu","male",38,"20150520","teacher",1501,401,1),
("bobo","male",55,"20110310","teacher",8222,401,1),
("哈哈","male",48,"20120321","sale",3333,402,2), # 以下是销售部
("呵呵","male",55,"20130620","sale",1050,402,2),
("西西","male",38,"20140720","sale",1200.31,402,2),
("嘟嘟","male",18,"20181201","sale",10011,402,2),
("萌萌","male",38,"20100502","sale",10020,402,2),
("拉拉","male",58,"20050325","sale",6250,402,2),
("娜娜","male",18,"20100319","operation",20000,403,3), # 以下是运营部
("宝宝","female",18,"20150405","operation",20000,403,3),
("辣辣","male",18,"20090315","operation",18220,403,3),
("蟹蟹","male",18,"20080320","operation",50000,403,3);
select * from employee;
+----+--------+--------+-----+------------+--------------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+--------------+--------------+----------+--------+-----------+
| 1 | sun | male | 18 | 2017-03-01 | 猥琐欲为 | NULL | 1000.22 | 401 | 1 |
| 2 | haha | male | 78 | 2015-03-02 | teacher | NULL | 10000.31 | 401 | 1 |
| 3 | xixi | male | 81 | 2013-03-05 | teacher | NULL | 8300.31 | 401 | 1 |
| 4 | dudu | male | 72 | 2015-03-12 | teacher | NULL | 3100.00 | 401 | 1 |
| 5 | biubiu | male | 28 | 2014-03-18 | teacher | NULL | 5000.00 | 401 | 1 |
| 6 | lala | female | 58 | 2021-03-23 | teacher | NULL | 1020.00 | 401 | 1 |
| 7 | bubu | male | 38 | 2015-05-20 | teacher | NULL | 1501.00 | 401 | 1 |
| 8 | bobo | male | 55 | 2011-03-10 | teacher | NULL | 8222.00 | 401 | 1 |
| 9 | 哈哈 | male | 48 | 2012-03-21 | sale | NULL | 3333.00 | 402 | 2 |
| 10 | 呵呵 | male | 55 | 2013-06-20 | sale | NULL | 1050.00 | 402 | 2 |
| 11 | 西西 | male | 38 | 2014-07-20 | sale | NULL | 1200.31 | 402 | 2 |
| 12 | 嘟嘟 | male | 18 | 2018-12-01 | sale | NULL | 10011.00 | 402 | 2 |
| 13 | 萌萌 | male | 38 | 2010-05-02 | sale | NULL | 10020.00 | 402 | 2 |
| 14 | 拉拉 | male | 58 | 2005-03-25 | sale | NULL | 6250.00 | 402 | 2 |
| 15 | 娜娜 | male | 18 | 2010-03-19 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 宝宝 | female | 18 | 2015-04-05 | operation | NULL | 20000.00 | 403 | 3 |
| 17 | 辣辣 | male | 18 | 2009-03-15 | operation | NULL | 18220.00 | 403 | 3 |
| 18 | 蟹蟹 | male | 18 | 2008-03-20 | operation | NULL | 50000.00 | 403 | 3 |
+----+--------+--------+-----+------------+--------------+--------------+----------+--------+-----------+
# distinct去重
select distinct post from employee;
+--------------+
| post |
+--------------+
| 猥琐欲为 |
| teacher |
| sale |
| operation |
+--------------+
# sql语句支持数字运算(+ - * /),as相当于给字段取了一个别名(不取别名,字段名默认为salary*12)
# 也可以直接写成: select name new_name,salary*12 year_salary from employee;
select name,salary*12 as year_salary from employee;
+--------+-------------+
| name | year_salary |
+--------+-------------+
| sun | 12002.64 |
| haha | 120003.72 |
| xixi | 99603.72 |
| dudu | 37200.00 |
| biubiu | 60000.00 |
| lala | 12240.00 |
| bubu | 18012.00 |
| bobo | 98664.00 |
| 哈哈 | 39996.00 |
| 呵呵 | 12600.00 |
| 西西 | 14403.72 |
| 嘟嘟 | 120132.00 |
| 萌萌 | 120240.00 |
| 拉拉 | 75000.00 |
| 娜娜 | 240000.00 |
| 宝宝 | 240000.00 |
| 辣辣 | 218640.00 |
| 蟹蟹 | 600000.00 |
+--------+-------------+
# sql语句支持字符串拼接,需要调用内置函数concat()功能
select id,concat(name,"_vip") as new_name,sex from employee;
+----+------------+--------+
| id | new_name | sex |
+----+------------+--------+
| 1 | sun_vip | male |
| 2 | haha_vip | male |
| 3 | xixi_vip | male |
| 4 | dudu_vip | male |
| 5 | biubiu_vip | male |
| 6 | lala_vip | female |
| 7 | bubu_vip | male |
| 8 | bobo_vip | male |
| 9 | 哈哈_vip | male |
| 10 | 呵呵_vip | male |
| 11 | 西西_vip | male |
| 12 | 嘟嘟_vip | male |
| 13 | 萌萌_vip | male |
| 14 | 拉拉_vip | male |
| 15 | 娜娜_vip | male |
| 16 | 宝宝_vip | female |
| 17 | 辣辣_vip | male |
| 18 | 蟹蟹_vip | male |
+----+------------+--------+
# select结合case语句可以根据判断条件进行字符串拼接
select
id,
(
case
when name="sun" then
concat(name,"_vip")
when name="haha" then
concat(name,"_NB")
else
name
end
) as new_name,age from employee;
+----+----------+-----+
| id | new_name | age |
+----+----------+-----+
| 1 | sun_vip | 18 |
| 2 | haha_NB | 78 |
| 3 | xixi | 81 |
| 4 | dudu | 72 |
| 5 | biubiu | 28 |
| 6 | lala | 58 |
| 7 | bubu | 38 |
| 8 | bobo | 55 |
| 9 | 哈哈 | 48 |
| 10 | 呵呵 | 55 |
| 11 | 西西 | 38 |
| 12 | 嘟嘟 | 18 |
| 13 | 萌萌 | 38 |
| 14 | 拉拉 | 58 |
| 15 | 娜娜 | 18 |
| 16 | 宝宝 | 18 |
| 17 | 辣辣 | 18 |
| 18 | 蟹蟹 | 18 |
+----+----------+-----+
distinct去重
# 查看所有的分组名
select distinct post from employee;
+--------------+
| post |
+--------------+
| 猥琐欲为 |
| teacher |
| sale |
| operation |
+--------------+
where过滤
# where过滤的使用
select * from employee where id>3 and id<5;
+----+------+------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+---------+--------------+---------+--------+-----------+
| 4 | dudu | male | 72 | 2015-03-12 | teacher | NULL | 3100.00 | 401 | 1 |
+----+------+------+-----+------------+---------+--------------+---------+--------+-----------+
select * from employee where id<3 or id>16;
+----+--------+------+-----+------------+--------------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+------+-----+------------+--------------+--------------+----------+--------+-----------+
| 1 | sun | male | 18 | 2017-03-01 | 猥琐欲为 | NULL | 1000.22 | 401 | 1 |
| 2 | haha | male | 78 | 2015-03-02 | teacher | NULL | 10000.31 | 401 | 1 |
| 17 | 辣辣 | male | 18 | 2009-03-15 | operation | NULL | 18220.00 | 403 | 3 |
| 18 | 蟹蟹 | male | 18 | 2008-03-20 | operation | NULL | 50000.00 | 403 | 3 |
+----+--------+------+-----+------------+--------------+--------------+----------+--------+-----------+
select * from employee where id>=3 and id<=5;
+----+--------+------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+------+-----+------------+---------+--------------+---------+--------+-----------+
| 3 | xixi | male | 81 | 2013-03-05 | teacher | NULL | 8300.31 | 401 | 1 |
| 4 | dudu | male | 72 | 2015-03-12 | teacher | NULL | 3100.00 | 401 | 1 |
| 5 | biubiu | male | 28 | 2014-03-18 | teacher | NULL | 5000.00 | 401 | 1 |
+----+--------+------+-----+------------+---------+--------------+---------+--------+-----------+
# 意思是同上一样,id为3到5之间(包含3和5)
select * from employee where id between 3 and 5;
+----+--------+------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+------+-----+------------+---------+--------------+---------+--------+-----------+
| 3 | xixi | male | 81 | 2013-03-05 | teacher | NULL | 8300.31 | 401 | 1 |
| 4 | dudu | male | 72 | 2015-03-12 | teacher | NULL | 3100.00 | 401 | 1 |
| 5 | biubiu | male | 28 | 2014-03-18 | teacher | NULL | 5000.00 | 401 | 1 |
+----+--------+------+-----+------------+---------+--------------+---------+--------+-----------+
select * from employee where id=3 or id=5 or id=7;
+----+--------+------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+------+-----+------------+---------+--------------+---------+--------+-----------+
| 3 | xixi | male | 81 | 2013-03-05 | teacher | NULL | 8300.31 | 401 | 1 |
| 5 | biubiu | male | 28 | 2014-03-18 | teacher | NULL | 5000.00 | 401 | 1 |
| 7 | bubu | male | 38 | 2015-05-20 | teacher | NULL | 1501.00 | 401 | 1 |
+----+--------+------+-----+------------+---------+--------------+---------+--------+-----------+
# 关键字in集合查询,意思同上一样,id为3或者5或者7
select * from employee where id in (3,5,7);
+----+--------+------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+------+-----+------------+---------+--------------+---------+--------+-----------+
| 3 | xixi | male | 81 | 2013-03-05 | teacher | NULL | 8300.31 | 401 | 1 |
| 5 | biubiu | male | 28 | 2014-03-18 | teacher | NULL | 5000.00 | 401 | 1 |
| 7 | bubu | male | 38 | 2015-05-20 | teacher | NULL | 1501.00 | 401 | 1 |
+----+--------+------+-----+------------+---------+--------------+---------+--------+-----------+
# 模糊匹配like;%表示匹配任意多个字符
select * from employee where name like 'b%';
+----+--------+------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+------+-----+------------+---------+--------------+---------+--------+-----------+
| 5 | biubiu | male | 28 | 2014-03-18 | teacher | NULL | 5000.00 | 401 | 1 |
| 7 | bubu | male | 38 | 2015-05-20 | teacher | NULL | 1501.00 | 401 | 1 |
| 8 | bobo | male | 55 | 2011-03-10 | teacher | NULL | 8222.00 | 401 | 1 |
+----+--------+------+-----+------------+---------+--------------+---------+--------+-----------+
# 模糊匹配like;_表示匹配任意一个字符
mysql> select * from employee where name like 'bub_';
+----+------+------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+---------+--------------+---------+--------+-----------+
| 7 | bubu | male | 38 | 2015-05-20 | teacher | NULL | 1501.00 | 401 | 1 |
+----+------+------+-----+------------+---------+--------------+---------+--------+-----------+
# 模糊匹配like;两个_表示匹配任意两个字符
select * from employee where name like '__';
+----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 9 | 哈哈 | male | 48 | 2012-03-21 | sale | NULL | 3333.00 | 402 | 2 |
| 10 | 呵呵 | male | 55 | 2013-06-20 | sale | NULL | 1050.00 | 402 | 2 |
| 11 | 西西 | male | 38 | 2014-07-20 | sale | NULL | 1200.31 | 402 | 2 |
| 12 | 嘟嘟 | male | 18 | 2018-12-01 | sale | NULL | 10011.00 | 402 | 2 |
| 13 | 萌萌 | male | 38 | 2010-05-02 | sale | NULL | 10020.00 | 402 | 2 |
| 14 | 拉拉 | male | 58 | 2005-03-25 | sale | NULL | 6250.00 | 402 | 2 |
| 15 | 娜娜 | male | 18 | 2010-03-19 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 宝宝 | female | 18 | 2015-04-05 | operation | NULL | 20000.00 | 403 | 3 |
| 17 | 辣辣 | male | 18 | 2009-03-15 | operation | NULL | 18220.00 | 403 | 3 |
| 18 | 蟹蟹 | male | 18 | 2008-03-20 | operation | NULL | 50000.00 | 403 | 3 |
+----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
# 意思同上一样,表示匹配名字为两个字符的数据
select * from employee where char_length(name)=2;
+----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 9 | 哈哈 | male | 48 | 2012-03-21 | sale | NULL | 3333.00 | 402 | 2 |
| 10 | 呵呵 | male | 55 | 2013-06-20 | sale | NULL | 1050.00 | 402 | 2 |
| 11 | 西西 | male | 38 | 2014-07-20 | sale | NULL | 1200.31 | 402 | 2 |
| 12 | 嘟嘟 | male | 18 | 2018-12-01 | sale | NULL | 10011.00 | 402 | 2 |
| 13 | 萌萌 | male | 38 | 2010-05-02 | sale | NULL | 10020.00 | 402 | 2 |
| 14 | 拉拉 | male | 58 | 2005-03-25 | sale | NULL | 6250.00 | 402 | 2 |
| 15 | 娜娜 | male | 18 | 2010-03-19 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 宝宝 | female | 18 | 2015-04-05 | operation | NULL | 20000.00 | 403 | 3 |
| 17 | 辣辣 | male | 18 | 2009-03-15 | operation | NULL | 18220.00 | 403 | 3 |
| 18 | 蟹蟹 | male | 18 | 2008-03-20 | operation | NULL | 50000.00 | 403 | 3 |
+----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
# regexp正则匹配
select * from employee where name regexp '^b';
+----+--------+------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+------+-----+------------+---------+--------------+---------+--------+-----------+
| 5 | biubiu | male | 28 | 2014-03-18 | teacher | NULL | 5000.00 | 401 | 1 |
| 7 | bubu | male | 38 | 2015-05-20 | teacher | NULL | 1501.00 | 401 | 1 |
| 8 | bobo | male | 55 | 2011-03-10 | teacher | NULL | 8222.00 | 401 | 1 |
+----+--------+------+-----+------------+---------+--------------+---------+--------+-----------+
# 查询数据为null的字段,针对null不能用=号,需要使用is
select * from employee where post_comment = null;
Empty set (0.00 sec)
select * from employee where post_comment is null;
+----+--------+--------+-----+------------+--------------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+--------------+--------------+----------+--------+-----------+
| 1 | sun | male | 18 | 2017-03-01 | 猥琐欲为 | NULL | 1000.22 | 401 | 1 |
| 2 | haha | male | 78 | 2015-03-02 | teacher | NULL | 10000.31 | 401 | 1 |
| 3 | xixi | male | 81 | 2013-03-05 | teacher | NULL | 8300.31 | 401 | 1 |
| 4 | dudu | male | 72 | 2015-03-12 | teacher | NULL | 3100.00 | 401 | 1 |
| 5 | biubiu | male | 28 | 2014-03-18 | teacher | NULL | 5000.00 | 401 | 1 |
| 6 | lala | female | 58 | 2021-03-23 | teacher | NULL | 1020.00 | 401 | 1 |
| 7 | bubu | male | 38 | 2015-05-20 | teacher | NULL | 1501.00 | 401 | 1 |
| 8 | bobo | male | 55 | 2011-03-10 | teacher | NULL | 8222.00 | 401 | 1 |
| 9 | 哈哈 | male | 48 | 2012-03-21 | sale | NULL | 3333.00 | 402 | 2 |
| 10 | 呵呵 | male | 55 | 2013-06-20 | sale | NULL | 1050.00 | 402 | 2 |
| 11 | 西西 | male | 38 | 2014-07-20 | sale | NULL | 1200.31 | 402 | 2 |
| 12 | 嘟嘟 | male | 18 | 2018-12-01 | sale | NULL | 10011.00 | 402 | 2 |
| 13 | 萌萌 | male | 38 | 2010-05-02 | sale | NULL | 10020.00 | 402 | 2 |
| 14 | 拉拉 | male | 58 | 2005-03-25 | sale | NULL | 6250.00 | 402 | 2 |
| 15 | 娜娜 | male | 18 | 2010-03-19 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 宝宝 | female | 18 | 2015-04-05 | operation | NULL | 20000.00 | 403 | 3 |
| 17 | 辣辣 | male | 18 | 2009-03-15 | operation | NULL | 18220.00 | 403 | 3 |
| 18 | 蟹蟹 | male | 18 | 2008-03-20 | operation | NULL | 50000.00 | 403 | 3 |
+----+--------+--------+-----+------------+--------------+--------------+----------+--------+-----------+
group by分组
首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的 指定表中的字段名，将指定字段名的数据进行分类
分组的意义在于指定表中的字段，将表中数据分类，以组的形式进行数据的操作
可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数
设置sql_mode="only_full_group_by"
# 查看mysql默认的sql_mode
select @@global.sql_mode;
# 由于没有设置only_full_group_by,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的
set global sql_mode='only_full_group_by';
# 设置成功后，一定要退出，然后重新登录方可生效
exit
# 设置完成后,我们重新进入mysql数据库,不借助聚合函数查看表中数据默认会报错
select * from employee group by post;
ERROR 1055 (42000): 'db01.employee.id' isn't in GROUP BY
group by必须配合聚合函数使用
# 求每个组的平均工资
select post,avg(age) from employee group by post;
+--------------+----------+
| post | avg(age) |
+--------------+----------+
| operation | 18.0000 |
| sale | 42.5000 |
| teacher | 58.5714 |
| 猥琐欲为 | 18.0000 |
+--------------+----------+
# 求男人和女人的平均工资
select sex,max(salary) from employee group by sex;
+--------+-------------+
| sex | max(salary) |
+--------+-------------+
| male | 50000.00 |
| female | 20000.00 |
+--------+-------------+
# 统计每个部门的人数
select post "部门",count(id) "部门人数" from employee group by post;
+--------------+--------------+
| 部门 | 部门人数 |
+--------------+--------------+
| operation | 4 |
| sale | 6 |
| teacher | 7 |
| 猥琐欲为 | 1 |
+--------------+--------------+
聚合函数
聚合函数：聚合函数只能在分组之后使用，聚合的是组的内容，若是没有分组，则默认一组
# count统计数量
select count(*) from employee;
+----------+
| count(*) |
+----------+
| 18 |
+----------+
mysql> select count(*) from employee where id>15;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
# max求最大值
mysql> select max(salary) from employee;
+-------------+
| max(salary) |
+-------------+
| 50000.00 |
+-------------+
)
# min求最小值
mysql> select min(salary) from employee;
+-------------+
| min(salary) |
+-------------+
| 1000.22 |
+-------------+
# avg求平均值
mysql> select avg(salary) from employee;
+-------------+
| avg(salary) |
+-------------+
| 9901.563889 |
+-------------+
# sum求和
mysql> select sum(salary) from employee;
+-------------+
| sum(salary) |
+-------------+
| 178228.15 |
+-------------+
1 row in set (0.00 sec)
mysql> select sum(salary) from employee where id>15;
+-------------+
| sum(salary) |
+-------------+
| 88220.00 |
+-------------+
1 row in set (0.00 sec)
having过滤
select查询执行命令的优先级从高到低：where > group by > having
where 发生在分组group by之前，因而where中可以有任意字段，但是绝对不能使用聚合函数
having发生在分组group by之后，因而having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
# 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,count(*) from employee group by post having count(id)>2;
+-----------+----------+
| post | count(*) |
+-----------+----------+
| operation | 4 |
| sale | 6 |
| teacher | 7 |
+-----------+----------+
# 查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) "平均工资" from employee group by post having avg(salary) > 10000;
+-----------+--------------+
| post | 平均工资 |
+-----------+--------------+
| operation | 27055.000000 |
+-----------+--------------+
# 查询各岗位平均薪资大于1000且小于10000的岗位名、平均工资
select post,avg(salary) "平均工资" from employee group by post having avg(salary)>1000 and avg(salary)<10000;
+--------------+--------------+
| post | 平均工资 |
+--------------+--------------+
| sale | 5310.718333 |
| teacher | 5306.231429 |
| 猥琐欲为 | 1000.220000 |
+--------------+--------------+
order by查询排序
# 升序(按照工资从低到高排序)
# select * from employee order by salary asc; 默认为升序,asc可忽略不写
select * from employee order by salary;
+----+--------+--------+-----+------------+--------------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+--------------+--------------+----------+--------+-----------+
| 1 | sun | male | 18 | 2017-03-01 | 猥琐欲为 | NULL | 1000.22 | 401 | 1 |
| 6 | lala | female | 58 | 2021-03-23 | teacher | NULL | 1020.00 | 401 | 1 |
| 10 | 呵呵 | male | 55 | 2013-06-20 | sale | NULL | 1050.00 | 402 | 2 |
| 11 | 西西 | male | 38 | 2014-07-20 | sale | NULL | 1200.31 | 402 | 2 |
| 7 | bubu | male | 38 | 2015-05-20 | teacher | NULL | 1501.00 | 401 | 1 |
| 4 | dudu | male | 72 | 2015-03-12 | teacher | NULL | 3100.00 | 401 | 1 |
| 9 | 哈哈 | male | 48 | 2012-03-21 | sale | NULL | 3333.00 | 402 | 2 |
| 5 | biubiu | male | 28 | 2014-03-18 | teacher | NULL | 5000.00 | 401 | 1 |
| 14 | 拉拉 | male | 58 | 2005-03-25 | sale | NULL | 6250.00 | 402 | 2 |
| 8 | bobo | male | 55 | 2011-03-10 | teacher | NULL | 8222.00 | 401 | 1 |
| 3 | xixi | male | 81 | 2013-03-05 | teacher | NULL | 8300.31 | 401 | 1 |
| 2 | haha | male | 78 | 2015-03-02 | teacher | NULL | 10000.31 | 401 | 1 |
| 12 | 嘟嘟 | male | 18 | 2018-12-01 | sale | NULL | 10011.00 | 402 | 2 |
| 13 | 萌萌 | male | 38 | 2010-05-02 | sale | NULL | 10020.00 | 402 | 2 |
| 17 | 辣辣 | male | 18 | 2009-03-15 | operation | NULL | 18220.00 | 403 | 3 |
| 15 | 娜娜 | male | 18 | 2010-03-19 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 宝宝 | female | 18 | 2015-04-05 | operation | NULL | 20000.00 | 403 | 3 |
| 18 | 蟹蟹 | male | 18 | 2008-03-20 | operation | NULL | 50000.00 | 403 | 3 |
+----+--------+--------+-----+------------+--------------+--------------+----------+--------+-----------+
# 降序(按照工资从高到低排序) desc
select * from employee order by salary desc;
+----+--------+--------+-----+------------+--------------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+--------------+--------------+----------+--------+-----------+
| 18 | 蟹蟹 | male | 18 | 2008-03-20 | operation | NULL | 50000.00 | 403 | 3 |
| 16 | 宝宝 | female | 18 | 2015-04-05 | operation | NULL | 20000.00 | 403 | 3 |
| 15 | 娜娜 | male | 18 | 2010-03-19 | operation | NULL | 20000.00 | 403 | 3 |
| 17 | 辣辣 | male | 18 | 2009-03-15 | operation | NULL | 18220.00 | 403 | 3 |
| 13 | 萌萌 | male | 38 | 2010-05-02 | sale | NULL | 10020.00 | 402 | 2 |
| 12 | 嘟嘟 | male | 18 | 2018-12-01 | sale | NULL | 10011.00 | 402 | 2 |
| 2 | haha | male | 78 | 2015-03-02 | teacher | NULL | 10000.31 | 401 | 1 |
| 3 | xixi | male | 81 | 2013-03-05 | teacher | NULL | 8300.31 | 401 | 1 |
| 8 | bobo | male | 55 | 2011-03-10 | teacher | NULL | 8222.00 | 401 | 1 |
| 14 | 拉拉 | male | 58 | 2005-03-25 | sale | NULL | 6250.00 | 402 | 2 |
| 5 | biubiu | male | 28 | 2014-03-18 | teacher | NULL | 5000.00 | 401 | 1 |
| 9 | 哈哈 | male | 48 | 2012-03-21 | sale | NULL | 3333.00 | 402 | 2 |
| 4 | dudu | male | 72 | 2015-03-12 | teacher | NULL | 3100.00 | 401 | 1 |
| 7 | bubu | male | 38 | 2015-05-20 | teacher | NULL | 1501.00 | 401 | 1 |
| 11 | 西西 | male | 38 | 2014-07-20 | sale | NULL | 1200.31 | 402 | 2 |
| 10 | 呵呵 | male | 55 | 2013-06-20 | sale | NULL | 1050.00 | 402 | 2 |
| 6 | lala | female | 58 | 2021-03-23 | teacher | NULL | 1020.00 | 401 | 1 |
| 1 | sun | male | 18 | 2017-03-01 | 猥琐欲为 | NULL | 1000.22 | 401 | 1 |
+----+--------+--------+-----+------------+--------------+--------------+----------+--------+-----------+
# 按多列排序,先按照age排序,如果age相同,则按照薪资排序
select * from employee order by age,salary desc;
+----+--------+--------+-----+------------+--------------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+--------------+--------------+----------+--------+-----------+
| 18 | 蟹蟹 | male | 18 | 2008-03-20 | operation | NULL | 50000.00 | 403 | 3 |
| 16 | 宝宝 | female | 18 | 2015-04-05 | operation | NULL | 20000.00 | 403 | 3 |
| 15 | 娜娜 | male | 18 | 2010-03-19 | operation | NULL | 20000.00 | 403 | 3 |
| 17 | 辣辣 | male | 18 | 2009-03-15 | operation | NULL | 18220.00 | 403 | 3 |
| 12 | 嘟嘟 | male | 18 | 2018-12-01 | sale | NULL | 10011.00 | 402 | 2 |
| 1 | sun | male | 18 | 2017-03-01 | 猥琐欲为 | NULL | 1000.22 | 401 | 1 |
| 5 | biubiu | male | 28 | 2014-03-18 | teacher | NULL | 5000.00 | 401 | 1 |
| 13 | 萌萌 | male | 38 | 2010-05-02 | sale | NULL | 10020.00 | 402 | 2 |
| 7 | bubu | male | 38 | 2015-05-20 | teacher | NULL | 1501.00 | 401 | 1 |
| 11 | 西西 | male | 38 | 2014-07-20 | sale | NULL | 1200.31 | 402 | 2 |
| 9 | 哈哈 | male | 48 | 2012-03-21 | sale | NULL | 3333.00 | 402 | 2 |
| 8 | bobo | male | 55 | 2011-03-10 | teacher | NULL | 8222.00 | 401 | 1 |
| 10 | 呵呵 | male | 55 | 2013-06-20 | sale | NULL | 1050.00 | 402 | 2 |
| 14 | 拉拉 | male | 58 | 2005-03-25 | sale | NULL | 6250.00 | 402 | 2 |
| 6 | lala | female | 58 | 2021-03-23 | teacher | NULL | 1020.00 | 401 | 1 |
| 4 | dudu | male | 72 | 2015-03-12 | teacher | NULL | 3100.00 | 401 | 1 |
| 2 | haha | male | 78 | 2015-03-02 | teacher | NULL | 10000.31 | 401 | 1 |
| 3 | xixi | male | 81 | 2013-03-05 | teacher | NULL | 8300.31 | 401 | 1 |
+----+--------+--------+-----+------------+--------------+--------------+----------+--------+-----------+
limit 限制查询的记录数
# 默认初始位置为0
select * from employee order by salary desc limit 3;
+----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 18 | 蟹蟹 | male | 18 | 2008-03-20 | operation | NULL | 50000.00 | 403 | 3 |
| 16 | 宝宝 | female | 18 | 2015-04-05 | operation | NULL | 20000.00 | 403 | 3 |
| 15 | 娜娜 | male | 18 | 2010-03-19 | operation | NULL | 20000.00 | 403 | 3 |
+----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
# 从第0开始，即先查询出第一条，然后包含这一条在内往后查5条
select * from employee order by salary desc limit 0,5;
+----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 18 | 蟹蟹 | male | 18 | 2008-03-20 | operation | NULL | 50000.00 | 403 | 3 |
| 15 | 娜娜 | male | 18 | 2010-03-19 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 宝宝 | female | 18 | 2015-04-05 | operation | NULL | 20000.00 | 403 | 3 |
| 17 | 辣辣 | male | 18 | 2009-03-15 | operation | NULL | 18220.00 | 403 | 3 |
| 13 | 萌萌 | male | 38 | 2010-05-02 | sale | NULL | 10020.00 | 402 | 2 |
+----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
# 从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
select * from employee order by salary desc limit 5,5;
+----+--------+------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+------+-----+------------+---------+--------------+----------+--------+-----------+
| 12 | 嘟嘟 | male | 18 | 2018-12-01 | sale | NULL | 10011.00 | 402 | 2 |
| 2 | haha | male | 78 | 2015-03-02 | teacher | NULL | 10000.31 | 401 | 1 |
| 3 | xixi | male | 81 | 2013-03-05 | teacher | NULL | 8300.31 | 401 | 1 |
| 8 | bobo | male | 55 | 2011-03-10 | teacher | NULL | 8222.00 | 401 | 1 |
| 14 | 拉拉 | male | 58 | 2005-03-25 | sale | NULL | 6250.00 | 402 | 2 |
+----+--------+------+-----+------------+---------+--------------+----------+--------+-----------+
regexp 使用正则表达式查询
# 匹配出名字以la开头的数据
select * from employee where name regexp "^la";
+----+------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| 6 | lala | female | 58 | 2021-03-23 | teacher | NULL | 1020.00 | 401 | 1 |
+----+------+--------+-----+------------+---------+--------------+---------+--------+-----------+
# 匹配出名字以a结尾的数据
select * from employee where name regexp "a$";
+----+------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| 2 | haha | male | 78 | 2015-03-02 | teacher | NULL | 10000.31 | 401 | 1 |
| 6 | lala | female | 58 | 2021-03-23 | teacher | NULL | 1020.00 | 401 | 1 |
+----+------+--------+-----+------------+---------+--------------+----------+--------+-----------+
# 匹配出名字以"辣"开头,任意一个字符结尾的数据
select * from employee where name regexp "辣{1}";
+----+--------+------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+------+-----+------------+-----------+--------------+----------+--------+-----------+
| 17 | 辣辣 | male | 18 | 2009-03-15 | operation | NULL | 18220.00 | 403 | 3 |
+----+--------+------+-----+------------+-----------+--------------+----------+--------+-----------+
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
