sql

SQL语句练习

SELECT * FROM test;

SELECT * FROM testdemo;

DROP TABLE IF EXISTS `demo`;
CREATE TABLE `demo`(
    `id` CHAR(10) NOT NULL DEFAULT '' COMMENT 'ID',
    `name` VARCHAR(10) COMMENT '名称',
    `gender` CHAR(5) COMMENT '性别',
    PRIMARY KEY (`id`)
) ENGINE = innodb DEFAULT CHARSET = utf8 COMMENT = 'TEST';

INSERT INTO `demo` VALUES (001, 'seafwg', 'M');
INSERT INTO `demo` VALUES (002, 'intelwisd', 'F');
INSERT INTO `demo` VALUES (003, 'assassion', 'M');
INSERT INTO `demo` VALUES (004, 'seafwg', 'M');
INSERT INTO `demo` VALUES (005, 'intelwisd', 'F');
INSERT INTO `demo` VALUES (006, 'assassion', 'M');
INSERT INTO `demo` VALUES (007, 'seafwg', 'M');
INSERT INTO `demo` VALUES (008, 'intelwisd', 'F');
INSERT INTO `demo` VALUES (009, 'assassion', 'M');

SELECT * FROM demo;

ALTER TABLE demo ADD INDEX idx_id(id);

ALTER TABLE demo ADD UNIQUE INDEX uni_name(name);

SELECT id FROM test WHERE id >= 2;
SELECT * FROM test WHERE name = '测试2';

SELECT * FROM demo;
SELECT * FROM demo WHERE id >= 100 OR gender='F';
SELECT * FROM demo WHERE gender <> 'F';

demo: 创建表：

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students`(
    `id` char(5) NOT NULL DEFAULT '' COMMENT 'id',
    `name` VARCHAR(10) COMMENT '姓名',
    `score` INT(5) COMMENT '成绩',
    `classes` VARCHAR(5) COMMENT '班级',
    PRIMARY KEY (`id`)
)ENGINE = innodb DEFAULT CHARSET = utf8 COMMENT '用于学习';
# ENGINE=InnoDB: 数据库引擎
# COMMENT 字段的注释

demo: 插入数据：

INSERT INTO `students` VALUES (001,'seafwg', 90, 2);
INSERT INTO `students` VALUES (002,'assassion', 100, 1);
INSERT INTO `students` VALUES (003,'smallSeaf', 100, 2);
INSERT INTO `students` VALUES (004,'intelwisd', 95, 2);
INSERT INTO `students` VALUES (005,'wunworld', 10, 1);
INSERT INTO `students` VALUES (006,'xiaohua', 80, 1);
INSERT INTO `students` VALUES (007,'xiaofang', 60, 2);
INSERT INTO `students` VALUES (008,'lili', 100, 1);
INSERT INTO `students` VALUES (009,'fangfang', 50, 1);

1.查询语句

1.1 条件查询[多条件查询]

SELECT * FROM students WHERE score >= 95 AND classes = 2;
SELECT * FROM students WHERE (score <= 95 AND score >= 90) OR classes = 2;

[#总结：<,>,<=,>=,AND,OR,<>[不等于]]

1.2 投影查询：[只希望返回某些列的字段]

SELECT id, name FROM students;
SELECT score, classes FROM students;

1.2.1 列起别名查询[同样可以配合WHERE使用]

[列的别名只显示在查询的显示上，表的别名可以直接在查询语句中使用]

SELECT id, score point FROM students stu WHERE stu.score >= 95;

1.3 排序[默认排序是id也就是表中的主键]

[ORDER BY table DESC[降序]/ASC[升序] 如果有WHERE语句ORDER BY语句放在后面]

SELECT id, score, name FROM students WHERE classes=2 ORDER BY score DESC;

1.4 分页查询

[ LIMIT 每页显示量 OFFSET 开始量; [OR] LIMIT 开始量 每页显示量 ]

SELECT * FROM students ORDER BY id ASC LIMIT 5 OFFSET 0;

LIMIT总是设定为pageSize；

OFFSET计算公式为pageSize * (pageIndex - 1)

在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。

SELECT * FROM students LIMIT 0, 9;

1.5 聚合查询：SQL提供了专门的聚合函数，使用聚合函数进行查询

COUNT(*)表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT(*)。

SELECT COUNT(*) FROM students;
SELECT COUNT(*) NUM FROM students; # COUNT(*) 起别名
SELECT COUNT(id) NUM FROM students WHERE score >= 90;

常用的聚合函数：

# SUM  计算某一列的合计值，该列必须为数值类型
# AVG  计算某一列的平均值，该列必须为数值类型
# MAX  计算某一列的最大值
# MIN  计算某一列的最小值
# MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。

1.6 分组 GROUP BY

SELECT COUNT(*) num FROM students GROUP BY classes;
SELECT id, COUNT(*) num FROM students GROUP BY classes;
SELECT id,name,score, COUNT(*) num FROM students GROUP BY classes;

会有语法错误在任意一个分组中，进行了分组，name属性每个组中都不相同。

1.7 多表查询 SELECT * FROM <表1> <表2>

SELECT * FROM students, demo;

查询的结果也是一个二维表，它是students表和demo表的“乘积”,对两个各自有100行记录的表进行笛卡尔查询将返回1万条记录，对两个各自有1万行记录的表进行笛卡尔查询将返回1亿条记录

1.8 连接查询：多个表进行JOIN运算

先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上

假设我们希望结果集同时包含所在班级的名称，上面的结果集只有class_id列，缺少对应班级的name列。

1.8.1 内连接 [INNER JOIN]

SELECT stu.id, stu.name, stu.score, stu.classes, de.gender FROM students stu INNER JOIN demo de ON stu.id = de.id;

1. 先确定主表，仍然使用FROM <表1>的语法；

2. 再确定需要连接的表，使用INNER JOIN <表2>的语法；

3. 然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；

4. 可选：加上WHERE子句、ORDER BY等子句

SELECT s.id,d.gender,s.name,s.score FROM students s INNER JOIN demo d ON s.id = d.id;
SELECT s.id,s.name,d.gender,s.score FROM students s INNER JOIN demo d ON s.id = d.id where score >= 90;

1.8.2 外连接 [OUTER JOIN]

SELECT s.id,s.name,d.gender,s.score FROM students s RIGHT OUTER JOIN demo d ON s.id = d.id WHERE score > 90;

两者的区别：

1. INNER JOIN 只返回同时存在于两张表的行数据，由于students表的class_id包含1，2，3，classes表的id包含1，2，3，4，所以，INNER JOIN根据条件s.class_id = c.id返回的结果集仅包含1，2，3。

2. RIGHT OUTER JOIN 返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。

3. LEFT OUTER JOIN 则返回左表都存在的行。如果我们给students表增加一行，并添加class_id=5，由于classes表并不存在id=5的行，所以，LEFT OUTER JOIN的结果会增加一行，对应的class_name是NULL：

4. FULL OUTER JOIN，它会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL：

2 修改数据

2.1 插入数据 INSERT INTO table () VALUES () ...()

INSERT INTO students (id,name,score,classes) VALUES (010, 'xiaoff', 40, 2),(011, 'xiaohh', 60, 1);
select * FROM students ORDER BY id;

2.2 更新数据 UPDATE table SET 字段1=值,... WHERE ...

UPDATE students SET name='xff' WHERE id = 10;

如果WHERE条件没有匹配到任何记录，UPDATE语句不会报错，也不会有任何记录被更新,要特别小心的是，UPDATE语句可以没有WHERE条件,这时，整个表的所有记录都会被更新

2.3 删除数据 DELETE FROM table WHERE...

DELETE FROM students WHERE id=10;

要特别小心的是，和UPDATE类似，不带WHERE条件的DELETE语句会删除整个表的数据：

3.实用性SQL:

3.1 插入或替换：

插入操作时如果记录存在无法插入，此时可以使用REPLACE[如果不存在插入，存在删除插入]

REPLACE INTO students (id, name, score, classes) VALUES (6,'XH', 90, 1);

3.2 快照：

如果想要对一个表进行快照，即复制一份当前表的数据到一个新表，可以结合CREATE TABLE和SELECT：

需要注意的是快照生成的表没有主见等约束条件

CREATE TABLE students1 SELECT * FROM students;
SELECT * FROM students1;

3.3 写入查询结果集

DROP TABLE IF EXISTS `students2`;
CREATE TABLE `students2`(
    `id` CHAR(5) NOT NULL DEFAULT '' COMMENT 'id',
    `name` VARCHAR(10) COMMENT '姓名',
    `score` INT(10) COMMENT '成绩',
    `classes` VARCHAR(10) COMMENT '班级',
    PRIMARY KEY (id)
)ENGINE = innodb DEFAULT CHARSET = utf8 COMMENT '快照写入查询结果';

INSERT INTO students2 (ID, NAME, SCORE, CLASSES) SELECT * FROM students;