数据库操作

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

• 数据库的创建

create database [if not exists] db_name [charset 字符集]

• 删除一个数据库

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 auth_name

MySQL数据类型

• 整型数据类型
  
• 浮点数据类型
  

• 日期和时间类型
  

• 字符串类型
  

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