[大数据]MySQL基本概念

MySQL

1. 数据类型

数据类型决定了数据在计算机中的存储格式。常用的数据类型有：整数数据类型、浮点数数据类型、精确小数类型、二进制数据类型、日期/时间数据类型、字符串数据类型。表中的每一个字段就是某种指定数据类型。常用的如下：

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIME和YEAR。

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT。该节描述了这些类型如何工 作以及如何在查询中使用这些类型。常用的如下：

主键

主键（Primary Key）又称主码，**用于唯一地标识表中的每一条记录。**可以定义表中的一列或多列为主键，主键列上既不能有两行相同的值，也不能为空值。

例子：假如，定义authors表，该表给每一个作者分配 一个“作者编号”，该编号作为数据表的主键，如果出现相同的值，将提示错误，系统不能确定查询的究 竟是哪一条记录；如果把作者的“姓名”作为主键，则不能出现重复的名字，这与现实中的情况不符，因 此“姓名”字段不适合作为主键。

2. 数据库技术构成

数据库系统

数据库系统有3个主要的组成部分

①数据库：用于存储数据的地方。

②数据库管理系统：用于管理数据库的软件。

③数据库应用程序：为了提高数据库系统的处理能力所使用的管理数据

库软件的补充。

3. SQL语言

SQL语言包含以下4部分。

（1）数据定义语言（Data Definition Language DDL）：DROP、CREATE、ALTER等语句。

（2）数据操作语言（Data Manipulation Language DML）：INSERT（插入）、UPDATE（修 改）、DELETE（删除）语句。

（3）数据查询语言（Data Query Language DQL）：SELECT语句。

（4）数据控制语言（Data Control Language DCL）：GRANT、REVOKE、COMMIT、 ROLLBACK等 语句。

4. 数据库操作

显示所有的库：

SHOW DATABASES;

使用库

USE  databases;

创建库：

CREATE DATABASE database_name;

查看数据库的定义：

SHOW CREATE DATABASE mybatis;

删除库：

 DROP DATABASE database_name;

5. InnoDB表

从MySQL 8.0开始，系统表全部换成事务型的InnoDB表，默认的 MySQL实例将不包含任何MyISAM 表，除非手动创建MyISAM表。

SELECT DISTINCT ENGINE FROM information_schema.`TABLES`;

面试题： 1. InnoDB和MyISAM的区别

区别：

1. InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
2. InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
3. InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因 此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，
   然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM
   是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引 是独立的;
4. InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一 个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
5. InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致 其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

如何选择：

1. 是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM；

2. 如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用InnoDB。

3. 系统奔溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB；

4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹 的。如果你不知道用什么存储引擎，那就用InnoDB，至少不会差。

2.删除数据库时需要注意什么？

使用DROP DATABASE命令时要非常谨慎，在执行该命令时，MySQL 不会给出任何提醒确认信 息。用DROP DATABASE声明删除数据库后，数 据库中存储的所有数据表和数据也将一同被删除， 而且不能恢复。

6. 创建数据表

在创建完数据库之后，接下来的工作就是创建数据表。所谓创建数据 表，指的是在已经创建好的数据库中建立新表。创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性等）约束的过程。

数据表属于数据库，在创建数据表之前，应该使用语句“USE <数据库 名>”指定操作是在哪个数据库中进 行，如果没有选择数据库，就会抛出“No database selected”的错误。

创建数据表的语句为CREATE TABLE，语法规则如下：

CREATE TABLE TAB_NAME(
  字段1 类型 约束 默认值,
  字段2 类型 约束 默认值,
  字段n 类型 约束 默认值
)

使用CREATE TABLE创建表时，必须指定以下信息：

（1）要创建的表的名称，不区分大小写，不能使用SQL语言中的关键 字，如DROP、ALTER、INSERT 等。

（2）数据表中每一列（字段）的名称和数据类型，如果创建多列，就要用逗号隔开。 案例：创建员工表：employee

创建表的sql语句如下：

CREATE TABLE employee(
  emp_id INT,
  emp_name VARCHAR(50),
  emp_sex CHAR(2),
  emp_phone VARCHAR(50),
  emp_birth DATE,
  dept_id INT
);

查看库下面的所有表：

SHOW TABLES;

7. 表约束

1. 数据完整性

数据的完整性是指数据的可靠性和准确性

1.实体完整性：实体的完整性强制表的标识符列或主键的完整性(通过索引,唯一约束,主键约束或标识列 属性).

2.域完整性：限制类型(数据类型),格式(通过检查约束和规则),可能值范围(通过外键约束,检查约束,默认值 定义,非空约束和规则).

3.引用完整性：在删除和输入记录时,引用完整性保持表之间已定义的关系.引用完整性确保键值在所有表 中一致.这样的一致辞性要求不能引用不存在的值.如果一个键值更改了,那么在整个数据库中,对该键值的 引用要进行一致的更改.

4.自定义完整性：用户自己定义的业务规则.

四种完整性约束：

实体完整性：唯一约束、主键约束、标识列

域完完整性：限制数据类型、外键约束、默认值、非空约束

引用完整性：外键

自定义完整性：过程，触发器等

2. 约束

约束是在表上强制执行的一些数据校验规则，被插入、修改或删除的数据必须符合在相关字段上设置的这些约束条件。

五类完整性约束： 
NOT NULL     非空 
UNIQUE       唯一 
PRIMARY KEY  主键 
FOREIGN KEY  外键 
CHECKED      检查(mysql不支持的)

1. 主键约束

主键，又称主码，是表中一列或多列的组合。**主键约束（Primary Key Constraint）要求主键列的数据唯一，并且不允许为空。**主键分为两种类型：单字段主键和多字段联合主键。

1)定义列的同时指定主键，语法如下：

字段名 字段类型 primary key  （AUTO_INCREMENT：自增）

2)定义表之后指定主键

ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY(字段);

主键选取原则：

1. 主键应当是对用户没有意义的。如果用户看到了一个表示多对多关系的连接表中的数据，并抱怨它没有什么用处，那就证明它的主键设计地很好。
2. 主键应该是单列的，以便提高连接和筛选操作的效率。
3. 永远也不要更新主键。实际上，因为主键除了惟一地标识一行之外，再没有其他的用途了，所以也就没有理由去对它更新。如果主键需要更新，则说明主键应对用户无意义的原则被违反了。
4. 主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等
5. 主键应当有计算机自动生成。

2. 非空约束

非空约束（Not Null Constraint）指字段的值不能为空。对于使用了非空 约束的字段，如果用户在添加数据时没有指定值，数据库系统会报错。

 字段名 数据类型 not null

3. 唯一约束

唯一性约束（Unique Constraint）要求该列唯一，允许为空，但只能出 现一个空值。唯一约束可以确保一列或者几列不出现重复值。

唯一性约束的语法规则如下：

字段名 数据类型 UNIQUE

ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (约束字段);

删除唯一索引的语法：

ALTER TABLE 表名 DROP INDEX 约束名;

4. 默认值约束

默认约束（Default Constraint）指定某列的默认值。如男性同学较多， 性别就可以默认为‘男’。如果插 入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为‘男’。

默认约束的语法规则如 下：

字段名 字段类型 default 默认值

5. 外键约束

外键用来在两个表的数据之间建立连接，可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某值。

外键：首先它是表中的一个字段，虽可以不是本表的主键，但要对应另 外一个表的主键。外键的主要作用是保证数据引用的完整性，定义外键后， 不允许删除在另一个表中具有关联关系的行。外键的作用是 保持数据的一致性、完整性。

主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。

从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。

语法：

ALTER TABLE 子表 ADD CONSTRAINT 约束名 FOREIGN KEY (外键) REFERENCES 主表(主键);

常用的表关系有三种： 一对一、一对多【自关联】多对多

一对一：人与身份证

/* 一对一 
人 身份证 
 1   1 */ 
CREATE TABLE person( 
pid INT PRIMARY KEY AUTO_INCREMENT, pname VARCHAR(50), 
pphone VARCHAR(50) 
); 
CREATE TABLE idcard( cid INT PRIMARY KEY, 
cnum VARCHAR(50), 
cstart DATE, 
cend DATE, 
cpublish VARCHAR(200) 
); 
ALTER TABLE idcard ADD CONSTRAINT fk_idcard_person FOREIGN KEY(cid) REFERENCES person(pid);

一对多：部门与员工

/*
1.一对多
部门 员工
1 N
1 1
*/
CREATE TABLE department(
departid INT PRIMARY KEY AUTO_INCREMENT,
departname VARCHAR(50),
departnum INT DEFAULT 0,
departdesc VARCHAR(200)
);
CREATE TABLE employee(
empid INT PRIMARY KEY AUTO_INCREMENT,
empname VARCHAR(50),
empsex CHAR(2) DEFAULT '男',
empbirth DATE,
empphone VARCHAR(50),
deptid INT -- FK
);
/*
alter table tabname add constratin 约束名 foreign key(字段) references 主表
(主键);
*/
ALTER TABLE employee ADD CONSTRAINT fk_emp_dept FOREIGN KEY(deptid)
REFERENCES department(departid);

多对多：用户与角色【中间表多列主键】

/* 多对多的关系
用户 角色
1 N
N 1
N N
*/
CREATE TABLE users(
uid INT PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(50) NOT NULL UNIQUE,
upwd VARCHAR(50) NOT NULL,
ustatus INT
);
CREATE TABLE roles(
rid INT PRIMARY KEY AUTO_INCREMENT,
rname VARCHAR(50) NOT NULL
);
CREATE TABLE userroles(
uid INT ,
rid INT,
PRIMARY KEY(uid,rid)
);
ALTER TABLE userroles ADD CONSTRAINT fk_ur_user FOREIGN KEY(uid) REFERENCES
users(uid);
ALTER TABLE userroles ADD CONSTRAINT fk_ur_role FOREIGN KEY(rid) REFERENCES
roles(rid);

自关联：省市

REATE TABLE cities(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(50),
pid INT ,
CONSTRAINT fk_pro_ci FOREIGN KEY(pid) REFERENCES cities(cid)
)

3. 查看表结构

DESCRIBE/DESC语句可以查看表的字段信息，其中包括字段名、字段 数据类型、是否为主键、是否有 默认值等。

语法规则如下：

DESC 表名;

SELECT 字段列表 FROM 表1,表2… WHERE 表达式 GROUP BY 字段 HAVING 条件 ORDER BY 字段
LIMIT [,] ]

其中，各个字段的含义分别解释如下

NULL：表示该列是否可以存储NULL值。

Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；

? UNI表示该列是UNIQUE索引的一部分；MUL表示在列中某个给定 值允许出现多次。

Default：表示该列是否有默认值，有的话指定值是多少。

Extra：表示可以获取的与给定列有关的附加信息，例如 AUTO_INCREMENT等。

查看表详细结构语句：

SHOW CREATE TABLE dept;

CREATE TABLE `dept` (
  `depid` int(11) NOT NULL,
  `dep_name` varchar(50) DEFAULT NULL,
  `dep_desc` varchar(200) DEFAULT NULL,
  `dep_num` int(11) DEFAULT NULL,
  PRIMARY KEY (`depid`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk

4. 修改表结构

修改表指的是修改数据库中已经存在的数据表的结构。**MySQL使用 ALTER TABLE语句修改表。**常用的修改表的操作有修改表名、修改字段数 据类型或字段名、增加和删除字段、修改字段的排列位置、更改表 的存储引擎、删除表的外键约束等。

1. 修改表名

ALTER TABLE <旧表名> RENAME [TO] <新表名>;

2. 修改字段的数据类型

ALTER TABLE <表名> MODIFY <字段名> <数据类型>

3. 修改字段名

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;

4. 添加字段

ALTER TABLE <表名> ADD <新字段名> <新字段类型> <约束条件> [FIRST|AFTER 已存在字段名];

5. 删除字段

ALTER TABLE <表名> DROP <字段名> ;

6. 修改字段的排序位置

ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>;

7. 删除表的外键约束

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>

8. 更改表的存储引擎

ALTER TABLE <表名> ENGINE=<更改后的存储引擎名>;

9. 删除数据表

DROP TABLE [IF EXISTS]表1, 表2,…表n;

5. 建表时，如何选择数据类型

1. 整数和浮点数

   如果不需要小数部分，就使用整数来保存数据；如果需要表示小数部分，就使用浮点数
   类型。对于浮点数据列，存入的数值会对该列定义的小数位进行四舍五入。例如，假设 列的值的范围为1~99999，若使用整数，则
   MEDIUMINT UNSIGNED是最好的类型；若 需要存储小数，则使用FLOAT 类型

   浮点类型包括FLOAT和DOUBLE类型。DOUBLE类型精度比FLOAT类型高，因此要求存 储精度较高时应选择DOUBLE类型

2. 浮点数和定点数 浮点数FLOAT、DOUBLE相对于定点数DECIMAL的优势是：在长度一定的情况下，浮点 数能表示更大的数据范围。由于浮点数容易产生误差，因 此对精确度要求比较高时，建
   议使用DECIMAL来存储。DECIMAL在MySQL中是以字符串存储的，用于定义货币等对 精确度要求较高的数据。
   在数据迁移中，float(M,D)是非标准SQL定义，数据库迁移可能
   会出现问题，最好不要这样使用。另外，两个浮点数进行减法和比较运算时也容易出 问
   题，因此在进行计算的时候，一定要小心。进行数值比较时，最好使用DECIMAL类型。

3. 日期与时间类型 MySQL对于不同种类的日期和时间有很多数据类型，比如YEAR和TIME。如果只需要记 录年份，则使用YEAR类型即可；如果只记录时间，则使用TIME类型。
   如果同时需要记录日期和时间，则可以使用TIMESTAMP或者DATETIME类型。由于
   TIMESTAMP列的取值范围小于DATETIME的取值范围，因此存储范围较大的日期最好使 用DATETIME。

   TIMESTAMP也有一个DATETIME不具备的属性。默认的情况下，当插入一条记录但并没
   有指定TIMESTAMP这个列值时，MySQL会把TIMESTAMP列设为当前的时间。因此当需
   要插入记录的同时插入当前时间时，使用TIMESTAMP是方便的。另外，TIMESTAMP在 空间上比 DATETIME更有效。

4. CHAR与VARCHAR之间的特点与选择

   CHAR和VARCHAR的区别如下：

   CHAR是固定长度字符，VARCHAR是可变长度字符。

   CHAR会自动补空格，VARCHAR不自动补。

   CHAR是固定长度，所以它的处理速度比VARCHAR的速度要快，但是它的缺点是浪费存
   储空间，所以对存储不大但在速度上有要求的可以使用CHAR类型，反之可以使用 VARCHAR类型来实现。

8. 数据CRUD操作

1.插入数据[insert]

INSERT INTO 表名 VALUES(val_list);
或
INSERT INTO 表名(col_list) VALUES(val_list);

可以插入多条记录

INSERT INTO 表名(col_list) VALUES(val_list1),(val_list2),(val_list3),
(val_list4);

备份数据表，如果表不存在

CREATE TABLE emp_bak AS SELECT * FROM emp;

如果表存在

INSERT INTO emp_bak SELECT * FROM emp;

2.更新数据[update]

UPDATE table_name SET col=val,col=val [WHERE <condition>];

UPDATE emp SET empname='jerry',empaddr='延安',empphone='118' WHERE empid=1;

3.删除数据[delete] or [truncate]

DELETE FROM table_name [WHERE <condition>];  where语句不存在的话，将删除整个表。
TRUNCATE TABLE table_name;  直接删除表而不是删除记录【软删除】

MySQL为什么不建议delete数据?

delete对性能会有影响，一般不建议硬性delete数据，而是标记deleted = 1这种软删除

为啥呢？

根据之前了解的mysql底层存储原理最小存储单元page页，无论是非叶子节点page存的是索引key和指 针，还是叶子节点存的是行数据.

1.当删除大量数据时

MySQL内部不会真正删除空间，而且做标记删除，即将delflag:N修改为delflag:Y，commit之后会 会被purge进入删除链表，如果下一次insert更大的记录，delete之后的空间不会被重用，如果插入 的记录小于等于delete的记录空会被重用

2.当少量删除中间数据时

你在中间删了某些个值，实际上只是找到那个page页找到对应的数据做删除标记，并不会实际影响page 页已经占的大小，这块空间可能也永远不会被利用，产生了内存碎片导致索引频繁分裂，影响SQL执行计 划的稳定性

正确姿势： 使用deleted = 1 字段来软删除，保证索引连续性， 必要时，可将deleted = 0的字段完整迁移到新表，解决碎片问题

查询数据[select]语句

select基本格式：

SELECT 字段列表 FROM 表1,表2... WHERE 表达式 GROUP BY 字段 HAVING 条件 
ORDER BY 字段 LIMIT [<offset>,] <row count>]

{* | <字段列表>}包含星号通配符和字段列表，表示查询的字段。其中，字段列表至少包含一个字段名
称，如果要查询多个字段，多个字段之间用逗号隔开，最后一个字段后不加逗号。

FROM <表1>,<表2>…，表1和表2表示查询数据的来源，可以是单个或者多个。

WHERE子句是可选项，如果选择该项，将限定查询行必须满足的查询条件。

GROUP BY <字段>，该子句告诉MySQL如何显示查询出来的数据，并按照指定的字段分组。

[ORDER BY <字段>]，该子句告诉MySQL按什么样的顺序显示查询出来的数据，可以进行的排序有升序 （ASC）、降序（DESC）。

LIMIT [,] ]，该子句告诉MySQL每次显示查询 出来的数据条数。

单表查询：

1.查询所有字段

SELECT * FROM 表名;

2.查询指定字段

SELECT 列名 FROM 表名;

3.查询多个字段

SELECT 字段名1,字段名2,…,字段名n FROM 表名;

起别名

SELECT e.`empid` AS 员工编号 ,e.`empname` AS 员工名字 FROM emp e;

4.查询指定记录

数据库中包含大量的数据，根据特殊要求，可能只需要查询表中的指定数据，即对数据进行过滤。在 SELECT语句中，通过WHERE子句可以对数 据进行过滤，语法格式为：

SELECT 字段名1,字段名2,…,字段名n FROM 表名 WHERE 查询条件

5.带IN关键字的查询

IN操作符用来查询满足指定范围内的条件的记录，使用IN操作符，将所有检索条件用括号括起来，检索条件之间用逗号分隔开，只要满足条件范围内的一个值即为匹配项。

NOT IN 正好相反

例如：查询编号为1 5 7 9的员工编号

SELECT * FROM emp WHERE empid IN (1,5,7,9);
SELECT * FROM emp WHERE empid NOT IN (1,5,7,9);

6.带BETWEEN AND的范围查询

BETWEEN AND用来查询某个范围内的值，该操作符需要两个参数,即范围的开始值和结束值，如果字段 值满足指定的范围查询条件，则这些记录被返回。

BETWEEN AND操作符前可以加关键字NOT，表示指定范围之外的 值，如果字段值不满足指定的范围内 的值，则这些记录被返回。

SELECT * FROM emp WHERE empid>=4 AND empid<=9;
SELECT * FROM emp WHERE empid BETWEEN 4 AND 9;

7.带LIKE的字符串匹配查询

通配符是一种在SQL的WHERE条件子句中拥有特殊意思的字符。SQL 语句中支持多种通配符，可以和 LIKE一起使用的通配符有‘%’和‘_’。
1)百分号通配符‘%’，匹配任意长度的字符，甚至包括零字符 。

2)下划线通配符‘_’，一次只能匹配任意一个字符。

SELECT * FROM emp WHERE empname LIKE 'c%'; -- 以c开头
SELECT * FROM emp WHERE empname LIKE '%c'; -- 以c结尾
SELECT * FROM emp WHERE empname LIKE '%c%'; -- 包含c

名字是两个字母的而且是a开头的

SELECT * FROM emp WHERE empname LIKE 'a_';
SELECT * FROM emp WHERE empaddr IN ('西安','武当山');
SELECT * FROM emp WHERE empaddr ='西安' OR empaddr = '武当山';
SELECT * FROM emp WHERE empaddr LIKE '西安%';

8.查询空值（null）

数据表创建的时候，设计者可以指定某列中是否包含空值（NULL）在SELECT语句中使用IS NULL子句， 可以查询某字段内容为空记录。与IS NULL相反的是NOT NULL，该关键字查找字段不为空的记录。

空的查询 查询地址为空的员工信息。

SELECT * FROM emp WHERE empaddr IS NULL;
SELECT * FROM emp WHERE empaddr ='';
SELECT * FROM emp WHERE empaddr IS NULL OR empaddr='';

非空查询(not null)

SELECT * FROM emp WHERE empaddr IS NOT NULL;
SELECT * FROM emp WHERE empaddr !='';
SELECT * FROM emp WHERE empaddr IS NOT NULL AND empaddr!='';

9.带AND的多条件查询

MySQL在WHERE子句中使用AND操作符限定只有满足所有查询条件的记录才会被返回。可以使用AND 连接两个甚至多个查询条件，多个条件表达式之间用AND分开。

查询性别为女同时地址是陕西省渭南市

SELECT * FROM emp WHERE empsex='女' AND empaddr='陕西省渭南市';

10.带OR的多条件查询

与AND相反，在WHERE声明中使用OR操作符，表示只需要满足其中一个条件的记录即可返回。OR也可 以连接两个甚至多个查询条件，多个条件表达式之间用OR分开。

11.查询结果不重复(DISTINCT关键字)

在SELECT语句中，可以使用DISTINCT关键字指示MySQL消除重复的记录值。

SELECT DISTINCT empid,empname,empsex FROM emp;

12.对查询结果排序(ORDER BY)

使用ORDER BY子句对指定的列数据进行排序。

desc:倒叙 asc:默认值，升序。

单列排序

SELECT * FROM emp ORDER BY empid DESC;

多列排序

SELECT * FROM emp ORDER BY empid DESC ,deptid DESC;

13.聚合函数

有时候并不需要返回实际表中的数据，而只是对数据进行总结。 MySQL提供一些查询功能，可以对获取 的数据进行分析和报告。这些函数 的功能有：计算数据表中记录行数的总数、计算某个字段列下数据的 总和， 以及计算表中某个字段下的最大值、最小值或者平均值。常用的聚合函数 MAX()、MIN()、 COUNT()、SUM()、AVG()。

1）COUNT()函数统计数据表中包含的记录行的总数，或者根据查询结果 返回列中包含的数据行数。

其使用方法有两种：

? COUNT(*)计算表中总的行数，不管某列是否有数值或者为空值。

? COUNT(字段名)计算指定列下总的行数，计算时将忽略空值的行。

2）SUM()是一个求总和的函数，返回指定列值的总和。 SUM()函数在计算时，忽略列值为NULL的行。

3）AVG()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。

4）MAX()函数返回指定列中的最大值。

MAX()函数除了用来找出最大的列值或日期值之外，还可以返回任意列 中的最大值，包括返回字符类型 的最大值。在对字符类型数据进行比较 时，按照字符的ASCII码值大小进行比较，从a~z，a的ASCII码最 小，z的 最大。在比较时，先比较第一个字母，如果相等，继续比较下一个字符，一直到两个字符不相 等或者字符结束为止。例如，‘b’与‘t’比较时，‘t’为最大值；“bcd”与“bca”比较时，“bcd”为最大值。

5）MIN()函数与MAX()函数类似，不仅适用于查找数值类型，也可应用于字符类型。

14.分组查询

GROUP BY关键字通常和集合函数一起使用。

查询每个部门的人数

SELECT deptid,COUNT(1) FROM emp GROUP BY deptid;

统计每个部门的男女人数

SELECT deptid,empsex,COUNT(1) FROM emp GROUP BY deptid,empsex;

分组后再排序

SELECT deptid,empsex,COUNT(1) FROM emp GROUP BY deptid,empsex ORDER BY
deptid DESC,COUNT(1) DESC;

15.使用HAVING过滤分组

GROUP BY可以和HAVING一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示

对于统计的每个部门的男女人数只要大于等于3的信息。

SELECT deptid,empsex,COUNT(1) FROM emp GROUP BY deptid,empsex HAVING COUNT(1)>=3 ORDER BY COUNT(1) DESC,deptid DESC;

16.LIMIT

SELECT返回所有匹配的行，有可能是表中所有的行，若仅仅需要返回 第一行或者前几行，可使用LIMIT 关键字。

9. 练习

1.创建表offices和表employess

USE company;
CREATE TABLE offices(
officeCode INT(10) PRIMARY KEY,
city VARCHAR(50) NOT NULL,
address VARCHAR(50),
country VARCHAR(50) NOT NULL,
postalCode VARCHAR(15) UNIQUE
)
DESC offices;
SHOW CREATE TABLE offices;

CREATE TABLE employees(
employeeNumber INT(11) PRIMARY KEY auto_increment,
lastName VARCHAR(50) NOT NULL,
firstName VARCHAR(50) NOT NULL,
mobile VARCHAR(25) UNIQUE,
officeCode INT(10) NOT NULL,
jobTitle VARCHAR(50) NOT NULL,
birth datetime NOT NULL,
note VARCHAR(255),
sex VARCHAR(5)
);
DESC employees;
SHOW TABLES;

– 结果！

– 2. 查看该库下几个表以及查看两张表结构。
SHOW TABLES
DESC employees;
DESC offices;
– 3.将表employees的mobile字段修改到officeCode字段后面。
ALTER TABLE employees MODIFY mobile VARCHAR(25) AFTER officeCode;
– 4. 将表employees的birth字段改名为employee_birth。
ALTER TABLE employees CHANGE birth comployee_birth datetime;
– 5. 修改sex字段，数据类型为CHAR(1)，非空约束。
ALTER TABLE employees MODIFY sex CHAR(1) NOT NUll;
– 6. 删除字段note。
ALTER TABLE employees DROP note;
– 7. 增加字段名favoriate_activity，数据类型为VARCHAR(100)。
ALTER TABLE employees ADD favourite_activity VARCHAR(100);
– 8. 删除表offices。
DROP TABLE offices;
– 9. 将表employees名称修改为employees_info。
L,
mobile VARCHAR(25) UNIQUE,
officeCode INT(10) NOT NULL,
jobTitle VARCHAR(50) NOT NULL,
birth datetime NOT NULL,
note VARCHAR(255),
sex VARCHAR(5)
);
DESC employees;
SHOW TABLES;

> -- 结果！
>
> -- 2. 查看该库下几个表以及查看两张表结构。
> SHOW TABLES
> DESC employees;
> DESC offices;
> -- 3.将表employees的mobile字段修改到officeCode字段后面。 
> ALTER TABLE employees MODIFY mobile VARCHAR(25) AFTER officeCode;
> -- 4. 将表employees的birth字段改名为employee_birth。 
> ALTER TABLE employees CHANGE birth comployee_birth datetime;
> -- 5. 修改sex字段，数据类型为CHAR(1)，非空约束。
> ALTER TABLE employees MODIFY sex CHAR(1) NOT NUll;
> -- 6. 删除字段note。
> ALTER TABLE employees DROP note;
> -- 7. 增加字段名favoriate_activity，数据类型为VARCHAR(100)。
> ALTER TABLE employees ADD favourite_activity VARCHAR(100);
> -- 8. 删除表offices。
> DROP TABLE offices;
> -- 9. 将表employees名称修改为employees_info。
> ALTER TABLE employees RENAME to employees_info;