MySQL 操作总结

1. 数据库级别操作

1.1 创建数据库

CREATE DATABASE db1 default charset utf8 collate utf8_general_ci;

1.2 删除数据库

DROP DATABASE db1;

2. 用户级别操作

2.1 单独创建用户并授权

CREATE USER 'hukey'@'%';

2.2 删除用户

DROP USER 'hukey'@'%';

2.3 修改用户

RENAME USER 'hukey'@'%' TO 'superman'@'%';

2.4 修改密码

SET PASSWORD FOR 'superman'@'%' = PASSWORD('111111');

3. 授权管理操作

3.1 为已有用户授权

GRANT SELECT, UPDATE, DELETE ON db1.* TO 'superman'@'%';

3.2 查看用户授权

SHOW GRANTS FOR 'superman'@'%';

3.3 删除用户授权

REVOKE DELETE ON db1.* FROM 'superman'@'%';

    all privileges          除grant外的所有权限
    select                  仅查权限
    select,insert           查和插入权限
    ...
    usage                   无访问权限
    alter                   使用alter table
    alter routine           使用alter procedure和drop procedure
    create                  使用create table
    create routine          使用create procedure
    create temporary tables 使用create temporary tables
    create user             使用create user、drop user、rename user和revoke??all privileges
    create view             使用create view
    delete                  使用delete
    drop                    使用drop table
    execute                 使用call和存储过程
    file                    使用select into outfile 和 load data infile
    grant option            使用grant 和 revoke
    index                   使用index
    insert                  使用insert
    lock tables             使用lock table
    process                 使用show full processlist
    select                  使用select
    show databases          使用show databases
    show view               使用show view
    update                  使用update
    reload                  使用flush
    shutdown                使用mysqladmin shutdown(关闭MySQL)
    super                   使用change master、kill、logs、purge、master和set global。还允许mysqladmin????????调试登陆
    replication client      服务器位置的访问
    replication slave       由复制从属使用

    对于目标数据库以及内部其他：
        数据库名.*          数据库中的所有
        数据库名.表         指定数据库中的某张表
        数据库名.存储过程   指定数据库中的存储过程
        *.*                 所有数据库

    用户名@IP地址       用户只能在改IP下才能访问
    用户名@192.168.1.%  用户只能在改IP段下才能访问(通配符%表示任意)
    用户名@%            用户可以再任意IP下访问(默认IP地址为%)

PS：修改完权限需要执行：

FLUSH PRIVILEGES;

 将数据读取到内存中，从而立即生效.

4. 常用数据类型

MySQL数据类型可以分为 3 大类：
（1）数值类型：int float
（2）字符串类型 char varchar text
（3）时间类型 datetime

重点问题：
    char 和 varchar 的区别？
    char： 定长，效率高
    varchar：不定长，效率偏低

5. 表内容操作

5.1 增

insert into 表 (列名,列名...) values (值,值,值...)
insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
insert into 表 (列名,列名...) select (列名,列名...) from 表

5.2 删

delete from 表
delete from 表 where id＝1 and name＝'alex'
truncate table tb2;   清空数据并将自增值设置为 1

5.3 改

update 表 set name ＝ 'alex' where id>1

5.4 查

select * from 表
select * from 表 where id > 1
select nid,name,gender as gg from 表 where id > 1

5.5 其他

5.5.1 条件
    select * from 表 where id > 1 and name != 'alex' and num = 12;
 
    select * from 表 where id between 5 and 16;
 
    select * from 表 where id in (11,22,33)
    select * from 表 where id not in (11,22,33)
    select * from 表 where id in (select nid from 表)

5.5.2 通配符
    select * from 表 where name like 'ale%'  - ale开头的所有（多个字符串）
    select * from 表 where name like 'ale_'  - ale开头的所有（一个字符）

5.5.3 限制
    select * from 表 limit 5;            - 前5行
    select * from 表 limit 4,5;          - 从第4行开始的5行
    select * from 表 limit 5 offset 4    - 从第4行开始的5行

5.5.4 排序
    select * from 表 order by 列 asc              - 根据 “列” 从小到大排列
    select * from 表 order by 列 desc             - 根据 “列” 从大到小排列
    select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序

5.5.5 分组
    select num from 表 group by num
    select num,nid from 表 group by num,nid
    select num,nid from 表  where nid > 10 group by num,nid order nid desc
    select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
 
    select num from 表 group by num having max(id) > 1021
 
    特别的：group by 必须在where之后，order by之前

5.5.6 连表
    无对应关系则不显示
    select A.num, A.name, B.name
    from A,B
    Where A.nid = B.nid
 
    无对应关系则不显示
    select A.num, A.name, B.name
    from A inner join B
    on A.nid = B.nid
 
    A表所有显示，如果B中无对应关系，则值为null
    select A.num, A.name, B.name
    from A left join B
    on A.nid = B.nid
 
    B表所有显示，如果B中无对应关系，则值为null
    select A.num, A.name, B.name
    from A right join B
    on A.nid = B.nid

5.5.7 组合
    组合，自动处理重合
    select nickname
    from A
    union
    select name
    from B
 
    组合，不处理重合
    select nickname
    from A
    union all
    select name
    from B

5.5.8 连表查询

left join 表名 on 
right join 表名 on 
inner join  将出现null时一行隐藏

6. 补充知识

6.1 mysql left join 中 where 和 on 条件的区别？

left join中关于where和on条件的几个知识点：

    1. 多表left join是会生成一张临时表，并返回给用户
    2. where条件是针对最后生成的这张临时表进行过滤，过滤掉不符合where条件的记录，是真正的不符合就过滤掉。
    3. on条件是对left join的右表进行条件过滤，但依然返回左表的所有行，右表中没有的补为NULL
    4. on条件中如果有对左表的限制条件，无论条件真假，依然返回左表的所有行,但是会影响右表的匹配值。也就是说on中左表的限制条件只影响右表的匹配内容，不影响返回行数。

测试：
    创建两张表：

-- t1 表
CREATE TABLE t1(id INT,name VARCHAR(20));
insert  into `t1`(`id`,`name`) values (1,'a11');
insert  into `t1`(`id`,`name`) values (2,'a22');
insert  into `t1`(`id`,`name`) values (3,'a33');
insert  into `t1`(`id`,`name`) values (4,'a44');

-- t2 表
CREATE TABLE t2(id INT,local VARCHAR(20));
insert  into `t2`(`id`,`local`) values (1,'beijing');
insert  into `t2`(`id`,`local`) values (2,'shanghai');
insert  into `t2`(`id`,`local`) values (5,'chongqing');
insert  into `t2`(`id`,`local`) values (6,'tianjin');

测试01：返回左表所有行，右表符合 on 条件的原样匹配，不满足条件的补 NULL

MariaDB [db2]> SELECT t1.id,t1.name,t2.local FROM t1 LEFT JOIN t2 ON t1.id=t2.id;
+------+------+----------+
| id   | name | local    |
+------+------+----------+
|    1 | a11  | beijing  |
|    2 | a22  | shanghai |
|    3 | a33  | NULL     |
|    4 | a44  | NULL     |
+------+------+----------+

测试02：on后面增加对右表的限制条件：t2.local='beijing'

结论02：左表记录全部返回，右表筛选条件生效

MariaDB [db2]> SELECT t1.id,t1.name,t2.local FROM t1 LEFT JOIN t2 ON t1.id=t2.id and t2.local='beijing';
+------+------+---------+
| id   | name | local   |
+------+------+---------+
|    1 | a11  | beijing |
|    2 | a22  | NULL    |
|    3 | a33  | NULL    |
|    4 | a44  | NULL    |
+------+------+---------+

测试03：只在 where 后面增加对右表的限制条件：t2.local = 'beijing'

结论03：针对右表，相同条件，在where后面对最后的临时表进行记录筛选，行数可能会减少；在on后面是作为匹配条件进行筛选，筛选的是右表的内容。

MariaDB [db2]> SELECT t1.id,t1.name,t2.local FROM t1 LEFT JOIN t2 ON t1.id=t2.id where t2.local='beijing';
+------+------+---------+
| id   | name | local   |
+------+------+---------+
|    1 | a11  | beijing |
+------+------+---------+

测试04：t1.name = 'a11' 或者 t1.name = 'a33'

结论04：on中对左表的限制条件，不影响返回的行数，只影响右表的匹配内容

MariaDB [db2]> SELECT t1.id,t1.name,t2.local FROM t1 LEFT JOIN t2 ON t1.id=t2.id and t1.name='a11';  
+------+------+---------+
| id   | name | local   |
+------+------+---------+
|    1 | a11  | beijing |
|    2 | a22  | NULL    |
|    3 | a33  | NULL    |
|    4 | a44  | NULL    |
+------+------+---------+

MariaDB [db2]> select t1.id, t1.name, t2.local from t1 LEFT JOIN t2 on t1.id = t2.id and t1.name = 'a33';
+------+------+-------+
| id   | name | local |
+------+------+-------+
|    1 | a11  | NULL  |
|    2 | a22  | NULL  |
|    3 | a33  | NULL  |
|    4 | a44  | NULL  |
+------+------+-------+

测试05：where t1.name = 'a33' 或者 where t1.name = 'a22'

结论05：where条件是在最后临时表的基础上进行筛选， 显示只符合最后where条件的行

MariaDB [db2]> SELECT t1.id,t1.name,t2.local FROM t1 LEFT JOIN t2 ON t1.id=t2.id where t1.name='a33';  
+------+------+-------+
| id   | name | local |
+------+------+-------+
|    3 | a33  | NULL  |
+------+------+-------+

MariaDB [db2]> SELECT t1.id,t1.name,t2.local FROM t1 LEFT JOIN t2 ON t1.id=t2.id where t1.name='a22'; 
+------+------+----------+
| id   | name | local    |
+------+------+----------+
|    2 | a22  | shanghai |
+------+------+----------+