SQL语法之DML

发表于 | 分类于
字数统计: 509 | 阅读时长 ≈ 3

本文主要介绍MySQL语法之DML(data manipulate language)操作。

1.数据操纵语言

DML主要是对数据库里的数据进行操作的语言。包括INSERT、UPDATE、DELETE等。

2.INSERT语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> create table emp  (empno int ,ename varchar(20), sex char(1),birthday date , hiredate date, sal decimal(10,2), deptno tinyint(1), managerno int) engine=memory default charset=utf8;
mysql> create table dept (deptno tinyint(1),deptname varchar(30),location varchar(50));	
mysql> insert into dept values (1,'manager','beijing'),(2,'it','shenzhen'), (3,'sale','shanghai'), (4,'services','guangzhou');

# 插入单条数据
mysql> insert into emp values (1,'boss','m','1964-08-08','1995-01-01','20000','1','1');
# 插入多条数据
mysql>insert into emp values
  -> (1,'boss','m','1964-08-08','1995-01-01','20000','1','1'),
  ->(2,'zhangsan','m','1967-04-05','1995-04-11','15000','2','1');
# 还可以插入特定的列（非所有列），那么没有插入的就成了空值（空值不是0，它做任何运算结果还是空值)
mysql> insert into emp (ename,sex) values ('lisi','m');
# 从其他表中查询到数据后插入
mysql> insert into emp (ename,sex) select ename,sex from emp1 limit 2;

3.UPDATE语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 把wangwu性别改成m
mysql> update emp set sex='m' where ename='wangwu';

# wangwu的工资加500
mysql> update emp set sal=sal+500 where ename='wangwu';

# 2号部门的所有员工工资涨10%
mysql> update emp set sal=sal*1.1 where deptno=2;

# mark由3号部门换成2号部门，同时工资加1000
mysql> update emp set deptno=2 and sal=sal+1000 where ename='mark';	--错误写法，两个执行动作不能用and
mysql> update emp set deptno=2,sal=sal+1000 where ename='mark';	--正确写法

# 工资就john和mark涨10%,其它人不涨
mysql> update emp set sal=sal*1.1 where ename='john' or  ename='mark';
mysql> update emp set sal=sal*1.1 where ename in ('john','mark');

# 工资都涨10%，john和mark犯错误，就他们不涨
mysql> update emp set sal=sal*1.1 where ename!='john' and  ename!='mark'
mysql> update emp set sal=sal*1.1 where ename not in ('john','mark');

4.DELETE语句

1
2
3
4
# 指定条件删除
mysql> delete from emp where empno=9;
# 不指定条件删除
mysql> delete from emp;
---------------- The End ----------------