一、创建数据库
mysql> create database daily_test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| daily_test |
| information_schema |
| mysql |
| performance_schema |
| pipeline |
| sys |
+--------------------+
6 rows in set (0.00 sec)
create table if not exists internet_company (id INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
person VARCHAR(100) NOT NULL,
country VARCHAR(100) NOT NULL,
PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
二、创建表
mysql> create table if not exists internet_company (id INT UNSIGNED AUTO_INCREMENT,
-> name VARCHAR(100) NOT NULL,
-> person VARCHAR(100) NOT NULL,
-> country VARCHAR(100) NOT NULL,
-> PRIMARY KEY (id)
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show tables;
+----------------------+
| Tables_in_daily_test |
+----------------------+
| internet_company |
+----------------------+
1 row in set (0.00 sec)
三、表中插入数据
mysql> insert into internet_company (id,name,person,country) values(1,"alibaba","mayun","china");
Query OK, 1 row affected (0.01 sec)
mysql> select * from internet_company;
+----+---------+--------+---------+
| id | name | person | country |
+----+---------+--------+---------+
| 1 | alibaba | mayun | china |
+----+---------+--------+---------+
1 row in set (0.00 sec)
# id values可以加引号
mysql> insert into internet_company (id,name,person,country) values("2","jingdong","liuqiangdong","china");
Query OK, 1 row affected (0.01 sec)
mysql> select * from internet_company;
+----+----------+--------------+---------+
| id | name | person | country |
+----+----------+--------------+---------+
| 1 | alibaba | mayun | china |
| 2 | jingdong | liuqiangdong | china |
+----+----------+--------------+---------+
2 rows in set (0.00 sec)
# AUTO_INCREMENT id可以不设置，默认递增
mysql> insert into internet_company (name,person,country) values("wangyi","dinglei","china");
Query OK, 1 row affected (0.01 sec)
mysql> select * from internet_company;
+----+----------+--------------+---------+
| id | name | person | country |
+----+----------+--------------+---------+
| 1 | alibaba | mayun | china |
| 2 | jingdong | liuqiangdong | china |
| 3 | wangyi | dinglei | china |
+----+----------+--------------+---------+
3 rows in set (0.00 sec)
# insert into 必须给所有的字段赋值
mysql> insert into internet_company (name,country) values("拼多多","china");
ERROR 1364 (HY000): Field 'person' doesn't have a default value
mysql> insert into internet_company (name,person,country) values("拼多多","","china");
Query OK, 1 row affected (0.00 sec)
mysql> select * from internet_company;
+----+-----------+--------------+---------+
| id | name | person | country |
+----+-----------+--------------+---------+
| 1 | alibaba | mayun | china |
| 2 | jingdong | liuqiangdong | china |
| 3 | wangyi | dinglei | china |
| 4 | 拼多多 | | china |
+----+-----------+--------------+---------+
4 rows in set (0.00 sec)
# 键值不对应，值中缺少person对应的
mysql> insert into internet_company (name,person,country) values("taobao","china");
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into internet_company (name,person,country) values("taobao","戴""珊","china");
Query OK, 1 row affected (0.00 sec)
mysql> select * from internet_company;
+----+-----------+--------------+---------+
| id | name | person | country |
+----+-----------+--------------+---------+
| 1 | alibaba | mayun | china |
| 2 | jingdong | liuqiangdong | china |
| 3 | wangyi | dinglei | china |
| 4 | 拼多多 | | china |
| 5 | taobao | 戴"珊 | china |
+----+-----------+--------------+---------+
5 rows in set (0.00 sec)
四、更新表数据
mysql> update internet_company set person='黄铮，”帅哥“，‘哈哈’，说你呢' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from internet_company;
+----+-----------+--------------------------------------------------+---------+
| id | name | person | country |
+----+-----------+--------------------------------------------------+---------+
| 1 | alibaba | mayun | china |
| 2 | jingdong | liuqiangdong | china |
| 3 | wangyi | dinglei | china |
| 4 | 拼多多 | 黄铮，”帅哥“，‘哈哈’，说你呢 | china |
| 5 | taobao | 戴"珊 | china |
+----+-----------+--------------------------------------------------+---------+
5 rows in set (0.00 sec)
# 引号需要转义
mysql> update internet_company set person='戴珊 [[ $(time) == 'now' ]]' where id=5;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'now' ]]' where id=5' at line 1
mysql> update internet_company set person='戴珊 [[ $(time) == \'now\' ]]' where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from internet_company;
+----+-----------+--------------------------------------------------+---------+
| id | name | person | country |
+----+-----------+--------------------------------------------------+---------+
| 1 | alibaba | mayun | china |
| 2 | jingdong | liuqiangdong | china |
| 3 | wangyi | dinglei | china |
| 4 | 拼多多 | 黄铮，”帅哥“，‘哈哈’，说你呢 | china |
| 5 | taobao | 戴珊 [[ $(time) == 'now' ]] | china |
+----+-----------+--------------------------------------------------+---------+
5 rows in set (0.00 sec)
五、参考文档：
1、https://blog.csdn.net/weixin_45126025/article/details/95970999
2、http://c.biancheng.net/view/2574.html
3、http://c.biancheng.net/view/2579.html
