Day09、10、11、12:基础SQL

 

 

SQL

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

  • DDL：Data Definition Language

DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。

  • DML：Data Manipulation Language

DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。

  • DQL：Data Query Language

DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。

语法特点

SQL语言关键字不区分大小写！！！但是，针对不同的数据库，对于表名和列名，有的数据库区分大小写，有的数据库不区分大小写。同一个数据库，有的在Linux上区分大小写，有的在Windows上不区分大小写。

所以，本教程约定：SQL关键字总是大写，以示突出，表名和列名均使用小写。

关系模型

表的每一行称为记录（Record），记录是一个逻辑意义上的数据。

表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段

主键

  • 和其他记录绝对不同的字段。

  • 记录一旦插入表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列影响。

  • 在业务中，身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均不可用作主键，作为主键最好是完全业务无关的字段，一般命名为id。

常见的可作为id的字段类型有：

 1. 自增整数类型
 2. 全局唯一GUID类型：使用一种全局唯一的字符串作为主键，类似`8f55d96b-8acc-4636-8cb8-76bf8abc2f57`。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。

外键

通过一个字段，把数据与另一张表关联起来，这种列称为外键。

外键的实现：

 ALTER TABLE students          
 ADD CONSTRAINT fk_class_id             //外键约束的名称
 FOREIGN KEY (class_id)                 //指定class_id作为外键                     
 REFERENCES classes (id);               //指定这个外键关联到classes表的id列里

外键的删除：

 ALTER TABLE students
 DROP FOREIGN KEY fk_class_id;

注意：删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN...实现的

关系数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。

索引

通过对数据库表创建索引，可以提高查询速度。

索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。

 ALTER TABLE students
 ADD INDEX idx_score (score);
 ADD INDEX idx_name_score (name, score);

唯一索引

在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。

但是，这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。

通过创建唯一索引，可以保证某一列的值具有唯一性。

数据库索引对于用户和应用程序来说都是透明的。

查询数据

基本查询

 SELECT * FROM <表名>

SELECT * FROM students; 查询students表的所有数据

条件查询

 SELECT * FROM <表名> WHERE <条件表达式>
 SELECT * FROM students WHERE score >= 80;

查询students表中score列大于等于80的数据

条件表达式可以用

<条件1> AND <条件2>表达满足条件1并且满足条件2。

 SELECT * FROM students WHERE score >= 80 AND gender = 'M';

第二种条件是<条件1> OR <条件2>，表示满足条件1或者满足条件2。

SELECT * FROM students WHERE score >= 80 OR gender = 'M';

第三种条件是NOT <条件>，表示“不符合该条件”的记录。

SELECT * FROM students WHERE NOT class_id = 2;

要组合三个或者更多的条件，就需要用小括号()表示如何进行条件运算。

SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';

如果不加括号，条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级。

条件表达式：

条件表达式举例1表达式举例2说明
使用LIKE判断相似 name LIKE 'ab%' name LIKE '%bc%' %表示任意字符，例如'ab%'将匹配'ab'，'abc'，'abcd'
使用<>判断不相等 score <> 80 name <> 'abc'  

投影查询

如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用SELECT 列1, 列2, 列3 FROM ...，让结果集仅包含指定列。这种操作称为投影查询。

SELECT id, score, name FROM students

使用SELECT 列1, 列2, 列3 FROM ...时，还可以给每一列起个别名，这样，结果集的列名就可以与原表的列名不同。它的语法是SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...。

SELECT id, score points, name FROM students;  --将score重命名为points

排序

可以加上ORDER BY子句。例如按照成绩从低到高进行排序：

SELECT id, name, gender, score FROM students ORDER BY score;

从高到低：

SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;

如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面。例如，查询一班的学生成绩，并按照倒序排序：

SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

分页查询

如果数据量很大，可以分页显示。

分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT <N-M> OFFSET <M>子句实现。我们先把所有学生按照成绩从高到低进行排序：

SELECT id, name, gender, score FROM students ORDER BY score DESC;
--分页 ，获取第一页的记录，LIMIT 3 OFFSET 0
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

  • LIMIT总是设定为pageSize；

  • 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提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。

仍然以查询students表一共有多少条记录为例，我们可以使用SQL内置的COUNT()函数查询：

SELECT COUNT(*) FROM students ;
COUNT(*)
10

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

通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果：

SELECT COUNT(*) num FROM students;
num
10

聚合查询同样可以使用WHERE条件。

  -- 查询男生数量
  SELECT COUNT(*) boys FROM students WHERE gender = 'M';

除了COUNT()函数外，SQL还提供了如下聚合函数：

函数说明
SUM 计算某一列的合计值，该列必须为数值类型
AVG 计算某一列的平均值，该列必须为数值类型
MAX 计算某一列的最大值
MIN 计算某一列的最小值 
--使用聚合查询计算男生平均成绩：
SELECT AVG(score) average FROM students WHERE gender = 'M';

分组聚合

对于聚合查询，SQL还提供了“分组聚合”的功能。我们观察下面的聚合查询：

--按class_id分组
SELECT COUNT(id) FROM students GROUP BY class_id;

也可以使用多个列进行分组。例如，我们想统计各班的男生和女生人数：

--统计个班男生和女生的数量
SELECT class_id,gender,COUNT(*) num  FROM students GROUP BY class_id,gender;

多表查询

SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据。

查询多张表的语法是：SELECT * FROM <表1> <表2>。

多表查询时，可以用投影来区别相同的列名。

SELECT
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
FROM 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;

小结：

  • 使用多表查询可以获取M x N行记录；

  • 多表查询的结果集可能非常巨大，要小心使用。

连接查询（JOIN查询）

最常用的一种内连接——INNER 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;

注意INNER JOIN查询的写法是：

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

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

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

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

外连接——OUTER JOIN

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;

有RIGHT OUTER JOIN，就有LEFT OUTER JOIN，以及FULL OUTER JOIN。它们的区别是：

  • INNER JOIN只返回同时存在于两张表的行数据。

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

  • LEFT OUTER JOIN则返回左表都存在的行。

修改数据

INSERT 增加

INSERT语句的基本语法是：

INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

可以一次性添加多条记录。

UPDATE 更新

UPDATE语句的基本语法是：

UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

在UPDATE语句中，更新字段时可以使用表达式。例如，把所有80分以下的同学的成绩加10分：

UPDATE students SET score=score+10 WHERE score<80;

如果WHERE条件没有匹配到任何记录，UPDATE语句不会报错，也不会有任何记录被更新。

使用UPDATE，我们就可以一次更新表中的一条或多条记录。

DELETE 删除

和UPDATE类似，DELETE语句也可以一次删除多条记录。

如果WHERE条件没有匹配到任何记录，DELETE语句不会报错，也不会有任何记录被删除。

MySQL

管理MySQL

数据库的启动

启动：

 net start mysql

关闭

 net stop mysql

登录与退出数据库

 mysql -u root -p
 --然后输入密码
 exit -- 退出

库

 --列出所有数据库
 show databases;
 --创建一个新的数据库
 create database <库名>
 --切换数据库
 USE <库名>
 ​

表

 --列出当前数据库的所有表
 show tables;
 --查看表的结构
 DESC <表名>;
 --创建表  (需要很精细的语言) 
 create table <.............>
 show create table <表名> --这是查看一个已经创建的表的结构
 --删除表
 drop table <表名>
 --修改表
 ---新增列 birth
 ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;
 ---修改 birth
 ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;
 ---删除 birth
 ALTER TABLE students DROP COLUMN birthday;

实用SQL语句

 

 

 

posted @ 2021-10-15 23:42  027code  阅读(91)  评论(0)    收藏  举报