数据库入门

数据库常用操作

建库

• 创建数据库，数据库名称：demo

	create database demo default character set utf8;

删库

• 删除名称是demo的数据库

	drop database demo;

查看所有数据库

• 查看所有数据库

	show databases；

表的常用操作

表设计

员工表：emp

部门表：dept

创建表

• 创建emp表

	CREATE TABLE `emp` (
	  id INT(11) PRIMARY KEY AUTO_INCREMENT,
	  NAME VARCHAR(40),
	  age INT(11),
	  dept_id INT(11) 
	) ;

删除表

• 删除名称是emp的表

	drop table emp;

查看所有表

• 查看所有表

	show tables;

查看表结构/设计表

• 查看emp表结构

	desc emp;

表记录的常用操作

插入表记录

• 向emp表中插入2条记录

	INSERT INTO emp VALUES(NULL,"张三",18,1)；
	INSERT INTO emp VALUES(NULL,"李四",19,1)；

查询记录

• 查询emp表中的所有记录

	SELECT * FROM emp;

修改记录

• 修改emp表中id为2的数据

	UPDATE emp SET age=20 WHERE id=2

删除记录

• 删除emp表中id为2的数据

	DELETE FROM emp WHERE id=2;

条件查询

• where

	SELECT * FROM emp WHERE id=1;
	SELECT * FROM emp WHERE NAME='张三' AND age=18
	SELECT * FROM emp WHERE NAME='张三' OR age=19
	SELECT * FROM emp WHERE age IN(18,19)
	SELECT * FROM emp WHERE age NOT IN(18,19)

• like
  通配符%代表0到n个字符，通配符下划线_代表一个字符

	SELECT * FROM emp WHERE NAME  LIKE '张%';
	SELECT * FROM emp WHERE NAME  LIKE '%张%';
	SELECT * FROM emp WHERE NAME  LIKE '%张';
	SELECT * FROM emp WHERE NAME  LIKE '张_'; 

• null
  is null—过滤字段为空的 ；is not null—过滤字段不为空的 ；

	SELECT * FROM emp WHERE age IS NULL; 

	SELECT * FROM emp WHERE age IS NOT NULL; 

• between and
  两条语句等效

	SELECT * FROM emp WHERE age>=18 AND age<=19; 
	SELECT * FROM emp WHERE age BETWEEN 18 AND 19; 

• limit
  分页查询

	SELECT * FROM emp LIMIT 2; --展示2条记录
	SELECT * FROM emp LIMIT 0,2; --从第一条开始，展示2条记录
	SELECT * FROM emp LIMIT 1,2; --从第二条开始，展示2条记录

• order by
  排序 ：desc–降序 ； asc–升序（默认）

	SELECT * FROM emp ORDER BY age ASC;
	SELECT * FROM emp ORDER BY age DESC;

聚合 aggregation

• 根据一列统计结果

count

	SELECT COUNT(*) FROM emp;
	SELECT COUNT(1) FROM emp; --效果和*一样
	SELECT COUNT(NAME) FROM emp;  --慢，根据字段统计非NULL的

max/min

	SELECT MAX(age) FROM emp; --获取最大值
	SELECT MIN(age) FROM emp; --获取最小值
	SELECT MIN(age) MIN,MAX(age) MAX FROM emp;  --最小值 最大值

sum/avg

	SELECT SUM(age) FROM emp; --求和
	SELECT AVG(age) FROM emp; --平均数

分组group

用于对查询的结果进行分组统计 group by表示分组，having子句类似where过滤返回的结果

group by

每个部门的最高薪资和平均薪资，结果中的非聚合列必须出现在分组中，否则业务意义不对

	SELECT deptno,MAX(sal),AVG(sal) FROM emp GROUP BY deptno #按照deptno分组

	SELECT job,MAX(sal),AVG(sal) FROM emp GROUP BY job #按照job分组

	SELECT deptno,job,MAX(sal),AVG(sal) FROM emp GROUP BY deptno,job #deptno和job都满足的

having

平均工资小于8000的部门

	select deptno, AVG(sal) from emp group by deptno #按部门分组
	having AVG(sal)<8000 #查询条件,类似where,但是group by只能配合having

	#deptno出现的次数
	SELECT deptno,COUNT(deptno) FROM emp GROUP BY deptno #按deptno分组
	HAVING COUNT(deptno)>1 #次数多的

关联查询

表关系： 一对一； 一对多；多对多；

多表连查 join

三种连接join

• 内连接 inner join
• 左（外）连接 left join
• 右（外）连接 right join

SELECT emp.*,dept.dept_name FROM emp INNER JOIN dept ON emp.`dept_id`=dept.dept_id
SELECT emp.*,dept.dept_name FROM emp LEFT JOIN dept ON emp.`dept_id`=dept.dept_id
SELECT emp.*,dept.dept_name FROM emp RIGHT JOIN dept ON emp.`dept_id`=dept.dept_id

inner join、left join 、right join的区别？

• INNER JOIN两边都对应有记录的才展示，其他去掉
• LEFT JOIN左边表中的数据都出现，右边没有数据以NULL填充
• RIGHT JOIN右边表中的数据都出现，左边没有数据以NULL填充

SQL优化

• 查询SQL尽量不要使用select *，而是具体字段
• 避免在where子句中使用or来连接条件（分两条写）
• 使用varchar代替char
• 尽量使用数值替代字符串类型
• 查询尽量避免返回大量数据（分页）
• 避免在where中对字段进行表达式操作
• 不要有超过5个以上的表连接
• inner join 、left join、right join，优先使用inner join（如果使用left join左边表尽量小）

SQL执行顺序

• FROM [left_table] 选择表
• ON <join_condition> 链接条件
• <join_type> JOIN <right_table> 链接
• WHERE <where_condition> 条件过滤
• GROUP BY <group_by_list> 分组
• AGG_FUNC(column or expression),… 聚合
• HAVING <having_condition> 分组过滤
• SELECT (9) DISTINCT column,… 选择字段、去重
• ORDER BY <order_by_list> 排序
• LIMIT count OFFSET count; 分页