MySQL的基本知识梳理

秒速五厘米 2024-04-20 11:23 96阅读 0赞

## MySQL的基本知识梳理 ##

### 一、数据库 ###

> 数据库（database）就是一个存储数据的仓库。为了方便数据的存储和管理，它将数据按照特定的规律存储在磁盘上。通过数据库管理系统，可以有效地组织和管理存储在数据库中的数据

### 二、数据库的五个基本概念 ###

#### 1.数据库服务器 ####

> 是指用来运行数据库服务的一台电脑。小型项目通常为一台，中大型项目通常是多台数据库服务器共同来存储或计算。由于数据安全非常重要，所以我们需要对数据库服务器里面的数据进经备份管理。

#### 2.数据库 ####

> 一个数据库服务器里面有可以有多个数据库。主要用来分类使用。我们可以建立学员管理系统数据库、电子商城数据库、CRM数据库、ERP数据库等等，主要用来将各个不同用途的数据，按照业务进行划分。

#### 3.数据表 ####

> 例如在学员管理系统数据库中。根据业务的不同又分为了不同的数据表。专门用来存放不同人员的数据。例如：学员数据表（学号、用户名、密码）；老师数据表（用户名、密码，工作经验）

#### 4.数据字段 ####

> 也叫数据列。就是我们日常所见表格里面的列。在表格中，我们会将一张用户表分成多个列。如用户编号、用户名、性别、年龄是字段。在真正的数据库中数据字段需要换成英文需要写成：id、username、sex、age。

#### 5.数据行 ####

> 真正的数据存在每一个表的行里面。字段（列）划分出来了一个表应该按照什么样的格式存数据。而行，是真正的数据。每一行需要遵循数据字段（列）的规范和要求进行存入数据。

### 三、MySQL ###

> MySQL是一款完全免费的产品，用户可以直接从网上下载使用，而不必支付任何费用。此外，MySQL数据库的跨平台性也是其一个很大的优势之一。  
> MySQL是一个真正的多用户、多线程SQL数据库服务器。它是以客户机/服务器结构实现的，由一个服务器守护程序以及很多不同的客户程序和库组成  
> 官方下载地址:  
> http://dev.mysql.com/downloads/mysql/
> 
> MySQL 5.融合了MySQL数据库和InnoDB存储引擎的优点，能够提供高性能的数据管理解决方案，包括：  
> InnoDB作为默认的数据库存储引擎。  
> 改善性能和可扩展性，全面利用各平台现代、多核构架的计算能力。  
> 提高实用性。  
> 提高易管理性和效率。  
> 提高可用性。

### 四、安装MySQL注意事项 ###

> 双击即可,大部分都是一路next
> 
> 关键节点注意事项:
> 
> 1.选中“Typical”,典型安装模式
> 
> 2.选中“Detailed Configuration”（详细配置）单选按钮
> 
> 3.选中“Developer Machine”（开发者机器）单选按钮
> 
> 4.选中“Multifunctional Database”（多功能数据库）单选按钮
> 
> 5.服务端口3306，不建议更改
> 
> 6.字符集注意要选“Manual Selected Default Character Set/Collation”单选按钮，设置字符集编码为utf8
> 
> 7.设置密码尽量简单，注意不要忘了，我们只是来做实验  
> 用户名：root 密码：root

### 五、启动MySQL服务 ###

> “我的电脑”→“管理”→“服务”在服务器的列表中找到mysql服务并右键单击，在弹出的快捷菜单中，完成MySQL服务的各种操作（启动、重新启动、停止、暂停和恢复）
> 
> 也可以通过命令行的方式来管理
> 
> Window键+r
> 
> 启动：net start mysql
> 
> 关闭：net stop mysql

### 六、SQL ###

> 结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。 hhy.sql  
> SQL是关系模型的数据库应用语言，由IBM在20世纪70年代为其关系型数据库 System R 所开发。  
> SQL 是1986年10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO）颁布了SQL正式国际标准。1989年4月，ISO提出了具有完整性特征的SQL89标准，1992年11月又公布了SQL92标准。  
> 虽然各个数据库系统略有不同，但是他们基本均遵循SQL 92标准。或者在SQL 92上做了一些简单的扩展和变化。

### 七、SQL分类 ###

> 使用MySQL应于配套软件，例如Navicat for MySQL 软件
> 
> char(32)：最多只能存储32个字符，如果未满32个，也占32个的空间
> 
> varchar(32)：最多只能存储32个字符，如果未满32个，占实际字符的个数
> 
> 注意：以上两种字符串表现形式最多只能存储255个字符

#### 1.**数据定义语言**(DDL ，Data Defintion Language)语句 ####

> 数据定义语句，用于定义不同的数据对象、数据库、表、列、索引等。常用的语句关键字包括create、drop、alter等。

##### 1.操作数据库 #####

> 创建数据库
> 
> 基本语法：create table 表名(字段名 字段类型 ，… ， 字段名 字段类型);

# 显示所有数据库
    SHOW DATABASES;
    
    # 创建数据库
    CREATE DATABASE aaa;
    
    # 使用指定数据库
    USE aaa;
    
    # 展示数据库中所有的表
    SHOW TABLES;
    
    # 删除数据库
    DROP DATABASE aaa;

##### 2.操作数据表 #####

> 语法：alter table 旧表名 rename 新的表名;

# 创建表
    CREATE TABLE s_student(
    	s_name VARCHAR(32),
    	s_sex VARCHAR(32),
    	age INT(3),
    	salary FLOAT(8,2),
    	course VARCHAR(32)
    )
    
    # 修改表名
    ALTER TABLE s_student RENAME student;
    
    # 删除表
    DROP TABLE student;

##### 3.操作字段 #####

> 修改字段类型
> 
> 语法格式：alter table 表名 modify 字段名 varchar(20);
> 
> 语法格式：alter table 表名 change 字段原名 字段新名 字段类型;
> 
> 增加表字段：  
> 语法格式：alter table 表名 add 字段名 类型;
> 
> 删除表字段：  
> 语法格式：alter table 表名 drop column 字段名;

# 查询字段
    DESC student;
    
    # 创建创建表信息
    SHOW CREATE TABLE student;
    
    # 修改字段类型
    ALTER TABLE student MODIFY course VARCHAR(8);
    
    # 修改字段名
    ALTER TABLE student CHANGE s_name name VARCHAR(32);
    
    # 修改字段名 + 类型
    ALTER TABLE student CHANGE s_sex sex VARCHAR(1);
    
    # 添加字段
    ALTER TABLE student ADD gl VARCHAR(32);
    
    # 删除字段
    ALTER TABLE student DROP gl;

#### 2.数据操作语言(DML ， Data Manipulation Language)语句 ####

> 数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据的完整性。常用的语句关键字主要包括insert、delete、update和select等。（DML:添加，修改，删除，DQL:查询）

##### 1.创建数据库 #####

# PRIMARY KEY - 主键
    # auto_increment - 自动增长
    CREATE TABLE student(
    	id INT(3) PRIMARY KEY auto_increment,
    	name VARCHAR(32),
    	sex VARCHAR(32),
    	age INT(3),
    	salary FLOAT(8,2),
    	course VARCHAR(32)
    )

##### 2.添加数据 #####

> 语法格式：
> 
> insert into 表 values(值1,值2,值n);
> 
> insert into 表(字段1,字段2,字段n) values(值1,值2,值n);

# 添加数据方式1：
    INSERT INTO student VALUES(1,"后裔","男",20,12000,"Java");
    
    # 添加数据方式2：
    INSERT INTO student(name,sex,age,salary,course) VALUES("刘备","男",25,15000,"Java");
    INSERT INTO student(name,sex,age,salary,course) VALUES("孙尚香","女",18,15000,"Java");
    INSERT INTO student(name,sex,age,salary,course) VALUES("赵云","男",64,8000,"Java");
    INSERT INTO student(name,sex,age,salary,course) VALUES("孙策","男",60,6000,"HTML");
    INSERT INTO student(name,sex,age,salary,course) VALUES("土行孙","男",32,12000,"HTML");
    INSERT INTO student(name,sex,age,salary,course) VALUES("土行孙","男",300,20000,"HTML");
    INSERT INTO student(name,sex,age,salary,course) VALUES("孙悟空","男",45,8000,"Python");
    INSERT INTO student(name,sex,age,salary,course) VALUES("公孙二娘","女",50,9000,"Python");
    INSERT INTO student(name,sex,age,salary,course) VALUES("欧克鎏孙","男",48,10000,"Python");

##### 3.修改数据 #####

> update 表名 set 字段1=值1,字段2=值2,字段n=值n where 条件

# 把指定字段上所有数据都修改
    UPDATE student SET salary=10000;
    
    # 依据条件修改数据
    UPDATE student SET salary=11000 WHERE id<4;
    
    # 依据多条件修改数据
    UPDATE student SET salary=15000 WHERE id>3 AND id<7;# AND - 与
    UPDATE student SET salary=18000 WHERE id>6 or sex="女";# OR - 或
    
    # 修改多个数据
    UPDATE student SET age=46,course="HTML" WHERE id=7;

##### 4.删除数据 #####

> delete from 表 \[where 条件\];
> 
> TRUNCATE TABLE 表名;

# 清空表中所有的数据
    TRUNCATE TABLE student;# 清空表后，再添加数据时，id从1开始
    DELETE FROM student;# 清空表后，再添加数据时，id从上次末尾开始
    # 注意1：推荐使用DELETE FROM student;去清空表，因为把之前删除的数据恢复时，id不冲突
    # 注意2：一般情况下数据不会使用DELETE去删除，删除数据不会真正删除，可以利用一个字段变量去说明该数据行的数据是否可用

##### 5.查询数据 #####

> select \* from 表;
> 
> select 字段 from 表;
> 
> select distinct 字段 from 表;
> 
> select 字段 from 表 where条件 and 条件;
> 
> select 字段 from 表 where 字段 like ‘%内容%’;（模糊查询）
> 
> select 字段 from 表 order by 字段 排序关键词
> 
> select 字段 from 表 limit 数量;
> 
> select 字段 from 表 order by 字段 关键词 limit 数量
> 
> select 字段 from 表 limit 偏移量,数量
> 
> 内连接  
> select 表1.字段 \[as 别名\],表n.字段 from 表1 \[别名\],表n where 条件;  
> select 表1.字段 \[as 别名\],表n.字段 from 表1 INNER JOIN 表n on 条件;
> 
> 外连接  
> select 表1.字段 \[as 别名\],表n.字段 from 表1 LEFT JOIN 表n on 条件;  
> 左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录  
> 右连接：包含所有的右边表中的记录甚至是右边表中没有和它匹配的记录  
> select 表1.字段 \[as 别名\],表n.字段 from 表1 right JOIN 表n on 条件;
> 
> select 表1.字段 \[as 别名\],表n.字段 from 表1 right JOIN 表n on 条件;

# 查询所有字段
    SELECT * FROM student;
    
    # 查询指定字段
    SELECT name,sex FROM student;
    
    # 查询指定字段 + 别名
    SELECT name AS "姓名",sex AS "性别" FROM student;
    
    # 去重
    SELECT DISTINCT name AS "姓名",sex AS "性别" FROM student;
    
    # 依据条件查询
    SELECT * FROM student WHERE id = 2;
    SELECT * FROM student WHERE id > 3 AND id<6;
    SELECT * FROM student WHERE id > 6 OR sex="女";
    
    # 模糊查询 - 需求：查询出姓名中带'孙'的数据
    # %表示没有或多个字符
    SELECT * FROM student WHERE name LIKE "%孙%";
    
    # 模糊查询 - 需求：查询出姓名中最后带'华'的数据
    # %表示没有或多个字符
    SELECT * FROM student WHERE name LIKE "%孙";
    
    # 模糊查询 - 需求：查询出姓名中最后带'华'的数据，名字是四个字
    # _表示一个字符
    SELECT * FROM student WHERE name LIKE "___孙";
    
    # 排序查询 - 需求：按照年龄排升序
    # ASC表示升序
    SELECT * FROM student ORDER BY age ASC;
    
    # 排序查询 - 需求：按照工资排降序
    # ASC表示升序
    # DESC表示降序
    SELECT * FROM student ORDER BY age ASC;
    
    # 多字段排序 - 需求：按照年龄排升序，年龄相同按照工资排降序
    SELECT * FROM student ORDER BY age ASC,salary DESC;
    
    # 限制查找
    SELECT * FROM student LIMIT 3;
    
    # 限制查找 - 需求：查询出年龄最大的前三的学生信息
    SELECT * FROM student ORDER BY age DESC LIMIT 3;
    
    # 分页查找
    # 公式：SELECT * FROM student LIMIT (页数-1)*数据条数,数据条数;
    # SELECT * FROM student LIMIT 偏移量,获取数据条数;
    SELECT * FROM student LIMIT 0,4;# 第一页
    SELECT * FROM student LIMIT 4,4;# 第二页
    SELECT * FROM student LIMIT 8,4;# 第三页
    
    # 聚合查询 - 利用MySQL给我们提供的函数去查询数据
    # 需求：查询工资的总和
    SELECT SUM(salary) FROM student;
    
    # 需求：查询最高的工资
    SELECT MAX(salary) FROM student;
    
    # 需求：查询最低的工资
    SELECT MIN(salary) FROM student;
    
    # 需求：查询平均工资
    SELECT AVG(salary) FROM student;
    
    # 需求：查询平均工资 + 保留两位小数
    SELECT ROUND(AVG(salary),2) FROM student;
    
    # 需求：查询学生表数据个数/条数
    # 注意：统计个数建议使用主键，如果使用其他字段，字段中的数据为null的话则不计入最后的结果
    SELECT COUNT(id) FROM student;
    
    # 子查询 - 需求：查询出工资最高的学生信息
    SELECT * FROM student WHERE salary=(SELECT MAX(salary) FROM student);
    
    # 分组过滤
    # 需求：查询出各个学科学生的平均工资
    SELECT course,AVG(salary) FROM student GROUP BY course;
    
    # 分组过滤 + 条件
    # 需求：查询出各个学科学生平均工资并且平均工资大于10000
    SELECT course,AVG(salary) FROM student GROUP BY course HAVING AVG(salary) > 10000;

##### 6.多表联合查询 ---- 一对多 #####

# ------------ 制作场景 -------------
    CREATE TABLE teacher(
    	id INT(3) PRIMARY KEY auto_increment,
    	name VARCHAR(32)
    )
    
    CREATE TABLE student(
    	id INT(3) PRIMARY KEY auto_increment,
    	name VARCHAR(32),
    	t_id INT(3)
    )
    
    INSERT INTO teacher(name) VALUES("刘备");
    INSERT INTO teacher(name) VALUES("曹操");
    INSERT INTO teacher(name) VALUES("孙权");
    INSERT INTO student(name,t_id) VALUES("关羽",1);
    INSERT INTO student(name,t_id) VALUES("张飞",1);
    INSERT INTO student(name,t_id) VALUES("诸葛亮",1);
    INSERT INTO student(name,t_id) VALUES("典韦",2);
    INSERT INTO student(name,t_id) VALUES("许褚",2);
    INSERT INTO student(name) VALUES("大乔");
    
    # 内连接
    SELECT s.name,t.name FROM student s,teacher t WHERE s.t_id = t.id;
    
    SELECT s.name,t.name FROM student s INNER JOIN teacher t ON s.t_id = t.id;
    
    # 外连接 -- 左连接
    SELECT s.name,t.name FROM student s LEFT JOIN teacher t ON s.t_id = t.id;
    
    # 外连接 -- 右连接
    SELECT s.name,t.name FROM student s RIGHT JOIN teacher t ON s.t_id = t.id;
    
    # 合并查询结果
    SELECT s.name,t.name FROM student s LEFT JOIN teacher t ON s.t_id = t.id
    UNION ALL
    SELECT s.name,t.name FROM student s RIGHT JOIN teacher t ON s.t_id = t.id;
    
    # 全连接(合并查询结果+去重)
    SELECT s.name,t.name FROM student s LEFT JOIN teacher t ON s.t_id = t.id
    UNION
    SELECT s.name,t.name FROM student s RIGHT JOIN teacher t ON s.t_id = t.id;

##### 7.多表联合查询 ---- 多对多 #####

# ------------ 制作场景 -------------
    CREATE TABLE teacher(
    	id INT(3) PRIMARY KEY auto_increment,
    	name VARCHAR(32)
    )
    
    CREATE TABLE student(
    	id INT(3) PRIMARY KEY auto_increment,
    	name VARCHAR(32)
    )
    
    CREATE TABLE mid_table(
    	id INT(3) PRIMARY KEY auto_increment,
    	s_id INT(3),
    	t_id INT(3)
    )
    
    INSERT INTO teacher(name) VALUES("刘备");
    INSERT INTO teacher(name) VALUES("曹操");
    INSERT INTO teacher(name) VALUES("孙权");
    INSERT INTO student(name) VALUES("董卓");
    INSERT INTO student(name) VALUES("吕布");
    INSERT INTO mid_table(s_id,t_id) VALUES(1,1);
    INSERT INTO mid_table(s_id,t_id) VALUES(1,2);
    INSERT INTO mid_table(s_id,t_id) VALUES(2,1);
    INSERT INTO mid_table(s_id,t_id) VALUES(2,2);
    INSERT INTO mid_table(s_id,t_id) VALUES(2,3);
    
    SELECT m.id,s.name,t.name FROM student s INNER JOIN teacher t INNER JOIN mid_table m ON s.id=m.s_id AND t.id=m.t_id;

#### 3.**数据控制语言**(DCL， Data Control Language)语句 ####

> 数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant、revoke等
> 
> 初学者一般接触不到，还是努力学到大咖吧