数据库操作

数据库操作

最近做web开发，跟数据库打交道的时间更多了，记录一下最近常用到的数据库知识，以免遗忘了。

• 数据库的创建

数据库的创建
create database [if not exists] db_name [charset 字符集]

查看数据库下面的表
show db_name

查询指定表的表结构
desc table_name

删除一个数据库
drop database if exists db_name

• 数据表的创建

create table tableName(列名1 类型 属性，列名2 类型 属性，...);
如：

drop TABLE IF EXISTS sh_auth;
CREATE TABLE sh_auth (
  auth_id  MEDIUMINT(6) unsigned NOT NULL AUTO_INCREMENT,
  auth_name  varchar(20) NOT NULL COMMENT '名称',
  auth_pid  MEDIUMINT(6) unsigned NOT NULL COMMENT '父级id',
  auth_c  varchar(32) NOT NULL default '' COMMENT '控制器',
  auth_a  varchar(32) NOT NULL default '' COMMENT '操作方法',
  auth_path  varchar(32) NOT NULL default '' COMMENT '全路径',
  auth_level  tinyint(4) NOT NULL default '0' COMMENT '级别',
  primary key (auth_id)
) ENGINE=InnoDB CHARSET=utf8 comment '权限表';

• 修改数据表结构

新增字段
alter table sh_auth add test int unsigned not null default 0 comment '测试' after goods_name

修改字段
alter table table_name modify 字段名称 字段类型 [完整性约束条件] [first|after 字段名称]

修改字段名称
alter table table_name change 旧字段名称 新字段名称 字段类型 [完整性约束条件] [FIRST|AFTER 字段名称]

添加默认值
alter table table_name alter 字段名称 set default 默认值

删除默认值
alter table table_name alter 字段名称 drop default

添加主键
alter table table_name add [constraint [symbol]] primary key[index_type] (字段名称,...)

删除主键
alter table table_name drop primary key

添加唯一索引
alter table table_name add [constraint [symbol]] unique [index|key] [索引名称](字段名称,...)

删除唯一索引
alter table table_name drop {index|key} index_name

删除字段
alter tabl table_name drop 字段名称

修改表名
alter table table_name rename [to|as] new_name

修改表的存储引擎
alter table table_name engine=存储引擎名称

设置自增长的值
alter table table_name auto_increment=值

MySQL数据类型

• 整型数据类型

类型	取值范围	存储空间	说明
tinyint	-128~127(有符号)或0~255	1	最小整数定义
smallint	-32768~32767(有符号)或0~255	2	小型整数
mediumint	-8388608~8388607或0~16777215	3	中型整数
int	-2147483648~2147483647或0~2^32-1	4	一般整数
bigint	0~2^64 - 1	8	大型整数

* 浮点数据类型

类型	取值范围	存储空间	说明
float[(m,d)]	+(-)1.175494351E-38 ~ +(-)3.402823466E+38	4	M为总位数，D为小数位数，精确到大约7位小数
double[(m,d)]	+(-)1.7976931348623157E+308 ~ +(-)2.2250738585072014E-308	8	M为总位数，D为小数位数，精确到大约15位小数

* 日期和时间类型

类型	取值范围	说明
date	1000-01-01 9999-12-31	日期，以YYYY-MM-DD格式显示
time	-838:59:59 838:59:59	时间，以HH:MM:SS格式显示
datetime	1000-01-01 00:00:00 9999-12-31 23:59:59	日期和时间，以YYYY-MM-DD HH:MM:SS格式显示
year	1901~2155	年份的格式
timestamp[(M)]	1970-01-01 00:00:01~2038-01-19 03:14:07	时间戳。TIMESTAMP值返回后显示为‘YYYY-MM-DD HH:MM:SS’格式的字符串，显示宽度固定为19个字符

* 字符串类型

类型	取值范围	说明
char(M)	0-255	固定长度为M的字符串，其中M的取值范围为0~255
varchar(M)	0~65535	长度可变，其他同char(M)一样
Tingtest	1-2^8 (255)	小text字段
text	1-2^16 (1677万)	常规text字段
longtext	2^32-1 (42亿)	长text字段

SQL语句基础

• 新增数据

INSERT INTO st_posts(keywords, title) VALUES('测试', '标题');

• 删除数据

DELETE FROM st_posts where id = 3;

• 重新创建表，清空表中的数据，重置auto_increment计数器

TRUNCATE st_posts;

• 更新数据

UPDATE st_posts SET keywords='关键字1',title='标题1' WHERE id=1;

• 查询数据
  格式：SELECT 字段列表|* FROM tab_name [WHERE条件] [ORDER BY排序规则] [LIMIT数量限定]

SELECT * FROM st_posts where id > 5 ORDER BY createtime LIMIT 5;

ORDER BY: 默认ESC升序 DESC降序

连接查询

连接查询分为：内连接、外链接、自然连接和交叉连接（后两种基本不用，就不介绍了）
* 内连接
内连接：[inner join]从左表取出每一条记录和右表中所有记录进行匹配，查询出符合条件的记录
如：

SELECT st_terms.name FROM st_terms INNER JOIN st_term_posts_r ON st_terms.id = st_term_posts_r.term_id

• 外连接
  外连接：outer join 以一张主表取出所有记录和宁外一张表进行匹配，满足条件的数据保留，不满足条件的从表字段以null显示
  分类：左外连接 left join 以左表为主表

SELECT * FROM st_terms left JOIN st_term_posts_r ON st_terms.id = st_term_posts_r.term_id

右外连接 right join 以右表为主表

SELECT * FROM st_terms RIGHT JOIN st_term_posts_r ON st_terms.id = st_term_posts_r.term_id

联合查询

联合查询：将多次查询（多条select语句），在记录上进行拼装（字段不会增加）
如：查询文章表中作者id=5按时间降序排列和作者id=1按时间升序排列的文章
注意： 要使order by生效 要配合 “LIMIT 最大数”使用

(SELECT * from st_posts where author = 5 ORDER BY createtime desc LIMIT 999999)
UNION 
(SELECT * from st_posts where author = 1 ORDER BY createtime LIMIT 999999)

视图

视图的创建：
Create view 视图名字 as select语句（select语句可以使普通查询、连接查询、联合查询等）

CREATE VIEW myView as select * FROM st_terms

存储过程

存储过程就是一系列SQL语句的集合。

delimiter $$; // 改变分隔符，不要让;作为执行结束的标记

create procedure p_test()
begin
declare a varchar(20) default 'aaa'; // 存储过程内部定义参数
select a;
end
$$;

delimiter ; // 替换回;分隔符

call p_test() // 调用

存储过程的参数：
in:输入参数（该值必须在存储过程调用之前指定，在存储过程中修改的值不能返回）
out:输出参数（该值可在存储过程内部改变，并返回）
inout:输入输出参数（该值在调用时指定，并可修改和返回）

列1 - 输入参数
delimiter $$;

create procedure p_test2(in a int)
brgin
select a;
set a = a + 1;
select a;
end
$$;

delimiter ;

set @a=1;
call p_test2(@a);

列2 - 输出参数
delimiter $$;

create procedure p_test3(out a_out int)
brgin
select a_out;
set a_out = 10;
select a_out;
end
$$;

delimiter ;

set @a_out=1;
call p_test3(@a_out);

列3 - 输入输出参数
delimiter $$;

create procedure p_test4(inout a_inout int)
brgin
select a_inout;
set a_inout = 11;
select a_inout;
end
$$;

delimiter ;

set @a_inout=5;
call p_test4(@a_inout);

其他

查看数据库下的存储过程
show procedure status where db=db_name;

查看当前数据库下面的存储过程的列表
select specific_name from mysql.proc;

查看存储过程的内容
select specific_name,body from mysql.proc;
或者 show create procedure 存储过程名称;

触发器

特殊的存储过程，在数据插入、删除、更新时触发执行。不能直接调用，由数据库主动执行。

说明：
1、对于insert而言，新插入的行用new来表示，列用new.列名来表示
2、对于delete而言，需要使用本删除的这条数据用old来表示，old.列名
3、对于update而言，被修改之前的数据用old来表示，修改之后的用new来表示

这里创建了一个触发器demo1在表table_name1插入数据之后往table_name2表插入一条数据
delimiter $$;
create trigger demo1 after insert 
on table_name1 for each row
begin
insert table_name2 values(new.id,'zhangsan', 34);
end
$$;

查看所有触发器
show triggers

查看触发器结构
desc information_schema.triggers

查看触发器名字
select * from information_schema.triggers where trigger_name='触发器名称'

删除触发器
drop trigger 触发器名称