关于操作系统模块的一些介绍
1 -
MySQL5.6.17编译安装
现在MySQL已经是8.x了， 这么老的版本应该不会再有人编译安装了。 再说单机版这个时代的背景下还是docker启动吧。 本文仅用于个人纪念
-
yum install -y gcc*
-
安装cmake
# mysql5.5之后需要cmake编译安装，因此，先下载cmake2.8.8
tar -zxvf cmake-2.8.8.tar.gz -C /usr/local/src
./configure --prefix=/usr/local/cmake
make
make install
- 安装mysql5.6.17
tar -zxvf mysql-5.6.17.tar.gz -C /usr/local/src
cmake \
#安装路径
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
#数据文件存放位置
-DMYSQL_DATADIR=/usr/local/mysql/data \
#my.cnf路径
-DSYSCONFDIR=/etc \
#支持MyIASM引擎
-DWITH_MYISAM_STORAGE_ENGINE=1 \
#支持InnoDB引擎
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
#支持Memory引擎
-DWITH_MEMORY_STORAGE_ENGINE=1 \
#快捷键功能(我没用过)
-DWITH_READLINE=1 \
#连接数据库socket路径
-DMYSQL_UNIX_ADDR=/tmp/mysqld.sock \
#端口
-DMYSQL_TCP_PORT=3306 \
#允许从本地导入数据
-DENABLED_LOCAL_INFILE=1 \
#安装支持数据库分区
-DWITH_PARTITION_STORAGE_ENGINE=1 \
#安装所有的字符集
-DEXTRA_CHARSETS=all \
#默认字符
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci
make
make install
- ：配置MySQL
# 使用下面的命令查看是否有mysql用户及用户组
cat /etc/passwd 查看用户列表
cat /etc/group 查看用户组列表
#如果没有就创建
groupadd mysql
useradd -g mysql mysql
#修改/usr/local/mysql权限
chown -R mysql:mysql /usr/local/mysql
#修改/usr/local/mysql权限
#初始化配置
#进入安装路径，执行初始化配置脚本，创建系统自带的数据库和表
cd /usr/local/mysql
scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql
注：在启动MySQL服务时，会按照一定次序搜索my.cnf，先在/etc目录下找，找不到则会搜索"$basedir/my.cnf"，在本例中就是 /usr/local/mysql/my.cnf，这是新版MySQL的配置文件的默认位置！注意：在CentOS 6.4版操作系统的最小安装完成后，在/etc目录下会存在一个my.cnf，需要将此文件更名为其他的名字，如：/etc/my.cnf.bak，否则，该文件会干扰源码安装的MySQL的正确配置，造成无法启动。 启动MySQL
- 添加服务，拷贝服务脚本到init.d目录，并设置开机启动
cp support-files/mysql.server /etc/init.d/mysql
chkconfig mysql on
service mysql start --启动MySQL
- 配置环境变量
echo "export PATH=/usr/local/mysql/bin:$PATH" >> /etc/profile
# 使当前终端配置生效
source /etc/profile
- 配置用户
# 设置root密码
$ mysql -uroot
mysql> SET PASSWORD = PASSWORD('123456');
# root用户可以远程访问
# password为远程访问时，root用户的密码，可以和本地不同。
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.16.%' IDENTIFIED BY 'password' WITH GRANT OPTION;
# 配置防火墙
# 打开/etc/sysconfig/iptables
# 在“-A INPUT –m state --state NEW –m tcp –p –dport 22 –j ACCEPT”，下添加：
-A INPUT m state --state NEW m tcp p dport 3306 j ACCEPT
service iptables restart
2 -
MySQL5.6和5.7的二进制安装
5.6和5.7有略微的不同在 5步和7步请注意
- 获得二进制文件
# 5.6
wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.28-linux-glibc2.5-x86_64.tar.gz
#
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.10-linux-glibc2.5-x86_64.tar.gz
- 加压到 /usr/local/mysql 目录(或者解压到当前目录然后做软链接到/usr/local/mysql)
mkdir /usr/local/mysql
tar -xvf mysql-5.6.28-linux-glibc2.5-x86_64.tar.gz
mv mysql-5.6.28-linux-glibc2.5-x86_64/* /usr/local/mysql/
mkdir /usr/local/mysql
tar -xvf mysql-5.7.10-linux-glibc2.5-x86_64.tar.gz
mv mysql-5.7.10-linux-glibc2.5-x86_64/* /usr/local/mysql/
- 创建 /usr/local/mysql/data 目录
# 5.6.28版本不需要
mkdir /usr/local/mysql/data
- 创建mysql用户和修改软件的权限
groupadd mysql
useradd -r -g mysql mysql -s /sbin/nologin (创建mysql用户并设置不可登录)
chown -R mysql.mysql /usr/local/mysql/
- 初始化数据(切换当前目录到/usr/local/mysql,在5.7中是bin目录下的mysql_install_db)
# 5.6
./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/
....此处省略一万字
# 5.7
bin/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/
2016-01-20 02:47:35 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2016-01-20 02:47:45 [WARNING] The bootstrap log isn't empty:
2016-01-20 02:47:45 [WARNING] 2016-01-19T18:47:36.732678Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead
2016-01-19T18:47:36.750527Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2016-01-19T18:47:36.750560Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
- 复制配置文件到 /etc/my.cnf
cp -a ./support-files/my-default.cnf /etc/my.cnf (如果问是否替换选择Y)
- mysql的服务脚本放到系统服务中
# 5.6
cp -a ./support-files/mysql.server /etc/init.d/mysqld
service mysqld start
./bin/mysql -uroot -p(空密码)
# 5.7 版本开始系统会默认初始化一个root密码
cp -a ./support-files/mysql.server /etc/init.d/mysqld
service mysqld start
cat /root/.mysql_secret
# Password set for user 'root@localhost' at 2016-01-20 03:22:59
Tl:iRfEhRlQ6
bin/mysql -uroot -p(输入Tl:iRfEhRlQ6)
[root@c12 mysql57]# ./bin/mysqladmin -u root -p password
Enter password:
New password:
Confirm new password:
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc |
+-----------+
1 row in set (0.00 sec)
3 - MySQL通用
MySQL基础
字段名称，数据类型，类型修饰（限制）
字符
CHAR(n)
VARCHAR(n)
BINARY(n)
VARBINARY(n)
TEXT(n)
BLOB(n)
数值
精确数值
整型
TINYINT
SMALLINT
MEDIUMINT
INT
BIGINT
修饰符：UNSIGNED，无符号
NOT NULL
十进制
DECIMAL
近似数值
浮点型
FLOAT
DOUBLE
日期时间
DATE
TIME
DATETIME
STAMP
布尔
内置：ENUM， SET
字段约束：限制
primary key 主键约束
foreign key 外键约束
not null 非空约束
null 空约束
check 检查约束
unique 唯一约束
default 默认值约束
auto_increment 自增长约束
drop、delete区别：
drop：删除库、删除表、删除字段
delete：
alter、update区别：
alter：修改表
update：修改记录
存储引擎(表的类型)
存储引擎分类
1、ISAM：查询速度快、支持全文索引、
不支持事务、不支持外键、不支持check约束
2、MyISAM：ISAM升级版
3、HEAP：速度快、数据驻留在内存
数据管理不稳定、计算机断电后所有数据全部丢失
4、InnoDB：速度较慢、支持事务、支持check约束、支持外键
不支持全文索引
常用的存储引擎：InnoDB、MyISAM
网站必须使用全文索引：MyISAM(大型网站)
不用非得使用全文索引：InnoDB(中小型网站)
数据库优化：修改数据库、表提高数据库运行效率
1、选择最合适的字段类型
char、varchar区别
字段的数据类型能用数字，就不用字符串
2、尽可能不使用子查询
1)用内连接代替子查询
2)子查询充当字段、delete语句出现子查询。只能用子查询
3、like查询尽可能用右侧%
1) like 'a%'
4、事务、保持数据的完整
1)避免数据库中出现大量的垃圾数据
5、尽可能的使用InnoDB引擎
1)事务：保持数据的完整性
2)外键：在业务层保证外键
6、给字段适当的添加索引
1)提高表的查询速度
7、每个表最好都有主键、而且主键还是数字类型
1)主键本身就是一个主键索引
8、避免数据库中出现NULL值
1)数据库对NULL值做了特殊处理
9、尽可能让相同类型的字段做比较
1)避免出现数据类型的转换
10、数据库设计是否合理
11、拆分表
12、主从数据库
13、负载均衡
14、服务器的硬件对数据库的影响
基础配置my.ini配置文件(/etc/my.cnf)
1、设置mysql端口号
port=3306
2、设置mysql编码
default-character-set=utf8
3、设置mysql的存储引擎
default-storage-engine=INNODB
4、设置mysql最大连接数
max_connections=100
5、设置数据库默认的存储引擎
default-storage-engine=MyISAM
控制台基础命令
# 登录 （在shell中执行）
mysql -h 主机地址 -u 用户名 -p密码
# 显示当前mysql版本和当前日期
select version(),current_date;
# 导出数据 （在shell中执行）
mysqldump -u 用户名 -p 数据库名 > FILE.sql
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
导出一个数据库结构
mysqldump -u wcnc -p -d -add-drop-table smgp_apps_wcnc >d:wcnc_db.sql
-d 没有数据 -add-drop-table 在每个create语句之前增加一个drop table
导出多个数据库
[root@localhost ~]# mysqldump -uroot -p -P3306 --databases course test > course.sql
导出所有数据库
[root@localhost ~]# mysqldump -uroot -p -P3306 --all-databases > all.sql
仅导出course数据库的数据，不包括表结构
[root@localhost ~]# mysqldump -uroot -p -P3306 --no-create-info course > course.sql
仅导出course数据库中的students和students_myisam两个表
[root@localhost ~]# mysqldump -uroot -p -P3306 --no-create-info course students students_myisam > students.sql
仅导出course数据库的表结构
[root@localhost ~]# mysqldump -uroot -p -P3306 --no-data course > course.sql
导出course数据库中除了teacher和score两个表的其他表结构和数据
[root@localhost ~]# mysqldump -uroot -p -P3306 --ignore-table=course.teacher --ignore-table=course.score course > course.sql
导出course数据库的表和存储过程和触发器
[root@localhost ~]# mysqldump -uroot -p -P3306 --routines --triggers course > course.sql
导出course数据库中符合条件的数据
[root@localhost ~]# mysqldump -uroot -p -P3306 --where="sid in (1,2)" course students students_myisam > course.sql
在主库备份
[root@codis-178 ~]# mysqldump -uroot -p -P3306 --master-data=2 --single-transctions course > course.sql
（在备份开始之初，在所有表上加一个只读锁（flush table with read lock），当成功获取该锁并开始备份之后，此锁就会立即释放，后续dump过程不会影响其他的读写操作）
在从库备份
[root@codis-178 ~]# mysqldump -uroot -p -P3306 --dump-slave --single-transctions test > test.sql
# 导入数据 （在shell中执行）
mysqldump -uUSER -p DATABASE < FILE.sql
mysql -u username -p -D dbname < filename.sql
mysql>source wcnc_db.sql
# 显示所有库
show databases;
查看当前使用的数据库
mysql> select database();
# 使用库
use 库名;
# 查看当前库中所有表
show tables;
# 查看表结构
desc 表名;
describe tablename;
# 显示当前mysql数据库所有的编码信息
show variables like 'character%';
# 查看表的存储引擎
use information_schema;
select table_schema,table_name,engine from tables;
select table_schema,table_name,engine from tables
where table_name='表名';
show create table 表名;
# mysql 新设置用户或更改密码后需用`flush privileges;`刷新MySQL的系统权限相关表，否则会出现拒绝访问，还有一种方法，就是重新启动mysql服务器，来使新设置生效。
# 命令行交互模式的一些控制命令 尤其是用windows的命令行连接数据库时很有用。不过说实话现在谁还用命令行连数据库呢
# mysql客户端窗口的编码
character_set_client utf8
# 连接数据库的编码
character_set_connection utf8
# 数据库的编码
character_set_database utf8
# 数据库的文件是哪种类型
character_set_filesystem binary
# 结果集的编码
character_set_results utf8
# mysql服务编码
character_set_server utf8
# mysql系统编码
character_set_system utf8
# mysql编码文件存储位置
character_sets_dir C:\Program Files\MySQL\MySQL Server 5.0\sharets
# 设置客户端编码
set names gbk;
数据库操作
create database name; 创建数据库
drop database name 直接删除数据库，不提醒
mysqladmin drop databasename 删除数据库前，有提示。
表操作和字段操作
create table 表名
(
字段名 数据类型 约束,
字段名 数据类型 约束,
字段名 数据类型 约束
)engine=存储引擎名;
修改表结构
1、修改表名
alter table 原名 rename to 新名;
2、添加字段
alter table 表名 add column 字段名 数据类型 约束;
alter table 表名 add 字段名 数据类型 约束;//使用这个即可
3、删除字段
alter table 表名 drop column 字段名;
alter table 表名 drop 字段名;//用这个即可
4、修改字段
alter table 表名 change 原字段名 新字段名 数据类型 约束;
alter table 表名 change userName userName varchar(20) not null;
切换clumon 顺序到最前
ALTER TABLE INFO MODIFY STAT TINYINT(4) FIRST;
切换到某clumon 后面
ALTER TABLE 表名 MODIFY clumonname AFTER clumonname;
复制表
1、整表复制(表结构、数据)
create table 新表 select * from 原表;
create table bbsInfo2 select * from bbsInfo;
create table bbsInfo2 select bbsId,clickTimes,bbsTime from bbsInfo;
2、复制表结构(表结构)
create table 新表 like 原表;
create table bbsInfo2 like bbsInfo;
索引操作
索引index
作用：提升数据库查询语句的速度
适合添加索引的字段：经常充当where条件的字段
索引的分类
1、主键索引：字段必须是主键、一张表最多只能有一个主键索引
2、普通索引：对字段没限制
3、唯一索引：字段里不能出现重复值
4、全文索引：对字段也没限制(建议：添加到中文字段)
各种索引适合添加到什么样字段
主键索引：主键
唯一索引：该字段没有重复值
全文索引：中文字段
普通索引：任意字段
创建索引的语法格式
1、主键索引
建表的同时指定主键
2、普通索引
create index 索引名 on 表名(字段名);
3、唯一索引
建表的同时指定唯一约束
create unique index 索引名 on 表名(字段名);
4、全文索引
create fulltext index 索引名 on 表名(字段名);
删除索引
drop index 索引名 on 表名;
索引的例子
1、普通索引
create index bbs_index on bbsInfo(title);
drop index bbs_index on bbsInfo;
2、唯一索引
create unique index bbs_index on bbsInfo(title);
drop index bbs_index on bbsInfo;
3、全文索引
create fulltext index bbs_index on bbsInfo(title);
drop index bbs_index on bbsInfo;
全文索引的例子
create table helloInfo
(
helloId int auto_increment primary key,
title varchar(100) not null,
content text not null,
helloTime timestamp default current_timestamp
)engine=MyISAM;
create fulltext index hello_index on helloInfo(title);
create fulltext index hello_c_index on helloInfo(content);
drop index hello_c_index on helloInfo;
索引注意：
1、经常充当where条件的字段必须要添加索引
2、全文索引对表的类型的限制
3、什么样的字段适合添加什么类型的索引
4、提升表的查询语句的速度
5、索引不是由用户来调用，而是执行查询时，自动提升查询速度
6、一个字段最多只能有一个索引
一张表可以有多个索引，但是索引不能同名
索引总结：
1、作用
2、分类
3、索引对字段的限制
4、什么样的字段适合添加什么样的索引
5、创建
6、删除索引
视图操作
创建视图语法格式
create view 视图名
as
select查询语句;
例子
create view myview
as
select claId,claName,schName from classInfo,schoolInfo;
create view bbsInfoView
as
select * from bbsInfo;
查看现存的所有视图
1、利用查看表的语句
show tables;
2、通过information_schema库--->views表 查看
select table_schema,table_name from views;
select table_schema,table_name from information_schema.views;
删除视图
1、删除库(视图所在的库)
drop database 库名;
2、删除视图
drop view 视图名;
修改视图
alter view 视图名
as
select语句;
例子
alter view myview
as
select claId,claName,schName from classInfo a,schoolInfo b
where a.schId=b.schId;
使用视图：把视图当表用
select * from 视图名;
例子
select * from myview;
select * from myview where claId=3;
select * from myview limit 0,2;
select * from myview order by claId;
使用视图的场合
1、一个多表查询语句在网站开发中使用的次数超3次
2、一个多表查询语句，添加分页功能
视图注意：
1、视图中只能存放select查询语句
2、把视图当表用
3、视图可以执行insert、update、delete操作
建议不要对视图执行insert、update、delete操作
视图总结：
1、视图作用
2、创建视图
3、查看视图
4、修改视图
5、使用视图
6、使用视图的场合
数据操作
增
添加记录
insert into 表名(字段,字段,字段...)values(值,值,值...);
insert into 表名 values(值,值,值...);//所有字段都要添加
复制数据到新表
1、假设两个表的表结构完全一样
insert into 新表 select * from 原表;
insert into bbsInfo2 select * from bbsInfo;
2、假设两个表的表结构不一样
insert into 新表(字段,字段...) select 字段,字段... from 原表;
insert into bbsInfo2 select bbsId,clickTimes,bbsTime from bbsInfo;
删
delete from 表名 where 条件;
# delete： 慢 可恢复 支持事务 不恢复id
# truncate： 快 不可恢复 不支持事务 恢复id
# 清空表
delete from 表名;//支持事务,进入日志文件
# 截段表
truncate table 表名;//不支持事务
改
update 表名 set 字段=值,字段=值... where 条件;
update 表名 set 字段=值,字段=值...;
查
运算符
逻辑运算符：and or not
比较运算符：< <= > >= = != <>
普通查询
select * from 表名;
select 字段,字段... from 表名;
//查询语句查询出来的结果：结果集、临时表
去掉重复值的查询
select distinct 字段,字段 from 表名;
条件查询
select * from 表名 where 条件;
select 字段... from 表名 where 条件;
排序
select * from 表名 order by 字段; #升序
select * from 表名 order by 字段 desc; #降序
select * from 表名 order by 字段,字段;
select * from 表名 order by 字段 desc,字段 desc;
select * from 表名 where 条件 order by 字段;
聚合函数(聚合查询)：统计查询
聚集函数 意义
SUM ( ) 求和
AVG ( ) 平均值
COUNT ( ) 表达式中记录的数目
COUNT (* ) 计算记录的数目
MAX 最大值
MIN 最小值
VAR 方差
STDEV 标准误差
FIRST 第一个值
LAST 最后一个值
个数统计
select count(*) from 表名;
select count(字段) from 表名;
总和统计
select sum(字段) from 表名;
平均数统计
select avg(字段) from 表名;
最大值统计
select max(字段) from 表名;
最小值统计
select min(字段) from 表名;
一个语句包含多个聚合函数
select sum(字段),max(字段) from 表名;
注意：除了count以外,所有的聚合函数中的字段必须为数字类型
给字段起别名
select 字段 as 别名 from 表名;
select 字段,字段 as 别名,字段 from 表名;
select 聚合函数(字段) as 别名 from 表名;
给表起别名
select * from 表 as 别名;
select * from 表 别名;
分组
1、查看某一字段有多少个不同的值
select 字段1 from 表名 group by 字段1;
select 聚合函数(字段1) from 表名 group by 字段2;
分页
select * from 表名 limit 起始值,条数;
select * from 表名 where 条件 order by 字段 limit 起始值,条数;
模糊查询(站内搜索)
%：任意长度的任意字符串
_：任意一个字符
使用 \_ 代表 _ (如果要查询的字符是下划线的话) 除了可以转移_ 还可以转义 \0 \' \" \b(退格) \n(换行) \r(回车) \t(tab) \\ \%
select * from 表名 where 字段 like '%_内容';
select * from 表名 where 字段 like '%_内容' and|or 条件;
select * from 表名 where 字段 like '%_内容' order by 字段;
select * from 表名 where 字段 like '%_内容' limit 起始值,条数;
select * from 表名 where userName like '张%';
select * from 表名 where userName like '张_';
select * from 表名 where userName like '%三%';
多表查询
1、内连接：将多张表的字段合为一张表
查询两张表
select * from 表1 inner join 表2 on 关联的字段相等;
查询多张表
select * from 表1 inner join 表2 on 关联的字段相等
inner join 表3 on 关联的字段相等
inner join 表4 on 关联的字段相等
内连接另一种使用方式
select * from 表1,表2,表3... where 关联的字段相等;//使用这个比较方便
2、外连接：将多张表的字段合为一张表
左外连接：左表为主、右表为副//以left|right outer join为分界线分左表右表
select * from 表1 left outer join 表2 on 关联的字段相等;
右外连接：右表为主、左表为副
select * from 表1 right outer join 表2 on 关联的字段相等;
3、子查询(嵌套查询)：在一个sql语句中包含另一个查询语句
在查询语句的条件中出现了另一个查询语句
select * from 表1
where 字段 in (select 字段 from 表2 where 条件);
在删除语句的条件中出现了另一个查询语句
delete from 表1
where 字段 in (select 字段 from 表2 where 条件);
在修改语句的条件中出现了另一个查询语句
update 表1 set 字段=值,字段=值...
where 字段 in (select 字段 from 表2 where 条件);
在查询语句的*位置出现了另一个查询语句
select 字段,字段,(select 字段 from 表2 where 条件) as 别名 from 表1;
4、联合查询union：将多张表的记录合为一张表//短期内用不到大型网站使用
将多张表的记录合为一张表，去掉了重复记录
select * from 表1 union select * from 表2;
将多张表的记录合为一张表，不去掉重复记录
select * from 表1 union all select * from 表2;
例：在'WA'地区的雇员表中按头衔分组后，找出具有同等头衔的雇员数目大于1人 group by Title having Count(Title)>1
的所有头衔。
Select Title ,Count(Title) as Total
FROM Employees
Where Region = 'WA'
GROUP BY Title
HAVING Count(Title)>1
//做例子要用
学院表
create table schoolInfo
(
schId int auto_increment primary key,
schName varchar(100) not null
);
insert into schoolInfo(schName)values('理工学院');
insert into schoolInfo(schName)values('自动化学院');
insert into schoolInfo(schName)values('网络学院');
#班级表
create table classInfo
(
claId int auto_increment primary key,
schId int,
claName varchar(100) not null,
foreign key (schId) references schoolInfo(schId)
);
insert into classInfo(schId,claName)values(1,'php1401');
insert into classInfo(schId,claName)values(3,'php1402');
insert into classInfo(schId,claName)values(2,'php1403');
insert into classInfo(schId,claName)values(3,'php1404');
#学生表
create table student
(
stuId int auto_increment primary key,
claId int,
stuName varchar(20) not null,
foreign key (claId) references classInfo(claId)
);
insert into student(claId,stuName)values(1,'张三');
insert into student(claId,stuName)values(2,'李四');
insert into student(claId,stuName)values(3,'王五');
insert into student(claId,stuName)values(4,'赵六');
insert into student(claId,stuName)values(3,'田七');
insert into student(claId,stuName)values(3,'张大三');
insert into student(claId,stuName)values(2,'李小五');
触发器Trigger：数据库中的事件
触发器Trigger：数据库里的事件
作用：当用户对表进行insert、update、delete同时，是否要执行其他的操作
触发器的调用：
当用户对表进行insert、update、delete操作时，由系统自动调用
触发器的类型：insert、update、delete
触发器执行的时间：before、after
创建触发器
delimiter // #修改mysql定界符
drop trigger if exists 触发器名// #如果该触发器己经存，删除
create trigger 触发器名
before|after insert|update|delete
on 表名
for each row
begin
sql语句; #注意：begin和end之间，不能出现无意义的查询语句
sql语句;
sql语句;
end //
delimiter ;//将mysql定界符改回系统默认
删除触发器
1、删除触发器所在的库
drop database 库名;
2、删除触发器所在的表
drop table 表名;
3、删除触发器
drop trigger 触发器名;
触发器注意：
1、触发器是由系统自动调用
2、触发器中不能出现无意义的查询语句
3、触发器没有参数
触发器总结
1、触发器作用
2、触发器的执行时间
3、触发器的类型
4、一张表最多能添加三个触发器
5、创建
6、删除
7、查看现存的所有触发器
查看现存的所有触发器
information_schema库--->triggers表
trigger_schema：库名
trigger_name：触发器名
EVENT_MANIPULATION：触发器类型
event_object_table：表名
select trigger_schema,trigger_name,EVENT_MANIPULATION,event_object_table from triggers;
select trigger_schema,trigger_name,EVENT_MANIPULATION,event_object_table from information_schema.triggers;
触发器例子
create table userInfo
(
userName varchar(20) primary key,
password varchar(20) not null,
sex char(3) check(sex='男' or sex='女')
);
create table newsInfo
(
newsId int auto_increment primary key,
title varchar(100) not null,
newsTime timestamp default current_timestamp
);
#统计信息表
create table msgInfo
(
userCount int default 0,
newsCount int default 0
);
insert into msgInfo values(0,0);
#给userInfo表添加一个insert触发器
delimiter //
drop trigger if exists addUserTrigger//
create trigger addUserTrigger
after insert
on userInfo
for each row
begin
#将msgInfo表的userCount字段值加一
update msgInfo set userCount=userCount+1;
end //
delimiter ;
#给userInfo添加一个delete触发器
delimiter //
drop trigger if exists delUserTrigger//
create trigger delUserTrigger
after delete
on userInfo
for each row
begin
update msgInfo set userCount=userCount-1;
end //
delimiter ;
insert into userInfo values('张三','123456','男');
insert into userInfo values('李四','123456','女');
delete from userInfo where userName='张三';
触发器注意：
1、触发器依赖于表
2、触发器类型：insert、update、delete
3、在触发器中不能出现无意义的查询语句
4、一个触发器只能监听一个表的一个操作
5、触发器由系统自动调用
6、触发器不能传参
触发器总结：
1、触发器作用、原理
2、触发器类型
3、触发器的执行时间
4、创建触发器
5、删除触发器
6、查看现存的所有触发器
7、触发器使用场合
存储过程Procedure：数据库中的自定义函数
创建存储过程
delimiter //
drop procedure if exists 存储过程名//
create procedure 存储过程名()
begin
sql语句;
sql语句;
sql语句;
end //
delimiter ;
删除存储过程
1、删除库
drop database 存储过程所在的库名;
2、删除存储过程
drop procedure 存储过程名;
查看现存的所有存储过程
mysql库--->proc表
select db,name from proc;
select db,name from mysql.proc;
调用存储过程
call 存储过程名();
call 存储过程名(值,值,值...);
call 存储过程名(值,值,@变量名,@变量名,@变量名);
select @变量名,@变量名...;
使用存储过程的场合: 操纵数据库时要执行多个sql语句
存储过程注意：
1、一次编写多次调用
2、参数：输入参数、输出参数
3、调用存储过程
存储过程总结：
1、作用
2、创建
3、删除
4、调用
5、参数
类拟于数据库中的输出语句
select '你好' as msg;
select 30 as age;
select current_timestamp as time;
例子：
delimiter //
drop procedure if exists hello//
create procedure hello()
begin
select current_timestamp as time;
end //
delimiter ;
call hello();
触发器(Trigger)与存储过程(Procedure)的区别
不同点 1、触发器由系统自动调用，存储过程必须由用户通过call来调用 2、触发器没有参数、存储过程可以有参数(输入、输出) 3、触发器依赖表、存储过程依赖于库 相同点 1、一次编写多次调用 2、都可以实现T-SQL编程
事务Transaction
事务特性：原子性、一致性、隔离性、持久性
开启一个事务
start transaction;
结束一个事务
commit;#事务成功
rollback;#事务失败
事务的最终状态：成功commit、失败rollback
回滚点
1、设置回滚点(你己经开启了事务)
savepoint 名;
2、回滚至某一个回滚点
rollback to 回滚点名;
注意：rollback to 仅仅只回滚到某一个回滚点，但并不会结束事务
例子：
start transaction;
delete from bbsInfo;
rollback;
select * from bbsInfo;
例子
start transaction;
delete from bbsInfo;
savepoint hello;
delete from userInfo;
rollback to hello;
select * from bbsInfo;#没有记录
select * from userInfo;#有记录
commit;或rollback;
事务的使用场合
1、调试客户的服务器上的数据库
2、存储过程(存储过程中包含了多个insert、update、delete语句)
不支持事务的sql语句
truncate table、create、alter、drop
只有InnoDB的存储引擎支持事务
事务总结
1、事务作用：保证数据的完整性
2、开启事务
3、结束事务
4、回滚点
5、不支持事务的语句
6、事务特性
Transaction-SQL编程 (事务编程)
Transaction-SQL编程 (Transaction：事务)
sql编程只能出现在：trigger、procedure
1、定义变量
declare 变量名 数据类型;
declare 变量名 数据类型 default 值;
注意：declare必须出现在procedure、trigger的最顶端
delimiter //
drop procedure if exists hello//
create procedure hello()
begin
declare a int;
declare b int default 30;
declare sex char(3) default '男';
declare userName varchar(20) default '张三';
select b,sex,userName;
end //
delimiter ;
2、给变量赋值
declare 变量名 数据类型 default 值;
set 变量名 = 值;
set 变量名 = (select语句);
select 字段 into 变量名 from 表名 where 条件;
delimiter //
drop procedure if exists hello//
create procedure hello()
begin
declare a int;
declare b varchar(50);
select count(*) into a from bbsInfo;
select title into b from bbsInfo where bbsId=3;
select a,b;
end //
delimiter ;
delimiter //
drop procedure if exists hello//
create procedure hello()
begin
declare a int;
declare b varchar(100);
set a = (select count(*) from bbsInfo);
set b = (select title from bbsInfo where bbsId=3);
select a,b;
end //
delimiter ;
delimiter //
drop procedure if exists hello//
create procedure hello()
begin
declare age int;
declare userName varchar(20);
set age = 100;
set userName = '田七';
select age,userName;
end //
delimiter ;
3、运算符
算术运算符：+ - * / %
比较运算符：< <= > >= = != <>
逻辑运算符：and or not
delimiter //
drop procedure if exists hello//
create procedure hello()
begin
declare a int default 10;
declare b float default 3.14;
declare c float;
set c = a*b;
select c;
end //
delimiter ;
delimiter //
drop procedure if exists hello//
create procedure hello()
begin
declare a int default 10;
declare b float;
declare c float;
#设置变量必须在全部变量声明完成后才能赋值
set b = 3.14;
set c = a*b;
select c;
end //
delimiter ;
4、判断语句
if 条件 then
sql语句;
elseif 条件 then
sql语句;
else
sql语句;
end if;
delimiter //
drop procedure if exists hello//
create procedure hello()
begin
declare age int default 40;
if age=1 then
select '出场亮相';
elseif age=10 then
select '天天向上';
elseif age=20 then
select '远大理想';
elseif age=30 then
select '基本定向';
elseif age=40 then
select '处处吃香';
else
select '不知道';
end if;
end //
delimiter ;
5、循环语句
while 条件 do
sql语句;
end while;
repeat
sql语句;
until 条件 end repeat;
别名:loop
sql语句;
if 条件 then
leave 别名;
end if;
end loop;
delimiter //
drop procedure if exists hello//
create procedure hello()
begin
declare i int default 0;
hello:loop
select i;
set i=i+1;
if i>5 then
leave hello;
end if;
end loop;
end //
delimiter ;
delimiter //
drop procedure if exists hello//
create procedure hello()
begin
declare i int default 0;
repeat
select i;
set i=i+1;
until i>5 end repeat;
end //
delimiter ;
delimiter //
drop procedure if exists hello//
create procedure hello()
begin
declare i int default 0;
while i<5 do
set i=i+1;
select i;
end while;
end //
delimiter ;
T-SQL编程总结：
1、sql编程可以出现的地方：trigger、procedure
2、定义变量
3、给变量赋值
4、运算
5、判断
6、循环(了解)
事务+存储过程的例子
#删除新闻分类
delimiter //
drop procedure if exists delNewsType//
create procedure delNewsType(tid int)
begin
declare a int;#存储受影响的行数
start transaction;#开启一个事务
delete from reviews
where articleId in (select articleId from newsArticles where typeId=tid);
delete from newsArticles where typeId=tid;
delete from newsTypes where typeId=tid;
set a = row_count();#获得上一个语句的受影响的行数
if a>0 then
commit;
else
rollback;
end if;
end //
delimiter ;
delimiter //
drop procedure if exists delNewsType//
create procedure delNewsType(tid int)
begin
declare a int;#存储一个变量值
start transaction;#开启一个事务
delete from reviews
where articleId in (select articleId from newsArticles where typeId=tid);
delete from newsArticles where typeId=tid;
delete from newsTypes where typeId=tid;
select count(*) into a from newsTypes where typeId=tid;
if a=0 then
commit;
else
rollback;
end if;
end //
delimiter ;
#通过articleId获得新闻的标题及内容
delimiter //
drop procedure if exists getNews//
create procedure getNews(aid int,out t varchar(100),out c text)
begin
set t = (select title from newsArticles where articleId=aid);
set c = (select content from newsArticles where articleId=aid);
end //
delimiter ;