Mysql基础知识+部分进阶知识

MySQL

1.数据库常用黑窗口命令

1.启动与停止

cmd窗口：services.msc可以开启数据库

客户端连接
黑窗口：mysql -u root -p黑窗口登录数据库

2.配置Datagrip时区

https://blog.csdn.net/qq_22136439/article/details/106429131

一、基础篇

1.MySQL概述

1. 数据库：数据存储的仓库
2. 数据库管理系统：操纵和管理数据库的大型软件
3. SQL：操作关系型数据库的编程语言，是统一标准

数据模型：二维表，类似excel表格

2.SQL语句!

• SQL通用语法
• SQL分类
• DDL
• DML
• DQL
• DCL

1.通用语法

1. SQL语句可以单行或多行书写，分号结尾
2. SQL语句可以使用空格缩进来增强语句的可读性
3. MySQL数据库的SQL语句部分大小写，关键字建议大写
4. 注释：
   1. 单行：–或#
   2. 多行：/注释内容/

2.SQL分类

3.DDL

数据库定义语言 定义数据库对象（数据库， 表，字段）

1.库操作

1. 查询 ：
   1. 查所有数据库：show databases
   2. 查当前：select database（）
2. 创建：
   1. create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则]
   2. 字符集就是utf8这样，但一般用utf8mb4，可以储存4个字节，空间更大
3. 删除
   1. drop database[if exists 数据库名]
4. 使用
   1. use 数据库名

2.表操作-查询

1. 查询当前数据库所有表
   1. show tables；
2. 查询表内结构
   1. desc 表名；
3. 查询指定表的建表语句
   1. show create table 表明；
4. 查看当前表内数据
   1. select * from 表名

3.表操作-创建

create table 表名（
	字段1 字段1类型[comment 字段1注释]， comment就是表示后面输入的是注释
	字段2 字段2类型[comment 字段2注释]，
	字段3 字段3类型[comment 字段3注释]，
	...
	字段n 字段n类型[comment 字段n注释]
）[comment 表注释]
# 添加内容
insert into 表名(字段1,字段2...) value (字段1val,字段2cal,....);

4.表操作-数据类型

1. 数值类型
   

2. 字符串类型
   

3. 日期时间类型
   

5.表操作-修改

1. 添加字段
   alter table 表名 add 字段名 类型（长度）[comment 注释] [约束];
2. 修改数据类型 修改原字段的数据类型
   alter table 表名 modify 字段名 新数据类型（长度）;
3. 修改字段名和字段类型
   alter table 表名 chagne 旧字段名 新字段名 类型（长度）[comment 注释] [约束];
4. 删除字段
   alter table 表名 drop 字段名；
5. 修改表名
   alter table 表名 rename to 新表名；

6.表修改-删除

1. 删除表
   drop table [if exists] 表名;
2. 删除指定表，并重新创建该表 等同于把表中的数据全部删除
   truncate table 表名；

4.DML

• 数据库操作语言 用来对数据库中表的数据记录进行增删改操作

1. 添加数据（insert）
   1. 给指定字段添加数据 insert into 表名（字段名1，字段名2，…） values （值1，值2，…）；
   2. 给全部字段添加数据 insert into 表名 values（值1，值2，…）
   3. 批量添加数据
      1. insert into 表名 (字段名1,字段名2…) values(值1,值2，…),(值1，值2，…),…；
      2. insert into 表名 values (值1,值2),(值1,值2,…),(值1,值2,…); 值需要和字段名一一对应,更便利了,多条数据的值用逗号隔开,可以单次插入多条数据
2. 修改数据（update）
   1. update 表名 set 字段名1 = 值1,字段名2 = 值2,…[where 条件];
   2. 如果不带where语句,会把证章表的内容都改变,因此最好给定修改限制
3. 删除数据（delete）
   1. delete from 表名 [where 条件]
   2. 如果没有条件则会删除整张表的所有数据
   3. delete语句不能删除某一个字段的值(可以使用update)

5.DQL!

• 数据查询 关键字 select

DQL-语法

	/*----------基本查询------------*/
select 
	字段列表
from
	表名列表
	/*----------条件查询------------*/
where	
	条件列表
	/*----------分组查询------------*/
group by
	分组字段列表
having
	分组后条件列表
	/*----------排序查询------------*/
order by
	排序字段列表
	/*----------分页查询------------*/
limit
	分页参数
	/*----------聚合函数 count max min avg sum------------*/

1.基本查询

1. 查询多个字段
   1. select 字段1,字段2,字段3… from 表名;
   2. select * from 表名; 返回所有字段
2. 设置别名
   1. select 字段1[as 别名1],字段2[as 别名2]…from 表名; as可以省略
3. 去除重复记录
   1. select distinct 字段列表 from 表名

2.条件查询

语法:

select 字段列表 from 表名 where 条件列表;

条件:

-- 条件查询
-- 1.查询年龄大于30岁以上的/年龄小于20岁的
select  name,age from emp where age between 20 and 30;
-- 2.查询姓名是两个字的 用like占位符判断 _匹配单个的字符,%匹配任意的字符,如果出现就行
select  * from  emp where  name like '__';
-- 3.查询身份证最后一位是X的数据
select  * from  emp where  idcard like '%X';
-- 4.查询年龄20岁,38岁,18岁的人
select * from emp where age = 20 || age = 38 || age = 18;
select * from emp where age in (20,38,18);//用in来进行多选1

4.聚合函数

1. 介绍:将一列数据作为一个整体,进行纵向计算

2. 常见聚合函数

   1. 函数	功能
      count	统计数量
      max	最大值
      min	最小值
      avg	平均值
      sum	求和

3. 语法:select 聚合函数(字段列表) from 表名

4. 注意:所有的null值不参与聚合函数运算

-- 聚合函数
-- 1.计算表中的数据之和
select count(*) from emp;

-- 2.统计该企业员工的平均年龄
select avg(age) from emp;

-- 3.统计该企业员工的最大年龄
select max(age) from emp;

-- 4.统计最小的年龄
select min(age) from emp;

-- 5.统计北京地区员工的年龄之和
select sum(age) from emp where workaddress = '北京';

5.分组查询

1. 语法 select 字段列表 from 表名 [where 条件] group by 分组字段名[having 分组后过滤条件];
2. where和having的区别
   1. 执行时机:where是分组之前进行过滤,不满足where条件不参与分组;而having 是分组之后对结果进行过滤
   2. 判断条件不同:where里不能写聚合函数进行判断,而having可以
   3. 注意:
      1. 执行顺序:where > 聚合函数 > having
      2. 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义

-- 分组查询
-- 1.根据性别分组,统计男性员工 和 女性员工的 数量(count)
select gender, count(gender)
from emp
group by gender;

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

-- 3.查询年龄小于30的员工,并根据工作地址分组,获取员工数量大于等于2的工作地址
select workaddress, count(*) as adddress_count
from emp
where age < 30
group by workaddress
having adddress_count >= 2;

6.排序查询

1. 语法 select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;
2. 排序方式
   1. ASC:升序(默认)
   2. DESC:降序
3. 注意:如果是多字段排序,当地一个字段值相同时,才会根据第二个字段进行排序

-- 排序查询
-- 1.根据年龄对公司的员工进行升序排序
select * from emp order by age;

-- 2.根据入职时间,对员工进行降序排序
select * from emp order by entrydate DESC ;

-- 3.根据年龄对公司员工进行升序排序,年龄相同,再按照入职时间进行降序排序
select * from emp order by age ,entrydate desc ;

7.分页查询

1. 语法 select 字段列表 from 表名 limit 起始索引,查询记录数;
2. 注意:
   1. 起始索引从0开始,起始索引 = (查询页码 - 1) * 每页显示记录数
   2. 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中的limit
   3. 如果查询的是第一页数据,起始索引就可以省略,直接简写为limit10

-- 分页查询
-- 1.查询第一页员工数据,每页展示10条记录
select * from emp limit 0,10;

-- 2.查询第二页员工数据,每页展示10条记录
select * from emp limit 10,10;

8.案例练习

-- 1.查询年龄为20,21,22,23岁的员工信息
select *
from emp
where age in (20, 21, 22, 23);
-- 2.查询性别为男,并且年龄在20-40岁(含)以内的姓名为三个字的员工
select *
from emp
where gender = '男' && name like '___' && age between 20 and 40;
-- 3.统计员工表中,年龄小于30岁的男性员工和女性员工的人数 分组查询
select gender, COUNT(gender)
from emp
where age < 30
group by gender;
-- 4.查询所有年龄小于等于20岁员工的姓名和年龄,并对查询结果按年龄升序排序,如果年龄相同按入职时间降序排序
select name, age
from emp
where age <= 20
order by age asc, entrydate desc;
-- 5.查询性别为男,且年龄在15-35岁以内的前五个员工信息(利用分页查询让五个人显示出来),对查询的结果按年龄升序排序,年龄相同按入职时间升序排序
select *
from emp
where gender = '男' || age between 15 and 35
order by age asc, entrydate desc limit 0,5;

9.DQL执行顺序

1. 编写顺序:
   1. select 字段列表
   2. from 表名列表
   3. where 条件列表
   4. group by 分组字段列表
   5. having 分组后条件列表
   6. order by 排序列表
   7. limit 分页参数
2. 执行顺序
   1. from 知到查那个表
   2. where 查询的条件
   3. group by 分组的字段
   4. select 字段列表
   5. order by 排序
   6. limit 分页参数
3. 在执行顺序之前的执行中定义的名称可以在其之后的执行起作用,后执行设置的名称无法应用在前面执行的

6.DCL(data control language)

• 用来管理数据库用户,控制数据库的访问 权限.

1.DCL-管理用户

1. 查询用户
   1. use MySQL; select * from user;
2. 创建用户
   1. create user ‘用户名’@‘主机名’ identified by ‘密码’;
3. 修改用户密码
   1. alter user ‘用户名’@‘主机名’ identified with mysql_native_password by ‘新密码’;
4. 删除用户
   1. drop user‘用户名’@‘主机名’;

2.DCL-权限控制

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LnOFbbuA-1658802129969)(D:\Typora\image-20220705171555613.png)]

1. 查询权限
   1. show grants for ‘用户名’@‘主机名’;
2. 授予权限
   1. grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;
3. 撤销权限
   1. revoke 权限列表 on 数据库名,表名 from ‘用户名’@‘主机名’;

3.函数

• 函数指一段可以直接被另一段程序调用的程序或代码

1.字符串函数

函数	功能
concat(s1,s2,…sn)	字符串拼接,将s1,s2,s3…拼接成一个字符串
lower(str)	将字符串str转化成小写
upper(str)	将字符串str转化成大写
lpad(str,n,pad)	左填充,用字符串pad对str左边进行补充,达到n个字符长度
rpad(str,n,pad)	右填充,用字符串pad对str右边进行补充,达到n个字符长度
substring(str,start,len)	返回从字符串str从start位置起的len个长度的字符串

-- 字符串方法
-- 1.concat 连接字符串
select concat('Hello',' mysql'); //Hello mysql
-- 2.lower 转化小写
select lower('HELLO'); //hello
-- 3.upper 转化大写
select upper('hello'); //HELLO
-- 4.lpad 左填充
select lpad('1',5,'-'); //----1
-- 5.rpad 右填充
select rpad('1',5,'-');//1----
-- 6.清除空格
select trim('    123     321      ');//123     321
-- 7.jsubstring 返回字符串长度 
select substring('crz say hello',1,3); //crz

-- 案例 企业员工的工号统一改为6位数,不足6位数的左边补0 比如 000001
update emp set workno = lpad(workno,6,'0');

2.数值函数

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x,y)	返回x/y的模(取余)
rand()	返回0~1的随机数
round(x,y)	求参数x的四舍五入的值,保留y位小数

-- 数值函数
-- 1.ceil 向上取整
select ceil(2.3);
-- 2.floor 向下取整
select floor(2.3);
-- 3.mod(x,y) 返回x/y的模 取余
select mod(8,4);
-- 4.rand() 返回0~1内的随机数
select rand()*10;
-- 5.round(x,y) 球参数x的四舍五入的值,保留y位小数
select round(3.1415,2);
-- 案例 利用函数随机生成一个六位数的验证码
select lpad(round(rand()*1000000,0),6,'0');

3.日期函数

函数	功能
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
year(date)	获取制定date的年份
month(date)	获取制定date的月份
day(date)	获取制定date的日期
date_add(date,interval expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
datediff(date1,date2)	返回起始时间date1 和 结束时间date2 之间的天数 date1 - date2

-- 案例 查询所有员工的入职天数,并根据入职天数倒序排序
select name,datediff(curdate(),entrydate) as 'entryday' from emp order by entryday DESC;