MySQL基础.3

最新推荐文章于 2024-07-21 00:00:44 发布

大泽泽的csdn

最新推荐文章于 2024-07-21 00:00:44 发布

阅读量189

点赞数

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

本文链接：https://blog.csdn.net/weixin_37710293/article/details/79248774

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

SELECT语句控制要查看哪些列和行。

SELECT 
    column_1, column_2, .../*
FROM
    table_1
[INNER | LEFT |RIGHT] JOIN table_2 ON conditions
WHERE
    conditions
GROUP BY column_1
HAVING group_conditions
ORDER BY column_1
LIMIT offset, length;

SELECT语句由以下列表中所述的几个子句组成：

SELECT之后是逗号分隔列或星号(*)的列表，表示要返回所有列。
FROM指定要查询数据的表或视图。
JOIN根据某些连接条件从其他表中获取数据。
WHERE过滤结果集中的行。
GROUP BY将一组行组合成小分组，并对每个小分组应用聚合函数。
HAVING过滤器基于GROUP BY子句定义的小分组。
ORDER BY指定用于排序的列的列表。
LIMIT限制返回行的数量。
语句中的SELECT和FROM语句是必须的，其他部分是可选的。

mysql> SELECT lastname, firstname, jobtitle FROM employees;
+-----------+-----------+----------------------+
| lastname  | firstname | jobtitle             |
+-----------+-----------+----------------------+
| Murphy    | Diane     | President            |
| Patterson | Mary      | VP Sales             |
| Firrelli  | Jeff      | VP Marketing         |
| Patterson | William   | Sales Manager (APAC) |
| Bondur    | Gerard    | Sale Manager (EMEA)  |
| Bow       | Anthony   | Sales Manager (NA)   |
| Jennings  | Leslie    | Sales Rep            |
| Thompson  | Leslie    | Sales Rep            |
| Firrelli  | Julie     | Sales Rep            |
| Patterson | Steve     | Sales Rep            |
| Tseng     | Foon Yue  | Sales Rep            |
| Vanauf    | George    | Sales Rep            |
| Bondur    | Loui      | Sales Rep            |
| Hernandez | Gerard    | Sales Rep            |
| Castillo  | Pamela    | Sales Rep            |
| Bott      | Larry     | Sales Rep            |
| Jones     | Barry     | Sales Rep            |
| Fixter    | Andy      | Sales Rep            |
| Marsh     | Peter     | Sales Rep            |
| King      | Tom       | Sales Rep            |
| Nishi     | Mami      | Sales Rep            |
| Kato      | Yoshimi   | Sales Rep            |
| Gerard    | Martin    | Sales Rep            |
+-----------+-----------+----------------------+
23 rows in set

mysql> SELECT * FROM employees;
+----------------+-----------+-----------+-----------+-----------------------+------------+-----------+----------------------+
| employeeNumber | lastName  | firstName | extension | email                 | officeCode | reportsTo | jobTitle             |
+----------------+-----------+-----------+-----------+-----------------------+------------+-----------+----------------------+
|           1002 | Murphy    | Diane     | x5800     | dmurphy@yiibai.com    | 1          | NULL      | President            |
|           1056 | Patterson | Mary      | x4611     | mpatterso@yiibai.com  | 1          |      1002 | VP Sales             |
|           1076 | Firrelli  | Jeff      | x9273     | jfirrelli@yiibai.com  | 1          |      1002 | VP Marketing         |
|           1088 | Patterson | William   | x4871     | wpatterson@yiibai.com | 6          |      1056 | Sales Manager (APAC) |
|           1102 | Bondur    | Gerard    | x5408     | gbondur@gmail.com     | 4          |      1056 | Sale Manager (EMEA)  |
|           1143 | Bow       | Anthony   | x5428     | abow@gmail.com        | 1          |      1056 | Sales Manager (NA)   |
|           1165 | Jennings  | Leslie    | x3291     | ljennings@yiibai.com  | 1          |      1143 | Sales Rep            |
|           1166 | Thompson  | Leslie    | x4065     | lthompson@yiibai.com  | 1          |      1143 | Sales Rep            |
|           1188 | Firrelli  | Julie     | x2173     | jfirrelli@yiibai.com  | 2          |      1143 | Sales Rep            |
|           1216 | Patterson | Steve     | x4334     | spatterson@yiibai.com | 2          |      1143 | Sales Rep            |
|           1286 | Tseng     | Foon Yue  | x2248     | ftseng@yiibai.com     | 3          |      1143 | Sales Rep            |
|           1323 | Vanauf    | George    | x4102     | gvanauf@yiibai.com    | 3          |      1143 | Sales Rep            |
|           1337 | Bondur    | Loui      | x6493     | lbondur@yiibai.com    | 4          |      1102 | Sales Rep            |
|           1370 | Hernandez | Gerard    | x2028     | ghernande@gmail.com   | 4          |      1102 | Sales Rep            |
|           1401 | Castillo  | Pamela    | x2759     | pcastillo@gmail.com   | 4          |      1102 | Sales Rep            |
|           1501 | Bott      | Larry     | x2311     | lbott@yiibai.com      | 7          |      1102 | Sales Rep            |
|           1504 | Jones     | Barry     | x102      | bjones@gmail.com      | 7          |      1102 | Sales Rep            |
|           1611 | Fixter    | Andy      | x101      | afixter@yiibai.com    | 6          |      1088 | Sales Rep            |
|           1612 | Marsh     | Peter     | x102      | pmarsh@yiibai.com     | 6          |      1088 | Sales Rep            |
|           1619 | King      | Tom       | x103      | tking@gmail.com       | 6          |      1088 | Sales Rep            |
|           1621 | Nishi     | Mami      | x101      | mnishi@gmail.com      | 5          |      1056 | Sales Rep            |
|           1625 | Kato      | Yoshimi   | x102      | ykato@gmail.com       | 5          |      1621 | Sales Rep            |
|           1702 | Gerard    | Martin    | x2312     | mgerard@gmail.com     | 4          |      1102 | Sales Rep            |
+----------------+-----------+-----------+-----------+-----------------------+------------+-----------+----------------------+
23 rows in set

WHERE子句允许根据指定的过滤表达式或条件来指定要选择的行。

mysql> SELECT lastname, firstname, jobtitle
FROM employees
WHERE jobtitle = 'Sales Rep';
+-----------+-----------+-----------+
| lastname  | firstname | jobtitle  |
+-----------+-----------+-----------+
| Jennings  | Leslie    | Sales Rep |
| Thompson  | Leslie    | Sales Rep |
| Firrelli  | Julie     | Sales Rep |
| Patterson | Steve     | Sales Rep |
| Tseng     | Foon Yue  | Sales Rep |
| Vanauf    | George    | Sales Rep |
| Bondur    | Loui      | Sales Rep |
| Hernandez | Gerard    | Sales Rep |
| Castillo  | Pamela    | Sales Rep |
| Bott      | Larry     | Sales Rep |
| Jones     | Barry     | Sales Rep |
| Fixter    | Andy      | Sales Rep |
| Marsh     | Peter     | Sales Rep |
| King      | Tom       | Sales Rep |
| Nishi     | Mami      | Sales Rep |
| Kato      | Yoshimi   | Sales Rep |
| Gerard    | Martin    | Sales Rep |
+-----------+-----------+-----------+
17 rows in set

mysql> SELECT lastname, firstname, jobtitle
FROM employees
WHERE jobtitle = 'Sales Rep' AND officeCode = 1;
+----------+-----------+-----------+
| lastname | firstname | jobtitle  |
+----------+-----------+-----------+
| Jennings | Leslie    | Sales Rep |
| Thompson | Leslie    | Sales Rep |
+----------+-----------+-----------+
2 rows in set

下表列出了可用于在WHERE子句中形成过滤表达式的比较运算符。
这里写图片描述

mysql> SELECT lastname, firstname, jobtitle
FROM employees
WHERE jobtitle <> 'Sales Rep';
+-----------+-----------+----------------------+
| lastname  | firstname | jobtitle             |
+-----------+-----------+----------------------+
| Murphy    | Diane     | President            |
| Patterson | Mary      | VP Sales             |
| Firrelli  | Jeff      | VP Marketing         |
| Patterson | William   | Sales Manager (APAC) |
| Bondur    | Gerard    | Sale Manager (EMEA)  |
| Bow       | Anthony   | Sales Manager (NA)   |
+-----------+-----------+----------------------+
6 rows in set

还有一些有用的运算符可以在WHERE子句中使用来形成复杂的条件，例如：

1、BETWEEN选择在给定范围值内的值。
2、LIKE匹配基于模式匹配的值。
3、IN指定值是否匹配列表中的任何值。
4、IS NULL检查该值是否为NULL。

INSERT语句允许您将一行或多行插入到表中

 INSERT INTO table(column1,column2...)
VALUES (value1,value2,...);


INSERT INTO table(column1,column2...)
VALUES (value1,value2,...),
       (value1,value2,...),
...;

INSERT INTO tasks(subject,start_date,end_date,description)
VALUES('Learn MySQL INSERT','2017-07-21','2017-07-22','Start learning..');


INSERT INTO tasks(subject,start_date,end_date,description)
VALUES ('任务-1','2017-01-01','2017-01-02','Description 1'),
       ('任务-2','2017-01-01','2017-01-02','Description 2'),
       ('任务-3','2017-01-01','2017-01-02','Description 3');

`select * from tasks;
+---------+--------------------+------------+------------+------------------+
| task_id | subject            | start_date | end_date   | description      |
+---------+--------------------+------------+------------+------------------+
|       1 | Learn MySQL INSERT | 2017-07-21 | 2017-07-22 | Start learning.. |
|       2 | 任务-1             | 2017-01-01 | 2017-01-02 | Description 1    |
|       3 | 任务-2             | 2017-01-01 | 2017-01-02 | Description 2    |
|       4 | 任务-3             | 2017-01-01 | 2017-01-02 | Description 3    |
+---------+--------------------+------------+------------+------------------+
4 rows in set

在MySQL中，可以使用SELECT语句返回的列和值来填充INSERT语句的值。此功能非常方便，因为您可以使用INSERT和SELECT子句完全或部分复制表：
首先，通过复制tasks表的结构，创建一个名为tasks_bak的新表，如下所示：

CREATE TABLE tasks_bak LIKE tasks;

第二步，使用以下INSERT语句将tasks表中的数据插入tasks_bak表：

INSERT INTO tasks_bak
SELECT * FROM tasks;

第三步，检查tasks_bak表中的数据，看看是否真正从tasks表复制完成了。

mysql> select * from tasks_bak;
+---------+--------------------+------------+------------+------------------+
| task_id | subject            | start_date | end_date   | description      |
+---------+--------------------+------------+------------+------------------+
|       1 | Learn MySQL INSERT | 2017-07-21 | 2017-07-22 | Start learning.. |
|       2 | 任务-1             | 2017-01-01 | 2017-01-02 | Description 1    |
|       3 | 任务-2             | 2017-01-01 | 2017-01-02 | Description 2    |
|       4 | 任务-3             | 2017-01-01 | 2017-01-02 | Description 3    |
+---------+--------------------+------------+------------+------------------+
4 rows in set

INSERT INTO tasks(task_id,subject,start_date,end_date,description)
VALUES (4,'Test ON DUPLICATE KEY UPDATE','2017-01-01','2017-01-02','Next Priority');
SQL

Error Code: 1062. Duplicate entry '4' for key 'PRIMARY' 0.016 sec

因为表中的主键task_id列已经有一个值为 4 的行了，所以该语句违反了PRIMARY KEY约束。
但是，如果在INSERT语句中指定ON DUPLICATE KEY UPDATE选项，MySQL将插入新行或使用新值更新原行记录。

INSERT INTO tasks(task_id,subject,start_date,end_date,description)
VALUES (4,'Test ON DUPLICATE KEY UPDATE','2017-01-01','2017-01-02','Next Priority')
ON DUPLICATE KEY UPDATE 
   task_id = task_id + 1, 
   subject = 'Test ON DUPLICATE KEY UPDATE';

mysql> select * from tasks;
+---------+------------------------------+------------+------------+------------------+
| task_id | subject                      | start_date | end_date   | description      |
+---------+------------------------------+------------+------------+------------------+
|       1 | Learn MySQL INSERT           | 2017-07-21 | 2017-07-22 | Start learning.. |
|       2 | 任务-1                       | 2017-01-01 | 2017-01-02 | Description 1    |
|       3 | 任务-2                       | 2017-01-01 | 2017-01-02 | Description 2    |
|       5 | Test ON DUPLICATE KEY UPDATE | 2017-01-01 | 2017-01-02 | Description 3    |
+---------+------------------------------+------------+------------+------------------+
4 rows in set

等效于

UPDATE tasks 
SET 
    task_id = task_id + 1,
    subject = 'Test ON DUPLICATE KEY UPDATE'
WHERE
    task_id = 4;

大泽泽的csdn

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础.3

SELECT语句控制要查看哪些列和行。SELECT column_1, column_2, .../*FROM table_1[INNER | LEFT |RIGHT] JOIN table_2 ON conditionsWHERE conditionsGROUP BY column_1HAVING group_conditionsORDER BY
复制链接

扫一扫