[SQL]常见的mysql 语句

原创 2016年08月29日 09:48:28

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








版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

Access、SQLServer、Oracle常见SQL语句应用区别

Access、SQLServer、Oracle常见SQL语句应用区别   -Access、SQLServer、Oracle常见SQL语句应用区别关劲松 PMP如果要兼容Access、SQL Serve...

sql常见的语句

  • 2012-10-29 10:57
  • 264B
  • 下载

SQL语句常见优化十大案例

1、慢SQL消耗了70%~90%的数据库CPU资源; 2、SQL语句独立于程序设计逻辑,相对于对程序源代码的优化,对SQL语句的优化在时间成本和风险上的代价都很低; 3、SQL语句可以有不同的写法; ...

常见sql语句效率优化方式

数据库sql优化

常见sql语句

  • 2016-06-02 09:56
  • 24KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)