MySQL进阶sql语句①

标签：进阶 sql 查询 score MySQL test where select 函数

一.MySQL进阶查询

1.1排序（order by）

①使用order by 语句进行排序

• ASC:升序，默认的排序，使用order by时，会默认该排序
• DESC:降序
• 排序可针对对一个或多个字段，多字段要第一个字段有重复，第二个字段的排序才会生效

order by  语法结构：对单或多字段进行升序或降序排序

select 字段1，字段2，... from  表名  order by   排序字段1，排序字段2， ... ASC|DESC;
select id,name,hobbid from test order by hobbid desc,id desc;  //当第一个字段重复时，第二个字段的排序才生效

多字段排序

order by  语法结构：结合where条件进行过滤

select name,score from test where address='hangzhou' order by score desc;

order by  语法结构：结合and或者or进行区间判断

select 字段名  from 表名 where 条件1 (and|or) 条件2 (and|or)条件3;
select * from test where score >70 and score <=90;  //70到90包括90区间

order by  语法结构：结合嵌套查询，优先子嵌套

select * from 表名 where 条件1 or 条件2 （条件3 and 条件4）；  //有括号会先执行括号里的内容然后再去执行括号外的
select * from test where score >70 or (score >75 and score <90);  //优先子子查询

1.2去重（distinct）

distinct 查询不重复记录

select distinct 字段 from 表名；

1.3分组（group by）

通过 SQL 查询出来的结果，还可以对其进行分组，使用 GROUP BY 语句来实现 ，GROUP BY 通常都是结合聚合函数一起使用的，常用的聚合函数包括：计数（COUNT）、 求和（SUM）、求平均数（AVG）、最大值（MAX）、最小值（MIN），GROUP BY 分组的时候可以按一个或多个字段对结果进行分组处理。

select conut(字段) from 表名 group by 字段；
select count(name),hobbid from test group by hobbid;  //对hobbid进行分组，并进行计数统计

 

#筛选分数大于等于80的分组，并计算个数
select count(name),hobbid from test where score>=80 group by hobbid; 

 

#结合order by把计算出的个数按升序排列
select count(name),score,hobbid from test where score>=80 group by hobbid order by count(name)；

 

1.4限制条目（limit）

• limit 限制输出的结果记录
• 在使用 MySQL SELECT 语句进行查询时，结果集返回的是所有匹配的记录（行）。
• 有时候仅 需要返回第一行或者前几行，这时候就需要用到 LIMIT 子句。

select 字段 from 表名 limit  限制条件；
select * from test limit 3;   //显示前4行信息，属性行属于0，也算作一条

select * from test limit 3，2;   //显示前3行记录的后2行

 

select * from test order by id desc limit 3； //显示最后三行，可以先进行排序再限制

 

1.5 别名（alias | as）

• 在 MySQL 查询时，当表的名字比较长或者表内某些字段比较长时，为了方便书写或者 多次使用相同的表，可以给字段列或表设置别名。使用的时候直接使用别名，简洁明了，增强可读性。
• 在使用 AS 后，可以用 alias_name 代替 table_name，其中 AS 语句是可选的。AS 之后的别名，主要是为表内的列或者表提供临时的名称，在查询过程中使用，库内实际的表名 或字段名是不会被改变的。

• 如果表的长度比较长，可以使用 AS 给表设置别名，在查询的过程中直接使用别名。

#不用as也可以，一样显示，使用空格
对于列的别名：select 字段名 as 你想要设置的别名 from 表名；
对于表的别名：select 表的别名.字段 as 字段的别名 from 表名 as 设置的表的别名；

使用场景：
①对复杂的表进行查询的时候，别名可以缩短查询语句的长度
②多表相连查询的时候（通俗易懂、减短sql语句）

 

• AS 还可以作为连接语句的操作符。
• 创建t1表，将test表的查询记录全部插入t1表。

①创建了一个新表t1 并定义表结构，插入表数据（与info表相同）
②但是”约束“没有被完全”复制“过来 #但是如果原表设置了主键，那么附表的：default字段会默认设置一个0

create table t1 as select * from test;
select * from t1;

 

 注意：

• 在为表设置别名时，要保证别名不能与数据库中的其他表的名称冲突。
• 列的别名是在结果中有显示的，而表的别名在结果中没有显示，只在执行查询时使用。

1.6通配符（ _   % ）

• 通配符主要用于替换字符串中的部分字符，通过部分字符的匹配将相关结果查询出来。
• 通常通配符都是跟 LIKE 一起使用的，并协同 WHERE 子句共同来完成查询任务。常用的通配符有两个
• %：百分号表示零个、一个或多个字符 
• _ ：下划线表示单个字符

select id,name from test where name like '张%';  //查询name以张开头的记录

 

 

 

 

1.7子查询 （in）

子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语 句。子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一 步的查询过滤。
PS: 子语句可以与主语句所查询的表相同，也可以是不同表。

select 字段 from 表名 where 条件 in (select 字段 from 表名 where 条件);

  select name,score from test where id in (select id from test where score >80); //同表查询

 

子查询不仅可以在 SELECT 语句中使用，在 INERT、UPDATE、DELETE 中也同样适用。在嵌套的时候，子查询内部还可以再次嵌套新的子查询，也就是说可以多层嵌套。

select name,score from test where id in (select id from test where score>80);  //查询分数大于80的记录

#子查询还可以用在 INSERT 语句中。子查询的结果集可以通过 INSERT 语句插入到其 他的表中
insert into t1 select * from test where id in (select id from test);  //通过子查询数据插入数据

#UPDATE 语句也可以使用子查询。UPDATE 内的子查询，在 set 更新内容时，可以是单独的一列，也可以是多列。
update test set score=50 where id in (select * from t2 where id=2);  //子条件id=2的test表内的score更新为50

#DELETE 也适用于子查询
delete from test where id in (select id where score>80);  //删除分数大于80的数据

 

二.MySQL数据库函数

可以对 MySQL 常用函数进行简单的分类，大概包括数值型函数、字符串型函数、日期时间函数、聚合函数等。

2.1MySQL 数值型函数

函数名称	作 用
ABS	求绝对值
SQRT	求二次方根
MOD	求余数
CEIL 和 CEILING	两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
FLOOR	向下取整，返回值转化为一个BIGINT
RAND	生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
ROUND	对所传参数进行四舍五入
SIGN	返回参数的符号
POW 和 POWER	两个函数的功能相同，都是所传参数的次方的结果值
SIN	求正弦值
ASIN	求反正弦值，与函数 SIN 互为反函数
COS	求余弦值
ACOS	求反余弦值，与函数 COS 互为反函数
TAN	求正切值
ATAN	求反正切值，与函数 TAN 互为反函数
COT	求余切值

2.2MySQL字符串函数

函数名称	作 用
LENGTH	计算字符串长度函数，返回字符串的字节长度
CONCAT	合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
INSERT	替换字符串函数
LOWER	将字符串中的字母转换为小写
UPPER	将字符串中的字母转换为大写
LEFT	从左侧字截取符串，返回字符串左边的若干个字符
RIGHT	从右侧字截取符串，返回字符串右边的若干个字符
TRIM	删除字符串左右两侧的空格
REPLACE	字符串替换函数，返回替换后的新字符串
SUBSTRING	截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE	字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串

3.3时间和日期函数

函数名称	作 用
CURDATE 和 CURRENT_DATE	两个函数作用相同，返回当前系统的日期值
CURTIME 和 CURRENT_TIME	两个函数作用相同，返回当前系统的时间值
NOW 和  SYSDATE	两个函数作用相同，返回当前系统的日期和时间值
UNIX_TIMESTAMP	获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数
FROM_UNIXTIME	将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数
MONTH	获取指定日期中的月份
MONTHNAME	获取指定日期中的月份英文名称
DAYNAME	获取指定曰期对应的星期几的英文名称
DAYOFWEEK	获取指定日期对应的一周的索引位置值
WEEK	获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
DAYOFYEAR	获取指定曰期是一年中的第几天，返回值范围是1~366
DAYOFMONTH	获取指定日期是一个月中是第几天，返回值范围是1~31
YEAR	获取年份，返回值范围是 1970〜2069
TIME_TO_SEC	将时间参数转换为秒数
SEC_TO_TIME	将秒数转换为时间，与TIME_TO_SEC 互为反函数
DATE_ADD 和 ADDDATE	两个函数功能相同，都是向日期添加指定的时间间隔
DATE_SUB 和 SUBDATE	两个函数功能相同，都是向日期减去指定的时间间隔
ADDTIME	时间加法运算，在原始时间上添加指定的时间
SUBTIME	时间减法运算，在原始时间上减去指定的时间
DATEDIFF	获取两个日期之间间隔，返回参数 1 减去参数 2 的值
DATE_FORMAT	格式化指定的日期，根据参数返回指定格式的值
WEEKDAY	获取指定日期在一周内的对应的工作日索引

3.4聚合函数

函数名称	作用
MAX	查询指定列的最大值
MIN	查询指定列的最小值
COUNT	统计查询结果的行数
SUM	求和，返回指定列的总和
AVG	求平均值，返回指定列数据的平均值

3.5MySQL 流程控制函数

函数名称	作用
IF	判断，流程控制
IFNULL	判断是否为空
CASE	搜索语句

 

三.总结

本章介绍了select 的高阶语句

order by 降序升序排序

order by 分组

and和or的使用

distinct的去重查询

limit 限制输出

as的别名设置

通配符_和% 的使用

函数的了解：对函数分类==》数值型函数、字符串型函数、日期时间函数、聚合函数

标签：进阶,sql,查询,score,MySQL,test,where,select,函数
From： https://www.cnblogs.com/suoluo212/p/16936944.html