MySQL语法（续）

最新推荐文章于 2023-11-23 17:32:42 发布

lazying_bird

最新推荐文章于 2023-11-23 17:32:42 发布

阅读量442

点赞数

分类专栏： SQL 文章标签： mysql

本文链接：https://blog.csdn.net/a_flying_bird/article/details/50735299

版权

SQL 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

create table & insert的例子。

Create Table & Insert

建表、插入数据的命令：

CREATE TABLE t3 (_id int NOT NULL AUTO_INCREMENT, name VARCHAR(20), age int, PRIMARY KEY (_id));
INSERT INTO t3 VALUES (1, 'John', 25);
INSERT INTO t3 VALUES (2, 'Tom', 15);
INSERT INTO t3 (name, age) VALUES ('Ann', 25);
INSERT INTO t3 (name, age) VALUES ('Charley', 5);

详细执行过程：

mysql> CREATE TABLE t3 (_id int NOT NULL AUTO_INCREMENT, name VARCHAR(20), age int, PRIMARY KEY (_id));
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t3 VALUES ('John', 25);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> INSERT INTO t3 VALUES (1, 'John', 25);
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM t3;
+-----+------+------+
| _id | name | age  |
+-----+------+------+
|   1 | John |   25 |
+-----+------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO t3 VALUES (1, 'Tom', 15);
ERROR 1062 (23000): Duplicate entry '1' for key 1
mysql> INSERT INTO t3 VALUES (2, 'Tom', 15);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t3;
+-----+------+------+
| _id | name | age  |
+-----+------+------+
|   1 | John |   25 |
|   2 | Tom  |   15 |
+-----+------+------+
2 rows in set (0.00 sec)

mysql> INSERT INTO t3 (name, age) VALUES ('Ann', 25);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t3;
+-----+------+------+
| _id | name | age  |
+-----+------+------+
|   1 | John |   25 |
|   2 | Tom  |   15 |
|   3 | Ann  |   25 |
+-----+------+------+
3 rows in set (0.00 sec)

mysql> INSERT INTO t3 (name, age) VALUES ('Charley', 5);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t3;
+-----+---------+------+
| _id | name    | age  |
+-----+---------+------+
|   1 | John    |   25 |
|   2 | Tom     |   15 |
|   3 | Ann     |   25 |
|   4 | Charley |    5 |
+-----+---------+------+
4 rows in set (0.00 sec)

mysql>

删除表

DROP TABLE table_name;

查询表的各个列 Describe

mysql> DESCRIBE t3;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| _id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.05 sec)

mysql>

注：Android集成的Sqlite3的语法在MySql这里不支持：

mysql> .schema t3;
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 '.sche
ma t3' at line 1
mysql>

更新数据

mysql> SELECT * FROM t3;
+-----+---------+------+
| _id | name    | age  |
+-----+---------+------+
|   1 | John    |   25 |
|   2 | Tom     |   15 |
|   3 | Ann     |   25 |
|   4 | Charley |    5 |
|   5 | Ben     | NULL |
+-----+---------+------+
5 rows in set (0.00 sec)

mysql> UPDATE t3 SET age=18 WHERE name="Ben";
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE t3 SET age=18 WHERE name="BenOooo";
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> SELECT * FROM t3;
+-----+---------+------+
| _id | name    | age  |
+-----+---------+------+
|   1 | John    |   25 |
|   2 | Tom     |   15 |
|   3 | Ann     |   25 |
|   4 | Charley |    5 |
|   5 | Ben     |   18 |
+-----+---------+------+
5 rows in set (0.00 sec)

mysql>

ALTER 增加列

增加列之后，再更高两列的数据。

mysql> SELECT * FROM t3;
+-----+---------+------+
| _id | name    | age  |
+-----+---------+------+
|   1 | John    |   25 |
|   2 | Tom     |   15 |
|   3 | Ann     |   25 |
|   4 | Charley |    5 |
|   5 | Ben     |   18 |
+-----+---------+------+
5 rows in set (0.00 sec)

mysql> ALTER TABLE t3 ADD score int;
Query OK, 5 rows affected (0.10 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t3;
+-----+---------+------+-------+
| _id | name    | age  | score |
+-----+---------+------+-------+
|   1 | John    |   25 |  NULL |
|   2 | Tom     |   15 |  NULL |
|   3 | Ann     |   25 |  NULL |
|   4 | Charley |    5 |  NULL |
|   5 | Ben     |   18 |  NULL |
+-----+---------+------+-------+
5 rows in set (0.00 sec)

mysql> UPDATE t3 SET age=28, score=100 WHERE name='Ben';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM t3;
+-----+---------+------+-------+
| _id | name    | age  | score |
+-----+---------+------+-------+
|   1 | John    |   25 |  NULL |
|   2 | Tom     |   15 |  NULL |
|   3 | Ann     |   25 |  NULL |
|   4 | Charley |    5 |  NULL |
|   5 | Ben     |   28 |   100 |
+-----+---------+------+-------+
5 rows in set (0.00 sec)

mysql>

SELECT仅显示指定的列

mysql> SELECT * FROM t3;
+-----+---------+------+-------+
| _id | name    | age  | score |
+-----+---------+------+-------+
|   1 | John    |   25 |  NULL |
|   2 | Tom     |   15 |  NULL |
|   3 | Ann     |   25 |  NULL |
|   4 | Charley |    5 |  NULL |
|   5 | Ben     |   28 |   100 |
+-----+---------+------+-------+
5 rows in set (0.00 sec)

mysql> SELECT name, age FROM t3;
+---------+------+
| name    | age  |
+---------+------+
| John    |   25 |
| Tom     |   15 |
| Ann     |   25 |
| Charley |    5 |
| Ben     |   28 |
+---------+------+
5 rows in set (0.00 sec)

mysql> SELECT name FROM t3;
+---------+
| name    |
+---------+
| John    |
| Tom     |
| Ann     |
| Charley |
| Ben     |
+---------+
5 rows in set (0.00 sec)

mysql>

删除表中的数据

删除满足某种条件的row；删除所有数据。

mysql> SELECT * FROM t3;
+-----+---------+------+-------+
| _id | name    | age  | score |
+-----+---------+------+-------+
|   1 | John    |   25 |  NULL |
|   2 | Tom     |   15 |  NULL |
|   3 | Ann     |   25 |  NULL |
|   4 | Charley |    5 |  NULL |
|   5 | Ben     |   28 |   100 |
+-----+---------+------+-------+
5 rows in set (0.00 sec)

mysql> DELETE FROM t3 WHERE name="Ben";
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM t3;
+-----+---------+------+-------+
| _id | name    | age  | score |
+-----+---------+------+-------+
|   1 | John    |   25 |  NULL |
|   2 | Tom     |   15 |  NULL |
|   3 | Ann     |   25 |  NULL |
|   4 | Charley |    5 |  NULL |
+-----+---------+------+-------+
4 rows in set (0.00 sec)

mysql> DELETE FROM t3;
Query OK, 4 rows affected (0.00 sec)

mysql> SELECT * FROM t3;
Empty set (0.00 sec)

mysql>