SQL语法使用示例1.添加外键约束创建外键语句结构： ALTER TABLE <表名> ADD CONSTRAINT

1.添加外键约束

创建外键语句结构：

ALTER TABLE <表名>

ADD CONSTRAINT FOREIGN KEY ( <列名> )

REFERENCES <关联表> （关联列）

2.修改表名，列名，索引名

修改表名

alter table 表名 rename to 新表名

修改列名/索引名

alter table 表名 rename 列名/索引名 to 新的名称

3.创建触发器

触发器trigger 作用:监视某种情况，并触发某种操作\ 很多人都不提倡使用触发器，因为这是一个隐藏的过程不好管理。

在MySQL中，创建触发器语法如下：\ CREATE TRIGGER trigger_name\ trigger_time trigger_event ON tbl_name\ FOR EACH ROW\ trigger_stmt

其中：

trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句，每条语句结束要分号结尾。

create trigger triggerName      
    after/before/        // 触发时间 trigger_time
    insert/update/delete // 监视事件 trigger_event
    on table_name        // 监视地点 table
    for each row         // 这句话在mysql中是固定的
    begin
    sql语句(insert/update/delete);             // 触发事件  trigger_stmt 注意这里要有分号
    end;

注意对同一个表相同触发时间的相同触发事件，只能定义一个触发器；
触发器只能建立在永久表上，不能对临时表使用
可以使用old和new来引用触发器中发生变化的记录内容。 old表示监视事件发生之前的原列，new表示监视事件发生后的新列

示例：

描述

构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。\ CREATE TABLE employees_test(\ ID INT PRIMARY KEY NOT NULL,\ NAME TEXT NOT NULL,\ AGE INT NOT NULL,\ ADDRESS CHAR(50),\ SALARY REAL\ );\ CREATE TABLE audit(\ EMP_no INT NOT NULL,\ NAME TEXT NOT NULL

);

后台会往employees_test插入一条数据:

INSERT INTO employees_test (ID,NAME,AGE,ADDRESS,SALARY)VALUES (1, 'Paul', 32, 'California', 20000.00 );

然后从audit里面使用查询语句:

select * from audit;

4.强制索引

create trigger audit_log
after     # 监视时间
insert on employees_test  # 监视事件
for each row
begin
insert into audit values(new.id,new.name); # 触发事件 使用新列数据
end;

MYSQL 中使用强制索引的语法

select col
from table_name
force index(index_name)
where condition

-- 示例
select *
from salaries
force index(idx_emp_no)
where emp_no=10005

关于强制索引的一些解释

查询优化器是MySQL数据库服务器中的一个组件，它为SQL语句提供最佳的执行计划;\ 查询优化器使用可用的统计信息来提出所有候选计划中成本最低的计划;\ 例如，查询可能会请求价格在10到80之间的产品。如果统计数据显示80％的产品具有这些价格范围，那么它可能会认为全表扫描效率最高。但是，如果统计数据显示很少有产品具有这些价格范围，那么读取索引后跟表访问可能比全表扫描更快，更有效。\ 如果查询优化器忽略索引，您可以使用FORCE INDEX提示来指示它使用索引。

5.创建视图

1.直接在视图名的后面用小括号创建视图中的字段名
create view actor_name_view (first_name_v,last_name_v) as
select first_name ,last_name from actor


2.在select后面对列重命名为视图的字段名
create view actor_name_view as
select first_name as first_name_v ,last_name as last_name_v
from actor

6.子查询示例

示例1

这道题最大的难点在于问题描述不清，“当月均完成试卷数”是什么意思？我觉得“当”字应该是打错了，因为按照描述的解释，只有移除“当”字，才说的通顺。因此，“月均完成试卷数”我理解的意思是某个人一共提交的次数除以提交的月数，比如：1002一共提交了3次，提交试卷所在的月份只有9月一个月，那就是3/1=3。简单说就是忽略未提交的数据，只关注提交的数据。再比如：小明分别在7,8,9,10月提交了3,5,7,0次，

因为10月提交了0次相当于未提交过试卷，所以忽略10月数据，那么小明的“月均完成试卷数”=（3+5+7）/3=5。只要这点明白了，题目就变得简单多了

另外，“爱作答的类别及作答次数”是什么意思？就是一共做过的试卷类别有哪些？每类试卷做了多少次，无论提交还是未提交的都包括。

第一步先求出“月均完成试卷数不小于3的用户”

SELECT
  uid 
FROM
  exam_record 
GROUP BY
  uid 
HAVING
  round(count( submit_time )/ count(DISTINCT date_format( submit_time, '%Y%m' ))) >= 3

第二步统计上一步的用户中试卷类别和试卷作答次数并降序

SELECT
    tag,
    count(tag) tag_cnt
FROM
    exam_record
    INNER JOIN examination_info USING (exam_id)
WHERE
    uid IN (
        SELECT
            uid
        FROM
            exam_record
        GROUP BY
            uid
        HAVING
            round(
                count(submit_time) / count(DISTINCT date_format (submit_time, '%Y%m'))
            ) >= 3
    )
group by
    tag
order by
    tag_cnt desc;

示例2

这个题目的关键点在于计算每张SQL类别试卷发布时间，因为可能存在多张SQL试卷，所以子查询<font style="color:rgb(102, 102, 102);">SELECT date ( release_time ) release_date FROM examination_info WHERE tag = 'SQL'</font>可能是多条数据，要用in而不是=

SELECT
	exam_id,
	COUNT( DISTINCT uid ) uv,
	round( AVG( score ), 1 ) avg_score 
FROM
	exam_record
	INNER JOIN user_info USING ( uid )
	INNER JOIN examination_info USING ( exam_id ) 
WHERE
	`level` > 5 
	AND `tag` = 'SQL' 
	AND date ( submit_time ) IN ( SELECT date ( release_time ) FROM examination_info WHERE tag = 'SQL' ) 
GROUP BY
	exam_id 
ORDER BY
	uv DESC,
	avg_score ASC;

7.操作表结构

修改字段

alter table user_info add school varchar(15) after level;
增加列在某列之后
alter table 增加的表格 add 增加列的名称 数据类型 位置(after level 在level 之后)
 
alter table user_info change job profession varchar(10);
更换列的名称及数据类型
alter table user_info change 原列名 修改列名 修改数据类型
 
alter table user_info modify achievement int(11) default 0;
更改数据类型
alter table 表名 modify 修改列名称 数据类型 默认值等

删除表

DROP TABLE [IF EXISTS] 表名1 [, 表名2]

删除表

-- 备份表exam_record_{YEAR}，{YEAR}为对应年份，把很久前的（2011到2014年）备份表都删掉（如果存在的话）
drop table if exists exam_record_2011,exam_record_2012,exam_record_2013,exam_record_2014;

truncate table 表名

截断表（删除表），会重置自增id，且无法回滚。

delete from 表名

删除表数据

查询有哪些表

<font style="color:rgb(102, 102, 102);">SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME LIKE 'hep_%'</font>

information_schema.TABLES，这个系统表存储了关于数据库中所有表的元数据信息。

创建索引

现有一张试卷信息表examination_info，其中包含各种类型试卷的信息。为了对表更方便快捷地查询，需要在examination_info表创建以下索引，规则如下：

在duration列创建普通索引idx_duration、在exam_id列创建唯一性索引uniq_idx_exam_id、在tag列创建全文索引full_idx_tag。

alter table examination_info add index idx_duration(duration);
alter table examination_info add unique index uniq_idx_exam_id(exam_id);
alter table examination_info add fulltext index full_idx_tag(tag);

CREATE INDEX idx_duration ON examination_info(duration);
CREATE UNIQUE INDEX uniq_idx_exam_id ON examination_info(exam_id);
CREATE FULLTEXT INDEX full_idx_tag ON examination_info(tag);

8.创建表的三种方式

直接创建

CREATE TABLE
[IF NOT EXISTS] tb_name -- 不存在才创建，存在就跳过
(column_name1 data_type1 -- 列名和类型必选
  [ PRIMARY KEY -- 可选的约束，主键
   | FOREIGN KEY -- 外键，引用其他表的键值
   | AUTO_INCREMENT -- 自增ID
   | COMMENT comment -- 列注释（评论）
   | DEFAULT default_value -- 默认值
   | UNIQUE -- 唯一性约束，不允许两条记录该列值相同
   | NOT NULL -- 该列非空
  ], ...
) [CHARACTER SET charset] -- 字符集编码
[COLLATE collate_value] -- 列排序和比较时的规则（是否区分大小写等）

从另一张表复制表结构创建表

CREATE TABLE tb_name LIKE tb_name_old

从另一张表的查询结果创建表

CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options

删除索引请删除examination_info表上的唯一索引uniq_idx_exam_id和全文索引full_idx_tag。

alter table examination_info drop index uniq_idx_exam_id;
alter table examination_info drop index full_idx_tag

drop index uniq_idx_exam_id on examination_info;
drop index full_idx_tag on examination_info;

9.插入数据的三种方式

# mysql中常用的三种插入数据的语句:
# insert into表示插入数据，数据库会检查主键，如果出现重复会报错；
# replace into表示插入替换数据，需求表中有PrimaryKey，
#             或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；
# insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；
insert ignore into actor values("3","ED","CHASE","2006-02-15 12:34:33");

10.创建索引的方式

alter方式创建索引：

添加主键
ALTER TABLE tbl_name ADD PRIMARY KEY (col_list);
// 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

添加唯一索引
ALTER TABLE tbl_name ADD UNIQUE index_name (col_list);
// 这条语句创建索引的值必须是唯一的。

添加普通索引
ALTER TABLE tbl_name ADD INDEX index_name (col_list);
// 添加普通索引，索引值可出现多次。

添加全文索引
ALTER TABLE tbl_name ADD FULLTEXT index_name (col_list);
// 该语句指定了索引为 FULLTEXT ，用于全文索引。

create方式创建索引：

注意：CREATE INDEX 不能用于创建PRIMARY KEY（主键）

// 添加唯一索引
CREATE UNIQUE INDEX uniq_idx_firstname ON actor (`first_name`);

// 添加普通索引
CREATE INDEX idx_lastname ON actor (`last_name`);

删除索引的语法：


DROP INDEX index_name ON tbl_name;
// 或者
ALTER TABLE tbl_name DROP INDEX index_name；
ALTER TABLE tbl_name DROP PRIMARY KEY;

11.表&视图oracle