mysql数据库实例 schema mysql数据库实例大全

Mysql数据库命令整理及使用案例

• 显示数据库中运行的进程
• DDl

• *创建/删除·数据库
• *修改表结构
• *更新表数据

• 显示数据库中的存储过程**
• 查询数据库中的表
• 数据库存储过程整理
• 函数在存储过程中的使用
• 游标
• 条件处理程序解决游标报错
• 捕获异常信息-测试
• 存储函数
• 触发器

注：mysql版本较低时有些函数会不支持

显示数据库中运行的进程

show processlist;

DDl

*创建/删除·数据库

create datebase database_name;
drop datebase database_name;
**查看数据库**
USE mysql  --进入指定数据库
show databases;
select database();

**创建表**
create table table_name
(    
id  int
,name varchar(50)
,age     varchar(10)
);

**删除表**
drop table table_name;

**删除表数据**
delete from table_name where id = '001';  -- 此删除会保留日志可以恢复
truncate table table_name;  -- 此删除不会保留日志
*注: 删除表数据或者表时，会有磁盘碎片残留占用磁盘空间**

*修改表结构

alter table table_name ;
如下命令使用了 ALTER 命令及 DROP 子句来删除以上创建表的 i 字段：

mysql> ALTER TABLE testalter_tbl  DROP i;
如果数据表中只剩余一个字段则无法使用DROP来删除字段。

MySQL 中使用 ADD 子句来向数据表中添加列，如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型:
mysql> ALTER TABLE testalter_tbl ADD i INT;
执行以上命令后，i 字段会自动添加到数据表字段的末尾。

如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 
FIRST (设定位第一列)
,AFTER 字段名（设定位于某个字段之后）。
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

使用 SHOW COLUMNS 查看表结构的变化
SHOW COLUMNS FROM testalter_tbl;

FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句，所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。

修改字段类型及名称
如果需要修改字段类型及名称
, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。

例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);
使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后
，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
mysql> ALTER TABLE testalter_tbl CHANGE j j INT;
ALTER TABLE 对 Null 值和默认值的影响

当你修改字段时，你可以指定是否包含值或者是否设置默认值。
以下实例，指定字段 j 为 NOT NULL 且默认值为100 。

mysql> ALTER TABLE testalter_tbl 
    -> MODIFY j BIGINT NOT NULL DEFAULT 100;
如果你不设置默认值，MySQL会自动设置该字段默认为 NULL。

修改字段默认值
你可以使用 ALTER 来修改字段的默认值，尝试以下实例：

mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
mysql> SHOW COLUMNS FROM testalter_tbl;

*更新表数据

update table_name set name = '王昭君' where id = '001';

显示数据库中的存储过程**

show PROCEDURE status;
--显示指定的存储过程
show PROCEDURE status WHERE name LIKE  '%execute_prepared_stmt%';

查询数据库中的表

show tables;   --所有表
show tables IN mysql LIKE '%help%'; --模糊查询指定表

数据库存储过程整理

--查看系统变量
show session variables;
show session variables LIKE 'auto%' ;
show global variables LIKE 'auto%'  ;

--查询指定的系统变量值
SELECT @@autocommit;
SELECT @@global.autocommit;
SELECT @@session.autocommit;

--设置系统变量
SET [session | global] = value;
SET session autocommit = 0 ;
SET global autocommit = 0 ;

--用户变量  注: 用户的的变量不需要提前声明,在用的时候直接用 @变量名 使用就可以。其作用域为当前连接
--赋值
--等于和冒号等于都可以使用，都是赋值,推荐使用 :=
--设置用户变量
SET @var_name=expr[,@var_name=expr]
SET @var_name=expr[,@var_name:=expr]

SET @myname = 'itcast';
SET @myage := 10 ;
SET @myaender := '男', @myhobby := 'java' ;

SELECT * FROM k_user
SELECT @color := 'red'; -- 赋值并查看
SELECT u_nickname INTO @mycount FROM k_user; --将查询结果赋值给变量
SELECT @mycount; --查看赋值结果

--使用变量
SELECT @myname,@myage,@myaender,@myhobby;


--局部变量
--是根据需要定义的在局部生效的变量,访问之前,需要declare声明. 可用与存储过程内的局部变量和输入参数
--,局部变量的范围是在期内声明的 bengin ... END 块
--变量数据类型字段类型: int begint char varchar date time 等
--赋值

SET 变量名 = 值
SET 变量名 := 值
SET 字段名 INTO 变量名 fron 表明...;

## 创建存储过程
```sql
--查找存储过程
show PROCEDURE status  WHERE name LIKE '%p2%'
-- 声明与多种赋值方式
delimiter $$  -- 命令行中执行时需要定义结束名，不然在命令行中识别到分号就会报错结束
CREATE PROCEDURE p2()
BEGIN
	DECLARE stu_count INT  DEFAULT 0 ;
	SELECT count(*) INTO stu_count FROM t_user ;
	SELECT stu_count;
END ;
$$

--执行存储过程
CALL p2();
--删除存储过程
drop procedure procedure_name ;

函数在存储过程中的使用

-- if函数

create procedure p3()
begin
    declare score int default 58 ;
     declare result varchar(10);

    if score >= 85 then
        set result := '优秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if ;
    select result;
end;

call p3() ;


--参数
in 该类参数作为输入，也就是需要调用时传入值
out 该类参数作为输出，也就是改参数可以作为返回值
inout 既可以作为输入参数，也可以作为输出参数

用法:
create procedure 存储过程名称(in/out/inout 参数名 参数类型)
begin
    --sql语句
end;


drop procedure  p4;
create procedure p4(in score int ,out result varchar(100))
begin
      -- declare score int default 58 ;
      -- declare result varchar(10);
      set score := score ;
    if score >= 85 then
        set result := '优秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if ;
    select result
    union
    select score
       union
    select @score;

end;


call p4(90,@result);
show PROCEDURE status  WHERE name LIKE '%p4%'



--repeat   计算从1累加到n的值，n为出入的参数值
--A. 定义局部变量，记录累加之后的值
--B. 每循环一次，就会对n进行-1，如果n减到0，则推出循环

create procedure p8(in n int )
begin
    declare total int default 0;

    repeat
        set total := total + n ;
        set n := n -1 ;
    until n <= 0
    end repeat;
    select total;
end;

call p8 (10 ) ;
call p8 (100 ) ;
show PROCEDURE status  WHERE name LIKE '%p8%'

-- loop 循环
-- 实现简单的循环，如果不在sql逻辑中曾键退出循环的条件，可以用其来实现简单的死循环。loop可以配合一下两个语句使用；
-- leave：配合循环使用，退出循环。
-- iterate: 必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

--loop 计算从1累加到n的值，n为出入的参数值。
--A. 定义局部变量，记录累加之后的值。
--B. 每循环一次，就会对n进行-1，如果n减到0，则退出循环 ----> leave xx

create procedure p9 (in n int )
begin
    declare total int default 0 ;
    sum: loop
        if n< 0 then
            leave sum;
        end if;

        set total := total + n;
        set n := n-1;
        end loop sum;
        select total;
end;

call p9(10);

游标

注： 声明变量必须在声明游标之前·数据库sql规范

游标(CURSOR) 是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标
的声明、OPEN\FETCH\CLOSE,其语法分别如下。

-- 声明游标
declare 游标名称 curse for select 查询语句；
-- 打开游标
open 游标名称;
-- 获取游标记录
fetch 游标名称 into 变量[,变量];
-- 关闭游标
close 游标名称;

--通过变量记录查询结果
根据传入的参数uahe，来查询用户表tb_user中，所有用户年龄小于uage的用户姓名（name）和专业（profession),并根据用户的姓名和专业插入到所创建的一张新表中（id，name，profession）中。

-- 逻辑：
-- A.声明游标，存储查询结果表
-- B. 准备：创建表结构
-- C. 打开游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标

DROP TABLE tb_user ;
CREATE TABLE tb_user
(
ID int primary KEY auto_increment
,name VARCHAR(100)
,age INT
,profession varchar(100)

);

INSERT INTO tb_user (name,age,profession )
VALUES ('吕布',25,'将军');
INSERT INTO tb_user (name,age,profession )
VALUES ('王昭君',19,'公主');
INSERT INTO tb_user (name,age,profession )
VALUES ('貂蝉',23,'舞女');

-- 如果已存在次存储过程就删除
drop procedure if exists  p11 ;
create procedure p11(in uage int )
begin
	declare uname varchar(100);
	declare upro varchar(100);
	declare u_cursor cursor for  select name,profession from tb_user where age<= uage;

	create table if not exists tb_user_pro(
	id int primary key auto_increment
	,name varchar(100)
	,profession varchar(100)
	);

	open u_cursor;
 	while true do
		fetch u_cursor into uname,upro;
		insert into tb_user_pro values(null, uname, upro);
 	end while;
	close u_cursor ;
end;

-- 执行存储过程
	call p11( 22) ;

-- 执行存储过程
	call p11() ;

条件处理程序解决游标报错

可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为：

declare handler_action handler for condition_value [,condition_value]... statement;

handler_action
	continue: 继续执行当前程序
	exit: 终止执行当前程序

condition_value
	sqlstate sqlstate_valew: 状态码，如02000
	sqlwarning: 所有以01开头的sqlstate代码的简写
	not found: 所有以02开头的sqlstate代码简写
	sqlexception: 所有没有被sqlwarining 或 not found 捕获的sqlstate代码简写

DROP TABLE tb_user ;
CREATE TABLE tb_user
(
ID int primary KEY auto_increment
,name VARCHAR(100)
,age INT
,profession varchar(100)

);

INSERT INTO tb_user (name,age,profession )
VALUES ('吕布',25,'将军');
INSERT INTO tb_user (name,age,profession )
VALUES ('王昭君',19,'公主');
INSERT INTO tb_user (name,age,profession )
VALUES ('貂蝉',23,'舞女');

SELECT * FROM tb_user ;
show procedure  status where Name like '%p%' ;
show tables  like 't%';

drop procedure p11 ;
create procedure p11(in uage int )
begin
	declare uname varchar(100);
	declare upro varchar(100);
    declare u_cursor cursor for  select name,profession from tb_user where age<= uage;

	-- 条件处理程序，抓取循环遍历时没有数据时捕获异常 并退出
	-- 三种捕获形式 一种是按报错编码捕获 一种是按所有的01或02开头的定义命令捕获，还有以一种是捕获其他异常信息
	-- declare exit handler for sqlstate '02000' close u_cursor;
	-- declare exit handler for not found ,sqlwarning close u_cursor;
    declare exit handler for sqlexception,sqlwarning, not found close u_cursor;
    drop table if exists tb_user_pro ;
	create table if not exists tb_user_pro(
	id int primary key auto_increment
	,name varchar(100)
	,profession varchar(100)
	);
	open u_cursor;
 	while true do
		fetch u_cursor into uname,upro;
		insert into tb_user_pro values(null, uname, upro);
 	end while;
	close u_cursor ;
end;

执行存储过程并查看结果
call p11(30) ;
select * from tb_user_pro ;

捕获异常信息-测试

drop  procedure if exists p10 ;

create procedure p10()
begin
-- 定义错误处理
DECLARE EXIT HANDLER FOR SQLEXCEPTION
    begin
      GET CURRENT DIAGNOSTICS CONDITION 1
      @errno = MYSQL_ERRNO, @msg = MESSAGE_TEXT;

      select @errno,@msg;
    end;
    begin
        select * from my ;
    end;
end;
call  p10();

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是in类型（默认也是in类型）。

--语法
create function 存储函数名称([参数列表])
returns type [characteristic... ]
begin 
	-- sql语句
	return ... ;
end ;


characteristic说明:
· deterministic: 相同的输入参数总是产生相同的结果
· no sql: 不含sql语句
· reads sql data: 包含读取数据的语句，但不包写入数据的语句


-- 存储函数
-- 从1到n的累加
create function fun1(n int )
returns int
begin
    declare total int default 0;

    while n>0 do
        set total := total + n ;
        set n := n-1;
    end while ;
    return total ;
 end ;

-- 执行查询函数
select fun1(10) ;

触发器

触发器是与表有关的数据库对象，指在insert/update/date之前或在之后，触发并执行触发器中定义的sql语句稽核。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
使用别名OLD和NEW来引用触发器中发生辩护的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发（比如更新或修改了几行数据就触发几次），不支持语句级触发（比如更新或修改了几行数据只触发一次）。

触发器类型：
insert型出发器： new表述将要或者已经新增的数据
update星触发器： old表示修改之前的数据，new表示将要或已经修改后的数据
delete型触发器： old表示将要或者已将删除的数据

通过触发器记录tb_user 表的数据变更日志，将user_logs中，包含增加，修改，删除；
drop table if exists user_logs ;
create table user_logs(
id int(11) not null auto_increment
,operation varchar(20) not null comment '操作类型inser/update/delete'
,oper_time datetime not null comment '操作时间'
,operate_id int(11) not null comment '操作id'
,operate_params varchar(500) comment '操作参数'
,primary key (id )
) engine=innodb default charset=utf8 ;

select * from user_logs;

-- 创建案例表
DROP TABLE tb_user ;
CREATE TABLE tb_user
(
ID int primary KEY auto_increment
,name VARCHAR(100)
,age INT
,phone VARCHAR(100)
,email VARCHAR(100)
,profession varchar(100)

);

INSERT INTO tb_user (name,age,profession )
VALUES ('吕布',25,'将军');
INSERT INTO tb_user (name,age,profession )
VALUES ('王昭君',19,'公主');
INSERT INTO tb_user (name,age,profession )
VALUES ('貂蝉',23,'舞女');
select* from tb_user ;

drop table if exists user_logs ;
create table user_logs(
id int(11) not null auto_increment
,operation varchar(20) not null comment '操作类型inser/update/delete'
,oper_time datetime not null comment '操作时间'
,operate_id int(11) not null comment '操作id'
,operate_params varchar(500) comment '操作参数'
,primary key (id )
) engine=innodb default charset=utf8 ;

select * from user_logs user_logs;

## 开始创建触发器
-- 注 : 触发其中更新字段时摸个字段是空值时进入日志表的信息就为null值了，所以 需要在触发器的拼接函数中使用ifnull处理 例如: ifnull(new.phone)
-- 插入数据触发器
create trigger tb_user_insert_trigger
	after insert on tb_user for each row
begin
    insert into user_logs(id,operation,oper_time,operate_id,operate_params)
    values (null,'insert',now(),new.id,concat('插入的数据内容为: id ',new.id,',name=',new.name,',phone=', ifnull(new.phone),',email=',new.email,', profession=', new.profession));
end;

-- 查看触发器
show triggers ;
-- 删除触发器
drop trigger tb_user_insert_trigger;

--插入一条数据测试触发器
INSERT INTO tb_user (id,name,age,email,profession )
VALUES (null,'貂蝉11',23,'@adsadas','舞女');
--查询日志表中触发器的记录
select * from user_logs order by oper_time desc ;