mysql 具体知识

0、删除mysql注册表

以管理员身份打开命令行

delete mysql(数据库名)
此行为会删除本地mysql的注册表但不会删除数据库源文件。

创建注册表 mysqld --install mysql
此行为会将mysql重新注册。
不输入后边的名称默认名字叫mysql

查询mysql安装路径：先登录mysql，然后输入show variables like "%char%";

查询mysql版本：登录后输入status或\s；也可以使用sql语句：select version() from dual;

0.1、问题

·在命令窗口中输入mysql命令时，提示“mysql不是内部命令也不是可运行的程序”，发现此问题是由于系统的环境变量中未配置MySQL安装目录可执行文件的路径
将mysql安装路径添加指用户和系统的path环境变量中

·如果命令行无法启动，则在系统变量的path中添加C:\windows\System32
·前提是在这里面加入命令行程序

·插入中文报错
	set character_set_client=gbk;通知服务器客户端使用编码是gbk
	set character_set_results=gbk;通知服务器客户端查看结果集使用编码是gbk

1、数据库：

0.数据库登录：mysql -u root -p（后面可携带密码）

远程登录：mysql -hlocthorst -P端口号 -u用户名 -p密码

数据库退出：exit/quit;

1.库的操作：

增加：create database (自定义数据库名字) charset utf8;

查找：show databases;

删除：drop database (定义的数据库名字);

使用数据库：use (数据库的名字);

查看表结构：desc 表名;

查看创建表的细节：show create table 表名;

2、表的操作：

建表：create table (表名) (

字段(英文) 字段类型 约束);

字段类型：

​ 整形：int、tinyint

​ 小数：decimal：decimal(5,2)代表一共5位数，小数占2位

​ 字符串：varchar、char、text(极少用,存储大文本才用)

​ varchar:可变长度，varchar(3) ， "ab" 2位

​ char:固定长度，比如char(3),代表3个字符，"ab"会用空格补全3

​ 时间日期类型：date、time、datetime

​ datetime:日期—"年—月—日 时:分:秒"

​ date："年—月—日"(日期)

​ time："时:分:秒"(时间)

​ 枚举(单选)：enum("男","女","保密")

​ 默认值: default ""

​ 集合：set

​ 需注意：最后一个字段后不能写逗号；库里面表名不能重复；表里面字段名不能重复，不区分大小写。

约束：

​ 约束：constraint

​ 非空约束：not null :非空，指定一行的值不能为空。

​ default(默认值)约束：default：指定默认值。

​ 唯一约束：unique key：可以保证一列或者几列不出现重复值。

​ 主键约束：primary key：用来保证数据完整性。可以包含非空约束和唯一约束。

​ 自增：auto_increment: 自动增加。

添加：

​ 全列添加：insert into 表名 values(值);——插入顺序要和表内字段顺序对应。

​ 指定列添加 (部分列)：insert into 表名(字段名) values(值);

​ 指定列添加 (部分列多行插入)：insert into 表名(字段名1,字段名2) values(值1),(值2)……;

​ 添加字段：alter table 表名 add 字段名 类型 约束;

查找：

​ 指定字段起别名 ：字段名as名字——逗号分割名字和字段名。

​ 查询限制数据：select * from 表名 limit *——限制显示数据

​ 查看数据库：show databases;

​ 查看表：show tables;

​ 查看表结构：desc (表名);

​ 查询所有列: select * from 表名;

​ 查询指定列: select 字段名1,字段名2... from 表名;

​ select * from 表名 where 字段=值;

​ 范围查询：select * from 表名 where 字段(最好是整形) in ();——用于不连续数字。

​ select * from 表名 where 字段 in (字符);——查询在该字符间的内容。

​ select * from 表名 where not 字段 in(字符);——查询不在该字符内的(输出除去该字符以外的其他内容)。

​ select * from 表名 where 字段 between 字符 and 字符;——查询1到2之间的内容(含头含尾)。

​ select * from 表名 where 字段 not between 字符 and 字符;——查询不在1-2之间的其他内容。

​ 去除重复：select distinct 字段 from 表名;

​ 查询重复数据：select * from 表名 where 字段 in (select 字段 from 表名 group by 字段 having count(字段)>*)——子查询

​ (查询年份：select * from 表名 where year(字段)=值;)

​ 起别名：select * from 表名 as 名字;

​ select 字段 as 名字 from 表名 as 名字;

​ 模糊查询： select * from 表名 where 字段 like"字符%";——以该字符开头的。

​ select * from 表名 where 字段 like "%字符%"——含有该字符的。

​ "_":匹配一个

​ select * from 表名 where 字段 like "__"——匹配两个。

​ select * from 表名 where 字段 like "__%"——至少匹配两个。

​ select * from 表名 where 字段 rlike "^字符.*$";——查询以该字符开始的。

​ select * from 表名 where 字段 rlike "^字符.*字符$"——查询以1开头以2结尾的。

​ 空判断：select * from 表名 where 字段 is null;

​ 非空判断：select * from 表名 where not 字段 is null;

​ select * from 表名 where 字段 is not null;

​

修改：

​ 修改表名: alter table (原表名) rename to (新表名);

​ 修改表字段类型: alter table (表名) modify 字段名 字段类型 约束;——不 重名版

​ 修改字段名：alter table (表名) change 原字段名 现字段名 类型 约束;

​ 修改数据：update 表名 set 字段=新值 where 条件(字段名=值);

删除：

​ 删除数据库：drop database 数据库名;

​ 删除表：drop table 表名;

​ 删除字段：alter table 表名 drop 字段名;

​ 删除数据(物理暴力删除)：delete from 表名 where 条件(字段名=值);——删除的数据不易恢复。

​ 删除所有数据留有表结构：truncate table 表名;

​ 删除数据(逻辑删除)：

​ alter table 表名 add 字段 类型 约束;——添加删除表示字段，0表示未删除，1表示删除。

​ update 表名 set 字段=值 where 条件;——逻辑删除本质就是修改数据。

​ between使用：where 字段 between（）and（）——在…到…之间。

注意：delete删除后主键自增还会保留；truncate删除后会将主键重新清空。

3、排序与分页

order by

不写的情况下默认是升序。

如果多个字段排序只需,隔开即可。

一个字段数据相同时可用另一个字段进行排序。

升序：

​ asc

​ select * from 表名 order by 字段 asc;——根据该字段排序

​ select * from 表名 where 字段 between 字符 and 字符 order by 字段 asc;——根据该字段排序

降序：

​ desc

​ select * from 表名 order by 字段 desc——根据该字段排序

​ select * from 表名 where 字段 between 字符 and 字符 order by 字段 desc;——根据该字段排序

分页：

​ limit

​ limit num:根据数字量显示多少条数据，写多少显示多少，数据量不够则显示全部数据。

​ limit num1(起始值),count(查询次数): 显示从起始值到次数值的数据。

​ 例如1,3：1代表查询起始值，起始值+1；3代表查询数量。

​ 原理：页面=(起始值-1)*查询次数=结果,显示个数。

4、分组与过滤

group_concat(字段) 每个分组的字段值都显示出来

分组：group by: 分组

​ select 字段 from 表名 group by 字段;

​ select 字段,group_concat(字段) from 表名 group by 字段;

​ having（过滤、筛选）：对分组结果进行过滤。用在group by后面。

​ group_concat:分字段。

​ "-": 表示分割，将分组所有结果连接起来。

查询多个字段并分割：select 字段,count(统计次数)(*),group_concat(字段1"-"字段2……) from 表名 group by 字段;

5、聚合函数

SELECT distinct 列名称 FROM 表名称——去重

select max(字段) from 表名;——找到该字段最大的值。

select min(字段) from 表名;——找到该字段最小的值。

select avg(字段) from 表名;——找到该字段的平均值。

select sum(字段) from 表名;——求和。

select count(字段) from 表名;——统计次数。

round：小数，保留位数。保留位数写0就是没有小数，1就是一位。

6、表连接查询

注意：查询null时为is而不是=

概念：当查询字段来源于多张表时，可以使用连接查询。

​ 连接查询分类：内连接、外连接、交叉连接(了解)。

​ 外连接分为左连接和右连接(理解其一即可)。

​ 内连接作用：两张表共同的部分。

​ 语法：select * from 表1名 inner join 表2 on 条件(字段=表2.相关联字段);

​ 条件：两张表相关联的字段。

​ 注意：如果两张表中有重名字段需标明要查询哪张表的字段。

左外连接：

​ select * from 表1名 left join 表2 on 条件(字段=表2.相关联字段);

​ 作用：将左表为主表(left旁的表名)，将表内所有的数据全部显示出来，如果关联表中没有对应的数据则会用null补充。

右外连接：

​ select * from 表1名 right join 表2 on 条件(字段=表2.相关联字段);

​ 作用：将右表为主表(以表2为主)，如果关联表中没有对应的数据则会用null补充。

自关联：

​ 一张表里自己关联自己的字段。

​ 格式：select a.name,b.name from 表名 as a(起别名) inner join 表名 as b(起别名) on a.id=b.pid(相同字段关联) where (别名).name=字段;

​ 当表起别名后就必须使用别名。

外键：

​ 字段名 字段类型,foreign key(连接子字段) references 连接表名(主字段)

​ create table 表名(字段，类型，约束);

​ create table 表2(字段，类型，外键约束);

​ 注意：设置外键约束的情况下表二类型要和表一类型相同才能连接。

​ 获取外键约束名称：show create table 表名;

​ 删除外键：alter table 表名 drop foreign key 外键名;

7：drop，truncate，delete 三者的区别

drop：删除内容和定义，释放空间。（表结构和数据一同删除）
truncate：删除内容，释放空间，但不删除定义。（表结构还在，数据删除）
delete：删除内容，不删除定义，也不释放空间。

https://blog.csdn.net/GRAY_KEY/article/details/86742248

子查询

概念：将两个select语句合成一个。

语法：select * from 表名 where 字段 in(select 聚合(最大、小、平均等) from 表名);

分类：标量子查询：子查询返回结果是一个数据（一列一行）。

​ 列子查询：返回结果是一列（一列多行）。

​ 行子查询：返回结果是一行（一行多列）。

关键字：in:在……里

​ ayn:任何;任意一个

​ some：同上

​ select * from 表名 where 字段 any(select 字段 from 表名 条件);

some是any的别名。

​ all：所有;全部

​ exists：子查询语句有结果才会执行主查询语句,子查询语句没有结果则会返回空

事物：unsigned:

​ 事物是一个操作序列，要么全部成功，要么全部失败。是一个不可分割的工作单元。

事物特性：原子性：保证事物要么全部成功要么全部失败。

​ 一致性：保证数据的一致性。

​ 隔离性：执行语句之间不会互相影响。

​ 持久性：永久保存数据。

开启事物：begin;start transcation

回滚：rollback,报错就回滚

提交：commit

索引：一种特殊文件，包含着对数据库表里所有记录的引用指针。相当于书的目录。

常见索引：普通索引：index

​ 唯一索引：primary key;unique

​ 联合索引：unique(字段);在文字字段后方有字符限制时也需要再输一次限制

​ 全文索引：fulltext：用于搜索长篇文章

​ 空间索引：spatial(仅限了解)

创建索引：1：建表事创建索引

语法：create table 表名(字段 类型,unique(字段(在文字字段后方有字符限制时也需要再输一次限制)));

​ 2：create 在已存在的表上创建索引

语法：create 索引 index 索引名(自设) on 表名(字段(限制));

​ 3：alter table 在已存在的表上创建索引

语法：alter table 表名 add 索引名(unique(字段(字符限制)));

set profiling=1; : 开启运行时间监测

show profiling(es): 查询运行时间

show index from 表名: 查询索引名字

drop index 索引名 on 表名：删除索引

pymysql：是在python3之后用于连接mysql服务器的一个库。

pymysql的大致过程:1、导包索引

​ import oymysql

​ 2、打开数据库连接

​ 变量名=connect(host="主机(localhost)",port="端口(3303)",database="数据库名",user="用户(root)",password="密码("自设密码"")",charset="utf8")

​ 3、获取游标

​ cursor()

​ 4、执行sql语句

​ execute()

​ 5、关闭游标、数据库

​ 变量名.close、con.close

建表：

import pymysql
con=pymysql.connect(host="localhost",port=3306,user="root",password="746860",database="db1",charset="utf8")
print("连接") 显示连接则表示数据库连接成功。
c1=con.cursor()
sql="""create table s1(
    id int primary key auto_increment,
    name varchar(10) not null,
    age int,
    gender enum("男","女") default "男"
);"""
c1.execute(sql)
c1.close()
con.close()
查看报错：pymysql.err.OperationalError: (1050, "Table 's1' already exists")
报错最后一行有这个代表该列表已存在无法再创建相同列表。

添加数据：

import pymysql
# 导包
con=pymysql.connect(host="localhost",port=3306,user="root",password="746860",database="db1",charset="utf8")
# 连接数据库
c1=con.cursor()
c1.execute("""insert into s1 values(1,"芜湖",19,"男")""")
# 获取游标、添加数据
try:     # 存放可能出现错误的代码
	con.commit()
# 提交数据
	print("插入成功")
except:   # 获取异常代码
	con.rollback()
	print("插入失败")
c1.close()
# 关闭游标
con.close()
# 关闭数据库

修改数据：

import pymysql
# 导包
con=pymysql.connect(host="localhost",port=3306,user="root",password="746860",database="db1",charset="utf8")
# 连接数据库
c1=con.cursor()
c1.execute(update 表名 set 字段=新值 where 字段=旧值)
# 获取游标、添加数据
con.commit()
# 提交数据库
c1.close()
# 关闭游标
con.close()
# 关闭数据库

删除数据：

import pymysql
# 导包
con=pymysql.connect(host="localhost",port=3306,user="root",password="746860",database="db1",charset="utf8")
# 连接数据库
c1=con.cursor()
c1.execute("""delete from 表名 where 字段=值""")
# 获取游标、删除数据
con.commit()
# 提交数据
c1.close()
# 关闭游标
con.close()
# 关闭数据库

查找数据：

import pymysql
# 导包
con=pymysql.connect(host="localhost",port=3306,user="root",password="746860",database="db1",charset="utf8")
# 连接数据库
c1=con.cursor()
# 获取游标
sql="""select * from stu"""
c2=c1.execute(sql)
print(c2) # 显示全部行列数。
或者
c1.execute(sql)
print(c1.frtchone)# 只显示一条数据，可搭配for循环输出全部数据。
print(c1.fetchmany())# 只取一条，写参数代表读取几条。
print(c1.fetchall)# 读取全部行列内容。
con.commit  # 提交数据