MySql学习系列（二）

Myth丶恋晨 2022-01-31 01:53 280阅读 0赞

### MySql学习系列（二） ###

*  2.1 MySQL 基础 （二）- 表操作
 *   *  \#学习内容\#
     *   *  1、MySQL表数据类型
         *  2、用SQL语句创建表
         *  3、用SQL语句向表中添加数据
         *  4、用SQL语句删除表
         *  5、用SQL语句修改表
 *  \#作业\#
 *   *  项目三 （超过5名学生的课（难度：简单））
     *  项目四：交换工资（难度：简单）
 *  2.2 MySQL 基础 （三）- 表联结
 *   *  \#学习内容\#
     *  项目五：组合两张表 （难度：简单）
     *  项目六：删除重复的邮箱（难度：简单）

# 2.1 MySQL 基础 （二）- 表操作 #

## \#学习内容\# ##

### 1、MySQL表数据类型 ###

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。  
数值类型：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg0ODYwMg_size_16_color_FFFFFF_t_70]  
日期和时间类型：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg0ODYwMg_size_16_color_FFFFFF_t_70 1]  
字符串类型：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg0ODYwMg_size_16_color_FFFFFF_t_70 2]  
注意：  
1、一个汉字占多少长度与编码有关：

UTF－8：一个汉字＝3个字节

GBK：一个汉字＝2个字节

2、varchar(n) 表示 n 个字符，无论汉字和英文，Mysql 都能存入 n 个字符，仅是实际字节长度有所区别

3、MySQL 检查长度，可用 SQL 语言来查看：

`select LENGTH(fieldname) from tablename`

### 2、用SQL语句创建表 ###

语句解释 设定列类型 、大小、约束 设定主键

2.1 创建MySQL数据表需要以下信息：

表名  
表字段名  
定义每个表字段

2.2 语法  
以下为创建MySQL数据表的SQL通用语法：

CREATE TABLE table_name (column_name column_type);

以下例子中我们将在 MYSQL 数据库中创建数据表runoob\_tbl：

CREATE TABLE IF NOT EXISTS  runoob_tbl (
        runoob_id  INT UNSIGNED AUTO_INCREMENT,
        runoob_title  VARCHAR(100) NOT NULL,
        runoob_author  VARCHAR(40) NOT NULL,
        submission_date  DATE,
       PRIMARY KEY (  runoob_id )
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

实例解析：

1、如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。  
2、AUTO\_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。  
3、PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。  
4、ENGINE 设置存储引擎，CHARSET 设置编码。  
5、UNSIGNED unsigned就是将数字类型无符号化，用此类型可以增加数据长度。（第一位符号位用来存储数，没有符号位了。）

2.3 通过命令提示符创建表  
通过 mysql> 命令窗口可以很简单的创建MySQL数据表。你可以使用 SQL 语句 CREATE TABLE 来创建数据表。

root@host# mysql -u root -p
    Enter password:*******
    mysql> use RUNOOB;
    Database changed
    mysql> CREATE TABLE runoob_tbl(
       -> runoob_id INT NOT NULL AUTO_INCREMENT,
       -> runoob_title VARCHAR(100) NOT NULL,
       -> runoob_author VARCHAR(40) NOT NULL,
       -> submission_date DATE,
       -> PRIMARY KEY ( runoob_id )
       -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
    Query OK, 0 rows affected (0.16 sec)
    mysql>

### 3、用SQL语句向表中添加数据 ###

语句解释 多种添加方式（指定列名；不指定列名）  
MySQL 表中使用 INSERT INTO SQL语句来插入数据。

3.1 语法

INSERT INTO table_name ( field1, field2,...fieldN )
                           VALUES
                           ( value1, value2,...valueN );

如果数据是字符型，必须使用单引号或者双引号，如：“value”。

3.2 通过命令提示窗口插入数据

实例  
以下实例中我们将向 runoob\_tbl 表插入三条数据：

root@host# mysql -u root -p password;
    Enter password:*******
    mysql> use RUNOOB;
    Database changed
    mysql> INSERT INTO runoob_tbl 
        -> (runoob_title, runoob_author, submission_date)
        -> VALUES
        -> ("学习 PHP", "菜鸟教程", NOW());
    Query OK, 1 rows affected, 1 warnings (0.01 sec)
    mysql> INSERT INTO runoob_tbl
        -> (runoob_title, runoob_author, submission_date)
        -> VALUES
        -> ("学习 MySQL", "菜鸟教程", NOW());
    Query OK, 1 rows affected, 1 warnings (0.01 sec)
    mysql> INSERT INTO runoob_tbl
        -> (runoob_title, runoob_author, submission_date)
        -> VALUES
        -> ("JAVA 教程", "RUNOOB.COM", '2016-05-06');
    Query OK, 1 rows affected (0.00 sec)
    mysql>

3.3 插入多行

想要在表中一次插入多行，可以使用具有以下语法的INSERT语句：

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

在这种形式中，每行的值列表用逗号分隔。 例如，要将多行插入到tasks表中，请使用以下语句：

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');

3.4 如果为表中的所有列指定相应列的值，则可以忽略INSERT语句中的列列表，如下所示：

INSERT INTO table
    VALUES (value1,value2,...),
                 (value1,value2,...)；

3.5 具有SELECT子句的MySQL INSERT

假设要将tasks表复制到tasks\_bak表。  
首先，通过复制tasks表的结构，创建一个名为tasks\_bak的新表，如下所示：

CREATE TABLE tasks_bak LIKE tasks;

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

INSERT INTO tasks_bak
    SELECT * FROM tasks;

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

select * from tasks;

> 参考： [https://www.yiibai.com/mysql/insert-statement.html][https_www.yiibai.com_mysql_insert-statement.html]

### 4、用SQL语句删除表 ###

语句解释 DELETE DROP TRUNCATE 不同方式的区别  
当你不再需要该表时， 用 drop；  
当你仍要保留该表，但要删除所有记录时， 用 truncate；  
当你要删除部分记录时（always with a WHERE clause), 用 delete.  
4.1 MySQL 删除数据表  
MySQL中删除数据表是非常容易操作的， 但是你再进行删除表操作时要非常小心，因为执行删除命令后所有数据都会消失。  
4.2 DROP 语法  
以下为删除MySQL数据表的通用语法：

DROP TABLE table_name ;

4.3 在命令提示窗口中删除数据表  
在mysql>命令提示窗口中删除数据表SQL语句为 DROP TABLE ：  
以下实例删除了数据表runoob\_tbl：

root@host# mysql -u root -p
    Enter password:*******
    mysql> use RUNOOB;
    Database changed
    mysql> DROP TABLE runoob_tbl
    Query OK, 0 rows affected (0.8 sec)
    mysql>

4.4 MySQL DELETE语句介绍

要从表中删除数据，请使用MySQL DELETE语句。下面说明了DELETE语句的语法：

DELETE FROM table_name
    WHERE condition;

在上面查询语句中 -

首先，指定删除数据的表(table\_name)。  
其次，使用条件来指定要在WHERE子句中删除的行记录。如果行匹配条件，这些行记录将被删除。

请注意，WHERE子句是可选的。如果省略WHERE子句，DELETE语句将删除表中的所有行。

4.5 MySQL TRUNCATE TABLE语句简介  
MySQL TRUNCATE TABLE语句允许您删除表中的所有数据。 因此，在功能方面，TRUNCATE TABLE语句就像没有WHERE子句的DELETE语句。 但是，在某些情况下，MySQL TRUNCATE TABLE语句比DELETE语句更有效。

TRUNCATE TABLE table_name;

在TRUNCATE TABLE子句后面指定要删除所有数据的表名称。  
TABLE关键字是可选的。 但是，应该使用它来区分TRUNCATE TABLE语句和TRUNCATE函数。

> 参考： [https://www.cnblogs.com/zhoufangcheng04050227/p/7991759.html][https_www.cnblogs.com_zhoufangcheng04050227_p_7991759.html]

### 5、用SQL语句修改表 ###

修改列名 修改表中数据 删除行 删除列 新建列 新建行

5.1 MySQL UPDATE语句简介  
我们使用UPDATE语句来更新表中的现有数据。也可以使用UPDATE语句来更改表中单个行，一组行或所有行的列值。

下面说明了MySQL UPDATE语句的语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_name 
    SET 
        column_name1 = expr1,
        column_name2 = expr2,
        ...
    WHERE
        condition;

在上面UPDATE语句中：

1、首先，在UPDATE关键字后面指定要更新数据的表名。  
2、其次，SET子句指定要修改的列和新值。要更新多个列，请使用以逗号分隔的列表。以字面值，表达式或子查询的形式在每列的赋值中来提供要设置的值。  
3、第三，使用WHERE子句中的条件指定要更新的行。WHERE子句是可选的。 如果省略WHERE子句，则UPDATE语句将更新表中的所有行。

请注意，WHERE子句非常重要，所以不应该忘记指定更新的条件。 有时，您可能只想改变一行; 但是，可能会忘记写上WHERE子句，导致意外更新表中的所有行。  
MySQL在UPDATE语句中支持两个修饰符。

LOW\_PRIORITY修饰符指示UPDATE语句延迟更新，直到没有从表中读取数据的连接。 LOW\_PRIORITY对仅使用表级锁定的存储引擎(例如MyISAM，MERGE，MEMORY)生效。  
即使发生错误，IGNORE修饰符也可以使UPDATE语句继续更新行。导致错误(如重复键冲突)的行不会更新。

5.2 MySQL UPDATE示例

UPDATE employees 
    SET 
        email = 'mary.new@yiibai.com'
    WHERE
        employeeNumber = 1056;

5.3 ALTER TABLE语句简介  
可以使用ALTER TABLE语句来更改现有表的结构。 ALTER TABLE语句可用来添加列，删除列，更改列的数据类型，添加主键，重命名表等等。 以下说明了ALTER TABLE语句语法：

> 参考：[https://www.yiibai.com/mysql/alter-table.html][https_www.yiibai.com_mysql_alter-table.html]

# \#作业\# #

## 项目三 （超过5名学生的课（难度：简单）） ##

1、创建如下所示的courses 表 ，有: student (学生) 和 class (课程)。  
编写一个 SQL 查询，列出所有超过或等于5名学生的课。  
创建表代码：

CREATE TABLE IF NOT EXISTS courses(
        student VARCHAR(20) NOT NULL,
        class varchar(40) NOT NULL,
        PRIMARY KEY(student)
        )ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO courses
      Values('A','Math'),
     ('B','English'),
     ('C','Math'),
     ('D','Biology'),
     ('E','Math'),
     ('F','Computer')，
     ('G','Math');

![在这里插入图片描述][20190515171526401.png]  
查询所有超过或等于5名学生的课：

SELECT class From courses
     group by class having count(*)>=5;

![在这里插入图片描述][20190515171536368.png]

## 项目四：交换工资（难度：简单） ##

创建一个 salary表，如下所示，有m=男性 和 f=女性的值 。  
例如:

id name sex salary  
1 A m 2500  
2 B f 1500  
3 C m 5500  
4 D f 500  
交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。  
运行你所编写的查询语句之后，将会得到以下表:

id name sex salary  
1 A f 2500  
2 B m 1500  
3 C f 5500  
4 D m 500

建表代码：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg0ODYwMg_size_16_color_FFFFFF_t_70 3]  
![在这里插入图片描述][20190516092640265.png]  
改动：

update salary set Sex = case Sex
    when 'f' then 'm'
    when 'm' then 'f'
    end;

![在这里插入图片描述][20190516112554349.png]  
像IF语句一样，CASE语句选择要执行的一个语句序列。 但是，要选择序列，CASE语句使用选择器而不是多个布尔表达式。选择器是一个表达式，其值用于选择几种替代方法之一。  
句法PL/SQL中的case语句的语法是 ：

CASE selector 
       WHEN 'value1' THEN S1; 
       WHEN 'value2' THEN S2; 
       WHEN 'value3' THEN S3; 
       ... 
       ELSE Sn;  -- default case 
    END CASE;

# 2.2 MySQL 基础 （三）- 表联结 #

## \#学习内容\# ##

1、MySQL别名  
2、INNER JOIN  
3、LEFT JOIN  
4、CROSS JOIN  
5、自连接  
6、UNION  
7、以上几种方式的区别和联系

## 项目五：组合两张表 （难度：简单） ##

在数据库中创建表1和表2，并各插入三行数据（自己造）  
表1: Person PersonId 是上表主键  
表2: Address AddressId 是上表主键

create table Person(
    PersonId int not null primary key,
    FirstName varchar(255),
    LastName varchar(255)
    );
    插入数据：
    insert into Person(PersonId,FirstName,LastName)
    values
    (1,'A','a'),
    (2,'B','b'),
    (3,'C','c');

create table Address(
    AddressId int not null,
    PersonId int not null,
    City varchar(255),State varchar(255)
    );
    插入数据：
    insert into Address(AddressId,PersonId,City,State)
    values
    (1,2,'Chengdu','Sichuan'),
    (3,4,'Beijing','Beijing'),
    (5,6,'Taibei','Taiwan');

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State

select FirstName,LastName,City,State 
        from Address left join Person
        on Person.PersonId = Address.PersonId
        order by Person.PersonId;

![在这里插入图片描述][20190516120431598.png]

## 项目六：删除重复的邮箱（难度：简单） ##

编写一个 SQL 查询，来删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 \*\*Id \*\*\*最小 \*的那个。

CREATE TABLE email (
        Id INT NOT NULL PRIMARY KEY,
        Email VARCHAR(50)
    );
    
    INSERT INTO email VALUES ('1', '[a@b.com](mailto:a@b.com)');
    INSERT INTO email VALUES ('2', '[c@d.com](mailto:c@d.com)');
    INSERT INTO email VALUES ('3', '[a@b.com](mailto:a@b.com)');

DELETE FROM email 
    WHERE Id NOT IN (
        SELECT mid FROM (
            SELECT MIN(Id) as mid FROM email group by Email 
        ) as t
    );

![在这里插入图片描述][20190516121944384.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg0ODYwMg_size_16_color_FFFFFF_t_70]: /images/20220131/e84b20aaffb14c73b93ceaafb93cc06e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg0ODYwMg_size_16_color_FFFFFF_t_70 1]: /images/20220131/eca37b7b64ec45f8bd076399bfbf956d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg0ODYwMg_size_16_color_FFFFFF_t_70 2]: /images/20220131/43baa64b053d41f3854f788c11ee3163.png
[https_www.yiibai.com_mysql_insert-statement.html]: https://www.yiibai.com/mysql/insert-statement.html
[https_www.cnblogs.com_zhoufangcheng04050227_p_7991759.html]: https://www.cnblogs.com/zhoufangcheng04050227/p/7991759.html
[https_www.yiibai.com_mysql_alter-table.html]: https://www.yiibai.com/mysql/alter-table.html
[20190515171526401.png]: /images/20220131/b89631751536431dbd9a1ff6ca91fc5d.png
[20190515171536368.png]: /images/20220131/bec8435994134b24bd4c3b18180b2827.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg0ODYwMg_size_16_color_FFFFFF_t_70 3]: /images/20220131/1acde0fab1ae4cc388bd83864207e2ef.png
[20190516092640265.png]: /images/20220131/f4c368ede4874d4c89a4868b0fd91800.png
[20190516112554349.png]: /images/20220131/e7ad0314e5ad42bcba6d37602e9c2c9a.png
[20190516120431598.png]: /images/20220131/170489a39dc344a98b0070f190f30868.png
[20190516121944384.png]: /images/20220131/46cfe33c78db4c658b8f4af2d33918d2.png