一.排序
1.按照关键字排序
ASC:是按照升序进行排序的,是默认的排序方式,即 ASC 可以省略。SELECT 语句中如果没有指定具体的排序方式,则默认按 ASC方式进行排序
DESC:是按降序方式进 行排列。当然 ORDER BY 前面也可以使用 WHERE 子句对查询结果进一步过滤。
select column1, column2, ... from table_name order by column1, column2, ...
创建表格,添加数据
①按照分数由小到大排序(升序)
select id,name,score from ba order by score;
②按照分数由大到小排序(降序)
select id,name,score from ba order by score desc;
③oder by结合where进行条件过滤
select name,score from ba where hobbid=’2’ order by score desc;
2.单字排序
按分数排序,默认不指定是升序排列(asc)
select id,name,score from ba order by score;(升序)
select id,name,score from ba order by score desc;(降序)
3.条件查询
order by还可以结合where进行条件过滤,筛选地址是南京的学生按分数降序排列
select name,score from ba where address=’nanjing’ order by score desc;
4.多字段排序
order by之后的参数,使用","分割,优先级是按先后顺序而定
select id,name,hobbid from ba order by hobbid desc,id desc;
从ba表内查询id、name、hobbid进行多字段的组合排序
hobbid进行降序排序,再在相同hobbid中的id进行降序
order by 之后的第一个参数只有在出现相同的数值,第二个字段才有意义
(select id, hobby from info order by id asc, hobby desc;)
5. 区间判断及查询不重复记录
①AND/OR ——且/或
and:同时满足
or:只要满足一个条件都会进行匹配
select * from ba where score >70 and score <=90;
select * from ba where score >70 or score <=90;
没有结果,因为or只要满足一个就可以,<=90的条件都满足,所以全部显示
②嵌套/多条件
select * from ba where score >70 or (score >75 and score <90);
③distinct查询不重复记录
多字段去重没有意义了,所以更多用于单字段的去重
select distinct 字段 from 表名﹔
select distinct hobbid from ba;
PS:以下语句是否可以筛重
select name, hobbid from ba where hobbid in (select distinct hobbid from ba);
1..distinct必须放在最开头
2. distinct只能使用需要去重的字段进行操作。
----也就是说我sidtinct了name , hobby两个字段,我后面想根据id进行排序,是不可以的,因为只能name,age两个字段进行操作.
3.distinct去重多个字段时,含义是:几个字段同时重复时才会被过滤。
6.对结果进行分组
通过 SQL 查询出来的结果,还可以对其进行分组,使用 GROUP BY 语句来实现 ,GROUP BY 通常都是结合聚合函数一起使用的,常用的聚合函数包括:计数(COUNT)、 求和(SUM)、求平均数(AVG)、最大值(MAX)、最小值(MIN),GROUP BY 分组的时候可以按一个或多个字段对结果进行分组处理。
①语法
SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator valueGROUP BY column_name;
selet字段,聚合函数from表名,(where字段名(匹配)数值)group by字段名;
select count(name),score from bz where score>=45 group by score;
②分组排序
select count(id),hobbid from ba group by hobbid;
select count(id),hobbid from ba group by hobbid order by count(id) desc;
③分组条件
结合where语句,筛选分数大于等于80的分组,计算学生个数按降序排列
select count(name),score,hoddid from ba where score >=80 group by hobbid order by count(name) desc;
7.限制结果条目(limit)
limit 限制输出的结果记录
在使用 MySQL SELECT 语句进行查询时,结果集返回的是所有匹配的记录(行)。有时候仅 需要返回第一行或者前几行,这时候就需要用到 LIMIT 子句
①语法
SELECT column1, column2, ... FROM table_name LIMIT [offset,] number
LIMIT 的第一个参数是位置偏移量(可选参数),是设置 MySQL 从哪一行开始显示。 如果不设定第一个参数,将会从表中的第一条记录开始显示。需要注意的是,第一条记录的 位置偏移量是 0,第二条是 1,以此类推。第二个参数是设置返回记录行的最大数目。
②查询所有信息显示前4行
select * from ba limit 3;
③从第4行开始,往后显示3行
select * from ba limit 3,3;
④结合order by语句,按id的大小升序排列显示前三行
select id,name from ba order by id limit 3;
⑤基础select小的升阶 怎么输出最后三行
select id,name from ba order by id desc limit 3;
(limit 是做为位置偏移量的定义,他的起始是从0开始,而0表示的是字段)
8.设置别名(alias——> as)
在 MySQL 查询时,当表的名字比较长或者表内某些字段比较长时,为了方便书写或者 多次使用相同的表,可以给字段列或表设置别名。使用的时候直接使用别名,简洁明了,增强可读性
①语法
对于列的别名:SELECT column_name AS alias_name FROM table_name;
对于表的别名:SELECT column_name(s) FROM table_name AS alias_name;
在使用 AS 后,可以用 alias_name 代替 table_name,其中 AS 语句是可选的。AS 之后的别名,主要是为表内的列或者表提供临时的名称,在查询过程中使用,库内实际的表名或字段名是不会被改变的
②列别名设置
select name as 姓名,score as 成绩 from ba;
#如果表的长度比较长,可以使用 AS 给表设置别名,在查询的过程中直接使用别名
③临时设置表的别名
select i.name as 姓名,i.score as 成绩 from ba as i; (as可以省略)
④查询表的字段数量,以number显示
select count(*) as number from ba;
不用as也可以一样显示
select count(*) number from ba;
⑤使用场景:
⑴对复杂的表进行查询的时候,别名可以缩短查询语句的长度
⑵多表相连查询的时候(通俗易懂、减短sql语句)
此外,AS还可以作为连接语句的操作符
⑥创建表,将ba表的查询记录全部插入新表
create table t1 as select * from ba;
#此处AS起到的作用:
1、创建了一个新表t1 并定义表结构,插入表数据(与info表相同)
2、但是”约束“没有被完全”复制“过来 #但是如果原表设置了主键,那么附表的:default字段会默认设置一个0
相似:
#克隆、复制表结构
create table t2 (select * from ba);
#也可以加入where 语句判断
create table t3 as select * from ba where sccore >=60;
在为表设置别名时,要保证别名不能与数据库中的其他表的名称冲突。
列的别名是在结果中有显示的,而表的别名在结果中没有显示,只在执行查询时使用。
9.通配符
通配符主要用于替换字符串中的部分字符,通过部分字符的匹配将相关结果查询出来。
通常通配符都是跟 LIKE(模糊查询) 一起使用的,并协同 WHERE 子句共同来完成查询任务。常用的通配符有两个,分别是:
%:百分号表示零个、一个或多个字符 *
_:下划线表示单个字符 .
①查询名字是l开头的记录
select id,name from ba where name like ‘l%’;
②查询名字里是l和u中间有一个字符的记录
select id,name from ba where name like ‘l_u_i’;
③查询名字中间有a的记录
select id,name from ba where name like ‘%a%’;
④查询hanmei后面3个字符的名字记录
select id,name from ba where name like ‘jia___’;
⑤通配符“%”和“_”不仅可以单独使用,也可以组合使用
查询名字以t开头的记录
select id,name from ba where name like ‘t%_’;
10.子查询
子查询也被称作内查询或者嵌套查询,是指在一个查询语句里面还嵌套着另一个查询语句。
子查询语句是先于主查询语句被执行的,其结果作为外层的条件返回给主查询进行下一步的查询过滤。
PS: 子语句可以与主语句所查询的表相同,也可以是不同表
①相同表
select name,score from ba where id in (select id from ba where score >80);
主语句:select name,score from info where id
子语句(集合): select id from info where score >80
PS:子语句中的sql语句是为了,最后过滤出一个结果集,用于主语句的判断条件
in:将主表和子表关联/连接的语法
②不同表/多表
create table ma (id int(4));
insert into ma values(1),(2),(3);
select id,name,score from ba where id in (select * from ma);
子查询不仅可以在 SELECT 语句中使用,在 INERT、UPDATE、DELETE 中也同样适用。在嵌套的时候,子查询内部还可以再次嵌套新的子查询,也就是说可以多层嵌套。
子查询还可以用在 INSERT 语句中。子查询的结果集可以通过 INSERT 语句插入到其他的表中
③将t1里的记录全部删除,重新插入ba表的记录
inset into t1 select * from ba where id in(select id from ba);
④update语句也可以使用子查询
UPDATE 内的子查询,在 set 更新内容时,可以是单独的一列,也可以是多列。
将某个用户的分数改为50
update ba set score=50 where id in(select * from ma where id=2);
⑤delete也适用于子查询
删除分数大于80的记录
delete from t2 where id in(select id where score>80);
select id,name,score from t2;
在 IN 前面还可以添加 NOT,其作用与 IN 相反,表示否定(即不在子查询的结果集里面)
删除分数不是大于等于80的记录
delete from t2 where id not in (select id where score>=80);
表示先匹配出ba表内的id字段为基础匹配的结果集(分数>=80)
然后再执行主语句,以主语句的id为基础 进行where 条件判断/过滤,
11.子查询-exists
EXISTS 这个关键字在子查询时,主要用于判断exists之后的条件是否成立,如果成立,则正常执行主语句的匹配,如不成立,则不会执行主语句查询
如子查询结果集不成立的话,输出为null
加exists:只是为了判断exists之后的条件是否成立,如果成立,则正常执行主语句的匹配,如不成立,则不会执行主语句查询
PS: count为计数,sum为求和,使用sum求和结合exists,如子查询结果集不成立的话,输出为null
①查询如果存在分数等于80的记录则计算ba的字段数
select count(*) from ba where exists(select id from ba where score=80);
②查询如果存在分数大于90的记录则计算ba的字段数,ba表没有大于90的,所以返回0
select count(*) from ba where exists(select id from ba where score>90);
#子查询,别名as
查询info表id,name 字段
select id,name from ba;
以上命令可以查看到ba表的内容
#将结果集做为一张表进行查询的时候,我们也需要用到别名,示例:
需求:从info表中的id和name字段的内容做为"内容" 输出id的部分
mysql> select id from (select id,name from ba);
ERROR 1248 (42000): Every derived table must have its own alias
#此时会报错,因为:
select * from 表名 此为标准格式,而以上的查询语句,"表名"的位置其实是一个结果集,mysql并不能直接识别,而此时给与结果集设置一个别名,以”select a.id from a“的方式查询将此结果集是为一张"表",就可以正常查询数据了,如下:
select a.id from (select id,name from ba) a;
相当于
select info.id,name from info;
select 表.字段,字段 from 表;
二、数据库视图
视图是从一个或多个表中导出来的表,是一种虚拟存在的表。
视图就像一个窗口,通过这个窗口可以看到系统专门提供的数据。
这样,用户可以不用看到整个数据库中的数据,而之关心对自己有用的数据。
数据库中只存放了视图的定义,而没有存放视图中的数据,这些数据存放在原来的表中。
使用视图查询数据时,数据库系统会从原来的表中取出对应的数据。
视图中的数据依赖于原来表中的数据,一旦表中数据发生改变,显示在视图中的数据也会发生改变。数据库中的虚拟表,这张虚拟表中不包含真实数据,只是做了映射
1.视图的作用
①使操作简单化,可以对经常使用的查询定义一个视图,使用户不必为同样的查询操作指定条件
②增加数据的安全性,通过视图,用户只能查询和修改指定的数据。
③提高表的逻辑独立性,视图可以屏蔽原有表结构变化带来的影响。
④简化查询结果集、灵活查询、可以针对不同用户呈现不同结果集、相对有更高的安全性
⑤视图适合于多表连接浏览时使用;不适合增、删、改
⑥而存储过程适合于使用较频繁的SQL语句,这样可以提高执行效率!
2.视图和表的区别
①视图是已经编译好的sql语句;而表不是
②视图没有实际的物理记录;而表有
③表只用物理空间而视图不占用物理空间,视图只是逻辑概念的存在,表可以及时对它进行修改, 但视图只能有创建的语句来修改
④视图是查看数据表的一种方法,可以查询数据表中某些字段构成的数据,只是一些SQL语句的集合。从安全的角度说,视图可以不给用户接触数据表,从而不知道表结构。
⑤表属于全局模式中的表,是实表;视图属于局部模式的表,是虚表
⑥视图的建立和删除只影响视图本身,不影响对应的基本表。
3.视图和表的联系
①视图(view)是在基本表之上建立的表,它的结构(即所定义的列)和内容(即所有数据行)都来自表,它依据基本表存在而存在
②一个视图可以对应一个基本表,也可以对应多个基本表。视图本表的抽象和在逻辑意义上建立的新关系。
4.单表创建视图
create view v_score as select * from ba where score>=80;
update ba set score='100' where name='lisi';
5.多表创建视图
create table t1 (id int,name varchar(10),age char(10));
insert into t1 values(1,'zhangsan','20'),(2,'lisi',25),(3,'wangwu',22);
create view v_ba(id,name,score,age) as select ba.id,ba.name,ba.score,t1.age from ba,t1 where ba.name=t1.name;
创建视图 视图叫v_组合,字段是id、name、score、t1.age来源于ba.id、ba.name、ba.score、t1.age,来自于ba和t1表,同时where判断ba表中的name等于t1表内的name字段
三、null值
在 SQL 语句使用过程中,经常会碰到 NULL 这几个字符。通常使用 NULL 来表示缺失 的值,也就是在表中该字段是没有值的。如果在创建表时,限制某些字段不为空,则可以使 用 NOT NULL 关键字,不使用则默认可以为空。在向表内插入记录或者更新记录时,如果该字段没有 NOT NULL 并且没有值,这时候新记录的该字段将被保存为 NULL。需要注意 的是,NULL值与数字 0 或者空白(spaces)的字段是不同的,值为NULL的字段是没有值的。在SQL语句中,使用 IS NULL 可以判断表内的某个字段是不是NULL值,相反的用IS NOT NULL可以判断不是NULL值。
查询ba表结构,id和name字段是不允许空值的
1. null值与空值的区别(空气与真空)
①空值长度为0,不占空间,NULL值的长度为null,占用空间
②is null无法判断空值
③空值使用"=“或者”<>"来处理(!=)
④count()计算时,NULL会忽略,空值会加入计算
插入一条记录,显示出来是null
alter table ba add column addr varchar(50);
update ba set addr=’nanjing’ where score >=70;
select count(addr) from ba;
统计数量:检测null不会加入统计中
update ba set addr=‘’ where name =‘hanmeimei’;#ba表中其中name等于tianqi修改为空值
select count(addr) from ba;
统计数量,检测空值反而会被添加到统计中
select * from ba where addr is null; 查询空值的判断方法;
select * from ba where addr is not null; 查询不是空值的判断方法;
1.排序
2.按照关键字排序
3.多字段排序:优先级是按先后顺序
4.区间判断及查询不重复记录,结合select使用
4.1and:同时满足
or:只需满足一个条件都会进行匹配
5.嵌套/多条件
6.distinct查询不重复记录
7.对结果进行分组
8.限制结果条目-limit:select id,name from ba order by id desc limit3;输出最后三行需结合oede by语句
9.设置别名-alias
10.通配符
%:百分号表示零个、一个或多个字符
_:下划线表示单个字符
11.子查询
11.1结合select使用
11.2子查询多层嵌套
二、视图
1.单表
create view v_score as select * from ba where score >=80;
select * from v_score;
2.多表
create view v_ba(id,name,score,age) as select ba.id,ba.name,ba.score,t1.age from ba,t1 where ba.name=t1.name;
3.修改
更改数据会影响原本视图
三、null
null值与空值的区别(空气与真空)
空值长度为0,不占空间,NULL值的长度为null,占用空间
is null无法判断空值
空值使用"=“或者”<>"来处理(!=)
count()计算时,NULL会忽略,空值会加入计算