常用命令:
select * from (table); 查询数据表中所有数据
desc (table); 查看数据表结构
select version(); 查看数据库版本
select database(); 查看当前所在数据库
一、简单查询语句(DQL)
查询一个字段:
select 字段名 from 表名; 其中注意,select和from都是关键字,字段名和表名都是标识符
查询两个字段、或者多个字段时,用逗号隔开“,”
给查询字段其别名:select 字段名 as 新字段名 from 表名; (as 可省略,同时别名如果有空格,要用单引号引起来,单引号是所有数据库中的标准,别名是中文也是如此)
select 语句永远都不会进行修改操作
二、条件查询
语法格式: select ... from ... where 条件;
select 字段名1,字段名2...
from 表名
where = / > / < / >= / <= / between and
(between and 遵从左小右大,等同于大于等于与小于等于之间)
注意:在数据库当中null不能使用等号进行衡量,需要使用is null ,因为数据库中的null代表什么也没有,它不是一个值,所以不能使用等号衡量。不为null表示为is not null
and 和or同时出现的话,and的优先级比or高,先执行and,再执行or.所以要先加括号,开发中不确定优先级,加小括号就行,例子:
select * from where sal >2500 (dep=10 or dep =20)
in 相当于多个or,in不是一个区间,in 后面跟的是具体的值,例如:in('dsn','ddd'),not in 表示不在这几个值里面
like 称为模糊查找,支持%或下划线匹配,%匹配任意多个字符,下划线,任意一个字符,查找下划线字符,用转义字符“\”
排序中用关键字“order by”,默认为升序,语法为select 字段1,字段2.. from 表 order by 字段1;指定降序时在字段后面加desc,指定升序在后面加asc;如果多个条件时,用逗号分隔,前面起主导作用,当前面条件有相等时,才会执行后面的条件
关键字顺序不能变:
select ... from ... where ... order by ...
执行顺序为:
第一步:from
第二步:where
第三步:select
第四步:order by(排序总是在最后执行!)
三、数据处理函数(单行处理函数)
特点:一个输入对应一个输出,与单行处理函数相对的是多行处理函数(多个输入,对应一个输出)
单行处理函数:lower(转化小写),upper(转换大写)
substr(取子串),substr(被截取的字符串,起始下标,截取的长度),注意,起始下标从1开始,没有0
字符串拼接用concat,语法为:select concat(字段1,字段2,字段3) from 表名;
字段取长度语法为:select length(字段1) from 表名;
trim去除前后空白:语法:select * from 表名 where 字段1 = trim(' KING');
select 后面可以跟某个表的字段名(等同于变量),也可以跟字面量/字面值(等同于值)
round(四舍五入);用法,select round(1234.678,保留位数(-2,-1,0,1,2)) from 表名;
rand();随机数 用法:select
rand() from 表名; select round(rand()*100,0) as result from student; (100以内的随机数)
ifnull 可以将null 转化成一个具体值,ifnull是空处理函数,专门处理空的,在所有数据库中,只要有null参与的数学运算,最终结果就是null。为了避免这个现象,需要使用ifnull函数
用法:ifnull(数据,被当作哪个值,一般为0)
case..when..then..when..then..else..end 当是什么是。。。
四、分组函数(多行处理函数)
特点:输入多行,最终输出一行
count(计数)、sum(求和)、avg(平均值)、max(最大值)、min(最小值)
分组函数在使用的时候必须先进行分组,最后才能使用,如果没有进行分组,整张表默认为一组。
1.分组函数自动忽略Null,不需要提前对null进行处理。
2.分组函数中count()和count(具体字段)是有区别的。
count(具体字段)表示统计该字段下所有不为null的元素的总数
count()统计表当中的总行数,只要有一行数据count则++,因为每一行记录不可能都为null,一行数据中有一列不为null,则这行数据就是有效的。
3.分组函数不能直接使用在where子句中。
分组函数使用前必须先分组才能使用,where执行的时候,还没有分组,所以不能出现分组函数,而用select后可以执行,是因为select 在group by 之后执行
4.所有的分组函数可以组合起来一起用
五.分组查询(重中之重)
语法:select ... from ... group by (字段1....)
关键字组合:
select ... from ... where ... group by ... order by ... 以上关键字顺序不能颠倒,需要记忆,1.from 2.where 3.group by 4.select 5.order by
重点结论:在一条select语句当中,如果有group by语句的话,select后面只能跟参加分组的字段,以及分组函数。其他的一律不能跟。
使用having子句对分完组的数据进一步过滤,having不能单独使用,它不能代替where,having必须和group by 联合使用。优化策略,where和having ,优先选择where,where实在完成不了了,再选择having.
单表查询总结:
select
...
from
...
where
...
group by
...
having
...
order by
...
以上关键字只能按照这个顺序来,不能颠倒
执行顺序:
1.from
2.where
3.group by
4.having
5.select
6.order by
从某张表中查询数据,先经过where条件筛选出有价值的数据,对这些有价值的数据进行分组,分组之后可以使用having继续筛选。筛选后查出数据,最后再对数据进行排序输出。
五、distinct(去除重复记录):把查询结果去除重复记录,原表数据不会被修改,只是对查询结果去重,关键子为distinct。用法:select distinct 字段1,字段 2 from 表; //distinct 只能出现在所有字段的最前方,表示多个字段联合起来去重
六、连接查询
连接查询的分类
根据语法的年代分类:
SQL92:1992年出现的语法
SQL99:1999年出现的语法
根据表连接的方式分类:
内连接:等值连接 非等值连接 自连接
外连接:左连接、右连接
全连接
当两张表进行连接查询,没有任何条件限制的时候,最终查询结果条数,是两张表条数的乘积,这种现象被称为:笛卡尔积现象。
如何避免笛卡尔积现象?
连接时加条件,满足这个条件的记录被筛选出来;
表起别名,很重要,效率问题
注意:通过笛卡尔积现象得出,表的连接次数越多效率越低,尽量避免表的连接次数。
1.内连接之等值连接:
SQL92语法:select s.stuname,t.teaname from student s,teacher t where s.username = t.username;
SQL92的缺点,结构不清晰,表的连接条件和后期进一步筛选的条件,都放到了where后面
SQL99语法: select s.stuname,t.teaname from student s join teacher t on s.username = t.username; //条件是等量关系,所以被称为等值连接
SQL99优点,表连接的条件是独立的,连接之后,如果还需要进一步筛选,再往后继续添加where就行了。
SQL语法格式:
select
...
from
a
(inner(内))join
b
on
a和b的连接条件
where
筛选条件
2.内连接之非等值连接
select
e.ename,e.sal,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal; //条件不是一个等量关系,称为非等值连接
3.内连接之自连接
select
a.ename as '员工名',b.ename as '领导名'
from
emp a
join
emp b
on
a.mgr = b.empo;
自连接技巧:一张表看做两张表。
内连接特点:完成能够匹配这个条件的数据查询出来
内连接:A和B连接,AB两张表没有主次关系,平等的。
4.外连接
外连接(右外连接)
right 代表什么, 表示将join关键字右边的这张表看成主表,主要是为了将这张表的数据全部查询出来,捎带着关联查询左边的表。在外连接当中,两张表连接,产生了主次关系。
select
字段1,字段2
from
表1
right join
表2
on
字段1 = 字段2;
带有right的是右外连接,又叫做右连接
带有left的是左外连接,又叫做左连接
任何一个右连接都有左连接的写法。
任何一个左连接都有右连接的写法
三张表、四张表怎么连接?
语法:
select
...
from
a
join
b
on
a和b的连接条件
join
c
on
a和c的连接条件
join
d
on
a和d的连接条件
什么是子查询?
select 语句中嵌套select语句,被嵌套的select语句称为子查询
子查询都可以出现在哪里呢?
select
...(select)
from
...(select)
where
...(select)
where子句的子查询
from后面的子查询,可以将子查询的查询结果当做一张临时表(技巧)
select 后面的子查询
注意:对于select 后面的子查询来说,这个子查询只能返回一条结果,多余一条,就报错。
5.union合并查询结果集
union的效率要高一些。对于表连接来说,每连接一次新表,则匹配的次数满足笛卡尔积,成倍的翻。但是对于union可以减少匹配的次数,在减少匹配次数的情况下,还可以完成两个结果集的拼接。(union把乘法运算变成了加法运算)
注意事项:union在进行结果集合并的时候,要求两个结果集的列数相同
6.limit :limit是将查询结果集的一部分取出来,通常使用在分页查询当中。
分页的作用是为了提高用户的体验,因为一次全部都查出来,用户体验差,可以一页一页翻页看
用法:limit startIndex ,length
startIndex是起始下标,length是长度
注意:mysql中limit在order by 之后执行!!!!
分页:
每页显示pagesize条记录
第pageNo页, limit (pageNO - 1) * pagesize ,pagesize
DQL语句的大总结:
select
...
from
...
where
...
group by
...
having
...
order by
...
limit
...
7.表的创建(建表)
建表的语法格式:(建表属于DDL语句,DDL包括:create frop alter)
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型
);
8.插入数据
9.空值转换函数nvl(),比如nvl(字段,0),指的就是查看某字段中的值,如果不是空值,则返回字段本身,如果是空值,就返回第二个参数