MySQL基础

MySQL

目录

• MySQL
  • 一、环境搭建
    • 下载解压
    • 创建本地配置文件
    • 初始化mysql
    • 启动mysql
    • 测试连接mysql
    • 密码相关
    • 基础命令
    • python操作mysql
    • SQL注入
    • 基本数据类型
      • 整型
      • 严格模式
      • 浮点型
      • 字符型
      • 时间类型
      • 枚举与集合类型

一、环境搭建

以windows下5.7版本为例

• 下载解压

https://downloads.mysql.com/archives/community/

下载64位压缩包在预计安装目录解压

• 创建本地配置文件

在mysql安装根目录下创建my.ini文件

[mysqld]

# 自定义服务端口
port=3306

# 自定义basedir
basedir=D:\\test\\mysql

# 自定义数据目录
datadir=D:\\test\\mysql\\data

配置文件查找顺序

C:\Windows\my.ini

C:\Windows\my.cnf

C:\my.ini

C:\my.cnf

path\to\mysql\my.ini

path\to\mysql\my.cnf

• 初始化mysql

管理员CMD窗口

"path\to\bin\mysqld.exe" --initialize -insecure

• 启动mysql

临时启动：

"path\to\bin\mysqld.exe" 

关闭窗口即可停止服务

制作服务启动：

"path\to\bin\mysqld.exe" --install server_name
net start server_name

停止服务

net stop server_name

移除服务

"path\to\bin\mysqld.exe" --remove server_name

• 测试连接mysql

"path\to\bin\mysql.exe" -h host_ip -P port_num -u username -p

show databases;
exit;

• 密码相关

# 连接登录mysql

# 设置/修改当前用户密码
set password = password("your_password");

密码找回

修改my.ini配置文件，在[mysqld]节点下添加skip-grant-tables=1

[mysqld]
skip-grant-tables=1
# 统一编码
# 创建my.ini配置文件，并保存进以下内容：
# [mysqld]
# character-set-server=utf8
# collation-server=utf8_general_ci
# [client]
# default-character-set=utf8
# [mysql]
# default-character-set=utf8

重启mysql并无密码进入mysql

net stop server_name
net start server_name
mysql -u root -p

修改密码

use mysql;
update user set authentication_string=password("new_password"), password_last_changed=now() where user="root";

退出mysql并移除之前在my.ini配置文件中添加的内容

[mysqld]
# skip-grant-tables=1

重启mysql即可持新密码进入

net stop server_name
net start server_name
mysql -u root -p

• 基础命令

# 查看数据库
show databases;

# 创建数据库
create database db_name DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

# 删除数据库
drop database db_name;

# 进入数据库
use db_name;

# 查看当前数据路下的数据表
show tables;

# 创建数据表
create table pymysql(
    id int auto_increment primary key,
    ip varchar(32) not null,
    type varchar(32) default "test",
    origin varchar(32) null
)default charset=utf8;

# 删除数据表
drop table pymysql;

# 清空表
delete from pymysql;
truncate table pymysql;

# 修改表
alter table pymysql add email varchar(32) null auto_increment;
alter table pymysql drop column email;
alter table pymysql modify column ip varchar(64);
alter table pymysql change origin visitor int(5)zerfill not null primary key auto_increment;
alter table pymysql alter type set default "python"
alter table pymysql alter type drop default;

# 新增表数据
insert into pymysql (id, type) values (1, "test"), (2, "python");

# 删除表数据
delete from pymysql; # 清空
delete from pymysql where id<2 and type="test";

# 修改数据
update pymysql set type="java" where id=2;
update pymysql set type=concat(type, "c") where id=2;

# 查询数据
select * from pymysql;
select id, type as i, t from pymysql where id=2;

• python操作mysql

安装pymysql

pip install pymysql

用python操作mysql数据库

import pymysql

# 获取数据库连接
conn = pymysql.connect(host='*****', port=3306, user='***', password='****', charset='utf8')
# 获取游标
cursor = conn.cursor()
# 执行查看数据库命令
cursor.execute('show databases')
# 获取命令执行结果
result = cursor.fetchall()

print(result)  # (('information_schema',), ('mysql',), ('performance_schema',), ('sys',))

# 创建数据库
cursor.execute('create database pymysql DEFAULT CHARSET utf8 COLLATE utf8_general_ci')
conn.commit()
cursor.execute('show databases')
print(cursor.fetchall())  # (('information_schema',), ('mysql',), ('performance_schema',), ('pymysql',), ('sys',))

# 删除数据库
cursor.execute('drop database pymysql')
conn.commit()
cursor.execute('show databases')
print(cursor.fetchall())  # (('information_schema',), ('mysql',), ('performance_schema',), ('sys',))

# 查看数据库表
cursor.execute('use mysql')
cursor.execute('show tables')
print(cursor.fetchall())

cursor.close()
conn.close()

• SQL注入

import pymysql

# 获取数据库连接
conn = pymysql.connect(host='****', port=3306, user='***', password='****', charset='utf8')
# 获取游标
cursor = conn.cursor()

usr = input('请输入用户名：')
pwd = input('请输入密码：')
# 防止SQL注入，应使用cursor.execute()传参完成字符串拼接
cursor.execute("select * from user where name=%s and password=%s", [usr, pwd])
# cursor.execute("select * from user where name=%(usr)s and password=%(pwd)s", {'usr':usr, 'pwd':pwd})

cursor.close()
conn.close()

存储引擎

innodb:version5.5+默认引擎
myisam:version5.5-默认引擎
memory:内存引擎（数据全部存放在内存中）
blackhole:无论存什么，都立刻消失

基本数据类型

整型

TINYINT    1byte -128~127   (默认)               无符号：0~2^n-1
SMALLINT   2bytes -32768~32767
MEDUIMINT  3bytes -8388608~8388607
INT        4bytes -2147483648~2147483647
BIGINT     8bytes ~
#默认带符号
#存入超出设定范围的值，只存最近的临界值
#指定无符号：create table test(id tinyint unsigned,name char(16));


#数据类型后面（）内跟的宽度限制一般是宽度位数，特殊情况：
ID int(8)
如果存入数字不足8位，用' '填充至8位，否则在类型范围内放入几位存几位。
create table test4(id int(8) unsigned zerofill);

严格模式

# 查看严格模式
show variables like '%mode';

%:匹配任意多个字符，_匹配任意单个字符

#修改严格模式：
set session 仅当前界面(临时)
set global 全局有效
set global sql_mode = 'STRICT_TRANS_TABLES';

浮点型

FLOAT:   float(255,30),共255位，小数部分30位
DOUBLE:  double(255,30),共255位，小数部分30位
DECIMAL: decimal(65,30),共65位，小数部分30位
精确度：FLOAT<DOUBLE<DECIMAL

字符型

char:定长     超长报错，不足用' '补全 读写速度快

varchar:变长  超长报错，不足按实际存（省空间）   使用较多

char_length：统计字段长度
select char_length(name) from test5;
# 显示补全空格
set global sql_mode = 'STRICT_TRANS_TABLES,PAD_CHAR_TO_LENGTH';

时间类型

date:
time:
datetime:

枚举与集合类型

枚举：enum 多选一
集合：set  多选多