在oracle中创建store模式.pptVIP

在Oracle中创建store模式 SQL Developer 需要安装JDK 启动后建立连接 可以操作表、运行脚本、调试PL/SQL代码等 建立连接 简单查询 创建store模式 管理员登录 运行store_schema.sql 从下载 创建后的store模式 创建数据库用户 DROP USER store CASCADE; -- create the store user CREATE USER store IDENTIFIED BY store_password; -- allow the store user to connect and create database items GRANT connect, resource TO store; -- connect as the store user CONNECT store/store_password; 创建表 -- create the tables CREATE TABLE customers ( customer_id INTEGER CONSTRAINT customers_pk PRIMARY KEY, first_name VARCHAR2(10) NOT NULL, last_name VARCHAR2(10) NOT NULL, dob DATE, phone VARCHAR2(12) ); 创建表（续） CREATE TABLE product_types ( product_type_id INTEGER CONSTRAINT product_types_pk PRIMARY KEY, name VARCHAR2(10) NOT NULL ); 创建表（续） CREATE TABLE products ( product_id INTEGER CONSTRAINT products_pk PRIMARY KEY, product_type_id INTEGER CONSTRAINT products_fk_product_types REFERENCES product_types(product_type_id), name VARCHAR2(30) NOT NULL, description VARCHAR2(50), price NUMBER(5, 2) ); 添加行 INSERT INTO customers ( customer_id, first_name, last_name, dob, phone ) VALUES ( 1, John, Brown, 01- 1月-1965, 800-555-1211 ); 修改行 UPDATE customers set last_name=‘orange’ WHERE customer_id=2; 删除行 DELETE FROM customers WHERE customer_id=1 ROLLBACK Oracle 数据库检索 选择所有的行 SELECT * FROM customers; SELECT customer_id, first_name, Last_name, dob,phone FROM customers WHERE 条件子句 SELECT * FROM customers WHERE customer_id = 2 条件可以用AND、OR连接或前加NOT 行标识符和行号 ROWID：唯一的行标识符 ROWNUM：结果集中的相对行号 SELECT ROWID, customer_id FROM customers SELECT ROWNUM, customer_id FROM customers SELECT ROWNUM, customer_id FROM customers WHERE customer_id = 3 执行算术运算 运算符： + - * / SELECT name, price,price+2 FROM products SELECT name, price,price*3+1 FROM products 列别名 SELECT expression ALIAS expression AS ALIAS SELECT price*2 DOUBLE_PRICE FROM products SELECT price*2 DOUBLE PRICE FROM products SELECT price*2 AS DOUBLE_PRICE FROM products 合并列 连接操作符： || SELECT first_name ||