一、数据库介绍
1.1常见的数据库
MySQL 甲骨文公司
Oracle 甲骨文公司
SQL Server 微软公司
DB2 IBM公司
Sybase Sybase公司
Access 微软公司
1.2数据库实际应用
高德地图------存储打车记录
XXX学堂-----存储课程
Mantis -------存储缺陷
12306---------存储车次
淘宝/京东/美团------存储购物车/历史记录等等等......
1.3什么是数据库
Database定义：是一个系统化有组织的数据集合，用于存储、管理和检索数据；亦可简单理解为：存储数据的仓库。
1.4数据库发展史
第一阶段：程序管理阶段(20世纪50年代中期)
特点：数据不能长期保存
第二阶段：文件系统阶段(20世纪50年代后期至60年代后期)
特点：数据缺乏独立性
第三阶段：数据库系统阶段(60年代后期至今)
特点：数据共享、减少冗余
二、MySQL介绍
2.1 介绍
MySQL是一款关系型数据库管理系统，由瑞典公司研发，目前属于Oracle公司，是最流行的数据库之一，主要分为2大版本，分别是社区版(免费版)和企业版(付费版)。
2.2主要特点
MySQL体积小、速度快、可跨平台(Win/Linux/Mac)使用；是一种广泛使用的开源关系型数据库管理系统。以其开源免费、高性能、安全性、可扩展性、可靠性和易用性著称。
2.3使用场景
1）功能项目时，配合数据库进行查询。
2）接口测试时，落库检查。
3）性能测试时，高并发事务处理，如银行系统、支付系统、办公系统等等，需要支持大量并发事务和实时数据处理。
2.4官方下载
https://dev.mysql.com/downloads/installer/
2.5数据库常用术语
1）关系
一个关系就是一张2维表(表)，例如Excel
2）属性
2维表中的一列，称为属性，工作中一般称为列或者字段
3）元组
2维表中的一行,称为元组，工作中一般称为行或者记录
2.6 MySQL访问和调式
访问MySQL：
(1)通过DOS访问(临时)
步骤:
(a)在DOS窗口中,输入:
XXXXX>mysql -uroot -p
Enter Password:在此输入密码
mysql>
(2)通过工具(DBeaver)访问(工作中)
常见工具(连接MySQL)介绍:
DBeaver、SQLyog、Navicat、phpmyadmin、workbench
步骤:
(1)启动DBeaver
(2)单击新建，输入服务器地址、端口
(3)输入用户名、密码
(4)选择本地客户端
三、SQL语句概述
3.1 SQL语句分类
3.1.1数据定义语句
用法：主要是对数据库中的表,创建、修改、删除
创建 --- create
修改 --- alter
删除 --- drop
3.1.2数据操纵语句
用法：主要是对数据库表中的数据,插入、更新、删除
插入 --- insert
更新 --- update
删除 --- delete
3.1.3数据查询语句
说明：此为重点（因为工作中常用）
用法：主要是对数据库表中的数据进行查询
查询：select
基本语法：
SELECT column1, column2, ... --------------指定要检索的列
FROM table_name --------------指定要查询的表
WHERE condition --------------过滤行数据（可选）
GROUP BY column --------按列分组（通常与聚合函数一起使用）
HAVING condition -----------过滤分组后的数据（可选）
ORDER BY column ASC|DESC -----------按列排序（可选）
LIMIT number; -----------限制返回的行数（可选）
3.1.4事务控制语句
说明：用于管理数据库事务
主要语句：
BEGIN 或 START TRANSACTION：开始一个事务。
COMMIT：提交事务，保存更改。
ROLLBACK：回滚事务，撤销更改。
SAVEPOINT：设置保存点，用于部分回滚。
3.1.5数据控制语句
说明：用于控制数据库的访问权限和安全性。
主要语句：
GRANT：授予用户权限。
REVOKE：撤销用户权限。
3.2 SQL语句编写规范
(1)SQL语句不区分大小写
(2)SQL语句建议关键词大写,其他部分小写,根据公司情况,具体要求
(3)一条SQL语句以分号结尾(;)
四、数据库基础语句
4.1查看当前全部的数据库
语法格式：show databases;
4.2创建数据库
语法格式：create database 数据库名称 charset 字符集;
说明:
(1)常见的字符集有gbk、utf8
(2)数据库名称不能重复
案例1：创建一个数据库,名称为:testing,字符集设置为utf8;
create database testing charset utf8;
案例2：创建一个数据库,名称为xyxt,字符集设置为gbk
create database xyxt charset gbk;
4.3查看创建好的数据库信息
语法格式：show create database 数据库名称;
案例1：查看testing数据库信息
show create database testing;
案例2：查看mysql数据库信息
show create database mysql;
4.4选择数据库
语法格式：use 数据库名称;
案例1：选择testing数据库
use testing；
案例2：选择xyxt数据库
use xyxt;
4.5查看数据库中全部的表
语法格式: show tables;
案例1：选择testing数据库,查看数据库全部的表
use testing;
show tables;
案例2：选择mysql数据库,查看数据库全部的表
use mysql;
show tables;
4.6删除数据库
语法格式：drop database 数据库名称;
案例：删除数据testing,并验证
drop database testing;
show databases;
五、数据类型
说明：数据类型就是对数据(文字、字母、符号、音频、图片、视频...)等等进行分类
5.1数值数据类型
5.1.1整数类型
int 或者 int(n) 表示整数类型,用来存储整数,n表示数值的宽度和大小无关
案例：int(5) 66666
5.1.2小数类型
double(n,m) 表示小数类型,用来存储小数,n表示数值的共位数,m表示小数的位数
案例：double(7,2) 15934.27
5.2字符数据类型
5.2.1固定长度字符类型
char(n) 表示固定长度字符,n表示字符的长度
案例：char(5) abcde
5.2.2可变长度字符类型
varchar(n) 表示可变长度字符,n表示字符串的最大长度。
案例：varchar(10) abcdefg
5.3日期时间数据类型
5.3.1日期类型
date 表示日期类型,一般日期类型格式: YYYY-MM-DD
5.3.2日期时间类型
datetime 表示日期时间类型,一般日期时间类型格式:YYYY-MM-DD HH:MM:SS
六、数据库操作语句
6.1创建表
语法格式:
create table 表名(
列名1 数据类型,
列名2 数据类型,
.....,
列名n 数据类型
);
说明:
a)表名不能重复
b)表中最后一列,不加逗号
c)列名不能重复
案例1：创建一张表,表名为test01,表中包含的字段有:id int,name varchar(30),age int,sex char(2),address varchar(80)
create table test01 (
id int,
name varchar(30),
age int,
Sex char(2),
Address varchar(80)
);
案例2：创建一张表,表名为test02,表中包含的字段有:id int,name varchar(30),age int,sid int,score double
create table test02(
id int,
name varchar(30),
age int,
Sid int,
Score double
);
6.2查看表结构
语法格式：desc 表名;
案例1：查看test01表,表结构
desc test01;
案例2：查看test02表,表结构
desc test02;
6.3插入语句
6.3.1向全部列,插入数据
语法格式：
insert into 表名 values(列值1,列值2,....,列值n);
说明：
(a)values中的列值必须和表结构中的列名是一一对应的(数量、顺序、类型)。
(b)在数据库中,数值类型数据,直接填写,字符和日期时间类型数据,需要添加单引号,对列值。
案例：向test01表中,插入数据
insert into test01 values(101,'hepeng',20);
insert into test01 values(102,'weiwei',25);
insert into test01 values(103,'bowen',23);
insert into test01 values(104,'川杭',39);
6.3.2向指定列,插入数据
语法格式：
insert into 表名 (列名1,列名2,...列名n) values(列值1,列值2,....,列值n);
说明：表名中的列名必须和values中的列值是一一对应的。
案例:向test01表中,插入数据
id name age sex address
10 凯丽 18 女 杭州天空云境
20 25 女 杭州西湖悦府
21 佳橙 男
战战 男 四川成都元华庄园
insert into test01(id,name,age,sex,address) values(10,'凯丽',18,’女’,’杭州天空云境’);
insert into test01(id,name,age,sex,address) values(20,null,25,'女','杭州西湖悦府');
insert into test01(id,name,age,sex,address) values(21,'佳橙',null,’男’,null);
insert into test01(id,name,age,sex,address) values(null,'战战',null,'男','四川成都元华庄园');
6.4更新语句
语法格式:
update 表名 set 列名1=该列新值,列名2=该列新值,....,列名n=该列新值 where 条件;
说明：如果没有where条件,更新某列的全部值。
案例1：更新test01表中数据,将编号(id)是20的,姓名(name)更新为杨淑敏
update test01 set name='杨淑敏' where id=20;
select * from test01;
案例2：更新test01表中数据,将姓名(name)为战战的,编号(id)更新为22
update test01 set id=22 where name='战战';
select * from test01;
案例3：更新test01表中数据,将编号(id)是10号的,性别(sex)更新为男,地址(address)更新为上海
update test01 set sex='男',address='上海' where id=10;
select * from test01;
案例4：更新test01表中的数据,将表中年龄(age)全部更新为18
update test01 set age=18;
select * from test01;
6.5删除表中数据
语法格式:
delete from 表名 where 条件;
说明：如果没有where 条件,则删除表中全部数据。
案例1：删除test01表,姓名(name)是战战的记录
delete from test01 where name='战战';
select * from test01;
案例2：删除test01表中,编号(id)是20号的记录
delete from test01 where id=20;
select * from test01;
案例3：删除test01表中,全部数据
delete from test01;
select * from test01;
6.6删除表
语法格式：drop table 表名;
案例1：删除test01表,并验证
drop table test01;
show tables;
案例2：删除test02表,并验证
drop table test02;
6.7查询语句(重点)
说明：工作中最常用的就是查询，属于应用的重点；增删改你可以不熟练，但是查，一定要熟得起飞才行。
6.7.1查询表中,全部列数据
语法格式: select * from 表名;
说明: * 表示全部的列
案例1:查询student表中,全部列数据
select * from student;
案例2:查询emp表中,全部列数据
select * from emp;
6.7.2查询表中,指定列数据
语法格式:
select 列名1,列名2,...,列名n from 表名;
案例1:查询student表中,学员编号(sid),姓名(sname),分数(score),地址(address)
select sid,sname,score,address
from student;
案例2:查询dept表中,部门编号(deptno),部门名称(dname),部门地址(loc)
select deptno,dname,loc from dept;
6.7.3给列起别名
语法格式:
select 列名1 as 别名1,列名2 as 别名2,...,列名n as 别名n from 表名;
说明:还可以省略as,列名和别名之间使用空格分隔。
案例1:查询emp表中,员工编号(empno),员工姓名(ename),入职时间(hiredate),工资(sal),并给每列起别名显示
写法一：
select empno as '员工编号',ename as '员工姓名',hiredate as '入职时间',sal as '工资' from emp;
写法二：
select empno '员工编号',ename '员工姓名',hiredate '入职时间',
sal '工资' from emp;
案例2:查询cou01表中,课程编号(cno),课程名称(cname),授课老师(ctea),并给每列起别名显示
select cno '课程编号',cname '课程名称',ctea '授课老师' from cou01;
6.7.4去掉重复的列值(distinct)
语法格式:
select distinct 列名 from 表名;
案例1:查询emp表中,员工的部门编号(deptno),并去重显示
select distinct deptno from emp;
案例2:查询student表中,所属班级(sclass),并去重显示
select distinct sclass from student;
6.7.5分页(限制)查询(limit)
语法格式:
select */列名 from 表名 limit 初始位置,行数;
说明:
(a)初始位置 表示从那行开始查询,是一个可选值,如果没添加初始位置,默认值是0,表示从第1行开始查询。
(b)行数 表示查询的行数
案例1:查询student表中,前5条记录
select * from student limit 0,5;
或者
select * from student limit 5;
案例2:查询student表中,从第8行开始查询,共查询5条记录
select * from student limit 7,5;
案例3:查询emp表中,从第6行开始查询,到第10行结束
select * from emp limit 5,5;
6.7.6排序(order by)
语法格式:
select */列名 from 表名 order by 列名 asc/desc,列名2 asc/desc;
说明:
asc 表示升序 desc 表示降序
多(两)列排序规则:先根据前面的列进行排序,如果前面的列有,相同的“列值”,才会进行后面的列排序,否则,不排序。
案例1:查询emp表中,员工姓名(ename),职位(job),工资(sal),入职时间(hiredate),根据工资升序排列
select ename,job,sal,hiredate
from emp
order by sal asc;
案例2:查询emp表中,员工姓名(ename),职位(job),工资(sal),入职时间(hiredate),根据员工姓名降序排列
select ename,job,sal,hiredate
from emp
order by ename desc;
案例3:查询emp表中,员工姓名(ename),职位(job),工资(sal),部门编号(deptno),根据部门编号升序排列,在根据工资降序排列
select ename,job,sal,deptno
from emp
order by deptno asc,sal desc;
案例4:查询student表中学员编号(sid),姓名(sname),分数(score),班级(sclass),根据班级升序排列,在根据分数降序排列
select sid,sname,score,sclass
from student
order by sclass asc,score desc;
6.7.7条件查询(where)
语法格式:
select */列名 from 表名
where 条件
order by 列名 asc/desc,列名 asc/desc;
条件说明:
(1)关系运算符
>、<、=、>=、<=、<> 或者 !=
(2)逻辑运算符
and(与)、or(或)、not(非)
(3)特殊情况
is、between....and、in、like
查询示例如下：
案例1:查询student表中,分数(score)大于80分的,学员的信息
select * from student where score > 80;
案例2:查询emp表中,部门编号(deptno)是30号部门的,员工信息
select * from emp where deptno=30;
案例3:查询student表中,分数小于等于90的,学员姓名(sname),分数(score),地址(address)
select sname,score,address
from student
where score <= 90;
案例4:查询emp表中,工资不等于1250的,员工的编号(empno),姓名(ename),工资(sal),根据工资降序排列
select empno,ename,sal
from emp
where sal <> 1250
order by sal desc;
案例5:查询student表中,分数在75--90分之间的,学员的编号(sid),姓名(sname),分数(score)
select sid,sname,score
from student
where 75 <= score and score <= 90;
说明:and 表示与的意思,可以理解为并且(和),可以通过and连接多个条件。
例如: 条件1 and 条件2 and 条件3.....
案例6:查询emp表中,部门编号(deptno)是30号并且工资大于3000的,员工的姓名(ename),职位(job),工资(sal),部门编号(deptno)
select ename,job,sal,deptno
from emp
where deptno=30 and sal > 3000;
案例7:查询student表中,班级是1年1班或者分数大于90的,学员姓名(sname),分数(score),班级(sclass)
select sname,score,sclass
from student
where sclass='1年1班' or score > 90;
说明:or 表示或的意思,可以理解为或者,可以通过or来连接多个条件。
例如:条件1 or 条件2 or 条件3........
案例8:查询emp表中,员工编号(empno)是7521、7369、7902的员工信息
select * from emp where empno=7521 or empno=7369 or empno=7902;
案例9:查询emp表中,工资(sal)不等于1250的,员工的信息
写法一：select * from emp where sal <> 1250;
写法二：select * from emp where not sal=1250;
说明:not 表示非的意思,可以理解为取反,一般放在列名前。
案例10:查询emp表中,奖金(comm)是NULL的,员工的信息
select * from emp where comm is null;
案例11:查询emp表中,奖金(comm)不是NULL的,员工的信息
写法一：select * from emp where not comm is null;
写法二：select * from emp where comm is not null;
案例12:查询emp表中,工资在1250---4450之间的,员工姓名(ename),工资(sal),部门编号(deptno)
select ename,sal,deptno
from emp
where sal between 1250 and 4450;
说明:使用between...and查询出来的结果包含最小值和最大值。
select ename,sal,deptno
from emp
where sal >= 1250 and sal <= 4450;
案例13:查询emp表中,工资不在1250--
-4450之间的,员工姓名(ename),工资(sal),部门编号(deptno)
写法一：
select ename,sal,deptno
from emp
where not sal between 1250 and 4450;
写法二：
select ename,sal,deptno
from emp
where sal < 1250 or sal > 4450;
案例14:查询student表中,班级(sclass)是1年1班、1年2班、2年2班的,学员信息
写法一：
select * from student where sclass='1年1班' or sclass='1年2班' or sclass='2年2班';
写法二：
select * from student where sclass in(
'1年1班','1年2班','2年2班');
说明: in 表示要查询的列值,是否包含在某列的列值中。
案例15:查询student表中,地址(address)是杭州、上海、杭州的,学员信息
select * from student where address in('杭州','上海','杭州');
案例16:查询student表中,地址(address)不是杭州、上海、杭州的,学员信息
select * from student where not address in('杭州','上海','杭州');
6.7.8模糊查询(like)
语法格式:
select */列名 from 表名
where 列名 like 条件;
条件说明:
% 表示0个或者多个任意字符
_ 表示任意1个字符
案例1:查询emp表中,员工姓名(ename)首字母是M的,员工信息
Mdfs M M21d ---- M%
select * from emp where ename like 'M%';
案例2:查询emp表中,员工姓名(ename)尾字母是N的,员工信息
DfdsfN 3fN N ----- %N
select * from emp where ename like '%N';
案例3:查询emp表中,员工姓名中包含字母N的,员工的信息
N12 dfrN 34Nsdv N --- %N%
select * from emp where ename like '%N%';
案例4:查询emp表中,员工姓名中倒数第2个字母是N的,员工的信息
N2 seNe --- %N_
select * from emp where ename like '%N_';
案例5:查询student表中,学员姓名(sname),姓杨的,学员信息
select * from student where sname like '杨%';
案例6:查询emp表中,员工姓名(ename)不包含字母O的,员工姓名(ename),职位(job),工资(sal)
select ename,job,sal
from emp
where not ename like '%O%';
案例7:查询emp表中,姓名不包含字母K并且奖金不为NULL的,员工的姓名(ename),职位(job),工资(sal),奖金(comm),根据工资升序排列
select ename,job,sal,comm
from emp
where not ename like '%K%' and not comm is null
order by sal asc;
6.7.9聚合(分组)函数
count、sum、avg、min、max
(1)min(列名) 求最小值
案例1:查询emp表中,员工的最低工资是多少
select min(sal) from emp;
(2)max(列名) 求最大值
案例1:查询student表中,学员的最高分数是多少
select max(score) from student;
(3)sum(列名) 求和
案例1:查询emp表中,员工的工资总和
select sum(sal) from emp;
(4)avg(列名) 求平均数
案例1:查询emp表中,员工的平均工资
select avg(sal) from emp;
案例2:查询student表中,班级(sclass)是1年1班的,学员的最高分数是多少
select max(score) from student where sclass='1年1班';
(5)count(列名/*) 统计数量
列名
表示统计某张表中,列值不为NULL的,总数量
*
表示统计某张表中,总数量
案例1:查询emp表中,员工总数
select count(*) from emp;
案例1:查询emp表中,奖金(comm)不为NUll的,员工的总数量
select count(comm) from emp;
6.7.10分组查询(group by)
说明:根据表中的某一列,把相同的列值,分成一组,然后对每一组数据,使用聚合函数,聚合函数经常和分组查询一起使用。
语法格式:
select 聚合函数/列名 from 表名
where 条件
group by 列名
order by 聚合函数/列名 asc/desc,聚合函数/列名 asc/desc;
案例1:查询student表中,男生和女生的人数
select sex,count(*) from student group by sex;
案例2:查询student表中,每个班级(sclass)的人数
select sclass,count(*) from student group by sclass;
案例3:查询emp表中,每个部门的人数
select deptno,count(*) from emp group by deptno;
案例4:查询emp表中,工资大于2000的,每个部门的人数,每个部门的最高工资
select deptno,count(*),max(sal)
from emp where sal > 2000
group by deptno;
案例5:查询student表中,分数(score)在70--90分之间的,每个班级(sclass)学员人数,每个班级平均分数
select sclass,count(*),avg(score)
from student
where score between 70 and 90
group by sclass;
案例6:查询student表中,分数(score)在70--90分之间的,每个班级(sclass)学员人数,每个班级平均分数,根据班级的平均分数升序排列
select sclass,count(*),avg(score)
from student
where score >= 70 and score <= 90
group by sclass
order by avg(score) asc;
案例7:查询emp表中,姓名(ename)不包含字母K的,每个职位(job)的人数,每个职位的最高工资
select job,count(*),max(sal)
from emp
where not ename like '%K%'
group by job;
6.7.11 having语句
说明:having语句就是对分组后的数据,再次进行过滤,经常和分组查询一起使用,having语句不可以单独使用。
语法格式:
select 聚合函数/列名 from 表名
where 条件
group by 列名
having 条件
order by 聚合函数/列名 asc/desc,聚合函数/列名 asc/desc;
where和having区别:
1）where是对整张表的数据进行过滤,可以单独使用。
2）having是对分组后的数据进行过滤,不可以单独使用,必须和group by一起使用
案例1:查询emp表中,部门的平均工资大于2000的,每个部门的编号(deptno),部门的人数,部门的平均工资
select deptno,count(*),avg(sal)
from emp
group by deptno
having avg(sal) > 2000;
案例2:查询emp表中,部门的人数大于等于3人的,每个部门的编号(deptno),部门的人数,部门的工资总和
select deptno,count(*),sum(sal)
from emp
group by deptno
having count(*) >= 3;
案例3:查询student表中,班级的人数大于等于2人,每个班级的名称(sclass),班级的人数,根据班级的名称降序排列
select sclass,count(*)
from student
group by sclass
having count(*) >= 2
order by sclass desc;
SQL语句编写思路-总结:
(1)解析需求,找关键字(查询、排序、分页、分组..)
(2)按照先后顺序,排好关键字
(3)再次阅读需求,填写SQL语句其他部分
having条件判断:
(1)先确定条件
(2)判断条件中,描述的列,是否包含在,查询的表中
存在(真列): 使用where
不存在(假列): 使用having
案例4:查询emp表中,工资(sal)在1000
--5000之间的,每个部门的编号(deptno),部门的人数,部门的平均工资,要求:部门的平均工资大于2000,根据部门的编号降序排列
select deptno,count(*),avg(sal)
from emp
where sal between 1000 and 5000
group by deptno
having avg(sal) > 2000
order by deptno desc;
6.7.12子(嵌套)查询
说明:在一条SQL语句内部,又包含一条SQL语句
案例1:查询emp表中,工资大于平均工资的,员工的姓名(ename),职位(job),工资(sal)
(1)先求平均工资
select avg(sal) from emp;
(2)合成
select ename,job,sal
from emp
where sal > (select avg(sal) from emp);
案例2:查询emp表中,工资大于30号部门最高工资的,员工的编号(empno),姓名(ename),工资(sal),部门编号(deptno)
(1)先求30号部门最高工资
写法一：
select max(sal) from emp
where deptno=30
group by deptno;
写法二：
select max(sal) from emp where deptno=30;
(2)合成
select empno,ename,sal,deptno
from emp
where sal > (select max(sal) from emp where deptno=30);
案例3:查询student表中,分数(score)大于1年3班(sclass)最高分数的,学员信息
(1)先求1年3班最高分数
select max(score) from stuent where sclass='1年3班';
(2)合成
select * from student
where score > (select max(score) from stuent where sclass='1年3班');
案例4:查询student表中,和姓名(sname)叫杨家三少是同一个班级(sclass)的,学员信息
(1)先求杨家三少的班级
select sclass from student where sname='杨家三少';
(2)合成
select * from student
where sclass=(select sclass from student where sname='杨家三少');
案例5:查询student表中,和姓名(sname)叫杨家三少是同一个班级(sclass)的,其他学员信息
写法一：
select * from student
where sclass=(select sclass from student where sname='杨家三少') and sname != '杨家三少';
写法二：
select * from student
where sclass=(select sclass from student
where sname='杨家三少') and not sname = '杨家三少';
6.7.13多表连接查询
说明:由于要查询的数据,分布在不同的表中,为了一次获取不同表中的数据,就需要用多表连接查询。
1）内连接查询
说明:查询只查询满足条件(有关系)的记录
语法格式:
select 别名1.*/列名,别名2.*/列名,....
from 表1 别名1,表2 别名2,....
where 关联条件;
案例1:查询emp表中,员工姓名(ename),职位(job),工资(sal),以及dept表中,部门名称(dname),部门地址(loc)
select e.ename,e.job,e.sal,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno;
说明:关联条件就是要查询的表中,相同的列,使其相等
案例2:查询stu01表中,学员编号(sno),姓名(sname),性别(sex),以及sco01表中,分数(score)
select st.sno,st.sname,st.sex,sc.score
from stu01 st,sco01 sc
where st.sno=sc.sno;
案例3:查询emp表中,工资(sal)在1000
--5000之间的,员工姓名(ename),职位(job),工资(sal),以及dept表中,部门名称(dname),部门地址(loc)
select e.ename,e.job,e.sal,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno and e.sal between 1000 and 5000;
案例4:查询sco01表中,课程编号(cno)是1002的,学员的编号(sno),姓名(sname),年龄(age),地址(address)
select st.sno,st.sname,st.age,st.address
from sco01 sc,stu01 st
where sc.sno=st.sno and sc.cno=1002;
案例5:查询emp表中,工资(sal)在1000
--5000之间的,员工姓名(ename),职位(job),工资(sal),以及dept表中,部门名称(dname),部门地址(loc),根据工资升序排列
select e.ename,e.job,e.sal,d.dname,d.loc
from emp e,dept d
where e.deptno = d.deptno and e.sal between 1000 and 5000
order by e.sal asc;
案例6:查询emp表中,员工姓名(ename),职位(job),工资(sal),以及salgrade中,工资等级(grade),该等级下最低工资(losal),该等级下最高工资(hisal) （特殊场景1）
select e.ename, e.job, e.sal, s.grade, s.losal, s.hisal
from emp e,salgrade s
where e.sal between s.losal and s.hisal;
案例7:查询emp表中,员工姓名(ename),员工职位(job),工资(sal),上级领导编号(mgr),上级领导姓名(ename) （特殊场景2）
select e.empno,e.ename,e.job,e.sal,e.mgr,m.ename
from emp e,emp m
where e.mgr=m.empno;
案例8:查询emp表中,姓名(ename)不包含字母K并且部门编号(deptno)是10号部门的,员工姓名(ename),工资(sal),入职时间(hiredate),以及dept表中全部列数据,根据入职时间降序排列
select e.ename,e.sal,e.hiredate,d.*
from emp e,dept d
where not e.ename like '%K%' and e.deptno=10 and e.deptno=d.deptno
order by e.hiredate desc;
2）外连接查询
a.左外连接查询
b.右外连接查询
c.全外连接查询
说明:查询左边表的全部数据,以及与之有关系,右边表的部分数据。
语法格式:
select 别名1.*/列名,别名2.*/列名
from 左表 别名1 left join 右表 别名2
on 关联条件;
案例1:查询emp表中,所有员工的编号(empno),姓名(ename),职位(job),工资(sal),以及dept表中,部门名称(dname),部门地址(loc) --- 使用外接连接查询
select e.empno,e.ename,e.job,e.sal,d.dname,d.loc
from emp e left join dept d
on e.deptno=d.deptno;
案例2:查询dept表中,所有部门的编号(deptno),部门名称(dname),以及emp表中,员工姓名(ename),员工职位(job),工资(sal),根据工资升序排列
select d.deptno,d.dname,e.ename,e.job,e.sal
from dept d left join emp e
on d.deptno=e.deptno
order by e.sal asc;
案例3:查询emp表中,员工姓名不包含字母C的,所有员工的编号(empno),姓名(ename),职位(job),以及dept表中,部门名称(dname),部门地址(loc),根据员工姓名升序排列
select e.empno,e.ename,e.job,d.dname,d.loc
from emp e left join dept d
on e.deptno = d.deptno
where not e.ename like '%C%'
order by e.ename asc;
七、约束规则
说明:约束就是在创建表的时候,对表设置一些规则,只有满足这些规则,才可以向表中插入数据,我们把这些规则叫做约束
介绍:常见的约束有哪几种
主键约束(primary key)
唯一约束(unique)
默认值约束(default)
非空约束(not null)
7.1主键约束(primary key)
说明:主键约束就是用来标识表中的数据,避免出现重复的数据,被主键约束所修饰的列;
列值是唯一且非空的,一张表中只能有一个主键约束。
7.1.1添加主键约束(创建表时)
create table test_01(
id int primary key,
name varchar(30),
age int
);
# 验证id列非空特性
insert into test_01(name,age) values('肉丝',20);
# 验证id列唯一特性
insert into test_01 values(101,'京酱肉丝',30);
insert into test_01 values(101,'鱼香肉丝',25);
7.1.2联合主键(重点)
说明:使用主键约束修饰2列或者多列的值
create table test_02(
id int,
name varchar(30),
score double(4,1),
primary key(id,name)
);
# 验证
desc test_02;
7.1.3添加主键约束(修改表时)
语法格式:
alter table 表名 add primary key(列名1,列名2,...列名n);
预置条件:(先建一个test_03)
create table test_03(
id int,
name varchar(30),
sex char(3)
);
desc test_03;
案例1:修改test_03表,对name列添加主键约束,并验证
alter table test_03 add primary key(name);
desc test_03;
案例2:创建一张表,表名为test_04,表中包含的字段有:id int、name varchar(30)、address varchar(50),修改表时,对name和address列添加主键约束,并验证
create table test_04(
id int,
name varchar(30),
address varchar(50)
);
desc test_04;
# 修改表时,添加主键约束
alter table test_04 add primary key(name,address);
# 验证
desc test_04;
7.1.4删除主键约束
语法格式:
alter table 表名 drop primary key;
案例1:删除test_01表中,主键约束,并验证
desc test_01;
alter table test_01 drop primary key;
desc test_01;
案例2:删除test_04表中,主键约束,并验证
desc test_04;
alter table test_04 drop primary key;
desc test_04;
7.2唯一约束(unique)
说明:唯一约束就是,指定一列或者多列的组合值,使其具有唯一性,防止用户输入重复的数据,被唯一约束所修饰的列,列值是唯一的,可以为NULL,一张表可以有多个唯一约束。
7.2.1添加唯一约束(创建表时)
create table test_05(
id int primary key,
name varchar(30) unique,
address varchar(50) unique,
email varchar(50),
phone char(11),
sex char(3)
);
desc test_05;
# 验证唯一约束
insert into test_05 values(101,'三少','杭州','shansao@163.com',
'13520080808','男');
select * from test_05;
insert into test_05 values(102,'三少夫人','上海','xjy@163.com',
'15910234567','女');
insert into test_05 values(103,'','杭州','qy@163.com',
'13112345678','女');
# 被唯一约束所修饰的列,可以为NULL
insert into test_05(id,email,phone,sex) values(104,
'mm@163.com','15712345678','男');
select * from test_05;
7.2.2添加唯一约束(修改表时)
语法格式:
alter table 表名 add unique(列名1,列名2,...列名n);
案例1:修改test_05表,对email列添加唯一约束,并验证
alter table test_05 add unique(email);
desc test_05;
案例2:修改test_05表,对phone列添加唯一约束,并验证
alter table test_05 add unique(phone);
desc test_05;
7.2.3删除唯一约束(重点)
语法格式A:
alter table 表名 drop index key_name值;
语法格式B:
show keys from 表名;
案例1:删除test_05表中,name列上的唯一约束,并验证
(a)先找到name列,的key_name值
show keys from test_05; -- name
(b)删除name列唯一约束
alter table test_05 drop index name;
(c)验证
desc test_05;
案例2:删除test_05表中,address列上的唯一约束,并验证
#(a)先找到address列,的key_name值
show keys from test_05; -- address
#(b)删除address列唯一约束
alter table test_05 drop index address;
#(c)验证
desc test_05;
7.3默认值约束(default)
说明:默认值约束就是用来,指定某列的默认值,当执行插入操作的时候,如果被默认值约束所修饰的列,没有插入列值,系统会自动将默认值变成列值,每列只有一个默认值。
7.3.1添加默认值约束(创建表时)
create table test_06(
id int primary key,
name varchar(30) unique,
age int default 18,
address varchar(30),
email varchar(30),
etime date
);
desc test_06;
#验证默认值特性
insert into test_06(id,name,address,email,etime) values(101,'jack','杭州',
'jack@163.com','2022-04-28');
select * from test_06;
7.3.2修改表时,添加默认值约束
语法格式:
alter table 表名 modify 列名 数据类型 default 默认值;
案例1:修改test_06表,将age列默认值修改为20,并验证
alter table test_06 modify age int default 20;
desc test_06;
案例2:修改test_06表,对address列,添加默认值,默认值为杭州,并验证
alter table test_06 modify address varchar(30) default '杭州';
desc test_06;
7.3.3删除默认值约束
语法格式:
alter table 表名 modify 列名 数据类型 default null;
或者
alter table 表名 modify 列名 数据类型;
案例1:删除test_06表中,age列上的默认值约束
alter table test_06 modify age int;
desc test_06;
案例2:删除test_06表中,address列上的默认值约束
alter table test_06 modify address varchar(30) default null;
desc test_06;
7.4非空约束(not null)
说明:非空约束就是,当执行插入操作的时候,被非空约束,所修饰的列,列值不能为空。
7.4.1添加非空约束(创建表时)
create table test_07(
id int primary key,
name varchar(30) not null,
sex char(3) not null,
age int default 18,
phone char(11) unique,
email varchar(30),
etime date
);
desc test_07;
# 验证非空约束
insert into test_07(id,sex,phone) values(101,'男','15510593332');
7.4.2修改表时,添加非空约束
语法格式:
alter table 表名 modify 列名 数据类型 not null;
案例1:修改test_07表,对email列添加非空约束,并验证
alter table test_07 modify email varchar(30) not null;
desc test_07;
案例2:修改test_07表,对etime列添加非空约束,并验证
alter table test_07 modify etime date not null;
desc test_07;
7.4.3删除非空约束
语法格式:
alter table 表名 modify 列名 数据类型;
案例1:删除test_07表中,name列非空约束
alter table test_07 modify name varchar(30);
desc test_07;
案例2:删除test_07表中,email列非空约束
alter table test_07 modify email varchar(30);
desc test_07;
八、索引(index)
说明:索引是建立在表中,列上的数据库对象,用于提高查询速度；索引是一种提高查询效率的机制。
8.1创建索引
语法格式:
create index 索引名称 on 表名(列名);
预置条件:
create table test_08(
id int primary key,
name varchar(30) unique,
phone char(11),
address varchar(30)
);
案例1:创建一个索引,名称为index_phone_test08,对test_08表中,phone列添加索引
create index index_phone_test08 on test_08(phone);
8.2查看表中索引
语法格式:
show index from 表名;
案例1:查看test_08表中索引
show index from test_08;
说明:
被主键约束所修饰的列,自带索引
被唯一约束所修饰的列,自带索引
示例比对：
a)创建索引前：查询效果10S
select id,name,phone from test_08 where phone='13074767410';
b)创建索引后：查询效果3S
select id,name,phone from test_08 where phone='13074767410';
九、常用函数
9.1 数值函数
9.1.1 round函数
说明:round(数值,位数)也称四舍五入函数
a)当位数>0,表示几位小数
b)当位数=0,表示不保留小数
c)当位数<0,表示小数点前第几位进行四舍五入
案例1：
select round(45.869,2); -------45.74
select round(45.869,0); -------46
select round(45.869,-2); -------0
select round(45.869,-1); -------50
9.1.2 ABS(x)
说明：ABS(x)返回绝对值。
语法格式：
SELECT ABS(-10);------输出：10
9.1.3 CEIL(x) /FLOOR(x)
说明：CEIL(x) 和 FLOOR(x)向上取整和向下取整。
语法格式：
SELECT CEIL(3.14);------输出：4
SELECT FLOOR(3.14);-----输出：3
9.1.4 MOD(x, y)
说明：MOD(x, y)返回 x 除以 y 的余数。
语法格式：
SELECT MOD(10, 3);------输出：1
9.1.5 POW(x, y)
说明：POW(x, y)返回 x 的 y 次方。
语法格式：
SELECT POW(2, 3);------输出：8
9.1.6 RAND()
说明：RAND()返回 0 到 1 之间的随机数。
语法格式：
SELECT RAND();------输出：随机数
9.2 字符串函数
9.2.1 LENGTH
说明：LENGTH(str)返回字符串的长度。
语法格式：
SELECT LENGTH('MySQL'); --------输出：5
案例1:查询emp表中,员工姓名,以及姓名的长度
select ename,char_length(ename) from emp;
说明：char_length(列名)，获取字符的长度(个数)
案例2:查询名字(ename)长度为6个字符的员工信息
select * from emp where char_length(
ename)=6;
9.2.2 CONCAT
说明：CONCAT(str1, str2, ...)连接多个字符串。
语法格式：
SELECT CONCAT('俺', '在杭州', '很想你'); --输出：俺在杭州很想你
9.2.3 SUBSTRING
说明：SUBSTRING(str, start, length)提取子字符串。
语法格式：
SELECT SUBSTRING('俺很想你啊', 2, 3); -- 输出：很想你啊
9.2.4 TRIM
说明:TRIM([LEADING|TRAILING|BOTH] trim_character FROM str)去除字符串两端的空格或指定字符。
语法格式：
SELECT TRIM(' MySQL ');------输出：MySQL
SELECT TRIM(LEADING '0' FROM '000123');------输出：123
9.2.5 REPLACE
说明：REPLACE(str, from_str, to_str)替换字符串中的子串。
语法格式：
SELECT REPLACE('MySQL', 'SQL', 'Database');-输出：MyDatabase
9.2.6 UPPER/LOWER
说明：UPPER(str) 和 LOWER(str)将字符串转换为大写或小写。
语法格式：
SELECT UPPER('mysql');------输出：MYSQL
SELECT LOWER('MySQL');-----输出：mysql
9.3 日期和时间函数
9.3.1year/month
说明：year(日期类型)、month(日期类型)
- year(日期类型)----------获取年份
- month(日期类型)-------获取月份
案例1:查询emp表中,员工姓名(ename),入职时间(hiredate),入职年份
select ename,hiredate,year(hiredate) from emp;
案例2:查询stu01表中,2018年入学(etime)的学生名单,根据学员编号(sno)降序排列
select * from stu01 where year(
etime)='2018'
order by sno desc;
案例3:查询stu01表中,10月入学(etime)的学生名单,根据入学时间(etime)升序排列
select * from stu01 where month(etime)='10' order by etime asc;
案例4:查询stu01表中,2018年10月06日入学(etime)的学生名单,根据入学时间(etime)升序排列
写法一:
select * from stu01 where etime='2018-10-06' order by etime asc;
写法二：
select * from stu01 where year(etime)='2018'
and month(etime)='10' and day(etime) ='06' order by etime asc;
9.3.2 NOW()
说明：NOW()返回当前日期和时间。
语法格式：
SELECT NOW();--------输出：2023-10-05 12:34:56
9.3.3 CURDATE() /CURTIME()
说明：CURDATE()和CURTIME()返回当前日期或时间。
语法格式：
SELECT CURDATE();-------输出：2023-10-05
SELECT CURTIME();-------输出：12:34:56
9.3.4 DATE_FORMAT
说明：DATE_FORMAT(date, format)格式化日期。
语法格式：
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
输出：2023-10-05 12:34:56
9.3.5 DATEDIFF
说明：DATEDIFF(date1, date2)返回两个日期之间的天数差。
语法格式：
SELECT DATEDIFF('2023-10-10', '2023-10-05');
输出：5
9.3.6 DATE_ADD
说明：DATE_ADD(date, INTERVAL expr unit)日期加法。
语法格式：
SELECT DATE_ADD('2023-10-05', INTERVAL 7 DAY);
输出：2023-10-12
9.3.7 DATE_SUB
说明：DATE_SUB(date, INTERVAL expr unit)日期减法。
语法格式：
SELECT DATE_SUB('2023-10-05', INTERVAL 7 DAY);
输出：2023-09-28
9.4聚合函数
9.4.1 COUNT(expr)
说明：COUNT(expr)返回行数。
语法格式：
SELECT COUNT(*) FROM employees;
返回employees表的行数
9.4.2 SUM(expr)
说明：SUM(expr)返回总和。
语法格式：
SELECT SUM(salary) FROM employees;
返回 employees 表的工资总和
9.4.3 AVG(expr)
说明：AVG(expr)返回平均值。
语法格式：
SELECT AVG(salary) FROM employees;
返回 employees 表的平均工资
9.4.4 MIN(expr) /MAX(expr)
说明：MIN(expr) 和 MAX(expr)返回最小值或最大值。
语法格式：
SELECT MIN(salary) FROM employees;
返回 employees 表的最低工资
SELECT MAX(salary) FROM employees;
返回 employees 表的最高工资
9.4.5 GROUP_CONCAT(expr)
说明：GROUP_CONCAT(expr)将分组中的值连接成字符串。
语法格式：
SELECT department, GROUP_CONCAT(first_name)
FROM employees
GROUP BY department;
十、视图
说明:视图就是一张虚拟表,可以通过视图,查询一张或者多张表中的数据。
10.1创建视图
语法格式:
create view 视图名称
as
查询语句;
案例1:创建一张视图,视图名称为view_emp_dept,查询emp表中员工编号(empno),姓名(ename),职位(job),工资(sal),以及dept表中,全部列数据,作为视图的结果
create view view_emp_dept
as
select e.empno,e.ename,e.job,e.sal,d.*
from emp e,dept d
where e.deptno=d.deptno;
案例2:查询emp表中,员工姓名(ename),职位(job),工资(sal),以及dept表中,部门名称(dname),部门地址(loc),根据工资降序排列
# 多表连接
select e.ename,e.job,e.sal,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno
order by e.sal desc;
# 查询视图
select ename,job,sal,dname,loc
from view_emp_dept
order by sal desc;
10.2视图操作语句
10.2.1创建视图
CREATE VIEW sales_team AS
SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Sales';
10.2.2查询视图
SELECT * FROM sales_team;
10.2.3更新视图
UPDATE sales_team
SET salary = salary * 1.1
WHERE last_name = 'Doe';
10.2.4删除视图
DROP VIEW sales_team;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
