Skip to content

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;