学习MYSQL的笔记分享(二)

Valky
阅读25 喜欢0 教程 更新2019-11-26

重点内容 一、SELECT语句

SELECT [ALL|DISTINCT]
select_expr [select_expr...]
FROM table_references
[WHERE where_condition]
[GROUP BY{col_name|expr|position}[AS|DESC],...]
[HAVING where_condition]
[ORDER BY{col_name|expr|poistion}[AS|DESC],...]
[LIMIT{[offset,]row_count|row_count OFFSET offset}]
[INTO OUTFILE "file_name"]

ALL|DISTINCT:默认为ALL,即SELECT后可能存在重复行,DISTINCT则可消除重复行 select_expr:指定需要查询的内容 INTO OUTFILE:构成SELECT...INTO OUTFILE将表中的行导出到服务器主机的一个文件 二、列的指定和选择 1.选择指定的列 列名之间用逗号隔开,查询所有列可用*通配符,列名也可写为tbl_name.col_name的格式 (1)查询数据库mysql_test的表customers中各个客户的姓名、性别和地址信息

USE mysql_test;
SELECT cust_name,cust_sex,cust_address
FROM customers;
SELECT DISTINCT(salary) FROM userinfo;
//检索用户表中工资有哪几种

(2)查询数据库mysql_test的表customers中各个客户的所有信息

USE mysql_test;
SELECT * FROM customers;

2.定义并使用列的别名

column_name[AS]column alias
SELECT cust_name,cust_address AS "地址",cust_contact
FROM customers;
//查询数据库中表customers中客户的cust_name,cust_address,cust_contact,并将cust_address列的名称用“地址”替代

3.替换查询结果集中的数据

CASE
WHEN 条件1 THEN 表达式1
WHEN 条件2 THEN 表达式2
...
ELSE 表达式
END [AS column_alias]
SELECT cust_name,
CASE WHEN cust_sex="M" THEN "男"
ELSE "女" AS "性别" FROM customers;
//判断cust_sex为M时显示为男,否则显示为女,同时cust_sex列的别名为“性别”

4.计算列值

SELECT cust_name,cust_sex,cust_id+1000
FROM customers;
//查询表中的cust_name,cust_sex,并对cust_id列加上数字100后的值

5.计算聚合函数 COUNT:求组中项数,返回INT类型整数 MAX:求最大值 MIN:求最小值 SUM:求和 AVG:求平均值

SELECT MAX(salary) FROM useinfo;//求表中salary列的最大值
SELECT SUM(salary) FROM useinfo;//求表中salry列的总和
三、FROM子句与连接表 1.FROM子句的语法
FROM table references;
2.连接表 (1)交叉连接:又称笛卡尔积,或没有连接
SELECT * FROM tbl1 CROSS JOIN tbl2;
SELECT * FROM tbl1 tbl2; //两张表记录行数的乘积
(2)内连接
SELECT * FROM userinfo inner join department on
userinfo.departmentid=department.departmentid //连接表userinfo和表department

相等连接:连接条件采用运算符“=”,通常包含一个主键一个外键 自连接:与它自身连接,为表指定不同的别名 不等连接:使用除运算符“=”之外的比较运算符 

(3)自然连接(natural join)

SELECT * FROM userinfo natural join department;
(4)外连接(left/right outer join) 四、WHERE子句 1.比较运算符
SELECT name,sex,salary,departmentid FROM userinfo
WHEREW sex="male" AND salary>4000;
SELECT MAX(salary) FROM userinfo
WHERE departmentid=101;
2.字符串匹配 运算符LIKE%--任何字符,且出现任意次数
SELECT * FROM customers WHERE cust_name
LIKE "王%";
//查找所有王姓的客户
注意:%可代表0、1或多个字符,%不能匹配空值 运算符_匹配单个字符
SELECT cust_id,cust_name FROM customers 
WHERE cust_name LIKE "万_";
在使用LIKE运算符时,可使用ESCAPE和一个转义字符,临时改变通配符的作用。
SELECT cust_address FROM customers
WHERE cust_address LIKE "%#_%" ESCAPE"#"; 
//#作转义字符,ESCAPE声明#是用来作转义字符,匹配地址中带有_这个符号的地址
注意:不要过度使用通配符,注意位置。 3.文本匹配 (1)基本字符匹配
SELECT DISTINCT cust_address FROM customers
WHERE cust_address REGEXP "市";
注意:区分大小写,需在REGEXP之后再加BINARY (2)| 选择匹配
SELECT DISTINCT cust_address FROM ccustomers
WHERE cust_address REGEXP "北京|武汉|上海";
//查询带有北京上海武汉的城市地址信息
(3)范围匹配:[ ]所包含的字符或数字的组合
SELECT * FROM userinfo where salary REGEXP"[4]";
(4)特殊字符匹配:在特殊字符前使用作为前导
SELECT * FROM customers
WHERE cust_address REGEXP "\_";
(5)字符类匹配:经常使用的数字、字符到呢个定义一个预定义字符集 (6)重复匹配 使用定位符匹配:^文本的开始,$文本的结尾
SELECT * FROM customers
WHERE salary REGEXP "^[3-4]";

SELECT * FROM customers
WHERE salary REGEXP "$[3-4]";
4.判断范围 (1)BETWEEN...AND [NOT] BETWEEN...AND
SELECT * FROM customers
WHERE cust_id BETWEEN 903 AND 905;
//查询cust_id在903-905之间的用户信息
(2)IN
SELECT * FROM customers
WHERE cust_id IN (903,905,908);
//查询cust_id为903,905,908的用户信息
5.判定空值——IS [NOT] NULL
SELECT cust_name FROM customers
WHERE cust_contact IS NULL;
//查询是否存在没有填写联系方式的客户名字
6.子查询 嵌套在SELECT查询中的SELECT查询 (1) [NOT] IN
SELECT * FROM cust_name cust_id IN 
(SELECT cust_id FROM customers WERE salary>4000);
//customers表中工资大于4000的id,再通过id查找到对应的name
(2)结合比较运算符 Expression 比较运算符 { ALL|SOME|ANY} (subquery) (3)结合EXIST使用的子查询 EXIST (subquery) 五、GROUP BY子句与分组数据
GROUP BY { col_name|expr|position} [ASC|DESC],...[WITH ROLLUP]
//表中获取一个数据结果集,要求该结果集中包含每个相同地址的男性客户人数和女性客户人数
 SELECT cust_address,cust_sex COUNT(*) AS "人数"
 FROM customers GROUP BY cust_address,cust_sex;
//表中获取一个数据结果集,要求该结果集中包含每个相同地址的男性客户人数和女性客户人数、
总人数以及客户总人数
SELECT cust_address,cust_sex COUNT(*) AS "人数"
 FROM customers GROUP BY cust_address,cust_sex WITH ROLLUP;
六、HAVING子句 HAVING where_condition,用于在分组里指定过滤条件,必须与GROUP BY一起使用 HAVING与WHERE的差异: WHERE用于过滤行,HAVING用于过滤分组; WHERE不包含聚合函数,HAVING可以; WHERE在分组前,HAVING在分组后。
SELECT cust_name,cust_address FROM customers
GROUP BY cust_address
HAVING COUNT (cust_name)<=3;
//在表中查找客户信息,同一地址中客户人数小于3的
七、ORDER BY子句
ORDER BY col_name [ASC|DESC]
SELECT * FROM userinfo
ORDER BY salary DESC,departmentid ASC;
//对于userinfo表,依次按照工资降序,部门号升序输出所有信息
八、LIMIT子句 限制SELECT返回的行数
LIMIT{[offset,]row_count OFFSET offset}
//查找从第三位客户开始的两位客户的id和客户信息
//tip1:
SELECT cust_id,cust_name FROM customers
ORDER BY cust_id LIMIT 2,2;
//tip2:
SELECT cust_id,cust_name FROM customers
ORDER BY cust_id LIMIT 2 OFFSET 2;
九、UNION语句与联合查询
SELECT ...UNION [ALL|DISTINCT] SELECT...
SELECT cust_name FROM customers WHERE cust_sex="F"
UNION SELECT cust_name FROM customers WHERE cust_address="北京市";

SELECT cust_name FROM customers WHERE cust_sex="F" OR cust_address="北京市";
//查找性别为’F‘或地址是北京市的客户姓名
WHERE与UNION相比:执行性能有所不同,UNION ALL可包含重复行