MySQL数据库

参考资料

• B站宋红康mysql数据库教程
• B站宋红康mysql课程资料,包含sql文件 提取码：m5hm
• B站宋红康mysql数据库全部md笔记 提取码：02vt

数据库概述

• 持久化persistence，把数据保存到可掉电是存储设备之中，主要作用是将内存中的数据存储在关系型的数据库中，数据持久化

数据库概念

• DB 数据库
• DBMS 数据库管理系统
• SQL 数据库语言
• 关系型数据库和非关系型数据库

关系型数据库

• E-R模型
• ORM思想 （Object Relational Mapping）
  • 表，类似于程序语言中类的设计
  • 表中的一行数据，和类中的对象相对应
  • 表中的一列，类中的一个字段

表的关联关系

一对一

• 个人信息

一对多

• 客户表和订单表，分类表和商品表
• 主表、从表

多对多

• 选课和学生
• 产品和订单

MySQL介绍

安装

• mySql版本介绍
• 官网 www.mysql.com
• 端口号设置 0 ~65535

基本指令

net start mysql80 #启动服务

mysql --version #查看版本
mysql -uroot -p #登录
mysql -u root -P3306 -p #指定端口登录
mysql -u root -P3306 -hlocalhost -p
show databases;
select version();
use name;
show tables;
exit;
quit;

更改加密规则

新版mysql使用sha算法

解决办法：

• 升级图形化界面工具版本

• 将MySql8用户登录密码加密规则还原成mysql_native_password

  #使用mysql数据库
  USE mysql;
  #修改‘root‘@’localhost’用户的密码规则和密码
  ALTER USER ‘root‘@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘abc123’;
  #刷新权限
  FLUSH PRIVILEGES;

初始数据库

• infomation_schema 存储数据库信息
• mysql 存储系统信息，字符文件夹
• perfomance_schema 监控数据库的各项指标
• sys 监控mysql性能的

create database 数据库名;
show create database dbtest1; #查看属性

MySQL编码设置

在5.7版本下需要更改

步骤1：查看编码命令

show variables like ‘character_%’;
show variables like ‘chollation_%’;

步骤2：修改mysql的数据目录下的my.ini配置文件,注意不用记事本打开

#63行下
default-character-set=utf8
#76行下
character-set-server=utf8
collation-server=utf8_general_ci

步骤3：重启mysql服务

步骤4：查看编码

可视化工具

• workbeach
• navicat https://cloud.tencent.com/developer/article/1804255
• sqlyog
• dbeaver （java）

SQL背景

• 1946 年，世界上第一台电脑诞生，如今，借由这台电脑发展起来的互联网已经自成江湖。在这几十年里，无数的技术、产业在这片江湖里沉浮，有的方兴未艾，有的已经几幕兴衰。但在这片浩荡的波动里，有一门技术从未消失，甚至“老当益壮”，那就是 SQL。

  • 45 年前，也就是 1974 年，IBM 研究员发布了一篇揭开数据库技术的论文《SEQUEL：一门结构化的英语查询语言》，直到今天这门结构化的查询语言并没有太大的变化，相比于其他语言，SQL 的半衰期可以说是非常长了。

• 不论是前端工程师，还是后端算法工程师，都一定会和数据打交道，都需要了解如何又快又准确地提取自己想要的数据。更别提数据分析师了，他们的工作就是和数据打交道，整理不同的报告，以便指导业务决策。

• SQL（Structured Query Language，结构化查询语言）是使用关系模型的数据库应用语言，与数据直接打交道，由IBM上世纪70年代开发出来。后由美国国家标准局（ANSI）开始着手制定SQL标准，先后有SQL-86，SQL-89，SQL-92，SQL-99等标准。

  • SQL 有两个重要的标准，分别是 SQL92 和 SQL99，它们分别代表了 92 年和 99 年颁布的 SQL 标准，我们今天使用的 SQL 语言依然遵循这些标准。

• 不同的数据库生产厂商都支持SQL语句，但都有特有内容。

SQL分类

• DDL（Data Definition Languages、数据定义语言），这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。

  • 主要的语句关键字包括CREATE、DROP、ALTER等。

• DML（Data Manipulation Language、数据操作语言），用于添加、删除、更新和查询数据库记录，并检查数据完整性。

  • 主要的语句关键字包括INSERT、DELETE、UPDATE、SELECT等。
  • SELECT是SQL语言的基础，最为重要。

• DCL（Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。

  • 主要的语句关键字包括GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT等。

因为查询语句使用的非常的频繁，所以很多人把查询语句单拎出来一类：DQL（数据查询语言）。

还有单独将COMMIT、ROLLBACK 取出来称为TCL （Transaction Control Language，事务控制语言）。

SQL语言的规则与规范

• 每行语句以分号结束

• 建议分行写

• 列的别名，尽量使用(“ “)

• linux区分大小写，windows大小写不敏感

• 规范：

  • 数据库名、表名、表别名、字段名、字段别名等都小写
  • SQL关键字、函数名、绑定变量等都大写

• 注释

  单行注释：#注释文字(MySQL特有的方式)
  单行注释：-- 注释文字(–后面必须包含一个空格。)
  多行注释：/* 注释文字 */

数据导入

atguigudb.sql 文件下载

链接：https://pan.baidu.com/s/1A2z7ZJnhBPxkynz7Z0YtWw?pwd=us1h
提取码：us1h

• 命令行导入

  #用命令行执行
  source .sql文件全路径

  #查看表
  show tables;

  #查看表内容
  select * from employees;

• sqlyog导入，执行sql脚本

基本SElECT语句

#含义
SELECT 标识选择哪些列
FROM 标识从哪个表中选择

#SELECT 字段1，字段2，… FROM 表名
SELECT 1; #没有任何子句
SELECT 9/2; #没有任何子句

#一般生产环境不建议用通配符查询
SELECT *
FROM departments;

#选定特定列
SELECT department_id, location_id
FROM departments;

#给列起别名，方便查询
SELECT employee_id emp_id,last_name AS lname, department_id “dept_id”
FROM employees;

#去除重复行
SELECT DISTINCT department_id
FROM employees;

#这样的去重会去掉只有全部字段重复的字段
SELECT DISTINCT department_id,salary
FROM employees;

#空值运算，null不等同于0，空值参与运算结果也为空
SELECT employee_id,salary,commission_pct,
12 * salary * (1 + commission_pct) “annual_sal”
FROM employees;
#修改为，ifnull为函数
SELECT employee_id,salary,commission_pct,
12 * salary * (1 + IFNULL(commission_pct,0) “annual_sal”
FROM employees;

#着重号，ORDER为系统关键字，用着重号区别
SELECT * FROM `ORDER`;

#查询常数，每一行都匹配
SELECT ‘尚硅谷’ as corporation, last_name FROM employees;

#显示表结构
DESCRIBE employees;
或
DESC employees;

/*过滤条件,其中过滤条件也是区分大小写，且from必须在where前
SELECT 字段1,字段2
FROM 表名
WHERE 过滤条件*/

SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90 ;

练习：

#查询员工12个月的工资总和，并起别名为ANNUAL SALARY
SELECT employee_id, last_name, salary * 12 “ANNUAL SALARY”
FROM employees;

SELECT employee_id, last_name, salary * 12 * (1 + IFNULL(commission_pct,0)) “ANNUAL SALARY”
FROM employees;

#查询employees表中去除重复的job_id以后的数据
SELECT DISTINCT job_id
FROM employees;

#查询工资大于12000的员工姓名和工资
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > 12000;

#显示表的结构，并查询全部数据
DESCRIBE departments;
SELECT * FROM departments;

运算符

#算数运算符
SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 -30, 100 + 35.5, 100 - 35.5 FROM dual;
+-—-+———+———+———-+————–+————+————+
| 100 | 100 + 0 | 100 - 0 | 100 + 50 | 100 + 50 -30 | 100 + 35.5 | 100 - 35.5 |
+-—-+———+———+———-+————–+————+————+
| 100 | 100 | 100 | 150 | 120 | 135.5 | 64.5 |
+-—-+———+———+———-+————–+————+————+

SELECT 100, 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2,100 + 2 * 5 / 2,100 /3, 100 DIV 0 FROM dual;
+-—-+———+———–+———–+———+—————–+———+———–+
| 100 | 100 * 1 | 100 * 1.0 | 100 / 1.0 | 100 / 2 | 100 + 2 * 5 / 2 | 100 /3 | 100 DIV 0 |
+-—-+———+———–+———–+———+—————–+———+———–+
| 100 | 100 | 100.0 | 100.0000 | 50.0000 | 105.0000 | 33.3333 | NULL |
+-—-+———+———–+———–+———+—————–+———+———–+

#对于 100 + ‘1’，mysql会将字符串隐式转换为数值1，但是字母转换为0
SELECT 100 + ‘1’
FROM DUAL;

#取模运算