数据库及SQL/MySQL基础-优快云博客

本文链接：https://blog.youkuaiyun.com/sarahperfectee/article/details/145160489

https://edu.aliyun.com/my/learningPath?spm=5176.10731460.0.0.5cc63d4bchUF8O

1 数据库概念

1.1 数据库的发展历程

没有数据库，使用磁盘文件存储数据；
层次结构模型数据库
网状结构模型数据库
关系结构模型数据库：使用二维表格来存储数据
关系-对象模型数据库

1.2 常见数据库

Oracle（市场占有率最高）：甲骨文
DB2：IBM
SQL Server：微软
Sybase：塞尔斯
MySQL:甲骨文
后来收费，现在用的5.5还免费

1.3 理解数据库

RDBMS: 关系型数据库管理系统
Relational DataBase Management System
我们现在所说的数据库泛指“关系型数据库管理系统”，即“数据库服务器”。
在这里插入图片描述
RDBMS = N多个仓库 + 管理员
我们要学习的这个mySQL就是这个RDBMS。
那以后你要从仓库中拿东西，可不可以直接打开门就拿，肯定不可以，必须要通过管理员，所以之后我们会经常和这个仓库管理员打交道。

当我们安装了数据库服务器后，就可以在数据库服务器中创建数据库，每个数据库中还可以包含多张表。
在这里插入图片描述
数据库表就是一个多行多列的表格。在创建表时，需要指定表的列数，以及列名称，列类型等信息。而不用指定表格的行数，行数时没有上限的。下面是tab_student表的结构：

列名s_id存储的是什么数据-字符串，字符串多长呢，最长10个字符
s_name又是一列…
表结构是对表的列名和列类型进行了定义

当把表格创建好了之后，就可以向表格中添加数据可。下面是s_student表的记录：
在这里插入图片描述
大家要学会区分什么是表结构，什么是表记录
那现有表结构，再有表记录

总结：

RDBMS = 管理员（manager） + 仓库（database）
database = N个table
table：
– 表结构：定义表的列名和列类型
– 表记录：一行一行的记录

1.5 应用程序与数据库

应用程序使用数据库完成对数据的存储
在这里插入图片描述
比如你是开小网店的，一开始数据都存在家里，后面买卖越做越大，比京东干的还大，数据都堆家里还合适嘛，楼下不乐意，掉人家楼下去了，那咱存到仓库里，那仓库准和家里挨着吗，可以离很远。这是什么意思，就是你买的数据库服务器和你的代码服务器不在一个电脑上，但今天我们学习时代码和数据库服务器在一个电脑上。
存在一个问题，别人访问你的应用程序，你的应用程序访问数据库服务器，这时他俩不在一个电脑上，这个时候就存在网络的传输。
你发送一个请求，数据库给你相应的响应
世面上90%都要用数据库，没有不用数据库的，所以数据库很重要

2 MySQL安装及配置

2.1 下载安装数据库

进入MySQL官方网站（https://www.mysql.com/downloads/），按下图顺序点击进入下载页面。
Downloads->MySQL Community Downloads->MySQL Installer for Windows
一路安装，设置密码

2.2 校验MySQL

验证是否安装成功：
计算机管理-》服务

在这里插入图片描述

2.3 登录MySQL

找到mysql的安装目录： D:\software\software-install\mySQL_8.0.4\bin
右键选择“在终端中打开”

./mysql -uroot -p

输入密码，即可登录

2.4 退出MySQL

exit

Debugging:
3306端口被占用
cmd-netstat -ano 查看是否有上3306被占用，去任务管理器中找到mysqld.exe，右击鼠标选择结束任务，那么该端口就被释放了
在这里插入图片描述

2.5 删除mySQL

https://blog.youkuaiyun.com/xtho62/article/details/114005976

停止服务
管理员打开cmd

net stop MYSQL80
MySQL80 服务正在停止.
MySQL80 服务已成功停止。

卸载mysql程序
找到mysql的安装目录删除

2.6 安装文件中几个重要配置信息

mySQL安装成功后会在两个目录中存储文件：

D:\software\software-install\mySQL_8.0.4： DBMS管理程序
C:\ProgramData\MySQL\MySQL Server 8.0\Data：DBMS数据库文件（卸载mysql时不会删除这个目录，需要自己手动删除），隐藏文件夹，需要将“显示-》隐藏文件夹”打开才能看到

MySQL重要文件

D:\software\software-install\mySQL_8.0.4\bin\mysql.exe:客户端程序，用来操作服务器。但必须保证服务器已开启才能连接上；
D:\software\software-install\mySQL_8.0.4\bin\mysqld.exe: 服务器端程序，必须先启动它，客户端才能连接上服务器。
D:\software\software-install\mySQL_8.0.4\my.ini: 服务器配置文件

C:\ProgramData\MySQL\MySQL Server 8.0\Data

该目录下的每个目录表示一个数据库，例如该目录下有一个mysql目录，说明你的DBMS中有一个名为mysql的database
在某个数据库目录下会有0~N个扩展名为frm的文件，，每个frm文件表示一个table。你不要用文本编辑器打开它，它是由DBMS来读写的！

D:\software\software-install\mySQL_8.0.4\my.in

配置mySQL的端口：默认为3306，没有必要去修改它；
配置字符编码：
[client]下配置客户端编码：default-character-set=gbk
[mysqld]下配置服务器编码：character-set-server=utf8
配置二进制数据大小上限：
在[mysqld]下配置：max_allowed_packet=8M

3 开启关闭服务器以及登录退出客户端

3.1 服务器操作

开启服务器（必须保证mysql为windows服务）： net start MYSQL80
查看进程表中是否存在:mysqld.exe(存在)
关闭服务器（必须保证mysql为windows服务）：net stop MYSQL80
查看进程表中是否存在:mysqld.exe(不存在)

Debugging:

查看进程表中mysqld.exe没有存在
管理员打开cmd：

再次连接，ok

3.2 客户端操作

cmd

登录服务器：mysql -uroot -p1234 -hlocalhost

-u: 后面跟随用户名
-p：后面跟随密码
-h: 后面跟随IP

退出服务器：exit或quit

4 SQL语言的概述

4.1 SQL

什么是SQL：结构化查询语言（structured Query Language）
SQL和mySQL有关没关？是两回事
SQL的作用：客户端使用SQL来操作服务器
启动mysql.exe, 连接服务器后，就可以使用sql来操作服务器了
将来会使用Java程序连接服务器，然后使用sql来操作服务器
SQL标准
由国际标准化组织（ISO）制定的，对DBMS的统一操作方式（例如相同的语句可以操作：mysql，oracle等）
SQL方言
某种DBMS不只支持SQL标准，而且还会有一些自己独有的语法，这就称之为方言！例如limit语句只在MySQL中可以使用

4.2 SQL语法

SQL语句可以在单行或多行书写，以分号结尾
可使用空格和缩进来增强语句的可读性
MySQL不区分大小写，建议使用大写（关键字）

4.3 SQL语句分类

DDL: 对数据库或表的结构操作（*****）
DML：对表的记录进行更新（增、删、改）（******）
DCL：对用户的创建，及授权（*****，难点）
DQL：对表的记录进行查询

5 DDL（数据定义语言）之操作数据库

数据定义语言（Data Definition Language，简称DDL）：DDL用于定义数据库的结构，包括创建、修改和删除数据库、表、索引、视图等元素。常用的DDL语句包括CREATE、ALTER和DROP。

5.1 数据库操作

查看所有数据库

SHOW DATABASES;

切换数据库

USE 数据库名;

创建数据库

CREATE DATABSES 数据库名 [CHARSET=utf8];

删除数据库

DROP 数据库名;

修改数据库编码

ALTER DATABSES mydb1 CHARACTER SET utf8;

5.2 数据类型(列类型)

数据类型是用来定义数据库中存储数据的格式和属性的规范。不同的数据类型决定了数据在数据库中的存储方式、占用空间以及可以进行的操作。在MySQL中，常见的数据类型包括数值型、字符串型、二进制型、日期型和其他类型。
在这里插入图片描述
数值型
MySQL支持多种数值型数据类型，包括整数型、小数型和布尔型。
整数型
TINYINT：1字节，取值范围为-128到127或0到255。
SMALLINT：2字节，取值范围为-32768到32767或0到65535。
MEDIUMINT：3字节，取值范围为-8388608到8388607或0到16777215。
INT：4字节，取值范围为-2147483648到2147483647或0到4294967295。
BIGINT：8字节，取值范围为-9223372036854775808到9223372036854775807或0到18446744073709551615。
小数型
FLOAT：4字节，单精度浮点数，精度为7位。
DOUBLE：8字节，双精度浮点数，精度为15位。
DECIMAL：可变长度，用于存储精确的小数，精度和数据长度可以自定义。
布尔型
MySQL支持布尔型数据类型，即BOOLEAN或BOOL。它只占用1位，取值为TRUE或FALSE。
日期型
DATE：用于存储日期，格式为YYYY-MM-DD。
TIME：用于存储时间，格式为HH:MM:SS。
DATETIME：用于存储日期时间，格式为YYYY-MM-DD HH:MM:SS。
TIMESTAMP：用于存储时间戳，格式为YYYY-MM-DD HH:MM:SS。
YEAR：用于存储年份，格式为YYYY。
字符串型
CHAR：用于存储固定长度的字符串，长度为0到255个字符。
VARCHAR：用于存储可变长度的字符串，长度为0到65535个字符。
TEXT：用于存储大量的文本数据，最大长度为65535个字符。
BLOB：用于存储大量的二进制数据，最大长度为65535个字节。
ENUM：用于存储枚举类型的数据，如性别、状态等。
二进制型
BIT：用于存储比特位，长度为1到64个比特位。
BINARY：用于存储固定长度的二进制数据，长度为0到255个字节。
VARBINARY：用于存储可变长度的二进制数据，长度为0到65535个字节。
其他类型
SET：用于存储一组可选值，最多可以有64个选项。
JSON：用于存储JSON格式的数据。
Geometry：用于存储几何类型的数据，如点、线、面等。
MySQL支持多种数据类型，可以根据不同的需求选择合适的数据类型。掌握MySQL的数据类型，对于开发人员来说非常重要，可以提高开发效率，减少错误。

int: 整型
double: 浮点型，例如double(5,2)表示最长5位，其中必须有2位小数，即最大值为999.99
decima: 浮点型，在表单钱方面使用该类型，因为不会出现精度缺失问题。
char: 固定长度字符串类型： char(255)，数据的长度不足指定长度，补足到指定长度；最长255。
varchar: 可变长度字符串类型，最长varchar(65535), 不足65535不用补0，但是需要拿出单独字节去存放长度
text(clob): mysql的方言用法，标准中是clob
blob: 二进制,字节类型
date：日期类型，格式为yyyy-MM-dd;
time：时间类型，格式为hh:mm:ss
timestamp: 时间戳类型，有年月日，也有时分秒

5.3 表操作

创建表

CREATE TABLE [IF NOT EXISTS] 表名(
列名 列类型,
列名 列类型,
...
列名 列类型
);

CREATE TABLE stu_1(
number char(11),
name varchar(50),
age int;
gender varchar(10)
);

查看当前数据库中所有表名称

SHOW TABLES;

查看指定表的创建语句（仅了解）

SHOW CREATE TABLE 表名;

查看表结构

DESC 表名;

删除表

DROP TABLE 表名;

修改表
1）修改之添加列

ALTER TABLE 表名 ADD (
列名 列类型,
列名 列类型,
...
);

ALTER TABLE stu_1
add(
education varchar(50)
);

2）修改之修改列类型

ALTER TABLE 表名 MODIFY 列名 列类型;

ALTER TABLE stu_1
MODIFY education varchar(100)
;

3）修改之修改列名

ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;

4）修改之删除列

ALTER TABLE 表名 DROP 列名;

ALTER TABLE stu_1
drop education;

5）修改表名称

ALTER TABLE 原表名 RENAME TO 新表名;

ALTER TABLE stu_1
RENAME TO student_1;

6 DML（数据操作语言）

数据操作语言（Data Manipulation Language，简称DML）：DML用于对数据库中的数据进行操作，包括插入、更新和删除数据。常用的DML语句包括SELECT、INSERT、UPDATE和DELETE。

6.1 DML之INSERT

INSERT INTO 表名 (列名1，列名2，...) VALUES (列值1，列值2，...);

在表名之后要给出插入的列名，其它没有指定的列等同插入null值，所以插入记录总是插入一行，不可能是半行
在VALUES后给出列值，值的顺序和个数必须与前面指定的列对应

INSERT INTO VALUES(列值1, 列值2,...);

没有给出要插入的列，那么表示插入所有列
值得个数必须是该表列的个数
值的顺序，必须与表创建时给出的列的顺序相同
举例：

//插入所有列
INSERT INTO stu1(
number, name, age, gender
) VALUES(
'ITCAST_0001','zhangSan',28,'male'
);

//插入部分列，没有指定的列默认为NULL值
INSERT INTO stu1(
number, name
) VALUES(
'ITCAST_0002','liSi'
);

//不给插入列，那么默认插入所有列
INSERT INTO stu1(
VALUES(
'ITCAST_0003','wangwu',82,'female'
);

6.2 DML之修改记录

修改表数据

UPDATE 表名 SET 列名=列值1，列名2=列值2，... [WHERE 条件];

条件（条件可选的）：

条件必须时一个boolean类型的值或表达式：
运算符： =、!=、<>、<、>、>=、<=、BETWEEN…AND、IN(…)、IS NULL、NOT、OR、AND

UPDATE stu1 SET age=18 WHERE name='liSi';

UPDATE stu1 SET age=18 WHERE name IN('zhangSan', 'liSi');

UPDATE stu1 SET age=age+1 WHERE age BETWEEN 18 AND 48;

UPDATE stu1 SET age=age+1 WHERE NOT age BETWEEN 18 AND 48;

注意：列名=null，永远是false
在这里插入图片描述

6.3 DML之DELETE

DELETE FROM 表名 [WHERE 条件];

TRUNCATE TABLE 表名: TRUNCATE是DDL语句，它是先删除drop该表，再create该表。而且无法回滚！！

7 DCL（数据控制语言）

数据控制语言（Data Control Language，简称DCL）：DCL用于控制数据库中数据的访问权限和数据完整性的约束，包括授权和回收权限、创建用户和角色等。常用的DCL语句包括GRANT和REVOKE。

7.1 创建用户

CREATE USER 用户名@IP地址 IDENTIFIED BY '密码';
//用户只能在指定的IP地址上登录

CREATE USER 用户名@'%' IDENTIFIED BY '密码';
//用户可以在任意IP地址上登录

mysql> CREATE user zp@localhost IDENTIFIED BY '1234';
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
PS D:\software\software-install\mySQL_8.0.4\bin> .\mysql -uzp -p
Enter password: ****
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)

只能看到图中的两个数据库

7.2 给用户授权

GRANT 权限1，权限2，... on 数据库.* TO 用户名@IP地址；
//权限、用户、数据库
//给用户分配在指定的数据库上的指定的权限
//例如：GRANT CREATE,INSERT,DROP,UPDATE ON mydb3.* TO user1@IP地址;表示给user1用户分配在mydb3上的CREATE...权限
//GRANT ALL ON 数据库.* TO 用户名@IP地址;表示给用户分配指定数据库上的所有权限

PS D:\software\software-install\mySQL_8.0.4\bin> .\mysql -uroot -p
Enter password: ****
mysql> GRANT ALL ON mydb3.* TO zp@localhost;
Query OK, 0 rows affected (0.01 sec)

PS D:\software\software-install\mySQL_8.0.4\bin> .\mysql -uzp -p
Enter password: ****
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb3              |
| performance_schema |
+--------------------+

7.3 撤销授权

REVOKE 权限1,... ON 数据库.* FROM 用户名@IP地址;
//撤销指定用户在指定数据库上的指定权限

PS D:\software\software-install\mySQL_8.0.4\bin> .\mysql -uroot -p
Enter password: ****
mysql> REVOKE delete ON mydb3.* FROM zp@localhost;
Query OK, 0 rows affected (0.00 sec)
exit
PS D:\software\software-install\mySQL_8.0.4\bin> .\mysql -uzp -p
Enter password: ****
USE mydb3;
mysql> DELETE FROM stu1 where name='liSi';
ERROR 1142 (42000): DELETE command denied to user 'zp'@'localhost' for table 'stu1'

7.4 查看权限

SHOW GRANTS FOR 用户名@IP地址
//查看指定用户的权限

mysql> show GRANTS FOR zp@localhost;
| Grants for zp@localhost
                                                                                                      |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `zp`@`localhost`
                                                                                                      |
| GRANT SELECT, INSERT, UPDATE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mydb3`.* TO `zp`@`localhost` |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

7.5 删除用户

DROP USER 用户名@IP地址

8 DQL(数据查询语言)

数据查询语言（Data Query Language，简称DQL）：DQL用于从数据库中获取数据，进行数据的查询和检索。常用的DQL语句是SELECT。

8.1 DQL之基础查询

8.1. 查询所有列

select * from emp;

8.2 查询指定列

select empno, ename, job from emp;

8.3 列运算

去除完全重复行

SELECT DINSTINCT deptno from emp;

数量类型的列可以做加、减、乘、除

select sal * 1.5 from emp;

字符串类型可以做连续运算

select CONCAT('我的名字叫', ename) from emp;

转换NULL值

select IFNULL(COMM,0) + sal from emp;

给列起别名

select ename 姓名 from emp;
or
select ename as 姓名 from emp;

8.2 DQL之条件查询

where语句，上面讲过不赘述

8.3 DQL之模糊查询

只需记住关键字like，_，%
其中%匹配0~N各任意字符，_匹配一个字符

select * from emp where ename like '%小%';
//查询所有名字中含有小的记录

8.4 DQL之排序

关键字ORDER BY， ASC(默认)， DESC（降序）

select * from emp ORDER BY sal ASC, comm DESC, empno ASC;

8.5 DQL之聚合函数

聚合函数就是纵向列的计算
五个函数，count（有效行数，NULL行不计入），sum, min， sum, avg

select count(*) 人数, sum(sal) 总和, aveg(sal) 平均, max(sal) 最高, min(sal) 最低 from emp;

8.6 DQL之分组查询

关键字group by, having
分组查询是把记录使用某一列进行分组，然后查询组信息
例如：查询所有部门的记录数

select deptno, count(*) from emp group by deptno;
//使用deptno分组，查询部门编号和每个部门的记录数
//查询的只有分组列，和聚合函数，group by后面跟分组列

组条件
以部门分组，查询每组记录数。条件为记录数大于3

select deptno,count(*) from emp group by deptno having count(*) > 3;

以部门分组(去掉工资小于15000)，查询每组记录数。条件为记录数大于3

select deptno,count(*) from emp where sal > 15000 group by deptno having count(*) > 3;

8.7 DQL之limit方言

LIMIT用来限定查询结果的起始行，以及总行数
例如：查询起始行为第5行，一共查询3行记录
select * from emp limit 4,3
–>其中4表示从第5行开始，其中3表示一共查询3行。即第5，6，7行记录

//列出第2，3，4，5，6行记录
select * from emp limit 1,5
//列出第13，14，15，16，17记录
select * from emp limit 12,5
//列出第1，2，3，4，5记录
select * from emp limit 0,5

将来用limit用于分页查询
如要求：

一页的记录数：10行
查询第3页

select * from emp limit 20,10

公式：（当前页-1）* 每页记录数

9 TCL

事务控制语言（Transaction Control Language，简称TCL）：TCL用于控制事务的提交和回滚，保证数据库的一致性和完整性。常用的TCL语句包括COMMIT和ROLLBACK。