DDL(数据定义语言)
1、使用命令行连接MySQL（两种方式）【命令行提示符变为"mysql>",表示你已成功登录】
①命令：
mysql -u root -p+Enter键后输入密码
②命令：
mysql -u root -p+密码
2、DDL-操作数据库：【注意：不区分大小写，命令后必带分号】
①查看MySQL版本信息及当前用户
--select version(),user();
②创建数据库
--create database 数据库名;
③查看数据库列表/表
--show databases/tables;(显示表的时候必须先选择数据库->use 数据库名)
④选择数据库
--use 数据库名;
⑤删除数据库
--drop database 数据库名;
⑥退出：
exit;3、DDL- 操作数据表：【注意：不区分大小写，命令后必带分号】①显示当前数据库中所有的表--show tables;②创建新表--create table 表名(字段 字段类型（可加-约束，注释-comment）,字段 字段类型)【▲注意：①主键约束：primary key->②外键约束：foreign key->③自增长约束：auto_increment->④非空约束：not null->⑤唯一约束：unique key->⑥默认约束：default->⑦零填充约束：zerofill--自动补0，默认长度为10-> ⑧有无符号：signed/unsigned】③查看表的数据结构（设计）--desc 表名；【describe 表名】④查看表所创建的查询语句--show create table 表名；⑤增加字段/修改数据类型/修改字段名和字段类型/删除字段名/对表重命名--alter table 表名 add/modify/change/drop/rename to⑥删除表--drop 表名；
4、MySQL的存储引擎（常见的两种：InnoDB、MyISAM）
①InnoDB（默认）:支持事务处理、外键约束、数据行锁定，表空间较大。
②MyISAM（便于读取）:不支持事务处理、外键约束、数据行锁定，表空间较小。
--查询当前数据库支持的存储引擎：
show engines;--【也可查询默认的引擎】
--查看当前的默认存储引擎：
show variables like'%storage engine%'；
--查看某个表用了什么引擎：
show create table 表名;
--创建新表时指定存储引擎：
create table(...)engine=引擎;
--修改数据表引擎：
alter table student engine=引擎;
5、MySQL系统帮助【help 查询内容=help contents;】
6、SQL高级（一）：
1). 添加字段
ALTER TABLE 表名 ADD 字段名 类型 (长度) [COMMENT 注释 ] [ 约束 ];
2). 修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);
3). 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [COMMENT 注释 ] [ 约束 ];
4). 删除字段
ALTER TABLE 表名 DROP 字段名;
5). 修改表名
ALTER TABLE 表名 RENAME TO 新表名;
6). 删除表
DROP TABLE [ IF EXISTS ] 表名;
7). 删除指定表, 并重新创建表【注意: 在删除表的时候，表中的全部数据也都会被删除】
TRUNCATE TABLE 表名;
8).给表添加主键约束【▲注意：主外键名称可自定义】
ALTER TABLE 表名 add constraint 主键名 primary key 表名（主键字段）；
9).给表添加外键约束
ALTER TABLE 表名 add constraint 外键名 foreign key（外键字段）references 关联主表名（关联字段）；
10). 删除主外键
DROP TABLE 从表名 drop constraint 键名;
DML（数据操作语言）
1). 批量添加数据【 注意：INSERT INTO ... SELECT 语法，values可以省略】
①INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;
②INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;
将查询结果插入新表->【①条件：表必须已存在】
①INSERT INTO 新表(字段1，字段2，...)select 字段1，字段2，...from 原表；
②create table 新表(select 字段1，字段2，...from 原表);
2). 修改数据
UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ;
3). 删除数据【注意：自增继续保持】
DELETE FROM 表名 [ WHERE 条件 ] ;
turncate table 表名；DQL(数据查询语言)★常见的查询顺序【▲注意：组合->（value1,value2）=（value1,value2）等价于value1=value1 and value2=value2】▶①表名列表->FROM▶②条件列表->WHERE▶③分组字段列表->GROUP BY▶④分组后条件列表->HAVING▶⑤字段列表->SELECT▶⑥排序字段列表->ORDER BY▶⑦分页参数->LIMIT
1). 查询多个字段【▲注意 : * 号代表查询所有字段，在实际开发中尽量少用（不直观、影响效率）】
SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;
SELECT * FROM 表名 ;
2). 字段设置别名【▲ 注意 :As可以省略】
SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;
SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;
3). 去除重复记录（distinct）
SELECT DISTINCT 字段列表 FROM 表名
4). 分组查询【 添加分组，你必须确保 SELECT 列表中的每个非聚合列都出现在 GROUP BY 子句中，或者使用聚合函数】
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];where与having区别执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。判断条件不同：where不能对聚合函数进行判断，而having可以。注意事项:• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。• 执行顺序: where > 聚合函数 > having 。• 支持多字段分组, 具体语法为 : group by columnA,columnB。
5). 排序查询（默认升序）【如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 】
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;
6). 分页查询【OFFSET也称之位置偏移量】
①SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;
②SELECT 字段列表 FROM 表名 LIMIT查询记录数,OFFSET 起始索引;注意事项:• 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。
7). 联合查询【union,union all《将多次查询的结果和并起来》】
select 字段列表 from 表 A...
union[all]
select 字段列表 from 表 B...
【▲ 注意 :联合查询的多张表列数必须保持一致，字段类型也要一致
union all会将全部数据直接合并，union会对合并之后实现数据去重】
8).子查询基础(嵌套查询->多个select查询语句)【注意：比较运算符可以是order by或者group by，子查询必须要加括号】
SELECT 字段列表 FROM 表名 WHERE 字段1 比较运算符(子查询) ;
9).常见的多表查询★
①内连接：
②外连接：
③自连接：【自己连自己，表必须起别名】
④子查询【位置：where之后、from之后、select之后】
《注意：在where之后的查询条件中，即字段、group by、having可加子查询，order by不可嵌套子查询》
注意：建议使用子查询的时候起别名（打括号起别名）的作用:
• 可读性、可维护性、可重用性。
• 避免歧义，不会报错。
Ⅰ标量子查询（子查询结果为单个值）:
SELECT column_name FROM table_name WHERE condition=(select codition);
Ⅱ 列子查询（子查询结果为一列）：
SELECT column_name FROM table_name WHERE in/not in/any/some/all(select codition);
常见操作符：
Ⅲ 行子查询（子查询结果为一行）【可多列】
Ⅳ 表子查询（子查询结果为多行多列）【select子句查表】：
10). 常见函数的使用
①字符串函数：↓
②日期函数：↓
③聚合函数：↓SELECT 聚合函数(字段列表) FROM 表名 ;
注意 : NULL值是不参与所有聚合函数运算的。
④数学函数：↓
⑤流程函数：↓【value可以为布尔表达式】
7、SQL高级（二）：IN和NOT IN子查询【注意：关键字后必须加()】IN关键字的作用-> IN 关键字使得在查询中指定多个可能的值变得简单，使得查询更加灵活和可读(NOT IN与之相反)EXISTS和NOT EXISTS子查询【注意：关键字后必须加()】EXISTS关键字的作用->检查是否存在符合查询条件的记录，存在返回true,否则false。注意事项（总结）：◆通常将子查询语句放在比较条件的右边以增加可读性。◆子查询可以返回单行或多行数据，此时要选择合适的关键字。◆子查询返回单行数据时，比较条件中可以使用比较运算符。◆子查询返回多行数据时，比较条件中需要使用IN或NOT IN关键字。◆当判断子查询是否有数据返回时，需要使用EXISTS或NOT EXISTS关键字。◆只出现在子查询中但是没有出现在父查询中的表不能包含在输出列中。
DCL(数据控制语言)【%代表所有主机名】
(1)用户控制(user)：
①查询用户：
use mysql; select*from user;
②创建用户:
create user '用户名'@'主机名' identified by '密码';
③修改用户密码：
alter user '用户名'@'主机名' identified by '新密码';
④删除用户：
drop user '用户名'@'主机名' ;
⑤修改超级管理员密码：
mysqladmin -u username -p password "new password"
(2)权限控制(grant)：
①查询权限：
show grants for '用户名'@'主机名' ;【*表所有】
②授予权限：
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名' ;
③撤销权限：
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名' ;MySQL_存储过程（ 预先定义并保存的可重复执行的程序）优点：1、减少网络流量 2、提升执行速度 3、减少数据库连接次数 4、安全性高 5、高复用性缺点：可移植性差（例：将MySQL更换为Oracle）创建存储过程(Procedure)：《select 列名》：将结果返回给调用者。delimiter 分隔符【$$或者//】//声明分隔符create procedure 过程名 (过程参数)【特性：SQL security definer|invoker】begin #过程体开始//过程体;end+分隔符 #过程体结束delimiter 分隔符//恢复默认分隔符【注意：如果没有声明分隔符，编译器就会把存储过程当成SQL语句处理，导致编译报错(Navicat中不用声明，有可视化窗口)】调用存储过程(call):语法：call 存储过程(参数列表);定义存储过程的参数(in|out|inout):语法：【in|out|inout】参数名 类型①in:输入参数->在调用存储过程时指定，不会被返回。②out:输出参数->可以被改变和返回。③inout:输入输出参数-> 在调用存储过程时指定，可以被改变和返回。④使用select into+变量--给多个变量赋值(使用场景：查询结果)存储过程的标识(begin……end+分隔符)
存储过程【变量】->两大类：1、系统变量 2、自定义变量(用户变量一般前面带个@)
自定义变量（局部变量|会话变量【用户变量】）
声明局部|·用户变量(declare):
declare 变量名,……数据类型 【default 值】;
局部变量赋值(set): set 变量名=值；
系统变量（局部变量session《默认》|会话变量global【用户变量】）
查看系统变量
show [session|global] variables;
模糊查看系统变量
show [session|global] variables like '……';
查看指定变量名
show @@[session|global] 系统变量名;
设置系统变量（set）
set [session|global] 系统变量名=值;
【注意：mysql重新启动之后，所设置的全局变量会失效，若不想失效，需更改/etc/my.cnf配置文件】
设置用户权限
create [definer={user| current_user}]#定义definer
procedure 存储过程名[SQL security{definer|invoker}]#特性
begin……end;
definer：创建者的权限执行。invoker：执行者的权限执行。【注意：definer和invoker决定存储过程不同的执行方式（SQL security不可省）】
查看存储过程(show procedure):①查看存储状态：show procedure status;②条件查询存储状态：show procedure status where DB='数据库名'||like '%content%';③查看存储过程的创建代码：show create procedure 存储过程名；④修改存储过程：alter procedure 存储过程名【特性……】;⑤删除存储过程：drop procedure 存储过程名;
存储过程的控制语句（end后加结束控制语句类型/label即标号，用于区分不同的循环）:
ⅠIF条件语句（if-elseif-else）：
if 条件 then 语句列表 [elseif 条件 then 语句列表][else 语句列表] end if;
Ⅱ CASE条件语句（case-when-then）:
case when 条件 then 语句列表[when 条件 then 语句列表][else 语句列表] end case;
Ⅲ while循环语句（while+条件+do）:
【label:】while 条件 do 语句列表 end while【label】;
Ⅳ loop循环语句（loop+leave）【注意：loop与repeat循环类似，，不需要初始化条件直接进入循环体，但loop有退出条件】：
【label:】loop 语句列表 end loop 【label】；leave 【label】;
Ⅴ repeat循环语句（repeat+until）->相当于do-while循环：
【label:】repeat 语句列表 until 条件 end repeat 【label】;
Ⅵ 迭代语句（iterate+【label】）->迭代下一次循环
Ⅶ ★游标(Cursor)是用于遍历和操作查询结果集合的数据库对象
声明游标（必须声明在变量之后，否则会语法报错）:
declare 游标名称 cursor for 查询语句;
打开/关闭游标：
open/close 游标名称；
获取游标记录：
fetch 游标名称 into 变量[,变量……]；
条件处理程序（Handler）[在定义流程控制结构遇到问题时相应的处理步骤]：
DECLARE handler action HANDLER FOR [condition value]...statement;
触发器（Trigger）是一种特殊的存储过程， 可以用于执行数据的插入、更新和删除操作之前或之后的额外操作，以确保数据的完整性和一致性。
【使用别名OLD和NEW来引用触发器中发生变化的记录内容,可以借用concat函数】
创建触发器：
create trigger trigger_name before/after insert/update/delete on table_name for each row--行级触发器
begin
trigger_operation;
end;
显示触发器：
show trigger_name;
删除触发器：
drop trigger_name;事务（指作为单个逻辑工作单元执行的一系列操作并进行统一管理机制）
查看事务提交方式：【确保一系列数据库操作的完整性和一致性】select @@autocommit;控制事务的两种方式：①将默认的自动提交更改为手动提交：（手动|自动--set autocommit=0 | set autocommit=1）提交事务：commit--回滚事务：rollback【注意： 手动提交，必须要执行commit，rollback只能回滚当前事务所做的更改，而不能撤销已经提交的事务。】②直接打开事务：begin;或start transaction;提交事务：commit--回滚事务：rollback事务的四大特性（简称ACID）：
查看事务隔离级别：
SELECT @@TRANSACTION_ISOLATION;
设置事务隔离级别(session表示当前用户，global表示所有用户)：
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |
READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
【注意：事务隔离级别越高，数据越安全，但是性能越低】
并发事务问题
1). 赃读：一个事务读到另外一个事务还没有提交的数据。
2). 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
3). 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据
已经存在，好像出现了 "幻影”。
索引（index提高查询效率）索引结构Btrees和B+trees：
索引的分类：1.普通索引(index) 2.唯一索引(unique) 3. 主键索引 4.全文索引 5.空间索引
在InnoDB引擎中索引的分类（根据索引的存储形式）：
聚集索引选取规则：
>如果存在主键，主键索引就是聚集索引。
>如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引。
>如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
Ⅰ创建索引：【注意：创建主键索引不适合create语法->alter table结构去完成】
create [unique|fulltext|spatial] index index_name on table_name(列名[长度]);
Ⅱ查看索引：
show index from table_name;
Ⅲ删除索引：
drop index index_name on table_name;
SQL的性能分析：
查看访问频次：
show session|global status like 'Com_______';
查看慢查询日志( 记录执行时间超过指定阈值的查询的日志，优化性能问题)：
show variables like '%slow_query_log%';
【▲注意：MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(my.cnf|my.ini)中配置slow_query_log、long_query_time参数，系统默认为10s】
查看性能的时间(show profile)：
MySQL是否支持：SELECT @@have_profiling;
explain关键字执行计划，各字段含义：使用在SQL语句前加个explain关键字。
常见的索引失效： 【为避免索引失效，应在查询中遵循索引的最左匹配原则，正确使用索引，避免使用函数或表达式，并定期评估和优化索引】①索引列上不要进行运算。②字符串不加引号会进行隐式类型转换。③like关键字进行模糊查询时，%出现在开头。④or关键字两侧存在没有索引的列。⑤不符合最左匹配原则：查询条件索引列没按顺序排列。⑥在查询时索引列使用聚合函数。⑦使用联合查询相对应的字段必须都存在索引列。在查询的时候可以通过三个关键字去优化索引：使用索引/忽略/强制----use/ignore/force index (索引列);
视图（虚拟表，不真实存在）
创建视图：【作用：简单方便，安全，数据独立】
CREATE[OR REPLACE]VIEW视图名称 AS SELECT语句[WITH[CASCADED|LOCAL]CHECK OPTION]--检查选项
查看创建的视图：
show create view 视图名称; 查看视图： select*from 视图名称;
修改视图：
alter view 视图名称[列名名称] as select语句;
删除视图：
drop view [if exists] 视图名称;
视图（检查选项--CASCADED|LOCAL）
+with[cascaded|local]check option;
cascaded:级联的，必须满足所有级联视图的条件。
local:只需要满足当前视图的条件，若级联的视图加了local检查选项就需要考虑条件了。
数据库备份和恢复①使用mysqldump命令来备份：【▲注意：不能进入mysql>命令行，属于DOS命令，否则无法执行】mysqldump [options] database [table1,table2...] >[path]\filename.sql-->(不要分号)[options]:-u username -p，主机名已省略->h-。命令恢复数据：dbname表示需要导入的数据库mysqldump -u username -p dbname < filename.sql结合source命令恢复数据：source filename; 在导入数据之前需要选择要导入的数据库->use database;②使用Navicat备份并恢复：右键数据库->转储SQL文件->结构和数据//|\\右键数据库->运行SQL文件->开始导入数据
MySQL中的锁的分类( 锁是一种机制，用于在并发环境下保证数据的一致性和完整性)
①全局锁：锁定数据库中的所有表【常见使用场景：数据库备份】
添加全局锁命令：【弊端：不能进行相应的操作，只能读取】
flush tables with read lock;
在InnoDB引擎中，我们可以在备份时加上参数-single-transaction参数来完成不加锁的一致性数据备份
mysqldump -single-transaction -u username -p dbname > filename.sql
解锁：
unlock tables;
②表级锁：每次操作锁定整张表（读锁和写锁）
lock tables 表名 read/write;
③行级锁：每次操作锁住对应的行数据（共享锁）
lock in share mode;
使用JDBC连接数据库JDBC关键步骤①创建工程，导入驱动jar包
②注册驱动（真正注册驱动的是DriverManager.registerDriver(),可查看Driver源码）Class.forName("com.mysql.jdbc.Driver"); jar包已经定义了，可以删除。③通过DriverManager接口创建连接对象Connection conn=DriverManager.getConnection(url,username,password);④定义SQL语句String sql="";⑤获取执行SQL语句的对象，由Connection产生Statement stmt=conn.createStatement();⑥使用Statement接口执行SQL语句（一般使用ProparedStatement防止SQL注入）stmt.executeUpdate(sql);⑦处理返回结果⑧释放资源
JDBC API详解
Ⅰ DirverManager(驱动管理类)
1.注册驱动
--Class.forName("com.mysql.jdbc.Driver");
2.获取数据库连接
Connection conn=DriverManager.getConnection(url,username,password);
注意：url：数据连接字符串，也称链接路径。
语法：若ip地址是本机，可以替换成localhost，并且端口号默认为3306,ip地址:端口都可以省略。
jdbc:mysql://ip地址:端口/数据库名称[?连接参数=参数值]->一般是[?serverTimezone=GMT-8]
指定字符编码集UTF-8：【参数之间用&连接】
?serverTimezone=GMT-8&useUnicode=true&characterEncoding/charSet=utf-8
Ⅱ Connection接口数据库连接对象
一.获取执行SQL语句的对象
1:创建一个statement对象并将SQL语句发送到数据库：
Statement createStatement();
2:创建一个PreparedStatement对象并将参数化的SQL语句发送到数据库：
PreparedStatement prepareStatement(String sql);
3:判断Connection对象是否关闭：
boolean isClosed();
4:释放资源：
void close();
二.管理事务
开启事务：
void setXxx(int index,xxx);注意：Xxx可以是基本数据类型，也可以是Object类型。
提交事务：
commit();
回滚事务：
rollback();
Ⅲ Statement执行数据库操作
缺点：会造成SQL语句的注入，因为在使用Statement接口时要进行语句拼接，不仅烦琐，还存在安全漏洞。
执行SQL语句↓(注意：执行DDL语句后，结果肯呢个返回0)
执行QL查询语句并获取ResultSet对象: 【DQL】
ResultSet executeQuery(String sql);
执行插入、删除、更新等操作，返回受影响的行数:【DML和DDL】
int executeUpdate(String sql);
可执行任意SQL语句:-> 若返回ResultSet对象，则返回true;
boolean execute(String sql);
Ⅳ ResultSet接口负责保存和处理查询结果
迭代结果集中的行，并返回下一行的数据:
resultSet.next();
获取mysql数据库的数据《 注意：Xxx指定数据库存储的数据类型》
resultSet.getXxx();
Ⅴ PreparedStatement详解
1.预编译SQL，提高代码的可读性、可维护性、SQL语句的执行性能和安全性。
2.防止SQL注入：将敏感字符进行转义。
PrearedStatement作为Statement的子类，是父类的进阶。
常见的方法：
可执行任意SQL语句:->若返回ResultSet对象，则返回true;
boolean execute(String sql);
执行SQL查询语句:
ResultSet executeQuery();
返回影响的行数:
int executeUpdate();
设置参数：【在SQL语句中将输入参数替换成"?"作为占位符】
void setXxx(int index,xxx);注意：Xxx可以是基本数据类型，也可以是Object类型。
数据访问和DAO模式（Data Access Object）[目的：实现数据访问和业务逻辑分离]
使用Properties配置文件的步骤
①添加.properties文件 在Java中"New->File"；
②编辑配置文件,与Map结构相似，“键-值对”的形式来存储（例：driver、url、username、password）;
③读取配置信息，常见方法↓
根据键获取值：
String getProperty(String key);
通过调用基类Hashtable的put()方法添加键-值对：
Object setProperty(String key,String value);
从输入流中读取属性列表：
void load(InputStream inStream);
void load(Reader reader);
注意：使用文件流去读取文件
清除键值对：void clear();
DAO模式【将数据库操作封装，对外提供相应的接口】耦合性：越低，代码的可扩展性和可维护性越高。优点：采用面向接口编程降低代码间的耦合性，提高代码的可扩展性和可维护性。DAO两次隔离：1. 隔离了数据访问代码和业务逻辑代码 。2. 隔离了不同数据库的实现。实体类：用于存放与传输对象数据。数据库工具类：避免数据库连接和关闭代码的重复使用。DAO接口：定义操作为抽象方法。DAO实现类：实现抽象方法的具体实现，重写方法。
992
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
