MySQL语句

最新推荐文章于 2024-07-11 21:41:59 发布

lalala1213888

最新推荐文章于 2024-07-11 21:41:59 发布

阅读量327

点赞数 7

文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/lalala1213888/article/details/135751367

版权

-- 关系型数据库 基本单位是表 表与表之间存在关联关系
INSERT INTO players SET playname='崔渭阳',id=5,height=143,weight=200,birthday='2002-10-14'
DELETE FROM players WHERE id=5

-- 查询的结果是虚拟的表格（只读），不能操作
-- 查询指定的列
SELECT playname,height,weight FROM players

-- 查询所有的列
SELECT * FROM players

-- 对查询结果进行算术计算
SELECT playname,height+1 FROM players

-- 去除重复数据  查询结果中所有列都相同，视为重复数据
-- 使用DISTINCT关键字去除重复数据
SELECT DISTINCT playname,height FROM players

-- 函数
-- 单行函数:会对查询中的每一行数据进行处理
-- 字符函数

-- length(列名)  以字节为单位
SELECT playname,LENGTH(playname) FROM players

-- char_length() 以字符为单位
SELECT playname,CHAR_LENGTH(playname) FROM players

-- concat可以连接多个字符串  as用于取别名  注:不能连接null，否则只会显示null
SELECT CONCAT (playname,":",CHAR_LENGTH(playname)) AS playname FROM players

-- 大小写转换
SELECT UPPER(playname),LOWER(playname)FROM players

-- 截取字符串SUBSTRING(字符串,开始位置,截取长度)
SELECT SUBSTRING(playname,1,2)FROM players

-- 返回指定字符在字符串中首次出现的位置，找不到则返回0
SELECT INSTR(playname,'文')FROM players

-- 去除字符串中的空格
SELECT TRIM(playname),playname FROM players

-- 去除字符串中指定的子串
SELECT TRIM('文'FROM playname),playname FROM players

-- 左填充到指定长度
SELECT LPAD(playname,8,'wy'),RPAD(playname,10,'yw') FROM players

-- 替代指定字符
SELECT REPLACE (playname,'文','武')FROM players

-- case when 条件 then 条件成立 else 条件不成立
SELECT
  playname,
  height,
  (CASE WHEN height>= 2 THEN '高个子' ELSE '矮子' END)AS height
FROM
 players
  
  -- ifnull(列名，'为空时的默认信息')
SELECT playname,height,
IFNULL(height,'0.0')AS height
FROM players  
  
-- if（条件，'结果一','结果二'）  
SELECT playname,height,
IF(height>2,'高子','矮子')
FROM players  

-- 数学函数

-- 四舍五入
SELECT playname,ROUND(height) FROM players

-- 向上取整
SELECT playname,CEIL(height) FROM players

-- 保留固定小数（不进位，不舍入）
SELECT playname,TRUNCATE(height,1) FROM players

-- mod(被除数，除数)

-- rand()获取随机数
SELECT playname,RAND() FROM players

-- 日期函数

-- now()：返回当前系统日期＋时间
SELECT playname,NOW() FROM players

-- curdate():返回当前日期
SELECT playname,CURDATE() FROM players

-- curtime():返回当前时分秒
SELECT playname,CURTIME() FROM players

-- year(格式化为年)，month(格式化为月)

-- str_to_date(日期,格式) 格式化为指定格式
SELECT STR_TO_DATE('2003-10-2','%Y-%m-%d') FROM players

SELECT DATE_FORMAT(birthday,'%Y-%m')FROM players

SELECT DATE_FORMAT(birthday,'%Y-%m'),COUNT(*)FROM players GROUP BY DATE_FORMAT(birthday,'%Y-%m')

-- DATEDIFF(被减天数,减的天数)计算日期相差的天数
SELECT birthday,DATEDIFF(CURDATE(),birthday) FROM players