DML数据操作语句 insert delete update
-
- 6.创建表:
-
- 建表语句的语法格式:
- 关于MYSQL当中常见的数据类型
- 7. insert语句插入数据
- 8.表的复制
- 9.将查询结果插入到一张表中
- 10.修改数据:update
- 11.删除数据:delete
- 12.DQL(select) DML(insert delete update) DDL(create drop alter)
6.创建表:
建表语句的语法格式:
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,
....
);**
关于MYSQL当中常见的数据类型
int 整数型
bigint 长整型
float 浮点型
double 浮点型
char 定长字符串 (对应JavaString)
varchar 可变长字符串(对应StringBuffered/StringBuilder)
data 日期类型 (对应Java中的java.sql.Data类型)
BLOB 二进制大对象(存储图片、视频等流媒体信息) Binary Large Object
CLOB 字符大对象(存储较大文本,比如可以存储4G的字符串)Character Larger Object
BLOB和CLOB类型的使用: 电影表:t_movie
id(int) name(varchar) palytime(data/char) haibao(BLOB) histroy(CLOB)
---------------------------------------------------------------------------
1 蜘蛛侠
2
3
表名在数据库当中一般建议以t_或者tbl_开始。
drop table if exists t_student;//当这个表存在的话删除
创建学生表:
学号、姓名、性别、班级编号、生日
mysql> create table t_student(
-> no bigint,
-> name varchar(255),
-> sex char(1) default '1',
-> classno varchar(255),
-> birth char(10)
-> );
Query OK, 0 rows affected (0.06 sec)
//default 默认值是1(如果不设置默认值是NULL)
mysql> desc t_student;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| no | bigint(20) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| sex | char(1) | YES | | 1 | |
| classno | varchar(255) | YES | | NULL | |
| birth | char(10) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
7. insert语句插入数据
语法格式:
insert into 表名(字段1,字段2.字段3,...)values(值1,值2,值3....);
要求:字段的数量和值的数量相同,并且数据类型要对应相同。
注意:
当一条insert语句执行成功之后,表格当中必然会多一行记录。
即使多的这一行记录当中某些字段是NULL,后期也没有办法在执行
insert语句插入数据了,只能使用update进行更新。
//省略字段
//一次插入多行数据
insert into t_student
(no,name,sex,classno,birth)
values
(一行数据,....),(第二行数据,....);
mysql> insert into t_student
->values(1'zhangsan','男','01','2000.2.2'),
->(2,'lisi','男','01','2000.2.2')
->(3,'wangwu','女','02','2000.2.2');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t_student;
+------+----------+------+---------+----------+
| no | name | sex | classno | birth |
+------+----------+------+---------+----------+
| 1 | zhangsan | 男 | 01 | 2000.2.2 |
| 2 | lisi | 男 | 01 | 2000.2.2 |
| 3 | wangwu | 女 | 02 | 2000.2.2 |
+------+----------+------+---------+----------+
3 rows in set (0.00 sec)
8.表的复制
语法:
create table 表名 as select语句;
将查询结果当作表创建出来
mysql> create table t_student2 as select * from t_student;
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_student |
| t_student2 |
+----------------+
2 rows in set (0.00 sec)
9.将查询结果插入到一张表中
*insert into dept1 select from dept;
mysql> insert into t_students select * from t_student;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t_students;
+------+----------+------+---------+----------+
| no | name | sex | classno | birth |
+------+----------+------+---------+----------+
| 1 | zhangsan | 男 | 01 | 2000.2.2 |
| 2 | lisi | 男 | 01 | 2000.2.2 |
| 3 | wangwu | 女 | 02 | 2000.2.2 |
+------+----------+------+---------+----------+
3 rows in set (0.00 sec)
10.修改数据:update
语法格式:
update 表名 set 字段名1=值1,字段名2=值2…where 条件;
注意:没有条件整张表数据全部更新。
案例:将部门10的LOC修改为SHANGHAI,将部门名称修改为RENSHIBU
update dept1 set loc =‘SHANGHAI’,dename=‘RENSHIBU’ where deptno=10;
mysql> select *from dept2;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | accounting | new york |
| 20 | research | dallas |
| 30 | sales | chicago |
| 40 | operations | boston |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> update dept2 set loc='SHANGHAI',dname='RENSHIBU' where deptno=10;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dept2;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | RENSHIBU | SHANGHAI |
| 20 | research | dallas |
| 30 | sales | chicago |
| 40 | operations | boston |
+--------+------------+----------+
4 rows in set (0.00 sec)
11.删除数据:delete
语法格式:
delete from 表名 where 条件;
mysql> select * from emp2;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | smith | clerk | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | allen | salesman | 7902 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | ward | salesman | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | clark | manager | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | scott | analyst | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | king | persident | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | turner | salesman | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | adams | clerk | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | james | clerk | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | frod | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | miller | clerk | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
mysql> delete from emp2 where ename='smith';
Query OK, 1 row affected (0.02 sec)
mysql> select * from emp2;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7499 | allen | salesman | 7902 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | ward | salesman | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | clark | manager | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | scott | analyst | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | king | persident | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | turner | salesman | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | adams | clerk | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | james | clerk | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | frod | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | miller | clerk | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)
删除所有数据:
delete from dept1;
删除大表中的数据(重点)
truncate table emp1;//不可回滚,永久丢失。
删除表
drop table 表名;
drop table if exists 表名;//Oracle不支持
12.DQL(select) DML(insert delete update) DDL(create drop alter)
对于表结构的修改,使用工具完成,实际开发对表结构的修改是很少的。
增删改查的术语:CRUD操作
Create(增) Retrieve(检索) Update(修改) Delete(删除)