MySql基础

一、数据库基本知识

1、数据库基本介绍

# 本地进入可不输入-h命令,-u和-p后面没有空格！
mysql [-h 服务器主机地址] -u用户名 -p密码
# 修改数据库密码
mysqladmin -u用户名 -p密码 password 新密码;

# 显示所有数据库
show databases; 
# 打开某个数据库
use dbname； 
# 显示表mysql数据库中user表的列信息
describe user; 

# 创建数据库
create database [if not exists] 数据库名;
# 删除数据库
drop database [if exists] 数据库名;
# 显示数据库中所有的表
show 数据库名;
# 选择数据库
use 数据库名; 

exit; 退出Mysql
? 命令关键词 : 寻求帮助
-- 表示注释

2、结构化查询语句分类

名称	解释	命令
DDL(数据定义语言)	定义和管理数据对象，如数据库，数据表等	CREATE、DROP、ALTER
DML(数据操作语言)	用于操作数据库对象中所包含的数据	INSERT、UPDATE、DELETE
DQL(数据查询语言)	用于查询数据库数据	SELECT
DCL(数据控制语言)	用于管理数据库的语言，包括管理权限及数据更改	GRANT、commit、rollback

3、数据类型和范围大小

类型	说明	长度(取值范围)
tinyint	十分小的数据	1字节
smallint	较小的数据	2字节
mediumint	中等大小的数据	3字节
int	标准整数(常用)	4字节
bigint	较大的的整数	8字节
float	单精度浮点数	4字节
double	双精度浮点数	8字节
decimal(m,d)	字符串形式的浮点数，涉及钱时使用	m字节
char(m)	固定长度字符串，检索快但浪费空间，0<=m<=255	m字符
varchar(m)	可变字符串，0<=m<=65535(常用)	变长数
tinytext	微型文本串	2^8-1
text	文本串	2^16-1
DATETIME	时间格式，YYYY-MM-DD HH:mm:ss	1000-01-01 00:00:00~9999-12-31 23:59:59
TIMESTAMP	时间戳，从1970开始的毫秒数	197010101000000~2037年

4、创建数据表

create table [if not exists] `表名`(
   '字段名1' 列类型 [属性][索引][注释],
   '字段名2' 列类型 [属性][索引][注释],
  #...
   '字段名n' 列类型 [属性][索引][注释]
)[表类型][表字符集][注释];

5、数据表字段属性

UnSigned

• 无符号的

• 声明该数据列不允许负数

ZEROFILL

• 0填充的
• 不足位数的用0来填充 , 如int(3),5则为005

Auto_Increment

• 自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1(默认)
• 通常用于设置主键 , 且为整数类型
• 可定义起始值和步长
  • 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
  • SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)

NULL 和 NOT NULL

• 默认为NULL , 即没有插入该列的数值
• 如果设置为NOT NULL , 则该列必须有值

DEFAULT

• 默认的，用于设置默认值
• 例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值

/*用户表*/
create table IF NOT EXISTS `user`(
id int(20) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` datetime DEFAULT NULL COMMENT '生日',
`address` varchar(100) DEFAULT NULL COMMENT '地址',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
primary key(`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 查看数据表的定义
SHOW CREATE TABLE user;
-- 显示表结构
DESC user;  -- 设置严格检查模式(不能容错了)SET sql_mode='STRICT_TRANS_TABLES';

对于Mysql的引擎，现在一般使用InnoDB，而MySql的一些默认设置都在my.ini文件中设置

• 适用 MyISAM : 节约空间及相应速度
• 适用 InnoDB : 安全性 , 事务处理及多用户操作数据表

6、修改与删除数据库

# 修改表名
ALTER TABLE 旧表名 RENAME AS 新表名
# 添加字段
ALTER TABLE 表名 ADD字段名 列属性[属性]
# 修改字段
ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]
# 删除字段
ALTER TABLE 表名 DROP 字段名
# 删除数据表
DROP TABLE [IF EXISTS] 表名

注：Mysql仅在windows下大小写不敏感！

二、MySql数据管理

1、外键

# 主表已经在第一章写明
CREATE TABLE `usertype` (
`type` varchar(20) NOT NULL COMMENT '用户类型',
`userid` int(20) NOT NULL COMMENT '用户id',
CONSTRAINT `FK_userid` FOREIGN KEY (`userid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 创建外键方式二 : 创建子表完毕后,修改子表添加外键
ALTER TABLE `usertype` ADD CONSTRAINT `FK_userid` FOREIGN KEY (`userid`) REFERENCES `user` (`id`);

再删除表时，要先删除有外键的表，再删除主表

-- 删除外键
ALTER TABLE usertype DROP FOREIGN KEY FK_gradeid;
-- 发现执行完上面的,索引还在,所以还要删除索引
-- 注:这个索引是建立外键的时候默认生成的
ALTER TABLE usertype DROP INDEX FK_gradeid;

注意现在都是用逻辑外键了，不使用这种物理外键方式

2、DML数据操作语言

INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')
UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHEREcondition];
# condition为筛选条件 , 如不指定则删除该表的所有列数据
DELETE FROM 表名 [WHERE condition];
# TRUNCATE用于完全清空表数据,但表结构,索引,约束等不变;
TRUNCATE [TABLE] table_name;

TRUNCATE和DELETE异同：

相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快

不同 :

• 使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器

• 使用TRUNCATE TABLE不会对事务有影响

3、DQL数据查询语言

# 注意 : [ ] 括号代表可选的 , { }括号代表必选得，*指查询所有，不推荐
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- 联合查询
  [WHERE ...]  -- 指定结果需满足的条件
  [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
  [HAVING]  -- 过滤分组的记录必须满足的次要条件
  [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
  [LIMIT {[offset,]row_count | row_countOFFSET offset}];
   -- 指定查询的记录从哪条至哪条

• As字句作为别名，查询指定字段，DISTINCT去掉SELECT查询返回的记录结果中重复的记录 ( 返回所有列的值都相同 ) , 只返回一条

SELECT CONCAT('用户',id) AS 新用户名 FROM user;
SELECT DISTINCT sex FROM user;
SELECT @@auto_increment_increment; -- 查询自增步长
SELECT VERSION(); -- 查询版本号
SELECT 100*3-1 AS 计算结果; -- 表达式

• where条件语句：AND、OR、NOT
• 模糊查询:比较操作符

操作符名称	语法	描述
IS NULL	a IS NULL	若操作符为NULL，则结果为真
IS NOT NULL	a IS NOT NULL	若操作符不为NULL，则结果为真
BETWEEN	a BETWEEN b AND c	若 a 范围在 b 与 c 之间，则结果为真
LIKE	a LIKE b	SQL 模式匹配，若a匹配b，则结果为真
IN	a IN (a1，a2，a3，…)	SQL 模式匹配，若a匹配b，则结果为真

-- 举例
-- like结合使用的通配符 : % (代表0到任意个字符) _ (一个字符)
-- 查询姓刘用户的姓名
SELECT name FROM `user` WHERE name LIKE '刘%';

• 连接查询:join对比

• 排序和分页、子查询

-- 举例
select name from `user` ORDER BY birthday DESC LIMIT 0,5;

4、MySql函数

  -- ================ 内置函数 ================
 -- 数值函数
 abs(x)            -- 绝对值 abs(-10.9) = 10
 format(x, d)    -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
 ceil(x)            -- 向上取整 ceil(10.1) = 11
 floor(x)        -- 向下取整 floor (10.1) = 10
 round(x)        -- 四舍五入去整
 mod(m, n)        -- m%n m mod n 求余 10%3=1
 pi()            -- 获得圆周率
 pow(m, n)        -- m^n
 sqrt(x)            -- 算术平方根
 rand()            -- 随机数
 truncate(x, d)    -- 截取d位小数
 
 -- 时间日期函数
 now(), current_timestamp();     -- 当前日期时间
 current_date();                    -- 当前日期
 current_time();                    -- 当前时间
 date('yyyy-mm-dd hh:ii:ss');    -- 获取日期部分
 time('yyyy-mm-dd hh:ii:ss');    -- 获取时间部分
 date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j');    -- 格式化时间
 unix_timestamp();                -- 获得unix时间戳
 from_unixtime();                -- 从时间戳获得时间
 
 -- 字符串函数
 length(string)            -- string长度，字节
 char_length(string)        -- string的字符个数
 substring(str, position [,length])        -- 从str的position开始,取length个字符
 replace(str ,search_str ,replace_str)    -- 在str中用replace_str替换search_str
 instr(string ,substring)    -- 返回substring首次在string中出现的位置
 concat(string [,...])    -- 连接字串
 charset(str)            -- 返回字串字符集
 lcase(string)            -- 转换成小写
 left(string, length)    -- 从string2中的左边起取length个字符
 load_file(file_name)    -- 从文件读取内容
 locate(substring, string [,start_position])    -- 同instr,但可指定开始位置
 lpad(string, length, pad)    -- 重复用pad加在string开头,直到字串长度为length
 ltrim(string)            -- 去除前端空格
 repeat(string, count)    -- 重复count次
 rpad(string, length, pad)    --在str后用pad补充,直到长度为length
 rtrim(string)            -- 去除后端空格
 strcmp(string1 ,string2)    -- 逐字符比较两字串大小
 
 -- 聚合函数
 count()
 sum();
 max();
 min();
 avg();
 group_concat()
 
 -- 其他常用函数
 md5();
 default();

针对count()函数来说，更推荐使用count(1)而不是count(*)

 -- 从含义上讲，count(1) 与 count(*) 都表示对全部数据行的查询。
 -- count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。
 -- count(*) 包括了所有的列，相当于行数，在统计结果的时候，包含字段为null 的记录；
 -- count(1) 用1代表代码行，在统计结果的时候，包含字段为null 的记录 。
 /*
 很多人认为count(1)执行的效率会比count(*)高，原因是count(*)会存在全表扫描，而count(1)可以针对一个字段进行查询。其实不然，count(1)和count(*)都会对全表进行扫描，统计所有记录的条数，包括那些为null的记录，因此，它们的效率可以说是相差无几。而count(字段)则与前两者不同，它会统计该字段不为null的记录条数。
 
 下面它们之间的一些对比：
 
 1）在表没有主键时，count(1)比count(*)快
 2）有主键时，主键作为计算条件，count(主键)效率最高；
 3）若表格只有一个字段，则count(*)效率较高。
 */

MySql快速入门

1、SQL语句的书写语法和规则

2、基础查询

利用最基础和最重要的select语句，对表中数据进行查询

3、聚合与排序

面对大量数据我们时常需要对其做汇总的分析。这部分内容就是说明各类汇总操作的方法

4、数据更新

处理数据库时，我们可能需要的不仅仅是“读”数据，更需要“写”数据，也就是对表进行更新和修改。这一天的内容侧重对表的修改操作，并学习重要的数据库管理方法——事务的创建

5、复杂查询

6、复杂查询，函数、谓词、case表达式

如同所有编程语言一样，SQL也有好用的工具包——函数。利用函数，可以将很多复杂的问题在一行代码内解决完成

7、集合运算

在有多张表的情况下，表和表之间的运算和联系就变得很重要，利用集合运算就可以将不同表中的数据整合起来。

四、事务

事务四大特性：ACID。原子性(Atomic)，一致性(Consist)，隔离性(Isolated)，持久性(Durable)

-- 使用set语句来改变自动提交模式
SET autocommit = 0;   /*关闭*/
SET autocommit = 1;   /*开启*/

-- 注意:
--- 1.MySQL中默认是自动提交
--- 2.使用事务时应先关闭自动提交

-- 开始一个事务,标记事务的起始点
START TRANSACTION  

-- 提交一个事务给数据库
COMMIT

-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK

-- 还原MySQL数据库的自动提交
SET autocommit =1;

-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点

五、索引

1、索引概述

索引的作用

• 提高查询速度
• 确保数据的唯一性
• 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
• 使用分组和排序子句进行数据

索引分类

• 主键索引 (Primary Key)
• 唯一索引 (Unique)
• 常规索引 (Index)
• 全文索引 (FullText)

2、主键索引

主键 : 某一个属性组能唯一标识一条记录

特点 :

• 最常见的索引类型
• 确保数据记录的唯一性
• 确定特定数据记录在数据库中的位置

3、唯一索引

作用 : 避免同一个表中某数据列中的值重复

与主键索引的区别

• 主键索引只能有一个
• 唯一索引可能有多个

-- 举例
CREATE TABLE `Grade`(
  `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
  `GradeName` VARCHAR(32) NOT NULL UNIQUE
   -- 或 UNIQUE KEY `GradeID` (`GradeID`)
)

4、常规索引

作用 : 快速定位特定数据

注意 :

• index 和 key 关键字都可以设置常规索引
• 应加在查询找条件的字段
• 不宜添加太多常规索引,影响数据的插入,删除和修改操作

CREATE TABLE `result`(
   -- 省略一些代码
  INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加
)
-- 创建后添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

5、全文索引

作用 : 快速定位特定数据

注意 :

• 只能用于MyISAM类型的数据表
• 只能用于CHAR , VARCHAR , TEXT数据列类型
• 适合大型数据集

6、索引测试

-- 新建表
CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '' COMMENT '用户昵称',
`email` varchar(50) NOT NULL COMMENT '用户邮箱',
`phone` varchar(20) DEFAULT '' COMMENT '手机号',
`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别（0:男；1：女）',
`password` varchar(100) NOT NULL COMMENT '密码',
`age` tinyint(4) DEFAULT '0' COMMENT '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

-- 插入100w数据
DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
  INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('用户', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();

索引效率测试

-- CMD命令行内的语句结束符可以为 ";", "\G", "\g"，仅影响显示结果。
-- 无索引
-- 查看耗时，本地我用了6+s
SELECT * FROM app_user WHERE name = '用户9999'; 
-- 查询信息，使用EXPLAIN关键字
EXPLAIN SELECT * FROM app_user WHERE name = '用户9999';

-- 有索引时
-- 先创建索引，需要花一段时间
CREATE INDEX idx_app_user_name ON app_user(name);
-- 测试发现，只查了一行，速度极快
EXPLAIN SELECT * FROM app_user WHERE name = '用户9999';

注

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表建议不要加索引
• 索引一般应加在查找条件的字段

-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

-- 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

六、权限管理

1、用户管理

/* 用户和权限管理 */
用户信息表：mysql.user
-- 刷新权限
FLUSH PRIVILEGES

-- 增加用户 CREATE USER shawn IDENTIFIED BY 'shawn'
CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
  - 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
  - 只能创建用户，不能赋予权限。
  - 用户名，注意引号：如 'user_name'@'192.168.1.1'
  - 密码也需引号，纯数字密码也要加引号
  - 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD

-- 重命名用户 RENAME USER shawn TO shawn1
RENAME USER old_user TO new_user

-- 设置密码
-- alter user'shawn'@'%' IDENTIFIED BY 'shawn'; 
alter user'用户名'@'%' IDENTIFIED BY '新密码'; 

-- 删除用户 DROP USER shawn
DROP USER 用户名

-- 分配权限/添加用户
-- 默认是没有GRANT权限的
-- GRANT all privileges ON *.* TO shawn
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
  - all privileges 表示所有权限
  - *.* 表示所有库的所有表
  - 库名.表名 表示某库下面的某表

-- 查看权限   SHOW GRANTS FOR shawn;
SHOW GRANTS FOR 用户名
   -- 查看当前用户权限
  SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();

-- 撤消权限
-- REVOKE all privileges ON *.* FROM shawn;
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名    -- 撤销所有权限

权限解释

-- 权限列表
ALL [PRIVILEGES]    -- 设置除GRANT OPTION之外的所有简单权限
ALTER    -- 允许使用ALTER TABLE
ALTER ROUTINE    -- 更改或取消已存储的子程序
CREATE    -- 允许使用CREATE TABLE
CREATE ROUTINE    -- 创建已存储的子程序
CREATE TEMPORARY TABLES        -- 允许使用CREATE TEMPORARY TABLE
CREATE USER        -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW        -- 允许使用CREATE VIEW
DELETE    -- 允许使用DELETE
DROP    -- 允许使用DROP TABLE
EXECUTE        -- 允许用户运行已存储的子程序
FILE    -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX     -- 允许使用CREATE INDEX和DROP INDEX
INSERT    -- 允许使用INSERT
LOCK TABLES        -- 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS     -- 允许使用SHOW FULL PROCESSLIST
REFERENCES    -- 未被实施
RELOAD    -- 允许使用FLUSH
REPLICATION CLIENT    -- 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE    -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT    -- 允许使用SELECT
SHOW DATABASES    -- 显示所有数据库
SHOW VIEW    -- 允许使用SHOW CREATE VIEW
SHUTDOWN    -- 允许使用mysqladmin shutdown
SUPER    -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE    -- 允许使用UPDATE
USAGE    -- “无权限”的同义词
GRANT OPTION    -- 允许授予权限


/* 表维护 */

-- 分析和存储表的关键字分布
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...
-- 检查一个或多个表是否有错误
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- 整理数据文件的碎片
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

2、MySql备份

-- 导出
1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
　　mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)

可以-w携带备份条件

-- 导入
1. 在登录mysql的情况下：-- source D:/a.sql
　　source 备份文件
2. 在不登录的情况下
　　mysql -u用户名 -p密码 库名 < 备份文件