mysql 语句

# 查看数据库
show databases;

1 2	# 查看数据库 show databases;

# 创建数据库
create databases dbname;

1 2	# 创建数据库 create databases dbname;

# 切换到指定数据库
use dbname;

1 2	# 切换到指定数据库 use dbname;

# 删除数据库
drop database dbname;

1 2	# 删除数据库 drop database dbname;

# 查看表
show tables;

1 2	# 查看表 show tables;

# 创建表
create table tabname(wugui1 int(10),wugui2 varchar(20));

1 2	# 创建表 create table tabname(wugui1 int(10),wugui2 varchar(20));

# 查看表结构
desc tabname;

1 2	# 查看表结构 desc tabname;

# 查看创建的表信息
show create table tabname\G;

1 2	# 查看创建的表信息 show create table tabname\G;

# 查看表的所有列
show full columns from tabname;

1 2	# 查看表的所有列 show full columns from tabname;

# 修改tabname表名为tabname1
alter table tabname rename to tabname1;

1 2	# 修改tabname表名为tabname1 alter table tabname rename to tabname1;

# 为tabname1表添加列
alter table tabname1 add column wugui3 varchar(30);

1 2	# 为tabname1表添加列 alter table tabname1 add column wugui3 varchar(30);

# 删除tablename1表的wugui3的列
alter table tabname1 drop column wugui3;

1 2	# 删除tablename1表的wugui3的列 alter table tabname1 drop column wugui3;

# 修改tabname1表的wugui1列为wugui6
alter table tabname1 change wugui1 wugui6 int;

1 2	# 修改tabname1表的wugui1列为wugui6 alter table tabname1 change wugui1 wugui6 int;

# 修改tabname1表的wugui6列的属性为varchar
alter table tabname1 modify wugui6 varchar(100);

1 2	# 修改tabname1表的wugui6列的属性为varchar alter table tabname1 modify wugui6 varchar(100);

# 向tabname1表的wugui6,wugui2列插入haha1,haha2
insert into tabname1(wugui6,wugui2) values('haha1','haha2');

1 2	# 向tabname1表的wugui6,wugui2列插入haha1,haha2 insert into tabname1(wugui6,wugui2) values('haha1','haha2');

# 一次插入多条数据
insert into tabname1(wugui6,wugui2) values('haha3','haha4'),('haha5','haha6');

1 2	# 一次插入多条数据 insert into tabname1(wugui6,wugui2) values('haha3','haha4'),('haha5','haha6');

# 查看表的所有列数据
select * from tabname1;

1 2	# 查看表的所有列数据 select * from tabname1;

# 修改tabname1表wugui6列的haha2为haha1
update tabname1 set wugui6='haha2' where wugui6='haha1';

1 2	# 修改tabname1表wugui6列的haha2为haha1 update tabname1 set wugui6='haha2' where wugui6='haha1';

# 修改tabname1表wugui6 列所有为haha2
update tabname1 set wugui6='haha2';

1 2	# 修改tabname1表wugui6 列所有为haha2 update tabname1 set wugui6='haha2';

# 删除wugui2列为haha6的数据
delete from tabname1 where wugui2='haha6';

1 2	# 删除wugui2列为haha6的数据 delete from tabname1 where wugui2='haha6';

# 清空整个表
delete from tabname1;

1 2	# 清空整个表 delete from tabname1;

# 删除表
drop table tabname1;

1 2	# 删除表 drop table tabname1;

# 创建用户
grant all on test-database.* to 'test'@'111.111.111.%' identified by '123456';

1 2	# 创建用户 grant all on test-database.* to 'test'@'111.111.111.%' identified by '123456';

# 刷新权限
flush privileges;

1 2	# 刷新权限 flush privileges;

# 查看一个用户权限
show grants for root;

1 2	# 查看一个用户权限 show grants for root;

# 删除用户
delete from user where user='test';

#升级数据结构 #升级数据库后需执行以下命令
mysql_upgrade 列：mysql_upgrade -u root -p123456

1

2

3

4

5

# 删除用户

delete from user where user='test';

#升级数据结构 #升级数据库后需执行以下命令

mysql_upgrade 列：mysql_upgrade -u root -p123456

简单语句记录