数据查询语言DQL

Data Query Language 基本结构是由 select<字段>from<表或视图>where<查询条件>组成。

mysql> use learning;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_learning |
+--------------------+
| houses             |
+--------------------+
1 row in set (0.02 sec)

mysql> desc houses;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| name            | varchar(100) | NO   |     | NULL    |       |
| house_location  | varchar(100) | NO   |     | NULL    |       |
| purchasing_year | varchar(100) | NO   |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> insert into houses (name,house_location,purchasing_year) values ('甲','天河',1997);/*向表格中插入新的行*/
Query OK, 1 row affected (0.12 sec)

mysql> insert into houses (name,house_location,purchasing_year) values ('甲','天河',1998);
Query OK, 1 row affected (0.07 sec)

mysql> select name,house_location,purchasing_year from houses;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| 甲   | 天河           | 1998            |
+------+----------------+-----------------+
2 rows in set (0.00 sec)

mysql> select name,house_location,purchasing_year from houses where name='甲';
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| 甲   | 天河           | 1998            |
+------+----------------+-----------------+
2 rows in set (0.00 sec)

mysql> select * from houses;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| 甲   | 天河           | 1998            |
+------+----------------+-----------------+
2 rows in set (0.00 sec)

mysql> select name from houses;
+------+
| name |
+------+
| 甲   |
| 甲   |
+------+
2 rows in set (0.00 sec)

mysql> select * from houses where name='ab';
Empty set (0.00 sec)

mysql> create view housesview as select * from houses;/*创建视图*/
Query OK, 0 rows affected (0.14 sec)

mysql> select * from housesview;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| 甲   | 天河           | 1998            |
+------+----------------+-----------------+
2 rows in set (0.00 sec)

mysql> select concat(name,"|",house_location) from houses;/*合并字段输出*/
+---------------------------------+
| concat(name,"|",house_location) |
+---------------------------------+
| 甲|天河                         |
| 甲|天河                         |
+---------------------------------+
2 rows in set (0.00 sec)

INSERT INTO语法：

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

CREATE VIEW语法：

CREATE VIEW <视图名> AS <SELECT语句>