#### 【例4-1】
* 建庫
CREATE DATABASE chapter04;
* 建表
USE chapter04;
CREATE TABLE student(
id INT(3) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
grade FLOAT,
gender CHAR(2)
);
* 插入數據
INSERT INTO student(name,grade,gender)
VALUES('songjiang',40,'男'),
('wuyong',100,'男'),
('qinming',90,'男'),
('husanniang',88,'女'),
('sunerniang',66,'女'),
('wusong',86,'男'),
('linchong',92,'男'),
('yanqing',90,NULL);
* 查詢語句
SELECT id,name,grade,gender FROM student;
#### 【例4-2】
SELECT * FROM student;
#### 【例4-3】
SELECT name,gender FROM student;
#### 【例4-4】
SELECT id,name FROM student WHERE id=4;
#### 【例4-5】
SELECT name,gender FROM student WHERE name='wusong';
#### 【例4-6】
SELECT name,grade FROM student WHERE grade>80;
#### 【例4-7】
SELECT id,grade,name,gender FROM student WHERE id IN(1,2,3);
#### 【例4-8】
SELECT id,grade,name,gender FROM student WHERE id NOT IN(1,2,3);
#### 【例4-9】
SELECT id,name FROM student WHERE id BETWEEN 2 AND 5;
#### 【例4-10】
SELECT id,name FROM student WHERE id NOT BETWEEN 2 AND 5;
#### 【例4-11】
SELECT id,name,grade,gender FROM student WHERE gender IS NULL;
#### 【例4-12】
SELECT id,name,grade,gender FROM student WHERE gender IS NOT NULL;
#### 【例4-13】
SELECT DISTINCT gender FROM student;
#### 【例4-14】
* 插入數據
INSERT INTO student(name,grade,gender)
VALUES('songjiang',20,'男');
* 查詢
SELECT DISTINCT gender,name FROM student;
#### 【例4-15】
SELECT id,name FROM student WHERE name LIKE "s%";
#### 【例4-16】
SELECT id,name FROM student WHERE name LIKE 'w%g';
#### 【例4-17】
SELECT id,name FROM student WHERE name LIKE '%y%';
#### 【例4-18】
SELECT id,name FROM student WHERE name NOT LIKE '%y%';
#### 【例4-19】
SELECT * FROM student WHERE name LIKE 'wu_ong';
#### 【例4-20】
SELECT * FROM student WHERE name LIKE '____ing';
#### 【例4-21】
* 插入記錄
INSERT INTO student(name,grade,gender) VALUES('sun%er',95,'男');
* 查詢
SELECT * FROM student WHERE name LIKE '%\%%';
#### 【例4-22】
SELECT id,name,gender FROM student WHERE id<5 AND gender='女';
#### 【例4-23】
SELECT id,name,grade,gender
FROM student
WHERE id in(1,2,3,4) AND name LIKE '%ng' AND grade<80;
#### 【例4-24】
SELECT id,name,gender FROM student WHERE id<3 OR gender='女';
#### 【例4-25】
SELECT id,name,grade,gender
FROM student
WHERE name LIKE 'h%' OR gender='女' OR grade=100;
#### 【例4-26】
SELECT name,grade,gender
FROM student
WHERE gender='女' OR gender='男' AND grade=100;
#### 【例4-27】
SELECT COUNT(*) FROM student;
#### 【例4-28】
SELECT SUM(grade) FROM student;
#### 【例4-29】
SELECT AVG(grade) FROM student;
#### 【例4-30】
SELECT MAX(grade) FROM student;
#### 【例4-31】
SELECT MIN(grade) FROM student;
#### 【例4-32】
SELECT * FROM student
ORDER BY grade;
#### 【例4-33】
SELECT * FROM student ORDER BY grade ASC;
#### 【例4-34】
SELECT * FROM student ORDER BY grade DESC;
#### 【例4-35】
SELECT * FROM student
ORDER BY gender ASC,grade DESC;
#### 【例4-36】
SELECT * FROM student GROUP BY gender;
#### 【例4-37】
SELECT COUNT(*),gender FROM student GROUP BY gender;
#### 【例4-38】
SELECT sum(grade),gender FROM student GROUP BY gender HAVING SUM(grade)<300;
#### 【例4-39】
SELECT * FROM student LIMIT 4;
#### 【例4-40】
SELECT * FROM student ORDER BY grade DESC LIMIT 4,4;
#### 【例4-41】
SELECT CONCAT(id,'_',name,'_',grade,'_',gender) FROM student;
#### 【例4-42】
SELECT id,IF(gender='男',1,0) FROM student;
#### 【例4-43】
SELECT * FROM student AS s WHERE s.gender='女';
#### 【例4-44】
SELECT name AS stu_name,gender stu_gender FROM student;
