1 初识数据库
1.1 MySQL 安装
1.2 MySQL 管理与连接
1.2.1 MySQL 管理
1.2.2 MySQL 连接
MySQL 命令行基本操作
(1)数据库连接
# 登录(打开CMD控制台,使用mysql命令登录MySQL数据库) mysql -h localhost -u root -p
(2)数据库简单操作
# 显示所有数据库 show databases; # 进入指定数据库 use 数据库; # 显示所有数据表 show tables; # 查询指定数据表 select * from 数据表;
1.3 DDL,DML,DCL,DQL,TCL 数据库语言
DQL:数据库查询语言。关键字:SELECT ... FROM ... WHERE。
DDL :数据库模式定义语言。关键字:CREATE,DROP,ALTER。
DML:数据操纵语言。关键字:INSERT、UPDATE、DELETE。
DCL:数据控制语言 。关键字:GRANT、REVOKE。
TCL:事务控制语言。关键字:COMMIT、ROLLBACK、SAVEPOINT。
DDL,DML,DCL,DQL,TCL共同组成数据库的完整语言。
2 MySQL 操作数据库与数据库表(DDL)
3.1 MySQL 数据库
3.1.1 数据库创建
示例:
建库:创建数据库jdbctest
create database jdbctest;
3.1.2 数据库删除
3.2 MySQL 数据表
3.1.1 数据表创建与删除
1、数据表创建
示例:
建表:创建数据表student
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`name` varchar(32) DEFAULT NULL COMMENT '姓名',
`sex` int DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
示例2:建表(包含添加外键)
CREATE TABLE `user` (
`oid` bigint(20) NOT NULL COMMENT '网点 ID',
`name` varchar(12) NULL COMMENT '网点名称',
`passwd` varchar(12) NULL COMMENT '网点密码',
`city` varchar(20) NULL COMMENT '城市',
`address` varchar(20) NULL COMMENT '地址',
PRIMARY KEY (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `flight` (
`oid` bigint(20) NOT NULL COMMENT '航班ID',
`first_class_remain_seats` int(11) NULL COMMENT '头等舱余位数',
`business_class_remain_seats` int(11) NULL COMMENT '商务舱余位数',
`economy_class_remain_seats` int(11) NULL COMMENT '经济舱余位数',
`priceOff` double NULL COMMENT '折扣',
`calendar` datetime NULL COMMENT '飞行日期',
`schid` bigint(20) NULL COMMENT '航班计划 ID',
PRIMARY KEY (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `flightscheduler` (
`oid` bigint(20) NOT NULL COMMENT '航班计划 ID',
`flightNumber` varchar(6) NULL COMMENT '航班号',
`fromAddress` varchar(10) NULL COMMENT '出发地',
`toAddress` varchar(10) NULL COMMENT '目的地',
`length` int(11) NULL COMMENT '里程',
`scheduler` tinyint(4) NULL COMMENT '班次',
`price` double NULL COMMENT '价格',
`startDate` datetime NULL COMMENT '有效开始日期',
`endDate` datetime NULL COMMENT '结束日期',
`fromhour` int(11) NULL COMMENT '起飞时',
`frommin` int(11) NULL COMMENT '起飞分',
`tohour` int(11) NULL COMMENT '到达时',
`tomin` int(11) NULL COMMENT '到达分',
`planemodel` bigint(20) NULL COMMENT '飞机ID',
PRIMARY KEY (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `planemodel` (
`oid` bigint(20) NOT NULL COMMENT '飞机 ID',
`model` varchar(255) NULL COMMENT '型号',
`first_class_seats` int(11) NULL COMMENT '头等舱数量',
`business_class_seats` int(11) NULL COMMENT '商务舱数量',
`economy_class_seats` int(11) NULL COMMENT '经济舱数量',
`maxLength` int(11) NULL COMMENT '最大里程',
PRIMARY KEY (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `ticketorder` (
`oid` bigint(20) NOT NULL COMMENT '订票 ID',
`passengerName` varchar(12) NULL COMMENT '订票人名称',
`passengerId` varchar(18) NULL COMMENT '订票人代号',
`cabinclass` varchar(15) NULL COMMENT '舱位',
`tickettype` varchar(15) NULL COMMENT '订票类型',
`cal` datetime NULL COMMENT '订票日期',
`branchid` bigint(20) NULL COMMENT '网点 ID',
`flightid` bigint(20) NULL COMMENT '航班 ID',
PRIMARY KEY (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 已存在表增加外键
ALTER TABLE `flight` ADD FOREIGN KEY (`schid`) REFERENCES `flightscheduler` (`oid`);
ALTER TABLE `flightscheduler` ADD FOREIGN KEY (`planemodel`) REFERENCES `planemodel` (`oid`);
ALTER TABLE `ticketorder` ADD FOREIGN KEY (`branchid`) REFERENCES `user` (`oid`);
ALTER TABLE `ticketorder` ADD FOREIGN KEY (`flightid`) REFERENCES `flight` (`oid`);
2、数据表删除
3.1.2 修改数据表
1、修改表名
2、添加字段
3、修改字段
4、删除字段
3 MySQL 数据管理(DML)
4.1 MySQL 基本语句
4.1 insert 语句(插入数据)
示例:
插入数据:向新建的student表中插入一条测试数据
INSERT INTO `jdbctest`.`student` (`id`, `name`, `sex`, `age`) VALUES ('1', '张三', '1', '10');
4.2 update 语句(修改数据)
4.3 delete 语句(删除数据)
4.2 select 语句(查询数据)(DQL)
4.2.1 基础查询
4.2.2 条件查询
4.2.3 分组查询
4.2.4 连接查询
4.2.5 排序与分页
4.2.6 子查询
4.2.7 MySQL 函数
1、常用函数
2、聚合函数
4 存储过程和函数
4.1 存储过程
一个简单的存储过程示例:
#这是一个简单的存储过程示例
-- 显示存储过程列表(name字段是存储过程名)
SHOW PROCEDURE STATUS LIKE 'mypro';
SHOW CREATE PROCEDURE mypro;
-- 删除存储过程
drop procedure if exists mypro;
delimiter $$
-- 创建存储过程
create procedure mypro(IN num int)
begin
select num;
set num=2;
select num;
end$$
delimiter ;
-- 设置入参(实参)
set @num=0;
-- 调用存储过程
call mypro(@num);
-- 查询变量值
select @num;
#注释内容
-- 注释内容
/*
第一行注释内容
第二行注释内容
*/
4.1.1 查看存储过程
查看存储过程
语法结构如下:SHOW PROCEDURE STATUS LIKE '存储过程名';
或者
SHOW CREATE PROCEDURE 存储过程名;
示例:
SHOW PROCEDURE STATUS LIKE 'mypro';
或者
SHOW CREATE PROCEDURE mypro;
4.1.2 创建存储过程
创建存储过程
语法结构如下:CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] [COMMENT '注释内容'] BEGIN 过程体 END
关于存储过程的创建,语法结构如下: (1)创建无参的存储过程 delimiter $$ CREATE PROCEDURE 存储过程名 () [COMMENT '注释内容'] BEGIN 存储过程体 END $$ delimiter ; (2)创建有参数的存储过程 delimiter $$ CREATE PROCEDURE 存储过程名 (IN | OUT | INOUT 参数名1 参数类型,in | OUT | INOUT 参数名2 参数类型……) [COMMENT '注释内容'] BEGIN 存储过程体 END $$ delimiter ;
示例:
-- 没有使用定界符
create procedure select_pro()
begin
select e1.empno bianhao, e1.ename xingming, e1.job zhiwei, e1.mgr lindaobianhao, e2.ename lindaoxingming, d.dname bumenmingchen, d.loc bumenweizhi
from emp e1 , emp e2 , dept d
where e1.mgr=e2.empno and e1.deptno=d.deptno ;
end
关于上面创建存储过程的例子,有以下需要注意的地方。
-
- 如果在 navicat 工具的查询界面或命令行界面执行,可以执行成功。
-
- 如果在DOS命令行工具内执行,有时会出现报错,无法执行成功(原因:MYSQL存储过程的默认结束符为";“,而SQL语句中含有”;",两者产生冲突导致出现报错,执行失败)。
为了避免报错,可以使用delimiter
在执行存储过程之前更改定界符,如改为“//”或“$$”;然后在执行之后改回默认定界符。示例如下。
-- 使用了delimiter定界符
delimiter // -- 将MySQL结束符改为“//”
create procedure select_pro()
begin
select e1.empno bianhao, e1.ename xingming, e1.job zhiwei, e1.mgr lindaobianhao, e2.ename lindaoxingming, d.dname bumenmingchen, d.loc bumenweizhi
from emp e1 , emp e2 , dept d
where e1.mgr=e2.empno and e1.deptno=d.deptno ;
end //
delimiter ; -- 改回MYSQL的默认结束符为";"
4.1.3 删除存储过程
删除存储过程
语法结构如下:DROP PROCEDURE IF EXISTS 存储过程名;
示例:
drop procedure if exists select_pro;
4.1.4 调用存储过程
调用存储过程
语法结构如下:(1)调用无参存储过程:
call 存储过程名;
或者
call 存储过程名();
(2)调用有参数的存储过程:
call 存储过程名([参数[,参数]]);
示例:
(1)调用无参存储过程:
call mypro;
或者
call mypro();
(2)调用有参数的存储过程:
call mypro(@num);
4.1.5 MySQL 的局部变量和用户变量
4.1.5.1 局部变量
MySQL 存储过程的局部变量声明、设置、查询(只在当前begin...end代码块中有效)
备注:局部变量在 BEGIN...END 中使用。
- 变量声明:
语法结构:
DECLARE 变量名 数据类型 [DEFAULT 默认值]
示例:
declare num int default 0;
- 变量设置:
语法结构:
SET 变量名=变量值;
或者
SELECT 变量值 INTO 变量名;
示例:
SET num=0;
或者
SELECT 0 INTO num;
- 变量查询
语法结构:
SELECT 变量名;
示例:
SELECT num;
一个完整的局部变量使用示例:
“在 BEGIN…END 中使用”的示例:
delimiter $$
-- 创建存储过程
create procedure mypro1()
begin
-- 定义变量
declare num int default 0;
select num;
set num=2;
select num;
end $$
delimiter ;
4.1.5.2 用户变量
MySQL 的用户变量设置、查询(用在客户端链接到数据库实例整个过程中用户变量都是有效的)
- 变量设置:
语法结构:
SET @变量名=变量值;
或者
SELECT 变量值 INTO @变量名;
示例:
SET @num=0;
或者
SELECT 0 INTO @num;
- 变量查询
语法结构:
SELECT @变量名;
示例:
SELECT @num;
一个完整的用户变量使用示例:
delimiter $$
-- 创建存储过程
create procedure mypro(IN num int)
begin
select num;
set num=2;
select num;
end$$
delimiter ;
-- 设置入参(实参)
set @num=0;
-- 调用存储过程
call mypro(@num);
-- 查询变量值
select @num;
4.1.6 MySQL注释
- 单行注释
#注释内容
-- 注释内容
备注:
#和--的区别就是:#后面直接加注释内容,而--的第 2 个破折号后需要跟一个空格符在加注释内容。
- 多行注释
/*
第一行注释内容
第二行注释内容
*/
4.1.7 一个简单的存储过程使用示例
(1)不使用定界符
-- 删除存储过程
drop procedure if exists mypro;
-- 创建存储过程
create procedure mypro(IN num int)
begin
select num;
set num=2;
select num;
end;
-- 设置变量值
set @num=0;
-- 调用存储过程
call mypro(@num);
-- 查询变量值
select @num;
(2)使用delimiter
定界符
-- 删除存储过程
drop procedure if exists mypro;
delimiter $$
-- 创建存储过程
create procedure mypro(IN num int)
begin
select num;
set num=2;
select num;
end$$
delimiter ;
-- 设置入参(实参)
set @num=0;
-- 调用存储过程
call mypro(@num);
-- 查询变量值
select @num;
4.1.8 存储过程的参数
MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT,形式如:
CREATE PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形…])
- IN 输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
- OUT 输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
- INOUT 输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)
1、in 输入参数
mysql> delimiter $$
mysql> create procedure in_param(in p_in int)
-> begin
-> select p_in;
-> set p_in=2;
-> select P_in;
-> end$$
mysql> delimiter ;
mysql> set @p_in=1;
mysql> call in_param(@p_in);
+------+
| p_in |
+------+
| 1 |
+------+
+------+
| P_in |
+------+
| 2 |
+------+
mysql> select @p_in;
+-------+
| @p_in |
+-------+
| 1 |
+-------+
2、out输出参数
mysql> delimiter //
mysql> create procedure out_param(out p_out int)
-> begin
-> select p_out;
-> set p_out=2;
-> select p_out;
-> end
-> //
mysql> delimiter ;
mysql> set @p_out=1;
mysql> call out_param(@p_out);
+-------+
| p_out |
+-------+
| NULL |
+-------+
#因为out是向调用者输出参数,不接收输入的参数,所以存储过程里的p_out为null
+-------+
| p_out |
+-------+
| 2 |
+-------+
mysql> select @p_out;
+--------+
| @p_out |
+--------+
| 2 |
+--------+
#调用了out_param存储过程,输出参数,改变了p_out变量的值
3、inout输入参数
mysql> delimiter $$
mysql> create procedure inout_param(inout p_inout int)
-> begin
-> select p_inout;
-> set p_inout=2;
-> select p_inout;
-> end
-> $$
mysql> delimiter ;
mysql> set @p_inout=1;
mysql> call inout_param(@p_inout);
+---------+
| p_inout |
+---------+
| 1 |
+---------+
+---------+
| p_inout |
+---------+
| 2 |
+---------+
mysql> select @p_inout;
+----------+
| @p_inout |
+----------+
| 2 |
+----------+
#调用了inout_param存储过程,接受了输入的参数,也输出参数,改变了变量
注意:
1、如果过程没有参数,也必须在过程名后面写上小括号例:
CREATE PROCEDURE sp_name ([proc_parameter[,…]]) ……
2、确保参数的名字不等于列的名字,否则在过程体中,参数名被当做列名来处理
建议:
- 输入值使用in参数。
- 返回值使用out参数。
- inout参数就尽量的少用。
附录
MYSQL 存储过程中的关键语法
(1)声明语句结束符,可以自定义:
DELIMITER $$
或
DELIMITER //
(2)声明存储过程:
CREATE PROCEDURE demo_in_parameter(IN p_in int)
(3)存储过程开始和结束符号:
BEGIN … END
(4)变量赋值:
SET @p_in=1
(5)变量定义:
DECLARE num int unsigned default 0;
(6)mysql存储过程的创建、调用、查询、删除:
创建存储过程:
create procedure 存储过程名(参数)
调用存储过程:
call 存储过程名[(传参)];
查询存储过程:
SHOW PROCEDURE STATUS LIKE ‘存储过程名’;
删除存储过程:
drop procedure if exists 存储过程名;
4.2 存储函数
一个简单的存储函数示例
#这是一个简单的存储函数示例
-- 显示存储函数列表(name字段是存储函数名)
SHOW FUNCTION STATUS LIKE 'func_student';
SHOW CREATE FUNCTION func_student;
-- 删除存储函数
drop FUNCTION if exists func_student;
DELIMITER //
-- 创建存储函数
CREATE FUNCTION func_student(id INT(11))
RETURNS VARCHAR(20)
COMMENT '查询某个学生的姓名'
BEGIN
RETURN(SELECT name FROM student WHERE student.id = id);
END //
DELIMITER ;
-- 调用存储函数
select func_student(2);
4.2.1 查看存储函数
查看存储函数
语法结构如下:SHOW FUNCTION STATUS LIKE '存储函数名';
或者
SHOW CREATE FUNCTION 存储函数名;
示例:
SHOW FUNCTION STATUS LIKE 'func_student';
或者
SHOW CREATE FUNCTION func_student;
4.2.2 创建存储函数
创建存储函数
语法结构如下:CREATE FUNCTION 存储函数名([参数名 数据类型[,参数名 数据类型…]]) [特性 ...] RETURNS 返回的数据类型 [COMMENT '注释内容'] BEGIN 函数体 END
关于存储函数的创建,语法结构如下: (1)创建无参的存储函数 delimiter $$ CREATE FUNCTION 存储函数名 () RETURNS 返回的数据类型 [COMMENT '注释内容'] BEGIN 存储函数体 END $$ delimiter ; (2)创建有参数的存储过程 delimiter $$ CREATE FUNCTION 存储函数名 (参数名1 参数类型,参数名2 参数类型……) RETURNS 返回的数据类型 [COMMENT '注释内容'] BEGIN 存储函数体 END $$ delimiter ;
注意事项:
(1)入参
括号内存储函数的所有参数:默认情况下,所有参数均为IN参数。不能为参数指定IN,OUT或INOUT修饰符。
(2)RETURNS
必须在RETURNS语句中指定返回值的数据类型。
它可以是任何有效的MySQL数据类型 。(3)函数体
在主体部分中,必须至少指定一个RETURN语句。
示例:
DELIMITER //
-- 创建存储函数
CREATE FUNCTION func_student(id INT(11))
RETURNS VARCHAR(20)
COMMENT '查询某个学生的姓名'
BEGIN
RETURN(SELECT name FROM student WHERE student.id = id);
END //
DELIMITER ;
4.2.3 删除存储函数
删除存储函数
语法结构如下:DROP FUNCTION IF EXISTS 存储函数名;
示例:
drop FUNCTION if exists func_student;
4.2.4 调用存储函数
调用存储函数
语法结构如下:(1)调用无参存储函数:
select 存储函数名();
备注:调用无参存储函数时,一定要加上(),否则会报错,无法调用。
(2)调用有参数的存储函数:
select 存储函数名([参数[,参数]]);
示例:
(1)调用无参存储函数:
select func_student();
(2)调用有参数的存储函数:
select func_student(2);
5 事务(TCL)
6 索引
7 权限管理与数据库备份
7.1 用户管理(DCL)
7.2 数据库备份
附录
参考资料 |
---|
MySQL详细学习教程(建议收藏) |
MySQL 教程 - 菜鸟教程 |