Ubuntu MySQL

最新推荐文章于 2024-09-19 19:11:44 发布

武亦姝

最新推荐文章于 2024-09-19 19:11:44 发布

阅读量299

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/JimTan_/article/details/105712056

版权

安装 MySQL

MySQL 需要安装服务器端和客户端

服务端

安装

sudo apt-get install mysql-server

启动服务

sudo service mysql start

查看服务进程

ps ajx|grep mysql

停止服务

sudo service mysql stop

重启服务

sudo service mysql restart

配置

配置文件目录为/etc/mysql/mysql.cnf
cd /etc/mysql/mysql.config.d
vim mysql.cnf

bind-address 表示服务器绑定的ip，默认为127.0.0.1

port 表示端口，默认为3306

datadir 表示数据库目录，默认为/var/lib/mysql

general_log_file 表示普通日志，默认为/var/log/mysql/mysql.log

log_error 表示错误日志，默认为/var/log/mysql/error.log

客户端

开发人员与dba使用,通过soket与服务器通信, 常用Navicat, 命令行

图形化界面Navicat安装
下载Navicat压缩包

拷贝到Ubuntu桌面,解压:
tar -zxvf navicat112_mysql_cs_x64.tar.gz

进入解压的目录,运行命令:
./start navicat

图形界面点击两次取消,点击试用

1.中文乱码:
打开start_navicat文件
将export LANG="en_US.UTF-8"改为export LANG=“zh_CN.UTF-8”

2.试用期
删除用户目录下的.navicat64目录
cd ~
rm -r .navicat64~

连接服务器:
打开Navicat,点击连接, 选择mysql, 填写名称、主机ip、端口、用户名、密码

命令行客户端安装

sudo apt-get install mysql-client

连接服务器:

mysql -u root -p mysql

退出:

Ctrl+d / exit / quit

数据类型

整数: (tinyint, smallint, mediumint, int, bigint), bit

小数: decimal
decimal(5,2),共5位,小数占2位

字符串: varchar, char, text
varchar:可变长度; char:不足补空格,不足补空格

日期时间: year, date, time, datetime, timestamp

枚举: enum

约束

primary key: 主键
foreign key: 外键
not null: 非空
unique: 唯一
default: 默认

命令

SQL

DQL: 数据查询语言
DML: 数据操作语言
DDL: 数据定义语言
DCL: 数据控制语言
TPL: 事物处理语言
CCL: 指针控制语言
SQL不区分大小写,可操作所有关系型数据库

连接数据库

mysql -uroot -pmysql

查看版本

selec version()

显示当前时间

select now()

修改输入提示符

prompt python>

数据库

查看所有数据库

show databases;

查看当前使用的数据库

select database;

创建数据库

create datebase 数据库名 charset=utf-8;

使用数据库

use 数据库名;

删除数据库

drop datebase 数据库名;

数据表

查看当前数据库中的所有表

show tables;

查看表结构

desc 表名;

创建表

create table table_name(	
    column1 datatype contrai,
    column2 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
    is_delete bit 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 student modify birthday datetime not null;

修改表-删除字段

alter table 表名 drop 列名;
alter student drop birthday;

删除表

drop table 表名;
drop table student;

产看表的创建语句

show create table 表名;
show create table classes;

增删改查(curd)

curd的解释: 代表创建（Create）、更新（Update）、读取（Retrieve）和删除（Delete）

查询
查询所有列

select * from 表名;
select * from classes;

查询指定列

select 列1, 列2, 列3, ... from 表名;
select id name from clasese;

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

全列插入：值的顺序与表中字段的顺序对应

insert into 表名 values(...);
insert into student values(0, "小明", 1, "新疆", "2020-4-24");

部分列插入：值的顺序与给出的列顺序对应

insert into 表名(列1,列2,...) value(值1,值2,...);
insert into student(name,hometown,birthday) values("小花","上海","2012-5-20");

全列多行插入：值的顺序与给出的列顺序对应

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

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

修改

update 表名 set 列1=值1,列2=值2,... where 条件;
update student set gender=0,hometown="北京" where id=5;

删除

delete from 表名 where 条件;
delete from student where id=5;

逻辑删除，本质就是修改操作
设置isDelete的列，类型为bit，表示逻辑删除，默认值为0

update 表名 set isdelete=1 where 条件;
update student set isdelete=1 where id=1;

条件 where

select * from 表名 where 条件;
select * from students where id=1;

where后面支持多种运算符，进行条件的处理:
比较运算符

等于:= 大于:> 大于等于:>= 小于:< 小于等于:<= 不等于:!=或<>
select * from students where name != '黄蓉';

逻辑运算符

and or not
select * from students where id > 3 and gender=0;

模糊查询

like
%表示任意多个任意字符
_表示一个任意字符
select * from students where name like '黄%' or name like '_靖';

范围查询

in
select * from students where id in(1,3,8);

between ... and ...
select * from students where (id between 3 and 8) and gender=1;

空判断

注意：null与''是不同的

is null
select * from students where height is null;

is not null
select * from students where height is not null;

优先级
优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符
and比or先运算，如果同时出现并希望先算or，需要结合()使用

排序 order by

select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]
select * from students  order by age desc,height desc;

聚合函数

总数 count(*)

select count(*) from students;

最大值 max(列)

select max(id) from students where gender=2;

最小值 min(列)

select min(id) from students where is_delete=0;

求和 sum(列)

select sum(age)/count(*) from students where gender=1;

平均值 avg(列)

select avg(id) from students where is_delete=0 and gender=2;

分组

group by
将查询结果按照1个或多个字段进行分组，字段值相同的为一组
可用于单个字段分组，也可用于多个字段分组

select gender from students group by gender;

group by + group_concat
group_concat(字段名)可以作为一个输出字段来使用
分组之后，根据分组结果，使用group_concat()放置每一组字段值的集合

select gender,group_concat(name) from students group by gender;

group by + 集合函数

select gender,avg(age) from students group by gender;
select gender,count(*) from students group by gender;

group by + having
having 条件表达式：用作分组查询后指定一些条件来输出查询结果
having作用和where一样，但having只能用于group by

select gender,count(*) from students group by gender having count(*)>2;

group by + with rollup
with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和

select gender,count(*) from students group by gender with rollup;
select gender,group_concat(age) from students group by gender with rollup;

分页

获取部分数据

从start开始，获取count条数据
select * from 表名 limit start,count
select * from students where gender=1 limit 0,3;

分页

已知：每页显示m条数据，当前显示第n页
求总页数：
– 查询总条数p1
– 使用p1除以m得到p2
– 如果整除则p2为总数页
– 如果不整除则p2+1为总页数
求第n页的数据

select * from students where is_delete=0 limit (n-1)*m,m

链接查询

语法
select * from 表1 inner/left/right join 表2 on 表1.列 = 表2.列

内连接

select * from students inner join classes on students.cls_id = classes.id;

左连接

select * from students as s left join classes as c on s.cls_id = c.id;

右链接

select * from students as s right join classes as c on s.cls_id = c.id;

自关联

表中的某一列，关联了这个表中的另外一列
但是它们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id
在这个表中，结构不变，可以添加区县、乡镇街道、村社区等信息
因为省没有所属的省份，所以可以填写为null
城市所属的省份pid，填写省所对应的编号id

创建地区表

create table areas(
    aid int primary key,
    atitle varchar(20),
    pid int
);

从sql文件导入数据

source areas.sql;

查询

查询一个共有多少个省
select count(*) from areas where pid is null;

查询省的名称为“山西省”的所有城市
select city.* from areas as city
inner join areas as province on city.pid=province.aid
where province.atitle='山西省';

子查询

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句

子查询是嵌入到主查询中
子查询是辅助主查询的,要么充当条件,要么充当数据源
子查询是可以独立存在的语句,是一条完整的 select 语句

标量子查询

select * from students where age > (select avg(age) from students);

列级子查询

select name from classes where id in (select cls_id from students);

行级子查询

select * from students where (height,age) = (select max(height),max(age) from students);

子查询中特定关键字使用

in
主查询 where 条件 in (列子查询)

查询的完整格式

SELECT select_expr [,select_expr,...] [      
      FROM tb_name
      [WHERE 条件判断]
      [GROUP BY {col_name | postion} [ASC | DESC], ...] 
      [HAVING WHERE 条件判断]
      [ORDER BY {col_name|expr|postion} [ASC | DESC], ...]
      [ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
]

完整select语句

select distinct * 
from 表名
where ....
group by ... having ...
order by ...
limit start,count

执行顺序

from 表名
where ....
group by ...
select distinct *
having ...
order by ...
limit start,count

备份数据库

mysqldump -u用户名 -p密码 数据库名>新数据库.sql
mysqldump -uroot -pmysql dataset>python.sql;

恢复数据库

连接mysql,创建新数据库
退出mysql, 执行备份命令

mysql -u用户 -p密码 新数据库名<python.sql;

数据库设计

三范式

第一范式(1NF): 列的原子性,不可再分
第二范式(2NF): 满足1NF. 1.表必须有主键; 2.非主键列必须完全依赖于主键,不能只依赖一部分
第三范式(3NF): 满足2NF. 非主键列必须直接依赖于主键,不能传递依赖

E-R模型

E: 实体, 相当于类,一个实体一个表
R: 关系, 实体间对应规则

一对一: 表A与表B都有一个字段存储对方主键
一对多: 表B中创建一个字段存储表A的主键
多对多: 创建关系表C,存储表A和表B的主键值

Python 操作 MySQL (pymysql)

流程:
1.创建connection对象
2.获取cursor
3.执行查询,执行命令,获取数据,处理数据
4.关闭cursor
5.关闭connection

from pymysql import *

1.创建Connection对象,建立数据库链接
conn = connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')

参数:
host: mysql主机
port: mysql端口
database: 数据库名
user: 链接的用户名
password: 链接的密码
charset: 通信采用的编码方式,utf8

2.获取Cursor对象
cs = conn.cursor()

3.执行sql命令,返回受影响行数
count = cs1.execute('select id,name from goods where id>=4')
print("查询到%条数据" % count)

获取查询结果
result = cs.fetchall()

4.关闭Cursor
cs.close()

5.关闭connection
conn.close()

Connection 对象的方法:

conn.close() # 关闭链接
conn.commit() # 提交任务
conn.cursor() # 返回Cursor对象,用于指定SQL语句并获得结果

Cursor 对象的方法:

cs.close() # 关闭Cursor
cs.execute(operation,[parameters]) # 执行sql语句,返回受影响的行数,主要用于执行insert,delete,update,也可执行create,alter,drop等
cs.fetchone() # 执行查询语句时,获取结果集中的第一行数据,返回元组
cs.fetchall() # 执行查询语句时,获取结果集中所有数据,一行一个元组,包含在一个大元组中返回

Cursor 对象的属性:

cs.rollcount: # 只读属性,表示最后一次execute()执行后受影响的行数
cs.connection: # 获得当前连接对象

参数化
sql语句的参数化，可以有效防止sql注入
注意：此处不同于python的字符串格式化，全部使用%s占位

 # 创建Connection连接
conn = connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
# 获得Cursor对象
cs1 = conn.cursor()

# 非安全的方式
# 输入find_name: " or 1=1 or " , (双引号也要输入)
# sql = 'select * from goods where name="%s"' % find_name
# 执行select语句，并返回受影响的行数：查询所有数据
# count = cs1.execute(sql)

# 安全的方式
# 构造参数列表
params = [find_name]
# 执行select语句，并返回受影响的行数：查询所有数据
count = cs1.execute('select * from goods where name=%s', params)
# 注意：
# 如果要是有多个参数，需要进行参数化
# 那么params = [数值1, 数值2....]，此时sql语句中有多个%s即可 

获取查询的结果
# result = cs1.fetchone()
result = cs1.fetchall()

# 关闭Cursor对象
cs1.close()
# 关闭Connection对象
conn.close()

视图

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

定义视图

create view 视图名 as select语句;

查看视图

show tables; # 查看表,会将所有视图列出

使用视图

select * from 视图名; # 视图的用途就是查询

删除视图

drop view 视图名;

视图的作用:
1.提高了重用性,就像函数
2.对数据库重构,却不影响程序运行
3.提高了安全性,可以对不同用户
4.简化查询语句,让数据更佳清晰

事务

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

事务四大特性(ACID)
1.原子性: 一个事务是不可分割的最小工作单元,要么全成功,要么全失败
2.一致性: 一个事务没有提交前,事务中所作的修改不会保存到数据库
3.隔离性: 一个事务没有提交前,对其他事务不可见
4.持久性: 一旦事务提交,事务中的修改会永久保存到数据库

事务命令
表的引擎必须是innodb才可以使用事务
查看表的创建语句,可以看到engine=innodb

开启事务

开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中

begin;
或
start transaction;

提交事务

将缓存中的数据变更维护到物理表中
commit;

回滚事务

放弃缓存中变更的数据
rollback;

注意:
1.修改数据的命令会自动的触发事务，包括insert、update、delete
2.在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据

提交

# 1.连接数据库

# 2.手动开启事务,添加数据
begin;
insert into goods_cates(name) values('小霸王游戏机');

# 3.提交
commit;

回滚

begin;
insert into goods_cates(name) values('小霸王游戏机');

rollback;

索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

查看索引

show index from 表名;

创建索引

# 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
# 字段类型如果不是字符串，可以不填写长度部分
create index 索引名 on 表名(字段名(长度));

删除索引

drop index 索引名 on 表名;

时间监控

# 开启时间监控
set profiling=1;

# 查询
select * from 表名 where 列名=值;

# 查看执行时间
show profiles;

# 创建索引
create index 索引名 on 表名(列名(长度));

# 查询
select * from 表名 where 列名=值;

# 查看执行时间
show profiles;

注意:
建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。
建立索引会占用磁盘空间

账户管理

根据权限不同,账户分为:
服务实例级账号
数据库级别账号
数据表级别账号
字段级别账号
存储程序级别账号

注意:
进行账户操作时，需要使用root账户登录

授予权限
需要使用实例级账户登录后操作,root

查看所有用户

# 查看user表结构
desc user; # 所有用户和权限都存在user表中

# 查看所有用户
select host,user,authentication_string from user;

创建账户,授权

# 常用权限主要包括：create、alter、drop、insert、update、delete、select
# 如果分配所有权限，可以使用all privileges
grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';

grant select on jing_dong.* to 'laowang'@'localhost' identified by '123456';

grant all privileges on jing_dong.* to "laoli"@"%" identified by "12345678"
# 可以操作python数据库的所有表，方式为:jing_dong.*
# 访问主机通常使用 百分号% 表示此账户可以使用任何ip的主机登录访问此数据库
# 访问主机可以设置成 localhost或具体的ip，表示只允许本机或特定主机访问

查看用户的权限

show grants for laowang@localhost;

修改权限

grant 权限名 on 数据库 to 账户@主机 with grant option;
grant select,insert on jindong.* to laowang@localhost with grant option;
flush privileges;

修改密码

update user set authentication_string=password('新密码') where user="用户名";
update user set authentication_string=password('123') where user="laowang";
flush privileges;

远程登录(危险慎用)

# 修改 /etc/mysql/mysql.conf.d/mysqld.cnf 文件
vim /etc/mysql/mysql.conf.d/mysqld.cnf
bind-addr-127.0.0.1

# 重启msyql
service mysql restart;

# 在另一台Ubuntu中进行连接
mysql -h172.16.7.137 -ulaowang -p123;

删除用户

# 1.drop user '用户名'@'主机';
drop user 'laowang'@'%';

# 2.delete from user where user='用户名';
delete from user where user="laowang";
flush privileges;

MySQL 主从同步

主从同步: 数据库从一个服务器复制到另一个/多个服务器
好处:

数据安全
提高数据库性能
– 主服务器负责写入/跟新,从服务器负责向外提供读取
– 主服务器生成数据,从服务器分析数据

方法: 通过二进制日志机制实现主从复制, 主服务器所有操作都会写入二进制日志, 从服务器执行日志记录的所有命令,完成复制

主从同步基本步骤:
1.主服务器必须开启二进制日志机制,并配置一个独立ID
2.每个从服务器上配置一个唯一ID,创建一个专门用来复制主服务器的账号
3.在开始复制进程前,在主服务器上记录二进制文件的位置
4.复制前,若数据库中有数据,先备份
5.配置从服务器要连接的主服务器的IP地址和登录授权,二进制日志文件名和位置

详见课件