数据库-记录操作
记录操作
#增 insert into 表名 values(),(),(); #删 1.delete from 表名 where 条件; 2.delete from 表名 清空表;#如果设置成主键自增,则会接着增加 truncate from 表名;即可 #改 update 表名 set 字段=新的值 where 条件;
单表查询:
#单表查询语法 select distinct(去重) 字段名 from 表名 where 条件 group by分组 having 筛选 order by排序 limit 限制条数 #单表查询关键字顺序: from#先找到表 where#根据条件取出记录 group by#将取出的数据进行group by select#执行select去重 distinct having#分组的结果在筛选 order by#再将结果进行排序 limit#限制条数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
create table employee( id int primary key auto_increment, emp_name char(12) not null, sex enum('male','female') not null default 'male', #大部分是男的 age int(3) unsigned not null default 28, hire_date date not null, post char(15), post_comment varchar(100), salary float(15,2), office int, #一个部门一个屋子 depart_id int ); insert into employee(emp_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) ;
简单查询
#简单查询: #避免重复:关键字distinct select distinct 字段 from 表名; #通过四则运算:(+ - * / 一般是数字) select salary*12 from employee; #定义显示格式: 字符串拼接:concat() select concat('<姓名:',emp_name,'>'),concat('<年薪',salary*12,'>')as annual_year from employee; concat_ws():分割符 select concat_ws(':',emp_name,salary) from employee; +--------------------------------+ | concat_ws(':',emp_name,salary) | +--------------------------------+ | egon:7300.33 | | alex:1000000.31 | #结合case语句: select( case when emp_name='jingliyang' then emp_name else concat(emp_name,'sb') end )as new_name from employee
where 约束
where约束: 1.单条件查询: 2.多条件查询: 需要用到逻辑运算符:not and or 3.关键字between and: mysql> select emp_name,salary from employee where salary between 10000 and 20000; 4.关键字is null:判断某个字段是否为null 5.关键字in: 判断某个字段括号内的值是否存在
不存在:not in mysql> select emp_name,salary from employee where salary in(1000,10000,9000); 6.模糊匹配: 通配符: %: 表示匹配任意多个字符 _: 表示匹配任意一个字符 关键字用like mysql> select * from employee where emp_name like 'jin%'; 匹配emp_name以'jin'开头的所有符合条件的数据;
group by
group by 1.按照字段将其分组: 2.group by 一般与 group_concat(字段)聚合函数合用 mysql> select group_concat(emp_name)as name from employee group by post; +---------------------------------------------------------+ | name | +---------------------------------------------------------+ | 程咬铁,程咬铜,程咬银,程咬金,张野 | | 格格,星星,丁丁,丫丫,歪歪 | | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex | | egon | +---------------------------------------------------------+ 聚合函数: count(*)#分组下的总个数,不能查全是null的 max()#最大值 min()#最小值 avg() sum()
having
若分组后还要继续筛选,用having
order by :
#order: 默认升序 asc mysql> select salary from employee order by salary; 降序 后面加 desc mysql> select salary from employee order by salary desc;
#按多列排序:先按照age排序,如果年纪相同,则按照薪资排序 SELECT * from employee ORDER BY age, salary DESC;
limit限制查询记录条数:
#limit限制查询的记录数,排序在最后 1.limit m;表示取表中的前m个记录 2.limit m,n;表示从m+1取n条记录 3.limit n offset m;从m+1取n条记录
多表查询
1.交叉连接
2.内连接
3.外连接
4.子查询
1.交叉连接:生成笛卡尔表 2.内链接:只连接两个表共有的数据 select 字段1|字段2 from 表1 inner join 表2 on 条件; 3.外连接: 1.左外连接: select 字段 from 表1 left join 表2 on 条件; #表1全部显示,表2没有匹配的数据显示None 2.右外连接: 3.全外连接: select * from 表1 left join 表2 on 条件 union select * from 表1 right join 表2 on 条件; 4.子查询: #子查询的效率比连表查询的效率低(每次子查询的表得出的结果,主select都会去在select 一次) 1.in:在不在 2.带比较运算符的子查询 select t1.name,t1.age from employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age; 3.exists:表示存在 exists()里面返回的是TRUE或者False,如果是True在继续select