数据库简介
数据库:存储数据的仓库
数据库系统：DBS，由数据库、数据库管理系统、应用开发工具构成
数据库管理系统：DBMS，定义管理和维护数据的软件
常见数据库系统：Oracle DB2 SQLserver Access Mysql Postgresql
SQL简介
SQL:结构化查询语言 数据库管理系统通过SQL语言来管理数据库中的数据
SQL组成:DDL 数据定义语言
DML 数据操作语言
DQL 数据检索语句
DCL 数据控制语句
DDL 定义数据库、表、视图、索引和触发器，像DROP、CREATE、ALTER
DML 对数据的增删改 INSERT UPDATE DELETE
DQL 从表中获得数据 SELECT
DCL 控制用户的访问权限GRANT REVOKE COMMIT ROLLBACK
MySQL简介
目前Internet上流行的网站构架方式是：LAMP
Linux Apache MySQL Php
mysql下载地址 http://www.mysql.com
在线安装
离线安装
为什么使用MySQL
MySQL是开放源代码的数据库
MySQL的跨平台性
开源免费
功能强大，使用方便
启动、停止mysql服务器
1.通过系统服务器
2.命令提示符
net start mysql
net stop mysql
断开、连接MySQL服务器
1.mysql -uroot -h127.0.0.1 -p
服务器所在的地址可以不写（-h127.0.0.1）
2.quit exit
MySQL数据类型和运算符
数据类型
数字、字符、日期和时间
运算符
算数运算、比较运算、逻辑运算、位运算
字段判null
is null is not null
in 指定范围内
数字类型
整型 7
tinyint bit bool 1个字节
浮点数
float
double
decimal 可变
根据真实的数据长度，设置的可能无效
字符串类型
1.常规 char varchar
2.可变类型 TEXT存储长文本 BLOB存储二进制数据 2 16 65535
3.特殊类型
set 64 容纳一组值或null
enum 65535 容纳所列值之一
日期和时间
date
time
datetime
timestamp
year 可指定2位和4位数字的格式
赋予不合理的值，将会被0替代
运算符
算术运算符
比较运算符
is null
is not null
between and
in
not in
like
not like
regexp
判断是否为null
<=> ;is null is; not null
MYSQL存储引擎
查询
show engines; g G
show variables like 'have%'
innodb
优点：提供事务、回滚、崩溃修复；事务处理能力强；支持自增长，外键
缺点：读写效率稍差，占用的数据空间较大
使用情况：
更新密集的表
处理事务
自动灾难恢复
myisam
存储格式：静态存储、动态存储、压索存储
优点：占用空间小处理速度快
缺点：不支持事务的完整性和并发性
memory
优点：处理速度快
缺点：数据容易丢失，生命周期短
使用情况：
暂时数据
丢失无关
第五章
数据库
create databases db;
show databases;
use database;
drop database db;
设置编码格式
set names gb2312
数据表
create table if not exists
not null,null,default value,auto_increment,primary key,reference_definition
查看表结构
desc table;
show columns from 数据表.数据库
修改表结构
alter [ignore] table alter_spec
//ignore,当出现重复关键行，则只执行一行，其他重复行被删除
//alter修改表列时，前提条件是必须把表的数据删除
重命名表
rename table t1 to t2
删除表
drop table if exists t1
插入记录
insert into t1() values(),()
查询数据库
select s_list from 数据表名
where
group by group_concat()//每个组中所有字段都显示出来
order by(ASC DESC )
having
limit count
//数据表名 既可以从一个表中查询，也可以从多个表中查询，中间用，隔开
where条件语句
常用比较运算符
!= <>
is null
is not null
between and
in
not in
like
not like
regexp
distinct
在结果中去除重复行
like 模糊查询
%匹配一个或多个字符
_只匹配一个字符
concat()联合多列
select id,concat(bookname,";",price) as info form tb;
limit限定结果行数
select * form tb where id limit 1,4//从1开始的4行
函数表达式
AVG）平均值
count（）非空记录；distinct，统计不同值；count（*）包含空的所有=count（1）
min()最小
max()最大
修改记录
update数据表名 set
删除记录delete
delete from 数据表名 where condition
内连接 外连接
子查询
in; not in
比较运算符
带exists关键字的子查询 exists;not exists
带any关键字的子查询
带all关键字的子查询
合并查询
union：将所有查询结果合并到一起，去除相同记录
union all：简单将结果合并到一起
为表取别名：select * from tb_book b where b.talk='JAVA'
为字段取别名：AS
使用正则表达式查询
字段名 regexp '匹配方式‘
^:开头
$:结尾
.：任意一个字符
[]:集合中的任意一个字符
[^]:除了字符集以外的任意一个字符
s1|s2|s3:任意一个字符串
*：匹配多个该符号之前的字符，包括0和1；select books from tbook where books regexp 'J*A';A之前出现过J的字符记录
+：匹配多个该符号之前的字符，包括1个：select books from tbook where books regexp 'J+A';A之前至少出现过1个J的字符记录
{N}：匹配字符串出现N次：select books from tbook where books regexp 'a{3}'连续出现3次a的记录
{M,N};匹配字符串最少M，最多N次；select books from tbook where books regexp 'a{2,4}
mysql函数
数学函数
ABS(X):绝对值
CEIL(X)CEILIN(X):不小于X的最小整数
FLOOR(X):不大于x的最大整数值
RAND(X)：返回0到1之间的随机小数 ，包括0 不包括1， 两个相同的RAND()返回的数值不同,两个相同的RAND(X)，只要里面的X一样，返回数就相同。
TRUNCATE(X,Y):返回X保留到小数点后Y位
ROUND(X):返回离X最近的整数
ROUND(X,Y): 返回X保留到小数点后Y位
SORT(X):求平方根
字符串函数
INSERT(S1,X,LEN,S2):将字符串S1中从X位置开始、长度为len的字符串用S2替换
UPPER(S)和UCASE(S)：变成大写字母
LEFT(S,N):字符串S的前N个字符
RTRIM(S):去掉字符串S结尾处空格
SUBSTRING(S,N,LEN)从字符串S的第N个位置开始获取长度为len的字符串
REVERSE(S):将字符串S的顺序反过来
FIELD(S,S1,S2...)返回函数第一个与字符串S匹配的字符串的位置
日期和时间函数
CURRENT_DATE()=CURDATE()：当期日期
CURTIME()=CURRENT_TIME():当前时间
NOW():当前日期和时间 URRENT_TIMESTAMP(),LOCALTIME(),SYSDATE(),LOCALTIMESTAMP()
DATEDIFF(D2,D2):两个日期之间相差的天数
ADDDATE(D,N):从D日期上加上N天的日期
ADDDATE(D,INTERVAL EXPR TYPE):当前日期加上时间段后的日期
SUBDATE(D,N):返回起始日期D减去N天后的日期
条件判断函数
IF(expr,v1,v2):表达式成立返回v1否则返回v2
CASE WHEN expr THEN v1[WHEN expr2 THEN v2][ELSE vn]END
CASE expr WHEN E1 THEN v1[WHEN e2 THEN v2][ELSE vn]END
系统信息函数
VERSION()
CONNECTION_ID()
DATEBASE(),SCHEMA()
USER(),SYSTEM_USER(),SESSION_USER()
CURRENT_USER(),CURRENT_USER
CHARSET(str)
COLLATION(str)
LASR_INSERT_ID()
加密函数
PASSWORD(str)
MD5(str)
ENCODE(str,pswd_str)
DECODE(crypt_str,pswd_str)
其他函数
FORMAT(x,n)
ASCII(s)
BIN(x)HEX(x)OCT(x)
CONV(x,f1,f2)
INET_ATON(IP)
INET_NTOA(n)
eg：生成3个0-100之间的随机数
ROUND(RAND()*100),FLOOR(RAND()*100),CEILINE(RAND()*100)
ROUND:与X最接近的数
FLOOR:小于或者等于X的最大整数
CEILINE:大于或者等于X的最小整数
索引
1.什么事索引
索引由数据库中一列或多列组合而成，作用是提高数据表的查询速度
优点是提高检索数据的速度
缺点是创建和维护索引需要耗费时间
索引可以提高查询速度，减慢写入速度
2.索引结构
B-tree hash
3.索引分类
普通索引
唯一索引 unique
全文索引（fulltext）char varchar text 只有MYISAM支持
单列索引
多列索引
空间索引
主键是特殊的索引
4.如何创建索引
创建表时创建
[UNIQUE|FULLTEXT|SPATIAL] INDEX]|KEY 别名(属性名 长度 ASC|DESC)
在已存在的表上创建
CREATE UNIQUE|FULLTEXT|SPATIAL] INDEX]|KEY 别名 ON TABLE_NAME(属性名 长度 ASC|DESC)
修改数据表添加索引
ALTER TABLE TABLE_NAME ADD UNIQUE|FULLTEXT|SPATIAL] INDEX]|KEY 别名 (属性名 长度 ASC|DESC)
删除索引
DROP INDEX index_name ON TABLE_NAME
视图
1.概念
视图是一个虚拟表，是从一个或多个表中导出来的表。
视图是存储在数据库中的查询的SQL语句。
2.作用
简单-使复杂的查询易于理解和使用
安全-用户只能看到表中特定的行
逻辑数据独立
3.创建视图
查看是否有创建视图权限
SELECT select_priv,create_view_priv from mysql.user where user='用户名'
创建
create view 视图名（属性清单） as select 语句
查看
desc 视图名
show table status like '视图名'
show create view 视图名
修改
create or replace view 视图名（属性清单） as select 语句
alter view 视图名（属性清单） as select 语句
更新
对视图的更新就是对表的更新
更新限制
a.视图中包含count(),sum(),max(),min()等函数
b.视图中包含union,union all,distinct,group by,having
c.常量视图
d.视图中select中包含子查询
e.由不可更新的视图导出的视图
f.创建视图时，algorithm为temptale
g.视图对应的表上存在没有默认的列，而且该列没有包含在视图里。
删除视图
drop view if exists 视图名 [restrict | cascade]级联删除
查看视图详细
select * form information_schema.views;
存储过程和函数
创建的意义
避免开发人员重复编写相同的SQL语句，减少客户端和服务器端数据的传输
创建存储过程
delimiter $$;
create procedure proc_name (in,out,inout name 类型)
begin
.....
end
函数的创建
create function sp_name(std_id int)
returns type
begin
return
end
变量的应用
declare a int default 4;
定义的局部变量只在begin end内有效
全局变量（回话变量）不需要定义，直接使用，用@作为起始字符
为变量赋值
set var=expr;
select col_name into var_name from table where condition
光标的运用
声明
declare cursor_name cursor for select_statement
打开
open cursor_name
使用
fetch cursor_name into var_name
关闭
close cursor_name
流程控制
IF
IF condition THEN
ELSE conditon THEN
ELSE
ENDIF
CASE
CASE value
WHEN value THEN
ELSE...
END CASE
WHILE
WHILE condition do
...
END WHILE;
LOOP
LOOP
LEAVE LABEL
END LOOP
REPEAT
REPEAT
...
UNTIL condition
END REPEAT
调用存储过程
call sp_name(parameter)
select @n
调用函数
select function_name(parameter)
查询存储过程和函数
show {procedure| function} status like 'pattern'
状态查询
show create {procedure| function} sp_name
修改存储过程和函数
alter {procedure| function} sp_name [characteristic]
删除存储过程和存储函数
drop {procedure| function} if exists sp_name
捕获存储过程中的错误
declare can_not_find condition for sqlstate '42s20'
declare can_not_find condition for 1146
存储过程和函数信息
select * from information_schema.Routines where routine_name='count_of_student'G
触发器
只有一个执行语句的触发器
create trigger 触发器名 before/after 触发事件 on 表名 for each row 执行语句
多个执行语句的触发器
create trigger 触发器名 before/after 触发事件 on 表名 for each row
begin
执行语句列表
end
查看触发器
show triggers;
查看triggers表中触发器信息
select * from information_schema.triggers;
select * from information_schema.triggers where trigger_name='触发器名称'
触发器执行顺序
before触发器、表操作、after触发器
注意事项
触发器不能包含start transcation、commit、rollback等关键词，也不能包含call语句
删除触发器
drop trigger 触发器名称
事务（只有innodb支持）
ACID 事务必须遵守的4个属性：原子性、一致性、隔离性、持久性
创建innodb表
create table table_name() type=innodb
alter table table_name type=innodb(alter tbale可能导致数据库中的数据丢失，使用之前备份数据)
初始化事务
start transaction
创建事务
SQL语句
提交事务
commit
事务回滚
rollback
mysql行为
可以控制行为的变量
autocommit transactionisolaction level
set autocommit=0
select @@autocommit
修改事务的孤立级
select @@tx_isolation
set global transaction isolation leve1
mysql伪事务
设置表锁替代事务
1.为指定表添加锁定
lock tables table_name lock_type
2.执行操作
3.解锁
unlock tables;
应用表锁实现伪事务
1.对数据库中的数据表进行锁定操作，可以对多个表做出不同的方式锁定
lock table table_name1 lock_type1,table_name2 lock_type2
2.执行数据库操作
3.释放锁定的数据表
mysql备份与还原
备份一个数据库
mysqldump -uroot -proot db table1 table2>路径名称
备份多个数据库
mysqldump -uroot -proot --datebases db1 db2>路径名称
备份所有数据库
mysqldump -uroot -proot --all-datebases>路径名称
通过复制进行备份
使用mysqlhotcopy工具快速复制(只能备份myisam)
1.将需要备份的数据表加上一个读锁；
2.用flush tables将内存中的数据写会到硬盘的数据库上；
3.把需要备份的数据库文件复制的目标目录
mysqlhotcopy[option] db1 db2 backupdir/
数据库还原
通过MySQL命令还原
mysql -uroot -proot [dbname]>backup.sql
通过复制还原（保证MySQL版本一致）
chown -R mysql.mysql datadir（将数据库的用户和组变成MySQL）
导出数据表
用select into outfile导出文本
select[列名]from table where into outfile '目录文件'[option]
用mysqldump命令导出文件
mysqldump -uroot -proot -T 目标目录 dbname table [option]
导出XML文件
mysqldump -uroot -proot -xml -X dbname table >D: name.xml
用MySQL命令导出文本文件
mysqldump -uroot -proot -e "select 语句" dbname >D: name.txt
mysqldump -uroot -proot -xml -X -e "select 语句" dbname >D: name.xml
mysqldump -uroot -proot --html -H -e "select 语句" dbname >D: name.html
mysql系统管理
MySQL系统管理的构成
1.MySQL服务器-mysqld
2.MySQL客户程序和工具程序-mysql mysqladmin mysqlcheck isamchk myisanchk mysqldump mysqlhotcopy
3.服务器的语言SQL
4.MySQL数据目录
MySQL日常管理
服务器的启动与关闭
对用户账号进行管理
对日志文件进行管理
对数据库进行备份和搬迁
建立数据库镜像
对服务器进行配置和优化
同时运行多个服务器
对MySQL进行软件升级
安全问题
加强文件系统的安全性
加强MySQL服务器的安全性
数据库维护
崩溃恢复
预防性维护
数据目录的位置和结构
mysqladmin variables
show variables like 'datadir'
多服务器的情况
mysqladmin --host=127.0.0.1 --port=port_num variables
mysqladmin --host=localhost --socket=/path/to/socket variables
mysqladmin --host=. --socket=pip_name variables
mysqladmin --host=hostname variables
%ps axww | grep mysql
$ps -ef | grep mysqld
%mysqld --help
%find / -name "*.frm" --print
MySQL支持的数据表类型
myisam
.frm
.myd
.myi
innodb
.frm
SQL语句如何操作数据表
创建数据表
创建.frm保存数据表的结构定义
跟新数据表
对.frm进行重新编码
删除数据表
删除数据表目录下相关的文件
MySQL状态文件和日志文件
进程ID文件
常规查询日志
慢查询日志
变更日志
二进制变更日志
二进制变更日志的索引文件
错误日志
数据目录的结构对系统性能的影响
尽可能少的创建文件用-innoDB，只需要一个文件
MySQL性能优化
查询MySQL数据库性能
show status like 'value'
connection:连接服务器次数
uptime:服务器上线时间
show_queries:慢查询次数
com_select:查询操作次数
com_insert:插入操作次数
com_delete:删除操作次数
查询innodb参数
innodb_rows_read
innodb_rows_insert
innodb_rows_updated
innodb_rows_deleted
优化查询
分析查询语句
explain select 语句
describe select 语句 desc
索引对查询 速度的影响
explain 数据库查询语句
使用索引优化查询
1.应用like关键字优化索引进行查询
2.查询语句中使用多列查询
3.查询语句中使用or关键字
查询高速缓存
1.检验高速缓存是否开启
show variables like '%query_cache%'
2.使用高速缓存
select SQL_CACHE * from 表名
优化数据库结构
分析表
analyze table 表名
检查表
check table 表名
优化表
optimize table 表名
将多字段的表分解成多个表
建立中间表
优化插入记录速度
1.禁用索引
2.禁用唯一性检查
3.优化insert语句
优化多表查询和表设计
优化多表查询
优化表设计
MySQL安全技术
基本安全策略
1.设置MySQL的root密码
2.禁止所有不使用的系统服务
3.为操作系统和所有安装的软件打补丁
4.关闭端口
5.审计服务器的用户账号
用户和权限管理
create user
drop user
rename user
grant all on * to mr identified by 'mr' with grant option
revoke all privileges,grant from fred
MySQL安全的常见问题
权限更改何时生效
flush privileges
mysqladmin flush-privileges
mysqladmin reload
设置用户密码
mysqladmin -u user_name -h host_name password "newpwd"
set password for 'jeffrey'@'%'=password('biscuit')
grant usage on '.' to 'jeffrey'@'%'indentified by 'biscuit';
mysql -u root mysql insert into user(host,user,password) values('%','jeffrey',password('biscuit')); flush privilges
mysql -u root mysql update user set password=password('bagel') where host='%'and user='francis';
mysql日常管理
UNIX套接字文件被误删
% mysqladmin -h 127.0.0.1 -u root -p shutdowm
忘记root口令
%kill -TERM PID
%kill 9 PID
net stop mysql
flush privileges
日志失效处理
日志轮转
定期删除过期的日志
使用镜像机制
MySQL服务器镜像配置
镜像机制创建步骤
1.确定主从服务器的镜像ID号，主从服务器的ID号不能相同 用--server_id
2.从服务器必须在主服务器上有一个具备足够权限的账号
grant replication slave on *.* to'slave_user'@'slave_host' identified by 'salve_pass'
3.把主服务器上的数据库文件复制到从服务器上完成同步工作
4.关闭主服务器
5.对主服务器的配置进行修改，使他知道之间的ID，并激活二进制日志功能
[mysqld]
server-id=master_server_id
log_bin=binlog_name
6.重新启动主服务器
7.关闭从服务器
8.对从服务器进行配置，使他知道自己的ID，到哪里去找主服务器和如何去连接主服务器
[mysqld]
server_id=salve_server_id
master_host=master_host
master_user=slave_user
master-password=slave_pass;
master-connet-retry=30
master-retry-count=100000
9.重新启动从服务器
MySQL服务器的优化
参数变量的设置
--table_cache=128
[mysqld]
table-cache=128
set-variable=table_cache=128
指定全局变量
set global var_name=value
set @@global.var_name=value
指定回话变量
set session var_name=value
set @@session.var_name=value
set var_name=value
set @@var_name=value
set session sql1=0,sq3=3;
--set-variable=table_cache=128 -O table_cache=128
PHP操作MySQL
php访问MySQL的步骤
1.连接MySQL服务器
mysql_connect() mysql_pconnect()
2.选择数据库
mysql_select_db()
3.执行SQL语句
mysql_query()
4.关闭连接集
mysql_free_result($result)
mysql_fetch_array() 从数组集中获取信息
mysql_fetch_object()从结果集中获取一行
mysql_fetch_row()逐行获取结果的每条记录
mysql_num_rows()获取查询结果中的记录数
mysql_close()关闭连接
5.断开与MySQL的连接