数据库的操作
结构化查询语句的分类
数据库的操作
命令行操作数据库
创建数据库 :
create database [if not exists] 数据库名;
删除数据库 :
drop database [if exists] 数据库名;
修改数据库:
修改表 ( ALTER TABLE )
查看数据库 : show databases;
使用数据库 : use 数据库名;
关于数据表的操作:
创建数据表(CREATE TABLE)
属于DDL的一种,语法 :
create table [if not exists] `表名`(
'字段名1' 列类型 [属性][索引][注释],
'字段名2' 列类型 [属性][索引][注释],
#...
'字段名n' 列类型 [属性][索引][注释]
)[表类型][表字符集][注释];
说明 : 反引号用于区别MySQL保留字与普通字符而引入的 (键盘esc下面的键).如上所示。
- 以下的操作都与创建差不多,现在就以创建表来讲解对表的类型属性等等
列类型(4种) :
规定数据库中该列存放的数据类型
- 数值类型
- 字符串类型
- 日期和时间型数值类型
- NULL值
- 理解为 “没有值” 或 “未知值”
- 不要用NULL进行算术运算 , 结果仍为NULL
数据字段属性(5种)
-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段)
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
-- 创建表之前 , 一定要先选择数据库
CREATE TABLE IF NOT EXISTS `student` (
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` datetime DEFAULT NULL COMMENT '生日',
`address` varchar(100) DEFAULT NULL COMMENT '地址',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
-- 查看数据库的定义
SHOW CREATE DATABASE school;
-- 查看数据表的定义
SHOW CREATE TABLE student;
-- 显示表结构
DESC student; -- 设置严格检查模式(不能容错了)SET sql_mode='STRICT_TRANS_TABLES';
1. UnSigned
- 无符号的
- 声明该数据列不允许负数 .
2. ZEROFILL
- 0填充的
- 不足位数的用0来填充 , 如int(3),5则为005
3. Auto_InCrement
-
自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1(默认)
-
通常用于设置主键 , 且为整数类型
-
可定义起始值和步长
-
- 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
- SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)
4. NULL 和 NOT NULL
- 默认为NULL , 即没有插入该列的数值
- 如果设置为NOT NULL , 则该列必须有值
5. DEFAULT
- 默认的
- 用于设置默认值
- 例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值
数据表的类型
设置数据表的类型
CREATE TABLE 表名(
-- 省略一些代码
-- Mysql注释
-- 1. # 单行注释
-- 2. /*...*/ 多行注释
)ENGINE = MyISAM (or InnoDB)
-- 查看mysql所支持的引擎类型 (表类型)
SHOW ENGINES;
MySQL的数据表的类型 : MyISAM , InnoDB , HEAP , BOB , CSV等…
常见的 MyISAM 与 InnoDB 类型:
经验 ( 适用场合 ) :
- 适用 MyISAM : 节约空间及相应速度
- 适用 InnoDB : 安全性 , 事务处理及多用户操作数据表
数据表的存储位置
-
MySQL数据表以文件方式存放在磁盘中
-
- 包括表文件 , 数据文件 , 以及数据库的选项文件
- 位置 : Mysql安装目录\data\下存放数据表 . 目录名对应数据库名 , 该目录下文件名对应数据表 .
-
注意 :
-
-
* . frm – 表结构定义文件
-
* . MYD – 数据文件 ( data )
-
* . MYI – 索引文件 ( index )
-
InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的ibdata1文件
-
MyISAM类型数据表对应三个文件 :
-
设置数据表字符集
我们可为数据库,数据表,数据列设定不同的字符集,设定方法 :
- 创建时通过命令来设置 , 如 : CREATE TABLE 表名()CHARSET = utf8;
- 如无设定 , 则根据MySQL数据库配置文件 my.ini 中的参数设定
删除数据表(DROP TABLE)
语法:DROP TABLE [IF EXISTS] 表名
- IF EXISTS为可选 , 判断是否存在该数据表
- 如删除不存在的数据表会抛出错误
修改数据表(ALTER TABLE)
修改表名 :ALTER TABLE 旧表名 RENAME AS 新表名
添加字段 : ALTER TABLE 表名 ADD字段名 列属性[属性]
修改字段 :
- ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
- ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]
删除字段 : ALTER TABLE 表名 DROP 字段名
DDL
create table
create table表名 (
列名1 列类型1 列1参数,
列名2 列类型2 列2参数,
....
列名n 列类型n 列n参数
)engine myisam/innodb/bdb charset utf8/gbk/latin1...
alter table
增加或删除或修改列
增加列
语句格式①:
Alter table 表名 add 列名称 列类型 列参数;[加的列在表的最后]
alter table m1 add birth date not null default '0000-00-00';
语句格式②:
Alter table 表名 add 列名称 列类型 列参数 after 某列[把新列加在某列后]
Alter table m1 add gender char(1) not null default '' after username;
语句格式③:
Alter table 表名 add 列名称 列类型 列参数 first[把新列加在最前面]
Alter table m1 add pid int not null default 0 first
修改列
1、修改列类型:
Alter table 表名 modify 列名 列的新类型 列的新参数
例: alter table m1 modify gender char(4) not null default '';
2、修改列名及列类型:
Alter table 表名 change 旧列名 新列名 新类型 新参数
例: alter table m1 change id uid int unsigned;
删除列
Alter table 表名 drop 列名
drop table
DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]
DML
Insert
INSERT INTO 表名 (字段名 1, 字段名 2, 字段名 3…) VALUES (值 1, 值 2, 值 3);
1.可以不写字段名
2.可以只插入部分字段
Delete
不带条件删除数据
DELETE FROM 表名;
带条件删除数据
DELETE FROM 表名 WHERE 字段名=值;
删除表中所有记录
TRUNCATE TABLE 表名;
-- truncate 和 delete 的区别:
delete 是将表中的数据一条一条删除
truncate 是将整个表摧毁,重新创建一个新的表,新的表结构和原来表结构一模一样
Update
不带条件修改数据
UPDATE 表名 SET 字段名=值;
带条件修改数据
UPDATE 表名 SET 字段名=值 WHERE 字段名=值;
关键字说明
UPDATE: 修改数据
SET: 修改哪些字段
WHERE: 指定条件
DQL
select [all|distinct]目标表达式 别名,目标表达式 别名...
from表或者视图名 别名
where 条件表达式
group by 列1 having条件表达式
order by 列2 ASC|DESC;
1. 目标表达式
(1)*
(2)表名.*
(3)count([all|distinct]*)
(4)表名.列名,表名.列名…
2. 聚集函数
count sum avg max min([distinct|all]列名)
3.where子句条件表达式的格式
1.属性列名 not between A and B
2.属性列名not in A
3.属性列名not like 匹配串
4.属性列名is not null
5. not exist(select 语句)
6.条件表达式AND/OR条件表达式AND/OR条件表达式AND/OR...
DCL
授权:grant(root有授权权限)
GRANT 权限 1, 权限 2... ON 数据库名. 表名 TO ' 用户名'@' 主机名';
回收权限:revoke
REVOKE 权限 1, 权限 2... ON 数据库. 表名 revoke all on test.* from 'user1'@'localhost'; ' 用户名'@' 主机名';
索引
存储过程
语法格式
创建:
create procedure 名称()
begin
.........
end
调用:
call 名称();
存储过程的变量
create procedure test2()
begin
-- 使用 declare语句声明一个变量
declare username varchar(32) default '';
-- 使用set语句给变量赋值
set username='xiaoxiao';
-- 将users表中id=1的名称赋值给username
select name into username from users where id=1;
-- 返回变量
select username;
end;
1、declare:声明变量,只能先声明后使用,一次只能声明一个变量;变量具有数据类型和长度,与mysql的SQL类型保持一致,所以我们还可以指定默认值、字符集、排序规则。
2、通过set
和select 表中的字段 into 新声明的字段
赋值
3、变量需要返回,可以使用select语句,如:select 变量名
。
变量作用域
1、变量的作用域在begin和end之间
2、如果需要多个块之间传值,可以使用全局变量,即把变量声明的所有代码块前
3、传参变量是全局的,可以多个模块使用
没使用全局变量前
create procedure test3()
begin
begin
declare userscount int default 0; -- 用户表中的数量
declare ordercount int default 0; -- 订单表中的数量
select count(*) into userscount from users;
select count(*) into ordercount from orders;
select userscount,ordercount; -- 返回用户表中的数量、订单表中的数量
end;
begin
declare maxmoney int default 0; -- 最大金额
declare minmoney int default 0; -- 最小金额
select max(money) into maxmoney from orders;
select min(money) into minmoney from orders;
select maxmoney,minmoney; -- 返回最金额、最小金额
end;
end;
使用后
create procedure test3()
begin
declare userscount int default 0; -- 用户表中的数量
declare ordercount int default 0; -- 订单表中的数量
begin
select count(*) into userscount from users;
select count(*) into ordercount from orders;
select userscount,ordercount; -- 返回用户表中的数量、订单表中的数量
end;
begin
declare maxmoney int default 0; -- 最大金额
declare minmoney int default 0; -- 最小金额
select max(money) into maxmoney from orders;
select min(money) into minmoney from orders;
select userscount,ordercount,maxmoney,minmoney; -- 返回最金额、最小金额
end;
end;
存储过程参数
基本语法:
create procedure 名称([IN|OUT|INOUT] 参数名 参数数据类型 )
begin
.........
end
说明:
in(传入参数):必须在存储过程调用中指定,如果显示不指定in,那么默认也是in,此类型一般用于传入,在调用过程中不作为修改和返回
out:如果调用存储过程中需要修改和返回值,可以使用OUT类型参数
in类型实例:
create procedure test4(userId int)
begin
declare username varchar(32) default '';
declare ordercount int default 0;
select name into username from users where id=userId;
select username;
end;
out类型实例: 需求:调用存储过程时,传入userId返回该用户的name
create procedure test5(in userId int,out username varchar(32))
begin
select name into username from users where id=userId;
end;
总结:
1、传入参数:在调用过程中,可以修改它的值,并且返回
2、out类型:只能用于传出参数,不能传入参数
3、调用存储过程中,out类型参数也需要指定,并且不能是常量,必须是变量
4、如果需要传入有传出,就是用inout类型参数
inout可变参数:
需求:调用存储过程时,传入userId和userName,即使传入,也是传出参数。
create procedure test6(inout userId int,inout username varchar(32))
begin
set userId=2;
set username='';
select id,name into userId,username from users where id=userId;
end;
说明:
1、可变变量INOUT:调用时可传入值,在调用过程中,可修改其值,同时也可返回值;
2、INOUT参数集合了IN和OUT类型的参数功能;
3、INOUT调用时传入的是变量,而不是常量;
存储过程条件语句
基本结构:
if() then...else...end if;
或者
if() then...
elseif() then...
else ...
end if;
实例1:
create procedure test7(in userId int)
begin
declare username varchar(32) default '';
if(userId%2=0)
then
select name into username from users where id=userId;
select username;
else
select userId;
end if;
end;
实例2:
需求:根据用户传入的uid参数判断
(1)、如果用户状态status为1,则给用户score加10分;
(2)、 如果用户状态status为2,则给用户score加20分;
(3)、 其他情况加30分
create procedure test8(in userid int)
begin
declare my_status int default 0;
select status into my_status from users where id=userid;
if(my_status=1)
then
update users set score=score+10 where id=userid;
elseif(my_status=2)
then
update users set score=score+20 where id=userid;
else
update users set score=score+30 where id=userid;
end if;
end;
存储过程循环语句
基本结构:
while(表达式) do
......
end while;
实例:
create procedure test9()
begin
declare i int default 0;
while(i<10) do
begin
select i;
set i=i+1;
insert into test1(id) values(i);
end;
end while;
end;
repeat语句基本的结构:
repeat...until...end repeat;
实例:
create procedure test10()
begin
declare i int default 0;
repeat
begin
select i;
set i=i+1;
insert into test1(id) values(i);
end;
until i>=10 -- 如果i>=10,则跳出循环
end repeat;
end;
游标
1、什么是游标
游标是保存查询结果的临时区域
2、示例
需求:编写存储过程,使用游标,把users表中 id为偶数的记录逐一更新用户名
create procedure test11()
begin
declare stopflag int default 0;
declare username VARCHAR(32);
-- 创建一个游标变量,declare 变量名 cursor ...
declare username_cur cursor for select name from users where id%2=0;
-- 游标是保存查询结果的临时区域
-- 游标变量username_cur保存了查询的临时结果,实际上就是结果集
-- 当游标变量中保存的结果都查询一遍(遍历),到达结尾,将变量stopflag设置为1,用于循环中判断是否结束
declare continue handler for not found set stopflag=1;
open username_cur; -- 打卡游标
fetch username_cur into username; -- 游标向前走一步,取出一条记录放到变量username中
while(stopflag=0) do -- 如果游标还没有结尾,就继续
begin
-- 在用户名前门拼接 '_cur' 字符串
update users set name=CONCAT(username,'_cur') where name=username;
fetch username_cur into username;
end;
end while; -- 结束循环
close username_cur; -- 关闭游标
end;
自定义函数
基本结构:
create function 函数名(参数) returns 返回类型;
说明:
- 函数体放在begin和end之间;
- returns指定函数的返回值;
- 函数调用使用select 函数名。
函数与存储过程最大的区别是函数必须有返回值,否则会报错
create function getusername(userid int) returns varchar(32)
reads sql data -- 从数据库中读取数据,但不修改数据
begin
declare username varchar(32) default '';
select name into username from users where id=userid;
return username;
end;
实例: 需求:根据userid,获取accoutid,id,name组合成UUID作为用户的唯一标识
create function getuuid(userid int) returns varchar(64)
reads sql data -- 从数据库中读取数据,但不修改数据
begin
declare uuid varchar(64) default '';
select concat(accontid,'_',id,'_',name) into uuid from users where id=userid;
return uuid;
end;
触发器
触发器与函数、存储过程一样,触发器是一种对象,它能根据对表的操作时间,触发一些动作,这些动作可以是insert,update,delete等修改操作。
触发器不允许返回数据集,以致由触发器所调用的存储过程或函数也不可以返回数据集,这是MySQL
的限制。
基本结构:
create trigger 触发器名 [before|after] [insert|update|delete|...] on 表明
for each row
说明:
影响的范围?for each row
**实例1:**出于审计目的,当有人往表users插入一条记录时,把插入的userid,username,插入动作和操作时间记录下来。
create trigger tr_users_insert after insert on users
for each row
begin
insert into oplog(userid,username,action,optime)
values(NEW.id,NEW.name,'insert',now());
end;
**实例2:**出于审计目的,当删除users表时,记录删除前该记录的主要字段值
create trigger tr_users_delete before delete on users
for each row
begin
insert into oplog(userid,username,action,optime)
values(OLD.id,OLD.name,'delete',now());
end;
流程控制
基本结构:
case ...
when ... then....
when.... then....
else ...
end case;
实例: users表中,根据userid获取status值,如果status为1,则修改score为10;如果status为2,则修改为20,如果status3,则修改为30;否则修改为40。
create procedure testcate(userid int)
begin
declare my_status int default 0;
select status into my_status from users where id=userid;
case my_status
when 1 then update users set score=10 where id=userid;
when 2 then update users set score=20 where id=userid;
when 3 then update users set score=30 where id=userid;
else update users set score=40 where id=userid;
end case;
end;
mysql存储过程学习笔记,此存储过程笔记包括了函数、触发器等
疑问:
1、unsigned的作用
unsigned 既为非负数,用此类型可以增加数据长度!
例如如果 tinyint最大是127,那 tinyint unsigned 最大 就可以到 127 * 2
unsigned 属性只针对整型,而binary属性只用于char 和varchar。
2、外键是什么
当一个表的列被引用作为另一个表的主键值的列时,就在两表之间创建了链接。这个列就成为第二个表的外键。
是因为他引用了别的表的主键,所以他才叫外键
3、约束有哪些?
1、约束条件分类:
1)not null :非空约束,保证字段的值不能为空
s_name VARCHAR(10) NOT NULL, #非空
2)default:默认约束,保证字段总会有值,即使没有插入值,都会有默认值!
age INT DEFAULT 18, #默认约束
3)unique:唯一,保证唯一性但是可以为空,比如座位号
s_seat INT UNIQUE,#唯一约束
4)check:检查性约束【MySQL不支持,语法不报错,但无效】
s_sex CHAR(1) CHECK(s_sex=‘男’ OR s_sex=‘女’),#检查约束(Mysql无效)
5)primary key :主建约束,同时保证唯一性和非空
id INT PRIMARY KEY,#主建约束(唯一性,非空)
6)foreign key:外键约束,用于限制两个表的关系,保证从表该字段的值来自于主表相关联的字段的值!
teacher_id INT REFERENCES teacher(id) #这是外键,写在列级,Mysql无效
4、 delimiter的作用
其实就是告诉mysql解释器,该段命令是否已经结束了,mysql是否可以执行了。
默认情况下,delimiter是分号**;**。在命令行客户端中,如果有一行命令以分号结束,
那么回车后,mysql将会执行该命令。如输入下面的语句
mysql> select * from test_table;
然后回车,那么MySQL将立即执行该语句。
但有时候,不希望MySQL这么做。在为可能输入较多的语句,且语句中包含有分号。我们还不希望它马上执行,因为命令还没有打完,这时候就需要delimiter分界了
这种情况下,就需要事先把delimiter换成其它符号,如//或$$。
mysql> delimiter //
mysql> CREATE FUNCTION `SHORTEN`(S VARCHAR(255), N INT)
mysql> RETURNS varchar(255)
mysql> BEGIN
mysql> IF ISNULL(S) THEN
mysql> RETURN '';
mysql> ELSEIF N<15 THEN
mysql> RETURN LEFT(S, N);
mysql> ELSE
mysql> IF CHAR_LENGTH(S) <=N THEN
mysql> RETURN S;
mysql> ELSE
mysql> RETURN CONCAT(LEFT(S, N-10), '...', RIGHT(S, 5));
mysql> END IF;
mysql> END IF;
mysql> END;//
这样只有当//出现之后,mysql解释器才会执行这段语句