#####################CHAPTER5操作数据DML##########
--5.2新数据填充表
--5.2.1把数据插入到表
--基本语法
INSERT INTO TABLE_NAME
VALUES ('value1', 'value2', [ NULL ] );
mysql> DESC PRODUCTS_TBL;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| PROD_ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| PROD_DESC | varchar(40) | NO | | NULL | |
| COST | decimal(10,2) | NO | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
3 rows in set
mysql> SELECT * FROM PRODUCTS_TBL;
+---------+-------------------------+-------+
| PROD_ID | PROD_DESC | COST |
+---------+-------------------------+-------+
| 13 | FALSE PARAFFIN TEETH | 1.1 |
| 90 | LIGHTED LANTERNS | 14.5 |
| 222 | PLASTIC PUMPKIN 18 INCH | 7.75 |
| 11235 | WITCH COSTUME | 29.99 |
+---------+-------------------------+-------+
4 rows in set
mysql> INSERT INTO PRODUCTS_TBL VALUES('7725', 'LEATHER GLOVES', 24.99);
Query OK, 1 row affected
mysql> SELECT * FROM PRODUCTS_TBL;
+---------+-------------------------+-------+
| PROD_ID | PROD_DESC | COST |
+---------+-------------------------+-------+
| 13 | FALSE PARAFFIN TEETH | 1.1 |
| 90 | LIGHTED LANTERNS | 14.5 |
| 222 | PLASTIC PUMPKIN 18 INCH | 7.75 |
| 7725 | LEATHER GLOVES | 24.99 |
| 11235 | WITCH COSTUME | 29.99 |
+---------+-------------------------+-------+
5 rows in set
--5.2.2给表里指定列插入数据
--基本语法
INSERT INTO TABLE_NAME('COLUMN1', 'COLUMN2')
VALUES ('VALUE1', 'VALUE2');
mysql> DESC EMPLOYEE_TBL;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| EMP_ID | char(9) | NO | PRI | NULL | |
| EMP_NAME | varchar(40) | NO | | NULL | |
| EMP_ST_ADDR | varchar(20) | NO | | NULL | |
| EMP_CITY | varchar(15) | NO | | NULL | |
| EMP_ST | char(2) | NO | | NULL | |
| EMP_ZIP | int(5) | NO | | NULL | |
| EMP_PHONE | int(10) | YES | UNI | NULL | |
| EMP_PAGER | int(10) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
8 rows in set
mysql> INSERT INTO EMPLOYEE_TBL
(EMP_ID, EMP_NAME, EMP_ST_ADDR, EMP_CITY, EMP_ST, EMP_ZIP, EMP_PHONE)
VALUES
('12345678', 'SMITH JAY JOHN','12 BEACON CT',
'INDIANAPLIS', 'IN', '46222', '317299686');
Query OK, 1 row affected
mysql> SELECT * FROM EMPLOYEE_TBL;
+----------+----------------+--------------+-------------+--------+---------+-----------+-----------+
| EMP_ID | EMP_NAME | EMP_ST_ADDR | EMP_CITY | EMP_ST | EMP_ZIP | EMP_PHONE | EMP_PAGER |
+----------+----------------+--------------+-------------+--------+---------+-----------+-----------+
| 12345678 | SMITH JAY JOHN | 12 BEACON CT | INDIANAPLIS | IN | 46222 | 317299686 | NULL |
+----------+----------------+--------------+-------------+--------+---------+-----------+-----------+
1 row in set
--5.2.3从另一个表插入
--语法
INSERT INTO TABLE_NAME [('column1', 'column2')]
SELECT [*|('column1', 'column2')]
FROM TABLE_NAME
[WHERE CONDITION(S)];
--5.2.4插入NULL值
--语法
INSERT INTO SCHEMA.TABLE_NAME VALUES
('column1', NULL, 'column3');
mysql> CREATE TABLE ORDERS_TBL(
ORD_NUM VARCHAR(10) NOT NULL,
CUST_ID VARCHAR(10) NOT NULL,
PROD_ID VARCHAR(10) NOT NULL,
QTY DECIMAL(4) NOT NULL,
ORD_DATA DATE NULL);
Query OK, 0 rows affected
mysql> SHOW TABLES;
+--------------------+
| Tables_in_learnsql |
+--------------------+
| employee_pay_tbl |
| employee_pay_tst |
| employee_tbl |
| orders_tbl |
| products_tbl |
| test_increment |
+--------------------+
6 rows in set
mysql> DESC ORDERS_TBL;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| ORD_NUM | varchar(10) | NO | | NULL | |
| CUST_ID | varchar(10) | NO | | NULL | |
| PROD_ID | varchar(10) | NO | | NULL | |
| QTY | decimal(4,0) | NO | | NULL | |
| ORD_DATA | date | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
5 rows in set
mysql> INSERT INTO ORDERS_TBL (ORD_NUM, CUST_ID, PROD_ID, QTY)
VALUES('23A16', '109', '7725', 2);
Query OK, 1 row affected
mysql> SELECT * FROM ORDERS_TBL;
+---------+---------+---------+-----+----------+
| ORD_NUM | CUST_ID | PROD_ID | QTY | ORD_DATA |
+---------+---------+---------+-----+----------+
| 23A16 | 109 | 7725 | 2 | NULL |
+---------+---------+---------+-----+----------+
1 row in set
--5.3更新现有数据
--5.3.1更新一列的数据
--语法
UPDATE TABLE_NAME
SET COLUMN_NAME = 'VALUE'
[WHERE CONDITION];
mysql> UPDATE ORDERS_TBL
SET QTY = 1
WHERE ORD_NUM = '23A16';
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM ORDERS_TBL;
+---------+---------+---------+-----+----------+
| ORD_NUM | CUST_ID | PROD_ID | QTY | ORD_DATA |
+---------+---------+---------+-----+----------+
| 23A16 | 109 | 7725 | 1 | NULL |
+---------+---------+---------+-----+----------+
1 row in set
--5.3.2更新一条或多记录里的多个字段
--语法
UPDATE TABLE_NAME
SET COLUMN1 = 'value',
[COLUMN2 = 'value',]
[COLUMN3 = 'value']
[where condition];
mysql> update orders_tbl
set qty = 1,
cust_id = '221'
where ord_num = '23A16';
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM ORDERS_TBL;
+---------+---------+---------+-----+----------+
| ORD_NUM | CUST_ID | PROD_ID | QTY | ORD_DATA |
+---------+---------+---------+-----+----------+
| 23A16 | 221 | 7725 | 1 | NULL |
+---------+---------+---------+-----+----------+
1 row in set
--5.4从表里删除数据
--delete之前先用select命令,DELETE语句应该总是使用WHERE子句
--语法
DELETE FROM TABLE_NAME
[WHERE CONDITION];
mysql> DELETE FROM ORDERS_TBL
WHERE ORD_NUM = '23A16';
Query OK, 1 row affected
mysql> SELECT * FROM ORDERS_TBL;
Empty set
--5.7实践
--5.7.1
mysql> create table test_employee_tbl(
last_name varchar(20) not null,
first_name varchar(20) not null,
ssn char(9) not null,
phone decimal(10) null);
Query OK, 0 rows affected
mysql> show tables;
+--------------------+
| Tables_in_learnsql |
+--------------------+
| employee_pay_tbl |
| employee_pay_tst |
| employee_tbl |
| orders_tbl |
| products_tbl |
| test_employee_tbl |
| test_increment |
+--------------------+
7 rows in set
mysql> desc test_employee_tbl;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| last_name | varchar(20) | NO | | NULL | |
| first_name | varchar(20) | NO | | NULL | |
| ssn | char(9) | NO | | NULL | |
| phone | decimal(10,0) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+
4 rows in set
mysql> select * from test_employee_tbl;
Empty set
INSERT INTO TEST_EMPLOYEE_TBL VALUES
('JACKSON', 'STEVE', '313546078', '3178523443');
--5.7.2
--E.2.5
INSERT INTO PRODUCTS_TBL VALUES
('11235', 'WITCH COSTUME', '29.99');
INSERT INTO PRODUCTS_TBL VALUES
('222', 'PLASTIC PUMPKIN 18 INCH', '7.75');
INSERT INTO PRODUCTS_TBL VALUES
('13', 'FALSE PARAFFIN TEETH', '1.10');
INSERT INTO PRODUCTS_TBL VALUES
('90', 'LIGHTED LANTERNS', '14.5');
INSERT INTO PRODUCTS_TBL VALUES
('15', 'ASSORTED COSTUMES', '10.00');
INSERT INTO PRODUCTS_TBL VALUES
('9', 'CANDY CORN', '1.35');
mysql> INSERT PRODUCTS_TBL VALUES
('301', 'FIREMAN COSTUME', '24.99');
INSERT PRODUCTS_TBL VALUES
('302', 'POLICEMAN COSTUME', '24.99');
INSERT PRODUCTS_TBL VALUES
('303', 'KIDDIE GRAB BAG', '4.99');
mysql> SELECT * FROM PRODUCTS_TBL;
+---------+-------------------------+-------+
| PROD_ID | PROD_DESC | COST |
+---------+-------------------------+-------+
| 9 | CANDY CORN | 1.35 |
| 13 | FALSE PARAFFIN TEETH | 1.1 |
| 15 | ASSORTED COSTUMES | 10 |
| 90 | LIGHTED LANTERNS | 14.5 |
| 222 | PLASTIC PUMPKIN 18 INCH | 7.75 |
| 301 | FIREMAN COSTUME | 24.99 |
| 302 | POLICEMAN COSTUME | 24.99 |
| 303 | KIDDIE GRAB BAG | 4.99 |
| 7725 | LEATHER GLOVES | 24.99 |
| 11235 | WITCH COSTUME | 29.99 |
+---------+-------------------------+-------+
10 rows in set
mysql> UPDATE PRODUCTS_TBL
-> SET COST = '29.99'
-> WHERE PROD_ID = '301';
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE PRODUCTS_TBL
-> SET COST = '29.99'
-> WHERE PROD_ID = '302';
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM PRODUCTS_TBL;
+---------+-------------------------+-------+
| PROD_ID | PROD_DESC | COST |
+---------+-------------------------+-------+
| 9 | CANDY CORN | 1.35 |
| 13 | FALSE PARAFFIN TEETH | 1.1 |
| 15 | ASSORTED COSTUMES | 10 |
| 90 | LIGHTED LANTERNS | 14.5 |
| 222 | PLASTIC PUMPKIN 18 INCH | 7.75 |
| 301 | FIREMAN COSTUME | 29.99 |
| 302 | POLICEMAN COSTUME | 29.99 |
| 303 | KIDDIE GRAB BAG | 4.99 |
| 7725 | LEATHER GLOVES | 24.99 |
| 11235 | WITCH COSTUME | 29.99 |
+---------+-------------------------+-------+
10 rows in set