MySQL
MySQL
配置
MySQL服务启动
-
使用管理员模式打开cmd,输入
services.msc
打开服务的窗口。 -
使用管理员模式打开cmd,输入
net start mysql
启动MySQL的服务。
MySQL登录
mysql -uroot -p密码
mysql --host=ip --user=root --password=连接目标的密码
MySQL退出
exit
quit
数据库备份和还原
数据库备份
mysqldump -u用户名 -p密码 数据库名 > 文件的路径
数据库还原
use 数据库;
source 导入文件的路径;
SQL(结构化查询语言)
SQL通用语法
- SQL语句可以单行或多行书写,以分号结尾。
- 使用空格和缩进来增强语句的可读性。
- MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。
- 单行注释:-- 注释内容或# 注释内容(MySQL特有);多行注释:/* 注释 */。
SQL分类
- DDL(Data Definition Language)数据定义语言:用来定义数据库对象(数据库,表,列等)。关键字:CREATE,DROP,ALTER等。
- DML(Data Manipulation Language)数据操作语言:用来对数据库中表的数据进行增删改。关键字:INSERT,DELETE,UPDATE等。
- DQL(Data Query Language)数据查询语言:用来查询数据库中表的记录(数据)。关键字:SELECT,WHERE等。
- DCL(Data Control Language)数据控制语言:用来定义数据库的访问权限和安全级别,以及创建用户。关键字:GRANT,REVOKE等。
DDL(操作数据库、表)
操作数据库
创建
-
创建数据库。
CREATE DATABASE 数据库名称;
-
创建数据库,判断不存在,再创建。
CREATE DATABASE IF NOT EXISTS 数据库名称;
-
创建数据库,并指定字符集。
CREATE DATABASE 数据库名称 CHARACTER SET 字符集名;
-
创建数据库,判断是否存在,并制定字符集。
CREATE DATABASE IF NOT EXISTS 数据库名称 CHARACTER SET 字符集名;
查询
-
查询所有数据库的名称。
SHOW DATABASES;
-
查询某个数据库的字符集和创建语句。
SHOW CREATE DATABASE 数据库名称;
修改
-
修改数据库的字符集。
ALTER DATABASE 数据库名称 CHARACTER SET 字符集名;
删除
-
删除数据库。
DROP DATABASE 数据库名称;
-
判断数据库存在,存在再删除。
DROP DATABASE IF EXISTS 数据库名称;
使用数据库
-
查询当前正在使用的数据库名称。
SELECT DATABASE();
-
使用数据库。
USE 数据库名称;
操作表
创建
-
创建表。
CREATE TABLE 表名( 列名1 数据类型1, 列名2 数据类型2, ...... 列名n 数据类型n );
-
复制表。
CREATE TABLE 表名 LIKE 被复制的表名;
查询
-
查询某个数据库中所有的表名称。
SHOW TABLES;
-
查询表结构。
DESC 表名;
修改
-
修改表名。
ALTER TABLE 表名 RENAME TO 新的表名;
-
修改表的字符集。
ALTER TABLE 表名 CHARACTER SET 字符集名称;
-
添加一列。
ALTER TABLE 表名 ADD 列名 数据类型;
-
修改列名称和数据类型。
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型; ALTER TABLE 表名 MODIFY 列名 新数据类型;
-
删除列。
ALTER TABLE 表名 DROP 列名;
删除
DROP TABLE 表名;
DROP TABLE IF EXISTS 表名;
DML(增删改表中数据)
添加数据
INSERT INTO 表名(列名1,列名2,...,列名n) VALUES(值1,值2,...,值n);
注意:
- 列名和值要一一对应。
- 如果表名后,不定义列名,则默认给所有列添加值。
- 除了数字类型,其他类型需要使用引号(单双都可以)引起来。
删除数据
DELETE FROM 表名 [WHERE 条件]; -- 不推荐使用。有多少条记录就会执行多少次删除操作。
TRUNCATE TABLE 表名; -- 推荐使用,效率高。先删除表,然后再创建一张一样的表。
注意:
- 如果不加条件,则删除表中所有记录。
修改数据
UPDATE 表名 SET 列名1 = 值1,列名2 = 值2,... [WHERE 条件];
注意:
- 如果不加条件,则会将表中所有记录全部修改。
DQL(查询表中的记录)
语法
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段
HAVING
分组之后的条件
ORDER BY
排序
LIMIT
分页限定
基础查询
多个字段的查询
SELECT 字段名1,字段名2,...,字段名n FROM 表名;
去除重复
DISTINCT
计算列
- 一般可以使用四则运算计算一些列的值。(一般只会进行数值型的计算)
IFNULL(表达式1,表达式2)
:null参与的运算,计算结果都为null。表达式1:哪个字段需要判断是否为null;表达式2:如果该字段为null后的替换值。
起别名
AS
:as也可以省略。
条件查询
where子句后跟条件
运算符
>、<、<=、>=、=、<>
BETWEEN...AND
IN(集合)
LIKE
IS NULL
AND 或 &&
OR 或 ||
NOT 或 !
排序查询
ORDER BY 排序字段1 排序方式1,排序字段2 排序方式2...
。- 排序方式:
ASC
为升序,默认的。DESC
为降序。 - 如果有多个排序条件,则当前边的条件值一样时,才会判断第二条件。
聚合查询
COUNT
:计算个数。一般选择非空的列:主键。MAX
:计算最大值。MIN
:计算最小值。SUM
:计算和。AVG
:计算平均值。
注意: 聚合函数的计算,排除null值。
分组查询
GROUP BY 分组字段;
WHERE 条件;
HAVING 条件;
注意:
- where在分组之前进行限定,如果不满足条件,则不参与分组。having分组之后进行限定,如果不满足结果,则不会被查询出来。
- where后不可以跟聚合函数,having可以进行聚合函数的判断。
分页查询
LIMIT 开始的索引,每页查询的条数;
多表查询
内连接
SELECT 字段列表 FROM 表名1 [INNER] JOIN 表名2 ON 条件;
SELECT 字段列表 FROM 表名1,表名2 WHERE 条件;
外连接
SELECT 字段列表 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;
SELECT 字段列表 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;
DCL(管理用户和权限)
管理用户
添加用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
删除用户
DROP USER '用户名'@'主机名';
修改用户密码
UPDATE USER SET Password = PASSWORD('密码') WHERE USER = '用户名';
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('密码');
注意: MySQL忘记用户的密码
- 使用管理员模式运行cmd,输入
net stop mysql
来停止MySQL服务。 - 使用无验证方式启动MySQL服务(
mysqld --skip-grant-tables
)。 - 运行新的cmd窗口,直接输入mysql命令登录。
- 输入
use mysql
。 - 输入
UPDATE USER SET PASSWORD = PASSWORD('密码') WHERE USER = '用户名';
- 关闭cmd窗口。
- 结束mysqld.exe进程。
- 重新启动MySQL服务。
管理权限
查询权限
SHOW GRANTS FOR '用户名'@'主机名';
授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
约束
- 对表中的数据进行限定,保证数据的正确性、有效性和完整性。
- 分类:主键约束(primary key)、非空约束(not null)、唯一约束(unique)、外键(foreign key)。
非空约束(not null)
-
创建表时添加约束。
CREATE TABLE 表名(列名 数据类型 NOT NULL);
-
创建表后,添加非空约束。
ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;
-
删除非空约束。
ALTER TABLE 表名 MODIFY 列名 数据类型;
唯一约束(unique)
注意: 唯一约束可以有null值,但是只能有一条记录为null。
-
创建表时添加唯一约束。
CREATE TABLE 表名(列名 数据类型 UNIQUE);
-
创建表后,添加唯一约束。
ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE;
-
删除唯一约束。
ALTER TABLE 表名 DROP INDEX 列名;
主键约束(primary key)
注意: 非空且唯一。一张表只能有一个字段为主键。
-
创建表时添加主键约束。
CREATE TABLE 表名(列名 数据类型 PRIMARY KEY);
-
创建表后,添加主键约束。
ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY;
-
删除主键约束。
ALTER TABLE 表名 DROP PRIMARY KEY;
自动增长(auto_increment)
-
创建表时,添加主键约束,并且完成主键自增长。
CREATE TABLE 表名(列名 数据类型 PRIMARY KEY AUTO_INCREMENT);
-
删除自动增长。
ALTER TABLE 表名 MODIFY 列名 数据类型;
-
添加自动增长。
ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;
外键约束(foreign key)
-
创建表时添加外键。
CREATE TABLE 表名( 外键列名 数据类型, CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 表名(主表列名称); )
-
创建表后,添加外键约束。
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 表名(主表列名称);
-
删除外键约束。
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
-
添加级联操作。
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 表名(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE; ON UPDATE CASCADE --级联更新 ON DELETE CASCADE --级联删除
事务
概念
- 如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。
操作
- 开启事务:start transaction。
- 回滚:rollback。
- 提交:commit。
数据库中事务的提交
事务提交的方式
- 自动提交。MySQL就是自动提交的。一条DML(增删改)语句会自动提交一次事务。
- 手动提交。Oracle数据库默认是手动提交事务。需要先开启事务,再提交。
修改事务的默认提交方式
- 查看事务的默认提交方式:
SELECT @@autocommit; --1代表自动提交 0代表手动提交
。 - 修改默认提交方式:
SET @@autocommit = 0;
事务的四大特征
- 原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败。
- 持久性:当事务提交或回滚后,数据库会持久化的保存数据。
- 隔离性:多个事务之间,相互独立。
- 一致性:事务操作前后,数据总量不变。
事务的隔离级别
概念
- 多个事务之间隔离的,相互独立的。但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题。
存在问题
- 脏读:一个事务,读取到另一个事务中没有提交的数据。
- 不可重复读(虚读):在同一个事务中,两次读取到的数据不一样。
- 幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。
隔离级别
- read uncommitted:读未提交。产生的问题:脏读、不可重复读、幻读。
- read committed:读已提交。产生的问题:不可重复读、幻读。(Oracle)
- repeatable read:可重复读。产生的问题:幻读。(MySQL)
- serializable:串行化。可以解决所有的问题。
注意: 隔离级别从小到大,安全性越来越高,但是效率**越来越低。
- 数据库查询隔离级别:
select @@tx_isolation;
。 - 数据库设置隔离级别:
set global transaction isolation level 级别字符串;
。