mysql01

CREATE TABLE class(id int PRIMARY KEY AUTO_INCREMENT, cname varchar(30), description varchar(100)) charset utf8;
DESC class;
DROP TABLE IF EXISTS class;
CREATE TABLE class(id int PRIMARY KEY AUTO_INCREMENT, cname varchar(30) NOT NULL, description varchar(100) null) charset utf8;
DESC class;
INSERT INTO class SET cname = ‘php’, description = ‘study php’;
INSERT INTO class (cname, description) VALUES (‘linux’, ‘服务器知识’), (‘mysql’, ‘数据库学习’), (‘JS’, null);
CREATE TABLE test LIKE class;
DESC test;
INSERT INTO test SELECT * FROM class;
INSERT INTO test(cname) SELECT cname FROM class;
CREATE TABLE a SELECT * FROM class;
CREATE TABLE b (id int PRIMARY KEY AUTO_INCREMENT, cname varchar(30)) SELECT cname FROM class;
CREATE TABLE c (id int PRIMARY KEY AUTO_INCREMENT, name varchar(30)) SELECT cname FROM class;
CREATE TABLE d (id int PRIMARY KEY AUTO_INCREMENT, name varchar(30)) SELECT cname AS name FROM class;

SELECT * FROM class;
DESC class;
SELECT id AS ids, cname FROM class;

SELECT * FROM class WHERE id>2;
SELECT * FROM class WHERE cname = ‘mysql’;
SELECT * FROM class WHERE description LIKE ‘%p%’ AND id>2;
SELECT * FROM class;
SELECT * FROM class WHERE description LIKE ‘%p%’;
SELECT * FROM class WHERE id>2;
SELECT CONCAT(cname, description) AS class_info FROM class;

CREATE TABLE stu (id int PRIMARY KEY AUTO_INCREMENT, sname char(10), class_id int DEFAULT NULL, age SMALLINT NOT NULL);
INSERT INTO stu(sname, class_id, age) VALUES (‘tom’,1,23), (‘lisa’,2,22), (‘linda’,3,33), (‘mno3’,null,24), (‘mno2’,2,24);
SELECT * FROM stu WHERE sname LIKE ‘%n%’ OR class_id = 2;
SELECT DISTINCT class_id FROM stu;
SELECT * FROM stu WHERE age >=20 AND age<30;
SELECT * FROM stu WHERE age BETWEEN 23 AND 30;
SELECT * FROM stu WHERE age NOT BETWEEN 23 AND 30;
SELECT * FROM stu WHERE class_id = 2 OR class_id = 3;
SELECT * FROM stu WHERE class_id IN(2,3);
SELECT * FROM stu WHERE class_id NOT IN (2,3);
SELECT * FROM stu WHERE class_id = NULL;
SELECT * FROM stu WHERE class_id IS NULL;
SELECT * FROM stu WHERE class_id IS NOT NULL;
SELECT sname,IF(class_id,class_id,‘未分配班级’ ) FROM stu;
SELECT sname,ifnull(class_id,‘未分配’) FROM stu;

SELECT sname,age FROM stu ORDER BY age ASC;
SELECT class_id, sname, age FROM stu ORDER BY class_id ASC, age ASC;
SELECT * FROM stu ORDER BY id ASC LIMIT 1;
SELECT * FROM stu ORDER BY id ASC LIMIT 0,2;
SELECT * FROM stu ORDER BY id ASC LIMIT 1,2;
SELECT * FROM stu WHERE class_id =2 AND age IS NOT NULL ORDER BY age ASC LIMIT 1;
SELECT age FROM stu WHERE class_id =2 AND age IS NOT NULL ORDER BY age ASC LIMIT 1;
SELECT * FROM stu WHERE age = 22;
SELECT * FROM stu;
SELECT sname,age FROM stu ORDER BY age DESC;
SELECT * FROM stu ORDER BY age DESC, class_id ASC;
SELECT * FROM stu ORDER BY age DESC LIMIT 1;
SELECT * FROM stu ORDER BY age ASC LIMIT 1;
SELECT * FROM stu ORDER BY age DESC LIMIT 1,2;

SELECT * FROM stu WHERE class_id = 2 AND age IS NOT NULL ORDER BY age ASC LIMIT 1;
SELECT * FROM stu WHERE class_id = 2 AND age = 22;
SELECT * FROM stu WHERE age = (SELECT age FROM stu WHERE class_id = 2 AND age IS NOT NULL ORDER BY age ASC LIMIT 1);
SELECT * FROM stu;
UPDATE stu SET class_id = 2 WHERE class_id IS NULL;
SELECT * FROM stu;
UPDATE stu SET class_id = 1 WHERE age < 30;
SELECT * FROM stu;
UPDATE stu SET class_id = 3 WHERE age BETWEEN 23 AND 25;
SELECT * FROM stu;
UPDATE stu SET age = age+10 WHERE class_id = 1 AND age < 29;
SELECT * FROM stu;
INSERT INTO stu (sname, class_id, age) VALUES (‘郑卫’, 9, 25),(‘胡梦迪’, 6, 24),(‘张三’, 6, 55);
SELECT * FROM stu;
UPDATE stu SET class_id = 5 WHERE age = 55 AND sname LIKE ‘%三’;
DELETE FROM stu WHERE class_id = 3 AND age BETWEEN 30 AND 40;
SELECT * FROM stu;
UPDATE stu SET class_id = NULL,age = 22 WHERE sname LIKE ‘%om’;
DELETE FROM stu WHERE class_id IS NULL AND age < 30;
SELECT * FROM stu;
DELETE FROM stu ORDER BY id DESC LIMIT 2;
INSERT INTO stu SET sname = ‘煞笔’, age = 22, class_id = NULL;
INSERT INTO stu (sname, class_id ,age) VALUES (‘张三’, 4, 44),(‘李四’, 2, 36),(‘王二’, 6, 26);