学习MYSQL的笔记分享(二)

/ 1评 / 1

重点内容
一、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可包含重复行

一条回应:“学习MYSQL的笔记分享(二)”

  1. 小鸡说道:

    不错不错

发表评论

电子邮件地址不会被公开。 必填项已用*标注