mysql大纲

基础知识
1 MySQL数据库概要
2 简单MySQL环境
3 数据的存储和获取
4 MySQL基本操作
5 函数
6 数据查询

核心技术
7 索引
8 存储过程
9 触发器
10 视图
11 事务
12 系统管理

高级技术
13 数据库备份与还原
14 MySQL性能优化
15 MySQL数据库安全技术
16 MySQL日常管理
17 PHP访问MySQL数据库

项目实战
18 Java与MySQL数据库——图书管理系统
19 C语言与MySQL数据库——图书管理系统

大纲细节 page 02
基础知识
1 MySQL数据库概要
特性
应用环境
管理软件
学习方法
2 简单MySQL环境
下载与安装
启动连接断开停止
MySQL Workbench图形化管理工具
phpMyAdmin图形化管理工具
3 数据的存储和获取
MySQL存储引擎
MySQL 数据类型
MySQL运算符
4 MySQL基本操作
数据库操作
创建
查看
选择
删除
数据表操作
创建数据表
查看表结构
修改表结构
重命名表结构
删除表
MySQL语句操作
插入记录
查询数据库记录
修改记录
删除记录
外键约束
5 函数
MySQL函数
数学函数（ABS FLOOR RAND PI TRUNCATE ROUND SQRT )
字符串函数( INSERT UPPER UCASE LEFT RTRIM SUBSTRING REVERSE FIELD)
日期和时间函数(CURDATE CURTIME NOW DATEDIFF ADDDATE SUBDATE)
条件判断函数
系统信息函数
加密函数(PASSWORD MD5)
其他函数
6 数据查询
基本查询语句
单表查询
聚合函数查询
连接查询
子查询
合并查询结果
定义表和字段的别名
使用正则表达式查询
实践：使用聚合函数sum对学生成绩进行汇总

核心技术
7 索引
MySQL索引概述与分类
创建索引
删除索引
8 存储过程
创建存储过程和存储函数
流程控制语句
调用存储过程和存储函数
查看存储过程和函数
修改存储过程和函数
删除存储过程和函数
捕获存储过程中的错误
实践： 使用存储过程实现用户注册
9 触发器
创建MySQL触发器
查看触发器
应用触发器
删除触发器
10 视图
视图的概念与作用
创建视图
查看视图
修改视图
更新视图
删除视图
实践：使用MySQL视图查询学生信息表
11 事务
MySQL事物概述（原子性 一致性 孤立性 持久性）
MySQL事物的创建与存在周期
MySQL行为
事物和性能
MySQL伪事物
12 系统管理
MySQL系统管理概述
数据目录的位置
数据目录的结构
MySQL服务器如何提供对数据的访问
MySQL数据库在文件系统里如何表示
MySQL数据表如何在文件系统里表示
SQL语句如何映射为数据表文件操作
操作系统对数据库和数据表命名的限制
数据目录的结构对系统性能的影响
MySQL状态文件和日志文件

高级技术
13 数据库备份与还原
数据备份
数据还原
数据库迁移
表的导出和导入
实践：导出XML文件
14 MySQL性能优化
优化简介
优化查询
优化数据库结构
查询高速缓存
优化多表查询
优化表设计
15 MySQL数据库安全技术
MySQL的基本安全和保护策略
用户和权限管理
MySQL数据库安全技术常见问题
16 MySQL日常管理
连接故障恢复
日志文件管理
MySQL服务器镜像配置
MySQL服务器优化配置
优化MySQL服务器
运行多个MySQL服务器
17 PHP访问MySQL数据库
PHP语言概述
PHP操作MySQL数据库的步骤
使用PHP操作MySQL数据库
PHP管理MySQL数据库中的数据
常见问题与解决方法
MySQL与PHP的应用实例—迷你日记

项目实战
18 Java与MySQL数据库——图书管理系统
19 C语言与MySQL数据库——图书管理系统
需求分析
系统设计
数据库设计
C语言开发数据库程序的流程
数据库管理模块设计
文件引用
变量和函数定义
管理模块设计

熟知概念 page 03
基础知识
1 MySQL数据库概要
特性

开放的 快速的 多线程的 多用户

应用环境

MySQL是最流行的关系型数据库管理系统之一，在web应用方面，MySQL是最好的RDBMS（Relational Database Management System）应用软件，LAMP LNMP

管理软件

命令行工具：mysql命令和mysqladmin命令
图形管理工具：workbench ，MySQL Administrator， MySQL Query Browser

学习方法

如何学好MySQL： 多上机练习 多编写SQL语句 牢记数据库理论知识
MySQL学习常见问题：待回答

2 简单的MySQL环境
下载与安装
启动连接断开停止

centos6:
chkconfig --list mysqld
chkconfig --add mysqld
chkconfig mysqld on # 设置MySQL开机自启
service mysqld start/stop
mysqladmin -u root -p shutdown # 命令关闭MySQL
mysql -u root -h localhost -p

MySQL Workbench图形化管理工具
phpMyAdmin图形化管理工具
3 数据的存储和获取
MySQL存储引擎

InnoDB 事务型数据库的首选引擎，支持ACID事务，支持行级锁定, MySQL 5.5 起成为默认数据库引擎
MyISAM MySQL 5.0 之前的默认数据库引擎，最为常用。拥有较高的插入，查询速度，但不支持事务
Memory 所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在 MySQL 重新启动时丢失

如何选择存储引擎：
除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该优先选择InnoDB引擎

如何设置数据表的存储引擎
alter table table_name engine=innodb; (修改表引擎）
show engines; （查看系统支持的存储引擎）
show variables like ‘%storage_engine%’; (查看当前默认的存储引擎)
mysql> show create table table_name; (查看某个表使用的存储引擎)

MySQL 数据类型

数值类型:
1 整数类型： int tinyint bigint
2 浮点型：float 和double 不推荐使用浮点型
3 高精度型： decimal （numeric）如：amount DECIMAL(6,2)表示amount列最多存储6位数字，小数位数为2位。

字符类型：
1 varchar和char 变长字符和定长字符
2 text和blob 存储文本大数据类型和存储二进制大数据类型（实际很少使用，太浪费资源）
3 varbinary和binary 存储二进制字符
4 enum和set 集合类型，enum可枚举65536个元素而set最多64个

日期和时间类型：
DateTime（8个字节） TimeStamp（4）Date（3） Year（1）Time (1)
实际开发中选择TimeStamp作为时间类型的字段，也可以用int

MySQL运算符

1 算数运算符： + - * / %
2 比较运算符： == <==>安全的等于 ， <>和!= 不等于 ，<= , >= ,> ,<,
IS NULL ,IS NOT NULL , IN, NOT IN, LIKE(通配符匹配)， REGEXP(正则表达式匹配)
3 逻辑运算符的求值所得结果均为1 (TRUE)、0( FALSE)，这类运算符有逻辑非（NOT或者!）、逻辑与（AND或者&&）、逻辑或（OR或者||）、逻辑异或（XOR）。
4 位操作运算符 参与运算符的操作数，按二进制位进行运算。包括位与（&）、位或（|）、位非（~）、位异或（^）、左移（<<）、右移(>>)6种。

4 MySQL基本操作
数据库操作
创建CREATE DATABASE db_name;
查看SHOW DATABASES;
查看某一个库：SHOW CREATE DATABASE db_name;
选择USE db_name;
查看当前use了哪个库：select database();
删除DROP DATABASE db_name；
数据表操作
创建数据表

create table 表名 (
字段名1 类型 (宽度) 约束条件,
字段名2 类型(宽度) 约束条件,
字段名3 类型(宽度) 约束条件,
…
);

注 :
同一张表中，字段名不能相同
字段名和类型必须有
宽度和约束条件为可选项

查看某库有多少个表： SHOW TABLES;
查看表结构:DESC db_name;
查看表的所有字段内容：SELECT * FROM db_name;

查看某些字段SELECT id, port FROM host;
修改表结构

修改一列类型
alter table tab_name modify 列名 类型 [完整性约束条件]［first｜after 字段名］;
alter table users2 modify age tinyint default 20;
alter table users2 modify age int after id;
修改列名
alter table tab_name change [column] 列名 新列名
类型 [完整性约束条件]［first｜after 字段名］;
alter table users2 change age Age int default 28 first;
删除一列
alter table tab_name drop [column] 列名;
思考：删除多列呢？删一个填一个呢？
alter table users2
add salary float(6,2) unsigned not null after name,
drop addr;
修改表名
rename table 表名 to 新表名;
修该表所用的字符集
alter table student character set utf8;
删除表
drop table tab_name;
添加主键，删除主键
alter table tab_name add primary key(字段名称,…)
ALTER TABLE person ADD PRIMARY KEY (id);
alter table users drop primary key;

重命名表RENAME TABLE old_table_name TO new_table_name;
删除表DROP TABLE table_name;
插入记录

INSERT INTO table_name ( field1, field2,…fieldN )
VALUES
( value1, value2,…valueN );

修改记录

需要修改或更新 MySQL 中的数据，我们可以使用 UPDATE 命令来操作
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

你可以同时更新一个或多个字段。
你可以在 WHERE 子句中指定任何条件。
你可以在一个单独表中同时更新数据。
当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。

UPDATE runoob_tbl SET runoob_title=‘学习 C++’ WHERE runoob_id=3;

删除记录

DELETE FROM 命令来删除 MySQL 数据表中的记录。
DELETE FROM table_name [WHERE Clause]

如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
你可以在 WHERE 子句中指定任何条件
您可以在单个表中一次性删除记录。
当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。

DELETE FROM runoob_tbl WHERE runoob_id=3;

MySQL SQL语句总结
库：CREATE, DROP, SHOW, USE
表：CEATE ,DROP, DESC,SHOW,RENAME,ALTER
字段：ALTER …CHANGE, ALTER…MODIFY, ALTER…ADD, ALTER…DROP
记录：INSERT INTO … ,UPDATE…SET…[WHERE…],
DELETE FROM…[WHERE…]
语言类型：

数据查询语言DQL (Data Query Language)
数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE
子句组成的查询块：
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>

2 .数据操纵语言DML（Data Manipulation Language）
数据操纵语言DML主要有三种形式：
(1) 插入：INSERT
(2) 更新：UPDATE
(3) 删除：DELETE

数据定义语言DDL（ Data Definition Language）
数据定义语言DDL用来创建数据库中的各种对象-----表、视图、
索引、同义词、聚簇等如：
CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
| | | | |
表 视图 索引 同义词 簇
DDL操作是隐性提交的！不能rollback

数据控制语言DCL（Data Control Language）
数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制
数据库操纵事务发生的时间及效果，对数据库实行监视等。如：
(1) GRANT：授权。
(2) ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。回滚
(3) COMMIT [WORK]：提交

约束

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。
约束类型：主键 外键 唯一 非空 自增 默认值
关键字： primary key foreign key unique not null
auto_increment default

1、主键约束 primary key
主键约束 主键列的数据类型不限，但此列必须是唯一并且非空。当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

创建主键约束
create table temp(
id int primary key,
name varchar(20)
);
删除主键约束
alter table temp drop primary key;
添加主键约束
alter table temp add primary key(id,name);

2、外键约束 foreign key
外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系

基本模式
– 主表
create table temp(
id int primary key,
name varchar(20)
);
– 副表
create table temp2(
id int,
name varchar(20),
classes_id int,
foreign key(id) references temp(id)
);
多列外键组合，必须用表级别约束语法
– 主表
create table classes(
id int,
name varchar(20),
number int,
primary key(name,number)
);
副表
create table student(
id int auto_increment primary key,
name varchar(20),
classes_name varchar(20),
classes_number int,
/表级别联合外键/
foreign key(classes_name, classes_number) references classes(name, number)
);
删除外键约束
alter table student drop foreign key student_id;
增加外键约束
alter table student add foreign key(classes_name, classes_number) references classes(name, number);

3、 唯一约束unique
唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。

创建唯一约束
创建表时设置，表示用户名、密码不能重复
create table temp(
id int not null ,
name varchar(20),
password varchar(10),
unique(name,password)
);
添加唯一约束
alter table temp add unique (name, password);
修改唯一约束
alter table temp modify name varchar(25) unique;
删除约束
alter table temp drop index name;

4、非空约束 not null 与 默认值 default
非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。

创建非空约束
创建table表，ID 为非空约束，name 为非空约束 且默认值为abc
create table temp(
id int not null,
name varchar(255) not null default ‘abc’,
sex char null
)；
增加非空约束
alter table temp modify sex varchar(2) not null;
取消非空约束
alter table temp modify sex varchar(2) null;
取消非空约束，增加默认值
alter table temp modify sex varchar(2) default ‘abc’ null;

5 函数
MySQL函数
数学函数（ABS FLOOR RAND PI TRUNCATE ROUND SQRT )

ABS(x) 返回x的绝对值
CEIL(x) 返回大于x的最小整数值
FLOOR(x) 返回小于x的最大整数值
MOD(x,y) 返回x/y的模（余数）
SQRT(x) 返回一个数的平方根
ROUND(x,y) 返回参数x的四舍五入的有y位小数的值
TRUNCATE(x,y) 返回数字x截短为y位小数的结果
PI() 返回pi的值（圆周率）
RAND()返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。

字符串函数( INSERT UPPER UCASE LEFT RTRIM SUBSTRING REVERSE FIELD)

INSERT(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
LOWER(str) 返回将字符串str中所有字符改变为小写后的结果
UPPER(str) 返回将字符串str中所有字符转变为大写后的结果
LEFT(str,x)返回字符串str中最左边的x个字符
RIGHT(str,x) 返回字符串str中最右边的x个字符
TRIM(str)去除字符串首部和尾部的所有空格
REVERSE(str) 返回颠倒字符串str的结果
FIELD(filed_name,str1,str2,str3,str4)可以用来对SQL中查询结果集进行指定顺序排序

select * from ta order by field(id,4,3,2,1);
select * from 表名 order by field(id,1,4,2,3) desc;

日期和时间函数(CURDATE CURTIME NOW DATEDIFF ADDDATE SUBDATE)

CURDATE()或CURRENT_DATE() 返回当前的日期
CURTIME()或CURRENT_TIME() 返回当前的时间
NOW() 返回当前的日期和时间
DATE_FORMAT(date,fmt) 依照指定的fmt格式格式化日期date值
DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date) 返回date是一个月的第几天(1~31)
DAYOFYEAR(date) 返回date是一年的第几天(1~366)
DAYNAME(date) 返回date的星期名，如：
SELECT DAYNAME(CURRENT_DATE);
HOUR(time) 返回time的小时值(0~23)
MINUTE(time) 返回time的分钟值(0~59)
MONTH(date) 返回date的月份值(1~12)
MONTHNAME(date) 返回date的月份名，如：
SELECT MONTHNAME(CURRENT_DATE);
FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts

条件判断函数

IF(test,t,f) 如果test是真，返回t；否则返回f
IFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2

CASE WHEN[test1] THEN [result1]…ELSE [default] END如果testN是真，则返回resultN，否则返回default
CASE [test] WHEN[val1] THEN [result]…ELSE [default]END 如果test和valN相等，则返回resultN，否则返回default
CASE函数的格式有些复杂，通常如下所示：

CASE [expression to be evaluated]
WHEN [val 1] THEN [result 1]
WHEN [val 2] THEN [result 2]
WHEN [val 3] THEN [result 3]
…
WHEN [val n] THEN [result n]
ELSE [default result]
END

CASE函数还有另外一种句法，有时使用起来非常方便，如下：
CASE
WHEN [conditional test 1] THEN [result 1]
WHEN [conditional test 2] THEN [result 2]
ELSE [default result]
END
这种条件下，返回的结果取决于相应的条件测试是否为真。

系统信息函数

DATABASE() 返回当前数据库名
BENCHMARK(count,expr) 将表达式expr重复运行count次
CONNECTION_ID() 返回当前客户的连接ID

FOUND_ROWS() 返回最后一个SELECT查询进行检索的总行数
USER()或SYSTEM_USER() 返回当前登陆用户名
VERSION() 返回MySQL服务器的版本
示例：
SELECT DATABASE(),VERSION(),USER();
SELECTBENCHMARK(9999999,LOG(RAND()*PI()));
该例中,MySQL计算LOG(RAND()*PI())表达式9999999次。
加密函数(PASSWORD MD5)

AES_ENCRYPT(str,key) 返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用AES_ENCRYPT的结果是一个二进制字符串，以BLOB类型存储
AES_DECRYPT(str,key) 返回用密钥key对字符串str利用高级加密标准算法解密后的结果
DECODE(str,key) 使用key作为密钥解密加密字符串str
ENCRYPT(str,salt) 使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str
ENCODE(str,key) 使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串，它以BLOB类型存储
MD5() 计算字符串str的MD5校验和
PASSWORD(str) 返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。
SHA() 计算字符串str的安全散列算法(SHA)校验和
示例：
SELECT ENCRYPT(‘root’,‘salt’);
SELECT ENCODE(‘xufeng’,‘key’);
SELECT DECODE(ENCODE(‘xufeng’,‘key’),‘key’);#加解密放在一起
SELECT AES_ENCRYPT(‘root’,‘key’);
SELECT AES_DECRYPT(AES_ENCRYPT(‘root’,‘key’),‘key’);
SELECT MD5(‘123456’);
SELECT SHA(‘123456’);

其他函数

1、格式化函数：FORMAT(x,n)将数字x进行四舍五入保留n位小数
2、不同进制数字转换：
ASCII(s)返回字符串s的第一个字符的ASCII码
BIN(x)返回x的二进制编码
3、IP地址与数字相互转换的函数：
INET_ATON(IP)将IP转换为数字
INET_NTOA(n)将n转换为IP
4、改变字段数据类型的函数：CAST(x AS type)、CONVERT(x,type)两个函数将x变成type类型，仅仅是改变输出，没有改变表中的字段类型
5、改变字符集的函数CONVERT(s USING cs)函数将字符串s的字符集变成cs。

6 数据查询
SQL 主要语句可以划分为一下3类

DDL:数据定义语言，这些语句定义不同的数据段、数据库、表、列、索引等数据库对象。常用语句关键字主要包括create,drop,alter等
DML:数据操作语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用语句关键字主要包括 insert,delete,update和select等。
DCL数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要 的语句包括关键字grant、revoke等

单表查询

MySQL数据库中查询数据通用的 SELECT 语法：
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N] [ OFFSET M]

1 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
2 SELECT 命令可以读取一条或者多条记录。
3 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
4 你可以使用 WHERE 语句来包含任何条件。
5 你可以使用 LIMIT 属性来设定返回的记录数。
6 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

查询字段
SELECT * FROM fruits;
SELECT f_id, s_id, f_name, f_price FROM fruits;
SELECT f_name FROM fruits;
查询指定记录
SELECT 字段名1, 字段名2,…, 字段名n
FROM 表名 WHERE 查询条件 ;
例子：
查询价格为10.2元的水果的名称
SELECT f_name, f_price FROM fruits WHERE f_price=10.2;
查询名称为"apple"的水果的价格
SELECT f_name, f_price FROM fruits WHERE f_name=‘apple’;
查询价格小于10的水果的名称
SELECT f_name, f_price FROM fruits WHERE f_price<10.00;
带IN关键字的查询
查询s_id为101和102的记录
SELECT s_id, f_name, f_price FROM fruits
WHERE s_id IN (101, 102);
查询所有s_id不等于101也不等于102的记录
SELECT s_id, f_name, f_price FROM fruits
WHERE s_id NOT IN (101, 102)
ORDER BY f_name;
带BETWEEN AND 的范围查询
查询价格在2.00元到10.20元之间的水果名称和价格
SELECT f_name, f_price FROM fruits
WHERE f_price BETWEEN 2.00 AND 10.20;
查询价格在2.00元到10.20元之外的水果的名称和价格
SELECT f_name, f_price FROM fruits
WHERE f_price NOT BETWEEN 2.00 AND 10.20;
带LIKE的字符匹配查询
‘%’ 匹配任意长度的字符,甚至包括零字符
查找所有以’b’开头的水果
SELECT f_id, f_name FROM fruits
WHERE f_name LIKE ‘b%’;
在fruits表中,查询f_name中包含’g’的记录
SELECT f_id, f_name FROM fruits
WHERE f_name LIKE ‘%g%’;
查询以’b’开头,并以’y’结尾的水果名称
SELECT f_name FROM fruits
WHERE f_name LIKE ‘b%y’;
查询空值
查询customers表中c_email为空的记录c_id,c_name和c_email字段值
SELECT c_id, c_name, c_email FROM customers
WHERE c_email IS NULL;
查询customers表中c_email不为空的记录的c_id, c_name, c_email字段值
SELECT c_id, c_name, c_email FROM customers
WHERE c_email IS NOT NULL;
带AND OR的多条件查询
在fruits表中查询s_id=101,并且f_price大于等于5的水果价格和名称
SELECT f_id, f_price, f_name FROM fruits
WHERE s_id=‘101’ AND f_price >= 5;
查询结果不重复
SELECT DISTINCT 字段名 FROM 表名
查询fruits表中s_id字段的值,返回s_id字段且不得重复
SELECT DISTINCT s_id FROM fruits;

分组查询

group by
(1) group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组
(2) group by可用于单个字段分组，也可用于多个字段分组
select * from employee group by sex;
根据sex字段来分组，sex字段的全部值只有两个(‘男’和’女’)，所以分为了两组
当group by单独使用时，只显示出每组的第一条记录
所以group by单独使用时的实际意义不大

group by + group_concat()
(1) group_concat(字段名)可以作为一个输出字段来使用， concat： 合并，连接
(2) 表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合
select sex,group_concat(name) from employee group by sex;

group by + 集合函数
(1) 通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个"值的集合"做一些操作
分别统计性别为男/女的人年龄平均值
select sex,avg(age) from employee group by sex;
select sex,count(sex) from employee group by sex;

group by + having
(1) having 条件表达式：用来分组查询后指定一些条件来输出查询结果
(2) having作用和where一样，但having只能用于group by
select sex,count(sex) from employee group by sex having count(sex)>2;

-group by + with rollup
(1) with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和
select sex,group_concat(age) from employee group by sex with rollup;
±-----±------------------+
| sex | group_concat(age) |
±-----±------------------+
| 女 | 24 |
| 男 | 26,25,15 |
| NULL | 24,26,25,15 |
±-----±------------------+

使用LIMIT限制查询记录数量
LIMIT [位置偏移量,] 行数
显示fruits表查询结果的前4行

SELECT * FROM fruits LIMIT 4;

聚合函数查询

COUNT()函数
查询customers表中总行数
SELECT COUNT(*) AS cust_num
FROM customers;
SUM()函数
在orderitems表中查询30005号订单一共购买的水果总量
SELECT SUM(quantity) AS items_total
FROM orderitems
WHERE o_num=30005;
AVG()函数
在fruits表中,查询s_id=103的供应商的水果价格的平均值
SELECT AVG(f_price) AS avg_total FROM fruits WHERE s_id=103;
MAX()函数
在fruits表中查找市场上价格最高的水果值
SELECT MAX(f_price) AS max_price FROM fruits;
MIN()函数
在fruits表中查找市场上价格最低的水果值
SELECT MIN(f_price) AS min_price FROM fruits;

连接查询

mysql常用连接
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

语句：
1 INNER JOIN
SELECT a.name,a.age,b.address FROM name_age a INNER JOIN name_address b WHERE|ON a.name=b.name;
2 LEFT JOIN
SELECT a.name,a.age,b.address FROM name_age a LEFT JOIN name_address b ON a.name=b.name;
3 RIGHT JOIN
SELECT a.name,a.age,b.address FROM name_age a RIGHT JOIN name_address b ON a.name=b.name;

子查询
合并查询结果
定义表和字段的别名
使用正则表达式查询
实践：使用聚合函数sum对学生成绩进行汇总
MySQL对查询结果排序

从表中查询出来的数据，可能是无序的，或者其排列顺序表示用户期望的 。 使用ORDER BY对查询结果进行排序
SELECT 字段名1，字段名2，……
FROM 表名
ORDER BY 字段名1 [ASC|DESC]，字段名2[ASC|DESC]……
参数说明
指定的字段名1、字段名2，是对查询结果排序的依据
参数ASC，表示按照升序进行排序 ascend
参数DESC，表示按照降序进行排序 descend
默认情况下，按照ASC方式，升序进行排序
SELECT * FROM student ORDER BY grade DESC;

MySQL中，指定按照多个字段，对查询结果进行排序
比如，查询student表中所有记录，按照gender和grade字段进行排序，在排序过程中，会先按照gender字段进行排序，如果遇到gender字段相同的记录，再把这些记录，按照grade字段进行排序
SELECT * FROM student ORDER BY gender ASC, grade DESC;

MySQL开发中常用的查询语句总结
1、查询数值型数据:
SELECT * FROM tb_name WHERE sum > 100;
查询谓词:>,=,<,<>,!=,!>,!<,=>,=<
2、查询字符串
SELECT * FROM tb_stu WHERE sname = ‘Cricode.com’
SELECT * FROM tb_stu WHERE sname like ‘Uncle%Too’
SELECT * FROM tb_stu WHERE sname like ‘%程序员’
SELECT * FROM tb_stu WHERE sname like ‘%PHP%’
3、查询日期型数据
SELECT * FROM tb_stu WHERE date = ’2011-04-08′
注:不同数据库对日期型数据存在差异: ：
(1)MySQL: SELECT * from tb_name WHERE birthday = ’2011-04-08′
(2)SQL Server: SELECT * from tb_name WHERE birthday = ’2011-04-08′
(3)Access:SELECT * from tb_name WHERE birthday = #2011-04-08#
4、查询逻辑型数据
SELECT * FROM tb_name WHERE type = ‘T’
SELECT * FROM tb_name WHERE type = ‘F’
逻辑运算符:and or not
5、查询非空数据
SELECT * FROM tb_name WHERE address <>” order by addtime desc
注:<>相当于PHP中的!=
6、利用变量查询数值型数据
SELECT * FROM tb_name WHERE id = ‘${}_{P}OST[text]‘注:利用变量查询数据时，传入SQL的变量不必用引号括起来7、利用变量查询字符串数据SELECT\ast FROMt{b}_{n}ameWHEREnameLIKE‘$_POST[name]%’
完全匹配的方法”%%”表示可以出现在任何位置
8、查询前n条记录(LIMIT n 等价于 LIMIT 0,n)
SELECT * FROM tb_name LIMIT 0,$N;
limit语句与其他语句，如order by等语句联合使用，会使用SQL语句千变万化，使程序非常灵活
SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.
9、查询后n条记录
SELECT * FROM tb_stu ORDER BY id ASC LIMIT $n
10、查询从指定位置开始的n条记录
SELECT * FROM tb_stu ORDER BY id ASC LIMIT ${}_{P}OST[begin],$n
注意:数据的id是从0开始的
11、查询统计结果中的前n条记录
SELECT * ,(yw+sx+wy) AS total FROM tb_score ORDER BY (yw+sx+wy) DESC LIMIT 0,$num12、查询指定时间段的数据SELECT要查找的字段FROM表名WHERE字段名BETWEEN初始值AND终止值SELECT\ast FROMt{b}_{s}tuWHEREageBETWEEN0AND1813、按月查询统计数据SELECT\ast FROMt{b}_{s}tuWHEREmonth(date)=‘$_POST[date]‘ ORDER BY date ;
注：SQL语言中提供了如下函数，利用这些函数可以很方便地实现按年、月、日进行查询
year(data):返回data表达式中的公元年分所对应的数值
month(data):返回data表达式中的月分所对应的数值
day(data):返回data表达式中的日期所对应的数值
14、查询大于指定条件的记录
SELECT * FROM tb_stu WHERE age>$_POST[age] ORDER BY age;
15、查询结果不显示重复记录
SELECT DISTINCT 字段名 FROM 表名 WHERE 查询条件
注:SQL语句中的DISTINCT必须与WHERE子句联合使用，否则输出的信息不会有变化 ,且字段不能用代替
16、NOT与谓词进行组合条件的查询
(1)NOT BERWEEN … AND … 对介于起始值和终止值间的数据时行查询 可改成 <起始值 AND >终止值
(2)IS NOT NULL 对非空值进行查询
(3)IS NULL 对空值进行查询
(4)NOT IN 该式根据使用的关键字是包含在列表内还是排除在列表外，指定表达式的搜索，搜索表达式可以是常量或列名，而列名可以是一组常量，但更多情况下是子查询
17、显示数据表中重复的记录和记录条数
SELECT name,age,count() ,age FROM tb_stu WHERE age = ’19′ group by date
18、对数据进行降序/升序查询
SELECT 字段名 FROM tb_stu WHERE 条件 ORDER BY 字段 DESC 降序
SELECT 字段名 FROM tb_stu WHERE 条件 ORDER BY 字段 ASC 升序
注:对字段进行排序时若不指定排序方式，则默认为ASC升序
19、对数据进行多条件查询
SELECT 字段名 FROM tb_stu WHERE 条件 ORDER BY 字段1 ASC 字段2 DESC …
注意:对查询信息进行多条件排序是为了共同限制记录的输出，一般情况下，由于不是单一条件限制，所以在输出效果上有一些差别。
20、对统计结果进行排序
函数SUM([ALL]字段名) 或 SUM([DISTINCT]字段名),可实现对字段的求和，函数中为ALL时为所有该字段所有记录求和,若为DISTINCT则为该字段所有不重复记录的字段求和
如：SELECT name,SUM(price) AS sumprice FROM tb_price GROUP BY name
SELECT * FROM tb_name ORDER BY mount DESC,price ASC
21、单列数据分组统计
SELECT id,name,SUM(price) AS title,date FROM tb_price GROUP BY pid ORDER BY title DESC
注:当分组语句group by排序语句order by同时出现在SQL语句中时，要将分组语句书写在排序语句的前面，否则会出现错误。
22、多列数据分组统计
多列数据分组统计与单列数据分组统计类似
SELECT ，SUM(字段1字段2) AS (新字段1) FROM 表名 GROUP BY 字段 ORDER BY 新字段1 DESC
SELECT id,name,SUM(price*num) AS sumprice FROM tb_price GROUP BY pid ORDER BY sumprice DESC
注：group by语句后面一般为不是聚合函数的数列，即不是要分组的列。
23、多表分组统计
SELECT a.name,AVG(a.price),b.name,AVG(b.price) FROM tb_demo058 AS a,tb_demo058_1 AS b WHERE a.id=b.id GROUP BY b.type;
MySQL查询结果复制到新表的方法(更新、插入)

1、如果t2表中存在score值，将score更新到t1表中。方法如下：
UPDATE t1,t2
SET t1.score = t2.score
WHERE t1.id = t2.id AND t2.score IS NOT NULL
这就是将查询结果作为条件更新另一张表，当然，t2也可以是更为复杂的一个查询结果而不是一个具体的表。
2、将t1表的username更新至t2表，将t2表的score更新至t1表。方法如下：
UPDATE t1,t2
SET t1.score = t2.score,t2.username = t1.username
WHERE t1.id = t2.id
这个方法其实跟上面的方法类似，可以同时更新两个表的数据，即做表部分数据的互相复制、更新。
3、将t2表的查询结果插入到t1表中。方法如下：
INSERT INTO t1(id,username,score)
SELECT t2.id,t2.username,t2.score FROM t2 where t2.username = ‘lucy’
前面两种方式是更新表的记录，这种方式是插入一条新的记录。其实，从脚本可以看出，这个方法就是将查询和插入两个步骤合二为一。

核心技术
7 索引
MySQL索引概述与分类

索引是在存储引擎层实现的而不是在服务器层。
MyISAM存储引擎使用前缀压缩技术存储表记录，InnoDB存储引擎用原数据的格式进行存储。
MyISAM存储引擎通过数据的物理位置引用被索引的行，InnoDB存储引擎通过主键引用被索引的行。

MySQL索引类型：
1.普通索引
2.唯一索引
3.主键索引
4.组合索引
5.全文索引
语法
CREATE TABLE table_name[col_name data type]
[unique|fulltext][index|key]index_name[asc|desc]
1.unique|fulltext为可选参数，分别表示唯一索引、全文索引
2.index和key为同义词，两者作用相同，用来指定创建索引
3.col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择
4.index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值
5.length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
6.asc或desc指定升序或降序的索引值存储

尖括号，用于分隔字符串，字符串为语法元素的名称，SQL语言的非终结符。
[ ] 方括号表示规则中的可选元素。方括号中的规则部分可以明确指定也可以省略。
{ } 花括号聚集规则中的元素。在花括号中的规则部分必须明确指定。
. . . 省略号表明在规则中省略号应用的元素可能被重复多次。

创建索引
CREATE INDEX可对表增加普通索引或UNIQUE索引。
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
删除索引
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

1.普通索引
是最基本的索引，它没有任何限制。它有以下几种创建方式：
（1）直接创建索引
CREATE INDEX index_name ON table(column(length))
（2）修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
（3）创建表的时候同时创建索引
CREATE TABLE table (
id int(11) NOT NULL AUTO_INCREMENT ,
title char(255) CHARACTER NOT NULL ,
content text CHARACTER NULL ,
time int(10) NULL DEFAULT NULL ,
PRIMARY KEY (id),
INDEX index_name (title(length))
);
（4）删除索引
DROP INDEX index_name ON table
2.唯一索引
与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
（1）创建唯一索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
（2）修改表结构
ALTER table mytable ADD UNIQUE [indexName] (username(length))
（3）创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE indexName (username(length))
);
3.主键索引
是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：
CREATE TABLE table (
id int(11) NOT NULL AUTO_INCREMENT ,
title char(255) NOT NULL ,
PRIMARY KEY (id)
);
4.组合索引
指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合
ALTER TABLE table ADD INDEX name_city_age (name,city,age);
5.全文索引
主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。
（1）创建表的时候添加全文索引
CREATE TABLE table (
id int(11) NOT NULL AUTO_INCREMENT ,
title char(255) CHARACTER NOT NULL ,
content text CHARACTER NULL ,
time int(10) NULL DEFAULT NULL ,
PRIMARY KEY (id),
FULLTEXT (content)
);
（2）修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content)
（3）直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content)

缺点
1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。
2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。
索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

注意事项
1.索引不会包含有null值的列
2.使用短索引
3.索引列排序
4.不推荐使用like操作
5.不要在列上进行计算
6.不使用not in操作

8 存储过程

一. 存储过程的定义：
　　存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
二. 存储过程的优点：
简化应用开发人员的工作。
增强安全性。
减少数据在数据库和应用服务器之间的传输。

创建存储过程和存储函数

CREATE PROCEDURE sp_name ([proc_parameter[,…]])
[characteristic …] routine_body

创建存储过程
1.进入mysql命令行
mysql>
2.用delimiter命令来把语句定界符从 ;变为//。这样就允许在程序体用;定界符传递到服务器，而不是被mysql自己来解释。
mysql> delimiter //
3.创建存储过程
mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO param1 FROM t;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;

创建存储函数
建立自定义函数的过程如下：
1.进入mysql命令行
mysql>
2.用delimiter命令来把语句定界符从 ;变为//。这样就允许在程序体用;定界符传递到服务器，而不是被mysql自己来解释。
mysql> delimiter //
3.创建自定义函数
mysql>CREATE FUNCTION hashDiff( s1 varchar(16), s2 varchar(16))
->RETURNS INT
->BEGIN
->DECLARE diff, x INT;
->SET diff =0;
->SET x = 0;
->WHILE (x < 16 ) DO
->SET x = x+1;
->if SUBSTRING(s1, x,1)<>SUBSTRING(s2, x,1) then
->set diff=diff+ 1;
->end if;
->END WHILE;
->RETURN diff;
->END
->//
mysql>select * from test t where hashDiff(t.hashcode,‘ff9880f0f680ceff’) < 5;

流程控制语句
条件控制
　　　　IF条件：条件为真，执行
　　　　CASE条件：匹配到，执行
循环控制
　　　　WHILE循环：先判断后执行
　　　　REPEAT循环：先执行后判断
　　　　LOOP循环(死循环)
　　　　LEAVE语句(离开)
　　　　ITERATE语句：迭代，再次循环
　　　　RETURN语句：返回
注意：MySQL不支持FOR循环

一、条件控制：if语句、case语句
1、IF语句
mysql> DELIMITER $$mysql>CREATEPROCEDUREdifference(->INp1INTEGER,->INp2INTEGER,->OUTp3INTEGER)->BEGIN->IFp1>p2THENSETp3=1;->ELSEIFp1=p2THENSETp3=2;->ELSESETp3=3;->ENDIF;->END$$
mysql> DELIMITER ;
mysql> call difference(12,56,@ax);
mysql> select @ax;

2、CASE语句
mysql> DELIMITER $$mysql>CREATEPROCEDUREp1()->BEGIN->DECLAREvINTDEFAULT3;->CASEv->WHEN2THENSELECTv;->WHEN3THENSELECT0;->ELSE->BEGIN->END->ENDCASE;->END$$
mysql> DELIMITER ;
mysql> call p1();
检索型case语句
mysql> DELIMITER $$
mysql> CREATE PROCEDURE p2(
-> IN p1 INTEGER,
-> IN p2 INTEGER,
-> OUT p3 INTEGER)
DECLARE diff, x INT;
->SET diff =0;
->SET x = 0;
->WHILE (x < 16 ) DO
->SET x = x+1;
->if SUBSTRING(s1, x,1)<>SUBSTRING(s2, x,1) then
->set diff=diff+ 1;
->end if;
->END WHILE;
->RETURN diff;
->END
->//
mysql>select * from test t where hashDiff(t.hashcode,‘ff9880f0f680ceff’) < 5;

流程控制语句
条件控制
　　　　IF条件：条件为真，执行
　　　　CASE条件：匹配到，执行
循环控制
　　　　WHILE循环：先判断后执行
　　　　REPEAT循环：先执行后判断
　　　　LOOP循环(死循环)
　　　　LEAVE语句(离开)
　　　　ITERATE语句：迭代，再次循环
　　　　RETURN语句：返回
注意：MySQL不支持FOR循环

一、条件控制：if语句、case语句
1、IF语句
mysql> DELIMITER m y s q l &gt; C R E A T E P R O C E D U R E d i f f e r e n c e ( − &gt; 　 　 I N p 1 I N T E G E R , − &gt;