Mysql 初始数据库
Mysql是基于C/S端的服务器软件
Mysql服务端
--server端开启
--解析指令
--对文件夹,文件,数据的增删改查
Mysql客户端
--连接S端
--发送指令(sql语句)
一.安装mysql
数据库服务器(一台电脑)
数据库管理系统(Mysql软件)
数据库(data/db 一个文件夹)
表: (一个文件)
记录:多个字段的信息组成一条记录,即文件中的一行内容
1.解压MySQL的压缩包 到E:
2.E:\mysql-5.7.21-winx64\bin 添加系统的环境变量
3.初始化软件data目录
生成data目录 存放数据库→文件→记录
**初始化的时候,一定要等着自己退出 mysqld --initialize-insecure
二.开启mysql服务端
4.mysql(必须授权)
三.开启客户端,连接服务端
5.mysql -uroot -p
四.安装window服务
mysqld --install 安装window服务
mysqld --remove 移除window服务
net start mysql : 开启服务端
net stop mysql: 关闭服务端
五.忘记密码怎么办
(1)先关闭掉之前的mysql服务器的进程
(2)跳过授权表开启mysql的服务端 mysql --skip-grant-tables (开启服务端的约束条件,跳过授权)
(3)客户端连接mysql -uroot -p
(4)更改密码:update mysql.user set authenation_string =password('') where User='root';
(5)刷新权限 flush privis;
查看进程号: tasklist |findstr mysql
杀死进程: taskkill /F /PID 进程号
六.统一字符编码
在Mysql软件的目录下新创建一个my.ini
[mysqld] # 设置mysql的安装目录 **后面的路径一定是安装sql的目录（自己电脑的）** basedir=C:\mysql-5.7.22-winx64\mysql-5.7.22-winx64 # 设置mysql数据库的数据的存放目录，必须是data datadir=C:\mysql-5.7.22-winx64\mysql-5.7.22-winx64\data sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES # mysql端口 port=3306 # 字符集 [mysqld] character-set-server=utf8 collation-server=utf8_general_ci [client] default-character-set=utf8 [mysql] default-character-set=utf8
再次重启mysql 服务端
查看mysql软件配置:
mysql -uroot -p
\s;
七.创建mysql用户
如何创建用户和密码
creat user 'lqy'@'192.118.1.1' identified by '123';
mysql -ulqy -h 192.168.13.29 -P 3306 -p123
授权 grant 这个权限只有root有
grant all privileges on *.* to 'xxx'@'%';
revoke all privileges on *.* from 'xxx'@'%';
show databases; 查看所有的数据库
use db ; 使用数据库
创建表(设计表)
creat table t1(id int,name char(10)) defult charset='utf8';
insert into t1(id,name) values(1,'liu'),(2,'wu');
查询所有的数据 select * from t1;
查询个别字段的数据 select id from t1;
八.sql的基本语法
对数据库 create databa db1;
对表 create table t1(id int,name char(10));
show create table t1;
show tables; 查看所有的表
desc t1; 查看表的详细结构
对数据
insert into t1(id,name) values(1,'liu'),(2,'wu'); 插入
insert into t1value(3,'cui');
select id from t1;
select id,name from t1;
select * from t1;
select * from db1.t1 where id = 2;
关系型数据库:mysql db2 oricle sqlite 表中存储
非关系型数据库:monogodb redis
key: value 基于model 模型 obj.insert
九.存储引擎
mysql 5.5版本之后默认为innodb 存储引擎,另外还有mysiam,memory,blackhole
*memory,在重启mysql或者重启机器后,表内数据清空
*blackhole,往表内插入任何数据,都相当于丢入黑洞,表内永远不存记录
复制表结构和表数据 create table a1 select * from db2.t1;
只复制表结构 create table a2 select * from db2.t1 where 1>2;
create table a3 like db2.t1;
十.数据类型
1.数字:
整型:tinyint int bigint
小数:float: 在位数比较短的情况下不精准
double: 在位数比较长的情况下不精准
decimal:内部原理是以字符串形式去存,精准.(如果用小数,则推荐使用decimal)
2.字符串
char(10):简单粗暴,浪费空间,存取速度块
varchar:精准,节省空间,存取速度慢
sql优化:创建表时,定长的类型往前放(比如性别),变长的往后放(比如地址或描述信息)
>255个字符,超了就把文件路径存放在数据库中.比如图片,视频等找到一个文件服务器,数据库只存在路径或url.
3.时间类型 最常用:datetime
4.枚举型与集合型
enum 和 set
--整型 默认是signed,有符号的 范围 -128~127
sql中没有布尔类型,使用tinyint(1)来表示boolean值1,表示true;tinyint(0)表示false
--int类型后面的存储是显示宽度而不是存储宽度,其他的数据烈性是村春宽度
所以我们来设计表的时候,int类型的字段不用加显示宽度,默认是总长度的位数+1(定义int类型不需要加宽度,使用默认就可以)
--datetime now() sql的内置函数,根据数据类型生成对应的时间格式
--char 定长 存储速度快,浪费空间
varchar 变长,存储速度慢,节省空间
--enum枚举 表示多选一
--set 集合 表示多选一或者多选多
create table consumer(
id int unsigned,
name,varchar(20),
sex enum('male','female','other'),
level enum('vip','svip','vvip'),
fav set('smoke','drink','tangtou'),
);
insert into consumer values(1,'刘清扬','male','vip','drink');
小结:
数据类型:
整型:tinyint int bigint
浮点型: float double decimal
时间: year date time datetime
字符:char 定长>varchar变长>text文本
枚举:enum
集合:set
布尔:boolean tinyint(1) 表示true/0,表示false
函数:now()根据字段的数据类型获取当前的时间格式
char_length:获取字符长度
length():获取字节长度
sql中默认是有符号的,如果设置无符号unsigned,用0填充 zerofill
十一.约束***
作用:保证数据的完整性和一致性
表的设计
1.not null 与 default
create table tb1(id int not null default 2,name char(20) not null);
insert into tb1 values(1,'liu');
insert into tb1(name) values('wu');
insert into tb1(id) values(3);
2.unique
单列唯一
create table dep(id int not null,name varchar(20) unique); insert into dep(id,name) values(1,'liu'); insert into dep(id,name) values(2,'liu');
多列唯一,表示每一列都唯一
create table dep2(id int unique,name varchar(20) unique); insert into dep2(id,name) values(1,'liu');
#不能插入值
insert into dep2(id,name) values(1,'liu2');
insert into dep2(id,name) values(2,'liu');
组合唯一(联合唯一) 只要有一列不同,就可以插入数据
create table dep3(
id int,
name varchar(20),
unique(id,name)
);
insert into dep3(id,name) values(1,'liu');
insert into dep3(id,name) values(1,'liu2');
insert into dep3(id,name) values(2,'liu');
#不能插入的格式
insert into dep3(id,name) values(1,'liu');
3.primary key(索引优化查询)
sql版本中,一张表只允许有一个主键,通常都是id,cid,nid,sid
create table stu(
id int primary key auto_increment,
name varchar(10) unique
);
insert into stu(name) values ('alex');
化学反应： not null + unique
create table stu2(
id int not null unique auto_increment,
name varchar(10) unique
);
primary key(索引优化)
key 查询优化:前提,准备大量数据
4.auto_increment 自增长
create table tb1(id int primary key auto_increment,name varchar(20) not null);
create table tb2(id int primary key auto_increment,name varchar(20) not null);
insert into tb1(name) values ('liu');
insert into tb2(id,name) values(4,'liu');
5.清空表区分delete和truncate的区别:
delete from t1:如果有自增id,新增的数据,仍然是以删除前的最后一样作为起始.
truncate table t1:数据量大,删除速度比上一条快,且直接从0开始.
6.完整性约束补充
foreign key 外键 建立两张表之间的联系
dep : 被关联表 主表
create table dep(
id int primary key auto_increment,
name vachar(20) not null,
des varchar(30) not null
);
create table dep2(
id int primary key auto_increment,
name vachar(20) not null,
des varchar(30) not null
);
emp:关联表 从表
create table emp(
eid int primary key auto_increment,
name char(10) not null,
age int not null,
dep_id int not null,
dep2_id int not null,
constraint fk_dep foreign key(dep_id) references dep (id)
on delete cascade # 同步删除
on update cascade, #同步更新
constraint fk_dep2 foreign key(dep2_id) references dep2 (id)
on delete cascade # 同步删除
on update cascade, #同步更新
);
7.外键的变种.
(1)先站在左表的角度 ,左表的多条记录对应右表的一条记录 成立
(2)先站在右表的角度,右表的多条记录对应左表的一条记录 成立
多对一 或者多对一,(1)和(2)条件 有一个成立
多对多,(1)和(2)都成立,通过建立第三张表,来建立多对多的关系
一对一,(1)和(2)都不成立,给一个表的fk的字段设置约束unique
多对多:使用第三张表建立多对多的关系
create table book(
id int primary key auto_increment,
name varchar(20)
);
create table author(
id int primary key auto_increment,
name varchar(20)
);
create table auto_book(
id int primary key auto_increment,
book_id int not null,
author_id int not mull,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade
);
insert into book(name) values('九阳神功'),('九阴真经'),('九阴白骨爪'), ('独孤九剑'),('降龙十巴掌'),('葵花宝典');
insert into autho_book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1);
8.核心:数据驱动视图
json -- server
设计模式: MVC ===>MTV
Model --View --Controller
十二.sql的查询
1.单表查询
(1),表单查询的语法
SELECT 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
(2)关键字的执行优先级*
from→where→group by→having→select→distinct→order by→limit
1.找到表:from
2.拿着where指定的约束条件,去文件/表中取出一条条记录
>,<,>=,<=,!=
between xxx and ooo
and or not
3.将取出的一条条记录进行分组group by,如果没有group by.则整体作为一组
sql_mode = ONLY_FULL_GROUP_BY
分组之后只能获取分组的字段,如果哦想获取组内的信息,需通过聚合函数
聚合函数:count(1) 计算总个数
avg() sum() max() min()
4.将分组的结果进行having过滤
二次筛选,having后面的字段只能是分组(group by)之后的字段
5.执行select
6.去重
7.将结果按条件排序:order by
asc升序 / desc降序
8.限制结果的显示条数
limit 0,5 第一个参数起始位置,第二个参数显示的个数
2.多表查询
外连接操作:select * from t1,t2 where t1_t2id = t2.id
内连接: 符合条件查询,只连接匹配的行
select * from t1 inner join t2 on t1_t2id = t2.id
左连接:优先显示左表记录
select * from t1 left join t2 on t1_t2id = t2.id
右连接:优先显示右表记录
select * from t1 right join t2 on t1_t2id = t2.id
全部连接
select * from t1 left join t2 on t1_t2id = t2.id
union
select * from t1 right join t2 on t1_t2id = t2.id
3.符合条件查询
以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25，即找出年龄大于25岁的员工以及员工所在的部门
select department.name,employee.name from employee inner join department on employee.dep_id = department.id where age > 25
4.子查询
子查询是将一个查询语句嵌套在另一个查询语句中;一个查询语句(内层)查询的结果作为另一个查询语句(外层)的条件
十三.pymysql的使用
pip3 install mysql 安装mysql包
import pymysql
conn = pymysql.connect(
host:'127.0.0.1',
port:3306,
database:'db1',
user:'root',
password:'',
charset:'utf8'
);
cur = conn.cursor()
sql = 'select * from userinfo'
res = cur.execute(sql,()||[]||{})
cur.close()
conn.close()
1.pymysql 增 删 改 一定要加commit()
2.pymysql 查询
fetchone()
fetchmany(size)
fetchall
十四.索引
作用:约束+加速查询
普通索引:create index ix_name on 表名(字段)
作用:加速查询
唯一索引:create unique index un_index_name on 表名(字段)
作用:约束和加速查询
主键索引: 加速查找和唯一约束（不含null）
#覆盖索引:在索引文件中直接获取数据
select name from data where name = 'liu10000';
#索引合并:把多个单列索引一起使用
select * from data where name = 'liu520' and id 520;
#联合索引:联合普通索引
联合唯一索引
联合主键索引
#最左前缀
如果使用组合索引加上,name和email组合索引之后,查询
(1)name和email ---使用索引
(2)name ---使用索引
(3)email ---不适用索引(字段太长)
对于索引
1.创建索引
注意事项(优化):
(1)避免使用select*
(2)count(1)或count(列) 代替count(*)
(3)创建表时尽量使用char代替varchar
(4)表的字段顺序固定长度的字段优先
(5)组合索引代替代替多个单列索引(经常使用多个条件查询时)
(6)尽量使用短索引
(7)使用连接(join)来代替子查询
(8)连表时注意条件类型需一致
(9)索引散列(重复少)不适用于建索引,列如:性别不合适
2.命中索引
3.正确使用索引
