MySQL数据库常用操作笔记总结

1. 连接DB操作

   # mysql -uroot -p"密码"-P 3306
   mysql> show databases;  
   mysql> use db_name;
   

2. table 操作

   mysql> show tables; 
   mysql> desc table_name; 
   mysql> CREATE TABLE table_name ( ); 
   

   CREATE TABLE table_name (id int(11) NOT NULL AUTO_INCREMENT primary key comment ‘primary key’,name varchar (255) NOT NULL comment ‘clipping group name’, description varchar (255) NOT NULL comment ‘description of this group’,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment ‘update time’,create_time timestamp NOT NULL comment ‘create time’,KEY idx_name (name))ENGINE=InnoDB DEFAULT CHARSET=utf8 comment ‘clipping group’;

   mysql> AlTER TABLE tableName RENAME TO newName;
   mysql> ALTER TABLE sched_service_node ADD COLUMN white_list_type int(11) NOT NULL default 0 COMMENT 'ServiceNode field WhiteListType';
   mysql> UPDATE table_name SET a="" where b="";
   mysql> delete from table_name;--清空表中数据
   mysql> DROP table table_name;--删除数据库表，释放空间
   

3. 查询操作

   mysql> select * from tb_name where a=“”
   		select * from tb_name where a=  order by 查询字段;
   		select count（）from tb_name where a="" group by 查询字段;
   		select count(ip) from tb_name；
   		select count(ip) from tb_name group by 查询字段;
   		select ip,dnsview from tb_name where a="" order by 查询字段
   

4. 其他：

   退出DB --> \q
   创建数据库 -->create database DBname
   修改权限 --> grant all on DB_name.* to admin@’%’ identified by ‘admin’;