MySQL 笔记

权限

创建用户

create user <user_name> IDENTIFIED by '<password>';

赋权

GRANT ALL PRIVILEGES ON *.* to '<user_name>'@'%' IDENTIFIED BY '<password>' WITH GRANT OPTION; //赋予全部权限

grant select on ry.* to 'test'@'localhost'; //赋予该库的表的查询权限

grant create on ry.* to 'test'@'localhost'; //赋予该库的建表权限

grant references on testdb.* to developer@'192.168.0.%'; //ip段操作外键权限

grant select(id, se, rank) on testdb.apache_log to dba@localhost; //某些列的查询权限

等等

• 可以限制用户访问哪些表、哪些库（其余库表对其隐藏）
• 可以限制对表的增删改查
• 可以限制用户登录的ip
• 可以限制是否有授权权限

修改权限后需要刷新权限flush privileges;

revoke create on *.* from 'test@localhost';//回收权限

表设计

基本要素

• id：主键，bigint
• 创建时间、创建者：表数据的创建信息
• 修改时间、修改者：表数据的修改信息

外键

外键表示一个表中的一个字段被另一个表中的一个字段引用。外键对相关表中的数据造成了限制，使MySQL能够保持参照完整性。

ALTER table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name(columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action;

① RESTRICT（限制外表中的外键改动，默认值）

② CASCADE（跟随外键改动，A表id由1变成2，B表的外键也跟着变，一般是UPDATE用）

③ SET NULL（设空值）

④ SET DEFAULT（设默认值，一般DELETE用）

⑤ NO ACTION（无动作，默认的）

内外连接

a表3条数据，id为1,2,3
b表2条数据，a_id为1,4

内连接

查询两张表的交集，1条

• 隐式内连接：select * from table_a,table_b where a.id = b.a_id
• 显式内连接：select * from table_a inner join table_b on a.id = b.a_id where

外连接

• 左连接：查询左表全部数据，右表交集部分正常查询，未命中交集部分显示为null，

Mybatis

插入返回主键

通过selectKey，返回的主键会在Student实例类中被带回

<insert id="insertStudent" parameterType="Student">
    insert into student(name, age)
    VALUES (#{name} , #{age})

    <selectKey keyProperty="sid" order="AFTER" resultType="int">
        SELECT LAST_INSERT_ID()
    </selectKey>
</insert>

sql 案例

连表查询

A外联B，并查询A或B命中的数据量

select count(d.dept_id), u.user_id from sys_user u left join sys_dept d on u.dept_id=d.dept_id GROUP BY u.user_id;  //左连接，A全命中，可统计B中数据命中情况

if函数

status为1改为0，否则为1

update stars_plan_fitness set status = if(status = 1,0,1) where id = #{id};

Case When

UPDATE mytable SET
myfield = CASE id
    WHEN 1 THEN 'value'
    WHEN 2 THEN 'value'
    WHEN 3 THEN 'value'
END
WHERE id IN (1,2,3)

时间格式化

DATE_FORMAT(f.create_time,'%Y-%m-%d') AS ctime

update 联表填充

a 表使用 b 表字段更新，pg 数据库是 update…from…，mysql 是 INNER JOIN

UPDATE a INNER JOIN b ON a.userName = b.userName SET a.password = b.password

GROUP BY 不兼容

报错信息：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ry-vue.pf.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因是 GROUP BY 语句只能查询聚合后的字段，方法是使用 ANY_VALUE() 包裹字段或者升级 MySQL 版本
https://blog.csdn.net/u012660464/article/details/113977173