SQL-1

SQL就是访问和处理关系数据库的计算机标准语言
NoSQL数据库，也就是非SQL的数据库，包括MongoDB、Cassandra、Dynamo等等，它们都不是关系数据库
数据库一共有三种模型：层次模型网状模型关系模型
REAL 即FLOAT24
主流的关系数据库主要分为以下几类：

商用数据库，例如：Oracle，SQL Server，DB2等；
开源数据库，例如：MySQL，PostgreSQL等；
桌面数据库，以微软Access为代表，适合桌面应用程序使用；
嵌入式数据库，以Sqlite为代表，适合手机应用和桌面程序。

SQL语言定义了这么几种操作数据库的能力：

DDL：Data Definition Language
DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。
DML：Data Manipulation Language
DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。
DQL：Data Query Language
DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。

不区分大小写
关系模型
- 关系数据库是建立在关系模型上的，关系模型本质上就是若干个存储数据的二维表
- 表的每一行称为记录（Record），记录是一个逻辑意义上的数据。
- 表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。
- 关系数据库的表和表之间需要建立“一对多”，“多对一”和“一对一”的关系
- 主键
  - 关系表任意两条记录不能重复。不是指两条记录不完全相同，而是指能够通过某个字段唯一区分出不同的记录，这个字段被称为主键。
  - 不使用任何业务相关的字段作为主键。因此，身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均不可用作主键
  - 可以使用多个列作为联合主键，但联合主键并不常用
- 外键
  - 在students表中，通过class_id的字段，可以把数据与另一张表关联起来，这种列称为外键。外键并不是通过列名实现的，而是通过定义外键约束实现的
  - ```
  ALTER TABLE students
  ADD CONSTRAINT fk_class_id
  FOREIGN KEY (class_id)
  REFERENCES classes (id);
```
- ```
ALTER TABLE students
DROP FOREIGN KEY fk_class_id;删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN ...实现的。
```
  - ```
  外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。
```
- 索引
  - ALTER TABLE students ADD INDEX idx_name_score (name, score);
  - 索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。
  - 使用主键索引的效率是最高的，因为主键会保证绝对唯一。
  - 唯一索引　
    - ```
    ALTER TABLE students
    ADD UNIQUE INDEX uni_name (name);
```
- 数据库索引对于用户和应用程序来说都是透明的
查询数据
- 虽然SELECT可以用作计算，但它并不是SQL的强项。但是，不带FROM子句的SELECT语句有一个有用的用途，就是用来判断当前到数据库的连接是否有效。许多检测工具会执行一条SELECT 1;来测试数据库连接。
- ```
条件查询
```
  - ```
  SELECT * FROM students WHERE score >= 80 AND gender = 'M';
```
- ```
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
```
  - name LIKE 'ab%'
  - WHERE score BETWEEN 60 AND 90 即 WHERE score >= 60 AND score <= 90
- 投影查询　
  - 使用SELECT 列1, 列2, 列3则可以仅返回指定列，这种操作称为投影。
  - SELECT语句可以对结果集的列进行重命名。
    SELECT id, score points, name FROM students WHERE gender = 'M
- 排序
  - 　SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;
- 分页查询
  - 使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。要实现分页功能，实际上就是从结果集中显示第1~100条记录作为第1页，显示第101~200条记录作为第2页，以此类推。
  - SELECT id, name, gender, score FROM students ORDER BY score DESC LIMIT 3 OFFSET 0;把结果集分页，每页3条记录。要获取第1页的记录，可以使用LIMIT 3 OFFSET 0，对结果集从0号记录开始，最多取3条
  - SELECT id, name, gender, score FROM students ORDER BY score DESC LIMIT 3 OFFSET 0；跳过第一页
  - OFFSET计算公式为pageSize * (pageIndex - 1)
  - OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集。
  - OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0。
    
    在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。
    
    使用LIMIT <M> OFFSET <N>分页时，随着N越来越大，查询效率也会越来越低。
- 聚合查询
  - 对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询
  - COUNT(*)表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT(*)。通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果：
    SELECT COUNT(*) FROM students; SELECT COUNT(*) num FROM students;~重命名
  - 如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符
  - 分组聚合
    - 如果我们要统计一班的学生数量可以用SELECT COUNT(*) num FROM students WHERE class_id = 1;。如果要继续统计二班、三班的学生数量，难道必须不断修改WHERE条件来执行SELECT语句吗？
      SELECT COUNT(*) num FROM students GROUP BY class_id;
    - SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
- 多表查询（笛卡尔查询）
  - SELECT * FROM students, classes; 是students表和classes表的“乘积”，即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。
  - 避免列名重复，利用投影查询的“设置列的别名” SELECT students.id sid, students.name, students.gender, students.score, classes.id cid, classes.name cnameFROM students, classes;
  - SQL还允许给表设置一个别名，让我们在投影查询中引用起来稍微简洁一点： SELECT
        s.id sid,
        s.name,
        s.gender,
        s.score,
        c.id cid,
        c.name cname
    FROM students s, classes c
    WHERE s.gender = 'M' AND c.id = 1;
- 连接查询
  - JOIN查询需要先确定主表，然后把另一个表的数据“附加”到结果集上； SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
    FROM students s
    INNER JOIN classes c
    ON s.class_id = c.id;
  - right/left/full outer join、inner join，如SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
    FROM students s
    FULL OUTER JOIN classes c
    ON s.class_id = c.id;
修改数据
- 增 INSERT INTO students (class_id, name, gender, score) VALUES (1, '大宝', 'M', 87),(2, '二宝', 'M', 81);SELECT * FROM students;
- 更新
  - UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7; SELECT * FROM students;
  - UPDATE students SET score=score+10 WHERE score<80;
- 删除
  - DELETE FROM students WHERE id=999;
其它实用语句
- 创建数据库mysql> CREATE DATABASE test;
  删除数据库mysql> DROP DATABASE test;
  切换数据库mysql> USE test;
  列出当前数据库的所有表mysql> SHOW TABLES;
  查看表结构mysql> DESC students;
  
  删除表mysql> DROP TABLE students;
  修改表
  增加列ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;
  修改列ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;
  删除列ALTER TABLE students DROP COLUMN birthday;
  
  EXIT仅仅断开了客户端和服务器的连接，MySQL服务器仍然继续运行。
- 插入或替换：
  若id=1的记录不存在，REPLACE语句将插入新记录，否则，当前id=1的记录将被删除，然后再插入新记录。
  REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
- 插入或更新：
  若id=1的记录不存在，INSERT语句将插入新记录，否则，当前id=1的记录将被更新，更新的字段由UPDATE指定。
  INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;
- 插入或忽略：
  若id=1的记录不存在，INSERT语句将插入新记录，否则，不执行任何操作。
  INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
- 快照:
  即复制一份当前表的数据到一个新表
  create table students_of_class1 select * from students where class_id=1;
- 查询结果集需要写入到表中:
  CREATE TABLE statistics (
      id BIGINT NOT NULL AUTO_INCREMENT,
      class_id BIGINT NOT NULL,
      average DOUBLE NOT NULL,
      PRIMARY KEY (id)
  );
  INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;
事务
- ACID

posted on 2019-09-02 14:39 rosyYY 阅读(174) 评论(0) 编辑收藏举报

刷新页面返回顶部

导航

SQL-1