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