SQL基础

之后学习SQL语句都是运行在MySQL上

数据类型

Mysql将数据类型分为三大类：
1.数值类型
2.字符串类型
3.日期类型

整数型

• 存放整型的数据：在SQL中，需要更多考虑磁盘空间，系统将整数类型又分为五类

  1.tinyint：迷你型   类似byte    最大：127
  2.smallint：小整型  2个字节     最大：65535
  3.mediumint：中整型    3个字节
  4.int：标准整型	   4个字节  （使用最多的）
  5.bigint：大整型    8个字节	
  
  ---创建一张整型表
  create table my_int(
  	int_1 tinyint,
      int_2 smallint,
      int_3 mediumint,
      int_4 int,
      int_5 bigint
  );
  
  ---向表中插入值
  正常插入
  insert into my_int values(-128,32767,100000,100000,100000);

浮点型

• SQL中：将小数类型分为两种，浮点型，定点型。

  浮点型：
  1.float
  	单精度 4个字节 精度小，会丢失精度
  2.double
  	双精度 8个字节 精度大，15位左右。
  3.decimal
  	定点型 8个字节 小数点固定，精度固定，绝对保证整数部分不会丢失。
  	
  ---创建一张表
  create table my_decimal(
  	float_f1 float(10,2),
      double_d1 double(10,2),
      decimal_d2 decimal(10,2)
  );
  
  --正常插入
  insert into my_decimal values(99999999.99,99999999.99,99999999.99);

时间日期类型

datetime：时间日期，格式 yyyy-MM-dd HH:mm:ss  公元
date：日期，就是datetime中的date部分【年月日】
time：时间，就是datetime中的time部分【时分秒】
timestamp：时间戳，格林威治时间。1970 - 至今
year：年份

--创建一张表
create table my_time(
	d1 datetime,
    d2 date,
    d3 time,
    d4 timestamp,
    d5 year
);

--插入数据
insert into my_time values(now(),now(),now(),now(),'1998');
insert into my_time values('2021-10-1',now(),now(),now(),'1998');
insert into my_time values(now(),now(),now(),'1970-01-01 00:00:01','1998');

--输出
mysql> select * from my_time;
+---------------------+------------+----------+---------------------+------+
| d1                  | d2         | d3       | d4                  | d5   |
+---------------------+------------+----------+---------------------+------+
| 2021-09-04 12:19:28 | 2021-09-04 | 12:19:28 | 1970-01-01 08:00:01 | 1998 |
+---------------------+------------+----------+---------------------+------+

字符串类型

• Mysql中字符串类型被划分为六类

  char：定长
  	char(18)：磁盘在定义结构的时候，就已经确定数据的存储长度
  varchar：变长
  	varchar(20)：按照实际数据量进行磁盘空间分配，实际使用多少，就是多少。 字符长度
  text ：文本字符串
  	text：一般超过255长度字节的都用text
  blob ：二进制存储
  	blob：存储文本二进制
  enum ：枚举
  	enum('男','女','未知','保密')：限定数据
  set	：集合
  	set('男','女','未知','保密')：多值，不能插入没有的数据
  
  ---创建一张表
  create table my_string(
  	c_1 char(11),
  	v_1 varchar(10),
      t_1 text,
      b_1 blob,
      e_1 enum('男','女','未知','保密'),
      s_1 set('足球','橄榄球','拳击')
  );
  
  ---正常插入
  insert into my_string values('13914767897','我爱你小芳','自我介绍一下，我是大炮，今年23岁，来自祖安...','基兰,露露，女枪','男','足球,拳击');
  
  ---错误。
  insert into my_string values('13914767897','我爱你小芳','自我介绍一下，我是大炮，今年23岁，来自祖安...','基兰,露露，女枪','男','橄榄球');

链接数据库

登录：

mysql -uroot -p
回车后输入密码，当前设置的密码为mysql

退出：

quit 和 exit
或
ctrl+d

登录成功后，输入如下命令查看效果

查看版本：select version();
显示当前时间：select now();

数据库操作

查看所有数据库

show databases;

使用数据库

use 数据库名;

例：
use testData;

查看当前使用的数据库

select database();

创建数据库

create database 数据库名 charset=utf8;

例：
create database python charset=utf8;

删除数据库

drop database 数据库名;

例：
drop database python;

约束

概念：对即将进入数据库的数据加以限制。
约束的分类：

1. 主键约束primary key
2. 外键约束foreign key(XXXX)references(XXXX)
3. 唯一约束unique
4. 非空约束not null
5. 检查约束【Mysql已经不支持】

• 自增长auto_increment
• 默认default
• 枚举enum('YYYY','XXXX')

创建表时添加约束

例子：

---班级表【主表】

create table class(

    c_id int primary key auto_increment,

    c_name varchar(20) not null,

    c_info varchar(50)

);

insert into class(c_name,c_info) values('开发班','Java开发班级GZ2107');

delete from class where c_id = 1;



---学生表【从表】

create table student(

    id int primary key auto_increment,

    name varchar(10) not null,

    sex enum('男','女') not null,

    age int,

    stuCode varchar(20) unique,

    class_id int,

    foreign key(class_id) references class(c_id)

);

---加入数据
insert into student(name,sex,age,stuCode,class_id) values('张三','男',20,'xxx-yyy-111',1);

insert into student(name,sex,age,stuCode,class_id) values('李四','男',20,'xxx-yyy-111',2);

insert into student(name,age,stuCode,class_id) values('王五',20,'xxx-yyy-222',1);

创建表之后添加约束

例子：

---添加addr字段
alter table class add addr varchar(50) not null;
---修改sex字段为枚举约束，并且默认“男”
alter table student modify sex enum('男','女') not null default '男';


---例子二
create table student_two(

    id int,

    name varchar(20),

    age int,

    sex enum('男','女'),

    stuCode varchar(20),

    class_id int

);

1.添加主键约束
    alter table student_two add primary key (id);

2.添加非空约束
    alter table student_two modify name varchar(20) not null;

3.添加唯一约束
    alter table student_two modify stuCode varchar(20)not null unique;
    alter table student_two add unique(stuCode);

4.添加外键约束
    alter table student_two add constraint foreign key(class_id) references class(c_id);

表操作

查看当前数据库中所有表

show tables;

查看表结构

desc 表名;

删除表

drop table 表名;
例：
drop table students;

创建表

语法：
CREATE TABLE table_name(
    column1 datatype constraint,
    column2 datatype,
    column3 datatype,
    .....
    columnN datatype,
    PRIMARY KEY(one or more columns)
);



---例：创建班级表
create table classes(
    id int unsigned auto_increment primary key not null,
    name varchar(10)
);

---例：创建学生表
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男','女','人妖','保密'),
    cls_id int unsigned default 0
)

修改表-添加字段

alter table 表名 add 列名 类型;
例：
alter table students add birthday datetime;

修改表-修改字段：重命名版

alter table 表名 change 原名 新名 类型及约束;
例：
alter table students change birthday birth datetime not null;

修改表-修改字段：不重命名版

alter table 表名 modify 列名 类型及约束;
例：
alter table students modify birth date not null;

修改表-删除字段

alter table 表名 drop 列名;
例：
alter table students drop birthday;

查看表的创建语句

show create table 表名;
例：
show create table classes;

增删改查(curd)

curd的解释: 代表创建（Create）、更新（Update）、读取（Retrieve）和删除（Delete）
由于查询语句过多且经常用后续会新开一章记录

增加

说明：主键列是自动增长，但是在全列插入时需要占位，通常使用0或者 default 或者 null 来占位，插入成功后以实际数据为准。

全列插入

值的顺序与表中字段的顺序对应

insert into 表名 values(...)
例：
insert into students values(0,’郭靖‘,1,'蒙古','2016-1-2');

部分列插入

值的顺序与给出的列顺序对应

    insert into 表名(列1,...) values(值1,...)
    例：
    insert into students(name,hometown,birthday) values('黄蓉','桃花岛','2016-3-2');

上面的语句一次可以向表中插入一行数据，还可以一次性插入多行数据，这样可以减少与数据库的通信

全列多行插入

值的顺序与给出的列顺序对应

insert into 表名 values(...),(...)...;
例：
insert into classes values(0,'python1'),(0,'python2');

insert into 表名(列1,...) values(值1,...),(值1,...)...;
例：
insert into students(name) values('杨康'),('杨过'),('小龙女');

删除

一般推荐逻辑删除

delete from 表名 where 条件
例：
delete from students where id=5;

逻辑删除

只显示客户需要看的东西，本质就是修改操作

---设置一个属性，1代表给客户看，0代表不给客户看。
    update 表名 set 属性名=1 where 关键值=1;
    例：
    update students set isdelete=1 where id=1;

修改

update 表名 set 列1=值1,列2=值2... where 条件
例：
update students set gender=0,hometown='北京' where id=5;

备份

运行mysqldump命令

mysqldump -u用户名 -p 需要备份的数据库名 > sql文件
例：
mysqldump –uroot –p pythonstest > python.sql;

# 按提示输入mysql的密码

恢复

连接mysql，创建新的数据库
退出连接，执行如下命令

mysql -u用户名 –p 新数据库名 < sql文件
mysql -uroot –p pythonNEW < python.sql

# 根据提示输入mysql密码

视图

问题：对于复杂的查询，往往是有多个数据表进行关联查询而得到，如果数据库因为需求等原因发生了改变，为了保证查询出来的数据与之前相同，则需要在多个地方进行修改，维护起来非常麻烦

概念：通俗的讲，视图就是一条SELECT语句执行后返回的结果集。视图是对若干张基本表的引用；是一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；

• 主要作用

  方便操作，特别是查询操作
  减少复杂的SQL语句，增强可读性。
  提高了重用性，就像一个函数
  对数据库重构，却不影响程序的运行
  提高了安全性能，可以对不同的用户
  让数据更加清晰

• 定义视图

  约定俗成，建议以v_开头
  
  create view 视图名称 as select语句;

• 查看视图

  查看表会将所有的视图也列出来
  
  show tables;

索引

问题：当数据量到达千万或以上级别时，查询效率便会逐渐下降。
遇到最多、最容易出问题还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。

概念：索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

原理：类似二叉树。
例子：如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。

注意事项：
索引不是越多越好，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。
建立索引会占用磁盘空间。

如何使用

• 查看索引

  show index from 表名;

• 创建索引
  如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
  字段类型如果不是字符串，可以不填写长度部分

  create index 索引名称 on 表名(字段名称(长度))

• 删除索引

  drop index 索引名称 on 表名;

事务

生活例子：

A用户和B用户是银行的储户，现在A要给B转账500元，那么需要做以下几件事：
检查A的账户余额>500元；
A 账户中扣除500元;
B 账户中增加500元;
正常的流程走下来，A账户扣了500，B账户加了500，皆大欢喜。

那如果A账户扣了钱之后，系统出故障了呢？A白白损失了500，而B也没有收到本该属于他的500。

以上的案例中，隐藏着一个前提条件：A扣钱和B加钱，要么同时成功，要么同时失败。事务的需求就在于此

概念：所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

事务四大特性(简称ACID)

一个很好的事务处理系统，必须具备这些标准特性：

• 原子性(Atomicity)
  一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性
• 一致性(Consistency)
  数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）
• 隔离性(Isolation)
  通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。）
• 持久性(Durability)
  一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）

如何使用

开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中。
表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎

---查看目前事务状态：
show variables like "autocommit";

--设置事务自动提交关闭
set autocommit = off; 

--设置事务自动提交开启
set autocommit = on; 


rollback; ---回滚
commit; ---提交

修改数据的命令会自动的触发事务，包括insert、update、delete
而在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据
后台操作的命令（如：select、update等）基本上都默认执行了事务

计算列

计算列是MySQL8新特性
什么叫计算列呢？简单来说就是某一列的值是通过别的列计算得来的。例如，a列值为1、b列值为2，c列
不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。
在MySQL 8.0中，CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。下面以CREATE TABLE为例进行讲
解。
举例：定义数据表tb1，然后定义字段id、字段a、字段b和字段c，其中字段c为计算列，用于计算a+b的
值。 首先创建测试表tb1，语句如下：

CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);

存储过程与函数

存储过程

语法：

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
存储过程体
END

以无输入无输出为例，查询emps所有数据，并命名叫select_all_data

DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
    SELECT * FROM emps;
END $
DELIMITER ;

调用存储过程
语法：CALL 存储过程名(实参列表)
CALL select_all_data()

存储函数

语法：

CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
    [characteristics ...]
BEGIN
    函数体 #函数体中肯定有 RETURN 语句
END

例子：创建存储函数，名称为email_by_name()，参数定义为空，该函数查询Abel的email，并返回，数据类型为字符串型。

DELIMITER //
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
    DETERMINISTIC
    CONTAINS SQL
    READS SQL DATA
BEGIN
    RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //
DELIMITER ;

调用
SELECT email_by_name();

区别

	关键字	调用语法	返回值	应用场景
存储过程	PROCEDURE	CALL 存储过程()	理解为有0个或多个	一般用于更新
存储函数	FUNCTION	SELECT 函数()	只能是一个	一般用于查询结果为一个值并返回时

窗口函数

8版本的新特新

特点：分组，但不聚合。

小结：窗口函数的特点是可以分组，而且可以在分组内排序。另外，窗口函数不会因为分组而减少原表中的行
数，这对我们在原表数据的基础上进行统计和排序非常有用。

公用表表达式

8版本的新特新

公用表表达式（或通用表表达式）简称为CTE（Common Table Expressions）。
CTE是一个命名的临时结果集，作用范围是当前语句。CTE可以理解成一个可以复用的子查询，当然跟子查询还是有点区别的，CTE可以引用其他CTE，但子查询不能引用其他子查询。所以，可以考虑代替子查询。

依据语法结构和执行方式的不同，公用表表达式分为 普通公用表表达式 和 递归公用表表达式 2 种。

公用表表达式的作用是可以替代子查询，而且可以被多次引用。
递归公用表表达式对查询有一个共同根节点的树形结构数据非常高效，可以轻松搞定其他查询方式难以处理的查询。

课外拓展：汉诺塔算法