MySQL学习笔记之三：SQL的用法

hc6538

　　一、mysql中的字符大小写问题：
　　①SQL关键字及函数名不区字符大小写，但是，为了保证缓存命中率，宜遵循同一种风格
　　②数据库、表、索引及视图的名称是否区分大小写取决于底层的OS及FS；
　　③存储过程、存储函数及事件调度器不区分字符大小写；但触发器区分；
　　④表别名不区分大小写；
　　⑤字段中字符数据，类型为binary、blog、varbinary时区分大小写；其它的不区分；
　　二、SQL
　　SQL(Structured Query Language,结构化查询语言)是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统
　　1、SQL语言有如下几个部分组成：
　　①数据定义语言：DDL；定义关系，修改关系，删除关系
　　完整性（intigrity）：定义完整性约束的命令，隶属于DDL
　　视图定义：管理视图view(虚表);
　　授权：定义对表或视图的访问权限；
　　DDL:CREATE, DROP,>
　　②数据操纵语言：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:
　　[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>　　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数据备份或迁移的场景中
　　改表名：

　　>　　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';
　　▲示例：
　　新建如下表（包括结构和内容）：

　　>　　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'>

　　例：create user 'testuser'@'192.168.30.%'>　　⑶删除用户账号：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,>　　REPLICATION SLAVE   主从架构中实现从服务器向主服务器获取日志数据的权限
　　REPLICATION CLIENT  向主服务器发起连接请求获取其状态信息的权限
　　②库和表级别：

　　>
　　>　　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.%'>　　⑼回收权限：
　　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>
　　update students set>　　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>　　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 =>　　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
　　说明：

　　>　　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 tb_name ADD INDEX [index_name] [index_type] (index_col_name,...) [index_option] ...;
　　⑼显示表上的索引：
　　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 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 ''];