目录

01. 数据操作_查询条件

02. 测试WHERE 条件的比较运算符

正文

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 9SELECT 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<=30SELECT 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或者用户名为queenSELECT id,username,age FROM user1 WHERE id=1 OR username='queen';

写在最后

本文是在学习MySQL过程中整理的相关笔记，是该系列的第七篇文章，以上内容纯手敲，可能有手误的地方，也可能有错误的地方，希望大家一起指正和见谅。最后，希望与每一个努力的人同行，一起加油！！！