MYSQL常用命令(二)

select 查询
语法:
SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>, ] <row count>]
]
SELECT <字段名1>, <字段名2>, …, <字段名n> FROM <表名>;

可以使用DISTINCT关键字指示MySQL消除重复的记录值,语法格式为:
SELECT DISTINCT <字段名> FROM <表名>;

为表取别名的基本语法格式为:
<表名> [AS] <别名>

多表查询
连接查询是关系数据库重要的查询,主要包括内连接、外连接等。通过连接运算符可以实现多个表的查询。
内连接是通过在查询中设置连接条件的方式,来移除查询结果集中某些数据行后的交叉连接。简单来说,就是利用条件表达式来消除交叉连接的某些数据行,在FROM子句中使用关键字INNER JOIN连接两张表,并使用ON子句来设置连接条件。如果没有任何条件,INNER JOIN和CROSS JOIN在语法上是等同的,两者可以互换。
语法格式如下:
SELECT <列名1,列名2 …>
FROM <表名1> INNER JOIN <表名2> [ ON子句]
例:
mysql> SELECT id, name, age, dept_name
-> FROM tb_students_info, tb_departments
-> ON tb_students_info.dept_id=tb_departments.dept_id;

自查询
自连接是将一个表和它自身进行连接,也是内连接的一种,同样使用INNER JOIN或者JOIN关键字来进行连接。
如果需要在一个表中查找具有相同列值的行,就可以考虑用自连接。
mysql> SELECT s1.id, s1.name
-> FROM tb_students_info s1, tb_students_info s2
-> WHERE s1.dept_id=s2.dept_id
-> AND s2.id = 1;

外连接先将连接的表分为基表和参考表,再以基表为依据返回满足和不满足条件的记录。

外连接更加注重两张表之间的关系。按照连接表的顺序,可以分为左外连接和右外连接。
左外连接又称为左连接,在FROM子句中使用关键字LEFT OUTER JOIN或者LEFT JOIN,用于接收该关键字左表(基表)的所有行,并用这些行与该关键字右表(参考表)中的行进行匹配,即匹配左表中的每一行及右表中符合条件的行。在左外连接的结果集中,除了匹配的行之外,还包括左表中有但在右表中不匹配的行,对于这样的行,从右表中选择的列的值被设置为NULL,即左外连接的结果集中的NULL值表示右表中没有找到与左表相符的记录。

左连接:mysql> SELECT name, dept_name
-> FROM tb_students_info s
-> LEFT OUTER JOIN tb_departments d
-> ON s.dept_id = d.dept_id;
+--------+-----------+
| name | dept_name |
+--------+-----------+
| Dany | Computer |
| Jane | Computer |
| Jim | Computer |
| Henry | Math |
| John | Math |
| Green | Chinese |
| Thomas | Chinese |
| Susan | Economy |
| Tom | Economy |
| Lily | NULL |
+--------+-----------+
10 rows in set (0.03 sec)
右连接:与左外连接相反,右外连接以右表为基表,连接方法和左外连接相同。在右外连接的结果集中,除了匹配的行外,还包括右表中有但在左表中不匹配的行,对于这样的行,从左表中选择的值被设置为NULL。
右连接:
mysql> SELECT name, dept_name
-> FROM tb_students_info s
-> RIGHT OUTER JOIN tb_departments d(基表)
-> ON s.dept_id = d.dept_id;
+--------+-----------+
| name | dept_name |
+--------+-----------+
| Dany | Computer |
| Green | Chinese |
| Henry | Math |
| Jane | Computer |
| Jim | Computer |
| John | Math |
| Susan | Economy |
| Thomas | Chinese |
| Tom | Economy |
| NULL | History |

子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询,这个特性从MySQL 4.1开始引入,在SELECT子句中先计算子查询,子查询结果作为外层另一个查询的过滤条件,查询可以基于一个表或者多个表。
子查询中常用的操作符有ANY(SOME)、ALL、IN和EXISTS。子查询可以添加到SELECT、UPDATE和DELETE语句中,而且可以进行多层嵌套。子查询也可以使用比较运算符,如“<”“<=”“>”“>=”“! =”等。
例:
mysql> SELECT * FROM tb_students_info WHERE EXISTS (SELECT dept_name FROM tb_departments WHERE dept_id=1);

mysql> SELECT name FROM tb_students_info
-> WHERE dept_id NOT IN
-> (SELECT dept_id
-> FROM tb_departments
-> WHERE dept_type=‘A‘);
使用分组查询的时机
在SELECT语句中,允许使用GROUP BY子句,将结果集中的数据行根据选择列的值进行逻辑分组,以便能汇总表内容的子集,实现对每个组而不是对整个结果集进行整合。
其语法格式为:GROUP BY { <列名> | <表达式> | <位置> } [ASC | DESC]
例:
mysql> SELECT dept_id, GROUP_CONCAT(name) AS names
-> FROM tb_students_info
-> GROUP BY dept_id 分组
-> HAVING COUNT(name)>1; having 子句过滤分组
+---------+---------------+
| dept_id | names |
+---------+---------------+
| 1 | Dany, Jane, Jim |
| 2 | Henry, John |
| 3 | Green, Thomas |
| 4 | Susan, Tom |
+---------+---------------+
4 rows in set (0.07 sec)

索引(略)

INSERT语句有两种语法形式,分别是INSERT…VALUES语句和INSERT…SET语句。
(1)INSERT…VALUES语句
INSERT VALUES的语法格式为:
INSERT INTO <表名> [ <列名1> [ , … <列名n>] ]
VALUES (值1) [… , (值n) ];
语法说明如下。
● <表名>:指定被操作的表名。
● <列名>:指定需要插入数据的列名。若向表中的所有列插入数据,则全部的列名均可以省略,直接采用INSERT <表名> VALUES (…) 即可。
● VALUES或VALUE子句:该子句包含要插入的数据清单。数据清单中数据的顺序要和列的顺序相对应。
(2)INSERT…SET语句
语法格式为:
INSERT INTO <表名>
SET <列名1> = <值1>,
<列名2> = <值2>,

此语句用于直接给表中的某些列指定对应的列值,即要插入的数据的列名在SET子句中指定,col_name为指定的列名,等号后面为指定的数据,而对于未指定的列,列值会指定为该列的默认值。

UPDATE语句的基本语法
使用UPDATE语句修改单个表,语法格式为:
UPDATE <表名> SET字段1=值1 [,字段2=值2… ] [WHERE子句 ]
[ORDER BY子句] [LIMIT子句]

删除单个表中的数据
使用DELETE语句从单个表中删除数据,语法格式为:
DELETE FROM <表名> [WHERE子句] [ORDER BY子句] [LIMIT子句]

创建用户
可以使用CREATE USER语句来创建一个或多个MySQL账户,并设置相应的口令。
语法格式:
CREATE USER <用户名> [ IDENTIFIED ] BY [ PASSWORD ] <口令>
GRANT
<权限类型> [ ( <列名> ) ] [ , <权限类型> [ ( <列名> ) ] ]
ON <对象> <权限级别> TO <用户>
其中<用户>的格式:
<用户名> [ IDENTIFIED ] BY [ PASSWORD ] <口令>
[ WITH GRANT OPTION]
| MAX_QUERIES_PER_HOUR <次数>
| MAX_UPDATES_PER_HOUR <次数>
| MAX_CONNECTIONS_PER_HOUR <次数>
| MAX_USER_CONNECTIONS <次数>
例:mysql> GRANT SELECT, INSERT ON *.*
-> TO ‘testUser‘@‘localhost‘
-> IDENTIFIED BY ‘testPwd‘
-> WITH GRANT OPTION;

可以使用REVOKE语句撤销一个用户的权限,此用户不会被删除。
语法格式如下。
第一种:
REVOKE <权限类型> [ ( <列名> ) ] [ , <权限类型> [ ( <列名> ) ] ]…
ON <对象类型> <权限名> FROM <用户1> [ , <用户2> ]…
第二种:
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user <用户1> [ , <用户2> ]…

MYSQL常用命令(二)

上一篇:【docker】MySQL忽略大小写


下一篇:FastAdmin 速极后台框架从 v1.0 到 v1.2 的数据库升级