MySql的学习笔记（基础篇）

阿薇薇薇薇薇薇啊

已于 2024-08-25 23:03:15 修改

阅读量572

点赞数 24

文章标签： mysql 学习笔记

于 2024-08-19 14:56:07 首次发布

本文链接：https://blog.csdn.net/2401_82858768/article/details/141316638

版权

DQL-查询数据

SQL-DQL-条件查询

语法：

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

条件的构成：

实例

1.查询没有身份证的员工的所有信息：

select * from employer where idcard is null;

select * from employer where not idcard;(自己写的是这个，不是很标准，但是可以运行）

2.查询有身份证的员工的所有信息：

select * from employer where idcard is not null;

3.查询年龄等于18、20、40的员工所有信息：

select * from employer where age=18 or age=20 or age=40;(这样写太冗杂了）

select * from employer where age in(18,20,40);

4.查询名字为两个字的员工的所有信息：

select * from employer where name like'_ _'; （一个下划线一个字符）like后面有单引号要注意

5.查询身份证最后一个字符为X的员工的所有信息：

select * from employer where idcard like'%X'; (%X中，是指在X之前可以是任意个字符）

DQL-聚合函数

常见的聚合函数：

语法：

select 聚合函数（字段列表）from 表名；

ps:null值不参与聚合函数的计算

实例

统计西安地区员工的年龄之和：

首先，先查找出西安地区的员工，要用到where：select * from employer where place=‘西安’；

最后，再统计年龄之和：select sum（age） from employer where place=‘西安’；

SQL-DQL-分组查询（group by）

语法：

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

ps:不满足where分组条件的不参与分组，且where中不能使用聚合函数，但是having可以。

”分组的字段名“ 标志词：“以......进行分组”

实例

1.根据性别分组，统计男员工和女员工的数量：

首先，看到分组，就写：select * from employer group by

其次，根据性别分组”,就写：group by gender

最后，是统计数量，所以用聚合函数的count，就写：select count(*) from employer，这时候运行一下会统计出两个数据（按男女分组统计出的），但是我们要知道哪个好是男性的，哪个是女性的，就要查询性别出来，就要写：select gender , count(*) from employer

即：select gender , count(*) from employer group by gender;

ps：count(*)是指可以用任何字段来统计，并且结果都是一样的（针对于统计人数比较常用）

2.根据性别分组，统计男员工和女员工的平均年龄：（对比一下1）

select gender , avg(age) from employer group by gender;

3.查询年龄小于45的员工，按照工作地址分组，并获取员工数量大于等于3的工作地址：

首先审题，第一句查询：select * from employer where age<45;

其次，分组：group by palce

然后，“获取员工数量>=3的工作地址”，所以肯定是要统计每个工作地址的员工数：select place， count（*），这样就会得到不用工作地址的员工数 [count(*)是统计的员工数]

最后，筛选出员工数>=3的工作地址，由于这是分组后的筛选了，所以用：having count(*)>=3

即：select place, count（*） from employer where age<45 group by palce having count(*)>=3;

小结：

执行顺序：where>聚合函数>having

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

SQL-DQL-排序查询

语法

select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2；

排序方式：

升序：asc（默认）

降序：desc

实例

根据年龄对员工进行降序排序

select * from employer order by age desc;

ps:如果是多字段排序，当第一个字段值相同时，才会根据第二个字段排序。

SQL-DQL-分页查询

语法

select 字段列表 from 表名 limit 起始索引，每页显示的记录数；

注意：

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

分页查询是数据库的“方言”，不同的数据库使用的语法是不一样的

如果查询的是第一页，起始索引可以省略，直接简写为 limit 每页显示的记录数

SQL-DQL-案例练习

查询性别为男，且年龄在20-40岁之间（包含20、40岁）的前五个员工的信息，对查询的结果按照年龄升序排序，年龄相同按照入职时间降序排序。

1>前五个员工信息：是暗示一页里面只显示5条记录，要用到分页查询

2>明确前后关系：肯定是排序了之后再在页面上显示出来

select * from employer where gender='男’ and( age between 20 and 40 )order by age asc,entrytime desc limit 5;

SQL-DQL-编写顺序

SQL-DQL-执行顺序（运行时）

DCL-管理用户、控制权限

SQL-DCL-用户管理

语法

1.查询用户

use mysql;

select * from user;

2.创建用户

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

3.修改用户密码

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

4.删除用户

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

实例

创建用户heima，可以在任意主机连接数据库，密码为’123456‘

create user 'heima'@'%' identified by '123456'; （改成%即可）

注意：这类sql语句开发人员用的比较少，主要是DBA（数据库管理员）使用

DCL-权限控制

语法

1.查询权限：

show grants for '用户名’@‘主机名’；

2.授予权限：

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

（"权限列表' 这里默认给所有权限用all）

（若想给所有的数据库以及所有的表授予权限，就用*.*即可）

3.撤销权限：

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

注意：多个权限间用逗号分隔。

函数

函数-字符串函数

常用字符串函数

语法

select 函数（参数）；

1.select concat('hello' , 'world');

2.select lpad('01' , 5 , '-'); # ---01 ，长度一共为5

3. select rpad('01' , 5 , '-'); # 01---

4.select substring('hello word' , 1 ,5); # hello 这里要注意索引值从1开始，且空格也算一个长度

实例

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

首先审题，要明白这是属于修改数据的操作，想到要用update

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

函数-数值函数

常见的数值函数

注意：

x/y取模就是取余数的意思

select ceil(1.1) ; # 2

rand()括号里不带参数，且生成的是介于0-1之间的小数

实例

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

首先还是审题，’随机‘就可以想到用rand函数，但是他生成的是小数，那么就要乘个倍数：

select rand(）*1000000 #784014.429862

由输出可知，接下来就要用到round函数，四舍五入，然后保留0位小数。

select round(rand()*1000000 , 0); #会发现有一个bug，六位数变成了五位数，这是因为0-1的随机数也会出现0.019255*1000000=19255等这种

所以就要确保她是6位数，可以通过补0的方式(补左补右都可以）

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

question：

那么这里改成用ceil函数行不行呢？

select lpad(ceil(rand()*1000000),6,'0'); 希望看到的uu可以告诉我一下~

函数-日期函数

常用日期函数

eg:

select date_add(now(),interval 70 day));

select datediff ('2021-09-22' , '2029-07-25' );

实例

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

select name,datediff(curdate(),entrytime) as 'entryday' from employer order by entryday desc;(该用引号的地方别漏了）

函数-流程函数

演示

1.if

select if(true,'ok','default'); #返回ok

select if(false,'ok','default') #返回default

2.ifnull 这里要这么理解：如果第一个是值null则返回第二个值，若第一个值不是null就返回第一个值。

select ifnull('ok','default') #返回ok

select innull(' ','default') #返回空格

select innull(null,'default') #返回default

3.case

查询employer表的员工姓名和工作地址（北京/上海--> 表示成一线城市，其他的表示成二线城市）

首先可以写出：select name,workpalce from employer；

再根据要求补充在workplace那里：case workplace when '北京'or '上海' then '一线城市' else '二线城市' end; (自己的写法，没报错可以运行)

标准写的是：case workplace when' 北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end;

所以：

select name,(case workaddress when '北京'or '上海' then '一线城市' else '二线城市' end) from employer;

4.case（对比3）

查询班级各个学员的成绩，展示规则如下：

>=85，优秀

>=60, 及格

否则，暂时不及格

首先先查询出

再根据要求添加条件：

(case when math >=85 then '优秀' when math >=60 then'及格' else '不及格' end) as '数学',

其他科目也是类似

所以：

select

id,name,

(case when math >=85 then '优秀' when math >=60 then'及格' else '不及格' end) as '数学',

(case when chinese >=85 then '优秀' when chinese >=60 then'及格' else '不及格' end) as '语文',

(case when English >=85 then '优秀' when English >=60 then'及格' else '不及格' end) as '英语'

from score;

约束

约束-概述

约束-实例

create table itheimaa(
    id int primary key auto_increment comment'ID',
    name varchar(10) not null unique comment'姓名',
    age int check(age>0 and age <=120 )comment'年龄',
    status char(1) default '1' comment'状态',
    gender char(1) comment '性别'
)comment '用户表';

ps：主键是不用插入数据的，再插入数据的时候，除了int类型的数据不用加引号，其他都要

约束-外键约束

概念

具有外键的那张表称为‘子表’（从表），外键所关联的那张表称为‘父表’（主表）

注意：以上两张表，在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的。

语法

在创建表时建立外键：

create table 表名（

字段名数据类型

........

constraint 外键名称 foreign key(外键字段名) references 主表(主表列名)

）;

建立表后，创建外键：

alter table 表名 add constraint 外键名称 foreign key(外键字段名）references 主表（主表列名）；

ps：‘外键名称’是自己命名的,外键字段名是在子表中的

eg:

alter table itheimaa add constraint fk_emp_dept_id_new2 foreign key (dept_id) references dept(id);

删除外键：

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

外键删除和更新行为

alter table 表名 add constraint 外键名称 foreign key(外键字段名）references 主表（主表列名）on update 行为 on delete 行为；

多表查询

多表查询-多表关系介绍：

1.一对多

2.多对多

实例

create table student(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名'
)comment '学生表';
insert into student values(null,'小虎'),(null,'小花'),(null,'小蝶'),(null,'小美');

create table course(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
)comment '课程表';
insert into course values(null,'java'),(null,'PHP'),(null,'Mysql'),(null,'Hadoop');

create table student_course(
    id int auto_increment comment '主键' primary key ,
    studentid  int not null comment '学生ID',
    courseid int not null comment '学生课程',
    constraint fk_courseid foreign key(courseid) references course(id),
    constraint fk_studentid foreign key (studentid) references student(id)
)comment'学生课程中间表';
insert into student_course values(null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

3.一对一

实例

create table tb_user(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    gender char(1) comment '1:男 ，2：女',
    phone char(11) comment '手机号'
)comment '用户基本信息表';

create table tb_user_edu(
    id int auto_increment primary key comment'主键ID',
    degree varchar(20) comment'学历',
    university varchar (30) comment '大学',
    userid int unique comment'用户ID',
    constraint fk_user_edu foreign key(userid) references tb_user(id)

) comment '用户教育信息';

多表查询概述

实例

select * from 表1，表2；查询结果如下：

那么要消除无效的笛卡尔积，观察表，发现这两张表是通过dept_id连接起来的，所以我们只需要：select * from 表1，表2 where 表1. 字段名 = 表2.字段名；(有个点别漏掉了）

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

多表查询分类

内连接

语法：

隐式内连接：

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

显式内连接：

select 字段列表 from 表1 [inner] join 表2 on 连接条件... ; (inner可省略）

内连接查询的是两张表的交集部分。

实例：

1.查询每一个员工的姓名，及关联的部门名称（隐式内连接）：

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

如果取了别名的话，都要改成别名的形式：

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

2.显式内连接：

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

外连接

语法

左外连接：

select 字段列表 from 表1 left [outer] join 表2 on 条件... ; #查询到的是表1所有的信息，包括表1与表2重合的部分。

右外连接：

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

实例：

1.查询emp表中的所有数据，和对应的部门信息（用左外连接）：

select e.* , d.name from emp as e left outer join dept as d on e.dept_id=d.id;

2.查询dept 表中的所有信息，和对应的员工信息（用右外连接）：

select d.* , e.* from emp as e right outer join dept as d on e.dept_id=d.id;

自连接

语法

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

select 字段列表 from 表A 别名A ，表A 别名B where 条件... ;

自连接查询，可以是内连接查询，也可以是外连接查询

实例

1.查询员工及其所属领导的名字

第一步先观察表可以得知，员工姓名跟所属领导都在一个表上，所以这是单表不是多表，但是要用到多表查询，单表查询不能实现。

第二步，由表知，领导也是员工中的一员，所以这张emp表可以看作两张表——员工表和领导表，并且员工表和领导表是通过员工表的manageid外键和领导表的id主键关联起来的

所以：select a.name,b.name from emp as a , emp as b where a.manageid=b.id;

2.查询emp表中的所有员工及其领导的名字，如果员工没有领导，也要显示出来

“如果没有领导也要展示出来” 这就代表要用左/右外连接，因为内连接的话，查询出的是公共部分，不是所有的。

select a.name as '员工' , b.name as ’领导‘ from emp as a left outer join emp as b on a.manegeid=b.id;

联合查询-union all/union

概念

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

语法

select 字段列表 from 表A ...

union [all]

select 字段列表 from 表B... ;

实例

将薪资低于5000的员工，和年龄大于50的员工全部查询出来

select * from emp where salary<5000

union all

select * from emp where age>50;

注意：

子查询

概念

sql语句中嵌套select语句，称为嵌套查询，又称子查询。

语法

select * from t1 where column1=(select column1 from t2); 紫色的语句为子查询语句

注意：子查询外的语句可以是 insert/ update/ delete/ select的任何一个

子查询分类

标量子查询

实例

1.查找’销售部‘所有员工的信息

第一步，要查询销售部的部门id

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

第二步，根据销售部的部门id，查找员工信息

select * from emp where dept_id=4;

若要把两条合成一条：

select * from emp where dept_id=(select id from emp where name='销售部'); 紫色为子查询语句

2.查询在’方东白‘入职之后的员工信息

第一步先查询方东白的入职日期

select entrydate from emp where name='方东白';

第二步再查询指定入职日期之后的员工的信息

select * from emp where entrydate>'2009-08-11';

要把两条语句合成一条：

select * from emp where entrydate > (select entrydate from emp where name='方东白');

列子查询

实例

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

select id from emp where name='销售部' or name='市场部'; #返回结果2跟4

select * from emp where dept_id in(2,4);

合并成一条：

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

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

select id from emp where name='财务部';

select max(salary) from emp where dept_id=4;

select * from emp where salary>100; 这是自己的想法

标准写法

select id from emp where name='销售部'; #返回id=4

select salary from emp where dept_id=4; #返回salary有4500、8730、8900

select * from emp where salary>all(8900);

合并就是：

select * from emp where salary>all(select salary from emp where dept_id=（select id from emp where name='销售部'）);

3.查询比研发部门任意员工工资高的员工的信息

select * from emp where salary>any(select salary from emp where dept_id=（select id from emp where name='研发部'）);

行子查询

ps：<>是不等于的意思

实例

查询与（‘张无忌’的薪资）及（直属领导）都相同的员工信息

select salary,manageid from emp where name='张无忌'; 返回12500,2

select *from emp where emp where (salary,manageid)=(12500,2)

select *from emp where emp where (salary,manageid)=(select salary,manageid from emp where name='张无忌');

表子查询 (常用在from后面)

实例

1.查询与’小花‘、’小明‘的职位和薪资相同的员工信息

select job ,salary from emp where name='小花 or name='小明'；

select * from emp where (job,salary) in (select job ,salary from emp where name='小花 or name='小明');

ps:与行子查询对比一下，行子查询为单行，用’=‘；表子查询为多行，用’in‘。

2.查询入职日期是’2006-01-01‘之后的员工信息，及其部门信息

先查询入职日期之后的员工信息

select * from emp where entrydate >'2006-01-01';

再查询这部分员工对应的部门（把查询出的这部分员工看作一张表）

select * from (select * from emp where entrydate >'2006-01-01')

由表中可以看出，还有一个员工没有部门，也需要查询出来，就要用到左外连接：

select e.* , d.* from (select * from emp where entrydate >'2006-01-01') as e left outer join dept as d on e.dept_id=d.id;

练习一

1.查询员工的姓名、年龄、职位、部门信息。(隐式内连接)

select e.name , e.age , e.job , d.name from emp as e, dept as d where e.deptid=d.id;

2.查询年龄小于30岁的员工姓名、年龄、职位、部门信息。(显式内连接)

[select * from emp where age<30;

select e.name, e.age, e.job,d.name from (select * from emp where age<30) as e join dept as d on e.dept_id=d.id;] 这是自己的想法(对比一下与表子查询的实例2)

标准答案：

select e.name, e.age, e.job,d.name from emp as e join dept as d on e.dept_id=d.id where e.age<30；

3.查询拥有员工的部门ID、部门名称。（重点理解）

解读题目，就是要找出部门表跟员工表的公共部分，保证部门下有员工，所以要用到内连接

select d.id,d.name from emp as e join dept as d on e.dept_id=d.id; #这样写出来会发现有很多重复的，所以要去重。

上面那串代码的含义是：

JOIN dept AS d ON e.dept_id = d.id: 这一行指定了如何将 emp 表和 dept 表连接起来。dept 表也起了一个别名 d。

ON e.dept_id = d.id: 这是连接条件，表示当 emp 表中的 dept_id 列与 dept 表中的 id 列相等时，这两行会被连接在一起。（即emp表的外键=dept表的主键）

从 emp 表中选取员工的部门 ID (dept_id)。

使用这些部门 ID 在 dept 表中找到对应的部门信息（ID 和名称）。

返回所有员工所属部门的 ID 和名称。

去重：

select distinct d.id,d.name from emp as e join dept as d on e.dept_id=d.id;

4.查询所有年龄大于40岁的员工,及其归属的部门名称;如果员工没有分配部门,也需要展示出来

select e.* ,d.name from emp as e left outer join dept as d on e.dept_id=d.id where age>40;

5.查询所有员工的工资等级。

这里遇到了一个新的知识点，就是要将emp与工资表关联起来，要找到他们关联的条件

观察表可知，emp表的salary可与薪资表的losal和hisal产生联系,所以连接条件是：

emp.salary >=salgrade.losal and emp.salary <= salgrade.hisal

select e.* , s.grade from emp as e join salgrade as s on e.salary >=s.losal and e.salary <= s.hisal;

标准答案:select e.*,s.grade from emp e ,salgrade s where e.salary between s.losal and s.hisal; (这是用隐式内连接写的)

6.查询"研发部"所有员工的信息及工资等级。（重点，涉及三张表）

当出现多张表关联时，n张表至少需要（n-1）个条件，梳理表关系的时候要两个两个去梳理。

多个连接条件中间用and去连接。

涉及的表:emp员工表，dept部门表，salgrae薪资等级表

连接条件：emp.salary >=salgrade.losal and emp.salary <= salgrade.hisal emp.dept_id=dept.id 查询条件：dept.name='研发部'

标准答案：

select d.* , s.grade from emp e ,dept.d,salgrade.s where (e.salary >=s.losal and e.salary <= s.hisal) and e.dept_id=d.id and dept.name='研发部';

7.查询"研发部"员工的平均工资。

select avg(e.salary) from emp e ,dept d where e.dept_id=d.id and d.name='研发部'；

8.查询工资比"灭绝"高的员工信息。

select salary from emp where name='灭绝';

标准答案：select * from emp where salary>(select salary from emp where name='灭绝');

9.查询比平均薪资高的员工信息。

select * from emp where salary>(select avg(emp.salary) from emp);

10.查询低于本部门平均工资的员工。（重点关注）用到了自查询

先找个例，比如说查的‘本部门’的id为1

select avg(e1.salary) from emp e1 where e1.dept_id=1;

select * from emp where emp.salary<(select avg(e1.salary) from emp e1 where e1.dept_id=1);

找一下规律，发现格式都差不多，只是在dept_id处会发生改变，即可推出本部门的。

标准答案：

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

11.查询所有的部门信息,并统计部门的员工人数。（要理解）

首先查询所有部门信息是比较容易的：

select id ，name from dept;

然后就是统计人数，可以先写个个例(部门id=1的人数)：

select count(*) from emp where emp.dept_id=1；

最后思考怎么把它们整合起来：

select id name '人数' from dept;

人数要=统计的人数

select id ，name，(select count(*) from emp where emp.dept_id=1) as '人数' from dept;

最后把1改称部门的id即可，所以标准答案是：

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

12.查询所有学生的选课情况,展示出学生名称,学号,课程名称。（这三张表属于多对多的关系，一个学生可以选很多课，一门课可以被很多学生选）

涉及的表：student,course,student_course

连接条件：student.id=student_course.studentid course.id=student_course.courseid

标准答案：select s.name, s.id,course.id from student s ,course c, student_course sc where s.id=sc.studentid and c.id=sc.courseid;

事务

概念

如：银行转账

对于Mysql数据库，事务是默认自动提交的

操作演示——银行转账

张三给李四转账1000

1.查询张三账户余额

select * from account where name='张三'；

2.将张三账户余额-1000

update account set money=money-1000 where name='张三';

3.给李四账户余额+1000

update account set money=money+1000 where name='李四'；

ps：默认情况下，一条sql语句就是一个事务，写完一条sql语句就回自动提交事务数据就会进行修改，那么就会出现一种异常，比如说以上三条sql语句在执行的过程中，第二条出现了异常，导致第三条的sql语句无法执行，那么张三的钱就会少1000，而李四的钱没有变化。那么我们就需要对事务的提交进行修改来避免这种错误的出现，使这三行sql语句，要么全部能执行，要么都不能执行。