时间戳

首页 > 技术文章 > 数据库学习笔记之MySQL(07)

数据库学习笔记之MySQL(07)

chenfengbiji 2021-04-01 08:07 原文

01. 数据操作_查询条件
// 格式
SELECT select_expr,... FROM tbl_name [WHERE 条件]
[GROUP BY {col_name|position}HAVING 二次筛选]
[ORDER BY {col_name|position|expr} [ASC|DESC]]
[LIMIT 限制结果集的显示条数]
  • 查询表中所有记录
SELECT * FROM user1;
  • 查询表中username,addr,age
SELECT username,addr,age FROM user1;
  • 查询数据库king下user1表中所有记录
SELECT * FROM king.user1;
  • 查询user1表中的id 编号 username 用户名 sex性别
//AS是别名 关键字
SELECT id AS '编号',username AS '用户名',sex AS '性别' FROM user1;
  • 给表起别名
SELECT id,username FROM user1 AS u;

// 利用别名查询字段名
SELECT u.id,u.username,u.addr,u.sex FROM user1 AS u;
02. 测试WHERE 条件的比较运算符
  • 查询id,username,age id=5的用户
// 查询id,username,age id=5的用户
SELECT id,username,age FROM user1 WHERE id=5;
SELECT id,username,age FROM user1 WHERE id=50;
  • 更新id<=9的用户 userDESC='this is a test'
UPDATE user1 SET userDesc='this is a test' WHERE id<=9;
  • 查询用户userDesc 为NULL的用户
SELECT id,username,age,userDesc FROM user1 WHERE userDesc=NULL;
  • 检测NULL值
SELECT id,username,age,userDesc FROM user1 WHERE userDesc<=>NULL;

// IS [NOT] NULL检测NULL值
SELECT id,username,age,userDesc FROM user1 WHERE userDesc IS NULL;
  • 测试范围BETWEEN AND
// 查询年龄在18~30之间的用户
SELECT id,username,age,sex FROM user1 WHERE age EBTWEEN 18 AND 30;

// 查询薪水在10000~50000之间的用户
SELECT id,username,age,salary FROM user1 WHERE salary NOT BETWEEN 10000 AND 50000;
  • 查询指定集合 IN
//查询编号为1 3 5 7 9
SELECT id,username,age FROM user1 WHERE id IN(1,3,5,7,9);
//查询名字
SELECT id,username,age FROM user1 WHERE id IN('king','queen','lily','rose');
  • 测试逻辑运算符
// 查询性别为男并且年龄>=20的用户
SELECT id,username,age,sex FROM user1 WHERE sex='男' AND age>=20;

// id>=5 && age<=30
SELECT id,username,age,sex FROM user1 WHERE id>=5 AND age<=30;
SELECT id,username,age,sex FROM user1 WHERE id>=5 AND age<=30 AND sex='男';

//要求sex='女' 并且 addr='北京'
SELECT id,username,age,sex,addr FROM user1 WHERE sex='女' AND addr='北京';

//查询薪水60000~100000并且性别为男 addr='北京'
SELECT id,username,age,sex,salary,addr FROM user1 WHERE salary BETWEEN 60000 AND 100000 AND sex='男' AND addr='北京';

//id为1或者用户名为queen
SELECT id,username,age FROM user1 WHERE id=1 OR username='queen';

推荐阅读