0403任务三MySQL 基础（二）- 表操作

ゝ一世哀愁。 2022-02-24 05:28 253阅读 0赞

### 任务三MySQL 基础 （二）- 表操作 ###

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

# 一、学习内容 #

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

[http://www.runoob.com/mysql/mysql-data-types.html][http_www.runoob.com_mysql_mysql-data-types.html]  
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

*  数值类型  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0d1aWFiYmV5_size_16_color_FFFFFF_t_70]
 *  日期和时间类型  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0d1aWFiYmV5_size_16_color_FFFFFF_t_70 1]
 *  字符串类型  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0d1aWFiYmV5_size_16_color_FFFFFF_t_70 2]

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

表格（table）用来存储数据，表格是一种结构化文件。表格的行被称为记录，表格列被称为字段。表格字段属性：字段名称、字段数据类型、字段长度、字段约束。  
2.1 创建表  
创建表的时候，表中有字段，每一个字段有：  
\* 字段名  
\* 字段数据类型  
\* 字段长度限制  
\* 字段约束  
创建表的语法：

create table tableName(
       columnName dataType(length),
       ………………..
       columnName dataType(length)
    );

如：建立学生信息表，字段包括：学号、姓名、性别、出生日期、email、班级标识。

create table t_student(
    	student_id int(10),
    	student_name varchar(20),
    	sex char(2),
    	birthday date,
    	email varchar(30),
    	classes_id int(3)	
    )

2.2 创建表加入约束  
常见的约束：  
**1)非空约束**，not null,针对某个字段设置其值不为空，如学生姓名不为空.  
**2)唯一约束**，unique,它可以使某个字段的值不能重复，如email不能重复

create table t_student(
    	student_id int(10),
    	student_name varchar(20) not null,
    	sex char(2),
    	birthday date,
    	email varchar(30) unique,
    	classes_id int(3),
    	constraint	fk_id foreign key(classes_id) references t_classes(classes_id)
    );

**3)主键约束**，primary key，主键可以标识记录的唯一性，主键分为单一主键和复合（联合）主键，单一主键是由一个字段构成的，复合（联合）主键是由多个字段构成的。  
列级约束：  
![在这里插入图片描述][20190403093309577.png]  
表级约束：  
![在这里插入图片描述][20190403093717834.png]  
复合约束：  
![在这里插入图片描述][20190403093913268.png]  
**d)外键约束**，foreign key  
外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值必须来源于参照的表的主键。  
![在这里插入图片描述][20190403094912389.png]  
外键名是fk\_id,clases\_id外键，参考列为t\_classes表的classes\_id列。

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

insert语法格式：  
字段和值必须一一对应，个数必须相同，数据类型必须一致。

INSERT INTO tablename(columname1,columname2,columname3)
    VALUES(value1,value2,value3);

多种添加方式（指定列名和不指定列名）

--指定列名
    INSERT INTO employee(id,name,phone) VALUES(01,'Tom',110110110); 
    --不指定列名
    INSERT INTO employee VALUES(02,'Jack',119119119);
    --指定列名
    INSERT INTO employee(id,name) VALUES(03,'Rose');

最好采用指定列名的方法，若省略表示将表中的所有字段全部加上，省略之后程序不健壮。

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

*  delete删除表内数据。`delete from 表名 where 删除条件;`
 *  清除表内数据，保存表结构，用 truncate。`truncate table 表名;`
 *  删除表用 drop，什么都没有了。`drop table 表名;`  
    1、当你不再需要该表时， 用 drop;  
    2、当你仍要保留该表，但要删除所有记录时， 用 truncate;  
    3、当你要删除部分记录时， 用 delete。

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

*  对一列的修改  
    1)增加一列(新建列)

ALTER TABLE 表名字 ADD COLUMN 列名字 数据类型 约束;
    或：
    ALTER TABLE 表名字 ADD 列名字 数据类型 约束;

新增加的列，被默认放置在这张表的最右边。如果要把增加的列插入在指定位置，则需要在语句的最后使用**AFTER关键词**(“AFTER 列1” 表示新增的列被放置在 “列1” 的后面)。如果想放在第一列的位置，则使用 \*\*FIRST \*\*关键词。  
2)删除一列（删除列）

ALTER TABLE 表名字 DROP COLUMN 列名字;
    或： ALTER TABLE 表名字 DROP 列名字;

3)重命名一列

ALTER TABLE 表名字 CHANGE 原列名 新列名 数据类型 约束;

重命名语句后面的数据类型不能省略，否则重命名失败。修改数据类型可能会导致数据丢失，所以要慎重使用。  
4)改变数据类型  
要修改一列的数据类型，除了使用刚才的 CHANGE 语句外，还可以用这样的 MODIFY 语句：

ALTER TABLE 表名字 MODIFY 列名字 新数据类型;

*  对表的内容修改  
    1)修改表中的某个值

UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;

如把 TOM的age改为21，salary改为3000。

UPDATE employee SET age=21,salary=3000 WHERE name='Tom';

2)删除一行记录（删除行）

DELETE FROM 表名字 WHERE 条件;

3)新建行  
就是插入了，INSERT

# 二、作业 #

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

创建表

CREATE TABLE courses(
    	student VARCHAR(6),
    	class VARCHAR(10)
    );

插入值

INSERT INTO courses(student,class)
    VALUES('A','Math');
    INSERT INTO courses(student,class)
    VALUES('B','English');
    INSERT INTO courses(student,class)
    VALUES('C','Math');
    INSERT INTO courses(student,class)
    VALUES('D','Biology');
    INSERT INTO courses(student,class)
    VALUES('E','Math');
    INSERT INTO courses(student,class)
    VALUES('F','Computer');
    INSERT INTO courses(student,class)
    VALUES('G','Math');
    INSERT INTO courses(student,class)
    VALUES('H','Math');
    INSERT INTO courses(student,class)
    VALUES('I','Math');
    INSERT INTO courses(student,class)
    VALUES('A','Math');

+---------+----------+
    | student | class    |
    +---------+----------+
    | A       | Math     |
    | B       | English  |
    | C       | Math     |
    | D       | Biology  |
    | E       | Math     |
    | F       | Computer |
    | G       | Math     |
    | H       | Math     |
    | I       | Math     |
    | A       | Math     |
    +---------+----------+

编写一个 SQL 查询，列出所有超过或等于5名学生的课。学生在每个课中不应被重复计算。

select class from courses group by class
    having 
        count(distinct student) >= 5; --distinct有重复记录去重
    +-------+
    | class |
    +-------+
    | Math  |
    +-------+

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

创建一个 salary表，有m=男性 和 f=女性的值 。交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。  
创建表并插入值

CREATE TABLE salary(
    	id int(2) primary key,
    	name VARCHAR(6),
    	sex CHAR(2),
    	salary VARCHAR(10)
    );
    
    INSERT INTO salary(id,name,sex,salary)
    VALUES(1,'A','m',2500);
    INSERT INTO salary(id,name,sex,salary)
    VALUES(2,'B','f',1500);
    INSERT INTO salary(id,name,sex,salary)
    VALUES(3,'C','m',5500);
    INSERT INTO salary(id,name,sex,salary)
    VALUES(4,'D','f',500);
    +----+------+------+--------+
    | id | name | sex  | salary |
    +----+------+------+--------+
    |  1 | A    | m    | 2500   |
    |  2 | B    | f    | 1500   |
    |  3 | C    | m    | 5500   |
    |  4 | D    | f    | 500    |
    +----+------+------+--------+
    4 rows in set (0.00 sec)

思路一：使用CASE…END

UPDATE salary SET sex= (CASE WHEN sex = 'm' THEN 'f' ELSE 'm' END);
    +----+------+------+--------+
    | id | name | sex  | salary |
    +----+------+------+--------+
    |  1 | A    | f    | 2500   |
    |  2 | B    | m    | 1500   |
    |  3 | C    | f    | 5500   |
    |  4 | D    | m    | 500    |
    +----+------+------+--------+
    4 rows in set (0.00 sec)

思路二:使用IF  
IF(expr1,expr2,expr3)  
如果 expr1 是TRUE ，则 IF()的返回值为expr2; 否则返回值则为 expr3。

UPDATE salary SET sex = IF(sex = 'm', 'f', 'm');
    +----+------+------+--------+
    | id | name | sex  | salary |
    +----+------+------+--------+
    |  1 | A    | m    | 2500   |
    |  2 | B    | f    | 1500   |
    |  3 | C    | m    | 5500   |
    |  4 | D    | f    | 500    |
    +----+------+------+--------+
    4 rows in set (0.00 sec)

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

1、MySQL别名  
[https://www.yiibai.com/mysql/alias.html][https_www.yiibai.com_mysql_alias.html]  
使用MySQL别名来提高查询的可读性。MySQL支持两种别名，称为列别名和表别名。  
1)列别名  
MySQL列的别名，可以使用AS关键字后跟别名；如果别名包含空格，必须将别名用引号引住。AS关键字可以省略。  
2)表别名  
表的别名称为表别名。像列别名一样，AS关键字是可选的，所以完全可以省略它。一般在包含INNER JOIN，LEFT JOIN，self join子句和子查询的语句中使用表别名。

2、Mysql 连接的使用  
[http://www.runoob.com/mysql/mysql-join.html][http_www.runoob.com_mysql_mysql-join.html]  
单表查询：查询时只从一张表检索数据  
连接查询/跨表查询：将多张表联合起来取得有效数据，在进行多表查询时，尽量给表起别名，效率高，可读性高。  
连接查询根据查询方式分为：  
**（inner join）内连接**：等值连接、非等值连接、自连接

--emp表
    +-------+--------+-----------+------+------------+---------+---------+--------+
    | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
    +-------+--------+-----------+------+------------+---------+---------+--------+
    |  7369 | SMITH  | CLERK     | 7902 | NULL       |  800.00 |    NULL |     20 |
    |  7499 | ALLEN  | SALESMAN  | 7698 | NULL       | 1600.00 |  300.00 |     30 |
    |  7521 | WARD   | SALESMAN  | 7698 | NULL       | 1250.00 |  500.00 |     30 |
    |  7566 | JONES  | MANAGER   | 7839 | 1981-02-04 | 2975.00 |    NULL |     20 |
    |  7654 | MARTIN | SALESMAN  | 7698 | NULL       | 1250.00 | 1400.00 |     30 |
    |  7698 | BLAKE  | MANAGER   | 7839 | NULL       | 2850.00 |    NULL |     30 |
    |  7782 | CLARK  | MANAGER   | 7839 | 1981-09-06 | 2450.00 |    NULL |     10 |
    |  7788 | SCOTT  | ANALYST   | 7566 | NULL       | 3000.00 |    NULL |     20 |
    |  7844 | TURNER | SALESMAN  | 7698 | 1981-08-09 | 1500.00 |    0.00 |     30 |
    |  7876 | ADAMS  | CLERK     | 7788 | NULL       | 1250.00 |    NULL |     20 |
    |  7900 | JAMES  | CLERK     | 7566 | 1981-03-12 | 1250.00 |    NULL |     30 |
    |  7902 | FROD   | ANALYST   | 7782 | 1981-03-12 | 3000.00 |    NULL |     20 |
    |  7934 | MILLER | CLERK     | 7698 | NULL       | 1300.00 |    NULL |     10 |
    |  7839 | KING   | PRESIDENT | NULL | NULL       | 5000.00 |    NULL |     10 |
    +-------+--------+-----------+------+------------+---------+---------+--------+
    14 rows in set (0.00 sec)
    
    +--------+------------+----------+
    | DEPTNO | DNAME      | LOC      |
    +--------+------------+----------+
    |     10 | ACCOUNTING | NEW YORK |
    |     20 | RESEARCH   | DALLAS   |
    |     30 | SALES      | CHICAGO  |
    |     40 | OPERATIONS | BOSTON   |
    +--------+------------+----------+
    4 rows in set (0.00 sec)
    --salgrade表
    +-------+-------+-------+
    | GRADE | LOSAL | HISAL |
    +-------+-------+-------+
    |     1 |   700 |  1200 |
    |     2 |  1201 |  1400 |
    |     3 |  1401 |  2000 |
    |     4 |  2001 |  3000 |
    |     5 |  3001 |  9999 |
    +-------+-------+-------+
    5 rows in set (0.00 sec)

**（outter join）外连接**：左外连接，右外连接

**（cross join）交叉连接**：又称笛卡尔积连接，是左侧、右侧所有元组的任意组合。该连接一般情况下是没有实际意义的。

SELECT e.ename,d.dname FROM emp e JOIN dept d;

**(full join)全外连接**：所有表的所有行、列都会显示，彼此条件不匹配的值显示为null。mysql不支持全外连接，可以借助union联合左外连接、右外连接来实现。

SELECT * from emp e LEFT JOIN dept d ON e.deptno=d.deptno
    UNION
    SELECT * from emp e RIGHT JOIN dept d ON e.deptno=d.deptno;
    +-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
    | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | DEPTNO | DNAME      | LOC      |
    +-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
    |  7782 | CLARK  | MANAGER   | 7839 | 1981-09-06 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
    |  7934 | MILLER | CLERK     | 7698 | NULL       | 1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
    |  7839 | KING   | PRESIDENT | NULL | NULL       | 5000.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
    |  7369 | SMITH  | CLERK     | 7902 | NULL       |  800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    |  7566 | JONES  | MANAGER   | 7839 | 1981-02-04 | 2975.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    |  7788 | SCOTT  | ANALYST   | 7566 | NULL       | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    |  7876 | ADAMS  | CLERK     | 7788 | NULL       | 1250.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    |  7902 | FROD   | ANALYST   | 7782 | 1981-03-12 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    |  7499 | ALLEN  | SALESMAN  | 7698 | NULL       | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO  |
    |  7521 | WARD   | SALESMAN  | 7698 | NULL       | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO  |
    |  7654 | MARTIN | SALESMAN  | 7698 | NULL       | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO  |
    |  7698 | BLAKE  | MANAGER   | 7839 | NULL       | 2850.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
    |  7844 | TURNER | SALESMAN  | 7698 | 1981-08-09 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO  |
    |  7900 | JAMES  | CLERK     | 7566 | 1981-03-12 | 1250.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
    |  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |     40 | OPERATIONS | BOSTON   |
    +-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
    15 rows in set (0.00 sec)

# 四、作业 #

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

--在数据库中创建表1和表2，并各插入三行数据
    
    CREATE TABLE Person(
    	Personld int(2) primary key,
    	FirstName VARCHAR(10),
    	LastName VARCHAR(10)
    );
    
    INSERT INTO Person(Personld,FirstName,LastName)
    VALUES(1,'A','B');
    INSERT INTO Person(Personld,FirstName,LastName)
    VALUES(2,'C','D');
    INSERT INTO Person(Personld,FirstName,LastName)
    VALUES(3,'E','F');
    INSERT INTO Person(Personld,FirstName,LastName)
    VALUES(4,'G','H');
    +----------+-----------+----------+
    | Personld | FirstName | LastName |
    +----------+-----------+----------+
    |        1 | A         | B        |
    |        2 | C         | D        |
    |        3 | E         | F        |
    |        4 | G         | H        |
    +----------+-----------+----------+
    4 rows in set (0.00 sec)
    CREATE TABLE Address(
    	Addressld int(4) primary key,
    	Personld int(2),
    	City VARCHAR(10),
    	State VARCHAR(20)
    );
    INSERT INTO Address(Addressld,Personld,City,State)
    VALUES(11,1,'China','Beijing');
    INSERT INTO Address(Addressld,Personld,City,State)
    VALUES(12,3,'US','London');
    INSERT INTO Address(Addressld,Personld,City,State)
    VALUES(13,2,'US','New York');
    +-----------+----------+-------+----------+
    | Addressld | Personld | City  | State    |
    +-----------+----------+-------+----------+
    |        11 |        1 | China | Beijing  |
    |        12 |        3 | US    | London   |
    |        13 |        2 | US    | New York |
    +-----------+----------+-------+----------+
    --编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State
    --左外连接
    SELECT P.FirstName,P.LastName,A.City,A.State FROM Person P LEFT JOIN
    Address A ON P.Personld=A.Personld;
    +-----------+----------+-------+----------+
    | FirstName | LastName | City  | State    |
    +-----------+----------+-------+----------+
    | A         | B        | China | Beijing  |
    | E         | F        | US    | London   |
    | C         | D        | US    | New York |
    | G         | H        | NULL  | NULL     |
    +-----------+----------+-------+----------+
    4 rows in set (0.00 sec)

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

--email表
    +----+---------+
    | ID | Email   |
    +----+---------+
    |  1 | a@b.com |
    |  2 | c@d.com |
    |  3 | a@b.com |
    +----+---------+
    --编写一个 SQL 查询，来删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。
    --如果本题没有要求重复的邮箱里只保留 Id 最小 的那个，删除 email 表中所有重复的电子邮箱。就很简单
    SELECT distinct Email,ID from email;
    +---------+----+
    | Email   | ID |
    +---------+----+
    | a@b.com |  1 |
    | c@d.com |  2 |
    +---------+----+
    2 rows in set (0.00 sec)
    
    --自连接，将自身相等的email重复列出
    SELECT e1.* FROM email e1,email e2 WHERE e1.email=e2.email；
    +----+---------+
    | ID | Email   |
    +----+---------+
    |  1 | a@b.com |
    |  3 | a@b.com |
    |  2 | c@d.com |
    |  1 | a@b.com |
    |  3 | a@b.com |
    +----+---------+
    
    --找到其他记录中具有相同电子邮件地址的更大 ID,自身作比较
    SELECT e1.* FROM email e1,email e2 WHERE
    e1.email=e2.email AND e1.ID>e2.ID;
    +----+---------+
    | ID | Email   |
    +----+---------+
    |  3 | a@b.com |
    +----+---------+
    --将上面查找出的内容删除
    DELETE e1 FROM email e1,email e2 WHERE
    e1.email=e2.email AND e1.ID>e2.ID;
    +----+---------+
    | ID | Email   |
    +----+---------+
    |  1 | a@b.com |
    |  2 | c@d.com |
    +----+---------+

[http_www.runoob.com_mysql_mysql-data-types.html]: http://www.runoob.com/mysql/mysql-data-types.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0d1aWFiYmV5_size_16_color_FFFFFF_t_70]: /images/20220224/ba9aac4fd09146359773dc73d254cb49.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0d1aWFiYmV5_size_16_color_FFFFFF_t_70 1]: /images/20220224/738bb5da1c464a3d9036c8e509e8e998.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0d1aWFiYmV5_size_16_color_FFFFFF_t_70 2]: /images/20220224/1a88c99397f042f7a951cdc915483fb8.png
[20190403093309577.png]: /images/20220224/c8aeb49a1bef43d2a347f9d4b327c10f.png
[20190403093717834.png]: /images/20220224/b28cebb471cc42f8a7df1708e3dabf4e.png
[20190403093913268.png]: /images/20220224/d3eb12e4962d4b6bbd5ac5c902df2a20.png
[20190403094912389.png]: /images/20220224/950751dd36834846aaff5e82df556446.png
[https_www.yiibai.com_mysql_alias.html]: https://www.yiibai.com/mysql/alias.html
[http_www.runoob.com_mysql_mysql-join.html]: http://www.runoob.com/mysql/mysql-join.html