一、mysql中的字符大小写问题：
①SQL关键字及函数名不区字符大小写，但是，为了保证缓存命中率，宜遵循同一种风格
②数据库、表、索引及视图的名称是否区分大小写取决于底层的OS及FS；
③存储过程、存储函数及事件调度器不区分字符大小写；但触发器区分；
④表别名不区分大小写；
⑤字段中字符数据，类型为binary、blog、varbinary时区分大小写；其它的不区分；
二、SQL
SQL(Structured Query Language,结构化查询语言)是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统
1、SQL语言有如下几个部分组成：
①数据定义语言：DDL；定义关系，修改关系，删除关系
完整性（intigrity）：定义完整性约束的命令，隶属于DDL
视图定义：管理视图view(虚表);
授权：定义对表或视图的访问权限；
DDL: CREATE, DROP, ALTER, GRANT, REVOKE
②数据操纵语言：DML；插入行、修改行、删除行
DML: INSERT, DELETE, UPDATE, SELECT
③事务控制：创建事务和结束事务
2、约束：
主键(primary key)：属于惟一键，但其值不能为空，一张表只能有一个主键
惟一键(unique key)
外键(foreign key)：引用性约束
检查性约束
3、SQL的数据定义：
创建关系，即创建表：
每个关系模式；
每个属性的取值类型；
完整性约束；
索引的集合；
表的安全性和权限信息；
表的物理存储结构：因存储引擎不同而不同
MyISAM：.frm, .MYD（数据）, .MYI（索引）
InnoDB：.frm, .ibd(数据+索引)
4、SQL数据的类型：
①字符型：（以下数字为可表示的最大字符数）
CHAR(255，固定长度，不区分大小写)、VARCARH(65535，可变长度)
TINYTEXT(255)、TEXT(65535，以文本格式存储)、MEDIUMTEXT(2^24)、LONGTEXT(2^32)
BINARY(255，区分大小写)、VARBINARY(65535)
TINYBLOB、BLOB(以二进制格式存储)、MEDIUMBLOG、LONGBLOB
修饰符：
NULL(可为空)、NOT NULL(不能为空)
DEFAULT 'string' #不适用于BLOB类型
CHARACTER SET 'set' #指定字符集，SHOW CHARACTER SET;
COLLATION 'collation' #指定排序规则，SHOW COLLATION;
查找字符时可使用通配符：
%：匹配任意长度的任意字符
_：匹配任意单个字符；
②数值型：
整型：
TINYINT(1Byte)、SMALLINT(2Bytes)、MEDIUMINT(3Bytes)、INT(4Bytes)、BININT
修饰符：
NULL、NOT NULL
DEFAULT #
UNSIGNED #无符号
AUTO_INCREMENT #自动增长
特殊要求：非空，且必须是主键或唯一键
符点型：
FLOAT、DOUBLE、REAL、BIT
修饰符：NULL、NOT NULL、DEFAULT #、UNSIGNED
③布尔型：没有专用布尔型，其是TINYINT(1)的别名；
④日期时间型：
DATE(3Bytes)、TIME(3Bytes)、DATETIME(8Bytes)、TIMESTAMP(4Bytes)
YEAR(2)：1Byte、YEAR(4)：1Byte
修饰符：NULL、NOT NULL、DEFAULT value
⑤内置类型：
ENUM：枚举，表示仅能从给出的选项选择其中一个
ENUM('string1','string2')
SET：集合, 表示能使用给出的元素组合成的字符串
SET('a','b','c')
修饰符：NULL、NOT NULL、DEFAULT 'string'
△另外，还有定义主键或唯一键的修饰符：PRIMARY KEY、UNIQUE KEY
选择数据类型应遵循以下几种法则：
越小越好，节约空间
越简单越好，能存储为数值就不要存储为字符
能用定长就不要使用变长，char在数据插入、比较、检索上的效率要高于varchar，varchar适用于最长串与最短串相差较大的场景
尽量避免NULL值；
5、MySQL sql_mode：sql模式，用来限定mysqld的工作特性，默认为空模式。
在服务器变量sql_mode中定义，可取值主要有：
TRADITIONAL
STRICT_TRANS_TABLES：对支持事务的表使用严格模式
STRICT_ALL_TABLES：对所有表使用严格模式
三、DDL
1、库管理
⑴创建库：
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...;
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
[DEFAULT] COLLATE [=] collation_name
说明：IF NOT EXISTS 的作用在于当欲创建的库已存在时，不阻止语句的执行，常用于脚本中
⑵删除库：DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;
⑶修改库：
ALTER {DATABASE | SCHEMA} [db_name] alter_specification ...;
alter_specification:
[DEFAULT] CHARACTER SET [=] charset_name
[DEFAULT] COLLATE [=] collation_name
ALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY NAME; #升级数据字典
③查看有哪些库：SHOW DATABASES;
④设置默认库：USE DB_NAME
[root@node1 ~]# mysql ... MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.24 sec) MariaDB [(none)]> create database testdb; #创建数据库 Query OK, 1 row affected (0.17 sec) MariaDB [(none)]> create database testdb; #欲创建的库已存在，报错 ERROR 1007 (HY000): Can't create database 'testdb'; database exists MariaDB [(none)]> create database if not exists testdb; #添加if not exists后不报错 Query OK, 1 row affected, 1 warning (0.00 sec) MariaDB [(none)]> show warnings\G #可显示上一条语句给出的警告信息 *************************** 1. row *************************** Level: Note Code: 1007 Message: Can't create database 'testdb'; database exists 1 row in set (0.00 sec)
2、表管理
⑴创建表：
CREATE TABLE [IF NOT EXISTS] [db_name.]tb_name (col1_def, col2_def, PRIMARY KEY(col_name,...), UNIQUE (col1,...), INDEX (col1,...)) [table_options];
table_option:
ENGINE [=] engine_name
COMMENT [=] 'string'
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] select_statement; #复制表数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name LIKE old_tb_name; #复制表结构
定义主键或唯一键的两种方式：
当作字段修饰符；
如 create table tb2 (Name char(30) primary key,Gender enum('m','f'));
当作特殊的字段；
如 create table tb2 (Name char(30),Gender enum('m','f'),primary key(Name,Gender));
⑵删除表：
DROP [TEMPORARY] TABLE [IF EXISTS] tb_name [, tb_name]... [RESTRICT | CASCADE];
CASCADE实现级连效果，即将依赖此表的其它表也一并删除
⑶修改表：
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tb_name
ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]; #添加一个字段，FIRST表示添加为第一个字段
例：alter table tb5 add ID int unsigned not null auto_increment primary key; #unsigned要位于not null之前
ADD [COLUMN] (col_name column_definition,...); #添加多个字段
ADD PRIMARY KEY [index_type] (index_col_name,...); #添加主键
DROP PRIMARY KEY;
DROP [COLUMN] col_name; #删除某个字段
CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]; #修改字段名和字段定义
MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]; #修改字段定义
例：alter table wuxia modify Age tinyint after Gender;
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]; #修改字符集和排序规则
ENGINE = 'engine_name'; #不宜随便修改，特别是对于很大的表
AUTO_INCREMENT = num 修改auto_increment的起始值
COMMENT = 'string'; #修改注释信息
InnoDB表的表空间管理（前提：每表使用独立表空间）：
DISCARD TABLESPACE; #删除表空间，即删除表的数据和索引，只留下表结构定义
IMPORT TABLESPACE;
以上两项常用于mysql数据备份或迁移的场景中
改表名：
ALTER TABLE tb_name RENAME TO new_tb_name;
RENAME TABLE old_name TO new_name;
⑷查看表结构：DESC tb_name;
⑸查看表的状态信息：SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr];
例：show table status like 'students';
▲示例：
新建如下表（包括结构和内容）：
ID Name Age Gender Course
1 Ling Huchong 27 Male Pixie Jianfa
2 Zhou Zhiruo 25 Female Jiuyin Zhenjin
3 Qiao Feng 30 Male Xianglong Shiba Zhang
①新增Class放置于Name字段后；
②将ID字段名称修改为TID;
③将Age字段放置最后；
MariaDB [(none)]> use testdb #设置其为默认库
Database changed
MariaDB [testdb]> create table wuxia ( #创建表
-> ID tinyint unsigned not null auto_increment primary key,
-> Name char(20) not null,
-> Age tinyint,
-> Gender enum('f','m') not null,
-> Course char(30)
-> );
Query OK, 0 rows affected (1.10 sec)
MariaDB [(none)]> desc testdb.wuxia; #查看表定义
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| ID | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| Name | char(20) | NO | | NULL | |
| Age | tinyint(4) | YES | | NULL | |
| Gender | enum('f','m') | NO | | NULL | |
| Course | char(30) | YES | | NULL | |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set (0.28 sec)
MariaDB [testdb]> alter table wuxia add Class char(20) after Name; #给表添加一个字段
Query OK, 0 rows affected (1.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [testdb]> alter table wuxia change ID TID tinyint unsigned not null auto_increment primary key;
ERROR 1068 (42000): Multiple primary key defined
MariaDB [testdb]> alter table wuxia change ID TID tinyint unsigned not null auto_increment;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [testdb]> alter table wuxia drop Course;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [testdb]> alter table wuxia modify Age tinyint after Gender;
Query OK, 0 rows affected (0.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [testdb]> desc wuxia; #查看表定义
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| TID | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| Name | char(20) | NO | | NULL | |
| Class | char(20) | YES | | NULL | |
| Gender | enum('f','m') | NO | | NULL | |
| Age | tinyint(4) | YES | | NULL | |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
MariaDB [testdb]> insert wuxia (Name,Class,Gender,Age) values #批量插入数据
-> ('Ling Huchong','Huashanpai','m',27),
-> ('Zhou Zhiruo','Emeipai','f',25),
-> ('Qiao Feng','Gaibang','m',30);
Query OK, 3 rows affected (0.18 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [testdb]> select * from wuxia;
+-----+--------------+------------+--------+------+
| TID | Name | Class | Gender | Age |
+-----+--------------+------------+--------+------+
| 1 | Ling Huchong | Huashanpai | m | 27 |
| 2 | Zhou Zhiruo | Emeipai | f | 25 |
| 3 | Qiao Feng | Gaibang | m | 30 |
+-----+--------------+------------+--------+------+
3 rows in set (0.01 sec)
MariaDB [testdb]> show table status like 'wuxia'\G #查看表状态
*************************** 1. row ***************************
Name: wuxia
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 3
Avg_row_length: 5461
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 4
Create_time: 2016-03-01 12:09:11
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
3、用户和权限管理
⑴mysql账号：user@host，表示允许此user从host范围内的某主机登录
host中可使用通配符%和_，例如 172.16.0.0/16 可表示为 172.16.%.%
⑵创建用户账号：CREATE USER 'username'@'host' IDENTIFIED BY 'your_password', ...;
例：create user 'testuser'@'192.168.30.%' identified by 'magedu';
⑶删除用户账号：DROP USER 'username'@'host';
⑷用户重命名：RENAME USER old_user TO new_user;
例：RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1';
⑸查看用户获得的权限：SHOW GRANTS FOR 'username'@'host';
⑹MySQL的权限类别：库级别，表级别，字段级别，管理类，程序类
①管理类：
CREATE USER, FILE, SHOW DATABASES, SHUTDOWN, LOCK TABLES, PROCESS
CREATE TEMPORARY TABLES 创建临时表会使用memory存储引擎，从而占据更多的内存空间
SUPER #不便归类的管理类权限
RELOAD #执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限
REPLICATION SLAVE 主从架构中实现从服务器向主服务器获取日志数据的权限
REPLICATION CLIENT 向主服务器发起连接请求获取其状态信息的权限
②库和表级别：
ALTER, CREATE, CREATE ROUTINE, CREATE VIEW, DROP, EXECUTE
ALTER ROUTINE #修改存储例程的权限
storage routine(存储例程)包括：
storage procedure
storage function
INDEX #创建和删除索引的权限
GRANT OPTION #是否可以转授权限的权限；
SHOW VIEW show create view stus;
③数据操作（表级别）：
SELECT, INSERT, UPDATE, DELETE
④字段级别：
SELECT(col1,...), UPDATE(col1,...), INSERT(col1,...)
⑤所有权限：
ALL [PRIVILEGES]
⑺mysql中的授权相关的表：mysql库中
db、host、user
columns_priv、tables_priv, procs_priv
⑻授权：
GRANT priv1, priv2, ... ON [TABLE|FUNCTION|PROCEDURE] db_name.tb_name|routine TO 'username'@'host' [IDENTIFIED BY 'password'] [REQUIRE ssl_option] [WITH with_option]
with_option:
GRANT OPTION #将获得的权限转赠给他人的权限
MAX_QUERIES_PER_HOUR count
MAX_UPDATES_PER_HOUR count
MAX_CONNECTIONS_PER_HOUR count
MAX_USER_CONNECTIONS count
说明：授权的时候可直接创建用户，不需事先用create命令创建
可指定ssl相关选项要求客户端使用ssl建立连接
刷新授权表，以使得权限立即生效：FLUSH PRIVILEGES;
若多次授权，后面的权限并不会覆盖之前的权限，而会合并
例：grant create,drop,index on testdb.* to 'testuser'@'192.168.30.%' identified by 'magedu';
⑼回收权限：
REVOKE priv1,priv2,... ON TABLE|FUNCTION|PROCEDURE] db_name.tb_name|routine FROM 'username'@'host',...; #可一次收回多个账号的权限
▲示例：
①授予testuser能够通过192.168.30.0/24网络内的任意主机访问当前mysql服务器的权限；
②让此用户能够创建及删除testdb数据库，及库中的表；
③让此用户能够在testdb库中的wuxia表中执行查询、删除、更新和插入操作；
④让此用户能够在testdb库上执行创建和删除索引；
⑤让此用户能够在testdb.students表上查询id和name字段，并允许其将此权限转授予其他用户；
MariaDB [testdb]> grant create,drop,index on testdb.* to 'testuser'@'192.168.30.%' identified by 'magedu'; Query OK, 0 rows affected (0.22 sec) MariaDB [testdb]> grant select,delete,update,insert on testdb.wuxia to 'testuser'@'192.168.30.%'; Query OK, 0 rows affected (0.08 sec) MariaDB [testdb]> grant select(ID,Name) on testdb.students to 'testuser'@'192.168.30.%' with grant option; Query OK, 0 rows affected (0.08 sec) MariaDB [testdb]> flush privileges; Query OK, 0 rows affected (0.06 sec) MariaDB [testdb]> show grants for 'testuser'@'192.168.30.%'; +--------------------------------------------------------------------------------------------------------------------+ | Grants for testuser@192.168.30.% | +--------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'testuser'@'192.168.30.%' IDENTIFIED BY PASSWORD '*6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664' | | GRANT CREATE, DROP, INDEX ON `testdb`.* TO 'testuser'@'192.168.30.%' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `testdb`.`wuxia` TO 'testuser'@'192.168.30.%' | | GRANT SELECT (Name, ID) ON `testdb`.`students` TO 'testuser'@'192.168.30.%' WITH GRANT OPTION | +--------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.01 sec)
四、DML
1、插入数据
INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} (...),...;
#如果省略字段名，则表示向每个字段都插入数据；建议批量插入，避免索引频繁更新
例：insert into students (Name,Age,Gender,Class) values ('jerry',43,'m','class 2'),('Ou Yangfeng',77,'m','Hamopai');
INSERT [INTO] tbl_name SET col_name={expr | DEFAULT}, ...;
例：insert into tb5 set Name='yue buqun',Gender='m';
INSERT [INTO] tbl_name [(col_name,...)] SELECT ...;
例：insert into temp2 (fld_id) select fld_order_id from temp1 where fld_order_id > 100;
2、更新数据
UPDATE table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count];
示例：update students set Age=70 where ClassID is null;
update students set ClassID=3 order by Age limit 3;
3、删除数据
DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count];
例：delete from students where Gender = 'f';
4、查询数据
⑴SELECT values_to_display FROM table_name
WHERE expression
GROUP BY how_to_group #分组
HAVING expression #对聚合运算的结果进行过滤
ORDER BY how_to_sort
LIMIT row_count;
说明：
WHERE expression：比较表达式，内置函数；
特殊的比较操作符：
BETWEEN... AND ...
IN(list)：指定的字段取的取值在此list中，则表示符合条件；
IS NULL：判断某字段值为空
IS NOT NULL
LIKE：可使用通配符，%, _
RLIKE或REGEXP：可使用正则表达式的模式
逻辑操作符：AND, OR, NOT
GROUP BY：表示根据指定的字段，对符合条件的行做分组；可对每组分别做聚合计算；
聚合计算：AVG(), SUM(), MAX(), MIN(), COUNT()
HAVING：对聚合计算的结果做过滤；
ORDER BY col1[,...] {ASC|DESC} #ASC表示升序，DESC表示降序
LIMIT [offset,]count #offset表示偏移量
示例：
select Name as Pupil,Age from students; #字段和表都可以使用as取别名
select distinct Age from students; #distinct表示对于相同的结果只显示一次
select ClassID,avg(Age) from students group by ClassId having avg(Age) > 25;
select Name,Age from students order by Age desc limit 4,3; #略过前4个后显示3个
⑵select语句的执行顺序：
FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY --> SELECT --> LIMIT
选择->分组->对聚合计算的结果做过滤->排序->投影->截取指定数目的行
⑶连接查询：事先将两张或多张表执行相应的join操作，而后根据join结果做查询；
交叉连接
内连接：
等值连接，不等值连接，自然连接
外连接：
左外连接：以左表为基准，右表对应在数据不存在则留空
右外连接
示例：
select students.Name,classes.Class from students,classes where students.ClassID = classes.ClassID; #等值连接
select s.Name as student,t.Name as teacher from students as s left join teachers as t on s.TeacherID = t.TID; #左外连接
⑷子查询：查询中嵌套着查询；msyql的子查询性能不好，尽量不要使用
用于WHERE子句的子查询：
①用于比较表达式中的子查询，要求子查询只能返回单个结果（IN除外）；
select Name,Age from students where Age > (select avg(Age) from students);
②用于IN中的子查询，判断是否存在于指定的列表中
select Name from students where StuID in (select TID from teachers);
③用于EXISTS中子查询
用于FROM中的子查询：
SELECT alias.col,... FROM (SELECT statement) AS alias WHERE clause; #必须给子表取个别名
例：select s.Name from (select * from students where Age > 20) as s where s.Name like 's%';
⑸联合查询：将两个或多个返回值字段相同的查询的结果合并输出；
SELECT statement UNION SELECT statement;
例：select Name,Age from teachers where Age >= 40 union select Name,Age from students where Age >= 40;
▲示例：
导入hellodb.sql，完成以下题目：
①对于students表，以Gender分组，显示各组中年龄大于19的学员的年龄之和
②显示前5位同学的姓名、课程及成绩；
③求前6位同学每位同学自己所有课程的平均成绩，并按降序排列；
④显示每门课程课程名称及学习了这门课的同学的个数；
MariaDB [(none)]> source hellodb.sql #
Query OK, 0 rows affected (0.05 sec)
...
MariaDB [hellodb]> use hellodb
Database changed
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.05 sec)
MariaDB [hellodb]> desc students;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
| TeacherID | int(10) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.13 sec)
MariaDB [hellodb]> desc courses;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| CourseID | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| Course | varchar(100) | NO | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
MariaDB [hellodb]> desc scores;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| ID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| StuID | int(10) unsigned | NO | | NULL | |
| CourseID | smallint(5) unsigned | NO | | NULL | |
| Score | tinyint(3) unsigned | YES | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> select sum(Age) from students where Age > 19 group by Gender;
+----------+
| sum(Age) |
+----------+
| 62 |
| 476 |
+----------+
2 rows in set (0.00 sec)
MariaDB [hellodb]> select prestu.Name,courses.Course,scores.Score from
-> (select Name from students limit 5) as prestu,students,courses,scores
-> where prestu.Name = students.Name and students.StuID = scores.StuID and scores.CourseID = courses.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 |
+-------------+----------------+-------+
10 rows in set (0.13 sec)
MariaDB [hellodb]> select students.Name,avg(scores.Score) from students,courses,scores
-> where students.StuID = scores.StuID and scores.CourseID = courses.CourseID
-> group by students.Name order by avg(scores.Score) desc limit 6;
+-------------+-------------------+
| Name | avg(scores.Score) |
+-------------+-------------------+
| Shi Qing | 96.0000 |
| Shi Zhongyu | 85.0000 |
| Xi Ren | 84.5000 |
| Xie Yanke | 81.5000 |
| Ding Dian | 80.0000 |
| Lin Daiyu | 75.0000 |
+-------------+-------------------+
6 rows in set (0.61 sec)
MariaDB [hellodb]> select courses.Course,count(courses.Course) from students,courses,scores
-> where students.StuID = scores.StuID and scores.CourseID = courses.CourseID
-> group by courses.Course;
+----------------+-----------------------+
| Course | count(courses.Course) |
+----------------+-----------------------+
| Dagou Bangfa | 2 |
| Daiyu Zanghua | 2 |
| Hamo Gong | 3 |
| Jinshe Jianfa | 1 |
| Kuihua Baodian | 4 |
| Taiji Quan | 1 |
| Weituo Zhang | 2 |
+----------------+-----------------------+
7 rows in set (0.00 sec)
五、视图(view)
视图是存储下来的SELECT语句，是虚表
虚表：视图
基表：视图中的查询语句针对其进行查询的表
⑴创建视图：CREATE VIEW view_name AS SELECT statement;
例：create view stus as select Stuid,Name,Age,Gender from students;
⑵删除视图：DROP VIEW view_name;
⑶可以将视图当作普通表那样执行插入、删除或更新操作，基表上的数据也会随之改变。
六、EXPLAIN：获取关于查询执行计划的信息
EXPLAIN [explain_type] SELECT select_options;
例：explain select Name,Age from students where Name like 's%' or Age > 28\G
id: 1
select_type: SIMPLE
table: students
type: index_merge
possible_keys: Name,Age
key: Name,Age
key_len: 152,1
ref: NULL
rows: 7
Extra: Using sort_union(Name,Age); Using where
说明：
id：SELECT语句的标识符，表示对应的SELECT语句在原始语句中的位置。如果原始语句只有一个SELECT语句，其id为1；在子查询中，主查询id为1，内层查询从2开始依次编号；在联合查询中，最后一个id为null；
select_type：查询类型
SIMPLE
PRIMARY：联合查询中的第一个查询
UNION：联合查询中相对于第一个查询而言的后续的查询
UNION RESULT：UNION的执行结果
SUBQUERY
DERIVED：FROM子句中的子查询
table：查询语句所关系到的表的名字；
type：访问到目标记录的方法
system：表中仅有一行；
const：表中至多有一行匹配；一般只有用于PRIMARY KEY或UNIQUE KEY（NOT NULL）索引时，此种结果才会出现；
eq_ref：使用的索引是唯一索引，表中至多有一行匹配
ref：[用到的索引不是主键或唯一健，即等值比较返回的结果可能不只一个]
fulltext
ref_or_null
index_merge: 索引合并优化
unique_subquery：通常出现于IN子查询中
index_subquery：类似上一个
range：带有范围限制的索引
index：全索引扫描
ALL：全表扫描
rows：加载到内存中的行数
Extra:
using where：从存储引擎返回的结果不是最终结果，还需二次过滤
using index：从索引中就能得到最终结果，即覆盖索引
using index for group by
using filesort：使用外部索引排序
七、索引
索引是特殊的数据结构，用于快速找到数据记录
⑴索引类型：B-Tree (B+ Tree) (左前缀), hash (key-value), R-Tree, FullText
⑵索引的优点：
大大减少服务器需要扫描的数据量；
索引可以帮助服务器尽量避免进行排序及使用临时表；
索引可以将随机I/O转换为顺序I/O；
⑶索引可以创建在一个字段，也可是多个字段上：简单索引、组合索引
示例：索引(姓名,性别)
where name like 'tom%'
where gender = 'female' #此用法使用不上索引
where familyname = 'tom' and gender = 'female'
⑷键可作索引，但并非所有的索引都是键
⑸B-Tree索引的适用场景:
全值匹配：使用比较操作符 =, <=>
左前缀匹配：LIKE 'tom%'
列前缀匹配
匹配范围值：
组合索引类型中，精确匹配前一列，并范围匹配后一列；
只访问索引的查询：覆盖索引，即从索引就可以直接得到最终结果；
⑹哈希索引适用场景：
哈希索引只包含哈希值和行指针；不是按照索引值顺序存储，无法用于排序；不支持部分索引匹配查找；
哈希索引只支持等值比较查询，包含 =, IN(), <=>
⑺聚簇索引和非聚簇索引：
聚簇索引：索引和数据一起存放；
数据文件：索引顺序文件
非聚簇索引：索引和数据分开存放，而数据记录未必顺序存放；但索引数据一般顺序存放；
数据文件：堆文件
⑻添加索引：
在创建表的同时创建索引
CREATE TABLE tb_name (col1_def,...INDEX [index_name] [index_type] (index_col_name,...) [index_option] ...);
index_type: USING {BTREE | HASH} #默认是BTREE
给已存在的表添加索引
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...) [index_option] ...;
例：create index na_ge on testdb.students (Name,Gender);
ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (索引字段名)[ASC|DESC];
⑼显示表上的索引：
SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr];
⑽删除索引
DROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name;
ALTER TABLE tb_name DROP INDEX index_name;
⑾索引创建的基本法则：基于搜索键来创建，SELECT的WHERE子句的查询条件中的字段；
⑿索引的使用策略：
①要使用独立的列：索引列不是表达式的一部分；
SELECT Name FROM students WHERE Age + 2 > 32; #错误的使用方式
②前缀索引
索引选择性：单独的索引项与数据表中的记录的总数的比值；取值范围：0<1/n≤1
③对于多列索引，使用多个简单索引还是一个组合索引：
查询条件多用AND，则建议使用组合索引；
查询条件多用OR，建议使用多个简单索引；
④合适的次序：将选择性高的列写在最左侧；例如(StuID, Gender)
⑤使用聚簇索引：
优点：数据访问更快；
缺点：更新聚簇索引列的代价很高；
⑥使用覆盖索引
SELECT Name FROM students WHERE Name LIKE'tom%';
⑧避免冗余索引，例如name, (name,gender)，这样会导致索引更新代价增大
MariaDB [hellodb]> show indexes in testdb.students; #查看表上的索引 +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | students | 0 | ID | 1 | ID | A | 0 | NULL | NULL | | BTREE | | | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.07 sec) MariaDB [hellodb]> create index na_ge on testdb.students (Name,Gender); #创建一个组合索引 Query OK, 0 rows affected (1.30 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [hellodb]> show indexes in testdb.students; +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | students | 0 | ID | 1 | ID | A | 0 | NULL | NULL | | BTREE | | | | students | 1 | na_ge | 1 | Name | A | 0 | NULL | NULL | | BTREE | | | | students | 1 | na_ge | 2 | Gender | A | 0 | NULL | NULL | | BTREE | | | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec) MariaDB [hellodb]> drop index na_ge on testdb.students; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0
八、几个常用的SHOW命令
①查看MariaDB支持哪此存储引擎：SHOW ENGINES;
②查看表的属性信息：SHOW TABLE STATUS [LIKE 'tb_name'];
③查看支持的字符集：SHOW CHARACTER SET;
④查看排序规则：SHOW COLLATION;
⑤查看MySQL的服务器变量或状态变量：SHOW {GLOBAL|SESSION} VARIALES [LIKE ''];
⑥查看MySQL的状态变量：SHOW {GLOBAL|SESSION} STATUS [LIKE ''];
转载于:https://blog.51cto.com/9124573/1749785
扫一扫
861
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
