一、Mysql基础
SQL的分类:
1、DDL—数据定义语言(Data Define
Language):create(创建),alter(修改),drop(删除),TRUNCATE(截断),RENAME(重命名);
2、DML—数据操纵语言(Data Manipulation
Language):select(查询),delete(删除),update(更新),insert(新增);
3、DCL—数据控制语言(Data Control Language):grant(添加权限),revoke(回收权限);
创建数据库:
create database test1 ;
查看数据库:
show databases;
选择数据库:
use mysql;
删除数据库:
drop database test1;
创建表:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [database_name.] <table_name>
(
<column_name> <data_type> [[not] null],…
)
注:TEMPORARY:指明创建临时表 IF NOT EXISTS:如果要创建的表已经存在,强制不显示错误消息
database_name:数据库名 table_name:表名 column_name:列名 data_type:数据类型
查看定义:
desc emp;
查看创建的表:
show create table emp ;
更新表名:
alter table emp rename users;
删除表:
drop table emp;
修改表字段:
alter table emp modify ename varchar(30);
增加表字段:
alter table emp add column age int(3);
修改表字段:
alter table emp change age age int(4);
删除表字段:
alter table emp drop column age;
change和modify:
前者可以修改列名称,后者不能.
change需要些两次列名称.
字段增加修改 add/change/modify/ 添加顺序:
1 add 增加在表尾. 2 change/modify 不该表字段位置. 3 修改字段可以带上以下参数进行位置调整(frist/after
column_name);
alter table emp change age age int(2) after ename;
alter table emp change age age int(3) first;
DML语句:
插入记录:
//指定字段,
//自增,默认值等字段可以不用列出来,没有默认值的为自动设置为NULL
insert into emp (ename,hiredate,sal,deptno) values ('jack','2000-01-01','2000',1);
//可以不指定字段,但要一一对应
insert into emp values ('lisa','2010-01-01','8000',2);
批量记录:
insert into emp values ('jack chen','2011-01-01','18000',2),('andy lao','2013-01-01','18000',2);
、
更新记录:
update emp set sal="7000.00" where ename="jack";
update emp e,dept d set e.sal="10000",d.deptname=e.ename where e.deptno=d.deptno and e.ename="lisa";
删除记录:
//请仔细检查where条件,慎重
delete from emp where ename='jack';
查看记录:
//查看所有字段
select * from emp;
//查询不重复记录
select distinct(deptno) from emp ;
select distinct(deptno),emp.* from emp ;
//条件查询
//比较运算符: > < >= <= <> != …
//逻辑运算符: and or …
select * from emp where sal="18000" and deptno=2;
排序
//desc降序,asc 升序(默认)
select * from emp order by deptno ;
select * from emp order by deptno asc;
select * from emp order by deptno desc,sal desc;
限制记录数:
select * from emp limit 1;
select * from emp limit 100,10;
select * from emp order by deptno desc,sal desc limit 1;
聚合:函数:count():记录数 / sum(总和); / max():最大值 / min():最小值
select count(id) from emp ;
select sum(sal) from emp ;
select max(sal) from emp ;
select min(sal) from emp ;
group by分组:
//分组统计
select count(deptno) as count from emp group by deptno;
select count(deptno) as count,deptno from emp group by deptno;
select count(deptno) as count,deptno,emp.* from emp group by deptno;
having 对分组结果二次过滤:
select count(deptno) as count,deptno from emp group by deptno having count > 2;
表连接:
left join :左连接,返回左表中所有的记录以及右表中连接字段相等的记录;
right join :右连接,返回右表中所有的记录以及左表中连接字段相等的记录;
inner join: 内连接,又叫等值连接,只返回两个表中连接字段相等的行;
full join:外连接,返回两个表中的行:left join + right join;
cross join:结果是笛卡尔积,就是第一个表的行数乘以第二个表的行数。
内连接:只返回两个表中连接字段相等的行
select * from emp as e,dept as d where e.deptno=d.deptno;
select * from emp as e inner join dept as d on e.deptno=d.deptno;
左外连接:包含左表中所有的记录以及右表中连接字段相等的记录
select * from emp as e left join dept as d on e.deptno=d.deptno;
右外连接:包含右表中所有的记录以及左表中连接字段相等的记录
select * from emp as e right join dept as d on e.deptno=d.deptno;
子查询:
//=, !=
select * from emp where deptno = (select deptno from dept where deptname="技术部");
select * from emp where deptno != (select deptno from dept where deptname="技术部");
//in, not in
//当需要使用里面的结果集的时候必须用in();
select * from emp where deptno in (select deptno from dept where deptname="技术部");
select * from emp where deptno not in (select deptno from dept where deptname="技术部");
//exists , not exists
//当需要判断后面的查询结果是否存在时使用exists();
select * from emp where exists (select deptno from dept where deptno > 5);
select * from emp where not exists (select deptno from dept where deptno > 5);