HIREDATE DATE CONSTRAINT EMPLYEE_HIREDATE_GH NOT NULL--全写,自己定义名字 )
DESC EMPLYEE_GH
****添加非空约束,必须在修改该列的情况下添加 ALTER TABLE EMPLYEE_GH
MODIFY (ID NUMBER(6) NOT NULL)
****唯一性约束 uk null除外
可以进行列级约束或表及约束(就是写完所有列,最后增加), CREATE TABLE EMPLYEE11( EID NUMBER (6) UNIQUE, NAME VARCHAR2(30), EMAIL VARCHAR2(50), SALARY NUMBER(7,2), HIREDATE DATE,
CONSTRAINT EMPLY1_EMAIL_UK UNIQUE(EMAIL) )
DESC EMPLYEE11
INSERT INTO EMPLYEE11(EID,NAME,EMAIL) VALUES(1,'SB','SB.QOM')
插入失败,因为id,email具有唯一性,null除外 INSERT INTO EMPLYEE1(EID,NAME,EMAIL) VALUES(1,'SB','SB.QOM')
INSERT INTO EMPLYEE11(EID,NAME,EMAIL) VALUES(NULL,'SB',NULL)
SELECT * FROM EMPLYEE11
***添加非空约束(表及约束),如果表上有相同的值,就不能添加唯一性约束 ALTER TABLE EMPLYEE11
ADD CONSTRAINT EMPLYEE11_NAME_UK UNIQUE(NAME)
*****主键约束 PRIMARY KEY
**用于唯一标示用的一列,一个表中只有一个主键, ** 就是唯一约束,非空约束的结合 CREATE TABLE EMPLYEE22( EID NUMBER(6) PRIMARY KEY, NAME VARCHAR2(30), EMAIL VARCHAR2(50), salary NUMBER(7,2), HIREDATE DATE )
DESC EMPLYEE22
***每一次插入数据,主键必须添加
INSERT INTO EMPLYEE22(NAME) VALUES('SB')
****一个表的外键,就是关联表的另一张的主键 ALTER TABLE EMPLYEE22
ADD CONSTRAINT EMPLYEE22_SAL_CHECK CHECK(SALARY>2000)
INSERT INTO EMPLYEE22(EID,NAME,SALARY) VALUES(1500,'SB',1000)
INSERT INTO EMPLYEE22(EID,NAME,SALARY) VALUES(1500,'SB',NULL)--不得行 SELECT * FROM EMPLYEE22 *************test
1:创建一个视图，包含20号部门的员工信息，字段:empno,ename,sal,JOB,deptno CREATE VIEW V_EMP_GH_20 AS
SELECT EMPNO,SAL,JOB,DEPTNO FROM EMP_GH WHERE DEPTNO=20
DROP VIEW V_EMP_GH_20
2:创建一个序列seq_emp_no,从10开始，步进为10 CREATE SEQUENCE SEQ_EMP_NO_GH START WITH 10 INCREMENT BY 10
SELECT SEQ_EMP_NO_GH.NEXTVAL FROM DUAL SELECT SEQ_EMP_NO_GH.CURRVAL FROM DUAL DROP SEQUENCE SEQ_EMP_NO_GH
3:编写SQL语句查看seq_emp_no序列的下一个数字
4:编写SQL语句查看seq_emp_no序列的当前数字 5:为emp表的ename字段添加索引:idx_emp_ename CREATE INDEX IDX_EMP_ENAME ON EMP(ENAME)
6:为emp表的LOWER(ename)字段添加索引:idx_emp_lower_ename CREATE INDEX IDFNHSI ON EMP(UPPER(ENAME)) DROP INDEX IDX_EMP_ENAME
7:为emp表的sal,comm添加多列索引
CREATE INDEX IDX_EMP_SAL_COMM ON EMP_GH(SAL,COMM) 8:创建myemployee表，字段: id NUMBER(4) , nameVARCHAR2(20), birthday DATE,
telephone VARCHAR2(11) scoreNUMBER(9,2)
其中id作为主键，name要求不能为空,telephone需要唯一,score值必须>=0
CREATE TABLE MYEMPLYEE_GH( ID NUMBER(4) PRIMARY KEY, NAME VARCHAR2(20) not null, birthday DATE,
telephone VARCHAR2(11) UNIQUE, scroe number(9,2) check(scroe>=0) )
DESC myemplyee_gh
SELECT * FROM MYEMPLYEE_GH
INSERT INTO myemplyee_gh
VALUES(1,'sb',to_date('2015-1-5','YYYY-MM-DD'),'12025143925',53)
INSERT INTO myemplyee_gh(ID,NAME) VALUES(12,'SB')
INSERT INTO myemplyee_gh(ID,NAME,TELEPHONE,SCROE) VALUES(132,'NB','12025163925',-2)