大家好呀~ 今天是 Mysql 的第二课，主要学习数据的增删改查、筛选、分组、排序等操作，以及一些常用的函数。其中，筛选、分组、排序等操作是重点，也是难点，需要多多动手练习才能熟练掌握并灵活应用。

本节课主要
内容如下：

1、插入数据
2、更新表中数据
3、更新表结构
（1）增加列
（2）删除列
（3）拓展
4、删除
（1）删除某些行
（2）删除表
（3）删除数据库
（4）delete 与 drop 的对比
5、SQL基础查询
（1）建表和插入数据
（2）检索数据：select
（3）原表筛选条件：where
（4）数据分组：group by
（5）结果排序：order by
（6）MySQL查询语句基本格式
（7）MySQL语句解析顺序
6、SQL函数
（1）聚合函数
（2）时间日期函数
（3）字符串函数
（4）数学函数

一、插入数据

插入一行：

第一种写法（每一列都要有值）：
INSERT INTO 表名
VALUES
(值1, 值2,...);

第二种写法（推荐，自己决定插入某些列的值）：
INSERT INTO 表名
(字段1, 字段2, ...)
VALUES
(值1, 值2,...)

插入多行：
INSERT INTO 表名
(字段1, 字段2, ...)
VALUES
(值1, 值2,...),
(值1, 值2,...),
(值1, 值2,...),
(值1, 值2,...),
......

例如：

insert into student values(1,'zs');
insert into student (id,name) values  (2,'李四');
insert into student (id) values (3);

课堂练习：创建如下表格“order_2017”

练习

代码示例（自己练习完再看哦~）：

# 创建表
create table order_2017(
    order_id VARCHAR(20),
    cust_id VARCHAR(20),
    date DATE,
    original_value FLOAT,
    discount FLOAT,
    items INT
);

# 添加数据
INSERT INTO order_2017 VALUES 
('0001','C1','2017-01-01',199.00,99.00,1),
('0002','C2','2017-01-01',30.70,0.00,3),
('0003','C3','2017-01-03',49.90,19.00,1),
('0004','C4','2017-02-19',125.00,0.00,5),
('0005','C2','2017-03-05',499.00,100.00,4),
('0006','C4','2017-11-11',273.50,23.50,11);

二、更新表中数据

语法结构：

UPDATE 表名   -- 要更新的表名
SET 字段=新值   -- 需要更新的字段和新值
WHERE 字段=旧值 -- 匹配需要更新的行

练习： 把订单ID为'0002'的订单中，日期改成'2017-04-01'

UPDATE order_2017
SET date = '2017-01-01'
WHERE order_id = '0002'

三、更新表结构

1、增加列

ALTER TABLE 表名
ADD 列名 数据类型
[约束条件] [FIRST | AFTER 已存在字段名];
-- 注：要是没有指定插入的位置，则默认插入到最后⼀列。

练习： 在 'order_2017' 表中，增加订单的实付金额 spend 列

ALTER TABLE order_2017
ADD `spend` FLOAT

2、删除列

ALTER TABLE 表名
DROP 列名;

练习： 在 'order_2017' 表中，删除订单的实付金额 spend 列

ALTER TABLE order_2017
DROP spend;

拓展

-- 修改表名
ALTER  TABLE 旧表名 RENAME  [TO] 新表名;

-- 修改字段的数据类型
ALTER  TABLE 表名 MODIFY 字段名 数据类型;

-- 修改字段名
ALTER  TABLE 表名 CHANGE 旧字段 新字段 新字段数据类型;

注：CHANGE也可以只修改数据类型，实现和MODIFY同样的效果， 即旧字段和新字段设置得⼀样。

-- 修改字段的排列位置
ALTER  TABLE 表名 MODIFY 字段1 数据类型 FIRST;
ALTER  TABLE 表名 MODIFY 字段1 数据类型 AFTER 字段2;

四、删除

1、删除某些行

语法结构：

delete from 表名  -- 从哪张表中删除
where 条件    -- 删除哪些⾏

练习： 在'order_2017'表中，删除订单ID为 '0002' 的订单

delete from order_2017
where order_id = '0002';

2、删除表

语法结构：

drop table 表名
或者
用工具手动删除

-- 删除多个没有被其他表关联的数据表。
DROP TABLE [IF EXISTS] 表1,表2,...表n;

2、删除数据库

语法结构：

drop database 数据库名;
或者
用工具手动删除

示例：删除名字为'xuexii'的数据库

DROP database xuexii;

4、delete 与 drop 的对比

delete 删除表中的内容，不删除表；（砍树枝、砍树叶）
drop 删除表或者数据库；（将树连根拔起）

五、SQL基础查询

1、建表和插入数据

-- 设定终端字符集
SET NAMES  GBK; -- Windows电脑需要设置终端字符集

-- 建 表
DROP TABLE IF EXISTS  `employee`;
CREATE  TABLE `employee` (
    `eno` int primary  key,
    `ename` varchar(20),
    `job` varchar(20),
    `mgno` int,
    `hiredate` date,
    `sal` double,
    `bonus` double,
    `deptno` int 17 
);

-- 插入数据
INSERT  INTO `employee` VALUES (1001, '钱多多', '总裁 ', NULL, '2009-11-17', 100000, 0, 10);
INSERT  INTO `employee` VALUES (1003, '张伟', '职员 ', 1021, '2010-12-17', 8000, 500, 20);
INSERT  INTO `employee` VALUES (1004, '王伟', '销售员 ', 1013, '2011-02-20', 6000, 1000, 30);
INSERT  INTO `employee` VALUES (1006, '王芳', '销售员 ', 1013, '2011-02-22', 6000, 500, 30);
INSERT INTO `employee` VALUES (1010, '李秀英', '经理 ', 1001, '2011-04-02', 50000, 500, 20);
INSERT INTO `employee` VALUES (1013, '李静', '经理 ', 1001, '2011-05-01', 35000, 500, 30);
INSERT INTO `employee` VALUES (1014, '赵丽', '经理 ', 1001, '2011-06-09', 20000, 500, 10);
INSERT INTO `employee` VALUES (1016, '⻢芯', '销售员 ', 1013, '2011-09-08', 6000, 500, 30);
INSERT INTO `employee` VALUES (1021, '⽜军', '分析师 ', 1010, '2011-12-03', 30000, 3000, 20);
INSERT INTO `employee` VALUES (1033, '孙刚', '职员 ', 1014, '2013-01-23', 6000, 0, 10);
INSERT INTO `employee` VALUES (1055, '张敏', '销售员 ', 1013, '2015-09-28', 5000, 500, 30);
INSERT INTO `employee` VALUES (1059, '刘洋', '助理 ', 1013, '2015-12-03', 5000, 1500, 30);
INSERT INTO `employee` VALUES (1087, '李强', '分析师 ', 1010, '2017-06-13', 20000, 500, 20);
INSERT INTO `employee` VALUES (1088, '韩爽', '职员 ', 1087, '2017-06-13', 8000, 1000, 20);

-- 建 表
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
    `deptno` int primary key,
    `dname` varchar(20),
    `location` varchar(20)
);

-- 插⼊数据
INSERT INTO `department` VALUES (10, '财务部', '北京');                  
INSERT INTO `department` VALUES (20, '运营部', '北京');                  
INSERT INTO `department` VALUES (30, '销售部', '全国各地');

2、检索数据：select

-- 1.查询所有的列
select *
from employee;

-- 2.查询单列:查询出员工表中员工编号eno列
select eno
from employee;

-- 3.查询多列:查询出员工表中员工编号、员工姓名列
select eno,ename
from employee;

-- 4.限制检索结果: limit
select *
from employee limit 3;

-- 5.去重检索:distinct

-- 5.1去重前
select job
from employee;

-- 5.2去重后
select distinct job
from employee;

-- 6.重命名查询出的列名: as
select distinct job as 职位类型
from employee;

3、原表筛选条件：where

-- 基本结构
select ...
from ...
where 条件;

# 判断条件
> < = <>(不等于) >= <=  BETWEEN AND OR NOT IN
like(%匹配任意长度的字符，包括零个字符  _:匹配任意⼀个字符)

# 使用ISNULL()来判断是否为NULL值，作为⼀个整体其简洁易懂，执行效率更快⼀些。

说明：NULL与任何值的直接比较都为NULL
①null <> null的返回结果是null，而不是false。
②null = null的返回结果是null，而不是true。
③null <> 1的返回结果是NULL，而不是true。

-- 1.检索出员工表中工资大于10000的员工信息
select *
from employee where sal>10000;

-- 2.查询员工表中工资⼤于10000并且拿到津贴的员工姓名select *
from employee
where sal>10000 and bonus>0;

-- 3.查询员工表中名字含有'伟'的员工姓名
select ename
from employee
where ename like '%伟%';

-- 练习：从员工表中筛选出部门编号为20或30、2013年~2015年期间入职、工资在5000以上(含5000)的员工信息

-- 查询计算字段:+ - * /
-- 创建的新字段可以使用别名：AS

基本格式:
select
    字段1+字段2 as '和', 
    字段1-字段2 as '差', 
    字段1*字段2 as '积', 
    字段1/字段2 as '商'
from 表 名

-- 4.查询员工表中每个员工的收入
income(income=sal+bonus)
select ename,sal+bonus as income
from employee;

-- 5.拼接字段
-- 错误写法
select (eno + ename) as "员工标识"
from employee;

-- 正确写法
select concat(eno,ename) as "员工标识"
from employee;

select concat(eno,"-",ename) as "员工标识" from employee;

4、数据分组：group by

-- 基本结构
SELECT 分组字段,聚合函数
FROM 表 名
group by 分组字段

-- 1.计算各级别员工的本月工资支出总和及津贴支出和
select job as 员工职级, 
    sum(sal) as '工资支出和', 
    sum(bonus) as "津贴支出和"
from employee 
group by job;

/* Having:分组筛选，对分组后的数据进行筛选

基本结构：
select 分组字段,聚合函数
from 表名
where 原表筛选条件
group by 分组字段
having 分组筛选条件

-- 2.找出工资支出总和大于50000的员工职级
select job,
sum(sal) as 工资支出和
from employee
group by job
having sum(sal) > 50000; 30

思考：having中的这个sum(sal)可以替换成 工资支出和 吗？
select job,
sum(sal) as 工资支出和
from employee
group by job
having 工资支出和 > 50000; 37

5、结果排序：order by

-- 计算每种职位的员工工资总支出与津贴总支出，只显示津贴总
支出超过1000的职位，并按照总工资从高到低进行排列。

select job as 职级名称,
sum(sal) as 职级总工资,
sum(bonus) as 职级总津贴
from employee
group by job
having sum(bonus)>1000
order by sum(sal) desc;
-- 注意：DESC表示降序排列，不写或者ASC是升序排列

6、MySQL查询语句基本格式

SELECT  查询字段,
DISTINCT <select_list> 去 重
FROM <left_table>   数据源
<join_type> JOIN <right_table> 连接表
ON <join_condition> 连接条件
WHERE <where_condition> 原表筛选条件
GROUP BY <group_by_list> 分组条件
HAVING <having_condition> 分组筛选条件
ORDER BY <order_by_condition> 排序条件
LIMIT <limit_number> 限制条件;

7、MySQL语句解析顺序

①from

②on

③join

④where

⑤group by

⑥having

⑦select

⑧distinct

⑨order by

⑩limit

六、SQL函数

1、聚合函数

1.  AVG():返回平均值
2.  MAX():返回最大值
3.  MIN():返回最小值
4.  SUM():返回总和
5.  COUNT():返回总个数

-- 1.查出员工表中的工资总⽀出、津贴总支出、平均工资
select sum(sal) 工资总支出, sum(bonus) 津贴总支出 , avg(sal) 平均工资
from employee;

-- 2.关于count的使⽤说明
select  count(*),
count(job),
count(distinct job) as 职级种类
from employee;

注意：①count(*) 计算表中总的行数，不管该列是否有值或者为空值
②count(字段) 计算指定列下的总行数，忽略空值的行
③不要使用count(列名)或count(常量)来替代count(*),count(*)是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关。
④count(distinct 字段) 计算该列出NULL之外的不重复行数
⑤当某⼀列的值全是NULL时，count(col)的返回结果为0， 但sum(col)的返回结果为NULL，因此使⽤sum()时需要注意NPE 问题。

2、时间日期函数

时间格式：

DATETIME 举例：2000-12-01 12:18:30
DATE 举例：2000-12-01
YEAR 举例：2000

注：以下代码自己练习输入查看输出结果，这里就不展示啦~

⼀般的日期函数除了使用DATE类型的参数外，也可以使用DATETIME或者TIMESTAMP类型的参数，但会忽略这些值的时间部   分。相似的，以TIME类型值为参数的函数，可以接受TIMESTAMP 类型的参数，但会忽略日期部分，许多日期函数可以同时接受数字   和字符串类型的两种参数。

-- 1.select curdate(),current_date(),curdate()+0;
注：curdate()+0将日期值转换为数值型

-- 2.select curtime(),current_time(),curtime()+0;

-- 3.select
current_timestamp(),localtime(),now(),sysdate();
注：4个函数返回的结果是相同的，格式根据是字符串还是数字语境
中确定。

-- 4.select
unix_timestamp(),now(),unix_timestamp(now());

-- 5.select
from_unixtime(1596098121),from_unixtime('15960981
21');

注：from_unixtime()与unix_timestamp()互为反函数，是⼀对好基友！GMT为格林尼治时间！

-- 6.select utc_date(),utc_date()+0,utc_time();
注：UTC世界标准时间，并不是北京时间。

-- 7.select month('2020-07-30'),monthname('2020-
07-30');

-- 8.select dayname('2020-07-30'),dayofweek('2020-07-30');

-- 9.select weekday('2020-07-30 12:00:20'),weekday('2020-07-30');

10.日期格式化
date_format():格式化输出日期和时间值
time_format():格式化输出时间值

-- 练习：从员工表中查询每个员工的工龄
select hiredate as 入职时间, 
    datediff(current_date,hiredate) as '工龄(天)'
from employee;

3、字符串函数

同上，自己输入看结果哈~

-- 1.字符串长度
select char_length('test'),length('test'),char_length('数据分析'),length('数据分析');

注：char_length()返回值为字符串str所包含的字符个数；
length()返回值为字符串的字节长度，使用utf8编码字符集时，⼀个汉字是3个字节，⼀个数字或字符算1个字节。

-- 2.字符串拼接
select concat('My SQL','8.0'),concat('My',null,'SQL');

-- 3.字符串拼接
select concat_ws('-','1st','2nd','3rd'),concat_ws('*','1st',null,'3r
d');

-- 4.字符串截取
select left('football',3),right('basketball',4);

-- 5.剔除空格
select '( book )',concat('(',ltrim(' book'),')');

注：ltrim(s)返回字符串s，字符串左侧空格字符被删除。
rtrim(s)是删除右边的空格。trim(s)是删除两边的空格。

-- 6.剔除两端指定字符
select trim('ab' from 'abcabaaacbabbbb');
注：剔除字符串两端指定的字符

-- 7.字符串的替换
select
concat('(',space(6),')'),replace('xxx.mysql.com','x','w');
注：SPACE(n)返回⼀个由n个空格组成的字符串；
replace(s,s1,s2)返回字符串s2替代s中所有的s1。

-- 8.求子串substring/mid
select substring('breakfast',5)
col1,substring('breakfast',5,3) col2,substring('lunch',-3) as col3,substring('lunch',-5,3) as col4;
注：substring(s,n,len),从字符串s返回⼀个长度为len，起始位置为n的子串。n若为负值，则从右向左数n为起始位置。若len
小于1，则结果始终为空字符串。

注：substring(s,n,len),从字符串s返回⼀个长度为len，起始位置为n的子串。n若为负值，则从右向左数n为起始位置。若len
小于1，则结果始终为空字符串。

4、数学函数

-- 常见的数学函数
ABS(x):返回绝对值
PI():返回PI，7位有效数字
SQRT(x):返回非负数的平方根
MOD(x,y):返回余数
CEIL(x)/CEILING(x):返回不小于x的最小整数值，返回值转化为
⼀个BIGINT。
FLOOR():返回不大于x的最大整数值，返回值转化为⼀个BIGINT。