增删改、视图、触发器、序列、常用命令

-- INSERT 不指定列

insert into customers values('1000000006','Toy Land','123 Any Street','New York','NY','1111','USA',NULL,NULL);

-- INSERT 指定列

insert into customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) values('1000000006','Toy Land','123 Any Street','New York','NY','1111','USA');

-- insert 多行 一次性插入多行数据，用括号括起来，然后逗号隔开每条记录

insert into customers (cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)

values('1000000007','Toy Land','123 Any Street','New York','NY','1111','USA',NULL,NULL),

('1000000008','Toy Land','123 Any Street','New York','NY','1111','USA',NULL,NULL),

('1000000009','Toy Land','123 Any Street','New York','NY','1111','USA',NULL,NULL),

('1000000010','Toy Land','123 Any Street','New York','NY','1111','USA',NULL,NULL);

-- insert 检索数据

-- mysql

create table customers_new like customers; 

insert into customers_new select * from customers ;

--oracle

CREATE TABLE CUSTOMERS_NEW AS SELECT * FROM CUSTOMERS ;

 

-- update 更新表中的数据

-- 更新表中的特定行

-- 更新表中的所有行

-- 客户1000000005现在有了电子邮件地址，因此他的记录需要更新：

update customers set cust_email ='kim@thetoystore.com' where cust_id ='1000000005';

update customers set cust_contact ='Sam Roberts', cust_email ='sam@toyland.com' where cust_id ='1000000006';

update customers set cust_contact ='Sam Roberts', cust_email ='sam@toyland.com' where cust_id in ('1000000005','1000000006');

update customers set cust_contact ='Sam Roberts', cust_email ='sam@toyland.com' where 1=1;

 

-- 删除

delete from customers ;

-- 直接删除表 DDL操作

drop table tb_name;

-- 截断表 将表数据全部删除 保留表结构 DDL

truncate table tb_name;

-- 清空表数据  保留表结构  DML  

delete table tb_name;

 

1. 了解drop、truncate、delete相关区别，分别通过界面和命令操作下。

Drop: 直接将表删除，删除完成后，这张表就找不到了，是一个DDL操作，无法回滚，速度很快。

Truncate: 截断表，将表数据全部删除，保留表结构，同样是DDL操作，无法回滚，速度很快。

Delete: 清空表数据，保留表结构，是DML操作，可以回滚，会将删除的数据记录到日志中，因此速度较慢，特别是数据量很大时。

 

创建视图

1. 创建一张视图view_cust_totalprice，可以通过cust_id条件查出该顾客的所有订单总额。

--orecle

create view view_cust_totalprice as SELECT o1.cust_id cust_id,sum(o2.sum_money) sumMoney FROM orders o1

JOIN (SELECT order_num,sum(QUANTITY * ITEM_PRICE ) sum_money FROM ORDERITEMS GROUP BY order_num) o2

ON o1.order_num = o2.order_num GROUP BY o1.cust_id;

 

-- mysql

create view view_cust_totalprice as 

select c.cust_id,sum(c.sum_order) sumMoney from (

select o1.cust_id cust_id ,o1.order_num order_num ,sum(o2.quantity*o2.item_price) sum_order from orders o1 join orderitems o2 on o1.order_num=o2.order_num

group by o1.order_num

) c group by c.cust_id;

select * from view_cust_totalprice where cust_id = '1000000001';

 

 触发器

 

 

 

 

 

 

 

 

 

 

1. 在Oracle中手动敲一遍中实现自增列的两种方式。

--测试表

 CREATE TABLE UserInfo( id NUMBER (10) NOT NULL,

username varchar2(15) NOT NULL,

password varchar2(15) NOT NULL,

CONSTRAINTS PF_UserInfo PRIMARY KEY (id)

);

-- 序列+触发器 方式一

CREATE SEQUENCE Tab_UserInfo_Sequence

START WITH 1 

MINVALUE 1

MAXVALUE 999999999

INCREMENT BY 1

CACHE 20;

CREATE TRIGGER Tig_UserInfo_Insert

BEFORE INSERT 

ON UserInfo

FOR EACH ROW

BEGIN 

 SELECT Tab_UserInfo_Sequence.nextval into :new.id FROM dual;

END;

-- 创建自增序列

CREATE SEQUENCE Tab_UserInfo_Sequence

START WITH 1

MINVALUE 1

MAXVALUE 999999999

INCREMENT BY 1

CACHE 20;

-- 插入值

INSERT INTO USERINFO INSERT INTO UserInfo values(Tab_UserInfo_Sequence.nextval,'bbb','222');

INSERT INTO UserInfo values(Tab_UserInfo_Sequence.nextval,'ddd','333');

INSERT INTO UserInfo values(Tab_UserInfo_Sequence.nextval,'eee','444');

SELECT * FROM UserInfo;

1. 所有数据库常用命令手动敲一遍。

-- MySQL

-- 远程连接

mysql -uroot -p -h 127.0.0.1 -P3306

-- 库表视图展示

show databases;

use epoint;

select database();

 

show full tables;

-- 版本查询

select version();

-- 列信息

desc tb_name;

desc customers;

-- 表定义

show create table tb_name;

show create table customers ;

 

-- 视图定义

show create view view_name ;

show create view view_cust_totalprice ;

-- 查看连接进程

show processlist;

 

-- 查看所有用户

select host,user from mysql.user;

 

-- oracle

-- 客户端连接数据库

sqlplus epoint/Gepoint@127.0.0.1:1521/orcl

-- 查看版本

SELECT * FROM v$version;

--查看数据库信息

SELECT * FROM v$database;

--查看实例信息

SELECT * FROM v$instance;

--所有用户表

SELECT owner,table_name,tablespace_name FROM dba_tables;

 

--当前用户表

SELECT table_name,tablespace_name FROM user_tables;

 

--查看表空间

SELECT * FROM dba_tablespaces;

 

--查看数据文件

SELECT * FROM dba_data_files;