day1 MySql数据库基础及其DQL语言学习

数据库好处：

实现数据持久化；可以结构化查询，方便管理

相关概念：

DB:数据库 存储数据的仓库，保存了一系列有组织的数据
DBMS：数据库管理系统，数据库通过DBMS进行创建和操作（增删改查）的容器
SQL：结构化查询语句，用于和DBMS通信的语句MySQL数据库的安装：

相关内容：

MySQL数据库的安装：
MySQL数据库的卸载：
配置文件介绍：my.ini 数据库的端口，安装路径等设置 ，改变后需重启服务，使设置生效
服务的启动与停止：
			dos命令：net start/stop 服务名
mysql服务的登陆和退出： 
				登陆：mysql 【-h localhost -P 3306】 -u root -p			
				退出：exit / ctrl+c  

Mysql的常见命令

show databses;   ---查看所有数据库
use database_name; ---打开指定库
show tables;   ---查看当前数据库的所有表
show tables from database_name; ---查看其他库的所有表
select database(); ---显示当前数据库
desc table_name; ---显示表结构
创建表：
	create table table_name(
		列名 列属性，
		列名 列属性，
		...
	);       
查看当前数据库版本：
	select version();   （mysql命令）
	mysql --version /  mysql -V(dos命令)

语法规范

1.不区分大小写，但建议关键字大写，表名列名小写
2.每条命令最好用分号结尾
3.每条命名根据需要可以进行缩进或换行
4.注释
	单行注释: #注释文字
			-- 注释文字
	多行注释： /* 注释文字 */

图形用户化界面

navicat,sqlyoung

DQL数据查询语言

1.基础查询

一、语法:  select 查询列表 from 表名
	 查询列表可以是：表中的字段，常量值，表达式，函数
	 查询的结果是一个虚拟的表格
二、查字段：
 	1）查询表中的单个字段 select 列名1 from 表名
	2）查询表中的多个字段  select 列名1,列名2... from 表名
	3）查询表中的所有字段 select * from 表名
三、查常量值：select 100; select 'jhon';(注意：字符型和日期型数值须用单引号引起来)
四、查表达式：select 100%3 ;
五、查询函数: select Version();
六、起别名 1）使用as
 			SELECT 100%3 AS 结果
 			SELECT last_name AS 姓，first_name AS 名 from 表名
 		 2）使用空格 字段后面加空格加别名即可
 			SELECT last_name 姓，first_name 名 from 表名
七、去重
 		SELECT DISTINCT 字段 FROM 表名
八、+号的作用:只有运算符的作用
 		select 100 + 90; 两个操作数都为数值型 则做加法运算
 		select '100' + 90;
 		select 'john' + 90;只要其中一方为字符串，试图将字符型数值转换为数值型，
 						   如果转换成功，则继续做加法运算；
 						   如果转换失败，则将字符型数值转换为0
 		select null + 0; 只要其中有一方为null，则结果肯定为null
*补充：1.concat函数 用于拼接字符 select concat(str1,str2,...)
	  2.ifnull函数 用于判断某字段是否为null，是null返回指定的值，反之返回原本的值
	  			  select ifnull(列名,0),列名2 from 表名;
	  3.isnull函数 判断某字段或表达式是否为null，如果是返回1，不是返回0
	  			select isnull(列名),列名2 from 表名;

2.条件查询

语法：SELECT 查询列表 FROM 表名 WHERE 筛选条件
一、按条件表达式筛选
	条件运算符：>, <, =,  !=(<>), >=, <=
二、逻辑表达式
	逻辑运算符(用于连接条件表达式)： &&， ||， !, and, or , not
	&& 和 and ：两个条件都为true 结果为true 反之为false
	|| 和 or：只有有一个条件为true，结果为true，反之为false
	！和 not：如果连接的条件本身为false，结果为true 反之为false
三、模糊查询
	like  跟通配符一起搭配使用用 ‘%’匹配任意多个字符包括0个字符，’_‘匹配一个字符
	between and   （包含临界值，两个临界值不要调换顺序）
	in (判读某字段的值是否属于in列表中的某一列,in列表的值类型必须一致或兼容，不支持通配符）
	is null, is not null 只能用于判断null值(=或<>不能用于判断null值)
	安全等于<=>： 可以判断null值 和 其他类型数值

3.排序查询

语法：select 查询列表 from 表名 【where 筛选条件】 order by 排序列表【asc | desc】
	 *注意：asc代表升序 desc代表降序 不写默认升序
	 	   order by子句中可以支持单个字段、多个字段、表达式、函数、别名
	 	   oder by一般是放在查询语句的最后面，limit子句除外
例子：   
select concat(last_name+firstname) AS 姓名,department_id,salary*12(1+IFNULL(commission_pct,0)) AS 年薪 from employees  ORDER BY 年薪 DESC, 姓名 ASC;
select last_name,salary from employees where salary not between 8000 and 12000 ORDER BY salary DESC;
select * from employees where email LIKE '%e%' ORDER BY LENGTH(email) DESC, department_id ASC;

4.常见函数

【补充】show VARIABLES LIKE ‘%char%’ 获取字符串格式
语法：select 函数名(实参列表) 【from 表名】	*记住函数名及其作用
一、单行函数：如concat，length，ifnull
	1）字符函数：
		length(str) 获取参数值的字节个数
		concat(str1,str2,str3...) 拼接字符串
		upper(str) / lower(str) 全部大写/小写参数字符串
		substr / substring(str,pos,len)  截取指定字符串，pos代表索引位置,len代表截取长度(索引从1开始)
		instr(str1,str2)  返回str2在str1中第一次出现的索引，如果找不到返回0
		trim(str) 消除前后空格    trim('a' from ”aaaaaWilllaaaaaLiuaaaaaaaa“) 可以消除字符串前后的'a'
		lapd(str,len,char) 用指定的字符实现左填充指定长度
		rpad(str,len,char) 用指定的字符实现右填充指定长度
		replace(str,str1,str2) 用str2替换str中的字串str1
	2）数学函数 
		round(X) 数字X四舍五入  round(X,D) 数字X的小数点后保留D位
		ceil(X) 向上取整  返回>=该参数的最小参数
		floor(X) 向下取整  返回<=该参数的最大参数
		truncate(X,D) 截断 数字X小数点后的D位后消除
		mod(x1,x2) 取余  x1 % x2     mod(a,b)  a-a/b*b
	3）日期函数
		now() 返回当前系统日期+时间
		curdate() 返回当前系统日期 不包含时间
		curtime() 返回当前系统时间 不包含系统
		获得指定的部分：年(YEAT())、月(MONTH() / MONTHNAME())、日(DAY())、小时(HOUR())、分钟(MINUTE())、秒(SECOND())
		str_to_date(date_str,format)     将日期字符串转换为format这个格式的日期
		date_format(date,format)  	将日期转换成fromat这个格式的字符串
		 %Y(四位年份) ，%y(二位年份) ，%m(月份(01,02...))，%c(月份(1,2...))，%d(日(01,02...))，%H(小时(24小时制))，%h(小时(12小时制))，%i(分钟(00,01,02...))，%s(秒(00,01,02...))
	4）其他函数【补充】
		VERSION() 当前数据库版本
		DATABASE() 当前数据库
		USER() 当前用户
	5）流程函数【补充】
		if(expr1,expr2,expr3) 判断expr1是否为true，如果为true返回expr2，反之返回expr3
		case函数：1）类似于这个switch(变量或表达式)...case(常量)
					case 要判断的字段或表达式
					when 常量1 then 要显示的值1或语句1
					when 常量2 then 要显示的值2或语句2
					...
					else 要显示的值n或语句n
					end
				2）case函数使用二 类似于多重if...else...
					case
					when 条件1 then 要显示的值1或语句1
					when 条件2 then 要显示的值2或语句2
					....
					else 要显示的值n或语句n
					end
		
二、分组函数（由于作用主要是做统计使用，又称之为统计函数，聚合函数，组函数）
	sum(字段)求和        
	avg(字段)平均值	  参数类型一般用于处理数值类型  
	max(字段)最大值  	   
	min(字段)最小值     参数类型适用于字符 数值 日期类型 
	count(字段)计算个数 参数类型适用于字符 数值 日期类型 只计算不为null的个数
	datadiff(date1,date2) 计算date1与date2之间的天数之差  用date1-date2
	select datediff(now(),'1999-02-11')
例子：
	select datediff(now(),'1999-02-11')
	select SUM(salary) 和, AVG(salary) 平均值, MAX(salary) 最高, MIN(salary) 最低, COUNT(salary) 个数 from employees;
三、特点：
	1.sum,avg一般用于处理数值型 max,min,count 适用于任意类型
	2.以上分组函数都忽略null值
	3.和distince搭配使用  select SUM(DISTINCT salary) from employees; 去重后求和
	4.一般都使用count(*) 来统计行数
	5.和分组函数一同查询的字段要求是group by后的字段

四、count函数的详细介绍
	count(*) / count(1) 统计行数   select COUNT(*) FROM 表名 / select COUNT(1) FROM 表名(多加了1列) 
	还可以使用count(字段)   这个字段是常量即可
效率：MYUSAM 存储引擎下， count(*)的效率高
	 INNODB存储引擎下，count(*) 和 count(1)的效率差不多，比count(字段)要高一些
五、和分组函数一同查询的字段有限制
	和分组函数一同查询的字段要求是group by后的字段

5.分组查询

语法：select 字段1，字段2(要求出现在group by 的后面)  
	 from 表名 
	 [where 筛选条件]
	 [group by 分组语句]
	 [order by column] 
查询每个工种的最高工资：
	select job_id,max(salary) 最高工资 from employees group by job_id;
查询每个位置上的部门个数：
	select count(*) 个数,location_id from departments group by location_id;
分组查询前添加筛选条件：WHERE关键字是作用的原始表的筛选条件
	select avg(salary),department_id from employees where email like '%a%' group by department_id
添加比较复杂的筛选条件:分组后再进行筛选使用HAVING关键字
	 		案例：查询那个部门的员工个数>2
	 			1)查询每个部门的员工个数大于2
	 			2)根据 1)的结果进行筛选，查询那个部门的员工个数>2
	 			select count(*),department_id from employees group by department_id having count(*) > 2; 

按表达式或函数分组：
	案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
	select count(*),length(last_name) len_name from employees group by length(last_name) having count(*) > 5; 
按多个字段进行分组：
	案例：查询每个部门每个工种的员工的平均工资
	select avg(salary),department_id,job_id from employees group by job_id,department_id;
添加排序：在真个插叙后添加条件进行升序或降序
	
注意：1.分组查询中的筛选条件分为两类：
			分组前 ---- 筛选条件作用于原始表   group by 子句的前面 使用where关键字
			分组后 ----筛选条件用于查询后的视图 group by 子句的后面 使用having关键字
	   	 分组函数做条件肯定是放在having子句中
	     能用分组前筛选的，优先考虑使用分组前的筛选，（考虑到性能）
	 2.group by 子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求），表达式函数（用的较少）
	 3.也可以添加排序，放在整个分组查询的最后面

6.连接查询

又称多表查询，当查询的字段 来自于多个表时，就会用到连接查询
笛卡尔积：没有有效的连接条件
分类：1）按年代分：sql92标准（仅仅支持内连接），sql99标准（推荐）支持内连接+外连接（左外和右外）+ 交叉连接
	 2）按功能分类：
	 	内连接
	 		等值连接
	 		非等值连接
	 		自连接
	 	外连接
	 		左外连接
	 		右外连接
	 		全外连接
	 	交叉连接
	 	
一、sql92标准
	等值连接：
	select 查询字段 from 表1 别名，表2 别名 where 表1.字段 = 表2.字段 and 筛选条件
		特点：1.多表等值连接的结果为多表的交集部分
			 2.n表连接，至少需要n-1个连接条件，可以实现三表连接
			 3. 多表的顺序没有要求，可以随意调换表的顺序
			 4.一般需要为表起别名
			 5.可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
			案例：#1：查询员工名和对应的部门名
					SELECT last_name,department_name
					FROM employees,departments
					WHERE employees.`department_id`=departments.`department_id`;
				 #2.查询员工名、工种号、工种名（为表起别名：提高语句的简洁度,区分多个重名的字段）
				 	SELECT e.last_name,e.job_id,j.job_title
					FROM employees  e,jobs j
					WHERE e.`job_id`=j.`job_id`;
				 #3.三表连接案例：查询员工名、部门名和所在的城市
					SELECT last_name,department_name,city
					FROM employees e,departments d,locations l
					WHERE e.`department_id`=d.`department_id`
					AND d.`location_id`=l.`location_id`
					AND city LIKE 's%'
					ORDER BY department_name DESC;
					
	非等值连接：(与等值连接基本一样，唯一不同的是连接条件的不同)
	select 查询字段 from 表1 别名，表2 别名 where 表1.字段 [>,>=,<=,...] 表2.字段 and 其他筛选条件
		#案例：查询员工的工资和工资级别
			SELECT salary,grade_level
			FROM employees e,job_grades g
			WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
			AND g.`grade_level`='A';

	自连接：（自连接查询的表是同一张表）
	select 查询字段 from 表1 别名1，表1 别名2 where 表1.字段 = 表1.字段 and 其他筛选条件
		案例：查询 员工名和上级的名称
			 SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
			 FROM employees e,employees m
			 WHERE e.`manager_id`=m.`employee_id`;
	
二、sql99标准
   语法：
   	select 查询列表
   	from 表1
   	【join type】join 表2
   	on 连接条件
   	where 筛选条件
   	group by 分组字段
   	having 分组后筛选条件
   	order by 排序字段
   	
	分类：【join type】
		内连接（★）：inner
		外连接
			左外(★):left 【outer】
			右外(★)：right 【outer】
			全外：full【outer】（mysql没有这个语法，Oracle有这个语法）
	交叉连接：cross 

1.内连接
	1.1等值连接
		select 查询列表
		from 表1 别名
		inner join 表2 别名
		on 连接条件;
	 1.1.1特点：
		1)添加排序、分组、筛选
		2)inner可以省略
		3) 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
		4)inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
	1.1.2案例 #1.查询名字中包含e的员工名和工种名（添加筛选）
				SELECT last_name,job_title
				FROM employees e
				INNER JOIN jobs j
				ON e.`job_id`=  j.`job_id`
				WHERE e.`last_name` LIKE '%e%';
			 #2.查询部门个数>3的城市名和部门个数，（添加分组+筛选）
				SELECT city,COUNT(*) 部门个数
				FROM departments d
				INNER JOIN locations l
				ON d.`location_id`=l.`location_id`
				GROUP BY city
				HAVING COUNT(*)>3;
			#3.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）
				SELECT last_name,department_name,job_title
				FROM employees e
				INNER JOIN departments d ON e.`department_id`=d.`department_id`
				INNER JOIN jobs j ON e.`job_id` = j.`job_id`
				ORDER BY department_name DESC;
			
	1.2非等值连接
		案例：
		#1.查询员工的工资级别
			SELECT salary,grade_level
			FROM employees e
		 	JOIN job_grades g
 			ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
 		#2.查询工资级别的个数>20的个数，并且按工资级别降序
 			SELECT COUNT(*),grade_level
			FROM employees e
	 		JOIN job_grades g
 			ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
 			GROUP BY grade_level
 			HAVING COUNT(*)>20
 			ORDER BY grade_level DESC;

	1.3自连接
		案例：
		#1.查询员工的名字、上级的名字
			SELECT e.last_name,m.last_name
			FROM employees e
			JOIN employees m
			ON e.`manager_id`= m.`employee_id`;

		#2.查询姓名中包含字符k的员工的名字、上级的名字
			SELECT e.last_name,m.last_name
			FROM employees e
			JOIN employees m
			ON e.`manager_id`= m.`employee_id`
 			WHERE e.`last_name` LIKE '%k%';
 		
2.外连接
	应用场景：用于查询一个表中有，另一个表没有的记录
 	2.1特点：
	1)外连接的查询结果为主表中的所有记录
	  如果从表中有和它匹配的，则显示匹配的值
	  如果从表中没有和它匹配的，则显示null
	  外连接查询结果=内连接结果+主表中有而从表没有的记录
	2)左外连接，left join左边的是主表
   	   右外连接，right join右边的是主表
	3)左外和右外交换两个表的顺序，可以实现同样的效果 
	4)全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有
	
	2.2案例：
	#案例1：查询哪个部门没有员工
 		#左外
			SELECT d.*,e.employee_id
			FROM departments d
			LEFT OUTER JOIN employees e
			ON d.`department_id` = e.`department_id`
			WHERE e.`employee_id` IS NULL;
		#右外
			SELECT d.*,e.employee_id
			FROM employees e
			RIGHT OUTER JOIN departments d
			ON d.`department_id` = e.`department_id`
			WHERE e.`employee_id` IS NULL;
	 	#全外（mysql没有这个，Oracle才有）
 			SELECT b.*,bo.*
			FROM beauty b
			FULL OUTER JOIN boys bo
			ON b.`boyfriend_id` = bo.id;
	 #交叉连接（类似于笛卡尔乘积）
			SELECT b.*,bo.*
			FROM beauty b
			CROSS JOIN boys bo;

7.子查询

含义：
	出现在其他语句中的select语句，称为子查询或内查询
	外部的查询语句，称为主查询或外查询
分类：
	按子查询出现的位置：
		select后面：仅仅支持标量子查询
		from后面：支持表子查询
		where或having后面：★
				标量子查询（单行） √
				列子查询  （多行） √
				行子查询（用的较少）
		exists后面(相关子查询):
				表子查询
	按结果集的行列数不同：
		标量子查询（结果集只有一行一列）
		列子查询（结果集只有一列多行）
		行子查询（结果集有一行多列）
		表子查询（结果集一般为多行多列

1.where或having后面
	1.1 特点：
		1)子查询放在小括号内
		2)子查询一般放在条件的右侧
		3)标量子查询，一般搭配着单行操作符使用 > < >= <= = <>
		  列子查询，一般搭配着多行操作符使用 in、any/some、all
		4)子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
	1.2 标量子查询★
		案例：#1.谁的工资比 Abel 高?
				SELECT *
				FROM employees
				WHERE salary>(
					SELECT salary
					FROM employees
					WHERE last_name = 'Abel'
				);
			#2.返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资
				SELECT last_name,job_id,salary
				FROM employees
				WHERE job_id = (
					SELECT job_id
					FROM employees
					WHERE employee_id = 141
				) AND salary>(
					SELECT salary
					FROM employees
					WHERE employee_id = 143
				);
			#3.返回公司工资最少的员工的last_name,job_id和salary
				SELECT last_name,job_id,salary
				FROM employees
				WHERE salary=(
					SELECT MIN(salary)
					FROM employees
				);
	1.3 列子查询（多行子查询）★
		案例：#1 返回location_id是1400或1700的部门中的所有员工姓名
				SELECT last_name
				FROM employees
				WHERE department_id  <>ALL(
					SELECT DISTINCT department_id
					FROM departments
					WHERE location_id IN(1400,1700)
				);
			#2 返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工   的员工号、姓名、job_id 以及salary
				SELECT last_name,employee_id,job_id,salary
				FROM employees
				WHERE salary<ALL(
					SELECT DISTINCT salary
					FROM employees
					WHERE job_id = 'IT_PROG'
				) AND job_id<>'IT_PROG';	

	1.4 行子查询（结果集一行多列或多行多列）
		案例：查询员工编号最小并且工资最高的员工信息
			SELECT *
			FROM employees
			WHERE employee_id=(
				SELECT MIN(employee_id)
				FROM employees
			)AND salary=(
				SELECT MAX(salary)
				FROM employees
			);
		
2.select后面：仅仅支持标量子查询
	案例：查询每个部门的员工个数
	 	SELECT d.*,(
      		SELECT COUNT(*)
			FROM employees e
			WHERE e.department_id = d.`department_id`
		) 个数
		FROM departments d;

3.from后面
	将子查询结果充当一张表，要求必须起别名
	案例：查询每个部门的平均工资的工资等级
		SELECT  ag_dep.*,g.`grade_level`
		FROM (
			SELECT AVG(salary) ag,department_id
			FROM employees
			GROUP BY department_id
		) ag_dep
		INNER JOIN job_grades g
	ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
	
4.exists后面（相关子查询）
	语法：
		exists(完整的查询语句)
		结果：(1或0)
	跟in的效果一样
	案例：查询有员工的部门名
			#in
			SELECT department_name
			FROM departments d
			WHERE d.`department_id` IN(
				SELECT department_id
				FROM employees
			)
			#exists
			SELECT department_name
			FROM departments d
			WHERE EXISTS(
				SELECT *
				FROM employees e
				WHERE d.`department_id`=e.`department_id`
			);

8.分页查询

当要显示的数据，一页显示不全，需要分页提交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;
案例：#1.查询前五条员工信息
		SELECT * FROM  employees LIMIT 0,5;
		SELECT * FROM  employees LIMIT 5;
	 #2.有奖金的员工信息，并且工资较高的前10名显示出来
		SELECT  * 
		FROM employees 
		WHERE commission_pct IS NOT NULL 
		ORDER BY salary DESC 
		LIMIT 10 ;

9.联合查询

union 联合 合并：将多条查询语句的结果合并成一个结果
语法：
	查询语句1
	union
	查询语句2
	union
	...
	应用场景：
		要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时
	特点：★
		1、要求多条查询语句的查询列数是一致的！
		2、要求多条查询语句的查询的每一列的类型和顺序最好一致
		3、union关键字默认去重，如果使用union all 可以包含重复项
	案例：#1 查询部门编号>90或邮箱包含a的员工信息
			SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;
			（两者效果一样）
			SELECT * FROM employees  WHERE email LIKE '%a%'
			UNION
			SELECT * FROM employees  WHERE department_id>90;
		 #2 查询中国用户中男性的信息以及外国用户中年男性的用户信息
			SELECT id,cname FROM t_ca WHERE csex='男'
			UNION ALL
			SELECT t_id,tname FROM t_ua WHERE tGender='male';

DML数据操作语言 的学习

DDL数据定义语言 的学习

TCL事务控制语言学习

DCL数据控制语言 关于权限的设置