day35 数据操作补充和单表操作
文章目录
今日内容概要
- 数据操作补充
- 增
- 删
- 改
- 单表查询
昨日内容回顾
-
基础数据类型:
- 数字类型:tinyint,int,float(7,2),decimal
- 时间类型:datetime,date,time
- 字符串类型:char定长浪费空间存取快 varchar变长节省空间存取慢
- 枚举和集合:enum()单选 set()多选和去重
-
约束
- unsigned
- not null
- default
- unique/联合
- primary key/联合
- auto_increment(依赖unique条件)
- foreign key(依赖关联字段的unique条件)
- on update cascade
- on delete cascade
-
表与表之间的关系
- 一对多/多对一:foreign key
- 一对一:foreign key + unique
- 多对多:创建第三张表
-
创建表
create table 表名( 字段名 类型(长度) 约束 约束2)
-
删除表
drop table 表名;
-
修改表
alter table 表名 add/drop/modify/change/rename ... xxx;
-
查看表结构
desc 表名; describe 表名; show create table 表名;
今日内容详细
数据操作补充
增 insert
insert into 表名(字段们) value (一行数据);
insert into 表名(字段们) values (第一行的值),(第二行的值);
insert into 表1(字段们) (select 字段们 from 表2);
通过第三条,我们可以实现将一个表格中的数据增加到另一张表中:
mysql> create table t1(id int,name char(20)); # 创建表t1
Query OK, 0 rows affected (0.04 sec)
mysql> create table t2(id int,name char(20),age int); # 创建表t2
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t1 values # 插入数据
-> (1,'alex'),
-> (2,'wusir'),
-> (3,'baoyuan');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into t2 values
-> (1,'吴彦祖',18),
-> (2,'蔡徐坤',21),
-> (3,'彭于晏',16);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into t2(id,name)(select * from t1); # 将t1中的数据插入t2中
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t2; # 查看t2表格,成功插入
+------+-----------+------+
| id | name | age |
+------+-----------+------+
| 1 | 吴彦祖 | 18 |
| 2 | 蔡徐坤 | 21 |
| 3 | 彭于晏 | 16 |
| 1 | alex | NULL |
| 2 | wusir | NULL |
| 3 | baoyuan | NULL |
+------+-----------+------+
6 rows in set (0.00 sec)
删 delete(谨慎操作,不可逆)
delete from 表名; # 清空表,自增字段位置
delete from 表名 where 条件; # 清楚符合条件的数据
truncate table 表名; # 清空并重置并重置自增字段
改 update
update 表名 set 字段名=新值 where 条件;
update 表名 set 字段1=新值1,字段2=新值2 where 条件;
单表查询
创建表:
create table employee(
id int not null unique auto_increment,
emp_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 employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','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)
;
单表查询的基本结构为:
select xxx from 表名 where 条件 group by 分组 having 聚合的过滤 order by 排序 limit m,n;
select...where...
select语句,用来筛选列。
select * from 表;
# 可以指定查看某几列
select 字段名1,字段名2 from 表;
# 去重
select distinct 字段名 from 表;
# 数据可以进行四则运算,可以用as取别名
select 字段名*12 as 新名字 from 表;
# 取别名的as可以省略
select 字段名*12 新名字 from 表;
备注:
对于单独的一列,使用distinct就是起到去重的作用:
mysql> select distinct sex from employee;
+--------+
| sex |
+--------+
| male |
| female |
+--------+
2 rows in set (0.00 sec)
但是对于多列来说,distinct的作用是限制两个列组合不重复:
mysql> select distinct sex,age from employee;
+--------+-----+
| sex | age |
+--------+-----+
| male | 18 |
| male | 78 |
| male | 81 |
| male | 73 |
| male | 28 |
| female | 18 |
| male | 48 |
| female | 48 |
| female | 38 |
| female | 28 |
+--------+-----+
10 rows in set (0.00 sec)
介绍两个重要的函数:
concat(...) # 拼接
concat_ws('符号',...)
其使用方法如下:
mysql> select concat(id,'号'),concat_ws(':',emp_name,age,sex) from employee;
+------------------+---------------------------------+
| concat(id,'号') | concat_ws(':',emp_name,age,sex) |
+------------------+---------------------------------+
| 1号 | egon:18:male |
| 2号 | alex:78:male |
| 3号 | wupeiqi:81:male |
| 4号 | yuanhao:73:male |
| 5号 | liwenzhou:28:male |
| 6号 | jingliyang:18:female |
| 7号 | jinxin:18:male |
| 8号 | 成龙:48:male |
| 9号 | 歪歪:48:female |
| 10号 | 丫丫:38:female |
| 11号 | 丁丁:18:female |
| 12号 | 星星:18:female |
| 13号 | 格格:28:female |
| 14号 | 张野:28:male |
| 15号 | 程咬金:18:male |
| 16号 | 程咬银:18:female |
| 17号 | 程咬铜:18:male |
| 18号 | 程咬铁:18:female |
+------------------+---------------------------------+
18 rows in set (0.00 sec)
concat和concat_ws函数同样可以使用as取别名:
mysql> select concat(id,'号') as eid,concat_ws(':',emp_name,age,sex) as info from employee;
+-------+----------------------+
| eid | info |
+-------+----------------------+
| 1号 | egon:18:male |
| 2号 | alex:78:male |
| 3号 | wupeiqi:81:male |
| 4号 | yuanhao:73:male |
| 5号 | liwenzhou:28:male |
| 6号 | jingliyang:18:female |
| 7号 | jinxin:18:male |
| 8号 | 成龙:48:male |
| 9号 | 歪歪:48:female |
| 10号 | 丫丫:38:female |
| 11号 | 丁丁:18:female |
| 12号 | 星星:18:female |
| 13号 | 格格:28:female |
| 14号 | 张野:28:male |
| 15号 | 程咬金:18:male |
| 16号 | 程咬银:18:female |
| 17号 | 程咬铜:18:male |
| 18号 | 程咬铁:18:female |
+-------+----------------------+
18 rows in set (0.00 sec)
case…when…end 条件语句,用来给符合指定条件的数据做一些特定的操作,其用法为:
mysql> select(
-> case
-> when emp_name='alex'
-> then concat(emp_name,'_bigsb')
-> when emp_name='jingliyang'
-> then emp_name
-> else concat(emp_name,'_sb')
-> end
-> )
-> from employee;
+---------------------------------------------------------------------------------------------------------------------------------------+
| (
case
when emp_name='alex'
then concat(emp_name,'_bigsb')
when emp_name='jingliyang'
then emp_name
else concat(emp_name,'_sb')
end
) |
+---------------------------------------------------------------------------------------------------------------------------------------+
| egon_sb |
| alex_bigsb |
| wupeiqi_sb |
| yuanhao_sb |
| liwenzhou_sb |
| jingliyang |
| jinxin_sb |
| 成龙_sb |
| 歪歪_sb |
| 丫丫_sb |
| 丁丁_sb |
| 星星_sb |
| 格格_sb |
| 张野_sb |
| 程咬金_sb |
| 程咬银_sb |
| 程咬铜_sb |
| 程咬铁_sb |
+---------------------------------------------------------------------------------------------------------------------------------------+
18 rows in set (0.00 sec)
表头很长,我们同样可以给它去一个别名:
mysql> select(
-> case
-> when emp_name='alex'
-> then concat(emp_name,'_bigsb')
-> when emp_name='jingliyang'
-> then emp_name
-> else concat(emp_name,'_sb')
-> end) as name
-> from employee;
+--------------+
| name |
+--------------+
| egon_sb |
| alex_bigsb |
| wupeiqi_sb |
| yuanhao_sb |
| liwenzhou_sb |
| jingliyang |
| jinxin_sb |
| 成龙_sb |
| 歪歪_sb |
| 丫丫_sb |
| 丁丁_sb |
| 星星_sb |
| 格格_sb |
| 张野_sb |
| 程咬金_sb |
| 程咬银_sb |
| 程咬铜_sb |
| 程咬铁_sb |
+--------------+
18 rows in set (0.00 sec)
where条件用来筛选行,其常用的条件有:
-
比较运算:> < = >= <= !=/<>
-
范围:in between…and…
-
模糊查询:
-
like
name like 'a%' # 任意多个字符内容 name like 'a_' # 任意一个字符内容
-
regexp
name regexp '^a' # 正则匹配
-
-
逻辑运算 与或非:
- and
- or
- not:not in
-
判断是否为空:
select * from employee where post_comment is not null; select * from employee where post_comment is null;
group by...having...
group by字段,表示根据这个字段进行分组。
聚合函数:
- count
- sum
- min
- max
- avg
聚合函数使用示例:
mysql> select post,avg(salary) from employee group by post;
+-----------------------------------------+---------------+
| post | avg(salary) |
+-----------------------------------------+---------------+
| operation | 16800.026000 |
| sale | 2600.294000 |
| teacher | 151842.901429 |
| 老男孩驻沙河办事处外交大使 | 7300.330000 |
+-----------------------------------------+---------------+
4 rows in set (0.00 sec)
having队组进行过滤:
mysql> select post,avg(salary) as avg_salary from employee group by post having avg_salary>10000;
+-----------+---------------+
| post | avg_salary |
+-----------+---------------+
| operation | 16800.026000 |
| teacher | 151842.901429 |
+-----------+---------------+
2 rows in set (0.00 sec)
order by...limit...
基本格式为:
select * from 表名 order by 字段; # 从小到大排
select * from 表名 order by 字段 asc; # 从小到大排
select * from 表名 order by 字段 asc; # 从大到小排
# limit
select * from 表 limit m,n; 表示从m+1开始取n条
select * from 表 limit n; 表示从1开始取n条;就是取前n条