mysql的使用

模式

创建模式:
创建模式的用户必须拥有数据库管理员权限
创建模式TEST并在其中定义一个表TAB1
CREATE SCHEMA TEST AUTHORIZATION ZHANG
CRATE TABLE TAB1(
    COL SMALLINT,
    COL2 INT,
    COL3 NUMERIC,
    COL4 DECIMAL
);

删除模式:
DROP SCHEMA <模式名><CASCADE|RESTRICT>
cascade级联:表示删除模式的同时该模式中的所有数据对象全部删除
restrict限制: 表示模式下如果定义了数据库对象,则拒绝该删除语句执行
DROP SCHEMA TEST CASCADE

基本表的定义

定义表的两种写法:
1, 列级完整性约束条件
CREATE TABLE student(
    sno char(9) PRIMARY KEY,
    sname char(200) UNIQUE,
    sdept char(20)
);
2, 表集完整性约束条件
CREATE TABLE sc(
    sno char(9),
    cno char(4),
    grade smallint,
    PRIMARY KEY (sno, cno),
    FOREIGN KEY(sno) REFERENCE student(sno),
    FOREIGN KEY(cno) REFERENCE course(cno)
);

连接表

/* 关系表 */
/* 内链接是取集合的交集 */
/* 左连接是取左表的所有记录，右表只显示符合搜索条件的记录 */

-- 产品目录(产品描述，价格，供应商) products
-- 供应商(名字，地址，联系) vendors
select vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;

-- 内部联结
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

-- 链接多个表
SELECT vend_name, prod_name, prod_price, quanitity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
AND orderitems.prod_id = products.prod_id
AND order num = 20005;

-- 右外链接
select customers.cust_id, orders.order_num
from customers RIGHT OUTER JOIN  orders
on orders.cust_id = customers.cust_id;

增删修查

/*查询*/
-- # 检索单个列
SELECT prdo_name from products;
-- # 检索多个列
SELECT prod_id, prod_name FROM products;
-- # 检索所有列
SELECT * FROM products;
-- # 检索不同的行
SELECT DISTINCT vend_id FROM products;
-- # 限制结果
SELECT prod_name FROM products LIMIT 5;
SELECT prod_name FROM products LIMIT 5,5;
-- # 使用完全限定的表明
SELECT products.prod_name FROM products;
-- # 排序数据
SELECT prod_name FROM products ORDER BY prod_name;
-- # 按多个列排序
SELECT prod_price, prod_name FROM products ORDER BY prod_price, prod_name
-- # 指定排序方向
SELECT prod_price, prod_name FROM products ORDER BY prod_price, prod_name DESC
SELECT prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name
-- # WHERE 子句操作符
-- =, !=, <, <=, BETWEEN, IS
SELECT prod_name FROM products WHERE prod_price IS NULL;
-- 组合 WHERE 子句
SELECT prod_id, prod_price, prod_name FROM products
WHERE vend_id = 1003 AND prod_price <= 10;

SELECT prod_id, prod_price, prod_name FROM products
WHERE vend_id = 1003 OR vend_id = 1002;

-- 计算次序, AND 高于 OR
SELECT prod_id, prod_price FROM products
WHERE (vend_id = 1003 OR vend_id = 1002) AND prod_price >= 10;

-- IN 操作符
SELECT prod_name, prod_price FROM products
WHERE vend_id IN (1002, 1003) ORDER BY prod_name;

-- NOT操作符
SELECT prod_name, prod_price FROM products
WHERE vend_id NOT IN (1002, 1003) ORDER BY prod_name;

-- 通配符 %， LIKE，_
SELECT prod_id, prod_price, prod_name FROM products
WHERE prod_name LIKE 'jet%';

SELECT prod_id, prod_price, prod_name FROM products
WHERE prod_name LIKE '%_jet_%';


SELECT prod_name from products WHERE prod_name REGEXP '//(^chen [0-9]{4} stick? //)'

SELECT COUNT (*) AS num_items,
       MIN (prod_price) AS price_min,
       max(prod_price) AS price_max
       FROM products

SELECT cust_id from orders where order_num in (
  select order_num from orderitems where prod_Id = 'TNT2'
)

-- 组合查询
SELECT vend_id, prod_Id, prod_price
FROM products
WHERE prod_price <= 5;
UNION
SELECT vend_id, prod_Id, prod_price
FROM products
WHERE vend_Id IN (1001, 1002)
ORDER BY vend_id, prod_price

-- 插入
INSERT INTO customers(cust_name, cust_address)
VALUES ('woyao', 'main street'),
       ('woruo', 'galaxy way')

-- 更新
UPDATE customers SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

-- 删除
DELETE FROM customers WHERE cust_id = 10006;

ALTER TABLE vendors ADD vend_phone CHAR (20);
ALTER TABLE vendors DROP COLUMN vend_phone;

DROP TABLE customers;
rename TABLE customers2 to customers;

使用视图

CREATE VIEW productCustomers AS
select cust_name, cust_contact, prod_id
from customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
and orderitems.order_num = orders.order_num;

全文本搜索

-- 搜索支持
-- match()指定搜索列，against()指定要使用的搜索表达式
CREATE TABLE productNotes(
  noted_id int NOT  null auto_increment,
  prod_id char(10) not NULL ,
  note_text text NULL,
  PRIMARY KEY (noted_id),
  FULLTEXT(note_text)
)ENGINE=MyISAM

select note_text FROM productNotes
WHERE MATCH(note_text)against('rabbit');

-- 查询扩展
SELECT note_text FROM productNotes
WHERE MATCH(note_text)against('anvils' WITH query expansion)

-- 布尔方式
select note_text FROM productNotes
WHERE MATCH(note_text)against('rabbit -rope*'IN boolean mode);
/*
+ 包含，词必须存在
- 排除，词必须不出现
> 包含，而且增加等级值
< 包含，且减少等级值
() 把词组成子表达式（允许这些子表达式作为一个组被包含、
排除、排列等）
~ 取消一个词的排序值
* 词尾的通配符
"" 定义一个短语（与单个词的列表不一样，它匹配整个短语以
便包含或排除这个短语）
*/

存储过程

-- begin,end用来限定存储过程体
CREATE PROCEDURE productpricing()
BEGIN
  SELECT avg(prod_price) as priceAverage
  FROM products
END;

CALL productpricing()
DROP PROCEDURE productpricing


CREATE PROCEDURE productpricing(out pl DECIMAL(8, 2),
  out ph DECIMAL(8, 2))
  BEGIN
    SELECT min(prod_price)
    INTO pl
    FROM products;
    SELECT max(prod_price)
    INTO ph
    FROM products;
  END;

call productpricing(@pricelow, @pricehigh)
select @pricelow


CREATE PROCEDURE ordertotal(IN onumber int,
  out ototal DECIMAL(8,2))
  BEGIN
    SELECT sum(item_price*quantity)
    FROM orderitems
    WHERE order_num = onumber
    into ototal
  END;

call ordertotal(20005, @total)
SELECT  @total

show create PROCEDURE ordertotal

CREATE proc C1
@fno char(20), @fdname char(2)
as insert into factory
values(@fno, @fdname)

exec C1 'aa', 'tom'

触发器

-- 触发器名字
-- 触发器关联的表
-- 触发器应该响应的活动
-- 触发器何时执行

CREATE trigger newproduct
after insert on products
for each row select 'product added'


DROP trigger newproduct

CREATE trigger updatevendor
before update on vendors
for each row set new.vend_state = Upper(new.vend_state)