mysql笔记

最新推荐文章于 2024-08-18 15:09:09 发布

i酷就好

最新推荐文章于 2024-08-18 15:09:09 发布

阅读量330

点赞数 4

文章标签： mysql 笔记 oracle

本文链接：https://blog.csdn.net/family168/article/details/141123773

版权

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"));
  }