2022-02-15 16:07 已编辑数据分析师

关注

MySQL基础知识

一.mysql基本命令
二.mysql进阶
- 1.外键
- 2.自增
- 3.pymysql
- 4.视图
- 5.索引
- 6.事务
三.mysql重要知识点

一.mysql基本命令

1.基础常用命令

本地登录： mysql -uroot -p密码;(也可以不带密码，之后输入)

远程登录： mysql -h 登录ip -p 端口(通常3306） -uroot -p密码;

查看表的各个字段的属性，以及自增键： desc 表名;

导出数据库文件，保存： mysqldump -u用户 -p 数据库名 > xx.sql;

导入数据库文件： mysql -u用户 -p密码数据库名< xx.sql;(也可以选择登录进去，在选择数据库后，使用source命令导入数据）

2.创建命令

创建用户： create user '用户名' @'ip' identified by '密码';

ip是指用户登录mysql的电脑ip，可以写%，本地写localhost

用户授权： grant 权限(select/insert/updata/all priveleges) on 表/数据库名 to '用户'@'ip' identified by '密码';

删除用户： drop user 用户名@ip

查数据库： show databases;

看表： show tables;

创建数据库： create database 数据库名 default charset utf8;

创建表： create table 表名(列名数据类型约束···，列名数据类型约束···）engine=innodb default charset=utf8

其中数据类型种类数字(int，tinyint,smallint,float,double),字符串(char(个数)varchar(个数))时间(DATE,TIME,DATETIME),枚举enum(值只能是枚举中的元素），集合set(值只能是结合元素的组合)

创建含主键的表： create table 表名(列名数据类型 primary key auto_increment,列名数据类型约束···）engine=innodb default charset=utf8

创建含外键的表： create table 表名(列名数据类型 primary key,列名数据类型 constraint fk_外键名 foreign key (列名[,]) references 表名2（列名[,]）···）engine=innodb default charset=utf8

3.查表命令

条件查询

select * from 表名;
select 列名 from 表名;
select 列名，常量 from 表名; 增加一个常量列
select 列名 from 表名 where 列名 >/</!= value;
select 列名 from 表名 where 列名 in/not in/between and value;
select 列名 from 表名 where 条件1 and 条件2;
特殊的：
select 列名 from 表名 where 列名 in (select 列名(只能一列) from 表名);
select 列名 from 表名 where 列名 like 'xx%'/'%xx'/"xx_";  %代表任意位，_代表一位
select 列名,(select xx from 表名) from 表名 where 条件;  查询条件做常量值

分页

select 列名 from 表名 limit num; 显示num个
select 列名 from 表名 limit num1,num2;从num1后取num2行数据，num1是起始位置，num2是个数
select 列名 from 表名 limit num1 offset num2;从num2后取num1行数据，num2是起始位置，num1是个数

排序

select * from 表名 order by 列名 desc;从大到小排序
select * from 表名 order by 列名 asc;从小到大排序
select * from 表名 order by 列名1 desc 列名2 asc; 首先遵循列1从大到小排序，遇到相同数据时，按列2从小到大排序

分组操作

select count/sum/max/min/avg(列名1），列名2 from 表名 group by 列名(通常是列名2）；分组操作 
select count/sum/max/min/avg(列名1），列名2 from 表名 group by 列名(通常是列名2）having 条件；分组操作后筛选

连表操作

select * from 表1 left join 表2 on 表1.列名=表2.列名;  左连接
select * from 表1 right join 表2 on 表1.列名=表2.列名;  右连接
select * from 表1 inner join 表2 on 表1.列名=表2.列名;  内连接

注意:如果超过3个表联合操作，如果其中两个表操作时已经改变了表结构，应该将这两个表操作的结果作为一个临时表再与第三个表联合操作。

临时表

(select * from 表名)as e

4.删除，修改，插入命令

插入

insert into 表名(列名1，列名2···) values(值1，值2···),(值1，值2···),(值1，值2···);  插入值
insert into 表名1(列名) select 列名 from 表2; 在一个表中插入另一个表中数据

修改

update 表名 set 列名1=value1，列名2=value2 where 条件1 [and/or 条件2]；

删除

delete from 表名; 清除表(如果有自增id，id 不会重新开始)
delete from 表名 where 条件； 清除特定数据
truncate table 表名;清除表(如果有自增id，id 会重新开始)

5.修改表结构

alter table 表名 auto_increment=value;设置自增键起始值；
alter table 表名 drop 列名;删除列
alter table 表名 add 列名 数据类型 约束; 增加列
alter table 表名 change 旧列名 新列名 数据类型;   修改字段类型
alter table 表名 modify 列名 数据类型;   修改数据类型
alter table 旧表名 rename 新表名; 修改表名
alter table 表名 drop primary key; 删除表中主键
alter table 表名 add 列名 数据类型 primary key;添加主键
alter table 表名 add primary key(列名);设置主键
alter table 表名 add column 列名 数据类型 after 列名;在某一列后添加主键

二.mysql进阶

1.外键

外键适用于一对多，一对一，多对多三种情况

一对多

典型案例员工与部门，一个部门对应于多个员工，一个员工对应于一个部分，所以要在员工表中设置部门id列，并设置为外键，与部门表id关联。

一对一

案例博客园用户与博客，不是每个用户都写博客，写博客的用户与拥有的博客地址一一对应，所以在博客用户表user中设置blog_id，设置成外键和唯一索引，与博客表blog中id关联

 create table user(id int not null auto_increment primary key,name char(10), blog_id int,unique uq1(blog_id),constraint fk_user_blog foreign key(blog_id) references blog(id))engine=innodb default charset=utf8;

多对多

典型案例电脑与用户，一个用户可以使用多台电脑，一台电脑对应多个用户，多对多，此时一般选择新建一个表contact，在其中设置两个外键，同时关联用户表id与电脑表id

create table contact(id int not null auto_increment primary key,user_id int,computer_id int,unique uq2(user_id,computer_id),constraint fk_user foreign key(user_id) references user(id),constraint fk_user foreign key(computer_id) references computer(id))engine=innodb default charset=utf8;

可以看情况决定要不要把两个外键弄成联合唯一索引。

2.自增

show create table 表名 [\G];查看表的创建信息

对于自增键，我们可以设置它的初始值以及与自增步长

alter table auto_increment=value;设置自增初始值

对于自增步长有两种类型：

第一种：基于会话级别(只对本次登录有效)

show session variables like "auto_incre%";    查看步长
set session auto_increment_increment=value;  改变步长
set session auto_increment_offest=value;  设置起始值(不常用，因为有之前的alter)

第二种：基于全局级别(对所有会话都有用)

set global auto_increment_increment=value; 设置全局步长

3.pymysql

pymysql 是python第三方模块，主要用于python与mysql交互。

pymysql 方法:

connect(host=,user=,passwd=,database=) 连接数据库

cursor() 建立游标

execute(sql)执行sql语句

fetchone()从查询结果中取出一条数据

fetchmany(num)从查询结果中取出num条数据

fetchall()从查询结果中取出所有数据

commit() 改变(updata，insert，drop)数据库内容专用,execute sql语句后必须commit才能真正改变数据库

close()注意最后有两个close,游标要关闭，连接也要关闭

4.视图

视图是一种虚拟存在的表，本身不包含数据，作为一个select语句保存在数据库中。通俗的讲视图代表的是一条select语句产生的结果集。

create view 视图名称 as select语句; 创建视图

视图单独不能插入，修改数据，因为视图是虚拟的。但可以修改select语句，改变视图结果集。

alter view 视图名称 as sql语句; 修改结果集
drop view 视图名称; 删除视图

5.索引

定义:索引（Index）是帮助MYSQL高效获取数据的数据结构

索引的作用:约束，加速查找

索引种类

普通索引:加速查找
主键索引:加速查找；约束(主键不能为空，不能重复)
唯一索引:加速查找；约束(索引不能重复）
组合索引:多列组成一个索引(联合主键索引，联合唯一索引，联合普通索引)

create index 索引名 on 表名(列名[,列名]);创建[联合]普通索引

create table 表名(列名 数据类型···index 索引名(列名[,列名])); 创建表时创建[联合]普通索引
create unique index 索引名 on 表名(列名);创建唯一索引
create table 表名(列名 数据类型···unique 索引名(列名)); 创建表时创建唯一索引
create unique index 索引名 on 表名(列名,列名);创建联合唯一索引
create table 表名(列名 数据类型···unique 索引名(列名,列名)); 创建表时创建联合唯一索引
drop index 索引名 on 表名; 删除索引

索引结构