MySQL入門基礎文(添削改ざん)

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'tinydolphin'@'localhost';

--      
CREATE DATABASE sampdb;
--           
SELECT database();
--      
USE sampdb;

--      
CREATE TABLE president(
  last_name VARCHAR(15) NOT NULL COMMENT ' ',
  first_name VARCHAR(15) NOT NULL COMMENT ' ',
  suffix VARCHAR(5) NULL COMMENT '    ',
  city VARCHAR(20) NOT NULL COMMENT '   （  ）',
  state VARCHAR(2) NOT NULL COMMENT '   （ ）',
  brith DATE NOT NULL COMMENT '    ',
  death DATE NULL COMMENT '    '
) COMMENT='     ';
-- comment '  '

CREATE TABLE member(
  member_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
  PRIMARY KEY (member_id), --     
  last_name VARCHAR(15) NOT NULL COMMENT ' ',
  first_name VARCHAR(15) NOT NULL COMMENT ' ',
  suffix VARCHAR(5) NULL COMMENT '    ',
  expiration DATE NULL COMMENT '    ',
  email VARCHAR(100) NULL COMMENT '      ',
  street VARCHAR(50) NULL COMMENT '    （    ）',
  city VARCHAR(50) NULL COMMENT '    （   ）',
  state VARCHAR(2) NULL COMMENT '    （ ）',
  zip VARCHAR(10) NULL COMMENT '    ',
  phone VARCHAR(20) NULL COMMENT '    ',
  interests VARCHAR(255) NULL COMMENT '       '
) COMMENT='     ';
-- unsigned         & auto_increment   
-- auto_increment     ：       member_id   （        null），MySQL                
-- primary key       member_id               ，                    

--          
DESCRIBE president '%name';
DESC president;
EXPLAIN president;
SHOW COLUMNS FROM president LIKE '%name';

--             
SHOW TABLES ;
--                
SHOW DATABASES ;

CREATE TABLE student(
  name VARCHAR(20) NOT NULL COMMENT '  ',
  sex ENUM('F','M') NOT NULL COMMENT '  （F：  M： ）',
  student_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '  ID',
  PRIMARY KEY (student_id)
)ENGINE = InnoDB COMMENT='   ';
-- ENUM('F','M')：    
-- engine       MySQL                ，
--       MyISAM（indexed sequential access method：         ）

--    student   sex   
DESCRIBE student 'sex';

CREATE TABLE grade_event(
  data DATE NOT NULL COMMENT '  ',
  category ENUM('T','Q') NOT NULL COMMENT '     ',
  event_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '  ID',
  PRIMARY KEY (event_id)
) ENGINE = InnoDB COMMENT='     ';

CREATE TABLE score(
  student_id INT UNSIGNED NOT NULL COMMENT '  ID',
  event_id INT UNSIGNED NOT NULL COMMENT '  ID',
  score INT NOT NULL COMMENT '  ',
  PRIMARY KEY (event_id,student_id),
  INDEX (student_id),   --          ？       ②，      ①。
  FOREIGN KEY (event_id) REFERENCES grade_event(event_id), 
  FOREIGN KEY (student_id) REFERENCES student(student_id) 
) ENGINE = InnoDB COMMENT='   ';
-- foreign key           
-- references        
--    foreign key        ，    ：①、      ；②、                    (event_id)。
--   ，   InnoDB                     （student_id）     ，               。

CREATE TABLE absence(
  student_id INT UNSIGNED NOT NULL COMMENT '  ID',
  date DATE NOT NULL COMMENT '    ',
  PRIMARY KEY (student_id,date),
  FOREIGN KEY (student_id) REFERENCES student(student_id)
) ENGINE = InnoDB COMMENT='   ';

--        
-- 1、   INSERT       
-- (1)、             
INSERT INTO student VALUES ('kyle','M',NULL );
INSERT INTO grade_event VALUES ('2017-12-03','Q',NULL );
INSERT INTO student VALUES ('Avery','F',NULL),('Nathan','M',NULL);
--             ''    ""       ，   ''      。
--     auto_increment           "   "  NULL   ，MySQL                 。

-- (2)、          
INSERT INTO member (last_name,first_name) VALUES ('Stein','Waldo');
INSERT INTO student (name, sex) VALUES ('Abby','F'),('Joseph','M');

-- (3)、   col_name = value（  values()  ）  SET         
INSERT INTO member SET last_name='Stein',first_name='Waldo';
--             

-- 2、             （    ）
LOAD DATA LOCAL INFILE 'member.txt' INTO TABLE member;

--     
SELECT * FROM president;
--   ：                    ，                 ，   MySQL      
-- Windows          ，Unix        

-- 1、      
SELECT * FROM score WHERE score > 95;
--         
SELECT last_name,first_name,state FROM president
WHERE state='VA' OR state='MA';
SELECT last_name,first_name,state FROM president
WHERE state IN ('VA','MA');

-- 2、NULL  
--    NULL    NULL       
SELECT * FROM president WHERE death IS NULL ;
SELECT * FROM president WHERE death <=> NULL;
SELECT * FROM president WHERE suffix IS NOT NULL;
SELECT * FROM president WHERE NOT (suffix<=> NULL);

-- 3、             ：  ASC    DESC  
SELECT * FROM president ORDER BY last_name DESC,first_name ASC;
--    NULL      ，     ，     。
SELECT * FROM president ORDER BY if(death IS NULL ,0,1),death DESC;
-- IF()：          。   death   NULL，   0，   death    NULL，   1

-- 4、            
SELECT * FROM president ORDER BY brith LIMIT 5; --        5  
SELECT * FROM president ORDER BY brith DESC LIMIT 10,5; --        11-15  。
SELECT * FROM president ORDER BY rand() LIMIT 1; --       

-- 5、           
SELECT concat(first_name,' ',last_name) AS 'Name', concat(city,',',state) AS 'Brithplace' FROM president;
-- concat()：    ; AS：    

-- 6、    
--      、 、          year()、month()、dayofmonth()     
SELECT * FROM president WHERE month(brith) = 3;
SELECT * FROM president WHERE timestampdiff(YEAR,brith,death) = 21; --     death、brith      
-- to_days()         
SELECT date_add('2017-1-1',INTERVAL 10 MONTH); --    2017-11-01
SELECT date_sub('2017-12-20',INTERVAL 10 MONTH); --    2017-02-20

-- 7、    
SELECT * FROM president WHERE last_name LIKE 'W%';
SELECT * FROM president WHERE last_name LIKE '____'; --        4      

-- 8、      SQL   
--     ：@    :=  
SELECT @brith := brith FROM president --            brith    ，     
WHERE last_name='Jackson' AND first_name = 'Andrew';
--     ：@   
SELECT * FROM president WHERE brith < @brith ORDER BY brith;
-- set            ，  ，'='   ':='           
SET @today = curdate();
SET @one_week_ago := date_sub(@today , INTERVAL 7 DAY);
SELECT @today,@one_week_ago;

-- 9、      
-- distinct：               
SELECT DISTINCT state FROM president ORDER BY state;
-- count(*)          （  NULL）
SELECT count(*) FROM member;
SELECT count(*),count(email),count(expiration) FROM member;
SELECT count(DISTINCT state) FROM president;
SELECT sex,count(*) FROM student GROUP BY sex;
SELECT month(brith) AS Month,monthname(brith) AS Name, count(*) AS count
FROM president
GROUP BY Name
ORDER BY Month;
--          MySQL    ONLY_FULL_GROUP_BY        ，       ONLY_FULL_GROUP_BY     
SET GLOBAL SQL_MODE='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
--        SQL_MODE   
SELECT @@global.SQL_MODE;
-- count()                 having      
SELECT state,count(*) AS count FROM president
GROUP BY state HAVING count > 1 ORDER BY count DESC;
--      "    "，       with rollup   
--   ：            
SELECT sex,count(*) FROM student GROUP BY sex WITH ROLLUP;

-- 10、          
--   ：join &&    ：select        select   
-- ①、    
SELECT st.name,g.data,s.score,g.category
FROM grade_event g INNER JOIN score s INNER JOIN student st
ON g.event_id = s.event_id
AND s.student_id = st.student_id
WHERE g.data = '2008-09-23';
--     ：left join                
SELECT s.student_id,s.name,count(a.date) AS absences
FROM student s LEFT JOIN absence a
ON s.student_id = a.student_id
GROUP BY s.student_id;
-- ②、   ：       
SELECT * FROM student
WHERE student_id NOT IN (SELECT student_id FROM absence);

DELETE FROM president WHERE state = 'OH';
UPDATE member SET email='[email protected]',street='123 Elm St',city='Anytown',state='NY',zip='01003'
WHERE last_name = 'York' AND first_name='Jerome';