MySQL第二次任务

2.1 MySQL 基础 （二）- 表操作

#学习内容#

1. 用SQL语句向表中添加数据

   （1）语句：INSERT语句

insert into table 
values (value1,value2,value3);

必须给每一列提供一个值，如果某一列没有值，应当给NULL。

   （2）多种添加方式

a.插入完整的行

不指定列名：

默认按照表的列名顺序来，SQL 语句高度依赖于表中列的定义次序，还依赖于其容易获得的次序信息。 这种语句虽然简单，但是不安全。代码实例如（1）所示

指定列名：

表名的括号里给出列名，在插入行时， DBMS 将用 VALUES 列表中的相应值填入列表中的对应项。 VALUES 中的第一个值对应于第一个指定列名，第二个值对应于第二个列名，如此等等。因为提供了列名， VALUES 必须以其指定的次序匹配指定的列名，不一定按各列出现在表中的实际次序。其优点是，即使表的结构改变，这条INSERT 语句仍然能正确工作。 

inset into table (field1,field2,field3)
values(value1,value2,value3);

 b.插入检索出的数据

insert into table1(field1,field2) 
select (field1,field2) from table2;

 PS: 复制表：

create table table1 as 
select * from table2;

 

2. 用SQL语句删除表

   （1）语句解释：

    DELETE：从表中删除特定行

delete from table
where field1=value;

    DROP:删除整个表，包括表结构

DROP TABLE Customer;

    TRUNCATE：清除表内容，但保留表结构，等同于删除所有行

TRUNCATE TABLE Customer;

 

3. 用SQL语句修改表

 （1）修改列名

注意一定要是新列名+新数据类型

ALTER Table table1 
CHANGE Fileld newname varchar(30);

 （2） 修改表中数据

可使用UPDATE语句，包括：

• 要更新的表
• 列名和新值
• 确定要更新哪些行的过滤条件

UPDATE Table1 
SET field1=value1
where field2=value2

 （3）  删除列

alter table table1 drop  column name; --删除表列  

 （4）新建列

alter table table1 add column newfield varchar(30);

 4. 自动增加关键字

sql中的 auto_increment 关键字可以用在建立表格时起到自动增加的作用，不用手动输入。多用于id的增加。

如下列 SQL 语句把 "Persons" 表中的 "P_Id" 列定义为 auto-increment 主键：

CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

　　默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。要让 AUTO_INCREMENT 序列以其他的值起始，请使用下列 SQL 语法：

ALTER TABLE Persons AUTO_INCREMENT=100　　

　　如果要在已有的字段上设立自增字段，语法为：

ALTER TABLE tbl_name
MODIFY col_name Datatype auto_increment;

　　其中，数据类型是无论你改不改都要加上的，其他的not null等属性可以自己选择加或不加。

　　要在 "Persons" 表中插入新记录，我们不必为 "P_Id" 列规定值（会自动添加一个唯一的值）：

INSERT INTO TABLE Persons(FirstName,LastName) 
VALUES ('Bill','Gates')

　　上面的 SQL 语句会在 "Persons" 表中插入一条新记录。"P_Id" 会被赋予一个唯一的值。"FirstName" 会被设置为 "Bill"，"LastName" 列会被设置为 "Gates"。

但是值得注意的是， Persons(FirstName,LastName) 中，一定要给定除了自增字段之外的字段，不然会报错。

#作业#

创建如下所示的 courses 表 ，有: student (学生) 和 class (课程)。

例如,表:

+---------+------------+

| student | class      |

+---------+------------+

| A       | Math       |

| B       | English    |

| C       | Math       |

| D       | Biology    |

| E       | Math       |

| F       | Computer   |

| G       | Math       |

| H       | Math       |

| I       | Math       |

| A      | Math       |

+---------+------------+

 

编写一个 SQL 查询，列出所有超过或等于5名学生的课。

应该输出:

+---------+

| class   |

+---------+

| Math    |

+---------+

Note:

学生在每个课中不应被重复计算。

 代码：

 

create table courses (student varchar(30) NOT NULL, class varchar(30) NOT NULL);
INSERT INTO courses
VALUES
    ('A', 'Math'),
    ('A', 'Math'),
    ('B', 'English'),
    ('C', 'Math'),
    ('D', 'Bio'),
    ('E', 'Math'),
    ('F', 'Comp'),
    ('G', 'Math'),
    ('H', 'Math'),
    ('I', 'Math');

select class from (select distinct * from courses) t 
group by t.class 
having count(*)>5;

 

 

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

创建一个 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    |

代码：

CREATE TABLE salary (
    id INT NOT NULL auto_increment PRIMARY KEY,
    NAME VARCHAR (30) NOT NULL,
    sex VARCHAR (1) NOT NULL,
    salary DOUBLE NOT NULL
);

INSERT INTO salary (NAME, sex, salary)
VALUES
    ("A", "m", 2500);

INSERT INTO salary (NAME, sex, salary)
VALUES
    ("B", "f", 1500);

INSERT INTO salary (NAME, sex, salary)
VALUES
    ("C", "m", 5500);

INSERT INTO salary (NAME, sex, salary)
VALUES
    ("D", "f", 500);

 

 

update salary 
set sex=(case when sex='f' then 'm' 
else 'f' end);

 

2.2 MySQL 基础 （三）- 表联结

#学习内容#

• MySQL别名

• INNER JOIN

• LEFT JOIN

• CROSS JOIN

• 自连接

• UNION

• 以上几种方式的区别和联系

#作业#

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

在数据库中创建表1和表2，并各插入三行数据（自己造）

表1: Person

+-------------+---------+

| 列名 | 类型 |

+-------------+---------+

| PersonId | int |

| FirstName | varchar |

| LastName | varchar |

+-------------+---------+

PersonId 是上表主键

 

表2: Address

+-------------+---------+

| 列名 | 类型 |

+-------------+---------+t

| AddressId | int |

| PersonId | int |

| City | varchar |

| State | varchar |

+-------------+---------+

AddressId 是上表主键

 

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

 代码：

建表：

create table person (
PersonId int not null primary key auto_increment,-- 设置自增字段
FirstName varchar(30) not null,
LastName varchar(30) not null);

INSERT INTO Person(FirstName,LastName) VALUES('James','Lee');
INSERT INTO Person(FirstName,LastName) VALUES('Jack','Tom');
INSERT INTO Person(FirstName,LastName) VALUES('Sam','Brown');

create table Address (
AddressId int not null primary key auto_increment,
PersonId int not null references person.PersonId,
City varchar(30) not null,
State varchar(30) not null);

INSERT INTO Address(PersonId,City,State) VALUES(1,'Eugene','Oregon');
INSERT INTO Address(PersonId,City,State) VALUES(2,'Las Vegas','Nevada');
INSERT INTO Address(PersonId,City,State) VALUES(3,'Denver','Colorado');

alter table person modify personId int auto_increment; -- 新增加自增字段

 

查询：

 

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

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

+----+---------+

| Id | Email |

+----+---------+

| 1 | a@b.com |

| 2 | c@d.com |

| 3 | a@b.com |

+----+---------+

Id 是这个表的主键。

例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:

+----+------------------+

| Id | Email |

+----+------------------+

| 1 | a@b.com |

| 2 | c@d.com |

+----+------------------+