DAY01
创建表
Create table 表明
(
列名 数据类型 约束 其他,
列名 2...,
...
)
e.g.
Create table tbl_stu
(
stuId int PRIMARY KEY auto_increment,
stuName VARCHAR(50) not NULL,
stuPwd char(32) not NULL,
stuTel char(11) UNIQUE,
stuStatus int default 1
)
PDM:物理数据模型,以图形的方式显示表结构,可以自动生成各种数据库软件识别的 s q l sql sql语句
MD5加密,加密后都是32位字符串,不可逆
-
s q l sql sql语句的分类
-
D M L DML DML:数据操作语言: i n s e r t , u p d a t e , d e l e t e insert, update, delete insert,update,delete
-
insert into 表名(列名,···) values(值,···)
-
update 表名 set 列名=值,列名2=值,··· where 主键=值
-
delete from 表名 where 条件
-
-
D Q L DQL DQL:数据查询语言: i n s e r t insert insert
- 表连接查询
- 分组查询数据
- 子查询
-
D D L DDL DDL:数据定义语言: c r e a t e , d r o p , a l t e r create,drop,alter create,drop,alter
-
D C L DCL DCL:数据控制语言,用户授权: g r a n t , r e v o k e grant,revoke grant,revoke
-
T C L ( T P L ) TCL(TPL) TCL(TPL):事务控制: c o m m i t , r o l l b a c k commit,rollback commit,rollback
-
作业8.15
-
从 e m p 、 d e p t 、 s a l g r a d e emp、dept、salgrade emp、dept、salgrade表中查询数据
-
查询工资高于2000的员工的姓名和部门名称
select ename,dname from dept, emp where emp.deptno = dept.deptno and emp.sal > 2000
select e.ename, d.dname from emp e inner join dept d on e.deptno = d.deptno where e.sal > 2000;
-
查询员工的工资和对应的级别
select ename,grade from emp,salgrade where emp.sal between salgrade.losal and salgrade.hisal
select e.ename, e.sal, s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal;
-
查询所有员工的姓名、部门名称、工资级别
select ename,dname,grade from emp,dept,salgrade where emp.deptno = dept.deptno and emp.sal between salgrade.losal and salgrade.hisal
select e.ename,d.dname,s.grade from emp e inner join dept d on e.deptno = d.deptno inner join salgrade s on e.sal between s.losal and s.hisal;
-
查询工资前三名员工姓名(Limit index,条数)
select ename from emp order by emp.sal desc LIMIT 0,3
create view empsal as select distinct sal from emp order by sal desc limit 0,3 select ename,emp.sal from emp inner join empsal on emp.sal=empsal.sal
-
查询部门人数最多的部门名称、及部门的工资总和
select dname, sum(sal) as sumsal from dept,emp where dept.deptno = emp.deptno group by emp.deptno order by count(emp.deptno) desc limit 0,1
create view v_empdept as select d.dname,sum(e.sal) sal from emp e inner join dept d on e.deptno = d.deptno where e.deptno in (select deptno from emp group by deptno having count(empno) = (select distinct count(empno) c from emp group by deptno order by c desc limit 0,1)) group by d.dname select * from v_empdept
-
-
向 e m p emp emp表中插入一条数据
-
姓名 工作 mgr 工资 部门 编号 张三 测试 7788 5000 40 1000 insert into emp(ename,job,mgr,sal,deptno,empno) values('张三','测试',7788,5000,40,1000)
-
-
修改员工张三的工资为6000,且奖金为500
update emp set sal=6000,comm=500 where ename='张三'
练习查询
-
统计每一种工作的人数
select job, count(*) c from emp group by emp.job
-
查询工资级别为2级的员工名称,部门编号和部门名称
select ename,emp.deptno,dname from emp,dept,salgrade where emp.deptno=dept.deptno and emp.sal between salgrade.losal and salgrade.hisal and grade = 2
-
查询员工姓名及其经理姓名
select e.ename ename, m.ename mname from emp e inner join emp m on e.mgr=m.empno
-
工资高于直属经理的员工姓名,工资和经理姓名
select e.ename ename, e.sal, m.ename mname from emp e inner join emp m on e.mgr=m.empno where e.sal > m.sal
-
查询所有员工的年收入,按收入的降序排列
select ename,sal*12+IFNULL(comm,0) totsal from emp order by totsal desc
-
查询每年2月份入职的员工信息
select ename,hiredate from emp where month(hiredate)=2
-
查询入职超过10年的员工信息
select * from emp where year(now())-year(hiredate)>10