1.Mysql 的正则表达式

admin管理员组

文章数量:1561870

2018-11-26 MySQL 学习笔记 周谦
MySQL 学习笔记
MySQL 的安装
2.1 MySQL 安装与配置
2.1.1 MYSQL 的安装
１、打开下载的 mysql 安装文件 mysql-5.5.27-winx64.msi，双击运行。
２、选择安装类型，有“Typical（默认）”、“Complete（完全）”、“Custom（用户
自定义）”三个选项，选择“Custom”，按“next”键继续。
３、点选“Browse”，手动指定安装目录。
４、填上安装目录，我的是“F:\Server\MySQL\MySQL Server 5.0”，也建议不要放在与
操作系统同一分区，这样可以防止系统备份还原的时候，数据被清空。按“OK”继续。
确认一下先前的设置，如果有误，按“Back”返回重做。按“Install”开始安装。
５、正在安装中，请稍候，直到出现下面的界面, 则完成 MYSQL 的安装
MySQL 的一些常用命令:
进入 MySQL 命令：
在命令行：
mysql -u root -p
exit 退出
直接进入
mysql -uroot -p123456
如果连接远程的计算机:
mysql -h 远程 ip -u 用户 -p 密码
注意 参数和值之间没有空格
show databases;
2018-11-26 MySQL 学习笔记 周谦
show create database 库名;
查看当前库的创建语句
库和表,以及 java 对象和表 之间的关系
show create table 显示表的创建语句;
2018-11-26 MySQL 学习笔记 周谦
\c 取消输入
\h 显示帮助
desc 查看表的结构
MySQL 第三方图形化工具:
Navicat
收费,免费试用,中文汉化的挺好
2018-11-26 MySQL 学习笔记 周谦
2018-11-26 MySQL 学习笔记 周谦
SQLyog
收费的,把每一项操作,都记录日志,图表
HeidiSQL
比较小巧,灵活
2018-11-26 MySQL 学习笔记 周谦
MySqlFront
2018-11-26 MySQL 学习笔记
SQL 基础命令
简介
SQL (Struct Query Language) 结 构 化 查 询 语 言 , 是 关 系 型 数 据 库 的 操 作 语 言 .
MySQL,Oracle,SQL Server 等关系数据库管理系统中使用的都是 SQL 语言, 在满足 SQL 规范
(ISO 标准)的基础上增加了各自独特的东西(方言).
SQL 语言的分类
DDL 数据定义语言: 定义结构, 创建,删除数据库,创建,修改,删除表,视图,索引等数据库
对象
DML 数据操作语言: 数据操作(增删改)
DCL 数据控制语言: 权限设置
DQL 数据查询语言: 数据的查询
数据库函数: 数学函数,三角函数,字符串函数,日期函数
数据库编程: 变量定义,条件判断,循环迭代等
创建和删除数据库
创建数据库
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification [, create_specification] …]
示例:
– 显示所有的数据库
show databases;
2018-11-26 MySQL 学习笔记 周谦
– 显示数据库的创建语句
show create database igeek;
– 创建数据库
create database igeek;
– 创建的时候判断是否存在
create database if not exists igeek;
– 创建数据库的时候指定字符编码
create database if not exists igeek character set utf8;
create database if not exists igeek default charset utf8;
删除数据库
语法:
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
示例:
– 删除数据库
drop database igeek;
– 先判断,再删除
drop database if exists igeek;
MySQL 基本的数据类型
Java 类型 MySQL 类型
int 或 Integer int
float 或 Float float
double 或 Double double 或 float
String char 固定长度的字符串
varchar 可变长 8000 个字符
text 可变长 很长
Java.util.Date date
2018-11-26 MySQL 学习笔记 周谦
创建和删除表
创建表
语法:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,…)]
[table_options] [select_statement]
示例:
– 使用库
use test;
– 创建表
create table student(
sid int,
sname varchar(20),
sex char(2)
);
约束简介
主键约束
在该表中列的值唯一,不能为空,建议每一张表都有一个 主键约束
一张表只能有一个主键约束
唯一约束
在该表中列的值唯一
非空约束
不能为空
默认值约束
如果在插入值的时候,该列未插入值,或插入的值为 null,则使用默认值
检查约束
按照用户的具体要求自定义约束,例如:性别只能是男和女, MySQL 不支持
外键约束
表和表之间的关系,一张表的某一列的值 引用的另外一张表的某列的值
2018-11-26 MySQL 学习笔记 周谦
创建表的时候添加约束
– 添加约束 (主键约束,唯一约束,非空约束,默认值约束,检查约束,外键约束)
create table if not exists student(
sid int primary key, – 主键约束
sname varchar(20) not null, – 非空约束
sex char(2) default ‘男’ – 默认值约束
);
删除表
语法:
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] …
[RESTRICT | CASCADE]
示例:
use test;
– 删除表
drop table tablename1;
drop table if exists tablename1;
插入数据
INSERT 用于向一个已有的表中插入新行。INSERT…VALUES 形式的语句根据明确指定的值插入
行。
INSERT 语法
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,…)]
VALUES ({expr | DEFAULT},…),(…),…
[ ON DUPLICATE KEY UPDATE col_name=expr, … ]
2018-11-26 MySQL 学习笔记 周谦
或：
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, …
[ ON DUPLICATE KEY UPDATE col_name=expr, … ]
或：
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,…)]
SELECT …
[ ON DUPLICATE KEY UPDATE col_name=expr, … ]
示例:
– 查看库的创建语句
SHOW CREATE DATABASE test;
USE test;
– 查看表的创建语句
SHOW CREATE TABLE student;
drop table if exists student;
create table if not exists student(
sid int primary key, – 主键约束
sname varchar(20) not null, – 非空约束
sex char(2) default ‘男’ – 默认值约束
);
– insert into 表名(列 1,列 2,…) values(列 1 对应的值,列 2 对应的值,…)
insert into student(sid,sname,sex) values(1001,‘张三’,‘男’);
insert into student(sid,sname,sex) values(1002,‘李四’,‘女’);
insert into student(sid,sname,sex) values(1003,‘王五’,‘女’);
insert into student(sid,sname,sex) values(1004,‘贾六’,‘女’);
insert into student(sid,sname,sex) values(1005,‘田七’,‘女’);
insert into student(sid,sname,sex) values(1006,‘德化’,‘男’);
– MySQL 所独有的可以插入多条
insert into student(sid,sname,sex)
values (1001,‘张三’,‘男’),(1002,‘李四’,‘女’),
2018-11-26 MySQL 学习笔记 周谦
(1003,‘王五’,‘女’),(1004,‘贾六’,‘女’),(1005,‘田七’,‘女’),(1006,‘德化’,‘男’);
– 插入部分列
insert into student(sid,sname) values(1007,‘小马’);
– 如果按照顺序插入所有列 可以省略列名
insert into student values(1008,‘小刘’,‘女’);
删除数据
语法:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_definition]
[ORDER BY …]
[LIMIT row_count]
删除数据的时候一般会添加一些条件,如果不添加任何条件,会删除表中的所有数据.
示例:
use test;
– 删除数据,如果不添加任何条件 会删除该表的所有数据
delete from student;
– 按照条件删除 可以使用运算符 >,<,=,>=,<=,<> 等
– 删除编号为 1003 的学生
delete from student where sid = 1003;
– 连接多个条件 可以使用 and ,or
– 删除 编号小于 1004 ,同时 性别为 女的学生
delete from student where sid < 1004 and sex=‘女’;
修改数据
语法:
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2 …]
[WHERE where_definition]
2018-11-26 MySQL 学习笔记 周谦
[ORDER BY …]
[LIMIT row_count]
示例:
– 修改数据,一般会添加条件,如果没有条件则修改所有数据
update student set sex = ‘男’;
– 把 田七 的性别 修改为 女
update student set sex = ‘女’ where sname=‘田七’;
– 把 编号为 1006 的学生 姓名修改为 景甜 性别 女
update student set sname = ‘景甜’,sex = ‘女’ where sid=1006;
查询数据
语法:
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr, …
[INTO OUTFILE ‘file_name’ export_options
| INTO DUMPFILE ‘file_name’]
[FROM table_references
[WHERE where_definition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], … [WITH ROLLUP]]
[HAVING where_definition]
[ORDER BY {col_name | expr | position}
[ASC | DESC] , …]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[FOR UPDATE | LOCK IN SHARE MODE]]
2018-11-26 MySQL 学习笔记 周谦
示例:
– 查询数据
– 查询学生表的 所有列() 和 所有行(不添加条件)
select * from student;
– 查询所有学生的编号和姓名
select sid,sname from student;
– 查询 张三 的性别和学号
select sid,sname,sex from student where sname=‘张三’;
– 在列上,使用别名
select
sid as 学生编号,
sname as 学生姓名,
sex as 学生性别
from student;
–在列上,使用别名 2
select
sid 学生编号,
sname 学生姓名,
sex 学生性别
from student
– 给表使用别名
select
st.sid 学生编号,
st.sname 学生姓名,
st.sex 学生性别
from student as st – as 可以省略
where st.sid < 1005;
– 查询 学生性别 使用 distinct 去除重复
select DISTINCT sex from student;
– 分页查询 limit (MySQL 所独有的)
/
limit 数字 表示显示数量
limit 起始行位置(从 0 开始),显示数量
*/
select * from student limit 2,2;
2018-11-26 MySQL 学习笔记 周谦
SQL 语言构成
MySQL 注意事项:
命令提示符下:

1. SQL 语句以分号(;)作为语句的结束.
2. 出现箭头(->)符号表示语句没有输入完成
3. SQL 语句在常规情况下是不区分大小写的.
4. 输入未完成,终止输入可以输入 \c
   MySQL 中的数据类型:
   整型
   2018-11-26 MySQL 学习笔记 周谦
   2018-11-26 MySQL 学习笔记 周谦
   创建数据库
   查看所有数据库
   Show databases;
   2018-11-26 MySQL 学习笔记 周谦
   创建数据库
   Create database 数据库名 [选项]
   删除数据库
   drop database if exists igeek;
   E-R 图
   2018-11-26 MySQL 学习笔记 周谦
   表的管理
   创建表
   示例:
   use igeek;
   –创建表
   create table users(
   id bigint,
   sname varchar(20),
   age tinyint default 20,
   sex char(2) default ‘男’,
   high decimal(5,2),
   birthday date,
   getTime timestamp,
   info text,
   pic blob,
   primary key(id)
   );
   select * from users;
   /*
   timestamp 如果不插入值,默认添加系统当前时间
   /
   –插入测试数据
   insert into users(id,sname,high,birthday)
   values
   (1,‘极客’,189.456,‘1995-5-6’),
   (2,‘张三’,175.12,‘1993-3-6’),
   (3,‘李四’,190.78,‘1992-7-16’);
   2018-11-26 MySQL 学习笔记 周谦
   删除表
   语法:
   Drop table [if exists] 表名;
   查看表的信息
   查看表的结构:
   Desc 表名
   查看表的创建语句
   Show create table 表名 [\g];
   2018-11-26 MySQL 学习笔记 周谦
   SQL 中的约束
   添加约束的作用: 保证数据的完整性,可靠性,一致性.
   添加完整性约束
   主键约束
   每一张表只能有一个主键,唯一和非空的,主键分为单列主键和组合主键
   单列主键,主键指定的是一列,该列唯一和非空的.
   组合主键,主键指定了多列, 多列组合唯一和非空
   示例:
   use igeek;
   – 删除表
   drop table if exists student;
   /
   分析 ER 图
   */
   –学生表
   create table student(
   sid int primary key,
   sname varchar(20),
   sex varchar(2) default ‘男’,
   birthday date default ‘1995-1-1’,
   tel varchar(11) default ‘13012345678’,
   address varchar(500) default ‘地址不详’
   );

insert into student(sid,sname)
values(1001,‘张三’),(1002,‘李四’),(1003,‘王五’);
–科目表
create table course(
cid int primary key,
cname varchar(20) not null
);
insert into course(cid,cname)
values(1,‘语文’),(2,‘数学’),(3,‘英语’);
– 成绩表
create table score(
s_sid int, – 学生编号
s_cid int, – 科目编号
scoreNum float default 0.0,
primary key(s_sid,s_cid)
);
select * from student;
select * from course;
select * from score;
insert into score(s_sid,s_cid,scoreNum)
values(1001,1,85),(1001,2,75),(1001,3,65);
insert into score(s_sid,s_cid,scoreNum)
values(1002,1,60),(1002,2,95),(1002,3,55);
– 违反了组合组件的组合唯一
insert into score(s_sid,s_cid,scoreNum) values(1001,3,66);
自增
Auto_increment 是 Mysql 所独有的,从 1 开始,每次插入的时候自动加 1
只能出现在主键上,同时类型只能是整型.
示例:
– 自增
2018-11-26 MySQL 学习笔记 周谦
create table testAutoIncrement(
id int auto_increment primary key,
name varchar(20)
);
insert into testAutoIncrement(name) values(‘aaa’);
insert into testAutoIncrement(name) values(‘bbb’);
insert into testAutoIncrement(name) values(‘ccc’);
insert into testAutoIncrement(name) values(‘ddd’);
insert into testAutoIncrement(id,name) values(null,‘ddd’);
insert into testAutoIncrement values(null,‘fff’);
insert into testAutoIncrement values(null,‘eee’);
select * from testAutoIncrement;
唯一约束
Unique 约束列值不能重复,比如身份证号码,登录号码,邮箱号等等.
use igeek;
– 创建表 给 user_id 唯一约束
create table person(
id int auto_increment primary key,
user_id varchar(18) unique, – 唯一约束
name varchar(20),
sex char(2)
);
insert into person(user_id,name,sex)
values(‘110123199516151234’,‘张三’,‘男’);
– 唯一约束 可以插入 null 值
insert into person(user_id,name,sex)
values(null,‘lisi’,‘男’);
insert into person(user_id,name,sex)
values(null,‘wangwu’,‘男’);
2018-11-26 MySQL 学习笔记 周谦
– 插入重复的值,出现错误,不允许插入
insert into person(user_id,name,sex)
values(‘110123199516151234’,‘贾六’,‘男’);
select * from person;
外键约束
示例:
/*
外键约束 示例
/
– 删除
drop table if exists student,course,score;
–学生表
create table student(
sid int primary key,
sname varchar(20),
sex varchar(2) default ‘男’,
birthday date default ‘1995-1-1’,
tel varchar(11) default ‘13012345678’,
address varchar(500) default ‘地址不详’
);
2018-11-26 MySQL 学习笔记 周谦
insert into student(sid,sname)
values(1001,‘张三’),(1002,‘李四’),(1003,‘王五’);
–科目表
create table course(
cid int primary key,
cname varchar(20) not null
);
insert into course(cid,cname)
values(1,‘语文’),(2,‘数学’),(3,‘英语’);
– 成绩表
/
添加外键约束,不指定外键的名称,mysql 会生成一个外键约束的名称
/
create table score(
s_sid int, – 学生编号
s_cid int, – 科目编号
scoreNum float default 0.0,
primary key(s_sid,s_cid),-- 组合主键
foreign key(s_sid) references student(sid), – 外键,引用 student 表的
sid
foreign key(s_cid) references course(cid) – 外键,引用 student 表的 sid
);
/
添加主键和外键,指定名称
命名规范:
主键约束 pk_表名_列名
唯一约束 un_表名_列名
外键约束 fk_表名_列名
*/
create table score(
s_sid int, – 学生编号
s_cid int, – 科目编号
scoreNum float default 0.0,
constraint PK_score_s_sid_s_cid primary key(s_sid,s_cid),-- 组合主键
constraint FK_student_score_sid foreign key(s_sid) references
student(sid), – 外键,引用 student 表的 sid
constraint FK_course_score_cid foreign key(s_cid) references
2018-11-26 MySQL 学习笔记 周谦
course(cid) – 外键,引用 student 表的 sid
);
drop table score;
------缩写(部分的 MySQL 的版本支持不好)---------
create table score(
s_sid int references student(sid), – 学生编号 外键
s_cid int references course(cid), – 科目编号 外键
scoreNum float default 0.0,
primary key(s_sid,s_cid)-- 组合主键
);
select * from student;
select * from course;
select * from score;
insert into score(s_sid,s_cid,scoreNum)
values(1001,1,85),(1001,2,75),(1001,3,65);
insert into score(s_sid,s_cid,scoreNum)
values(1002,1,60),(1002,2,95),(1002,3,55);
– 测试外键约束,如果插入的值 在引用的表中不存在,则插入失败
insert into score(s_sid,s_cid,scoreNum)
values(1003,5,60);
– 不能修改和删除 主表的数据,而导致 从表(子表) 数据孤立
update student set sid = 1005 where sid=1001;
delete from student where sid = 1001;

update student set sid = 1008 where sid=1003;
2018-11-26 MySQL 学习笔记 周谦
修改表
MySQL 的数据库引擎
查看当前数据库支持的引擎
Show engines;
2018-11-26 MySQL 学习笔记 周谦
InnoDB 引擎
MyISAM 引擎
MEMORY 引擎
数据存储在内存中的,定义结构, 因为数据存储在内存中,所以读写的效率很高,缺点服务器重
2018-11-26 MySQL 学习笔记 周谦
启,重置,数据容易丢失.
基本查询
简单语法:
示例:
– 基本查询
/*
select 查询,指定显示的列,可以使用 * 通配符
where 表示条件,筛选
order by 排序 (asc 升序 |desc 降序) 默认 asc
/
select
sid,sname,sex
from student
where sid < 9999
order by sid desc;
2018-11-26 MySQL 学习笔记 周谦
插入数据
简单语法:
示例
select * from student;
desc student;
insert into student(sid,sname) values(1003,‘王五’);
insert into student(sid,sname) values(1004,“极客”);
插入多条数据
– 插入多条数据
desc student;
show create table student;
CREATE TABLE student2 (
sid int(11) NOT NULL,
sname varchar(20) DEFAULT NULL,
sex varchar(2) DEFAULT ‘男’,
PRIMARY KEY (sid)
2018-11-26 MySQL 学习笔记 周谦
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
– 通过查询另外一张表,给表插入数据 可以用来备份
insert into student2(sid,sname,sex)
select sid,sname,sex from student where sid <1005;
select * from student;
select * from student2;
修改数据
语法
一般来说,修改数据的时候都需要添加 条件,如果没有添加 where 条件,则修改该表中的所有
数据.
示例
– 修改数据
select * from student2;
– 把编号为 1002 的用户的 姓名改为 流川枫 性别 男
update student2 set sname=‘流川枫’,sex=‘男’ where sid=1002;
2018-11-26 MySQL 学习笔记 周谦
删除数据
语法:
示例:
– 删除数据
– 如果不添加任何条件则删除所有数据
delete from student2;
– 删除编号为 1003 的学生
delete from student2 where sid = 1003;
select * from student2;
数据库设计的范式
2018-11-26 MySQL 学习笔记 周谦
第一范式
每一列的原子性
第二范式
每一张表描述一件事情
2018-11-26 MySQL 学习笔记 周谦
第三范式
在表中不能存在传递依赖.
SQL 查询
DML 数据操作语言, CRUD
DQL 数据查询语言, 在对于数据库的操作中,使用频率最高的是 select 查询.
什么是查询
2018-11-26 MySQL 学习笔记 周谦
Select 查询
语法
示例 1
–SQL 查询
select sid,sname,sex from student;
– 使用固定列
select ‘学生’ 学生信息 ,sid,sname,sex from student;
– 查询可以进行计算 基本算术运算
select 1+1
select (3+2-5)3/2
/
0011 << 2
12^3 + 12^2 + 02^1 + 0*2^0 = 12
1100 = 12
/
select 3<<2
2018-11-26 MySQL 学习笔记 周谦
– 查询可以添加固定列
select ‘1+1=’, 1+1
– 可以调用函数
select now()
– 明年的今天
SELECT ADDDATE(now(), 365);
select * from student;
insert into student(sid,sname)
values(1005,‘Rose’),(1006,‘Jeck’);
select sid,UPPER(sname) from student;
select sid,lower(sname) from student;
示例 2
– 去重复 列
select distinct tel from student;
– 去除重复的行
select distinctrow tel,birthday,address from student;
– 通过 limit 限制显示的数量
select * from student limit 2;
– 起始位置,数量
select * from student limit 0,2;
/
使用 limit 分页显示
总记录数
总页数
每页显示数量 = 3
当前页 1
2018-11-26 MySQL 学习笔记 周谦
页码 begin
1 0 (1-1)*3
2 3 (2-1)*3
3 6 (3-1)*3
begin = (当前页-1)*每页显示数量
*/
– 显示第 1 页
select * from student limit 0,3;
– 显示第 2 页
select * from student limit 3,3;
– 显示第 3 页
select * from student limit 6,3;
使用 where 条件中的谓词
Between and
Not between and
In
Not in
like
示例 1
– 别名 列
select sid as ‘学生编号’,sname as ‘姓名’ from student;
select sid ‘学生编号’,sname ‘姓名’ from student;
– 别名 表
select st.sid,st.sname from student as st
– 排序(asc|desc) 默认升序 asc
select * from student order by sid;
2018-11-26 MySQL 学习笔记 周谦
select * from student order by sid desc;
– 多列排序, 首先按照 sname 升序排序,如果 sname 相同,再按照 sid 降序排序
select * from student order by sname asc,sid desc;
– 查询 sid 小于 1005 的学生
select * from student where sid<1005 ;
– 查询 sid 大于 1002 ,小于 1006 的学生
select * from student where sid>1002 and sid<1006 ;
– 查询 sid 大于等于 1002 ,小于等于 1006 的学生
select * from student where sid>=1002 and sid<=1006 ;
– 等同
select * from student where sid between 1002 and 1006 ;
– 查询 sid 不在{1002-1006}之间 的学生
select * from student where sid<1002 or sid>1006 ;
select * from student where not (sid>=1002 and sid<=1006) ;
select * from student where sid not between 1002 and 1006 ;
– 查询 北京和上海以及重庆的学生
select * from student
where address=‘北京’ or address=‘上海’ or address=‘重庆’;
– 等同
select * from student where address in(‘北京’,‘上海’,‘重庆’);
– 查询 不在 北京和上海以及重庆的学生
select * from student where address not in(‘北京’,‘上海’,‘重庆’);
–通过 not 取反
select * from student
where not (address=‘北京’ or address=‘上海’ or address=‘重庆’);
–不使用取反
select * from student
where address != ‘北京’ and address!=‘上海’ and address!=‘重庆’;
select * from student
where address <> ‘北京’ and address<>‘上海’ and address<>‘重庆’;
2018-11-26 MySQL 学习笔记 周谦

示例 2
– 模糊查询
/*
like 主要针对的是 字符串
如果不使用通配符, like 的结果 和 = 一样
通配符
% 表示任意字符,任意多个
_ 表示任意的一个字符
/
–查询所有姓王的学生, 姓名以 王 开头,之后随意
select * from student where sname like ‘王%’;
–查询所有姓王的学生, 姓名以 王 开头, 两个字的
select * from student where sname like ‘王_’;
– 结尾 以 五结尾的
select * from student where sname like ‘%五’;
– 包含
select * from student where sname like ‘%五%’;
select * from student where sname like ‘五’;
– 查询 info 列为 null 的用户
select * from users where info is null;
– 查询 info 列不为 null 的用户
select * from users where info is not null;
2018-11-26 MySQL 学习笔记 周谦
使用正则表达式
示例
–查询所有 三 结尾的学生,
select * from student where sname regexp ‘三 ′ ; − − 查 询 1995 出 生 的 学 生 s e l e c t ∗ f r o m s t u d e n t w h e r e b i r t h d a y r e g e x p ′ 199 5 ′ ; − − 查 询 所 有 三 个 字 的 学 生 , s e l e c t ∗ f r o m s t u d e n t w h e r e s n a m e r e g e x p ′ . 3 , &#x27;; -- 查询 1995 出生的学生 select * from student where birthday regexp &#x27;1995&#x27;; --查询所有 三个字 的学生, select * from student where sname regexp &#x27;^.{3,} ′;−−查询1995出生的学生select∗fromstudentwherebirthdayregexp′1995′;−−查询所有三个字的学生,select∗fromstudentwheresnameregexp′.3,’;
2018-11-26 MySQL 学习笔记 周谦
SELECT 'new\nline’ REGEXP 'new\.\line’;
分组查询
示例
– 分组查询
/
group by 分组: 值相同的为一组
/
– 查询 统计所有学生中 男生 和 女生的人数
select sex 性别,count(sex) 人数 from student group by sex;
– 统计所有学生的人数
select count() 人数 from student;
– 查询 统计所有学生中 男生 和 女生的人数 显示人数 大于 2 的 性别
select sex 性别,count(sex) 人数
from student
2018-11-26 MySQL 学习笔记 周谦
group by sex
having count(sex)>2;
– 成绩表
create table score(
s_sid int,
s_cid int,
scoreNum float default 0.0,
primary key(s_sid,s_cid),
foreign key(s_sid) references student(sid),
foreign key(s_cid) references course(cid)
);
select * from student;
select * from course;
select * from score;
– 测试数据
insert into score
values
(1001,1,65),(1001,2,75),(1001,3,62),
(1002,1,67),(1002,2,90),(1002,3,88),
(1003,1,65);
insert into score
values
(1004,1,89),(1004,2,80),
(1005,1,90),(1005,3,92);
– 查询 每个学生参加考试的科目数量
select
s_sid 学生编号, count() 科目数量
from score
group by s_sid;
/
having 对分组的信息再次的进行 筛选
只能出现在 group by 之后
/
– 查询 每个学生参加考试的科目数量 显示 参加科目小于 3 的
select
s_sid 学生编号, count() 科目数量
2018-11-26 MySQL 学习笔记 周谦
from score
group by s_sid
having count()❤️;
组函数或聚合函数
数据汇总:
count() 计数,可以针对任意的类型
sum()数值类型 求和
max() 数值类型 最大值
min() 数值类型 最小值
avg() 数值类型 平均值
示例
– 聚合函数(组函数)
/
分组查询一定会使用 聚合函数
/
– 单独使用 聚合函数
– 计算出学生的数量
select count() from student;
select count(sname) from student;
– 查询优化
select count(1) from student;
– 求出学生编号之和
select sum(sid) 学生编号之和 from student;
– 求出 学生编号 最大编号
select max(sid) 最大编号 from student;
– 求出 学生编号 最小编号
select min(sid) 最小编号 from student;
2018-11-26 MySQL 学习笔记 周谦
– 求出 学生编号 平均值
select avg(sid) 编号平均值 from student;
– 求出 学生编号 数量 求和 最大值 最小值 平均值
select count(1) 数量, sum(sid) 求和,
max(sid) 最大值,min(sid) 最小值,avg(sid)平均值
from student;
示例 2
/*
分组查询一定会使用 聚合函数
分类汇总
/
select * from score;
– 查询每一个参加考试的学生的 科目数量
select s_sid 学生编号,count(1) 科目数量
from score
group by s_sid;
– 错误示例
select s_sid 学生编号
from score
group by s_sid;
– 统计每个参加考试的学生 所有科目的平均分
select
s_sid 学生编号,
count(1) 科目数量 ,
sum(scoreNum) 总分,
avg(scoreNum) 平均分,
max(scoreNum) 最高分,
min(scoreNum) 最低分
from score
group by s_sid;
2018-11-26 MySQL 学习笔记 周谦
– 计算所有科目的 最高分,最低分,平均分
select
s_cid 科目编号,
max(scoreNum)最高分,min(scoreNum) 最低分,avg(scoreNum) 平均分
from score
group by s_cid;
with rollup
– with rollup
select
s_cid 科目编号,
max(scoreNum)最高分,min(scoreNum) 最低分,avg(scoreNum) 平均分
from score
group by s_cid
with rollup;
连接查询
连接分类
内连接
2018-11-26 MySQL 学习笔记 周谦
也叫做等值链接
外连接
左外联
右外联
全外联
示例
– 多表查询
/
连接查询
/
select * from student; – 7
select * from course; – 3
select * from score; – 11
– 连接查询 不添加条件 产生笛卡尔积
select * from student,course,score; – 73*11=231
内连接
在连接两张或两张以上的表的时候,添加连接条件,当值相等的时候,显示数据,不相等的
不显示.
示例
– 等值连接
select
st.sid,st.sname,sc.s_cid,sc.scoreNum
from student st,score sc
where st.sid = sc.s_sid;
– 使用等值连接 连接三张表
select
st.sid,st.sname,coame,sc.scoreNum
from student st,score sc,course co
where st.sid = sc.s_sid and sc.s_cid=co.cid
2018-11-26 MySQL 学习笔记 周谦
order by st.sid,coame
– 内连接
select
st.sid,st.sname,sc.s_cid,sc.scoreNum
from student st
inner join score sc on st.sid = sc.s_sid;
– 使用内连接 连接三张表
select
st.sid,st.sname,coame,sc.scoreNum
from student st
inner join score sc on st.sid = sc.s_sid
inner join course co on sc.s_cid = co.cid
order by st.sid,coame;
– 显示所有学生的语文成绩
select
st.sid,st.sname,coame,sc.scoreNum
from student st
inner join score sc on st.sid = sc.s_sid
inner join course co on sc.s_cid = co.cid
where coame=‘语文’
order by st.sid,coame;
左外联
左表作为主体 右表去匹配左表,如果匹配失败 则显示 null
示例
– 外连接
select * from score; --11
select * from student; – 7
2018-11-26 MySQL 学习笔记 周谦
select distinct s_sid from score;
– 左外联 左表作为主体 右表去匹配左表,如果匹配失败 则显示 null
/**
student 是左表,score 是右表,使用成绩表去匹配学生表
如果 student 学生存在的记录,在 score 成绩表中没有 匹配项,则显示 null
/
select
*
from student st
left outer join score sc on sc.s_sid=st.sid
– 查询所有学生的语文成绩
select
st.sid 学生编号, st.sname 姓名,
IFNULL(sc.s_cid,1) 科目编号,
IFNULL(sc.scoreNum,0) 成绩
from student st
left outer join score sc on sc.s_sid=st.sid and sc.s_cid = 1
右外联
右表作为主体 左表去匹配右表,如果匹配失败 则显示 null
示例
– 右外联
/student 是右表,使用 score 左表去匹配 右表/
select
st.sid 学生编号, st.sname 姓名,
IFNULL(sc.s_cid,1) 科目编号,
IFNULL(sc.scoreNum,0) 成绩
from score sc
right outer join student st on sc.s_sid=st.sid and sc.s_cid = 1
2018-11-26 MySQL 学习笔记 周谦
自连接
表自己连接自己
– 查询一下 和 极客 同一天生日的学生 (自连接)
select st2. from student st1
inner join student st2 on st1.birthday = st2.birthday
where st1.sname = ‘极客’ and st2.sname <> ‘极客’;
子查询
子查询属于高级查询,一个查询中嵌套了另外的一个查询
在子查询中使用 =,<,>,!= 只能返回一个值
如果 内部的查询没有返回值 或者 返回了多个值 就会出错
如果多个值就需要 使用关键词
in ,not in,all, exists ,not exists
示例 1
– 查询一下 和 极客 同一天生日的学生 (子查询)
select * from student st
where st.birthday =
(select st1.birthday from student st1 where st1.sname = ‘极客’)
and st.sname <> ‘极客’;
示例 2
– 查询 和 Rose 以及王维 同第一个地方的学生
select * from student
where
address in
(select address from student where sname in (‘Rose’,‘王维’));
2018-11-26 MySQL 学习笔记 周谦
– any 和 all
– 学号比张三 大的同学
select * from student
where sid > (select sid from student where sname=‘张三’);
– 学号比班上女同学 都 大的同学 (比所有的女同学都大,比最大的都大)
select * from student
where sid > all (select sid from student where sex=‘女’);
– 学号比班上女同学 大的同学 (比所有的女同学 中任意的一个大的)
select * from student
where sid > any (select sid from student where sex=‘女’)
and sex != ‘女’
示例 3
– 查询所有学生的语文成绩
select
学生编号,姓名,
coame 课程,
成绩
from
(
select
st.sid 学生编号, st.sname 姓名,
IFNULL(sc.s_cid,1) 科目编号,
IFNULL(sc.scoreNum,0) 成绩
from student st
left outer join score sc on sc.s_sid=st.sid and sc.s_cid = 1
) tb1
inner join course co on tb1.科目编号 = co.cid;
MySQL 中的函数
2018-11-26 MySQL 学习笔记 周谦
数学函数
示例
– 数学函数
– 绝对值
select abs(-1)
– SMITH 工资 和 平均工资 差多少钱
select
abs(
(select sal from emp where ename=‘SMITH’)

2018-11-26 MySQL 学习笔记 周谦
(select avg(sal) from emp)) as 差多少钱
– 圆周率
select pi();
– 计算 半径为 5 的圆的面积
select pi()55 面积;
– 平方根
select sqrt(3);
– 取余
select 10/3;
select mod(10,3)
/*
取整
*/
– 向上取整
select ceil(5.654);
select ceiling(5.654);
select ceil(5.154);
– 向下取整
select floor(5.654);
select floor(5.154);
– 四舍五入
select round(5.654);
select round(5.154);
select round(5.6558,3); – 保留小数
select round(5.6,3); – 如果小数点位数不够,补 0
– 随机函数
select rand(); – 随机 0~1 之间的一个数
select rand(1000); – 指定随机的因子
– 随机产生一个 0-100 之间的整数
select floor(rand()100);
2018-11-26 MySQL 学习笔记 周谦
– 随机产生一个 100-200 之间的整数
select floor(rand()100+100);
select floor(rand()(200-100)+100);
– 随机产生一个 25-88 之间的整数
select floor(rand()(88-25)+25);
– 求幂
select pow(2,3);
select power(2,3);
select exp(2); – 自然对数低
– 对数
select log(10);
select ln(10); --自然对数
– 三角函数
–正弦
SELECT SIN(1);
–反正弦
SELECT ASIN(1);
–余弦
SELECT COS(0);
–反余弦
SELECT ACOS(1);
–反正切
SELECT ATAN(-2,2);
2018-11-26 MySQL 学习笔记 周谦
字符串的函数
示例
– 字符串函数
– 计算字符串的字符数量
select char_length(‘sadfasdfjkasldjflaksd 中文’);
2018-11-26 MySQL 学习笔记 周谦
– 计算字符串的字符长度 编码有关 UTF8 一个汉字 3 个长度,GBK 2 个
select length(‘sadfasdfjkasldjflaksd 中文’);
– 拼接字符串
select concat(‘极客’,‘帅哥’,‘美女’,‘高富帅’,‘白富美’,‘傻白甜’);
– 拼接字符串,使用指定字符连接
select concat_ws(’-’,‘极客’,‘帅哥’,‘美女’,‘高富帅’,‘白富美’,‘傻白甜
‘);
– 插入和替换字符串 从 1 开始
– insert(原始字符串,插入位置,替换个数,将要插入的字符串)
select insert(‘ABCDEFGHIJKLMN’,4,0,’@@’);
– 替换
select insert(‘ABCDEFGHIJKLMN’,4,5,’@@’);
– 删除
select insert(‘ABCDEFGHIJKLMN’,4,5,’’);
– 大小写转换
–转换为大写
select upper(‘abcDEfghiJKLMN’);
–转换为小写
select lower(‘abcDEfghiJKLMN’);
– 从首尾 提取字符串
– 提取前 3 个
select left(‘ABCDEFGHIJKLMN’,3);
– 提取后 3 个
select right(‘ABCDEFGHIJKLMN’,3);
– 取得所有学生的姓
use igeek;
select distinct left(sname,1) 姓 from student;
– 取得所有学生的姓和名
select left(sname,1) 姓,right(sname,char_length(sname)-1) 名
2018-11-26 MySQL 学习笔记 周谦
from student;
– 翻转
select reverse(‘ABC’);
select reverse(sname) from student;
– 返回多个字符串的 某一个
select elt(2,‘AA’,‘BB’,‘CC’,‘DD’,‘EE’,‘FF’);
use test1;
– 返回 工资第二高的员工 姓名
select ename from emp order by sal desc limit 1,1;
–select elt(2,(select ename from emp order by sal));
– 字符串的填充
– 开头填充 左侧
select lpad(‘ABC’,5,‘0’);
select lpad(‘ABC’,10,’#’);
select lpad(sal,4,0) from emp order by sal
– 末尾填充 右侧
select rpad(‘ABC’,5,‘0’);
select rpad(‘ABC’,10,’#’);
– 去除字符串中的空格 去除左侧,右侧,两侧
select (’ ABC ‘) k
– 去除左侧的空格
select ltrim(’ ABC ‘) k
– 去除右侧的空格
select rtrim(’ ABC ‘) k
– 去除两侧的空格
select trim(’ ABC ‘) k
– 重复字符串
select repeat(‘我喜欢你 ‘,10);
– 替换
select replace(‘我喜欢你’,‘喜欢’,‘love’);
2018-11-26 MySQL 学习笔记 周谦
select replace(‘asdf,asdfac,sdfa,sdfas,dfa’,’,’,’#’);
– 比较字符串 单个字符逐个 比较的是自然顺序 a,b,c,d 返回 -1,0,1
对应 小于 等于 大于
select strcmp(‘abc’,‘zbcc’);
select strcmp(‘abcdefg’,‘ac’);
select strcmp(‘abcdefg’,‘ab’);
– 截取字符串 substring(原始字符串,截取的位置,截取的数量)
select substring(‘123456789’,3,4);
– 获取字符串的位置
select locate(‘56’,‘123456789’);
select position(‘56’ in ‘123456789’);
select instr(‘123456789’,‘56’);
日期和时间函数
2018-11-26 MySQL 学习笔记 周谦
示例
– 日期和时间函数
– 获取当前日期 系统
select curdate();
select current_date();
– 获取时间 系统
select now();
select current_timestamp();
select localtime();
select sysdate();
select current_time(); – 时间
– 以’1970-01-01 00:00:00’ GMT 后的秒数的形式返回
select unix_timestamp(); – 秒数 当前
select unix_timestamp(‘1995-5-2’); – 指定具体时间
– 格式化日期
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),’%Y %M %D %h:%i:%s %x’);
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),’%Y 年%m 月%d 日’);
– 获取 UTC
select utc_date();
select utc_time();
2018-11-26 MySQL 学习笔记 周谦
– 获取月份
select month(now())
select monthname(now())
select dayname(now()); --星期的 英文
select dayofweek(now());
select year(now()) – 年
select week(now()) – 周
select weekofyear(now()) – 周
select quarter(now()); – 季度
– 日期的计算
SELECT TO_DAYS(‘0000-1-07’); – 从公元 元年开始计算的天数
– 世界杯距离现在的天数
select TO_DAYS(‘2018-6-14’) - TO_DAYS(now());
– 通过天数 得到日期
SELECT FROM_DAYS(729669);
– 比较日期
select datediff(now(),‘2018-6-14’)

SELECT ADDDATE(‘2018-04-19’, 31);
MySQL 函数
2018-11-26 MySQL 学习笔记 周谦
条件判断函数
IF()
语法:
If(表达式,值 1,值 2)
如果表达式成立,返回 值 1,如果表达式不成立 返回 值 2
示例
– 条件判断函数
select if(1=2,‘正确’,‘不正确’)
use igeek;
select sid,sname,if(sex=‘男’,‘先生’,‘女生’) 称呼 from student;
Ifnull()
判断空,等同于 if(xx is null,值 1,xx)
示例
– 计算收入 工资 + 奖金
select
empno,ename,job,(sal+ if(comm is null,0,comm)) 收入,
sal 工资,ifnull(comm,0) 奖金
from emp;
select if(comm is null,0,comm) 收入 from emp;
2018-11-26 MySQL 学习笔记 周谦
条件判断语句
类似 java 中的 switch case 多重条件判断
示例
– 条件判断语句
– 格式 1
select case
when 1+1=1 then ‘1+1=1’
when 1+1=2 then ‘1+1=2’
when 1+1=3 then ‘1+1=3’
else
‘1+1=0’
end

– 格式 2
select case 1+1
when 1 then ‘1+1=1’
when 2 then ‘1+1=2’
when 3 then ‘1+1=3’
else
0
end
– 类似 switch case 或 if…else if
select * from score;
/*
2018-11-26 MySQL 学习笔记 周谦
60 以下 不及格
60 - 70 及格
70 - 80 良
80 - 90 良好
90 以上 优
/
select s_sid,s_cid,scoreNum,
case
when scoreNum<60 then ‘不及格’
when scoreNum>=60 and scoreNum<70 then ‘及格’
when scoreNum>=70 and scoreNum<80 then ‘良’
when scoreNum>=80 and scoreNum<90 then ‘良好’
else ‘优’
end 等级
from score;
系统信息函数
主要用来获取一些当前 MySQL 系统一些基本信息
示例
– 系统信息函数
– 获取当前 MySQL 的版本
2018-11-26 MySQL 学习笔记 周谦
select version();
– 获取当前数据库
select database();
– 当前数据库连接数
select connection_id();
– 当前登录用户
/
root@域名或 IP 用户
root@192.168.6.2 和 root@localhost 是两个不同的用户 可以具有不同的权
限
*/
SELECT USER();
SELECT system_USER();
SELECT session_USER();
SELECT current_USER();
SELECT current_USER;
– 获取字符串的字符集
select charset(‘abc’);
select charset(‘中国人’);
select collation(‘abcd’);

select * from person;
–上一次查询 返回的记录数
SELECT FOUND_ROWS();
insert into person(user_id,name) values(‘1234567878’,‘geek’);
insert into person(name) values(‘hehe’);
– 获取 auto_increment 自增长,最后产生的值
select last_insert_id();
2018-11-26 MySQL 学习笔记 周谦
–BENCHMARK() 函数重复 count 次执行表达式 expr 。
SELECT BENCHMARK(5,‘hello’);
SELECT BENCHMARK(1000000,ENCODE(‘hello’,‘goodbye’));
加密函数
MySQL 提供多个用于加密算法的函数.
示例
– 加密函数
select * from users;
– 添加列
alter table users add pwd varchar(300);
alter table users add loginName varchar(100);
– 登录
select id,sname,age,sex
from users
where loginName=‘zhangsan’ and pwd=‘123’;
– 加密
– 我好想你 --> 465465asdfasdf454 sds –
– 不可逆的加密
update users set pwd = md5(pwd);
update users set pwd = password(pwd);
update users set pwd = ‘123’;

select id,sname,age,sex
from users
where loginName=‘zhangsan’ and pwd=md5(‘123’);

2018-11-26 MySQL 学习笔记 周谦
select id,sname,age,sex
from users
where loginName=‘zhangsan’ and pwd=password(‘123’);
– 可逆的加密
update users set pwd = encode(‘123’,‘797987asdfasd’);
– 加密
select encode(‘123’,‘797987asdfasd’);
– 解码
select decode(‘3F3F’,‘797987asdfasd’);
select DES_DECRYPT(‘abc’,‘797987asdfasd’)
SELECT ENCRYPT(‘123’);
SELECT SHA1(‘abc’);
SELECT AES_ENCRYPT(‘text’,‘password’);
– AES_ENCRYPT()和 AES_DECRYPT()
select id,sname,age,sex
from users
where loginName=‘zhangsan’ and pwd=encode(‘123’,‘797987asdfasd’);
select * from emp;
select empno,ename,(year(now())-year(hiredate)) 年份 from emp;
select empno,ename,datediff(now(),hiredate)/365 年份 from emp;
–select empno,ename,(now()-hiredate) 年份 from emp;
2018-11-26 MySQL 学习笔记 周谦
视图
视图简介
视图的作用
创建视图的语法
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED |
MERGE | TEMPTABLE}]
2018-11-26 MySQL 学习笔记 周谦
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
ALGORITHM 是 MySQL 对标准 SQL 的扩展
UNDEFINED 交给数据库自己决定
可选的 ALGORITHM 子句是对标准 SQL 的 MySQL 扩展。ALGORITHM 可取三个值：MERGE、
TEMPTABLE 或 UNDEFINED。如果没有 ALGORITHM 子句，默认算法是 UNDEFINED（未定义的）。
算法会影响 MySQL 处理视图的方式。
UNDEFINED，MySQL将选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，
这是因为 MERGE 通常更有效，而且如果使用了临时表，视图是不可更新的。
MERGE，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语
句的对应部分。
TEMPTABLE，视图的结果将被置于临时表中，然后使用它执行语句。
ALGORITHM = TEMPTABLE（使用临时表总会使视图成为不可更新的）。
示例
– 视图
– 创建视图
create view view_student
as
select
sid,sname,sex,
(year(now())-year(birthday)) age,
tel,address
from student;
– 查询视图
select * from view_student;
– 成绩的视图
2018-11-26 MySQL 学习笔记 周谦
create view view_score
as
select
sc.s_sid 学号,
st.sname 姓名,
coame 科目,
sc.scoreNum 成绩
from score sc
inner join student st on st.sid = sc.s_sid
inner join course co on co.cid = sc.s_cid;
– 查询视图
select * from view_score;
– 查询所有学生的语文成绩
select * from view_score where 科目=‘语文’;
– 查询 所有科目 的平均成绩
select 科目,round(avg(成绩),2) 平均成绩
from view_score
group by 科目;
– 在视图的基础上创建视图
create view view_score_avg
as
select 科目,round(avg(成绩),2) 平均成绩
from view_score
group by 科目;

select * from view_score_avg;
查看视图
– 查看视图的结构
desc view_score_avg;
– 查看当前库中 所有的表和视图的信息
show table status;
2018-11-26 MySQL 学习笔记 周谦
– 查看某一张表 或 视图
show table status like ‘course’;
show table status like ‘view_score_avg’;
– 查看视图的定义语句
show create view view_score_avg;
修改视图
– 创建和替换视图
create or replace view view_score_avg
as
select 科目 kemu ,round(avg(成绩),2) pjcj
from view_score
group by 科目;
– 修改已经存在的视图
alter view view_score_avg
as
select 科目 cname ,round(avg(成绩),2) avg_score
from view_score
group by 科目;
desc view_score_avg;
select * from view_score_avg;
删除视图
Drop view [if exists] 视图名;
– 删除视图
drop view if exists view_score_avg;
2018-11-26 MySQL 学习笔记 周谦
更新视图
如果视图中有多张表,不允许更新 (insert,update,delete)
– 更新视图
– 如果视图中有多张表,不允许更新 (insert,update,delete)
insert into view_score(学号,姓名,科目,成绩)
values(1010,‘测试’,‘影月’,50);
– 通过视图去更新 单表 ,通过视图去修改 基表
select * from view_st;
select * from student;
update view_st set sname = ‘张三丰’ where sid=1001;
选项[WITH [CASCADED | LOCAL] CHECK OPTION]
在关于可更新视图的 WITH CHECK OPTION子句中，当视图是根据另一个视图定义的时，LOCAL
和 CASCADED 关键字决定了检查测试的范围。LOCAL 关键字对 CHECK OPTION 进行了限制，
使其仅作用在定义的视图上，CASCADED 会对将进行评估的基表进行检查。如果未给定任一
关键字，默认值为 CASCADED。
– 可更新视图
create or replace view view_test_student
as
select * from student where sex = ‘女’
WITH CASCADED CHECK OPTION
2018-11-26 MySQL 学习笔记 周谦
;
select * from view_test_student;
– 插入
insert into view_test_student(sid,sname,sex,birthday,address)
values(1007,‘杨幂’,‘女’,‘1989-5-12’,‘北京’);
– 男生无法插入
insert into view_test_student(sid,sname,sex,birthday,address)
values(1009,‘刘恺威’,‘男’,‘1988-11-12’,‘香港’);
索引
在创建表的时候添加索引
– 在创建表的时候添加索引
create table test1(
tid int,
tname varchar(20),
2018-11-26 MySQL 学习笔记 周谦
index(tid)
)
– 创建表的时候 创建唯一索引
– 在创建表的时候 添加了唯一约束 自动添加一个唯一索引
create table test2(
tid int,
tname varchar(20) unique
);
– 主键索引 创建表的时候 添加了主键约束
create table test3(
tid int primary key,
tname varchar(20)
);
在已经存在的表上添加索引
– 在已经存在的表上添加索引
create table test4(
tid int,
tname varchar(20)
);
– 创建索引
– 普通索引
2018-11-26 MySQL 学习笔记 周谦
create index index_test4_tid
on test4 (tid desc);
–唯一索引
create unique index index_test4_tid
on test4 (tid desc);
– 全文索引
– 您可以创建特殊的 FULLTEXT 索引，用于全文搜索。只有 MyISAM 表类型支持
FULLTEXT 索引。
–FULLTEXT 索引只可以从 CHAR, VARCHAR 和 TEXT 列中创建。
create fulltext index index_test4_tid
on test4 (tname desc);
– 空间索引
–创建 SPATIAL 索引。
–只有 MyISAM 表支持空间类型，已编索引的列必须声明为 NOT NULL。
通过修改表来添加索引
– 通过修改表添加索引
alter table test5
add unique index index_test5_tid (tid asc);
2018-11-26 MySQL 学习笔记 周谦
查看索引
– 查看索引
– 查看表上添加的索引
show index from student;
show keys from student;
删除索引
– 删除索引
drop index index_test4_tid on test4;
alter table test4 drop index index_test4_tid;
– 删除主键索引
alter table test4 drop primary key;
设计索引的原则
2018-11-26 MySQL 学习笔记 周谦
触发器
示例
– 触发器
– 日志
create table student_log(
log_id int auto_increment primary key,
msg varchar(500),
log_time timestamp
);
select * from student;
select * from student_log;
– 在 student 表上 添加一个 插入数据的 前置触发器
create trigger trigger_student_insert before insert
on student for each row
insert into student_log(msg) values(‘插入数据’);
– 测试触发器,触发器不能手动调用,数据库系统自动执行
insert into student(sid,sname,sex)
values(1007,‘花花’,‘男’);
2018-11-26 MySQL 学习笔记 周谦
查看触发器
show triggers;
删除触发器
– 删除触发器
drop trigger trigger_student_insert;
在触发器中特殊的表
使用别名 OLD 和 NEW，能够引用与触发程序相关的表中的列。OLD.col_name 在更新或删除
它之前，引用已有行中的 1 列。NEW.col_name 在更新它之后引用将要插入的新行的 1 列或
已有行的 1 列。
激活触发程序时，对于触发程序引用的所有 OLD 和 NEW 列，需要具有 SELECT 权限，对于
作为 SET 赋值目标的所有 NEW 列，需要具有 UPDATE 权限。
– 在触发器上的特殊的 对象 NEW 和 OLD
/*
NEW.列名 可以获取新 插入和更新的 列的数据
OLD.列名 可以获取 更新和删除之前 的 列的数据
*/
– 在 student 表上 添加一个 插入数据的 前置触发器
delimiter \
create trigger trigger_student_insert before insert
on student for each row
begin
insert into student_log(msg) values(concat(‘插入数据
[’,new.sid,’,’,new.sname,’,’,new.sex,’]’));
end;
\
2018-11-26 MySQL 学习笔记 周谦
– 在 student 表上 添加一个 更新数据的 后置触发器
delimiter \
create trigger trigger_student_update after update
on student for each row
begin
insert into student_log(msg)
values(concat(‘更新的数据(new)[’,
new.sid,’,’,new.sname,’,’,new.sex,’]’
,‘更新前数据(old)[’,old.sid,’,’,old.sname,’,’,old.sex,’]’));
end;
\
select * from student;
select * from student_log;
– 测试 new
insert into student(sid,sname,sex)
values(1008,‘曹操’,‘男’);
– 测试 old 和 new 同时使用
update student set sname=‘草草’,sex=‘女’ where sid=1007;
– old 只有在 update 和 delete 才能使用
– new 只有 insert 和 update 才能使用
存储过程
MySQL中的一种子程序,在MySQL服务器上预先定义一些SQL语句操作,在客户端可以通过存
储过程的名字,传递参数,就能执行对应的 SQL 语句.
存储过程是预编译的,执行的效率相对高一些,同时传递语句变少,减少了网络流量,也提高了
执行的效率,所以也经常成为 SQL 优化的一种方案.
语法
CREATE PROCEDURE 存储过程的名称 ([参数列表[,…]])
[characteristic …] 过程的内容
2018-11-26 MySQL 学习笔记 周谦
存储过程的参数
in 输入参数,在调用存储过程的时候,需要传递进入的具体参数的值
out 输出参数,调用过程过程 需要去接收
inout 即作为输入参数,也作为输出参数输出
示例 1
– 存储过程
delimiter c r e a t e p r o c e d u r e p r o a d d ( i n n u m 1 i n t , i n n u m 2 i n t ) b e g i n s e l e c t n u m 1 + n u m 2 r s ; e n d ; create procedure pro_add(in num1 int,in num2 int) begin select num1+num2 rs; end; createprocedureproa​dd(innum1int,innum2int)beginselectnum1+num2rs;end;
– 调用过程
call pro_add(10,20);
– 输出参数 输入两个 整数,输入 加减乘除 四个结果
delimiter $$
create procedure pro_add2(in num1 int,in num2 int,
out rs1 int,out rs2 int,out rs3 int,out rs4 float)
begin

set rs1 = num1 + num2;
set rs2 = num1 - num2;
set rs3 = num1 * num2;
set rs4 = num1 / num2;
end;
$$
call pro_add2(10,20,@a,@b,@c,@d);
select @a 加法,@b 减法,@c 乘法,@d 除法
– 测试 inout
2018-11-26 MySQL 学习笔记 周谦
delimiter \
create procedure pro_sq(inout num int)
begin
set num = num * num;
end;
\
– 调用
set @param = 5;
call pro_sq(@param);
select @param;
在过程中使用变量
使用关键词 declare 定义变量
变量赋值:

1. 使用 set 给变量赋值
2. 使用 select …. into 给变量赋值
   – 使用 set 给变量赋值
   delimiter \
   create procedure pro_testval1()
   begin
   declare num int; – 定义变量
   set num = 128; – 修改变量的值
   select num;
   end;
   \
   call pro_testval1
   – select … into 给变量赋值 查询表或视图
   – 查询 返回 student 的记录数
   delimiter \
   create procedure pro_testval2(out countNum int)
   begin
   declare num int; – 定义变量
   2018-11-26 MySQL 学习笔记 周谦
   – 通过 select … into
   select count(1)into num from student;
   set countNum = num; – 修改变量的值
   end;
   \
   call pro_testval2(@cc);
   select @cc 记录数;
   在过程中使用流程控制语句
   If 条件判断语法
   If 条件 then
   End if;
   While 循环语法:
   While 条件 do
   End while;
   2018-11-26 MySQL 学习笔记 周谦
   Loop 循环语法:
   标记 : loop
   \ leave
   End loop 标记;
   示例
   – 条件判断
   – 判断奇数和偶数
   delimiter \
   create procedure pro_if(in num int)
   begin
   if num%2 = 0 then
   select ‘偶数’;
   end if;
   if num%2 != 0 then
   select ‘奇数’;
   end if;
   end;
   \
   delimiter \
   create procedure pro_if3(in num int)
   begin
   if num%2 = 0 then
   select ‘偶数’;
   else
   select ‘奇数’;
   end if;
   end;
   \
   delimiter \
   create procedure pro_if2(in num int)
   begin
   2018-11-26 MySQL 学习笔记 周谦
   if num%2 = 0 then
   select ‘偶数’;
   elseif num%2 != 0 then
   select ‘奇数’;
   end if;
   end;
   \
   call pro_if3(6)
   delimiter \
   create procedure pro_if4(in num int)
   begin
   case
   when num%2 = 0 then
   select ‘偶数’;
   else
   select ‘奇数’;
   end case;
   end;
   \
   – 打印 10 以内的整数
   delimiter \
   create procedure pro_while1()
   begin
   declare num int;
   set num = 0;
   while num < 10 do
   select num;
   set num = num+1;
   end while;
   end;
   \
   call pro_while1;
   – 打印 10 以内的偶数
   delimiter \
   create procedure pro_while2()
   begin
   2018-11-26 MySQL 学习笔记 周谦
   declare num int;
   set num = 0;
   while num < 10 do
   if num%2 = 0 then
   select num;
   end if;
   set num = num+1;
   end while;
   end;
   \
   call pro_while2;
   – loop 循环
   – 打印 10 以内的整数
   delimiter \
   create procedure pro_loop1()
   begin
   declare num int;
   set num = 0;
   lab:loop
   if num >=10 then
   leave lab;
   end if;
   select num;
   set num = num+1;
   end loop lab;
   end;
   \
   call pro_loop1
   删除存储过程
   – 删除存储过程
   drop procedure if exists pro_paging;
   2018-11-26 MySQL 学习笔记 周谦
   自定义函数
   语法
   CREATE FUNCTION sp_name ([func_parameter[,…]])
   RETURNS type
   [characteristic …] routine_body
   示例
   – 自定义函数
   delimiter \
   create function fun1(num1 int,num2 int)
   returns int
   begin
   return num1 + num2;
   end;
   \
   – 调用
   select fun1(1,2);
   /*
   函数和存储过程的区别:
   函数只有输入参数
   函数必须有一个返回值 return
   过程的参数 in out inout
   /
   delimiter \
   create function fun2(num1 int,num2 int)
   returns int
   begin
   return 100;
   end;
   \
   2018-11-26 MySQL 学习笔记 周谦
   删除自定义函数
   – 删除存储过程
   drop function if exists fun2;
   查看过程和函数的创建语句
   存储过程综合案例
   实现一个分页
   – 分页的存储过程
   select * from student;
   – 删除存储过程
   drop procedure if exists pro_paging;
   – 查看过程
   show create procedure pro_paging;
   2018-11-26 MySQL 学习笔记 周谦
   – 创建分页过程
   delimiter \
   create procedure pro_paging(
   in currPage int, – 当前页
   in pageSize int, – 每页显示的记录数
   out rowsCount int, – 总记录数
   out pageCount int – 总页数
   )
   begin
   – 定义变量
   declare beginIndex int;
   – 查询出总记录数
   select count(1) into rowsCount from student;
   – 通过总记录数计算出 总页数
   set pageCount = ceil(rowsCount/pageSize);
   – 计算出起始的记录的索引
   set beginIndex=(currPage-1)pageSize;
   – 分页查询
   select sid,sname,sex from student limit beginIndex,pageSize;
   end;
   \
   – 测试分页
   call pro_paging(3,3,@rc,@pc);
   select @rc 总记录数,@pc 总页数;
   – 取整
   select ceil(8/3)
   – 分页
   select * from student limit 3,3;
   2018-11-26 MySQL 学习笔记 周谦
   JDBC 基础
   JDBC 是 Java 提供的,Java 应用程序访问关系型的数据库的一套 API,提供了一系列访问数据库
   的接口,
   插入示例
   package org.somken;
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.Statement;
   /
   2018-11-26 MySQL 学习笔记 周谦

• 插入数据
• @author www.igeekhome
• @company 极客营
• 2018年4月23日下午3:45:16
  /
  public class InsertDemo {
  /*
• @param args
  /
  public static void main(String[] args) throws Exception {
  //1. 加载驱动
  Class.forName(“com.mysql.jdbc.Driver”);
  //2.创建一个连接
  Connection conn = DriverManager.getConnection(
  “jdbc:mysql://127.0.0.1:3306/test”,
  “root”, “123456”);
  //3. 创建SQL语句执行对象
  Statement stmt = conn.createStatement();
  //4.执行SQL语句 (jdbc中 insert update delete 都属于 更新 返回 影响的
  行数)
  int i = stmt.executeUpdate(“insert into student(sid,sname,sex)
  values(1009,‘极客’,‘女’)”);
  System.out.println((i>0)?“成功”:“失败”);
  //5.关闭
  stmt.close();
  conn.close();
  }
  }
  更新示例
  package org.somken;
  import java.sql.Connection;
  2018-11-26 MySQL 学习笔记 周谦
  import java.sql.DriverManager;
  import java.sql.Statement;
  /*
• 更新数据
• @author www.igeekhome
• @company 极客营
• 2018年4月23日下午4:05:16
  /
  public class UpdateDemo {
  /*
• @param args
  /
  public static void main(String[] args) throws Exception {
  String driver = “com.mysql.jdbc.Driver”;
  String url = “jdbc:mysql://127.0.0.1:3306/test”;
  String user = “root”;
  String password = “123456”;
  //1. 加载驱动
  Class.forName(driver);
  //2.创建一个连接
  Connection conn = DriverManager.getConnection(url,user,password);
  //3. 创建SQL语句执行对象
  Statement stmt = conn.createStatement();
  //4.执行SQL语句 (jdbc中 insert update delete 都属于 更新 返回 影响的
  行数)
  int i = stmt.executeUpdate(“update student set sname=‘极客营’ where
  sid=1009”);
  System.out.println((i>0)?“成功”:“失败”);
  //5.关闭
  stmt.close();
  conn.close();
  }
  }
  2018-11-26 MySQL 学习笔记 周谦
  删除示例
  package org.somken;
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.Statement;
  /*
• 删除数据
• @author www.igeekhome
• @company 极客营
• 2018年4月23日下午4:07:16
  /
  public class DeleteDemo {
  /*
• @param args
  /
  public static void main(String[] args) throws Exception {
  String driver = “com.mysql.jdbc.Driver”;
  String url = “jdbc:mysql://127.0.0.1:3306/test”;
  String user = “root”;
  String password = “123456”;
  //1. 加载驱动
  Class.forName(driver);
  //2.创建一个连接
  Connection conn = DriverManager.getConnection(url,user,password);
  //3. 创建SQL语句执行对象
  Statement stmt = conn.createStatement();
  //4.执行SQL语句 (jdbc中 insert update delete 都属于 更新 返回 影响的
  行数)
  int i = stmt.executeUpdate(“delete from student where sid=1009”);
  System.out.println((i>0)?“成功”:“失败”);
  //5.关闭
  stmt.close();
  conn.close();
  }
  2018-11-26 MySQL 学习笔记 周谦
  }
  查询示例
  package org.somken;
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.Statement;
  /*
• 查询数据库
• @author www.igeekhome
• @company 极客营
• 2018年4月23日下午4:09:26
  /
  public class SelectDemo {
  /*
• @param args
  /
  public static void main(String[] args) throws Exception{
  //定义数据库访问的变量
  String driver = “com.mysql.jdbc.Driver”;
  String url = “jdbc:mysql://127.0.0.1:3306/test”;
  String user = “root”;
  String password = “123456”;
  //1.加载驱动
  Class.forName(driver);
  //2.获取连接
  Connection conn = DriverManager.getConnection(url,user,password);
  //3.创建SQL语句执行对象
  Statement stmt = conn.createStatement();
  //4.执行查询,返回一个结果集ResultSet
  2018-11-26 MySQL 学习笔记 周谦
  ResultSet rs = stmt.executeQuery(“select sid,sname,sex from
  student”);
  //5.循环取得查询的结果
  // rs.next();
  // rs.next();
  // rs.next();
  while(rs.next()){
  int sid = rs.getInt(1);
  //int sid = rs.getInt(“sid”); //建议写列名
  String sname = rs.getString(“sname”);
  String sex = rs.getString(“sex”);
  System.out.println(sid+"\t"+sname+"\t"+sex);
  }
  //6. 关闭结果集
  rs.close();
  //7. 关闭 SQL执行对象
  stmt.close();
  //8. 关闭数据库连接
  conn.close();
  }
  }
  SQL 注入问题
  由于我们拼接字符串的形式来拼接 SQL 语句,可以使用恒等式绕过登录校验.
  /*
• 登录
• @return
  */
  public static User login(String loginName,String pwd){
  2018-11-26 MySQL 学习笔记 周谦
  //定义数据库访问参数
  String driverClassName = “com.mysql.jdbc.Driver”;
  String url = “jdbc:mysql://127.0.0.1:3306/test”;
  String user = “root”;
  String password = “123456”;
  User u = null;
  String sql = "SELECT id,NAME,sex,birthday,loginName FROM users "

• “where loginName=’”+loginName+"’ and pwd=’"+pwd+"’";
  System.out.println(sql);
  Connection conn = null; //连接对象
  Statement stmt = null; //sql语句执行对象
  ResultSet rs = null; //结果集对象
  try {
  //加载驱动
  Class.forName(driverClassName);
  //创建一个连接
  conn = DriverManager.getConnection(url, user, password);
  //创建sql语句执行对象
  stmt = conn.createStatement();
  //执行sql语句
  rs = stmt.executeQuery(sql);
  //循环从结果集中取值
  if(rs.next()) {
  u = new User();
  u.setId(rs.getInt(“id”));
  u.setName(rs.getString(“name”));
  u.setSex(rs.getString(“sex”));
  u.setBirthday(rs.getDate(“birthday”));
  u.setLoginName(rs.getString(“loginName”));
  }
  } catch (Exception e) {
  e.printStackTrace();
  } finally {
  try {
  if(rs!=null) rs.close();
  if(stmt!=null) stmt.close();
  if(conn!=null) conn.close();
  } catch (Exception e2) {
  }
  2018-11-26 MySQL 学习笔记 周谦
  }
  return u;
  }
  测试 sql 注入
  //测试登录
  User user = login("","’ or ‘1’=‘1");
  if (null != user) {
  System.out.println(“登录成功:”+user);
  } else {
  System.err.println(“登录失败”);
  }
  由于拼接,产生了的 sql 语句:
  – SQL 注入
  SELECT id,NAME,sex,birthday,loginName FROM users
  WHERE loginName=’’ AND pwd=’’ OR ‘1’=‘1’
  解决方案 1
  先通过登录名取得密码,再次比较密码
  /**

• 登录
• 解决SQL注入的问题
• @return
  */
  public static User login2(String loginName,String pwd){
  //定义数据库访问参数
  String driverClassName = “com.mysql.jdbc.Driver”;
  String url = “jdbc:mysql://127.0.0.1:3306/test”;
  String user = “root”;
  String password = “123456”;
  User u = null;
  String sql = "SELECT id,NAME,sex,birthday,loginName,pwd FROM users
  "

• “where loginName=’”+loginName+"’";
  System.out.println(sql);
  2018-11-26 MySQL 学习笔记 周谦
  Connection conn = null; //连接对象
  Statement stmt = null; //sql语句执行对象
  ResultSet rs = null; //结果集对象
  try {
  //加载驱动
  Class.forName(driverClassName);
  //创建一个连接
  conn = DriverManager.getConnection(url, user, password);
  //创建sql语句执行对象
  stmt = conn.createStatement();
  //执行sql语句
  rs = stmt.executeQuery(sql);
  //循环从结果集中取值
  if(rs.next()) {
  //取得数据库中的密码
  String pwd1 = rs.getString(“pwd”);
  //通过数据库取得的密码 ,再次的去比较 传递的密码
  if(pwd1.equals(pwd)) {
  u = new User();
  u.setId(rs.getInt(“id”));
  u.setName(rs.getString(“name”));
  u.setSex(rs.getString(“sex”));
  u.setBirthday(rs.getDate(“birthday”));
  u.setLoginName(rs.getString(“loginName”));
  }
  }
  } catch (Exception e) {
  e.printStackTrace();
  } finally {
  try {
  if(rs!=null) rs.close();
  if(stmt!=null) stmt.close();
  if(conn!=null) conn.close();
  } catch (Exception e2) {
  }
  }
  return u;
  }
  2018-11-26 MySQL 学习笔记 周谦
  解决方案 2
  使用 PreparedStatement 对象来查询
  //使用占位符 ?
  String sql = "SELECT id,NAME,sex,birthday,loginName,pwd FROM users "
• “where loginName = ? and pwd = ?”;
  PreparedStatement pstmt = null; //sql语句执行对象 预编译的
  //创建sql语句执行对象
  pstmt = conn.prepareStatement(sql);
  //给参数赋值 pstmt.setXxxx()
  pstmt.setString(1,loginName);
  pstmt.setString(2,pwd);
  //执行sql语句
  rs = pstmt.executeQuery();
  PreparedStatement 预编译的 SQL 语句执行
  对象
  以后除非特殊情况,建议使用 PreparedSatement 来替代 Statement.
  PreparedSatement 采用的预编译的 SQL,执行的效率相对 Statement 较高.
  //模拟传递的user
  User userParam = new User();
  userParam.setName(“黎明”);
  userParam.setSex(“男”);
  userParam.setBirthday(new Date(1965-1900,5-1,6));
  userParam.setLoginName(“liming”);
  userParam.setPwd(“456”);
  2018-11-26 MySQL 学习笔记 周谦
  //定义数据库访问参数
  String driverClassName = “com.mysql.jdbc.Driver”;
  String url = “jdbc:mysql://127.0.0.1:3306/test”;
  String user = “root”;
  String password = “123456”;
  String sql = “insert into users(name,sex,birthday,loginName,pwd)
  values(?,?,?,?,?)”;
  Class.forName(driverClassName);//加载驱动
  Connection conn = DriverManager.getConnection(url, user, password);
  //创建PreparedStatement
  PreparedStatement ps = conn.prepareStatement(sql);
  //设置参数
  ps.setString(1, userParam.getName());
  ps.setString(2, userParam.getSex());
  ps.setDate(3, new java.sql.Date(userParam.getBirthday().getTime()));
  ps.setString(4, userParam.getLoginName());
  ps.setString(5, userParam.getPwd());
  //执行
  int i = ps.executeUpdate();
  System.out.println(i>0?“成功”:“失败”);
  Java 的单元测试工具 Junit
  Junit 是 Java 中的一种常用的单元测试工具,软件测试中简单分类:单元测试,集成测试,系统测
  试,验收测试.
  使用准备工作
  1.导入 Junit 的包
  2018-11-26 MySQL 学习笔记 周谦
  2018-11-26 MySQL 学习笔记 周谦
  2018-11-26 MySQL 学习笔记 周谦
  常用的注解
  @Test
  在将要测试的方法上添加,表示该方法需要测试
  @Before
  在运行所有的测试方法之前,会自动执行的方法
  @After
  在运行所有的测试方法之后会自动执行的方法
  @BeforeClass
  在测试类执行前执行
  @AfterClass
  在测试类销毁的时候执行
  示例
  package org.somken.test;
  import java.util.Date;
  import java.util.List;
  2018-11-26 MySQL 学习笔记 周谦
  import org.junit.Assert;
  import org.junit.Test;
  import org.somken.demo.Demo1;
  import org.somken.demo.Demo2;
  import org.somken.entity.User;
  /**

• 单元测试
• @author www.igeekhome
• @company 极客营
• 2018年4月25日上午10:39:58
  /
  public class MyTest {
  @Test
  public void demo1() {
  List users = Demo1.findAll();
  for (User user : users) {
  System.out.println(user);
  }
  }
  @Test
  public void login() {
  //断言 执行测试过程中,判断执行结果和预期结果 是否一致
  Assert.assertNotNull(Demo1.login(“san”, “123”));
  Assert.assertNull(Demo1.login("", “’ or ‘1’='1”));
  Assert.assertNotNull(Demo1.login(“lisi”, “123”));
  }
  @Test
  public void insert() throws Exception{
  //模拟传递的user
  User userParam = new User();
  userParam.setName(“黎明2”);
  userParam.setSex(“男”);
  userParam.setBirthday(new Date(1965-1900,5-1,6));
  userParam.setLoginName(“liming”);
  userParam.setPwd(“456”);
  boolean b = Demo2.insert(userParam);
  Assert.assertTrue(b);
  2018-11-26 MySQL 学习笔记 周谦
  }
  }
  JDBC 调用 MySQL 的存储过程
  示例 1
  Sql 语句
  – 存储过程
  – 过程的定义
  delimiter c r e a t e p r o c e d u r e p r o a d d ( i n n u m 1 i n t , i n n u m 2 i n t , o u t r s i n t ) b e g i n s e t r s = n u m 1 + n u m 2 ; e n d ; create procedure pro_add(in num1 int,in num2 int,out rs int) begin set rs = num1+num2; end; createprocedureproa​dd(innum1int,innum2int,outrsint)beginsetrs=num1+num2;end;
  – 调用
  call pro_add(1,2,@r);
  select @r
  Jdbc 代码
  /*
• 调用存储过程
• @throws Exception
  /
  @Test
  public void call_pro_add() throws Exception{
  //获取连接
  2018-11-26 MySQL 学习笔记 周谦
  Connection conn = C3P0Utils.getConnection();
  //创建SQL语句执行对象 CallableStatement 专门调用 存储过程和函数的
  CallableStatement astmt = conn.prepareCall(“call pro_add(?,?,?)”);
  //设置输入参数
  astmt.setInt(1, 33);
  astmt.setInt(2, 65);
  //设置输出参数,注册输出参数
  astmt.registerOutParameter(3, Types.INTEGER);
  //执行
  astmt.execute();
  //取得输出变量
  int i = astmt.getInt(3);
  System.out.println(“结果:”+i);
  }
  示例 2 使用存储过程实现分页
  SQL 代码
  – 创建分页过程
  delimiter \
  create procedure pro_paging_book(
  in title varchar(100),
  in currPage int, – 当前页
  in pageSize int, – 每页显示的记录数
  out rowsCount int, – 总记录数
  out pageCount int – 总页数
  )
  begin
  – 定义变量
  declare beginIndex int;
  – 查询出总记录数
  select count(1) into rowsCount from book;
  – 通过总记录数计算出 总页数
  2018-11-26 MySQL 学习笔记 周谦
  set pageCount = ceil(rowsCount/pageSize);
  – 计算出起始的记录的索引
  set beginIndex=(currPage-1)pageSize;
  – 分页查询
  select b.isbn,b.title,b.author,b.price,b.publishingId,p.name,
  b.booktype,b.description
  from book b
  inner join publishing p on b.publishingId = p.id
  where title like concat(’%’,title,’%’) limit beginIndex,pageSize;
  end;
  \
  Java 代码
  package org.somkenhome.tests;
  import java.sql.CallableStatement;
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.Types;
  import org.junit.Test;
  /
• 测试使用存储过程分页
• @author www.igeekhome
• @company 极客营
• 2018年4月28日下午2:09:03
  */
  public class TestProcedurePaging {
  @Test
  public void testPaging() throws Exception{
  Class.forName(“com.mysql.jdbc.Driver”);//加载驱动
  Connection conn =
  DriverManager.getConnection(“jdbc:mysql://127.0.0.1:3306/books”,
  “root”, “123456”); //创建数据库连接
  2018-11-26 MySQL 学习笔记 周谦
  int currPage = 3;
  int pageSize = 2;
  //创建SQL语句执行对象 调用过程 CallableStatement
  CallableStatement astmt = conn.prepareCall(“call
  pro_paging_book(?,?,?,?,?)”);
  //设置输入参数
  astmt.setString(1, “”);
  astmt.setInt(2, currPage);
  astmt.setInt(3, pageSize);
  //设置输出参数
  astmt.registerOutParameter(4, Types.INTEGER);
  astmt.registerOutParameter(5, Types.INTEGER);
  //执行
  astmt.execute();
  //取值
  int rowsCount = astmt.getInt(4);
  int pageCount = astmt.getInt(5);
  //取得结果集
  ResultSet rs = astmt.getResultSet();
  //打印数据
  System.out.println(“当前页/总页数:”+currPage+"/"+pageCount);
  System.out.println(“总记录数:”+rowsCount+",每页显示:"+pageSize+“条
  “);
  System.out.println(”------------------------------------------------
  —”);
  while(rs.next()) {
  int isbn = rs.getInt(“isbn”);
  String title = rs.getString(“title”);
  String author = rs.getString(“author”);
  double price = rs.getDouble(“price”);
  String publishingId = rs.getString(“publishingId”);
  String publishingname = rs.getString(“name”);
  String booktype = rs.getString(“booktype”);
  String description = rs.getString(“description”);
  System.out.println(isbn+"\t"+title+"\t"+author+"\t"+price+"\t"+publi
  2018-11-26 MySQL 学习笔记 周谦
  shingId
  +"\t"+publishingname+"\t"+booktype+"\t"+description);
  }
  System.out.println("------------------------------------------------
  —");
  }
  }
  2018-11-26 MySQL 学习笔记 周谦

本文标签： 正则表达式mysql