安装及基础知识

[client]
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
port    = 3306
socket  = /tmp/mysql.sock

##  dir set

datadir           = /data/mysql/data
innodb_data_home_dir   = /data/mysql/data
innodb_log_group_home_dir = /data/mysql/data
log-bin           = /data/mysql/binlog/mysql-bin
log_bin_index             = /data/mysql/binlog/mysql-bin.index  
relay-log         = /data/mysql/binlog/mysql-relay-bin
tmpdir            = /data/mysql/tmpdir
slow_query_log_file   = /data/mysql/log/mysql-slow.log
general_log_file   = /data/mysql/log/mysql-general.log
log-error         = /data/mysql/log/mysql.err

## slave and binlog

server-id = 6666            #   
skip-slave-start = 0        #
read_only = 0           #
binlog_format = row             
log-slave-updates = 1
master_info_repository = table
relay_log_info_repository = table
relay_log_purge = 1
relay_log_recovery = 1
sync_binlog = 100                   # !!!

binlog_cache_size = 1M
expire_logs_days = 30
log_bin_trust_function_creators = 1         
slave_net_timeout=60                
#binlog_error_action="IGNORE_ERROR"     

innodb_autoinc_lock_mode=1          

## 
back_log = 200
bulk_insert_buffer_size = 8M            
#character-set-server = utf8
lower_case_table_names = 1              #  1：不区分

## 基线

local-infile = off
skip-networking = off
skip-name-resolve = on 

## connect

max_allowed_packet = 32M
max_connect_errors = 1000
max_connections = 3000
wait_timeout = 3600             # 关闭 非交互 连接之前等待活动的秒数 default:8h
interactive_timeout = 3600          # 关闭 交互式 连接之前等待活动的秒数 default:8h

table_open_cache = 4096
thread_cache_size =  64
thread_stack = 192K
transaction-isolation = REPEATABLE-READ     # 
pid-file = mysql.pid

## slow

slow_query_log = 1              
long_query_time = 1
log-slow-admin-statements
log_queries_not_using_indexes = 0
slow_launch_time = 1
read_buffer_size = 4M               
read_rnd_buffer_size = 8M           
sort_buffer_size = 8M
join_buffer_size = 32M
tmp_table_size = 128M
max_heap_table_size = 128M

default-storage-engine = innodb
explicit_defaults_for_timestamp = on           

## innodb

innodb_buffer_pool_size = 1G                    
innodb_max_dirty_pages_pct = 80         
innodb_thread_concurrency = 8           
innodb_buffer_pool_instances = 1        
innodb_flush_log_at_trx_commit = 2      
innodb_read_io_threads = 8          
innodb_write_io_threads = 4         
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000
innodb_lru_scan_depth = 1024
innodb_use_native_aio = 1
innodb_flush_neighbors = 1
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1

innodb_data_file_path=ibdata:1G:autoextend
innodb_log_files_in_group = 3
innodb_log_file_size = 2G
innodb_file_per_table = 1 

innodb_flush_method = O_DIRECT
innodb_strict_mode = 1
innodb_lock_wait_timeout = 30
innodb_log_buffer_size = 16M
innodb_adaptive_flushing = 1
innodb_change_buffering = all
innodb_purge_threads = 4            
innodb_purge_batch_size = 300           

innodb_old_blocks_time = 1
innodb_fast_shutdown = 0
performance_schema = 1
innodb_print_all_deadlocks = 1
innodb_sort_buffer_size = 4M 

innodb_page_size = 16k
gtid_mode=on
enforce_gtid_consistency=on     

table_open_cache_instances=16
binlog_rows_query_log_events=1      

slave_parallel_workers = 0          # 多线程复制线程数
#slave_parallel_type=LOGICAL_CLOCK      
#binlog_group_commit_sync_delay = 500000  
#binlog_group_commit_sync_no_delay_count =12  

## pasword

default_password_lifetime=0                     # 0密码永不过期，N n天过期

[mysqldump]
quick
max_allowed_packet = 32M

[mysql]
no-auto-rehash
prompt=\p@\d>\_

[mysqld_safe]
open-files-limit = 28192

[mysqlhotcopy]
interactive-timeout

1、create database china;
创建数据库

2、show databases;
查看当前实例所拥有的数据库

3、use china;
选择（使用）数据库

4、create table t1( id int not null auto_increment, name char(10), city char(10), country char(10), email char(20),primary key (`id`)) engine=Innodb charset=utf8;
建表

5、show tables;
查看当前库所拥有的表

6、desc t1;
获取数据表结构

7、show create table t1;
查看建表语句

8、show status;
显示当前mysql实例的运行状态

9、status;
显示当前库的状态

10、alter table t1 add address char(40);
增加字段

11、alter table t1 drop address;
删除字段

12、create table t3 like t1;
复制表（只包含表结构）

13、alter table t1 modify city char(30);
更改类型

14、alter table t1 rename t2;
表的重命名

15、alter table t2 convert to charset gbk;
更改字符集

16、alter table t2 change city sex char(40);
更改字段名

17、alter table t3 add primary key(id);
增加主键

首先，创建一张表以备以下语句使用
create table t2(id int not null auto_increment,name varchar(10),city varchar(10),primary key(`id`),index index_city(`city`))engine=innodb charset=utf8;

1、insert into t2(id,name,city) values(1,'刘','长沙');
写入数据

2、insert into t2 values (2,'朱','上海');
每行都写入数据，可不写字段名

3、insert into t2(name,city) values ('张','北京'),('王','上海');
一次写入多行数据（由于id为自增，因此如果id未指定值将自动分配,分配的值为表中原有数据的id最大值加1）

4、create table t3 like t2;
insert into t3 select * from t2;
把一个表里的所有数据全部写入另一个表中

5、select * from t2;
查看表中所有数据
select * from t2 where id=2;
按照条件查询数据

6、update t2 set name='李' where id=1;
更新id为1的名字

7、delete from t2 where id=3;
删除表中id为3的数据

8、truncate t3;
delete from t2;
删除表中所有数据
truncate的原理为先drop表，再创建表；而delete为逐行删除。

1、create user 'test_1'@'localhost' identified by '123456';
创建用户
验证：
退出当前登录，使用
mysql -utest_1 -p123456
如果能登录，则证明创建成功

2、select user,host,password from mysql.user;
显示当前实例所有用户的信息

3、delete from mysql.user where user ='test_1';
删除用户
flush privileges;
刷新权限

4、create user 'test_2'@'localhost';
set password for 'test_2'@'localhost'=password('123456');
添加/更改密码

5、update mysql.user set password=password('abcdef') where user='test_2';
更新密码
flush privileges;

6、grant select on china.t2 to 'tws'@'192.168.11.114' identified by '123456';
允许192.168.11.114通过用户名tws、密码123456连接到数据库服务器，可读取数据库china中t2表的数据
flush privileges;

7、grant update(name) on china.t2 to 'tws'@'192.168.11.114';
允许192.168.11.114连接到数据库，并且能修改china库中t2表name字段的值（注：后面未添加identity by是因为在第6条已经为tws用户添加密码）
flush privileges;

8、revoke update(name) on china.t2 from 'tws'@'192.168.11.114';
撤销用户tws更新t2表中name字段的权限。
flush privileges;

9、show grants for 'tws'@'192.168.11.114';
查看该用户的权限

一、创建表及写入数据方便后续使用

create table student(`id` int(11) NOT NULL AUTO_INCREMENT,`name` char(10) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB CHARSET=utf8;

create table score(`id` int(11) NOT NULL AUTO_INCREMENT,score int,PRIMARY KEY (`id`))ENGINE=InnoDB CHARSET=utf8;

insert into student values(1,'zhang'),(2,'wang'),(3,'zhao');

insert into score values(1,88),(2,90),(4,92);

select * from student;

+----+-------+

| id | name |

+----+-------+

| 1 | zhang |

| 2 | wang |

| 3 | zhao |

+----+-------+

select * from score;

+----+-------+

| id | score |

+----+-------+

| 1 | 88 |

| 2 | 90 |

| 4 | 92 |

+----+-------+

二、连接查询

1、内连接（inner join）

select * from student inner join score on student.id = score.id;

+----+-------+----+-------+

| id | name | id | score |

+----+-------+----+-------+

| 1 | zhang | 1 | 88 |

| 2 | wang | 2 | 90 |

+----+-------+----+-------+

可以看出，在内查询中，只有满足条件（两表id相等）的数据才会出现。

2、外连接

2.1、左连接(left join)

select * from student left join score on student.id=score.id;

+--+-------+------+-------+

| id | name | id | score |

+--+-------+------+-------+

| 1 | zhang | 1 | 88 |

| 2 | wang | 2 | 90 |

| 3 | zhao | NULL | NULL |

+----+-------+------+-------+

可以看出，左连接查询的结果为左表的全部记录，即使右表中没有对应的匹配记录

2.2、右连接(right join)

select * from student right join score on student.id=score.id;

+------+-------+----+-------+

| id | name | id | score |

+------+-------+----+-------+

| 1 | zhang | 1 | 88 |

| 2 | wang | 2 | 90 |

| NULL | NULL | 4 | 92 |

+------+-------+----+-------+

可以看出，右连接查询的结果为右表的全部记录，即使左表中没有对应的匹配记录

三、子查询

1、where子查询

字句结果只匹配一条数据的情况

select * from student where id = (select id from score where score = 90);

+----+------+

| id | name |

+----+------+

| 2 | wang |

+----+------+

子句结果匹配多行数据的情况

select * from student where id in (select id from score where score <= 90);

+----+-------+

| id | name |

+----+-------+

| 1 | zhang |

| 2 | wang |

+----+-------+

2、from子查询

select id,score from (select id,score-1 as score from score) as score_tmp where score >=90;

+----+-------+

| id | score |

+----+-------+

| 4 | 91 |

+----+-------+

可理解为将score表中score字段的每个值减1生成一个临时表，然后查询这个临时表中score大于或等于90的。

3、exists子查询

select * from score where exists (select * from student where student.id=score.id);

+----+-------+

| id | score |

+----+-------+

| 1 | 88 |

| 2 | 90 |

+----+-------+

查出score表中的数据，但必须score的id在student表中存在。

1、CREATE TABLE class_info(`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',`class` int(11) NOT NULL COMMENT '班级',`name` char(10) NOT NULL COMMENT '姓名',`score` int(11) NOT NULL COMMENT '分数',PRIMARY KEY (`id`),index index_score(`score`)) ENGINE=InnoDB CHARSET=utf8;

创建表，以便后续使用

2、insert into class_info(`class`,`name`,`score`) values (1601,'a',87),(1601,'b',90),(1602,'d',91),(1602,'c',85);

写入测试数据

3、select * from class_info;

+----+-------+------+-------+

| id | class | name | score |

+----+-------+------+-------+

| 1 | 1601 | a | 87 |

| 2 | 1601 | b | 90 |

| 3 | 1602 | d | 91 |

| 4 | 1602 | c | 85 |

+----+-------+------+-------+

显示表中所有数据

4、select * from class_info group by score;

+----+-------+------+-------+

| id | class | name | score |

+----+-------+------+-------+

| 4 | 1602 | c | 85 |

| 1 | 1601 | a | 87 |

| 2 | 1601 | b | 90 |

| 3 | 1602 | d | 91 |

+----+-------+------+-------+

根据分数字段排序（升序）显示全表数据

5、select * from class_info group by score desc;

+----+-------+------+-------+

| id | class | name | score |

+----+-------+------+-------+

| 3 | 1602 | d | 91 |

| 2 | 1601 | b | 90 |

| 1 | 1601 | a | 87 |

| 4 | 1602 | c | 85 |

+----+-------+------+-------+

根据分数字段排序（降序）显示全表数据

6、select class,count(*) from class_info group by class;

+-------+----------+

| class | count(*) |

+-------+----------+

| 1601 | 2 |

| 1602 | 2 |

+-------+----------+

显示每个班级的人数

7、select class,group_concat(name) from class_info group by class;

+-------+--------------------+

| class | group_concat(name) |

+-------+--------------------+

| 1601 | a,b |

| 1602 | d,c |

+-------+--------------------+

按班级分开显示学生名字

8、select class,max(score) as maxscore from class_info group by class;

+-------+----------+

| class | maxscore |

+-------+----------+

| 1601 | 90 |

| 1602 | 91 |

+-------+----------+

显示每个班的最高分

9、select class,avg(score) as avgscore from class_info group by class;

+-------+----------+

| class | avgscore |

+-------+----------+

| 1601 | 88.5000 |

| 1602 | 88.0000 |

+-------+----------+

显示每个班的平均分数

10、select class,avg(score) as avgscore from class_info group by class having avgscore>88;

+-------+----------+

| class | avgscore |

+-------+----------+

| 1601 | 88.5000 |

+-------+----------+

显示平均分大于88的班级