三、管理数据库和表

关系型数据库的常见组件

• 数据库：database
• 表：table
    行：row
    列：column
• 索引：index
• 视图：view
• 用户：user
• 权限：privilege
• 存储过程：procedure，无返回值
• 存储函数：function，有返回值
• 触发器：trigger
• 事件调度器：event scheduler，任务计划

SQL语言的兴起于语法标准

• 20世纪70年代，IBM开发出SQL，用于DB2
• 1981年，IBM推出SQL/DS数据库
• 业内标准微软和Sybase的T-SQL，Oracle的PL/SQL
• SQL作为关系型数据库所使用的标准语言，最初是基于IBM的实现在1986年被批准的。1987年，“国际标准化组织(ISO)”把ANSI(美国国家标准化组织) SQL作为国际标准。
• SQL：ANSI SQL
  SQL-1986, SQL-1989, SQL-1992, SQL-1999, SQL-2003 , SQL-2008，SQL-2011

SQL语言规范

• 在数据库系统中，SQL语句不区分大小写(建议用大写)
• SQL语句可单行或多行书写，以“;”结尾
• 关键词不能跨多行或简写
• 用空格和缩进来提高语句的可读性
• 子句通常位于独立行，便于编辑，提高可读性
• 注释：
    SQL标准：
      /*注释内容*/ 多行注释
      --注释内容单行注释，注意有空格
    MySQL注释：
      #

数据库对象

• 数据库的组件(对象)：
    数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等
• 命名规则：
    必须以字母开头
    可包括数字和三个特殊字符（# _ $）
    不要使用MySQL的保留字
    同一database(Schema)下的对象不能同名

SQL语句分类

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

SQL语句构成

• SQL语句构成：
  Keyword组成clause
  多条clause组成语句

示例：

SELECT *           SELECT子句
FROM products      FROM子句
WHERE price>400    WHERE子句

说明：一组SQL语句，由三个子句构成，SELECT,FROM和WHERE是关键字

数据库操作

• 创建数据库：
    CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME';
    CHARACTER SET 'character set name' 定义子句的字符集
    COLLATE 'collate name' 定义子句的排序规则
  注意：字符集：推荐选择utf8编码，支持全世界的语言；utf8mb64编码，兼容性更高，支持表情包

示例：

create database db1;  创建数据库
show  create  database  db1;   查看创建数据库命令
注意，数据库默认使用的编码为latin1，即拉丁语
create database db2  character set=utf8mb4;  创建数据库db2，并指定数据库使用的编码格式
show  collation   查看排序规则，排序规则推荐使用默认即可

• 删除数据库
    DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';
• 查看支持所有字符集：SHOW CHARACTER SET;
• 查看支持所有排序规则：SHOW COLLATION;
• 获取命令使用帮助：
    mysql> HELP KEYWORD;
• 查看数据库列表：
    mysql> SHOW DATABASES;

表

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

创建表

• 创建表：CREATE TABLE
  (1) 直接创建

示例：

直接创建表(可以分开多行书写)：
MariaDB [db1]> create table students (
    -> id int unsigned AUTO_INCREMENT  primary key, 
    -> name varchar (50) NOT NULL,
    -> sex enum('m','f'),     性别定义使用枚举(在男和女之间选其中一个)
    -> age tinyint unsigned default 20
    -> );
show create table students；  查看表的创建
desc students   查看表结构的定义

(2) 通过查询现存表创建；新表会被直接插入查询而来的数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name[(create_definition,...)] [table_options] [partition_options]select_statement

示例：

select * from  表名；    查看数据库中的某张表
create table newstudents seletc * from students;  查询students表，把表结构和信息导入newstudents表中
MariaDB [hellodb]> select * from newstudents;   此命令查看新表newstudents的内容
MariaDB [hellodb]> desc newstudents;   查看新表的表结构
+-----------+---------------------+------+-----+---------+-------+
| Field     | Type                | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| StuID     | int(10) unsigned    | NO   |     | 0       |       |
| 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.01 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.01 sec)

跨数据库创建表

示例：

在db1数据库中创建newstudents表，从hellodb数据库复制students表的结构和内容
MariaDB [db1]> create table newstudents select * from hellodb.students;
Query OK, 25 rows affected (0.01 sec)
Records: 25  Duplicates: 0  Warnings: 0
查询表
MariaDB [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| newstudents   |
| students      |
+---------------+
2 rows in set (0.00 sec)
查询表内容
MariaDB [db1]> select * from newstudents;

(3) 通过复制现存的表的表结构创建，但不复制数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name{ LIKE old_tbl_name| (LIKE old_tbl_name) }

示例：

MariaDB [db1]> create table test like newstudents;   创建新表test，该表为空表 
查看两张表的表结构，发现表结构一致
MariaDB [db1]> desc test;
+-----------+---------------------+------+-----+---------+-------+
| Field     | Type                | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| StuID     | int(10) unsigned    | NO   |     | 0       |       |
| 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.00 sec)

MariaDB [db1]> desc newstudents;
+-----------+---------------------+------+-----+---------+-------+
| Field     | Type                | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| StuID     | int(10) unsigned    | NO   |     | 0       |       |
| 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.00 sec)
查看test表的内容，发现新表test为空
MariaDB [db1]> select * from test;
Empty set (0.00 sec)

知识扩展：

使用以下语法也可以只复制表结构，不复制表内容
create table test2  newstudents select * from hellodb.students where 1=0;
在第二种语法的基础之上，添加一条不成立的判断语句where 1=0即可

另外，数据库在5.5版本中，数据库存在一个缺点：数据库文件所在目录中，数据被集中存放在一个表ibdata1中，并没有存放各自的目录下如(db1,db2,hellodb等)，各自目录下只是存放了表的定义，管理起来非常不方便。而10以上版本mariadb数据库则没有这种缺点.
解决方法：
在配置文件/etc/my.cnf中添加innodb_file_per_table=on即可
在之前编译安装数据库准备配置文件时，我们提到过该项

vim  /etc/my.cnf
innodb_file_per_table=on   是指存放表时，每张表分开存放

更改后重启数据库
systemctl restart mariadb
如果再添加新的数据库，每张表将会分开存放

• 注意：
    Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎，同一库中不同表可以使用不同的存储引擎
    同一个库中表建议要使用同一种存储引擎类型
  CREATE TABLE [IF NOT EXISTS] ‘tbl_name’ (col1 type1 修饰符, col2 type2 修饰符, ...)
• 字段信息
    col type1 字段类型
    PRIMARY KEY(col1,...) 主键
    INDEX(col1, ...) 索引
    UNIQUE KEY(col1, ...) 惟一键
• 表选项：
    ENGINE [=] engine_name 存储引擎名称
      SHOW ENGINES;查看支持的engine类型
    ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
• 获取帮助：mysql> HELP CREATE TABLE;

表操作

• 查看所有的引擎：SHOW ENGINES
• 查看表：SHOW TABLES [FROM db_name]
• 查看表结构：DESC [db_name.]tb_name
• 删除表：DROP TABLE [IF EXISTS] tb_name
• 查看表创建命令：SHOW CREATE TABLE tbl_name
• 查看表状态：SHOW TABLE STATUS LIKE 'tbl_name'
• 查看库中所有表状态：SHOW TABLE STATUS FROM db_name

数据类型

• 数据类型：
    数据长什么样？
    数据需要多少空间来存放？
• 系统内置数据类型和用户定义数据类型
• MySql支持多种列类型：
    数值类型
    日期/时间类型
    字符串(字符)类型
    https://dev.mysql.com/doc/refman/5.5/en/data-types.html
• 选择正确的数据类型对于获得高性能至关重要，三大原则：
    更小的通常更好，尽量使用可正确存储数据的最小数据类型
    简单就好，简单数据类型的操作通常需要更少的CPU周期
    尽量避免NULL，包含为NULL的列，对MySQL更难优化
  

数据类型

• 1、整型

  tinyint(m)          1个字节范围(-128~127)
  smallint(m)         2个字节范围(-32768~32767)
  mediumint(m)        3个字节范围(-8388608~8388607)
  int(m)              4个字节范围(-2147483648~2147483647)
  bigint(m)           8个字节范围(+-9.22*10的18次方)

  加了unsigned，则最大值翻倍，如：tinyintunsigned的取值范围为(0~255)
  int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，Int(1)和Int(20)是相同的
    BOOL，BOOLEAN：布尔型，是TINYINT(1)的同义词。zero值被视为假，非zero值视为真

• 2、浮点型(float和double)，近似值
    float(m,d) 单精度浮点型8位精度(4字节) m总个数，d小数位
    double(m,d)双精度浮点型16位精度(8字节) m总个数，d小数位
    设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位
• 3、定点数
    在数据库中存放的是精确值,存为十进制
    decimal(m,d) 参数m\<65 是总个数，d\<30且d\<m 是小数位
    MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。例如，decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节
    浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节
    因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal——例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decima
• 4、字符串(char,varchar,_text)

说明：

char(n)         固定长度，最多255个字符
varchar(n)      可变长度，最多65535个字符
tinytext        可变长度，最多255个字符
text            可变长度，最多65535个字符
mediumtext      可变长度，最多2的24次方-1个字符
longtext        可变长度，最多2的32次方-1个字符
BINARY(M)       固定长度，可存二进制或字符，长度为0-M字节
VARBINARY(M)    可变长度，可存二进制或字符，允许长度为0-M字节
内建类型：ENUM枚举, SET集合

• char和varchar：
    (1)char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。
    (2)char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n< n>255)，所以varchar(4),存入3个字符将占用4个字节。
    (3)char类型的字符串检索速度要比varchar类型的快
  
• varchar和text：
    (1)varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n< n>255)，text是实际字符数+2个字节。
    (2)text类型不能有默认值
    (3)varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text
• 5.二进制数据：BLOB
    BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob是以二进制方式存储，不分大小写
    BLOB存储的数据只能整体读出
    TEXT可以指定字符集，BLOB不用指定字符集
• 6.日期时间类型

说明：

date    日期'2008-12-2'
time    时间'12:25:36'
datetime     日期时间'2008-12-2 22:06:44'
timestamp    自动存储记录修改时间
YEAR(2), YEAR(4)：年份

timestamp字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字段可以存放这条记录最后被修改的时间

修饰符

• 所有类型：

说明：

NULL                   数据列可包含NULL值
NOT NULL               数据列不允许包含NULL值
DEFAULT                默认值
PRIMARY KEY            主键
UNIQUE KEY             唯一键
CHARACTER SET name     指定一个字符集

• 数值型

说明：

AUTO_INCREMENT         自动递增，适用于整数类型
UNSIGNED               无符号

示例：

CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY,name VARCHAR（20）NOT NULL,age tinyint UNSIGNED);
DESC students;
CREATE TABLE students2 (id int UNSIGNED NOT NULL ,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED,PRIMARY KEY(id,name));

表操作

• DROP TABLE [IF EXISTS] 'tbl_name';
• ALTER TABLE 'tbl_name'
    字段：
      添加字段：add
      ADD col1 data_type[FIRST|AFTER col_name]
      删除字段：drop
      修改字段：
      alter（默认值）, change（字段名）, modify（字段属性）
    索引:
      添加索引：add index
      删除索引：drop index
    表选项
      修改:
• 查看表上的索引：SHOW INDEXES FROM [db_name.]tbl_name;
• 查看帮助：Help ALTER TABLE

示例：修改表

ALTER TABLE students RENAME s1;
ALTER TABLE s1 ADD phone varchar(11) AFTER name;
ALTER TABLE s1 MODIFY phone int;
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
ALTER TABLE s1 DROP COLUMN mobile;
Help ALTER TABLE 查看帮助
ALTER TABLE students ADD gender ENUM('m','f')
ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;
ALTER TABLE students ADD UNIQUE KEY(name);
ALTER TABLE students ADD INDEX(age);
DESC students;
SHOW INDEXES FROM students;
ALTER TABLE students DROP age;

DML语句

• DML: INSERT, DELETE, UPDATE
• INSERT：
    一次插入一行或多行数据
  语法：
  (1)INSERT [L OW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name[(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE 如果重复更新之
    col_name=expr
    [, col_name=expr] ... ]
  简化写法：
  INSERT tbl_name[(col1,...)] VALUES (val1,...), (val21,...)

示例：

一次性添加一条记录
insert into students (name,phone,sex,age)values('mage','10086','m','30');
一次性添加多条记录
MariaDB [db1]> insert into students (name,phone,sex,age)values('wang','10000','m','20'),('li','111111','m','20');

(2)INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
  [INTO] tbl_name
  SET col_name={expr | DEFAULT}, ...
  [ ON DUPLICATE KEY UPDATE
  col_name=expr
  [, col_name=expr] ... ]

示例：

把其他数据库的表导入数据库中
insert ... select 写法
insert into students(name,phone,sex,age) select name,phone,sex,age from db1.students;
注意：数据类型必须一致

(3)INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
  [INTO] tbl_name[(col_name,...)]
  SELECT ...
  [ ON DUPLICATE KEY UPDATE
  col_name=expr
  [, col_name=expr] ... ]

示例：

把其他数据库的表导入数据库中
insert ... set写法
insert into students set name='han',sex='m';

• UPDATE:
  UPDATE [LOW_PRIORITY] [IGNORE] table_reference
  SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
  [WHERE where_condition]
  [ORDER BY ...]
  [LIMIT row_count]
• 注意：一定要有限制条件，否则将修改所有行的指定字段
    限制条件：
      WHERE
      LIMIT

示例：

update students set sex='f' where id=5;
可同时更改多个字段：
update students set name='li',sex='f' where id=5;
把id为5的字段名字和性别进行更改

• Mysql选项：-U|--safe-updates| --i-am-a-dummy

示例：

数据库修改时一定要添加限制条件，否则将修改所有行的指定字段
为了避免出现忘记添加限制条件，可以使用以下选项：
在登录数据库时，加上-U选项，即mysql -U -uroot -p
加上-U选项后，如果在数据库中执行命令忘记添加限制条件时，会出现错误信息
MariaDB [db2]> update students set name='li',sex='f';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

为了使用方便，实现不输入-U选项也会有错误提示
在数据库客户端配置文件mysql语句块中添加safe-updates
vim /etc/my.cnf.d/mysql-clients.cnf
[mysql]
safe-updates

• DELETE:
• DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
  [WHERE where_condition]
  [ORDER BY ...]
  [LIMIT row_count]
    可先排序再指定删除的行数
• 注意：一定要有限制条件，否则将清空表中的所有数据
    限制条件：
      WHERE
      LIMIT
• TRUNCATE TABLE tbl_name; 清空表
  -U选项也适用与delete命令，可以避免出现忘记条件限制删除全部记录的情况

DQL语句

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

SELECT相关用法

• 字段显示可以使用别名：
  col1 AS alias1, col2 AS alias2, ...

示例：

select stuid as 学员编号,name 姓名,age 年龄 from students;  把关键字段以中文显示，而且字段显示的前后顺序可以自定义
+--------------+---------------+--------+
| 学员编号      | 姓名          | 年龄   |
+--------------+---------------+--------+
|            1 | Shi Zhongyu   |     22 |
|            2 | Shi Potian    |     22 |
|            3 | Xie Yanke     |     53 |
|            4 | Ding Dian     |     32 |
|            5 | Yu Yutong     |     26 |
|            6 | Shi Qing      |     46 |
+--------------+---------------+--------+

• 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: 读锁，共享锁，同时多个读

示例：

比较操作符
select * from students where classid=1;  挑选出students表中班级编号为1的学员
注意：这里表名大小写敏感，书写时要与表名一致；而字段名则大小写不敏感，不用与表中字段名一致，但为了使得命令规范化，尽量使其与原内容格式保持一致。
select * from students where age > 30 and age <50;

between  and 命令：
select * from students where age between 30 and  50;
挑出students表中年龄大于30小于50岁的学员
这两种写法类似，但是要注意，between and命令表示闭区间，包括30和50；而大于和小于号则是开区间，并不包括30和50

in命令：
select * from students where classid in (1,2,3);

IS NULL和IS NOT NULL命令：
select * from students where teacherid is null;
select * from students where teacherid is not null;

distinct  去除重复列
select distinct  classid from students ;

like：支持通配符
%  匹配任意长度的字符
select * from students where name like 's%';  选择以s开头的学员
select * from students where name like '%o';  选择以o结尾的学员
建议使用左前缀写法，即's%'，因为在性能优化方面，右后缀写法'%o'和包含写法'%o%'会带来性能的影响

rlike和regexp：支持正则表达式
select * from students where name rlike '^s';
这种写法不推荐使用，因为这种写法性能更差

order by：排序
select * from students order by age;  以年龄排序，默认正序排序
select * from students order by age desc;   倒序排序
默认排序是根据排序规则进行排序的
show character set；  查看默认排序规则
简体中文  gb2312

LIMIT [[offset,]row_count]： 对显示结果进行行数数量限制
select * from students limit 3;
limit 2,3    2是指偏移量，3是指显示行数
select * from students order by age limit 2,3;   是指跳过前两个，显示后三个

group：分组，分类别
count() 函数  返回select命令中检索到的行中非null值的数目
注意()内的字段的值为非空字段，即该字段的值中不能出现null，否则将不能查询出准确的值
MariaDB [hellodb]> select count(*) from students ;
+----------+
| count(*) |
+----------+
|       25 |
+----------+
1 row in set (0.00 sec)

select classid,count(*) from students group by classid ;
统计每个班级中的人数，即以班级id为分组，统计每个班级中的总人数

一旦做分组，select 后跟的语句必须是两种内容：第一，要写分组本身，第二，要跟聚合函数，即做汇总的数据，如：最大值max，最小值min，平均值avg等
如：
select classid,min(age) from students group by classid ;
统计每个班级中最小年龄的人

先分组再做过滤
注意：一旦分组，如果要在分组后再做条件过滤，则不能使用where，而是要使用having
select classid,avg(age) from students group by classid having classid in (1,2);
统计students表中1班2班的平均年龄

select classid,avg(age) from students where gender = 'm' group by classid having classid in (1,2);
统计1班2班中男生的平均年龄
注意：如果先过滤后分组，则还是使用where

对多个字段分组：
select classid,gender,count(*) from students group by classid,gender;
统计每个班级中的男生女生分别有几个，即以班级作为分组后，再以性别作为分组进行统计

select * from students where birth between '1988-01-01 00:00:00' and '1998-12-31 00:00:00';
从student表中查询出生日期在1988年到1998年的人

练习：

导入hellodb.sql生成数据库  
(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄  
select * from students where age > 25 and gender='M';   
(2) 以ClassID为分组依据，显示每组的平均年龄  
select avg(age) from students group by classid;  
(3) 显示第2题中平均年龄大于30的分组及平均年龄  
select classid,avg(age) from students group by classid having avg(age) > 30;
(4) 显示以L开头的名字的同学的信息  
select * from students where name like 'L%';
或select * from students where name rlike '^L';
(5) 显示TeacherID非空的同学的相关信息  
select * from students where teacherid is not null;  
(6) 以年龄排序后，显示年龄最大的前10位同学的信息  
select * from students order by age desc limit 10 ;  
(7) 查询年龄大于等于20岁，小于等于25岁的同学的信息  
select * from students where age  between 20 and 25;
注意：大于小于号为开区间，不包括20或25.

多表查询

• 交叉连接：笛卡尔乘积
• 内连接：
    等值连接：让表之间的字段以“等值”建立连接关系；
    不等值连接
    自然连接:去掉重复列的等值连接
    自连接
• 外连接：
    左外连接：
      FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
    右外连接
      FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
• 子查询：在查询语句嵌套着查询语句，性能较差
    基于某语句的查询结果再次进行的查询
• 用在WHERE子句中的子查询：
    用于比较表达式中的子查询；子查询仅能返回单个值
      SELECT Name,AgeFROM students WHERE Age>(SELECT avg(Age) FROM students);
    用于IN中的子查询：子查询应该单键查询并返回一个或多个值从构成列表
      SELECT Name,AgeFROM students WHERE Age IN (SELECT Age FROM teachers);
    用于EXISTS
• 用于FROM子句中的子查询
    使用格式：SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_aliasWHERE Clause;

示例：

SELECT s.aage,s.ClassIDFROM (SELECT avg(Age) AS aage,ClassIDFROM students WHERE ClassIDIS NOT NULL GROUP BY ClassID) AS s WHERE s.aage>30;

• 联合查询：UNION
  SELECT Name,AgeFROM students UNION SELECT Name,AgeFROM teachers

练习：

导入hellodb.sql，以下操作在students表上执行  
1、以ClassID分组，显示每班的同学的人数   
select classid,class,numofstu from classes;  
2、以Gender分组，显示其年龄之和  
select count(age) from students group by gender;  
3、以ClassID分组，显示其平均年龄大于25的班级  
select classid,avg(age) from students group by classid having avg(age) > 25;
4、以Gender分组，显示各组中年龄大于25的学员的年龄之和  
select gender,sum(age) from students where age > 25 group by gender;  
5、显示前5位同学的姓名、课程及成绩  
select student_name,course,score from courses as c inner join (select st.name as student_name,courseid,score from scores as sc inner join (select * from students limit 5) as st on st.stuid=sc.stuid )as a on c.courseid=a.courseid;
6、显示其成绩高于80的同学的名称及课程；  
select student_name,course,score from courses as c inner join  (select st.name as student_name,courseid,score from scores as sc  inner join (select * from students limit 5) as st  on st.stuid=sc.stuid group by name)as a  on c.courseid=a.courseid  having score > 80;
7、求前8位同学每位同学自己两门课的平均成绩，并按降序排列  
select st.name as student_name,avg(score) as sc_avg from students as st inner join scores as sc on st.stuid=sc.stuid group by st.stuid order by sc_avg desc;
8、显示每门课程课程名称及学习了这门课的同学的个数  
select c.course,count(stuid) as studnet_count  from scores as s left join courses as c on s.courseid=c.courseid group by s.courseid;
9、显示其年龄大于平均年龄的同学的名字  
`select name from students where age > (select avg(age) from students);`  
10、显示其学习的课程为第1、2，4或第7门课的同学的名字  
select name from students as st inner join (select distinct  sc.stuid  from scores as sc inner join (select * from courses where courseid in(1,2,4) or courseid in (7)) as c on sc.courseid=c.courseid) as a on st.stuid=a.stuid;
11、显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学  
select student.name,student.age,student.classid,second.avg_age from (select students.name as name ,students.age as age,students.classid as classid from students left join (select count(name) as num,classid as classid from students group by classid having num>=3) as first on first.classid=students.classid) as student,(select avg(age) as avg_age,classid as classid from students group by classid) as  second where student.age>second.avg_age and student.classid=second.classid;
12、统计各班级中年龄大于全校同学平均年龄的同学   
select name,classid,age  from students where age > (select avg(age) from students);    

视图

• 视图：VIEW,虚表，保存有实表的查询结果
  例如，把内连接或外连接查询出来的结果定义为视图，使用该视图可直接查询出内连接或外连接查询出的结果，类似于给查询结果定义一个别名
  但是，视图本身不保存查询出来的数据，只显示查询出来的结果，数据来自于实际的表
• 创建方法：
    CREATE VIEW view_name[(column_list)]
      AS select_statement
      [WITH [CASCADED | LOCAL] CHECK OPTION]
• 查看视图定义：SHOW CREATE VIEW view_name
• 删除视图：
    DROP VIEW [IF EXISTS]
      view_name[, view_name] ...
      [RESTRICT | CASCADE]
• 视图中的数据事实上存储于“基表”中，因此，其修改操作也会针对基表实现；其修改操作受基表限制