创建数据库表
-
对已经建好的表和数据库,是可以查看其建表或建库的语句的
show create database `user`; -- 可以查看创建user库的sql语句 show create table `user`; -- 查看创建user表的sql语句 -- 输出结果 CREATE TABLE `user` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `username` VARCHAR(255) NOT NULL, `password` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
数据库引擎
INNODB
:默认使用
MYISAM
:早些年使用
对比:
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,约为MYISAM 的两倍 |
常规使用操作:
MYISAM
:节约空间,速度较快INNODB
:安全性高,事务的处理,多表多用户操作
字符集编码:
charset = utf8
,不设置的话是mysql默认的字符集编码,会不支持中文。
查询
简单查询(DQL:data query language)
select 字段名1,字段名2,字段名3,.... from 表名;
注意:标准sql语句中要求字符串使用单引号括起来。虽然mysql支持双引号,尽量别用。
条件查询
select
字段,字段...
from
表名
where
条件;
执行顺序:先from,然后where,最后select
条件
-
between … and … :表示的是闭区间,必须是小的在前、大的在后。
between and除了可以使用在数字方面之外,还可以使用在字符串方面,例如:
select ename from emp where ename between 'A' and 'C'
;查找的结果就是
ename
字段的首字母处于A和C之间的名字。 -
不等于 的 表示方法:<> 或者 !=
-
关于
null
:在数据库中
null
不是一个值,代表什么也没有,为空。空不是一个值,所以不能用等号来衡量!必须使用
is null
或者is not null
。还有,只有有NULL参与的运算,其结果一定为NULL(乌龟的屁股-----规定)
例如,从emp表中查出comm字段为null的ename, sal ,comm:
select ename,sal,comm from emp where comm is null
; -
或:
or
---->in
等同于or
select ename,job from emp where job = 'SALESMAN' or job = 'MANAGER'; select ename,job from emp where job in('SALESMAN', 'MANAGER');
-
not in
:不在这几个值当中 -
和:
and
and 和 or 连起来使用,例如:从emp表中查出sal>1000并且deptno是20或者30的ename,sal,depno
slect ename,sql,deptno from emp where sal > 1000 and (deptno = 20 or deptno = 30)
注意一定要加上括号,不加查出来的是错的!
当运算符的优先级不确定的时候加小括号。
-
模糊查询
like
在模糊查询中,有两个特殊的符号,
%
:代表任意多个字符_
:代表任意一个字符
例如,找出名中中还有字母
O
的人:select ename from emp where ename like '%O%'
找出名字中第二个字母是
A
的:select ename from emp where ename like '_A%'
找出名字中有下划线的?
select ename from emp where ename like '%\_%';(注意要加斜线,不加的话会认为这三个都是特殊的符号!!)
-
排序(升序,降序)
按照工资升序,找出员工名和薪资?
select ename,sal from emp order by sal;
注意:查询结果默认是升序的。如何指定升序降序?
asc
:表示升序。desc
:表示降序。
select ename , sal from emp order by sal; // 升序 select ename , sal from emp order by sal asc; // 升序 select ename , sal from emp order by sal desc; // 降序。 例子: 1.按照工资的降序排列,当工资相同的时候再按照名字的升序排列。 select ename,sal from emo order by sal desc,ename asc 即 靠前的字段会起主导作用,只有当前面的字段无法完成排序的时候,才会启用后面的字段排序。 2.找出工作岗位是SALESMAN的员工,并且要求按照薪资的降序排列。 select ename , job , sal from emp where job = 'SALESMAN' order by sal desc;
执行顺序
select 5 -- 过滤完之后就从表里面查数据
..
from 1 -- 先找出是哪个表
..
where 2 -- 再拿到查询的条件进行过滤
..
group by 3 -- 按什么来分组
..
having 4 -- 对分组后的数据进行过滤
..
order by 6 -- 查出来的数据按照哪个字段进行排列
..
--------------------------------
order by是最后执行的。
分组函数(多行处理函数)
- count:计数
- sum:求和
- avg:求平均
- max:最大值
- min:最小值
总共就上面这5种。所有的分组函数都是对“某一组”数据进行操作的!
多行处理函数的特点:输入多行,最终输出的结果是1行。
例子:
1.查出工资总和
select sum(sal) from emp;
2.找出最高工资
select max(sal) from emp;
3.找出最低工资
select min(sal) from emp;
4.找出平均工资
select avg(sal) from emp;
5.找出总人数
select count(*) from emp;
select count(ename) from emp;
注意:分组函数会自动忽略null,如果某个记录的某个字段为null,在统计那个字段的数量的时候就会忽略掉
比如,下面这条语句的where comm is not null
是不需要的,因为分组函数已经自动忽略了。
select sum(comm) from emp where comm is not null; // 不需要额外添加这个过滤条件。sum函数自动忽略NULL。
count(*)
和 count(某个字段)
的区别:
count(*)
:不是统计某个字段数据的个数,而是统计总记录的条数,和某一个字段无关,即使某个字段是null也无所谓!count(comm)
:表示统计comm字段中部位NULL的数据总数量。
分组函数组合起来用
select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
例子:
找出工资高于平均工资的员工?
select ename,sal from emp where sal > (select avg(sal) from emp)
错误的写法:
select ename,sal from emp where sal > avg(sal); -- 是错误的。因为分组函数是分完组之后执行的,即avg(sal)是分完组之后才可以执行,到那时上述语句没有分组,所以会报错。就算有group by 也不行,因为where 是在 group by 之前执行的。
--- 即有一个很重要的规则: 分组函数不能直接用在where子句当中!!!
单行处理函数
什么是单行处理函数??? ---------> 输入一行,输出一行
ifnull()
:空处理函数
ifnull(可能为null的数据,被当作什么处理)
:属于单行处理函数。
例子:
计算每个员工的年薪
select ename,(sal+comm)*12 as yearsal from emp; -- 是错误的,因为如果comm为null的话,sal+comm就是NULL了;
-- 解决办法 使用ifnull() 函数:
select ename,(sal+ifnull(comm,0))*12 as yearsal from emp; -- 如果comm为null,就让comm为0代入计算
group by 和 having
group by
:按照某个字段或者某些字段进行分组。having
:having是对分组之后的数据进行再次过滤。
例子:
找出每个工作岗位的最高薪资:
select
max(sal),job
from
emp
group by
job
注意:分组函数一般会和group by
组合使用,这也是为什么被称为分组函数的原因;
并且每一个分组函数都是在group by
语句执行之后才执行的。
当一条sql语句没有group by
的时候,整张表的数据会自成一组(也就是会有一个默认的group by 将整张表当成一组)。
规则:当一条语句中有 group by 的时候,select 紧接着后面只能跟分组函数和参与分组的字段,别的都不能跟。
例如:查每个部门的最高工资select ename,max(sal),job from emp group by job;
就是不对的,没有意义,在mysql(语 法松散)里面不会报错,但是在orcale(语法严谨)里面会报错。下面分析一下:
select
ename,max(sal),job --3.都根据部门分完组了,那么查出来的名字是谁的名字呢?实际上是随机给的名字,是没意义的。
from
emp --1.先说明从哪个表里面查
group by
job --2.再根据job进行分组
-----正确的应该是--------
select job,max(sal) from emp group by job; -- 其中job 和 max(sal)可以调换位置
多个字段联合分组查询:
-- 案例:找出每个部门不同工作岗位的最高薪资 -- 分析 :要看查的是谁,每个部门 是一个分组条件 即 deptno ,工作岗位是一个分组条件 job
---- 把多个字段看程是一个字段,即把deptno 和 job 看成联合起来的一个字段
select
deptno,job,max(sal)
from
emp
group by
deptno,job
------------------------------------------
-- 案例:找出每个部门的最高薪资,要求显示薪资大于2900的数据。
-- 分析:碰见长的问题时,将其分开来看,最好能拆成很多个块。
-- 第一步 : 找出每个部门的最高工资
select max(sal),deptno from emp group by deptno;
-- 第二步 : 找出薪资大于2900的部门
select max(sal),deptno from emp group by deptno having max(sal) > 2900; -- 这种方式效率较低,因为分组查询之后又通过 一个过滤条件把它过滤掉了,浪费查找资源!!
select max(sal),deptno from emp where sal > 2900 group by deotno;--效率高,能用where过滤的尽量用where过滤
--------------------
-- 当然,有时候用where搞不定,就不得不用having了
-- 案例: 找出每个部门的平均薪资,要求显示薪资大于2000的数据。
select avg(sal),deptno from emp group by deptno having avg(sal) > 2000 ;
查询结果集的去重
select distinct job from emp
注意 : distinct
关键字只能出现在所有字段的最前面。
select ename,distinct job from emp
就是错误的语法。
select distinct ename,job from emp
;-- 》 distinct出现在多个字段的前面代表多个字段联合起来去重。
-- 案例 :统计岗位的数量
select count(distinct job) from emp;