mysql基本語法

union和union all

-- 查詢t_book的id
SELECT id FROM t_book;
-- 查詢t_booktype的id
SELECT id FROM t_booktype;
-- union查詢去重
SELECT id FROM t_book UNION SELECT id FROM t_booktype;
-- union all簡單重復
SELECT id FROM t_book UNION ALL SELECT id FROM t_booktype;

使用別名查詢，方便書寫

-- 使用別名查詢
SELECT * FROM t_book WHERE id=1;
SELECT * FROM t_book t WHERE t.id=1;
SELECT t.bookName FROM t_book t WHERE t.id=1;
SELECT t.bookName bName FROM t_book t WHERE t.id=1;
SELECT t.bookName AS bName FROM t_book t WHERE t.id=1;

插入幾條樣例記錄，方便更新和洗掉

INSERT INTO t_book VALUES(NULL,'我愛我家',20,'張三',1);

INSERT INTO t_book(id,bookName,price,author,bookTypeId) VALUES(NULL,'我愛我家',20,'張三',1);

INSERT INTO t_book(bookName,author) VALUES('我愛我家','張三');

INSERT INTO t_book(id,bookName,price,author,bookTypeId) VALUES (NULL,'我愛我家2',20,'張三',1),(NULL,'我愛我家3',20,'張三',1);

-- 根據主鍵更新記錄
UPDATE t_book SET bookName='Java編程思想',price=120 WHERE id=1;
-- 根據模糊條件跟新記錄
UPDATE t_book SET bookName='我' WHERE bookName LIKE '%我愛我家%';
-- 根據主鍵洗掉記錄
DELETE FROM t_book WHERE id=5;
-- 根據條件洗掉記錄
DELETE FROM t_book WHERE bookName='我';

基本的索引

-- 創建普通索引
CREATE TABLE t_user1(id INT ,
                     userName VARCHAR(20),
                     PASSWORD VARCHAR(20),
                     INDEX (userName)
                 );
-- 創建唯一性索引                 
CREATE TABLE t_user2(id INT ,
                     userName VARCHAR(20),
                     PASSWORD VARCHAR(20),
                     UNIQUE INDEX index_userName(userName)
                 );
-- 創建全文索引               
CREATE TABLE t_user3(id INT ,
                     userName VARCHAR(20),
                     PASSWORD VARCHAR(20),
                     INDEX index_userName_password(userName,PASSWORD)
                 );
-- 創建單列索引                 
CREATE     INDEX index_userName ON t_user4(userName);
-- 創建多列索引
CREATE     UNIQUE INDEX index_userName ON t_user4(userName);
-- 創建空間索引
CREATE  INDEX index_userName_password ON t_user4(userName,PASSWORD);
-- 在已經存在的表上創建索引
ALTER TABLE t_user5 ADD INDEX index_userName(userName);
-- 使用ALTER TABLE 陳述句來創建索引
ALTER TABLE t_user5 ADD UNIQUE INDEX index_userName(userName);
ALTER TABLE t_user5 ADD INDEX index_userName_password(userName,PASSWORD);
-- 洗掉索引
DROP INDEX index_userName ON t_user5;

DROP INDEX index_userName_password ON t_user5;

視圖

-- 創建視圖
CREATE VIEW v1 AS SELECT * FROM t_book;

CREATE VIEW v2 AS SELECT bookName,price FROM t_book;

CREATE VIEW v3(b,p) AS SELECT bookName,price FROM t_book;


SELECT * FROM v1;

SELECT * FROM v2;

SELECT * FROM v3;
-- 在多表上創建視圖
CREATE VIEW v4 AS SELECT bookName,bookTypeName FROM t_book,t_booktype WHERE t_book.bookTypeId=t_booktype.id;

CREATE VIEW v5 AS SELECT tb.bookName,tby.bookTypeName FROM t_book tb,t_booktype tby WHERE tb.bookTypeId=tby.id;

SELECT * FROM v4;

SELECT * FROM v5;
-- 陳述句查看視圖基本資訊
DESC v5;
SHOW TABLE STATUS LIKE 'v5';
SHOW TABLE STATUS LIKE 't_book';
-- 陳述句查看視圖詳細資訊
SHOW CREATE VIEW v5;

觸發器

-- 一個執行陳述句的觸發器
CREATE TRIGGER trig_book AFTER INSERT 
     ON t_book FOR EACH ROW
        UPDATE t_bookType SET bookNum=bookNum+1 WHERE new.bookTypeId=t_booktype.id;
        
-- 顯然這里主鍵不能為空      
INSERT INTO t_book VALUES(NULL,'java好',100,'ke',1);

-- DELIMITER是用來執行整條陳述句的
DELIMITER |
CREATE TRIGGER trig_book2 AFTER DELETE 
    ON t_book FOR EACH ROW
    BEGIN
       UPDATE t_bookType SET bookNum=bookNum-1 WHERE old.bookTypeId=t_booktype.id;
       INSERT INTO t_log VALUES(NULL,NOW(),'在book表里洗掉了一條資料');
       DELETE FROM t_test WHERE old.bookTypeId=t_test.id;
    END 
|
DELIMITER ;
-- 刪記錄
DELETE FROM t_book WHERE id=5;
-- 查看觸發器記錄
SHOW TRIGGERS;
-- 刪觸發器
DROP TRIGGER trig_book2 ;

博客使用的mysql實體均來自http://www.java1234.com/

標籤：MySQL

上一篇：mysql自定義排序

下一篇：MySQL 操作資料庫、資料表