DDL 语句
创建数据库:
create database test;
切换数据库:
use test;
查看该库地下所有的数据表
show tables;
删除数据库:
drop database test;
创建表:
create table emp(column_name1 column_type,column_name2 column_type ...)
删除表:
drop table emp;
修改表
alter table emp modify column_name1 int(3) [first | after col_name]
增加表字段
alter table emp add column age int(2);
删除表字段:
alter table emp drop column age;
字段改名:
alter table emp change age age1 int(4)
修改表名称
alter table emp rename emp1;
DML语句:
插入语句:
insert into emp(id,age) values(1,20);
insert into emp(id,age) values(1,20),(2,21);批量添加
更新语句:
update emp set age = 22 where id = 2;
删除语句:
delete from emp where id = 2;
查询语句:
select * from emp;
去重:select distinct deptno from emp;
条件查询:select * from emp where deptno = 4;
排序: select * from emp order by id;
分页: select * from emp limit 2,3; 2:表示起始偏移量,3:表示显示条数
聚合:select count(id) from emp group by depno having count(id) > 1;
表连接:select ename,deptname from emp p,dept t where p.depno = t.depno;
子查询:select ename from emp where depno in (select depno from dept);