厦门找一家做网站的公司好,云南省建设造价协会网站,上海小程序开发定制公司,医院预约挂号系统网站开发方案MySQL高级语句(第一部分)一、MySQL进阶查询1、select ----显示表格中一个或数个字段的所有数据记录2、distinct ----不显示重复的数据记录3、where ----有条件查询4、and or ----且 或5、in ----显示已知的值的数据记录6、between ----显示两个值范围内的数据记录7、通配符8、l… MySQL高级语句(第一部分)一、MySQL进阶查询1、select ----显示表格中一个或数个字段的所有数据记录2、distinct ----不显示重复的数据记录3、where ----有条件查询4、and or ----且 或5、in ----显示已知的值的数据记录6、between ----显示两个值范围内的数据记录7、通配符8、like ----模糊匹配9、order by10、group by----汇总分组11、having12、别名 ----字段別名 表格別名13、子查询14、exists15、练习 二、MySQL数据库函数1、数学函数2、聚合函数3、字符串函数3.1 trim3.2 concat3.3 substr3.4 length3.5 replace 三、连接查询1、表连接2、union语句3、多表查询之求交集值4、多表查询之求无交集值 四、SQL语句执行顺序 MySQL高级语句(第一部分)
一、MySQL进阶查询
先建立www数据库再建立location 表和Store_Info 表用于测试和演示
create database www;use www;
create table location (Region char(20),Store_Name char(20));
insert into location values(East,Boston);
insert into location values(East,New York);
insert into location values(West,Los Angeles);
insert into location values(West,Houston);create table store_info (Store_Name char(20),Sales int(10),Date char(10));
insert into store_info values(Los Angeles,1500,2020-12-05);
insert into store_info values(Houston,250,2020-12-07);
insert into store_info values(Los Angeles,300,2020-12-08);
insert into store_info values(Boston,700,2020-12-08);1、select ----显示表格中一个或数个字段的所有数据记录
语法select 字段 from 表名;举例select * from store_info;select store_name from store_info;2、distinct ----不显示重复的数据记录
语法select distinct 字段 from 表名;举例
select distinct store_name from store_info;3、where ----有条件查询
语法select 字段 from 表名 where 条件;举例select * from store_info where store_nameLos Angeles;select * from store_info where sales 1000;select * from store_info where sales ! 1500;
# !不等于4、and or ----且 或
语法select 字段 from 表名 where 条件1 {[and|or] 条件2} ;举例select * from store_info where sales 1000 or (sales 200 and sales 500);
#sales1000或sales200且sales5005、in ----显示已知的值的数据记录
语法select 字段 from 表名 where 字段 in (值1, 值2, ...);举例select * from store_info where store_name in (Houston,Boston);
#显示指定项的数据记录select * from store_info where store_name not in (Houston,Boston);6、between ----显示两个值范围内的数据记录
语法select 字段 from 表名 where 字段 between 值1 and 值2;举例select * from store_info where sales between 300 and 1000;7、通配符
----通常通配符都是跟 like 一起使用的
% 百分号表示零个、一个或多个字符
_ 下划线表示单个字符A_Z所有以 A 起头另一个任何值的字符且以 Z 为结尾的字符串。例如ABZ 和 A2Z 都符合这一个模式而 AKKZ 并不符合 (因为在 A 和 Z 之间有两个字符而不是一个字符)。
ABC%: 所有以 ABC 起头的字符串。例如ABCD 和 ABCABC 都符合这个模式。
%XYZ: 所有以 XYZ 结尾的字符串。例如WXYZ 和 ZZXYZ 都符合这个模式。
%AN%: 所有含有 AN这个模式的字符串。例如LOS ANGELES 和 SAN FRANCISCO 都符合这个模式。
_AN%所有第二个字母为 A 和第三个字母为 N 的字符串。例如SAN FRANCISCO 符合这个模式而 LOS ANGELES 则不符合这个模式。8、like ----模糊匹配
----匹配一个模式来找出我们要的数据记录
模糊匹配默认会扫描全表索引不生效
语法select 字段 from 表名 where 字段 like {模式};举例select * from store_info where store_name like %os%;
#模糊匹配store_name列包含os的数据记录9、order by
----按关键字排序
语法select 字段 from 表名 [where 条件] order by 字段 [asc, desc];
#ASC 是按照升序进行排序的是默认的排序方式。
#DESC 是按降序方式进行排序。#举例
select * from store_info order by sales asc;
#升序排序
select * from store_info order by sales desc;
#降序排序10、group by----汇总分组
----对GROUP BY后面的字段的查询结果进行汇总分组通常是结合聚合函数一起使用的
GROUP BY 有一个原则凡是在 GROUP BY 后面出现的字段必须在 SELECT 后面出现 凡是在 SELECT 后面出现的、且未在聚合函数中出现的字段必须出现在 GROUP BY 后面
语法select 字段1, sum(字段2) from 表名 group by 字段1;select store_name, sum(sales) from store_info group by store_name order by sales desc;
#汇总销售总额然后排序11、having
----用来过滤由 GROUP BY 语句返回的记录集通常与 GROUP BY 语句联合使用
HAVING 语句的存在弥补了 WHERE 关键字不能与聚合函数联合使用的不足。
语法select 字段1, sum(字段2) from 表格名 group by 字段1 having (函数条件);select store_name, sum(sales) from store_info group by store_name having sum(sales) 1500;
#返回sales1500的数据12、别名 ----字段別名 表格別名
语法select 表格別名.字段1 [as] 字段別名 from 表格名 [as] 表格別名;#举个例子
select a.store_name store, sum(a.sales) as total sales from store_info as a group by a.store_name;
#sum函数别名定义为total sales,as可省略store_info表别名定义为a别名定义后当前SQL语句中所有用到store_info表的地方都能用a代替13、子查询
----连接表格在WHERE 子句或 HAVING 子句中插入另一个 SQL 语句
语法select 字段1 from 表格1 where 字段2 [比较运算符] (select 字段1 from 表格2 where 条件);
#外查询 (#内查询)
#内查询的结果作为外查询的参数[比较运算符]
#可以是符号的运算符例如 、、、、
#也可以是文字的运算符例如 like、in、between#举例1
select sum(sales) from store_info where store_name in (select store_name from location where region West);
#外查询返回符合的数据记录的销售总额
#内查询的结果作为外查询的参数#举例2
select sum(A.sales) from store_info as A where A.store_name in (select store_name from location as B where B.store_name A.store_name);
#store_info表 别名为A表在当前语句中可以直接用a代替store_info使用
#location表 别名为B表14、exists
用来测试内查询有没有产生任何结果类似布尔值是否为真
如果有的话系统就会执行外查询中的SQL语句。若是没有的话那整个 SQL 语句就不会产生任何结果
语法select 字段1 from 表格1 where exists (select * from 表格2 where 条件;#举个例子
select sum(sales) from store_info where exists (select * from location where region West);
#存在region为West的数据记录继续执行外查询
select sum(sales) from store_info where exists (select store_name from location where region Westt);
#不存在region为Westt的数据记录不会继续执行外查询返回Null15、练习 group by store_name having count(store_name) 2;select store_name from store_info group by store_name having count(store_name) 2;select store_name,count(store_name) from store_info group by store_name having count(store_name) 2;二、MySQL数据库函数
1、数学函数
数字函数功能abs(x)返回 x 的绝对值rand()返回 0 到 1 的随机数mod(x,y)返回 x 除以 y 以后的余数power(x,y)返回 x 的 y 次方round(x)返回离 x 最近的整数round(x,y)保留 x 的 y 位小数四舍五入后的值sqrt(x返回 x 的平方根truncate(x,y)返回数字 x 截断为 y 位小数的值ceil(x)返回大于或等于 x 的最小整数floor(x)返回小于或等于 x 的最大整数greatest(x1,x2…)返回集合中最大的值也可以返回多个字段的最大的值least(x1,x2…)返回集合中最小的值也可以返回多个字段的最小的值
select abs(-1), rand(), mod(5,3), power(2,3), round(1.89);
#-1的绝对值随机数5 3取余数2的3次方1.89四舍五入select round(1.8937,3), truncate(1.235,2), ceil(5.2), floor(2.1), least(1.89,3,6.1,2.1);
#1.8937第三位四舍五入1.235取2位小数大于或者等于5.2的整数2.1的整数合集中的最小值2、聚合函数
聚合函数功能avg()返回指定列的平均值count()返回指定列中非 NULL 值的个数min()返回指定列的最小值max()返回指定列的最大值sum(x)返回指定列的所有值之和
avgselect avg(sales) from store_info;
#求平均值count#count(*) 包括了所有的列的行数在统计结果的时候不会忽略列值为 NULL #count(列名) 只包括列名那一列的行数在统计结果的时候会忽略列值为 NULL 的行
select count(store_name) from store_info;
#非null行数重复的单独计数
select count(distinct store_name) from store_info;
#去重
select count(*) from store_info;
#统计所有列的行数包括null行max和minselect max(sales) from store_info;
#返回最大值
select min(sales) from store_info;
#返回最小值sumselect sum(sales) from store_info;
#sales列的数据记录的和3、字符串函数
字符串函数功能trim()返回去除指定格式的值concat(x,y)将提供的参数 x 和 y 拼接成一个字符串substr(x,y)获取从字符串 x 中的第 y 个位置开始的字符串跟substring()函数作用相同substr(x,y,z)获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串length(x)返回字符串 x 的长度replace(x,y,z)将字符串 z 替代字符串 x 中的字符串 yupper(x)将字符串 x 的所有字母变成大写字母lower(x)将字符串 x 的所有字母变成小写字母left(x,y)返回字符串 x 的前 y 个字符right(x,y)返回字符串 x 的后 y 个字符repeat(x,y)将字符串 x 重复 y 次space(x)返回 x 个空格strcmp(x,y)比较 x 和 y返回的值可以为-1,0,1reverse(x)将字符串 x 反转
3.1 trim
select trim ([ [位置] [要移除的字符串] from ] 字符串);
#[位置]的值可以为 leading (起头), trailing (结尾), both (起头及结尾)。
#[要移除的字符串]从字串的起头、结尾或起头及结尾移除的字符串。缺省时为空格。select trim(leading Los from (select store_name from location where store_nameLos Angeles));
#去除store_name字段中Los Angeles的Los ,只提取Angeles
select trim(trailing York from (select store_name from location where store_nameNew York));
#去除store_name字段中New York的York ,只提取New3.2 concat
字段名 不要加
字符串 要加
select concat (region , ,store_name) from location;
#拼接region和store_name字段并且在中间加上空格select region || store_name from location;3.3 substr
select substr(store_name,5) from location where store_name Los Angeles;
#Los Angeles第5个位置开始的字符串
select substr(store_name,5,6) from location where store_name Los Angeles;
#第5个位置开始长度为6的字符3.4 length
select store_name,length(store_name) from location;
#返回字段store_name中字符串的长度3.5 replace
select replace (region,st,stern) from location;
#把region字段st结尾替换成stern结尾三、连接查询
1、表连接
表连接概述inner join内连接只返回两个表中联结字段相等的行记录left join左连接返回包括左表中的所有记录和右表中联结字段相等的记录不相等的部分返回NULLright join右连接返回包括右表中的所有记录和左表中联结字段相等的记录不相等的部分返回NULL
修改表数据
update store_info set store_nameWashington where sales300;内连接一
select * from location A inner join store_info B on A.store_name B.store_name;
内连接二
select * from location A, store_info B where A.store_name B.store_name;
#通过多表查询实现右连接
select * from location A right join store_info B on A.store_name B.store_name;左连接
select * from location A left join store_info B on A.store_name B.store_name;2、union语句
联集将两个SQL语句的结果合并起来两个SQL语句所产生的字段需要是同样的数据记录种类
union生成结果的数据记录值将没有重复且按照字段的顺序进行排序
语法[select 语句 1] union [select 语句 2];select store_name from location union select store_name from store_info;
#合并store_info表和location表中的store_name字段去重union all 将生成结果的数据记录值都列出来无论有无重复
语法[select 语句 1] union all [select 语句 2];select store_name from location union all select store_name from store_info;
#合并不去重所以会有重复的数据记录3、多表查询之求交集值
取两个SQL语句结果的交集
基本语法
select A.字段 from 左表 A inner join 右表 B on A.字段 B.字段;
select A.字段 from 左表 A inner join 右表 B using(同名字段);select A.字段 from 左表 A, 右表 B where A.字段 B.字段;
#多表查询select A.字段 from 左表 A where A.字段 in (select B.字段 from 右表 B);
#子查询select A.字段 from 左表 A left join 右表 B on A.字段 B.字段 where B.字段 is not null;
select B.字段 from 左表 A right join 右表 B on A.字段 B.字段 where A.字段 is not null;举例#方式一
select A.store_name from location A inner join store_info B on A.store_name B.store_name;
#返回两表中字段相等的记录#方式二
select A.store_name from location A inner join store_info B using (store_name);
#字段相同时可以直接使用using(字段)#取两个SQL语句结果的交集且没有重复
select distinct A.store_name from location A inner join store_info B on A.store_name B.store_name;
#inner join返回两表中相等的部分distinct去重select distinct A.store_name from location A inner join store_info B using (store_name) where B.store_name is not NULL;
#增加了条件判断非null的数据记录select A.store_name from (select B.store_name from location B inner join store_info C on B.store_name C.store_name) A group by A.store_name;
#通过子查询内连接查询group by排序返回store_name中相等的行记录select A.store_name from (select distinct store_name from location union all select distinct store_name from store_info) A group by A.store_name having count( *) 1;#首先子查询select distinct store_name from location从“location”表中选择所有不重复的店铺名称。
#然后子查询select distinct store_name from store_info从“store_info”表中选择所有不重复的店铺名称。
#使用union all将两个子查询的结果合并并作为临时表A。
#最后对临时表A按照店铺名称进行分组使用having count(*) 1筛选出出现次数大于1的店铺名称。4、多表查询之求无交集值
显示第一个SQL语句的结果且与第二个SQL语句没有交集的结果且没有重复
求左表无交集
select A.字段 from 左表 A left join 右表 B on A.字段 B.字段 where B.字段 is null;select 字段 from 左表 where 字段 not in (select 字段 from 右表);求右表无交集
select B.字段 from 左表 A right join 右表 B on A.字段 B.字段 where A.字段 is null;select 字段 from 右表 where 字段 not in (select 字段 from 左表);求多表的无交集
select A.字段 from (select distinct 字段 from 左表 union all select distinct 字段 from 右表) A group by A.字段 having count(A.字段)1;举例select distinct store_name from location where (store_name) not in ( select store_name from store_info);
#返回所有在location表中出现但在store_info表中不存在的店铺名称即无交集值#子查询select store_name from store_info从store_info表中选择所有的店铺名称。
#主查询select distinct store_name from location从location表中选择所有不重复的店铺名称。
#使用where (store_name) not in条件将主查询中的店铺名称过滤掉那些在子查询结果中出现的店铺名称。select distinct A.store_name from location A left join store_info B using (store_name) where B.store_name is NULL;
#返回在location表中出现但在store_info表中不存在的店铺名称#使用left join将location表作为左表记为A和store_info表作为右表记为B按照店铺名称进行连接。
#使用using (store_name)条件指定以店铺名称为连接的字段。
#使用where B.store_name is NULL条件过滤掉在连接结果中店铺名称在location表中出现但在store_info表中没有匹配的记录。
#最后使用distinct关键字来返回不重复的店铺名称。select A.store_name from (select distinct store_name from location union all select distinct store_name from store_info) as A group by A.store_name having count(*)1;
#返回只在location表或store_info表中出现一次的店铺名称即只出现在其中一个表中但没有重复出现的店铺名称#子查询select distinct store_name from location从location表中选择所有不重复的店铺名称。
#子查询select distinct store_name from store_info从store_info表中选择所有不重复的店铺名称。
#使用union all将两个子查询的结果合并。
#将合并结果作为临时表A并使用as A来给临时表起一个别名。
#在临时表A的基础上使用group by A.store_name对店铺名称进行分组。
#使用having count(*) 1筛选出出现次数为1的店铺名称。四、SQL语句执行顺序
FROM
left tableON
join_condition
join_typeJOIN
right_tableWHERE
where conditionGROUP BY
group_by_listHAVING
having_conditionSELECTDISTINCT
select listORDER BY
order_by_conditionLIMIT
limit number########################################################################################################
在SQL中一般而言SQL查询语句的执行顺序如下1. FROM指定要查询的数据表或视图。
2. JOIN根据指定的条件连接多个表。
3. WHERE基于指定的条件筛选出符合要求的行。
4. GROUP BY按照指定的列进行分组。
5. HAVING对分组后的结果进行条件筛选。
6. SELECT选择要返回的列。
7. DISTINCT去除重复的行。
8. ORDER BY按照指定的列进行排序。
9. LIMIT/OFFSET限制返回的结果数量和起始位置。
