SQL基础教程-优快云博客

本文详细介绍了SQL的基本操作，包括创建和管理数据库、数据表，插入、更新、删除数据，以及复杂的查询技巧，如条件查询、排序、分组和聚合函数等。

-- 一、管理数据库
-- 1.1 创建数据库
CREATE DATABASE day15;

SHOW DATABASES;

CREATE TABLE student(
id INT,
NAME VARCHAR(20),
age INT
);

-- 查看表
SHOW TABLES;

-- 二、管理数据
-- 1.1插入数据（insert into）
-- 需求：往学生表插入数据
INSERT INTO student VALUES(1,'张三',20);
-- 1）插入的字段值顺序一定是按照表的字段顺序
INSERT INTO student VALUES('张三',1,20); -- ERROR 1366 (HY000): Incorrect integer value: 'eric' for column 'id' at row 1
-- 2）字段的数据一定要和值的数量要一致
INSERT INTO student VALUES(2,20); -- ERROR 1136 (21S01): Column count doesn't match value count at row 1
-- 3）插入部分字段
INSERT INTO student(id,NAME) VALUES(2,'李四');

-- 1.2 修改数据(update)
-- 需求：修改学生表
-- 1)批量操作（不建议经常使用这种）
UPDATE student SET NAME='张三';
-- 2)按条件修改,修改一个字段
-- 需求：修改id为2的学生姓名
UPDATE student SET NAME='李四' WHERE id=2;
-- 需求：　修改id为1的学生姓名和年龄
-- 3)修改多个字段
UPDATE student SET NAME='王五',age=40 WHERE id=1;

-- 1.3 删除数据（delete from）
-- 1) 全表数据删除
DELETE FROM student;
-- 2）按条件删除数据
DELETE FROM student WHERE id=2;
-- 3)truncate table也可以删除全表数据
TRUNCATE TABLE student;
-- 注意：delete from 和 truncate table 的区别？
-- 1）delete from删除全部，也可以按条件删除，但是truncate table只能全表删除，不能按条件删除
-- 2）delete from删除的数据可以回滚，truncate table删除的数据不能回滚。
-- 3）delete from不可以把自增长约束(auto_increment)重置，truncate table可以把自增长约束(auto_increment)重置

-- truncate table student where id=1; 错误的语法

-- 1.4 查看所有数据（重点中的重点）
SELECT * FROM student;

CREATE TABLE test(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
)

INSERT INTO test(NAME) VALUES('张三');
INSERT INTO test(NAME) VALUES('张三');

SELECT * FROM test;

DELETE FROM test;
TRUNCATE TABLE test;

-- 三、查询数据(select)
-- 3.1 查询所有字段
SELECT * FROM student;

-- 3.2 查询指定的字段
SELECT id,NAME FROM student;

-- 3.3 查询时指定别名
SELECT id AS '编号',NAME AS '姓名' FROM student;
-- AS可以省略
SELECT id '编号',NAME '姓名' FROM student;

-- 3.4 查询时添加常量列
-- 临时使用的列可以通过查询时动态添加进去
-- 需求：查询学生数据时添加一个"班级"列。值为“java就业班”
SELECT id AS '编号',NAME AS '姓名','java就业班' AS '班级' FROM student;

-- 3.5 查询时合并列
-- 需求：查询每个学生的总分。
SELECT NAME AS '姓名',(servlet+mysql) AS '总成绩' FROM student;
-- 注意：合并列的字段必须是数值类型的字段。非数值类型合并没有效果
SELECT NAME AS '姓名',(servlet+NAME) FROM student;

-- 3.6 查询去除重复记录(distinct)
-- 需求：查询有哪些的地区学生
SELECT DISTINCT address FROM student;
-- 另一种语法
SELECT DISTINCT(address) FROM student;

-- 3.7 条件查询(where)
-- 1)逻辑条件： and(与) or（或）
-- 当出现两个或两个以上的条件，那么这两个条件直接一定会存在逻辑关系。要么是与关系，要么或关系

-- 需求：查询学生的id为1，且姓名为张三的学生　
SELECT * FROM student WHERE id=1 AND NAME='张三'; -- (交集，结果数小于或等于任何一个条件的结果数)

-- 需求：查询学生的id为2，或姓名为张三的学生
SELECT * FROM student WHERE id=2 OR NAME='张三'; -- (并集，结果数一定会大于或等于任何一个条件的结果数)

-- 2)比较条件： > < >= <= = <> (between and)
-- 需求：查询servlet分数大于80分的学生
SELECT * FROM student WHERE servlet>80;
-- 需求：查询mysql分数小于或等于85分的学生
SELECT * FROM student WHERE mysql<=85;
SELECT * FROM student WHERE mysql<85 OR mysql=85;
-- 需求：查询servlet分数大于或等于80分，且小于或等于85分的学生
SELECT * FROM student WHERE servlet>=80 AND servlet<=85;
-- 上面sql代替的语法
SELECT * FROM student WHERE servlet BETWEEN 80 AND 85; -- 在..。之间（包前包后）
-- 需求：查询年龄不等于30岁的学生
SELECT * FROM student WHERE age<>30;

-- 3)判空条件： is null , is not null, ='' ， <>''
-- null：表示没有数据 is null is not null
-- 空字符：有数据 =''
-- 需求：查询没有性别数据的学生（数据‘男’或‘女’）
SELECT * FROM student WHERE gender IS NULL OR gender='';

-- 需求：查询有性别数据的学生
SELECT * FROM student WHERE gender IS NOT NULL AND gender<>'';

-- 4)模糊条件： like
-- 模糊替代符号：
-- %：替代任意个字符
-- _: 替代一个字符
-- 需求：查询姓‘李’的学生
SELECT * FROM student WHERE NAME LIKE '李%';
-- 需求：查询姓名中包含‘四’字的学生
SELECT * FROM student WHERE NAME LIKE '%四%';
-- 需求：查询姓‘李’，全名只有两个字的学生
SELECT * FROM student WHERE NAME LIKE '李_';

-- 3.8 聚合函数查询:用于统计结果
-- max() min() avg() count()
-- 需求：查询servlet的最高分
-- max() 取最大值
SELECT MAX(servlet) FROM student;
-- 需求：查询mysql的最低分
-- min(): 取最小值
SELECT MIN(mysql) FROM student;
-- 需求：查询servlt的平均分
-- avg(): 平均函数
SELECT AVG(servlet) FROM student;
-- 需求：查询当前有几个学生
-- count(): 统计表的记录数量
SELECT COUNT(*) FROM student;
-- count(id): 统计有值的id字段的数量(排除null的数据)
SELECT COUNT(id) FROM student;
-- 使用count统计表的数据，不要使用存在null的字段
SELECT COUNT(gender) FROM student;

-- 3.9 分页查询(limit)
-- limit 起始行数,查询的行数
-- 起始行数从0开始
SELECT * FROM student;
-- 需求：学生共20条数据，每页显示5条，共4页
-- 看第3页(第11到15条)的学生数据sql：select * from student limit 10,5;
-- 看第4页(第16到20条)的学生数据sql: select * from student limit 15,5;

-- 知道：当前页码，每页显示条数

-- 结论分页查询当前页数据的sql： select * from student limit (当前页码-1)*每页显示条数,每页显示条数;

-- 需求：查询第1,2条数据
SELECT * FROM student LIMIT 0,2;
-- 需求：查询第3,4条数据
SELECT * FROM student LIMIT 2,2;
-- 需求：查询第5,6条数据
SELECT * FROM student LIMIT 4,2;

-- 3.10 查询后排序（order by）
-- desc: 降序。数值从大到小，字母z-a
-- asc: 升序。数值从小到大，字母a-z
-- 默认情况下，按照插入的顺序排序
SELECT * FROM student;
-- 需求：按照id的升序排序
SELECT * FROM student ORDER BY id ASC;
-- 需求：按照servlet成绩降序排序
SELECT * FROM student ORDER BY servlet DESC;

-- 多个排序条件的情况：先按照前面的条件排序，当出现重复记录，再按照后面的条件排序
-- 需求：按照age升序，按照servlet成绩升序排序
SELECT * FROM student ORDER BY age ASC,servlet ASC;

-- select * from student order by username asc;

-- 3.11 分组查询(group by)
-- 需求：查询每个地区有多少人
-- 预期结果：
-- 广州天河 3
-- 广州越秀 1
SELECT address,COUNT(*) FROM student;

-- 1)对地区进行分组 2)在分组的基础可以进行统计，统计的是每组的数据
SELECT address,COUNT(*) FROM student GROUP BY address;
-- 需求：统计男女的人数
-- 注意： where条件必须放在group by分组之前
SELECT gender,COUNT(*) FROM student WHERE gender IS NOT NULL AND gender<>'' GROUP BY gender;

-- 3.12 分组后筛选(having)
-- 需求：查询哪些地区的人数大于2个的地区
-- 1）查询哪些地区多少人 2）筛选人数大于2的地区
-- 注意： having使用在group by分组之后的，对分组后的条件进行筛选
SELECT address,COUNT(*) FROM student GROUP BY address HAVING COUNT(*)>2 ;

-- 四、字段类型
-- char(20) vs varchar(20)
-- char(20): 固定长度的字符串。不管实际存储的数据的大小，一定占用20个字符空间
-- varchar(20): 可变长度的字符串。占用的空间大小就是实际存储的数据大小。

-- int vs int(4)
-- int: 默认最多11位，长度根据实际存储的数值的长度
-- int(4): 固定的数组长度
CREATE TABLE test(
id1 INT,
id2 INT(4) ZEROFILL -- zerofill: 零填充
)
INSERT INTO test VALUES(1,1);
SELECT * FROM test;

-- date vs datetime vs timestamp
-- date: 日期
-- datetime：日期+时间
-- timestamp: 时间戳,用于记录当前数据的插入或更新的时间
CREATE TABLE test_date(
date1 DATE,
date2 DATETIME,
date3 TIMESTAMP
)

INSERT INTO test_date(date1,date2) VALUES('2015-06-17 17:35:45','2015-06-17 17:35:45');

SELECT * FROM test_date;

UPDATE test_date SET date2='2015-06-17 17:40:45';

SELECT * FROM student;

ALTER TABLE student CHANGE NAME username VARCHAR(20);

CREATE TABLE student2(
id INT,
NAME VARCHAR(20),
chinese FLOAT,
english FLOAT,
math FLOAT
);

INSERT INTO student2(id,NAME,chinese,english,math) VALUES(1,'张小明',89,78,90);
INSERT INTO student2(id,NAME,chinese,english,math) VALUES(2,'李进',67,53,95);
INSERT INTO student2(id,NAME,chinese,english,math) VALUES(3,'王五',87,78,77);
INSERT INTO student2(id,NAME,chinese,english,math) VALUES(4,'李一',88,98,92);
INSERT INTO student2(id,NAME,chinese,english,math) VALUES(5,'李来财',82,84,67);
INSERT INTO student2(id,NAME,chinese,english,math) VALUES(6,'张进宝',55,85,45);
INSERT INTO student2(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);

SELECT * FROM student2;

-- 练习：
-- 查询表中所有学生的信息。
SELECT * FROM student2;

-- 查询表中所有学生的姓名和对应的英语成绩。
SELECT NAME,english FROM student2;

-- 过滤表中英语成绩的重复数据
SELECT DISTINCT english FROM student2;

-- 使用别名表示学生分数。
SELECT NAME AS '姓名',chinese AS '语文',english AS '英语',math AS '数学' FROM student2;

-- 查询姓名为李一的学生成绩
SELECT * FROM student2 WHERE NAME='李一';

-- 查询英语成绩大于等于90分的同学
SELECT * FROM student2 WHERE english>=90;

-- 查询总分大于200分的所有同学
SELECT * FROM student2 WHERE (chinese+english+math)>200;

-- 查询所有姓李的学生英语成绩。
SELECT NAME,english FROM student2 WHERE NAME LIKE '李%';

-- 查询英语>80或者总分>200的同学
SELECT * FROM student2 WHERE english>80 OR (chinese+english+math)>200;

-- 统计每个学生的总分。
SELECT NAME AS '姓名',(chinese+english+math) AS '总分' FROM student2;

-- 在所有学生总分数上加10分特长分。
SELECT NAME AS '姓名',(chinese+english+math+10) AS '总分' FROM student2;