http://user.qzone.qq.com/170475387/blog/1196532795
SQL Tables
http://baike.baidu.com/view/913128.htm CRUD :In computing, CRUD is an acronym for create, retrieve, update, and delete. It is used to refer to the basic functions of a database or persistence layer in a software system. DML:SELECT、INSERT、UPDATE、DELETE create create table table_name (columnName1 columnType1,columnName2 columnType2) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); CREATE TABLE t_user(id number(6),userid varchar2(20),password varchar2(20),isdisable number(6)); read/select select from table_name; select * from table_name; select columnName1,columnName2 from table_name; select columnName1,columnName2 from table_name1,table_name2; update/insert UPDATE {table_name|view_name} SET [{table_name|view_name}] {column_list|variable_list|variable_and_column_list} [,{column_list2|variable_list2|variable_and_column_list2}! [,{column_listN|variable_listN|variable_and_column_listN}]] [WHERE clause] INSERT [INTO] {table_name|view_name} [(column_list)] {DEFAULT VALUES | Values_list | select_statement} insert into table_name (column1,column2) values('',''); delete delete from table_name; delete * from table_name; DELETE FROM table_name WHERE column_name = some_value; --删除表person、note和序列note_sequ drop table person; drop table note; drop sequence note_sequ; --创建序列//用于插入数据 create sequence note_sequ; --创建表 create table person ( id varchar(32) not null primary key, username varchar(32)not null, password varchar(32)not null ); create table note ( --sequence id int not null primary key, title varchar(32)not null, author varchar(32)not null, content varchar(32)not null ); insert into person values('txj','xxx','zzzzzz'); insert into person values('txj','程序员','zzzzzz'); 城市列表汇总 -- Create table create table CITY ( CITYID NUMBER(19) not null, CITY_CODE VARCHAR2(20), COUNTRY_CODE VARCHAR2(100), CREATE_USER VARCHAR2(20), CREATE_TIME TIMESTAMP(6), UPDATE_USER VARCHAR2(20), UPDATE_TIME TIMESTAMP(6), STATUS VARCHAR2(2), FK_PROVINCE NUMBER(19), FK_CITY_GROUP NUMBER(19) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); -- Create/Recreate primary, unique and foreign key constraints alter table CITY add constraint CITYID_PK primary key (CITYID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); ---------------------------------------------------------------------------------------- oracle instance:snpdb id:**** pwd:**** 查询60000内会员的数据 select t.*,t.rowid from member t where t.MEMBERID<60000; 查询60000~120000内会员的数据 select t.*,t.rowid from member t where t.MEMBERID>60000 and t.MEMBERID<120000; 查询会员数据总条数 select max(MEMBERID) - min(memberid) "total" from member t1; 查询最小会员id号 select min(memberid) "min" from member t2; ---------------------------------------------------------------------------------------- --E:\Program\video\Hibernate\Hibernate-Oracle\userList.sql DROP TABLE userList; DROP sequence my_seq; CREATE TABLE userList ( Id number primary key, userName varchar2(20) not null, userPwd varchar2(20) not null ); --oracle不支持自动编号,创建之。 create sequence my_seq nocycle maxvalue 9999999999 start with 1; insert into UserList(id,userName,userPwd)values(my_seq.nextval,'admin','admin'); select * from userList; --dos cmd desc userList; lucksoft.sql --create database lucksoft /* 表名称: USERS(用户) ID --用户编号 LOGINID --登陆账号 PASSWD --密码 NAME --用户名 EMAIL --电子邮箱 STATUS --状态 DESCN --简介 */ CREATE TABLE USERS( ID INT IDENTITY(1,1) NOT NULL, LOGINID VARCHAR(20) NOT NULL, PASSWD VARCHAR(255) NOT NULL, NAME VARCHAR(80) NOT NULL, EMAIL VARCHAR(255), STATUS VARCHAR(2) DEFAULT '1', DESCN VARCHAR(255), CONSTRAINT PK_USERS PRIMARY KEY(ID) ) /* 表名称: ROLES(角色) ID --角色编号 NAME --角色名称 DESCN --描述 */ CREATE TABLE ROLES( ID INT IDENTITY(1,1) NOT NULL, NAME VARCHAR(80) NOT NULL, DESCN VARCHAR(255), CONSTRAINT PK_ROLES PRIMARY KEY(ID) ) /* 表名称: USER_ROLE(用户权限) USER_ID --用户编号 ROLE_ID --角色编号 */ CREATE TABLE USER_ROLE( USER_ID INT NOT NULL, ROLE_ID INT NOT NULL, CONSTRAINT PK_USER_ROLE PRIMARY KEY(USER_ID,ROLE_ID), CONSTRAINT FK_USER_ROLE_1 FOREIGN KEY(USER_ID) REFERENCES USERS(ID), CONSTRAINT FK_USER_ROLE_2 FOREIGN KEY(ROLE_ID) REFERENCES ROLES(ID) ) /* 表名称: PERMISSIONS(权限) ID --权限编号 NAME --权限名称 DESCN --权限描述 STATUS --权限状态 */ CREATE TABLE PERMISSIONS( ID INT IDENTITY(1,1) NOT NULL, NAME VARCHAR(80) NOT NULL, DESCN VARCHAR(255),OPERATION VARCHAR(80), STATUS VARCHAR(2) DEFAULT '1', CONSTRAINT PK_PERMISSONS PRIMARY KEY(ID) ) /* 表名称: ROLE_PERMIS(角色权限) ROLE_ID 角色ID PERMIS_ID 权限ID */ CREATE TABLE ROLE_PERMIS( ROLE_ID INT NOT NULL, PERMIS_ID INT NOT NULL, CONSTRAINT PK_ROLE_PERMIS PRIMARY KEY(ROLE_ID,PERMIS_ID), CONSTRAINT FK_ROLE_ROLE_PERMIS_1 FOREIGN KEY(ROLE_ID) REFERENCES ROLES(ID), CONSTRAINT FK_ROLE_ROLE_PERMIS_2 FOREIGN KEY(PERMIS_ID) REFERENCES PERMISSIONS(ID) ) /* 表名称: RESOURCES(资源) ID --资源ID NAME --资源名称 RES_TYPE --资源类型 RES_STRING --资源串 DESCN --资源描述 */ CREATE TABLE RESOURCES( ID INT IDENTITY(1,1) NOT NULL, NAME VARCHAR(80) NOT NULL, RES_TYPE VARCHAR(20) NOT NULL, RES_STRING VARCHAR(255) NOT NULL, DESCN VARCHAR(255),CONSTRAINT PK_RESOURCES PRIMARY KEY(ID), ) /* 表名称: role_permis(权资源限) PERMIS_ID --权限ID RESC_ID --资源ID */ CREATE TABLE PERMIS_RESC( PERMIS_ID INT NOT NULL, RESC_ID INT NOT NULL, CONSTRAINT PK_PERMIS_RESC PRIMARY KEY(PERMIS_ID,RESC_ID), CONSTRAINT FK_ROLE_PERMIS_RESC_1 FOREIGN KEY(RESC_ID) REFERENCES RESOURCES(ID), CONSTRAINT FK_ROLE_PERMIS_RESC_2 FOREIGN KEY(PERMIS_ID) REFERENCES PERMISSIONS(ID) ) /* 表名称: MENUS(角色权限) ID --菜单ID PARENT_ID --上级菜单ID TITLE --菜单名 DESCN --菜单描述 IMAGE --图片 FORWARD --超链地址 */ CREATE TABLE MENUS( ID INT IDENTITY(1,1) NOT NULL, PARENT_ID INT, SEQ INT DEFAULT 1, TITLE VARCHAR(255) NOT NULL, TIP VARCHAR(255), DESCN VARCHAR(255), IMAGE VARCHAR(255), FORWARD VARCHAR(255), CONSTRAINT PK_MENU PRIMARY KEY(ID), CONSTRAINT FK_MENU_1 FOREIGN KEY(PARENT_ID) REFERENCES MENUS(ID) ) /* 表名称: role_permis(角色菜单) ROLE_ID --角色ID MENU_ID --菜单ID */ CREATE TABLE MENU_ROLE( ROLE_ID INT NOT NULL, MENU_ID INT NOT NULL, CONSTRAINT PK_MENU_ROLE PRIMARY KEY(ROLE_ID,MENU_ID), CONSTRAINT FK_MENU_ROLE_1 FOREIGN KEY(ROLE_ID) REFERENCES ROLES(ID), CONSTRAINT FK_MENU_ROLE_2 FOREIGN KEY(MENU_ID) REFERENCES MENUS(ID) ) select * from users INSERT INTO USERS VALUES('admin','21232f297a57a5a743894a0e4a801fc3','\u8d85\u7ea7\u7ba1\u7406\u5458','admin@springside.org.cn','1',NULL) INSERT INTO USERS VALUES('employee','fa5473530e4d1a5a1e1eb53d2fedb10c','\u5e7f\u5dde\u5458\u5de5','employee@springside.org.cn','1',NULL) INSERT INTO USERS VALUES('employee2','af74a83ae0d5777401f86af4df941e98','\u5317\u4eac\u5458\u5de5','employee2@springside.org.cn','1',NULL) coupon_no_list 模糊查询 SELECT t.*, t.rowid FROM coupon_no_list t WHERE t.coupon_no LIKE '%00000000%'; database:oracle hostname:localhost instance:orcl id:scott pwd:tiger table:all E:\Program\Database\Oracle\lindows oracle\ test.sql 查询该用户下所有表 select * from tab; 查询多表 select * from salgrade,emp,dept; --出现笛卡尔逻辑错误 select * from dept d; select * from emp e; select empno,ename,job,dname,loc from emp,dept; --消除笛卡尔积 select e.empno,e.ename,e.job,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno; --根据雇员的工资求出工资的等级 select e.empno,e.ename,e.sal,s.grade from salgrade s,emp e where e.sal between s.losal and s.hisal; --同表关联,查找同表中x的上级主管y database:oracle hostname:192.168.129.21 instance:snpdb id:***** pwd:***** table:ord_mst E:\Sn\B2C 3\sql\ ord_mst.sql --查询2008-06-01与2008-06-15之间订单总数 SELECT count(o.ttl_amt) FROM ord_mst o WHERE o.ord_date >= TO_DATE ('2008-06-01', 'yyyy-mm-dd') AND o.ord_date <= TO_DATE ('2008-06-15', 'yyyy-mm-dd'); --查询2008-06-01与2008-06-15之间订单明细 SELECT o.ORD_DATE,o.ORD_NO,o.ORD_STS,o.ORD_TYP,o.TTL_AMT FROM ord_mst o WHERE o.ord_date >= TO_DATE ('2008-06-01', 'yyyy-mm-dd') AND o.ord_date <= TO_DATE ('2008-06-15', 'yyyy-mm-dd'); --查询2008-06-01与2008-06-15之间订单均价 select avg(o.ttl_amt) from ord_mst o WHERE o.ord_date >= TO_DATE ('2008-06-01', 'yyyy-mm-dd') AND o.ord_date <= TO_DATE ('2008-06-15', 'yyyy-mm-dd'); --查询2008-06-01与2008-06-15之间订单总价 select sum(o.ttl_amt) from ord_mst o WHERE o.ord_date >= TO_DATE ('2008-06-01', 'yyyy-mm-dd') AND o.ord_date <= TO_DATE ('2008-06-15', 'yyyy-mm-dd'); E:\Program\video\SSH综合项目 网址1:http://www.programsalon.com/downloads111/sourcecode/internet/webserver/detail462585.html 网址2:http://xidong.net/File001/File_54148.html 《我的智囊团J2EE项目实训视频》 下载 网址3:http://www.verycd.com/groups/datum/219722.topic 《我的智囊团项目笔记以及代码》下载 ed2k://|file|%E6%88%91%E7%9A%84%E6%99%BA%E5%9B%8A%E5%9B%A2%E9%A1%B9%E7%9B%AE%E7%AC%94%E8%AE%B0%E4%BB%A5%E5%8F%8A%E4%BB%A3%E7%A0%81.rar|37130398|cd976526bbb4010ea0b967d668636b7b|h=ECNDO7BGVTAVQB2EUHAA2LP3HGQFD23L| 文件:我的智囊团—数据库创建脚本.sql 内容: /*==============================================================*/ /* DBMS name: MySQL 4.0 */ /* Created on: 2007-6-28 15:25:46 */ /*==============================================================*/ drop table if exists admin; drop table if exists answer; drop table if exists subitem; drop table if exists item; drop table if exists question; drop table if exists user; /*==============================================================*/ /* Table: admin */ /*==============================================================*/ create table admin ( id INT AUTO_INCREMENT PRIMARY KEY , adminid VARCHAR(50) not null, adminpwd VARCHAR(50) ) ; /*==============================================================*/ /* Table: question */ /*==============================================================*/ create table question ( qid int auto_increment not null, title VARCHAR(50), content text, itemid int, subid int, userid VARCHAR(50), grade VARCHAR(50), offerscore int, status int, questiontime datetime, clickcount int, acceptflag int, commenflag int, primary key (qid) ) ; /*==============================================================*/ /* Table: answer */ /*==============================================================*/ create table answer ( aid int auto_increment not null, quesans VARCHAR(50), userid VARCHAR(50), grade VARCHAR(50), anstime datetime, status int, qid int, primary key (aid) , foreign key (qid) references question(qid) on delete cascade ) ; /*==============================================================*/ /* Table: item */ /*==============================================================*/ create table item ( itemid int auto_increment not null, itemname VARCHAR(50), itemcode int, primary key (itemid) ) ; /*==============================================================*/ /* Table: subitem */ /*==============================================================*/ create table subitem ( subid int auto_increment not null, subname varchar(50), itemid int, subcode int, primary key (subid) , foreign key (itemid) references item(itemid) on delete cascade ) ; /*==============================================================*/ /* Table: user */ /*==============================================================*/ create table user ( id INT AUTO_INCREMENT PRIMARY KEY , userid VARCHAR(50) not null , userpwd VARCHAR(50), userques VARCHAR(50), userans VARCHAR(50), usermail VARCHAR(50), integral int, grade int, sex VARCHAR(2), realname VARCHAR(50) ) ; |
本文标签:
d
end
相关推荐
按以上sql语句导入数据库即可,如果没有oracle用户,建议创建oracle用户。 含有emp,dept表等。详见log日志。 . about to export ORACLE's tables via Conventional Path ... . . exporting table BOOK 1 rows ...
SQL>select * from user_tables; 查看名称包含log字符的表 SQL>select object_name,object_id from user_objects where instr(object_name,'LOG')>0; 查看某表的创建时间 SQL>select object_name,created ...
oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 第一章 Oracle入门 一、 数据库概述 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,它产生于距今五十年前。简单来说是本身可视...
1 SQL基础 1.1 基本概念 结构化查询语言(Structured Query Language)简称SQL,是一种关系数据库查询语言,用于存取数据以及查询、更新和管理关系数据库系统。 1.2 语句结构 1.2.1 数据查询语言(DQL) 对数据库进行...
Oracle 最常用的命令 1.登陆系统用户 :sqlplus 然后输入系统用户名和密码 登陆别的用户 : conn 用户名/密码; 2.创建表空间 create tablespace 空间名 datafile 'c:\空间名' size 15M --表空间的存放路径,...
scheme2ddl是用于将... 命令java -jar scheme2ddl.jar -url scott/tiger@localhost:1521:ORCL -o C:/temp/oracle-ddl2svn/将产生目录树 views/ view1.sql view2.sql tables/ table1.sql functions /f1.sql 更多命令
sql> temporary tablespace temp quota 10m on data password expire sql> [account lock|unlock] [profile profilename|default]; <1>.查看当前用户的缺省表空间 SQL>select username,default_tablespace ...
SQL> select tablespace_name from user_tables where table_name='DB_JJ_INFO_TEMP'; TABLESPACE_NAME ------------------------------ PDATA SQL> alter tablespace PDATA offline; 表空间已更改。 SQL> alter ...
- ash_sql <sql_id> Show all ash rows group by sampli_time and event for the specified sql_id - [-u ] degree degree of objects for a given user - [-u ] colstats stats for each table, column - [-u ]...
oracle会避开sql语句处理引擎,直接从数据库文件中读取数据,然后写入导出文件. 可以在导出日志中观察到: exp-00067: table xxx will be exported in conventional path 如果没有使用直接路径,必须保证buffer参数的值...
SQL Monitor detail tab page added (Oracle 11.2 and later) Queries in the can now be database version specific Object Browser enhancements The Object Browser has a new filter field where you can ...
注意:alter modify不支持一次修改多个列,但是Oracle支持多列修改 但是MySQL可以通过多个modify的方式完成: alter table user modify tel varchar(15) default '02087654321' first, modify name varchar(20) ...
-- 首先,以超级管理员的身份登录oracle sqlplus sys/bjsxt as sysdba --然后,解除对scott用户的锁 alter user scott account unlock; --那么这个用户名就能使用了。 --(默认全局数据库名orcl) 1、...