MySQL学习笔记（基础入门篇）

MySQL学习笔记(基础篇)

SQL

• SQL通用语法

1、SQL可以单行或多行书写，以分号结尾

2、SQL可以用空格/缩进来增强语句可读性

3、MySQL数据库的SQL语句不区分大小写，关键字建议用大小写

4、单行注释–或#，多行注释/* */

• SQL分类

1、DDL—数据定义语言，定义对象

2、DML—数据操作语言，对数据增删改

3、DQL—数据查询语言，查询表的记录

4、DCL—数据控制语言，创建用户、控制访问权限

DDL

• DDL-数据库操作

查询所有数据库
show databases;

查询当前数据库
select database();

创建
create database [if not exists] 数据库名;

删除
drop database [if exists] 数据库名;

使用
use 数据库名;

• 创建表

几个注意的点：1、创建字符串类型用varchar(num) 2、comment 之后为注释，可写可不写 3、最后一个字段没有括号4、逗号和分号都要是英文输入5、单词一定不能打错比如create和comment

只要错一个点，表就建立不起来

• DDL-表操作-查询

查询当前数据库所有表
show tables;

查询表结构
desc 表名;

查询指定表的建表
show create table 表名;

• DDL-表操作-修改

添加字段
alter table 表名 add 字段名 类型 [comment][约束];

修改数据类型
alter table 表名 modify 字段名 新数据类型;

修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 新类型;

删除字段
alter table 表名 drop 字段名;

修改表名
alter table 表名 rename to 新表名

• DDL-表操作-删除

删除表，全部删除
drop table [if exists] 表名;

删除指定表，并重新创建该表，只删数据
truncate table 表名;

DML

• DML-添加数据

给指定字段添加数据
insert into 表名(字段名1，字段名2...) values(值1，值2...);
    
给全部字段添加数据
insert into 表名 values(值1，值2...);

批量添加数据
insert into 表名(字段1，字段2...) values(值1，值2...),(值1，值2...);

insert into 表名 values(值1，值2...),(值1，值2...);

注意：

1、插入数据时，指定的字段顺序需要与值的顺序时一一对应的

2、字符串和日期型数据应该包含在单引号中

3、插入的数据大小，应该在字段的规定范围内

• DML-修改数据

update 表名 set 字段1=值1,字段2=值2,...[where 条件]

条件可以有，也可以没有，如果没有条件，则修改整张表所有数据

• DML-删除数据

delete from 表名 [where 条件]

注意：

1、条件可有可无，如果没有条件，则删除整张表所有数据

2、delete不能删除某一个字段的值（可以使用update）

DQL

• DQL-基本查询

查询多个字段
select 字段1,字段2,... from 表名;
select * from 表名;

设置别名
select 字段1[as 别名1],... from 表名;

去除重复记录
select distinct 字段列表 from 表名;

• 条件查询

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

• DQL-聚合函数

select 聚合函数(字段列表) from 表名;

常见聚合函数
count--->统计数量
max--->最大值
min--->最小值
avg--->平均值
sum--->求和

null值不参与所有聚合函数的运算的

• DQL-分组查询

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

where 与having的区别：

1、执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤

2、判断条件不同：where不能对聚合函数进行判断，而having可以

• DQL-排序查询

select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2;

排序方式：
ASC：升序（默认值）
DESC：降序

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

• DQL-分页查询

select 字段列表 from 表名 limit 起始索引,查询记录数

注意：

• 起始索引从0开始，起始索引=(查询页码-1)*每页显示记录数
• 分页查询是数据库方言，不同数据库有不同实现语法
• 如果查询的是第一页数据，起始索引可以省略，可简写为limit 10

• DQL-执行顺序

from->where->(group by->)select->order by->limit

DCL

• DCL-管理用户

查询用户
use mysql;
select * from user;

创建用户
create user '用户名'@'主机名' identified by '密码';

修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码'

删除用户
drop user '用户名'@'主机名';

• DCL-权限控制

查询权限
show grants for '用户名'@'主机名';

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

撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

函数

• 字符串函数

字符串拼接，将s1,s2,...sn拼接成一个字符串
concat(s1,s2,...sn)

将字符串str全部转为小写
lower(str)  

将字符串str全部转为大写
upper(str)

左填充，用字符串pad对str左边进行填充，达到n个字符串长度
lpad(str, n, pad)

右填充，用字符串pad对str右边进行填充，达到n个字符串长度
rpad(str, n, pad)

去掉字符串头部和尾部的空格
trim(str)

返回字符串str从start位置起的len个长度的字符串
substring(str, start, len)

练习

由于业务需求变更，企业员工工号统一为5位数，目前不足5位数的全部在前面补0.比如：1号员工的工号应该为00001

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

• 数值函数

向上取整
ceil(x)

向下取整
floor(x)

返回x/y的模
mod(x, y)

返回0-1内的随机数
rand()

求参数x的四舍五入的值，保留y位小数
round(x, y)

练习

生成一个六位数随机验证码

select lpad(round(rand()*1000000, 0), 6, '0');

• 日期函数

返回当前日期
curdate()

返回当前时间
curtime()

返回当前日期和时间
now()

获取指定date的年份
year(date)

获取指定date的月份
month(date)

获取指定date的日期
day(date)

返回一个日期/时间值加上一个时间间隔expr后的时间值,expr可正可负
date_add(date, interval expr type)

返回起始时间date1和结束时间date2之间的天数
datediff(date1,date2)

练习

查询所有员工的入职天数，并根据入职天数倒叙排序

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

• 流程函数

如果value为true，则返回t，否则返回f
if(value, t, f)

如果value1不为空，返回value1，否则返回value2
ifnull(value1, value2)

如果val1为true，返回res1，否则返回default默认值
case when [val1] then [res1]...else[default] end

如果expr的值等于val1，返回res1，否则返回default默认值
case[expr] when [val1] then [res1] .. else[default] end

练习

1、查询emp表员工姓名和工作地址 内蒙/新疆–>一线城市 其他–>二线城市

select
    name,
    (case workaddress when '内蒙' then '一线城市' when '新疆' then '一线城市' else '二线城市' end) as '工作地址'
from emp;

2、统计班级各个学员成绩>=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;

注意两个函数的使用，存在细微差别。第一个是判断确切的某个值，而第二个是判断其范围在什么位置

约束

• 分类

非空约束
not null

唯一约束
unique

主键约束
primary key

自增约束
auro_increment

默认约束
default

检查约束
check

外键约束
foreign key

创建案例

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 '用户表';

多表查询

select * from emp, dept where emp.dept_id = dept.id;

• 内连接(查询两表交集部分)

隐式内连接
select 字段列表 from 表1，表2 where 条件;

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

练习

查询每一个员工的姓名，及关联部门的名称分别用隐式和显示内连接

select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;

select emp.name, dept.name from emp join dept on emp.dept_id = dept.id;

• 外连接

左外连接用left 右外连接用right
select 字段列表 from 表1 left/right [outer] join 表2 on 条件;

练习

查询emp所有数据和对应部门姓名

select emp.*, dept.name from emp left join dept on emp.dept_id = dept.id;

• 自连接

练习

1、查询员工及其所属领导的名字(用内连接)

select a.name, b.name from emp a, emp b where a.managerid = b.id;

2、查询所有员工emp及其领导名字emp，如果员工没有领导，也需要查询出来（用外连接）

select a.name '员工' b.name '领导' from emp a left join emp b on a.managerid = b.id;

注意：在资连接的时候一定要给字段列表起别名，否则区别不了。

• 联合查询-union,union all

select * from emp where id > 10
union [all]
select * from emp where gender = 'M';

按顺序输出两张表，无all关键字表示去重，有all则只要是符合条件的全部显示

注意：上表和下表查询的列数一定要一致

• 子查询

• • 标量子查询（子查询返回单个值）

  练习

  1、根据研发部的id 查询员工信息

  select * from emp where dept_id = (select id from dept where name = '研发部');
  
  理解不了可以拆分成两部分来写再最后进行合并
  1、select id from dept where name = '研发部';--->显示结果为3
  
  2、select * from emp where dept_id = 3;--->显示研发部员工信息
  
  最后将第一步的整体将第二部的3整体替换，并且要加上括号！
  

  2、查询在dyt（2000-01-05）之后的员工信息

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

  • 列子查询（子查询返回一列）

  常用操作符：IN、NOT IN、ANY、SOME、ALL

  

练习

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

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

2、查询比财务部所有人年龄都高的员工信息

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

3、比研发部其中任意一人年龄高的员工信息

select * from emp where age > any(select age from emp where dept_id = (select id from dept where name = '研发部'));

• • 行子查询（子查询返回一行）

  练习

  查询与wf的工作地点和性别相同的员工信息

  select * from emp where (gender, workaddress) = (select gender, workaddress from emp where name = 'wf');
  

  • 表子查询

  练习

  1、查询与wf、htf工作地点和性别相同的员工信息

  select * from emp where (gender, workaddress) = (select gender, workaddress from emp where name = 'wf' || name = 'htf');
  

  2、查询入职日期是2000-01-10之后的员工信息，及其部门信息

  select e.*, dept.* from (select * from emp where entrydate > '2000-01-10') e left join dept on e.dept_id = dept.id;
  

• 多表查询案例

1、查询员工的姓名、年龄、性别、部门信息（隐式内连接）

select e.name, e.age, e.gender, d.name from emp e, dept d where e.dept_id = d.id;

2、查询年龄小于30的员工的姓名、年龄、性别、部门信息（显示内连接）

select e.name, e.age, e.gender, d.name from emp e join dept d on e.dept_id = d.id where e.age < 30;

3、查询拥有员工的部门ID、部门名称

select distinct d.id, d.name from emp e, dept d where e.dpet_id = d.id;-- 使用distinct关键字起到去重的作用

4、查询所有小于40的员工，及其归属部门名称；如果没有分配部门也要展示

select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age < 40;-- 使用左外连接，显示包括所有的左表数据

5、查询所有员工表年龄等级

select e.*, a.grade from emp e, agegrade a where e.age >= a.loage && e.age <= a.hiage

-- 或者这样写

select e.*, a.grade from emp e, agegrade a where e.age between a.loage and a.hiage;

6、查询研发部所有员工的信息及年龄等级

select e.*, a.grade from emp e, dept d, agegrade a where e.dept_id = d.id && (e.age between a.loage and a.hiage) && e.name = '研发部';
-- 使用隐式内连接方式，多个条件写在where中，多个条件用与符号相关联

7、查询研发部员工的平均年龄

select avg(e.age) from emp e, dept d where e.dept_id = d.id && d.name =  '研发部';

8、查询年龄比lzl高的员工信息

select * from emp where age > (select age from emp where name = 'lzl');

9、查询比平均年龄高的员工信息

select * from emp where age > (select avg(age) from emp);

10、查询比本部门平均年龄低的员工信息

select * from emp e2 where e2.age < (select avg(e1.age) from emp e1 where e1.dept_id = e2.dept_id);

11、查询所有部门信息，统计部门的员工人数

select * , (select count(*) from emp where emp.dept_id = dept.id) '人数' from dept;

12、查询所有学生的选课情况，展示出学生名称，学号，课程名称

select s.name, s.no, c.name from student s, student_course sc, course c where s.id = sc.studentid && sc.courseid = c.id;

事务

• 数据准备

create table account(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    money int comment '余额'
) comment '账户表';
insert into account(id, name, money) values (null, 'lzl', 2000), (null, 'xrg', 2000);

• 事务操作

方法一

查看/设置事务提交方式
select @@autocommit; -- 默认值为1，1为自动提交
set @@autocommit = 0;
-- 程序段 ------------
update account set money = money-100 where name = 'lzl';
程序执行报错...
update account set money = money+100 where name = 'lxc';
---------------------

提交事务
commit;

回滚事务
rollback;

取消自动提交改为手动提交，执行程序段之后表中数据暂时不会改变，此时查看控制台各语句是否正常执行，如果正常执行则执行提交事务，反之则执行回滚事务。

方法二

开启事务
start transaction 或 begin;

-- 程序段 ------------
update account set money = money-100 where name = 'lzl';
程序执行报错...
update account set money = money+100 where name = 'lxc';
---------------------

提交事务
commit;

回滚事务
rollback;

大致过程与方法一相同，只是将设置事务方式改成了开启事务的语句，执行完程序段之后，查看控制台的语句是否正常执行。如果正常执行则执行提交事务，反之则执行回滚事务。

• 事务四大特性ACID

1、原子性：事务时不可分割的最小操作单元，要么全部成功，要么全部失败

2、一致性：事务完成时，必须使所有的数据都保持一致状态

3、隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行

4、持久性：事务一旦提交或回滚，它对数据库中的数据的改变是永久的

• 并发事务问题（多个事务在并发的过程当中出现）

脏读：一个事务读到另外一个事务还没有提交的数据

不可重复读：一个事务先后读取同一条记录，单两次读取的数据不同

幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影。

• 事务隔离级别

查看事务隔离级别
select @@transaction_isolation;

设置事务隔离级别
set [session global] transaction isolation level {read uncommitted|read committed|repeatable read|serializable};