mysql笔记
2024/5/5
数据定义语言(DDL)、数据查询语言(DQL)、数据操纵语言(DML)、数据控制语言(DCL)
- 数据定义语言(DDL): DDL 用于定义和修改数据库的结构。它包括创建、修改和删除数据库对象(如表、索引、视图、同义词、聚簇和模式)的语句。常见的 DDL 语句包括 CREATE, ALTER, 和 DROP。
CREATE
:用于创建新的数据库对象,如表、索引或视图。ALTER
:用于修改现有数据库对象的结构或特性。DROP
:用于删除数据库对象。
- 数据查询语言(DQL): DQL 是用来检索、查询数据库中数据的语句。它允许用户指定要查询的数据以及查询条件。SQL 中最常用的 DQL 是 SELECT 语句,它用于从数据库表中选取数据。
SELECT
:用于从数据库中检索数据。
- 数据操纵语言(DML): DML 用于对数据库中的数据进行插入、更新、删除和检索操作。DML 语句允许用户改变数据库中的数据,但不改变数据库的结构。
INSERT
:用于向表中添加新的数据行。UPDATE
:用于修改表中的现有数据。DELETE
:用于从表中删除数据。SELECT
:虽然 SELECT 也属于 DML,因为它可以用于检索数据,但它通常被归类为 DQL,因为它不修改数据。
- 数据控制语言(DCL): DCL 用于定义数据库的安全策略和权限。它包括设置数据库中数据的访问控制和权限的语句。DCL 的主要目的是保护数据库的安全性和完整性。
GRANT
:用于给用户授权,如访问或者修改数据库中的数据。REVOKE
:用于撤销用户的权限。
# 关于测试
# 数据库、数据表、数据的操作
#
# 对库的操作
SHOW DATABASES;
drop database zsy_system_zz;
drop database zsy_1385531;
create database Kh62;
drop database Kh62;
use my_ku;
# 对表的操作
# 创建一个表
create table kh62(
id int auto_increment primary key ,
name varchar(32) not null ,
passwd varchar(32) not null ,
birthdate date
)character set utf8mb4 collate utf8mb4_general_ci;
create
# 删除
# drop table kh62;
# 改表的名字
alter table kh62 rename my_kh62_123;
# 添加一列
alter table my_kh62_123 add column test varchar(32) not null ;
# 删除一列
alter table my_kh62_123 drop column test;
#修改列名(并且可以同时修改类型,必须写原数据类型------可以不修改,但必须写)
alter table my_kh62_123 change column passwd1 passwd varchar(32);
# 修改列的数据类型(可以用change,也可以用modify)
alter table my_kh62_123 modify column name varchar(16);
#查看表结构
desc my_kh62_123;
# 设置列的默认值
alter table my_kh62_123 alter name set default 'admin';
# 测试
# alter table my_kh62_123 modify column birthdate timestamp;
# alter table my_kh62_123 alter column birthdate set default current_timestamp;
alter table my_kh62_123 modify column name varchar(16) not null ;
# 主键只有一个,但可以设置复合主键(删除原有的,增添复合)
# 修改自增属性
alter table my_kh62_123 modify id int not null ;
# 删除主键
alter table my_kh62_123 drop primary key ;
# 添加复合主键
alter table my_kh62_123 add primary key (id,name);
alter table my_kh62_123 modify id int not null auto_increment;
desc my_kh62_123;
# 对数据的操作
insert into my_kh62_123 (name, passwd) values ('张三','123456');
select * from my_kh62_123;
2024/5/6—增删改
对数据的增改删操作
# 对数据的操作
########插入insert关键字#########
insert into my_kh62_123 (name, passwd) values ('张三','123456');
insert into my_kh62_123 values (null,'李四','123456',null);
# 插多个
insert into my_kh62_123 values (null,'王五','123',null),
(null,'李兴','123456',null);
##########删除delete关键字#########
# 删除所有
delete from my_kh62_123;
#删除条件
delete from my_kh62_123 where name='王五';
# truncate table my_kh62_123;
############修改UPDATE 命令来操作##########
# 单个值
update my_kh62_123 set name = '哈哈' where name = '张三';
# 多个值
update my_kh62_123 set name='我的世界',passwd='4545' where name='李四';
查询操作
简单查询
-- 选择所有列的所有行
SELECT * FROM users;
-- 选择特定列的所有行
SELECT username, email FROM users;
-- 添加 WHERE 子句,选择满足条件的行
SELECT * FROM users WHERE is_active = TRUE;
-- 添加 ORDER BY 子句,按照某列的升序排序
SELECT * FROM users ORDER BY birthdate;
-- 添加 ORDER BY 子句,按照某列的降序排序
SELECT * FROM users ORDER BY birthdate DESC;
-- 添加 LIMIT 子句,限制返回的行数
SELECT * FROM users LIMIT 10;
-- 使用 AND 运算符和通配符
SELECT * FROM users WHERE username LIKE 'j%' AND is_active = TRUE;
-- 使用 OR 运算符
SELECT * FROM users WHERE is_active = TRUE OR birthdate < '1990-01-01';
-- 使用 IN 子句
SELECT * FROM users WHERE birthdate IN ('1990-01-01', '1992-03-15', '1993-05-03');
where
操作符 | 描述 | 实例 |
---|---|---|
= | 等号,检测两个值是否相等,如果相等返回true | (A = B) 返回false。 |
<>, != | 不等于,检测两个值是否相等,如果不相等返回true | (A != B) 返回 true。 |
> | 大于号,检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true | (A > B) 返回false。 |
< | 小于号,检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true | (A < B) 返回 true。 |
>= | 大于等于号,检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true | (A >= B) 返回false。 |
<= | 小于等于号,检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true | (A <= B) 返回 true。 |
like
- 百分号通配符 %:
% 通配符表示零个或多个字符。例如,‘a%’ 匹配以字母 ‘a’ 开头的任何字符串。
SELECT * FROM customers WHERE last_name LIKE 'S%';
以上 SQL 语句将选择所有姓氏以 ‘S’ 开头的客户。
- 下划线通配符 _:
_ 通配符表示一个字符。例如,‘_r%’ 匹配第二个字母为 ‘r’ 的任何字符串。
SELECT * FROM products WHERE product_name LIKE '_a%';
多表查询
多表查询:指的是在一个查询语句中同时操作多个表。这通常用于需要从多个表中联合提取数据的情况。多表查询可以通过几种不同的方式实现,包括内连接(INNER JOIN)、外连接(LEFT JOIN 或 RIGHT JOIN)、交叉连接(CROSS JOIN)以及使用子查询。
内连接(INNER JOIN)
#返回交集
SELECT column_names FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field
[INNER JOIN table3
ON table2.common_field = table3.common_field];
-- 可以继续添加更多的 INNER JOIN 来链接更多的表;
外连接(LEFT JOIN 或 RIGHT JOIN)
外连接会返回左表(LEFT JOIN)或右表(RIGHT JOIN)的所有行,即使右表或左表中没有匹配的行,它们用null代替。
-- 左外连接,返回左表所有行,右表匹配的行(若右没有已null代替)
SELECT column_names
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;
-- 右外连接,返回右表所有行,左表匹配的行(若左没有已null代替)
SELECT column_names
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;
交叉连接(CROSS JOIN)
交叉连接会返回两个表所有行的笛卡尔积,可能产生大量数据。
SELECT column_names
FROM table1
CROSS JOIN table2;
使用子查询
子查询允许在一个查询中嵌套另一个查询,可以用于复杂的数据选择和过滤。
SELECT * FROM table1
WHERE EXISTS (
SELECT 1
FROM table2
WHERE table2.common_field = table1.common_field
);
2024/5/7—分组
GROUP BY
GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。GROUP BY 语句是 SQL 查询中用于汇总和分析数据的重要工具,尤其在处理大量数据时,它能够提供有用的汇总信息。
SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id;
HAVING
HAVING
子句在 SQL 中用于对 GROUP BY
子句生成的分组结果进行条件过滤。它与 WHERE
子句类似,但 WHERE
子句在数据分组前对每行进行过滤,而 HAVING
子句则在数据分组后对每个分组进行过滤。HAVING
经常与聚合函数一起使用,以便对聚合结果设定条件。
SELECT column1, SUM(column2)
FROM table_name
GROUP BY column1
HAVING SUM(column2) > value;
执行顺序:
- FROM:
- 这是查询执行的起点,数据库系统首先访问FROM子句中指定的表或派生表。
- JOIN:
- 如果查询中包含JOIN操作,那么在FROM之后,JOIN操作会被执行。JOIN操作会基于指定的连接条件将多个表组合成一个结果集。
- WHERE:
- 之后执行WHERE子句,它对JOIN操作的结果进行筛选,只保留满足条件的行。
- GROUP BY:
- WHERE子句之后,如果查询中包含GROUP BY子句,那么数据库会对WHERE子句的结果进行分组,以便进行聚合操作。
- HAVING:
- HAVING子句通常用在GROUP BY之后,它对分组后的结果进行进一步的筛选,与WHERE子句类似,但HAVING是针对分组后的数据进行筛选。
- SELECT:
- 然后执行SELECT子句,它指定了需要返回的列或者表达式。如果使用了聚合函数,那么聚合计算在这一步完成。
- DISTINCT:
- 如果查询中包含DISTINCT关键字,那么在SELECT之后,数据库会对结果集进行去重操作。
- ORDER BY:
- 之后执行ORDER BY子句,它对结果集进行排序。
- LIMIT:
- 最后,如果查询中包含LIMIT子句,它将用来限制返回的行数。
- 子查询和派生表:
- 对于包含子查询或派生表(即在FROM子句中通过子查询或WITH子句定义的表)的查询,子查询或派生表的执行通常优先于主查询的其他部分。
2024/5/8----视图
视图
视图(View)是一个虚拟的表,其内容由SQL查询结果定义。
视图可以简化用户操作,使得用户不必处理复杂的SQL查询,尤其是当需要重复使用相同的查询时。
#创建
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
#查询
SHOW CREATE VIEW view_name;
SELECT * FROM view_name;
#删除
DROP VIEW IF EXISTS view_name;
#增删改(底层)
#可以对它们进行INSERT、UPDATE或DELETE操作。但请注意,这些更改将直接影响到视图所基于的底层表中的数据
#注意不能与WHERE condition 的条件违背
#修改
CREATE OR REPLACE VIEW view_name AS ....(新语句);
alter view view_name as ....(新语句);
存储过程
存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集合,它被存储在数据库中,可以通过调用其名称来执行——————(语句封装,重用)。
可以带有参数,支持输入、输出以及不定量的返回结果
DELIMITER // <----这不是注释呦
CREATE PROCEDURE procedure_name (param1 TYPE, param2 TYPE, ...)
BEGIN
-- SQL语句
-- ...
END // <----这不是注释呦
DELIMITER ;
#调用
CALL procedure_name(param1, param2, ...);
#删除
DROP PROCEDURE [IF EXISTS] procedure_name;
系统变量
#系统变量
#查看当前的系统变量及其值
SHOW [global | session]VARIABLES;
#全局变量
SET GLOBAL max_connections = 200;
#会话变量
SET max_connections = 200;
#查看值
SELECT @@global.max_connections;
SELECT @@session.max_connections;
用户定义变量
#用户定义变量(俩@是系统变量)
SET @variable_name = value;
SET @var1 = value1, @var2 = value2, ..., @varN = valueN;
SELECT @variable_name;//查看
局部变量
#局部变量
#局部变量的声明必须在存储过程、函数或触发器的BEGIN...END块内部进行,并且使用DECLARE关键字
DELIMITER //
CREATE PROCEDURE example_procedure()
BEGIN
DECLARE 变量名 数据类型;
#赋值
set ...
-- 其他SQL语句
END //
DELIMITER ;
IF
#IF 语句是一种条件控制流语句
IF condition THEN
-- 当条件为真时执行的代码
ELSEIF another_condition THEN
-- 当第一个条件不为真且第二个条件为真时执行的代码
...
ELSE
-- 如果所有条件都不为真时执行的代码
END IF;
参数
#MySQL中的参数可以是以下几种类型之一:
IN:输入参数,向存储过程或函数提供值。
OUT:输出参数,用于从存储过程或函数返回值。
INOUT:可以作为输入提供值,也可以作为输出返回值。
CREATE PROCEDURE CalculateTotalSalary(
IN emp_id INT,
OUT total_salary DECIMAL(10, 2)
)
CALL CalculateTotalSalary(123, @total_salary);
#这里 @total_salary 是一个用户定义的变量,用于接收存储过程的输出参数
CASE
#简单 CASE 表达式用于基于单个表达式的值来返回结果。
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
ELSE default_result
END
WHILE
WHILE condition DO
-- 循环体内的SQL语句
END WHILE;
REPEAT
do_while
REPEAT
-- 循环体内的SQL语句
UNTIL condition
END REPEAT;
LOOP
LOOP
-- 循环体内的SQL语句
-- 需要包含退出循环的逻辑,如LEAVE(类似break)语句
-- ITERATE(类似continue)
END LOOP;
2024/5/9----事务
事务
确保数据的完整性和一致性
一个事务可以包含一个或多个数据库操作(如插入、更新或删除),这些操作要么全部成功执行,要么全部不执行。
"事务开关"通常指的是控制事务自动提交行为的设置
单独的SQL语句都被视为一个事务
SET autocommit = 0; -- 关闭自动提交
SET autocommit = OFF;
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- 如果两个更新都成功,提交事务
COMMIT;
-- 如果有任何错误,撤销事务
ROLLBACK;
特性
- 原子性(Atomicity):事务中的所有操作要么全部完成,要么全部不完成。如果事务中的某个操作失败,整个事务将回滚到事务开始前的状态。
- 一致性(Consistency):事务必须使数据库从一个一致的状态转移到另一个一致的状态。这意味着在事务开始之前和事务结束之后,数据库的完整性约束都必须得到满足。
- 隔离性(Isolation):并发执行的事务需要被隔离开来,以防止数据不一致。不同的事务在执行期间不能相互干扰。
- 持久性(Durability):一旦事务提交,它对数据库的改变就是永久性的,即使系统发生故障也不会丢失。
索引
CREATE INDEX index_name ON table_name (column1, column2...);
CREATE UNIQUE INDEX index_name ON table_name (column_name);
数据库设计
java连接数据库
String url = "jdbc:mysql://localhost:3306/数据库?characterEncoding=UTF-8&autoReconnect=true";
String user = "root";
String password = "root";
Connection conn = null; //链接数据库
ResultSet rs = null;
PreparedStatement pstmt = null;
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
String select = "SELECT * FROM my_kh62_123";
pstmt = conn.prepareStatement(select);
// 执行查询
rs = pstmt.executeQuery();
while (rs.next()) {
// 获取并打印结果集中的数据
System.out.println(rs.getString("name"));
}