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

1[mysqld]
2# 设置mysql的安装目录 **后面的路径一定是安装sql的目录（自己电脑的）**
3basedir=C:\mysql-5.7.22-winx64\mysql-5.7.22-winx64
4# 设置mysql数据库的数据的存放目录，必须是data
5datadir=C:\mysql-5.7.22-winx64\mysql-5.7.22-winx64\data            sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
6# mysql端口
7port=3306
8# 字符集
9[mysqld]
10character-set-server=utf8
11collation-server=utf8_general_ci
12[client]
13default-character-set=utf8
14[mysql]
15default-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 集合 表示多选一或者多选多

1create table consumer(
2             id int unsigned,
3             name,varchar(20),
4             sex enum('male','female','other'),
5             level enum('vip','svip','vvip'),
6             fav set('smoke','drink','tangtou'),
7 );
8  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

1create table tb1(id int not null default 2,name char(20) not null);
2insert into tb1 values(1,'liu');
3insert into tb1(name) values('wu');
4insert into tb1(id) values(3);

　　　　2.unique

单列唯一

1create table dep(id int not null,name varchar(20) unique);
2insert into dep(id,name) values(1,'liu');
3insert into dep(id,name) values(2,'liu');

　　　　　　多列唯一,表示每一列都唯一

1create table dep2(id int unique,name varchar(20) unique);
2insert into dep2(id,name) values(1,'liu');#不能插入值insert into dep2(id,name) values(1,'liu2');
3
4insert into dep2(id,name) values(2,'liu');

　　　　　　组合唯一(联合唯一) 只要有一列不同,就可以插入数据

1create table dep3(
2            id int,
3            name varchar(20),
4            unique(id,name)
5  );
6insert into dep3(id,name) values(1,'liu'); 
7insert into dep3(id,name) values(1,'liu2');    
8insert into dep3(id,name) values(2,'liu');    
9#不能插入的格式
10insert into dep3(id,name) values(1,'liu');

　　　　3.primary key(索引优化查询)

sql版本中,一张表只允许有一个主键,通常都是id,cid,nid,sid

1create table stu(
2		id int primary key auto_increment,
3		name varchar(10) unique
4);
5insert into stu(name) values ('alex');
6				
7化学反应： not null + unique
8create table stu2(
9		id int not null unique auto_increment,
10		name varchar(10) unique
11);

　　primary key(索引优化)

　　key 查询优化:前提,准备大量数据

　　　　4.auto_increment 自增长

1create table tb1(id int primary key auto_increment,name varchar(20) not null);
2create table tb2(id int primary key auto_increment,name varchar(20) not null);
3insert into tb1(name) values ('liu');
4insert into tb2(id,name) values(4,'liu');

　　　　5.清空表区分delete和truncate的区别:

delete from t1:如果有自增id,新增的数据,仍然是以删除前的最后一样作为起始.

truncate table t1:数据量大,删除速度比上一条快,且直接从0开始.

6.完整性约束补充

foreign key 外键 建立两张表之间的联系

dep : 被关联表  主表

1create table dep(
2            id int primary key auto_increment,
3            name vachar(20) not null,
4            des varchar(30) not null
5);
6create table dep2(
7            id int primary key auto_increment,
8            name vachar(20) not null,
9            des varchar(30) not null
10);

　　　　　　emp:关联表  从表

1create table emp(
2           eid int primary key auto_increment,
3           name char(10) not null,
4           age int not null,
5           dep_id int not null,
6           dep2_id int not null,
7           constraint fk_dep foreign key(dep_id) references dep (id)
8           on delete cascade # 同步删除
9           on update cascade, #同步更新
10           constraint fk_dep2 foreign key(dep2_id) references dep2 (id)
11           on delete cascade # 同步删除
12           on update cascade, #同步更新
13);

　　　　7.外键的变种.

(1)先站在左表的角度 ,左表的多条记录对应右表的一条记录   成立

(2)先站在右表的角度,右表的多条记录对应左表的一条记录   成立

多对一 或者多对一,(1)和(2)条件 有一个成立

多对多,(1)和(2)都成立,通过建立第三张表,来建立多对多的关系

一对一,(1)和(2)都不成立,给一个表的fk的字段设置约束unique

多对多:使用第三张表建立多对多的关系

1create table book(
2            id int primary key auto_increment,
3            name varchar(20)
4);
5create table author(
6            id int primary key auto_increment,
7            name varchar(20)
8);
9create table auto_book(
10            id int primary key auto_increment,
11            book_id int not null,
12            author_id int not mull,
13            constraint fk_book foreign key(book_id) references book(id)
14            on delete cascade
15            on update cascade,
16            constraint fk_author foreign key(author_id) references author(id)
17            on delete cascade
18            on update cascade
19);
20insert into book(name) values('九阳神功'),('九阴真经'),('九阴白骨爪'), ('独孤九剑'),('降龙十巴掌'),('葵花宝典');  
21insert into autho_book(author_id,book_id) values
22(1,1),
23(1,2),
24(1,3),
25(1,4),
26(1,5),
27(1,6),
28(2,1),
29(2,6),
30(3,4),
31(3,5),
32(3,6),
33(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包

1import pymysql
2conn = pymysql.connect(
3            host:'127.0.0.1',
4            port:3306,
5            database:'db1',
6            user:'root',
7            password:'',
8            charset:'utf8'
9);
10cur = conn.cursor()
11sql = 'select * from userinfo'
12res = cur.execute(sql,()||[]||{})
13cur.close()
14conn.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.正确使用索引