我的SQL笔记&一些练习题（一）
/*主外键关系一对多*/CREATE TABLE student
(
stuId VARCHAR2(10) PRIMARY KEY ----行级约束
,Name VARCHAR2(30) NOT NULL
,age VARCHAR2(3)NOT NULL
,address VARCHAR2(100)
,subId VARCHAR2(10) NOT NULL
/*创建外键将本表subId字段的取值引用（主表）至subject表的subId列*/
,CONSTRAINT fk_student_subId FOREIGN KEY(subid) REFERENCE subject(subId) --表级约束
);
-------------------------
/*主外键关系多对多*/
CREATE TABLE student
(
stuId VARCHAR2(10) PRIMARY KEY ----行级约束
,Name VARCHAR2(30) NOT NULL
,age VARCHAR2(3)NOT NULL
,address VARCHAR2(100)
/*创建外键将本表subId字段的取值引用（主表）至subject表的subId列*/
,CONSTRAINT fk_student_subId FOREIGN KEY(subid) REFERENCE subject(subId) --表级约束
);
CREATE TABLE subject
(
subId VARCHAR2(10) PRIMARY KEY
,nameVARCHAR2(30) NOT NULL
,description VARCHAR2(100) NOT NULL
,CONSTRAINT fk_relation_subId FOREIGN KEY(stuid) REFERENCE student(stuId)
,CONSTRAINT fk_relation_subId FOREIGN KEY(subid) REFERENCE subject(subId)
);
CREATE TABLE relation
(
rId VARCHAR2(10) PRIMARY KEY
,stuId VARCHAR2(10) NOT NULL
,subId VARCHAR2(10) NOT NULL
);
------------------------
CREATE TABLE student --CREATE TABLE 创建表格
(
stuNo VARCHAR2(10) --学号字符串类型
,stuName VARCHAR2(30) --学员姓名，字符串类型
,stuAgeNUMBER(3) --学员年龄，整数类型，3位长度
,stuScore NUMBER(3, 1) --学员成绩，浮点类型
);
/*为student表添加数据*/
INSERT INTO student --插入数据到表中
VALUES('TX1001', 'TOM', 20, 99.5);
INSERT INTO student
VALUES('TX1002', 'aa', 20, 99);
INSERT INTO student
VALUES('TX1003', 'bb', 20, 98);
/* 查询student表中数据*/
SELECT stuNo AS 学生编号, StuName AS 学生姓名, StuAge AS 学生年龄, stuScore / 10 AS 成绩
FROM student
WHERE stuNo = 'TX1001';
-------修改--------------
UPDATE student
SET stuScore = 8
WHERE stuNo = 'TX1001';
-------删除数据----------
DELETE FROM student
WHERE stuNo = 'TX1003';
/*查询全表数据*/
SELECT *
FROM student;
/*删除student表*/
DROP TABLE student;
/* 创建员工表 */9000
CREATE TABLE employee
(
eId VARCHAR2(10)
,eName VARCHAR2(30)
,eAge NUMBER(2)
,eSalary NUMBER(7, 2)
);
/* 为员工表添加数据 */
INSERT INTO employee
VALUES('EMP1001', 'Tom', 25, 5000.00);
INSERT INTO employee
VALUES('EMP1002', 'Jim', 25, 6000.00);
INSERT INTO employee
VALUES('EMP1003', 'Rose', 30, 8000.00);
/* 全表查询 */
SELECT *
FROM employee;
/* 查询列为表达式，并为列取别名 */
SELECT eName AS 姓名, eAge 年龄, eSalary * 12 年薪
FROM employee
WHERE eId = 'EMP1001';
/* 去除重复记录 */
SELECT DISTINCT eAge
FROM employee;
/* 修改数据 */
UPDATE employee
SET eSalary = 9000.00, eAge = 28
WHERE eId = 'EMP1003';
/* 删除记录 */
DELETE FROM employee
WHERE eId = 'EMP1003';
/* 创建表，使用约束 */
CREATE TABLE teacher
(
tId VARCHAR2(10) PRIMARY KEY
,tName VARCHAR2(30) UNIQUE NOT NULL
,tAgeNUMBER(2) CHECK(tAge >= 22 AND tAge
页:
[1]