mysql的查询方法

一.基本数据查询类型

DQL（Data Query Language）数据查询语言查询是使用频率最高的一个操作， 可以从一个表中查询数据，也可以从多个表中查询数据。

● 基础查询

● 语法：

select 查询列表 from 表名;

● 特点：

查询列表可以是：表中的字段、常量、表达式、函数

查询的结果是一个虚拟的表格

-- 查询数据
SELECT id,NAME,height FROM player
-- 查询数据并设置限制条件
SELECT NAME,height,location FROM player WHERE id=9
-- 查询表中所有列数据
SELECT * FROM player
-- 使用算数运算符表达式，算术运算符只能用作算数运算
SELECT  id+10 FROM player
-- 将多个字符串进行拼接
SELECT CONCAT(NAME,"t",location) FROM player
-- DISTTNCT去除查询结果中的重复数据，每个值都相同才视作重复数据
SELECT DISTINCT NAME,id FROM player

-- 查询函数，获取字符串的长度
SELECT LENGTH(NAME) FROM player
-- 查询字符长度
SELECT
  id,
  CHAR_LENGTH (NAME),
  NAME
FROM
  player
  -- 返回字串第一个出现的索引
  SELECT INSTR	(NAME,"i") ,id,NAME FROM player
  -- 去除字符串前后的空格或者字串
  SELECT TRIM(NAME) FROM player
  -- 左右填充至至指定长度
  SELECT RPAD(NAME ,"10","a")FROM player
  SELECT LPAD(NAME ,"15","a")FROM player
  -- 替代
  SELECT REPLACE(NAME,"i","I")FROM player
  -- upper 字符串变大 ，lower 字符串变小
  SELECT UPPER(NAME),LOWER(NAME)FROM player
  -- 截取字符串pos位置从1开始，length为截取字符串的长度
  SELECT SUBSTRING(NAME,1,4)FROM player	
  -- 查询case when then 方法判断
  SELECT 
  id,
  NAME,
  (CASE WHEN height>200 THEN "大高个子"
  WHEN height>190 AND height<200 THEN"高个子"
  ELSE "不是大高个子" END)AS height
  FROM player
  -- 如果查询结果为空白，给出默认值
  SELECT id,IFNULL(location,"暂未录入信息") FROM player
  -- if判断单个条件
  SELECT IF(height>190,"大高个子","不是大高个子") FROM player
  

  
  

-- 日期函数
SELECT NOW()  
SELECT CURDATE()
SELECT CURTIME()
-- 查询具体的年月
SELECT id,NAME,YEAR(birthday),
MONTH(birthday)FROM player
-- 查询时将字符串转换为具体的日期格式
SELECT YEAR(STR_TO_DATE("2003-2-3","%Y-%m-5d"))	
SELECT DATE_FORMAT(birthday,"%Y-%m")FROM player
-- 获取两个日期之间间隔的天数
SELECT DATEDIFF(CURDATE(),birthday)FROM player
-- 分组函数，sum,avg,max,min,count
 SELECT SUM(height)FROM player
  SELECT AVG(height)FROM player
  SELECT MAX(height)FROM player
  SELECT MIN(height)FROM player
  SELECT COUNT(location)FROM player
  

  
  

-- 查询的or和and 判断
  SELECT * FROM player WHERE height>190 AND location='后卫'
  SELECT * FROM player WHERE height>190 OR location='后卫'
  SELECT * FROM player WHERE (height>190 OR location='后卫')AND id<4
  -- IN查询与NOT IN 查询
  SELECT *FROM player WHERE height IN(193,203,198)
  SELECT *FROM player WHERE height  NOT IN(193,203,198)
  -- is null筛选出为null的数据
  SELECT *FROM player WHERE location IS NULL
  SELECT *FROM player WHERE location IS  NOT NULL
  -- 查询以--开头的数据
  SELECT *FROM player WHERE NAME LIKE'科%'
  SELECT *FROM player WHERE NAME LIKE'科_'
  SELECT *FROM player WHERE NAME LIKE'科______'
  SELECT *FROM player WHERE NAME LIKE'%尔%'
  
  
  
  
  

  

-- union将多条查询语句结果进行合并，将重复元素去除
SELECT id,NAME,gender FROM player WHERE gender='男' UNION SELECT id,NAME,gender FROM player WHERE gender='女'
-- union all 也是合并，但是不会去去除合并产生的重复元素
SELECT id,NAME,gender FROM player WHERE gender='男' UNION SELECT id,NAME,gender FROM player WHERE gender='女' UNION SELECT id,NAME,gender FROM player WHERE gender='男' UNION SELECT id,NAME,gender FROM player WHERE gender='女'
-- 排序
SELECT * FROM player ORDER BY height DESC
SELECT * FROM player ORDER BY height ASC
SELECT * FROM player ORDER BY height DESC,weight ASC
-- 数量限制
SELECT * FROM player LIMIT 0,3
SELECT * FROM player LIMIT 3,3
-- 分组，以某个列进行分组，把内容相同的分到同一个组中去处理
SELECT * FROM player WHERE id>0 ORDER BY id DESC LIMIT 0,3
SELECT COUNT(height)c,gender FROM player GROUP BY gender HAVING c>3

length()：获取参数值的字节个数

char_length()获取参数值的字符个数

concat(str1,str2,.....)：拼接字符串

upper()/lower()：将字符串变成大写/小写

substring(str,pos,length)：截取字符串 位置从1开始

instr(str,指定字符)：返回子串第一次出现的索引，如果找不到返回0 trim(str)：去掉字符串前后的空格或子串,trim(指定子串 from 字符串) lpad(str,length,填充字符)：用指定的字符实现左填充将str填充为指定长度

rpad(str,length,填充字符)：用指定的字符实现右填充将str填充为指定长度

replace(str,old,new)：替换，替换所有的子串

case when 条件 then 结果1 else 结果2 end; 可以有多个when ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回 原本的值 if函数：if else的 效果 if(条件,结果1,结果2)