时间：2021-07-01 10:21:17 帮助过：31人阅读
<!doctype html>zuoye
1.数据操作
1.1 增:insert
1.2 删:delete
1.3 改:update
1.4 编码问题
2. 单表查询
2.1 基础查询
2.2 拼接concat
2.3 where语句
2.4 聚合函数
2.5 分组 group by
2.6 过滤 having
2.7 排序 order by
2.8 limit的使用
insert into 表名 values(1,‘alex‘,23,‘female‘,2.2,‘linux,python‘),
(1,‘alex‘,23,‘female‘,2.2,‘linux,python‘);
insert into 表名(id,name) values(5,‘wusir‘),(6,‘wusir‘);
?
insert into emp2 select * from emp; 将emp的数据导入到emp2里
?
insert into emp2(id,name) select id,name from emp;将emp的id和name导入到emp2里
delete from 表名 where 条件 删除一行数据
update 表 set 字段1=值1,字段2=值2 where 条件;
?
\c取消当前操作,
‘>‘ \c 取消当前操作
1.临时解决问题 在客户端执行 set xxxx = utf8;
2.永久解决问题 在my.ini添加 set xxxx=utf8;
3.实时解决问题 创建表的时候 create table 表名() charset=utf8;
select:(并不能直接在表中进行修改,只是在临时修改后查询到)
select * from 表名;
指定列查询
select name,age from 表名;
在列中进行四则运算(查找姓名和薪水*12打印出来的表)
select name,salary*12 from employee;
重命名(并不能真正修改,只是修改后查询出来)
select name,salary as annul_salary from employee;
select name,salary annul_salary from employee;
去重
select distinct post from 表名;
select distinct sex,post from 表名;
函数concat()拼接
select concat(‘要拼接的‘,name),concat(‘要拼接的‘,age)from 表名;
select concat_ws(‘|‘,‘a‘,‘b‘,‘c‘);
case when语句 ==if 条件判断句
select语句筛选列
where语句筛选行
?
where语句:根据条件筛选行
可用范围符号进行筛选:= > < >= <= != <>(不等于)
select * from employee where age>18;
范围: between a and b 在什么什么之间
select * from employee where age between 10 and 20 只找age里面10~20之间的
精准范围: in
select * from employee where age in (10,20);只找age里等于10和20的
模糊查询 like
通配符 _(表示一个字符长度的任意内容)
select * from employee where name like ‘jin_‘一个_代表一个字符
通配符 % 表示任意字符的任意内容
select * from employee where name like ‘jin%‘ name里面以jin开头的
select * from employee where name like ‘%g‘ name里面以g结尾的
select * from employee where name like ‘%g%‘ name里面带g的数值
正则匹配 regexp
select * from employee where emp_name regexp ‘^jin‘
逻辑运算
and
select * from employee where age>18 and post=‘teacher‘;
查询employee表里面age大于18的而且post是teacher的
or
select * from employee where salary20000;
查询employee表里面salary小于1000的或者是salary大于20000的
not
select * from employee where salary not in(1000,2000);不是1000和2000的
查询employee表里面salary里不是1000和2000的
?
关于null:
查看岗位描述为NULL的员工信息
select * from employee where comment(指岗位描述) is null;
查看岗位描述不为NULL的员工信息
select * from employee where comment(指岗位描述) is not null;
?
聚合函数:
count:计数看看有多少行(非空的)
select count(salary) from employee;
max:最大值
min:最小值
avg:平均值
select avg(salary) from employee;
sum:相加
select sum(salary) from employee;
分组
select * from 表 where 条件 group by 分组;
分组聚合
select post,max(salary) from employee group by post;
?
select group_connect(emp_nae) from employee group by post;
分组
select * from 表 where 条件 group by 分组;
分组聚合
select post,max(salary) from employee group by post;
?
select group_connect(emp_nae) from employee group by post;
过滤 hving(group by + 聚合函数)
查询平均年龄大于15的人员
select name,avg(age) from employee group by post having avg(age)>15;
查询各岗位内包含的员工个数小于2的岗位名,岗位名包含员工姓名,个数
select post,name,count(id) from employee group b post having count(id)<2;
查询各岗位平均薪资大于10000的岗位名,平均薪资
select post,avg(salary) from employee group by post having avg(salary)>10000;
3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;
排序:
order by
升序
select * from employee order by salary;
select * from employee order by salary asc;
降序
select * from employee order by salary desc;
?
select * from employee order by age,salary;
select * from employee order by age,salary desc;
select * from employee order by age desc,salary;
limit:
select * from 表 order by 列 limit n; 取前n条
select * from 表 order by 列 limit m,n; 从m开始,取n条
select * from 表 order by 列 limit n offset m; 从m+1开始,取n条
select * from 表 where 条件 group by 分组 having 过滤 order by 排序 limit n;
回到顶部
数据库基础操作
标签：平均值 拼接 均值 delete group by limit 最大 基础 create