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 ;

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 ifexists 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)

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脚本的名称mysql学习，自定义；在上述导出的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

装一个下的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 *.* TOIDENTIFIED BY 'root' WITH GRANT OPTION;

FLUSH PRIVILEGES;

详细的配置过程请参考：

（编辑：云计算网_宿迁站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!