centos6默认安装MySQL（已安装）
centos7默认mariadb

实验：yum安装

centos6

[root@CentOS6 ~]# service mysqld start    #开启服务，端口3306
/usr/bin/mysqladmin -u root password 'new-password'    #安全加固命令
/usr/bin/mysqladmin -u root -h CentOS6.localdomain password 'new-password'
Alternatively you can run:
/usr/bin/mysql_secure_installation        #安全加固脚本（常用）
[root@CentOS6 ~]# mysql    #进入（无密码）
mysql>\h      #查看帮助命令
mysql>status    #查看状态（\s）
mysql> show batabases;   #显示数据库（服务器命令用;表示结束，客户端命令不需要）   
+--------------------+
| Database           |
+--------------------+
| information_schema |     #内存中的信息
| mysql              |
| test               |
+--------------------+
mysql> show tables;    #查看该数据库所有表格
mysql> select * from user;    #显示user表的所有信息（太乱、显示不全）；可以查询字段再显示内容
mysql> desc user;     #显示user表的所有字段
mysql> select host,user,password from user;    #查看字段host，user，password的属性
+---------------------+------+----------+
| host                | user | password |
+---------------------+------+----------+
| localhost           | root |          |
| centos6.localdomain | root |          |   #远程登录
| 127.0.0.1           | root |          |   #密码为空（安全隐患）
| localhost           |      |          |   #用户为空表示任何用户都可登录  mysql -u123可直接登录（并没有123用户）随便写什么都能登录
| centos6.localdomain |      |          |   #太危险需要安全加固，运行mysql_secure_installation
+---------------------+------+----------+
5 rows in set (0.00 sec)
[root@CentOS6 ~]# mysql_secure_installation     #安全加固
Enter current password for root (enter for none):      #输入密码（输入none或直接回车）
Set root password? [Y/n] y        #设置root密码
Remove anonymous users? [Y/n] y     #移除嘉宾登录
Disallow root login remotely? [Y/n] y      #禁止远程登录
Remove test database and access to it? [Y/n] y     #删除test数据库
Reload privilege tables now? [Y/n] y     #是否立即生效（前面的操作其实已经生效）
[root@CentOS6 ~]# mysql -pcentos   #可直接进入，但显示密码（centos）
[root@CentOS6 ~]# mysql -p           #交互式登录，回车后输入密码，密码隐藏
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |     #已删除test
+--------------------+
mysql> select user,host,password from user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *128977E278358FF80A246B5046F51043A2B1FCED |    #已加密，删除其他用户
| root | 127.0.0.1 | *128977E278358FF80A246B5046F51043A2B1FCED |
+------+-----------+-------------------------------------------+
[root@CentOS7 ~]# mysql -uroot -pcentos -h192.168.8.6      #已不能远程登录
ERROR 1130 (HY000): Host '192.168.8.7' is not allowed to connect to this MySQL server
[root@CentOS6 ~]# mysqladmin -pcentos ping      #显示数据库是否活跃
mysqld is alive 
[root@CentOS6 ~]# mysqladmin ping -p
Enter password: 
mysqld is alive
[root@CentOS6 ~]# mysqladmin -pcentos shutdown     #关闭数据库
[root@CentOS6 ~]# service mysqld start             #开启
[root@CentOS6 ~]# mysqladmin password centos       #添加密码（开始密码为空）
[root@CentOS6 ~]# mysqladmin -pcentos password magedu    #修改新密码
[root@CentOS6 ~]# mysqladmin -pmagedu creat db1          #创建数据库db1
[root@CentOS6 ~]# mysql -e "show databases" -p        #不登录数据库显示数据库信息
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |                        #已创建DB1
| mysql              |
+--------------------+
[root@CentOS6 ~]# cat  test.sql     #由于MySQL是交互式命令，可以以下方式实现
show databases;
use mysql;
select user,host,password from mysql.user;
[root@CentOS6 ~]# mysql -pmagedu< test.sql      #第一种
[root@CentOS6 ~]# cat test.sql |mysql -pmagedu     #第二种
##选择文件时区分大小写，而表格内容是不区分大小写

centos7

[root@CentOS7 ~]#yum install mariadb-server    #安装
[root@CentOS7 ~]# rpm -qi mariadb-server   系统安装版本太低，需要建立yum仓库在安装
Version     : 5.5.65
官网找到相应版本进入找到RPM Package点击use the Repository Configuration Tool.进入选取相应版本，复制制作repo仓库
[root@CentOS7 ~]# vim /etc/yum.repos.d/aliyun.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
[root@CentOS7 ~]# yum install mariadb-server

实验：二进制安装

二进制安装为完成编译过程后进行的部分，官网下载已编译完成的软件包进行安装

[root@CentOS7 ~]# mkdir /data/mysql       #第一步
[root@CentOS7 ~]# useradd -r -s /sbin/nologin -d /data/mysql mysql       #第二步：创建用户及组，默认家目录为/data/mysql
[root@CentOS7 ~]# tar xvf mariadb-10.2.25-linux-x86_64.tar.gz -C /usr/local    #3解压到/usr/local
[root@CentOS7 ~]# du -sh /usr/local/
1.5G	/usr/local/
[root@CentOS7 /usr/local]# ln -s mariadb-10.2.25-linux-x86_64/ mysql     #创建软连接（编译生成的文件）
[root@CentOS7 /usr/local]# chown -R mysql.mysql mysql/     #修改用户和组权限
[root@CentOS7 /usr/local]# cd mysql/                      #进入mysql（后面跟源码编译编译完成后步骤一致）
[root@CentOS7 /usr/local/mysql]# ./scripts/./mysql_install_db --datadir=/data/mysql --user=mysql                                     #复制安装脚本
[root@CentOS7 /usr/local/mysql]# cp support-files/my-huge.cnf /etc/mysql/my.cnf     #复制启动配置
[root@CentOS7 /usr/local/mysql]# vim /etc/mysql/my.cnf                  #编辑配置
[mysqld]
datadir         = /data/mysql
[root@CentOS7 /usr/local/mysql]# cp support-files/mysql.server /etc/init.d/mysqld    #复制命令
[root@CentOS7 /usr/local/mysql]# chkconfig --add mysqld       #查看开机服务裂口
[root@CentOS7 /usr/local/mysql]# service mysqld start           #重启 
[root@CentOS7 /usr/local/mysql]# echo 'PATH=/usr/local/mysql/bin:$PATH'>     #修订path浪费时间 /etc/profile.d/mysql.sh
[root@CentOS7 /usr/local/mysql]# mysql

实验：源码编译安装

#####做准备用户和数据目录#####
[root@CentOS7 ~]# yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel gcc gcc-c++ cmake ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel libdb4-cxx-devel         #安装环境
[root@CentOS7 ~]# useradd -r -s /sbin/nologin -d /data/mysql mysql
[root@CentOS7 ~]# mkdir /data/mysql
[root@CentOS7 ~]# chown mysql.mysql /data/mysql
[root@CentOS7 ~]# tar xvf mariadb-10.2.25.tar.gz
[root@CentOS7 ~]# cd mariadb-10.2.25/
#################################################编译开始
[root@CentOS7 ~/mariadb-10.2.25]#cmake .\
-DCMAKE_INSTALL_PREFIX=/app/mysql \
-DMYSQL_DATADIR=/data/mysql/ \
-DSYSCONFDIR=/etc/ \
-DMYSQL_USER=mysql \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \
-DWITH_DEBUG=0 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci
[root@CentOS7 ~/mariadb-10.2.25]# make -j 8 && make install
#############################################编译完成
[root@CentOS7 ~/mariadb-10.2.25]# cd /app/mysql/
[root@CentOS7 /app/mysql]# ./scripts/mysql_install_db --datadir=/data/mysql --user=mysql   #生成数据库
[root@CentOS7 /app/mysql]# cp support-files/my-huge.cnf /etc/my.cnf         #准备配置文件
[root@CentOS7 /app/mysql]# cp support-files/mysql.server /etc/init.d/mysqld      #准备启动脚本
[root@CentOS7 /app/mysql]# chkconfig --add mysqld                     #启动服务
[root@CentOS7 /app/mysql]# service mysqld start
[root@CentOS7 /app/mysql]# echo 'PATH=/app/mysql/bin:$PATH' > /etc/profile.d/mysql.sh    #准备环境变量
[root@CentOS7 /app/mysql]# . /etc/profile.d/mysql.sh
[root@CentOS7 /app/mysql]# mysql                           #启动

实验：三实列（3个数据库软件）安装

[root@CentOS7 ~]# vim /etc/yum.repos.d/aliyun.repo           #修改yum仓库
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
[root@CentOS7 ~]# yum install mariadb-server               #安装
[root@CentOS7 ~]# mkdir /mysql/{3306,3307,3308}/{data,bin,log,socket,pid,etc} -pv
[root@CentOS7 ~]# chown -R mysql.mysql /mysql/
[root@CentOS7 ~]# mysql_install_db --datadir=/mysql/3306/data --user=mysql
[root@CentOS7 ~]# cp /etc/my.cnf /mysql/3306/etc/
[root@CentOS7 ~]# vim /mysql/3306/etc/my.cnf 
[mysqld]
port=3306
datadir=/mysql/3306/data/
socket=/mysql/3306/socket/mysql.sock[mysqld_safe]
log-error=/mysql/3306/log/mariadb.log
pid-file=/mysql/3306/pid/mariadb.pid
[root@CentOS7 ~]# mysql_install_db --datadir=/mysql/3307/data --user=mysql
[root@CentOS7 ~]# mysql_install_db --datadir=/mysql/3308/data --user=mysql
[root@CentOS7 ~]# cp /mysql/3306/etc/my.cnf /mysql//3307/etc/
[root@CentOS7 ~]# cp /mysql/3306/etc/my.cnf /mysql//3308/etc/
[root@CentOS7 ~]# sed -i 's/3306/3307/' /mysql/3307/etc/my.cnf 
[root@CentOS7 ~]# sed -i 's/3306/3308/' /mysql/3308/etc/my.cnf 
####准备启动脚本
[root@CentOS7 ~]# cd /mysql/3306/bin
[root@CentOS7 /mysql/3306/bin]# vim mysqld       #线下上传脚本
port=3306
mysql_user="root"
mysql_pwd="centos"
cmd_path="/usr/bin"         #修改可以ps aux查看mysqld_safe的路径（启动服务）                                                                                                           
mysql_basedir="/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"
[root@CentOS7 /mysql/3306/bin]# chmod +x mysqld 
[root@CentOS7 /mysql/3306/bin]# cp mysqld /mysql/3307/bin/
[root@CentOS7 /mysql/3306/bin]# cp mysqld /mysql/3308/bin/
[root@CentOS7 /mysql/3306/bin]# sed -i 's/3306/3307/' /mysql/3307/bin/mysqld 
[root@CentOS7 /mysql/3306/bin]# sed -i 's/3306/3308/' /mysql/3308/bin/mysqld
#####
[root@CentOS7 /mysql/3306/bin]# /mysql/3306/bin/mysqld start    #启动服务
[root@CentOS7 /mysql/3306/bin]# mysql -S /mysql/3306/socket/mysql.sock     #进入数据库（空口令）
[root@CentOS7 ~]# mysqladmin -S /mysql/3306/socket/mysql.sock password centos    #修改密码
[root@CentOS7 ~]# mysqladmin mysql -S /mysql/3306/socket/mysql.sock -p      #进入数据库（密码）
[root@CentOS7 ~]# /mysql/3306/bin/mysqld stop         #关闭服务

字符集

推荐使用utf8mb4

MariaDB [(none)]> show charset set    #查看支持字符集
MariaDB [(none)]> show create database test2;         #显示test2数据库属性
MariaDB [(none)]> create database test3 character set utf8mb4;    #创建test3（设置字符集为utf8mb4）
[root@CentOS7 /var/local]# cat /var/lib/mysql/test2/db.opt       #字符集保存配置文件
default-character-set=latin1
default-collation=latin1_swedish_ci
MariaDB [test]> show variables like "%character%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |       客户端
| character_set_connection | utf8                       |       
| character_set_database   | latin1                     |       数据库
| character_set_filesystem | binary                     |       字段
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |       服务器
| character_set_system     | utf8                       |       系统
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
[root@CentOS7 ~]# vim /etc/my.cnf      #配置服务器端默认字符集（以后新建全部为默认utf8mb4）
[mysqld]
character_set_server=utf8mb4
[root@CentOS7 ~]# mysql --default-character-set=utf8mb4       #临时修改客户端字符集登录，退出消失
[root@CentOS7 ~]# vim /etc/my.cnf.d/mysql-clients.cnf         #永久修改客户端字符集
[mysql]
default-character-set=utf8mb4

DDL

DDL: Data Defination Language 数据定义语言（表创建与修改）
CREATE，DROP，ALTER
MariaDB [(none)]> help create help后跟命令可以查看该命令后面命令的帮助

MariaDB [(none)]> show databases;             #显示数据库
MariaDB [(none)]> create database test2;            #创建
MariaDB [(none)]> show create database test2;         #显示属性
MariaDB [(none)]> drop database test2;                  #删除数据库（实际是删除/var/lib/mysql/test2/目录）
MariaDB [(none)]> use test;
MariaDB [test]> create table student (id smallint unsigned auto_increment primary key,name varchar(20) not null,gender enum('M','F') default 'M',mobile char(11) not null);     #在test中创建表student（第一种方式）
MariaDB [test]> desc student;             #显示表student的字段
+--------+----------------------+------+-----+---------+----------------+
| Field  | Type                 | Null | Key | Default | Extra          |
+--------+----------------------+------+-----+---------+----------------+
| id     | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)          | NO   |     | NULL    |                |
| gender | enum('M','F')        | YES  |     | M       |                |
| mobile | char(11)             | NO   |     | NULL    |                |
+--------+----------------------+------+-----+---------+----------------+
MariaDB [test]> show create table student;        #显示表student的字段所有属性
MariaDB [test]> show indexes from student;         #显示表student的索引
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
MariaDB [test]> use test3;
Database changed
MariaDB [test3]> create table student2 like test.student;     #创建表单（第二种方式）只是复制test.student属性
MariaDB [test3]> alter table student2 change name name varchar(30) character set utf8mb4;    #修改student2字段name的默认字符集及字段长度
MariaDB [test3]> alter table student2 change mobile mobile char(20);
MariaDB [test6]> create table student select * from test.student;      #创建（第三种方法）复制test.student属性及内容，相当于批量导入
MariaDB [test6]> alter table student change name name varchar(20 )character set utf8mb4;    #改字符集
MariaDB [test6]> insert student (name,mobile) values ("王",'123000'),("张丽",'11325');    #插入
MariaDB [test6]> select * from student;      #查询 
+----+---------+--------+-------------+
| id | name    | gender | mobile      |
+----+---------+--------+-------------+
|  1 | wang    | M      | 13880912480 |
|  2 | zhang   | M      | 18006       |
|  3 | zhangli | M      | 1862414     |
|  0 | 王      | M      | 123000      |
|  0 | 张丽    | M      | 11325       |
+----+---------+--------+-------------+

DML

DML: Data Manipulation Language 数据操纵语言 （内容创建修改删除）
INSERT，DELETE，UPDATE

数字型后面加unsigned表示为正数.
char类型的字符串检索速度要比varchar类型的快
.varchar类型的字符串占用空间要比char类型的小

MariaDB [test]> insert student (name,mobile) values ('wang','13880912480'),('zhang','18006');  #插入数据
MariaDB [test]> select * from student;           #查询
+----+-------+--------+-------------+
| id | name  | gender | mobile      |
+----+-------+--------+-------------+
|  1 | wang  | M      | 13880912480 |
|  2 | zhang | M      | 18006       |
+----+-------+--------+-------------+
MariaDB [test]> insert student (name,mobile) values ('zhangli','1862414')   #插入（方法一常用）
[root@CentOS7 ~]# mysql <hellodb_innodb.sql     #hellodb_innodb.sql本地上传，导入数据库，生成测试表
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)
MariaDB [hellodb]> insert students set name='mage',age=30,gender='M';     #插入（方法二不常用）
MariaDB [hellodb]> insert students (name,age) select name,age from teachers;   #插入（方法三，插入查询数据）
MariaDB [hellodb]> update students set ClassID=1 where StuID=25;      #修改数据
##where必须添加，否则会修改所有字段（危险），为防止不添加where修改数据，安全起见可以跟-U登录或者更改客户端配置
[root@CentOS7 ~]# mysql -U
MariaDB [(none)]> update students set ClassID=1;    #-U登录，不添加where报错
[root@CentOS7 ~]# vim /etc/my.cnf.d/mysql-clients.cnf
[mysql]
safe-update
[root@CentOS7 ~]# mysql 
MariaDB [(none)]> update students set ClassID=1;           #配置修改后，不带选项登录后修改报错
MariaDB [hellodb]> delete from students where StuID>20;      #删除大于20的，不带清空全部内容，速度慢（会写入日志）TRUNCATE table students 删除速度更快（不写入日志）

DQL

DQL：Data Query Language 数据查询语言（重点掌握）
SELECT

字段显示可以使用别名：
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

GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算
avg(), max(), min(), count(), sum()
HAVING: 对分组聚合运算后的结果指定过滤条件
ORDER BY: 根据指定的字段对查询结果进行排序
升序：ASC
降序：DESC
LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制
对查询结果中的数据请求施加“锁”
FOR UPDATE: 写锁，独占或排它锁，只有一个读和写
LOCK IN SHARE MODE: 读锁，共享锁，同时多个读

单表查询

MariaDB [hellodb]> select 'hello';
+-------+
| hello |
+-------+
| hello |
+-------+
MariaDB [hellodb]> select 2 * 3 ;
+-------+
| 2 * 3 |
+-------+
|     6 |
+-------+
MariaDB [hellodb]> select * from students;            #查询所有
MariaDB [hellodb]> select stuid,name,age from students;     #查询部分
+-------+---------------+-----+
| stuid | name          | age |
+-------+---------------+-----+
MariaDB [hellodb]> desc students;                        #显示表字段
MariaDB [hellodb]> select name as 姓名,age 年龄, stuid from students;         #字段用相应别名显示    
+---------------+--------+-------+
| 姓名          | 年龄   | stuid |
+---------------+--------+-------+
MariaDB [hellodb]> select name as 姓名,age 年龄, stuid from students as 学生表;     #表用别名表示，后面有用
MariaDB [hellodb]> select * from students where stuid >=20;          #显示stuid号大于20的内容
MariaDB [hellodb]> select * from students where stuid =20;           #显示stuid号大于20的内容
MariaDB [hellodb]> select * from students where stuid <>20;          #显示stuid号不等于20的内容
MariaDB [hellodb]> select * from students where stuid !=20;          #显示stuid号不等于20的内容（不是SQL标准语言，使用较少）
MariaDB [hellodb]> select * from students where name = 'Yu Yutong';     #显示特定姓名
MariaDB [hellodb]> select * from students where age>=20 and gender='f';    #显示年龄大于等于20且为女
MariaDB [hellodb]> select *from students where age>=20 and age<=30;     #查询20-30
MariaDB [hellodb]> select *from students where age between 20 and 30;    #查询20-30
MariaDB [hellodb]> select *from students where name like 's%';           #查询姓名s开头
MariaDB [hellodb]> select *from students where name like '%yu%';         #查询姓名中间含义yu的
MariaDB [hellodb]> select *from students where name  rlike '^s';        #查询姓名s开头（正则表达式，不常用）
MariaDB [hellodb]> select distinct age from students;        #年龄合并查询（只显示一条，不显示重复的）
MariaDB [hellodb]> select *from students where classid is null;    #显示classid为空字符串
MariaDB [hellodb]> select *from students where classid is not null;    #显示classid为非空字符串
MariaDB [hellodb]> select count(stuid) as 记录数 from students;      #查询统计数
+-----------+
| 记录数    |
+-----------+
|        20 |
+-----------+
1 row in set (0.00 sec)MariaDB [hellodb]> select count(teacherid) as 记录数 from students;     #查询统计数（默认不包括空字符串）
+-----------+
| 记录数    |
+-----------+
|         5 |
+-----------+
1 row in set (0.00 sec)MariaDB [hellodb]> select count(*) as 记录数 from students;     #查询统计数（一般使用、总有字段为非空）
+-----------+
| 记录数    |
+-----------+
|        20 |
+-----------+
MariaDB [hellodb]> select max(age) as 年龄 from students;        #查最大
MariaDB [hellodb]> select min(age) as 年龄 from students;        #查最小
MariaDB [hellodb]> select avg(age) as 年龄 from students;        #查平均
MariaDB [hellodb]> select gender,avg(age) as 平均数 from students group by gender;    #查男女年龄平均数
+--------+-----------+
| gender | 平均数    |
+--------+-----------+
| F      |   18.5000 |
| M      |   28.7500 |
+--------+-----------+
MariaDB [hellodb]> select classid,avg(age) as 平均数 from students group by classid having classid>3;       #查班级大于3每班年龄平均数（having在后）
MariaDB [hellodb]> select classid,avg(age) as 平均数 from students where classid>3 group by classid;         #查班级大于3每班年龄平均数（where在前）
+---------+-----------+
| classid | 平均数    |
+---------+-----------+
|       4 |   25.3333 |
|       5 |   46.0000 |
|       6 |   20.3333 |
|       7 |   19.6667 |
+---------+-----------+MariaDB [hellodb]> select classid,avg(age) as 平均数 from students where classid>3 group by classid having 平均数>30;         # #查班级大于3年龄平均数大于30
+---------+-----------+
| classid | 平均数    |
+---------+-----------+
|       5 |   46.0000 |
+---------+-----------+
MariaDB [hellodb]> select * from students group by classid,gender;    #根据班级，性别查询，只显示每个不同的第一条
MariaDB [hellodb]> select classid,gender,avg(age) from students group by classid,gender;   #根据班级，性别查询，只显示平均数
MariaDB [hellodb]> select *from students order by age;     #按年龄正序排列（从小到大）
MariaDB [hellodb]> select *from students order by age asc;     #按年龄正序排列（从小到大）
MariaDB [hellodb]> select *from students order by age desc;    #按年龄倒序排列（从大到小）
MariaDB [hellodb]> select *from students order by -teacherid desc;   #按教师编号正序排列（从小到大）（空字符串排除在外，否则默认空字符串排前）
MariaDB [hellodb]> select classid,sum(age) from students group by classid order by classid;     #查询班级总年龄并按班级排序
MariaDB [hellodb]> select classid,sum(age) from students group by classid having classid is not null order by classid;   ##查询班级总年龄（班级不为空）并按班级排序
MariaDB [hellodb]> select classid,sum(age) from students where classid is not null group by classid  order by classid;    ##查询班级（班级不为空）总年龄并按班级排序（先排除）
MariaDB [hellodb]> select classid,sum(age) from students where classid is not null group by classid  order by classid limit 3;    #显示前3行
MariaDB [hellodb]> select classid,sum(age) from students where classid is not null group by classid  order by classid limit 2,3;    #排除前2行，显示3-5行
MariaDB [hellodb]> select *from students where classid=1 or classid=3 or classid=5;   #显示1、3、5班
MariaDB [hellodb]> select *from students where classid in (1,3,5);    #显示1、3、5班

实验：SQL注入

一般情况下，用户密码存放于数据库中，输入用户名密码查询出即登录成功，但黑客可以通过一些方式也可以查询出结果，进而登录系统

MariaDB [hellodb]> create table users(id int,username char(30),password char(30));     #创建表
MariaDB [hellodb]> insert users values (1,'admin','magedu'),(2,'mage','magedu'),(2,'wang','centos');     #添加用户信息
MariaDB [hellodb]> select *from users    ; 
+------+----------+----------+
| id   | username | password |
+------+----------+----------+
|    1 | admin    | magedu   |
|    2 | mage     | magedu   |
|    2 | wang     | centos   |
+------+----------+----------+
MariaDB [hellodb]> select *from users where username='admin' and password='magedu';   #当用户名和密码匹配出查询结果
当黑客输入用户：admin，密码：' or '1'='1也能查询，是一个SQL的安全隐患，成为SQL注入，现阶段的MySQL软件已解决相关问题，但也可以注意一下
MariaDB [hellodb]> select *from users where username='admin' and password='' or '1'='1'; 
+------+----------+----------+
| id   | username | password |
+------+----------+----------+
|    1 | admin    | magedu   |
|    2 | mage     | magedu   |
|    2 | wang     | centos   |
+------+----------+----------+
当黑客输入用户：admin'--，密码：'也能查询，
MariaDB [hellodb]> select *from users where username='admin'--' and password='''; 
+------+----------+----------+
| id   | username | password |
+------+----------+----------+
|    1 | admin    | magedu   |
|    2 | mage     | magedu   |
|    2 | wang     | centos   |
+------+----------+----------+

多表查询

纵向查询

MariaDB [hellodb]> select stuid,name,age,gender from students union select *from teachers;
##相同字段内容会合并
+-------+---------------+-----+--------+
| stuid | name          | age | gender |
+-------+---------------+-----+--------+
|     1 | Shi Zhongyu   |  22 | M      |
|     2 | Shi Potian    |  22 | M      |
|     3 | Xie Yanke     |  53 | M      |
|     4 | Ding Dian     |  32 | M      |
MariaDB [hellodb]> select stuid,name,age,gender from students union all select *from teachers;
##相同字段内容不会合并
+-------+---------------+-----+--------+
| stuid | name          | age | gender |
+-------+---------------+-----+--------+
|     1 | Shi Zhongyu   |  22 | M      |
|     2 | Shi Potian    |  22 | M      |
MariaDB [hellodb]> select *from users;
+------+----------+----------+
| id   | username | password |
+------+----------+----------+
|    1 | admin    | magedu   |
|    2 | mage     | magedu   |
|    2 | wang     | centos   |
|    2 | wang     | centos   |
+------+----------+----------+
MariaDB [hellodb]> select *from users union select *from users;       #去重
+------+----------+----------+
| id   | username | password |
+------+----------+----------+
|    1 | admin    | magedu   |
|    2 | mage     | magedu   |
|    2 | wang     | centos   |
+------+----------+----------+
MariaDB [hellodb]> select distinct *from users;        #去重
+------+----------+----------+
| id   | username | password |
+------+----------+----------+
|    1 | admin    | magedu   |
|    2 | mage     | magedu   |
|    2 | wang     | centos   |
+------+----------+----------+

横向查询

教材连接（笛卡尔乘积）

成倍增加

MariaDB [hellodb]> select * from users cross join teachers;
+------+----------+----------+-----+---------------+-----+--------+
| id   | username | password | TID | Name          | Age | Gender |
+------+----------+----------+-----+---------------+-----+--------+
|    1 | admin    | magedu   |   1 | Song Jiang    |  45 | M      |
|    2 | mage     | magedu   |   1 | Song Jiang    |  45 | M      |
|    2 | wang     | centos   |   1 | Song Jiang    |  45 | M      |
|    2 | wang     | centos   |   1 | Song Jiang    |  45 | M      |
|    1 | admin    | magedu   |   2 | Zhang Sanfeng |  94 | M      |
|    2 | mage     | magedu   |   2 | Zhang Sanfeng |  94 | M      |
|    2 | wang     | centos   |   2 | Zhang Sanfeng |  94 | M      |
|    2 | wang     | centos   |   2 | Zhang Sanfeng |  94 | M      |
|    1 | admin    | magedu   |   3 | Miejue Shitai |  77 | F      |
|    2 | mage     | magedu   |   3 | Miejue Shitai |  77 | F      |
|    2 | wang     | centos   |   3 | Miejue Shitai |  77 | F      |
|    2 | wang     | centos   |   3 | Miejue Shitai |  77 | F      |
|    1 | admin    | magedu   |   4 | Lin Chaoying  |  93 | F      |
|    2 | mage     | magedu   |   4 | Lin Chaoying  |  93 | F      |
|    2 | wang     | centos   |   4 | Lin Chaoying  |  93 | F      |
|    2 | wang     | centos   |   4 | Lin Chaoying  |  93 | F      |
+------+----------+----------+-----+---------------+-----+--------+

内连接inner join

MariaDB [hellodb]> select * from students inner join teachers on students.teacherid=teachers.tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name        | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
MariaDB [hellodb]> select * from students inner join teachers on teacherid=tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name        | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
MariaDB [hellodb]> select stuid,s.name,s.age,tid,t.name from students as s inner join teachers as t on s.teacherid=t.tid;    #正确书写格式
+-------+-------------+-----+-----+---------------+
| stuid | name        | age | tid | name          |
+-------+-------------+-----+-----+---------------+
|     5 | Yu Yutong   |  26 |   1 | Song Jiang    |
|     1 | Shi Zhongyu |  22 |   3 | Miejue Shitai |
|     4 | Ding Dian   |  32 |   4 | Lin Chaoying  |
+-------+-------------+-----+-----+---------------+
MariaDB [hellodb]> select stuid,s.name studentname,s.age,tid,t.name teachername from students as s inner join teachers as t on s.teacherid=t.tid;   #别名显示
+-------+-------------+-----+-----+---------------+
| stuid | studentname | age | tid | teachername   |
+-------+-------------+-----+-----+---------------+
|     5 | Yu Yutong   |  26 |   1 | Song Jiang    |
|     1 | Shi Zhongyu |  22 |   3 | Miejue Shitai |
|     4 | Ding Dian   |  32 |   4 | Lin Chaoying  |
+-------+-------------+-----+-----+---------------+
MariaDB [hellodb]> select stuid,s.name,s.age,tid,t.name from students s,teachers t where s.teacherid=t.tid;     #以前老式方法
+-------+-------------+-----+-----+---------------+
| stuid | name        | age | tid | name          |
+-------+-------------+-----+-----+---------------+
|     5 | Yu Yutong   |  26 |   1 | Song Jiang    |
|     1 | Shi Zhongyu |  22 |   3 | Miejue Shitai |
|     4 | Ding Dian   |  32 |   4 | Lin Chaoying  |
+-------+-------------+-----+-----+---------------+
MariaDB [hellodb]> select stuid,s.name studentname,s.age,tid,t.name teachername from students as s inner join teachers as t on s.teacherid=t.tid and s.age>30;    #过滤年龄大于30
+-------+-------------+-----+-----+--------------+
| stuid | studentname | age | tid | teachername  |
+-------+-------------+-----+-----+--------------+
|     4 | Ding Dian   |  32 |   4 | Lin Chaoying |
+-------+-------------+-----+-----+--------------+

左边没有显示完

左外连接left outer join

MariaDB [hellodb]> select stuid,s.name,s.age,tid,t.name from students as s left outer join teachers as t on s.teacherid=t.tid;
+-------+---------------+-----+------+---------------+
| stuid | name          | age | tid  | name          |
+-------+---------------+-----+------+---------------+
|     1 | Shi Zhongyu   |  22 |    3 | Miejue Shitai |
|     2 | Shi Potian    |  22 | NULL | NULL          |
|     3 | Xie Yanke     |  53 | NULL | NULL          |
|     4 | Ding Dian     |  32 |    4 | Lin Chaoying  |
|     5 | Yu Yutong     |  26 |    1 | Song Jiang    |
|     6 | Shi Qing      |  46 | NULL | NULL          |
|     7 | Xi Ren        |  19 | NULL | NULL          |
|     8 | Lin Daiyu     |  17 | NULL | NULL          |
|     9 | Ren Yingying  |  20 | NULL | NULL          |
|    10 | Yue Lingshan  |  19 | NULL | NULL          |
|    11 | Yuan Chengzhi |  23 | NULL | NULL          |
|    12 | Wen Qingqing  |  19 | NULL | NULL          |
|    13 | Tian Boguang  |  33 | NULL | NULL          |
|    14 | Lu Wushuang   |  17 | NULL | NULL          |
|    15 | Duan Yu       |  19 | NULL | NULL          |
|    16 | Xu Zhu        |  21 | NULL | NULL          |
|    17 | Lin Chong     |  25 | NULL | NULL          |
|    18 | Hua Rong      |  23 | NULL | NULL          |
|    19 | Xue Baochai   |  18 | NULL | NULL          |
|    20 | Diao Chan     |  19 | NULL | NULL          |
|    34 | xiaom         |  20 | NULL | NULL          |
+-------+---------------+-----+------+---------------+
MariaDB [hellodb]> select stuid,s.name,s.age,tid,t.name from students as s left outer join teachers as t on s.teacherid=t.tid where t.tid is null;    #左外连接扩展
+-------+---------------+-----+------+------+
| stuid | name          | age | tid  | name |
+-------+---------------+-----+------+------+
|     2 | Shi Potian    |  22 | NULL | NULL |
|     3 | Xie Yanke     |  53 | NULL | NULL |
|     6 | Shi Qing      |  46 | NULL | NULL |
|     7 | Xi Ren        |  19 | NULL | NULL |
|     8 | Lin Daiyu     |  17 | NULL | NULL |
|     9 | Ren Yingying  |  20 | NULL | NULL |
|    10 | Yue Lingshan  |  19 | NULL | NULL |
|    11 | Yuan Chengzhi |  23 | NULL | NULL |
|    12 | Wen Qingqing  |  19 | NULL | NULL |
|    13 | Tian Boguang  |  33 | NULL | NULL |
|    14 | Lu Wushuang   |  17 | NULL | NULL |
|    15 | Duan Yu       |  19 | NULL | NULL |
|    16 | Xu Zhu        |  21 | NULL | NULL |
|    17 | Lin Chong     |  25 | NULL | NULL |
|    18 | Hua Rong      |  23 | NULL | NULL |
|    19 | Xue Baochai   |  18 | NULL | NULL |
|    20 | Diao Chan     |  19 | NULL | NULL |
|    34 | xiaom         |  20 | NULL | NULL |
+-------+---------------+-----+------+------+
18 rows in set (0.00 sec)MariaDB [hellodb]> select stuid,s.name,s.age,tid,t.name from students as s left outer join teachers as t on s.teacherid=t.tid where t.tid is not null; 
+-------+-------------+-----+-----+---------------+
| stuid | name        | age | tid | name          |
+-------+-------------+-----+-----+---------------+
|     5 | Yu Yutong   |  26 |   1 | Song Jiang    |
|     1 | Shi Zhongyu |  22 |   3 | Miejue Shitai |
|     4 | Ding Dian   |  32 |   4 | Lin Chaoying  |
+-------+-------------+-----+-----+---------------+

右外连接right outer join

右边显示完全

MariaDB [hellodb]> select stuid,s.name,s.age,tid,t.name from students as s right outer join teachers as t on s.teacherid=t.tid;
+-------+-------------+------+-----+---------------+
| stuid | name        | age  | tid | name          |
+-------+-------------+------+-----+---------------+
|     1 | Shi Zhongyu |   22 |   3 | Miejue Shitai |
|     4 | Ding Dian   |   32 |   4 | Lin Chaoying  |
|     5 | Yu Yutong   |   26 |   1 | Song Jiang    |
|  NULL | NULL        | NULL |   2 | Zhang Sanfeng |
+-------+-------------+------+-----+---------------+

完全外连接union

左外连接union右外连接（两张表都全部显示，没有部分空字符串）

MariaDB [hellodb]> select stuid,s.name,s.age,tid,t.name from students as s left outer join teachers as t on s.teacherid=t.tid-> union-> select stuid,s.name,s.age,tid,t.name from students as s right outer join teachers as t on s.teacherid=t.tid;
+-------+---------------+------+------+---------------+
| stuid | name          | age  | tid  | name          |
+-------+---------------+------+------+---------------+
|     1 | Shi Zhongyu   |   22 |    3 | Miejue Shitai |
|     2 | Shi Potian    |   22 | NULL | NULL          |
|     3 | Xie Yanke     |   53 | NULL | NULL          |
|     4 | Ding Dian     |   32 |    4 | Lin Chaoying  |
|     5 | Yu Yutong     |   26 |    1 | Song Jiang    |
|     6 | Shi Qing      |   46 | NULL | NULL          |
|     7 | Xi Ren        |   19 | NULL | NULL          |
|     8 | Lin Daiyu     |   17 | NULL | NULL          |
|     9 | Ren Yingying  |   20 | NULL | NULL          |
|    10 | Yue Lingshan  |   19 | NULL | NULL          |
|    11 | Yuan Chengzhi |   23 | NULL | NULL          |
|    12 | Wen Qingqing  |   19 | NULL | NULL          |
|    13 | Tian Boguang  |   33 | NULL | NULL          |
|    14 | Lu Wushuang   |   17 | NULL | NULL          |
|    15 | Duan Yu       |   19 | NULL | NULL          |
|    16 | Xu Zhu        |   21 | NULL | NULL          |
|    17 | Lin Chong     |   25 | NULL | NULL          |
|    18 | Hua Rong      |   23 | NULL | NULL          |
|    19 | Xue Baochai   |   18 | NULL | NULL          |
|    20 | Diao Chan     |   19 | NULL | NULL          |
|    34 | xiaom         |   20 | NULL | NULL          |
|  NULL | NULL          | NULL |    2 | Zhang Sanfeng |
+-------+---------------+------+------+---------------+

子查询

MariaDB [hellodb]> select *from students where age>(select avg(age) from students);    #大于平均年龄
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     3 | Xie Yanke    |  53 | M      |       2 |        16 |
|     4 | Ding Dian    |  32 | M      |       4 |         4 |
|     5 | Yu Yutong    |  26 | M      |       3 |         1 |
|     6 | Shi Qing     |  46 | M      |       5 |      NULL |
|    13 | Tian Boguang |  33 | M      |       2 |      NULL |
|    17 | Lin Chong    |  25 | M      |       4 |      NULL |
+-------+--------------+-----+--------+---------+-----------+
MariaDB [hellodb]> update students set age=(select avg(age) from teachers) where stuid=20;  #修改编号20的年龄为平均数
MariaDB [hellodb]> select *from students where stuid=20;
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name      | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
|    20 | Diao Chan |  77 | F      |       7 |      NULL |

自连接

MariaDB [hellodb]> create table emp(id int,name char(20),leaderid int);
MariaDB [hellodb]> insert emp values (1,'mage',null),(2,'zhang',1),(3,'wang',2),(4,'li',3);
MariaDB [hellodb]> select *from emp;
+------+-------+----------+
| id   | name  | leaderid |
+------+-------+----------+
|    1 | mage  |     NULL |
|    2 | zhang |        1 |
|    3 | wang  |        2 |
|    4 | li    |        3 |
+------+-------+----------+
MariaDB [hellodb]> select e.name,l.name leadername from emp as e left outer join emp as l on e.leaderid=l.id;     #as可省略
+-------+------------+
| name  | leadername |
+-------+------------+
| zhang | mage       |
| wang  | zhang      |
| li    | wang       |
| mage  | NULL       |
+-------+------------+MariaDB [hellodb]> select e.name,l.name leadername from emp e left outer join emp l on e.leaderid=l.id;
+-------+------------+
| name  | leadername |
+-------+------------+
| zhang | mage       |
| wang  | zhang      |
| li    | wang       |
| mage  | NULL       |
+-------+------------+

三表查询

MariaDB [hellodb]> select st.name,co.course,sc.score from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid;
+-------------+----------------+-------+
| name        | course         | score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian |    77 |
| Shi Zhongyu | Weituo Zhang   |    93 |
| Shi Potian  | Kuihua Baodian |    47 |
| Shi Potian  | Daiyu Zanghua  |    97 |
| Xie Yanke   | Kuihua Baodian |    88 |
| Xie Yanke   | Weituo Zhang   |    75 |
| Ding Dian   | Daiyu Zanghua  |    71 |
| Ding Dian   | Kuihua Baodian |    89 |
| Yu Yutong   | Hamo Gong      |    39 |
| Yu Yutong   | Dagou Bangfa   |    63 |
| Shi Qing    | Hamo Gong      |    96 |
| Xi Ren      | Hamo Gong      |    86 |
| Xi Ren      | Dagou Bangfa   |    83 |
| Lin Daiyu   | Taiji Quan     |    57 |
| Lin Daiyu   | Jinshe Jianfa  |    93 |
+-------------+----------------+-------+

视图view

虚拟表格，相当于命令的别名（工作中不使用）

MariaDB [hellodb]> create view view_test as (select st.name,co.course,sc.score from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid);
Query OK, 0 rows affected (0.00 sec)MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| emp               |
| scores            |
| students          |
| teachers          |
| toc               |
| users             |
| view_test         |
+-------------------+
10 rows in set (0.01 sec)MariaDB [hellodb]> select * from view_test;
+-------------+----------------+-------+
| name        | course         | score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian |    77 |
| Shi Zhongyu | Weituo Zhang   |    93 |
| Shi Potian  | Kuihua Baodian |    47 |
| Shi Potian  | Daiyu Zanghua  |    97 |
| Xie Yanke   | Kuihua Baodian |    88 |
| Xie Yanke   | Weituo Zhang   |    75 |
| Ding Dian   | Daiyu Zanghua  |    71 |
| Ding Dian   | Kuihua Baodian |    89 |
| Yu Yutong   | Hamo Gong      |    39 |
| Yu Yutong   | Dagou Bangfa   |    63 |
| Shi Qing    | Hamo Gong      |    96 |
| Xi Ren      | Hamo Gong      |    86 |
| Xi Ren      | Dagou Bangfa   |    83 |
| Lin Daiyu   | Taiji Quan     |    57 |
| Lin Daiyu   | Jinshe Jianfa  |    93 |
+-------------+----------------+-------+
MariaDB [hellodb]> show table status like 'view_test'\G     #查询表格状态Comment: VIEW
MariaDB [hellodb]> create view view_oldstudents as select *from students where age>30;   #创建
MariaDB [hellodb]> select *from view_oldstudents;    #查询
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     3 | Xie Yanke    |  53 | M      |       2 |        16 |
|     4 | Ding Dian    |  32 | M      |       4 |         4 |
|     6 | Shi Qing     |  46 | M      |       5 |      NULL |
|    13 | Tian Boguang |  33 | M      |       2 |      NULL |
|    20 | Diao Chan    |  77 | F      |       7 |      NULL |
+-------+--------------+-----+--------+---------+-----------+
MariaDB [hellodb]> insert view_oldstudents values(25,'xiaoming',40,'F',3,null);   #插入
MariaDB [hellodb]> drop view view_test;   #删除视图##使用视图插入时，大于30的查询能查到，小于30的查不到（实际已经写入），会给用户造成困惑，因此工作中不用

自定义函数

系统函数和自定义函数
在mysql.proc中

MariaDB [mysql]> CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "hello world";   #创建
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> select simpleFun();    #调用
+-------------+
| simpleFun() |
+-------------+
| hello world |
+-------------+
1 row in set (0.00 sec)
MariaDB [mysql]> show function status;    #查看函数列表
MariaDB [mysql]> SHOW CREATE FUNCTION simpleFun;     #查看函数定义
MariaDB [mysql]> DROP FUNCTION simpleFun;       #删除函数
Query OK, 0 rows affected (0.00 sec)

存储过程

流程控制

触发器

用户管理

权限管理

存储引擎

MyISAM引擎特点
不支持事务
表级锁定
读写相互阻塞，写入不能读，读时不能写
只缓存索引
不支持外键约束
不支持聚簇索引
读取数据较快，占用资源较少
不支持MVCC（多版本并发控制机制）高并发
崩溃恢复性较差
MySQL5.5.5前默认的数据库引擎

MyISAM存储引擎适用场景
只读（或者写较少）、表较小（可以接受长时间进行修复操作）
MyISAM引擎文件
tbl_name.frm 表格式定义
tbl_name.MYD 数据文件
tbl_name.MYI 索引文件

InnoDB引擎特点
行级锁
支持事务，适合处理大量短期事务
读写阻塞与事务隔离级别相关
可缓存数据和索引
支持聚簇索引
崩溃恢复性更好
支持MVCC高并发
从MySQL5.5后支持全文索引
从MySQL5.5.5开始为默认的数据库引擎

InnoDB数据库文件
所有InnoDB表的数据和索引放置于同一个表空间中
表空间文件：datadir定义的目录下
数据文件：ibddata1, ibddata2, …
每个表单独使用一个表空间存储表的数据和索引
启用：innodb_file_per_table=ON（5.5后默认为on）
两类文件放在数据库独立目录中
数据文件(存储数据和索引)：tb_name.ibd
表格式定义：tb_name.frm

MySQL中的系统数据库

performance_schema数据库
MySQL 5.5开始新增的数据库，主要用于收集数据库服务器性能参数,库里表的存储引擎均为PERFORMANCE_SCHEMA，用户不能创建存储引擎为PERFORMANCE_SCHEMA的表
information_schema数据库
MySQL 5.0之后产生的，一个虚拟数据库，物理上并不存在information_schema数据库类似与“数据字典”，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式）

服务器配置

获取mysqld的可用选项列表：
mysqld --help --verbose
mysqld --print-defaults 获取默认设置
设置服务器选项方法：
在命令行中设置
shell> ./mysqld_safe --skip-name-resolve=1
在配置文件my.cnf中设置
skip_name_resolve=1
服务器系统变量：分全局和会话两种
获取系统变量
mysql> SHOW GLOBAL VARIABLES;
mysql> SHOW [SESSION] VARIABLES;
mysql> SELECT @@VARIABLES;
修改服务器变量的值：
mysql> help SET
修改全局变量：仅对修改后新创建的会话有效；对已经建立的会话无效
mysql> SET GLOBAL system_var_name=value;
mysql> SET @@global.system_var_name=value;
修改会话变量：
mysql> SET [SESSION] system_var_name=value;
mysql> SET @@[session.]system_var_name=value;

缓存