MySql数据库

MySQL笔记

Mysql概述

• 数据库：存储数据的仓库，简称DataBase

• 数据库管理系统：操纵和管理数据库的大型软件，简称DBMS

• SQL：操作关系型数据库的编程语言，定义了一套操作关系型数据库统一的标准

  ​ 主流的关系型数据库管理系统：Oracle、Mysql、SQLserver等

• 关系型数据库：建立在关系模型基础上，有多张相互连接的二维表组成的数据库

Mysql操作

DDL定义数据库、表和字段

数据库操作

1. 创建数据库：

   create database database_name;

2. 删除数据库：

   drop database database_name;

3. 选择数据库

   use database_name;

表操作

1. 创建表：

   create table table_name (
      column1 datatype,
      column2 datatype,
      column3 datatype,
      ...
   );

2. 删除表：

   drop table table_name;

字段操作

1. 添加新列

   alter table customers add age int;

2. 修改列：

   alter table customers modify age date;

3. 删除列

   alter table customers drop column age;

4. 重命名列

   alter table customers rename column age to dob;

DML数据增删改

mysql数据类型

1. 数值型：

　　　　　　tinyint 超小整数

　　　　　　smallint 小整数

　　　　　　mediumint 中整数

　　　　　　int　　整数

　　　　　　bigint　　大整数

　　　　　　float 单精度浮点型

　　　　　　double 双精度浮点型

2. 字符型：

　　　　　　char　　定长字符串

　　　　　　varchar　　变长字符串

　　　　　　blob　　二进制长文本

　　　　　　text　　长文本

　　　　　　longblob　　二进制超长文本

　　　　　　longtext　　超长文本

3. 日期类型：

　　　　　　date　　日期

　　　　　　time　　时间

　　　　　　year　　年份

　　　　　　datetime　　时期加时间

　　　　　　timestamp　　时间戳

数据增删改

1. 插入数据：

   insert into 表名 (字段1, 字段2, ...) values (值1, 值2, ...);

2. 更新数据：

   update 表名 set 字段1=值1, 字段2=值2, ... where 条件;

3. 删除数据：

   delete from 表名 where 条件;

DQL查询

基础查询

1. 检索所有列的数据：

   select * from table_name;

2. 指定要检索的列：

   select column1, column2, ... from table_name;

3. 在检索结果中使用计算表达式：

   这些查询将返回一个新的列，名为new_column_name，其中包含column1和column2的和

   select column1 + column2 as new_column_name from table_name;

4. 去除重复记录：

   select distinct column1, column2, ... from table_name;

5. 对检索结果进行排序：

   select column1, column2, ... from table_name order by column1 asc/desc;

6. 限制检索结果集的数量：

   select column1, column2, ... from table_name limit x offset y;

条件查询

1. 查询所有名称以“a”开头的用户：

   SELECT * FROM users WHERE name LIKE 'a%';

2. 查询所有年龄大于等于18岁的用户：

   SELECT * FROM users WHERE age >= 18;

3. 查询所有拥有电子邮件地址的用户：

   SELECT * FROM users WHERE email IS NOT NULL;

4. 查询所有在2022年5月1日之前注册的用户：

   SELECT * FROM users WHERE registration_date < '2022-05-01';

5. 查询所有来自中国的用户：

   SELECT * FROM users WHERE country = 'china';

聚合函数

1. count：计算给定列中非空行的数量。 例如：

   SELECT COUNT(*) FROM table_name;

2. sum：计算给定列中所有值的总和。 例如：

   SELECT SUM(column_name) FROM table_name;

3. avg：计算给定列中所有值的平均值。 例如：

   SELECT AVG(column_name) FROM table_name;

4. min：找出给定列中的最小值。 例如：

   SELECT MIN(column_name) FROM table_name;

5. max：找出给定列中的最大值。 例如：

   SELECT MAX(column_name) FROM table_name;

分组查询

SELECT 字段列表 FROM表名 [WHERE条件 ] GROUP BY 分组字段名[HAVING 分组后过滤条件];

1. 对学生表按照性别进行分组查询各组的平均年龄：

   select gender, avg(age) from student group by gender;

2. 对订单表按照客户ID和订单状态进行分组统计各组的订单数量：

   select customer_id, status, count(*) from orders group by customer_id, status;

3. 对销售表按照日期和产品类型进行分组查询各组的销售总额：

   select date, product_type, sum(sales_amount) from sales group by date, product_type;

4. 对员工表按照职位进行分组查询各组的平均工资：

   select job_title, avg(salary) from employees group by job_title;

排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;

1. 从表格中选择所有行，按升序排列名为“age”的列：

   select * from table order by age asc;

2. 从表格中选择前10行，按降序排列名为“salary”的列：

   select * from table order by salary desc limit 10;

3. 从表格中选择所有行，首先按名为“category”的列进行升序排列，然后再按名为“price”的列进行降序排列：

   select * from table order by category asc, price desc;

4. 从表格中选择前5行，按名为“date”的列进行升序排列，如果值相同，则按名为“time”的列进行降序排列：

   select * from table order by date asc, time desc limit 5;

分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

从11页开始向后查20条数据

SELECT * FROM table LIMIT 10 OFFSET 20;

约束

MySQL中的约束用于限制表中列的取值范围，可以提高数据完整性和一致性。下面是MySQL中常见的约束类型及其示例：

1. PRIMARY KEY（主键）约束

主键约束用于将一个或多个列标识为唯一标识该表中每一行的关键字。主键必须包含唯一的值，并且不能为NULL。

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

2. NOT NULL（非空）约束

非空约束用于确保列中不包含NULL值。

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  salary DECIMAL(10,2) NOT NULL
);

3. UNIQUE（唯一性）约束

唯一性约束用于确保列中的所有值都是唯一的。

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(50),
  author VARCHAR(50),
  isbn VARCHAR(20) UNIQUE
);

4. FOREIGN KEY（外键）约束

外键约束用于确保在一个表中的列中的值必须与另一个表中的列中的值匹配。

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

5. CHECK（检查）约束

检查约束用于确保列中的值满足特定的条件。

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  salary DECIMAL(10,2) CHECK (salary >= 0)
);

多表查询

表间的对应关系

1. 一对多&多对一

   案例：部门与员工的关系

   关系：一个部门对应多个员工，一个员工对应一个部门

   实现：在多的一方建立外键，指向一的一方的主键

   

2. 多对多

   案例：学生与课程的关系

   关系：一个学生可以选秀多门课程，一门课程也可以供多个学生选择

   实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

   

3. 一对一

   案例：用户与用户详情的关系
   关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中
   以提升操作效率

   实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

   

   

多表联查是指在数据库中同时查询多个表，并根据它们之间的关联条件返回结果集。常见的多表联查操作包括内连接、外连接和自连接。

内连接（INNER JOIN）：内连接通过匹配两个或多个表之间的共同值来返回满足条件的记录。只有在所有参与连接的表中存在匹配的行才会出现在结果集中。内连接使用关键字”INNER JOIN”来表示。

示例SQL语句：

SELECT *
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

外连接（OUTER JOIN）：外连接用于返回不仅匹配的记录，还包括未匹配的记录。外连接分为左外连接（LEFT JOIN）、右外连接（RIGHT JOIN）和全外连接（FULL JOIN）。

• 左外连接（LEFT JOIN）：返回左表中的所有记录和右表中与之匹配的记录。 示例SQL语句：

SELECT *
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

• 右外连接（RIGHT JOIN）：返回右表中的所有记录和左表中与之匹配的记录。 示例SQL语句：

SELECT *
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

• 全外连接（FULL JOIN）：返回左表和右表中的所有记录。 示例SQL语句：

SELECT *
FROM table1
FULL JOIN table2 ON table1.column = table2.column;

自连接（SELF JOIN）：自连接是指将单个表视为两个不同的实例进行连接操作。它通常用于在表中建立父子关系或层次结构。

示例SQL语句：

SELECT *
FROM table1 t1
INNER JOIN table1 t2 ON t1.column = t2.column;

Mysql主从复制

主从复制是一种常见的数据库架构方法，它允许在多个服务器之间同步数据。在主从复制架构中，有一个主服务器和一个或多个从服务器。主服务器负责处理所有写操作，而从服务器则对主服务器进行复制，并处理所有读操作。

具体实现步骤：

1. 设置一个mysql为主库

   windows中：在C盘的ProgramData文件夹中找到mysql——my.ini

   在my.ini中的[mysqld]中添加：

   log-bin=mysql-bin	#启用二进制日志
   binlog-format=ROW
   server-id=100	#设置专属id名

2. 重启服务

   systemctl restart mysqld

3. 登录mysql数据库，执行下面mysql

   GRANT REPLICATION SLAVE ON *.* to 'xiaoming'@'%' identified by 'Root@123456';

   注:上面SQL的作用是创建一个用户xiaoming，密码为Root@123456，并且给xiaoming用户授予

   REPLICATION SLAVE权限

4. 执行代码

   show master status;

   

5. 设置一个或多个从库

   linux中：var/log/my.cnf添加：

   server-id=102

   进入mysql运行：

   change master to master_host='主机ip' ,master_user='xiaoming' ,master_password='Root@123456',master_log_file='mysql-bin.000005' ,master_log_pos=441;

   master_log_file对应上面的File master_log_pos对应上面的Position

6. 开启io线程

   starts slave;

7. 查看连接情况

   show slave status\G;

   出现这个即为成功：