mysql命令巧记_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';