CS_SQL

MySQL

建表

  下面是建表时的注意事项

1. 必须要有主键,主键自增,就是选择那个A_I (auto increment)
2. varchar类型的默认要是'' (英文单引号),这个意思就是空字符串,不能为NULL
3. create_time 类型必须是TIMESTAMPE ,默认必须是CURRENT_TIMES,不能为空
4. update_time 类型必须是TIMESTAMPE ,默认为空,可以为空,属性选择ON UPDATE CURRENT_TIMESTAMP .这个的作用是当这一段数据更新的时候,它会自动更新
5. 作为外键的字段一定不能为空

以下是参考表结构

CREATE TABLE `t_baby` (
`baby_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`nickname` varchar(64) DEFAULT NULL,
`avatar` varchar(64) DEFAULT '',
`sex` int(2) unsigned NOT NULL DEFAULT '0' COMMENT '0：未知；\\n 1：男；\\n 2：女。',
`birthday` datetime NOT NULL,
`creater` varchar(16) DEFAULT '',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`is_delete` bigint(20) DEFAULT NULL COMMENT '0',
`updater` varchar(16) DEFAULT NULL,
PRIMARY KEY (`baby_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000000 DEFAULT CHARSET=utf8mb4;

Foreign key

在表结构下的关联视图里面设置

外键类型参考

CASCADE
在父表上update/delete记录时，同步update/delete掉子表的匹配记录

SET NULL
在父表上update/delete记录时，将子表上匹配记录的列设为null (要注意子表的外键列不能为not null)

NO ACTION
如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作

RESTRICT
同no action, 都是立即检查外键约束

查询

  查询当前数据库下所有的表名

select table_name from information_schema.TABLES WHERE TABLE_SCHEMA = '当前表名'

  从网上复制的代码要先放到typora里把里面的空格都变成合法格式再放到Navicat里运行
  在MySQL中，把 information_schema 看作是一个数据库，确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权 限等。在INFORMATION_SCHEMA中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件。

Oracle

登录

想要登录scott用户先要解锁scott用户，密码就是解锁之后设定的口令，SID一定要用myorcl

无有效月份问题

• 这是因为客户端是中文环境，格式mon就不能用英文的月份写法，必须用中文的“六月”
  如果不想修改sql语句运行的话，就需要在执行该语句之前，使用alter session 命令将nls_date_language修改为american，如下：
  alter session set nls_date_language = 'american' 以英语显示日期。

• 若你的客户端是英文环境，那就是你没有用oracle数据的时间函数查询数据，应在sql语句中使用oracle数据库中的时间函数。

建表

现有一个商店的数据库，记录客户及其购物情况，由下面三个表组成：
t1.商品goods（商品号goodsId，商品名 goodsName，单价 unitprice，商品类别category，供应商provider）；
t2.客户customer（客户号customerId，姓名name，住在address，电邮email，性别sex，身份证cardId）；
t3.购买purchase（客户号customerId，商品号goodsId，购买数量nums）；

请用SQL语言完成下列功能：
建表，在定义中要求声明：
(1). 每个表的主外键；
(2). 客户的姓名不能为空值；
(3). 单价必须大于0，购买数量必须在1到30之间；
(4). 电邮不能够重复；
(5). 客户的性别必须是 男 或者 女，默认是男；

--删除数据表
drop table purcase;
drop table goods;
drop table customer;
--创建商品表
create table goods(
	goodsId varchar2(10),
	goodsName varchar2(20) NOT NULL,
	unitprice number,
	category varchar2(20),
	provider varchar2(20),
	CONSTRAINT pk_goodsId primary key (goodsId ),  --建立主键约束
	CONSTRAINT CK_unitprice CHECK (unitprice>0)  --建立约束
);
--创建顾客表customer：
create table customer(
	customerid varchar2(10),
	name varchar2(20) NOT NULL,
	address varchar2(20),
	Email varchar2(20),
	Sex varchar2(20),
	cardId varchar2(30),
	CONSTRAINT pk_customerid primary key(customerid)  --主键约束
);
--创建购买记录表 purcase：
create table purcase(
    customerid varchar2(10),
    goodsId varchar2(10),
    Nums number,
    CONSTRAINT FK_customerid FOREIGN KEY(customerid) REFERENCES customer(customerid) on delete cascade,  --建立外键约束
    CONSTRAINT FK_goodsId FOREIGN KEY(goodsId ) REFERENCES goods(goodsId ) on delete cascade,  --外键约束
    CONSTRAINT CK_Nums CHECK(Nums BETWEEN 0 AND 30)  --范围约束
);

子查询

select * from EMP WHERE deptno = (select deptno from EMP where ename='SMITH'); 
select ename,deptno,sal from emp where sal > (select max(sal) from emp where deptno = 30);

要注意大小写和 ASCII编码问题，出了编码问题重写一遍就行

procedure 存储过程

为scott.emp表创建一个能完成插入功能过程insert_emp。

CREATE OR REPLACE Procedure insert_emp2 
(emp_num in number,
emp_name in varchar2,
emp_job in varchar2,
emp_mgr in number,
emp_hiredate scott.emp.hiredate%TYPE,  --%TYPE是使用那个表里那一列原本的类型
emp_sal scott.emp.sal%TYPE,
emp_comm scott.emp.comm%TYPE,
emp_deptno scott.emp.deptno%TYPE) as 
BEGIN
    INSERT INTO EMP 
    VALUES(emp_num,emp_name,emp_job,emp_mgr,emp_hiredate,emp_sal,emp_comm,emp_deptno);
    commit;
END;

exec insert_emp2(6503,'zzk2','CLERK',7786,SYSDATE,20000,500,20);

CREATE OR REPLACE的意思是创建或覆盖这个存储过程对象
SYSDATE是Oracle里获取系统时间的函数

---

从scott.emp表中查询给定职工编号的职工姓名和工资，并利用out模式的参数将值传给调用者。

--创建或覆盖
CREATE OR REPLACE Procedure select_emp 
(emp_num IN scott.emp.empno%TYPE,
emp_name OUT scott.emp.ename%TYPE) as 
BEGIN
    SELECT ename INTO emp_name 
    FROM emp WHERE empno=emp_num;
    commit;
END;

VARIABLE emp_name2 VARCHAR2(10);
exec select_emp(6503,:emp_name2);
PRINT emp_name2;

FUNCTION　函数

创建函数，返回scott.emp表中指定职工的工资和姓名。

CREATE OR REPLACE FUNCTION select_sal(emp_num scott.emp.empno%TYPE)
return scott.emp.sal%TYPE
IS salary scott.emp.sal%TYPE;
BEGIN
    select sal into salary from scott.emp where empno=emp_num;
    return salary;
exception
when NO_DATA_FOUND THEN
RETURN 0;
END;

VAR salary NUMBER;
EXEC :salary := select_sal(7499);
PRINT salary;

函数的输出有以下几种

package

创建一个包sp_package 。

• 声明该包有一个过程update_sal ：可以输入雇员名，新工资，使用该过程修改雇员的工资。
• 声明该包有一个函数annual_income：输入雇员的姓名，返回该雇员的年薪（年薪公式：sal12+comm12）。

create package sp_package is
	procedure update_sal(name varchar2,newsal number);  --过程
	function annual_income(name varchar2) return number;    --函数
end;

建立包体可以使用create package body命令

• 给包sp_package实现包体

create or replace package body sp_package is  
  procedure update_sal(name varchar2, newsal number)    
  is  
  begin    
    update emp set sal = newsal where ename = name;   
  end;   
  function annual_income(name varchar2) return number is  
    annual_salary number;   
  begin  
    select sal * 12 + nvl(comm, 0) into annual_salary from emp   
     where ename = name;   
    return annual_salary;   
  end;   
end;

当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其它方案的包，还需要在包名前加方案名。

call sp_package.update_sal('SCOTT', 8000);

触发器

为scott.emp表创建一个事后语句级触发器。当用户向emp表中插入新数据后，该触发器将统计emp表中的新行数并输出。

set serveroutput on;
create or replace TRIGGER tri1_insert_emp
after insert on scott.emp
DECLARE rows number;
begin
    select count(*) into rows from scott.emp;
    dbms_output.put_line('emp 表当前包含'||rows||'条新纪录');
end;

insert into emp values(2333,'ycc','CLERK',7786,SYSDATE,20000,500,20);

---

为scott.emp表创建一个带有触发条件的行级触发器。当用户向emp表中插入新记录时，如果新插入的员工工资是空值，那么触发器将该工资改为0。

set serveroutput on;
create or replace TRIGGER tri1_insert_emp
before insert on scott.emp
for each row
when(new.sal is null)
begin
    :new.sal:=0;
end;

---

4.首先创建一个记录DDL事件的表event_table，字段包括事件名称event，事件的操作者username，被操作对象的所有者owner，被操作对象的名称objname，被操作对象的类型objtype，操作的时间opertime。

create table event_table(   event varchar2(20),
                            username varchar2(20),
                            owner varchar2(10),
                            objname varchar2(30),
                            objtype varchar2(20),
                            opertime TIMESTAMP);
                            

然后创建一个数据库级的事后DDL触发器，一旦有DDL时间发生，则将该事件的信息记录到上面的event_table表中。

create or replace trigger tri3_ddl_database
after ddl on database
begin
    insert into event_table
    values( ora_sysevent,
            ora_login_user,
            ora_dict_obj_owner,
            ora_dict_obj_name,
            ora_dict_obj_type,
            sysdate);
end;