MySQL的基础表操作

学习内容1：表操作

1. MySQL表数据类型
2. 用SQL语句创建表
   语句解释
   设定列类型 、大小、约束
   设定主键
3. 用SQL语句向表中添加数据
   语句解释
   多种添加方式（指定列名；不指定列名）
4. 用SQL语句删除表
   语句解释
   DELETE
   DROP
   TRUNCATE
   不同方式的区别
5. 用SQL语句修改表
   修改列名
   修改表中数据
   删除行
   删除列
   新建列
   新建行

项目一

创建如下所示的 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名学生的课。

创建表：

CREATE TABLE courses (
	student VARCHAR (255),
	class VARCHAR (255)
);

INSERT INTO courses VALUES("A", "Math");

INSERT INTO courses VALUES("B", "English");

INSERT INTO courses VALUES("C", "Math");

INSERT INTO courses VALUES("D", "Biology");

INSERT INTO courses VALUES("E", "Math");

INSERT INTO courses VALUES("F", "Computer");

INSERT INTO courses VALUES("G", "Math");

INSERT INTO courses VALUES("H", "Math");

INSERT INTO courses VALUES("I", "Math");

INSERT INTO courses VALUES("A", "Math");

程序&运行结果：

项目二

创建一个 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，反之亦然)。要求使用一个更新查询，并且没有中间临时表。
新建表：

CREATE TABLE salary(
id int not NULL,
name varchar(1),
sex varchar(1),
salary int
);

insert into salary VALUES(1,"A","m",2500);
Insert into salary VALUES(2,"B","f",1500);
Insert into salary VALUES(3,"C","m",5500);
Insert into salary VALUES(4,"D","f",500);

程序：

update salary 
set sex = "" where sex="m";

update salary 
set sex = "m" where sex="f";

update salary 
set sex = "f" where sex="";

运行结果：

学习内容2：表联结

• MySQL别名
• INNER JOIN
• LEFT JOIN
• CROSS JOIN
• 自连接
• UNION
• 以上几种方式的区别和联系

项目三

项目五：组合两张表 （难度：简单）
在数据库中创建表1和表2，并各插入三行数据（自己造）
表1: Person

列名	类型
PersonId	int
FirstName	varchar
LastName	varchar

PersonId 是上表主键

表2: Address

列名	类型
AddressId	int
PersonId	int
City	varchar
State	varchar

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

create table person(
  persionid int not null primary key,
  firstname varchar(255),
  lastname varchar(255)
  );
  
create table address(
  addressid int not null primary key,
  persionid int not null,
  city varchar(255),
  state varchar(255)
  );
  
insert into person values(1, "a", "b");
insert into person values(2, "c", "d");
insert into person values(3, "e", "f");

insert into address values(1, 1, "cityA", "stateA");
insert into address values(2, 2, "cityB", "stateB");
insert into address values(3, 4, "cityC", "stateC");

程序：

select firstName, lastName, city, state 
from person LEFT JOIN address
on person.persionid = address.persionid

结果：

项目四

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

Id	Email
1	a@b.com
2	c@d.com
3	a@b.com

Id 是这个表的主键。
新建表：

CREATE TABLE emails(
id int not null PRIMARY key,
email varchar(255)
);

insert into emails values(1, "a@b.com");
insert into emails values(2, "c@b.com");
insert into emails values(3, "a@b.com");

程序：

DELETE from emails where id in(
SELECT e.x FROM(
SELECT max(id) x from emails GROUP BY(email) HAVING count(email)>1)e
);

结果：