[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"));