mysql建表与练习

Create table class (
classid int(2) primary KEY,
cname varchar(20),
ccount int(3)
)

INSERT into class values (1,‘JAVA1班’,null),(1,‘JAVA2班’,null),(1,‘JAVA3班’,null);
INSERT into student VALUES (‘A001’,‘张三’,‘男’,‘01-05-05’,100,1);
INSERT into student VALUES (‘A002’,‘MIKE’,‘男’,‘1905-05-06’,10,NULL);
INSERT into student VALUES (‘A003’,‘JOHN’,‘女’,null,null,null);

select classid from class where cname=‘JAVA3班’;

UPDATE student SET studentcid=(select classid from class where cname=‘JAVA3班’) where birthday is null;

CREATE TABLE copy_emp (
empno int(4),
ename varchar(20),
hiredate date,
deptno int(2),
sal double(8,2))

INSERT into copy_emp

INSERT into

SELECT avg(sal) from copy_emp;

update copy_emp set sal=null where (SELECT * FROM((SELECT avg(sal) from copy_emp) as tt))

SELECT count(sal) from emp
GROUP BY deptno;

SELECT count(sal) from emp e, dept d WHERE e.DEPTNO=d.deptno;

update class set ccount=(select count(*) from student where class.classid=student.studentcid GROUP BY classid)

select count(*) from student where class.classid=student.studentcid

INSERT INTO emp VALUES
(7369,‘SMITH’,‘CLERK’,7902,‘1980-12-17’,800,NULL,20);
INSERT INTO EMP VALUES
(7499,‘ALLEN’,‘SALESMAN’,7698,‘1981-02-20’,1600,300,30);
INSERT INTO EMP VALUES
(7521,‘WARD’,‘SALESMAN’,7698,‘1981-02-22’,1250,500,30);
INSERT INTO EMP VALUES
(7566,‘JONES’,‘MANAGER’,7839,‘1981-04-02’,2975,NULL,20);
INSERT INTO EMP VALUES
(7654,‘MARTIN’,‘SALESMAN’,7698,‘1981-09-28’,1250,1400,30);
INSERT INTO EMP VALUES
(7698,‘BLAKE’,‘MANAGER’,7839,‘1981-05-01’,2850,NULL,30);
INSERT INTO EMP VALUES
(7782,‘CLARK’,‘MANAGER’,7839,‘1981-06-09’,2450,NULL,10);
INSERT INTO EMP VALUES
(7788,‘SCOTT’,‘ANALYST’,7566,‘1987-07-13’,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,‘KING’,‘PRESIDENT’,NULL,‘1981-11-17’,5000,NULL,10);
INSERT INTO EMP VALUES
(7844,‘TURNER’,‘SALESMAN’,7698,‘1981-09-08’,1500,0,30);
INSERT INTO EMP VALUES
(7876,‘ADAMS’,‘CLERK’,7788,‘1987-07-13’,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,‘JAMES’,‘CLERK’,7698,‘1981-12-03’,950,NULL,30);
INSERT INTO EMP VALUES
(7902,‘FORD’,‘ANALYST’,7566,‘1981-12-03’,3000,NULL,20);
INSERT INTO EMP VALUES
(7934,‘MILLER’,‘CLERK’,7782,‘1982-01-23’,1300,NULL,10);

INSERT INTO DEPT VALUES (10,‘ACCOUNTING’,‘NEW YORK’);
INSERT INTO DEPT VALUES (20,‘RESEARCH’,‘DALLAS’);
INSERT INTO DEPT VALUES (30,‘SALES’,‘CHICAGO’);
INSERT INTO DEPT VALUES (40,‘OPERATIONS’,‘BOSTON’);

SELECT ename ,IFNULL(SAL,0) ,(IFNULL(SAL,0)*6+IFNULL(SAL,0)1.26) from emp
SELECT ename as 员工姓名 ,IFNULL(SAL,0) AS 工资收入 ,(IFNULL(SAL,0)*6+IFNULL(SAL,0)1.26) as 年工资 from emp

SELECT ename as 员工姓名 ,IFNULL(SAL,0) AS 工资收入 ,IFNULL(comm,0) as 奖金收入,(IFNULL(SAL,0)*6+IFNULL(SAL,0)1.26+IFNULL(comm,0)) as 年工资
from emp

desc emp;

show CREATE table emp;

create TABLE copy_emp (SELECT * from emp);

SELECT * from emp where ENAME like ‘张%’;
SELECT * from emp where ENAME like ‘%张%’;

SELECT * from emp where ENAME like ‘%张’;

SELECT * from emp where ENAME like ‘%张@_’ ESCAPE ‘@’;

SELECT now(),date(now());

SELECT ename,JOB,sal from emp
where sal>2000 and job=‘MANAGER’ or job=‘SALESMAN’;

SELECT ename,deptno
from emp
WHERE deptno=20 or deptno=30
order by sal desc;

select ename,deptno,sal
from emp
where

SELECT empno,ename,sal,deptno,loc
FROM emp
NATURAL JOIN dept;

SELECT e.ename,e.ename,e.sal,deptno,d.loc
FROM emp e JOIN dept d USING (deptno)
WHERE deptno = 20 ;

SELECT e.empno, e.ename, d.loc,m.ename
FROM emp e
JOIN dept d
ON e.deptno = d.deptno
JOIN emp m
ON e.mgr = m.empno;

SELECT ename, deptno, sal
FROM emp
LIMIT 0,5;

SELECT ename ,hiredate
from emp
ORDER BY HIREDATE
limit 5;

SELECT ename ,hiredate
from emp
where deptno =
(select deptno
from dept
where loc=“CHICAGO”)
ORDER BY HIREDATE
limit 2;

SELECT ename,hiredate,deptno from emp
limit 0,5;
SELECT ename,hiredate,deptno from emp
limit 5,5;
SELECT ename,hiredate,deptno from emp
limit 10,5;

SELECT ename,hiredate,job
from emp
where hiredate>‘1982-07-09’ and job != ‘SALEMAN’;

SELECT ename
from emp
where ename like ‘__a%’;

SELECT ename ,deptno
from emp
where DEPTNO not in (10,20)

SELECT *
from emp
where DEPTNO=30
ORDER BY sal desc,ENAME

SELECT ename
from emp
where mgr is null

SELECT ename,sal,deptno
from emp
where sal>4500 and (deptno=10 or deptno=20);