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

数据类型

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

整数型

  • 存放整型的数据:在SQL中,需要更多考虑磁盘空间,系统将整数类型又分为五类
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    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
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    浮点型:
    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);

时间日期类型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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中字符串类型被划分为六类
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    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岁,来自祖安...','基兰,露露,女枪','男','橄榄球');

链接数据库

登录:

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

退出:

1
2
3
quit 和 exit

ctrl+d

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

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

数据库操作

查看所有数据库

1
show databases;

使用数据库

1
2
3
4
use 数据库名;

例:
use testData;

查看当前使用的数据库

1
select database();

创建数据库

1
2
3
4
create database 数据库名 charset=utf8;

例:
create database python charset=utf8;

删除数据库

1
2
3
4
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')

创建表时添加约束

例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
---班级表【主表】

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);

创建表之后添加约束

例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
---添加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);

表操作

查看当前数据库中所有表

1
show tables;

查看表结构

1
desc 表名;

删除表

1
2
3
drop table 表名;
例:
drop table students;

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
语法:
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
)

修改表-添加字段

1
2
3
alter table 表名 add 列名 类型;
例:
alter table students add birthday datetime;

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

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

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

1
2
3
alter table 表名 modify 列名 类型及约束;
例:
alter table students modify birth date not null;

修改表-删除字段

1
2
3
alter table 表名 drop 列名;
例:
alter table students drop birthday;

查看表的创建语句

1
2
3
show create table 表名;
例:
show create table classes;

增删改查(curd)

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

增加

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

全列插入

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

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

部分列插入

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

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

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

全列多行插入

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

1
2
3
4
5
6
7
insert into 表名 values(...),(...)...;
例:
insert into classes values(0,'python1'),(0,'python2');

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

删除

一般推荐逻辑删除

1
2
3
delete from 表名 where 条件
例:
delete from students where id=5;

逻辑删除

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

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

修改

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

备份

运行mysqldump命令

1
2
3
4
5
mysqldump -u用户名 -p 需要备份的数据库名 > sql文件
例:
mysqldump –uroot –p pythonstest > python.sql;

# 按提示输入mysql的密码

恢复

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

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

# 根据提示输入mysql密码

视图

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

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

  • 主要作用

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

  • 定义视图

    1
    2
    3
    约定俗成,建议以v_开头

    create view 视图名称 as select语句;
  • 查看视图

    1
    2
    3
    查看表会将所有的视图也列出来

    show tables;

索引

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

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

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

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

如何使用

  • 查看索引
    1
    show index from 表名;
  • 创建索引
    如果指定字段是字符串,需要指定长度,建议长度与定义字段时的长度一致
    字段类型如果不是字符串,可以不填写长度部分
    1
    create index 索引名称 on 表名(字段名称(长度))
  • 删除索引
    1
    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表的默认引擎

1
2
3
4
5
6
7
8
9
10
11
12
---查看目前事务状态:
show variables like "autocommit";

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

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


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

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