MySQL数据库

参考资料

数据库概述

持久化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;

#取模运算