- 在ID是自增长的情况下获取刚插入数据的ID
在MYSQL中可以使用@@IDENTITY或者LAST_INSERT_ID()函数，当使用INSERT语句插入多条记录的时候，使用LAST_INSERT_ID()返 回的还是第一条的ID值，而使用@@IDENTITY是返回最后一条的ID值
- 配置上可以免用户名密码登陆数据库
[mysqld]
skip-grant-tables
- Mysql服务启动停止
servcie mysql start
service mysql stop
- 创建用户并授权，先设置2并以mysql -uroot 登录，才能执行以下命令
CREATE USER 'username'@'host' IDENTIFIED BY 'password';（如果报ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement，就先执行一下flush privileges;）
GRANT all privileges ON databasename.tablename TO 'username'@'host'
update mysql.user set password=password('新密码') where User='root';（mysql5.7以下用这个）
update mysql.user set authentication_string=password('新密码') where user='root' ；（mysql5.7以上用这个方法）
mysql8.0无法给用户授权或提示You are not allowed to create a user with GRANT的问题
提示意思是不能用grant创建用户，mysql8.0以前的版本可以使用grant在授权的时候隐式的创建用户，8.0以后已经不支持，所以必须先创建用户，然后再授权，命令如下：
mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'yourpassword';
Query OK, 0 rows affected (0.04 sec)
mysql> grant all privileges on *.* to 'root'@'%';
Query OK, 0 rows affected (0.03 sec)
另外，如果远程连接的时候报plugin caching_sha2_password could not be loaded这个错误，可以尝试修改密码加密插件：
mysql> alter user 'root'@'%' identified with mysql_native_password by 'yourpassword';
- 所有权限
| 权限指定符 | 权限允许的操作 |
|---|---|
| Alter | 修改表和索引 |
| Create | 创建数据库和表 |
| Delete | 删除表中已有的记录 |
| Drop | 删除数据库和表 |
| INDEX | 创建或抛弃索引 |
| Insert | 向表中插入新行 |
| Select | 检索表中的记录 |
| Update | 修改现存表记录 |
| FILE | 读或写服务器上的文件 |
| PROCESS | 查看服务器中执行的线程信息或杀死线程 |
| RELOAD | 重载授权表或清空日志、主机缓存或表缓存。 |
| SHUTDOWN | 关闭服务器 |
| ALL | 所有；ALL PRIVILEGES同义词 |
| USAGE | 特殊的“无权限”权限 |
- 有重复数据时自动更新
-- 如果存在a,b,c中的某一列触发了唯一索引或主键索引异常（有重复值），则执行后边的update操作
INSERT INTO table (a,b,c）VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
-- 使用VALUES()函数可以获取insert的value中c列的值，上面的例子中c=3+6，这种方式特别适合插入多条记录
INSERT INTO table (a,b,c) VALUES (1,2,3), (4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
- 有重复数据时自动删除
-- 如果id为123和134的用户数据存在则先删除再插入，使用REPLACE的最大好处就是可以将DELETE和INSERT合二为一，形成一个原子操作
REPLACE INTO users(id, name, age) VALUES(123,'赵本山', 50), (134,'Mary',15);
- 更新时自动记录最后修改时间
ALTER TABLE `db_name`.`table_name`
ADD COLUMN `udpate_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL COMMENT '修改时间';
- 其他SQL技巧
-- 用正则表达式代替like
select * from t where c1 regexp '^[0-9]{5}abc$'
-- 用rand()提取随机行
select * from t order by rand() limit 5
-- 利用 group by 的 with rollup 子句做统计(rollup不能与order by一同使用)
select a,b,c,count(id) from t group by a,b,c with rollup
- 判断数据是否存在优化
-- 错误用法
select count(*) from tableName where colName=value;
-- 正确用法
select 1 from tableName where colName=value limit 1;
- mysql 导出命令
-- 导出整个库(包含结构和数据)
mysqldump --no-defaults -uroot -p dbname > exportfile.sql
-- 导出整个库(仅结构)
mysqldump --no-defaults -uroot -p -d dbname > exportfile.sql
-- 导出一个或多个表(包含结构和数据)
mysqldump --no-defaults -uroot -p dbname table1 table2 > exportfile.sql
-- 导出一个或多个表(仅结构)
mysqldump --no-defaults -uroot -p -d dbname table1 table2 > exportfile.sql
-- 导出一个表的部分数据（仅数据）
mysqldump --no-defaults -uroot -p --no-create-info --databases dbname --tables a1 --where="id='a'" >exportfile.sql
【说明：-d 表示只导出结构 --no-create-info 表示不导出结构 --no-defaults 避免报--no-beep错误】
--根据SQL导出CSV文件,sed部分为处理字符串为csv的格式
mysql -A lms -h localhost -u root -p -e "select * from tableName" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > export.csv
