数据库复习整理一

数据库查询语言（DQL）

一、常见基础命令

 1. net stop mysql；    关闭mysql服务
 2. net start mysql；	开启mysql服务	
 3. mysql -h localhost  -p3306 -u root  -p  命令行方式登录
 4. show databases； 显示当下所有的数据库
 5. use 数据库名； 使用哪个数据库
 6. show tables from mysql；mysql这个数据库中有哪些表
 7. create table student（
 	id int，
 	name varchar ((20))
 	）创建一个简单的student表
 
 8. desc student； 查看表的信息

二、基础查询

 1. select id from student；	    查询单个字段
 2. select id,name from student；   查询多个字段
 3. select * from student;          查询所以字段
 4. select 100； select ’小明’；     查询常量
 5. select version（）；             查询函数，查询版本号
 6. select 18%5 as 结果；            as 起别名
 7. select 18%5  结果；              空格起别名
 8. 去重:
	案例：查询员工表中涉及到的所有的部门编号
	select **distinct** department_id from employees;
 9. +号的作用:
 	java中的+号：
		①运算符，两个操作数都为数值型
		②连接符，只要有一个操作数为字符串
	 mysql中的+号：
		仅仅只有一个功能：运算符
		select 100+90; 两个操作数都为数值型，则做加法运算
		select '123'+90;只要其中一方为字符型，试图将字符型数值转换成数值型，如果转换成功，则继续做加法运算
		select 'john'+90;	如果转换失败，则将字符型数值转换成0
		select null+10; 只要其中一方为null，则结果肯定为null

三、条件查询

语法：select  查询列表  from  表名  where   筛选条件;
分类：	
 
	一、按条件表达式筛选	
		简单条件运算符：> < = != <> >= <=
	二、按逻辑表达式筛选
		逻辑运算符：
		作用：用于连接条件表达式
			&& || !
			and or not		
			&&和and：两个条件都为true，结果为true，反之为false
			||或or： 只要有一个条件为true，结果为true，反之为false
			!或not： 如果连接的条件本身为false，结果为true，反之为false	
	三、模糊查询
		like、between and、in、is null
		like 特点：
			①一般和通配符搭配使用
			通配符：
			% 任意多个字符,包含0个字符
			_ 任意单个字符

四、分组查询

功能：用作统计使用，又称为聚合函数或统计函数或组函数

分类：
sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数
特点：
	1、sum、avg一般用于处理数值型
	  max、min、count可以处理任何类型
	2、以上分组函数都忽略null值
	3、可以和distinct搭配实现去重的运算
	4、count函数的单独介绍  一般使用count(*)用作统计行数
	效率：
		MYISAM存储引擎下  ，COUNT(*)的效率高
		INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些
	5、和分组函数一同查询的字段要求是group by后的字段

五、排序查询

语法：
	select 查询列表
	from 表名
	【where  筛选条件】
	order by 排序的字段或表达式;
特点：
	1、asc代表的是升序，可以省略
	desc代表的是降序
	2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段
	3、order by子句在查询语句的最后面，除了limit子句
案例：查询部门编号>=90的员工信息，并按员工编号降序
	SELECT *
	FROM employees
	WHERE department_id>=90
	ORDER BY employee_id DESC;

六、分组查询

语法：
	select 查询列表
	from 表
	【where 筛选条件】
	group by 分组的字段
	【order by 排序的字段】;
特点：
	1、和分组函数一同查询的字段必须是group by后出现的字段
	2、筛选分为两类：分组前筛选和分组后筛选
		问题1：分组函数做筛选能不能放在where后面
		答：不能
		问题2：where——group by——having
		一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率
	3、分组可以按单个字段也可以按多个字段
	4、可以搭配着排序使用
案例：
1、可以实现分组前的筛选
	案例：查询邮箱中包含a字符的 每个部门的最高工资
	SELECT MAX(salary),department_id
	FROM employees
	WHERE email LIKE '%a%'
	GROUP BY department_id;

2、分组后筛选
	案例：查询哪个部门的员工个数>5
	I查询每个部门的员工个数
	SELECT COUNT(*),department_id
	FROM employees
	GROUP BY department_id;
	II筛选刚才I结果
	SELECT COUNT(*),department_id
	FROM employees
	GROUP BY department_id
	HAVING COUNT(*)>5;

3.添加排序
	案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
	SELECT job_id,MAX(salary) m
	FROM employees
	WHERE commission_pct IS NOT NULL
	GROUP BY job_id
	HAVING m>6000
	ORDER BY m ; #可以不写asc 默认升序

##七、连接查询

语法：
	select 查询列表
	from 表1 别名 【连接类型】
	join 表2 别名 
	on 连接条件
	【where 筛选条件】
	【group by 分组】
	【having 筛选条件】
	【order by 排序列表】
	

分类：
内连接（★）：inner
外连接
	左外(★):left 【outer】
	右外(★)：right 【outer】
	全外：full【outer】
	
	 应用场景：用于查询一个表中有，另一个表没有的记录
	 
	 特点：
	 1、外连接的查询结果为主表中的所有记录
		如果从表中有和它匹配的，则显示匹配的值
		如果从表中没有和它匹配的，则显示null
		外连接查询结果=内连接结果+主表中有而从表没有的记录
	 2、左外连接，left join左边的是主表
	    右外连接，right join右边的是主表
	 3、左外和右外交换两个表的顺序，可以实现同样的效果 
	 4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
 
交叉连接：cross 

八、分页查询

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
	select 查询列表
	from 表
	【join type join 表2
	on 连接条件
	where 筛选条件
	group by 分组字段
	having 分组后的筛选
	order by 排序的字段】
	limit 【offset,】size;
	
	offset要显示条目的起始索引（起始索引从0开始）
	size 要显示的条目个数
特点：
	①limit语句放在查询语句的最后
	②公式
	要显示的页数 page，每页的条目数size
	
	select 查询列表
	from 表
	limit (page-1)*size,size;
	
	size=10
	page  
	1	0
	2  	10
	3	20

九、子查询

出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

分类：
按子查询出现的位置：
	select后面：
		仅仅支持标量子查询
	
	from后面：
		支持表子查询
	where或having后面：★
		标量子查询（单行） √
		列子查询  （多行） √
		
		行子查询
		
	exists后面（相关子查询）
		表子查询
按结果集的行列数不同：
	标量子查询（结果集只有一行一列）
	列子查询（结果集只有一列多行）
	行子查询（结果集有一行多列）
	表子查询（结果集一般为多行多列）

数据定义语言（DDL）

一、库的管理
创建、修改、删除
二、表的管理
创建、修改、删除

创建： create
修改： alter
删除： drop

库案例：
库的创建：
create database if not exists books;
修改；
alter database books to 新库名；
修改库的字符集：
alter database books character set gbk;
库的删除：
drop database if exists books;

表案例：、
语法：
create table 表名(
	列名 列的类型【(长度) 约束】,
	列名 列的类型【(长度) 约束】,
	列名 列的类型【(长度) 约束】,
	...
	列名 列的类型【(长度) 约束】
)
创建表：
create table if exists author(
	id int(10),
	name varchar(10);
)

修改表：
语法
alter table 表名 add|drop|modify|change  column 列名 【列类型 约束】;

修改列名：
alter table book change column publisdate pubDate  datetime; 
修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
添加新列：
alter table book add column b_name varchar(10);
删除列：
alter table book drop column b_name; 
修改表名
ALTER TABLE author RENAME TO book_author;

删除表：
DROP TABLE IF EXISTS book_author;