初识mysql

DDL操作

库的创建、使用删除等

创建数据库

create database dbname

使用数据库

use dbname

查看数据库

show databases

删除数据库

drop database dbname

修改表名

alter table oldname rename newname

表的相关操作

创建

create table tbname(
 'cname' var(32) not null comment 'a',
primary key ('cname'));

查看表

desc tbname;             
show create table tbname;

修改表

//修改列定义
alter table tbname modify colname var(23)...
//新增列
alter table tbname add colname var(23)... [after colname|first]
//删除
alter table tname drop colname;
//修改列名及定义
alter table tbname change colnameold colnamenew varchar(12)....

DML操作

插入

insert into tbname (col1,col2...) values(value1,value2...);
//不写字段但是后面的值要对应(顺序及数量）
insert into tbname values(value1,value2...);
//一次性插入多条记录，节约开销
insert into tbname (col1,col2..) values( val1,val2..),(val1,val2..)....;

跟新

//单表跟新
update tbname set col=value where ....
//多表跟新
uodate tb1,tb2 set ....= ,,, where...

删除

//无别名
delete from tbname [condition]
//有别名
delete a from tbname a [condition]

查询

//查询所有
select * from tbname

//去重,所有列相同的去掉
select distinct col1,col2... from tbname [condition] 

//多条件查询
select * from tbname condition [or|and] conditon ...

//排序(默认为asc升序，可以多条件，先判断第一个，相同在第二个
select * from tbname order by col[asc],col2 desc...

//限制（分页）a第几条开始（初始为0），b显示几条
select * from tbname limit a,b;

//聚合
select col,{coun()|sun()...} from tbname [where condition] group by col [with rollup][having condition];
with rollup ：多一行结果（汇总的） having用于group by后，一般先用where看情况使用having

//连接查询

//内连接 select col,col2 from tb1,tb2 where tb1.col1 =tb2.col2;等效于select col,col2 from tb1 inner join tb2 on|where tb1.col1 =tb2.col2;

//左外连接
select col1,col2 from tb1 left join tb2 on [condition];列出tb1中查询的所有及时tb2中没有

+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |                  emp
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 2000.00 | 1 |
| lisa | 2003-02-01 | 4000.00 | 2 |
| bjguan | 2004-04-02 | 5000.00 | 1 |
| bzshen | 2005-04-01 | 4000.00 | 3 |
| dony | 2005-02-05 | 2000.00 | 4 |
+--------+------------+---------+--------+

deptno | deptname |               dept
+--------+----------+
| 1 | tech |
| 2 | sale |
| 3 | hr 

mysql> select ename,deptname from emp left join dept on emp.deptno=dept.deptno;
+--------+----------+
| ename | deptname |
+--------+----------+
| zzx | tech |
| lisa | sale |
| bjguan | tech |
| bzshen | hr |
| dony | |
+--------+----------+

右连接与左连接相似

子查询

即使用的某个条件为一个select语句的结果，关键字：in、not in、=、!=、exists、not exists

当子查询语句无重复纪录是可以用=代替in

子查询有时可以转换为连接查询，如：

select a,b from tb1 where a in (select a from tb2);
等效于
select a,b from tb1,tb2 where tb1,a = tb2.a; 

纪录联合

将多表的结果联合union（去重） 和 union all

SELECT * FROM t1
UNION|UNION ALL
SELECT * FROM t2
select语句的列数和类型应保持一致