MySQL数据库的操作

一 mysql 分类和操作

1.1 SQL语句分类：

DDL: Data Defination Language 数据定义语言   CREATE，DROP，ALTER 
DML: Data Manipulation Language 数据操纵语言   INSERT，DELETE，UPDATE 
DCL：Data Control Language 数据控制语言   GRANT，REVOKE，COMMIT，ROLLBACK 
DQL：Data Query Language 数据查询语言   SELECT 

1.2 数据库操作

创建数据库：  CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME';  
 CHARACTER SET 'character set name’COLLATE 'collate name' 

修改数据库：    ALTER DATABASE DB_NAME character set  utf8; 

删除数据库  DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';  

查看支持所有字符集：SHOW CHARACTER SET; 

查看支持所有排序规则：SHOW COLLATION; 

获取命令使用帮助：  mysql> HELP KEYWORD; 

查看数据库列表：  mysql> SHOW DATABASES;  
表 
表：二维关系
 设计表：遵循规范
定义：字段，索引  字段：字段名，字段数据类型，修饰符  约束，索引：应该创建在经常用作查询条件的字段上 
 

1.3查询表

SELECT  [ALL | DISTINCT | DISTINCTROW ]   [SQL_CACHE | SQL_NO_CACHE]    select_expr [, select_expr ...]      [FROM table_references      [WHERE where_condition]      [GROUP BY {col_name | expr | position}        [ASC | DESC], ... [WITH ROLLUP]]      [HAVING where_condition]      [ORDER BY {col_name | expr | position}        [ASC | DESC], ...]      [LIMIT {[offset,] row_count | row_count OFFSET offset}]      [FOR UPDATE | LOCK IN SHARE MODE] 

字段显示可以使用别名：     col1 AS alias1, col2 AS alias2, ... 
WHERE子句：指明过滤条件以实现“选择”的功能： 
过滤条件：布尔型表达式 
算术操作符：+, -, *, /, % 
比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <= 
BETWEEN min_num
 AND max_num IN (element1, element2, ...)
  IS NULL IS NOT NULL 
DISTINCT 去除重复列  
#例子
SELECT DISTINCT  gender FROM students;
 
 LIKE:  
  % 任意长度的任意字符
   _ 任意单个字符 
 RLIKE：正则表达式，索引失效，不建议使用 
 
 REGEXP：匹配字符串可用正则表达式书写模式，同上 逻辑操作符： NOT AND OR XOR 
 

1.4 select语句使用的条件词

GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算 avg(), max(), min(), count(), sum()     
HAVING: 对分组聚合运算后的结果指定过滤条件 

ORDER BY: 根据指定的字段对查询结果进行排序 升序：ASC 降序：DESC

LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制 

对查询结果中的数据请求施加“锁” 
 FOR UPDATE: 写锁，独占或排它锁，只有一个读和写 
 LOCK IN SHARE MODE: 读锁，共享锁，同时多个读 

二 MySQL查询语句

2.1 查看慢查询日志

MariaDB [(none)]> show variables like ‘%slow%’

    -> ;
+---------------------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name             | Value                                                                                                        |
+---------------------------+--------------------------------------------------------------------------------------------------------------+
| log_slow_admin_statements | ON                                                                                                           |
| log_slow_filter           | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |
| log_slow_rate_limit       | 1                                                                                                            |
| log_slow_slave_statements | ON                                                                                                           |
| log_slow_verbosity        |                                                                                                              |
| slow_launch_time          | 2                                                                                                            |
| slow_query_log            | ON                                                                                                           |
| slow_query_log_file       | /usr/local/mysql/data/mysql-slow.log 

log_slow_admin_statements 记录那些慢的optimize table，analyze table和alter table语句
log_slow_filter
log_slow_rate_limit 记录那些由于查找了多次而引发的慢查询nts
log_slow_slave_stateme
log_slow_verbosity
slow_launch_time 表示超过这个时间，就存入慢查询日志中
slow_query_log 慢查询记录是否开启，如果没有开启执行set global slow_query_log = 1(临时)，最好配置文件中配置
slow_query_log_file 慢查询日志存储的位置

2.2显示慢查询全局状态

MariaDB [(none)]> show global status like ‘%slow%’;

+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 0     |
+---------------------+-------+
slow_launch_time=t   表示如果建立线程花费了比这个值更长的时间
slow_queries 表示有多个慢查询语句。

2.3 MySQL服务器最大连接数

MariaDB [(none)]> show variables like ‘max_connections’;

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+

2.4 服务器响应的最大连接数。

MySQL服务器过去的最大连接数是1，没有达到服务器连接数上限256，
比较理想的设置是：Max_used_connections / max_connections * 100% ≈ 85%，最大连接数占上限连接数的85%左右，
如果发现比例在10%以下，MySQL服务器连接数上限设置的过高了。

MariaDB [(none)]> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 1     |

2.5 给key_buffer_size分配了多少内存

MariaDB [(none)]> show variables like ‘key_buffer_size’;

+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 4194304 |
+-----------------+---------+

2.6 显示key_buffer使用情况。

MariaDB [(none)]> show global status like ‘key_read%’;

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 0     |
| Key_reads         | 0     |
+-------------------+-------+
key_read_requests 表示有多个索引请求。
key_reads 有这么多请求在内存中没有找到数据，直接从硬盘中读取索引。

计算命中缓存概率：Key_reads / Key_read_requests * 100%，缓存命中率在0.1%以下就很好(每1000个请求有一个直接读硬盘)，
如果缓存命中率在0.01%以下的话，key_buffer_size分配的过多，可以适当减少。

2.7 显示全局 blocks数状态

MariaDB [(none)]> show global status like 'key_blocks_u%';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_blocks_unused | 3341  |
| Key_blocks_used   | 0     |
+-------------------+-------+
Key_blocks_unused 表示未使用的缓存blocks数。
Key_blocks_used   表示曾经用到的最大的blocks数。
假如所有缓存都用到了，要么增加key_buffer_size，
要么就是过渡索引了，把缓存占满了。
比较理想的设置： Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

2.8 显示线程数 show status like ‘Threads%’;

| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 1     |
| Threads_created   | 2     |
| Threads_running   | 1     |
+-------------------+-------+
Threads_cached  线程数缓存大小
Threads_connected 打开的连接数。
Threads_created 表示创建过的线程数。
Threads_running这个数值指的是激活的连接数，这个数值一般远低于connected数值。

{ 在MySQL服务器配置文件中设置了thread_cache_size，
当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)。
Threads_created表示创建过的线程数，
如果发现Threads_created  值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，查询服务器thread_cache_size配置

2.9 查看表结构、引擎、默认的字符编码。

show variables like 'default_storage_engine'; 查看当前默认引擎
show variables like '%storage_engine%'; 查看所有引擎
show create table testmode; 查看当前表结构

三 数据库导入和查询相关

3.1 导入sql文件，生成数据库

mysql –uUSERNAME -pPASSWORD < /path/somefile.sql  
mysql>source    /path/from/somefile.sql 

进入数据库，查看数据库的students有哪些内容

3.2 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄

select Name,Age  from students where Age >25 AND Gender ="M"

3.3 以ClassID为分组依据，显示每组的平均年龄

select StuID,Name,avg(Age) from students  where ClassID is not null group by ClassID;

3.4 显示第2题中平均年龄大于30的分组及平均年龄

select s.aage,s.ClassID from (select avg(Age) as aage,ClassID from students where ClassID is not null group by ClassID) as s where s.aage >30;

3.5 显示以L开头的名字的同学的信息

select Name from students where Name like 'L%';

3.6 以年龄排序后，显示年龄最大的前10位同学的信息

select * from students order by Age desc limit 10;

3.7 数据库授权magedu用户，允许192.168.1.0/24网段可以连接mysql

create user magedu identified by '123456';
#允许magedu账户有查询权限。
 grant  sleect  on *.* to magedu@'192.168.1.0/24' identified by '123456';
 flush privileges;