本文详细介绍了在MySQL中进行实验环境的准备，包括创建和操作两个数据表，以及深入讲解了高阶查询的语句与命令、实验操作，涉及select、distinct、where、and/or、in、between、like等查询技巧，以及MySQL中的数学、聚合和字符串函数的使用实例。
目录
一、实验环境准备
#创建两个数据表，为实验提供环境：
use kgc; #选择数据库，有则直接使用 无则按照以下步骤自建即可。
表一：
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');
location 表格
+----------+--------------+
| Region | Store_Name |
|----------+--------------|
| East | Boston |
| East | New York |
| West | Los Angeles |
| 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');
Store_Info 表格
+--------------+---------+------------+
| Store_Name | Sales | Date |
|--------------+---------+------------|
| Los Angeles | 1500 | 2020-12-05 |
| Houston | 250 | 2020-12-07 |
| Los Angeles | 300 | 2020-12-08 |
| Boston | 700 | 2020-12-08 |
+--------------+---------+------------+
二、MySQL高阶查询
1、语句与命令
1、---- select ----显示表格中一个或数个字段的所有数据记录
语法：select "字段" from "表名";
select Store_Name from Store_Info;
2、---- distinct ----不显示重复的数据记录
语法：select distinct "字段" from "表名";
select distinct store_name from store_info;
3、---- where ----有条件查询
语法：select "字段" from "表名" where "条件";
select store_name from store_info where sales > 1000;
4、---- and or ----且 或
语法：select "字段" from "表名" where "条件1" {[and|or] "条件2"}+ ;
select store_name from store_info where sales > 1000 or (sales < 500 and sales > 200);
5、---- in ----显示已知的值的数据记录
语法：select "字段" from "表名" where "字段" in ('值1', '值2', ...);
select * from store_info where store_name in ('los angeles','houstton');
6、---- between ----显示两个值范围内的数据记录
语法：select "字段"from "表名" where "字段" between '值1' and '值2';
select * from store_info where date between '2020-12-06' and '20200-12-10';
7、---- 通配符 ----通常通配符都是跟 like 一起使用的
% ：百分号表示零个、一个或多个字符
_ ：下划线表示单个字符
1）'A_Z'：所有以 'A' 起头，另一个任何值的字符，且以 'Z' 为结尾的字符串。例如，'ABZ' 和 'A2Z' 都符合这一个模式，而 'AKKZ' 并不符合 (因为在 A 和 Z 之间有两个字符，而不是一个字符)。
2）'ABC%': 所有以 'ABC' 起头的字符串。例如，'ABCD' 和 'ABCABC' 都符合这个模式。
3）'%XYZ': 所有以 'XYZ' 结尾的字符串。例如，'WXYZ' 和 'ZZXYZ' 都符合这个模式。
4）'%AN%': 所有含有 'AN'这个模式的字符串。例如，'LOS ANGELES' 和 'SAN FRANCISCO' 都符合这个模式。
5）'_AN%'：所有第二个字母为 'A' 和第三个字母为 'N' 的字符串。例如，'SAN FRANCISCO' 符合这个模式，而 'LOS ANGELES' 则不符合这个模式。
例：---- like ----匹配一个模式来找出我们要的数据记录
语法：select "字段" from "表名" where "字段" like {模式};
select * from store_info where store_name like '%os%';
8、---- order by ----按关键字排序
语法：select "字段" from "表名" [where "条件"] order by "字段" [asc, desc];
#asc 是按照升序进行排序的，是默认的排序方式。
#desc 是按降序方式进行排序。
select store_name,sales,date from store_info order by sales desc;2、实验实操
1、---- select ----显示表格中一个或数个字段的所有数据记录
语法：select "字段" from "表名";
select Store_Name from Store_Info;
2、---- distinct ----不显示重复的数据记录
语法：select distinct "字段" from "表名";
select distinct store_name from store_info;
3、---- where ----有条件查询
语法：select "字段" from "表名" where "条件";
select store_name from store_info where sales > 1000;
4、---- and or ----且 或
语法：select "字段" from "表名" where "条件1" {[and|or] "条件2"}+ ;
select store_name from store_info where sales > 1000 or (sales < 500 and sales > 200);
5、---- in ----显示已知的值的数据记录
语法：select "字段" from "表名" where "字段" in ('值1', '值2', ...);
select * from store_info where store_name in ('los angeles','houstton');
6、---- between ----显示两个值范围内的数据记录
语法：select "字段"from "表名" where "字段" between '值1' and '值2';
select * from store_info where date between '2020-12-06' and '20200-12-10';
7、---- 通配符 ----通常通配符都是跟 like 一起使用的
% ：百分号表示零个、一个或多个字符
_ ：下划线表示单个字符1）'A_Z'：所有以 'A' 起头，另一个任何值的字符，且以 'Z' 为结尾的字符串。例如，'ABZ' 和 'A2Z' 都符合这一个模式，而 'AKKZ' 并不符合 (因为在 A 和 Z 之间有两个字符，而不是一个字符)。
2）'ABC%': 所有以 'ABC' 起头的字符串。例如，'ABCD' 和 'ABCABC' 都符合这个模式。
3）'%XYZ': 所有以 'XYZ' 结尾的字符串。例如，'WXYZ' 和 'ZZXYZ' 都符合这个模式。
4）'%AN%': 所有含有 'AN'这个模式的字符串。例如，'LOS ANGELES' 和 'SAN FRANCISCO' 都符合这个模式。
5）'_AN%'：所有第二个字母为 'A' 和第三个字母为 'N' 的字符串。例如，'SAN FRANCISCO' 符合这个模式，而 'LOS ANGELES' 则不符合这个模式。
例：---- like ----匹配一个模式来找出我们要的数据记录
语法：select "字段" from "表名" where "字段" like {模式};
select * from store_info where store_name like '%os%';
8、---- order by ----按关键字排序
语法：select "字段" from "表名" [where "条件"] order by "字段" [asc, desc];
#asc 是按照升序进行排序的，是默认的排序方式。
#desc 是按降序方式进行排序。
select store_name,sales,date from store_info order by sales desc;
三、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);
select round(1.8937,3), truncate(1.235,2), ceil(5.2), floor(2.1), least(1.89,3,6.1,2.1);
#聚合函数：
avg() 返回指定列的平均值
count() 返回指定列中非 NULL 值的个数
max() 返回指定列的最大值
min() 返回指定列的最小值
sum(x) 返回指定列的所有值之和
聚合函数运用：
select avg(sales) from store_info;
#返回指定列的平均值
select count(store_name) from store_info;
#返回指定列中非NULL 值的个数
select count(distinct store_name) from store_info;
#返回指定列中非重复，非NULL 值的个数
select max(sales) from store_info;
#返回指定列的最大值
select min(sales) from store_info;
#返回指定列的最小值
select sum(sales) from store_info;
#返回指定列的所有值之和
City 表格
+----------+
| name |
| -------- |
| beijing |
| nanjing |
| shanghai |
| <null> |
| <null> |
+----------+
select count(name) from city;
select count(*) from city;
#count(列名) 只包括列名那一列的行数，在统计结果的时候，会忽略列值为 NULL 的行
#count(*) 包括了所有的列的行数，在统计结果的时候，不会忽略列值为 NULL 的行
#字符串函数：
concat(x,y) 将提供的参数 x 和 y 拼接成一个字符串
substr(x,y) 获取从字符串x中的第y个位置开始的字符串，跟substring()函数作用相同
substr(x,y,z) 获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
trim() 返回去除指定格式的值
length(x) 返回字符串 x 的长度
replace(x,y,z) 将字符串 z 替代字符串 x 中的字符串 y
upper(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,1
reverse(x) 将字符串 x 反转
select concat(region,store_name) from location where store_name = 'boston';
#将参数 region 和 store_name 拼接成一个字符串
select region || '' || store_name from location where store_name = 'boston';
#如sql_mode开启了PIPES_AS_CONCAT，"||"视为字符串的连接操作符而非或运算符，和字符串的拼接函数Concat相类似，这和Oracle数据库使用方法一样的
select substr(store_name,3) from location where store_name = 'Los Angeles';
#获取字符串store_name中的第 3 个位置开始的字符串
select substr(store_name,2,4) from location where store_name ame = 'New York';
#获取字符串store_name中的第 2 个位置开始长度为 4 的字符串
select trim ([ [位置] [要移除的字符串] from ] 字符串);
#[位置]：的值可以为 leading (起头), tralling (结尾), both (起头及结尾)。
#[要移除的字符串]：从字串的起头、结尾，或起头及结尾移除的字符串。缺省时为空格。
select trim(leading 'ne' from 'new york');
#去除 new york 字符串中的开头 ne 字符
select region,length(store_name) from location;
#显示字符串 store_name 的长度
select replace(region,'ast','astern')from location;
#将字符串 region 替代字符串 'ast' 中的字符串'astern'2、实验操作
#数学函数：
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);
select round(1.8937,3), truncate(1.235,2), ceil(5.2), floor(2.1), least(1.89,3,6.1,2.1);
#聚合函数：
avg() 返回指定列的平均值
count() 返回指定列中非 NULL 值的个数
max() 返回指定列的最大值
min() 返回指定列的最小值
sum(x) 返回指定列的所有值之和聚合函数运用：
select avg(sales) from store_info;
#返回指定列的平均值
select count(store_name) from store_info;
#返回指定列中非NULL 值的个数
select count(distinct store_name) from store_info;
#返回指定列中非重复，非NULL 值的个数
select max(sales) from store_info;
#返回指定列的最大值
select min(sales) from store_info;
#返回指定列的最小值
select sum(sales) from store_info;
#返回指定列的所有值之和
City 表格
+----------+| name |
| -------- |
| beijing |
| nanjing |
| shanghai |
| <null> |
| <null> |+----------+
select count(name) from city;
select count(*) from city;
#count(列名) 只包括列名那一列的行数，在统计结果的时候，会忽略列值为 NULL 的行
#count(*) 包括了所有的列的行数，在统计结果的时候，不会忽略列值为 NULL 的行
#字符串函数：
concat(x,y) 将提供的参数 x 和 y 拼接成一个字符串
substr(x,y) 获取从字符串x中的第y个位置开始的字符串，跟substring()函数作用相同
substr(x,y,z) 获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
trim() 返回去除指定格式的值
length(x) 返回字符串 x 的长度
replace(x,y,z) 将字符串 z 替代字符串 x 中的字符串 y
upper(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,1
reverse(x) 将字符串 x 反转select concat(region,store_name) from location where store_name = 'boston';
#将参数 region 和 store_name 拼接成一个字符串
select region || '' || store_name from location where store_name = 'boston';
#如sql_mode开启了PIPES_AS_CONCAT，"||"视为字符串的连接操作符而非或运算符，和字符串的拼接函数Concat相类似，这和Oracle数据库使用方法一样的
select substr(store_name,3) from location where store_name = 'Los Angeles';
#获取字符串store_name中的第 3 个位置开始的字符串select substr(store_name,2,4) from location where store_name ame = 'New York';
#获取字符串store_name中的第 2 个位置开始长度为 4 的字符串
select trim ([ [位置] [要移除的字符串] from ] 字符串);
#[位置]：的值可以为 leading (起头), tralling (结尾), both (起头及结尾)。
#[要移除的字符串]：从字串的起头、结尾，或起头及结尾移除的字符串。缺省时为空格。
select trim(leading 'ne' from 'new york');
#去除 new york 字符串中的开头 ne 字符
select region,length(store_name) from location;
#显示字符串 store_name 的长度
select replace(region,'ast','astern')from location;
#将字符串 region 替代字符串 'ast' 中的字符串'astern'
扫一扫
2953
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
