select database()
show tables
show tables from 其他库
desc<table name>表结构
查询
select column1, column2 from tablename
select * from emp
select empno, ename, sal*12 from emp
select empno as ‘员工编号’, ename as ‘员工姓名’, sal*12 as ‘年薪’from emp
select empno, ename, sal from emp where sal=5000;
<>/!=/</<=/is null/is not null/between …and …./and/or/in/not in/like
select * from emp where job in ('manager','salesman')
select * from emp where ename like '_A%'
select * from emp order by sal
select * from emp where job='MANAGER' order by sal
select * from emp order by job,sal
select * from emp order by sal asc/desc
select * from emp order by job desc, sal desc;
select lower/upper/(ename) from emp
select * from emp where substr(ename, 1, 1)=upper('m')/length(ename)=5
select * from emp where HIREDATE='1981-02-20'
select ifnull(comm,0) from emp;
如果 comm 为 null 就替换为 0
select count(*) from emp
select count(comm) from emp
select count(distinct job ) from emp
select sum/avg/max/min/(sal+comm) from emp
select job, sum(sal) from emp group by job having avg(sal) >2000
内连接没有主次关系
select e.ename, e.sal, d.dname from emp e join dept d on e.deptno=d.deptno where
e.sal>2000
外连接（右左外连接）右边的这张表看成主表：
select
e.ename,d.dname
from
emp e
right/left join
dept d
on
e.deptno = d.deptno;
union合并查询结果集
select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';
limit
limit startIndex, length
关于DQL语句的大总结：
select from where group by having order by limit
表的创建
create table 表名(字段名1 数据类型，...);
varchar(最长255)/char(最长255)/int(最长11)/bigint/float/double/date/datetime长日期类型/clob字符大对象
/blob二进制大对象
删除表：
drop table t_student;
drop table if exists t_student;
插入
nsert into 表名(字段名1,字段名2,字段名3...) values(值1,值2,值3);
数字格式化：format
修改update
update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3... where 条件;
删除数据
delete from 表名 where 条件;
快速创建表
create table emp2 as select * from emp;
快速删除表中的数据
delete from dept_bak慢
truncate table dept_bak
删除表
drop table 表名;
对表结构的增删改
alter
约束
not null / unique / primary key/ foreign key
name varchar(255) not null
primary key(id,name)
存储引擎
建表时指定存储引擎，以及字符编码方式。
create table t_product(id int primary key,name varchar(255)
)engine=InnoDB default charset=gbk;
事务
commit
rollback
索引
create index emp_ename_index on emp(ename);
drop index emp_ename_index on emp;
视图
create view dept2_view as select * from dept2;
create view dept2_view as select * from dept2;
设计范式
第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。
第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，
不要产生部分依赖。
第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，
不要产生传递依赖。