概念
mysql 开源,跨平台支持性好,提供了多种语言调用的API
database:数据库,数据的集合
持久化存储
读写效率极高
保证数据的有效性
对程序的支持性非常好,容易扩展
(数据库客户端---数据库服务器---数据库文件)数据库管理系统
客户端和服务器通过网络通信的规范:SQL
学习如何编写SQL语句
关系型数据库(通过SQL语句通信),非关系型数据库
1.数据库的作用:
高效的管理数据的软件
2.数据库管理系统
数据库客户端---数据库服务器---数据库文件
3.关系性数据库,非关系型数据库
关系型数据库是以表的形式存储的
表格概念 数据库概念(关系型数据库)
列 字段(属性)
行 记录record(元组)
工作表sheet 表table
表格文件 数据库database
SQL表现形式:带有一定格式的字符串
SQL:是结构化查询语言(可以操作关系型的数据库)
不区分大小写
SQL语句主要分为:
*DQL:数据查询语言, 对数据进行查询 select
*DML:数据操作语言,对数据增删改查 insert update delete
TPL: 事务处理语言,对事务进行处理
DCL:数据控制语言,进行授权和权限回收
DDL:数据定义语言,进行数据库,表的管理
数据类型:存储在数据库中的所有数据值均为正确的类型
数据约束:在数据类型限定的基础上,添加的额外的要求
数据类型影响约束数据的类型和数值大小
约束
主键primary key :唯一确定一行记录(id)一般自动增长
非空not null:此字段不能为空 ,null在编程中并补等于0,而是没有
唯一性un:此字段值不能重复
默认值default:如果用户不填数据,数据默认为默认是
外键freign key: 对关系字段进行约束,A表中的a字段的数据来自于,B表中的b字段的数据
a字段称之为外键,
外键约束要求:a字段所有值必须在b字段中存在
服务器端的安装
sudo apt-get install mysql-server
启动服务
sudo service mysql start
查看进程中是否有MySQL服务
ps ajx|grep mysql
停止服务
sudo service mysql stop
重启服务
sudo service mysql restart
配置
·配置文件目录为/etc/mysql/mysqql.cnf
·进入conf.d目录,打开mysql.cnf,发现没有配置
· 进入mysql.conf.d目录,打开mysql.cnf,可以看到配置项
· 主要配置项如下
bind-address表示服务器绑定的ip,默认127.0.0.1
port表示端口,默认306
datadir表示数据库目录,默认/var/log/mysql/mysql.log
log_error表示错误日志,默认/var/log/mysql/error.log
数据的完整性
在表中为了更加准确地存储数据,保证数据的正确有效,可以在创建表的时候,为表添加一些强制的验证,约束。
数据类型
使用数据类型的原则是:够用就行,尽量使用取值范围小的,而不用大的,这样可以更多的节省存储空间
常用的数据类型如下:
整数:int bit
小数: decimal
字符串: varchar char
日期时间:date time datetime
枚举类型:(enum)
decimal表示浮点数,decimal(5, 2)表示存5位数,小数占两位
char表示固定长度的字符串,char(3)m,如果填补‘ab’时会补一个空格为‘ab ’
varchar 表示可变长度的字符串,varchar(3),表示最多三个字节,填充‘ab’时会存‘ab’
对于图片,音频,视频等文件,不存储在数据库中,而是上传到某个服务器上
字符串text表示存储大文本,当字符串大于4000,推荐使用
约束
主键primary key :唯一确定一行记录(id)一般自动增长
非空not null:此字段不能为空 ,null在编程中并补等于0,而是没有
唯一性unique:此字段值不能重复
默认值default:如果用户不填数据,数据默认为默认是
外键freign key: 对关系字段进行约束,A表中的a字段的数据来自于,B表中的b字段的数据
a字段称之为外键,
外键约束要求:a字段所有值必须在b字段中存在
数值类型(常用)
类型
|
字节大小
|
有符号范围(Signed)
|
无符号范围(Unsigned)
|
TINYINT
|
1
|
-128 ~ 127
|
0 ~ 255
|
SMALLINT
|
2
|
-32768 ~ 32767
|
0 ~ 65535
|
MEDIUMINT
|
3
|
-8388608 ~ 8388607
|
0 ~ 16777215
|
INT/INTEGER
|
4
|
-2147483648 ~2147483647
|
0 ~ 4294967295
|
BIGINT
|
8
|
-9223372036854775808 ~ 9223372036854775807
|
0 ~ 18446744073709551615
|
字符串
类型
|
字节大小
|
示例
|
CHAR
|
0-255
|
类型:char(3) 输入 'ab', 实际存储为'ab ', 输入'abcd' 实际存储为 'abc'
|
VARCHAR
|
0-255
|
类型:varchar(3) 输 'ab',实际存储为'ab', 输入'abcd',实际存储为'abc'
|
TEXT
|
0-65535
|
大文本
|
日期时间类型
类型
|
字节大小
|
示例
|
DATE
|
4
|
'2020-01-01'
|
TIME
|
3
|
'12:29:59'
|
DATETIME
|
8
|
'2020-01-01 12:29:59'
|
YEAR
|
1
|
'2017'
|
TIMESTAMP
|
4
|
'1970-01-01 00:00:01' UTC ~ '2038-01-01 00:00:01' UTC
|
命令行来连接
打开终端运行命令
mysql -u -root -p
回车后输入MySQL服务器密码
退出登录
quit / exit/ ctrl+d
连接数据库之后命令使用 ;结尾!!!
查看版本:select version();
显示当前时间:select now();
修改输入提示符
\D 完整日期
\U 使用用户
数据库操作
查看所有数据库:show databases;
使用数据库:use 数据库名; 使用此命令后才能修改此数据库
查看当前使用的数据库:select database();
创建数据库:create database 数据库名 charset=utf8;
create database python charset=utf8; 创建名字为python的数据库,charset必须设置为utf8
删除数据库:drop database 数据库名;
drop database python; 删除名字为python的数据库
数据表操作
查看当前数据库中所有表:show tables;
查看表结构:desc 表名;
创建表:
格式
auto_increment表示自动增长
create table table_name(
column1 datatype contrai,
column2 datatype,
column3 datatype,
...
columnN datatype,
primary key(one or more columns)
);
例子:创建班级表 创建了表名为classes,字段id(整数,无符号范围,自动增加,主键,不能为空),字段name(10一下的非定长字符串)
create table classes(
id int unsigned auto_increment primary key not null,
name varchar(10)
);
例子:创建学生表:
创建表名为students(
字段名id(整数,无符号范围,主键,自动增长,不能为空),
字段名name(长度不大于20的字符串,默认为空),
字段名age(数值类型0~255,默认为0),
字段名height(浮点数,存5位数小数占两位),
字段名gender(枚举,四个中任选),
字段名cls_id (整型,无符号范围,默认为0)
);
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;
向students表中添加字段birthday 类型为’年月日,时分秒‘
修改表-修改字段:重命名版
alter table 表名 change 原名 新名 类型和约束;
alter table students change birthday birth datetime not null;
修改students表中brthday字段为birth并且类型为’年月日,时分秒‘,约束为不能为空
alter table students change birthday birthday datetime not null;
修改students表中brthday字段的类型为’年月日,时分秒‘,约束为不能为空
修改表-修改字段:不重命名版
alter table 表名 modify 字段名 类型及约束;
alter table students modify birthday data not null;
修改students表中brthday字段的类型为’年月日,时分秒‘,约束为不能为空
修改表-删除字段
alter table 表名 drop 字段名;
alter table students drop birthday;
删除students表中birthday字段
删除表
drop table 表名;
drop table students;
删除表名为students的表
查看表的创建语句
show create table 表名;
show create table classes;
查看表名为classes的创建语句
数据操作
(增删改查)curd:创建(create),更新(update),读取(retrieve),删除(delete)。
查询
查询所有列(字段)的内容:selsct * from 表名;
select * from classes;
查询表名为classes的所有列(即所有内容)
如果查询所有字段名(即表结构)可以用 desc 表名;
查询指定列(字段)的内容:(可以使用as为列或表指定别名)
select 列1,列2,... from 表名;
select id,name from clasddes;
查询表名为classes中字段名为id,name的内容
增加
格式:insert [into] tb_name [(col_name,...)] {values|value} ({expr|default},...),(...),...
说明:主键列时自动增长,但是在全列插入时需要占位,通常使用0/default/null/来占位,插入成功后以实际数据为准
全列插入:值的顺序与表中字段的顺序对应
insert into 表名 values(...);
inssert into students values(0,'郭靖',1,'蒙古','2016-1-2');
在表名为students中按字段顺序插入(主键占位,。。。)
部分列插入:值的顺序与给出 的列顺序对应
insert into 表名(列1,...) values(值1,...);
insert into students(name,hometown,birthday) values('黄蓉','桃花岛','2016-3-1');
在students表中插入对应字段的值
上面的语句一次可以向表中插入一行数据,换可以一次性插入多行数据,这样可以减少与数 据库的通信
全列多行插入:值的顺序与给出的列的顺序对应
insert into 表名 values(...)(...)...;
insert into classes values(0,'python1'),(0,'python2');
在表名为classes中按字段顺序插入(主键占位,。。。)
insert into 表名(列1,...) values(值1,...)(值2,...)...;
insert into students(name) values('jack'),('jia'),('bai');
在表名为students的name字段下插入值
修改
格式:update tb_name set col1={expr1|default} [,col={expr2|default}]...[where条件判断]
updata 表名 set 列1=值1,列2=值2... where 条件
update students set gender=0, hometown='北京' where id=5;
更新表名为students里 id=5的字段gender和hometown的值
删除
delete from tb_name [where条件判断]
delete from 表名 where 条件
delete from students where id=5;
删除表名为students中字段id=5的记录(一行)
逻辑删除,本质就是修改操作
update students set isdelete=1 where id=1;
删除表名为students中字段id=1的记录(一行)
备份
运行mysqldump命令
mysqldump -u root -p 数据库名 > python.sql;
输入mysql密码
恢复
连接mysql,创建新的数据库
退出连接,执行命令
mysql -uroot -p 新数据库名 < python.sql
输入MySQL密码
查询
select * from table_name; 查询所有字段
select 字段1,字段2 from table_name; 查询指定字段
select id as self_name from table_name;使用as给字段起别名
select distinct 字段1 from table_name;消除重复行,如果写两个字段据观察都没有消除。
查询条件
使用where子句对数据进行筛选,条件为true的行会出现在结果中
select * from table_name where 条件(id=4);
where 后面支持多种运算符号
比较运算符
=,>,<,<=,>=,!= / <>
逻辑运算符
and,or,not(and比or的优先级大)
模糊查询
like %表示任意多个任意字符,_表示一个任意字符(可以放在确定的字符的前后)
范围查询
in (in(1,2,3)),between...and...(表示在一个连续的范围如:between 1 and 3)
空判断
null(空值)is null / is not null
''零字符串长度
排序
select * from table_name order by 字段1 asc(默认)|desc(降序);
后面可以跟任意多个字段,字段1数据相同时,按照字段2排序
聚合函数
count(*)表示计算总行数(真实的行数),如果()中填写字段名字默认不会计算数据为null的数据,则计算的行数是错误的(除非该字段没有null)
max(字段名) 求该字段数据的最大值
min (字段名) 求该字段数据的最小值
sum(字段名)求该字段数据的总和
avg(字段名)求该字段数据的平均值,默认四位小数
round(avg(字段名),2)显示两位小数
都可以填写多个字段,一个聚合函数加一个字段
分组
group by 将查询结果按照一个或多个字段进行分组,字段值相同的为一组,也可以用于多个字段分组(暂时没想到有什么意义)
group by单独使用没有什么意义
group by + group_concat(字段名) 根据分组结果显示该组要显示的字段的集合
![](https://images2018.cnblogs.com/blog/1209673/201808/1209673-20180802215022068-507178308.png)
group by + 聚合函数 既然可以统计出每个分组的某个字段的数据的集合,那也可以对该集合做一些操作(这里显示不使用group_concat())(round保留两位小数)
![](https://images2018.cnblogs.com/blog/1209673/201808/1209673-20180802215335061-1229819435.png)
group by + having
用来分组查询后指定条件输出结果,和where一样,但只用于group by
![](https://images2018.cnblogs.com/blog/1209673/201808/1209673-20180802220354784-1801507248.png)
group by + with rollup
在最后新增一行,显示当前列中所记录的总和
![](https://images2018.cnblogs.com/blog/1209673/201808/1209673-20180802220622244-1237066946.png)
分页
select * from table_name start,count;
数据中的第一条数据是从0开始的,select * from students where gender=1 limit 0,3;查询前三行男生信息
select * from studnets where is_delete=0 limit (n-1)*m,m;(n为第几页,m为查询行数)
连接查询
select * from table_1 inner/left/right join table_2 on table_1.字段=table_2.字段;
内连接(显示两个表中固定字段相同的数据),左连接(显示两个表中固定字段相同的数据和左边表中的数据),右连接(显示两个表中固定字段相同的数据和右边表中的数据)
![](https://images2018.cnblogs.com/blog/1209673/201808/1209673-20180802222005032-811458740.png)
![](https://images2018.cnblogs.com/blog/1209673/201808/1209673-20180802222059191-669665254.png)
![](https://images2018.cnblogs.com/blog/1209673/201808/1209673-20180802222125289-1891246538.png)
自关联
自关联:同一个表中某一字段的数据关联了这个表中的另一个字段的数据
如下表中包含省(直辖市)和市(区)的数据表中aid和pid中的数据
省(直辖市)的pid为null,市(区)的pid和所关联的省(直辖市)的aid相同,则可以使用自关联进行查询
![](https://images2018.cnblogs.com/blog/1209673/201808/1209673-20180804204250151-816701120.png)
同一个表进行自关联(即内连接)
![](https://images2018.cnblogs.com/blog/1209673/201808/1209673-20180804204838753-1650932618.png)
可以通过自连接生成的虚表进行查询,如查询北京市下属单位
![](https://images2018.cnblogs.com/blog/1209673/201808/1209673-20180804205327295-262786436.png)
当让不通过自连接也可以查询,可以直接在一个表中进行查找‘北京市’的aid,然后根据aid匹配pid进行查询
![](https://images2018.cnblogs.com/blog/1209673/201808/1209673-20180804210052164-1130328262.png)
如果有直辖市的情况下就会报错,因为无法确定哪一个是真正的上一级城市,如下!
![](https://images2018.cnblogs.com/blog/1209673/201808/1209673-20180804205940624-1542224415.png)
子查询
子查询就是被查询的结果作为主查询的条件或者基础(注意表的查询是必须要使用select语句,刚才说的虚表不是查询出来的而是假设存在的)
主查询是将子查询的结果作为条件来对数据进行精确操作(select name from classes where id in (select cls_id from students);)(select * from students where age > (select avg(age) from students);)
标量子查询:子查询的结果是一个数据(如select atitle from areas where id=8;)(select count(*) from areas;)
列子查询:子查询的结果是一列数据(select atitle from areas;)(select id from areas where i>8;)
行子查询:子查询的结果是一行数据(select atitle,id,age from areas where id=4;)(select atitle avg(age),max(id) from areas; )
完整查询执行顺序为:
- from 表名
- where ....
- group by ...
- select distinct *
- having ...
- order by ...
- limit start,count
source语句
通过source语句进行自动执行文件内的sql命令(可以输入绝对路径也可以在命令行切换到对应文件夹再连接mysql)
![](https://images2018.cnblogs.com/blog/1209673/201808/1209673-20180804213005181-758986005.png)
对应文件夹下的内容(如果想要写同时写入多个文件,可以创建一个新的文件(新文件里写source ....; 写了那几行就会运行那几行~~))
![](https://images2018.cnblogs.com/blog/1209673/201808/1209673-20180804213141236-935509566.png)
连表更新
下面是完成后的商品表
![](https://images2018.cnblogs.com/blog/1209673/201808/1209673-20180805230931627-1650544138.png)
可以通过创建命令创建goods_cates表,并添加数据
create table goods_brands (
id int unsigned primary key auto_increment,
name varchar(40) not null) select brand_name as name from goods group by brand_name;(brand_name必须要起一个和创建的表待插入字段的名字一样)
也可以创建表之后再插入数据
create table goods_brands (
id int unsigned primary key auto_increment,
name varchar(40) not null);
insert into goods_cates (name) select cate_name from goods group by cate_name;(根据分组查询到的结果自动添加数据)
将创建的goods_cates表中的id更新到goods表
update goods g
join goods_cates gc
on g.cate_name=gc.name
set g.cate_name=gc.id;
![](https://images2018.cnblogs.com/blog/1209673/201808/1209673-20180805232413453-607287684.png)
外键
foreign key约束指定某一个列或一组列作为外部键,其中包含外部键的表称为子表,包含外键所引用的键的表称为父表。
给brand_id 添加外键约束成功
alter table goods add foreign key (brand_id) references goods_brands(id);
在创建数据表的时候设置外键约束
- 注意: goods 中的 cate_id 的类型一定要和 goods_cates 表中的 id 类型一致
create table goods(
id int primary key auto_increment not null,
name varchar(40) default '',
price decimal(5,2),
cate_id int unsigned,
brand_id int unsigned,
is_show bit default 1,
is_saleoff bit default 0,
foreign key(cate_id) references goods_cates(id),
foreign key(brand_id) references goods_brands(id)
);
删除外键约束
-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
show create table goods;
-- 获取名称之后就可以根据名称来删除外键约束
alter table goods drop foreign key 外键名称;
- 使用到外键约束会极大的降低表更新的效率, 所以在追求读写效率优先的场景下一般很少使用外键。
- 外键约束作用 子表中的外键字段在插入和更新 新值的时候 新值必须 在主表中相应字段出现过。