MySQL之查询

数据脚本：

CREATE TABLE sys_user(  
    fami_id INT(5),  
    id INT(11),  
    user_name VARCHAR(18),  
    age INT(3),  
    birth_day DATE,  
    address VARCHAR(30)
);

INSERT INTO sys_user(fami_id,id,user_name,age,address) VALUES(1,1,'陈三',25,'深圳市宝安区');  
INSERT INTO sys_user(fami_id,id,user_name,address) VALUES(1,2,'陈大','深圳市福田区');  
INSERT INTO sys_user(fami_id,id,user_name,age,address) VALUES(2,3,'陈二',27,'深圳市龙华新区');  
INSERT INTO sys_user VALUES(2,4,'陈四',23,'1994-02-23','深圳市龙华新区');  
INSERT INTO sys_user VALUES(3,5,'张三',22,'1995-10-11','南京市');  
INSERT INTO sys_user VALUES(4,6,'李三',21,'1996-5-13','南京市');  
INSERT INTO sys_user VALUES(4,7,'李1',22,'1995-3-12','江西省南昌市');  
INSERT INTO sys_user VALUES(4,8,'李2',20,'1997-5-25','江西省九江市');  
INSERT INTO sys_user VALUES(4,9,'李3',21,'1996-5-15','江西省上饶市');  
INSERT INTO sys_user VALUES(8,10,'陈胜',23,'1994-3-15','');

1.mysql查询：

1) 比较运算（>  >=  !<  <  <=  !> =  !=  <>）：

SELECT * FROM sys_user WHERE user_name !='陈三';
SELECT * FROM sys_user WHERE user_name <>'张三';
SELECT * FROM sys_user WHERE user_name='李三';
SELECT * FROM sys_user WHERE address='';
SELECT * FROM sys_user WHERE birth_day IS NULL;
SELECT * FROM sys_user WHERE birth_day = '1994-02-23';
SELECT * FROM sys_user WHERE birth_day != '1994-02-23';
SELECT * FROM sys_user WHERE birth_day >= '1994-02-23';

2) 逻辑比较（AND  OR  NOT）：

SELECT * FROM sys_user WHERE fami_id>3 AND id>7;
SELECT * FROM sys_user WHERE fami_id>3 OR id>7;
SELECT * FROM sys_user WHERE id IN(1,3,5,7);
SELECT * FROM sys_user WHERE id NOT IN(1,3,5,7);
SELECT * FROM sys_user WHERE id BETWEEN 3 AND 9;
SELECT * FROM sys_user WHERE id NOT BETWEEN 3 AND 9;

3) 模糊查询：

    %：表示任意0个或多个字符。可匹配任意类型和长度的字符

    _： 表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句

SELECT * FROM sys_user WHERE address LIKE '%市%'
SELECT * FROM sys_user WHERE address LIKE '%市'
SELECT * FROM sys_user WHERE user_name LIKE '_三';

4) MySQL正则和类似正则的用法这里先写这么多，后面会单独用一篇介绍：

1 [ ] ：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个；

SELECT * FROM sys_user WHERE user_name LIKE '[张李陈]三';-- 按说是可以查出user_name等于张三、李三、陈三的记录，实际不报错，亦无记录；

2 [^ ] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符；

SELECT * FROM sys_user WHERE user_name LIKE '[^陈]三';-- 按说user_name以三结尾的前部一个字符匹配的有陈三、李三、张三，[^陈]三匹配的结果为李三、张三，实际不报错，亦无记录；

5) 五大聚合函数