mysql基本语法，

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; View Code

使用别名查询，方便书写

-- 使用别名查询 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; View Code

插入几条样例记录，方便更新和删除

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); View Code -- 根据主键更新记录 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='我'; View Code

基本的索引

-- 创建普通索引 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; View Code

视图

-- 创建视图 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; View Code

触发器

-- 一个执行语句的触发器 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 ; View Code

博客使用的mysql实例均来自http://www.java1234.com/