MySQL之查询语句

1.查询表达式

-- 查询
SELECT * FROM cms_admin;

-- 字段来自于表cms_admin
SELECT cms_admin.* FROM cms_admin;

-- 查询管理员的编号和名称
SELECT id,username 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;

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

-- 给字段起别名
SELECT id AS '编号',username AS '用户名',email AS '邮箱',role AS '角色' FROM cms_admin;

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

2.待条件的查询

-- WHERE条件
-- 查询编号为1的用户
-- = , <= , >= , != , > , < 
SELECT id,username,email FROM cms_user WHERE id=1;

SELECT id,username,email FROM cms_user WHERE username='king';

-- 查询编号为1的用户
SELECT * FROM cms_user WHERE id!=1;

SELECT * FROM cms_user WHERE id<>1;

-- 添加age字段
ALTER TABLE cms_user ADD age TINYINT UNSIGNED DEFAULT 18;

INSERT cms_user(username,password,regTime,proId) VALUES('test1','test1',1419811708,1,NILL);

-- 查询表中记录age值为NULL
SELECT * FROM cms_user WHERE age=NULL;  -- = 不能检测NULL

SELECT * FROM cms_user WHERE age<=>NULL;   -- <=> 也是等号，比 = 多了检测NULL的功能

-- IS NULL  或者 IS NOT NULL
SELECT * FROM cms_user WHERE age IS NULL;

3.范围查询

-- 查询编号在3~10的用户
SELECT * FROM cms_user WHERE id BETWEEN 3 AND 10;

-- 查询编号为1,3,5,7,9,11,13,100
SELECT * FROM cms_user WHERE id IN(1,3,5,7,9,11,13,100);

-- 查询proId为1和3 的用户
SELECT * FROM cms_user WHERE proId IN(1,3);

-- 查询用户名为king,queen,张三，章子怡的记录 
SELECT * FROM cms_user WHERE username IN('king','queen','张三','章子怡');

4.模糊查询

-- 模糊查询
-- %: 代表0个一个或者多个字符
-- _: 代表任意字符 
-- 查询姓张的用户 
SELECT * FROM cms_user WHERE username LIKE '张%';

-- 查询用户名中包含in的用户 
SELECT * FROM cms_user WHERE username LIKE '%in%';

-- 查询用户名为3位的记录 
SELECT * FROM cms_user WHERE username LIKE '___';

-- 用户名_i%
select * from cms_user where username like '_i%';

5.逻辑运算符

-- 查询用户名为king 并且密码为king的用户 
select * from cms_user where username='king' and password='king';

-- 查询编号大于等于3的变量年龄不为null的用户
select * from cms_user where id>=3 and age is not null;

-- 查询编号大于等于3的变量年龄不为null的用户并且proId为3
select * from cms_user where id>=3 and age is not null and proId=3;

-- 查询编号在5~10的用户且用户名为4位的用户 
select * from cms_user where id between 5 and 10 and username like '____';

-- 查询用户名以张开头或者用户所在身份为2,4的记录 
select * from cms_user where username like '张%' or proId in(2,4);