1.全套装备
select [select选项] 字段列表[字段别名]/*from 数据源[where 条件子句] [group by条件子句] [having 子句] [order by 子句] [limit 子句];
什么是select选项呢?select选项是值select对查出来的结果的处理方式,主要有两种。all:默认的,保留所有的结果;distinct: 对查询结果进行去重;
Select select_expr [,select_expr…]
[From table_references
[ where where_condition]
[group by {col_name | position} [asc|desc],… ][having where_condition]
[order by {col_name | expr | position} [asc|desc],… ][limit {[offset,] row_count|row_count offset offset} ]
]
2.表中所有信息的查询 *
select * from student;
3.去重 distinct
select distinct class fromstudent;select distinct class,teacher from student;
显然,所查询的列的值组合起来的行完全一样才会被去重。
4.指定列排序 order by,asc,desc
select * from student order by col_name {asc|desc};
asc 数值 :递增,字母:字典序,默认排序
desc 数值:递减, 字母:反序
如果有多个列,例如先按姓、再按名则在order by后用逗号[,]分隔开,每个列都可以指定排序方式。
如果是文本的A和a,哪个大取决于数据库具体设置。
5.别名 as
select 列名 as 别名[,列名2 as 别名,...] from table_name;
6.列查询(不同顺序结果不同)
select col_name[,col_name2...] from table_name;
7.聚合函数查询
sum()、avg()、max()、min()分别表示对某一列求和、平均、最大、最小。
count()表示查询记录出现的数量,count(列)不算null值的,count(*)有算null值。
select sum(age),avg(age),max(id),min(age),count(teacher) from student
8.查询过滤 where(where子句不能用别名)
字符串类型的列比较需要加上单引号['],数值则不需要。
如果同时有and 和 or,会先识别and,例如A or B and C这样就直接忽略掉了A,通过括号区分。
in操作符会比or更快,例如id in(A,B) 和 id=A or id=B比较
添加了几条记录测试模糊查询
模糊查询的通配符:"%"表示任意长度的字符串(包括空字符但不包括空值),“_”表示单个字符。这里的汉字和字母都算1个字符。
通配符和正则表达式要求MySQL尝试遍历所有行!
Select id as myid, name as myname ,teacher from student where teacher like ‘老%’;--查询以"老"开头的
Select id as myid, name as myname ,teacher from student where teacher like ‘%’;--查询所有记录,除了null
Select * from student where teacher like ‘老_’;--查询以老开头的两个字的记录
9.限制查询数量 limit
select * from student where age<30 limit 4;--默认从第一条记录开始搜寻,查找出4条记录就停止
select * from student limit 2,5;--记录的索引从0开始,这里限制从索引为2的开始即第3条语句,查找出5条语句--如果数量不够则显示不出那么多记录
10.分组查询 group by
(1)最普通的用法,对于某一列相同的值只取1条语句,尝试后推测应该是记录中的第一条出现该列值的记录
select * from student group by class;
这样的记录显然没什么用,和去重效果一样,所以运用时需要加点东西。
(2)想知道该列的各个分组除了第一条记录的其他记录 group_concat(列名)
select group_concat(id),group_concat(name),age,class from student group by class;
显然,没有使用group_concat()函数的都只显示一个值,id和name显示了所有的值并且一一对应,但是没有顺序可言,第4条记录的id可以看出。
(3)如果记录太多看不清一个组到底有多少人,用count()函数嵌套
select count(*),group_concat(name),group_concat(teacher),class from student group byclass;select count(*),group_concat(name),count(teacher),class from student group by class;
由图可知这样查询非常人性化,注意区分count(*)和count(列名)对于null值的计数。
其他聚合函数同理,例如查询每个班最大年龄的人则可以用max()函数嵌套。
11.二次筛选 having
担心数据不够又加了几组记录
where和having的区别:where作用于表或视图,从中选择满足条件的元组。having短语作用于组,从中选择满足条件的组(所以需要与group by配合使用)。where是从查询满足条件的数据,用于查询数据之前;having用于在查出的数据中挑选满足条件的数据,在数据查出来之后处理。并且having的子句不能使用没有查出来的列名,where则可以,如下:
12.子查询 where
出现在其他sql语句内的select子句嵌套在查询内部,必须始终出现在圆括号内;可以包含多个关键字或条件 distinct,group by,order by,limit和函数等;子查询的外层查询可以是 select insert update set do 等。子查询返回的结果可以是标量、一行、一列或者是子查询。
select * from t1 where col1 = ( select col2 from t2);
13.连接查询
左表 inner|left|right join 右表 on 条件
(1)内连接
查询左右表的交集
(2)左连接
左表全部查出来;右表显示符合搜索条件的记录,不符合条件的记录显示为NULL
(3)右连接
右表全部查出来;左表显示符合搜索条件的记录,不符合条件的记录显示为NULL
selectA.id,B.name,C.agefromAtable Ainner|left|right joinBtable BonA.id=B.idinner|left|right joinCtable ConA.id=C.idwhere...
(4)例题
有两个表:
表1dept包含列dept_id,dept_name,parent_id,bi_parent_id
表2dept_thirdparty包含列dept_id,thirdparty_id
关系:
两个表以dept_id关联,表1中每个dept_id对应一个dept_name、parent_id、bi_parent_id,每个parent_id和bi_parent_id也是一个dept_id,表2中每个dept_id对应一个thirdparty_id。
要求:
查询出parent_id和bi_parent_id不同的dept_id(门店)的thirdparty_id,dept_name,parent_id对应的thirdparty_id,parent_id对应的dept_name,bi_parent_id对应的thirdparty_id,bi_parent_id对应的dept_name
SELECTb. thirdparty_id,
a. dept_name,
c. thirdparty_id'parent_id对应的thirdparty_id',
d. dept_name'parent_id对应的dept_name',
e. thirdparty_id'bi_parent_id对应的thirdparty_id',
f. dept_name'bi_parent_id对应的dept_name'
FROMdept aINNER JOIN dept_thirdparty b ON a. dept_id =b. dept_idINNER JOIN dept_thirdparty c ON a. parent_id =c. dept_idINNER JOIN dept d ON c. dept_id =d. dept_idINNER JOIN dept_thirdparty e ON a. bi_parent_id =e. dept_idINNER JOIN dept f ON e. dept_id =f. dept_idWHEREa. parent_id!= a. bi_parent_id
14.视图
视图是一张虚拟的表,包含的是一条SQL语句查出来的内容。视图的应用在于 查看存储在别处的(部分)数据,可以起到限制作用,保护数据,也可以起到化简作用,隐藏复杂的SQL语句。视图有这么几个规则和限制
视图名唯一;
创建的视图数目没有限制;
创建视图必须有足够的访问权限;
视图可以嵌套,用A视图查出的数据构建B视图;
order by可以用在视图中,但如果从该视图检索数据select中也含有order by,那么该视图中的order by将被覆盖;
视图不能被索引,也不能有关联的触发器和默认值;
视图可以和表一起使用,可以编写一条联结表和视图的SQL语句;
create view viewname as select... 创建视图
show create view viewname 查看视图
视图不是真实的表,所以不能更新,所谓的"更新"实际上是对所映射的表更新。