(转载)数据库操作命令-CSDN博客

本文链接：https://blog.csdn.net/zuoshuliangwang/article/details/84168143

前一段时间很忙，基本没有自己写Blog多少都是转载别人的，想想很长时间没有使用MySQL数据库了，今天有空闲时间，再次开始学习MySQL数据的一些知识总结一下：

MySQL总结
基本信息查看
登录： mysql -uroot -pxxx -h192.168.0.1
语法帮助：> help show variables;
查数据库：show databases;
创建数据库
create database
删除数据库：
drop database if exists testdb;
查告警：show warnings;
查看数据库及表，行数：mysqlshow -uroot --count; mysqlshow --count
切换数据库：use xxx;
查数据库中的表：show tables;
查看数据库中的参数：
show variables ;
show variables like '%lo%';

       show的命令的学习
       show语法如下：
          SHOW DATABASES [LIKE wild]
      or SHOW TABLES [FROM db_name] [LIKE wild]
      or SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]
      or SHOW INDEX FROM tbl_name [FROM db_name]
      or SHOW STATUS
      or SHOW VARIABLES [LIKE wild]
      or SHOW [FULL] PROCESSLIST
      or SHOW TABLE STATUS [FROM db_name] [LIKE wild]
      or SHOW GRANTS FOR user


   例如：
   查看数据库表中索引
   show index from userinfo from testdb;
   show index from testdb.userinfo;
   查询表的状态
   show table status;
   或者
   show status;

   查看用户的权限
   show grants for xiaobai@'localhost';

   explain语法：
    EXPLAIN tbl_name
     or EXPLAIN SELECT select_options

     EXPLAIN tbl_name是DESCRIBE tbl_name或SHOW COLUMNS FROM tbl_name的一个同义词。

查看表结构的方法
方法一：
desc userinfo; 或者describe userinfo；

方法二：
show columns from userinfo;

查看表的schema
show create table 表名;

通过information_schema看表结构
use information_schema

select * from columns where table_name='表名';

针对表的操作

创建Mysql表结构：

drop table if exists userinfo ;

create table userinfo
(
userName varchar(25) not null),
email varchar(30)
);

添加一列
alter table userinfo
add column userId varchar(20) not null;

设置为主键
alter table userinfo
add prmariy key (userId);

删除一列：
alter table userinfo
drop column userId ;

添加列并添加主键：
alter table userinfo
add column userId int primary key (userId);


     添加列并主键：添加主键同时为自增
alter table userinfo
add column userId int primary key auto_increment;

设置自动增长的列从10开始增长
alter table userinfo
auto_increment=10;

设置列的默认值的方法
alter table userinfo
add column sex int not null default 1;

添加唯一约束
alter table userinfo
   add unique UQ_UserID(userId);


   添加单字段索引的
   alter table userinfo
     add index index_UserId(userId);


     添加多字段索引的
   alter table userinfo
     add index index_UN(userId,email);


     删除索引
    alert table userinfo
      drop index index_UN;


      修改表名称
      alter table userInfo rename user_Info;

      或者
      rename table userInfo to user_Info;


      表的复制
      create table user_info
      select * from userInfo;

删除表
drop table user_info;

删除数据库

drop database userdb;

查看数据库的编码格式：show create database testdb;


      让其用UTF-8编码创建数据库：
       create database testdb character set utf8;

MySQL用户信息管理
       查看用户信息
       select * from mysql.user where user='root';
       或者
       show grants for root@localhost;
       创建用户
       CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
            [, user [IDENTIFIED BY [PASSWORD] 'password']] ...

         例如：
         create User xiaobai identified by 'abc';
         备注：
         CREATE USER用于创建新的MySQL账户。要使用CREATE USER，您必须拥有mysql数据库的全局CREATE USER权限，
         或拥有INSERT权限。对于每个账户，CREATE USER会在没有权限的mysql.user表中创建一个新记录。如果
          账户已经存在，则出现错误。

       GRANT 语句的语法如下：
         GRANT 语句的语法如下：

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    TO user_name [IDENTIFIED BY 'password']
        [, user_name [IDENTIFIED BY 'password'] ...]
    [WITH GRANT OPTION]

REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
FROM user_name [, user_name ...]

       授权操作
       将所有数据库的查询，添加，修改权限分配给用户
       grant select,insert,update,delete on testdb.* to xiaobai@localhost identified by "abc";

       revoke delete on testdb.* from xiaobai;

       授权操作
       将所有数据库的查询，添加，修改权限分配给用户
       select select ,insert update on *.* to xiaobai@localhost identified by 'xiaobai';

删除用户
drop user username；

      MySQL 系统的一些信息
      查询时间的
      select now();
      select current_date();
      select curdate();
      查询版本
      select version();
      查询当前用户
      select user();

      查询当前登录的用户数量（用root用户和一般的用户登录看到的数据不一样）
      如果是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接。
      show full processlist；

      查看数据的最大连接数
      默认最大连接数 max_connections对应的值。
      show variables like '%max_con%';

      修改mysql最大连接数
   对于window下的mysql 找到my.ini文件，找到[mysqld] ，在#set-variable=key_buffer=16M   后面加上如下代码：
   set-variable = max_connections=2000
   对于linux下的mysql 找到my.cnf文件，找到[mysqld] ，加上如下代码：
   set-variable = max_connections=2000
   修改完毕以后，重启mysql服务；这样我们就把最大连接数由默认的100修改成2000了。
   注意在改这些配置文件以前一定先做备份

    使用命令修改连接数
     set GLOBAL max_connections=2000;


   命令： show status;

   Aborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。
   Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。
   Connections 试图连接MySQL服务器的次数。
   Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。
   Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。
   Delayed_writes 用INSERT DELAYED写入的行数。
   Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。
   Flush_commands 执行FLUSH命令的次数。
   Handler_delete 请求从一张表中删除行的次数。
   Handler_read_first 请求读入表中第一行的次数。
   Handler_read_key 请求数字基于键读行。
   Handler_read_next 请求读入基于一个键的一行的次数。
   Handler_read_rnd 请求读入基于一个固定位置的一行的次数。
   Handler_update 请求更新表中一行的次数。
   Handler_write 请求向表中插入一行的次数。
   Key_blocks_used 用于关键字缓存的块的数量。
   Key_read_requests 请求从缓存读入一个键值的次数。
   Key_reads 从磁盘物理读入一个键值的次数。
   Key_write_requests 请求将一个关键字块写入缓存次数。
   Key_writes 将一个键值块物理写入磁盘的次数。
   Max_used_connections 同时使用的连接的最大数目。
   Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。
   Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。
   Open_tables 打开表的数量。
   Open_files 打开文件的数量。
   Open_streams 打开流的数量(主要用于日志记载）
   Opened_tables 已经打开的表的数量。
   Questions 发往服务器的查询的数量。
   Slow_queries 要花超过long_query_time时间的查询数量。
   Threads_connected 当前打开的连接的数量。
   Threads_running 不在睡眠的线程数量。
   Uptime 服务器工作了多少秒。

典型函数的应用

      SELECT username, birthday, curdate(),(year(curdate())-year(birthday)) - (right(curdate(),5)<right (birthday,5)) as age from userinfo ORDER BY username;

      year 提取年部分 right(curdate(),5)提取右面的5位
   month()月 month(curdate())
   day() 天或dayofmonth 意思是拿月来说的天数
   dayofyear() 拿年来说的天

   select date_add(curdate(),interval 1 year);
   interval(间隔的意思) date_add 时间相加的函数
   interval 数字 year或者month

   mysql数据库的导入和导出，有两种方式，一种是命令的方式，一种是用mysql－front这种工具来做export和import，
   不过用front导出后导入可能存在很多问题，版本的差异会导致导入失败，所以为了不发生错误，还是用最原始的命令方式来的安全和准确；

数据库的导出：

1、cmd命令，找到安装mysql的路径，如：D:\Program Files\MySQL\MySQL Server 4.1\bin

2、导出：输入导出命令，

如：D:\Program Files\MySQL\MySQL Server 4.1\bin>mysqldump -uroot -p12345 sc>sc.sql

此处-uroot，-u后跟用户名（root）；-p后跟用户密码（12345）；sc为目标数据库名，sc.sql为

保存sql脚本的名称，自定义；在上述导出的sc.sql文件存放在D:\Program Files\MySQL\MySQL

Server 4.1\bin下

3、导入：输入导入命令，如：

如：D:\Program Files\MySQL\MySQL Server 4.1\bin>mysql -uroot -p12345》 sc.sql sctarget

此处，sc.sql为需要导入的sql脚本文件，sctarget为目标数据库，即将sc.sql导入到sctarget数据

库中

导入方式还有一种：
1），cmd进入mysql安装路径，如D:\Program Files\MySQL\MySQL Server 4.1\bin，
2）输入mysql -uroot -p 回车，提示Enter Password：
3）输入正确的密码后，输入命令 use targetdb 回车
4）输入：source D:\dback.sql 回车

“use targetdb”是指：打开目标数据库targetdb；
“source d:\dback.sql”是指：被导入的脚本文件dback.sql所在的路径源；

4、创建数据库：

     1），cmd进入mysql安装路径，如D:\Program Files\MySQL\MySQL Server 4.1\bin，
     2）输入mysql -uroot -p 回车，提示Enter Password：
     3）输入正确的密码后，输入命令 create database testdb character set utf8; 创建utf8的数据库就成功了。


      索引创建规则：
1、表的主键、外键必须有索引；
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5、索引应该建在选择性高的字段上；
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
A、正确选择复合索引中的主列字段，一般是选择性较好的字段；
B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
8、频繁进行数据操作的表，不要建立太多的索引；
9、删除无用的索引，避免对执行计划造成负面影响；
几个优化建议：
（1）适当的建立字段索引。
（2）注意sql条件的顺序，把能够排除掉大量数据的条件写在前面
以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。