MySQL知识点笔记_update emp set workno = lpad(workno,5,'0');-优快云博客

本文链接：https://blog.youkuaiyun.com/exercise_smile/article/details/138811397

参考视频：黑马程序员MySQL教学

1.MySQL概述

数据库概念

Oracle：大型的收费数据库，Oracle公司产品，价格昂贵。
MySQL：开源免费的中小型数据库，后来Sun公司收购了MySQL，而Oracle又收购了Sun公司。目前Oracle推出了收费版本的MySQL，也提供了免费的社区版本。
SQL Server
DB2
…

不论我们使用的是上面的哪一个关系型数据库，最终在操作时，都是使用SQL语言来进行统一操作，因为我们前面讲到SQL语言，是操作关系型数据库的 统一标准。

MySQL数据库

启动MySQL：

[]内为可选参数，如果需要连接远程的MySQL，需要加上这两个参数来指定远程主机IP、端口，如果连接本地的MySQL，则无需指定这两个参数。

注意：使用这种方式进行连接时，需要安装完毕后配置PATH环境变量。

关系型数据库：概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

特点：
A. 使用表存储数据，格式统一，便于维护。
B. 使用SQL语言操作，标准统一，使用方便。

2.SQL

SQL通用语法

SQL语句可以单行或多行书写，以分号结尾。
SQL语句可以使用空格/缩进来增强语句的可读性。
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
注释：

单行注释：-- 注释内容或 # 注释内容
多行注释：/* 注释内容 */

SQL分类

DDL

数据库操作

-- 查询所有数据库
show databases;
-- 查询当前数据库
select database();
-- 创建数据库
create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序规则 ] ;
-- 删除数据库
drop database [ if exists ] 数据库名 ;

-- 切换数据库
# 我们要操作某一个数据库下的表时，就需要通过该指令，切换到对应的数据库下，否则是不能操作的。
user 数据库名;

表操作

-- 建表
CREATE TABLE 表名(
	字段1 字段1类型 [ COMMENT 字段1注释 ],
	字段2 字段2类型 [COMMENT 字段2注释 ],
	字段3 字段3类型 [COMMENT 字段3注释 ],
	......
	字段n 字段n类型 [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ] ;
-- 查询建表语句 
show create table 表名;
-- 查询指定表结构
desc 表名;
-- 查询当前数据库所有表
show tables;

数据类型

数值类型：

字符串类型：

日期类型：

表操作-修改

-- 添加字段
ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';
-- 修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);
-- 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
-- 删除字段
ALTER TABLE 表名 DROP 字段名;
-- 修改表名
ALTER TABLE 表名 RENAME TO 新表名;

表操作-删除

-- 删除表
DROP TABLE [ IF EXISTS ] 表名;
-- 删除指定表, 并重新创建表 (清空表数据)
TRUNCATE TABLE 表名;

注意: 在删除表的时候，表中的全部数据也都会被删除。

DML

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作。

-- 添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
-- 给全部字段添加数据
INSERT INTO 表名 VALUES (值1, 值2, ...);

-- 批量添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

注意：

插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
字符串和日期型数据应该包含在引号中。
插入的数据大小，应该在字段的规定范围内。

-- 修改数据
UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ;

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

DELETE FROM 表名 [ WHERE 条件 ] ;

注意事项:

DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
DELETE 语句不能删除某一个字段的值(可以使用UPDATE，将该字段值置为NULL即可)。
当进行删除全部数据操作时，DataGrip会提示我们，询问是否确认删除，我们直接点击Execute即可。

DQL

基础查询

-- 查询多个字段
SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;
SELECT * FROM 表名 ;

注意 : * 号代表查询所有字段，在实际开发中尽量少用（不直观、影响效率）。

-- 设置别名
SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;
# as 可省略
SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;
-- 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;

条件查询

SELECT 字段列表 FROM 表名 WHERE 条件列表 ;

案例：

# 查询年龄在15岁(包含) 到 20岁(包含)之间的员工信息
select * from emp where age between 15 and 20;
# 查询年龄等于18 或 20 或 40 的员工信息
select * from emp where age in(18,20,40);
# 查询身份证号最后一位是X的员工信息
select * from emp where idcard like '%X';
select * from emp where idcard like '_________________X';

聚合函数

将一列数据作为一个整体，进行纵向计算。

 SELECT 聚合函数(字段列表) FROM 表名 ;

注意 : NULL值是不参与所有聚合函数运算的。

分组查询

-- 语法
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];

where与having区别

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
判断条件不同：where不能对聚合函数进行判断，而having可以。

注意事项:

• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

• 执行顺序: where > 聚合函数 > having 。

• 支持多字段分组, 具体语法为 : group by columnA,columnB

案例:

-- 根据性别分组 , 统计男性员工 和 女性员工的平均年龄
select gender, avg(age) '平均年龄' from emp group by gender ;

-- 查询年龄小于45的员工 , 并根据工作地址分组  , 获取员工数量大于等于3的工作地址
select workaddress, count(*) address_count from emp where age < 45 group by workaddress having address_count >= 3;
-- 统计各个工作地址上班的男性及女性员工的数量
select workaddress, gender, count(*) '数量' from emp group by gender , workaddress;

排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;

ASC : 升序(默认值)
DESC: 降序

注意事项：

如果是升序, 可以不指定排序方式ASC ;

如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 ;

分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;

注意事项:

起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。

分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。

如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

执行顺序

DCL

DCL英文全称是Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访问权限。

管理用户

-- 查询用户
select * from mysql.user;
-- 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
-- 修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;
-- DROP USER '用户名'@'主机名' ;
DROP USER '用户名'@'主机名' ;

注意事项:

在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户。

主机名可以使用 % 通配。

这类SQL开发人员操作的比较少，主要是DBA（ Database Administrator 数据库
管理员）使用

权限控制

MySQL中定义了很多种权限，但是常用的就以下几种：

权限	说明
ALL, ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名' ;
-- 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
-- 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意事项：

多个权限之间，使用逗号分隔。

授权时，数据库名和表名可以使用 * 进行通配，代表所有。

3.函数

字符串函数

函数	功能
CONCAT(S1,S2,…Sn)	字符串拼接，将S1，S2，… Sn拼接成一个字符串
LOWER(str)	将字符串str全部转为小写
UPPER(str)	将字符串str全部转为大写
LPAD(str,n,pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str,n,pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str)	去掉字符串头部和尾部的空格
SUBSTRING(str,start,len)	返回从字符串str从start位置起的len个长度的字符串

案例：

# 由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如： 1号员工的工号应该为00001。
update emp set workno = lpad(workno, 5, '0');

数值函数

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	返回x/y的模
RAND()	返回0~1内的随机数
ROUND(x,y)	求参数x的四舍五入的值，保留y位小数

日期函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date, INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1,date2)	返回起始时间date1 和结束时间date2之间的天数

流程函数

函数	功能
IF(value , t , f)	如果value为true，则返回t，否则返回 f
IFNULL(value1 , value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN [ val1 ] THEN [res1] … ELSE [ default ] END	如果val1为true，返回res1，… 否则返回default默认值
CASE [ expr ] WHEN [ val1 ] THEN [res1] … ELSE [ default ] END	如果expr的值等于val1，返回 res1，… 否则返回default默认值

案例1：

# 需求：查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)  
select
	name,
	( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else'二线城市' end ) as '工作地址'
from emp

案例2：

select
	id,
	name,
	(case when math >= 85 then '优秀' when math >=60 then '及格' else '不及格' end )'数学',
	(case when english >= 85 then '优秀' when english >=60 then '及格' else '不及格'end ) '英语',
	(case when chinese >= 85 then '优秀' when chinese >=60 then '及格' else '不及格'end ) '语文'
from score;