Mysql基础学习四-CSDN博客

本文链接：https://blog.csdn.net/Wind_know/article/details/106275032

连接查询

连接查询是将多个表按某个条件连接起来，从中选取需要的数据，当不同的表中存在相同意义字段时，可以通过该字段连接起来。

内连接

-- 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);