数据操作和单表查询
数据操作：
插入数据INSERT：
1. 插入完整数据（顺序插入）
语法一：
INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);
语法二：
INSERT INTO 表名 VALUES (值1,值2,值3…值n);
2. 指定字段插入数据
语法：
INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);
3. 插入多条记录
语法：
INSERT INTO 表名 VALUES
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);
4. 插入查询结果
语法：
INSERT INTO 表名(字段1,字段2,字段3…字段n) SELECT (字段1,字段2,字段3…字段n) FROM 表2 WHERE …;
查询式拷贝数据;
create table 表名(字段1 字段类型,字段2 字段类型,字段3 字段类型…字段n 字段类型) SELECT (字段1,字段2,字段3…字段n) FROM 表2;
如果新表的字段名和被拷贝的字段名不一样的话，就会创建新表的字段名和被拷贝的字段名
只有一样的时候才能只拷贝被开背的字段名
create table 表名(新字段1 字段类型,新字段2 字段类型,新字段3 字段类型…新字段n 字段类型)
SELECT (字段1 as 新字段名,字段2 as 新字段名,字段3 as 新字段名…字段n as 新字段名) FROM 表2;
这样去创建的时候就会改成新的字段名，as可以省略
更新数据UPDATE：
语法：
UPDATE 表名 SET 字段1=值1,字段2=值2,WHERE CONDITION;
删除数据DELETE
语法：
DELETE FROM 表名 WHERE CONITION;
\G -- 把记录中的字段按列展开
简单操作查询拷贝表 create table t1(id int,name char); insert into t1 values(1,'alex'),(2,'egon'),(3,'yh'); create table t2(name char) select(name) from t1; 结果： mysql> create table t1(id int,name char); Query OK, 0 rows affected (0.25 sec) mysql> insert into t1 values(1,'alex'),(2,'egon'),(3,'yh'); Query OK, 3 rows affected, 3 warnings (0.11 sec) Records: 3 Duplicates: 0 Warnings: 3 mysql> create table t2(name char) select(name) from t1; Query OK, 3 rows affected (0.24 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> desc t2; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | name | char(1) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ row in set (0.16 sec) 查出所有员工的名字，薪资,格式为 <名字:egon> <薪资:3000> select concat('<名字：',name,'> ','<薪资：',salary,'>') from employee; 结果： mysql> select concat('<名字：',name,'> ','<薪资：',salary,'>') from employee; +-----------------------------------------------------------+ | concat('<名字：',name,'> ','<薪资：',salary,'>') | +-----------------------------------------------------------+ | <名字：egon> <薪资：7300.33> | | <名字：alex> <薪资：1000000.31> | | <名字：wupeiqi> <薪资：8300.00> | | <名字：yuanhao> <薪资：3500.00> | | <名字：liwenzhou> <薪资：2100.00> | | <名字：jingliyang> <薪资：9000.00> | | <名字：jinxin> <薪资：30000.00> | | <名字：成龙> <薪资：10000.00> | | <名字：歪歪> <薪资：3000.13> | | <名字：丫丫> <薪资：2000.35> | | <名字：丁丁> <薪资：1000.37> | | <名字：星星> <薪资：3000.29> | | <名字：格格> <薪资：4000.33> | | <名字：张野> <薪资：10000.13> | | <名字：程咬金> <薪资：20000.00> | | <名字：程咬银> <薪资：19000.00> | | <名字：程咬铜> <薪资：18000.00> | | <名字：程咬铁> <薪资：17000.00> | +-----------------------------------------------------------+ rows in set (0.00 sec) 查出所有的岗位（去掉重复） select distinct post from employee; 结果： mysql> select distinct post from employee; +-----------------------------------------+ | post | +-----------------------------------------+ | 老男孩驻沙河办事处外交大使 | | teacher | | sale | | operation | +-----------------------------------------+ rows in set (0.00 sec) 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year select name,salary*12 as annual from employee; 结果： mysql> select name,salary*12 as annual from employee; +------------+-------------+ | name | annual | +------------+-------------+ | egon | 87603.96 | | alex | 12000003.72 | | wupeiqi | 99600.00 | | yuanhao | 42000.00 | | liwenzhou | 25200.00 | | jingliyang | 108000.00 | | jinxin | 360000.00 | | 成龙 | 120000.00 | | 歪歪 | 36001.56 | | 丫丫 | 24004.20 | | 丁丁 | 12004.44 | | 星星 | 36003.48 | | 格格 | 48003.96 | | 张野 | 120001.56 | | 程咬金 | 240000.00 | | 程咬银 | 228000.00 | | 程咬铜 | 216000.00 | | 程咬铁 | 204000.00 | +------------+-------------+ rows in set (0.00 sec) 数据操作
单表查询
select distinct 字段1,字段2,字段3,...from 表名
where 约束条件
group by 分组的字段
having 过滤条件 ---分组之后才过滤
order by 排序字段
limit 限制条件;
from 找表--where--group by---having---distinct---order by---limit
以上顺序是固定的，优先级从上到下，除了distinct 去重
employee 员工id id int 姓名 emp_name varchar 性别 sex enum 年龄 age int 入职日期 hire_date date 岗位 post varchar 职位描述 post_comment varchar 薪水 salary double 办公室 office int 部门编号 depart_id int #创建表 create table employee( id int not null unique auto_increment, name varchar(20) not null, sex enum('male','female') not null default 'male', #大部分是男的 age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int, #一个部门一个屋子 depart_id int ); #插入记录 #三个部门：教学，销售，运营 insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values ('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部 ('alex','male',78,'20150302','teacher',1000000.31,401,1), ('wupeiqi','male',81,'20130305','teacher',8300,401,1), ('yuanhao','male',73,'20140701','teacher',3500,401,1), ('liwenzhou','male',28,'20121101','teacher',2100,401,1), ('jingliyang','female',18,'20110211','teacher',9000,401,1), ('jinxin','male',18,'19000301','teacher',30000,401,1), ('成龙','male',48,'20101111','teacher',10000,401,1), ('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门 ('丫丫','female',38,'20101101','sale',2000.35,402,2), ('丁丁','female',18,'20110312','sale',1000.37,402,2), ('星星','female',18,'20160513','sale',3000.29,402,2), ('格格','female',28,'20170127','sale',4000.33,402,2), ('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门 ('程咬金','male',18,'19970312','operation',20000,403,3), ('程咬银','female',18,'20130311','operation',19000,403,3), ('程咬铜','male',18,'20150411','operation',18000,403,3), ('程咬铁','female',18,'20140512','operation',17000,403,3) ; #ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk 结果： #查看表结构 mysql> desc employee; +--------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | sex | enum('male','female') | NO | | male | | | age | int(3) unsigned | NO | | 28 | | | hire_date | date | NO | | NULL | | | post | varchar(50) | YES | | NULL | | | post_comment | varchar(100) | YES | | NULL | | | salary | double(15,2) | YES | | NULL | | | office | int(11) | YES | | NULL | | | depart_id | int(11) | YES | | NULL | | +--------------+-----------------------+------+-----+---------+----------------+ mysql> select * from employee; +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ rows in set (0.00 sec) 创建表
where字句中可以使用：
1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在80到1000之间,包含80,100
3. in(80,90,100) 值是80或90或100
4. like 'egon%'
like后面可以跟 %表示任意多字符 _表示一个字符
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
6.关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
1. 查看岗位是teacher的员工姓名、年龄 select name,age from employee where post='teacher'; 结果： mysql> select name,age from employee where post='teacher'; +------------+-----+ | name | age | +------------+-----+ | alex | 78 | | wupeiqi | 81 | | yuanhao | 73 | | liwenzhou | 28 | | jingliyang | 18 | | jinxin | 18 | | 成龙 | 48 | +------------+-----+ rows in set (0.00 sec) 2. 查看岗位是teacher且年龄大于30岁的员工姓名、 select name,age from employee where post='teacher' and age>30; 结果： mysql> select name,age from employee where post='teacher' and age>30; +---------+-----+ | name | age | +---------+-----+ | alex | 78 | | wupeiqi | 81 | | yuanhao | 73 | | 成龙 | 48 | +---------+-----+ rows in set (0.02 sec) 3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资 select name,age,salary from employee where post='teacher' and salary between 9000 and 10000; 结果： mysql> select name,age,salary from employee where post='teacher' and salary between 9000 and 10000; +------------+-----+----------+ | name | age | salary | +------------+-----+----------+ | jingliyang | 18 | 9000.00 | | 成龙 | 48 | 10000.00 | +------------+-----+----------+ rows in set (0.02 sec) 4. 查看岗位描述不为NULL的员工信息 select * from employee where not post_comment is Null; 结果： mysql> select * from employee where not post_comment is Null; Empty set (0.00 sec) 5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资 select name,age,salary from employee where post='teacher' and salary in(10000,9000,30000); 结果： mysql> select name,age,salary from employee where post='teacher' and salary in(10000,9000,30000); +------------+-----+----------+ | name | age | salary | +------------+-----+----------+ | jingliyang | 18 | 9000.00 | | jinxin | 18 | 30000.00 | | 成龙 | 48 | 10000.00 | +------------+-----+----------+ rows in set (0.00 sec) 6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资 select name,age,salary from employee where post='teacher' and salary not in(10000,9000,30000); 结果： mysql> select name,age,salary from employee where post='teacher' and salary not in(10000,9000,30000); +-----------+-----+------------+ | name | age | salary | +-----------+-----+------------+ | alex | 78 | 1000000.31 | | wupeiqi | 81 | 8300.00 | | yuanhao | 73 | 3500.00 | | liwenzhou | 28 | 2100.00 | +-----------+-----+------------+ rows in set (0.00 sec) 7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪 select name,salary from employee where post='teacher' and name like'jin%'; 结果： mysql> select name,salary from employee where post='teacher' and name like'jin%'; +------------+----------+ | name | salary | +------------+----------+ | jingliyang | 9000.00 | | jinxin | 30000.00 | +------------+----------+ rows in set (0.02 sec) where
分组查询: GROUP BY
1、分组发生在where之后，即分组是基于where之后得到的记录而进行的
2、分组：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等
3、分组是为了更好的管理数据
4、可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数
强调：
如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据
分组之后只能查看分组的字段，
如果查组内其他的字段，不能直接查到，需要借助聚合函数，max min avg sum count
count(字段) --查看该字段有多少个，一般统计唯一的字段
group_concat(字段) -- 查到该字段所有的内容
分组的目的是为了以组为单位来处理记录，而不是单独处理记录
设置语句---禁止无意义的查询
ONLY_FULL_GROUP_BY
select @@global.sql_mode; @ 全局的意思
select global sql_mode='on_full_group_by'; @ 全局的意思
1. 查询岗位名以及岗位包含的所有员工名字 select post,group_concat(name) from employee group by post; 结果; mysql> select post,group_concat(name) from employee group by post; +-----------------------------------------+---------------------------------------------------------+ | post | group_concat(name) | +-----------------------------------------+---------------------------------------------------------+ | operation | 程咬铁,程咬铜,程咬银,程咬金,张野 | | sale | 格格,星星,丁丁,丫丫,歪歪 | | teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex | | 老男孩驻沙河办事处外交大使 | egon | +-----------------------------------------+---------------------------------------------------------+ rows in set (0.02 sec) 2. 询岗位名以及各岗位内包含的员工个数 select post,count(id) from employee group by post; 结果： mysql> select post,count(id) from employee group by post; +-----------------------------------------+-----------+ | post | count(id) | +-----------------------------------------+-----------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 老男孩驻沙河办事处外交大使 | 1 | +-----------------------------------------+-----------+ rows in set (0.00 sec) 3. 查询公司内男员工和女员工的个数 select sex,count(id) from employee group by sex; 结果： mysql> select sex,count(id) from employee group by sex; +--------+-----------+ | sex | count(id) | +--------+-----------+ | male | 10 | | female | 8 | +--------+-----------+ rows in set (0.00 sec) 4. 查询岗位名以及各岗位的平均薪资 select post,avg(salary) from employee group by post; 结果： mysql> select post,avg(salary) from employee group by post; +-----------------------------------------+---------------+ | post | avg(salary) | +-----------------------------------------+---------------+ | operation | 16800.026000 | | sale | 2600.294000 | | teacher | 151842.901429 | | 老男孩驻沙河办事处外交大使 | 7300.330000 | +-----------------------------------------+---------------+ rows in set (0.00 sec) 5. 查询岗位名以及各岗位的最高薪资 select post,max(salary) from employee group by post; 结果： mysql> select post,max(salary) from employee group by post; +-----------------------------------------+-------------+ | post | max(salary) | +-----------------------------------------+-------------+ | operation | 20000.00 | | sale | 4000.33 | | teacher | 1000000.31 | | 老男孩驻沙河办事处外交大使 | 7300.33 | +-----------------------------------------+-------------+ rows in set (0.00 sec) 6. 查询岗位名以及各岗位的最低薪资 select post,min(salary) from employee group by post; 结果： mysql> select post,min(salary) from employee group by post; +-----------------------------------------+-------------+ | post | min(salary) | +-----------------------------------------+-------------+ | operation | 10000.13 | | sale | 1000.37 | | teacher | 2100.00 | | 老男孩驻沙河办事处外交大使 | 7300.33 | +-----------------------------------------+-------------+ rows in set (0.00 sec) 7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资 select sex,avg(salary) from employee group by sex; 结果： mysql> select sex,avg(salary) from employee group by sex; +--------+---------------+ | sex | avg(salary) | +--------+---------------+ | male | 110920.077000 | | female | 7250.183750 | +--------+---------------+ rows in set (0.00 sec) group by
HAVING过滤
执行优先级从高到低：where > group by > having
1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
2. Having发生在分组group by之后，因而Having中可以使用分组的字段和聚合函数，无法直接取到其他字段
having 只能用在group by之后，直接使用报错
distinct --在字段之前加，表示去掉重复的
在having之后执行
1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数 select post,group_concat(name),count(id) from employee group by post having count(id)<2; 结果： mysql> select post,group_concat(name),count(id) from employee group by post having count(id)<2; +-----------------------------------------+--------------------+-----------+ | post | group_concat(name) | count(id) | +-----------------------------------------+--------------------+-----------+ | 老男孩驻沙河办事处外交大使 | egon | 1 | +-----------------------------------------+--------------------+-----------+ row in set (0.00 sec) 2. 查询各岗位平均薪资大于10000的岗位名、平均工资 select post,avg(salary) from employee group by post having avg(salary)>10000; 结果： mysql> select post,avg(salary) from employee group by post having avg(salary)>10000; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | operation | 16800.026000 | | teacher | 151842.901429 | +-----------+---------------+ 3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资 select post,avg(salary) from employee group by post having avg(salary)>10000 and avg(salary)<20000; 结果： mysql> select post,avg(salary) from employee group by post having avg(salary)>10000 and avg(salary)<20000; +-----------+--------------+ | post | avg(salary) | +-----------+--------------+ | operation | 16800.026000 | +-----------+--------------+ row in set (0.00 sec) having
查询排序:ORDER BY---一定是在表结构出来之后才执行的
按单列排序
SELECT * FROM employee ORDER BY salary;
SELECT * FROM employee ORDER BY salary ASC;
SELECT * FROM employee ORDER BY salary DESC;
asc 升序--默认升序
dasc 降序
按多列排序:先按照age排序，如果年纪相同，则按照薪资降序排
SELECT * from employee ORDER BY age,salary DESC;
1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序 select * from employee order by age,hire_date desc; 结果： mysql> select * from employee order by age,hire_date desc; +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ rows in set (0.00 sec) 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列 select post,avg(salary) as s from employee group by post having avg(salary)>10000 order by s; 结果： mysql> select post,avg(salary) as s from employee group by post having avg(salary)>10000 order by s; +-----------+---------------+ | post | s | +-----------+---------------+ | operation | 16800.026000 | | teacher | 151842.901429 | +-----------+---------------+ rows in set (0.01 sec) 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列 select post,avg(salary) as x from employee group by post having avg(salary)>10000 order by x desc; 结果：mysql> select post,avg(salary) as x from employee group by post having avg(salary)>10000 order by x desc; +-----------+---------------+ | post | x | +-----------+---------------+ | teacher | 151842.901429 | | operation | 16800.026000 | +-----------+---------------+ rows in set (0.00 sec) order by
限制查询的记录数:LIMIT
limit 个数 -- 只能查到几条记录
limit 1,5 ---从1开始往后取5条
select * from employee limit 5; 结果： mysql> select * from employee limit 5; +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ rows in set (0.00 sec) select * from employee limit 2,3; 结果： mysql> select * from employee limit 2,3; +----+-----------+------+-----+------------+---------+--------------+---------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+------+-----+------------+---------+--------------+---------+--------+-----------+ | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | +----+-----------+------+-----+------------+---------+--------------+---------+--------+-----------+ rows in set (0.00 sec) limit
使用正则表达式查询
正则表达式就是匹配字符串内容的一种规则（工具：regex）
字符集---[多种可能]--表示一个字符的位置--# 多种可能可以是范围--必须是从小到大
元字符--占一个字符所表示的内容
重点，常用
. 匹配除了换行符以外的任意字符
\w 匹配字母数字或下划线
\s 匹配任意的空白符
\d 匹配数字
beautiful|b 匹配字符a或字符b
()匹配括号内的表达式，也表示一个组
[...] 匹配字符组中的字符
[^...]在字符集里表示匹配除了字符组中的所有字符
比较常用
\n 匹配换行符
^ 匹配字符串的开始
$ 匹配字符串的结尾
不太常用
\t 制表符
\b 匹配单词的结尾
\W 匹配非字母数字或下划线
\D 匹配非数字
\S 匹配非空白符
量词--只表示前面一个字符的次数
* 重复零次或更多次
+ 重复一次或更多次
？重复零次或一次
{n} 重复n次
{n,} 重复n次或更多次
{n,m} 重复n到m次
规则：贪婪匹配（字符+量词）：
每个量词只控制前面一个字符的出现次数
这个量词的匹配方式就是贪婪匹配（尽量多的匹配，能匹配多少匹配多少）
非贪婪匹配： 字符+量词+？
常用： .*? + 任意字符，匹配到任意字符就返回
分组：约束多个字符集匹配次数
SELECT * FROM employee WHERE name REGEXP '^ale';
SELECT * FROM employee WHERE name REGEXP 'on$';
SELECT * FROM employee WHERE name REGEXP 'm{2}';
小结：对字符串匹配的方式
WHERE name = 'egon';
WHERE name LIKE 'yua%';
WHERE name REGEXP 'on$';
select * from employee where name regexp '^a'; 结果： mysql> select * from employee where name regexp '^a'; +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ row in set (0.01 sec) select * from employee where name regexp 'zhou$'; 结果： mysql> select * from employee where name regexp 'zhou$'; +----+-----------+------+-----+------------+---------+--------------+---------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+------+-----+------------+---------+--------------+---------+--------+-----------+ | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | +----+-----------+------+-----+------------+---------+--------------+---------+--------+-----------+ row in set (0.00 sec) 查看所有员工中名字是jin开头，n或者g结果的员工信息 select * from employee where name regexp '^jin.*[ng]$'; 结果： mysql> select * from employee where name regexp '^jin.*[ng]$'; +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ rows in set (0.00 sec) 使用正则表达式查询
