目录
连接查询
连接查询是将多个表按某个条件连接起来,从中选取需要的数据,当不同的表中存在相同意义字段时,可以通过该字段连接起来。
内连接
-- cms_news id,title
-- cms_admin username,role
SELECT n.id,n.title,a.username,a.role
FROM
cms_news AS n
JOIN
cms_admin AS a
ON n.aId=a.id;
-- cms_news id ,title
-- cms_cate cateName
-- cms_admin username,role
SELECT n.id,n.title,c.cateName,a.username,a.role
FROM cms_cate AS c
JOIN
cms_news AS n
ON n.cId=c.id
JOIN
cms_admin AS a
ON n.aId=a.id;
外连接
-- 左外连接
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
LEFT JOIN provinces AS p
ON u.proId=p.id;
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
LEFT JOIN cms_user AS u
ON u.proId=p.id;
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
RIGHT JOIN cms_user AS u
ON u.proId=p.id;
--右外连接
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
RIGHT JOIN cms_user AS u
ON u.proId=p.id;
外键
如果公共关键字在一个关系中是主关键字,那么这个公共关键字被称为另一个关系的外键。外键作用是保持数据一致性,完整性,主要目的是控制存储在外键表中的数据。 使两张表形成关联,外键只能引用外表中的列的值或使用空值。
添加外键
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);
删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_fk_dep;
-- 创建员工表employee(子表)
-- id ,username ,depId
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES department(id) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=INNODB;
-- 创建员工表employee(子表)
-- id ,username ,depId
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES department(id) ON DELETE SET NULL ON UPDATE SET NULL
)ENGINE=INNODB;
联合查询
SELECT username FROM employee UNION SELECT username FROM cms_user;
SELECT username FROM employee UNION ALL SELECT username FROM cms_user;
SELECT id,username FROM employee UNION ALL SELECT username,age FROM cms_user;
-- 查询部门表中
SELECT * FROM department WHERE id=5;
SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id=5);
SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id=4);
SELECT id,username FROM employee WHERE NOT EXISTS(SELECT * FROM department WHERE id=41);
子查询
子查询就是指的在一个完整的查询语句之中,嵌套若干个不同功能的小查询,从而一起完成复杂查询的一种编写形式,为了让读者更加清楚子查询的概念。
-- 查询学员表中没有获得奖学金的学员
SELECT id,username,score FROM student WHERE score<ALL(SELECT level FROM scholarship);
SELECT id,username,score FROM student WHERE score<ANY(SELECT level FROM scholarship);
SELECT id,username,score FROM student WHERE score<=ANY(SELECT level FROM scholarship);
-- 相当于IN
SELECT id,username,score FROM student WHERE score=ANY(SELECT level FROM scholarship);
SELECT id,username,score FROM student WHERE score IN(SELECT level FROM scholarship);
-- 相当于NOT IN
SELECT id,username,score FROM student WHERE score NOT IN(SELECT level FROM scholarship);
SELECT id,username,score FROM student WHERE score <> ALL(SELECT level FROM scholarship);
字符
REGEXP正则表达式
^匹配字符开始的部分
-- 查询用户名以t开始的用户
SELECT * FROM cms_user WHERE username REGEXP '^t';
$匹配字符串结尾的部分
SELECT * FROM cms_user WHERE username REGEXP 'g$';
.代表任意字符
SELECT * FROM cms_user WHERE username REGEXP '.';
SELECT * FROM cms_user WHERE username REGEXP 'r..g';
SELECT * FROM cms_user WHERE username LIKE 'r__g';
[字符集合] [lto]
SELECT * FROM cms_user WHERE username REGEXP '[lto]';
[^字符集合] 除了字符集合中的内容
SELECT * FROM cms_user WHERE username REGEXP '[^lto]';
SELECT * FROM cms_user WHERE username REGEXP '[^l]';
INSERT cms_user(username,password,regTime,proId)
VALUES('lll','lll',138212349,2),
('ttt','lll',138212349,2),
('ooo','lll',138212349,2);
SELECT * FROM cms_user WHERE username REGEXP '[a-k]';
SELECT * FROM cms_user WHERE username REGEXP '[^a-m]';
SELECT * FROM cms_user WHERE username REGEXP 'ng|qu';
SELECT * FROM cms_user WHERE username REGEXP 'ng|qu|te';
SELECT * FROM cms_user WHERE username REGEXP 'que*';
SELECT * FROM cms_user WHERE username REGEXP 't+';
SELECT * FROM cms_user WHERE username REGEXP 'que+';
SELECT * FROM cms_user WHERE username REGEXP 'que{2}';
SELECT * FROM cms_user WHERE username REGEXP 'que{3}';
SELECT * FROM cms_user WHERE username REGEXP 'que{1,3}';
索引
索引好比手机的通讯录,你要找某个联系人,肯定是找姓名查找,索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她,则与在表中搜索所有的行相比,索引有助于更快地获取信息。
创建索引
-- 创建普通索引
CREATE TABLE test4(
id TINYINT UNSIGNED,
username VARCHAR(20),
INDEX in_id(id),
KEY in_username(username)
);
DROP INDEX in_id ON test4;
DROP INDEX in_username ON test4;
CREATE INDEX in_id ON test4(id);
ALTER TABLE test4 ADD INDEX in_username(username);
-- 创建唯一索引
CREATE TABLE test5(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
card CHAR(18) NOT NULL,
UNIQUE KEY uni_card(card)
);
ALTER TABLE test5 DROP INDEX uni_card;
DROP INDEX username ON test5;
CREATE UNIQUE INDEX uni_username ON test5(username);
ALTER TABLE test5 ADD UNIQUE INDEX uni_card(card);
-- 创建全文索引
CREATE TABLE test6(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
userDesc VARCHAR(20) NOT NULL,
FULLTEXT INDEX full_userDesc(userDesc)
);
DROP INDEX full_userDesc ON test6;
CREATE FULLTEXT INDEX full_userDesc ON test6(userDesc);
-- 创建单列索引
CREATE TABLE test7(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
test3 VARCHAR(20) NOT NULL,
test4 VARCHAR(20) NOT NULL,
INDEX in_test1(test1)
);
-- 创建多列索引
CREATE TABLE test8(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
test3 VARCHAR(20) NOT NULL,
test4 VARCHAR(20) NOT NULL,
INDEX mul_t1_t2_t3(test1,test2,test3)
);
ALTER TABLE test8 DROP INDEX mul_t1_t2_t3;
ALTER TABLE test8 ADD INDEX mul_ti_t2_t3(test1,test2,test3);
CREATE TABLE test9(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
test3 VARCHAR(20) NOT NULL,
test4 VARCHAR(20) NOT NULL,
UNIQUE KEY mul_t1_t2_t3(test1,test2,test3)
);
-- 创建空间索引
CREATE TABLE test10(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test GEOMETRY NOT NULL,
SPATIAL INDEX spa_test(test)
)ENGINE=MyISAM;
DROP INDEX spa_test ON test10;
CREATE SPATIAL INDEX spa_test ON test10(test);