MySQL数据库(基础篇)学习笔记

最新推荐文章于 2022-09-21 00:44:26 发布

HHU.项亮亮

最新推荐文章于 2022-09-21 00:44:26 发布

阅读量259

点赞数

分类专栏：学习笔记数据库 MySQL

本文链接：https://blog.csdn.net/XLL_123456/article/details/118567346

版权

学习笔记同时被 3 个专栏收录

19 篇文章 0 订阅

订阅专栏

数据库

2 篇文章 0 订阅

订阅专栏

MySQL

2 篇文章 0 订阅

订阅专栏

MySQL数据库学习笔记

一.为什么要用数据库？
二.MySQL安装与使用
三.DQL语言
四.DML语言
五.DDL语言

尚硅谷网课《MySQL基础教程丨MySQL数据库实战（SQL数据库优化）》
讲师：李玉婷老师
链接地址：https://www.bilibili.com/video/BV1xW411u7ax
学习时间：2021/07/08

一.为什么要用数据库？

保存数据的容器有：数组、集合（内存层面，数据易丢失）；文件（不利于数据管理、查询）；数据库。
数据库的好处：

 1.实现数据持久化
 2.使用完整的管理系统统一管理，易于查询

数据库相关术语：

DB：数据库（ database ）：存储数据的“仓库”。它保存了一系列有组织的数据。
DBMS：数据库管理系统（ Database Management System ）：数据库是通过 DBMS 创建和操作的容器。
SQL：结构化查询语言（ Structure Query Language ）：专门用来与数据库通信的语言

常见的java层面的数据库管理系统：MySQL、Oracle、DB2、SqlServer等。
SQL的优点：

1.不是某个特定数据库供应商专有的语言，几乎所有 DBMS都支持SQL ；
2.简单易学 ；
3.虽然简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。

数据是怎样存储的？

将数据放到表中，表再放到库中；
一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性；
表具有一些特性，这些特性定义了数据在表中如何存储，类似java中“类”的设计；
表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java中的“属性”；
表中的数据是按行存储的，每一行类似于java中的“对象”。

二.MySQL安装与使用

MySQL数据库最初由MySQL AB公司开发，总部位于瑞典，08年被SUN公司收购，09年SUN公司又被oracle收购。
优点：

1.成本低：开源，一般免费使用
2.性能高：执行很快
3.简单：容易安装和使用

MySQL服务的启动和停止的两种方式：

方式一：通过计算机管理方式： 右击计算机—管理—服务—启动或停止MySQL服务；
方式二：管理员身份进入windows命令行，
       启动：net start mysql服务名；
       停止：net stop mysql服务名。

MySQL服务的登录和退出的两种方式：

方式一：通过MySQL自带的客户端，直接输入密码进入。只限于root用户。
方式二：进入windows命令行登录：mysql 【-h 主机名 -P 端口号】 -u 用户名 -p密码

退出：
exit或ctrl+C

MySQL的常见命令：

//1.查看当前所有的数据库
show databases;

//2.打开指定的库
use 库名;

//3.查看当前库的所有表
show tables;

//4.查看其他库的所有表
show tables from 库名;

//5.创建表
create table 表名（
      列名 列类型
      列名 列类型
      ...
）

//6.查看表结构
desc 表名;

//7.查看服务器版本
//方式一：登录到mysql服务端
select version();
//方式二：没有登录到mysql服务端
mysql --version 或者 mysql -V

三.DQL语言

DQL：Data Query Language 数据查询语言

进阶1：基础查询

语法：

  select 查询列表
  from 表名

特点：
1.查询列表可以是：表中的字段、常量值、表达式、函数
2.查询的结构是一个虚拟的表格

//1.查询表中的单个字段
SELECT last_name FROM employees ;

//2.查询表中的多个字段
SELECT last_name,salary,email FROM employees ;

//3.查询表中的所有字段
//方式一：
SELECT 
  `employee_id`,
  `first_name`,
  `last_name`,
  `email`,
  `phone_number`,
  `job_id`,
  `salary`,
  `commission_pct`,
  `manager_id`,
  `department_id`,
  `hiredate` 
FROM
  employees ;
 
//方式二： 
SELECT * FROM employees ;

//备注：执行快捷键：F9;格式化语句快捷键：F12;

//4.查询常量值
SELECT 100;
SELECT 'john';

//5.查询表达式
SELECT 100*98；

//6.查询函数
SELECT VERSION();

//7.起别名
/*
好处一：便于理解
好处二：如果要查询的字段有重名的情况，使用别名可以区分开来
*/
//方式一：（使用as）
SELECT 100*98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
//方式二：(使用空格）
SELECT last_name 姓,first_name 名 FROM employees;

//案例：查询salary，显示结果为 out put（建议加双引号“”）
SELECT salary AS "out put" FROM employees;

//8.去重
//案例：查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM employees;

//9.+号的作用
/*
mysql中的+号：
仅仅只有一个功能：运算符
select 100+90;两个操作数都为数值型，则做加法运算
select '123'+90;其中一方为字符型，试图将字符型数值转换成数值型
                      如果转换成功，则继续做加法运算
select 'john'+90;     如果转换失败，则将字符型数值转换成0
select null+10;       只要其中一方为null，则结果为null
*/

//案例：查询员工民和姓连接为一个字段，并显示为 姓名,使用concat();
SELECT CONCAT('a','b','c') AS 结果;
SELECT CONCAT(last_name,'-', first_name) AS 姓名 FROM employees;

//备注：IFNULL(expr1,expr2)
SELECT CONCAT(`first_name`,',',`last_name`,',',`job_id`,',',IFNULL(commission_pct,0)) AS out_put FROM employees;
//ISNULL(expr)：为null返回1，不为null返回0。

进阶2：条件查询

语法：

 select 查询列表
  from 表
  where 筛选条件

分类：
一.按条件表达式筛选
比较运算符：>、<、=、!=、 <>、<=、>=
二.按逻辑表达式筛选
逻辑运算符：&& || !
and or not
三.模糊查询
like
between and
in
is null

//一.按条件表达式筛选
//案例1:查询工资>12000的员工信息
SELECT 
 * 
FROM
 employees 
WHERE salary > 12000 ;

//案例2：查询部门编号不等于90号的员工名和部门编号
SELECT 
 first_name,
 department_id 
FROM
 employees 
WHERE department_id <> 90 ;

//二.按逻辑表达式筛选
//案例1：查询工资在10000到20000之间的员工名、工资以及奖金
SELECT 
 `first_name`,
 `salary`,
 `commission_pct` 
FROM
 employees 
WHERE salary >= 10000 
 AND salary <= 20000 ;
//案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
SELECT 
 * 
FROM
 employees 
WHERE department_id < 90 
 OR department_id > 110 
 OR salary > 15000 ;
 
//三.模糊查询
/*
like
between and 
in 
is null   
*/
//1.like
/*
特点：
①一般和通配符搭配使用
通配符：
% 任意多个字符，包含0个字符
_ 任意单个字符
*/
//案例1：查询员工名中包含字符a的员工信息
SELECT 
 * 
FROM
 employees 
WHERE first_name LIKE '%a%' ;

//案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资
SELECT 
 first_name,
 salary 
FROM
 employees 
WHERE first_name LIKE '__e_a%' ;

//案例3：查询员工名中第二个字符为_的员工名
//方式一：
SELECT 
 first_name 
FROM
 employees 
WHERE first_name LIKE '_\_%' ;
//方式二：
SELECT 
 first_name 
FROM
 employees 
WHERE first_name LIKE '_$_%' ESCAPE '$' ;

//2.between and 
/*
①可以提高语句的简洁度
②包含临界值
③两个临界值不要调换顺序
*/
//案例1：查询员工编号在100到120之间的员工信息
//方式一：
SELECT 
 * 
FROM
 employees 
WHERE employee_id >= 100 
 AND employee_id <= 120 ;

//方式二：
SELECT 
 * 
FROM
 employees 
WHERE employee_id BETWEEN 100 
 AND 120 ;

//3.in
/*
含义：查询某字段的值是否属于in列表中的某一项
特点：
①使用in提高语句的简洁度
②in列表的值类型必须一致或兼容
*/
//案例1：查询原因的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
//方式一：
SELECT 
 first_name,
 job_id 
FROM
 employees 
WHERE job_id = 'IT_PROG' 
 OR job_id = 'AD_VP' 
 OR job_id = 'AD_PRES' ;
//方式二：
SELECT 
 first_name,
 job_id 
FROM
 employees 
WHERE job_id IN ('IT_PROG', 'AD_VP', 'AD_PRES') ;

//4.is null
/*
= 或 <> 不能用于判断null值
is null 或 is not null可以
*/

//案例1：查询没有奖金的员工名和奖金率
SELECT 
 first_name,
 commission_pct 
FROM
 employees 
WHERE commission_pct IS NULL ;

//案例2：查询有奖金的员工名和奖金率
SELECT 
 first_name,
 commission_pct 
FROM
 employees 
WHERE commission_pct IS NOT NULL ;

//安全等于
//案例1：查询没有奖金的员工名和奖金率
SELECT 
 first_name,
 commission_pct 
FROM
 employees 
WHERE commission_pct <=> NULL ;
//案例2：查询工资为12000的员工信息
SELECT 
 * 
FROM
 employees 
WHERE salary <=> 12000 ;

//is null pk <=>
//IS NULL：仅仅可以判断NULL值，可读性较高，建议使用
//<=>：既可以判断NULL值，又可以判断普通的数值，但是可读性较低

进阶3：排序查询

语法：

  select 查询列表
  from 表
  【where 筛选条件】
  order by 排序列表 asc | desc

特点：
1.asc代表是升序，desc代表是降序；如果不写，默认是升序。
2.order by 子句中可以支持单个字段、多个字段、表达式、函数、别名。
3.order by 子句一般放在查询语句的最后面，limit子句除外。

//案例1：查询员工信息，要求工资从高到低排序
SELECT 
 * 
FROM
 employees 
ORDER BY salary DESC ;

//案例2：查询部门编号>=90的员工信息，按入职时间的先后进行排序【添加筛选条件】
SELECT 
 * 
FROM
 employees 
WHERE department_id >= 90 
ORDER BY hiredate ASC ;

//案例3：按年薪的高低显示员工的信息和年薪【按表达式排序】
SELECT 
 *,
 salary * 12 * (1+ IFNULL(commission_pct, 0)) AS 年薪 
FROM
 employees 
ORDER BY salary * 12 * (1+ IFNULL(commission_pct, 0)) ASC ;

//案例4：按年薪的高低显示员工的信息和年薪【按别名排序】
SELECT 
 *,
 salary * 12 * (1+ IFNULL(commission_pct, 0)) AS 年薪 
FROM
 employees 
ORDER BY 年薪 ASC ;

//案例5：按姓的长度显示员工的姓和工资【按函数排序】
SELECT 
 last_name,
 salary 
FROM
 employees 
ORDER BY LENGTH(last_name) ASC ;

//案例6：查询员工信息，要求先按工资升序，再按员工编号降序【按多个字段排序】
SELECT 
 * 
FROM
 employees 
ORDER BY salary ASC,
 employee_id DESC ;

进阶4：常见函数

语法：

select 函数名(实参列表) 【from 表】;

//一.字符函数
//1.length：获取参数值的字节个数
SELECT LENGTH('john');
SELECT LENGTH('张三丰hahaha');

SHOW VARIABLES LIKE '%char%';

//2.concat：拼接字符串
SELECT CONCAT(first_name,'_',last_name) 姓名 FROM employees;

//3.upper、lower
SELECT UPPER('john');
SELECT LOWER('JOHN');

//示例：将姓边大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) 姓名 FROM employees;

//4.substr、substring
//注意：SQL语言中，索引从1开始
//截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7);//陆展元

//截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3);//李莫愁

//案例：姓名中首字符大写，其他字符小写，然后用_拼接，显示出来。
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)),LOWER(first_name)) 姓名 FROM employees;

//5.instr：返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put;//7

//6.trim：
SELECT TRIM('   张翠山   ');
SELECT TRIM('a' FROM 'aaaaaaaa张aaaaaaaa翠山aaaaaaaaaa');

//7.lpad：用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',10,'*');

//8.rpad：用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',12,'ab');

//9.replace：替换
SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏');

//二.数学函数
//1.round：四舍五入
SELECT ROUND(-1.45);
SELECT ROUND(1.567,2);

//2.ceil：向上取整,返回>=该参数的最小整数；
SELECT CEIL(1.002);

//3.floor：向下取整，返回<=该参数的最大整数；
SELECT FLOOR(9.99);

//4.truncate：截断
SELECT TRUNCATE(1.69,1);

//5.mod：取余
SELECT MOD(10,3);
SELECT 10%3;

//6.rand：获取随机数，返回0~1之间的小数

//三.日期函数
//1.now：返回当前系统日期+时间
SELECT NOW();

//2.curdate：返回当前系统日期，不包含时间
SELECT CURDATE();

//3.curtime：返回当前系统时间，不包含日期
SELECT CURTIME();

//4.可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1997-2-11') 年;
SELECT YEAR(hiredate) FROM employees;

SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());

DAY、HOUR、MINUTE、SECOND

//5.str_to_date：将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1997-2-11','%Y-%c-%d');

//查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate ='1992-4-3';
SELECT * FROM employees WHERE hiredate =STR_TO_DATE('4-3 1992','%c-%d %Y');

//6.date_format：将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日');

//查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT 
  first_name,
  DATE_FORMAT(hiredate, '%m月/%d日 %y年') AS 日期 
FROM
  employees 
WHERE commission_pct IS NOT NULL ;

//四.其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
password('字符')或MD5('字符');//两种加密方式

//五.流程控制函数
//1.if函数：if else的效果
SELECT IF(10>5,'大','小');

SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金','有奖金') 备注 FROM employees;

//2.case函数的使用一：switch case的效果
/*
case：要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；
when 常量2 then 要显示的值2或语句2；
...
else 要显示的值n语句n；
end
*/
/*
案例：查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资
*/
SELECT salary 原工资,department_id,
  CASE
    department_id 
    WHEN 30 
    THEN salary * 1.1 
    WHEN 40 
    THEN salary * 1.2 
    WHEN 50 
    THEN salary * 1.3 
    ELSE salary  
    END AS 实际工资
FROM
  employees ;

//2.case函数的使用二：类似于多重if
/*
case 
when 条件1 then 要显示的值1或语句1；
when 条件2 then 要显示的值2或语句2；
...
else 要显示的值n或语句n
end
*/

//案例：查询员工的工资的情况
/*
如果工资>20000，显示A级别
如果工资>15000，显示B级别
如果工资>10000，显示C级别
否则，显示D级别
*/
SELECT salary AS 原工资,employee_id,
CASE
WHEN salary>20000 THEN 'A级别'
WHEN salary>15000 THEN 'B级别'
WHEN salary>10000 THEN 'C级别'
ELSE 'D级别'
END AS 工资级别
FROM employees;

/*
二.分组函数
功能：用于统计使用
分类：sun求和；avg平均值；max最大值；min最小值；count计算个数
注意：
1.以上分组函数都忽略null值
2.可以和distinct搭配 
3.一般使用count(*)查询行数
4.和分组函数一同查询的字段要求是group by后的字段
*/
//1.简单的使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数 FROM employees;
SELECT SUM(salary) 和,ROUND(AVG(salary)) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数 FROM employees;

//2.和distinct搭配
SELECT SUM(DISTINCT salary) FROM employees;
SELECT COUNT(DISTINCT salary) FROM employees;

//3.count函数的详细介绍
SELECT COUNT(salary) FROM employees;

SELECT COUNT(*) FROM employees;#输出表的行数

SELECT COUNT(1) FROM employees;#也是输出表的行数

SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) FROM employees;

SELECT DATEDIFF(NOW(),'1997-2-11');#输出天数差

SELECT COUNT(*) FROM employees
WHERE department_id=90;

进阶5：分组查询

可以使用GROUP BY子句将表中的数据分成若干组
语法：

 select 分组函数，列(要求出现在group by的后面)
 from 表
 【where 筛选条件】
 group by 分组的列表
 【order by 子句】
注意：
      查询列表必须特殊，要求是分组函数和group by后出现的字段
特点：
      1.分组查询中的筛选条件分为两类
                         数据源           位置                     关键字
      分组前筛选         原始表           group by子句的前面       where
      分组后筛选         分组后的结果集   group by子句的后面       having
      ①分组函数做条件肯定放在having子句中
      ②能用分组前筛选的，优先考虑分组前筛选      
      2.group by子句支持单个字段分组，也支持多个字段分组(多个字段之间用逗号隔开，没有顺序)，
        还支持表达式或函数(用得较少)
      3.也可以添加排序(排序放在整个分组查询的最后)

//引入：查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;

//简单分组查询
//案例1：查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;

//案例2：查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

//添加筛选条件
//案例1：查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

//案例2：查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary) 最高工资,manager_id 领导编号
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

//添加复杂的筛选条件
//案例1：查询哪个部门的员工个数大于2
//①查询每个部门的员工个数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;

//②根据①的结果进行筛选，查询哪个部门的员工个数>2
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;

//案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
//①查询每个工种有奖金的员工的最高工资
SELECT job_id 工种编号,MAX(salary) 最高工资
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id;

//②根据①的结果继续筛选，最高工资>12000
SELECT job_id 工种编号,MAX(salary) 最高工资
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING 最高工资>12000;

//案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个,以及其最低工资
SELECT MIN(salary) 最低工资,manager_id 领导编号
FROM employees
WHERE manager_id>102
GROUP BY 领导编号
HAVING MIN(salary)>5000;

//按表达式或函数分组
//案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
SELECT COUNT(*),LENGTH(first_name) 员工名长度
FROM employees
GROUP BY LENGTH(first_name)
HAVING COUNT(*)>5;

//按多个字段分组
//案例：查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;

//添加排序
//案例：查询每个部门每个工种的员工的平均工资，并且按平均工资降序排列
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY AVG(salary) DESC;

进阶6：连接查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1 有m行，表2 有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：     
            按年代分类：
            sql92标准：仅仅支持内连接
            sql99标准【推荐】：支持内连接+外连接(左外和右外)+交叉连接      
            按功能分类：
            内连接：
                   等值连接
                   非等值连接
                   自连接                   
            外连接：
                   左外连接
                   右外连接
                   全外连接            
            交叉连接

//一.sql92标准
//1.等值连接   
SELECT * FROM beauty;
SELECT * FROM boys;

SELECT NAME,boyName FROM boys,beauty
WHERE beauty.boyfriend_id=boys.id;
//1.
/*
①多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
*/

//案例1：查询女神名和对应的男神名
SELECT NAME,boyName 
FROM boys,beauty
WHERE beauty.boyfriend_id=boys.id;

//案例2：查询员工名和对应的部门名
select `first_name`,`department_name`
FROM `employees`,`departments`
WHERE `employees`.`department_id`=`departments`.`department_id`;

//2.为表起别名
/*
好处：①提高语句的简洁度
      ②区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/      

//查询员工号、工种号、工种名
SELECT `employee_id`,e.`job_id`,`job_title`
FROM `employees` e,`jobs` j
WHERE e.`job_id`=j.`job_id`;

//3.两个表的顺序是否可以调换？可以
//查询员工号、工种号、工种名
SELECT `employee_id`,e.`job_id`,`job_title`
FROM `jobs` j,`employees` e
WHERE e.`job_id`=j.`job_id`;

//4.是否可以加筛选？可以

//案例1：查询有奖金的员工名、部门名
SELECT `first_name`,`department_name`
FROM `employees` e,`departments` d
WHERE e.`department_id`=d.`department_id` AND `commission_pct` IS NOT NULL;

//案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT `department_name`,`city`
FROM `departments` d,`locations` l
WHERE d.`location_id`=l.`location_id`
AND l.`city` LIKE '_o%';

//5.是否可以加分组？可以

//案例1：查询每个城市的部门个数
SELECT COUNT(*) 个数,`city`
FROM `departments` d,`locations` l
WHERE d.`location_id`=l.`location_id`
GROUP BY `city`;

//案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT `department_name`,d.manager_id,MIN(salary)
FROM `departments` d,`employees` e
WHERE d.`department_id`=e.`department_id`
AND `commission_pct` IS NOT NULL
GROUP BY `department_name`,d.`manager_id`;

//6.是否可以加排序？可以

//案例1：查询每个工种的工种名和员工的个数，并且按照员工个数降序
SELECT `job_title`,COUNT(*) 员工个数
FROM `jobs` j,`employees` e
WHERE j.`job_id`=e.`job_id`
GROUP BY `job_title`
ORDER BY COUNT(*) DESC;

//7.是否可以三表连接？可以
//案例：查询员工名、部门名和所在的城市
SELECT `first_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`;

//1.非等值连接
//案例：查询员工的工资和工资级别
SELECT `salary`,`grade_level`
FROM `employees` e,`job_grades` j
WHERE `salary` BETWEEN `lowest_sal` AND `highest_sal`; 

//3.自连接
//案例：查询员工名和上级的名称
SELECT e.`first_name` 员工名,m.`first_name` 上级
FROM `employees` e,`employees` m
WHERE e.`manager_id`=m.`employee_id`;

二.sql99标准
语法：    select 查询列表
          from 表1 别名  
         【连接类型】jion 表2 别名   
          on 连接条件
         【where 筛选条件】
         【group by分组】
         【having 筛选条件】
         【order by 排序列表】        
分类：
内连接：inner
外连接： 左外：left【outer】
        右外：right【outer】
        全外：full【outer】
交叉连接：cross

//1.内连接
//一.内连接
/*
语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;

分类：
等值
非等值
自连接

特点：
①添加排序、分组、筛选
②inner可以省略
③连接条件放在on后面，筛选条件放在where后面，提高分离性，可读性高。
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
*/

//1.等值连接
//案例1：查询员工名、部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`;

//案例2：查询名字中包含e的员工名和工种名(添加筛选)
SELECT `first_name`,`job_title`
FROM `employees` e
INNER JOIN `jobs` j
ON e.`job_id`=j.`job_id`
WHERE e.`first_name` LIKE '%e%';

//案例3：查询部门个数>3的城市名和部门个数(添加分组+筛选)
SELECT `city`,COUNT(*)
FROM `locations` l
INNER JOIN `departments` d
ON l.`location_id`=d.`location_id`
GROUP BY l.`city`
HAVING COUNT(*)>3;

//案例4：查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序(添加排序)
SELECT `department_name`,COUNT(*)
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id`=d.`department_id`
GROUP BY `department_name`
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;

//案例5：查询员工名、部门名、工种名，并按部门名降序( )
SELECT `first_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 d.`department_name` DESC;

//2.非等值连接
//案例1：查询员工的工资级别
SELECT `grade_level`,`salary`
FROM `job_grades` j
INNER JOIN `employees` e
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

//案例2：查询工资级别的个数>20的个数，并且按工资级别降序
SELECT `grade_level`,COUNT(*)
FROM `job_grades` j
INNER JOIN `employees` e
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
GROUP BY `grade_level`
HAVING COUNT(*)>20
ORDER BY `grade_level` DESC;

//3.自连接
//案例：查询姓名中包含字符k的员工的名字、上级的名字
SELECT e.`first_name`,q.`first_name`
FROM  `employees` e
INNER JOIN `employees` q
ON e.`manager_id`=q.`employee_id`
WHERE e.`first_name` LIKE '%k%';

//2.外连接
//二.外连接
/*
应用场景：用于查询一个表中有，另一个表没有的记录

特点：
1.外连接的查询结果为主表中的所有记录
  如果从表中有和它匹配的，则显示匹配的值
  如果从表中没有和它匹配的，则显示null
  外连接查询结果=内连接结果+主表中有而从表中没有的记录
2.左外连接，left join左边的是主表
  右外连接，right join右边的是主表
3.左外和右外交换两个表的顺序，可以实现同样的效果
4.全外连接=内连接的结果+表1有但表2没有的+表2有但表1没有的
*/

//引入：查询男朋友不在男神表的女神名
//左外连接
SELECT b.name,bo.*
FROM `beauty` b
LEFT OUTER JOIN `boys` bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;

//右外连接
SELECT b.name,bo.*`myemployees`
FROM `boys` bo
RIGHT OUTER JOIN `beauty` b
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;

//案例1：查询哪个部门没有员工
SELECT `department_name`,e.`employee_id`
FROM `departments` d
LEFT OUTER JOIN `employees` e
ON d.`department_id`=e.`department_id`
WHERE `employee_id` IS NULL;

//全外
USE girls;

SELECT b.*,bo.*
FROM `beauty` b
FULL OUTER JOIN `boys` bo
ON b.`boyfriend_id`=bo.`id`;//会报错

// 3.交叉连接：笛卡尔乘积
//三.交叉连接：笛卡尔乘积
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

//sql92 pk sql99
//功能：sql99支持的较多
//可读性：sql99实现连接条件和筛选条件的分离，可读性较高

进阶7：子查询

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

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

//一.where或having后面
//1.标量子查询(单行子查询)
//2.列子查询(多行子查询)
//3.行子查询(多列子查询)
/*
特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配单行操作符使用
> < >= <= = <>
 列子查询，一般搭配着多行操作符使用
in，any/some，all

④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
*/
//1.标量子查询
//案例1：谁的工资比Able高？
//①查询Able的工资
SELECT 
  salary 
FROM
  `employees` 
WHERE `last_name` = 'Abel' 

//②查询员工的信息，满足salary>①的结果
  SELECT 
    * 
  FROM
    `employees` 
  WHERE `salary` > 
    (SELECT 
      salary 
    FROM
      `employees` 
    WHERE `last_name` = 'Abel') ;

//案例2：返回job_id和141号员工相同，salary比143号员工多的员工 姓名，job_id和工资
//①查询141号员工的job_id
SELECT `job_id`
FROM `employees`
WHERE `employee_id`=141

//②查询143号员工的salary
SELECT `salary`
FROM `employees`
WHERE `employee_id`=143

//③查询员工的姓名，job_id和工资，要求job_id=①并且salary>②
SELECT `first_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 MIN(`salary`)
FROM `employees`

#②查询last_name，job_id和salary，要求salary=①
SELECT `last_name`,`job_id`,`salary`
FROM `employees`
WHERE salary=(
	SELECT MIN(`salary`)
	FROM `employees`
);

//案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
//①查询50号部门的最低工资
SELECT MIN(salary)
FROM `employees`
WHERE `department_id`=50

//②查询每个部门的最低工资
SELECT `department_id`,MIN(`salary`)
FROM `employees`
GROUP BY `department_id`

//③在②的基础上筛选，满足min(salary)>①
SELECT `department_id`,MIN(salary)
FROM `employees`
GROUP BY `department_id`
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM `employees`
WHERE `department_id`=50
);

//2.列子查询(多行子查询)

多行比较操作符

//案例1：返回location_id是1400或1700的部门中的所有员工姓名
//方式一：内连接
SELECT `last_name`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id`=d.`department_id`
WHERE `location_id` IN(1400,1700);

//方式二：列子查询
//①查询location_id是1400或1700的部门编号
SELECT `department_id`
FROM `departments`
WHERE `location_id` IN(1400,1700)

//②查询员工姓名，要求部门号是①列表中的某一个
SELECT `last_name`
FROM `employees`
WHERE `department_id` IN(
SELECT `department_id`
FROM `departments`
WHERE `location_id` IN(1400,1700)
);

//案例2：返回其它工种中比job_id为'IT_PROG'部门任意工资低的员工的员工号、姓名、job_id以及salary
//①查询job_id为'IT_PROG'的部门的任意工资
SELECT `salary`
FROM `employees`
WHERE `job_id`='IT_PROG';

//②查询员工号、姓名、job_id以及salary，salary<(①)中的任意一个
SELECT `employee_id`,`last_name`,`job_id`,`salary`
FROM `employees`
WHERE `salary`<ANY(
SELECT `salary`
FROM `employees`
WHERE `job_id`='IT_PROG'
)
AND `job_id`<>'IT_PROG';

//案例3：返回其他部门中比job_id为'IT_PROG'部门所有员工工资都低的员工的员工号、姓名、job_id以及salary
SELECT `employee_id`,`last_name`,`job_id`,`salary`
FROM `employees`
WHERE `salary`<ALL(
SELECT `salary`
FROM `employees`
WHERE `job_id`='IT_PROG'
)
AND `job_id`<>'IT_PROG';

//3.行子查询(一行多列)
//案例：查询员工编号最小并且工资最高的员工信息
//方式一：
//①查询最小的员工编号
SELECT MIN(`employee_id`)
FROM `employees`

//②查询最高的工资
SELECT MAX(`salary`)
FROM `employees`

//③查询员工信息
SELECT *
FROM `employees`
WHERE `employee_id`=(
SELECT MIN(`employee_id`)
FROM `employees`)
AND
`salary`=(
SELECT MAX(`salary`)
FROM `employees`
);

//方式二：行子查询
SELECT *
FROM `employees`
WHERE (`employee_id`,`salary`)=(
SELECT MIN(`employee_id`),MAX(`salary`)
FROM `employees`
);

//二.select后面(了解，一般可以用其他方式代替)

//案例1：查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM `employees` e
WHERE e.`department_id`=d.`department_id`
)
FROM `departments` d

//案例2：查询员工号=102的部门名
SELECT `department_name`,`employee_id`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id`=d.`department_id`
WHERE `employee_id`=102;

//三.from后面
/*
将子查询结果充当一张表，要求必须起别名
*/
//案例：查询每个部门的平均工资的工资等级(?)
//①查询每个部门的平均工资
SELECT AVG(`salary`),`department_id`
FROM `employees`
GROUP BY `department_id`;

//②连接①的结果集和job_grades表，筛选条件平均工资 
SELECT ag,`department_id`,`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`;

//四.exisits后面(相关子查询)
/*
语法：exists(完整的查询语句)
结果：
1或0
*/
SELECT EXISTS(SELECT `employee_id` FROM `employees`);

进阶8：分页查询

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
     select 查询列表
     from 表1
     【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：查询第11条-第25条
SELECT *
FROM `employees`
LIMIT 10,15;

//案例3：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT *
FROM `employees`
WHERE `commission_pct` IS NOT NULL
ORDER BY salary DESC
LIMIT 10;

//查询语句的执行先后顺序？
select 查询列表                //7  
from 表2                       //1                    
连接类型 join 表2              //2
on 连接条件                    //3
where 筛选条件                 //4
group by 分组条件              //5
having 分组后筛选              //6
order by 排序列表              //8
limit 偏移，条目数             //9

进阶9：union联合查询

union 联合：将多条查询语句的结果合并成一个结果

语法：
查询语句1
union
查询语句2
union
...

应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时。

特点：
1.要求多条查询语句的查询列数是一致的
2.要求多条查询语句的查询的每一列的类型和顺序最好是一致的
3.使用union关键字默认去重，如果使用union all可以包含重复项

//引入的案例：查询部门编号>90或邮箱包含a的员工信息
//方式一
SELECT *
FROM `employees`
WHERE `department_id`>90
OR `email` LIKE '%a%';

//方式二：联合查询
SELECT * FROM `employees` WHERE `email` LIKE '%a%'
UNION
SELECT * FROM `employees` WHERE `department_id`>90;

//案例：查询中国用户中男性的信息以及外国用户中男性的信息
SELECT id,cname,csex FROM t_ca WHERE csex='男'
UNION
SELECT t_id,tName,tGender FROM t_ua WHERE tGender='male';

四.DML语言

/*
数据操作语言
插入：insert
修改：update
删除：delete
*/
//一.插入语句
//方式一：经典的插入
/*
语法：
insert into 表名(列名,...) values(值1,...);
*/
//1.插入的值的类型要与列的类型一致或兼容
INSERT INTO `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`)
VALUES(13,'唐艺昕','女','1990-4-23','18988888888',NULL,2);

//2.不可以为null的列必须插入值，可以为null的列如何插入值？
//方式一：
INSERT INTO `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`)
VALUES(13,'唐艺昕','女','1990-4-23','18988888888',NULL,2);

//方式二：列名和值都不写
INSERT INTO `beauty`(`id`,`name`,`sex`,`phone`)
VALUES(14,'金星','女','13888888888');

INSERT INTO `beauty`(`id`,`name`,`sex`,`phone`)
VALUES(15,'娜扎','女','13888888888');

//3.列的顺序是否可以调换？可以，一一对应即可
INSERT INTO `beauty`(`name`,`sex`,`id`,`phone`)
VALUES('金星','女',16,'13888888888');

//4.列数和值的个数必须一致
INSERT INTO `beauty`(`name`,`sex`,`id`,`phone`,`boyfriend_id`)
VALUES('关晓彤','女',17,'13888888888');

//5.可以省略列名，默认是所有列，而且列的顺序和表中列的顺序一致
INSERT INTO `beauty`
VALUES(18,'张飞','男',NULL,'119',NULL,NULL);

//方式二
/*
insert into 表名
set 列名=值，列名=值
*/
INSERT INTO `beauty`
SET `id`=19,`name`='刘涛',`phone`='999';

//两种方式大PK
//1.方式一支持插入多行,方式二不支持
INSERT INTO `beauty`
VALUES(23,'唐艺昕','女','1990-4-23','18988888888',NULL,2)
,(24,'唐艺昕','女','1990-4-23','18988888888',NULL,2)
,(25,'唐艺昕','女','1990-4-23','18988888888',NULL,2);

//方式一支持子查询，方式二不支持
INSERT INTO `beauty`(`id`,`name`,`phone`)
SELECT 26,'宋茜','11809866';

//二.修改语句
/*
1.修改单表的记录 √

语法：
update 表名
set 列=新值,列=新值,...
where 筛选条件;

2.修改多表的记录【补充】
语法：
sql92语法：
update 表1 别名,表2 别名 
set 列=值,...
where 连接条件
and 筛选条件;

sql99语法：
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件;
*/

//1.修改单表的记录
//案例1：修改beauty表中姓唐的女神的电话为13899888899
UPDATE `beauty` SET `phone`='13899888899' WHERE `name` LIKE '唐%';

//案例2：修改boys表中id号为2的名称为张飞，魅力值为10
UPDATE `boys` SET `boyName`='张飞',`userCP`=10 WHERE `id`=2;

//2.修改多表的记录
//案例1：修改张无忌的女朋友的手机号为114
UPDATE `beauty` b
INNER JOIN `boys` bo
ON b.`boyfriend_id`=bo.`id`
SET `phone`='114'
WHERE `boyName`='张无忌';

//案例2：修改没有男朋友的女神的男朋友的编号都为张飞
UPDATE `beauty` 
SET `boyfriend_id`=(
SELECT `id` 
FROM `boys`
WHERE `boyName`='张飞'
)
WHERE `boyfriend_id` IS NULL;

//三.删除语句
/*
方式一：delete
语法：
1.单表的删除 √
delete from 表名 where 筛选条件;

2.多表的删除【补充】

sql92语法：
delete 别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;

sql99语法：
delete 别名
from 表1 别名
inner|left|right join 表2 别名
on 连接条件
where 筛选条件;


方式二：truncate
语法：truncate table 表名;
*/

//方式一：delete
//1.单表的删除
//案例1：删除手机手机号以9结尾的女神信息
DELETE FROM `beauty`
WHERE `phone` LIKE '%9';

//2.多表的删除
//案例1：删除张无忌的女朋友的信息
DELETE b
FROM `beauty` b
INNER JOIN `boys` bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='张无忌';

//案例2：删除黄晓明的信息以及它女朋友的信息
DELETE b,bo
FROM `beauty` b
INNER JOIN `boys` bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='黄晓明';

//方式二：truncate语句
TRUNCATE TABLE `boys`; #清空整张表

//delete pk truncate(常见的一道面试题)
/* 1.delete可以加where条件，truncate不能加
   2.truncate删除，效率高一丢丢
   3.假如要删除的表有自增长列，
   如果用delete删除后，在插入数据，自增长列的值从断点开始，
   而truncate删除后，在插入数据，自增长列的值从1开始。
   4.truncate删除后没有返回值,delete删除后有返回值。
   5.truncate删除不能回滚，delete删除可以回滚。
*/

五.DDL语言

/*
数据定义语言

库和表的管理

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

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

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

//一.库的管理
//1.库的创建
/*
语法：create database (if not exists) 库名;
*/

//案例：创建库Books
CREATE DATABASE IF NOT EXISTS books;

//案例：库的修改
//库名的修改一般不做，因为不太安全，已经废弃的一句sql语句：
RENAME DATABASE books TO 新库名;
//真要想修改，直接去改库文件夹名字。

//更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;

//3.库的删除
DROP DATABASE IF EXISTS books;

//二.表的管理
//1.表的创建 √
/*
create table 表名(
          列名 列的类型【(长度) 约束】,
          列名 列的类型【(长度) 约束】,
          列名 列的类型【(长度) 约束】,
          ...
          列名 列的类型【(长度) 约束】
)
*/
//案例：创建表Book
CREATE TABLE book(
                id INT,#编号
                bName VARCHAR(20),#图书名
                price DOUBLE,#价格
                authorId INT,#作者编号
                publishDate DATETIME #出版日期
);

DESC book;

//案例：创建表author
CREATE TABLE author(
            id INT,
            au_name VARCHAR(20),
            nation VARCHAR(10)
);

//2.表的修改
/*
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
*/
//①修改列名
ALTER TABLE book CHANGE COLUMN `publishDate` pubDate DATETIME;

//②修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;

//③添加新列
ALTER TABLE book ADD COLUMN annual DOUBLE;

//④删除列
ALTER TABLE book DROP COLUMN  annual;
//⑤修改表名
ALTER TABLE author RENAME TO book_author;

//3.表的删除
DROP TABLE book_author;
DROP TABLE IF EXISTS book_author;

SHOW TABLES;
//4.表的复制
INSERT INTO author
VALUES(1,'村上春树','日本'),
(2,'莫言 ','中国'),
(3,'冯唐','中国'),
(3,'金庸','中国');

SELECT * FROM author;

//1.仅仅复制表的结构
CREATE TABLE copy LIKE author;

SELECT * FROM copy;

//2.复制表的结构+数据
CREATE TABLE copy2
SELECT * FROM author;

SELECT * FROM copy2;

//只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='中国';

//仅仅复制某些字段
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 1=2;
//where 0;

//常见的数据类型
/*
数值型：
       整型
       小数：
            定点数
            浮点数
字符型：
       较短的文本：char、varchar
       较长的文本：text、blob(较长的二进制数据)
日期型：
*/

//一.整型
/*
分类：
tinyint、smallint、mediumint、int|integer、bigint
   1        2          3           4          8

特点：
①如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
②如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值
③如果不设置长度，会有默认的长度
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！
*/

//1.如何设置无符号数和有符号数
CREATE TABLE tab_int(
             t1 INT,
             t2 INT UNSIGNED
);

//一.整型
/*
分类：
tinyint、smallint、mediumint、int|integer、bigint
   1        2          3           4          8

特点：
①如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
②如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值
③如果不设置长度，会有默认的长度
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！
*/

//1.如何设置无符号数和有符号数
CREATE TABLE tab_int(
             t1 INT,
             t2 INT UNSIGNED
);

//二.小数
/*
1.浮点型
flooat(M,D)
double(M,D)
2.定点型
dec|decimal(M,D)

特点：
①
M：整数部位+小数部位
D：小数部位
如果超过范围，则插入临界值

②M和D可以省略
如果是decimal，则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来确定精度

③定点型的精确度较高，如果要求插入的数值的精确度较高如货币运算等，则考虑使用
*/

//原则：
/*
所选择的类型越简单越好，能保存数值的类型越小越好
*/

//三.字符型
/*
较短的文本：
char
varchar

其他：
binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合

较长的文本：
text
blob(较大的二进制)

特点：
            写法         M的意思                                 特点          空间的耗费  效率
char        char(M)      最大的字符数，可以省略，默认为1     固定长度的字符     比较耗费    高
varchar     varchar(M)   最大的字符数；不可以省略            可变长度的字符     比较节省    低
*/

//四.日期型
/*
分类：
date：只保存日期
time：只保存时间
year：只保存年

datetime：保存日期+时间
timestamp：保存日期+时间

特点：
               字节     范围        时区等的影响
datetime        8       1000-9999      不受
timestamp       4       1970-2038       受
*/

CREATE TABLE tab_date(
           t1 DATETIME
           t2 TIMESTAMP
);

INSERT INTO tab_date VALUES(NOW(),NOW());

SELECT * FROM tab_date;

SHOW VARIABLES LIKE 'time_zone';

SET time_zone='+9:00';

//常见约束
/*
含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确性和可靠性

分类：六大约束
       NOT NULL：非空，用于保证该字段的值不能为空
       DEFAULT：默认，用于保证该字段有默认值
       PRIMARY KEY：主键，用于保证该字段的值具有唯一性，并且非空
       UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空
       CHECK：检查，【mysql中不支持】
       FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自主表的关联列的值
                    在从表添加外键约束，用于引用主表中某列的值

添加约束的时机：
1.创建表时
2.修改表时

约束的添加分类：
列级约束：
         六大约束语法上都支持，但外键约束没有效果

表级约束：
         除了非空、默认，其他的都支持
         

主键和唯一的大对比：(面试题)
        保证唯一性  是否允许为空    一个表中可以有多少个     是否允许组合
主键       √           ×                至多有一个              √，但不推荐
唯一       √           √                可以有多个              √，但不推荐

外键：
1.要求在从表设置外键关系
2.从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3.主表的关联列必须是一个key(一般是主键或唯一)
4.插入数据是，先插入主表，在插入从表
删除数据时，先删除从表，在删除主表。
       
    
*/

//一.创建表时添加约束
//1.添加列级约束
/*
语法：
直接在字段名和类型后面追加约束类型即可。
只支持：默认、非空、主键、唯一
*/

CREATE DATABASE students;
CREATE TABLE stuinfo(
             id INT PRIMARY KEY,#主键
             stuName VARCHAR(20) NOT NULL,#非空
             gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查
             seat INT UNIQUE,#唯一
             age INT DEFAULT 18,#默认约束
             majorId INT REFERENCES major(id) #外键
);

CREATE TABLE major(
              id INT PRIMARY KEY,
              majoyName VARCHAR(20)
);

//2.添加表级约束
/*
语法：在各个字段的最下面
【constaint 约束名】 约束类型(字段名)
*/
DROP TABLE stuinfo;
CREATE TABLE stuinfo(
             id INT ,
             stuName VARCHAR(20) ,
             gender CHAR(1) ,
             seat INT ,
             age INT ,
             majorId INT,
             
             CONSTRAINT pk PRIMARY KEY(id),#主键
             CONSTRAINT uq UNIQUE(seat),#唯一性
             CONSTRAINT ck CHECK(gender='男' OR gender='女'),#检查
             CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id)#外键             
);     

//通用的写法：
CREATE TABLE IF NOT EXISTS stuinfo(
             id INT PRIMARY KEY,
             stuName VARCHAR(20) NOT NULL,
             gender CHAR(1),
             age INT DEFAULT 18,
             seat INT UNIQUE,
             majorid INT,
             CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
); 

//二.修改表时添加约束
/*
1.添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

2.添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
*/
//1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

//2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age DEFAULT 18;   

//3.添加主键
//①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
//②表级约束
ALTER TABLE stinfo ADD PRIMARY KEY(id);

//4.添加唯一
//①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
//②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);

//5.添加外键
ALTER TABLE stunifo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES maor(id); 

//三.修改表时的删除约束
//1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

//2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;       

//3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

//4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;

//5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major ;

//标识列
/*
又称为自增长列
含义：可以不用手动的插入值，系统提供默认的序列值

特点：
1.标识列必须和主键搭配吗？不一定，但要求是一个key
2.一个表可以有几个标识列？至多一个
3.标识列的类型只能是数值型
4.标识列可以通过auto_increment_increment=3：设置步长
        可以通过手动插入值，设置起始值
*/
//一.创建表时设置标识列
CREATE TABLE tab_identify(
            id INT PRIMARY KEY AUTO_INCREMENT,
            NAME VARCHAR(20)
);

INSERT INTO tab_identify(id,NAME) VALUES(NULL,'john');
INSERT INTO tab_identify(NAME) VALUES('john');
SELECT * FROM `tab_identify`; 

TRUNCATE TABLE `tab_identify`;
SHOW VARIABLES LIKE '%auto_increment%';

SET auto_increment_increment=3;#可以手动设置步长
SET auto_increment_offset=3;#可以手动设置起始值
//设置起始值，还可以先插入一个自定义的数据。

//二.修改表时设置标识列
ALTER TABLE tab_identify MODIFY COLUMN id INT  PRIMARY KEY AUTO_INCREMENT;

//三.修改表时删除标识列
ALTER TABLE tab_identify MODIFY COLUMN id INT;

/*
1.存储引擎的概念：在mysql中的数据用各种不同的技术存储在文件(或内存)中。
2.通过show engines：来查看mysql支持的存储引擎。
3.在mysql中用的最多的存储引擎有：innodb，myisam，memory等。其中innodb支持事务，
而myisam、memory等不支持事务。
*/
//TCL
/*
Transaction Control language 事务控制语言

事务：
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

事务的ACID(acid)属性
1.原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
2.一致性(Consistency)
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
3.隔离性(Lsolation)
事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相互干扰。
4.持久性(Durability)
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

事务的创建
隐式事务：事务没有明显的开启和结束的标记
比如insert、update、delete语句

显示事务：事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用
SET autocommit=0;
步骤1：开启事务
SET autocommit=0;
start transaction;可选的
步骤2：编写事务中的sql语句(select、insert、update、delete)
语句1;
语句2;
...
步骤3：结束事务
commit：提交事务 或
rollback：回滚事务
*/
SET autocommit=0;

SHOW VARIABLES LIKE 'autocommit';

//1.delete和truncate在事务使用时的区别
//演示delete：可以回滚
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;

//演示truncate：不能回滚
SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;


/*
mysql中有4中隔离级别：
                  脏读       不可重复读        幻读
read uncommitted:  √             √              √             
read committed:    ×             √              √
repeatable read：  ×             ×              √
serializable：     ×             ×              ×
mysql中默认  第三个隔离级别 repeatable read
oracle中默认 第二个隔离级别 read committed

查看当前的隔离级别：SELECT @@tx_isolation;
设置当前的mysql连接的隔离级别：set transaction isolation level 隔离级别;
设置数据库系统的全局的隔离级别：
set global transaction isolation level 隔离级别;
*/

//2.演示savepoint的使用
SET aytocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;//设置保存点
DELETE FROM account WHERE id+28;
ROLLBACK TO a;//回滚到保存点

//视图 view
/*
含义：虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据

比如：舞蹈班和普通班级的对比

         创建语法的关键字        是否实际占用物理空间    使用
视图     create view             只是保存了sql逻辑       增删改查，一般不使用增删改
表       create table            保存了数据              增删改查
*/

//案例：查询姓张的学生名和专业名
SELECT `studentname`,`majorname`
FROM `student` s
INNER JOIN `major` m
ON s.`majorid`=m.`majorid`
WHERE s.`studentname` LIKE '张%';

//使用视图
CREATE VIEW v1
AS
SELECT `studentname`,`majorname`
FROM `student` s
INNER JOIN `major` m
ON s.`majorid`=m.`majorid`;

SELECT * 
FROM v1
WHERE `studentname` LIKE '张%';

//一.创建视图
/*
语法：
create view 视图名
as
查询语句;
*/

//1.查询姓名中包含a字符的员工名、部门名和工种信息
//①创建
CREATE VIEW myv1
AS
SELECT `last_name`,`department_name`,`job_title`,`email`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id`=d.`department_id`
INNER JOIN `jobs` j
ON j.`job_id`=e.`job_id`;


//②使用
SELECT * 
FROM myv1
WHERE `last_name` LIKE '%a%';

//2.查询各部门的平均工资级别
//创建视图查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT AVG(`salary`) ag,`department_id`
FROM `employees`
GROUP BY `department_id`;

//②使用
SELECT ag,`grade_level`
FROM myv2
INNER JOIN `job_grades` g
ON myv2.`ag` BETWEEN `lowest_sal` AND `highest_sal`;

//二.视图的修改
//方式一:
/*
create or replace view  视图名
as
查询语句;
*/

//方式二：
/*
语法：
alter view 视图名
as 
查询语句;
*/

//三.删除视图
/*
语法：
drop view 视图名,视图名,...;
*/

//四.查看视图结构
DESC myv3;
SHOW CREATE VIEW myv3;

//五.视图的更新
//1.视图的插入
INSERT INTO myv1 VALUES('张飞','zf@qq.com',1000000); 

//2.修改
UPDATE myv1 SET last_name='张无忌' WHERE last_name='张飞';

//3.删除
DELETE FROM myv1 WHERE last_name='张无忌';

//具备以下特点的视图不允许更新：
//①包含以下关键字的sql语句：
/*分组函数、distinct、group by、having、union或者union all
常量视图
select中包含子查询
join
from一个不能更新的视图
where子句子查询引用了from子句的表
*/

//变量
/*
系统变量：
         全局变量
         会话变量
自定义变量：
         用户变量
         局部变量
*/

//一.系统变量
//说明：变量由系统提供，不是用户定义，属于服务器层面
/*
使用是的语法：
1.查看所有的系统变量
查看全局变量：show global variables;
查看会话变量：show 【session】 variables;

2.查看满足条件的部分系统系统变量
show global|【session】 variables like '%char%';

3.查看指定的某个系统变量的值
select @@global|【session】.系统变量名;

4.为某个系统变量复制
方式一：
set global|【session】 系统变量名=值;

方式二：
set @@global|【session】.系统变量名=值;

注意：如果说全局级别，则需要加glocal，如果是会话级别，则需要加session，如果不写，则默认session
*/

//1.全局变量
/*
作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话(连接)有效，但不能跨重启。
*/
//①查看所有的全局变量
SHOW GLOBAL VARIABLES;

//②查看部分的全局变量
SHOW GLOBAL VARIABLES LIKE '%char%';

//②查看指定的全局变量的值
SELECT @@global.autocommit;
SELECT @@global.tx_isolation;

///④为某个指定的全局变量赋值
//方式一：
SET @@global.autocommit=0;

//方式二：
SET GLOBAL autocommit=0;

//2.会话变量
/*
作用域：仅仅针对于当前会话(连接)有效
*/

/①查看所有的会话变量
SHOW VARIABLES;
SHOW SESSION VARIABLES;

//②查看部分的会话变量
SHOW VARIABLES LIKE '%char%';
SHOW SESSION VARIABLES LIKE '%char%';

//③查看指定的会话变量
SELECT @@tx_isolation;
SELECT @@session.tx_isolation;

//④为某个会话变量赋值
//方式一：
SET @@session.tx_isolation='read-uncommitted';

//方式二：
SET SESSION tx_isolation='read-uncommitted';

//二.自定义变量
/*
说明：变量是用户自定义的，不是由系统指定的
使用步骤：
声明
赋值
使用(查看、比较、运算等)
*/

//1.用户变量
/*
作用域：针对于当前会话(连接)有效，同于会话变量的作用域
*/

//赋值的操作符为：=或:=
//①声明并初始化(三种方式)
SET @用户变量名=值;
SET @用户变量名:=值;
SELECT @用户变量名:=值;

//②赋值(更新用户变量的值)
//方式一：
SET @用户变量名=值;
SET @用户变量名:=值;
SELECT @用户变量名:=值;

//方式二：
SELECT 字段 INTO @变量名
FROM 表;

//③使用(查看用户变量的值)
SELECT @用户变量名;

//2.局部变量
/*
作用域：仅仅在定义它的begin end中有效
*/

//①声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;

//②赋值
//方式一：
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT 局部变量名:=值;

//方式二：
SELECT 字段 INTO 局部变量名
FROM 表;

//③使用
SELECT 局部变量名;

//存储过程和函数
/*
存储过程和函数：类似于java中的方法
好处：
1.提高代码的重用性
2.简化操作
*/

//存储过程
/*
含义：一组预先编译好的sql语句的集合，可以理解成批处理语句
1.提高代码的重用性
2.简化操作
3.减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
*/

//一.创建语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN 
      存储过程提(一组合法有效的SQL语句)
END


注意：
1.参数列表包含三部分
参数模式 参数名 参数类型
举例：
IN stuname VARCHAR(20)

参数模式：
IN：该参数可以作为输入，也就是该参数需要调用方传入值
OUT：该参数可以作为输出，也就是该参数可以作为返回值
INOUT：该参数既可以作为输入又可以作为输出，也就是该参数既需要输入值，又可以返回值

2.如果存储过程体仅仅只有一句话，BEGIN END可以省略
3.存储过程体中的每条SQL语句的结尾要求必须加分号。
  存储过程的结尾可以使用DELIMITER重新设置
语法：
DELIMITER 结束标记
DELIMITER $

//二.调用语法
CALL 存储过程名(实参列表)

//1.空参列表
//案例：插入到admin表中五条记录
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
        INSERT INTO admin(username,`password`) 
        VALUES('john1','0000'),('john2','0000'),('john3','0000'),('john4','0000'),('john5','0000');
END $

//调用
CALL myp1()$;

//存储过程和函数
/*
存储过程和函数：类似于java中的方法
好处：
1.提高代码的重用性
2.简化操作
*/

//存储过程
/*
含义：一组预先编译好的sql语句的集合，可以理解成批处理语句
1.提高代码的重用性
2.简化操作
3.减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
*/

//一.创建语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN 
      存储过程提(一组合法有效的SQL语句)
END


注意：
1.参数列表包含三部分
参数模式 参数名 参数类型
举例：
IN stuname VARCHAR(20)

参数模式：
IN：该参数可以作为输入，也就是该参数需要调用方传入值
OUT：该参数可以作为输出，也就是该参数可以作为返回值
INOUT：该参数既可以作为输入又可以作为输出，也就是该参数既需要输入值，又可以返回值

2.如果存储过程体仅仅只有一句话，BEGIN END可以省略
3.存储过程体中的每条SQL语句的结尾要求必须加分号。
  存储过程的结尾可以使用DELIMITER重新设置
语法：
DELIMITER 结束标记
DELIMITER $

//二.调用语法
CALL 存储过程名(实参列表)

//1.空参列表
#案例：插入到admin表中五条记录
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
        INSERT INTO admin(username,`password`) 
        VALUES('john1','0000'),('john2','0000'),('john3','0000'),('john4','0000'),('john5','0000');
END $

//调用
CALL myp1()$;

//函数
/*
含义：一组预先编译好的sql语句的集合，可以理解成批处理语句
1.提高代码的重用性
2.简化操作
3.减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
区别：
存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1个返回，适合做处理数据后返回一个结果
*/
//一.创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
     函数体
END

/*
注意：
参数列表 包含两部分
参数名 参数类型

函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议

retrun 值;
3.函数体中仅有一句话，则可以省略begin end
4.使用delimiter语句设置结束标记
*/

//二.调用语法
SELECT 函数名(参数列表)

//流程控制结构
/*
顺序结构：程序从上往下依次执行
分支结构：程序从两条或多条路径中选择一条去执行
循环结构：程序在满足一定条件的基础上，重复执行一段代码
*/

//一.分支结构
//1.if函数
/*
功能：实现简单的双分支
语法：
if(表达式1,表达式2,表达式3)
执行顺序：
如果表达式1成立，则if函数返回表达式2的值，否则返回表达式3的值

应用：任何地方
*/

//2.case结构
//情况1：类似于java中的switch语句，一般用于实现等值判断

语法：
       CASE 变量|表达式|字段
       WHEN 要判断的值 THEN 返回的值1
       WHEN 要判断的值 THEN 返回的值2
       ...
       ELSE 也要返回的值n
       END

//情况2：类似于java中的多重if语句，一般用于实现区间判断
语法：
       CASE 
       WHEN 要判断的条件1 THEN 返回的值1
       WHEN 要判断的条件2 THEN 返回的值2
       ...
       ELSE 也要返回的值n
       END
      
//3.if结构
/*
功能：实现多重分支

语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
【else 语句n】
end if;

应用场合：应用在begin end中
*/

//二.循环结构
/*
分类：
while、loop、repeat

循环控制：
iterate：类似于 continue，继续，结束本次循环，继续下一次
leave：类似于 break，跳出，结束当前所在的循环
*/

//1.while
/*
语法：
【标签：】 while 循环条件 do
       循环体;
end while 【标签】;
*/

//2.loop
/*
语法：
【标签：】 loop
       循环体;
end loop 【标签】;

可以用来模拟简单的死循环
*/

//3.repeat
/*
语法：
【标签：】 repeat
       循环体;
until 结束循环的条件
end repeat 【标签】;
*/

HHU.项亮亮

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库(基础篇)学习笔记

MySQL数据库学习笔记一.为什么要用数据库？二.MySQL安装与使用尚硅谷网课《MySQL基础教程丨mysql数据库实战（sql数据库优化）》讲师：李玉婷老师链接地址：https://www.bilibili.com/video/BV1xW411u7ax学习时间：2021/07/08一.为什么要用数据库？保存数据的容器有：数组、集合（内存层面，数据易丢失）；文件（不利于数据管理、查询）；数据库。数据库的好处：①实现数据持久化②使用完整的管理系统统一管理，易于查询数据库相关术语：①
复制链接

扫一扫