mysql学习笔记

知识点的讲解：
10.07

1. 数据库
   a) 作用
   i. 数据的持久化
   b) 大的种类
   i. 关系型数据库
2. Mysql/oracle/sqlserver/DB2/SQLite 等
   ii. 非关系型数据库
3. Redis 等
   大数据：HBase/hive 数据仓库
4. MySQL
   a) 学习版本
   i. 5.x  或  8.x
5. MySQL 下载
   a) 官网 https://www.mysql.com/
6. MySQL客户端：Navicat
   a) https://www.navicat.com.cn/
7. MySQL安装
   a) 注意事项：
   i. Mysql端口号：3306
   ii. 密码设置
   iii. 路径
8. 数据库名词
   a) 数据（Data）
   i. 结构化数据与非结构化数据
   ii. 基于表进行存储
   b) 数据库（DB）
   i. 存储数据的仓库就是数据库
   ii. 大数据=》数仓
   c) 数据库管理系统（DBMS）
   i. 管理数据库的系统
   ii. 例如：mysql/oracle 等
9. SQL语言
   a) Structured Query Language:结构化查询语言
   b) 主要用于存取数据以及查询数据、更新和管理关系数据库胸痛的标准语言。
   c) 分类
   i. DDL/DML/DQL/DCL/TCL
10. 连接数据
    a) Cmd命令行的形式
    i. 公式：mysql –u用户名 –p
    ii. 公式：mysql –h地址 –P端口号 –u用户 –p密码
11. 例如：mysql –h192.168,194.100 –P3306 –uroot -p
    b) 客户端形式：navicat
12. 数据库操作
    a) 查看所有的数据库
    i. show databases;
    b) 创建数据库
    i. Create database 数据库名;
    ii. Create database 数据库名 character set ‘字符集’
    iii. Create database if not exists 数据库名
    iv. Create database if not exists数据库名 character set ‘字符集’
    c) 查看字符编码
    i. show create database 数据库名;
    d) 修改数据库字符编码
    i. alter database 数据库名 character set ‘字符集’
    e) 查看当前使用的数据库
    i. select database();
    f) 切换数据库
    i. use 数据库名;
    g) 删除数据库
    i. drop database 数据库名;
    ii. drop database if exists 数据库名;
13. 创建用户
    a) Create user ‘用户名’@’host’ identified by ‘密码’
    i. 解读：host：表示该用户在哪个主机上可以登录，如果是本地用户则用localhost，如果远程访问则用 %
14. 删除用户
    a) drop user ‘用户名’@’host’
15. 创建用户的同时进行权限的设置（根据不同的数据库版本）
    a) Grant 权限 on 数据库.表 to ‘用户名’@‘登录主机’ identified by ‘密码’

10.08
知识点讲解：

1. 简单查询：Select
   a) Select 列名 from 表名
   b) Select 列名1，列名2，… from 表名
   c) Select * from 表名
2. 算术运算符
   a) 包含 + - * /
   b) 优先级
   c) 例如：
   i. Select sal*12 from t_emp;
   ii. Select sal_12+500_12 from t_emp;
   iii. Select (sal+500) from t_emp;
3. 别名设置： as
   a) Select 列名 as ‘别名’ from 表名
   b) Select 列名 from 表名 as 别名
   c) Select 别名.列名 from 表名 as 别名
   d) 备注：
   列设置别名时as不要省略，表设置别名时as可以省略
4. 消除重复项： distinct
   a) Select distinct 列1 from 表名
5. 条件查询
   a) 公式：
   Select 列名1, 列名2,…… from 表名 where 条件表达式
   i. 条件表达式中包含：比较运算符
   =、>、<>、!= 等
   b) 条件表达式中可以使用and 和 or 关键字
   i. And： 将查询条件进行并列
   Select * from 表名 where 列1=数据 and 列2=数据
   ii. Or：或者关系
   Select * from 表名 where 列1=数据 or 列2=数据
   c) in
   select * from 表名 where 列名 in(数据1，数据2，……)
   d) not in
   select * from 表名 where 列名 not in(数据1，数据2，……)
   e) between…and…… （闭区间）
   select * from 表名 where 列名 between 数据1 and 数据2；
   select * from t_emp where (sal between 2000 and 4000) and sal not in(2000,4000);

10.09
知识点讲解：

1. 排序查询
   a) 公式
   i. Select 列1 from 表名 where 条件语句 order by 列名 [ASC|DESC]
   ii. Select 列1 from 表名 where 条件语句 order by 列名 [ASC|DESC], 列名 [ASC|DESC]
   iii. 备注：1. 默认升序 2.ASC:升序  3.DESC：降序
   b) 多使用列的别名
2. 分组查询
   a) 分组函数(搭配distinct 进行使用)
   i. Max :最大值
   ii. Min：最小值
   iii. Sum：和
   iv. Count：统计个数
   v. Avg：平均数
   b) Count():统计总个数
   i. Count(列)
   ii. Count(*)
   iii. Count(1)
   c) 公式：
   Select 列名，分组函数(列名) from 表名
   Where 条件语句
   Group by 列名1，列名2
   Order by 列名 [ASC|DESC]
   d) 备注：分组查询时，查询的列名必须在group by后面进行包含。
   e) 案例
   Select count(distinct name) from t_emp;
   f) 需求：查询哪个部门的最高工资大于3000
   select deptno,max(sal) as msal from t_emp where msal>3000 group by deptno;  （错误）
3. 分组结果的过滤
   a) 公式
   Select 列 from 表名
   Where 条件语句
   Group by 列名
   Having 分组结果
   Order by 列
   b) 需求：查询哪个部门的最高工资大于3000
   select deptno,max(sal) as msal from t_emp group by deptno having msal>3000;
4. 分组函数中空值的处理
   a) IFNULL(表达式1，表达式2)
   如果表达式1的值为NULL，则取表达式2的值
   b) select sum(IFNULL(sal,0)) from t_emp;
5. 模糊查询，作为查询条件
   a) like
   b) 公式：
   Select * from 表名
   Where name like ‘内容’
   备注：内容这里一般使用通配符 %
   c) 查看名字中包含a字母的学生信息
   select * from t_student where name like ‘%t%’;
   select * from t_student where name like ‘%t’;
   select * from t_student where name like ‘t%’;
6. DML数据操作
   a) 主要操作：增删改
   b) 插入数据
   i. Insert into 表名(列名1，列名2，列名3，……)values(值1，值2，值3，……)
   ii. Insert into 表名values(值1，值2，值3，……)
   iii.
   c) 插入时间字段
   i. 时间字段：datetime、date、year 等
   ii. INSERT INTO T_STUDENT(ID,LOGTIME3)VALUES(8,SYSDATE());
   d) 补充：获取当前的时间
   i. Select SYSDATE（）；
   e) 批量插入
   Insert into 表(列1，列2，列3,……)
   Values(值1，值2，值3，……),
   (值1，值2，值3，……),
   (值1，值2，值3，……)
   f) 插入查询之后的结果
7. Insert into 表(列1，列2，列3,……) select 列1，列2，列3,…… from 表
8. 修改：update
   a) 公式：
   Update 表名 set 列1=数据，列2=数据，列3=数据，……

Update 表名 set 列1=数据，列2=数据，列3=数据，…… where 条件语句
a) 在原有的数据基础上进行修改，例如：薪资全部+500
update t_emp set sal=IFNULL(sal,0)+500;

9. 删除：delete
   a) 删除的是整条记录
   b) 公式
   Delete from 表名
   Delete from表名 where 过滤条件
   c) 补充：
   i. Truncate table 表名；
   截断表
   d) Truncate与delete区别
   i. Truncate是DDL命令，数据被删除之后不能通过rollback进行回滚，delete是DML命令，数据被删除之后可以使用rollback回滚。
   ii. Delete可以通过条件语句进行部分数据的删除，truncate不能进行有条件的删除
10. 约束管理
    a) 理解：给列设置规则
    b) 用来保证数据库中数据的正确性、有效性、完整性。
    c) 约束的种类：列级别、表级别
    d) 数据库约束：6类
    i. Primary key,unique,not null,check,foreign key,default
11. 主键约束：primary key
    a) 表中的唯一标识，一般情况主键为id
    b) 主键约束两种限制：
    i. 不能为空，唯一的
    c) 公式：
    Create table 表名(
    id int primary key,
    列名 数据类型 [约束]，
    列名 数据类型 [约束]，
    ……
    )
    d) 删除主键
    Alter table 表名 drop primary key；
    e) 追加主键
    i. Alter table 表名 add primary key(id)；
12. 主键自增策略：auto_increment
    a) 公式
    Create table 表名(
    Id int primary key auto_increment,
    列 数据类型，
    ……
    )

10.10
知识点讲解：

1. 问题处理：别名的使用？
   a) 对列与表进行别名的设置
   b) 别名：小名，当表名或列名复杂的时候，则会对列名与表名起别名。

2. 唯一约束：unique key
   a) 注意点：
   i. 一个表中可以有多个唯一约束的列
   ii. 唯一约束的值可以是NULL
   b) 公式
   Create table 表名(
   Id int primary key auto_increment,
   列名 数据类型 unique,
   ……
   )
   c) 删除唯一约束
   i. alter table 表名 drop index 列名；
   d) 新增唯一约束
   i. alter table 表名 add unique(列名)；
   e)
3. 非空约束 NOT NULL
   a) 公式：
   Create table 表名（
   Id int primary key auto_increment,
   列名1 数据类型 NOT NULL，
   ……
   )

b) 案例：（注意这几种空的判别）

c) 删除非空约束
i. alter table 表名 modify 列名 数据类型 NULL/NOT NULL；
4. 检查约束：check
a) 公式
Create table 表名(
Id int primary key auto_increment,
列名 数据类型 CHECK(表达式)，
……
)
b) 案例：设置学生表的性别只能为男或女（0或1）

c) 案例：设置学生表的年龄只能在19-25之间

5. 默认值 ：default
   a) 公式
   Create table 表名(
   Id int primary key auto_increment,
   列名 数据类型 default 默认值，
   ……);
   b) 案例：
6. 外键约束：foreign key
   a) 作用：限制外键所定义的列的值必须来源于引用列的值
   b) 公式：(需要添加外键的表)
   Create table 表名（
   Id int primary key auto_increment,
   列 数据类型，
   外键列 数据类型，
   Foreign key(外键列) references 主表(引用列)
   ）
   c) 案例：
   班级表：（id，班级名称）
   学生表：（id，学生姓名，年龄，性别，班级ID）
   关联关系：学生表：班级ID = 班级表:ID

d) 删除外键
alter table 表名 drop foreign key 约束名;
//-查看约束名称
show create table 表名;

7. 表级别约束
   a) 公式
   b) 约束名
   Constraint 约束名 约束类型(列名)
   c) 案例

d) 两个外键关联表，先删除子表，再删除主表

8. 连接查询（多表查询）
   a) 从多个表中获取数据
   b) 连接查询的公式
   Select 表1.列名，表2.列名 from 表1，表2
   Where 表1.列名 = 表2.列名
   c) 连接查询的种类
   i. 按照年份：SQL92 / SQL99
   ii. 按表的连接方式
9. 内连接
   a) 等值连接
   b) 非等值连接
   c) 自连接
10. 外连接
    a) 左外连接
    b) 右外连接
11. 全连接（不讲）
    d) 笛卡尔积
    i. 避免出现可以使用条件语句进行关系查询
12. 内连接之等价连接
    a) 特点：连接条件是等价的
    b) 使用：=
    c) 显示学生对应的班级名称

d) 显示一班学生的名字及对应的班级名

e) 三个表关联


==92=
select st.name as ‘sname’,sc.score,c.name as ‘cname’ from t_class c,t_student st,t_score sc where c.id=st.classid and st.id=sc.stuid;
99

10. 内连接之不等价连接
    a) 特点：获取某个范围内的数据
    i. 比较运算符/in/not in/between…and
    b) 案例：获取分数再30-40之间的学生信息
    select st.* from t_student st,t_score sc where st.id=sc.stuid and sc.score between 30 and 50;

10.11

1. 补充：like
   a) 通配符：%     _
   i. %: 代表0个或多个字符
   ii. _ : 表示一个字符
   b) 案例：查询名字中包含t的所有学生信息
   select * from t_student where name like ‘%t%’;
   c) 案例：查询名字第二个字母是a的学生信息
   select * from t_student where name like ‘_a%’
2. 自连接
   a) 理解：自己连接自己

3. 外连接
   a) 场景：查询一个表中有，一个表中没有的记录
   b) 特点：
   i. 外连接的查询结果为主表中所有的记录
   如果从表中有和主表数据匹配的，则显示从表中匹配的数据
   如果从表中没有和主表数据匹配的，则显示NULL
   c) 左外连接：left join /left outer join 左边的是主表
   d) 右外连接：right join/right outer join 右边的是主表
   e) 全连接：内连接的结果+主表中有但是从表中没有的+从表中有的主表中没有的
   f) 案例：查询所有员工对应的领导名称
   select yg.name as ‘ygname’,ld.name as ‘ldname’  from t_emp yg left outer join t_emp ld on yg.mgrid=ld.id;
   select yg.name as ‘ygname’,ld.name as ‘ldname’  from t_emp ld right outer join t_emp yg on yg.mgrid=ld.id;
4. 联合查询
   a) 关键字：union           union all
   b) 理解：将多个查询结果合并起来，形成一个新的查询结果
   i. Union 将结果中重复的数据消除掉，union all 不会消除重复项
   c) 公式：
   Select 字段列表 from 表A
   union [all]
   Select 字段列表 from 表B
   d) 案例：查询领导编号为1的员工信息与名字中包含t字母的所有员工信息
5. 分页查询：Limit
   a) 公式：
   Select 字段列表 from 表名
   Limit [start,]length;
   备注：start 从第几行(索引)记录开始数据，默认是0
   length：输出的行数
   b) 案例：每页显示5条记录，查询第三页的数据
   i. 0-5-10  ：（page-1）*pagesize
   ii. Select * from 表名 limit 0,5;
   iii. Select * from 表名 limit 10,5;
6. 子查询
   a) 需求：查看哪些人的工资比petter的工资高
   b) 需求：查看哪个员工的工资最高
   c) 子查询公式
   Select 字段列表 from 表 where 列 操作（select 字段列表 from 表名）
   d) 查看哪些人的工资比petter的工资高
   i. 分别查询

Petter工资 =>3000
Select sal from t_emp where name=’petter’
哪些人的工资比petter高
Select * from t_emp where sal>3000
ii. 合并语句：子查询
Select * from t_emp where sal>(Select sal from t_emp where name=’petter’)
e) 子查询中常用的运算符（子查询中返回的结果）
i. 单行运算符：>、 =、 >=、<、<=、<>
ii. 多行运算符:in 、any 、all
iii. 子查询返回多行单列
哪些员工的薪资与名字中带p字母的员工薪资相同
f) Having中可以使用子查询
select deptno,min(sal) from t_emp group by deptno having min(sal)>(select min(sal) from t_emp where deptno=10);
g) From 后面可以使用子查询
select * from t_emp e,(select deptno,avg(sal) as ‘ag’ from t_emp group by deptno) d where e.deptno = d.deptno and e.sal>d.ag;

7. 多行子查询
   a) 与多行子查询配合使用的关键字：
   i. in       any        all
   ii. in 与之前的使用方式相同/ not in
   b) in： 案例 ：查询领导的信息（name/sal）
   select * from t_emp where id in(select distinct(mgrid) from t_emp where mgrid is NOT NULL);
   c) any
   i. 表示和子查询的任意一行结果匹配上的即可
   ii. =any(子查询)    类似于：in
   iii. >any(子查询)
8. 大于子查询结果中的任意一个
   iv. <any(子查询)
9. 小于子查询结果中的任意一个
   d) 案例：
   select name,sal from t_emp where id = any(select mgrid from t_emp)
   e) All：表示和子查询中所有的记录进行比较，每一行都必须满足。
   i. >all: 大于子查询中所有的行
   ii. <all: 小于子查询中所有的行
   iii. =all：等于子查询中所有的行

10.12
知识点讲解：

1. 内置函数：字符串函数
   a) concat() :连接字符串
   b) instr(str,substring)
   c) ucase() :
   d) lcase():
   e) left(string,length)
   f) right(string,length)
   g) length(string)
   h) replace(str,oldstr,newstr)
   i) substring(str,index,length)
   i. length不指定，则截取到末尾
   j) ltrim(str)   rtrim(str)  trim(str)
   k) 需求：要求第一个字母大写，其他字母小写
   select concat(ucase(substring(name,1,1)),lcase(substring(name,2))) from t_emp;
2. 数学函数
   a) abs()
   b) ceiling()   向上取整
   c) floor()     向下取整
   d) format(number,decimal_places) :格式化保留小数的位数
   e) rand()   返回随机数，浮点型，(0.0-1.0)
   i. 获取100以内整数的随机数
   select format(rand()*100,0);
   f) mod(num,denominator)
3. 其他函数
   a) Show databases;
   b) Show tables;
   c) Select database();
   d) Select user();
   e) ifnull