目录
- MySQL命令/语法
MySQL命令/语法
启动 MySQL服务
net start mysql
退出MySQL服务
net stop mysql
查看MySQL中有哪些数据库
show databases;
- 注意英文的分号
选择需要操作的数据库
use 操作的数据库名;
创建数据库
create database 数据库名
查看当前使用的数据库
select database();
查看数据库中有哪些表
show tables;
查看表结构
desc 表名;
查询所有字段
方法一:select 字段名a,字段名b,字段名c... from 表名
- 依次列出表中的所有字段
- 推荐使用这种方式
- 优点查询速度快,缺点挨个写字段麻烦。
方法二:select * from 表名
- 使用通配符代替所有字段
- 不推荐使用这种方式,因为通配符会自动转换成字段名需要时间,测试的时候可以使用
- 优点方便快捷,缺点查询时间慢
给列起别名
select 字段名 as 别名 from 表名;
- 使用
as
关键字起别名,有多条列需要起别名使用逗号,
隔开 - 别名中有中文或者空格,使用单引号
''
括起来 as
关键字可以省略用空格代替,可读性会差点- 这个别名只会在输出显示中生效,实际表中的列并未被修改
列参与数学运算
select 字段名*12 from 表名;
- 可以直接在字段名后加上数学运算
条件查询
select 字段1,字段2,字段3... from 表名 while 条件;
例:查询A表中字段1大于5且小于10的数。
select
字段1
from
A表
where
字段1 > 5 && 字段1 < 10;
- 需要查询多个条件可以使用逻辑运算符
&&
也可以使用and
between and用法
between ... and ...
例:查询A表中字段1大于等于5且小于等于10的数。
select
字段1
from
A表
where
字段1 between 5 and 10;
- between ... and ...等于
>= and <=
- 使用
between and
时需要注意数据左小右大
注意:在数据库中查询值为null
的字段时不能使用等号=
,需要使用is null
。因为null
指的是什么也没有,它不是一个值,所以不能使用等号匹配。
条件查询中in
的用法
select 字段名 from 表名 where 字段名 in(条件1,条件2)
例:查询A表中字段1中age为18和25的人。
select
字段1
from
A表
where
age in(18,25);
注意:例子中18和25查找的不是18到25中的所有值
in
代表的不是一个区间,代表的是指定的条件或数值not in
则是取反
模糊查询
要点:使用like
关键字后接下划线或者百分号来代替任意字母作为模糊对象;
- 下划线
_
:用于代替一个字符 - 百分号
%
:用于代替任意数量的字符
需要查询字段中包含下划线或百分号,使用反斜杠
\
进行转义后即可查询
百分号例子:
例1:查询字段中含有A字母的字段。
select
字段名
from
表名
where
查询的字段名 like '%A%';
%A%
:第一个百分号代表前面有任意字符;其中包含字母A;第二个字母代表后面也含有任意字符
例2:查询以B字母结尾的字段。
select
字段名
from
表名
where
查询的字段名 like '%B';
%B
:百分号代表任意字符最后结尾是B的作为条件B%
:这样就是查找B开头的字段
下划线例子:
例1:查找第二个字母是C的字段。
select
字段名
from
表名
where
查询的字段名 like '_C';
_C
:下划线代替前任意一个字符,第二位限定为C
例2:查找第四个字母是D的字段
select
字段名
from
表名
where
查询的字段名 like '___D';
- 条件D前有几个字符就加几个下划线代替
排序查询
升序(ascend)
select 查哪个字段 from 在哪个表查 order by 需要排序的字段;
- 关键词
order by
默认是升序(从低到高) - 完整的指定升序可以在结尾加上
asc
,例如``select 查哪个字段 from 在哪个表查 order by 需要排序的字段 asc;
降序(descend)
select 查哪个字段 from 在哪个表查 order by 需要排序的字段 desc;
- 降序在需要排序的字段后加上
desc
,叫做指定降序。
多字段排序
select 字段名A,字段B from 表名 order by 需要排序的字段A asc,需要排序的字段B desc;
- 多字段排序优先按照第一个条件(字段A)来排序,当第一个条件(字段A)相等的情况下再按照第二个条件(字段B)来排序
根据字段位置排序
select 字段名A,字段B from 表名 order by 2
- 这里的2表示第二列(字段B),查询结果按照字段B进行排序
- 这种写法不健壮,一旦列的顺序发生改变,2就会发生改变
- 作为了解,不推荐使用
单行数据处理函数
数据处理函数也被称为单行处理函数
特点:一个输入对应一个输出。
常见的单行处理函数:
函数名 | 作用 |
---|---|
lower | 转换小写 |
upper | 转换大写 |
substr | 取子串substr(需要截取的字符串,起始下标,截取长度) |
length | 取长度 |
trim | 去除空格 |
str_to_date | 将字符串转换为日期 |
date_format | 格式化日期 |
format | 设置千分位 |
round | 四舍五入 |
rand() | 生成随机数 |
ifnull | 可以将null 转换成一个具体值 |
例:大小写转换
select
lower(字段名)
from
表名;
- 转换大写更换函数名
例:取子串
select
substr(字段名,起始下标,截取长度)
from
表名;
- 下标从1开始
例:取长度
select
length(字段名)
from
emp;
例:ifnull()
函数用法
select
ifnull(字段名,0)
from
表名
ifnull()
函数可以将字段名中值为null
的值转换为0,当作0来处理- 在数学运算中一旦有
null
的值参与运算,结果必定为null
,这时候就需要ifnull
函数来进行转换
多行处理函数
特点:输入多行,最终输出一行
常见的单行处理函数:
函数名 | 作用 |
---|---|
count | 计数 |
sun | 求和 |
avg | 平均值 |
max | 最大值 |
min | 最小值 |
注意:
- 分组函数在使用时必须先进行分组,然后才能用
- 若未对数据进行分组,则默认整张表为一组
- 分组函数自动忽略
NULL
- 分组函数不能直接使用在
where
条件判断语句中 - 分组函数可以进行组合使用
使用方法和单行处理函数一致
分组查询
实际应用中,可能会出现这样的需求,需要先对每一组数据进行分组,然后对每一组的数据进行操作。这时候就需要进行分组查询。
语法:
select
字段名
from
表名
group by
字段名
例:查询A表中每个岗位的工资和
select
sum(工资字段)
from
A表
group by
岗位字段;
执行过程:从A表中对岗位字段进行分组,计算工资字段的和查询出来
关键字组合在一起时的执行顺序
select ④
...
from ①
...
where ②
...
group by ③
...
order by ⑤
desc
having
关键字
使用having
可以对分完组之后的数据进一步过滤,但它不能代替where
,只能和group by
进行联合使用。
例:找出每个部门中最高薪资,只查询最高薪资大于3000的。
select
部门字段,max(工资字段)
from
表名
group by
部门字段
having
max(工资字段) > 3000;
执行过程:从表中对部门字段进行分组,
having
条件是最大工资字段是3000以上的进行筛选,最后查询显示出部门字段和筛选后的最大工资字段。
having
筛选后小于3000的数据就舍弃了,不效率。
优化:
select
部门字段,max(工资字段)
from
表名
where
工资字段 > 3000
group by
部门字段;
- 直接在分组前就筛选出大于3000的数据,效率高
- 在
where
和having
中优先使用where
进行过滤
查询数据去重
关键字:distinct
- 对查询的结果去除重复记录
destinct
只能出现在去重的字段前
例:
select distinct 去重的字段名 from 表名;
笛卡尔积现象
当两张表进行连接查询,没有任何条件限制的时候,最终查询结果的条数,是两张表条数的乘积,这种现象被称之为笛卡尔积现象。
笛卡尔发现的一种数学现象
通过笛卡尔积现象可以得出,表的连接次数越多,效率越低
如何避免笛卡尔积现象出现
- 连表查询时附加条件进行限制,将满足这个条件的记录筛选出来
连接查询
对多张表联合起来进行查询数据,称之为连接查询
内连接(inner)
连接的两张表,没有主次关系,关系是平等的
inner
关键字加在join
关键字前,可省略
等值连接
- 条件是等量关系,所以称之为等职连接
示例:查询每个员工所在的部门名称,显示员工名和部门名
EMP表:
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
DEPT表:
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
SQL92语法:
select
d.dname,
e.ename
from
emp e,
dept d
where
e.deptno = d.deptno and 后续筛选条件;
SQL92缺点:
- 表连接的条件和后续的筛选条件都放到了
where
后面,杂糅在一起结构不清晰
SQL99语法:
select
d.dname,
e.ename
from
emp e
inner join // inner可以省略
dept d
on
e.deptno = d.deptno
where
后续筛选条件;
SQL99优点:
- 表连接的条件是独立的,连接之后如果还需要进一步筛选,可以继续添加
where
筛选过滤 join
关键字前的inner
不省略可读性更好,可以一眼看出是内连接
非等值连接
- 条件不是一个等量关系,称之为非等值连接
示例:找出每个员工的薪资等级,要求显示员工名、薪资、薪资等级。
EMP表:
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
SALGRADE表:
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
查询过程:
select
e.ename,
e.sal,
s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal; // e.sal >= s.losal and e.sal <= s.hisal
自连接
- 单张表自己连接自己,称之为自连接
示例:查询员工的上级领导,要求显示员工名和对应的领导名。
EMP表:
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
查询过程:
- 先找出需要的条件
select
empno, // 员工编号
ename, // 姓名
mgr // 上级领导编号
from
emp;
+-------+--------+------+
| empno | ename | mgr |
+-------+--------+------+
| 7369 | SMITH | 7902 |
| 7499 | ALLEN | 7698 |
| 7521 | WARD | 7698 |
| 7566 | JONES | 7839 |
| 7654 | MARTIN | 7698 |
| 7698 | BLAKE | 7839 |
| 7782 | CLARK | 7839 |
| 7788 | SCOTT | 7566 |
| 7839 | KING | NULL |
| 7844 | TURNER | 7698 |
| 7876 | ADAMS | 7788 |
| 7900 | JAMES | 7698 |
| 7902 | FORD | 7566 |
| 7934 | MILLER | 7782 |
+-------+--------+------+
- 把一张表看作是两张表,
emp as a
看作是员工表,emp as b
看作是领导表,连接条件是a表的领导编号等于b表的员工编号
select
a.ename '员工名',
b.ename '领导名'
from
emp a
join
emp b
on
a.mgr = b.empno;
+-----------+-----------+
| 员工名 | 领导名 |
+-----------+-----------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+-----------+-----------+
13 rows in set (0.00 sec)
KING
没有被匹配,需要使用外连接
外连接(outer)
连接的两张表有主次关系,左外连接则
join
关键字左边的表是主表,右外连接则类似
- 在
join
关键字前使用left
ORright
关键字来区分左右连接,同时定位主表在哪个位置 - 主表就是那个表的所有匹配的全查出来
- 任何一个左连接都有右连接的写法
- 任何一个右连接都有左连接的写法
左外连接(左连接)
示例:查询员工的上级领导,要求显示员工名和对应的领导名。
- 和自连接的示例一样,这次要把
KING
的值查询出来
筛选过的EMP表:
select empno,ename,mgr from emp;
+-------+--------+------+
| empno | ename | mgr |
+-------+--------+------+
| 7369 | SMITH | 7902 |
| 7499 | ALLEN | 7698 |
| 7521 | WARD | 7698 |
| 7566 | JONES | 7839 |
| 7654 | MARTIN | 7698 |
| 7698 | BLAKE | 7839 |
| 7782 | CLARK | 7839 |
| 7788 | SCOTT | 7566 |
| 7839 | KING | NULL |
| 7844 | TURNER | 7698 |
| 7876 | ADAMS | 7788 |
| 7900 | JAMES | 7698 |
| 7902 | FORD | 7566 |
| 7934 | MILLER | 7782 |
+-------+--------+------+
14 rows in set (0.00 sec)
查询过程:
左外查询语法:
select
a.ename '员工名',b.ename '领导名'
from
emp a left join emp b
on
a.mgr = b.empno;
right
:表示左边的a表是主表,把这个表中全部数据查询出来,顺带查询右边的表
右外查询语法:
select
a.ename '员工名',b.ename '领导名'
from
emp b right outer join emp a
on
a.mgr = b.empno;
outer
:表示外连接,可省略,左外查询语法中就省略了,写上只是为了增加可读性- 示例的左外和右外查询结果一致,
left
和right
关键字只是用于区分主表位置
查询结果:
+-----------+-----------+
| 员工名 | 领导名 |
+-----------+-----------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+-----------+-----------+
14 rows in set (0.00 sec)
- 通过外连接
KING
的值也被查询出来了
右外连接(右连接)
和左外连接类似,详情查看左外连接
全连接(暂时忽略)
多表连接
语法:
select
...
from
A表
join
B表
on
A表和B表的连接条件
left join
C表
on
A表和C表的连接条件
right join
D表
on
A表和D表的连接条件
......
- 一条SQL中内连接和外连接可以混合使用
示例:找出每个员工的部门名称以及工资等级,要求显示员工名、部门名、薪资、薪资等级。
EMP表:
- 员工数据
- e表
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
DEPT表:
- 工作岗位
- d表
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
SALGRADE表:
- 薪资等级
- s表
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)
思路:
-
找出表连接条件
- e表和d表的
DEPTNO
部门编号相同 - e表的
sal
工资在s表的lowsal
最低工资和hisal
最高工资之间
- e表和d表的
-
编写SQL
select e.ename '员工名', d.dname '部门名称', e.sal '薪资', s.grade '薪资等级' from emp e right join dept d on e.deptno = d.deptno join salgrade s on sal between s.losal and s.hisal;