MySQL 基础语句
博客专区 > s-xk 的博客 > 博客详情
MySQL 基础语句
s-xk 发表于5个月前
MySQL 基础语句
  • 发表于 5个月前
  • 阅读 5
  • 收藏 1
  • 点赞 0
  • 评论 0

标题:腾讯云 新注册用户域名抢购1元起>>>   

#####1、直接量

select '直接量', host, user from mysql.user;

#####2、计算两个列的合

select 'Totle number: ', list1+ list2 from table_name;

#####3、打印显示列的别名

select list1 AS '别名' from table_name;

#####4、连接多个列

select CONCAT(list1, ' ', list2) AS '别名' from table_name;

#####5、表的别名

select * from table_name AS 别名;   #表的别名不需要加引号

#####6、查看带有空格的列

select `list name` from table_name;

##函数使用(字符函数) #####1、LEFT(显示列最左边的三个字符)

select LEFT(listtname, 3) from table_name;

#####2、RIGHT(显示列最右边的三个字符)

select RIGHT(listtname, 3) from table_name;

#####3、SUBSTRING(显示左边第三个字符到第八个字符的值)

select SUBSTRING(listname, 3, 8) from table_name;

#####4、LTRIM(去掉列左边的空格)

select LTRIM(listname) from table_name;

#####5、RTRIM(去掉列右边的空格)

select RTRIM(listname) from table_name;

#####6、UPPER(把所有小写转换为大写)

select UPPER(listname) from table_name;

#####7、LOWER(把所有大写转换为小写)

select LOWER(listname) from table_name;

#####8、CONCAT(连接多个列)

select CONCAT(list1, list2, list3, list4) from table_name;

##函数使用(复合函数)

select RIGHT(RTIRMR(listtname), 5) from table_name;

##函数使用(日期/时间函数) #####1、NOW

select NOW();

##函数使用(数值函数) #####1、ROUND(对任意数值进行四舍五入)

select ROUND(list_name, 0) from table_name;
#值为0时,表示没有小数位
#值为负数时,表示对小数点左侧前几位进行四舍五入
# 值为正数时,表示将数字四舍五入到指定的那么多个小数位

#####2、RAND(产生一个随机数)

select RAND();
select RAND(listname) from table_name;

#####3、PI(返回一个圆周率数值)

select PI();

#####4、CAST(转换函数)

select CAST(listname as DATETIME) AS '别名' from table_name;

#####5、IFNULL(把一个空值转换为一个有意义的值)

select IFNULL(listname, 'number') from table_name;

##排序数据 #####1、ORDER BY DESC

select list1, list2 from table_name ORDER BY list1;
select list1, list2 from table_name ORDER BY list1 DESC;    #降序排序
select * from table_name ORDER BY list1, list2;   #根据多个列进行排序
select CONCAT(list1, ' ', list2) as '别名' from table_name ORDER BY 别名;  #根据计算字段和别名进行排序

##基于列的逻辑 #####1、CASE WHEN THEN ELSE END (简单格式---对单一的列)

select CASE listtname WHEN 'V' THEN 'Version' WHEN 'S' THEN 'Some' ELSE 'Just so so' END as 'Bag' from table_name;

#####2、CASE WHEN THEN ELSE END (查询格式---对多个列)

select CASE WHEN list1='V' THEN 'Version' WHEN list2='S' THEN 'Some' ELSE 'Just so so' END as 'Bag' from table_name;

##基于行的逻辑 #####1、WHERE

select * from list_name where user = 'root';
select * from list_name where number > 5;    #number大于5
select * from list_name where number < 5;    #number 小于5
select * from list_name where number >= 5;  #number 大于等于5
select * from list_name where number <= 5;  #number 小于等于5
select * from list_name where number != 5;   #number 不等于5

#####2、LIMIT(限制输出行数)

select host, user from mysql.user limit 2;
select * from table_name ORDER BY list_name DESC LIMIT 5;  #对list_name进行倒序排序,只取5行数据

##布尔逻辑 1、AND OR NOT BETWEEN IN IS NULL

select * from table_name where state >= 5 AND state <=20;   #state的值大于等于5但是小于等于20
select * from table_name where state BETWEEN 5 AND 20;  #该语句同上面一条结果相同
select list1, list2 from table_name where state NOT BETWEEN state 5 AND 20;  #取反
select * from table_name where state = 'LI' OR state = 'YN';  #state值为LI,或者state值为YN
select * from table_name where state IN ('LI', 'YN');  #该语句同上面一条结果相同
select list1, list2 from table_name where state NOT IN ('LI', 'YN');  #取反
select list1, list2 from table_name where list_name IS NULL; #取值为空的行
select list1, list2 from table_name where list_name IS NOT NULL #取值不为空的行

##模糊匹配,使用表如下: 输入图片说明 ####1、LIKE

select * from SongTitles WHERE Artist LIKE '%Beat%' ;  #查找包含Beat的行
select * from SongTitles WHERE Artist LIKE '_he%'; #查找开头为任意字符,后面跟着he字符的行
# '%' 号为任意多个字符,'_'号为单个字符

##数据汇总, 使用表如下:Fees, Grades 输入图片说明

输入图片说明

####1、DISTINCT(消除重复)

select  DISTINCT Student from Grades;  #消除Student列的重复值
select DISTINCT CONCAT(Student, ' ', GradeType) AS 'Clear' from Grades; # 连接二个列,并消除重复的行

####2、SUM(计算合)

select SUM(Fee) AS 'TotalGym Fee: ' from Fees WHERE FeeType='Gym';  #计算所有学生的健身费用总合

####3、AVG(计算平均值)

select  AVG(Grade) from Grades WHERE GradeType='Quiz';  #计算Quiz的平均成绩

####4、MIN(计算最小值)

select MIN(Grade) from Grades WHERE GradeType='Quiz';  #计算Quiz的最小成绩

####5、MAX(计算最大值)

select MAX(Grade) from Grades WHERE GradeType='Quiz';  #计算Quiz的最大成绩

####6、AVG, MIN, MAX,SUM

select AVG(Grade) AS '平均值: ', MAX(Grade) AS '最大值: ', MIN(Grade) AS '最小值: ', SUM(Grade) AS '总合: ' from Grades WHERE GradeType='Quiz';   #计算Quiz的平均值,最大值,最小值,总合。

####7、COUNT (计算行数)

select COUNT(*) from Grades WHERE GradeType ="Homework";  #显示GradeType为Homework的所有行数
select COUNT(DISTINCT FeeType) from Fees;    #取消FeeType列重复的值,然后计算行数

####8、GROUP BY (分组数据)

select GradeType AS 'Group: ', MAX(Grade), MIN(Grade), SUM(Grade), AVG(Grade) from Grades GROUP BY GradeType;  #根据GradeType列分组,并且计算平均值,最大值,最小值,总合。

####9、聚合查询条件

select Student, GradeType, AVG(Grade) from Grades WHERE GradeType='Quiz' GROUP BY Student, GradeType HAVING AVG(Grade)>=71.5;  #根据Student,GradeType两个列进行分组并且计算出平均值,然后查询GradeType列为‘Quiz’的行,最后显示Grade列平均值大于71.5的行。
共有 人打赏支持
粉丝 2
博文 12
码字总数 6555
×
s-xk
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: