SQL查询与优化-优快云博客

(1)数据库查询

格式：
SELECT <列名1,2,3...> FROM <表名> [WHERE子句] [GROUP BY 子句] [HAVING 子句] [ORDER BY子句] [LIMIT子句];
例子：

SELECT 1+4-3;
SELECT * FROM table;
SELECT a,b FROM table;
SELECT a,b AS '别名' FROM table;*//(别名不允许出现在WHERE中)
SELECT a,b,b+100 FROM table;

case条件语句:

SELECT a,
CASE
WHEN sex='0' THEN '男'
ELSE '女'
END AS '性别'
FROM table;

聚合函数 COUNT,MAX,MIN,SUM,AVG,STD,VARIANCE...

SELECT MAX(a) FROM table;

连接表(JOIN)

交叉连接（CROSS JOIN）:SELECT * FROM a CROSS JOIN b;
内连接（INNER JOIN）:SELECT * FROM a INNER JOIN b ON a.id>b.id;有限制的交叉连接
相等连接：上面a.id=b.id;
不等连接：类似上面
自然连接：(NATURAL JOIN)

WHERE子句

比较运算：SELECT * FROM table WHERE a>2;
字符串统配:SELECET id,name FROM table WHERE name LIKE '李%'//百分号不匹配NULL，匹配0,1，多个字符，下划线匹配一个字符；
文本匹配（正则表达式REGEXP）:SELECT name,age,major FROM table WHERE major REGEXP '物';//匹配专业里面含有物字的所有行（| = OR）([ ] = 集合) (\=转义字符）（*,+,?,{n},{n,},{n,m} = 重复元字符）（^,&=匹配定位符）

判定范围：

1) BETWEEN AND
SELECE name,age FROM table WHERE age BETWEEN 20 AND 23;
2) IN
SELECT ....
WHERE age IN (20,23);
判定空值(IS [NOT] NULL):SELECT a,b FROM table WHERE b IS NULL;

GROUP BY 分组

GROUP BY { <列名> | <表达式> |<位置>} [ASC | DESC]]
SELECT sex,count(*) AS '人数' FROM table GROUP BY sex;
SELECT maior AVG(age) AS '平均年龄' FROM table GROUP BY major;

HAVING 过滤分组

SELECT major,COUNT(*) AS '总人数' FROM table GROUP BY major HAVING COUNT(*)>2;
//ORDER BY  同GROUP BY
SELECT name,age FORM table ORDER BY age DESC;//NULL排序为最小值

LIMIT

*//限制显示行数

SELECT id,name FROM table LIMIT 4,3;//第五行开始取三行数据
SELECT id,name FROM table ORDER BY id LIMIT 3 OFFSET 4;//同上

UNION联合查询

*//(多条SELECT联合查询，返回一条查询结果，ORDER,LIMIT必须置于最后一个SELECT)
SELECT... UNION [ALL |DISTINCT] SELECT...

SELECT name,major,sex FROM table WHERE majot='物理专业' UNION SELECT name,.... FROM table WHERE sex='1';

(2)索引

创建索引：

例子：

CREATE  INDEX <索引名> ON table( <列名>[ <长度> ] [ASC| DESC] )
CREATE INDEX index_name ON table(a(3) ASC);//单列索引
CREATE INDEX index_name ON table(a,b) USING BTREE;//组合索引

CREATE TABLE table (id,INT,NOT NULL,........,
PRIMARY KEY(id), *//主键
INDEX indx_course(name), *//普通索引
or
UNIQUE INDEX index_name(a)* //唯一索引
or
FOREIGN KEY index_name(a))；*//外键

ALTER TABLE table 
ADD INDEX index_name(a);
or
ADD PRIMARY KEY(a);
or
ADD UNIQUE INDEX index_name(a);
or
ADD FOREIGN KEY index_name(a);

查看索引：

SHOW INDEX FROM table FROM database;

删除索引：

1)DROP INDEX index_name ON table;
2)ALTER TABLE table
DROP PRIMARY KEY(a);
or
DROP INDEX index_name(a);
or
DROP FORIEN KEY index_name(a);

(3)视图

*//（虚拟表，不包含数据，仅显示数据）

创建和删除视图

CREATE VIEW <视图名> AS <SELECT语句>

CREATE OR REPLACE VIEW view_name
AS
SELECT a,b,c 
FROM table 
WHERE a ='0' 
WITH CHECK OPTION ;*//(插入数据需符合WHERE条件)

DROP VIEW view_name;

修改查看视图：

ALTER VIEW view_name AS <SELECT语句> //同CREATE VIEW
OR
SHOW CREAE VIEW view_name

更新视图数据

*//（插入，修改，删除更新视图时实际更新了基础表数据,视图更新受限）

INSERT INTO view_name VALUES(.....)；*//视图依赖多个基础表时，不能插入，更新只更新一个表，不能删除
UPDATE view_name SET a = '0' WHERE b = 22；
DELETE FROM view_name WHERE a='王五';

查询视图数据（同查询表）

SELECT a,b FROM view_name WHERE A='0';

创建嵌套视图

CREATE VIEW view_a AS SELECT * FROM view_b WHERE c='物理专业';

转载于:https://www.cnblogs.com/CYRgrowing1215/p/9520265.html