MySQL的增删查改命令用法及举例

1、准备工作

主要针对DDL和DML命令作详细解释。为此我们首先准备三张表，见下图，以供后续命令操作。

针对学生表（student_table）的列属性：

针对雇员表（emp）的列属性：

emp表的内容：

针对部分表（dept）的列属性：

dept表的内容：

2、DDL（data definition language）

i、create

如创建学生表：

create table student_table(

number char(6) not null primary key,

name char(6) not null,

sex char(2) default '1' not null,

dateOfBorn date null,

major char(12) null,

allSorce int(2) null,

other varchar(200) null);

或者利用其他表等效生成：create table student_table2 as select * from student_table;

ii、alter

添加列：alter table student_table add column address varchar(50);

修改列：alter table student_table modify column other varchar(20);

删除列：alter table student_table drop column address;

iii、drop

删表：drop table student_table;  //一旦使用该命令，就彻底删净该表啦！

3、DML（data manipulation language）

i、insert

插入数据：insert into student_table(number,name,sex,dateOfBorn,major,allSorce,other)

values(116034,'tyliang','1',sysdate(),'EE',99,NULL);

批量插入数据：insert into student_table select * from student_old_table where student_old_table.allSorce>60;

ii、update

更新数据：update student_table set allSorce=80 where number='116034';

iii、delete

删除表中一行：delete from student_table where number='116034';

删除整线表：delete from student_table;   此外还有，truncate table student_table;//不会产生回滚。

iv、强大而常用的select

用法如下

select  {[distinct | all] column | *}     //用于选择数据表、视图中的列 

[into table_name]                            //用于将原表的结构和数据插入新表中

from {tables | views | other select}  //用于指定数据来源，包括表、视图和其他select语句

[where conditions]                          //用于对检索的数据进行筛选

[group by columns]                         //用于对检索结果进行分组显示

[having conditions]                         //用于从使用group by子句分组后的查询结果中筛选数据行

[order by columns]                         //用于对结果集进行排序（asc为升，desc为降）

用法举例(简单的用法省略，只写下不太熟悉的)：

a、为列指定别名

select empNo as number,eName as name from emp;

b、计算列值

select sal*(1+0.1),sal from emp;

c、消除结果集中重复行

select distinct job from emp;

d、表达式比较

=,<,<=,>,>=,<>,!=   如：select empNo,eName,sal from emp where sal>1500;

e、模式匹配

两个通配符%和_，加like关键字。%：代表0个或多个字符。_：代表一个且只能是一个字符。

select empNo,eName,job from emp where eName like 'S%';

f、范围比较

两个关键字in和between

in或not in：select empNo,eName,job from emp where job in('PRESIDENT','MANAGER','ANALYST');

between ... and ...(a<=指定值<=b的意思)：select empNo,eName,sal from emp where sal between 2000 and 3000;

g、空值比较

不能用=检测空值，所以引入is null：select empNo,eName,sal,comm from emp where comm is null;

h、单行子查询（返回一行数据的子查询语句）

注意，子查询的返回结果必须是一行数据：

select empNo,eName,sal from emp where sal >(select min(sal) from emp) and sal < (select max(sal) from emp);

i、多行子查询

主要使用in、any、all运算符。

in：select empNo,eName,job from emp where deptNo in (select deptNo from dept where dName <> ‘SALES’)；

any：select deptNo,eName,sal from emp where sal > any (select sal from emp where deptNo =10) and deptNo <>10 ；

all：select deptNo,eName,sal from emp where sal > all (select sal from emp where deptNo =30) ；

j、关联子查询（内查询的执行需要借助于外查询，而外查询的执行又离不开内查询的执行）

select empNo,eName,sal from emp f  //将emp别名为f

where sal > (select avg(sal) from emp where job=f.job)

order by job;

k、表别名

select e.empNo as number, e.eName as name,d.dName as department

from emp e,dept d  //使用别名

where e.deptNo=d.deptNo and e.job='MANAGER';

使用别名注意的地方：

表的别名在from子名中定义，别名放在表名之后，它们之间用空格隔开；

别名一经定义，在整个的查询语句中就只能使用表的别名而不能再使用表名；

表的别名只在所定义的查询语句中有效；

应该选择有意义的别名，表的别名最长为30字符，简洁为主。

l、内连接（使用join指定连接的两表，使用on指定连接表的连接条件）

select e.empNo as number,e.eName as name,d.dName as department

from emp e join dept d

on e.deptNo=d.deptNo;//连接条件

m、外连接

i、左外连接（左表中满足连接条件的数据行也会列举出来）

select e.empNo as number,e.eName as name,d.dName as department

from emp e left join dept d

on e.deptNo=d.deptNo;//连接条件

ii、右外连接（右表中满足连接条件的数据行也会列举出来）

select e.empNo as number,e.eName as name,d.dName as department

from emp e right join dept d

on e.deptNo=d.deptNo;//连接条件

iii、完全外连接（左右表中满足连接条件的数据行也会列举出来）

select e.empNo as number,e.eName as name,d.dName as department

from emp e full join dept d

on e.deptNo=d.deptNo;//连接条件

n、聚合函数

常用的聚合函数：avg/max/min/count/sum等

o、group by函数

使用group by子名与聚集函数可以实现对查询结果中每一组数据进行分类统计。如下图举例：

使用group by值得注意地方：

在select子句的后面只可以有两类表达式：统计函数和进行分组的列名。

在select子句中的列名必须是进行分组的列，除此之外添加其他的列名都是错误，但是，group by子句后面的列名可以不出现在select子句中。

默认情况下，group by子句指定的分组升序排序，如果需要重新排序，可以使用order by子句指定新的排序顺序。

p、HAVING子句（对分组数据作进一步筛选，若不与group by搭配，则功能与where子句一样）

使用having子句，值得注意且记住的一句话：在select语句中，首先由from子名找到数据表，where子句则接收from子句输出的数据，而having子句则接收来自group by、where或from子句的输出。含有聚合函数的select，不能使用where子句接收。

r、排序

这个好理解，order by  column_name [asc | desc];