1.安装mysql数据库
2.创建一个mydb1数据库,并查看。
createdatabase mydb1;show mydb1;showcreatedatabase mydb1;3.创建一个mydb2数据库,指定字符编码为gb2312;createdatabase mydb2 chracter set gb2312 collate gb2312_bin;4.创建一个mydb3数据库,指定字符编码为utf8,并且指定校验规则;createdatabase mydb3 character utf8 collate utf8_bin;update练习
droptableifexists employee;createtableifnotexists employee
(
id int,
name varchar(20),
sex varchar(10),
birthday date,
salary float,
resume text);insertinto employee values(1,'zhangsan','male','1980-11-25',2000,'good body');insertinto employee values(2,"lisi","male","1980-04-25",1000,"good body");insertinto employee values(3,"xiaohong","female","1978-11-25",4000,"good girl");
将所有员工薪水修改为5000元。
--alter table employee salary "alter这个关键字仅仅是用来修改表的结构的,对表中的内容无法修改,修改表中的内容要用update"--update table employee set salary = 5000; update 是修改表中的数据,就不用在其后加上talbe关键字了.update employee set salary =5000;
将姓名为’zhangsan’的员工薪水修改为3000元。
update employee set salary =3000where name ='zhangsan';
将姓名为’lisi’的员工薪水修改为4000元,sex改为female。
--update employee set salary = 4000 and sex = female where name = 'lisi' ; 有多个列要修改的时候不用and连接列,用逗号.!!!update employee set salary =4000,sex ='female'where name ='lisi';
将xiaohong的薪水在原有基础上增加1000元。
update employee set salary = salary +1000where name ='xiaohong';select 练习
createtable student(
id int,
name varchar(20),
chinese float,
english float,
math float);insertinto student(id,name,chinese,english,math)values(1,'张小明',89,78,90);insertinto student(id,name,chinese,english,math)values(2,'李进',67,53,95);insertinto student(id,name,chinese,english,math)values(3,'王五',87,78,77);insertinto student(id,name,chinese,english,math)values(4,'李一',88,98,92);insertinto student(id,name,chinese,english,math)values(5,'李来财',82,84,67);insertinto student(id,name,chinese,english,math)values(6,'张进宝',55,85,45);insertinto student(id,name,chinese,english,math)values(7,'黄蓉',75,65,30);
查询表中所有学生的信息。
select*from student;
查询表中所有学生的姓名和对应的英语成绩。;select name,english from student ;
过滤表中重复数据。
selectdistinct*from student;
统计每个学生的总分。
--select sum(chinese+english+math) as Total from student; 这个是所有学生的总分, 貌似sum()函数是对以整列求和,对一个记录内的求和是要单独加select name ,(chinese+english+math)as Total from student;select name ,chinese+english+math as Total from student;--这两个都对.
在所有学生总分数上加10分特长分。
--select name , chinese+english+math+10 as Total from student;是所有学生分数上,并不是学生总成绩上.select name , chinese+10,english+10,math+10as Total from student;
使用别名表示学生分数。
select name,chinese ch ,english en,math ma from student;--这个地方别名 加不加as都对.
查询姓名为李一的学生成绩
select name,chinese, math,english from student where name ='李一';
查询英语成绩大于90分的同学
select name from student where english >90;
查询总分大于200分的所有同学
select name from student where english+chinese+math>200;
查询英语分数在 80-90之间的同学。
--select name from student where english>=80 & english<=90; &这个符号不能代表and!!!!select name from student where english>=80and english<=90;select name from student where english between80and90;
查询数学分数为89,90,91的同学。
select name from student where math in(89,90,91);--select name from student where math=89 and math=90 and math=91;这个地方用and就不正确.检索不到东西.可以用关键字ORselect name from student where math=89or math=90or math=91;
查询所有姓李的学生英语成绩。
select name,english from student where name like"李%";
查询数学分>80并且语文分>80的同学。
select name from student where math>80and chinese >80;
查询英语>80或者总分>200的同学
select name from student where english >80OR english+math+chinese>200;
对数学成绩排序后输出。
select*from student orderby math ;--默认是升序排列.select*from student orderby math desc;select*from student orderby math asc;
对总分排序后输出,然后再按从高到低的顺序输出
select*from student orderby chinese+math+english desc;
对姓李的学生成绩排序输出
select*from student where name like"李%"orderby chinese+math+english desc;
练习二
1、查找部门30中员工的详细信息。
--select * from emp where deptno=30;select*from emp where deptno=30;2、找出从事clerk工作的员工的编号、姓名、部门号。
-- select empno,ename,deptno from emp where job = 'clerk';select empno,ename,deptno from emp where job='clerk';3、检索出奖金多于基本工资的员工信息。
--select * from emp where comm>sal;select*from emp where comm >sal
4、检索出奖金多于基本工资60%的员工信息。
--select * from emp where comm>sal*0.6;select*from emp where comm>sal*0.6;5、找出10部门的经理(job=Manger)、20部门的职员(job=clerk) 的员工信息。
--select * from dept,emp where dept.deptno=10 and emp.deptno=20;--select * from emp where deptno=10 and job = 'manager' and deptno=20 and job='clerk';--这个地方找出10号部门的经理20部门的职员的员工信息.中间用OR连接.别用AND,用and是找不到这么个条件的值的.select*from emp where deptno=10and job='MANAGER'or deptno=20and job='CLERK'6、找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。
--select * from emp where deptno=10 and job='MANAGER' or deptno=20 and job='CLERK' or 最后这个条件不会写,原来用not 外加inselect*from emp where(deptno=10and job='MANAGER')or( deptno=20and job='CLERK')or( job notin('MANAGER','CLERK')and sal>2000)7、找出获得奖金的员工的工作。
--select * from emp where comm not null; 用is not null.select*from emp where comm isnotnull.select*from emp where comm isnotnull;8、找出奖金少于100或者没有获得奖金的员工的信息。
select*from emp where comm<100or comm isnull;select*from emp where comm isnullor comm<100;9、找出姓名以A、B、S开始的员工信息。
--select * from emp where ename like "A%","B%","S%";要写多个like语句.select*from emp where ename like'A%'OR ENAME LIKE'B%'OR ENAME LIKE'S%';10、找到名字长度为7个字符的员工信息。
--select * from emp where ename.length= 7;--报错:ERROR 1054 : Unknown column 'ename.length' in 'where clause'--用到了length()函数,还有就是弄like中引入对应数字的空格' '.select*from emp where length(ename)=7;select*from emp where ename like'______';11、名字中不包含R字符的员工信息。
--利用 not like '%R%'select*from emp where ename notlike'%R%';12、返回员工的详细信息并按姓名排序。
select*from emp orderby ename;13、返回员工的信息并按工作降序工资升序排列。
select*from emp orderby job desc,sal asc;14、计算员工的日薪(按30天)。
select sal/30from emp
15、找出姓名中包含A的员工信息。
select*from emp where ename like'%A%';
练习三
//多表与分组练习1、返回拥有员工的部门名、部门号。
--不知道这个具体干什么的.通过看部门dept表和emp表可以看出来,员工并不是都是在dept表中存在的地方的.--dept表中可能有四个部门号,但是员工emp表中可能只占其中的三个部门号码--题干的需求就是干这个的.(即有的部门可能没有"员工")select dname,deptno from dept where deptno in(select deptno from emp);+------------+--------+| dname | deptno |+------------+--------+| ACCOUNTING |10|| RESEARCH |20|| SALES |30|+------------+--------+2、工资水平多于smith的员工信息。
select*from emp where sal>(select sal from emp where ename ='smith');3、返回员工和所属经理的姓名。
--这个感觉很简单就是没有想出来怎么办,看答案才理解了--复用表emp,再加上使用外连接.--select ee.ename as eename ,mm.ename as mename from emp ee left join emp mm where ee.mgr=mm.empno; --看完答案理解了之后也写错了,注意外连接是left/right join onselect ee.ename as eename ,mm.ename as mename from emp ee leftjoin emp mm on ee.mgr=mm.empno;--现在左外连接还有另外的一种写法,就是用加号"+";仔细查了一下,并且纳闷为什么+的SQL语句外连接在navicate中没有效果.--得知加号的外连接只有在oracle中才是用.+--------+--------+| eename | mename |+--------+--------+| SMITH | FORD || ALLEN | BLAKE || WARD | BLAKE || JONES | KING || MARTIN | BLAKE || BLAKE | KING || CLARK | KING || SCOTT | JONES || KING |NULL|| TURNER | BLAKE || ADAMS | SCOTT || JAMES | BLAKE || FORD | JONES || MILLER | CLARK |4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。
--感觉这个也是要复用emp表,确实复用emp表--select ee.ename as eename,mm.ename as mename from emp ee,emp mm where ee.hiredate<mm.hiredate;--这句话没有加上是其经理的条件--select ee.ename as eename,mm.ename as mename from emp ee left join emp mm on ee.mgr = mm.empno and ee.hiredate < mm.hiredate;--上面这句仅仅是把第三句中加上了日期小于的条件.select ee.ename as eename,mm.ename as mename from emp ee ,emp mm where ee.mgr = mm.empno and ee.hiredate < mm.hiredate;--这句为什么不用left join? 第三句用where可不可以? 注意时间老的日期大小是小于时间老的日期的.(select ee.ename as eename ,mm.ename as mename from emp ee leftjoin emp mm where ee.mgr=mm.empno;)第三句的写法.5、返回员工姓名及其所在的部门名称。
--这句有点像第三句,不过这个牵扯到了两个表--select ee.ename as ename, ee.deptno as dptname from emp ee, dept dd where ee.deptno=dd.deptno;--最开始写的,写错了.还是没有把最基本的需求高清,人家要部门名.select ename as ename ,dname from emp ee,dept dd where ee.deptno=dd.deptno;+--------+------------+| ename | dname |+--------+------------+| CLARK | ACCOUNTING || KING | ACCOUNTING || MILLER | ACCOUNTING || SMITH | RESEARCH || JONES | RESEARCH || SCOTT | RESEARCH || ADAMS | RESEARCH || FORD | RESEARCH || ALLEN | SALES || WARD | SALES || MARTIN | SALES || BLAKE | SALES || TURNER | SALES || JAMES | SALES |+--------+------------+6、返回从事clerk工作的员工姓名和所在部门名称。
select ename ,dname from emp ee, dept dd where ee.job='clerk'and ee.deptno = dd.deptno;+--------+------------+| ename | dname |+--------+------------+| MILLER | ACCOUNTING || SMITH | RESEARCH || ADAMS | RESEARCH || JAMES | SALES |+--------+------------+7、返回部门号及其本部门的最低工资。
--这个语句不用where条件进行过滤.--找出部门的最低工资 ----想不起来.提示一下用到了min()函数.--select min(sal) from emp ;--select deptno , min(sal) from deptno ,emp --首先想需求中要用到哪几个表.然后再写.--因为是按照部门为单位的所以用到分组select deptno ,min(sal)from emp groupby deptno;+--------+----------+| deptno |min(sal)|+--------+----------+|10|1300||20|800||30|950|+--------+----------+8、返回销售部(sales)所有员工的姓名。
--根据需求用到了emp和dept两个表select ename from emp ee,dept dd where dd.dname='sales'and ee.deptno =(select deptno from dept where dname ='sales');+--------+| ename |+--------+| ALLEN || WARD || MARTIN || BLAKE || TURNER || JAMES |+--------+9、返回工资水平多于平均工资的员工。
--这个用到了avg()函数. 用到表emp--select * from emp where sal > avg(sal);--一开始这样写的,但是报错,提示信息为:ERROR 1111 : Invalid use of group function--在这种情况下要把聚合函数放在select语句中select*from emp where sal >(selectavg(sal)from emp);+-------+-------+-----------+------+------------+------+------+--------+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |+-------+-------+-----------+------+------------+------+------+--------+|7566| JONES | MANAGER |7839|1981-04-02|2975|NULL|20||7698| BLAKE | MANAGER |7839|1981-05-01|2850|NULL|30||7782| CLARK | MANAGER |7839|1981-06-09|2450|NULL|10||7788| SCOTT | ANALYST |7566|1987-07-03|3000|NULL|20||7839| KING | PRESIDENT |NULL|1981-11-17|5000|NULL|10||7902| FORD | ANALYST |7566|1981-12-03|3000|NULL|20|+-------+-------+-----------+------+------------+------+------+--------+10、返回与SCOTT从事相同工作的员工。
--只用到表emp--select * from emp where job = '(select job from emp where ename = 'scott')';--上一句在语法上是没有问题的,但是有个小问题就是job是varchar字符串类型的,但是在其后是select语句-- 的时候其后是不能加上单引号,或者是单引号的,否则都会报错的.--还要把搜索到的结果去掉scott他本身这条记录.select*from emp where job =(select job from emp where ename ='scott')and ename<>'scott';11、返回与30部门员工工资水平相同的员工姓名与工资。
--这个也只用到了emp表select ename ,sal from emp where sal in(select sal from emp where deptno =30);+--------+------+| ename | sal |+--------+------+| ALLEN |1600|| WARD |1250|| MARTIN |1250|| BLAKE |2850|| TURNER |1500|| JAMES |950|+--------+------+12、返回工资高于30部门所有员工工资水平的员工信息。
--从题意中就可以看出要使用到关键字all 也只用到了emp表--all关键字怎么使用.select*from emp where sal>all(select sal from emp where deptno =30);+-------+-------+-----------+------+------------+------+------+--------+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |+-------+-------+-----------+------+------------+------+------+--------+|7566| JONES | MANAGER |7839|1981-04-02|2975|NULL|20||7788| SCOTT | ANALYST |7566|1987-07-03|3000|NULL|20||7839| KING | PRESIDENT |NULL|1981-11-17|5000|NULL|10||7902| FORD | ANALYST |7566|1981-12-03|3000|NULL|20|+-------+-------+-----------+------+------------+------+------+--------+13、返回部门号、部门名、部门所在位置及其每个部门的员工总数。
--需要用到emp和dept两个表select deptno ,dname ,loc ,count(*)from emp ee,dept dd where ee.deptno = dd.deptno
14、返回员工的姓名、所在部门名及其工资。
--需要用到emp表和dept表.select ee.ename , dd.dname ,ee.sal from emp ee,dept dd where ee.deptno = dd.deptno;+--------+------------+------+| ename | dname | sal |+--------+------------+------+| CLARK | ACCOUNTING |2450|| KING | ACCOUNTING |5000|| MILLER | ACCOUNTING |1300|| SMITH | RESEARCH |800|| JONES | RESEARCH |2975|| SCOTT | RESEARCH |3000|| ADAMS | RESEARCH |1100|| FORD | RESEARCH |3000|| ALLEN | SALES |1600|| WARD | SALES |1250|| MARTIN | SALES |1250|| BLAKE | SALES |2850|| TURNER | SALES |1500|| JAMES | SALES |950|+--------+------------+------+15、返回员工的详细信息。(包括部门名)--需要用到两个表emp和dept--在用select * 的基础上还要加上部门名这一列,这咋办????--看了答案明白了既然是还要加上部门名并且是详细信息,那么就对这三个表都select* 操作--select * from emp ee,dept dd,salgrade ss where ee.deptno = dd.deptno;--一开始没有加上工资的限制.select*from emp ee,dept dd,salgrade ss where ee.deptno = dd.deptno and sal between losal and hisal;+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+-------+-------+-------+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC | GRADE | LOSAL | HISAL |+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+-------+-------+-------+|7369| SMITH | CLERK |7902|1980-12-17|800|NULL|20|20| RESEARCH | DALLAS |1|700|1200||7499| ALLEN | SALESMAN |7698|1981-02-20|1600|300|30|30| SALES | CHICAGO |3|1401|2000||7521| WARD | SALESMAN |7698|1981-02-22|1250|500|30|30| SALES | CHICAGO |2|1201|1400||7566| JONES | MANAGER |7839|1981-04-02|2975|NULL|20|20| RESEARCH | DALLAS |4|2001|3000||7654| MARTIN | SALESMAN |7698|1981-09-28|1250|1400|30|30| SALES | CHICAGO |2|1201|1400||7698| BLAKE | MANAGER |7839|1981-05-01|2850|NULL|30|30| SALES | CHICAGO |4|2001|3000||7782| CLARK | MANAGER |7839|1981-06-09|2450|NULL|10|10| ACCOUNTING | NEW YORK |4|2001|3000||7788| SCOTT | ANALYST |7566|1987-07-03|3000|NULL|20|20| RESEARCH | DALLAS |4|2001|3000||7839| KING | PRESIDENT |NULL|1981-11-17|5000|NULL|10|10| ACCOUNTING | NEW YORK |5|3001|9999||7844| TURNER | SALESMAN |7698|1981-09-08|1500|0|30|30| SALES | CHICAGO |3|1401|2000||7876| ADAMS | CLERK |7788|1987-07-13|1100|NULL|20|20| RESEARCH | DALLAS |1|700|1200||7900| JAMES | CLERK |7698|1981-12-03|950|NULL|30|30| SALES | CHICAGO |1|700|1200||7902| FORD | ANALYST |7566|1981-12-03|3000|NULL|20|20| RESEARCH | DALLAS |4|2001|3000||7934| MILLER | CLERK |7782|1981-01-23|1300|NULL|10|10| ACCOUNTING | NEW YORK |2|1201|1400|+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+-------+-------+-------+16、返回员工工作及其从事此工作的最低工资。
--需要用到表emp,很明显需要 用到分组group by.select job ,min(sal)from emp groupby job;--原来可以在from前面直接用聚合函数.+-----------+----------+| job |min(sal)|+-----------+----------+| ANALYST |3000|| CLERK |800|| MANAGER |2450|| PRESIDENT |5000|| SALESMAN |1250|+-----------+----------+17、返回不同部门经理的最低工资。
--需要用到一个表emp,需要用到group byselect deptno ,min(sal)from emp where job ='manager'groupby deptno;+--------+----------+| deptno |min(sal)|+--------+----------+|10|2450||20|2975||30|2850|+--------+----------+18、计算出员工的年薪,并且以年薪排序。
--需要用到一个表empselect sal*12as ysal from emp orderby ysal asc;--上面的语句没有问题,关键是年薪的计算方法,这里还牵扯到奖金的问题.19、返回工资处于第四级别的员工的姓名。
--这里需要两个表,一个是emp一个是salgrade--select ename form emp ee,salgrade where grade =4;--这句话仔细一想就可以看出不会有任何结果,where过滤不掉任何东西select ename from emp ,salgrade where grade =4and sal>=losal and sal <= hisal;select ename from emp ,salgrade where grade =4and sal between losal and hisal;+-------+| ename |+-------+| JONES || BLAKE || CLARK || SCOTT || FORD |+-------+20、返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资
--从题意中可知需要用到三个表 emp dept salgrade--二等级的最低工资和最高工资.并不是二等级员工的工资.这个是和第21问的区别.--select ename ,loc ,min(sal) ,max(sal) from emp ee,dept dd, salgrade ss where grade = 2 and sal between losal and hisal;--二等级的最低工资和最高工资并不是二等级员工的最低工资和最高工资.select ename ,loc ,losal,hisal from emp ee,dept dd,salgrade ss where grade =2and sal between losal and hisal;21、返回工资为二等级的职员名字、部门所在地、二等级员工工资的最低工资和最高工资
--需要用到salgrade , dept表和emp表
mysql>select ename , loc ,min(sal),max(sal)from emp ee, dept dd, salgrade ss where grade =2and sal between losal and hisal;+-------+----------+----------+----------+| ename | loc |min(sal)|max(sal)|+-------+----------+----------+----------+| WARD | NEW YORK |1250|1300|+-------+----------+----------+----------+--这样写是错误的.到底在select后加上聚合函数的应用?????--select e.ename,loc,max(m.sal) ,min(m.sal),grade from emp e,emp m ,dept salgrade where e.deptno=dept.deptno and e.sal beteeen losal and hissal--m.sal between losal and hisal and grade = 2 group by e.ename , loc, grade;select ename , loc ,(selectmin(sal)from emp,salgrade where sal between losal and hisal and grade =2) minsal,(selectmax(sal)from emp,salgrade
where sal between losal and hisal and grade =2) maxsal from emp,salgrade ,dept where emp.deptno = dept.deptno and sal between
losal and hisal and grade =2;+--------+----------+--------+--------+| ename | loc | minsal | maxsal |+--------+----------+--------+--------+| WARD | CHICAGO |1250|1300|| MARTIN | CHICAGO |1250|1300|| MILLER | NEW YORK |1250|1300|+--------+----------+--------+--------+22.工资等级多于smith的员工信息。
--获得员工信息,三个表中有的信息都获取出来,就 用到三个表.--需要用到两个表 emp表和salgrade 表--select * from emp,salgrade where grade >(select sal from emp,salgrade where sal between losal and hisal);--select * from emp ee,salgrade ss,dept dd where grade > (select grade from salgrade, emp where ename = 'smith' and sal between losal and ss.hisal ) --and sal > (select sal from em );select*from emp,salgrade where sal between losal and hisal and grade >(select grade from emp,salgrade where sal between losal
and hisal and ename ='smith');+-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | GRADE | LOSAL | HISAL |+-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+|7499| ALLEN | SALESMAN |7698|1981-02-20|1600|300|30|3|1401|2000||7521| WARD | SALESMAN |7698|1981-02-22|1250|500|30|2|1201|1400||7566| JONES | MANAGER |7839|1981-04-02|2975|NULL|20|4|2001|3000||7654| MARTIN | SALESMAN |7698|1981-09-28|1250|1400|30|2|1201|1400||7698| BLAKE | MANAGER |7839|1981-05-01|2850|NULL|30|4|2001|3000||7782| CLARK | MANAGER |7839|1981-06-09|2450|NULL|10|4|2001|3000||7788| SCOTT | ANALYST |7566|1987-07-03|3000|NULL|20|4|2001|3000||7839| KING | PRESIDENT |NULL|1981-11-17|5000|NULL|10|5|3001|9999||7844| TURNER | SALESMAN |7698|1981-09-08|1500|0|30|3|1401|2000||7902| FORD | ANALYST |7566|1981-12-03|3000|NULL|20|4|2001|3000||7934| MILLER | CLERK |7782|1981-01-23|1300|NULL|10|2|1201|1400|+-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+