MySQL学习总结-CSDN博客

本文链接：https://blog.csdn.net/weixin_44162346/article/details/88971656
索引~~~移步林海峰老师博客
http://www.cnblogs.com/linhaifeng/articles/7274563.html
mysqladmin -uroot -p password "123" 默认空密码

windows启动跳过密码
mysqld --skip-grant-table
update mysql.user set password=password("新密码") where user="root" and host="localhost";
flush privileges;

mysql -uroot -p123 -h 127.0.0.1 -P 3306 默认格式

MAC
停止服务（系统偏好设置）
mysqld_safe --skip-grant-tables 命令行启动
5.6及以下版本：
mysql> update mysql.user set password=password("0219") where user="root" and host="localhost";
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
5.7版本更改为authentication
mysql> update mysql.user set authentication_string=password("0219") where user="root" and host="localhost";

统一字符编码
\s 查看字符编码

#1. 修改配置文件
[mysqld]
default-character-set=utf8 
[client]
default-character-set=utf8 
[mysql]
default-character-set=utf8

#mysql5.5以上：修改方式有所改动
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

#2. 重启服务
#3. 查看修改结果：
\s
show variables like '%char%'


SQL语句
操作文件夹（库）
增
    create database db1 charset utf8;
查
    show create database db1;
    show databases;
改
    alter database db1 charset gbk;
删
    drop database db1;

操作文件（表）
切换文件夹: use db1;
查看当前所在文件夹： select database();
增
    create table t1(id int,name char);
查
    show create table t1;
    show tables;
    describe t1;    desc t1;
改
    alter table t1 modify name char(6);
    alter table t1 change name NAME char(7);
删
    drop table t1;

操作文件内容（记录）
增
    insert t1(id,name) values(1,'wyq'),(2,'yqw'),(3,'qyw');
    insert t1 values(1,'wyq'),(2,'yqw'),(3,'qyw');
查
    select id,name from t1;
    select id,name from db1.t1;
改
    update db1.t1 set name='NB';
    update db1.t1 set name="wyq" where id=2;
删
    delete from t1 where id=1;
    delete from t1;

库操作

库的增删改查

系统数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
information_schema         虚拟库，不占磁盘空间（安装目录无文件夹）
mysql                      授权库
performance_schema         5.5后新增的库，收集数据库服务器性能参数
test                    mysql自动创建的测试库

数据库命名规则：
    可以由字母、数字、下划线、@、#、$
    区分大小写
    唯一性
    不能使用关键字  如create  select
    不能单独使用数字
    最长128位

什么是存储引擎
存储引擎就是表的类型

查看mysql支持的存储引擎
show engines;
默认InnoDB

指定表类型/存储引擎
create table t1(id int)engine=innodb;
create table t2(id int)engine=memory;
create table t3(id int)engine=blackhole;
create table t4(id int)engine=myisam;

insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);

表的增删改查
修改表结构
语法：
1. 修改表名
    alter table 表名
                    rename 新表名;
2. 增加字段
    alter table 表名
                    add 字段名 数据类型 [完整性约束条件],
                    add 字段名 数据类型 [完整性约束条件];

                    add 字段名 数据类型 [完整性约束条件] first;
                    add 字段名 数据类型 [完整性约束条件] after 字段名;
3. 删除字段
    alter table 表名
                    drop 字段名;
4. 修改字段
    alter table 表名
                    modify 字段名 数据类型 [完整性约束条件];
                    change 旧字段名 新字段名 旧数据类型 [完整性约束条件];
                    change 旧字段名 新字段名 新数据类型 [完整性约束条件];

整数类型
    tinyint        1字节
    smallint    2字节
    mediumint    3字节
    int         4字节
    bigint         8字节
    
    无符号 unsigned    insert into t1(x tinyint ensigned);
    用0填充    zerofill 
    对于整型，创建表的时候不需要加宽度
    那个宽度并不是存储宽度，指的是显示宽度
    不指定宽度，默认是最大宽度

浮点型
    float
        float[(M,D)][unsigned][zerofill]
        单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数，m最大值为255，d最大值为30
        随着小数的增多，精度会变的不准确
    double
        double[(M,D)][unsigned][zerofill]
        双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数，m最大值为255，d最大值为30
        随着小数的增多，精度会变的不准确，精度比float高
    decimal
        decimal[(M,D)][unsigned][zerofill]
        准确的小数值，没事数字总个数（负号不算），d是小数点后个数，m最大值为65，d最大值为30

    三种浮点型精度对比
    mysql> desc t8;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| x     | float(255,30) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.02 sec)

mysql> desc t9;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| x     | double(255,30) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> desc t10;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| x     | decimal(65,30) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+

mysql> select * from t8;
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from t9;
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from t10;
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
1 row in set (0.01 sec)

日期类型
date time datetime timestamp year

create table student(
    id int,
    name char(6),
    born_year year,
    birth_date date,
    class_time time,
    reg_time datetime
);


insert into student vlaues
(1,'wyq',now(),now(),now(),now());


+------+------+-----------+------------+------------+---------------------+
| id   | name | born_year | birth_date | class_time | reg_time            |
+------+------+-----------+------------+------------+---------------------+
|    1 | wyq  |      2018 | 2018-12-31 | 13:26:40   | 2018-12-31 13:26:40 |
+------+------+-----------+------------+------------+---------------------+


mysql终止运行命令  \c

字符类型
    char     定长
    varchar     变长

create table t13(name char(5));
create table t14(name varchar(5));

insert into t13 values('王先生 ');
insert into t14 values('王先生 ');

mysql> select char_length(name) from t13;
+-------------------+
| char_length(name) |
+-------------------+
|                 3 |
+-------------------+
1 row in set (0.01 sec)

mysql> select char_length(name) from t14;
+-------------------+
| char_length(name) |
+-------------------+
|                 4 |
+-------------------+
1 row in set (0.00 sec)
函数：
 length:查看字节数
 char_length:查看字符数

 char类型
     优点：简单粗暴
     缺点：浪费空间
 varchar
     优点：更加节省空间
     缺点：存取都慢
 大部分场景都用char类型

 建表的时候尽量定长的数据往前放


枚举类型与集合类型
enum 枚举类型，单选，只能在给定的范围中选择一个值
set 集合类型，多选，在给定的范围内可以选择一个或多个值

create table consumer(
    id int,
    name char(16),
    sex enum('male','female'),
    level enum('vip','vp'),
    hobbies set('play','run','sleep','music')
);

insert into consumer values
(1,'wyq','male','vip','run,sleep,music');
mysql> select * from consumer;
+------+------+------+-------+-----------------+
| id   | name | sex  | level | hobbies         |
+------+------+------+-------+-----------------+
|    1 | wyq  | male | vip   | run,sleep,music |
+------+------+------+-------+-----------------+
1 row in set (0.00 sec)

约束条件
    not null:默认不允许为空
    default:默认值
        create table t16(
            id int,
            name char(16),
            sex enum('male','female') not null default 'male'
        );
        mysql> desc t16;
        +-------+-----------------------+------+-----+---------+-------+
        | Field | Type                  | Null | Key | Default | Extra |
        +-------+-----------------------+------+-----+---------+-------+
        | id    | int(11)               | YES  |     | NULL    |       |
        | name  | char(16)              | YES  |     | NULL    |       |
        | sex   | enum('male','female') | NO   |     | male    |       |
        +-------+-----------------------+------+-----+---------+-------+
        3 rows in set (0.02 sec)
        mysql> insert into t16(id,name) values(1,'wyq');
        Query OK, 1 row affected (0.01 sec)

        mysql> select * from t16;
        +------+------+------+
        | id   | name | sex  |
        +------+------+------+
        |    1 | wyq  | male |
        +------+------+------+
        1 row in set (0.01 sec)

    unique key:标识该字段的值是唯一的
    单列唯一
    方式一：
        create table department(
            id int unique,
            name char(10) unique
        );
    方式二：
        create table department(
            id int,
            name char(10),
            unique(id),
            unique(name)
        );

    联合唯一
        create table services(
            id int,
            ip char(15),
            port int,
            unique(id),
            unique(ip,port)
        );
        mysql> desc services;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  | UNI | NULL    |       |
| ip    | char(15) | YES  | MUL | NULL    |       |
| port  | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

insert into services values
(1,'192.168.0.100',80),
(2,'192.168.0.101',80),
(3,'192.168.0.100',81);
mysql> select * from services;
+------+---------------+------+
| id   | ip            | port |
+------+---------------+------+
|    1 | 192.168.0.100 |   80 |
|    2 | 192.168.0.101 |   80 |
|    3 | 192.168.0.100 |   81 |
+------+---------------+------+
3 rows in set (0.00 sec)


    primary key:主键 不为空且唯一（not null unique）
    存储引擎 innodb：对于innodb存储引擎来说，一张表必须有一个主键
    如果不指定主键，mysql会自动找一个不为空且唯一的字段当主键，如果没有符合条件的会自动生成一个隐藏字段作为主键。
    单列主键
            create table t17(
                id int primary key,
                name char(16)
            );

            mysql> desc t17;
        +-------+----------+------+-----+---------+-------+
        | Field | Type     | Null | Key | Default | Extra |
        +-------+----------+------+-----+---------+-------+
        | id    | int(11)  | NO   | PRI | NULL    |       |
        | name  | char(10) | YES  |     | NULL    |       |
        +-------+----------+------+-----+---------+-------+
        2 rows in set (0.00 sec
    
    复合主键
        create table t18(
            ip char(15),
            port int,
            primary key(ip,port)
        );
        +-------+----------+------+-----+---------+-------+
        | Field | Type     | Null | Key | Default | Extra |
        +-------+----------+------+-----+---------+-------+
        | ip    | char(15) | NO   | PRI | NULL    |       |
        | port  | int(11)  | NO   | PRI | NULL    |       |
        +-------+----------+------+-----+---------+-------+
        2 rows in set (0.00 sec)

    auto_increment:自增长
        create table t20(
            id int primary key auto_increment,
            name char(16)
        );
        mysql> desc t20;
        +-------+----------+------+-----+---------+----------------+
        | Field | Type     | Null | Key | Default | Extra          |
        +-------+----------+------+-----+---------+----------------+
        | id    | int(11)  | NO   | PRI | NULL    | auto_increment |
        | name  | char(16) | YES  |     | NULL    |                |
        +-------+----------+------+-----+---------+----------------+
        2 rows in set (0.00 sec)

        insert into t20(name) values('wyq'),('qyw'),('yqw');
        mysql> select * from t20;
        +----+------+
        | id | name |
        +----+------+
        |  1 | wyq  |
        |  2 | qyw  |
        |  3 | yqw  |
        +----+------+
        3 rows in set (0.00 sec)

        也可以插入自增长字段：
        insert into t20(id,name) values(7,'wyq1');
        mysql> select * from t20;
        +----+------+
        | id | name |
        +----+------+
        |  1 | wyq  |
        |  2 | qyw  |
        |  3 | yqw  |
        |  7 | wyq1 |
        +----+------+
        4 rows in set (0.00 sec)

        再插入新内容时从最新的开始增长：
        insert into t20(name) values('wyq1'),('qyw1'),('yqw1');

        mysql> select * from t20;
        +----+------+
        | id | name |
        +----+------+
        |  1 | wyq  |
        |  2 | qyw  |
        |  3 | yqw  |
        |  7 | wyq1 |
        |  8 | wyq1 |
        |  9 | qyw1 |
        | 10 | yqw1 |
        +----+------+
        7 rows in set (0.00 sec)

        自增长默认从1开始，步长也是1.
        show variables like 'auto_inc%';   (%代表任意个数任意字符)
        mysql> show variables like 'auto_inc%';
        +--------------------------+-------+
        | Variable_name            | Value |
        +--------------------------+-------+
        | auto_increment_increment | 1     | 步长 默认为1
        | auto_increment_offset    | 1     | 起始偏移量 默认为1
        +--------------------------+-------+
        2 rows in set (0.02 sec)
        设置步长
            set session auto_increment_increment=5;
            set session auto_increment_offset=3;
            强调：起始偏移量一定要小于等于步长，否则失效
            session 会话级别，只在本次链接生效
            global  全局级别

            create table t21(
                id int primary key auto_increment,
                name char(16)
            );
            mysql> desc t21;
            +-------+----------+------+-----+---------+----------------+
            | Field | Type     | Null | Key | Default | Extra          |
            +-------+----------+------+-----+---------+----------------+
            | id    | int(11)  | NO   | PRI | NULL    | auto_increment |
            | name  | char(16) | YES  |     | NULL    |                |
            +-------+----------+------+-----+---------+----------------+
            2 rows in set (0.01 sec)

            mysql> insert into t21(name) values('wyq'),('yqw');
            Query OK, 2 rows affected (0.01 sec)
            Records: 2  Duplicates: 0  Warnings: 0

            mysql> select * from t21;
            +----+------+
            | id | name |
            +----+------+
            |  3 | wyq  |
            |  8 | yqw  |
            +----+------+
            2 rows in set (0.00 sec)

    清空表：
        delete from t20;自增长的AUTO_INCREMENT=11没有重置，还是=11.
        使用truncate t20; 清空表应该用truncate清空。

        mysql> select * from t20;
        +----+------+
        | id | name |
        +----+------+
        |  1 | wyq  |
        |  2 | qyw  |
        |  3 | yqw  |
        |  7 | wyq1 |
        |  8 | wyq1 |
        |  9 | qyw1 |
        | 10 | yqw1 |
        +----+------+
        7 rows in set (0.00 sec)

        mysql> show create table t20;
        +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
        | Table | Create Table                                                                                                                                                             |
        +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
        | t20   | CREATE TABLE `t20` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `name` char(16) DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 |
        +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
        1 row in set (0.01 sec)

        mysql> delete from t20;
        Query OK, 7 rows affected (0.01 sec)

        mysql> show create table t20;
        +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
        | Table | Create Table                                                                                                                                                             |
        +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
        | t20   | CREATE TABLE `t20` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `name` char(16) DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 |
        +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
        1 row in set (0.00 sec)

        mysql> insert into t20(name) values('www');
        Query OK, 1 row affected (0.01 sec)

        mysql> select * from t20;
        +----+------+
        | id | name |
        +----+------+
        | 11 | www  |
        +----+------+
        1 row in set (0.00 sec)


        truncate t20;
        mysql> show create table t20;
        +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
        | Table | Create Table                                                                                                                                           |
        +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
        | t20   | CREATE TABLE `t20` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `name` char(16) DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
        +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
        1 row in set (0.00 sec)


    foreign key: 外键，建立表之间的关系 
    不建议使用外键，不利于扩展，从逻辑上关联
    先建立被关联的表,并且保证被关联的字段唯一

    create table dep(
        id int primary key,
        name char(16),
        comment char(50)
    );
    再建立关联的表
    create table emp(
        id int,
        name char(16),
        sex enum('male','female'),
        dep_id int,
        foreign key(dep_id) references dep(id) on delete cascade on update cascade
    );
    插入数据
    insert into dep values(1,'IT','牛逼部门'),(2,'销售','一般部门'),(3,'财务','钱特别多部门');
    insert into emp values(1,'wyq','male',1);
    insert into emp values(2,'www','male',2);
    insert into emp values(3,'w2w','male',3);
    insert into emp values(4,'wyw','male',1);

    mysql> update dep set id=202 where id=2;
    Query OK, 1 row affected (0.02 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    mysql> select * from dep;
    +-----+--------+--------------------+
    | id  | name   | comment            |
    +-----+--------+--------------------+
    |   1 | IT     | 牛逼部门           |
    |   3 | 财务   | 钱特别多部门       |
    | 202 | 销售   | 一般部门           |
    +-----+--------+--------------------+
    3 rows in set (0.00 sec)

    mysql> select * from emp;
    +------+------+------+--------+
    | id   | name | sex  | dep_id |
    +------+------+------+--------+
    |    1 | wyq  | male |      1 |
    |    2 | www  | male |    202 |
    |    3 | w2w  | male |      3 |
    |    4 | wyw  | male |      1 |
    +------+------+------+--------+
    4 rows in set (0.00 sec)

表关系之 多对一
表关系之 多对多
表关系之 一对一

记录的增删改查
插入数据insert
1. 插入完整数据（顺序插入）
    语法一：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);

    语法二：
    INSERT INTO 表名 VALUES (值1,值2,值3…值n);

2. 指定字段插入数据
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);

3. 插入多条记录
    语法：
    INSERT INTO 表名 VALUES
        (值1,值2,值3…值n),
        (值1,值2,值3…值n),
        (值1,值2,值3…值n);

4. 插入查询结果
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) 
                    SELECT (字段1,字段2,字段3…字段n) FROM 表2
                    WHERE …;


更新数据UPDATE
语法：
    UPDATE 表名 SET
        字段1=值1,
        字段2=值2,
        WHERE CONDITION;

示例：
    UPDATE mysql.user SET password=password(‘123’) 
        where user=’root’ and host=’localhost’;


删除数据DELETE
语法：
    DELETE FROM 表名 
        WHERE CONITION;

示例：
    DELETE FROM mysql.user 
        WHERE password=’’;

查询数据select
单表查询
select distinct 字段1,字段2,字段3 from 库.表 
    where 条件
    group by 分组条件
    having 过滤
    order by 排序字段
    limit n; 限制显示条数

    distinct 去重

单表查询
语法顺序
select distinct 字段1,字段2,字段3 from 库.表
    where 条件
    group by 分组
    having 过滤
    order by 排序
    limit n;


避免重复distinct
通过四则运算查询
定义显示格式

concat()函数用于连接字符串
select concat('姓名：', name) as Name from employee;

concat_ws()第一个参数为分隔符
select concat_ws(':',name,salary) as salary from employee;

where约束
where字句中可以使用：
    1、比较运算符：> < >= <= <> !=
    2、between 80 and 100
    3、in(80,90,100)
    4、like 'egon%'
       pattern可以是%或者_
       %代表任意多字符
       _代表一个字符
    5、逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

group by分组
set global sql_mode="ONLY_FULL_GROUP_BY"
分组之后只能取分组的字段，以及每个组聚合结果

聚合函数
max 最大
min 最小
avg 平均
sum 求和
count 计数

没有group by则默认整体算作一组

group_concat
select post,group_concat(name) from employee group by post;

having过滤
执行优先级从高到低  where > group by > having
where发生在分组group by之前，因而where中可以有任意字段，但绝对不能使用聚合函数
having发生在分组group by之后，因而having中可以使用分组的字段，无法直接取到其他字段，可以使用聚合函数

order by排序
select * from employee order by age asc; 升序
select * from employee order by age desc; 降序
select * from employee order by age asc,id desc; 先按照age升序，一样的情况下按照id降序

limit 控制显示条数


总结：
语法顺序：
select distinct 字段1,字段2,字段3 from 库.表
    where 条件
    group by 分组
    having 过滤
    order by 排序
    limit n;
执行顺序：
select-->from-->where-->group by-->having-->distinct-->order by-->limit

正则查询
select * from employee where name like 'jin%';
select * from employee where name regexp '^jin';
select * from employee where name regexp '^jin.*(g|n)$';

多表查询
连表方式
1、内连接
    只取两张表的共同部分
    select * from employee inner join department on employee.dep_id = department.id;
2、左连接
    在内连接的基础上保留左表的记录
    select * from employee left join department on employee.dep_id = department.id;
3、右连接
    在内连接的基础上保留右表的记录
    select * from employee right join department on employee.dep_id = department.id;
4、全外连接
    在内连接的基础上保留左右两表没有对应关系的记录
    select * from employee left join department on employee.dep_id = department.id
    union
    select * from employee right join department on employee.dep_id = department.id;

select语句关键字的定义顺序

select distinct <select_list>
from <left_table>
<join_type> join <right_table>
on <join_condition>
where <where_condition>
group up <group_by_list>
having <having_condition>
order_by <order_by_condition>
limit <limit_number>

select语句关键字的执行顺序

select 
distinct <select_list>
from <left_table>
<join_type> join <right_table>
on <join_condition>
where <where_condition>
group up <group_by_list>
having <having_condition>
order_by <order_by_condition>
limit <limit_number>


子查询
子查询是将一个查询语句嵌套在另一个查询语句中。
内层查询语句的查询结果，可以为外层查询语句提供查询条件。
子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS、NOT EXISTS等关键字
还可以包含比较运算符：=、!=、>、<等
select
    cid,cname
from
    course
where cid in (
    select
    course_id
from 
    score
group by
    course_id
having
    count(sid) = (
    select
        count(sid)
    from
        student
    )
    );


权限管理
1、创建账号
    本地账号
    create user 'egon1'@'localhost' identified by '123'
    mysql -uegon1 -p123
    远程账号
    create user 'egon2'@'192.168.0.1' identified by '123'
    create user 'egon2'@'192.168.0.%' identified by '123'   %代表任意
    create user 'egon2'@'%' identified by '123'   %代表任意
    mysql -uegon2 -p123 -h 服务端ip
2、授权
    user:*.*
    db:db1.*
    tables_priv:db1.t1
    columns_priv:id,name

    授权grant select/all on *.* to 'egon1'@'localhost';

    回收权限revoke select on *.* from 'egon1'@'localhost';


pymysql模块

连接数据库
# _author_:wyq
# _date_:2019/2/10
import pymysql
user = input('user>>:').strip()
pwd = input('password>>:').strip()
conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='lhwyq1314',
    db='db6',
    charset='utf8'
)
# 拿到游标
cursor = conn.cursor()
# 执行sql语句
# sql = 'select * from userinfo where user="{}" and pwd="{}"'.format(user, pwd)  有sql注入风险，username" -- xxx
sql = 'select * from userinfo where user=%s and pwd=%s'
print(sql)
res = cursor.execute(sql, (user, pwd))
cursor.close()
conn.close()

if res:
    print('登录成功')
else:
    print('登录失败')

pymysql模块之增删改查

# _author_:wyq
# _date_:2019/2/11
import pymysql
# 建立连接
conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='lhwyq1314',
    db='db6',
    charset='utf8'
)
# 拿游标
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 执行sql语句
# 增、删、改
sql = 'insert into userinfo(user,pwd) values(%s,%s)'
# rows = cursor.execute(sql, ('wyq', '123'))
rows = cursor.executemany(sql, [('www1', '321'), ('yyy1', '333')])
print(cursor.lastrowid)  # 查询该到哪个id了，如果已经有4条，会返回5，显示该第五条了
print(rows)
conn.commit()
# 关闭连接
cursor.close()
conn.close()


# 执行sql语句
# 查询
# rows = cursor.execute('select * from userinfo;')
# print(cursor.fetchone())
# cursor.scroll(2, mode='relative')
# print(cursor.fetchone())
# print(rows)
# cursor.close()
# conn.close()

MySQL内置功能介绍
视图(虚拟表)
    create view course2teacher as select * from course inner join teacher on course.teacher_id = teacher.tid;
    Query OK, 0 rows affected (0.03 sec)

    mysql> show tables;
    +----------------+
    | Tables_in_db7  |
    +----------------+
    | class          |
    | course         |
    | course2teacher |
    | score          |
    | student        |
    | teacher        |
    +----------------+
    6 rows in set (0.00 sec)
    只有表结构 没有表数据
    注意：不推荐使用视图，视图太多的话不利于数据库后期扩展

触发器
    使用触发器可以定制用户对表进行增删改操作时前后的行为，注意，没有查询
    # 插入前
    CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
    BEGIN
        ...
    END

    # 插入后
    CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
    BEGIN
        ...
    END

    # 删除前
    CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
    BEGIN
        ...
    END

    # 删除后
    CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
    BEGIN
        ...
    END

    # 更新前
    CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
    BEGIN
        ...
    END

    # 更新后
    CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
    BEGIN
        ...
    END

存储过程
    存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql
    使用存储过程的优点：
        1、用于替代程序写的sql语句，实现程序与sql的解耦
        2、基于网络传输，传别名的数据量小，而直接传sql数据量大
    使用存储过程的缺点：
        1、程序员扩展功能不方便

    
    无参存储过程
    delimiter //
    create procedure p1()
    BEGIN
        select * from db7.teacher;
    END //
    delimiter ;

    MySQL中调用
        call p1();

    Python中调用
        cursor.callproc('p1')


    有参存储过程
mysql> delimiter //
mysql> create procedure p2(in n1 int,in n2 int,out res int)
    -> BEGIN
    -> select * from db7.teacher where tid > n1 and tid < n2;
    -> set res = 1;
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
    MySQL中调用
        set @x=0
        call p2(2,4,@x);
        select @x
    Python中调用
        cursor.callproc('p2', (2, 4, 0))
        # print(cursor.fetchall())
        cursor.execute('select @_p2_2')
        print(cursor.fetchone())

程序与数据库结合使用的三种方式
    方式一：
        MySQL：存储过程
        程序：调用存储过程
    方式二：
        MySQL：
        程序：纯SQL语句
    方式三：
        MySQL：
        程序：类和对象 ORM->纯SQL语句

事务
    事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据的完整性。

    start transaction; 开启事务
    sql语句
    rollback; 回滚
    commit; 提交
索引
移步林海峰老师博客
http://www.cnblogs.com/linhaifeng/articles/7274563.html