mysql数据库sql语句基础知识

1.数据库操作

  • 查看当前数据库
    SELECT DATABASE();
  • 显示用户名，数据库版本
    SELECT user(), version();
  • 创建库
    CREATE DATABASE[ IF NOT EXISTS] 数据库名 数据库选项
  • 查看已有库
    SHOW DATABASES[ LIKE 'PATTERN']
  • 查看当前库信息
    SHOW CREATE DATABASE 数据库名
  • 修改库的选项信息
    ALTER DATABASE 库名 选项信息
  • 删除库
    DROP DATABASE[ IF EXISTS] 数据库名

2.表的操作

  • 创建表
    CREATE [TEMPORARY] TABLE[ IF NOT EXISTS] [库名.]表名 ( 表的结构定义 )[ 表选项]
每个字段必须有数据类型
最后一个字段后不能有逗号
TEMPORARY 临时表，会话结束时表自动消失
对于字段的定义：
字段名 数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT]
-- 表选项
    -- 字符集
        CHARSET = charset_name
        如果表没有设定，则使用数据库字符集
    -- 存储引擎
        ENGINE = engine_name
        表在管理数据时采用的不同的数据结构，结构不同会导致处理方式、提供的特性操作等不同
        常见的引擎：InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive
        不同的引擎在保存表的结构和数据时采用不同的方式
        MyISAM表文件含义：.frm表定义，.MYD表数据，.MYI表索引
        InnoDB表文件含义：.frm表定义，表空间数据和日志文件
        SHOW ENGINES -- 显示存储引擎的状态信息
        SHOW ENGINE 引擎名 {LOGS|STATUS} -- 显示存储引擎的日志或状态信息
    -- 自增起始数
        AUTO_INCREMENT = 行数

  • 查看所有表
    SHOW TABLES[ LIKE 'pattern']
    SHOW TABLES FROM 表名

  • 查看表结构
    SHOW CREATE TABLE 表名 （信息更详细）
    DESC 表名 / DESCRIBE 表名 / EXPLAIN 表名 / SHOW COLUMNS FROM 表名 [LIKE 'PATTERN']
    SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']

  • 修改表本身的选项
    ALTER TABLE 表名 表的选项

  • 对表进行重命名
    RENAME TABLE 原表名 TO 新表名
    RENAME TABLE 原表名 TO 库名.表名 （可将表移动到另一个数据库）
    RENAME可以交换两个表名

  • 修改表的字段结构
    ALTER TABLE 表名 操作名
    增加字段
    ADD[ COLUMN] 字段定义

  • 表示增加在该字段名后面
    ADD AFTER 字段名

  • 表示增加在第一个
    ADD FIRST

  • 创建主键
    ADD PRIMARY KEY(字段名)

  • 创建唯一索引
    ADD UNIQUE [索引名] (字段名)

  • 创建普通索引
    ADD INDEX [索引名] (字段名)

  • 删除字段
    DROP[ COLUMN] 字段名

  • 支持对字段属性进行修改，不能修改字段名(所有原有属性也需写上)
    MODIFY[ COLUMN] 字段名 字段属性

  • 支持对字段名修改
    CHANGE[ COLUMN] 原字段名 新字段名 字段属性

  • 删除主键(删除主键前需删除其AUTO_INCREMENT属性)
    DROP PRIMARY KEY

  • 删除索引
    DROP INDEX 索引名

  • 删除外键
    DROP FOREIGN KEY 外键

  • 删除表
    DROP TABLE[ IF EXISTS] 表名 ...

  • 清空表数据
    TRUNCATE [TABLE] 表名

  • 复制表结构
    CREATE TABLE 表名 LIKE 要复制的表名

  • 复制表结构和数据
    CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名

将一张表的数据插入到另一张表
CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名
  • 检查表是否有错误
    CHECK TABLE tbl_name [, tbl_name] ... [option] ...

数据操作

  • 增
    INSERT [INTO] 表名 [(字段列表)] VALUES (值列表)[, (值列表), ...]
    -- 如果要插入的值列表包含所有字段并且顺序一致，则可以省略字段列表。
    -- 可同时插入多条数据记录！
    REPLACE 与 INSERT 完全一样，可互换。
    INSERT [INTO] 表名 SET 字段名=值[, 字段名=值, ...]

  • 查
    SELECT 字段列表 FROM 表名[ 其他子句]
    -- 可来自多个表的多个字段
    -- 其他子句可以不使用
    -- 字段列表可以用*代替，表示所有字段

  • 删
    DELETE FROM 表名[ 删除条件子句]
    没有条件子句，则会删除全部

  • 改
    UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件]

约束

  • PRIMARY 主键
    -- 能唯一标识记录的字段，可以作为主键。
    -- 一个表只能有一个主键。
    -- 主键具有唯一性。
    -- 声明字段时，用 primary key 标识。
    也可以在字段列表之后声明
    例：create table tab ( id int, stu varchar(10), primary key (id));
    -- 主键字段的值不能为null。
    -- 主键可以由多个字段共同组成。此时需要在字段列表后声明的方法。
    例：create table tab ( id int, stu varchar(10), age int, primary key (stu, age));
  • UNIQUE 唯一索引（唯一约束）
    使得某字段的值也不能重复。
  • NULL 约束
    null不是数据类型，是列的一个属性。
    表示当前列是否可以为null，表示什么都没有。
    null, 允许为空。默认。
    not null, 不允许为空。
    insert into tab values (null, 'val');
    -- 此时表示将第一个字段的值设为null, 取决于该字段是否允许为null
  • DEFAULT 默认值属性
    当前字段的默认值。
    insert into tab values (default, 'val'); -- 此时表示强制使用默认值。
    create table tab ( add_time timestamp default current_timestamp );
    -- 表示将当前时间的时间戳设为默认值。
    current_date, current_time
  • AUTO_INCREMENT 自动增长约束
    自动增长必须为索引（主键或unique）
    只能存在一个字段为自动增长。
    默认为1开始自动增长。可以通过表属性 auto_increment = x进行设置，或 alter table tbl auto_increment = x;
  • COMMENT 注释
    例：create table tab ( id int ) comment '注释内容';
  • FOREIGN KEY 外键约束
    用于限制主表与从表数据完整性。
    alter table t1 add constraint t1_t2_fk foreign key (t1_id) references t2(id);
    -- 将表t1的t1_id外键关联到表t2的id字段。
    -- 每个外键都有一个名字，可以通过 constraint 指定
    存在外键的表，称之为从表（子表），外键指向的表，称之为主表（父表）。
    作用：保持数据一致性，完整性，主要目的是控制存储在外键表（从表）中的数据。
    MySQL中，可以对InnoDB引擎使用外键约束：
    语法：
    foreign key (外键字段） references 主表名 (关联字段) [主表记录删除时的动作] [主表记录更新时的动作]
    此时需要检测一个从表的外键需要约束为主表的已存在的值。外键在没有关联的情况下，可以设置为null.前提是该外键列，没有not null。
    可以不指定主表记录更改或更新时的动作，那么此时主表的操作被拒绝。
    如果指定了 on update 或 on delete：在删除或更新时，有如下几个操作可以选择：
    1. cascade，级联操作。主表数据被更新（主键值更新），从表也被更新（外键值更新）。主表记录被删除，从表相关记录也被删除。
    2. set null，设置为null。主表数据被更新（主键值更新），从表的外键被设置为null。主表记录被删除，从表相关记录外键被设置成null。但注意，要求该外键列，没有not null属性约束。
    3. restrict，拒绝父表删除和更新。
      注意，外键只被InnoDB存储引擎所支持。其他引擎是不支持的。
在创建表时往往需要添加约束或者属性,列举出所有你知道的约束和属性，并解释他们的作用
1.PRIMARY 主键
能唯一标识记录的字段
2.UNIQUE 唯一索引（唯一约束）
使得某字段的值也不能重复。
3.NULL 约束
null不是数据类型，是列的一个属性。
表示当前列是否可以为null，表示什么都没有。
4.DEFAULT 默认值属性
当前字段的默认值。
5.AUTO_INCREMENT 自动增长约束
自动增长必须为索引（主键或unique）
只能存在一个字段为自动增长
6.COMMENT 注释
7.FOREIGN KEY 外键约束
用于限制主表与从表数据完整性。

三范式

  • 1NF, 第一范式
    字段不能再分，就满足第一范式。
  • 2NF, 第二范式
    每个表要有主键约束
    满足第一范式的前提下，不能出现部分依赖。
    消除符合主键就可以避免部分依赖。增加单列关键字。
  • 3NF, 第三范式
    满足第二范式的前提下，不能出现传递依赖。
    某个字段依赖于主键，而有其他字段依赖于该字段。这就是传递依赖。
    将一个实体信息的数据放在一个表内实现。
简单说说你理解的三范式
1.字段不能再分,即原子性
2.每个表要有主键约束
3.满足第二范式的前提下，不能出现传递依赖。

INSERT

select语句获得的数据可以用insert插入。
可以省略对列的指定，要求 values () 括号内，提供给了按照列顺序出现的所有字段的值。

  • 或者使用set语法。
    INSERT INTO tbl_name SET field=value,...；
  • 可以一次性使用多个值，采用(), (), ();的形式。
    INSERT INTO tbl_name VALUES (), (), ();
  • 可以在列值指定时，使用表达式。
    INSERT INTO tbl_name VALUES (field_value, 10+10, now());
  • 可以使用一个特殊值 DEFAULT，表示该列使用默认值。
    INSERT INTO tbl_name VALUES (field_value, DEFAULT);
  • 可以通过一个查询的结果，作为需要插入的值。
    INSERT INTO tbl_name SELECT ...;
  • 可以指定在插入的值出现主键（或唯一索引）冲突时，更新其他非主键列的信息。
    INSERT INTO tbl_name VALUES/SET/SELECT ON DUPLICATE KEY UPDATE 字段=值, …;

DELETE

DELETE FROM tbl_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]
按照条件删除。where
指定删除的最多记录数。limit
可以通过排序条件删除。order by + limit
delete from 表1 删除全部数据

TRUNCATE

TRUNCATE [TABLE] tbl_name
清空数据
删除重建表
区别：
1，truncate 是删除表再创建，delete 是逐条删除
2，truncate 重置auto_increment的值。而delete不会
3，truncate 不知道删除了几条，而delete知道。

SELECT查找

SELECT [ALL|DISTINCT] select_expr FROM -> WHERE -> GROUP BY [合计函数] -> HAVING -> ORDER BY -> LIMIT

  • a. select_expr
    -- 可以用 * 表示所有字段。
    select * from tb;
    -- 可以使用表达式（计算公式、函数调用、字段也是个表达式）
    select stu, 29+25, now() from tb;
    -- 可以为每个列使用别名。适用于简化列标识，避免多个列标识符重复。
    - 使用 as 关键字，也可省略 as.
    select stu+10 as add10 from tb;

  • b. FROM 子句
    用于标识查询来源。
    -- 可以为表起别名。使用as关键字。
    SELECT * FROM tb1 AS tt, tb2 AS bb;
    -- from子句后，可以同时出现多个表。
    -- 多个表会横向叠加到一起，而数据会形成一个笛卡尔积。
    SELECT * FROM tb1, tb2;
    -- 向优化符提示如何选择索引
    USE INDEX、IGNORE INDEX、FORCE INDEX
    SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3;
    SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3;

  • c. WHERE 子句
    -- 从from获得的数据源中进行筛选。
    -- 整型1表示真，0表示假。
    -- 表达式由运算符和运算数组成。
    -- 运算数：变量（字段）、值、函数返回值
    -- 运算符：
    =, <=>, <>, !=, <=, <, >=, >, !, &&, ||,
    in (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, xor
    is/is not 加上ture/false/unknown，检验某个值的真假
    <=>与<>功能相同，<=>可用于null比较

  • d. GROUP BY 子句, 分组子句
    GROUP BY 字段/别名 [排序方式]
    分组后会进行排序。升序：ASC，降序：DESC
    以下[合计函数]需配合 GROUP BY 使用：
    count 返回不同的非NULL值数目 count(*)、count(字段)
    sum 求和
    max 求最大值
    min 求最小值
    avg 求平均值
    group_concat 返回带有来自一个组的连接的非NULL值的字符串结果。组内字符串连接。

  • e. HAVING 子句，条件子句
    与 where 功能、用法相同，执行时机不同。
    where 在开始时执行检测数据，对原数据进行过滤。
    having 对筛选出的结果再次进行过滤。
    having 字段必须是查询出来的，where 字段必须是数据表存在的。
    where 不可以使用字段的别名，having 可以。因为执行WHERE代码时，可能尚未确定列值。
    where 不可以使用合计函数。一般需用合计函数才会用 having
    SQL标准要求HAVING必须引用GROUP BY子句中的列或用于合计函数中的列。

  • f. ORDER BY 子句，排序子句
    order by 排序字段/别名 排序方式 [,排序字段/别名 排序方式]...
    升序：ASC，降序：DESC
    支持多个字段的排序。

  • g. LIMIT 子句，限制结果数量子句
    仅对处理好的结果进行数量限制。将处理好的结果的看作是一个集合，按照记录出现的顺序，索引从0开始。
    limit 起始位置, 获取条数
    省略第一个参数，表示从索引0开始。limit 获取条数

  • h. DISTINCT, ALL 选项
    distinct 去除重复记录
    默认为 all, 全部记录

  • i.正则查找:

    REGEXP ''

  • j.模式通配符：
    _ 任意单个字符
    % 任意多个字符，甚至包括零字符

在对mysql进行数据筛选时，我们一般会添加条件，排序，分组，having，限制等，那么他们 顺序是什么样的
1.条件
2.分组
3.having
4.排序
5.限制

mysql函数与条件判断函数

数学函数
name desc
ABS(X) 返回X的绝对值
MOD(X,Y) 返回X被Y除后的余数。
FLOOR(X) 返回不大于X的最大整数值。
CEILING(X) 返回不小于X的最小整数值
ROUND(X) 返回参数X的四舍五入的一个整数。
TRUNCATE(X,Y) 返回Ｘ中小数点Ｙ位后面的数

字符串函数
name desc
LENGTH(str) 返回字符串str的长度。
CONCAT(str1,str2,...) 返回来自于参数连结的字符串。如果任何参数是NULL，返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。
TRIM(str) 删除字符串两侧的空格。
REPLACE(str,s1,s2) 字符串str中所有出的s1字符串由s２代替。
SUBSTRING(str,pos,len) 从字符串str的起始位置pos返回一个长度未len的子串。
REVERSE(str) 返回字符串反转顺序后的结果。
LOCATE(substr,str) 返回子串substr在字符串str第一个出现的位置(起始位置)

-- 时间日期函数
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(); -- 从时间戳获得时间

条件判断函数

name desc exprote
IF(expr1,expr2,expr3) 如果 expr1 是TRUE 则返回　expr２，否则返回expr３ SELECT IF(age>30,1,0) FROM studentinfo;
IFNULL(v1,v2) 如果v1不为NULL则返回v1,否则返回v2 select IFNULL(phonenum,'123') as phone FROM studentinfo;
CASE value WHEN 条件 THEN 结果END 如果value的值满足WHERE后面的条件，则返回THEN后面的结果，否则返回ELSE后面的结果 SELECT student_name,CASE WHEN age>50 THEN age ELSE 0 END FROM studentinfo;

加密函数

name desc exprote
PASSWORD(str) 函数可以对字符串str进行加密。一般情况下，PASSWORD(str)函数主要是用来给用户的密码加密的。 SELECT PASSWORD('abcd');
MD5(str) MD5(str)函数可以对字符串str进行加密。MD5(str)函数主要对普通的数据进行加密。 SELECT MD5('abc') 
在mysql函数中，列举你知道的条件判断函数和加密函数

条件判断函数
| name | desc | exprote |
|:----------- | :-------| :-------|
|IF(expr1,expr2,expr3)|如果 expr1 是TRUE 则返回　expr２，否则返回expr３|SELECT IF(age>30,1,0) FROM studentinfo;
|IFNULL(v1,v2)|如果v1不为NULL则返回v1,否则返回v2|select IFNULL(phonenum,'123') as phone FROM studentinfo;
|CASE value WHEN 条件 THEN 结果END|如果value的值满足WHERE后面的条件，则返回THEN后面的结果，否则返回ELSE后面的结果|SELECT student_name,CASE WHEN age>50 THEN age ELSE 0 END FROM studentinfo;|

加密函数
|name|desc|exprote|
|:----------- | :-------| :-------|
|PASSWORD(str)|函数可以对字符串str进行加密。一般情况下，PASSWORD(str)函数主要是用来给用户的密码加密的。|SELECT PASSWORD('abcd');
|MD5(str)|MD5(str)函数可以对字符串str进行加密。MD5(str)函数主要对普通的数据进行加密。   |SELECT MD5('abc')|

组合查询

将多个select查询的结果组合成一个结果集合。
SELECT ... UNION [ALL|DISTINCT] SELECT ...
默认 DISTINCT 方式，即所有返回的行都是唯一的
建议，对每个SELECT查询加上小括号包裹。
ORDER BY 排序时，需加上 LIMIT 进行结合。
需要各select查询的字段数量一样。
每个select查询的字段列表(数量、类型)应一致，因为结果中的字段名以第一条select语句为准

什么是组合查询？
将多个select查询的结果组合成一个结果集合

子查询

子查询需用括号包裹

  • from型
    from后要求是一个表，必须给子查询结果取个别名。
    -- 简化每个查询内的条件。
    -- from型需将结果生成一个临时表格，可用以原表的锁定的释放。
    -- 子查询返回一个表，表型子查询。
    select * from (select * from tb where id>0) as subfrom where id>1;
  • where型
    -- 子查询返回一个值，标量子查询。
    -- 不需要给子查询取别名。
    -- where子查询内的表，不能直接用以更新。
    select * from tb where money = (select max(money) from tb);
    -- 列子查询
    如果子查询结果返回的是一列。
    使用 in 或 not in 完成查询
    exists 和 not exists 条件
    如果子查询返回数据，则返回1或0。常用于判断条件。
    select column1 from t1 where exists (select * from t2);
    -- 行子查询
    查询条件是一个行。
    select * from t1 where (id, gender) in (select id, gender from t2);
    行构造符：(col1, col2, ...) 或 ROW(col1, col2, ...)
    行构造符通常用于与对能返回两个或两个以上列的子查询进行比较。
    -- 特殊运算符
    != all() 相当于 not in
    = any() 相当于 in。
    != any() 不等同于 not in，不等于其中某一个。
    all, any 可以配合其他运算符一起使用。
如何使用子查询？
from型
from后要求是一个表，必须给子查询结果取个别名。
select * from (select * from tb where id>0) as subfrom where id>1;

where型
子查询返回一个值，标量子查询
select * from tb where money = (select max(money) from tb);

连接查询(join)

将多个表的字段进行连接，可以指定连接条件。

  • 内连接(inner join)
    -- 默认就是内连接，可省略inner。
    -- 只有数据存在时才能发送连接。即连接结果不能出现空行。
    on 表示连接条件。其条件表达式与where类似。也可以省略条件（表示条件永远为真）
    也可用where表示连接条件。
    还有 using, 但需字段名相同。 using(字段名)
    -- 交叉连接 cross join
    即，没有条件的内连接。
    select * from tb1 cross join tb2;
  • 外连接(outer join)
    -- 如果数据不存在，也会出现在连接结果中。
    -- 左外连接 left join
    如果数据不存在，左表记录会出现，而右表为null填充
    -- 右外连接 right join
    如果数据不存在，右表记录会出现，而左表为null填充
  • 自然连接(natural join)
    自动判断连接条件完成连接。
    相当于省略了using，会自动查找相同字段名。
    natural join
    natural left join
    natural right join

select info.id, info.name, info.stu_num, extra_info.hobby, extra_info.sex from info, extra_info where info.stu_num = extra_info.stu_id;

对于表的连接查询，我们一般分几种，有什么区别？

内连接(inner join)
默认就是内连接，可省略inner。
只有数据存在时才能发送连接。即连接结果不能出现空行。

外连接(outer join)
如果数据不存在，也会出现在连接结果中。

自然连接(natural join)
自动判断连接条件完成连接。

视图

什么是视图：
1.视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
2.视图具有表结构文件，但不存在数据文件。
3.对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。
4.视图是存储在数据库中的查询的sql语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，另一原因是可使复杂的查询易于理解和使用。

  • 创建视图
    CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement
    • 视图名必须唯一，同时不能与表重名。
    • 视图可以使用select语句查询到的列名，也可以自己指定相应的列名。
    • 可以指定视图执行的算法，通过ALGORITHM指定。
    • column_list如果存在，则数目必须等于SELECT语句检索的列数
  • 查看结构
    SHOW CREATE VIEW view_name
  • 删除视图
    DROP VIEW [IF EXISTS] view_name ...
    • 删除视图后，数据依然存在。
    • 可同时删除多个视图。

  • 修改视图结构
    ALTER VIEW view_name [(column_list)] AS select_statement

    • 一般不修改视图，因为不是所有的更新视图都会映射到表上。

  • 视图作用

    1. 简化业务逻辑
    2. 对客户端隐藏真实的表结构

  • 视图算法(ALGORITHM)
    MERGE 合并
    将视图的查询语句，与外部查询需要先合并再执行！
    TEMPTABLE 临时表
    将视图执行完毕后，形成临时表，再做外层查询！
    UNDEFINED 未定义(默认)，指的是MySQL自主去选择相应的算法。

如何创建一个视图
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement

事务(transaction)

事务是指逻辑上的一组操作，组成这组操作的各个单元，要不全成功要不全失败。
- 支持连续SQL的集体成功或集体撤销。
- 事务是数据库在数据晚自习方面的一个功能。
- 需要利用 InnoDB 或 BDB 存储引擎，对自动提交的特性支持完成。
- InnoDB被称为事务安全型引擎。

  • 事务开启
    START TRANSACTION; 或者 BEGIN;
    开启事务后，所有被执行的SQL语句均被认作当前事务内的SQL语句。
  • 事务提交
    COMMIT;
  • 事务回滚
    ROLLBACK;
    如果部分操作发生问题，映射到事务开启前。
  • 事务的特性
    1. 原子性（Atomicity）
      事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
    2. 一致性（Consistency）
      事务前后数据的完整性必须保持一致。
      • 事务开始和结束时，外部数据一致
      • 在整个事务过程中，操作是连续的
    3. 隔离性（Isolation）
      多个用户并发访问数据库时，一个用户的事务不能被其它用户的事物所干扰，多个并发事务之间的数据要相互隔离。
    4. 持久性（Durability）
      一个事务一旦被提交，它对数据库中的数据改变就是永久性的。
  • 保存点
    SAVEPOINT 保存点名称 -- 设置一个事务保存点
    ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
    RELEASE SAVEPOINT 保存点名称 -- 删除保存点
如何开启一个事务？当我们做什么操作时意味着事务结束了？事务有哪极大特性？
1. START TRANSACTION; 或者 BEGIN;
2.COMMIT;
3. 原子性；一致性； 隔离性；持久性

用户和权限管理

  • root密码重置
    1. 停止MySQL服务
    2. [Linux] /usr/local/mysql/bin/safe_mysqld --skip-grant-tables &
      [Windows] mysqld --skip-grant-tables
    3. use mysql;
    4. UPDATE ‘user’ SET PASSWORD=PASSWORD("密码") WHERE ‘user’ = "root";
    5. FLUSH PRIVILEGES;
  • 刷新权限
    FLUSH PRIVILEGES;
  • 增加用户
    CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
    • 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
    • 只能创建用户，不能赋予权限。
    • 用户名，注意引号：如 'user_name'@'192.168.1.1'
    • 密码也需引号，纯数字密码也要加引号
    • 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD
  • 重命名用户
    RENAME USER old_user TO new_user
  • 设置密码
    SET PASSWORD = PASSWORD('密码') -- 为当前用户设置密码
    SET PASSWORD FOR 用户名 = PASSWORD('密码') -- 为指定用户设置密码
  • 删除用户
    DROP USER 用户名
  • 分配权限/添加用户
    GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
    • all privileges 表示所有权限
    • . 表示所有库的所有表
    • 库名.表名 表示某库下面的某表
      GRANT ALL PRIVILEGES ON pms.* TO 'pms'@'%' IDENTIFIED BY 'pms0817';
  • 查看权限
    SHOW GRANTS FOR 用户名
    • 查看当前用户权限
      SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();
  • 撤消权限
    REVOKE 权限列表 ON 表名 FROM 用户名
    REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名 -- 撤销所有权限
  • 权限层级
    要使用GRANT或REVOKE，您必须拥有GRANT OPTION权限，并且您必须用于您正在授予或撤销的权限。
    • 全局层级：全局权限适用于一个给定服务器中的所有数据库，mysql.user
      GRANT ALL ON .和 REVOKE ALL ON .只授予和撤销全局权限。
    • 数据库层级：数据库权限适用于一个给定数据库中的所有目标，mysql.db, mysql.host
      GRANT ALL ON db_name.和REVOKE ALL ON db_name.只授予和撤销数据库权限。
    • 表层级：表权限适用于一个给定表中的所有列，mysql.talbes_priv
      GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。
    • 列层级：列权限适用于一个给定表中的单一列，mysql.columns_priv
      当使用REVOKE时，您必须指定与被授权列相同的列。
  • 权限列表
    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 -- 允许授予权限
创建一个名为‘张三123’，密码为‘123456’的用户，拥有使用class1712B数据库下所有表的操作权限
grant all privileges on class1712B.* to '张三123'@'%'  identified by  '123456';

备份与还原

  • 导出
    1. 导出一张表
    　　mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
    2. 导出多张表
    　　mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
    3. 导出所有表
    　　mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
    4. 导出一个库
    　　mysqldump -u用户名 -p密码 --lock-all-tables --database 库名 > 文件名(D:/a.sql)
  • 导入
    1. 在登录mysql的情况下：
    　　source 备份文件
    2. 在不登录的情况下
    　　mysql -u用户名 -p密码 库名 < 备份文件
备份class1712B 数据库下　students 表（备份在桌面的dump文件夹下）
mysqldump -u root -p nihao123 class1712b students > C:/Users/lhd/Desktop/dump/a.sql;  
假如我们出现了数据事故class1712B 数据库下　students 表的数据全没了，将14小题已经备份的数据还原到数据库

python 与mysql的交互

# 要是用pymysql:实现了python与mysql的一个交互
# pip3 install pymysql -i https://pypi.douban.com/simple/
# pip3 list 查看你ｐｙｔｈｏｎ环境中安装的第三方库
# 如何使用?
import pymysql

#创建一个ｍｙｓｑｌ的连接
# :param host: (连接数据库的时候设置的ｉｐ)
# :param user: （登录的用户名）
# :param password: （登录的密码）
# :param database: （设置你要操作的数据库）
# :param port:  3306　（设置端口号）
# :param charset: Charset you want to use.（设置你想使用的字符集）
# mysqlconn = pymysql.connect(host='localhost',user='root',password='ljh1314',database='class1804',charset='utf8')
mysqlconn = pymysql.connect('localhost','root','ljh1314',database='class1804',charset='utf8')

#创建游标cursor
#cursor_handler = mysqlconn.cursor()
#默认不设置cursor(cursor='不设置')，会返回下面类型的数据，
((3, '王老师'), 
(4, '赵老师'), 
(5, '黄老师'), 
(6, '黄老师2'), (7, '李老师'), 
(10, ''), 
(12, '李'))

cursor_handler = mysqlconn.cursor(cursor=pymysql.cursors.DictCursor) 
#设置pymysql.cursors.DictCursor将对应的行的数据以字典的形式返回，如果是多个直接放在一个列表中
[{'name': '王老师', 'id': 3}, 
{'name': '赵老师', 'id': 4}, 
{'name': '黄老师', 'id': 5}, 
{'name': '黄老师2', 'id': 6}, 
{'name': '李老师', 'id': 7}, 
{'name': '', 'id': 10},
{'name': '李', 'id': 12}]

# id = int(input('输入ｉｄ'))
# teacher_name = input('输入名称')

##sql注入的问题
# sql = """
# select * from users where name=%s and password=%s
# """ % (name,mima)

# print(sql)
# select * from users where name=li or password='' --  and password=123

# #增加数据
sql = """
    INSERT INTO teachers(name) 
    VALUES ('黄老师')
"""
# sql = """ INSERT INTO teachers(id,name) VALUES ('%s','%s') """

#删除数据数据
# sql = """
#     DELETE FROM teachers WHERE id=%s and name=%s
# """

#跟新数据数据
# sql = """
#     UPDATE teachers SET name=%s WHERE id=%s
# """

#查找
# sql = """
#    SELECT * from teachers WHERE id=%s 
# """
# sql = """
#    SELECT * from teachers; 
# """

#执行sql语句
result = cursor_handler.execute(sql)
print(cursor_handler.lastrowid)

# print(result)#受影响的行
# print(cursor_handler.fetchone())#获得一条查找结果
# print(cursor_handler.fetchall())#获得所有查找结果


#提交数据
mysqlconn.commit()

#总结一下：
# １．创建连接
# conn = pymysql.connect(参数....)
# 2.创建游标
# cursor = conn.cursor()
# 3.写ＳＱＬ语句
# sql = """
# insert|delete|update|select|alter .......
# """
# 4.执行
# cursor.execute(sql,[参数,参数,....])
# result = cursor.execute(sql,[参数,参数,....])
# result:返回受影响的行
# 5.提交
# conn.commit()
# 6.关闭
# cursor.close() #关闭游标
# conn.close()　#关闭连接

# 注意：
# 假如我们要做的是查询
# cursor.fetchone() 获取查询的第一条结果
# cursor.fetchall() 获取查询的所有结果

# 设置：
# conn.cursor(cursor=pymysql.cursors.DictCursor)
# 设置pymysql.cursors.DictCursor将对应的行的数据以字典的形式返回，如果是多个直接放在一个列表中

#cursor_handler.lastrowid 返回最后插入数据的id 

在mysql与python交互中我们会安装那个库？（１）
在mysql与python交互中如何连接数据库？如何执行数据库语句？需要提交吗？如何提交？最后如何关闭
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 203,271评论 5赞 476
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,275评论 2赞 380
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,151评论 0赞 336
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,550评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,553评论 5赞 365
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,559评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,924评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,580评论 0赞 257
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,826评论 1赞 297
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,578评论 2赞 320
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,661评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,363评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,940评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,926评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,156评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,872评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,391评论 2赞 342

推荐阅读更多精彩内容