mysql基础学习笔记

SQL语句

数据库的数据类型

DDL数据定义语言(Definition)

DDL-数据库操作

• 创建数据库

create database test charset utf8;

• 查看数据库

show databases;

• 使用数据库

use test;

• 删除数据库

drop test;

• 查看当前处于哪个数据库中

select database();

DDL-表操作

show tables;-- 查看当前数据库所有的表
create table 表名(字段 字段类型,字段 字段类型)；-- 创建表
desc 表名; -- 查询当前表的字段
show create table 表名;-- 查询建表语句
alter table 表名 add/modify/change/drop/rename to ...;-- 修改表
drop table 表名;-- 删除表

DML数据操作语言(Manipulation)

DML-数据操作

insert into表名(字段1,字段2...)values(值1,值2...),[...]; -- 添加数据
update 表名 set 字段1 = 值1,字段2 = 值2 [where 条件]; -- 修改数据

delete from 表名 [where 条件]; -- 删除数据

DQL数据查询语言(Query)

DQL语句

• 查询年龄为21，22，23岁的女性员工信息

select * from emp where gender = '女' and age in(21,22,23);

• 查询性别为男，并且年龄在20-40岁(含)以内的姓名为三个字的员工

select * from emp where gender = '男' and (age between 20 and 40) and name like '___';

• 统计员工表中，年龄小于60岁的，男性员工和女性员工的数量

select gender,count(*) from emp where age < 60 group by gender;

• 查询所有年龄小等于35岁员工的姓名和年龄，并查询结果按年龄的升序排序，如果年龄相同按入职时间降序排序

select name,age from emp where age <= 35 order by age asc,entrydate desc; -- asc可以不写，desc必须写

• 查询性别为男，且年龄在20-40岁（含）以内的5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序

select * from emp where gender = '男' and age between and 40 order by age asc,entrydate asc limit 5; -- limit在最后

DQL编写顺序

select -> from -> where -> group by -> having -> order by -> limit

DQL执行顺序

from -> where -> group by -> having -> select -> order by -> limit

DCL数据控制语言(Control)

DCL-管理用户

• 创建用户，只能在当前主机localhost访问，设置密码

create user 'itcast'@'localhost' identified by '123456;'

• 创建用户，可以在任意主机上访问数据库，设置密码

create user 'hello'@'%' identified by '123456';

• 修改用户密码

alter user 'hello'@'%' identified with mysql_native_password by '1234';

• 删除用户

drop user 'itcast'@'localhost';

DCL-权限控制

• 查询权限

show grants for 'hello'@'%';

• 授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'；

grant all on test.Student to 'hello'@'%'; -- 授权时数据库和表名可以使用*进行通配，代表所有

• 撤销权限

revoke all on test.Student from 'hello'@'%'; 

函数

字符串函数

select 函数名();
select lpad('01',5,'-');
select substring('hello Mysql',1,5);

• 将企业员工的工号统一为5位数，不足5位数的全部在前面补0

update emp set workid = lpad(workid,5,'0');

数值函数

• 通过数据库的函数，生成一个六位数的随机验证码

select lapd(round(rand()*1000000,0),0,'0');

日期函数

• 查询所有员工的入职天数，并按时间倒叙排序

select name,datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc;  

流程控制函数

• 查询员工姓名和工作地址（北京/上海—>一线城市，其他—>二线城市）

select
	name,
	(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp;

• 统计班级各个学员的成绩，展示的规则如下：

  > =85,展示优秀

  > =60,展示及格

  否则，展示不及格

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

约束

概述

案例

• 根据需求完成表结构的创建

create table user(
    id int primary key auto_increment comment '主键'，
    name varchar(10) not null unique comment '姓名',-- 约束条件直接打空格
    age int check ( age > 0 && age <= 120 ) comment '年龄',
    status char(1) default '1' comment '状态'，
    gender char(1) comment '性别'
)comment '用户名';

外键约束

添加外键

• 语句

create table 表名(
    字段名 数据类型，
    ...
    [constraint] [外键名称] foreign key (外键字段名) references 主表(主表列名)
);

alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表 (主表列名);

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

删除外键

• 语句

alter table 表名 drop foreign key 外键名称;

删除/更新行为

• 语法

alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表 (主表列名) on update cascade on delete cascade;

多表查询

多表关系

• 一对一

  在多的一方设置外键，关联一的一方的主键

• 多对一

  建立中间表，中间表包含两个外键，关联两张表的主键

• 多对多

  用于表结构拆分，在其中任何一方设置外键(unique),关联另一方的主键

多表查询

select *from emp,dept where emp.dept_id = dept.id; -- 逗号分割表名 条件限制删除无效的笛卡尔积

连接查询

内连接

查询的是两张表交集的部分

• 隐式内连接

select 字段列表 from 表1 , 表2 where 条件...;

• 显式内连接

select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

外连接

• 左外连接

相当于查询表1(左表)的所有数据 包含表1和表2交集部分的数据

select 字段列表 from 表1 left [outer] join 表2 on 条件...;

• 右外连接

相当于查询表1(右表)的所有数据 包含表1和表2交集部分的数据

select 字段列表 from 表1 right [outer] join 表2 on 条件...;

• 查询emp表的所有数据和对应的部门信息(左外连接)

  表结构：emp, dept

  连接条件：emp.dept_id = dept.id

  select e.*,d.name from emp e left join dept d on e.dept_id = d.id;-- 表名太长起别名
  

自连接

自连接可以是内连接也可以是外连接

把自己看作两张表

select 字段列表 from 表A 别名A join 表A 别名B on 条件...;-- 必须起别名

联合查询-union,union all

把多次查询的结果合并起来，形成一个新的查询结果集

多张表的列数必须一直，返回字段必须一直

union会去重

select 字段列表 from 表A...
union[all]
select 字段列表 from 表B...;

子查询

SQL语句中嵌套select语句

外部语句可以是insert/update/delete/select

标量子查询

子查询返回结果是单个值，最简单的形式

常用操作符：= <> < > <= >=

• 查询销售部的所有员工信息

  查询销售部的部门id

  select id from dept where name = '销售部';
  

  根据销售部的部门id，查询员工信息

  select * from emp where dept_id = 4;
  

  select * from emp where dept_id = (select id from dept where name = '销售部');
  

• 查询在"Bob"入职之后的员工信息

  select * from emp where entrydate > (select entrydate from emp where name = 'Bob');
  

列子查询

返回的结果是一列(可以是多行)

常用操作符：in,not in,any,some,all

• 查询销售部和市场部的所有员工信息

  select * from emp where dept_id in (select id from dept where name = '销售部' or '市场部');
  

• 查询比财务部所有人员工资都高的员工信息

  select * from emp where salary > all (select salary from emp where dept_id =(select id from dept where name = '财务部'));
  

行子查询

返回的结果是一行(可以是多列)

常用的操作符 ： = ,<> ,in ,not in

• 查询与"Bob"的薪资及直属领导相同的员工信息

  select * from emp where (salary,managerid) = (select salary,managerid from emp where name = 'Bob');
  

表子查询

返回结果是多行多列

常用操作符：in

• 查询与"Alice","Bob"的职位和薪资相同的员工信息

  select * from emp where (job,salary) in (select job,salary from emp where name = 'Bob' or name = "Alice");
  

• 查询入职日期是"2024-01-01"之后的员工信息

  select e.*,d.* from (select * from emp where entrydate > '2024-01-01') e left join dept d on e.dept_id = d.id;-- 子查询的结果作为一张表
  

  事务

事物操作

方式一

• 查看/设置事物提交方式

select @@autocommit;-- 1为自动提交
set @@autocommit = 0;-- 设置为手动提交

• 提交事务

commit;

• 回滚事务

rollback;-- 出现异常时

方式二

• 开启事务

start transaction 或 begin;

• 提交事务

commit;

• 回滚事务

rollback;-- 出现异常先回滚再提交

事务四大特性

• 原子性
• 一致性
• 隔离性
• 持久性
  

并发事务问题

事务隔离级别

解决并发事务引发的问题

• 查看事务隔离级别

select @@transaction_isolation;

• 设置事务隔离级别

set [session|global] transaction isolation level {read uncommitted|read committed|repeatable read|serializable}-- session当前 global全局

索引

索引是帮助数据库高校获取数据的数据结构

结构

• 每一个节点可以存储多个key(有n个key,就有n个指针)
• 所有的数据都存储在叶子节点，非叶子节点仅用于索引数据
• 叶子节点形成了一颗双向链表，便于数据的排序及区间范围查询

语法

创建索引

create [unique] index 索引名 on 表名(字段名，...);
create index idx_emp_name on tb_emp(name);

• 主键字段，在建表时，会自动创建主键索引
• 添加唯一约束时。数据库实际上会添加唯一索引

查看索引

show index from 表名；

删除索引

drop index 索引名 on 表名;