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 | +------+------+----------+
