文章目录
数据库定义
数据库就是一款帮助我们管理数据的程序
什么是SQL语句
操作数据库的软件命令
为什么要有SQL语句
数据库能够支持的语言非常多,为了避免识别问题,发明了SQL语句,规定只要是像使用数据库就统一使用SQL语句操作
数据库的分类
关系型数据库
具有固定的表结构,并且表与表之间可以通过外键建立关系
MySQL、MariaDB、Oracle、PostgreSQL、sqlserver、sqlite、db2
非关系型数据
没有固定的表结构,数据存储采用K:V键值对的形式
redis、mongoDB、memcache
通常将操作关系型数据库的语句称为SQL语句,操作非关系型数据库称为NoSQL语句
基本操作命令
登录服务端
mysql # 以游客模式登录
mysql -u用户名 -p密码 -hIP -P port
mysql -u用户名 -p密码
查看当前数据库所有名称
show databases;
取消之前的命令
\c
退出客户端
exit;
quit;
系统服务制作
解决路径切换问题
将bin目录路径添加到环境变量中
将mysql服务制做成系统服务(开机自启动)
先以管理员身份打开cmd窗口
输入制作命令
mysqld --install
启动服务
windows : net start mysql
mac: mysqld.server start
停止服务
windows : net stop mysql
mac: mysqld.server stop
修改管理员密码
#设置密码
mysqladmin -uroot -p password 123456
#修改密码
mysqladmin -uroot -p123456 password 123
#在客户端中修改
set password=PASSWORD('123456')
忘记密码
#1、关闭mysql服务
#2、以跳过授权表的方式重新启动
mysqld --skip-grant-tables
#3、修改指定用户的密码
update mysql.user set password=password(123) where uer="root" and host="localhost";
#4、退出重启服务
字符编码
#查看编码信息
\s
配置文件
mysql默认的配置文件 my-default.ini 复制一份
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oUbyjggz-1637070296127)(/Users/raoxurou/Library/Application Support/typora-user-images/image-20211109204032148.png)]
修改配置文件
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[root@db02 ~]# vim /etc/my.cnf
[root@db02 ~]# systemctl restart mysqld
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6FX02faU-1637070296134)(/Users/raoxurou/Library/Application Support/typora-user-images/image-20211109204741693.png)]
基本SQL语句的库操作
增
create database 数据库名称;
mysql> show databases;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Nijg30uf-1637070296136)(/Users/raoxurou/Library/Application Support/typora-user-images/image-20211109205230490.png)]
查
show databases; #查询所有数据库
show create database 数据库名称;
改
alter database 数据库名称 charset='编码' #修改编码
删
drop database 数据库名称;
基本SQL语句表操作
#切换数据库
mysql> use db;
Database changed
"""
查看当前所有数据库名称
select database();
"""
增
create table 表名(字段名称 字段类型) #创建表要给字段
mysql> create table t1(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+--------------+
| Tables_in_db |
+--------------+
| t1 |
+--------------+
1 row in set (0.00 sec)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ePCwYZKc-1637070296138)(/Users/raoxurou/Library/Application Support/typora-user-images/image-20211109210555477.png)]
查
show tables; #查看当前所有表的名称
show create table 表名;# 查看指定表的详细信息
describle 表名; #简写desc
mysql> show create table t1;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4Y48BTcu-1637070296139)(/Users/raoxurou/Library/Application Support/typora-user-images/image-20211109211120859.png)]
改
alter table 表名 rename 新表名
mysql> alter table t1 rename T1;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ots2JeOG-1637070296140)(/Users/raoxurou/Library/Application Support/typora-user-images/image-20211109211329532.png)]
删
drop table 表名;
mysql> drop tables T1;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mvJuPfSO-1637070296141)(/Users/raoxurou/Library/Application Support/typora-user-images/image-20211109211459249.png)]
基本SQL语句记录操作
'''
创建库和表。
create database db;
use db;
create table t1(id int ,name varchar(32));
'''
增
insert into 表名 values('按照字段顺序一一传值(逗号隔开)');
insert into tables_name values(...).(...),(...);
mysql> insert into t1 values(1,'cc'),(2,'xx');
查
select * from 表名; #查询所有字段的数据
select 字段名1,字段名2 from 表名; #可以通过库名,表名跨库
'''如果字段过多导致展示错乱 还可以使用\G结尾分行展示'''
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wpVrCrJB-1637070296143)(/Users/raoxurou/Library/Application Support/typora-user-images/image-20211109213525995.png)]
改
update 表名 set 字段名=新值 where 字段名=存在的值
mysql> update t1 set name='zz' where id=2;
删
delete from 表名 where 字段名='存在的值'
mysql> delete from t1 where id=1;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OBQAgt0M-1637070296145)(/Users/raoxurou/Library/Application Support/typora-user-images/image-20211109214431987.png)]
存储引擎
简单的理解为:存储引擎就是处理数据的底层逻辑 不同的引擎底层处理方式有有所不同
如何查看引擎信息
show engines;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DxvFtvu6-1637070296146)(/Users/raoxurou/Library/Application Support/typora-user-images/image-20211109214711207.png)]
MyISAM
是mysql5.5之前的默认引擎,该引擎存取数据的速度都很快,但安全性低,不支持很多额外的功能
InnoDB
是mysql5.5之后的默认引擎,该引擎支持事物,行级锁、外键。
存储数据的速度没有MyISAM快但是功能和安全性更高
MEMORY
数据全部存储在内存中,速度很快,但断电立刻消失
BLACKHOLE
黑洞 任何放入其中的数据都会丢失(类似于垃圾处理)
存储引擎之间的差异
'创建表可以指定引擎'
create table t1(id int) engine=存储引擎;
create table t2(id int) engine=myisam;
create table t3(id int) engine=innodb;
create table t4(id int) engine=memory;
create table t5(id int) engine=blackhole;
# 不同存储引擎文件后缀名
MyISAM
三个文件
.frm 表结构
.MYD 表数据
.MYI 表索引
InnoDB
两个文件
.frm 表结构
.ibd 数据与索引
memory
.frm 表结构
blackhole
.frm 表结构
插入数据
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
insert into t5 values(1);
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RKQMOFGz-1637070296148)(/Users/raoxurou/Library/Application Support/typora-user-images/image-20211109220459727.png)]
创建表的完整语法
create table table_name(字段名1 字段类型(数字) 约束条件,
字段名2 字段类型(数字) 约束条件,
字段名3 字段类型(数字) 约束条件
);
主要事项
1、字段名和字段类型是必须的 数字和约束条件是可选的
2、约束条件可以有多个
3、最后一个字段结尾不能有逗号
数据类型
整型
tinyint 1bytes
smallint 2bytes
int 4bytes
bigint 8bytes
'不同类型的int能够存储的数字范围不一样'
验证不同类型的是否会空出一个正负号
create table t7(id tinyint unsigned);
insert into t7 values(256),(-129);
浮点型
float(255,30) #总共255位小,数占30位
double(255,30) #总共255位,小数占30位
decimal(65,30) #总共65位,小数占30位
create table t8(id float(255,30));
create table t9(id double(255,30));
create table t10(id decimal(65.30));
insert into t8 values(1.111111111111);
insert into t9 values(1.111111111);
insert into t10 values(1.1111111);
'三者精确度不同'
decimal > double > float
字符类型
char(4) #定长
最多存储四个字符 超出则报错 没有超出则空格填充
varchar(4)
最多存储四个字符 超出则报错 没有超出有几个存几个
create table t11(id int ,name char(4));
create table t12(id int,name varchar(4));
insert into t11 values(1,'cc');
insert into t12 values(1,'qq');
日期相关类型
date 年月日
datetime 年月日时分秒
time 时分秒
year 年
create table user(id int(4) ,
name varchar(32),
reg_time datetime,
brithday time,
study_time time,
born_year year);
'字段可以加类似注释的说明 coment'
insert into user values(1,'jason','2000-11-11 11:11:11','2000-11-11','11:11:11','2000');
枚举
对选一:提前定义好数据后,后续录入只能录入定义好的内容之一
create table t2(
id int,
name char(6),
gender enum('male','femal','other')
);
insert into t2 values(1,'jason','male'); #正常
insert into t2 values(2,'kevin','男'); #报错
#################################################
mysql> create table t2(
-> id int,
-> name char(6),
-> gender enum('male','femal','other')
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t2 values(1,'jason','male');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values(2,'kevin','nan');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
集合
多选多:包含多选一,
create table t3(id int,
name char(32),
hobbies set('baskball','football','doublecolorball')
);
insert into t3 values(1,'jason','baskball'); #正常
insert into t3 values(2,'kevin','football,doublecolorball'); #正常
insert into t3 values(3,'cc','pingpangball'); #报错
#######################################################################
mysql> insert into t3 values(2,'kevin','football,doublecolorball');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 values(3,'cc','pingpangball');
ERROR 1265 (01000): Data truncated for column 'hobbies' at row 1
SQL_MODE
模糊查询
show variables like '%mode%';
修改
set session
set global
直接修改配置文件
sql_mode = 'strict_trans_tables'
数字在数字类型与字符类型中的区别
数字是否用来限制长度
create table t13(id int(3));
insert into t13 values(1111);
'数字在数字类型中不是用来限制存储长度,而是用来控制战术长度'
create table t14 (id int(8) unsignes zerofill);
insert into t14 values(1111);
insert into t14 values(11111);
'数字类型不指定数字,使用自带的即可'
create table t15 (id int);
char和varchar的差异
create table t16 (id int,name char(5));
insert into t16 values(1,'tom');
create table t17(id int,name varchar(5));
insert into t17 values(1,'tom');
'取消底层优化'
set global sql_mode='strict_trans_tables,pad_char_to_full_length'
统计某个字段数据的长度
char_length()
char和varchar那个好
char
优势:整存整取 速度快
劣势:浪费存储空间
varchar
优势:节省存储空间
劣势:存取数据的时候都需要考虑报头 速度较char慢
#创建库
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
#查看所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db |
| db1 |
| django |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.00 sec)
#使用库
mysql> use db1;
Database changed
#创建表字段
mysql> create table t1(id int ,name char(32));
Query OK, 0 rows affected (0.00 sec)
#查看表
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
+---------------+
1 row in set (0.01 sec)
#在新创建的表中加入数据
mysql> insert into t1 values(1,'cc'),(2,'dd');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
#查看表记录内容
mysql> select id,name from t1;
+------+------+
| id | name |
+------+------+
| 1 | cc |
| 2 | dd |
+------+------+
2 rows in set (0.00 sec)
#删除一条记录
mysql> delete from t1 where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> select id,name from t1;
+------+------+
| id | name |
+------+------+
| 2 | dd |
+------+------+
1 row in set (0.00 sec)
#查看表字段
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(32) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
约束条件
约束条件相当于是在字段上添加的额外约束(条件)
1.unsigned
无符号
id int unsigned
2.zerofull
0填充
id int zerofull
3.not null
非空
create table t4(id int,name varchar(16) not null);
insert into t4(name) values('jason');
create table t5(id int,name varchar(16));
insert into t5(id) values(1);
#######################################################################
mysql> create table t4(id int,name varchar(16) not null);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t4(name) values('jason');
Query OK, 1 row affected (0.00 sec)
mysql> create table t5(id int,name varchar(16));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t5(id) values(1);
Query OK, 1 row affected (0.00 sec)
4.default
默认值(用户给了就用用户的,用户没给就用默认的)
create table t6(id int,name varchar(16) default '匿名用户');
insert into t6(id) values(1);
insert into t6(id,name) values(2,'cc');
#######################################################################
mysql> create table t6(id int,name varchar(16) default '');
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t6(id) values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t6(id,name) values(2,'cc');
Query OK, 1 row affected (0.01 sec)
5.unique
唯一(保证字段(一个、多个)在整个表中没有重复的数据)
#单列唯一
create table t7(id int,name char(4) unique);
insert into t7 values(1,'zz'),(2,'cc');
#联合唯一
create table server(id int,
host varchar(4),
port varchar(16),
unique(host,port)
);
6.primary key
主键
1、从约束条件上而言主键相当于not null + unique(非空且唯一)
2.主键的功能目前简单的理解为能够加快数据的查询速度,相当于书的目录
3.InnDB存储引擎规定每张表都必须有且有一个主键
表中没有任何的主键和其他约束条件
InnDB默认会采用一个隐藏字段作为表的主键
表中没有主键但是有非空且唯一的字段
InnDB会自动将该字段升级为主键
'结论:每张表都必须要有一个id字段并且该字段就是主键'
#单列主键
create table t8(id int primary key,
name varchar(16)
);
create table t9(id int,
name varchar(16),
age int not null unique,
phone bigint not null unique,
num int not null unique
);
#多列主键(联合主键)
create table t10(id int ,
age int,
name varchar(16),
primary key(id,age)
);
7.auto_increment
自增(专门配合主键一起使用的,让主键能够自增)
create table t11(id int primary key auto_increment,
name varchar(32)
);
'''
在插入数据的时候还可以指定表字段
insert into t3(name) values('jason')
'''
主键自增特性
1.主键自增是不会收到delete from删除操作的影响
2.truncate既可以看清空数据表页会重置主键值
外键
# 前戏
1.定义一张员工表
id name age dep_name dep_desc
1 jason 18 外交部 搞外交
2 kevin 28 教学部 教书育人
3 tony 38 教学部 教书育人
4 oscar 48 安保部 保家卫国
5 jackson 58 财务部 发工资
"""
上述表不合理之处
1.表内部数据混乱(可忽略)
2.反复的录入重复数据(可忽略)
3.修改数据太过繁琐 浪费磁盘空间(可忽略)
4.极大地影响了操作数据的效率
"""
2.将上述表拆分成两张表
id name age
1 jason 18
2 kevin 28
3 tony 38
4 oscar 48
5 jackson 58
id dep_name dep_desc
1 外交部 搞外交
2 教学部 教书育人
3 安保部 保家卫国
4 财务部 发工资
"""拆分完之后解决了上述四个缺陷"""
#外键
用来记录表与表之间的关系
如何查找表关系
多对一
多对多
一对一
多对一
查找关系换位思考,
书籍表与出版社表
站在书籍表
问:一本书能够对应多个出版社
答:不可以
2.再站在出版社表
问:一个出版社能否对应多本书
答:可以
结论:一个可以一个不可以 表关系为"多对一"
那么外键字段建在"多"的一方
# 针对具有表关系的SQL建议先写普通字段 最后再考虑外键字段
create table book(
id int primary key auto_increment,
title varchar(32),
price int,
pub_id int,
foreign key(pub_id) references publish(id)
);
create table publish(
id int primary key auto_increment,
name varchar(32)
);
外键约束
1、在创建表的时候需要先创建被关联表(没有外键的表)
2、在写入数据的时候也需要先写入被关联表(没有外键的表)
3、被关联表里面的数据无法直接删除和修改关联字段的操作
级联更新,级联删除
create table emp(
id int primary key auto_increment,
name varchar(16),
age int,
dep_id int,
foreign key (dep_id) references dep(id)
on update cascade
on delete cascade);
create table dep(
id int primary key auto_increment,
name varchar(16)
);
多对多
以书籍表与作者表为例
1.先站在书籍表的基础之上
问:一本书能否对应多个作者
答:可以
2.在站在作者表的基础之上
问:一个作者能否对应多本书
答:可以
结论:两个都可以那么表关系就是"多对多"
外键字段建在第三张关系表中
#在写普通字段之后,再考虑外键字段
create table book1(
id int primary key auto_increment,
name varchar(16)
);
create table author(
id int primary key auto_increment,
name varchar(16));
create table book2author(
id int primary key auto_increment,
author_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade,
book_id int,
foreign key(book_id) references book1(id)
on update cascade
on delete cascade);
一对一
关系
# 场景
客户表与学生表
QQ用户表
以用户表与用户详情表为例
1.先站在用户表的基础之上
问:一个用户能否对应多个用户详情
答:不可以
2.在站在用户详情表基础之上
问:一个用户详情能否对应多个用户
答:不可以
结论:换位思考之后两边都不可以 那么表关系有两种
1.没有关系(用膝盖都能判断出来)
2.一对一关系
'外键字段建在哪里?'
#理论上建在任何一方都可以但是推荐建在查询频率较高的表中
create table user(
id int primary key auto_increment,
name int,
detail_id int unique,
foreign key(detail_id) references user_detail(id)
on update cascade
on delete cascade
);
操作表的SQL语句补充
修改表名称
alter table t1 rename t33;
#关键字修改
rename table t1 to 123;
rename table t1 to t44,t3 to 55;
添加表字段
alter table t10 add pwd int;
alter table t10 add hobby varchar(32) after age;
alter table t10 add nid int first;
修改字段和字段类型
alter table t10 modify age tinyint;
alter table t10 change ped password int;
复制表(了解)
# 查询语句执行的结果也是一张表,可以看成虚拟表
# 复制表结构+记录 (key不会复制: 主键、外键和索引)
create table new_service select * from service;
# 只拷贝表结构(不包含键)
create table new1_service select * from service where 1=2;
# 拷贝结构包含各种key
create table t4 like emp;