SQL语句知识体系
# 数据库连接
mysql -h host -P port -u username -ppassword
常用SQL雨具
# 创建用户
create user username identified by "password";
insert into mysql.user(User,Password) values('username',password('password'));
# 刷新权限
flush privileges;
# 查看用户权限
show grants for db_user@host;
# 赋予用户权限
grant all privileges on db_name.table_name to 'db_user'@'host' identified by 'password';
# 收回用户权限
# 创建数据库
create database db_name CHARACTER set utf8 COLLATE utf8_general_ci;
# 删除数据库
drop database db_name;
# 查看数据库
show databases;
# 创建表
create table table_name(column_name type);
create table if not exists;
create table table_name select * from oldtable_name;
create table table_name select * from oldtable_name where 0=1;
create temporary table table_name; # 创建临时表
# 查看表结构
desc table_name;
# 重命名表
alter table_name rename newtable_name;
# 删除表
drop table table_name;
drop table if exists table_name;
# 添加字段
alter table table_name add column_name type;
# 删除字段
alter table table_name del column_name;
# 修改字段属性
alter table table_name modify column_name new_type;
# 修改字段名称与属性
alter table table_name change column oldcolumn_name newcolumn_name type;
# 将时间戳转换为日期
select FROM_UNIXTIME(1551422203);
# 将日期转换为时间戳
select UNIX_TIMESTAMP('2019-03-01 13:44:00');
# 设置字段为空
update table_name set column_name is null;
update table_name set column_name=NULL;