条件查询

条件查询

：查询语法：select 结果 from 表名 where 条件过滤 group by order by

1、等于：

SELECT 100-- 查询常量
SELECT VERSION()-- 查询数据库版本
SELECT stu_num,stu_name,stu_sex FROM t_student   -- 查询指定列(建议使用)
SELECT * FROM t_student  -- 查询所有列(不建议)

2、不等于：！=或<>

-- DISTINCT  stu_sex 去除查询出来的重复数据（行） (所有的列都想同)
SELECT DISTINCT  stu_sex FROM t_student

3、and：

-- + - * / 只能做算数运算符 
SELECT stu_num+5 FROM t_student
SELECT stu_num*5 FROM t_student

4、or：

 # 修改数据  update 表名 -- length(字符串,列名)字符串长度  字节为单位
SELECT LENGTH(stu_name)FROM t_student

-- char_length(stu_name)字符为单位
SELECT CHAR_LENGTH(stu_name)FROM t_student

-- 连接concat多个字符串                  as 定义别名
SELECT CONCAT(stu_num,":",stu_name)AS sname FROM t_student

-- 将字符串变成大upper、小lower写
SELECT UPPER(stu_name)AS sname FROM t_student
SELECT LOWER(stu_name)AS sname FROM t_student

-- 截取字符串  substring(列名,开始位置,长度)  //开始位置从1开始
SELECT SUBSTRING(stu_name,1,3)AS sname FROM t_student

-- instr 查找指定字符首次出现的位置
SELECT INSTR(stu_name,'3')AS sname FROM t_student

-- trim(列名) 默认去掉字符串前后的空格
SELECT CHAR_LENGTH(TRIM(stu_name))FROM t_student
-- trim(指定的子串  from 列名)  去掉某列前后指定的子串
SELECT  TRIM('a' FROM  stu_name)FROM t_student

-- 用指定的字符串 左填充lpad,右填充tpad到指定的长度
SELECT  LPAD(stu_name,8,"ab")FROM t_student
SELECT  RPAD(stu_name,8,"ab")FROM t_student

-- 替换字符串
SELECT  REPLACE(stu_name,'1',"A")FROM t_studentset 列名=值,列名=值  [where 条件]
UPDATE t_student SET  stu_sex="男",stu_address='汉中市 东一环路'

5、模糊查询：like

SELECT SUM(stu_score) FROM t_student

SELECT AVG(stu_score) FROM t_student

SELECT MAX(stu_score) FROM t_student

SELECT MIN(stu_score) FROM t_student

SELECT COUNT(*) FROM t_student -- 列的值为null不会被统计
SELECT COUNT(*) FROM t_student GROUP BY stu_sex

6、in：

-- 查询成绩为80、90和100的学生
-- in(值1,值2...)
SELECT * FROM t_student WHERE stu_score=80 OR stu_score =90 OR stu_score =100
SELECT * FROM t_student WHERE stu_score IN (80,90,100)
SELECT * FROM t_student WHERE stu_score NOT IN (80,90,100)

7、is null 及 is not null：

-- 查询性别不为空  
-- is null 为null   is not null 不为空
SELECT * FROM  t_student  WHERE stu_sex IS NULL
SELECT * FROM  t_student  WHERE stu_sex IS NOT NULL

8、合并：union 及 union all

-- union：保证查询列数相同   会去掉查询结果重复的行  将结果按字段顺序进行排序
-- union all 只是简单合并，不会去掉重复的行
SELECT stu_num,stu_name,stu_score FROM t_student WHERE stu_sex = '男'
UNION
SELECT stu_num,stu_name,stu_score FROM t_student WHERE stu_sex = '男'

SELECT stu_num,stu_name,stu_score FROM t_student WHERE stu_sex = '男'
UNION ALL
SELECT stu_num,stu_name,stu_score FROM t_student WHERE stu_sex = '男'

9、order by排序：

SELECT * FROM t_student #默认按主键升序排列

-- 				指定排序列,排序规则
SELECT * FROM t_student ORDER BY stu_score ASC#升序
SELECT * FROM t_student ORDER BY stu_score DESC#降序

10、限制行数： limit子句：

	语法：1、limit 查询行数
				2、limit 开始位置(从0开始),数量    分页原理
				 3、limit 要查询的行数 offset 开始查的位置

SELECT * FROM t_student LIMIT 3

--          limit 开始位置(从0开始),数量    分页原理
SELECT * FROM t_student WHERE stu_score > 80 ORDER BY stu_score DESC LIMIT 0,3
-- SELECT * FROM t_student LIMIT 3,3分页实例
-- SELECT * FROM t_student LIMIT 6,3分页实例

--          limit 要查询的行数 offset 开始查的位置
SELECT * FROM t_student WHERE stu_score > 80 ORDER BY stu_score DESC LIMIT 3 OFFSET 0