sql笔记（二）表格

查看表格

oracle

• desc：查看表格字段及其类型
• select TABLE_NAME from user_tables;：查看当前用户所拥有的表格

mysql

• SHOW TABLES;
• SHOW COLUMNS FROM tbl;，查看表格中各列的名字及其属性（数据类型、默认值什么的），同desc tbl;
• SHOW CREATE TABLE tbl;，查看创建表格时的命令

show来查看表格/数据库的属性，select则用来查看表格中的数据

表格中字段的属性

• Field：字段名字
• Type：数据类型
• Null：是否可以是NULL
• Default：默认值
• Key：约束
• Extra：

创建表格

oracle

-- 没有 if not exists
-- 双引号表示区分大小写，以后无论是sql语句还是sqlplus的命令中涉及这张表的，表名和字段都要带双引号
create table "TEST"(
    "ID" number,
    "NAME" varchar2(10)
);

-- ORA-01950: no privileges on tablespace 'USERS'
-- 用户的默认表空间是USERS，这里说USERS表空间没有给当前用户分配存储空间
-- 切换成数据库管理员后，给用户分配空间
alter user user_name quota 4M on users;
GRANT UNLIMITED TABLESPACE TO user_name;

mysql

创建表格的时候，最好将表格名、所有字段名都用反单引号包围，因为我常常把SQL关键字作为字段名，比如index

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT,
   `runoob_title` VARCHAR(100),
   `runoob_author` VARCHAR(40),
   `submission_date` DATE,
)CHARSET=utf8;

/*
1. 表名、列名中的反引号不加也没关系
2. CHARACTER 同 DEFAULT CHARACTER
3. 不指定table的默认编码，就使用database的默认编码
*/

我现在才发现指定数据库默认编码用的是DEFAULT CHARACTER SET utf8，而指定表格的默认编码用DEFAULT CHARSET=utf8

删除表格

oracle

没有 if exists
DROP TABLE tbl;

mysql

DROP TABLE IF EXISTS tbl;

修改表格

oracle

-- 修改表格名字
ALTER TABLE tbl_name RENAME TO new_tbl_name;
-- 同，表名可以不加任何引号
RENAME "TEST" TO test;

-- 增加列
ALTER TABLE tbl  ADD col data_type;
-- 删除列，增加列时不需要COLUMN关键字，删除时却需要
ALTER TABLE tbl DROP COLUMN  col;

-- 修改列名
ALTER TABLE tbl RENAME COLUMN col to new_col;
-- 修改字段数据类型
ALTER TABLE tbl MODIFY col data_type;

mysql

-- 修改表格名字
ALTER TABLE tbl_name RENAME TO new_tbl_name;
同
RENAME TABLE tbl TO new tbl;

增加列
ALTER TABLE tbl  ADD col data_type;
删除列
ALTER TABLE tbl  DROP col;

-- 修改列的数据类型
ALTER TABLE tbl MODIFY col data_type;
-- 修改列名以及其数据类型
ALTER TABLE tbl CHANGE old_col new_col data_type;

约束

oracle

oracle虽然有默认值，但是default不是约束。
主键、唯一键、外键见相关笔记。

约束有如下两类状态：
启用/禁用（enable/disable）：是否对新变更的数据启用约束验证。
**验证/非验证 (validate/novalidate) **：是否对表中已客观存在的数据进行约束验证。
这两类状态可以任意组合，默认是enable validate。

在dba_constraints、all_constraints、user_constraints这三个视图中可以查看约束名和约束的状态等信息。

-- 注意，系统给的约束名必须使用双引号
alter table tbl disable/enable constraint "con_name";
-- 验证状态只能使用modify来修改
alter table tbl modify constraint "con_name" disable/enable novalidate/validate;

not null

alter table tbl modify col not null;
ALTER TABLE tbl DROP CONSTRAINT "con_name";

check

-- alter table tbl add col data_type check(expression);
alter table test add gender varchar2(10) check(gender in ('male','female'));
-- alter table tbl add constraint con_name check(expression);
alter table test add constraint gender_check check(gender in ('male','female'));

mysql

InnoDB引擎提供下面5种约束。
在创建列的时候（比如添加列/创建表格什么的），在数据类型的后面增加约束。
或者在 ALTER TABLE tbl CHANGE/MODIFY ... 中，在修改数据类型后面添加（或者不添加）约束。

主键、唯一键、外键见相关笔记。

1. 非空 NOT NULL

所有数据类型中的数据都可以是NULL，NOT NULL约束数据不能为NULL

2. 默认值 DEFAULT

在mysql中，默认值是一种约束。

-- 修改列的默认值
ALTER TABLE tbl ALTER col SET DEFAULT new_def_value;
-- 删除列的默认值
ALTER TABLE tbl ALTER col DROP DEFAULT;
-- 创建表格，或者用modify/change修改时，只需要用【DEFAULT new_def_value】即可，不用SET关键字

MySQL AUTO_INCREMENT

oracle没有像类似AUTO_INCREMENT的关键字，故只记录MySQL的。

1. 默认自增从1开始，每次增加1
2. AUTO_INCREMENT要求只有作为【键】（主键、外键、唯一键）的字段可以使用该约束
3. 表格中的数据全被删除后，AUTO_INCREMENT不会自动重置为从1开始，需要手动重置
4. 一张表格只能有一个AUTO_INCREMENT
5. insert失败，auto_increment还是会自增1，这有点坑爹

-- 创建表格时设置自增约束
CREATE TABLE tbl (col INT PRIMARY KEY AUTO_INCREMENT) AUTO_INCREMENT=100;
-- 重置AUTO_INCREMENT，不能重置为0，至少需要是1
ALTER TABLE tbl AUTO_INCREMENT=1;

-- 删除/增加 AUTO_INCREMENT约束同样使用 ALTER TABLE ... MODIFY ...

oracle default

oracle的默认值不是约束，所以另起一个标题来记录。

create table test(id number,test varchar(10) default 'defaultValue');
alter table tablename modify col varchar(15) default 'defaultValue';