mysql基本语法 -云博客

mysql基本语法

# 数据库 2024-05-05 03:25 0 18 来源：云博客

union和union all

-- 查询t_book的idSELECT id FROM t_book;-- 查询t_booktype的idSELECT 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 ;