数据库（一）

2023-01-07 14:53:52

数据库的学习（一）

数据库及其概念

  • 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;
  1. 进入库
use 库的名称;
  1. 查看库里有哪些表
show tables;
  1. 从现在所在的库中查看别的库中存有的表
show tables from 库的名称;
  1. 查看现在在哪个库
select database();
  1. 查看表的内容
desc 表的名称;
  1. 创建表
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%;
  1. 安全等于 <=> : 可以判断显示NULL

3、排序查询

  • 语法：
select 字段from 表where 筛选条件order by 排序列表 asc|desc   //不写关键字，默认是asc# asc 从低到高 （升序）# desc 从高到底 （降序）
  • 支持别名排序， 运算表达式排序
  • 支持对多个列表进行先后排序查询，列表间用逗号隔开
  • 排序语句一般是放在查询语句的最后面

常见函数

  • 类似python中的方法
  • 函数可以嵌套使用
  • 分类：
  1. 单行函数：concat, length, ifnull等
  2. 分组函数：
    • 功能：做统计使用，又称为统计函数，聚合函数，组函数
单行函数
一、字符函数
  1. length
select length('str');
  1. concat ( ) 拼接函数
concat(str1, str2 ,..., str n); //拼接字符串字段进行查询，字段之间用逗号隔开# 若str中有值为NULL，则查询的所有值为NULL

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

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

select substr('今天买了个大白菜'， 6);结果: 大白菜select substr('今天买了个大白菜'， 3, 4);结果: 买了
  1. instr( ) :返回字串第一个字符的索引，没有则返回0
select instr('今天买了个大白菜'， '大白菜');结果：6
  1. trim( ) :去掉字段中的空格或指定字符
select trim('烂的' from '今天买了个烂的大白菜');结果：今天买了个大白菜

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

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

select ipad('大白菜', 6, '*');结果：×××大白菜select rpad('大白菜', 6, '*');结果：大白菜×××
  1. replace 替换
select replace('今天买了个大白菜', '大白菜', '胡萝卜');结果：今天买了个胡萝卜
二、数学函数
  1. round( ) : 四舍五入函数
select round(1.1236);  //默认保留整数部分结果：1select round(1.1236, 3);  //可指定保留小数部分结果：1.124
  1. ceil( ): 向上取整，返回 >= 该函数的最小整数
  2. floor( ): 向下取整，返回 <= 该函数的最大整数
select floor(-9.99);结果：-10
  1. truncate 截断函数，截断小数点后指定的位数
select truncate(1.123, 2);结果：1.12
  1. 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;
  1. 实例二：查询每个位置上的部门个数
select count(*), location_idfrom departmentsgroup by lacation_id;
  • 添加筛选条件的分组查询
  1. 实例三：查询邮箱中包含a字符的，每个部门的平均工资
select avg(salsry), department_idfrom employeeswhere email '%a%'group by department_id;
  1. 实例四：查询有奖金的每个领导手下员工的最高工资
select max(salary), manager_idfrom employeeswhere commission_pct is ont nullgroup by manager_id;
  • 添加复杂筛选条件的分组查询
  • 分组后进行条件筛选的查询
  1. 实例五：查询哪个部门的员工个数>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';
  1. 非等值连接(内链接)
案例1：查询员工的工资级别select salary, grdae_levelfrom employees ejoin job_grades gon e.'salary' between g.'lowest_sal' and 'highest_sal';
  1. 自连接（内连接）
查询员工的名字，上级的名字,包含字符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);
  1. 列子查询(多行子查询）
案例：返回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));

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

    略

  • （二）放在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 要显示的条目个数
  1. 案例：查询前五条员工信息
select * from employees limit 0, 5;
  1. 案例2：查询11条—第25条
select * from employees limit 10, 15;
  1. 案例3：有奖金的员工信息，并且工资较高的前10名显示出来
select *from employeeswhere commission_pic is not nullorder by salsry desclimit 10;
  1. 特点：
  • limit语句放在查询语句德尔最后
  • 公式：要显示的页数page，每页的条目数size
select 查询列表from 表limit (page-1)*size, size;

8、联合查询

  1. union联合 ：将多个查询语句的结果合并成一个结果
  2. 语法：
查询语句1union查询语句2union...
  1. 案例：查询部门编号>90或邮箱包含a的员工信息
select *from employeeswhere email like '%a%' or deaprtment_id>90;select *from employeeswhere email like '%a%'unionselect *from employeeswhere deparment_id>90;
  1. 特点：
  • 要求多条查询语句的查询列数是一致的
  • 要求多条查询语句的查询的每一列的类型和顺序最好一致
  • union关键字默认去重，如果使用union all 可以包含重复选项
上一篇：练手一个SpringBoot项目


下一篇：day04 mysql单表查询 多表查询 pymysql的使用