[SQL]常见的mysql 语句

// mysql 语句总结

(1) 数据库操作

//  建立数据库
create database_name;

// 查看所有数据库
show databases;

//使用该数据库
use  database_name;

//赋予某人以密码访问某数据库的权利
grant all privileges on  database_name.* to user_name@localhost identified by  "111123456";
grant all  on database_name.* to user_name@localhost identified by  "2111123456";

（2） 表格操作

//显示当前数据库下所有的表格
use database_name;
show tables;
+----------------+
| Tables_in_test |
+----------------+
| bmc_device     |
| bmc_device_2   |
| user_info      |
+----------------+
3 rows in set (0.00 sec)

// 修改表名字
alter table bmc_device_2 rename to bmc_device_test;

//查看表格的结构
describe table_name;
desc table_name;

+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | NO   |     | NULL    |                |
| password | varchar(20) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)

//修改列名
alter table table_name  change  original_column_name  new_column_name  int;

// 修改列属性
alter table bmc_device_test modify test varchar(20);

//建立表格
 create table bmc_device
 (
id int  auto_increment primary key not null,    //自增id,主键，非空
platform  varchar(20),
ip varchar(40),
nuc varchar(40),
hostname varchar(40),
usb_port  varchar(40),
cpu varchar(20),
chassis varchar(20),
psu varchar(20),
owner varchar(20),
free boolean
 );
 
 //向表格中插入数据
 
insert into  user_info values('0003','lzz');
insert into user_info(username,password) values('ooo','zzz');
insert into bmc_search(product,cpu,mm,chassis,hdd,psu,fan,free) values('wfp','cpu1','mm1','chassis1','12hdd','psu1','fan1',true);
insert into bmc_device(platform,ip,nuc,hostname,usb_port,cpu,chassis,psu,owner,free) values('wfp','10.219.55.77','10.239.56.57','htet-nuc','usb1','cpu1','chassis-2000','psu1','james',1);
insert into bmc_device(platform,ip,nuc,hostname,usb_port,cpu,chassis,psu,owner,free) values('wfp','10.229.57.35','10.239.56.57','test-nuc','usb2','cpu1','chassis-1000','psu2','tester',0);

//表格增加字段
alter table bmc_device add column golden boolean;  //增加字段
 
alter table bmc_device add column lab  varchar(40);  //增加字段
 
alter table bmc_device  add column  platform_num  varchar(40) after lab;  //增加字段

 //表格删除字段
 alter table bmc_device drop column cpu;   //column 可以省略
 alter table bmc_device drop  psu;

 //删除表格某一行
 delete from bmc_device where id=21;
 
 //删除表格全部内容，但是不删除表格
 delete from bmc_device;   
 
 // 删除表格全部内容，同时删除表格
 drop table user_info;
 
 // 修改字段值
update bmc_device set platform='bnp' where platform='btp';  
 update bmc_device set platform='bnp'，name='Purely' where id='20';  
 
 （3） 关于权限问题
 
 //  查看所有用户以及他们的主机，用这种形式显示出来
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

+---------------------------+
| query                     |
+---------------------------+
| User: 'root'@'localhost'; |
| User: 'test'@'localhost'; |
+---------------------------+

//查看某一特定用户的权限
show grants for 'test'@'localhost';
show grants for 'root'@'localhost';

+-------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost                              |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*6BBTTTTYYYYYGGG05EE45999DDA7DC67ED2$$DD9' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'test'@'localhost'                              |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


//插入新的用户
insert into mysql.user(Host,User,Password) values("localhost","xx",password("xxxxxx"));