MySQL数据库基础
基于mysql8.0的语法来进行,mysql是关系型数据库,数据存放在磁盘中。
一 用户操作
用户基本操作
-
登录:
mysql> mysql -u "username" -p "password" # 这里可以-p后直接Enter,这样密码隐藏输入,操作更安全
-
退出:exit;
-
CentOS 7.6 64位 安装数据库
1. MySQL8.0下载,注意是MySQL8.0版本的,其他版本的语法有一些不一样
yum localinstall https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm # 下载mysql8.0 yum install mysql-community-server # 安装mysql数据库 service mysqld start # 开启mysql服务 service mysqld status # 查看mysql状态
2. MySQL软连接
ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql # mysql进行软连接 rpm -aq | grep -i mysql # 查看mysql的版本
3. 修改密码和创建新用户
grep 'temporary password' /var/log/mysqld.log # 查看mysql初始密码 mysql -uroot -p 密码 # 登录mysql # 修改密码: # 方法1: ./bin/mysqladmin -u root -p +旧密码 password 新密码 # 直接修改密码会报密码等级不够,不允许修改密码(见问题解决) # 方法2: 1. mysql -uroot -p 密码 登进mysql 2. alter user "root"@"localhost" identified with mysql_native_password by "新密码"; 3. 修改密码会报密码等级不够,不允许修改密码(见问题解决)
创建数据库和新用户并授权
create database 数据库名 charset 'utf8' # 创建数据库 create user "用户名"@"可连接权限" identified by "密码"; #创建新用户 grant all on 数据库名.* to "alyblog"@"%"; # 授权 flush privileges; # 刷新权限
安装到这里表示数据库已经是安装和配置成功了。
4. 遇到的一些问题解决
-
报错1:ERROR 1819 (HY000): Your password does not satisfy the current policy requirements 这是因为密码的验证强度等级造成的
#解决办法: 1. set global validate_password.policy=LOW; # 设置密码验证等级为低 2. set global validate_password.length=6; # 将密码的最低长度设置为6,这样超过6的密码长度就能用了 3. 重复方法2就行了
-
报错2:grep ‘temporary password’ /var/log/mysqld.log 拿不到临时密码
# 解决办法 1. rm -rf /var/lib/mysql # 删除原来安装过的mysql残留的数据 2. systemctl restart mysqld # 重启mysql服务 3. grep 'temporary password' /var/log/mysqld.log # 获取临时密码
-
报错3:数据库配置好后出现链接超时问题:django.db.utils.OperationalError: (2003, “Can’t connect to MySQL server on ‘IP地址’ (timed out)”)
# 解决办法: 1. 阿里云服务器上实例安全组那里没有设置MYSQL端口3306 IP:0.0.0.0/0 2. 修改/etc/my.cnf里面的bind_address=0.0.0.0,同时注释掉skip-grant-tables # 修改mysql可允许访问的IP地址 3. systemctl restart mysqld # 重启mysql服务
-
报错4:出现缺少mysql密码加密问题:RuntimeError: cryptography is required for sha256_password or caching_sha2_password
解决办法: pip3 installcryptography # 导入模块,再重试
-
报错5:出现1045报错:django.db.utils.OperationalError: (1045, “Access denied for user ‘账号’@‘访问ip’ (using password: YES)”)
# 解决办法: 检查下django中mysql配置账号密码是否正确
5. MySQL的卸载
光有安装肯定是不行的,毕竟有时候安装有问题想重新推到进行安装,那么卸载就很有作用了,一开始遇到这类问题我都是采取的重装阿里云服务器的系统,简直被虐的不要不要的。
1. systemctl status mysqld # 查看mysql状态 2. systemctl stop mysqld # 关闭mysql服务 3. rpm -qa | grep mysql # 执行卸载命令 4. 卸载安装组件(rpm -ev)后面跟的是上面命令执行后的要卸载的组件,由于依赖关系,所以要按顺序卸载。需要注意的是:安装的时候是带rpm后缀的,但是卸载时不带的。 1. rpm -ev mysql-community-server + 自己的版本 2. rpm -ev mysql-community-client + 自己的版本 3. rpm -ev mysql-community-libs + 自己的版本 4. rpm -ev mysql-community-common + 自己的版本 5. whereis msyql # 执行查找路径命令 6. rm -rf + 路径 # 删除上面命令下所显示的路径下的文件 7. find / -name mysql # 查找mysql相关文件 8. rm -rf /var/log/mysqld.log # 删除日志文件
-
二、数据库操作
1. 库级操作
create database +数据库名 charset "编码方式"
show databases; # 查看有哪些数据库
use +数据库名; # 进入到该数据库
drop database +数据库名; # 删除该数据库
show tables from +数据库名; # 查看该数据库下面有哪些表
2. 表级操作
show columns from + 表名; # 查看该表内有哪些字段
show plugins; 查看数据库是否支持分区
create table +表名(
字段1名 数据类型 约束条件
字段2名 数据类型 约束条件
......
); # 在该表内创建字段,字段数据类型和约束条件
describe+表名; # 查看当前表内字段的信息
desc+表名; # 查看当前表内字段的信息
show create table +表名; # 查看当前表内字段的约束信息关键字
alter table 表名 add+(新字段名 数据类型 [约束条件])[first|after 字段名]; #添加列,其中first为在最前面添加,after为在什么字段之后添加
alter table 表名 modify +字段名+数据类型; #修改该列的数据类型
alter table 表名 modify +字段名+数据类型[约束条件] +first|after+字段名; # 修改字段排列位置,其中first为最前,after为什么字段之后
alter table 就表名 rename[to] 新表名; # 修改表名
alter table 表名 change 旧字段名 新字段名 数据类型 [约束条件]; # 修改字段名
alter table 表名 drop 字段名; # 删除字段
drop table [if exists] 表1,表2......; # 删除数据库中的表,只能删除没有外键约束的,其中if exists 添加后可以防止报错变为警告
create table 表名(
字段1 数据类型 [约束条件],
字段2 数据类型 [约束条件],
...
partition by range +字段几(
partition P1 values less than(分区范围),
partition P2 values less than(分区范围),
partition P3 values less than(maxvalues),
)
); # 对字段进行分区
表级5大约束条件:1. 非空约束
create table 表名(
字段1 数据类型 [约束条件],
字段2 数据类型 not null(非空约束,默认为null)
); # 创建表时添加约束条件
alter table 表名 modify 字段名 数据类型 [约束条件]; # 增加约束条件
alter table 表名 modify +字段名+数据类型; # 删除约束条件 只能删除auto_increment
表级5大约束条件:2. 主键约束
# 1个表中只能有一个主键约束(非空且唯一),主键约束字段一般放在第1,auto_increment应用时只能用于主键字段
create table 表名(
字段1 数据类型 primary key [not null|default],
字段2 数据类型 [约束条件]
); # 创建时添加主键约束
alter table 表名 add primary key(字段名); # 在已创建好的表中添加主键
alter table 表名 drop primary key; # 删除主键
表级5大约束条件:3. 唯一约束
create table 表名(
字段1 数据类型 unique key [not null|default],
字段2 数据类型 [约束条件]
); # 创建表时添加唯一约束条件
alter table 表名 modify 字段名 数据类型 unique key; # 在已创建好的表中添加唯一约束条件
alter table 表名 add [constraint +自定义约束名] unique key(字段名); # 在已创建好的表中添加唯一约束条件
alter table 表名 drop index uk_bname; # 删除唯一约束条件
alter table 表名 drop key uk_bname; # 删除唯一约束条件
表级5大约束条件:4. 默认约束
create table 表名(
字段1 数据类型 default 默认值,
字段2 数据类型 [约束条件]
); # 在创建时添加默认约束条件
alter table 表名 modify 字段名 数据类型 default 默认值; # 在已创建的表中添加默认约束
alter table 表名 alter column 字段名 set default 默认值; # 在已创建的表中添加默认约束
alter table 表名 modify 字段名 数据类型; # 删除表中指定列的默认约束条件
alter table 表名 alter column 字段名 drop default; # 删除表中指定列的默认约束条件
表级5大约束条件:5. 外键约束
# 设置自动编号——auto_increment(与主键约束一起用)
# auto_increment 约束的字段必须是整型数据(int,tingint,smallint,bigint)
create table 表名(
字段1 数据类型 priamry key auto_increment,
字段2 数据类型 [约束条件],
auto_increment = int(数字)
[constraint 自定义外键名} foreign key(本字段) references 关联表(关联字段)
); # 创建表时设置自增字段,其中int为自定义自增初始值
alter table 表名 modify 列名+整数类型 auto_increment; # 在已有的表中设置自增字段
alter table 表名 auto_increment= int; # 在已有的表中设置自增列的初始值
alter table 表名 modify 字段名 数据类型; # 删除自增字段
alter table 本表名 add foreign key(本字段) references 关键表名(关联字段);
alter table 表名 drop foreign key+外键名; # 删除外键约束,再增加自增列,然后再增加外键关联当有外键约束的字段更改方法
foreign key(字段) references 关联表名(字段名); # 关联表格
3. 字段数据操作
select * from +表名; # 查询表所有字段中数据内
insert into 表名(字段1,字段2....)values(数据1,数据2....); # 对表中字段增加数据,其中自增列可以不用写
insert into 表名(字段1,字段2....)values
(数据1),
(数据2),
....; # 同时插入多行数据
insert into 表名1(字段1)select (字段2)from 表名2 where条件; # 将查询条件插入到另一个表中
update 表名 set 字段名1 更新数据 where 字段名2 条件; # 表中数据的更新
delete from 表名[where +条件]; # 删除指定条件下的数据,如果没有指定条件则整个表数据内容全部删除
turncate table 表名; # 删除整个表然后重新新建一个同名的表,速度比delete快
select 字段1,字段2...from 表名; # 查询该表下字段1,字段2....的数据
select 字段1,字段2...from 表名 where 查询条件; # 查询该表下满足条件的字段1,字段2...的数据
select distinct 字段名 from 表名; # 查询该字段中不重复的数据
select * from 表名 where 内容 is null; # 查询空值
# having用于group by分组后的数据查询,where用于group by之前的数据查询
select 字段1,count(*) from 表名 group by 字段1; # 对字段1进行分组
select 字段1,字段2...[count(*)] from 表名 [where 条件1] group by 字段 having 条件2; # 对字段1,字段2...从满足条件1的数据中分组选出满足条件2的数据
select count(*) from 表名 where 条件; # 满足条件的数据个数
select * from 表名 order by 字段1 [asc|desc]; # 对字段1进行排序其中asc为升序,desc为降序,不写默认为升序
# limit限制查询数量
select * from 表名1 where 字段名 = (select 字段2 from 表2 where 条件); # 括号内为子查询:子查询时先执行子查询内容;子查询的结果作为外层查询的条件;自查询结果可以添加uodate,select,delete等语句
select * from 表1,表2; # 内连接,将表1中的所有数据对应表2;
select * from 表1 inner join 表2; # 内连接,将表1中的所有数据对应表2;
select * from 表1 inner join 表2 on 条件; # 内连接,添加条件将表1中满足条件的数据对应到表2
select * from 表1 left join 表2 on 条件; # 左连接,以表1中的数据为基准来连接表2;(表2中数据变化)
select * from 表1 right join 表2 on条件; # 右连接,以表2中的数据为基准来间接1,(表1中数据变化)
select * from 表1 [left|rigth] join 表2 on 条件;
select * from 表3 [left|rigth] join 表4 on 条件;
三、pycharm链接MySQL数据库
pip install pymysql # 导入包
import pymysql # 在pycharm中导入模块
conn = pymysql.connect(
host ="链接IP地址",
port = "链接端口号",
user = "用户名",
password = "密码",
db = "需要链接的数据库",
charset = "编码格式"
) # pycharm链接MySQL
cur = conn.curcor() # 创建游标,所有pycharm中的mysql语句都是通过游标来执行的
cur.execute("mysql语句") # 进行MySQL语句,语句跟交互命令下一样,必须要事件手动查看结果
cur.commit() # 提交,每次数据修改后都要进行数据提交
cur.rollback() # 回滚,将数据回滚,可以添加条件判断来进行回滚
cur.close() # 关闭游标
conn.close() #关闭连接
cur.fetchall() # 查看所有数据,基于上一条execute操作
cur.fetchone() # 查看一条数据
cur.fetchmany(num) # 查看num条数据