（笔记）最近写的SQL语句，用于复习

SHOW DATABASES;
CREATE DATABASE mysql_study;
SHOW TABLES;
CREATE TABLE USER (NAME VARCHAR(20),PASSWORD VARCHAR(20));
DESCRIBE pet;
LOAD DATA LOCAL INFILE 'E:/mashen/mysqlTest/pet.txt' INTO TABLE pet;
INSERT INTO pet VALUE ('FishMan','loli');
SELECT * FROM pet;
UPDATE pet SET NAME='PigMan' WHERE OWNER='lzc';
SELECT NAME FROM pet;
ALTER TABLE pet ADD birth DATE ;.
UPDATE pet SET birth = '1996-06-30' WHERE NAME = 'Fluffy';
UPDATE pet SET birth = '1996-06-22' WHERE OWNER = 'Gwen';
SELECT NAME, birth FROM pet WHERE DAY(birth) = 30;
SELECT * FROM pet WHERE NULL = NULL;
SELECT 1 FROM DUAL WHERE nvl(NULL,-1)=nvl(NULL,-1)
SELECT * FROM pet WHERE OWNER LIKE '%d';
SELECT * FROM pet WHERE OWNER LIKE '______';
SELECT * FROM pet WHERE OWNER REGEXP 'w';
SELECT * FROM pet WHERE OWNER REGEXP '.';
SELECT * FROM pet WHERE OWNER REGEXP '^......$';
SELECT COUNT(*) FROM pet;
SELECT OWNER,COUNT(*) FROM pet GROUP BY OWNER;
CREATE TABLE EVENT(NAME VARCHAR(20),EventDate DATE,Descibe VARCHAR(20),TYPE VARCHAR(20));
LOAD DATA LOCAL INFILE 'C:/Users/Gwen/Desktop/pet.txt' INTO TABLE EVENT;
ALTER TABLE EVENT CHANGE TYPE event_describe VARCHAR(20);
ALTER TABLE pet ADD sex CHAR(1);


SELECT p1.name,p1.sex,p2.name,p2.sex,p1.owner
FROM pet AS p1,pet AS p2
WHERE p1.`owner` = p2.`owner` AND p1.`sex` = 'f' AND p2.`sex`= 'm';


SELECT DATABASE
SELECT DISTINCT OWNER FROM pet;


 CREATE TABLE shop (
    article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
    dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
    price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
    PRIMARY KEY(article, dealer));
    
    INSERT INTO shop VALUES
     (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
     (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);


SELECT MAX(article) AS maxArticle  FROM shop;


SELECT article,dealer,price FROM shop ORDER BY price DESC;


SELECT article,dealer,price FROM shop GROUP BY article;


INSERT INTO shop VALUES (2,'B','5.55'),(4,'6','7.77');


SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);
              


SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
SELECT * FROM shop WHERE price=@min_price OR price=@max_price;


CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    NAME CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);


CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    OWNER SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
)


INSERT INTO person VALUES (NULL, 'Antonio Paz');


SELECT @last := LAST_INSERT_ID();


INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);


INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);


SELECT * 
FROM person p,`shirt`s 
WHERE p.`id` = s.owner


SELECT * 
FROM person p,`shirt`s 
WHERE p.`id` = s.owner
AND p.`name` LIKE 'Lilliana%'
AND s.color <> 'white'


SELECT field1_index, field2_index
    FROM test_table WHERE field1_index = '1'
UNION
SELECT field1_index, field2_index
    FROM test_table WHERE field2_index = '1';
  
INSERT INTO person VALUES(3,'suguowen');
UPDATE  person SET NAME='suguoren' WHERE id = 3;


SELECT * FROM person;


DELETE FROM person WHERE id = 3;


CREATE TABLE USER (id INTEGER(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL , NAME VARCHAR(20),role VARCHAR(20))


CREATE INDEX idx_user_name ON USER(NAME);
CREATE INDEX idx_user_nameRole ON USER(NAME,role); 






CREATE TABLE sgw_dept(
id INT(11) NOT NULL AUTO_INCREMENT,
deptName VARCHAR(30) DEFAULT NULL,
locAdd VARCHAR(40) DEFAULT NULL,
PRIMARY KEY(id)
)


CREATE TABLE sgw_emp(
id INT(11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20) DEFAULT NULL,
deptid INT(11) DEFAULT NULL,
PRIMARY KEY(id),
CONSTRAINT id_fk FOREIGN KEY (deptid) REFERENCES sgw_dept(id)
)
`


insert into sgw_dept(deptName,locAdd) values('RD',11);
INSERT INTO sgw_dept(deptName,locAdd) VALUES('HR',12);
INSERT INTO sgw_dept(deptName,locAdd) VALUES('MK',13);
INSERT INTO sgw_dept(deptName,locAdd) VALUES('MIS',14);
INSERT INTO sgw_dept(deptName,locAdd) VALUES('FD',15);


INSERT INTO sgw_emp(name,deptid) values('z3',1);
INSERT INTO sgw_emp(NAME,deptid) VALUES('z4',1);
INSERT INTO sgw_emp(NAME,deptid) VALUES('z5',1);


INSERT INTO sgw_emp(NAME,deptid) VALUES('w5',2);
INSERT INTO sgw_emp(NAME,deptid) VALUES('w6',2);


INSERT INTO sgw_emp(NAME,deptid) VALUES('s7',3);


INSERT INTO sgw_emp(NAME,deptid) VALUES('s8',4);


INSERT INTO sgw_emp(NAME,deptid) VALUES('s9',99);


select * 
from sgw_dept d inner join sgw_emp e
on d.`id` = e.`deptid`