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(删除)