SQL_由创建表引出，sql创建引出
***********************************************声明***********************************************************************
原创作品，出自 “深蓝的blog” 博客，欢迎转载，转载时请务必注明出处，否则追究版权法律责任。
深蓝的blog：http://blog.csdn.net/huangyanlong/article/details/39933069
****************************************************************************************************************************
SQL_由创建表引出
目标：
1、hyl用户下创建表cool；
2、将scott用户下emp表数据抽取到hyl用户下的cool表内；
3、更改cool表内数据，把scott的emp按hyl用户下的cool表进行更改。
操作：
SQL> create table cool ("1" number(4),"2" varchar2(10),"3" varchar2(9),"4" number(4),"5" date,"6" number(7,2),"7" number(7,2),"8" number(2));
Table created
--创建表
SQL> select * from cool;
1 2 3 4 5 6 7 8
----- ---------- --------- ----- ----------- --------- --------- ---
SQL> desc cool
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
1 NUMBER(4) Y
2 VARCHAR2(10) Y
3 VARCHAR2(9) Y
4 NUMBER(4) Y
5 DATE Y
6 NUMBER(7,2) Y
7 NUMBER(7,2) Y
8 NUMBER(2) Y
使用查询语句，计划抽取的数据，如下：
SQL> select t.empno as "1",t.ename as "2",t.job as "3",t.mgr as "4",t.hiredate as "5",t.sal as "6",t.comm as "7",t.deptno as "8" from scott.emp t;
1 2 3 4 5 6 7 8
----- ---------- --------- ----- ----------- --------- --------- ---
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
SQL> insert into cool("1","2","3","4","5","6","7","8") select t.empno as "1",t.ename as "2",t.job as "3",t.mgr as "4",t.hiredate as "5",t.sal as "6",t.comm as "7",t.deptno as "8" from scott.emp t;
--将数据按序号字段，由emp表抽取到cool表内
14 rows inserted
SQL> commit;
Commit complete
SQL> select * from cool;
1 2 3 4 5 6 7 8
----- ---------- --------- ----- ----------- --------- --------- ---
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
小结：
A表数据抽取到B表
SQL>insert into select B表（”B表列1”,”B表列2”,”B表列3”） select t.”A表列1” as “B表列1”，”t.A表列2” as “B表列2”，”t.A表列3” as “B表列3” fromA表 t;
--hyl用户下，如下所示：
SQL> update cool set "3"='DBA' where "8"=30;
6 rows updated
SQL> commit;
Commit complete
SQL> select * from cool;
1 2 3 4 5 6 7 8
----- ---------- --------- ----- ----------- --------- --------- ---
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN DBA 7698 1981/2/20 1600.00 300.00 30
7521 WARD DBA 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN DBA 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE DBA 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER DBA 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES DBA 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
--scott用户下，执行操作，如下：
SQL> update emp t set (job)=(select "3" from hyl.cool p where t.empno=p."1");
--对hyl的cool表进行更新
14 rows updated
补充：通过另外一个用户的表向本用户下表插入数据时，应具有访问该表的权限
如该例，需使用sys用户向scott用户赋予查询hyl表的权限
SQL> grant select on hyl.cool to scott;
Grant succeeded.
补充完毕
SQL> select * from emp;
--可以看到，此时scott用户下的emp表数据job列已经更新为同hyl用户下的cool表相同了
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN DBA 7698 1981/2/20 1600.00 300.00 30
7521 WARD DBA 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN DBA 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE DBA 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER DBA 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES DBA 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
SQL> commit;
Commit complete
小结：
把一个A表的某列更改为另一个B表的列值，使用某一列进行关联。
SQL> update A表 t set(列)=（select 列 from B表 p where t.列=p.列）；
例：SQL> update emp t set (job)=(select "3" from hyl.cool p where t.empno=p."1");
***********************************************声明***********************************************************************
原创作品，出自 “深蓝的blog” 博客，欢迎转载，转载时请务必注明出处，否则追究版权法律责任。
深蓝的blog：http://blog.csdn.net/huangyanlong/article/details/39933069
****************************************************************************************************************************
use master
go
if exists (select * from sysdatabases where name = 'bookinfo')
drop database bookinfo
create database bookinfo
------ 如果不写 数据库就创建在 默认路径 ------
--on
--(
-- name = 'bookinfo_data',
-- filename = 'E:\bookinfo_data.mdf',
-- size = 10mb,
-- filegrowth = 10%
--)
--log on
--(
-- name = 'bookinfo_log',
-- filename = 'e:\bookinfo_log.ldf',
-- size = 1mb,
-- filegrowth = 1
--)
--go
use bookinfo
go
------ 创建表 student ------
create table student
(
stuid char(10) not null,
stuname varchar(10) not null,
major varchar(50) not null,
)
go
alter table student add constraint pk_student primary key(stuid) ---主键约束
----- 创建表 book --------
create table book
(
bid char(10) not null,
title varchar(50) not null,
author varchar(50)
)
go
alter table book add constraint pk_book primary key(bid) ---主键约束
--- 创建表 borrow -------
create table borrow
(
borrowid char(10) not null,
stuid char(10) not null,
bid char(10) not null,
t_time datetime not null,
b_time datetime not null
)
go
alter table borrow add constraint pk_borrow primary key(borrowid) ---主键约束
alter table borrow add constraint fk_stuid foreign key(stuid)
references student(stuid) ----外键约束
alter table borrow add constraint fk_bid foreign key(bid)
references book(bid) ----外键约束
------ 向 student表中 插入数据 -------
insert into student(stuid,stuname,major)
values('1001','林林','计算机')
insert into student(stuid,stun......余下全文>>
创建表
这个create table语句是用于创建一个新的表格。以下是一个简单创建表格语句的格式：
create table "tablename"
("column1" "data type",
"column2" "data type",
"column3" "data type";
如果你想使用可选的约束，创建表格的格式为：
create table "tablename"
("column1" "data type" [constraint],
"column2" "data type" [constraint],
"column3" "data type" [constraint]);
[ ] = optional
这里注意：你可以任意创建多列的表格，这个条件是可选的。