数据库的操作
结构化查询语句的分类
数据库的操作
命令行操作数据库
创建数据库 :
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解释器才会执行这段语句
349
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
