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_dbG;
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';