SQL学习笔记

SQL语法

Structured Query Language 结构化查询语言
定义了才做所有关系型数据库的规则
每一种数据库操作的方式存在不一样的地方(方言)

通用语法

SQL语句可以单行或者多行书写，以分号结尾
可使用空格和缩进来增强语句可读性
MySQL中的SQL不区分大小写，关键字建议大写
三种注释
- 单行注释：-- 注释内容(空格) 或 # 注释内容(MySQL特有#)
- 多行注释： /* 注释 */

SQL分类

DDL数据定义语言
- 客户端操作数据库、表
  - create drop alter ...
DML数据操作语言Manipulation
- 客户端增删改表中的数据
  - insert delete update ...
DQL数据查询语言Query
- 客户端查询表中的数据
  - select where ...
DCL数据控制语言
- 定义数据库的访问权限、安全级别，及创建用户
  - GRANT REVOKE ...

DDL数据定义语言

操作数据库：CRUD
1. C(Create):创建
  - 创建数据库:
    - create database 数据库名;
  - 创建数据库，不存在再创建：
    - create database if not exists 数据库名;
  - 创建数据库，并指定字符集
    - create database 名 character set 字符集名
  - 实例：创建数据库db1，判断是否存在，并制定字符集gbk
    - create database if not exists db1 character set gbk
2. R(Retrieve):查询
  - 查询所有数据库名：show databases;
  - 查询某数据库的创建语句/字符集：show create database mysql;
3. U(Update):修改
  *修改数据库的字符集
```
 * `alter database 数据库名 character set 字符集名;`
```
4. D(Delete):删除
  - 删除数据库
    - drop database 数据库名;
  - 判断数据库存在再删除
    - drop database if exists 数据库名;
5. 使用数据库 use 数据库名;
  - 查询当前正在使用的数据库名
    - select database();
操作表
1. C(Create):创建
  1. 语法：
```
 create table 表名(
     列名1 数据类型1,
     列名2 数据类型2,
     ...
     列名n 数据类型n #最后一列不加逗号
 );
```
  2. 数据类型：
    - int:整数类型 -> age int,
    - double:小数类型 -> score double(5,2)//位数,保留位数
    - date:日期 -> yyyy-MM-dd
    - datetime:日期 -> yyyy-MM-dd HH:mm:ss
    - timestamp:时间戳类型，格式同datetime
      - 若不给其赋值或赋值为null，则默认使用当前的系统时间，来自动赋值
    - varchar:字符串类型 -> name varchar(20),//姓名最长20字符
  3. 创建表
```
 create  table student(
     id int,
     name varchar(32),
     age int,
     score double(4,1),
     birthday date,
     insert_time timestamp
 );
```
  4. 复制表
    - create table 表名 like 已存在表名;
2. R(Retrieve):查询
  - 查询某数据库中所有表名:show tables;
  - 查询表结构：desc 表名;
3. U(Update):修改
  1. 修改表名
    - alter table 表名 rename to 薪的表名;
  2. 修改表的字符集
    - alter table 表名 character set 字符集名;
  3. 添加一列
    - alter table 表名 add 列名数据类型;
  4. 修改列名、类型
    - alter table 表名 change 列名新列名新数据类型;
    - alter table 表名 modify 列名新数据类型;
  5. 删除列
    - alter table 表名 drop 列名;
4. D(Delete):删除
  - drop table 表名;
  - drop table if exists 表名;

客户端图形化工具:SQLYog

注册
- Name：cr173
- Code：59adfdfe-bcb0-4762-8267-d7ffcf16beda
新建连接输入用户名密码
测试成功后
连接进入

DML:增删改表中数据

添加数据:
- 语法：
  - insert into 表名(列名1,列名2,...,列名n) values(值1,值2,...,值n);
- 注意：
  1. 列名和值要一一对应
  2. 如果表名后，不定义列名，则默认给所有列添加值(不能省略NULL)
  3. 除了数字类型，其他类型要用引号(单双都行)
删除数据:
- delete from 表名 [where 条件];
- 注意：
  1. 如果不加条件，则删除表中所有记录
  2. 若要删除所有记录:
    - delete from 表名; --不推荐，逐条记录进行删除操作，效率低
    - truncate table 表名; --推荐！先删除表,然后再创建一张一样的空表
修改数据:
- 语法：
  - update 表名 set 列名1=值1, 列名2=值2,... [where 条件];
- 注意：
  - 如果不加任何条件，则会将表中所有记录修改！

DQL:查询表中的记录

 `select * from 表名;`

语法：
- select 字段列表 from 表名列表 where 条件列表 group by 分组字段 having 分组后的条件 order by 排序 limit 分页限定;
基础查询
1. 多个字段的查询
  - 注意：如果查询所有字段可以用'*'来代替查询列表
```
select
  name, --姓名
  age   --年龄
  ...
  # 全张表用'*'代替
from
  student; --学生表
```
2. 去除重复
  - select distinct 列名 from 表名;
  - 去重时，查询出的多个结果集需完全一样才会去重
3. 计算列
  - select 列名1,列名2,...,列名1+列名2 from 表名
  - 一般可以使用四则运算计算列值(数值型)
  - 如果有null参与运算则结果均为null
    - 解决方案：select 列名1,列名2,...,列名1 + ifnull(列名2,0) from 表名
    - ifnull(表达式1,表达式2)
      - 表达式1：那个字段需要判断是否为null
      - 表达式2：若表达式1为null，替换为表达式2
4. 起别名
  - 上表中最后一列的列名为列名1 + ifnull(列名2,0)
    - 解决方案：select 列名1,列名2,...,列名1 + ifnull(列名2,0) as 总分 from 表名
    - 起别名的'as'可省略
条件查询
1. where子句后跟随条件
2. 运算符：
  - < >= <= = <>
    - 不等号 <>、!=
  - and && 、or || 、not ！
  - between .. and ..
    - select * from stu where age between 20 and 30; --查询年龄在20~30之间的记录(替换掉and)
  - in(集合)
    - select * from stu where age in(22,18,25); --查询年龄为18,22,25岁的记录(替换掉or)
  - is null
    - select * from stu where endlish IS (NOT) NULL; --查询英语成绩是/不是null的记录(null不能用=/<>/!=来判断)
  - like --模糊查询
    - 占位符：
      - _ : 单个任意字符
      - % : 多个任意字符(0个或多个)
    - select * from stu where name like '马%' ; --查询姓马的人的记录
    - select * from stu where name like '_化%' ; --查询姓名第二个字为化的人
    - select * from stu where name like '___' ; --查询名字是三个字的人
    - select * from stu where name like '%马%' ; --查询名字中有马的人
DQL高级查询语句
1. 排序查询
  - 语法：order by 语句
    - order by 排序字段1 排序方式1, 排序字段2 排序方式2,...
  - 排序方式
    - ASC：升序(默认)
    - DESC：降序
  - 实例：按照数学成绩排名，若数学一样，按英语成绩排
    - selecy * from stu ORDER BY math desc, english desc;
  - 注意：如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件
2. 聚合函数：将一列数据作为整体，进行纵向计算(平均分)
  1. count 计算个数
    - 一般选择非空的列：主键
    - select count(列名) from stu ;
  2. max/min：计算最大/最小值
  3. sum/avg：计算和/平均值
  - 注意：聚合函数的计算，会排除null，个数不计在内
    - 解决方案：
      1. 选择不包含非空的列进行计算
      2. IFNULL函数可以灵活运用
3. 分组查询
  - 语法：group by
  - 注意：
    1. 分组后查询的字段：分组字段，聚合函数
    2. where和having的区别
      - where在分组前进行限定，若不满足条件则不参与；having在分组后进行限定，若不满足结果则不会被查询
      - where后不可以跟聚合函数，而having可以
  - 实例
    - -- 按照性别分组查询男女平均分,人数要求：分数低于70不参与分组，分组后人数大于2人
    - SELECT sex,AVG(math) 平均分 ,COUNT(id) 人数 FROM student WHERE math>70 GROUP BY sex ;
4. 分页查询
  - limit分页——是一个‘方言’ 只能在MySQL中使用
  - 语法：limit 开始的索引, 每页查询的条数
  - 公式：开始的索引 = (当前页码 - 1) *每页条数
  - SELECT * FROM student LIMIT 0,3; -- 每页显示3条、第1页

约束

概念：对表中的数据进行限定，保证数据的正确性、有效性、完整性。(人不能没名字/必填项)
分类：
- 主键约束 primary key
- 非空约束 not null
- 唯一约束 unique
- 外键约束 foreign key

非空约束 not null
- 创建表时添加约束
```
  CREATE TABLE stu2(
  id INT,
  NAME VARCHAR(20) NOT NULL 
  );
```
- 创建完表后添加约束ALTER TABLE stu2 MODIFY NAME VARCHAR(20) NOT NULL;
- 删除name的非空约束ALTER TABLE stu2 MODIFY NAME VARCHAR(20) NOT NULL;
唯一约束 unique
- 添加约束：(同非空约束)
- 删除唯一约束 ALTER TABLE stu DROP INDEX phone_num;
- 注意：MySQL中，唯一约束限定的列的值可以有多个null
主键约束 primary key
1. 注意：
  - 含义：非空且唯一
  - 一张表只能有一个字段为主键
  - 主键是表中记录的唯一标识
2. 添加主键约束
  - 建表时
```
create table stu3(
  id int primary key,
  name varchar(20)
);
```
  - 建表后：ALTER TABLE stu3 MODIFY id INT PRIMARY KEY;
3. 删除主键
  - alter table stu3 modify id int; //错误，主键只有一个，不需指定
  - 正确方式：ALTER TABLE stu3 DROP PRIMARY KEY;
4. 自动增长
  - 概念:若某一列是数值类型，使用 auto_increment 实现值的自动增长,通常和主键并用
  - 实现自动增长
```
  create table stu3(
      id int primary key auto_increment,
      name varchar(20)
  );
  insert into stu3(id,name) values(0001,'lzj');
  insert into stu3(id,name) values(null,'lzj'); # [0002,'lzj']
```
  - 删除自动增长 ALTER TABLE stu MODIFY id INT;
  - 添加自动增长 ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

外键约束 foreign key ：让表之间产生关系，从而保证数据的正确性

创建表时，可添加外键

语法：

  create table 表名(
      ...
      外键列,
      constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
  );

删除外键 ALTER TABLE 表名 DROP FOREIGN KEY 外键名
添加外键 ALTER TABLE 表名 constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)

级联操作
1.添加级联操作

 * 语法:
     * 添加外键，设置级联更新 `ALTER TABLE 表名 ADD CONSTRAINT  FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE`
     * 设置级联删除 `ON DELETE CASCADE`  ——谨慎！若级联多，不安全效率低