[大数据]MySQL-数据查询操作

查询记录

查询模板:

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

示例:执行查询某个数据库下的表

--指定查询cms库中admin表的id,username字段
SELECT id,username FROM cms.admin

指定查询某个表下的字段：

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

给表起别名

--把cms_admin命名为ad
SELECT id,username FROM cms_admin as ad;

给字段起别名

SELECT id as 'i',username as 'ua' FROM cms_admin;

更新数据

语句用法:

UPDATE tbl_name SET 字段名称 = 值，... [WHERE 条件] [ORDER BY 字段名称] [LIMIT限制条数];

删除数据

语句用法:

DELETE FROM  tbl_name [WHERE 条件] [ORDER BY 字段名称] [LIMIT 限制条数]

示例:

--按照id降序排列，更新前3条，
UPDATE cms_user SET age = age + 10 ORDER BY id DESC LIMIT 3;

--删除用户名性别为男的用户，按照年龄降序排列，删除前一条记录
DELETE FROM cms_user WHERE sex = '男' ORDER BY age DESC LIMIT 1;