MySQL从入门到精通
Mysql各种关键字总结以及使用
目录
PS:没有MySQL的小伙伴可以从地址http://dev.mysql.com/downloads/mysql/中选择windows的版本下载
SQL语句的分类
SQL语句有很多，最好进行分门别类，这样更容易记忆。
可以分为：
DML：数据操作语言（凡是对表当中的数据进行增删改的都是DML）
insert 增
delete 删
update 改
DQL：数据查询语言（凡是带有select关键字的都是查询语句）
DDL：数据定义语言 (DDL主要操作的是表的结构。不是表中的数据)
凡是带有create、drop、alter的都是DDL;(这个增删改和DML不同，这个主要是对表结构进行操作)
DCL：数据控制语言 (例如：授权grant、撤销权限revoke....)
例如：授权grant、撤销权限revoke....
TCL：事务控制语言 (事务提交：commit;事务回滚：rollback)
MySQL权限级别
mysql中存在4个控制权限的表，分别为user表，db表，tables_priv表，columns_priv表
#查看用户权限信息
SELECT USER,HOST FROM mysql.user; #查看mysql有哪些用户,对应的主机名是什么
SHOW GRANTS FOR root@'localhost'; # 查看root这个用户在本机有哪些权限
-----------------------------------------------------
#创建用户 :CREATE USER
CREATE USER test@localhost; #创建一个test的用户,这个用户只能在本机登录
CREATE USER test@localhost IDENTIFIED BY '123456'; #指定密码
-----------------------------------------------------------------
#删除用户 :DROP USER
DROP USER test@localhost;
----------------------------------------------------
#添加权限 :GRANT 什么权限 ON 所有数据库.所有表 TO 用户名字 (WITH GRANT OPTION :可以把自己的权限赋给别的用户)
GRANT ALL PRIVILEGES ON '*' TO test@localhost;
GRANT ALL PRIVILEGES ON '*' TO test@localhost WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON '*' TO test@localhost IDENTIFIED BY'123456'WITH GRANT OPTION;
#添加更新studentno字段的权限,在myschool.student表里,给test这个用户
GRANT UPDATE(studentno) ON myschool.student TO test@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;
#添加查询权限,只能查询myschool数据库里面的student表,给zhangsan这个用户
GRANT SELECT ON myschool.student TO zhangsan@localhost WITH GRANT OPTION;
-------------------------------------------------------------
#删除/收回权限
格式:REVOKE 要移除的权限名字 在哪个数据库哪个表 从哪个用户移除
REVOKE ALL PRIVILEGES ON '*' FROM test@localhost; 数据库 的创建,查看,使用,删除
ps: sm是我创建的数据库名
---------------------
创建数据库
CREATE DATABASE sm;
查看数据库
SHOW DATABASE sm;
选择使用数据库
USE sm;
删除数据库(删库跑路,谨慎操作)
DROP DATABASE sm;创建表(CREATE TABLE `表名`)
CREATE TABLE `表名`(
`字段名1` 列类型[字段属性|约束] [索引] [注释],
`字段名2` 列类型[字段属性|约束] [索引] [注释],
... ...
`字段名n` 列类型[字段属性|约束] [索引] [注释]
) [表类型] [表字符集] [注释] ;
ps: [] 中的内容选择性添加 , 多个字段之间使用 逗号分隔 ,最后一个字段无需添加逗号列类型分类:
作用 : 规定数据库中该列存放的数据类型
数值类型,字符串类型,日期和时间型等
| 数值类型 | 说明 |
| tinyint | 非常小的数据 |
| smallint | 较小的数据 |
| mediumint | 中等大小的数据 |
| int | 标准整数 |
| bigint | 较大的整数 |
| float | 单精度浮点数 |
| double | 双精度浮点数 |
| decimal | 字符串形式的浮点数 |
| 字符串类型 | 说明 |
| char | 固定长字符串,检索快但费空间 |
| varchar | 可变字符串 |
| tinytext | 微型文本串 |
| text | 文本串 |
| 日期和时间类型 | 说明 |
| date | YYYY-MM-DD,日期格式 |
| time | Hh:mm:ss , 时间格式 |
| datetime | YY-MM-DD hh:mm:ss |
| timestamp | YYYYMMDDhhmmss格式表示的时间戳 |
| year | YYYY格式的年份值 |
ps: 类型没有列举完,欢迎补充
字段属性:
| 字段属性 | 说明 |
| UNSIGNED | 无符号的，声明该数据列不允许负数。 |
| ZEROFILL | 1 . 自动填充的，不足位数的用0来填充，如 int(3),5则为 005； 2. 使用zerofill会默认加unsigned(不允许负值)。 |
| AUTO_INCREMENT | 1. 自动增长的，每添加一条数据，自动在上一个记录数上加1； 2. 通常用于设置主键时，且为整数类型； 3. 可定义起始值和步长 4. 如果不用于主键,则必须设置唯一索引 |
| NULL 和 NOT NULL | 1. 默认为NULL,即没有插入该列的数值； 2. 如果设置为NOT NULL，则非空； |
| DEFAULT | 1. 默认的，用于设置默认值； 2. 例如，性别字段，默认为“男”，否则为“女”；若无指定该列的值，则默认为“男”的值 |
字段注释
COMMENT '注释内容'设置表的类型
常用的存储引擎: MyISAM 和 InnoDB (MySQL5.5以上版本默认为InnoDB)
| 名称 | MyISAM | InnoDB |
| 事务处理 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间大小 | 较小 | 较大 |
ps: 使用场景
MyISAM : 节约空间以及响应速度,适合以访问为主的应用
InnoDB : 注重安全,事务处理以及多用户操作的应用
设置表的字符集
charset = utf8;演示
CREATE TABLE `student`(
studentno INT(4) UNSIGNED NOT NULL COMMENT '学号',
loginpwd VARCHAR(20) NOT NULL COMMENT '密码',
studentname VARCHAR(50) NOT NULL COMMENT '学生姓名',
sex CHAR(1) NOT NULL DEFAULT '男' COMMENT'性别',
gradeid INT(4) UNSIGNED COMMENT '年纪编号',
phone VARCHAR(50) COMMENT '联系电话',
address VARCHAR(255) DEFAULT '地址不详' COMMENT '家庭住址',
borndate DATETIME COMMENT '出生日期',
email VARCHAR(50) COMMENT '邮箱账号',
identitycard VARCHAR(18) COMMENT '身份证号'
);清空表 truncate
作用:完全清空一个数据库表,表的结构和索引约束不会变!
TRUNCATE `student`DELETE 和 TRUNCATE 的区别
- 相同点: 都能删除数据 , 都不会删除表结构
- 不同点:
- TRUNCATE 重新设置自增列, 计数器会归零
- TRUNCATE 不会影响事务
快速备份表:
CREATE TABLE emp2 AS SELECT * FROM emp;
ps:把后面的 emp表 快速的再创建一份 (原理为: 将一个查询结果当作一张表, 新建一份!)修改表的结构 ALTER
修改表名 : ALTER TABLE 旧表名 RENAME AS 新表名
添加字段 : ALTER TABLE 表名 ADD 字段名 列类型[属性]
修改字段 : ALTER TABLE 表名 MODIFY 字段名 列类型[属性] #修改列的数据类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列类型[属性] # 修改列的数据类型以及名称
删除字段 : ALTER TABLE 表名 OROP 字段名添加约束
| 约束类型 | 关键字 | 说明 |
| 非空约束 | NOT NULL | 如果字段不允许为空,则需要设置此约束 |
| 默认约束 | DEFAULT | 如果该字段没有赋值,赋予该字段默认值 |
| 唯一约束 | UNIQUE KEY | 设置该字段的值是唯一的,允许为空 |
| 主键约束 | PRIMARY KEY | 设置该字段为表的主键,可以作为该表记录的唯一标识 |
| 外键约束 | FOREIGN KEY | 用在两表之间简历关系,需要引用主表的哪一个字段 |
添加数据(INSERT INTO)
插入单行数据:
INSERT INTO 表名 (字段1,字段2,字段3,...) VALUES (值1,值2,值3,...);插入多行数据:
INSERT INTO 表名 (字段1,字段2,字段3,...) VALUES
(值1,值2,值3,...),
(值1,值2,值3,...),
(值1,值2,值3,...),
....;修改数据(UPDATE)
UPDATE 表名 SET 列名 = '要修改成什么' WHERE 条件判断,达成某种条件后才会修改;删除数据(DELETE)
DELETE FROM 表名 [WHERE 条件]数据查询语言
SELECT * FROM 表名 #查询全部 ,效率低下,不推荐使用
SELECT 字段1,字段2 FROM 表名 #查询指定字段
ps : SELECT语法 记住: select语句永远都不会进行修改操作(因为只负责查询),
在查询中使用“AS”关键字可以给查询的字段起别名。
DISTINCT 去重
作用: 去掉select 查询返回的记录结果中,重复的记录
SELECT DISTINCT name FROM student; #去掉重复名字,保证每个名字仅返回一条WHERE 判断条件
作用: 用于检索数据表中符合条件的记录(太过简单,不再演示)
BETWEEN AND 范围查询
作用: 根据一个范围值来检索
SELECT 字段1,字段2,... FROM 表名 WHERE 字段x BETWEEN 值1 AND 值2LIKE模糊查询
作用: 可以在WHERE 子句中,使用LIKE关键字,进行模糊匹配查询
"%" ,表示匹配0或任意多个字符
"_" ,表示匹配单个字符
例1: 找出名字中含有 o 的英文名:
SELECT name FROM student WHERE name LIKE '%o%' ;
例2: 找出名字以 T 结尾的英文名:
SELECT name FROM student WHERE name LIKE '%T' ;
例3: 找出名字以 K 开始的英文名:
SELECT name FROM student WHERE name LIKE 'K%' ;
例4: 找出第二个字母是 A 的英文名:
SELECT name FROM student WHERE name LIKE '_A%' ;
例5: 找出第三个字母是 R 的英文名:
SELECT name FROM student WHERE name LIKE '__R%' ; //两个下划线IN 范围查询
作用: 查询的 字段x的值, 至少与括号中的一个值相同即可匹配
SELECT 字段1,字段2,... FROM 表名 WHERE 字段x IN (值1,值2,值3,...)聚合函数
| 函数名 | 作用 |
| AVG(某字段) | 返回某字段的平均值 |
| COUNT(某字段) | 返回某字段的行数 |
| MAX(某字段) | 返回某字段的最大值 |
| MIN(某字段) | 返回某字段的最小值 |
| SUM(某字段) | 返回某字段的和 |
#查询学生的总数。
SELECT COUNT(studentname) FROM student;
#查询课程编号为1的平均分。
SELECT AVG(studentresult) FROM result WHERE subjectno = 1;
#查询课程编号为1的最高分。
SELECT MAX(studentresult) FROM result WHERE subjectno = 1;
#查询课程编号为1的最低分。
SELECT MIN(studentresult) FROM result WHERE subjectno = 1;
#查询课程编号为1的成绩总和。
SELECT SUM(studentresult) FROM result WHERE subjectno = 1;数学函数
| 函数名 | 作 用 | 举 例 |
| CEIL(x) | 返回大于或等于数值x的最小整数 | SELECT CEIL(2.3) 返回：3 |
| FLOOR(x) | 返回小于或等于数值x的最大整数 | SELECT FLOOR(2.3) 返回：2 |
| RAND() | 返回0~1间的随机数 | SELECT RAND() 返回：0.5525468583708134 |
字符串函数
| 函 数 名 | 作 用 | 举 例 |
| CONCAT(str1, str1...strn) | 字符串连接 | SELECT CONCAT('My','S','QL'); 返回：MySQL |
| INSERT(str,pos,len,newstr) | 字符串替换 | SELECT INSERT( '这是SQL Server数据库', 3, //从第三个开始 (S) (包括第三个) 10, //从上面选择的位置开始(S),往后几个位置(包括开始和结束) (r) 'MySQL' //替换选中部分 ); 返回：这是MySQL数据库 |
| LOWER(str) | 将字符串转为小写 | SELECT LOWER('MySQL'); 返回：mysql |
| UPPER(str) | 将字符串转为大写 | SELECT UPPER('MySQL'); 返回：MYSQL |
| SUBSTRING (str,num,len) | 字符串截取 | SELECT SUBSTRING( 'JavaMySQLOracle',5,5); 返回：MySQL |
日期时间函数
| 函数名 | 作用 | 举例（结果与当前时间有关） |
| CURDATE() | 获取当前日期 | SELECT CURDATE(); 返回：2016-08-08 |
| CURTIME() | 获取当前时间 | SELECT CURTIME(); 返回：19:19:26 |
| NOW() | 获取当前日期和时间 | SELECT NOW(); 返回：2016-08-08 19:19:26 |
| WEEK(date) | 返回日期date为一年中的第几周 | SELECT WEEK(NOW()); 返回：26 |
| YEAR(date) | 返回日期date的年份 | SELECT YEAR(NOW()); 返回：2016 |
| HOUR(time) | 返回时间time的小时值 | SELECT HOUR(NOW()); 返回：9 |
| MINUTE(time) | 返回时间time的分钟值 | SELECT MINUTE(NOW()); 返回：43 |
| DATEDIFF(date1,date2) | 返回日期参数date1和date2之间相隔的天数 | SELECT DATEDIFF(NOW(), '2008-8-8’); 返回：2881 |
| ADDDATE(date,n) | 计算日期参数date加上n天后的日期 | SELECT ADDDATE(NOW(),5); 返回：2016-09-02 09:37:07 |
ORDER BY 排序
作用: 实现按一定顺序显示查询结果; 升序(ASC) 降序(DESC)
PS: 以ORDER BY 后面的第一个字段为主进行排序,只有第一个字段相等的时候,才会考虑逗号后的字段排序
LIMIT 分页查询
作用: LIMIT,用于限制查询结果返回的数量
select * from tableName limit [i] , n;
注:
tableName : 数据表
i : 为查询结果的索引值 (默认从0开始) //可以省略
n : 为查询结果返回的数量GROUP BY 分组查询
举例演示:找出每个工作岗位的工资和?
SELECT job, sum(sal) FROM emp GROUP BY job;
#GROUP BY job : 按照工作岗位的不同,分为不同的组,一组一组的求工资sal的和WHERE 与 HAVING 对比
>使用HAVING 可以对分完组之后的数据进行进一步过滤
>HAVING不能单独使用,HAVING不能代替WHERE,
>HAVING必须和GROUP BY 联合使用
>优化策略: WHERE 和 HAVING , 优先选择 WHERE , WHERE实在完成不了,再选择HAVING
全部Select关键字的执行顺序
一: 语法顺序
1. select
...
2. from
...
3. where
...
4. group by
...
5. having
...
6. order by
...
以上关键字只能按照这个顺序来, 不能颠倒
二: 执行顺序
1. from
2. where
3. group by //分组
4. having //过滤
5. select
6. order by //排序
👇👆
1> 从某张表中查询数据
2> 先经过where条件筛选出有价值的数据
3> 对这些有价值的数据进行分组
4> 分组之后可以使用 having 继续过滤筛选
5> select 查询出来
6> 最后排序输出
子查询
作用 : SELECT 语句中 嵌套 SELECT 语句 , 被嵌套的 SELECT语句 称为 子查询
子查询可以出现在哪里:
SELECT
...(SELECT)
FROM
...(SELECT)
WHERE
...(SELECT)EXISTS 子查询 : 只要 EXISTS(子查询) 子查询内返回的有值, 就是true, 没有则是 false
连接查询JOIN
多表连接查询,其实就是根据两张表之间的关联字段,用一个表连接另一个表,如果其中某些表还有其他的关联字段,则可以继续连接更多的表;
SELECT c1.tid,c1.tname,c2.ntitle,c3.ccontent FROM
表1 c1
RIGHT JOIN
表2 c2 ON c1.tid=c2.ntid
#此时,c1表根据两者的关系字段,连接了c2表,可以获取他们两个表中的数据,
(因为此处使用的是右连接,所以以c2表为主表展示数据,c1为从表,只匹配符合连接条件的数据,不符合的则不查询)
#如果c1或c2还有其他关系字段可以关联到其他表,可以继续连接,然后使用关联字段继续连接更多的表,整合更多的数据,
RIGHT JOIN
表3 c3 ON c3.cnid=c2.nid
如果还有其他的表和这三个表中某个表有关联字段,则可以继续连接!(连接查询,指定连接条件后,两个表连在一起,可以拿到两个表里的任何数据,可以看作是一张大表)
内连接 : INNER JOIN
左外连接 : LEFT JOIN
右外连接 : RIGHT JOIN
Mysql中的事务
事务小白详解
ps: 怕有小伙伴不理解事务 此处废话较多 大佬可以略过
一个事务其实就是一个完整的业务逻辑。 (就是要完成一件事儿,一件事儿就是一个事务)
什么是一个完整的业务逻辑？
假设转账，从A账户向B账户中转账10000.
将A账户的钱减去10000（update语句）
将B账户的钱加上10000（update语句）
这就是一个完整的业务逻辑。
以上的操作是一个最小的工作单元，要么同时成功，要么同时失败，不可再分。
这两个update语句要求必须同时成功或者同时失败，这样才能保证钱是正确的。
- 注意: 只有DML语句才会有事务这一说，其它语句和事务无关！！！
insert 增
delete 删
update 改
只有以上的三个语句和事务有关系，其它都没有关系。
其实说到底，说到本质上，一个事务其实就是多条DML语句同时成功，或者同时失败！
事务：就是批量的DML语句同时成功，或者同时失败！
在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。
在事务的执行过程中，我们可以提交事务，也可以回滚事务。
提交事务:
清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。
提交事务标志着，事务的结束。并且是一种全部成功的结束。
回滚事务: （回滚永远都是只能回滚到上一次的提交点！）
将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件
回滚事务标志着，事务的结束。并且是一种全部失败的结束。
事务的特性(ACID)
*Mysql事务
Mysql中支持事务的引擎有 InnoDB , BOD
开启事务的方式①
BEGIN; # 开启事务 标记一个事务的起点
.
.
.
#此处逻辑不会直接提交
.
.
.
ROLLBACK; # 回滚事务, 清除(撤销) 从 事务起点 至 该语句所作的所有更新 ,并释放由事务控制的资源
.
.
.
COMMIT; # 提交事务 提交所有更改,持久化到数据库开启事务的方式②
# MySQL默认情况下是自动提交事务的
# 什么是自动提交？
# 每执行一条DML语句，则提交一次！
# 此时只需关闭 自动提交即可
# autocommit = 0 ; 关闭自动提交 关闭后所执行的操作都不会被提交持久化到数据库中,需要手动提交
.
.
.
# autocommit = 1 ; 开启自动提交
SET autocommit = 0/1;
事务的隔离性
比如 : A教室和B教室中间有一道墙，这道墙可以很厚，也可以很薄。这就是事务的隔离级别。
这道墙越厚，表示隔离级别就越高。
mysql 默认的隔离级别为 : “Repeatable Read(可重读)
各个隔离级别所出现的问题
ps: 自行了解 此处不做详解
视图(view)
什么是视图 ? 其实就是站在不同的角度去看待同一份数据。
视图是一张虚拟表,视图中不存放数据, 数据是存放在视图所引用的原始表中的;
使用SQL 创建视图
create view 视图名 as select 语句
create view stu_view as select * from stu;
使用SQL 查看视图
select 字段名 from 视图名;
select * from stu_view ;使用SQL 删除视图
drop view 视图名;
drop view stu_view;可以面向视图增删改查,
对视图对象的增删改查，会导致原表被操作！（视图的特点：通过对视图的操作，会影响到原表数据。）
注意: 一般开发中视图仅作为查询, 增删改有很多限制, 比如 当视图数据来自多个表时, 不允许增删改等
什么是索引?
索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。
一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。
索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。
分类:
主键索引 (Primary Key)
某一个属性组能唯一标识一条记录
特点 :
最常见的索引类型
确保数据记录的唯一性
确定特定数据记录在数据库中的位置
唯一索引 (Unique)
作用 : 避免同一个表中某数据列中的值重复
与主键索引的区别
主键索引只能有一个
唯一索引可能有多个
常规索引 (Index)
作用 : 快速定位特定数据
注意:
不宜添加太多常规索引,影响数据的插入,删除和修改操作
全文索引 (FullText).
作用 : 快速定位特定数据
注意 :
只能用于MyISAM类型的数据表
只能用于CHAR , VARCHAR , TEXT数据列类型
适合大型数据集
总结:
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表建议不要加索引
- 索引一般应加在查找条件的字段
本社区面向用户介绍CSDN开发云部门内部产品使用和产品迭代功能，产品功能迭代和产品建议更透明和便捷
更多推荐
20
1- 0
已为社区贡献1条内容
所有评论(0)