MYSQL 基本练习

--创建email表格
CREATE TABLE email (
ID INT NOT NULL PRIMARY KEY,
Email VARCHAR(255)
);
--插入数据
INSERT INTO email(ID, Email)
VALUES ('1','a@b.com'),
       ('2','c@d.com'),
       ('3','a@b.com');
--查找 Email 表中所有重复的电子邮箱。
select Email,count(*) as count from email group by Email having count>1;       
--删除重复的邮箱，重复的邮箱里只保留 Id 最小 的那个。
DELETE e1 FROM email e1,email e2 WHERE e1.Email = e2.Email AND e1.ID > e2.IdD
delete from email where ID not in (select minid from (select min(ID) as minid from email group by Email) b);

--创建courses表
CREATE TABLE courses(
    student VARCHAR(255),
    class VARCHAR(255)
);
--插入数据
INSERT INTO courses(student,class)
VALUES('A','Math'),('B','English'),('C','Math'),('D','Biology'),
      ('E','Math'),('F','Computer'),('G','Math'),('H','Math'),
      ('I','Math'),('A','Math');
--列出所有超过或等于5名学生的课,学生在每个课中不应被重复计算。
select class from courses group by class having count(distinct student) >= 5;
--下面这一句比下面多一列记录count
select class,count(distinct student) as count from courses group by class having count(distinct student)>=5; 

--创建salary表
CREATE TABLE salary(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    sex VARCHAR(255),
    salary INT
); --AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
--插入数据     
INSERT INTO salary(name,sex,salary)
VALUES('A','m','2500'),('B','f','1500'),
      ('C','m','5500'),('D','f','5500');
--交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。      
UPDATE salary set sex=case sex
    when 'm' then 'f'
    when 'f' then 'm'
end;   

--创建cinema表
CREATE TABLE cinema(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    movie VARCHAR(255),
    description VARCHAR(255),
    rating FLOAT
); --AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
--插入数据
INSERT INTO cinema(movie,description,rating)
VALUES('War','great 3D','8.9'),('Science','fiction','8.5'),
      ('irish','boring','6.2'),('Ice Song','Fantacy','8.2'),
      ('House card','interesting','9.1');
--找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片，结果请按等级 rating 排列。
select * from cinema where description != 'boring' and id%2 = 1 ORDER BY rating DESC;

--创建Person表
CREATE TABLE Person(
    Personid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(255),
    lastname VARCHAR(255)
);
--插入数据
INSERT INTO Person(firstname,lastname)
VALUES('A','B'),('C','D'),('A','E');
-- --创建Address表
CREATE TABLE Address(
    Addressid INT PRIMARY KEY,
    Personid INT,
    City VARCHAR(255),
    State VARCHAR(255)
);
--插入数据
INSERT INTO Address(Addressid,Personid,City,State)
VALUES('123','3','Changsha','Hunan'),('456','4','Yueyang','Hunan'),
      ('124','5','Beijing','Beijing');
--：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State
SELECT FirstName, LastName, City, State from Person left join Address on Address.PersonId  = Person.PersonId;

CREATE DATABASE IF NOT EXISTS data3 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
use data3;
--创建Customers表格
CREATE TABLE Customers (
ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255)
);
INSERT INTO Customers(Name)
VALUES('Joe'),('Henry'),('Sam'),('Max');
--创建Orders表格
CREATE TABLE Orders (
ID INT NOT NULL PRIMARY KEY,
CustomerId INT
);
INSERT INTO Orders(ID,CustomerId)
VALUES('1','3'),('2','1');
--找出所有从不订购任何东西的客户
SELECT Name from Customers left join Orders ON Orders.CustomerId = Customers.ID where Orders.ID is Null;