SQL学习笔记-基本操作

Myth丶恋晨 2021-12-14 20:39 389阅读 0赞

### 文章目录 ###

*   *   *  0. 推荐书 << sql必知必会>>
         *  1. 安装好mysql后, 进入mysql
         *  2. 显示所有数据库
         *  3. 创建数据库
         *  4. 选定使用某个数据库
         *  5. 显示数据库的表
         *  6. 查看表的列信息
         *  7. 从表table\_1中选定所有列
         *  8. 选出其中的两列
         *  9. 新建一个表(增)
         *  10. 删除数据表 (删)
         *  11. 插入一行到表中(增)
         *  12. 更新特定行的特定列(改)
         *  13. 删除某一或所有行(删)
         *  14. 三种注释
         *  15. 数据排序
         *   *  指单行排序
             *  指定多行排序, 先按url, 再按age排序.
             *  按列的序号来排序
             *  按降序(从大到小)排序
         *  16. 使用where过滤数据
         *  17. 高级过滤(and, or, in, not)
         *  18. 使用%, \_通配符
         *   *  %的使用
             *  \_ 的使用
         *  19. 使用union组合查询
         *  20. 插入部分行
         *  21. 插入选中的行
         *  22. 函数
         *   *  文本处理函数
             *  日期和时间处理函数
             *  数值处理函数
         *  23. 数据类型
         *   *  字符串类型
             *  数值类型
             *  日期和时间类型
             *  二进制类型

### 0. 推荐书 << sql必知必会>> ###

### 1. 安装好mysql后, 进入mysql ###

`mysql -u root -p`

### 2. 显示所有数据库 ###

`SHOW DATABASES;`

### 3. 创建数据库 ###

`create database studydb_2;`

### 4. 选定使用某个数据库 ###

`USE studydb;`

### 5. 显示数据库的表 ###

`show tables;`

### 6. 查看表的列信息 ###

`desc table_1;`

### 7. 从表table\_1中选定所有列 ###

`select * from table_1;`

### 8. 选出其中的两列 ###

`select name, url from table_1;`

### 9. 新建一个表(增) ###

新建表比较麻烦, 因为需要指定有哪些行, 行的数据类型, 主键, 编码等等. 下面是一个例子.

CREATE TABLE IF NOT EXISTS `table_2`(
        joke varchar(255),
        name varchar(100) not null,
        id int,
        time date,
        primary key (id)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

### 10. 删除数据表 (删) ###

`drop table table_2;`

### 11. 插入一行到表中(增) ###

`insert into table_2 values( '测试', 'hello world', 110, '2019-7-3');`  
例子

### 12. 更新特定行的特定列(改) ###

update table_2
    set name = '我的名字'
    where id = 1110;

结果

mysql> select * from table_2;
    +---------+-------------+------+------------+
    | joke    | name        | id   | time       |
    +---------+-------------+------+------------+
    | 测试    | hello world |  110 | 2019-07-03 |
    | 测试2   | hefadfald   | 1110 | 2019-07-03 |
    +---------+-------------+------+------------+
    2 rows in set (0.00 sec)
    
    mysql> update table_2
        -> set name = '我的名字'
        -> where id = 1110;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from table_2;
    +---------+--------------+------+------------+
    | joke    | name         | id   | time       |
    +---------+--------------+------+------------+
    | 测试    | hello world  |  110 | 2019-07-03 |
    | 测试2   | 我的名字     | 1110 | 2019-07-03 |
    +---------+--------------+------+------------+
    2 rows in set (0.00 sec)
    
    mysql>

### 13. 删除某一或所有行(删) ###

delete from table_2
    where id = 1110;

mysql> delete from table_2
        -> where id = 1110;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from table_2;
    +--------+-------------+-----+------------+
    | joke   | name        | id  | time       |
    +--------+-------------+-----+------------+
    | 测试   | hello world | 110 | 2019-07-03 |
    +--------+-------------+-----+------------+
    1 row in set (0.00 sec)
    
    mysql>

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dqaDI2MjIwNzUxMjc_size_16_color_FFFFFF_t_70]

### 14. 三种注释 ###

mysql> /*hlajf*/ select * 
        -> -- 说什么
        -> # 注释测试
        -> from table_2;
    +--------+-------------+-----+------------+
    | joke   | name        | id  | time       |
    +--------+-------------+-----+------------+
    | 测试   | hello world | 110 | 2019-07-03 |
    +--------+-------------+-----+------------+
    1 row in set (0.00 sec)
    
    mysql>

### 15. 数据排序 ###

默认从小到大

select * from table_1 order by name, age;

#### 指单行排序 ####

#### 指定多行排序, 先按url, 再按age排序. ####

mysql> select * from table_1;
    +-------+------+----------------+
    | name  | age  | url            |
    +-------+------+----------------+
    | Jamey |   12 | www.baidu.com  |
    | Tom   |   21 | www.google.com |
    | jack  |    6 | www.cccccc.net |
    | timy  |   12 | www.google.com |
    | smile |   15 | www.baidu.com  |
    +-------+------+----------------+
    5 rows in set (0.00 sec)
    
    mysql> select * from table_1 order by url, age;
    +-------+------+----------------+
    | name  | age  | url            |
    +-------+------+----------------+
    | Jamey |   12 | www.baidu.com  |
    | smile |   15 | www.baidu.com  |
    | jack  |    6 | www.cccccc.net |
    | timy  |   12 | www.google.com |
    | Tom   |   21 | www.google.com |
    +-------+------+----------------+
    5 rows in set (0.00 sec)
    
    mysql>

#### 按列的序号来排序 ####

mysql> select url, name, age from table_1 order by 2;
    +----------------+-------+------+
    | url            | name  | age  |
    +----------------+-------+------+
    | www.cccccc.net | jack  |    6 |
    | www.baidu.com  | Jamey |   12 |
    | www.baidu.com  | smile |   15 |
    | www.google.com | timy  |   12 |
    | www.google.com | Tom   |   21 |
    +----------------+-------+------+
    5 rows in set (0.00 sec)
    
    mysql>

#### 按降序(从大到小)排序 ####

mysql> select url, name, age from table_1 order by 2 desc;
    +----------------+-------+------+
    | url            | name  | age  |
    +----------------+-------+------+
    | www.google.com | Tom   |   21 |
    | www.google.com | timy  |   12 |
    | www.baidu.com  | smile |   15 |
    | www.baidu.com  | Jamey |   12 |
    | www.cccccc.net | jack  |    6 |
    +----------------+-------+------+
    5 rows in set (0.01 sec)
    
    mysql>

### 16. 使用where过滤数据 ###

运算符过滤

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dqaDI2MjIwNzUxMjc_size_16_color_FFFFFF_t_70 1]  
图片来自 <<sql必知必会(第4版)>>

### 17. 高级过滤(and, or, in, not) ###

需要注意优先级的问题

mysql> select * from table_1 where age < 13 and url <> 'www.baidu.com' or name = 'Tom';
    +------+------+----------------+
    | name | age  | url            |
    +------+------+----------------+
    | Tom  |   21 | www.google.com |
    | jack |    6 | www.cccccc.net |
    | timy |   12 | www.google.com |
    +------+------+----------------+
    3 rows in set (0.00 sec)

使用in操作符

mysql> select * from table_1 where age in (6, 21, 13);
    +------+------+----------------+
    | name | age  | url            |
    +------+------+----------------+
    | Tom  |   21 | www.google.com |
    | jack |    6 | www.cccccc.net |
    +------+------+----------------+
    2 rows in set (0.00 sec)

使用not操作符

mysql> select * from table_1 where age not in (6, 21, 13);
    +-------+------+----------------+
    | name  | age  | url            |
    +-------+------+----------------+
    | Jamey |   12 | www.baidu.com  |
    | timy  |   12 | www.google.com |
    | smile |   15 | www.baidu.com  |
    +-------+------+----------------+
    3 rows in set (0.00 sec)
    
    mysql> select * from table_1 where not age in (6, 21, 13);
    +-------+------+----------------+
    | name  | age  | url            |
    +-------+------+----------------+
    | Jamey |   12 | www.baidu.com  |
    | timy  |   12 | www.google.com |
    | smile |   15 | www.baidu.com  |
    +-------+------+----------------+
    3 rows in set (0.00 sec)
    
    mysql> select * from table_1 where age in not (6, 21, 13);
    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 'not (6, 21, 13)' at line 1
    mysql> select * from table_1 not where age in not (6, 21, 13);
    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 'not where age in not (6, 21, 13)' at line 1
    mysql>

### 18. 使用%, \_通配符 ###

可以设置搜索是否区分大小写

#### %的使用 ####

mysql> select * from table_1 where name like 't%';
    +--------+------+----------------+
    | name   | age  | url            |
    +--------+------+----------------+
    | Tom    |   21 | www.google.com |
    | timy   |   12 | www.google.com |
    | tohaha |   17 | blog.csdn.net  |
    +--------+------+----------------+
    3 rows in set (0.00 sec)
    
    mysql> select * from table_1 where name = 'tom';
    +------+------+----------------+
    | name | age  | url            |
    +------+------+----------------+
    | Tom  |   21 | www.google.com |
    +------+------+----------------+
    1 row in set (0.01 sec)
    
    mysql>

#### \_ 的使用 ####

\_ 用来匹配单个字符

mysql> select * from table_1 where name like 't_m';
    +------+------+----------------+
    | name | age  | url            |
    +------+------+----------------+
    | Tom  |   21 | www.google.com |
    +------+------+----------------+
    1 row in set (0.00 sec)
    
    mysql>

### 19. 使用union组合查询 ###

UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过,  
各个列不需要以相同的次序列出)。

mysql> select * from table_1 where name like 't_m' 
        -> union
        -> select * from table_1 where name like 
        -> 'j%';
    +-------+------+----------------+
    | name  | age  | url            |
    +-------+------+----------------+
    | Tom   |   21 | www.google.com |
    | Jamey |   12 | www.baidu.com  |
    | jack  |    6 | www.cccccc.net |
    +-------+------+----------------+
    3 rows in set (0.00 sec)
    
    mysql> select * from table_1 where name like 't_m' union select url from table_1 where name like  'j%';
    ERROR 1222 (21000): The used SELECT statements have a different number of columns

列不需要相同, 但这样的话, 似乎数据就乱了.

mysql> select url, age, name from table_1 where age = 6  union select name, age, url from table_1 where name = "tom";
    +----------------+------+----------------+
    | url            | age  | name           |
    +----------------+------+----------------+
    | www.cccccc.net |    6 | jack           |
    | Tom            |   21 | www.google.com |
    +----------------+------+----------------+
    2 rows in set (0.01 sec)
    
    mysql>

### 20. 插入部分行 ###

未插入的为NULL

mysql> insert into table_1(name, age)
        -> values('test_2', 100);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from table_1;
    +--------+------+----------------+
    | name   | age  | url            |
    +--------+------+----------------+
    | Jamey  |   12 | www.baidu.com  |
    | Tom    |   21 | www.google.com |
    | jack   |    6 | www.cccccc.net |
    | timy   |   12 | www.google.com |
    | smile  |   15 | www.baidu.com  |
    | tohaha |   17 | blog.csdn.net  |
    | test_2 |  100 | NULL           |
    +--------+------+----------------+
    7 rows in set (0.00 sec)
    
    mysql>

### 21. 插入选中的行 ###

从另一个表中选中部分数据插入该表

mysql> select * from table_2;
    +-------+------+
    | name  | age  |
    +-------+------+
    | name1 |   11 |
    | name2 |   12 |
    +-------+------+
    2 rows in set (0.00 sec)
    
    mysql> select * from table_1;
    +--------+------+----------------+
    | name   | age  | url            |
    +--------+------+----------------+
    | Jamey  |   12 | www.baidu.com  |
    | Tom    |   21 | www.google.com |
    | jack   |    6 | www.cccccc.net |
    | timy   |   12 | www.google.com |
    | smile  |   15 | www.baidu.com  |
    | tohaha |   17 | blog.csdn.net  |
    | test_2 |  100 | NULL           |
    +--------+------+----------------+
    7 rows in set (0.00 sec)
    
    mysql> insert into table_1(name, age)
        -> select name, age from table_2 where age < 12;
    Query OK, 1 row affected (0.01 sec)
    Records: 1  Duplicates: 0  Warnings: 0
    
    mysql> select * from table_1;
    +--------+------+----------------+
    | name   | age  | url            |
    +--------+------+----------------+
    | Jamey  |   12 | www.baidu.com  |
    | Tom    |   21 | www.google.com |
    | jack   |    6 | www.cccccc.net |
    | timy   |   12 | www.google.com |
    | smile  |   15 | www.baidu.com  |
    | tohaha |   17 | blog.csdn.net  |
    | test_2 |  100 | NULL           |
    | name1  |   11 | NULL           |
    +--------+------+----------------+
    8 rows in set (0.00 sec)
    
    mysql>

### 22. 函数 ###

图片截自<<sql必知必会>>

#### 文本处理函数 ####

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dqaDI2MjIwNzUxMjc_size_16_color_FFFFFF_t_70 2][]

#### 日期和时间处理函数 ####

#### 数值处理函数 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dqaDI2MjIwNzUxMjc_size_16_color_FFFFFF_t_70 3]

### 23. 数据类型 ###

图片截自<<sql必知必会>>

#### 字符串类型 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dqaDI2MjIwNzUxMjc_size_16_color_FFFFFF_t_70 4]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dqaDI2MjIwNzUxMjc_size_16_color_FFFFFF_t_70 5]

#### 数值类型 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dqaDI2MjIwNzUxMjc_size_16_color_FFFFFF_t_70 6]

#### 日期和时间类型 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dqaDI2MjIwNzUxMjc_size_16_color_FFFFFF_t_70 7]

#### 二进制类型 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dqaDI2MjIwNzUxMjc_size_16_color_FFFFFF_t_70 8]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dqaDI2MjIwNzUxMjc_size_16_color_FFFFFF_t_70]: /images/20211214/62b1f4886b9a4fd192f677eb9e4341ae.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dqaDI2MjIwNzUxMjc_size_16_color_FFFFFF_t_70 1]: /images/20211214/5de416f2f85145959b74127811b9629e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dqaDI2MjIwNzUxMjc_size_16_color_FFFFFF_t_70 2]: /images/20211214/8eb01e910abc42e793b60ed993629a42.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dqaDI2MjIwNzUxMjc_size_16_color_FFFFFF_t_70 3]: /images/20211214/5b26a64818a34c8a8c12a9cd700471e0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dqaDI2MjIwNzUxMjc_size_16_color_FFFFFF_t_70 4]: /images/20211214/fafb26d9907e48d58ca0e96cf6d42896.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dqaDI2MjIwNzUxMjc_size_16_color_FFFFFF_t_70 5]: /images/20211214/390582a054074e0f85d65ec1e2ec52ef.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dqaDI2MjIwNzUxMjc_size_16_color_FFFFFF_t_70 6]: /images/20211214/d4bdf4c874cd4a7299c268417fa31b3a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dqaDI2MjIwNzUxMjc_size_16_color_FFFFFF_t_70 7]: /images/20211214/863e83b41d3747c8ac09639c3a710dca.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dqaDI2MjIwNzUxMjc_size_16_color_FFFFFF_t_70 8]: /images/20211214/5191667ffeee4e2689a5e335d6043122.png