MySQL学习总结

    前一段时间很忙，基本没有自己写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条件的顺序，把能够排除掉大量数据的条件写在前面
以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。

 

 

 

 

     

sqlDbx连接mysql 及乱码

安装了mysql5.5 用户root 密码root

实例是默认的test

装一个http://dev.mysql.com/downloads/connector/odbc/下的odbc

如：Windows (x86, 32-bit), MSI Installer Connector-ODBC

版本、cpu*64之类的自己看着办吧

装完之后，添加数据源（管理工具——数据源——添加----）

64位机器装的32位odbc ，要去system32里的odbc打开，添加，不要去“管理工具”下的打开，去打开“C:\Windows\System32”下的odbcad32.exe

原来有截图，今天看了截图都不可读了，懒得再截了

加完之后，

测试通过，就完成了准备工作

打开sqldbx，连接

dbmstype：odbc

是不是DNS就可以找到你要的库了，哈哈

 

乱码

sqldbx是不支持中文的，有中文的sql我一般在text下写好了复制过来，但是返回值可以有中文，需要在odbc下做一点配置

直接去odbc数据源也可，sqldbx——tools--> odbc admin -->点击你自己的配置（俺的是 60serverDB ），我是xp的系统，点击details展开，在connection下charactor set 我选了 gbk2312，就可以了（其实我的mysql是utf-8的，但是还是乱码）

 

另外如果使用SqlDbx.exe 3.47及以后的版本可以在Options里面输入"CHARSET=GB2312"，连接后也会修正中文乱码问题

 

注意

1）要授权，数据库可能不让其他机器访问，导致测试不成功：

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

FLUSH PRIVILEGES;

例：让root登陆密码root

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY   'root' WITH GRANT OPTION;  

FLUSH   PRIVILEGES;

  详细的配置过程请参考：

                          http://www.docin.com/p-352527140.html 

评论

1 楼 kelongsoft 2010-11-17  

很好，看别人的笔记就是高效率学习！！！