数据库_days2_查询数据（1）

查询记录

SELECT select_expr [, select expr ..]
[
FROM table_ references
[WHERE条件]
[GROUP BY {col_name | position} [ASC | DESC], ...分组]
[HAVING条件对分组结果进行二次筛选]
[ORDER BY {col_name | position} [ASC | DESC], ..排序]
[LIMIT限制显示条数]
]

查询表达式

• 每一个表达式表示想要的一列，必须至少有一列，多个列之间以逗号隔开
• '*'表示所有列,tbI_name. *可以表示命名表的所有列
• 查询长达式可以使用[AS]alias_name为其赋予別名

#cms_admin是已经创建好的表，存入了以下信息：
| id | username | password | email| role

查询

SELECT * FROM cms_admin; 
SELECT cms_admin.* FROM cms_admin;

查询表中特定内容
查询管理员编号和名称

SELECT id,username FROM cms_admin;
SELECT username,id,role FROM cms_admin;

表来自于哪个数据库下db_name.tbl_name（库名.表名）

SELECT id,username,role FROM cms.cms_admin;

字段来自于哪张表（表名.字段名）

SELECT cms_admin.id,cms_admin.username FROM cms.cms_admin;

为表、字段️起别名

给表名起别名

SELECT id,username FROM cms_admin AS a;
# AS可省略
SELECT id,username FROM cms_admin a;

SELECT a.id,a.username,a.email,a.role FROM cms_admin AS a;

给字段起别名

SELECT id AS '编号',username AS '用户名',email AS '邮箱',role '角色' FROM cms_admin;

结果：
mysql> SELECT id AS '编号',username AS '用户名',email AS '邮箱',role ' 角色' FROM cms_admin;
+------+--------+--------------+------------+
| 编号     | 用户名      | 邮箱             | 角色          |
+------+--------+--------------+------------+
|    1 | admin  | admin@qq.com | 超级管理员          |
|    2 | king   | admin@qq.com | 普通管理员         |
|    3 | 麦子       | admin@qq.com | 普通管理员         |
|    4 | queen  | admin@qq.com | 普通管理员         |
|    5 | test   | admin@qq.com | 普通管理员         |
+------+--------+--------------+------------+

SELECT a.id AS i,a.username AS u,a.email as e,a.role AS r FROM cms_admin AS a; 

结果：
mysql> SELECT a.id AS i,a.username AS u,a.email as e,a.role AS r FROM cms_admin AS a;
+---+-------+--------------+------------+
| i | u     | e            | r          |
+