数据库的学习（一）

• 相关视频链接

数据库及其概念

• DB 数据库
• DBMS 数据库管理系统，如Mysql， MongoDB 等，用来管理与操作DB
• SQL 管理数据库的语言，几乎被所有的DBMS支持
• DB是大宅子，DBMS是宅子管家，SQL是管家的指挥口令

数据库管理的特点

1. 先将数据放到 “一张表” 中， 在将表放到仓库中
2. 一个数据库中存放多张表，每张表都有自己独特的名字，用来标识自己
3. 数据库中的每张表都有自己的类别，有自己的门派，参照python的 “类”
4. 表是由列组成，也称为字段，相当与python中的 “属性”
5. 表是按行储存的，每一行相当与python中的 ”对象“

• MySQL端口号port 3306

Linux 系统 Mysql 相关事项

• 检查电脑是否存在Mysql ：whereis mysql 或者输入 mysql --version

• systemctl start mysql.service 启动 或者 service mysql start
  systemctl stop mysql.service 停止 或者 service mysql stop
  systemctl restart mysql.service 重启

• 检查电脑是否启动或关闭数据库：ps aux | grep mysqld 或者 pidof mysqld
  相关检查操作链接

数据库常用命令

1. 进入管理系统时时查看有那些数据库

show databases;

2. 进入库

use 库的名称;

3. 查看库里有哪些表

show tables;

4. 从现在所在的库中查看别的库中存有的表

show tables from 库的名称;

5. 查看现在在哪个库

select database();

6. 查看表的内容

desc 表的名称;

7. 创建表

create table 表名（
	列名 列类型，
	列名 列类型，
	...
)

SQL语法规范

1. 不区分大小写，但建议关键字大写，表名，列名小写
2. 每行命令u最好用分号结尾
3. 每行命令根据需要，可以进行缩进，或换行
4. SQL语法中索引从1开始
5. 注释

• 单行注释：#注释文字
• 单行注释：-- 注释文字（注意横杠后面有空格）
• 多行注释：/× 注释文字 ×/

使用 DBMS 图形化界面以及命令行登录时出现错误解决

• 原文链接

~$ mysql -u root -p
Enter password: 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

• 解决

# 强制登录sudo mysqlselect user, plugin from mysql.user;update mysql.user set authentication_string=PASSWORD('hero1314'), plugin='mysql_native_password' where user='root';flush privileges;sudo service mysql restart

数据库语言的学习

DQL语言的学习

1、基础查询

• 语法

select 查询列表 from 表名;

• 特点：

1. 查询列表可以是：表中的字段，常量值，表达式，函数
2. 查询结果是一个虚拟的表格，只是将结果用表格呈现出来而已

• 查询字段

select 字段一，字段二 from 表格;    //字段之间用逗号隔开select * from 表格;  // 查询所有字段

• 着重号 ` （1的旁边的那个），用来标识字段

• 查询常量值：select ' 字符或字符串 ’ ;

• 起别名

  ​ 别名有空格时，应用空格将别名括起来

  ​ 注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

select 字段值 as 别名;  // as可以省略

• 去重：在被查询的字段之前加上关键字 distinct
• + 号的作用

select 100+90; // 俩个操作数都为数值型，则做加法运算# 结果：190select '100'+90;  // 俩个数中若有一方为字符串，则将进行转换，若转换成功，则将继续做加法运算，若转换失败，则将字符型数值转换成再在进行加法运算# 结果：190select null+10; //若有一个方为null，则结果肯定为null

• ifnull( ) 函数

select IFNULL(字段，0) as 别名; //若该字段真未NULL，则输出0,否则则输出该字段的值

2、条件查询

• 语法：

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

• 分类：

1. 按表达条件筛选：

   ​ 逻辑运算符：> , <, =, <> , <=, >=

   ​ 注意：<>为不等于的意思

2. 按逻辑表达式筛选：

   ​ &&， ||， ！

   ​ and，or，not // SQL推荐使用

3. 模糊查询

   ​ like, between and, in, is null

   • between and 等价与 >= and <=

   模糊查询通常与通配符一起使用 (in 不支持)

   • 常见通配符：% 匹配任意一个或零个

     ​ _ (下划线) 匹配任意一个

     • 支持对通配符进行转义

select 字段 from 表名 where like '_$_' escape '$'; //使用$符号对下划线进行转义

select × from 表名 where like %a%;

4. 安全等于 <=> : 可以判断显示NULL

3、排序查询

• 语法：

select 字段from 表where 筛选条件order by 排序列表 asc|desc   //不写关键字，默认是asc# asc 从低到高 （升序）# desc 从高到底 （降序）

• 支持别名排序， 运算表达式排序
• 支持对多个列表进行先后排序查询，列表间用逗号隔开
• 排序语句一般是放在查询语句的最后面

常见函数

• 类似python中的方法
• 函数可以嵌套使用
• 分类：

1. 单行函数：concat, length, ifnull等
2. 分组函数：
   • 功能：做统计使用，又称为统计函数，聚合函数，组函数

单行函数

一、字符函数

1. length

select length('str');

2. concat ( ) 拼接函数

concat(str1, str2 ,..., str n); //拼接字符串字段进行查询，字段之间用逗号隔开# 若str中有值为NULL，则查询的所有值为NULL

3. upper( ), lower( ) :改变大小写

4. substr( ) ：截取字符函数

select substr('今天买了个大白菜'， 6);结果: 大白菜select substr('今天买了个大白菜'， 3, 4);结果: 买了

5. instr( ) :返回字串第一个字符的索引，没有则返回0

select instr('今天买了个大白菜'， '大白菜');结果：6

6. trim( ) :去掉字段中的空格或指定字符

select trim('烂的' from '今天买了个烂的大白菜');结果：今天买了个大白菜

7. lpad( ): 左填充指定长度的字符，

   rpad( ): 右填充指定长度的字符

select ipad('大白菜', 6, '*');结果：×××大白菜select rpad('大白菜', 6, '*');结果：大白菜×××

8. replace 替换

select replace('今天买了个大白菜', '大白菜', '胡萝卜');结果：今天买了个胡萝卜

二、数学函数

1. round( ) : 四舍五入函数

select round(1.1236);  //默认保留整数部分结果：1select round(1.1236, 3);  //可指定保留小数部分结果：1.124

2. ceil( ): 向上取整，返回 >= 该函数的最小整数
3. floor( ): 向下取整，返回 <= 该函数的最大整数

select floor(-9.99);结果：-10

4. truncate 截断函数，截断小数点后指定的位数

select truncate(1.123, 2);结果：1.12

5. mod( ): 取模（取余）函数

select mod(10,3);结果：1

三、日期函数

1. now( ) 返回当前系统日期+时间
2. curdate( ) 返回当前系统日期，不返回时间
3. curtime( ) 返回当前时间，不返回日期
4. str_to_date( ) 将日期格式的字符转换成指定格式的日期
5. date_format( ) 将日期转换成字符

• 更多日期函数请上网搜索

四、流程控制函数

1. if( ) :该函数起到的作用类似与python或C语言中的else if( )函数
2. case( )：

case 要判断的字段或表达式    // 选择函数——判断并选择显示一个when 常量1 then 要显示的值1或语句1;  //如果显示的是值，则不显示分号 when 常量2 then 要显示的值2或语句2;...else 默认显示的值或语句end

casewhen 常量1 then 要显示的值1或语句1;  //如果显示的是值，则不显示分号 when 常量2 then 要显示的值2或语句2;...else 默认显示的值或语句end

五、其它函数

• 其他函数太多，用时上网搜

分组函数

• 功能：用作统计使用，又称为聚合函数或统计函数或组函数
• 分类：sum 求和， avg 平均值， max， min， count 计算个数
• 忽略 NULL值
• 可以与distinct( )去重函数或其它函数搭配使用
• count( )详细介绍

select count(*) from 表单;  //统计总行数

• 和分组函数一同查询的字段要求是group by后的字段

4、分组查询

1. 语法：

select 分组函数，列（要求出现在group by的后面）from 表[where 筛选条件]group by 分组的列表;[order by 子句]// 查询列表必须特殊，要求是分组函数和group by 后出现的字段

• 简单的分组查询

1. 实例一：查询每个工种的最高工资

select max(salary), job_idfrom employeesgroup by job_id;

3. 实例二：查询每个位置上的部门个数

select count(*), location_idfrom departmentsgroup by lacation_id;

• 添加筛选条件的分组查询

4. 实例三：查询邮箱中包含a字符的，每个部门的平均工资

select avg(salsry), department_idfrom employeeswhere email '%a%'group by department_id;

5. 实例四：查询有奖金的每个领导手下员工的最高工资

select max(salary), manager_idfrom employeeswhere commission_pct is ont nullgroup by manager_id;

• 添加复杂筛选条件的分组查询
• 分组后进行条件筛选的查询

6. 实例五：查询哪个部门的员工个数>2

select count(*), depatment_idfrom employeesgroup by department_idhaving count(*)>2;   //用查询的结果再进行筛选时用having

5、连接查询【 优先学习99语法 】

1. 含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

2. 笛卡尔乘积现象：表1有m行，表2有n行，结果有m×n行

   1. 发生原因：没有有效的链接条件
   2. 如何避免：添加有效的连接条件

3. 按功能分类：

   • 内连接：等值连接

     ​ 非等值连接

     ​ 自连接

   • 外连接：左外连接

     ​ 右外连接

     ​ 全外连接

   • 交叉连接

SQL92语法

(1) 等值查询

• 查询员工名和对应的部门名

select last_name, department_namefrom employees, departmentswhere employees.'department_id=departments.'department_id';

• 多条件筛选连接查询
  • 查询有奖金的员工名，部门名

select last_name, department_name, commission_pctfrom emplloees e, departments dwhere e.'department_id'=d.'department_id'and e.'commission_pct' is not null;

(2) 非等值链接

• 在等值链接的基础上将条件改为非等值就行

(3) 自链接

• 自己链接自己

• 实例:查询查询一张表中员工名和上级的名称

select e.employee_id, e.last_name, m.employee_id, m.last_namefrom employees e, employees mwhere e.'manager_id'=m.'employee_id';

SQL99语法

• 语法： select 查询列表

  ​ from 表1 别名

       [连接类型]  join 表2 别名
  

  ​ on 连接条件

  ​ where 筛选条件

  ​ group by 分组

  ​ having 筛选条件

  ​ order by 排序条件

内链接

• 语法：

select 查询列表from 表1 别名inner join 表2 别名on 连接条件；

• 分类：

内链接：inner外链接	左外：left【outer】  //括号中outer可以省去	右外：right 【outer】	全外：full 【outer交叉连接：cross

1. 等值查询（内连接）

案例1 查询员工名、部门名select last_name, department_namefrom employees einner join departments don e.'department_id' = d.'department_id';

2. 非等值连接(内链接)

案例1：查询员工的工资级别select salary, grdae_levelfrom employees ejoin job_grades gon e.'salary' between g.'lowest_sal' and 'highest_sal';

3. 自连接（内连接）

查询员工的名字，上级的名字,包含字符k的select e.last_name, m.last_namejoin employees mon e.'manager_id' = m.'employee_id'where e.'last_name' like '%k%';

外连接

• 应用场景，一个表格有，一个表格没有
• 特点：

1. 外连接的查询结果为主表中的所有记录

   如果从表中有和他匹配的，则显示匹配值

   如果从表中没有和它匹配的值，则显示null

   外连接查询结果=内链接结果+主表中有而从表中没有的记录

2. 左外连接，left join左边的是主表

   右外连接，right join右边的是主表

   左外右外和俩个表都换一下顺序，可以实现同样的效果

• 主表：查询内容较多的表格

• 全外连接：全外连接=内连接的结果+表1中有但表2中没有的+表2中有但表1中没有的

交叉连接

• 相当于用99语法实现的笛卡尔乘积

图示：

6、子查询

1. 含义：出现在其他语句中的select语句，称为子查询或内查询
2. 分类：

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

• （一）where或having后面

1. 标量子查询（单行子查询）

2. 列子查询（多行子查询）

3. 行子查询（多行多列）

4. 特点：（1）子查询放在小括号内

   ​ （2）子查询一般放在条件的右侧

   ​ （3）标量子查询，一般搭配着单行操作符使用< > = >= <=等

   ​ 列子查询，一般搭配多行操作符使用 in, any,some, all

   ​ (4)子查询的执行优先于主查询，因为主查询要用到子查询

5. 标量子查询

案例：谁的工资比sam高？#查询sam的工资select salaryfrom employeeswhere last_name = 'sam'；#查询员工的信息，满足salary > sam工资 的结果select *from employeeswhere salary >(    select salary    from employees      //为更清晰的表达，子查询最好进行缩进    where last_name = 'sam'  )；

案例：返回公司工资最少的员工的last_name,job_id,salary#查询公司的最低工资select min(salary)from empolyees;#查询last_name, job_id, salary, 要求salary= 公司的最低工资select last_name, job_id, salaryfrom employeeswhere salary=(    select min(salary)    from empolyees);

2. 列子查询(多行子查询）

案例：返回location_id是1400或1700的部门中的所有员工姓名#查询location_id是1700或1400的部门编号select department_idfrom departmentswhere location_id in(1400, 1700);#查询员工姓名，要求部门号是上个查询结果中的某一个select employeeswhere department_id in(    select department_id    from departments    where location_id in(1400, 1700));

3. 行子查询（多行多列）

   略

• （二）放在select后面的标量子查询

案例：查询每个部门的员工个数select d.*, (	select count(*)	from empolyees e	where e.department_id = d.'department_id') 个数from departments d;

• （三）放在from后面
  • 将子查询结果当成一张表，要求必须起别名

案例：查询每个部门的平均工资的平均等级#查询每个部门的平均工资select avg(salary), department_idfrom employeesgroup by department_id#连接上个查询结果集和job_grades表，筛选条件平均工资select ag_dep.*, g.'grade_level'from(	select avg(salary), department_id	from employees	group by department_id) ag_depinner join job_grades gon ag_dep.ag between lowest_sal and highest_sal;

• （四）放在exists后面（相关子查询）
  • 能用exists的都能用in代替

语法：	exists 完整的查询语句结果：0或1   //返回一个布尔值

案例：查询员工的部门名select department_namefrom departments dwhere exists(	select *	from employees e	where d.'department_id'=e.'depaertment_id');

7、分页查询

1. 应用场景：当要显示的数据，一页显示不全，需要枫叶提交sql请求
2. 语法：

select 查询列表from 表连接类型 join 表2on 连接条件where 筛选条件group by 分组字段having 分组后的筛选order by 排序的字段limit offset, size;offset要显示条目的起始索引（起始索引从0开始）size 要显示的条目个数

3. 案例：查询前五条员工信息

select * from employees limit 0, 5;

4. 案例2：查询11条—第25条

select * from employees limit 10, 15;

5. 案例3：有奖金的员工信息，并且工资较高的前10名显示出来

select *from employeeswhere commission_pic is not nullorder by salsry desclimit 10;

6. 特点：

• limit语句放在查询语句德尔最后
• 公式：要显示的页数page，每页的条目数size

select 查询列表from 表limit (page-1)*size, size;

8、联合查询

1. union联合 ：将多个查询语句的结果合并成一个结果
2. 语法：

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

3. 案例：查询部门编号>90或邮箱包含a的员工信息

select *from employeeswhere email like '%a%' or deaprtment_id>90;select *from employeeswhere email like '%a%'unionselect *from employeeswhere deparment_id>90;

4. 特点：

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