更新
insert into goods(id,goods,name,price) values (107,'葡萄'，10) on duplicate key update goods name='葡萄'，price =10;
替换
replace insert into goods(id,goods,name,price) values (107,'葡萄'，10);
数据更新
update tb1_name set col_name=expr1,[,,] [condition] [limit]
如果没有where子句，则更新所有行
update goods set price=300; //更改数据库所有价格信息
update goods set price=1000 where id=100; //更改id=100的价格信息
update goods set price=price+200 where id=101; //将id=101 的价格增加200
update goods set price=550 limit 3; //修改前三个
select * from goods; //查看
删除
delete from tb1_name [where condition]
delete from goods where id=101; //删除id=101 的行
create table goods2 like goods;
desc goods;
insert into goods2 select * from goods; //把goods内容复制到goods2
delete from goods; //表中内容全删
insert into goods select * from goods2; //把goods2内容复制到goods
truncate goods; //删除表
drop goods; //删除表
delete truncate 区别
效果一样，trucate速度快
delete返回被删除的记录数，而truncate返回0
清空表数据建议使用trucate
delete
不会删掉某一列的值，可以用update置null
6.5 简单查询
6.5.1 select id,name,math from student;
select * from student; //可以指定查询哪些字段
select math from student;
select distinct math from student; //去掉重复行
select id,name,math+2 from student; //所有数学+2 但是对数据库数据不会有影响，+的只是查询出来的结果，不会有影响数据
select id,name,(chinese+english+math)*1.6 from student where name like '唐%'; //所有姓唐的学生成绩+60%
6.5.2
where子句
比较运算符： > < <= >= <>(不等于) !=
between and //显示在某一区间的值
in() //显示在in列表中的值 例：in(100,200)
like //模糊查询
not like //模糊查询
is null //判断是否为空
逻辑运算符
and
or
not //条件不成立
select * from student where name like '李%'； //查询 李 的信息
select id,name,english from student where english >90; //查询英语成绩大于90 的同学
select id,name ,(chinese+english+math) from student;
select id,name,(chinese+english+math)as total from student; //总分大于200
select * from student where name like '李%' and id >10 ; //查询 姓李 id > 10 的成绩
select * from student where english > chinese ; //查询英语成绩大于语文的
select id,name,english from student where english between 80 and 90; //这里是闭区间 >=80 <=90
6.5.3 order by 子句
select id,name,math from student order by math; //默认升序
select id,name,math from student order by math desc; //降序
select id,name,（chinese+english+math）as total from student; //总分
select id,name,（chinese+english+math）as total from student order by total(可以使用别名) desc; //总分降序
select id,name,（chinese+english+math）as total from student where name like '李%'order by total(可以使用别名) desc;
//对姓李的学生成绩进行排序
select count(*) from student; //一个班有多少学生
select count(*) from student where math>90;
select count(*) from student where (chinese+english+math)>250; //总分 > 250
//新建表
create table tt2(name varchar(32));
insert into tt2 values ('a'),('b'),(null);
select count(*) from tt2; == select count(1) from tt2;(1 不会扫描全表)
select count(name) from tt2;
6.6.2 sum
select sum(math) from student; //统计班级数学总分
select sum(chinese),sum(english),sum(math) from student;
select sum(chinese+english+math) from student; //统计班级总分
select sum(chinese)/count(*) from student; //统计班级平均分 sum 仅对数值有意义
select avg(chinese) from student; //算出语文平均值
select avg(chinese+english+math) from student; //班级平均分
6.6.4 max/min
select max(math),min(math) from student; //数学最高 最低分
6.4 group by
source D:\\scott_data.sql; //导入表
select empno,sal from emp; //员工编号，与工资
select avg(sal),max(sal) from emp; //所有员工最高工资，及平均工资
select deptno,avg(sal),max(sal) from emp group by deptno; //部门的最高工资，及平均工资
select deptno,job,avg(sal),min(sal) from emp group by deptno job;//显示每个部门，每种岗位的平均工资
select deptno,avg(sal) from emp group by deptno; //部门平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal) < 2000;//where 对原始数据进行过滤删选， having 对查询结果进行过滤
7.函数
7.1 常见日期函数
select current_timestamp();
时间戳的计数开始时间：1970：1：1：0：0：1
select data_add('2018-08-26',interval 1 day); //加一天
select data_add(current_date(),interval 2 day); //加两天
select data_sub('2018-08-26',interval 1 day); //当前天数减一天
select data_sub('2018-08-26',interval -1 day); //加一天
select datediff('2018-09-01',current_date()); //离指定日期还有多少天
select datediff(current_date,'2018-09-01'); //这个是负值，前面的减去后面的
select now();
insert into tmp (birthday) values (current_date())
insert into tmp (birthday) values ('2018-09-01')
create table msg (id int primary key auto_increment,content varchar(32),sendtime datetime);
select id,content,date(sendtime) from msg; //只保留日期，不保留时间
select id,content,sendtime from msg where data_add(sendtime,interval 2 minute) >= now(); //两分钟内发布的时间
7.2 字符串函数
use scott;
select empno,charset(ename) from emp; //获取ename字符集
select charset('中文'); //获取字符集
select concat(name,'的语文是',chinexe,'分，数学'，math,'分，英语'，english,'分') as ''成绩通知单' from student;
select length(name),name from student; //学生表中姓名的长度，字节长度
select id,length(id),name length(name) from student;
select repleace(name,) //将所有的名字有s的替换为上海
select ename,substring(ename,2,2) //取ename第二个到第三个字符，mysql下标从1开始
select ename,concat(lcase(substring(ename,1,1)), substring(ename,2))//以首字母小写的方式显示所有员工的姓名
7.3 数学函数
select abs(-1); //取绝对值
select ceiling(23.34) //向上取整
select floor(23.34) //向下取整
select format(23.345,2); //取两位小数
select bin(10),hex(16),conv(10,10,8); //转换二进制，十进制，八进制
7.4 查询当前用户
select usert(); //查看链接用户
slect database(); //查看当前在哪个数据库
md5(str) 对一个字符串进行md5摘要
select md5('admin'); //将字符串进行md5处理
select password('root');
ifnull(val1,val2) //如果val1为null，则返回val2
//若都为null,返回第二个null
8.多表查询
select ename,sal,job from emp where (sal>500 or job='MANAGER') and ename like 'J%';
and substring(ename,1,1)='J';
select ename,deptno sal from emp order by deptno asc,sal desc;
select ename,sal*12+ ifnull(comm,0) as year_sal from emp order by year_sal desc;//使用年薪进行排序
select ename,sal,job from
where sal=(select max(sal) from emp); //员工薪水最高
//每个部门的平均工资和最高工资
select deptno,format(avg(sal),2) (保留两位小数),max(sal) from emp group by deptno;
//显示平均工资低于2000 的部门号和它的平均工资
//数值比较时不要format
select deptno,avg(sal) as avg_sal from emp group by deptno having avg_sal < 2000;
//显示每种岗位的雇员总数
select job,count(ename) ,avg(sal) from emp group by job;
8.2 多表查询
//笛卡尔集
//a表的记录和b表所有的记录做和， a*b
select ename,dname from emp,dept;
select ename,dname from emp ,dept where emp.deptno=dept.deptno;
select emp.deptno,dename,ename,sal from emp,dept where emp.deptno=10 and emp.deptno=dept.deptno;
select * from salgrade;
//查询等级范围
select emp.ename,emp.sal,salgrade.grade
from emp,salgrade
where
emp.sal between salgrade.losal and salgrade.hisal;
8.3 自连接
//上级领导编号和姓名
select empno,ename from emp
where
empno=
(select mar from emp where ename ='FORD');
emp worker
emp leader
//多表查询中的自查询
select leader.empno,leader.ename
from emp as leader,emp as worker
where
leader.empno=worker.mgr and worker.ename='FORD'
//显示SMITH同一部门的员工
select ename,deptno from emp
where(
select deptno from emp where ename='SMITH');
//多条
select ename,job,sal,deptno from emp
where deptno in(
select distinct job from emp where deptno=10) and deptno!=10;
select ename,sal,deptno from emp
where sal >
all(select sal from emp where deptno=30);
select ename,sal,deptno from emp
where sal >
any(select any)
select empno,ename,deptno,job from emp
where (deptno,job)=
(select deptno,job from emp where ename='SMITH')
and ename!='SMITH';
select empno,ename,sal,emp.deptno from emp,
(select deptno,avg(sal) as avg_sal from emp group by deptno) as avg_sal_table
where emp.sal > avg_sal_table.avg.sal
and emp.deptno =avg_sal_table.deptno;
select empno,ename,sal,deptno from emp,
(select deptno,max(sal) as max_sal from emp group by deptno) as dm
where emp.sal = dm.max_sal and emp.deptno=dm.deptno;
//每个部门信息和人员数量
select dept.deptno,dept.name,dep
select demtno,count(ename) from emp group by deptno;
//笛卡尔集属于内连接
select dept.dename,emp.epno,emp.ename from emp,dept
from emp inner join dept
on emp.deptno=dept.deptno;
select stu.id,name,grade from stu left join exam on stu.id=exam.id
扫一扫
2047
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
