MYSQL基本練習

--  email  
CREATE TABLE email (
ID INT NOT NULL PRIMARY KEY,
Email VARCHAR(255)
);
--    
INSERT INTO email(ID, Email)
VALUES ('1','[email protected]'),
       ('2','[email protected]'),
       ('3','[email protected]');
--   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;