change 旧列名 新列名 [first | after 列名]
可以更改列类型和列名称，如果原列的名字和新列的名字相同
modify 列名 [first | after 列名]
和change的作用相同
drop 列名 // 可以删除一列
drop primary key // 可以删除主键
rename as 新表名 // 可以将表名更改
举例：
举例1：
向people表中添加字段address2, 类型为varchar, 最大长度为100
alter table people add address2 varchar(100);
举例2:
将 people 表 中的 name 列 默认值改为 100:
alter table people alter name set default 100;
举例3:
向 student 表 增加 “入学时间” 列，其数据类型为日期型。
alter table student add scome date；
注：无论基本表中原来是否已有数据，新增加的列一律为空值。
举例4:
将年龄的数据类型 改为 半字长整数。
alter table student modify sage smallint；
注：修改原有的列定义有可能会破坏已有数据
distinct: 排重
from子句： 指定查询数据的表
where子句： 查询数据的过滤条件
group by子句： 对匹配where子句的查询结果进行分组
having子句： 对分组后的结果进行条件限制
order by子句： 对查询结果进行排序，后面跟desc降序或asc升序（默认）。
limit子句： 对查询的显示结果限制数目
procedure子句： 查询存储过程返回的结果集数据
MySQL数据库的知识点
举例1 : 查询全体学生的学号与姓名。
select sno,sname from student;
举例2 : 查询全体学生的详细记录。
select sno,sname,ssex,sage,sdept from student;
或 select * from student;
举例3 : 查全体学生的姓名及其出生年份。
select sname,2014-sage from student；
举例4 : 查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。
select sname,'year of birth: ',2008-sage, lower(sdept) from student;
举例5 : 查询选修了课程的学生学号(去掉重复的记录)
select distinct studentid from sc;
举例6 : 查询全体学生的学号与姓名，用中文显示列名。
select sno as ‘编号’,sname as ‘姓名’ from student;
举例7 : 给表设置别名。
select s.sno,s.sname from student as s;
举例8 : 查询年龄在20以下的学生的姓名。
select sname from student where sage<20；
举例9 : 查询全体学生的姓名、年龄，要求按照年龄降序排序。
select sname,sage from student order by sage desc;
举例10 : 查询年龄最大的前3个学生的姓名和年龄，或第4、5个学生
select sname,sage from student order by sage desc limit 3;或（limit 3,2）
举例11 : 查询学生总数。
select count(*) from student;
举例12 : 查询选修了课程的学生人数。
select count(distinct studentid) from sc;
举例13 : 查询1号课程的学生平均成绩。
select avg(grade) from sc where courseid=1;
举例14 : 查询1号课程的学生最高分和最低分。
select max(grade) as ‘最高分’,min(grade) as ‘最低分’ from sc where courseid=1;
举例15 : 查询每个学生的平均成绩。
select studentid,avg(grade) as ‘平均成绩’ from sc group by studentid;
举例16 : 查询学生的平均成绩在70分以上的。
select studentid,avg(grade) as ‘平均成绩’ from sc group by studentid having avg(grade)>70;
在WHERE子句中使用谓词 :
BETWEEN AND : 在两数之间
NOT BETWEEN AND ： 不在两数之间
IN <值表> ： 是否在特定的集合里（枚举）
NOT IN <值表> ： 与上面相反
LIKE ： 是否匹配于一个模式
IS NULL（为空的）或 IS NOT NULL（不为空的）REGEXP : 检查一个值是否匹配一个常规表达式。
举例17 : 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄 。
select sname,sdept,sage from student where sage between 20 and 23;
举例18 : 查询年龄不在20~23岁之间的学生姓名、系别和年龄。
select sname,sdept,sage from student where sage not between 20 and 23;
举例19 : 查询'信息系'、'美术系'和'计算机系'学生的姓名和性别。
select sname,ssex from student where sdept in (‘信息系',‘美术系',‘计算机系');
举例20 : 查询学号为95001的学生的详细情况。
select * from student where sno like '95001'；
等价于：select * from student where sno='95001';
举例21 : 查询所有姓刘学生的姓名、学号和性别。
select sname,sno,ssex from student where sname like ‘刘%';
举例22 : 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。
select studentid,courseid from sc where grade is null;
举例23 : 查所有有成绩的学生学号和课程号。
select studentid,courseid from sc where grade is not null;
举例24 : 查询计算机系年龄在20岁以下的学生姓名。
select sname from student where sdept=‘计算机系' and sage<20;
举例25 : 查询信息系、美术系和计算机系学生的姓名和性别
select sname,ssex from student where sdept in (‘信息系','美术','计算机系');
可改写为：
Select sname,ssex from student where sdept='信息系' or sdept='美术' or sdept='计算机系';
举例26 : 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。
select studentid,grade from sc where courseid=3 orber by grade desc;
举例27 : 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。
select * from student order by sdept,sage desc;
select size,color_id,count(size) from bbs_sku where price>0 GROUP BY size,color_id having size in (‘S’,‘M’,‘L’) ORDER BY color_id desc LIMIT 5,5
–
导入导出
多表联查
sql优化
1.选择部门30中的雇员
select * from emp where deptno=30;
2.列出所有经理的姓名,编号和部门.
select ename,empno,dname from emp,dept where job='MANAGER' and emp.deptno=dept.deptno; # MANAGER是字符串，加引号
3.列出佣金高于薪金的雇员.
select * from emp where comm>sal;
4.找出佣金高于或等于薪金50%的雇员
select * from emp where comm>=sal*0.5;
5.找出部门10中所有经理和部门20中所有办事员的详细信息
select * from emp where (job='MANAGER' and deptno=10) or (job='CLERK' and deptno=20);
6.找出部门10中所有经理和部门20中所有 办事员 以及既不是 经理又不是办事员但薪金大于或等于2000的所有雇员的详细资料.
select * from emp where (job='MANAGER' and deptno=10) or (job='CLERK' and deptno=20) or (job not in('MANAGER','CLERK') and (sal>=2000 and deptno=20);
7.找出收取佣金的雇员的不同工作.
select distinct job from emp where comm>0;
select job from emp where comm is not null and comm != 0; # : 收取佣金：comm is not null
8.找出不收取佣金或收取的佣金低于是200的雇员
select * from emp where comm<200 or comm is null; # 不收取佣金：comm is null
9.找出各月最后一天受雇的所有雇员.
select * from emp where hiredate = last_day(hiredate);
？？？10.找出早于25年之前受雇的所有雇员.
？？？select * from emp where add_months(hiredate,25*12)<sysdate; # add_months是增加月份的函数，若小于sysdate，就是25年前
11.查询只有首字母大写的所有雇员姓名.
select * from emp where ename = initcap(ename);
12.查询所有雇员的信息,按照姓名排序.
select * from emp order by ename;
13.查询所有雇员的信息,按薪金正序排序.
select * from emp order by sal ;
14.查询所有经理,并按薪金正序 和 雇员姓名正序排序.
select * from emp where job='MANAGER' order by sal ,ename;
15.查询姓名是MILLER的雇员.
16.查询薪金1500和3000,或者佣金高于500的雇员.
select * from emp where sal in(1500,3000) or comm>500;
17.查询每个工种的人数.
select job,count(job) from emp group by job;
18.查询每个工种的人数.并按 工种的 人数排序.
select job,count(job) a from emp group by job order by a;
19.查询MANAGER的平均工资。
select round(avg(sal),2) from emp where job='MANAGER'; # 2 —— 保留两位小数
20.查询每个工种的人数,和平均工资,并按平均工资进行排序.
select count(job),avg(sal) a from emp group by job order by a;
21.查询部门 名称是SALES 的所有员工.
select * from emp ,(select * from dept where dname='SALES') a where emp.deptno=a.deptno;
22.查询部门名称是RESERCH的所有员工的平均工资.
select avg(sal) from emp ,(select * from dept where dname='REARCH') a where emp.deptno=a.deptno;
23.查询每个工种中雇员的最高薪金;
select job, max(sal) from emp group by job;
24.查询职务是经理的雇员的最低薪金;
select job, min(sal) from emp where job = 'MANAGER';
25.新增两条记录
empno为 57,ENAME是ANDY,JOB是MANAGER,SAL是3300,HIREDATE是'12-8月-90',DEPTNO是20;
empno为 58,ENAME是JACK,JOB是MANAGER,SAL是3000,HIREDATE是'12-9月-90',DEPTNO是20;
insert into emp values
(57 , 'ANDY' , 'MANAGER' , null , '1990-08-12' , 3300 , null , 20),
(58 , 'JACK' , 'MANAGER' , null , '1990-09-12' , 3000 , null , 20)
修改EMPNO为 57的记录,将薪金涨20%,DEPTNO改成30;
update emp set sal = sal*1.2 , deptno=30 where empno = 57
修改ENAME是JACK的记录,将HIREDATE改成当前日期
update emp set hiredate = sysdate where ename='JACK';
删除EMPNO为58记录.
delete from emp where empno = 58;
26.查询平均工资高于1500的所有职务的名称和平均工资金额.
select job,avg(sal) from emp group by job having avg(sal)>1500
27.查询每个员工的详细信息.包括部门编号,部门名称及部门所在城市.
select emp.*,dname,loc from emp,dept where emp.deptno=dept.deptno;
28.按部门分组,查询出部门名称,及部门中员工的平均工资.
# 两表联查
select a.*,dname from
(select round(avg(sal),2),deptno from emp group by deptno) a,dept where a.deptno=dept.deptno;
29.查询所有员工中工资数在前五的员工详细信息。并从高到低进行排序。
select * from emp order by sal desc LIMIT 0,5
30.查询所有经理中工资第三高的员工的详细信息。
select * from emp where job='MANAGER' order by sal desc LIMIT 2,1
31.找出所有员工中薪金 第五高 到 第八高 的员工信息。
select * from emp order by sal desc LIMIT 4,4;
32.查询出SCOTT的上级领导的详细信息。
select * from emp where empno in (select MGR from emp where ename='SCOTT');
33.查询出没有员工的部门。
select * from dept where deptno not =(select distinct deptno from emp);
34．查询出平均高于2000的部门信息。
select deptno, avg(sal) from emp group by deptno having avg(sal)>2000
35．向EMP表中插入一条记录
查询出所的员工的 员工编号，员工姓名，工资，职位，部门名称（包含未分配部门的员工，例如后插入的SCOTT）
select * from emp e LEFT JOIN dept d on e.deptno=d.deptno
36．找到同名的所有员工
select ename,count(ename) from emp group by ename having count(ename)>1
37.找出所有同名的员工后，通过一条语句只保留同名员工中的一个，其余的则删除掉。
select * from emp where
ename in (select ename from emp group by ename having count(ename)>1)
1.选择部门30中的雇员
select * from emp where deptno=30;
2.列出所有经理的姓名,编号和部门.
select ename,empno,deptno from emp where job='MANAGER'
3.列出佣金高于薪金的雇员.
select * from emp where comm>sal;
4.找出佣金高于或等于薪金50%的雇员
select * from emp where comm>=sal*0.5;
5.找出部门10中所有经理和部门20中所有办事员的详细信息
select * from emp where (job='MANAGER' and deptno=10) or (job='CLERK' and deptno=20);
6.找出部门10中所有经理和部门20中所有办事员以及即不是经理又不是办事员但薪金
大于或等于2000的所有雇员的详细资料.
select * from emp
where (job='MANAGER' and deptno=10) or (job='CLERK' and deptno=20) or (job not in('MANAGER','CLERK') and sal>=2000 and deptno=20);
7.找出收取佣金的雇员的不同工作.
select distinct job from emp where comm>0;
select job from emp where comm is not null and comm != 0 group by job
8.找出不收取佣金或收取的佣金低于是200的雇员
select * from emp where comm<200 or comm>0;
select * from emp where comm between 0 and 200
9.找出各月最后一天受雇的所有雇员.
select * from emp where hiredate = last_day(hiredate);
10.找出早于25年之前受雇的所有雇员.
SELECT * from emp where SUBDATE(hiredate,SYSDATE())>25
11.查询只有首字母大写的所有雇员姓名.
12.查询所有雇员的信息,按照姓名排序.
select * from emp order by ename;
13.查询所有雇员的信息,按薪金正序排序.
select * from emp order by sal ;
14.查询所有经理,并按薪金正序和雇员姓名正序排序.
select * from emp where job='MANAGER' order by sal ,ename;
15.查询姓名是MILLER的雇员.
select * from emp where ename='MILLER'
16.查询薪金1500和3000,或者佣金高于500的雇员.
select * from emp where sal in(1500,3000) or comm>500;
17.查询每个工种的人数.
select job,count(job) from emp group by job ;
18.查询每个工种的人数.并按工种的人数排序.
select job,count(job) a from emp group by job order by a;
19.查询MANAGER的平均工资。
select round(avg(sal),2) from emp where job='MANAGER';
20.查询每个工种的人数,和平均工资,并按平均工资进行排序.
select count(job),avg(sal) a from emp group by job order by a;
21.查询部门名称是SALES的所有员工.
select * from emp where DEPTNO =
(SELECT deptno from dept where dname='SALES')
22.查询部门名称是RESERCH的所有员工的平均工资.
select avg(sal) from emp where DEPTNO =
(SELECT deptno from dept where dname='RESERCH')
23.查询每个工种中雇员的最高薪金;
select job, max(sal) from emp group by job;
24.查询职务是经理的雇员的最低薪金;
select job, min(sal) from emp where job='MANAGER';
25.新增两条记录
empno为57,ENAME是ANDY,JOB是MANAGER,SAL是3300,HIREDATE是'12-8月-90',DEPTNO是20;
empno为58,ENAME是JACK,JOB是MANAGER,SAL是3000,HIREDATE是'12-9月-90',DEPTNO是20;
insert into emp values(57,'ANDY','MANAGER',null,1990-08-12,3300,null,20),(58,'JACK','MANAGER',null,1990-09-12,3000,null,20)
修改EMPNO为57的记录,将薪金涨20%,DEPTNO改成30;
update emp set sal = sal*1.2 ,deptno=30 where
empno = 57
修改ENAME是JACK的记录,将HIREDATE改成当前日期
update emp set hiredate = sysdate where ename=‘JACK’;
删除EMPNO为58记录.
delete from emp where empno=58
26.查询平均工资高于1500的所有职务的名称和平均工资金额.
select job,avg(sal) from emp group by job having avg(sal)>1500
27.查询每个员工的详细信息.包括部门编号,部门名称及部门所在城市.
select emp.*,dname,loc from emp,dept where emp.deptno=dept.deptno;
28.按部门分组,查询出部门名称,及部门中员工的平均工资.
select e.deptno,e.a,d.DNAME from (SELECT deptno,avg(sal) a from emp GROUP BY deptno) e LEFT JOIN dept d on e.deptno=d.DEPTNO
29.查询所有员工中工资数在前五的员工详细信息。并从高到低进行排序。
select * from emp ORDER BY sal desc LIMIT 0,5
30.查询所有经理中工资第三高的员工的详细信息。
select * from emp where job='MANAGER' ORDER BY sal desc LIMIT 2,1
31.找出所有员工中薪金第五高到第八高的员工信息。
select * from emp ORDER BY sal desc LIMIT 4,4
32.查询出SCOTT的上级领导的详细信息。
select * from emp where empno=(SELECT mgr from emp where ename='SCOTT')
33.查询出没有员工的部门。
select * from emp e RIGHT JOIN dept d on e.DEPTNO=d.DEPTNO where e.EMPNO is null GROUP BY d.DEPTNO
34．查询出平均高于2000的部门信息。
select deptno,avg(sal) from emp GROUP BY deptno having avg(sal)>2000
35．向EMP表中插入一条记录
insert into emp(empno,ename,sal,job,hiredate) values(7999,'SCOTT',2000,'MANAGER',sysdate());
查询出所的员工的 员工编号，
员工姓名，工资，职位，部门名称（包含未分配部门的员工，例如后插入的SCOTT）
SELECT * from emp e LEFT JOIN dept d on e.deptno=d.deptno
36．找到同名的所有员工
SELECT ename,count(ename) from emp group by ename having count(ename)>1
37.找出所有同名的员工后，通过一条语句只保留同名员工中的一个，其余的则删除掉。
select * from emp where ename in (SELECT ename from emp group by ename having count(ename)>1)
FOR
select * from emp where ename in (SELECT ename from emp group by ename having count(ename)>1);
DELETE from emp where empno in
(SELECT empno from emp LIMIT 0,1);
*/
SELECT * from emp where
datediff(SYSDATE(),hiredate)>25*365
