//操作数据库 create database test use test show tables drop database test
//操作表 create table emp(ename varchar(10), hiredate date , sal decimal(10,2),deptno int(2)) create table dept(deptno int(2),deptname varchar(10)) desc emp //查询表的定义 show create table emp //查询表的定义(详细) drop table emp //删除表
//修改表字段 alter table emp modify ename varchar(20) // 修改表字段 alter table emp add column age int(3) //添加表字段 alter table emp drop column age //删除表字段
//新增记录 insert into emp (ename, hiredate,sal,deptno) values(‘hanhan’,‘2018-01-01’,‘2000’,1) //新增记录 insert into emp values(‘liuyan’,‘2018-01-01’,‘3000’,2,30) insert into dept values(1,“dept1”),(2,“dept2”) //批量新增
//更新数据 update emp set sal=‘4000’, deptno=2 where ename=“qiezi” //删除数据 delete from emp where ename=‘qiezi’
//查询数据 select from emp select distinct deptno from emp //查询不重复的记录 select * from emp where deptno=1 and sal>1000 select * from emp order by deptno desc limit 3 //排序和限制查询 select * from emp order by deptno desc limit 0,2 // 分页查询 select deptno,count() from emp GROUP BY deptno having count(1) >1 //查询部门人数大于1的编号 select * from emp e left join dept d on e.deptno = d.deptno //链接查询 select * from emp where deptno in (select deptno from dept) //子查询 select deptno from emp union select deptno from dept //联合查询
