mysql-two-day

mysql数据查询.

查询所有列

SELECT * FROM employee; -- 所有列的查询

查询指定列

SELECT id,age,email FROM employee;-- 指定列查询

查询时候指定别名

SELECT id AS '编号' , email AS '邮箱' FROM employee ; -- 指定别名

多表查询时候可以配合表的别名.
SELECT id AS '编号' , email AS '邮箱' FROM employee AS s;

查询时候添加常量列

SELECT id,username,email,age,'研发部' FROM employee; 

SELECT id,username,email,age,'研发部'AS '部门' FROM employee;  -- 设置常量列的别名

查询时候合并列: 只能合并数值类型字段

SELECT id,username,(selvet+jsp) AS '总分数'FROM employee;

查询时候去除重复(去重查询) –> DISTINCT

SELECT DISTINCT age FROM employee;

SELECT DISTINCT (age) FROM employee;

条件查询: WHERE

• 逻辑条件: and(与) or(或)

  SELECT * FROM employee WHERE id=2 AND age=30; -- 查询id=2,age=30的员工        
  
  SELECT * FROM employee WHERE id=3 OR age=32; -- 查询id=3或者age=30的员工

• 比较条件: > < == >= <= <>(不等于) between and

  SELECT * FROM employee WHERE jsp> 80;
  SELECT * FROM employee WHERE jsp>70 AND jsp<90;
  SELECT * FROM employee WHERE jsp BETWEEN 75 AND 90; -- 75<= jsp <= 90 (都是闭合区间)

• 判空条件 (null 空字符串) is null / is not null / =’’ / <>’’

  null: 没有值
  空字符串: 有值
  SELECT * FROM employee WHERE age IS NULL ; -- 判断null
  SELECT * FROM employee WHERE email=''; -- 筛选出空字符串
  SELECT * FROM employee WHERE age IS NULL OR age='';

• 模糊条件: like

  替换标记:
  % 表示任意字符    
  _ 表示一个字符
  SELECT * FROM employee WHERE username LIKE "王%";
  SELECT * FROM employee WHERE username LIKE "王_"; -- 表示筛选出username=王姓开头两个字的名字

聚合查询: 使用聚合函数

sum(): 求和函数
avg()
max()
min()
count(): 统计时候排除null 

SELECT SUM(english) AS '所有人英语总成绩' FROM student; -- 统计所有人的english分数之和

SELECT AVG(english) AS '所有人英语平均分' FROM student; -- 统计英语学科所有人的平均分

SELECT MAX(english) AS '英语最高分' FROM student; -- 统计英语学科最高分

SELECT MIN(english) AS '英语最高分' FROM student; -- 统计英语学科最低分

SELECT COUNT(*) FROM student; -- 统计该表下所有字段的数量，取最大那个值
SELECT COUNT(id) FROM student; -- 统计该表下id字段的数量

分页查询： limit(起始行[从0开始],需要查询数)

SELECT * FROM student LIMIT 0,3; -- 查询前三行记录

排序查询 order by 字段 排序 条件(asc:顺序,正序[数值就是地递增,字母就是按照a-z],desc:倒序.)

SELECT * FROM student ORDER BY english ASC; -- 按照英语成绩低到高排序 

SELECT * FROM student ORDER BY math DESC;

SELECT * FROM student ORDER BY math ASC,chinese ASC,english ASC; -- 多个排序条件并存

分组查询 (group by)

SELECT sex,COUNT(*)  FROM student GROUP BY sex; -- 先按照sex进行分组,再计算每组的值.

分组查询后的筛选(having)

SELECT sex,COUNT(*) FROM student GROUP BY sex HAVING COUNT(*)>2; -- 先按照sex进行分组,再计算每组的值，再找出大于2的那组.