Mysql 基本操作

//操作数据库
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 //联合查询