上海网站建设公司有多少家,做球服的网站有哪些,网站维护描述,轻量级数据库wordpress写在前面
鉴于全网MySQL知识点的总结分散难懂、良莠不齐#xff0c;为了避免初学者少走弯路#xff0c;更好更快地掌握MySQL知识#xff0c;博主特地将自己所学的笔记分享出来。
如果想深度理解掌握MySQL#xff0c;欢迎订阅专栏#xff1a;MySQL进阶之路【秋说】#…写在前面
鉴于全网MySQL知识点的总结分散难懂、良莠不齐为了避免初学者少走弯路更好更快地掌握MySQL知识博主特地将自己所学的笔记分享出来。
如果想深度理解掌握MySQL欢迎订阅专栏MySQL进阶之路【秋说】本专栏注重实践和案例应用带你成为优质后端程序员。 文章目录 写在前面MySQL基础概念SQL语句的分类MySQL常用命令MySQL数学运算语句MySQL条件查询MySQL排序MySQL数据处理函数MySQL分组查询MySQL查询结果去重MySQL连接查询MySQL子查询MySQL部分操作符MySQL之增删改查MySQL约束MySQL事务MySQL索引MySQL视图**MySQL设计的三个范式**写在最后 MySQL基础概念
数据库 用于存储和组织结构化数据的系统。它是一个电子化的数据仓库可以方便地管理、访问和更新大量数据。在数据库中数据按照一定的规则和结构进行组织和存储以便于对数据进行检索、处理和管理。数据库通常由多个表格或称为关系组成每个表格包含了一组相关的数据字段列和数据记录行。
数据库管理系统 用于管理和操作数据库的软件系统。它提供了一系列的工具和功能用于创建、访问、维护和控制数据库。
常见的数据库管理系统DBMS包括 关系型数据库如MySQL、Oracle、SQL Server、非关系型数据库如MongoDB、Redis等
表 在关系型数据库中表是一种数据结构用于存储和组织数据。它由行和列组成。表可以被看作是一个二维的数据表格其中每一列代表一个字段每一行代表一条记录。
如下图所示
SQL语句的分类
数据定义语言Data Definition LanguageDDL用于创建、修改和删除数据库对象如数据库、表、视图、索引等。常见的DDL语句包括
create创建数据库对象
alert修改数据库对象的结构
drop删除数据库对象
数据操作语言Data Manipulation LanguageDML用于查询、插入、更新和删除数据。常见的DML语句包括
select查询数据
insert插入数据
update更新数据
delete删除数据
数据查询语言Data Query LanguageDQL专门用于查询数据是DML的子集用于从数据库中检索数据。数据控制语言Data Control LanguageDCL用于授权和权限管理。常见的DCL语句包括
grant授予用户权限
revoke撤销用户权限
事务控制语言Transaction Control LanguageTCL用于管理数据库中的事务。常见的TCL语句包括
commit提交事务
rollback回滚事务
savepoint设置保存点 MySQL常用命令
查看mysql数据库的版本号
select version();启动MySQL服务基于Windows
net start mysql停止MySQL服务
net stop mysql登录MySQL数据库
mysql -u 用户名 -p其中用户名是MySQL用户名接着根据系统提示输入密码即可
如果安装MySQL时没有设置root密码可以使用以下命令登录
mysql -u root退出 MySQL 命令行界面
exit或者quit显示所有的数据库名
show databases;创建数据库
create database 数据库名;切换到特定的数据库
use 数据库名;查看当前使用的数据库名
select database();删除数据库
drop database 数据库名;显示数据库中的表
show tables;创建表
create table 表名 (列名1 数据类型,列名2 数据类型,...
);删除表
drop table 表名;向表中插入数据
insert into 表名(列名1, 列名2, ...)
VALUES (具体值1, 具体值2, ...);在表中查询特定的数据
select 列名1, 列名2, ...
from 表名
where 查询条件;不需要查询条件时
select 列名1, 列名2, ...
from 表名;获取某表的所有数据
select * from 表名 获取某表的结构
desc 表名; 也可使用 describe 表名;更改表中某列的名字
alert table 表名 rename column 原始列名 to 新列名;为表中某列指定一个别名并不改变表结构中列的名称
select 原始列名 as 新列名 from 表名;MySQL数学运算语句
加法使用 运算符执行加法运算。
select column1 column2 as sum from 表名; //执行加法运算后的结果列指定别名为sumselect column1 10 from 表名; //也可以不指定别名减法使用 - 运算符执行减法运算。
select column1 - column2 as reduce from 表名;乘法使用 * 运算符执行乘法运算。
select column1 * column2 as ride from 表名;select column1 * 10 as ride from 表名; //数值扩大十倍除法使用 / 运算符执行除法运算。
select column1 / column2 as division from 表名;取余使用 % 运算符计算两个数的余数。
select column1 % column2 as remainder from 表名;其它运算 POWER() 函数用于计算幂次方SQRT() 函数用于计算平方根ABS() 函数用于获取绝对值等等。 MySQL条件查询
MySQL 中的条件查询是通过使用 WHERE 子句来筛选满足特定条件的数据。
MySQL 中条件查询的基本语法
SELECT column1, column2, ... 。。列名
FROM table_name //表名
WHERE condition; //筛选条件示例
等于 () 运算符
SELECT * FROM table_name WHERE column_name value;
//也可指定特定的列不等于 ( 或 !) 运算符
SELECT * FROM table_name WHERE column_name value;大于 () 运算符
SELECT * FROM table_name WHERE column_name value;小于 () 运算符
SELECT * FROM table_name WHERE column_name value;大于等于 () 运算符
SELECT * FROM table_name WHERE column_name value;小于等于 () 运算符
SELECT * FROM table_name WHERE column_name value;BETWEEN运算符
//第一种方式
SELECT * FROM table_name WHERE column_name value1 AND column_name value2;//第二种方式
SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;
//value1必须小于value2模糊匹配 (LIKE) 运算符
SELECT * FROM table_name WHERE column_name LIKE pattern;条件查询还可以通过逻辑运算符例如 AND, OR, NOT组合多个条件。NULL运算符
SELECT * FROM table_name WHERE column_name IS NULL;这将返回所有 column_name 列中值为 NULL 的行。
IN运算符
IN 运算符用于在查询中匹配多个值。它允许您指定一个值列表并检查某个列是否与该列表中的任何值匹配。
SELECT * FROM table_name WHERE column_name IN (value1, value2, value3);例如
SELECT * FROM users WHERE country IN (USA, Canada, UK);这将返回所有国家为 “USA”、“Canada” 或 “UK” 的用户。
注意IN后跟的是具体的值并不是一个区间。
AND 运算符AND 运算符将多个条件连接起来并要求所有条件都满足才能返回结果。如果任何一个条件不满足那么整个条件将被视为不满足。例如
SELECT * FROM table_name WHERE condition1 AND condition2;在上述查询中只有当 condition1 和 condition2 都满足时才会返回满足条件的行。
OR 运算符OR 运算符将多个条件连接起来并要求至少一个条件满足才能返回结果。如果所有条件都不满足那么整个条件将被视为不满足。例如
SELECT * FROM table_name WHERE condition1 OR condition2;在上述查询中只要 condition1 或者 condition2 中至少一个满足就会返回满足条件的行。
在MySQL中AND 运算符的优先级高于OR运算符。这意味着当查询中同时存在AND和OR运算符时AND运算符的条件会首先进行计算然后再计算OR运算符的条件。
例如对于以下查询
SELECT * FROM table_name WHERE condition1 AND condition2 OR condition3;根据运算符优先级条件 condition1 和 condition2 会首先进行计算然后再计算 condition3。所以上述查询可以等价地写为
SELECT * FROM table_name WHERE (condition1 AND condition2) OR condition3;可以以使用括号来明确指定优先级以控制运算符的组合方式。例如
SELECT * FROM table_name WHERE (condition1 OR condition2) AND condition3;在上述查询中condition1 或者 condition2 至少满足一个并且同时还要满足 condition3 才会返回满足条件的行。
%运算符
在MySQL中% 运算符是用于模糊匹配的通配符。它通常与 LIKE 关键字一起使用用于在查询中匹配特定的模式。
% 表示零个或多个字符的任意序列。当放置在模式字符串的开头、中间或结尾时它可以匹配任意长度和任意字符的序列。
以特定字符开头的模式pattern%
例如SELECT * FROM table_name WHERE column_name LIKE abc% 会匹配 column_name 列中以 “abc” 开头的任何字符串如 “abcd”、“abc123” 等。
以特定字符结尾的模式%pattern
例如SELECT * FROM table_name WHERE column_name LIKE %xyz 会匹配 column_name 列中以 “xyz” 结尾的任何字符串如 “abcxyz”、“123xyz” 等。
包含特定字符的模式%pattern%
例如SELECT * FROM table_name WHERE column_name LIKE %abc% 会匹配 column_name 列中包含 “abc” 的任何字符串如 “abc”, “abcdef”, “xyzabcxyz” 等。
_运算符
在MySQL中下划线_运算符是用于模糊匹配的通配符之一通常与LIKE关键字一起使用。它表示匹配任意单个字符的位置。
例如SELECT * FROM table_name WHERE column_name LIKE a_b 可以匹配 column_name 列中以 “a” 开头然后是任意一个字符最后是 “b” 结尾的字符串如 “aab”、“acb” 等。 MySQL排序
在MySQL中使用 ORDER BY 子句来对结果进行排序。ORDER BY 子句允许指定一个或多个列作为排序依据并指定升序ASC或降序DESC进行排序。
单列排序
SELECT * FROM table_name ORDER BY column_name ASC;上述查询将按照 column_name 列的升序对结果进行排序。
多列排序
SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC;上述查询将首先按照 column1 列的升序进行排序对于相同的值则按照 column2 列的降序进行排序。
如果在查询中使用了列别名则可以通过别名进行排序
SELECT column_name AS alias_name FROM table_name ORDER BY alias_name ASC;指定了要选择的列 column_name并使用 AS 关键字将其命名为 alias_name并按照 alias_name 列进行升序排序。
默认情况下排序是升序的ASC。如果要进行降序排序可以使用 DESC 关键字。示例如下
SELECT * FROM table_name ORDER BY column_name DESC;注意ORDER BY子句应该位于查询语句的末尾。如果查询中使用了LIMIT子句则结果将首先根据排序顺序进行排序然后再进行限制。
数字排序
SELECT * FROM table_name ORDER BY 1(数字);排序时将结果集中在第一个字段
举个例子
假设有一个名为 employees 的表其中包含以下字段id和name。
SELECT * FROM employees ORDER BY 1 ASC;上述查询将按照结果集中的第一个列即 id 列进行升序排序。即按照员工的身份证号进行由小到大排序。 MySQL数据处理函数
1单行处理函数指在SQL中用于处理单个值的函数。它们接受一个或多个输入参数并返回一个单一的结果。
以下是一些常见的 SQL 单行处理函数
UPPER()将字符串转换为大写。例如SELECT UPPER(hello) 将返回 HELLO。
LOWER()将字符串转换为小写。例如SELECT LOWER(WORLD) 将返回 world。
LENGTH()返回字符串的长度。例如SELECT LENGTH(abcde) 将返回 5。
SUBSTRING()提取字符串的一个子串。例如SELECT SUBSTRING(Hello,World!, 1, 5) 将返回 Hello。
如果我们将参数 1 改为 7即 SELECT SUBSTRING(‘Hello, World!’, 7, 5)结果将返回子串 ‘World’因为它从第 7 个字符开始提取长度为 5 个字符。
CONCAT()将多个字符串连接起来。例如SELECT CONCAT(Hello, , World) 将返回 Hello World。
TRIM()去除字符串前后的空格。例如SELECT TRIM( hello ) 将返回 hello。
ROUND()对数值进行四舍五入。例如SELECT ROUND(3.14159, 2) 将返回 3.14。
ABS()返回数值的绝对值。例如SELECT ABS(-10) 将返回 10。
COALESCE()返回参数列表中第一个非空的值。例如SELECT COALESCE(NULL, default) 将返回 default。
NOW()返回当前日期和时间。例如SELECT NOW() 将返回当前的日期和时间。
2多行处理函数指在 SQL 中用于处理多个值的函数。它们接受多个输入参数并返回一个结果集可以包含多行和多列。
以下是一些常见的 SQL 多行处理函数
COUNT()计算满足条件的行数。例如SELECT COUNT(*) FROM employees 将返回 employees 表中的行数。
对于查询 SELECT COUNT(employee_id) FROM employees它将返回 employees 表中 employee_id 列中非空值的行数。
SUM()计算数值列的总和。例如SELECT SUM(salary) FROM employees 将返回 employees 表中所有员工的薪资总和。
AVG()计算数值列的平均值。例如SELECT AVG(salary) FROM employees 将返回 employees 表中所有员工的薪资平均值。
MIN() 和 MAX()分别返回数值列的最小值和最大值。例如SELECT MIN(salary), MAX(salary) FROM employees 将返回 employees 表中员工的最低和最高薪资。
GROUP_CONCAT()将多行值连接为一个字符串。例如SELECT GROUP_CONCAT(name) FROM employees 将返回 employees 表中所有员工姓名的逗号分隔列表。
GROUP BY 子句按照指定的列对结果进行分组。例如SELECT department, AVG(salary) FROM employees GROUP BY department 将按部门计算员工薪资的平均值并对结果进行分组。
HAVING 子句在 GROUP BY 分组结果上进行过滤。例如SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) 5000 将返回薪资平均值大于 5000 的部门。 MySQL分组查询
分组查询是一种在 SQL 中使用 GROUP BY 子句对结果进行分组的操作。它结合了聚合函数可以对数据集按照一个或多个列进行分组并对每个分组应用聚合函数计算。
SELECT 列1, 列2, ..., 聚合函数(column)
FROM 表名
GROUP BY 列1, 列2, ...列1, 列2, ... 表示要分组的列可以指定一个或多个列。在 SELECT 语句中除了分组列外还可以选择其他列。而 聚合函数(column) 是对数据进行聚合计算的函数可以是 SUM()、COUNT()、AVG()、MAX()、MIN() 等聚合函数。
例如假设我们有一个 orders 表其中包含 order_id、product_id 和 quantity 列。我们可以使用以下查询来计算每个产品的总销售数量
SELECT product_id, SUM(quantity) AS total_quantity
FROM orders
GROUP BY product_id;这个查询将根据 product_id 列将订单表中的数据进行分组并使用 SUM() 聚合函数计算每个产品的总销售数量。结果将包含每个产品的 product_id 和对应的总销售数量。
当存在group by与order by关键字时执行顺序如下
SELECT ...
FROM ...
WHERE...
GROUP BY ...
ORDER BY ...;首先根据 FROM 和 WHERE 子句的条件从表中选择相关的行。 然后根据 GROUP BY 子句中指定的列将结果集分成不同的分组。 接下来对每个分组进行聚合计算例如使用 SUM()、COUNT()、AVG() 等聚合函数。 最后使用 SELECT 子句选择要返回的列并在必要时进行别名定义。
如果存在 ORDER BY 子句会在最后对结果进行排序。
HAVING子句
HAVING 子句用于在 GROUP BY 子句之后对分组进行条件过滤。
假设我们有一个 orders 表包含订单信息其中包括 product_id、quantity 和 price 列。我们想要找出销售数量大于 100 且总金额大于 5000 的产品。
SELECT product_id, SUM(quantity) AS total_quantity, SUM(quantity * price) AS total_amount
FROM orders
GROUP BY product_id
HAVING total_quantity 100 AND total_amount 5000;在这个查询中们首先使用 GROUP BY 子句将 orders 表按照 product_id 进行分组。然后使用 SUM() 函数计算每个产品的总销售数量命名为 total_quantity和总金额命名为 total_amount。
接下来使用 HAVING 条件 total_quantity 100 AND total_amount 5000即只返回销售数量大于 100 且总金额大于 5000 的产品。
注意HAVING子句是在GROUP BY分组之后进行条件过滤与WHERE子句不同WHERE子句是在分组之前对原始数据进行条件过滤的。 MySQL查询结果去重
DISTINCT 关键字用于从结果集中选择唯一的记录并去除重复。
假设我们有一个 customers 表包含客户的信息其中包括 customer_id 和 email 列部分用户用的邮箱地址可能是一致的均为公司邮箱。我们希望获取所有唯一的邮箱地址。
SELECT DISTINCT email
FROM customers;对于每个邮箱地址只会返回一条记录重复的记录会被去除。
注意如果在 SELECT 语句中选择多列并且希望根据多列进行唯一性判断和去重只需将多列包含在 SELECT DISTINCT 子句中。
SELECT DISTINCT column1, column2
FROM table;这样将根据 column1 和 column2 的组合来做为判断唯一性的依据返回唯一的记录。 MySQL连接查询
连接查询用于在多个表之间建立关系并检索满足特定条件的数据。
常见的连接类型如下
内连接INNER JOIN返回两个表中满足连接条件的匹配行。只有在两个表中都存在匹配的值时才会返回结果。
SELECT *
FROM table1
INNER JOIN table2 ON table1.column table2.column;等值连接Equi Join一种基于相等条件进行连接的连接类型。在等值连接中两个表通过一个或多个列的值相等来建立连接。
假设我们有两个表employees 和 departments它们分别包含员工的信息和部门的信息。这两个表中都有一个共同的列 department_id我们可以使用该列来进行等值连接以获取每个员工所在的部门。
SELECT *
FROM employees
INNER JOIN departments ON employees.department_id departments.department_id;在这个查询中我们使用了 INNER JOIN 进行连接连接条件是 employees.department_id departments.department_id。这意味着只有在 employees 表中的 department_id 值等于 departments 表中的 department_id 值时才会返回匹配的行。
非等值连接Non-Equi Join一种基于不相等条件进行连接的连接类型。在非等值连接中两个表根据不相等的条件来建立连接。
假设我们有两个表orders 和 discounts分别包含订单信息和折扣信息。我们想要找出订单总额大于折扣金额的订单。
SELECT orders.order_id, orders.total_amount, discounts.discount_amount
FROM orders, discounts
WHERE orders.total_amount discounts.discount_amount;在这个查询中使用了 WHERE 子句来指定连接条件即 orders.total_amount discounts.discount_amount。这表示只有在 orders 表中的 total_amount 值大于 discounts 表中的 discount_amount 值时才会返回匹配的行。
自连接Self Join指在同一个表内进行连接操作。在自连接中我们将表视为两个不同的实体并使用相同表的别名来建立连接关系。
假设我们有一个表 employees其中包括员工的姓名和直接上级的员工ID。我们想要获取每个员工及其直接上级的姓名。
SELECT e.employee_name, m.employee_name AS manager_name
FROM employees e
INNER JOIN employees m ON e.manager_id m.employee_id;在这个查询中我们使用了别名 e 和 m 来表示同一个表 employees 的两个实体。我们通过连接条件 e.manager_id m.employee_id 来建立员工与其直接上级员工的连接关系。
左连接LEFT JOIN返回左表的所有行以及与右表满足连接条件的匹配行。如果右表中没有与左表匹配的行则对应的右表列将显示为 NULL。
SELECT *
FROM table1
LEFT JOIN table2 ON table1.column table2.column;右连接RIGHT JOIN返回右表的所有行以及与左表满足连接条件的匹配行。如果左表中没有与右表匹配的行则对应的左表列将显示为 NULL。
SELECT *
FROM table1
RIGHT JOIN table2 ON table1.column table2.column;全连接FULL JOIN返回左表和右表的所有行并将它们组合在一起。如果某行在其中一个表中有匹配而在另一个表中没有匹配对应的列将显示为 NULL。
SELECT *
FROM table1
FULL JOIN table2 ON table1.column table2.column;上述1、2、3、4均为内连接5、6、7均为外连接。
内连接与外连接的区别是 匹配方式不同内连接Inner Join基于连接条件匹配两个表中的行只返回满足连接条件的匹配行。而外连接Outer Join则返回符合连接条件的匹配行以及未能匹配到的行。结果集不同内连接仅返回连接条件匹配的行因此结果集中只包含共有的行。外连接则可以返回连接条件匹配的行也可以返回某个表中没有匹配项的行。表的顺序内连接和外连接的表的顺序可以影响结果集。对于内连接来说调换连接顺序不会改变最终结果。而对于外连接来说左外连接和右外连接的表的顺序会影响返回结果。使用场景不同内连接适用于需要获取两个表中相互匹配的数据的情况例如获取员工和部门信息的内连接查询。而外连接常用于需要显示关联表中所有数据包括没有匹配的数据的情况例如获取所有员工及其薪水信息的左外连接查询。 MySQL子查询
子查询Subquery是指在一个查询语句中嵌套另一个完整的查询语句。子查询可以在主查询的条件、列列表或者其他子查询中使用用于提供额外的数据或者限制结果集。
WHERE子句中的子查询 在WHERE子句中使用子查询可以作为条件来过滤主查询的结果集。子查询通常会返回一个值或者一组值用于与主查询的条件进行比较。
例如我们可以使用子查询来找出超过平均年龄的员工
SELECT employee_name, age
FROM employees
WHERE age (SELECT AVG(age) FROM employees);在这个查询中子查询 (SELECT AVG(age) FROM employees) 返回了员工表中年龄的平均值然后将其与主查询中的每个员工的年龄进行比较以筛选出年龄超过平均值的员工。
FROM子句中的子查询 在FROM子句中使用子查询可以将子查询作为临时表或视图来处理。子查询会被执行并将其结果作为一个临时表供主查询使用。
假设我们有两个表orders包含订单信息和 customers包含客户信息。我们想要找出每个客户的订单数量。
SELECT c.customer_id, c.customer_name, o.order_count
FROM customers c
JOIN (SELECT customer_id, COUNT(*) AS order_countFROM ordersGROUP BY customer_id
) o ON c.customer_id o.customer_id;在这个查询中内部的子查询 (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id) 返回了每个客户的订单数量。然后将该子查询作为临时表 o并与 customers 表进行连接操作。
select列表中的子查询
可以在SELECT列表中使用子查询来检索特定的数据并将其作为一个列返回。
例如我们可以使用子查询来计算每个客户的订单数量并将其作为一个新的列返回
SELECT customer_id, (SELECT COUNT(*) FROM orders WHERE customer_id customers.customer_id) AS order_count
FROM customers;在这个查询中子查询 (SELECT COUNT(*) FROM orders WHERE customer_id customers.customer_id) 会计算每个客户的订单数量并将其作为名为 order_count 的新列返回。 MySQL部分操作符
Union
UNION 是一个用于合并两个或多个 SELECT 语句结果集的操作符。它将多个 SELECT 查询的结果合并为一个单一的结果集且去除重复的行。
合并两个结果集 可以使用 UNION 操作符合并两个具有相同列数和数据类型的结果集。
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;这个查询将会合并 table1 和 table2 的结果集并返回一个包含两个表的唯一行的结果集。
去除重复行 UNION 操作符会自动去除重复的行只返回唯一的行。
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;这个查询将会合并 table1 和 table2 的结果集并返回一个包含唯一值的结果集。
结果集列的顺序和数据类型 UNION 操作要求两个 SELECT 查询的结果集具有相同的列数、列的顺序和数据类型。如果不满足这些要求可以使用显式的列别名来调整列的顺序或进行数据类型转换。
SELECT column1 AS name, column2 AS age FROM table1
UNION
SELECT column3 AS name, CAST(column4 AS INT) AS age FROM table2;这个查询会将 table1 和 table2 的结果集合并并使用别名来调整列的顺序和进行数据类型转换。
注意 使用 UNION 操作符时需要确保两个或多个 SELECT 查询的结果集具有相同的列数、列的顺序和数据类型。同时需要 UNION 操作是对结果集进行去重的如果需要包含重复行可以使用 UNION ALL 操作符。
Limit
LIMIT 关键字用于限制 SQL 查询返回的结果行数量。它可以与 SELECT 语句一起使用以便从结果集中选择指定数量的行。
返回前几行 使用 LIMIT 可以返回查询结果中的前几行。
SELECT column1, column2 FROM table1 LIMIT 5;这个查询会返回 table1 中的前 5 行数据。
跳过前几行并返回接下来的行 除了限制返回的行数还可以通过使用 OFFSET 子句来跳过前几行。
SELECT column1, column2 FROM table1 LIMIT 10 OFFSET 5;这个查询会跳过 table1 的前 5 行并返回接下来的 10 行数据。
简化的写法 可以直接使用逗号分隔的两个参数而不是使用 OFFSET 子句。
SELECT column1, column2 FROM table1 LIMIT 5, 10;这个查询与前一个示例中的查询是等效的会跳过 table1 的前 5 行并返回接下来的 10 行数据。
含有操作符的SQL查询语句模板
select
...
from
...
where
...
group by
...
having
...
order by
...
limit
...MySQL之增删改查
表的创建
CREATE TABLE 语句用于在数据库中创建新的数据表并定义表的列以及相关的属性。
CREATE TABLE table_name (column1 data_type,column2 data_type,column3 data_type,...PRIMARY KEY (column_name)
);其中table_name 是要创建的表的名称column1, column2, column3 是表中的列名data_type 是列的数据类型。
如果某一列是主键可以使用 PRIMARY KEY 子句指定例如 PRIMARY KEY (column_name)。
以下是一个示例创建一个名为 customers 的表具有 id、name 和 email 三个列并将 id 列作为主键
CREATE TABLE customers (id INT PRIMARY KEY,name VARCHAR(50),email VARCHAR(100)
);上述语句将在数据库中创建一个名为 customers 的表该表包含一个整数类型的 id 列、一个最大长度为 50 的字符串类型的 name 列和一个最大长度为 100 的字符串类型的 email 列并将 id 列设为主键。
常见的数据类型
INT整数用于表示整数值如 1、-5、100。
FLOAT浮点数用于表示带有小数的数值如 3.14、-0.5、2.0。
DECIMAL高精度小数用于表示具有高精度小数的数值适用于需要更精确计算的场景。
VARCHAR可变长度字符串用于表示可变长度的字符串可以存储不同长度的文本数据。
CHAR固定长度字符串用于表示固定长度的字符串存储时会将字符串补齐到指定长度适用于长度固定的数据。
BOOLEAN布尔值用于表示真或假的值通常用于存储逻辑判断结果。
DATE日期用于表示日期 它通常以 YYYY-MM-DD 的格式表示。 如 ‘2023-01-11’。
TIME时间用于表示时间如 ‘15:30:00’。
DATETIME用于表示精确的时间 它通常以 YYYY-MM-DD HH:MM:SS 的格式表示。 如’2023-01-11 15:30:00’。
DATETIME/TIMESTAMP日期时间戳用于表示日期和时间的组合包括日期、时间和时区。
BLOB二进制大对象用于存储二进制数据如图像、音频、视频等非文本数据。
表的删除
DROP TABLE 语句用于从数据库中永久删除现有的表。
DROP TABLE table_name;其中table_name 是要删除的表的名称。
INSERT插入数据
INSERT INTO 语句用于将新行或记录插入到数据库表中。
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);其中table_name 是要插入数据的表的名称。在括号中指定要插入数据的列名如果不是插入所有列和对应的值。
以下是一个示例向名为 customers 的表插入数据
INSERT INTO customers (name, email, phone) VALUES (John Doe, johnexample.com, 1234567890);上述语句将向 customers 表中插入一条新记录包含 name、email 和 phone 列的数据。
如果要一次性插入多行数据可以使用多个 VALUES 子句如下所示
INSERT INTO customers (name, email, phone) VALUES (John Doe, johnexample.com, 1234567890), (Jane Smith, janeexample.com, 9876543210);上述语句将向 customers 表中同时插入两条新记录。
**注意**插入日期类型DATE的数据时不可使用单引号将日期值括起来。相反应该按照特定的日期格式直接提供日期值。
以下是一个示例向名为 orders 的表插入日期数据
INSERT INTO orders (order_date) VALUES (2023-01-11);在上述示例中我们将日期值 2023-01-11 直接插入到 order_date 列中。不需要使用单引号将日期值括起来。
update更新语句
UPDATE语句用于更新数据库表中的数据。它允许您指定要更新的表、列和新值以及一个可选的WHERE子句来限制更新的行。
UPDATE 表名
SET 列名1 新值1, 列名2 新值2, ...
WHERE 条件;其中表名 是要更新的表的名称列名1, 列名2, ... 是要更新的列的名称新值1, 新值2, ... 是对应列的新值条件 是可选的WHERE子句用于限制更新的行。
例如假设我们有一个名为 customers 的表其中包含列 name、age 和 email。如果要将名为 “John” 的客户的年龄更新为 30 岁可以使用以下UPDATE语句
UPDATE customers
SET age 30
WHERE name John;delete删除语句
DELETE语句用于从数据库表中删除行。它允许您指定要删除的表和一个可选的WHERE子句来限制删除的行。
DELETE FROM 表名
WHERE 条件;其中表名 是要删除的表的名称条件 是可选的WHERE子句用于限制删除的行。
例如假设我们有一个名为 customers 的表其中包含列 name、age 和 email。如果要删除名为 “John” 的客户的记录可以使用以下DELETE语句
DELETE FROM customers
WHERE name John;上述语句将在 customers 表中找到名为 “John” 的行并将其删除。
注意 如果没有指定WHERE子句DELETE语句将会删除表中的所有行导致表被完全清空。
表的复制
流程如下使用SELECT语句选择要复制的原始表中的数据并将其插入到新创建的表中。 使用CREATE TABLE语句创建一个新的目标表定义列和数据类型。 使用INSERT INTO语句和SELECT语句的组合从原始表中选择数据并将其插入到新的目标表中。
例如假设我们有一个名为 employees 的表并且我们想要创建一个名为 employees_copy 的表来复制所有员工的信息。我们可以按照以下步骤进行操作
使用CREATE TABLE语句创建新的目标表 employees_copy与原始表 employees 相同的列和数据类型
CREATE TABLE employees_copy (id INT,name VARCHAR(50),department VARCHAR(50),salary DECIMAL(10, 2)
);使用INSERT INTO和SELECT语句的组合从原始表 employees 中选择数据并将其插入到新的目标表 employees_copy 中
INSERT INTO employees_copy (id, name, department, salary)
SELECT id, name, department, salary
FROM employees;MySQL约束
在数据库中约束constraints用于定义和强制表中数据的完整性规则。它们可以确保插入、更新或删除数据时的数据一致性和有效性。
以下是几种常见的约束类型
主键约束Primary Key Constraint主键约束用于唯一标识表中的每一行并防止重复数据。它要求指定一个或多个列作为主键并保证其唯一性和非空性。
当某两行的记录是完全一致时可通过主键值来区分它们。 唯一约束Unique Constraint唯一约束确保指定的列或列组合中的值是唯一的但允许空值。 外键约束Foreign Key Constraint外键约束用于定义表与其他表之间的关系。它要求一个或多个列的值必须匹配关联表中的主键或唯一键值。
假设我们有两个表orders订单和 customers客户。每个订单都关联到特定的客户我们可以使用外键约束来确保订单表中的 customer_id 列的值必须在客户表中存在。
首先我们需要在客户表中定义主键约束以唯一标识每个客户的记录
CREATE TABLE customers (customer_id INT PRIMARY KEY,customer_name VARCHAR(50)
);接下来在订单表中我们使用外键约束来指定 customer_id 列引用了客户表的主键 customer_id
CREATE TABLE orders (order_id INT PRIMARY KEY,order_date DATE,customer_id INT,FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);上述示例中外键约束指定了 orders 表的 customer_id 列引用了 customers 表的主键 customer_id。这意味着当我们在订单表中插入一条新记录时数据库会验证 customer_id 的值是否存在于客户表中。如果不存在插入操作将被拒绝。
非空约束Not Null Constraint非空约束要求指定的列在插入或更新时不能包含空值。默认约束Default Constraint默认约束定义了当未明确提供值时应该使用的默认值。检查约束Check Constraint检查约束定义了在插入或更新操作中所允许的值范围或条件。
这些约束可以在创建表时指定也可以通过ALTER TABLE语句添加到现有表中。例如以下是一个示例展示了如何在创建表时定义主键约束和唯一约束
CREATE TABLE students (id INT PRIMARY KEY,name VARCHAR(50) NOT NULL,email VARCHAR(100) UNIQUE,age INT,CONSTRAINT check_age CHECK (age 0)
);在上述示例中students 表具有一个名为 id 的主键列name 列被定义为非空的email 列具有唯一约束age 列使用检查约束确保其值大于等于0。
联合唯一约束要求指定的多个列的组合值在表中是唯一的。
示例
CREATE TABLE employees (id INT,first_name VARCHAR(50),last_name VARCHAR(50),department VARCHAR(50),CONSTRAINT uq_employee_name_dept UNIQUE (first_name, last_name, department)
);上述示例中的 employees 表定义了一个联合唯一约束 uq_employee_name_dept它包含了 first_name、last_name 和 department 这三个字段。这表示在表中不允许相同的 first_name、last_name 和 department 组合出现多次。
注意 每个字段单独的值可以不是唯一的但是它们的组合值必须是唯一的。 MySQL事务
事务是数据库管理系统中的一种机制用于确保数据库操作的一致性和完整性。它将一系列的数据库操作例如插入、更新或删除作为一个单独的执行单元要么全部成功地执行要么全部回滚到事务开始前的状态从而保证了数据的一致性。
举个例子当一个用户从银行账户A转账给账户B时涉及到多个数据库操作比如减少账户A的余额和增加账户B的余额。这个转账过程可以作为一个事务来处理。
在这个例子中事务的执行过程如下 开始事务。 查询账户A的余额并检查是否足够支付转账金额。 如果足够支付更新账户A的余额减去转账金额。 查询账户B的余额。 更新账户B的余额增加转账金额。 提交事务将所有操作永久保存到数据库中。 如果在执行过程中出现任何错误比如账户余额不足或数据库连接失败事务将回滚到开始前的状态。也就是说账户A的余额不会减少账户B的余额也不会增加。这样可以确保转账操作要么完全成功要么完全失败避免了数据不一致的情况。
常用的事务语句包括
BEGIN/START TRANSACTION开始一个事务。这个语句标志着一个新的事务的开始。
COMMIT提交事务。这个语句将所有对数据库的修改保存到数据库中并结束当前的事务。
ROLLBACK回滚事务。这个语句将撤销事务中的所有修改并结束当前的事务。
SAVEPOINT设置一个保存点。这个语句允许在事务中设置一个保存点以便在后续的操作中能够回滚到这个保存点。
RELEASE SAVEPOINT释放保存点。这个语句用于释放一个事务中设置的保存点。
START TRANSACTION;执行一系列的数据库操作
事务具有四个基本的特性通常被称为 ACID 特性 原子性Atomicity事务被视为一个不可分割的原子单位。事务中的所有操作要么全部成功执行要么全部回滚到事务开始前的状态保证数据库的一致性。一致性Consistency事务在执行之前和执行之后数据库都必须保持一致的状态。这意味着事务必须遵循预定义的规则和约束以确保数据的完整性和有效性。隔离性Isolation多个事务同时执行时每个事务都应该与其他事务相互隔离互不干扰。隔离性确保了每个事务都能访问被其它事务修改之前的数据并防止并发操作引起的数据不一致问题。持久性Durability一旦事务提交成功其所做的修改将被永久保存到数据库中即使出现系统崩溃或断电等故障情况也能够保证数据的持久性。 隔离性涉及到事务隔离级别
事务隔离级别是指多个并发事务之间相互隔离的程度用于控制并发操作可能导致的数据不一致性和并发冲突的问题。关系型数据库通常支持以下四个标准的事务隔离级别 读未提交Read Uncommitted最低级别的隔离级别。在该级别下一个事务可以读取到另一个事务尚未提交的数据。这样会导致脏读Dirty Read问题即读取到了未经验证的、可能被回滚的数据。该级别的并发性高但数据完整性较差。读已提交Read Committed在该级别下一个事务只能读取到已经提交的数据。这样避免了脏读问题但依然存在不可重复读Non-repeatable Read问题。不可重复读指的是在同一事务内对同一记录进行多次读取而得到的结果却不一致。因为在读取期间其他事务可能已经修改了同一条记录。可重复读Repeatable Read在该级别下事务执行期间保证多次读取同一记录时结果始终一致。为达到该目的数据库会对读取的数据进行加锁防止其他事务对其进行修改。这样解决了不可重复读问题但仍存在幻读Phantom Read问题。幻读指的是在同一事务中对同一范围的查询但得到的结果集却不一致。幻读通常是由于其他事务插入或删除了符合查询条件的数据造成的。串行化Serializable最高级别的隔离级别。该级别下事务完全串行执行避免了脏读、不可重复读和幻读的问题。虽然能够确保最高的数据一致性但并发性最差通常会导致较低的系统性能。 MySQL索引
索引是数据库中用于提高数据检索效率的数据结构。它类似于书籍的目录通过创建特定的数据结构来快速定位和访问数据库中的数据。
索引可以在一个或多个列上创建并根据特定的算法和数据结构存储索引数据。当执行查询时数据库系统可以使用索引来快速定位符合查询条件的数据而不必扫描整个数据库表。
假设我们有一个名为学生的数据库表其中包含了学生的信息如学生ID、姓名、年龄和成绩等字段。我们需要频繁地根据学生ID进行查询学生的信息。
如果没有创建任何索引每次执行这样的查询时数据库系统将会扫描整个学生表来找到相应的学生记录。当数据量庞大时这将会消耗大量的时间和资源。
但是如果我们在学生ID字段上创建了一个B树索引数据库系统将会在创建索引时对学生ID进行排序并构建一颗平衡的B树结构。这样当执行查询时系统只需在B树中搜索对应的学生ID就能快速定位到相关的学生记录而不必扫描整个表。
常见的索引类型包括
B树索引B-Tree Index是最常见和广泛使用的索引类型。B树索引适用于等值查找和范围查找可以提供较好的查询性能。它对索引列进行排序并构建一颗平衡的B树支持高效的数据插入、更新和删除操作。
哈希索引Hash Index基于哈希函数的索引结构。哈希索引适用于等值查找将索引列的值哈希到索引桶中可以直接定位到对应的数据。但是哈希索引不适合范围查找或排序操作。
全文索引Full-Text Index针对文本内容的索引。全文索引可以对文本数据进行快速的全文搜索支持模糊匹配、关键字搜索和语义查询等功能。
空间索引Spatial Index用于处理包含地理位置或空间数据的索引。空间索引可以加速地理位置相关的查询如范围查找、邻近搜索和交集计算等。
索引的创建
选择要创建索引的表和字段通常是根据查询频率较高或经常用于连接操作的字段。
使用CREATE INDEX语句创建索引。语法如下
CREATE [UNIQUE] INDEX index_name
ON table_name (column_name1, column_name2, ...)UNIQUE关键字可选表示创建唯一索引保证索引列的值唯一。
index_name为索引的名称可以根据实际需要进行命名。
table_name为要创建索引的表名。
column_name1, column_name2, …为要创建索引的字段名。
索引的删除
使用DROP INDEX语句删除索引。语法如下
DROP INDEX index_name
ON table_nameindex_name为要删除的索引名称。
table_name为索引所属的表名。
假设有一个名为users的表包含以下字段
id (主键)、name、email、age
现在我们想要对email字段创建一个索引。 我们可以使用以下语句在users表的email字段上创建一个非唯一索引
CREATE INDEX idx_email ON users (email);这将在users表的email字段上创建名为idx_email的索引。 如果我们想要删除刚刚创建的索引可以使用以下语句进行操作
DROP INDEX idx_email ON users;这将删除users表上名为idx_email的索引。
索引在以下情况下可能会失效
不满足索引字段的顺序
当查询条件中的索引字段不按照索引的顺序出现时索引可能无法被利用导致失效。
例如如果索引是 (first_name, last_name)但查询条件中只提供了 last_name那么索引将无法发挥作用。
使用函数或表达式对索引字段进行操作
如果查询中对索引字段使用函数、表达式或计算操作索引可能无法被利用。
例如如果索引是 LOWER(email)但查询中使用了 WHERE UPPER(email) ABC索引就无法被使用。
模糊查询通配符开头的查询
以通配符 % 开头的模糊查询如 LIKE %abc通常无法利用索引因为无法确定索引的起始点。
另外如果模糊查询的通配符位于字符串的开始位置如 LIKE abc%某些情况下也无法使用索引。 MySQL视图
视图View是在数据库中基于查询结果定义的虚拟表其内容由一个或多个基本表或其他视图的查询结果决定。视图并不直接存储数据而是根据查询的定义在需要时动态生成结果。
通过创建视图可以隐藏底层表结构的复杂性并提供一种简化和抽象的方式来访问和操作数据。以下是一些使用视图的好处
简化复杂查询通过创建一个视图可以对多个表进行联接、过滤、排序等操作将复杂的查询逻辑封装到视图中使用户可以使用简单的查询来获取所需数据。数据安全性视图可以用来限制对表的访问权限只向用户提供必要的数据保护敏感信息的安全性。通过授权机制可以控制用户对视图的查询和修改权限而无需直接访问底层表。重用和模块化通过创建视图可以将常用查询的定义保存下来方便在其他查询中复用。这样可以减少代码冗余提高开发效率和可维护性。数据逻辑独立性视图可以对表结构进行抽象和封装当底层表的结构发生变化时只需要修改视图的定义而无需修改依赖于视图的应用程序或查询。
创建视图
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table1
WHERE condition;通过以上语句可以创建名为 view_name 的视图该视图基于 table1 表并根据指定的条件筛选出需要的数据列。
删除视图
DROP VIEW [IF EXISTS] view_name;其中
view_name 是要删除的视图的名称。
IF EXISTS 是可选的关键字用于在视图不存在时避免产生错误。
MySQL中导入和导出数据库
导出数据库
导出整个数据库
mysqldump -u username -p database_name dump_file.sql这将导出名为database_name的整个数据库并将其保存到名为dump_file.sql的SQL脚本文件中。在运行此命令后系统会要求输入密码。
导出特定表
mysqldump -u username -p database_name table1 table2 dump_file.sql这将只导出数据库database_name中的table1和table2两个表并将结果保存到dump_file.sql文件中。
导入数据库
导入整个数据库
mysql -u username -p database_name dump_file.sql这将从dump_file.sql文件中读取SQL命令并执行这些命令以导入整个数据库。在运行此命令后系统会要求输入密码。
导入特定表 如果只想导入特定的表而不是整个数据库可以使用以下命令
mysql -u username -p database_name dump_file.sql --tables table1 table2这将只导入dump_file.sql文件中指定的table1和table2两个表。
username为MySQL用户名password为MySQL密码database_name是要导入或导出的数据库的名称而dump_file.sql是指定导入或导出的SQL脚本文件的名称。 MySQL设计的三个范式
数据库设计中的三个范式通常称为关系数据库的三个范式用于规范化数据库模式以减少冗余和数据异常。
第一范式1NF确保每个列都具有原子性值不可再分割。
每个表中的每个字段都包含一个原子值不可再分割成更小的部分。 避免在同一列中存储多个值使用多个字段或新表来分隔重复的数据。
第二范式2NF确保非主键列完全依赖于主键而不是部分依赖。
数据库表必须符合第一范式。 非主键列必须完全依赖于主键而不是仅依赖于主键的部分。 如果一个表中存在组合主键那么非主键列必须依赖于全部组合主键而不仅仅是其中的一部分。
第三范式3NF确保非主键列之间没有传递依赖关系。 数据库表必须符合第二范式。 非主键列之间不能存在传递依赖关系即不能通过其他非主键列推导出某个非主键列的值。 如果存在传递依赖关系应该将其移到单独的表中。
举个例子假设有以下两个实体学生Students和课程Courses。
第一范式1NF 在第一范式中我们需要确保每个字段都包含原子值。假设每个学生可以选择多门课程并且每门课程可能有多个学分和多个教师。在这种情况下我们可以将学生和课程的关系拆分成以下表格
学生表Students
学生ID姓名年龄1张三202李四193王五21
课程表Courses
课程ID课程名称学分教师1数学4张老师2英语3王老师3物理5李老师
第二范式2NF 在第二范式中非主键列必须完全依赖于主键而不是部分依赖。在上述示例中假设我们的主键是学生ID和课程ID并且每个学生在每门课程中的成绩是独立存储的。那么我们可以拆分成以下表格
学生表Students
学生ID姓名年龄1张三202李四193王五21
课程表Courses
课程ID课程名称学分教师1数学4张老师2英语3王老师3物理5李老师
成绩表Grades
学生ID课程ID成绩11901285219532883392
第三范式3NF 在第三范式中我们需要确保非主键列之间没有传递依赖关系。假设每位学生的年龄决定了学费折扣的等级。那么我们可以进一步拆分成以下表格
学生表Students
学生ID姓名年龄学费折扣等级1张三2022李四1913王五213
课程表Courses
课程ID课程名称学分教师1数学4张老师2英语3王老师3物理5李老师
成绩表Grades
学生ID课程ID成绩11901285219532883392
学费折扣表FeeDiscounts
学费折扣等级折扣率10.820.730.6 写在最后
本文内容均为重点知识点是学习MySQL的不二选择。学习不是一蹴而就的过程切勿囫囵吞枣。
我是秋说我们下次见。
