MySQL 必知必会学习笔记(常用命令二)

CREATE TABLE students(student_id INT UNSIGNED, name VARCHAR(30), sex CHAR(1), birth DATE, PRIMARY KEY(student_id));

INSERT INTO students(student_id, name, sex, birth) VALUES(41048101, 'Lucy Green', '1', '1990-02-14');

SELECT name FROM students WHERE student_id = 41048101;

ALTER TABLE students MODIFY student_id INT(10) AUTO_INCREAMENT;
-- 创建数据库

CREATE DATABASE test_db;

SHOW CREATE DATABASE test_db\G;

DROP DATABASE test_db;

CREATE DATABASE test_db;

USE test_db;

CREATE TABLE tb_empl(id INT(1), name VARCHAR(25), deptID INT(11), salary FLOAT);

CREATE TABLE tb_empl2(id INT(1) PRIMARY KEY, name VARCHAR(25), deptID INT(11), salary FLOAT);

CREATE TABLE tb_empl3(id INT(1), name VARCHAR(25), deptID INT(11), salary FLOAT, PRIMARY KEY(id));

CREATE TABLE tb_empl4(name VARCHAR(25), deptID INT(11), salary FLOAT, PRIMARY KEY(name, deptID));

CREATE TABLE tb_dept1(id INT(11) PRIMARY KEY, name VARCHAR(22) NOT NULL, location VARCHAR(50));

CREATE TABLE tb_emp5(id INT(11) PRIMARY KEY, name VARCHAR(25), deptID INT(11), salary FLOAT,
    CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptID) REFERENCES tb_dept1(id));

CREATE TABLE tb_emp6(id INT(11) PRIMARY KEY, name VARCHAR(25) NOT NULL, deptID INT(11), salary FLOAT,
    CONSTRAINT fk_emp_dept2 FOREIGN KEY(deptID) REFERENCES tb_dept1(id));

CREATE TABLE tb_dept2(id INT(11) PRIMARY KEY,name VARCHAR(22) UNIQUE,location VARCHAR(50));

CREATE TABLE tb_dept3(id INT(11) PRIMARY KEY, name VARCHAR(22), location VARCHAR(50), CONSTRAINT STH UNIQUE(name));

CREATE TABLE tb_emp7(id INT(11) PRIMARY KEY, name VARCHAR(25) NOT NULL, deptID INT(11) DEFAULT 1111, salary FLOAT,
    CONSTRAINT fk_emp_dept3 FOREIGN KEY(deptID) REFERENCES tb_dept1(id));

CREATE TABLE tb_emp8(id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(25) NOT NULL, deptID INT(11), salary FLOAT,
    CONSTRAINT fk_emp_dept5 FOREIGN KEY(deptID) REFERENCES tb_dept1(id));

INSERT INTO tb_emp8(name,salary) VALUES('Lucy', 20000),('Lilei', 25000),('Hanmeimei', 23000);

DESC tb_emp8;

SHOW CREATE TABLE tb_emp8;

ALTER TABLE tb_dept3 RENAME TO tb_deptment3;

ALTER TABLE tb_dept1 MODIFY name VARCHAR(30);

ALTER TABLE tb_dept1 MODIFY location VARCHAR(24);

ALTER TABLE tb_dept1 CHANGE location loc INT(11);

ALTER TABLE tb_dept1 MODIFY  loc VARCHAR(11);

ALTER TABLE tb_dept1 ADD managerId INT(10);

ALTER TABLE tb_dept1 ADD column1 VARCHAR(12) NOT NULL;

ALTER TABLE tb_dept1 ADD colunm2 INT(11) FIRST;

ALTER TABLE tb_dept1 ADD column3 INT(11) AFTER name;

ALTER TABLE tb_dept1 DROP colunm2;

ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) FIRST;

ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) AFTER loc;

SHOW CREATE TABLE tb_deptment3;

ALTER TABLE tb_deptment3 ENGINE=MyISAM;

CREATE TABLE tb_emp9(id Int(11) PRIMARY KEY, name VARCHAR(25), deptId INT(11), salary FLOAT,
    CONSTRAINT fk_emp_dept FOREIGN KEY(deptId) REFERENCES tb_dept1(id));

ALTER TABLE tb_emp9 DROP FOREIGN KEY fk_emp_dept;

DROP TABLE IF EXISTS tb_dept2;

CREATE TABLE tb_dept2(id INT(11) PRIMARY KEY, name VARCHAR(22), location VARCHAR(50));

CREATE TABLE tb_emp(id INT(11) PRIMARY KEY, name VARCHAR(25), deptId INT(11), salary FLOAT,
    CONSTRAINT fk_emp_dept FOREIGN KEY(deptId) REFERENCES tb_dept2(id));

ALTER TABLE tb_emp DROP FOREIGN KEY fk_emp_dept;

DROP TABLE tb_dept2;

CREATE DATABASE company;

SHOW CREATE DATABASE company;

USE company;

CREATE TABLE offices(officeCode INT(10) NOT NULL UNIQUE,
    city INT(11) NOT NULL, address VARCHAR(50), country VARCHAR(50), postalCode VARCHAR(25) UNIQUE, PRIMARY KEY(officeCode));

CREATE TABLE offices_new(officeCode INT(10) NOT NULL UNIQUE,
    city INT(11) NOT NULL, address VARCHAR(50), country VARCHAR(50) NOT NULL, postalCode VARCHAR(25) UNIQUE, PRIMARY KEY(officeCode));

#ALTER TABLE offices MODIFY officeCode INT(10) NOT NULL UNIQUE PRIMARY KEY(officeCode));

#ALTER TABLE offices MODIFY officeCode PRIMARY KEY(officeCode));

ALTER TABLE offices ADD PRIMARY KEY(officeCode);

ALTER TABLE offices MODIFY country VARCHAR(50) NOT NULL;

CREATE TABLE employees(employeeNumber INT(11) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
    lastName VARCHAR(50) NOT NULL,firstName VARCHAR(50) NOT NULL,mobile VARCHAR(25) UNIQUE,
    officeCode INT(10) NOT NULL, jobTitle VARCHAR(50) NOT NULL, birth DATETIME NOT NULL,
    note VARCHAR(255), sex VARCHAR(5), CONSTRAINT fk_emp_ofc FOREIGN KEY(officeCode) REFERENCES offices(officeCode) );

ALTER TABLE employees MODIFY mobile VARCHAR(25) AFTER officeCode;

ALTER TABLE employees CHANGE birth employee_birth DATETIME;

ALTER TABLE employees MODIFY sex CHAR(1) NOT NULL;

ALTER TABLE employees DROP note;

ALTER TABLE employees ADD favoriate_activity VARCHAR(100);

DROP TABLE offices;

ALTER TABLE employees DROP FOREIGN KEY fk_emp_ofc;

ALTER TABLE employees ENGINE=MyISAM;

ALTER TABLE employees RENAME TO employees_info;

SHOW DATABASES;

CREATE DATABASE IF NOT EXISTS Market;

USE Market;

CREATE TABLE customers(c_num INT(11) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
    c_name VARCHAR(50), c_contact VARCHAR(50), c_city VARCHAR(50), c_birth DATETIME NOT NULL );

ALTER TABLE customers MODIFY c_contact VARCHAR(50) AFTER c_birth;

ALTER TABLE customers MODIFY c_name VARCHAR(70);

ALTER TABLE customers CHANGE c_contact c_phone VARCHAR(50);

ALTER TABLE customers ADD c_gender CHAR(1);

ALTER TABLE customers RENAME TO customers_info;

ALTER TABLE customers_info DROP c_city;

ALTER TABLE customers_info ENGINE=MyISAM;

CREATE TABLE orders(o_num INT(11) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT, o_date DATE, c_id VARCHAR(50),CONSTRAINT fk_or_cu FOREIGN KEY(c_id) REFERENCES customers_info(c_num));

CONSTRAINT fk_emp_ofc FOREIGN KEY(officeCode) REFERENCES offices(officeCode)

CREATE TABLE tmp1(x TINYINT, y SMALLINT, z MEDIUMINT, m INT, n BIGINT);

CREATE TABLE tmp1(x TINYINT, y SMALLINT, z MEDIUMINT, m INT, n BIGINT);

CREATE TABLE worker(ID INT AUTO_INCREAMENT NOT NULL PRIMARY KEY,Name VARCHAR(30));

INSERT INTO worker VALUES(NULL, 'jimy');

INSERT INTO worker VALUES(NULL, 'Tom');

INSERT INTO worker VALUES(NULL, 'Kevin'),(NULL, 'Michal'), (NULL,'Nick');

CREATE TABLE fruits(f_id CHAR(10) NOT NULL, s_id INT NOT NULL, f_name CHAR(255) NOT NULL, f_price DECIMAL(8,2) NOT NULL, PRIMARY KEY(f_id));

INSERT INTO fruits(f_id, s_id, f_name, f_price) VALUES('a1',101,'apple', 5.2),
('b1',101,'blackberry', 10.2),
('bs1',102,'orange', 11.2),
('bs2',105,'melon', 8.2),
('t1',102,'banana', 10.3),
('t2',102,'grape', 5.3),
('o2',103,'coconut', 9.2),
('c0',101,'cherry', 3.2),
('a2',103,'apricot', 2.2),
('l2',104,'lemon', 6.4),
('b2',104,'berry', 7.6),
('m1',106,'mango', 15.6),
('m2',105,'xbabay', 2.6),
('t4',107,'xbababa', 3.6),
('m3',105,'xxtt', 11.6),
('b5',107,'xxxx', 3.6);

CREATE TABLE test.t(s1 INT, PRIMARY KEY(s1));


DEMIMITER //

CREATE PROCEDURE handledemo()
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
    SET @x = 1;
    INSERT INTO test.t VALUES(1);
    SET @x = 2;
    INSERT INTO test.t VALUES(1);
    SET @x = 3;
END;
//
DELIMITER ;

IF val IS NULL
    THEN SELECT 'val' IS NULL;
    ELSE SELECT 'val' IS NOT NULL;
END IF;

CASE val
    WHEN 1 THEN SELECT 'val is 1';
    WHEN 2 THEN SELECT 'val is 2';
    ELSE SELECT 'val is not 1 or 2';
END CASE;

CASE
    WHEN val IS NULL THEN SELECT 'val is null';
    WHEN val < 0 THEN SELECT 'val is less than 0';
    WHEN val > 0 THEN SELECT 'val is greater than 0';
    ELSE SELECT 'val is 0';
END CASE

DELCARE id INT DEFAULT 0;
add_loop:LOOP
    SET id = id + 1;
        IF id >= 10 THEN LEAVE add_loop;
        END IF;
END LOOP add_loop;

add_num: LOOP
    SET @count = @count + 1;
    IF @count = 50 THEN LEAVE add_num;
END LOOP add_num;

CREATE PROCEDURE doiterate()
    BEGIN
        DECLARE p1 INT DEFAULT 0;
        my_loop:LOOP
            SET p1 = p1 + 1;
            IF p1 < 10 THEN ITERATE my_loop;
            ELSEIF p1 > 20 THEN LEAVE my_loop;
            END IF;
            SELECT 'p1 is BETWEEN 10 AND 20';
        END LOOP my_loop;
    END;
    
DECLARE id INT DEFAULT 0;
REPEAT
    SET id = id + 1;
    UNTIL id >= 10;
END REPEAT;

DECLARE i INT DEFAULT 0;
WHILE i < 10 DO
    SET i = i + 1；
END WHILE;

DELIMITER //

CREATE PROCEDURE CountProc(IN sid INT, OUT num INT)
BEGIN
    SELECT COUNT(*) INTO num FROM fruits WHERE s_id = sid;
END //

DELIMITER ;

CALL CountProc(101, @num);

SELECT @num;

DELIMITER //

CREATE FUNCTION CountProc(sid INT)
RETURNS INT
BEGIN
RETURN (SELECT COUNT(*) FROM fruits WHERE s_id = sid);
END //

DELEMITER ;

SELECT CountProc(101);

SHOW STATUS;

SHOW PROCEDURE STATUS LIKE 'C%'\G;

CREATE TABLE sch(id INT, name VARCHAR(50), glass VARCHAR(50));
INSERT INTO sch VALUES(1,'xiaoming','glass 1'),(2, 'xiaojun','glass 2');

DELIMITER //

CREATE FUNCTION count_sch()
RETURNS INT
RETURN (SELECT COUNT(*) FROM sch);
//

DELIMITER //

CREATE PROCEDURE add_id(OUT count INT)
BEGIN
    DECLARE itmp INT;
    DECLARE cur_id CURSOR FOR SELECT id FROM sch;
    DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_id;
    
    SELECT count_sch() INTO count;
    SET @sum = 0;
    OPEN cur_id;
    REPEAT
    FETCH cur_id INTO itmp;
    IF itmp <10
    THEN SET @sum = @sum + itmp;
    END IF;
    UNTIL 0 END REPEAT;
    CLOZE cur_id;
    
END;

SELECT @a, @sum //

CREATE TRIGGER testref
/* BEFORE INSERT ON test1 FOR EACH ROW  */
BEGIN INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END //

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='ins_num'\G;

GRANT SELECT,UPDATE ON *.* TO 'william'@'localhost' IDENTIFIED BY '111111';

SELECT Host,User, Select_priv,Update_priv FROM mysql.user WHERE user='william';

UPDATE `QB_SysWriSenImitation` SET `QuestNum` = 2 WHERE `Id` = '01-Imitation-Primary-061';