登录MySQL服务器
mysql -u root -p如果没有配合环境变量就需要执行（起别名）：
alias mysql=/usr/local/mysql/bin/mysql
alias mysqladmin=/usr/local/mysql/bin/mysqladmin插入记录需求
| 表名称、表字段名称、数据内容、其他 |
语法
名称：'INSERT'
描述：插入关键字
句法：
INSERT [低优先级 |延迟 | 高优先级] [省略] [into] 表名称 [分区 (分区名称, ...)][(字段名称, ...)] { {VALUES |VALUE} (记录内容, ...) | {VALUES} 根据行创建的函数列表 } [AS 行别名[(列别名, ...]] [ON 复制 KEY UPDATE 分配的列表]
INSERT [低优先级 |延迟 | 高优先级] [省略] [into] 表名称 [分区 (分区名称, ...)][(字段名称, ...)] { {VALUES |VALUE} (记录内容, ...) | {VALUES} 根据行创建的函数列表 } [AS 行别名[(列别名, ...]] SET 分配的列表 [ON 复制 KEY UPDATE 分配的列表]
INSERT [低优先级 |延迟 | 高优先级] [省略] [into] 表名称 [分区 (分区名称, ...)][(字段名称, ...)] { {VALUES |VALUE} (记录内容, ...) | {VALUES} 根据行创建的函数列表 } [AS 行别名[(列别名, ...]] {SELECT ... | TABLE 表名称} [ON 复制 KEY UPDATE 分配的列表]
value：{ 表达式 | 默认 | 具体数据 }
value_list： value [一个或多个记录数据]
根据行创建的函数列表：ROW(value_list)[, ROW(value_list)][, ...]
赋值：字段名称 = value | [行别名.]字段名称 | [表名称.]字段名称 | [行别名.]字段别名
分配列表：赋值 [, 赋值] ...
INSERT 将新行插入到现有表中
INSERT ... VALUES ROW() 插入一行或多行
INSERT ... SET 单独字段单个设置插入
INSERT ... TABLE 复制表。
INSERT ... SELECT 选择别表记录
INSERT ... ON 关于
注意：插入表需要表的 INSERT 权限。插入记录的格式
1.若指定的表字段和表字段的记录内容都为空，那么INSERT将创建一个空行，每列的记录内容都是各个字段对应的数据类型的默认值，格式如下：
INSERT INTO 表名称 () VALUES();注意：启用严格默认，会报错；反之，默认创建。
2.若指定的表字段和表字段的记录内容都为不为空，正常插入记录，格式如下：
INSERT INTO 表名称 (c1,c2) VALUES(record1,record2);3.若指定的表字段和表字段的记录内容都为不为空，正确交叉插入记录，格式如下：
INSERT INTO 表名称 (c1,c2) VALUES(1,record1*2);注意：前提是c1,c2的数据类型一致或者在某种精度范围内可以隐式转换。
4.若指定的表字段和表字段的记录内容都为不为空，非法交叉插入记录，格式如下：
INSERT INTO 表名称 (c1,c2) VALUES(record2,record1);注意：因为c1的值指向record2，而record2是c2的记录，其在c1之后分配的。
5.若指定的表字段和表字段的记录内容都为不为空，不指定字段名称插入记录，格式如下：
INSERT INTO 表名称 VALUES(record2,record1);注意：VALUES中的记录内容必须按照字段名称的正确顺序一次设置所有字段对应的内容。
6.若指定的表字段和表字段的记录内容都为不为空，可以多行插入记录，行与行之间用逗号分隔开来，记录被封闭在小括号内，格式如下：
INSERT INTO 表名称 (c1,c2) VALUES(record1,record2),(record3,record4),(record5,record6);注意：每个字段对应的每行插入的记录数量一样多。
7.若指定的表字段和表字段的记录内容都为不为空，无效的多行插入记录，格式如下：
INSERT INTO 表名称 (c1,c2) VALUES(record1,record2,record3,record4,record5);注意：每个字段对应的每行插入的记录数量一样多，反之无效。
8.若指定的表字段和表字段的记录内容都为不为空，使用行构造函数ROW()插入一条或多条记录，格式如下：
INSERT INTO 表名称 (c1,c2) VALUES ROW(record1,record2), ROW(record3,record4), ROW(record5,record6);注意：每个字段对应的每行插入的记录数量一样多，反之无效。
9.若指定的表字段和表字段的记录内容都为不为空，使用SET插入记录数据，格式如下：
INSERT INTO 表名称 SET c1=record1, c2=record2;注意：这里是字段名与记录内容单独对应,(换句话说SET不能多条插入）。
10.若指定的表字段和表字段的记录内容都为不为空，省略into插入记录，格式如下：
INSERT 表名称 (c1,c2) VALUES(record1,record2);注意：可以省略into，但是不推荐。
11.若指定的表字段和表字段的记录内容都为不为空，省略into使用vaule插入记录，格式如下：
INSERT 表名称 (c1,c2) VALUE(record1,record2);注意：可以省略into，但是不推荐。
12.若指定的表字段和表字段的记录内容都为不为空，使用vaule插入记录，格式如下：
INSERT INTO 表名称 (c1,c2) VALUE(record1,record2);13.若两个表中指定的表字段和表字段的记录内容都为不为空，使用select插入记录，格式如下：
INSERT INTO 表名称1 (c11,c12) SELECT c21,c22 FROM 表名称2;注意：c11与c21、c12与c22数据类型一致。
14.若两个表中指定的表字段和表字段的记录内容都为不为空，使用TABLE插入记录，格式如下：
INSERT INTO 表名称1 TABLE 表名称2;注意：表名称1与表名称2对应字段的数据类型一致（简称表的复制）。
15.若两个表中指定的表字段和表字段的记录内容都为不为空，使用on插入记录，格式如下：
INSERT INTO 表名称1 (c1,c2) VALUE(record1,record2) ON DUPLICATE KEY UPDATE 字段名称=操作表达式;INSERT INTO 表名称1 (c1,c2) VALUE(record1,record2) ON DUPLICATE KEY UPDATE c1=VALUES(c2)*2;INSERT INTO 表名称1 (c1,c2) VALUE(record1,record2) AS 行别名 ON DUPLICATE KEY UPDATE c1=行别名.c1 + 行别名.c2;INSERT INTO 表名称1 (c1,c2) VALUE(record1,record2) AS 行别名(列别名1,列别名2) ON DUPLICATE KEY UPDATE c1= 列别名1 + 列别名2;注意：该语句会导致唯一索引或主键中的重复值。
终端操作
1.默认插入
INSERT INTO shqing.SKYRIM_INFO () VALUES();执行结果：
mysql> select * from SKYRIM_INFO;
Empty set (0.00 sec)
mysql> INSERT INTO shqing.SKYRIM_INFO () VALUES();
Query OK, 1 row affected (0.01 sec)
mysql> select * from SKYRIM_INFO;
+----+------+
| id | name |
+----+------+
| 1 | NULL |
+----+------+
1 row in set (0.00 sec)2.正常插入
INSERT INTO shqing.SKYRIM_INFO (name) VALUES('A');执行结果：
INSERT INTO shqing.SKYRIM_INFO (name) VALUES('A');
Query OK, 1 row affected (0.00 sec)
mysql> select * from SKYRIM_INFO;
+----+------+
| id | name |
+----+------+
| 1 | NULL |
| 2 | A |
+----+------+
2 rows in set (0.00 sec)3.正确交叉插入
INSERT INTO shqing.SKYRIM_INFO (name,nick_name,age,language_score,math_score,english_score,comprehensive_score,total_score)VALUES ('B', 'both', 20, 145, 150, 148, 299,shqing.SKYRIM_INFO.language_score +SKYRIM_INFO.math_score +SKYRIM_INFO.english_score +SKYRIM_INFO.comprehensive_score);
执行结果：
mysql> INSERT INTO shqing.SKYRIM_INFO (name, nick_name, age, language_score, math_score, english_score, comprehensive_score,
-> total_score)
-> VALUES ('B', 'both', 20, 145, 150, 148, 299,
-> shqing.SKYRIM_INFO.language_score + SKYRIM_INFO.math_score + SKYRIM_INFO.english_score +
-> SKYRIM_INFO.comprehensive_score);
Query OK, 1 row affected (0.00 sec)
mysql> select * from SKYRIM_INFO;
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| id | name | nick_name | age | language_score | math_score | english_score | comprehensive_score | total_score |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | A | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3 | B | both | 20 | 145 | 150 | 148 | 299 | 742 |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
3 rows in set (0.00 sec)4.非法交叉插入
INSERT INTO shqing.SKYRIM_INFO (name,nick_name,age,language_score,math_score,english_score,comprehensive_score,total_score)values (nick_name,name * 2,age,shqing.SKYRIM_INFO.math_score * 2,200,english_score,comprehensive_score,total_score)
执行结果：
mysql> INSERT INTO shqing.SKYRIM_INFO (name, nick_name, age, language_score, math_score, english_score, comprehensive_score,
-> total_score)
-> values (nick_name,name*2,age, shqing.SKYRIM_INFO.math_score*2, 200, english_score, comprehensive_score,
-> total_score);
Query OK, 1 row affected (0.00 sec)
mysql> select * from SKYRIM_INFO;
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| id | name | nick_name | age | language_score | math_score | english_score | comprehensive_score | total_score |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | A | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3 | B | both | 20 | 145 | 150 | 148 | 299 | 742 |
| 4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 5 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 6 | NULL | NULL | NULL | NULL | 200 | NULL | NULL | NULL |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
6 rows in set (0.00 sec)
注意：我给math_score分配一个值为200，而给language_score分配值为math_score*2，执行结果就是language_score中的值任然为NULL默认值，而math_score的值为200，这种现象就是非法记录值，禁止使用。
5.不指定字段名称插入
INSERT INTO shqing.SKYRIM_INFOvalues (7, 'F', 'for', 20, 149, 151, 152, 301,language_score +SKYRIM_INFO.math_score +SKYRIM_INFO.english_score +SKYRIM_INFO.comprehensive_score);
执行结果：
INSERT INTO shqing.SKYRIM_INFO
-> values (7, 'F', 'for', 20, 149, 151, 152, 301,
-> language_score +
-> SKYRIM_INFO.math_score +
-> SKYRIM_INFO.english_score +
-> SKYRIM_INFO.comprehensive_score);
Query OK, 1 row affected (0.01 sec)
mysql> select * from SKYRIM_INFO;
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| id | name | nick_name | age | language_score | math_score | english_score | comprehensive_score | total_score |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | A | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3 | B | both | 20 | 145 | 150 | 148 | 299 | 742 |
| 4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 5 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 6 | NULL | NULL | NULL | NULL | 200 | NULL | NULL | NULL |
| 7 | F | for | 20 | 149 | 151 | 152 | 301 | 753 |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
7 rows in set (0.00 sec)6.多行插入
INSERT INTO shqing.SKYRIM_INFO (name,
nick_name,
age,
language_score,
math_score,
english_score,
comprehensive_score,
total_score)
values ('E', 'EYE', 20, 141, 131, 142, 311,
language_score +
SKYRIM_INFO.math_score +
SKYRIM_INFO.english_score +
SKYRIM_INFO.comprehensive_score),
('W', 'WOW', 20, 141, 135, 442,211,
language_score +
SKYRIM_INFO.math_score +
SKYRIM_INFO.english_score +
SKYRIM_INFO.comprehensive_score),
('R', 'REW', 10, 241, 134, 342,151,
language_score +
SKYRIM_INFO.math_score +
SKYRIM_INFO.english_score +
SKYRIM_INFO.comprehensive_score),
('T', 'TWO', 21, 121, 145, 132,111,
language_score +
SKYRIM_INFO.math_score +
SKYRIM_INFO.english_score +
SKYRIM_INFO.comprehensive_score);执行结果：
INSERT INTO shqing.SKYRIM_INFO (name,
-> nick_name,
-> age,
-> language_score,
-> math_score,
-> english_score,
-> comprehensive_score,
-> total_score)
-> values ('E', 'EYE', 20, 141, 131, 142, 311,
-> language_score +
-> SKYRIM_INFO.math_score +
-> SKYRIM_INFO.english_score +
-> SKYRIM_INFO.comprehensive_score),
-> ('W', 'WOW', 20, 141, 135, 442,211,
-> language_score +
-> SKYRIM_INFO.math_score +
-> SKYRIM_INFO.english_score +
-> SKYRIM_INFO.comprehensive_score),
-> ('R', 'REW', 10, 241, 134, 342,151,
-> language_score +
-> SKYRIM_INFO.math_score +
-> SKYRIM_INFO.english_score +
-> SKYRIM_INFO.comprehensive_score),
-> ('T', 'TWO', 21, 121, 145, 132,111,
-> language_score +
-> SKYRIM_INFO.math_score +
-> SKYRIM_INFO.english_score +
-> SKYRIM_INFO.comprehensive_score);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from SKYRIM_INFO;
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| id | name | nick_name | age | language_score | math_score | english_score | comprehensive_score | total_score |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | A | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3 | B | both | 20 | 145 | 150 | 148 | 299 | 742 |
| 4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 5 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 6 | NULL | NULL | NULL | NULL | 200 | NULL | NULL | NULL |
| 7 | F | for | 20 | 149 | 151 | 152 | 301 | 753 |
| 8 | E | EYE | 20 | 141 | 131 | 142 | 311 | 725 |
| 9 | W | WOW | 20 | 141 | 135 | 442 | 211 | 929 |
| 10 | R | REW | 10 | 241 | 134 | 342 | 151 | 868 |
| 11 | T | TWO | 21 | 121 | 145 | 132 | 111 | 509 |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
11 rows in set (0.00 sec)7.无效的多行插入
INSERT INTO shqing.SKYRIM_INFOvalues (7, 'F', 'for', 20, 149, 151, 152, 301,language_score +SKYRIM_INFO.math_score +SKYRIM_INFO.english_score +SKYRIM_INFO.comprehensive_score,'s');
执行结果：
mysql> INSERT INTO shqing.SKYRIM_INFO-> values (7, 'F', 'for', 20, 149, 151, 152, 301,-> language_score +-> SKYRIM_INFO.math_score +-> SKYRIM_INFO.english_score +-> SKYRIM_INFO.comprehensive_score,'s');ERROR 1136 (21S01): Column count doesn't match value count at row 1
8.ROW()函数插入
1.单行插入
INSERT INTO shqing.SKYRIM_INFO (name,
nick_name,
age,
language_score,
math_score,
english_score,
comprehensive_score,
total_score)
VALUES ROW('N',
'NAN',
18,
123,
100,
124,
1234,
language_score +
SKYRIM_INFO.math_score +
SKYRIM_INFO.english_score +
SKYRIM_INFO.comprehensive_score);
2.多行插入
INSERT INTO shqing.SKYRIM_INFO (name,
nick_name,
age,
language_score,
math_score,
english_score,
comprehensive_score,
total_score)
VALUES ROW ('H',
'HA',
18,
121,
120,
134,
134,
language_score +
SKYRIM_INFO.math_score +
SKYRIM_INFO.english_score +
SKYRIM_INFO.comprehensive_score),
ROW ('M',
'MAR',
19,
153,
140,
144,
134,
language_score +
SKYRIM_INFO.math_score +
SKYRIM_INFO.english_score +
SKYRIM_INFO.comprehensive_score);
3.单行构造表
VAlUES ROW(1,2,3);
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
| 1 | 2 | 3 |
+----------+----------+----------+
1 row in set (0.00 sec)
4.多行构造表
VAlUES ROW(1,2,3), ROW(4,5,6), ROW(12,13,14);
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
| 1 | 2 | 3 |
| 4 | 5 | 6 |
| 12 | 13 | 14 |
+----------+----------+----------+
3 rows in set (0.00 sec)
执行结果：
1.单行插入
INSERT INTO shqing.SKYRIM_INFO (name,
-> nick_name,
-> age,
-> language_score,
-> math_score,
-> english_score,
-> comprehensive_score,
-> total_score)
-> VALUES ROW('N',
-> 'NAN',
-> 18,
-> 123,
-> 100,
-> 124,
-> 1234,
-> language_score +
-> SKYRIM_INFO.math_score +
-> SKYRIM_INFO.english_score +
-> SKYRIM_INFO.comprehensive_score);
Query OK, 1 row affected (0.00 sec)
mysql> select * from SKYRIM_INFO;
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| id | name | nick_name | age | language_score | math_score | english_score | comprehensive_score | total_score |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | A | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3 | B | both | 20 | 145 | 150 | 148 | 299 | 742 |
| 4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 5 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 6 | NULL | NULL | NULL | NULL | 200 | NULL | NULL | NULL |
| 7 | F | for | 20 | 149 | 151 | 152 | 301 | 753 |
| 8 | E | EYE | 20 | 141 | 131 | 142 | 311 | 725 |
| 9 | W | WOW | 20 | 141 | 135 | 442 | 211 | 929 |
| 10 | R | REW | 10 | 241 | 134 | 342 | 151 | 868 |
| 11 | T | TWO | 21 | 121 | 145 | 132 | 111 | 509 |
| 12 | N | NAN | 18 | 123 | 100 | 124 | 1234 | 1581 |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
12 rows in set (0.00 sec)
2.多行插入
INSERT INTO shqing.SKYRIM_INFO (name,
-> nick_name,
-> age,
-> language_score,
-> math_score,
-> english_score,
-> comprehensive_score,
-> total_score)
-> VALUES ROW ('H',
-> 'HA',
-> 18,
-> 121,
-> 120,
-> 134,
-> 134,
-> language_score +
-> SKYRIM_INFO.math_score +
-> SKYRIM_INFO.english_score +
-> SKYRIM_INFO.comprehensive_score),
-> ROW ('M',
-> 'MAR',
-> 19,
-> 153,
-> 140,
-> 144,
-> 134,
-> language_score +
-> SKYRIM_INFO.math_score +
-> SKYRIM_INFO.english_score +
-> SKYRIM_INFO.comprehensive_score);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from SKYRIM_INFO;
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| id | name | nick_name | age | language_score | math_score | english_score | comprehensive_score | total_score |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | A | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3 | B | both | 20 | 145 | 150 | 148 | 299 | 742 |
| 4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 5 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 6 | NULL | NULL | NULL | NULL | 200 | NULL | NULL | NULL |
| 7 | F | for | 20 | 149 | 151 | 152 | 301 | 753 |
| 8 | E | EYE | 20 | 141 | 131 | 142 | 311 | 725 |
| 9 | W | WOW | 20 | 141 | 135 | 442 | 211 | 929 |
| 10 | R | REW | 10 | 241 | 134 | 342 | 151 | 868 |
| 11 | T | TWO | 21 | 121 | 145 | 132 | 111 | 509 |
| 12 | N | NAN | 18 | 123 | 100 | 124 | 1234 | 1581 |
| 13 | H | HA | 18 | 121 | 120 | 134 | 134 | 509 |
| 14 | M | MAR | 19 | 153 | 140 | 144 | 134 | 571 |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
14 rows in set (0.00 sec)9.SET插入
INSERT INTO shqing.SKYRIM_INFO
SET name='J',
nick_name='JET',
shqing.SKYRIM_INFO.age=100,
language_score=120,
shqing.SKYRIM_INFO.math_score=130,
shqing.SKYRIM_INFO.english_score=139,
shqing.SKYRIM_INFO.comprehensive_score=300,
shqing.SKYRIM_INFO.total_score=shqing.SKYRIM_INFO.language_score + SKYRIM_INFO.math_score +
SKYRIM_INFO.english_score + SKYRIM_INFO.comprehensive_score;执行结果：
mysql> select * from shqing.SKYRIM_INFO;
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| id | name | nick_name | age | language_score | math_score | english_score | comprehensive_score | total_score |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | A | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3 | B | both | 20 | 145 | 150 | 148 | 299 | 742 |
| 4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 5 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 6 | NULL | NULL | NULL | NULL | 200 | NULL | NULL | NULL |
| 7 | F | for | 20 | 149 | 151 | 152 | 301 | 753 |
| 8 | E | EYE | 20 | 141 | 131 | 142 | 311 | 725 |
| 9 | W | WOW | 20 | 141 | 135 | 442 | 211 | 929 |
| 10 | R | REW | 10 | 241 | 134 | 342 | 151 | 868 |
| 11 | T | TWO | 21 | 121 | 145 | 132 | 111 | 509 |
| 12 | N | NAN | 18 | 123 | 100 | 124 | 1234 | 1581 |
| 13 | H | HA | 18 | 121 | 120 | 134 | 134 | 509 |
| 14 | M | MAR | 19 | 153 | 140 | 144 | 134 | 571 |
| 15 | J | JET | 100 | 120 | 130 | 139 | 300 | 689 |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
15 rows in set (0.01 sec)10.省略into插入
INSERT shqing.SKYRIM_INFO (name, nick_name, age, language_score, math_score, english_score, comprehensive_score, total_score)
values ('cancel',
'EVENT',
1,
12,
100,
120,
260,
shqing.SKYRIM_INFO.language_score +
SKYRIM_INFO.math_score +
SKYRIM_INFO.english_score +
SKYRIM_INFO.comprehensive_score);执行结果：
+----+--------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| id | name | nick_name | age | language_score | math_score | english_score | comprehensive_score | total_score |
+----+--------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | A | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3 | B | both | 20 | 145 | 150 | 148 | 299 | 742 |
| 4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 5 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 6 | NULL | NULL | NULL | NULL | 200 | NULL | NULL | NULL |
| 7 | F | for | 20 | 149 | 151 | 152 | 301 | 753 |
| 8 | E | EYE | 20 | 141 | 131 | 142 | 311 | 725 |
| 9 | W | WOW | 20 | 141 | 135 | 442 | 211 | 929 |
| 10 | R | REW | 10 | 241 | 134 | 342 | 151 | 868 |
| 11 | T | TWO | 21 | 121 | 145 | 132 | 111 | 509 |
| 12 | N | NAN | 18 | 123 | 100 | 124 | 1234 | 1581 |
| 13 | H | HA | 18 | 121 | 120 | 134 | 134 | 509 |
| 14 | M | MAR | 19 | 153 | 140 | 144 | 134 | 571 |
| 15 | J | JET | 100 | 120 | 130 | 139 | 300 | 689 |
| 16 | cancel | EVENT | 1 | 12 | 100 | 120 | 260 | 492 |
| 17 | cancel | EVENT | 1 | 12 | 100 | 120 | 260 | 492 |
+----+--------+-----------+------+----------------+------------+---------------+---------------------+-------------+
17 rows in set (0.00 sec)11.省略into使用vaule插入
1.指定全部字段
INSERT shqing.SKYRIM_INFO (id, name, nick_name, age, language_score, math_score, english_score, comprehensive_score,
total_score)
value (18,'save',
'SAVE',
10,
140,
120,
125,
269,
shqing.SKYRIM_INFO.language_score +
SKYRIM_INFO.math_score +
SKYRIM_INFO.english_score +
SKYRIM_INFO.comprehensive_score);
2.指定需要的字段
INSERT shqing.SKYRIM_INFO (name, nick_name, age, language_score, math_score, english_score, comprehensive_score,
total_score)
value
('services',
'SERVICES',
10,
141,
122,
185,
299,
shqing.SKYRIM_INFO.language_score +
SKYRIM_INFO.math_score +
SKYRIM_INFO.english_score +
SKYRIM_INFO.comprehensive_score); 执行结果：
1.指定全部字段
select * from shqing.SKYRIM_INFO;
+----+--------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| id | name | nick_name | age | language_score | math_score | english_score | comprehensive_score | total_score |
+----+--------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | A | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3 | B | both | 20 | 145 | 150 | 148 | 299 | 742 |
| 4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 5 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 6 | NULL | NULL | NULL | NULL | 200 | NULL | NULL | NULL |
| 7 | F | for | 20 | 149 | 151 | 152 | 301 | 753 |
| 8 | E | EYE | 20 | 141 | 131 | 142 | 311 | 725 |
| 9 | W | WOW | 20 | 141 | 135 | 442 | 211 | 929 |
| 10 | R | REW | 10 | 241 | 134 | 342 | 151 | 868 |
| 11 | T | TWO | 21 | 121 | 145 | 132 | 111 | 509 |
| 12 | N | NAN | 18 | 123 | 100 | 124 | 1234 | 1581 |
| 13 | H | HA | 18 | 121 | 120 | 134 | 134 | 509 |
| 14 | M | MAR | 19 | 153 | 140 | 144 | 134 | 571 |
| 15 | J | JET | 100 | 120 | 130 | 139 | 300 | 689 |
| 16 | cancel | EVENT | 1 | 12 | 100 | 120 | 260 | 492 |
| 17 | cancel | EVENT | 1 | 12 | 100 | 120 | 260 | 492 |
| 18 | save | SAVE | 10 | 140 | 120 | 125 | 269 | 654 |
+----+--------+-----------+------+----------------+------------+---------------+---------------------+-------------+
18 rows in set (0.00 sec)2.指定需要的字段
select * from shqing.SKYRIM_INFO;
+----+----------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| id | name | nick_name | age | language_score | math_score | english_score | comprehensive_score | total_score |
+----+----------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | A | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3 | B | both | 20 | 145 | 150 | 148 | 299 | 742 |
| 4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 5 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 6 | NULL | NULL | NULL | NULL | 200 | NULL | NULL | NULL |
| 7 | F | for | 20 | 149 | 151 | 152 | 301 | 753 |
| 8 | E | EYE | 20 | 141 | 131 | 142 | 311 | 725 |
| 9 | W | WOW | 20 | 141 | 135 | 442 | 211 | 929 |
| 10 | R | REW | 10 | 241 | 134 | 342 | 151 | 868 |
| 11 | T | TWO | 21 | 121 | 145 | 132 | 111 | 509 |
| 12 | N | NAN | 18 | 123 | 100 | 124 | 1234 | 1581 |
| 13 | H | HA | 18 | 121 | 120 | 134 | 134 | 509 |
| 14 | M | MAR | 19 | 153 | 140 | 144 | 134 | 571 |
| 15 | J | JET | 100 | 120 | 130 | 139 | 300 | 689 |
| 16 | cancel | EVENT | 1 | 12 | 100 | 120 | 260 | 492 |
| 17 | cancel | EVENT | 1 | 12 | 100 | 120 | 260 | 492 |
| 18 | save | SAVE | 10 | 140 | 120 | 125 | 269 | 654 |
| 19 | services | SERVICES | 10 | 141 | 122 | 185 | 299 | 747 |
+----+----------+-----------+------+----------------+------------+---------------+---------------------+-------------+
19 rows in set (0.00 sec)12.使用vaule插入
INSERT INTO shqing.SKYRIM_INFO (name) value ('CHAOS');执行结果：
+-------+
| name |
+-------+
| CHAOS |
+-------+
1 row in set (0.00 sec)13.使用select插入
INSERT INTO shqing.SKYRIM_INFO (name) SELECT name FROM shqing.CHAOS_INFO;执行结果：
INSERT INTO shqing.SKYRIM_INFO (name) SELECT name FROM shqing.CHAOS_INFO;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select name from SKYRIM_INFO where name='A' AND id=21;
+------+
| name |
+------+
| A |
+------+
1 row in set (0.00 sec)14.使用TABLE插入
1.查空表
select * from CHAOS_INFO;
Empty set (0.00 sec)
2.查有数据表
select * from SKYRIM_INFO;
+----+----------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| id | name | nick_name | age | language_score | math_score | english_score | comprehensive_score | total_score |
+----+----------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | A | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3 | B | both | 20 | 145 | 150 | 148 | 299 | 742 |
| 4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 5 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 6 | NULL | NULL | NULL | NULL | 200 | NULL | NULL | NULL |
| 7 | F | for | 20 | 149 | 151 | 152 | 301 | 753 |
| 8 | E | EYE | 20 | 141 | 131 | 142 | 311 | 725 |
| 9 | W | WOW | 20 | 141 | 135 | 442 | 211 | 929 |
| 10 | R | REW | 10 | 241 | 134 | 342 | 151 | 868 |
| 11 | T | TWO | 21 | 121 | 145 | 132 | 111 | 509 |
| 12 | N | NAN | 18 | 123 | 100 | 124 | 1234 | 1581 |
| 13 | H | HA | 18 | 121 | 120 | 134 | 134 | 509 |
| 14 | M | MAR | 19 | 153 | 140 | 144 | 134 | 571 |
| 15 | J | JET | 100 | 120 | 130 | 139 | 300 | 689 |
| 16 | cancel | EVENT | 1 | 12 | 100 | 120 | 260 | 492 |
| 17 | cancel | EVENT | 1 | 12 | 100 | 120 | 260 | 492 |
| 18 | save | SAVE | 10 | 140 | 120 | 125 | 269 | 654 |
| 19 | services | SERVICES | 10 | 141 | 122 | 185 | 299 | 747 |
| 20 | CHAOS | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 21 | A | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+----+----------+-----------+------+----------------+------------+---------------+---------------------+-------------+
21 rows in set (0.00 sec)
3.将表SKYRIM_INFO的数据复制到表CHAOS_INFO中
INSERT INTO shqing.CHAOS_INFO TABLE shqing.SKYRIM_INFO;
Query OK, 21 rows affected (0.01 sec)
Records: 21 Duplicates: 0 Warnings: 0
4.查看复制数据的结果
select * from CHAOS_INFO;
+----+----------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| id | name | nick_name | age | language_score | math_score | english_score | comprehensive_score | total_score |
+----+----------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | A | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3 | B | both | 20 | 145 | 150 | 148 | 299 | 742 |
| 4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 5 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 6 | NULL | NULL | NULL | NULL | 200 | NULL | NULL | NULL |
| 7 | F | for | 20 | 149 | 151 | 152 | 301 | 753 |
| 8 | E | EYE | 20 | 141 | 131 | 142 | 311 | 725 |
| 9 | W | WOW | 20 | 141 | 135 | 442 | 211 | 929 |
| 10 | R | REW | 10 | 241 | 134 | 342 | 151 | 868 |
| 11 | T | TWO | 21 | 121 | 145 | 132 | 111 | 509 |
| 12 | N | NAN | 18 | 123 | 100 | 124 | 1234 | 1581 |
| 13 | H | HA | 18 | 121 | 120 | 134 | 134 | 509 |
| 14 | M | MAR | 19 | 153 | 140 | 144 | 134 | 571 |
| 15 | J | JET | 100 | 120 | 130 | 139 | 300 | 689 |
| 16 | cancel | EVENT | 1 | 12 | 100 | 120 | 260 | 492 |
| 17 | cancel | EVENT | 1 | 12 | 100 | 120 | 260 | 492 |
| 18 | save | SAVE | 10 | 140 | 120 | 125 | 269 | 654 |
| 19 | services | SERVICES | 10 | 141 | 122 | 185 | 299 | 747 |
| 20 | CHAOS | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 21 | A | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+----+----------+-----------+------+----------------+------------+---------------+---------------------+-------------+
21 rows in set (0.00 sec)注意：
复制之前必须准备一个空表并且字段与对应表中的字段保持一致。15.使用on插入
INSERT INTO shqing.SKYRIM_INFO (age, language_score, math_score, english_score)
VALUES (1, 2, 3, 4)
ON DUPLICATE KEY UPDATE english_score = english_score + 6;执行结果：
select * from SKYRIM_INFO where id = 37;
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| id | name | nick_name | age | language_score | math_score | english_score | comprehensive_score | total_score |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| 37 | NULL | NULL | 1 | 2 | 3 | 10 | NULL | NULL |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
1 row in set (0.00 sec)16.行别名插入
INSERT INTO shqing.SKYRIM_INFO (age, language_score, math_score, english_score)
VALUES (1, 2, 3, 4) AS sh
ON DUPLICATE KEY UPDATE english_score = sh.language_score+sh.math_score+sh.age;执行结果：
select * from SKYRIM_INFO where id = 38;
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| id | name | nick_name | age | language_score | math_score | english_score | comprehensive_score | total_score |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| 38 | NULL | NULL | 1 | 2 | 3 | 6 | NULL | NULL |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
1 row in set (0.00 sec)17.行别名+列别名插入
INSERT INTO shqing.SKYRIM_INFO (age, language_score, math_score, english_score)
VALUES (1, 2, 3, 4) AS sh(a,b,c,d)
ON DUPLICATE KEY UPDATE english_score = a+b+c;执行结果：
mysql> select * from SKYRIM_INFO where id = 39;
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| id | name | nick_name | age | language_score | math_score | english_score | comprehensive_score | total_score |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| 39 | NULL | NULL | 1 | 2 | 3 | 6 | NULL | NULL |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
1 row in set (0.00 sec)18.VALUES(column_name)插入(可以多行操作，推荐使用）
单行操作
INSERT INTO shqing.SKYRIM_INFO (age, language_score, math_score, english_score)
VALUES (1, 2, 3, 4)
ON DUPLICATE KEY UPDATE english_score = VALUES(language_score) + VALUES(math_score);
多行操作
INSERT INTO shqing.SKYRIM_INFO (age, language_score, math_score, english_score)
VALUES (1, 2, 3, 4),
(5, 6, 7, 8),
(11, 22, 33, 44)
ON DUPLICATE KEY UPDATE english_score = VALUES(language_score) + VALUES(math_score);
执行结果：
单行操作
mysql> select * from SKYRIM_INFO where id = 40;
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| id | name | nick_name | age | language_score | math_score | english_score | comprehensive_score | total_score |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| 40 | NULL | NULL | 1 | 2 | 3 | 5 | NULL | NULL |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
1 row in set (0.00 sec)
多行操作
mysql> INSERT INTO shqing.SKYRIM_INFO (age, language_score, math_score, english_score)
-> VALUES (1, 2, 3, 4),
-> (5, 6, 7, 8),
-> (11, 22, 33, 44)
-> ON DUPLICATE KEY UPDATE english_score = VALUES(language_score) + VALUES(math_score);
Query OK, 3 rows affected, 2 warnings (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 2
mysql> select * from SKYRIM_INFO where id in (43,44,45);
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| id | name | nick_name | age | language_score | math_score | english_score | comprehensive_score | total_score |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
| 43 | NULL | NULL | 1 | 2 | 3 | 5 | NULL | NULL |
| 44 | NULL | NULL | 5 | 6 | 7 | 13 | NULL | NULL |
| 45 | NULL | NULL | 11 | 22 | 33 | 55 | NULL | NULL |
+----+------+-----------+------+----------------+------------+---------------+---------------------+-------------+
3 rows in set (0.00 sec)
Python实现插入记录
import pymysql
def useDb(curses):
# 执行SQL语句
curses.execute('use shqing')
def insertDb(curses):
sql_default = "INSERT INTO shqing.SKYRIM_INFO () VALUES();"
sql_normal = "INSERT INTO shqing.SKYRIM_INFO (name) VALUES(name);"
sql_cross = "INSERT INTO shqing.SKYRIM_INFO (name,nick_name,age,language_score,math_score,english_score," \
"comprehensive_score,total_score) VALUES ('B', ' * ', 20, 145, 150, 148, 299, " \
"shqing.SKYRIM_INFO.language_score + SKYRIM_INFO.math_score + SKYRIM_INFO.english_score + " \
"SKYRIM_INFO.comprehensive_score); "
sql_illegal_crossing = "INSERT INTO shqing.SKYRIM_INFO (name,nick_name,age,language_score,math_score," \
"english_score,comprehensive_score,total_score) values (nick_name, name * 2, age, " \
"shqing.SKYRIM_INFO.math_score * 2, 200, english_score, comprehensive_score,total_score) "
sql_without_specifying_field = "INSERT INTO shqing.SKYRIM_INFO values (67, 'F', 'For', 29, 199, 150, 159, 381," \
"language_score + SKYRIM_INFO.math_score + SKYRIM_INFO.english_score + " \
"SKYRIM_INFO.comprehensive_score); "
sql_multi_lines = "INSERT INTO shqing.SKYRIM_INFO (name, nick_name, age, language_score, math_score," \
"english_score,comprehensive_score,total_score) " \
"values ('O', 'OO', 25, 148, 181, 162, 211,language_score + SKYRIM_INFO.math_score " \
"+SKYRIM_INFO.english_score +SKYRIM_INFO.comprehensive_score)," \
"('U', 'UN', 30, 161, 135, 442,211,language_score +SKYRIM_INFO.math_score " \
"+SKYRIM_INFO.english_score +SKYRIM_INFO.comprehensive_score)," \
"('K', 'K8S', 19, 244, 134, 342,151,language_score +SKYRIM_INFO.math_score " \
"+SKYRIM_INFO.english_score +SKYRIM_INFO.comprehensive_score)," \
"('M', 'MOVE', 21, 126, 148, 192,151,language_score +SKYRIM_INFO.math_score " \
"+SKYRIM_INFO.english_score +SKYRIM_INFO.comprehensive_score); "
sql_row_single_line = "INSERT INTO shqing.SKYRIM_INFO (name,nick_name,age,language_score, math_score," \
"english_score,comprehensive_score,total_score) VALUES ROW('L','LOVE',28,133,120,144,234," \
"language_score +SKYRIM_INFO.math_score +SKYRIM_INFO.english_score + " \
"SKYRIM_INFO.comprehensive_score); "
sql_row_multi_lines = "INSERT INTO shqing.SKYRIM_INFO (name,nick_name,age,language_score, math_score," \
"english_score,comprehensive_score,total_score) VALUES ROW('L','LOVE',28,133,120,144,234," \
"language_score +SKYRIM_INFO.math_score +SKYRIM_INFO.english_score + " \
"SKYRIM_INFO.comprehensive_score)," \
"ROW('M', 'MOVE', 21, 126, 148, 192,151,language_score +SKYRIM_INFO.math_score " \
"+SKYRIM_INFO.english_score +SKYRIM_INFO.comprehensive_score); "
sql_structure_single_line = "VAlUES ROW(1,2,3);"
sql_structure_multi_lines = "VAlUES ROW(1,2,3), ROW(4,5,6), ROW(7,8,9);"
sql_set_insert = "INSERT INTO shqing.SKYRIM_INFO SET name='J', nick_name='JET',shqing.SKYRIM_INFO.age=20," \
"language_score=109,shqing.SKYRIM_INFO.math_score=108,shqing.SKYRIM_INFO.english_score=112," \
"shqing.SKYRIM_INFO.comprehensive_score=200," \
"shqing.SKYRIM_INFO.total_score=shqing.SKYRIM_INFO.language_score + SKYRIM_INFO.math_score " \
"+SKYRIM_INFO.english_score + SKYRIM_INFO.comprehensive_score; "
sql_omit_into = "INSERT shqing.SKYRIM_INFO (name, nick_name, age, language_score, math_score, english_score, " \
"comprehensive_score, total_score)values ('ok', 'okay', 13,120,150,130,250," \
"shqing.SKYRIM_INFO.language_score +SKYRIM_INFO.math_score +SKYRIM_INFO.english_score + " \
"SKYRIM_INFO.comprehensive_score); "
sql_specify_all_col = "INSERT shqing.SKYRIM_INFO (id,name, nick_name, age, language_score, math_score, " \
"english_score, " \
"comprehensive_score, total_score) value (69,'POW', 'POWER', 15,130,155,160,290," \
"shqing.SKYRIM_INFO.language_score +SKYRIM_INFO.math_score +SKYRIM_INFO.english_score + " \
"SKYRIM_INFO.comprehensive_score); "
sql_specify_local_col = "INSERT shqing.SKYRIM_INFO (language_score, math_score, english_score, " \
"comprehensive_score, total_score) value (10,15,16,20," \
"shqing.SKYRIM_INFO.language_score +SKYRIM_INFO.math_score +SKYRIM_INFO.english_score + " \
"SKYRIM_INFO.comprehensive_score); "
sql_value = "INSERT INTO shqing.SKYRIM_INFO (name) value ('world');"
sql_select = "INSERT INTO shqing.SKYRIM_INFO (name) SELECT name FROM shqing.CHAOS_INFO;"
sql_table = "INSERT INTO shqing.CHAOS_INFO TABLE shqing.SKYRIM_INFO;"
sql_on = "INSERT INTO shqing.SKYRIM_INFO (age, language_score, math_score, english_score) VALUES (1, 2, 3, " \
"4) ON DUPLICATE KEY UPDATE english_score = english_score + 6; "
sql_row_alias = "INSERT INTO shqing.SKYRIM_INFO (age, language_score, math_score, english_score) " \
"VALUES (10, 20, 30, 40) AS sh ON DUPLICATE KEY UPDATE english_score = " \
"sh.language_score+sh.math_score+sh.age; "
sql_row_col_alias = "INSERT INTO shqing.SKYRIM_INFO (age, language_score, math_score, english_score) " \
"VALUES (11, 22, 33, 44) AS sh(a,b,c,d) ON DUPLICATE KEY UPDATE english_score = a+b+c; "
sql_values_single_opt = "INSERT INTO shqing.SKYRIM_INFO (age, language_score, math_score, english_score) " \
"VALUES (100, 200, 300, 400) ON DUPLICATE KEY UPDATE english_score = VALUES(" \
"language_score) + VALUES(math_score); "
sql_values_multi_opt = "INSERT INTO shqing.SKYRIM_INFO (age, language_score, math_score, english_score) " \
"VALUES (100, 200, 300, 400),(101, 201, 301, 401),(102, 202, 302, 402) " \
"ON DUPLICATE KEY UPDATE english_score = VALUES(language_score) + VALUES(math_score); "
curses.execute(sql_default)
curses.execute(sql_normal)
curses.execute(sql_cross)
curses.execute(sql_illegal_crossing)
curses.execute(sql_without_specifying_field)
curses.execute(sql_multi_lines)
curses.execute(sql_row_single_line)
curses.execute(sql_row_multi_lines)
curses.execute(sql_structure_single_line)
curses.execute(sql_structure_multi_lines)
curses.execute(sql_set_insert)
curses.execute(sql_omit_into)
curses.execute(sql_specify_all_col)
curses.execute(sql_specify_local_col)
curses.execute(sql_value)
curses.execute(sql_select)
curses.execute(sql_table)
curses.execute(sql_on)
curses.execute(sql_row_alias)
curses.execute(sql_row_col_alias)
curses.execute(sql_values_single_opt)
curses.execute(sql_values_multi_opt)
def connAndExecuteSqlStatement(host, user, password, database):
# 连接数据库
connection = pymysql.Connect(host=host, user=user, password=password, database=database)
# 获取执行游标
curses = connection.cursor()
# 使用数据库
useDb(curses)
# 插入数据
insertDb(curses)
# 关闭游标
curses.close()
# 关闭连接
connection.close()
if __name__ == '__main__':
HOST = '127.0.0.1'
USER = 'root'
PASSWORD = '*'
DATABASE = 'mysql'
connAndExecuteSqlStatement(HOST, USER, PASSWORD, DATABASE)
Java实现插入记录
switch (stat.executeUpdate("INSERT INTO shqing.SKYRIM_INFO () VALUES();")) {
case 0 -> System.out.println("默认插入OK");
}
switch (stat.executeUpdate("INSERT INTO shqing.SKYRIM_INFO (name) VALUES('A');")) {
case 0 -> System.out.println("正常插入OK");
}
switch (stat.executeUpdate("INSERT INTO shqing.SKYRIM_INFO (name, nick_name, age, language_score, math_score,english_score,comprehensive_score,total_score) " +
"VALUES ('B', 'both', 20, 145, 150, 148, 299,shqing.SKYRIM_INFO.language_score + SKYRIM_INFO.math_score + SKYRIM_INFO.english_score + SKYRIM_INFO.comprehensive_score);")) {
case 0 -> System.out.println("正常交叉插入OK");
}
switch (stat.executeUpdate("INSERT INTO shqing.SKYRIM_INFO (name, nick_name, age, language_score, math_score, english_score, comprehensive_score, total_score) " +
"values (nick_name, name * 2, age, shqing.SKYRIM_INFO.math_score * 2, 200, english_score, comprehensive_score,total_score)")) {
case 0 -> System.out.println("非法交叉插入OK，注意插入的数据");
}
switch (stat.executeUpdate("INSERT INTO shqing.SKYRIM_INFO " +
"values (7, 'F', 'for', 20, 149, 151, 152, 301, language_score + SKYRIM_INFO.math_score +SKYRIM_INFO.english_score +SKYRIM_INFO.comprehensive_score);")) {
case 0 -> System.out.println("不指定字段名称插入OK");
}
switch (stat.executeUpdate("INSERT INTO shqing.SKYRIM_INFO (name, nick_name, age, language_score, math_score, english_score, comprehensive_score, total_score) " +
"values " +
"('E', 'EYE', 20, 141, 131, 142, 311,language_score + SKYRIM_INFO.math_score + SKYRIM_INFO.english_score + SKYRIM_INFO.comprehensive_score), " +
"('W', 'WOW', 20, 141, 135, 442,211,language_score + SKYRIM_INFO.math_score + SKYRIM_INFO.english_score + SKYRIM_INFO.comprehensive_score)," +
"('R', 'REW', 10, 241, 134, 342,151,language_score + SKYRIM_INFO.math_score + SKYRIM_INFO.english_score + SKYRIM_INFO.comprehensive_score)," +
"('T', 'TWO', 21, 121, 145, 132,111, language_score + SKYRIM_INFO.math_score + SKYRIM_INFO.english_score + SKYRIM_INFO.comprehensive_score);")) {
case 0 -> System.out.println("多行插入OK");
}
switch (stat.executeUpdate("INSERT INTO shqing.SKYRIM_INFO " +
"values (7, 'F', 'for', 20, 149, 151, 152, 301,language_score + SKYRIM_INFO.math_score + SKYRIM_INFO.english_score + SKYRIM_INFO.comprehensive_score,'s');")) {
case 0 -> System.out.println("无效插入，会报错。");
}
switch (stat.executeUpdate("INSERT INTO shqing.SKYRIM_INFO (name,nick_name,age,language_score,math_score,english_score,comprehensive_score,total_score)" +
"VALUES ROW('N','NAN',18,123,100,124,1234,language_score + SKYRIM_INFO.math_score + SKYRIM_INFO.english_score +SKYRIM_INFO.comprehensive_score);")) {
case 0 -> System.out.println("ROW()单行插入OK");
}
switch (stat.executeUpdate("INSERT INTO shqing.SKYRIM_INFO (name,nick_name,age,language_score,math_score,english_score,comprehensive_score,total_score) " +
"VALUES ROW ('H','HA',\18,121,120,134,134,language_score + SKYRIM_INFO.math_score + SKYRIM_INFO.english_score + SKYRIM_INFO.comprehensive_score)," +
"ROW ('M','MAR',19,153,140,144,134,language_score + SKYRIM_INFO.math_score + SKYRIM_INFO.english_score + SKYRIM_INFO.comprehensive_score);")) {
case 0 -> System.out.println("ROW()多行插入OK");
}
switch (stat.executeUpdate(" VAlUES ROW(1,2,3);")) {
case 0 -> System.out.println("ROW()单行构造失败，IDE不支持。");
}
switch (stat.executeUpdate(" VAlUES ROW(1,2,3), ROW(4,5,6), ROW(12,13,14);")) {
case 0 -> System.out.println("ROW()多行构造失败，IDE不支持。");
}
switch (stat.executeUpdate("INSERT INTO shqing.SKYRIM_INFO " +
"SET name='K', nick_name='KO', shqing.SKYRIM_INFO.age=120, " +
"language_score=220, shqing.SKYRIM_INFO.math_score=120," +
"shqing.SKYRIM_INFO.english_score=119, shqing.SKYRIM_INFO.comprehensive_score=200, " +
"shqing.SKYRIM_INFO.total_score=shqing.SKYRIM_INFO.language_score + SKYRIM_INFO.math_score + " +
"SKYRIM_INFO.english_score + SKYRIM_INFO.comprehensive_score;")) {
case 0 -> System.out.println("set插入OK");
}
switch (stat.executeUpdate("INSERT shqing.SKYRIM_INFO (name, nick_name, age, language_score, math_score, english_score, comprehensive_score, total_score) " +
"values ('cancel','EVENT',10,102,100,120,260,shqing.SKYRIM_INFO.language_score + SKYRIM_INFO.math_score + SKYRIM_INFO.english_score + SKYRIM_INFO.comprehensive_score);")) {
case 0 -> System.out.println("省略into插入OK");
}
switch (stat.executeUpdate("INSERT shqing.SKYRIM_INFO (id, name, nick_name, age, language_score, math_score, english_score, comprehensive_score,total_score) " +
"value (18,'save','SAVE',10,140,120,125,269,shqing.SKYRIM_INFO.language_score + SKYRIM_INFO.math_score + SKYRIM_INFO.english_score + SKYRIM_INFO.comprehensive_score);")) {
case 0 -> System.out.println("省略into使用vaule并指定全部字段插入OK");
}
switch (stat.executeUpdate("INSERT shqing.SKYRIM_INFO (name, nick_name, age, language_score, math_score, english_score, comprehensive_score, total_score) " +
"value('services','SERVICES',10,141,122,185,299," +
"shqing.SKYRIM_INFO.language_score + SKYRIM_INFO.math_score + SKYRIM_INFO.english_score + SKYRIM_INFO.comprehensive_score);")) {
case 0 -> System.out.println("省略into使用vaule并指定需要的字段插入OK");
}
switch (stat.executeUpdate("INSERT INTO shqing.SKYRIM_INFO (name) value ('CHAOS');")) {
case 0 -> System.out.println("使用value插入OK");
}
switch (stat.executeUpdate("INSERT INTO shqing.SKYRIM_INFO (name) SELECT name FROM shqing.CHAOS_INFO;")) {
case 0 -> System.out.println("使用select插入OK");
}
switch (stat.executeUpdate("INSERT INTO shqing.CHAOS_INFO TABLE shqing.SKYRIM_INFO;")) {
case 0 -> System.out.println("使用TABLE插入OK");
}
switch (stat.executeUpdate("INSERT INTO shqing.SKYRIM_INFO (age, language_score, math_score, english_score) " +
"VALUES (1, 2, 3, 4) ON DUPLICATE KEY UPDATE english_score = english_score + 6;")) {
case 0 -> System.out.println("使用on插入OK");
}
switch (stat.executeUpdate("INSERT INTO shqing.SKYRIM_INFO (age, language_score, math_score, english_score) " +
"VALUES (1, 2, 3, 4) AS sh ON DUPLICATE KEY UPDATE english_score = sh.language_score+sh.math_score+sh.age;")) {
case 0 -> System.out.println("行别名插入时，IDE不支持，只能在终端演示。");
}
switch (stat.executeUpdate("INSERT INTO shqing.SKYRIM_INFO (age, language_score, math_score, english_score) " +
"VALUES (1, 2, 3, 4) AS sh(a,b,c,d) ON DUPLICATE KEY UPDATE english_score = a+b+c;")) {
case 0 -> System.out.println("行别名+列别名插入时，IDE不支持，只能在终端演示。");
}
switch (stat.executeUpdate("INSERT INTO shqing.SKYRIM_INFO (age, language_score, math_score, english_score) " +
"VALUES (1, 2, 3, 4) ON DUPLICATE KEY UPDATE english_score = VALUES(language_score) + VALUES(math_score);")) {
case 0 -> System.out.println("VALUES(column_name)单行插入OK");
}
switch (stat.executeUpdate("INSERT INTO shqing.SKYRIM_INFO (age, language_score, math_score, english_score) " +
"VALUES (1, 2, 3, 4),(5, 6, 7, 8),(11, 22, 33, 44) ON DUPLICATE KEY UPDATE english_score = VALUES(language_score) + VALUES(math_score);")) {
case 0 -> System.out.println("VALUES(column_name)多行插入（推荐使用）OK");
}Shell实现插入记录
#!/bin/bash
#
# insert 数据
# 版权 2022 shqing
#连接MySQL
HOSTNAME="localhost"
PORT="3306"
USERNAME="root"
PASSWORD="*"
DATABASE_NAME="shqing"
MYSQL=/usr/local/mysql/bin/
DATA=$$
#命令模式插入
$MYSQL/mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p <<EOF
INSERT INTO $DATABASE_NAME.SKYRIM_INFO (name,nick_name,age,language_score,math_score,english_score,comprehensive_score,total_score) VALUES ('Shell$DATA', 'Google_Shell', 2+$DATA, 15, 10, 18, 99, $DATABASE_NAME.SKYRIM_INFO.language_score + SKYRIM_INFO.math_score + SKYRIM_INFO.english_score + SKYRIM_INFO.comprehensive_score); SELECT * FROM $DATABASE_NAME.SKYRIM_INFO WHERE name like'Sh%';
EOF
#字符串形式
function main() {
echo "============= Insert data start. ============= "
$MYSQL/mysql -h $HOSTNAME -P $PORT -u $USERNAME -p -e "INSERT INTO $DATABASE_NAME.SKYRIM_INFO (name,nick_name,age,language_score,math_score,english_score,comprehensive_score,total_score) VALUES ('Shell', 'Google_Shell', 2, 15, 10, 18, 99, $DATABASE_NAME.SKYRIM_INFO.language_score + SKYRIM_INFO.math_score + SKYRIM_INFO.english_score + SKYRIM_INFO.comprehensive_score); SELECT * FROM $DATABASE_NAME.SKYRIM_INFO WHERE name like 'Sh%';"
echo "============= Insert data end. ============= "
}
main "$@"
#循环插入
function cycle_insert() {
echo "============= Cycle insert data start. ============= "
while [ $DATA -le 10000 ]; do
$MYSQL/mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p -e "INSERT INTO $DATABASE_NAME.SKYRIM_INFO (name,nick_name,age,language_score,math_score,english_score,comprehensive_score,total_score) VALUES ('Shell$DATA', 'Google_Shell', 2+$DATA, 15+$DATA, 10+$DATA, 18+$DATA, 99+$DATA, $DATABASE_NAME.SKYRIM_INFO.language_score + SKYRIM_INFO.math_score + SKYRIM_INFO.english_score + SKYRIM_INFO.comprehensive_score); SELECT * FROM $DATABASE_NAME.SKYRIM_INFO WHERE name like'Sh%';"
DATA=$(($DATA + 2000))
echo "$DATA"
sleep 0.50
done
exit 0
echo "============= Cycle insert data end. ============= "
}
cycle_insert "$@"
最终结果：
+-----+-----------+--------------+------+----------------+------------+---------------+---------------------+-------------+
| id | name | nick_name | age | language_score | math_score | english_score | comprehensive_score | total_score |
+-----+-----------+--------------+------+----------------+------------+---------------+---------------------+-------------+
| 46 | Shell | Google_Shell | 2 | 15 | 10 | 18 | 99 | 142 |
| 47 | Shell | Google_Shell | 2 | 15 | 10 | 18 | 99 | 142 |
| 48 | Shell | Google_Shell | 2 | 15 | 10 | 18 | 99 | 142 |
| 49 | Shell | Google_Shell | 2 | 15 | 10 | 18 | 99 | 142 |
| 50 | Shell | Google_Shell | 2 | 15 | 10 | 18 | 99 | 142 |
| 51 | Shell | Google_Shell | 2 | 15 | 10 | 18 | 99 | 142 |
| 52 | Shell | Google_Shell | 2 | 15 | 10 | 18 | 99 | 142 |
| 53 | Shell | Google_Shell | 2 | 15 | 10 | 18 | 99 | 142 |
| 54 | Shell | Google_Shell | 2 | 15 | 10 | 18 | 99 | 142 |
| 55 | Shell | Google_Shell | 2 | 15 | 10 | 18 | 99 | 142 |
| 56 | Shell | Google_Shell | 2 | 15 | 10 | 18 | 99 | 142 |
| 57 | Shell | Google_Shell | 2 | 15 | 10 | 18 | 99 | 142 |
| 58 | Shell | Google_Shell | 2 | 15 | 10 | 18 | 99 | 142 |
| 59 | Shell | Google_Shell | 2 | 15 | 10 | 18 | 99 | 142 |
| 60 | Shell | Google_Shell | 2 | 15 | 10 | 18 | 99 | 142 |
| 61 | Shell | Google_Shell | 2 | 15 | 10 | 18 | 99 | 142 |
| 62 | Shell | Google_Shell | 2 | 15 | 10 | 18 | 99 | 142 |
| 63 | Shell1 | Google_Shell | 3 | 15 | 10 | 18 | 99 | 142 |
| 64 | Shell0 | Google_Shell | 2 | 15 | 10 | 18 | 99 | 142 |
| 65 | Shell0 | Google_Shell | 2 | 15 | 10 | 18 | 99 | 142 |
| 66 | Shell4301 | Google_Shell | 4303 | 15 | 10 | 18 | 99 | 142 |
| 67 | Shell4307 | Google_Shell | 4309 | 15 | 10 | 18 | 99 | 142 |
| 68 | Shell4312 | Google_Shell | 4314 | 15 | 10 | 18 | 99 | 142 |
| 69 | Shell4423 | Google_Shell | 4425 | 4438 | 4433 | 4441 | 4522 | 17834 |
| 70 | Shell4425 | Google_Shell | 4427 | 4440 | 4435 | 4443 | 4524 | 17842 |
| 71 | Shell4426 | Google_Shell | 4428 | 4441 | 4436 | 4444 | 4525 | 17846 |
| 72 | Shell4427 | Google_Shell | 4429 | 4442 | 4437 | 4445 | 4526 | 17850 |
| 73 | Shell4428 | Google_Shell | 4430 | 4443 | 4438 | 4446 | 4527 | 17854 |
| 74 | Shell4429 | Google_Shell | 4431 | 4444 | 4439 | 4447 | 4528 | 17858 |
| 75 | Shell4430 | Google_Shell | 4432 | 4445 | 4440 | 4448 | 4529 | 17862 |
| 76 | Shell4431 | Google_Shell | 4433 | 4446 | 4441 | 4449 | 4530 | 17866 |
| 77 | Shell4454 | Google_Shell | 4456 | 4469 | 4464 | 4472 | 4553 | 17958 |
| 78 | Shell4550 | Google_Shell | 4552 | 4565 | 4560 | 4568 | 4649 | 18342 |
| 79 | Shell4551 | Google_Shell | 4553 | 4566 | 4561 | 4569 | 4650 | 18346 |
| 80 | Shell4552 | Google_Shell | 4554 | 4567 | 4562 | 4570 | 4651 | 18350 |
| 81 | Shell4568 | Google_Shell | 4570 | 4583 | 4578 | 4586 | 4667 | 18414 |
| 82 | Shell4668 | Google_Shell | 4670 | 4683 | 4678 | 4686 | 4767 | 18814 |
| 83 | Shell4577 | Google_Shell | 4579 | 4592 | 4587 | 4595 | 4676 | 18450 |
| 84 | Shell5577 | Google_Shell | 5579 | 5592 | 5587 | 5595 | 5676 | 22450 |
| 85 | Shell6577 | Google_Shell | 6579 | 6592 | 6587 | 6595 | 6676 | 26450 |
| 86 | Shell7577 | Google_Shell | 7579 | 7592 | 7587 | 7595 | 7676 | 30450 |
| 87 | Shell8577 | Google_Shell | 8579 | 8592 | 8587 | 8595 | 8676 | 34450 |
| 88 | Shell9577 | Google_Shell | 9579 | 9592 | 9587 | 9595 | 9676 | 38450 |
| 89 | Shell | Google_Shell | 2 | 15 | 10 | 18 | 99 | 142 |
| 90 | Shell4603 | Google_Shell | 4605 | 4618 | 4613 | 4621 | 4702 | 18554 |
| 91 | Shell6603 | Google_Shell | 6605 | 6618 | 6613 | 6621 | 6702 | 26554 |
| 92 | Shell8603 | Google_Shell | 8605 | 8618 | 8613 | 8621 | 8702 | 34554 |
| 93 | Shell4612 | Google_Shell | 4614 | 15 | 10 | 18 | 99 | 142 |
| 94 | Shell | Google_Shell | 2 | 15 | 10 | 18 | 99 | 142 |
| 95 | Shell4612 | Google_Shell | 4614 | 4627 | 4622 | 4630 | 4711 | 18590 |
| 96 | Shell6612 | Google_Shell | 6614 | 6627 | 6622 | 6630 | 6711 | 26590 |
| 97 | Shell8612 | Google_Shell | 8614 | 8627 | 8622 | 8630 | 8711 | 34590 |
| 98 | Shell4627 | Google_Shell | 4629 | 15 | 10 | 18 | 99 | 142 |
| 99 | Shell | Google_Shell | 2 | 15 | 10 | 18 | 99 | 142 |
| 100 | Shell4627 | Google_Shell | 4629 | 4642 | 4637 | 4645 | 4726 | 18650 |
| 101 | Shell6627 | Google_Shell | 6629 | 6642 | 6637 | 6645 | 6726 | 26650 |
| 102 | Shell8627 | Google_Shell | 8629 | 8642 | 8637 | 8645 | 8726 | 34650 |
+-----+-----------+--------------+------+----------------+------------+---------------+---------------------+-------------+
10627OK 插入数据操作就到这里，哈哈哈。。。。
本文含有隐藏内容，请 开通VIP 后查看