MySQL数据库（一）

数据库连接

MySQL数据库用于管理文件的一个软件
服务端软件：socket服务端，解析指令
客户端软件（各种各样）：socket客户端，发送指令
PS：DBMS数据库管理系统。SQL语句

其他类似的软件：sqllite, db2, oracle, access, sql server, MySQL
非关系型数据库：MongoDB, redis

技能：安装服务端和客户端，链接，学习SQL语句

MySQL安装
Windows：第一种安装方法：可执行文件====点点点的安装
第二种安装方法：
压缩包：放置任意目录，初始化：在E:\MYSQL\mysql-8.0.12-winx64\bin\mysqld目录下，执行 --initialize-insecure，创建了root账户，密码为空。
启动服务端：E:\MYSQL\mysql-8.0.12-winx64\bin\mysqld
客户端连接：E:\MYSQL\mysql-8.0.12-winx64\bin\mysql -u root -p
发送指令：show databases;

添加环境变量E:\MYSQL\mysql-8.0.12-winx64\bin
%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0;E:\MYSQL\mysql-8.0.12-winx64\bin;E:\vscode\Microsoft VS Code\bin

安装服务：E:\MYSQL\mysql-8.0.12-winx64\bin\mysqld --install
移除服务：E:\MYSQL\mysql-8.0.12-winx64\bin\mysqld --remove
启动服务：net start MYSQL
关闭服务：net stop MYSQL

MySQL连接
默认：root用户
创建新用户：create user ‘shiqian’@‘192.168.1.%’ identified by ‘123123’;
——create user ‘shiqian’@’%’ identified by ‘123123’; （不限制IP）
创建用户
create user ‘用户名’@‘IP地址’ identified by ‘密码’;
删除用户
drop user ‘用户名’@‘IP地址’;
修改用户
rename user ‘用户名’@‘IP地址’; to ‘新用户名’@‘IP地址’;;
修改密码
set password for ‘用户名’@‘IP地址’ = Password(‘新密码’)
PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）

授权：grant select,insert,updata on db1.* to ‘shiqian’@‘192.168.1.%’ ;
——grant all privileges on db1.* to ‘shiqian’@‘192.168.1.%’ ;（给用户赋予所有权限，除了给别人设置权限的权限）

在公司：DBA给提供用户名和密码。

操作文件夹（库）：
create database db2;
create database db2 default charset utf8;（指定字符集）
show tables;
drop database db2;

操作文件（表）：
show tables;
create table t1(id int,id int,) default charset=utf8;
create table t1(id int,name char(10)) engine=innodb default charset=utf8;

create table t1(
	id int not null auto_increment primary key,
	列名 类型 null,
	列名 类型 not null,
	列名 类型 not null default 1,
	num decimal(10,5),          
	name char(10)
	) engine=innodb default charset=utf8;
	
innodb  支持事物，原子性操作    
myisam  不支持事物

auto_increment表示：自增，一个表里面只能有一个自增列，自增列一定是主键列。
primary key   主键：约束不能重复且不能为空；加速查找，一个表只能有一个主键。
not null  	  是否为空

数据类型：
	数字：
		int tinyint bigint
		float double decimal(精准的)     num decimal(10,5) 共10位，小数5位 
	字符串：
		char(10)		10个位置要占满，查询速度快，最多255
		varchar(10)		节省空间，速度没有char快，最多255
		PS：SQL优化：创建数据表时，把定长的数据往前放。
		text 	长的字符串
		上传文件：文件存硬盘，数据库存路径
		enum 枚举类型	size ENUM('x-small', 'small', 'medium', 'large', 'x-large') 只能插入一个
		set  集合类型	col SET('a', 'b', 'c', 'd')      任意组合都可以插进去
	时间：
		data time datatime
		

清空表：
	delete from t1;        不清除主键索引
	truncate table t1;		清除主键索引
删除表：
	drop table t1;

对于自增：
	desc t1;   查看表结构
	show create table t1 \G;   查看表是如何创建的
	alter table t1 auto_increment=10;    修改表自增列的起始值   
自增步长：
	MySQL自增基于会话级别；SqlServer自增基于表级别。
	基于会话级别：
		show session variables like 'auto_inc%'; 查看全局变量
		set session auto_increment_increment=2;	设置会话步长
		set session auto_increment_offset=2;
	基于全局级别：
		show globle variables like 'auto_inc%'; 查看全局变量
		set globle uto_increment_increment=2; 设置会话步长
		set globle auto_increment_offset=2; 

操作文件内容：
插入数据：
insert into t1(id,name) values(1,‘shiqian’);
删除数据：
delete from t1 where id<6;
修改数据：
update t1 set age=18;
update t1 set age=18 where age=17;
查看数据：
select * from t1;

外键：
create table userinfo(
uid int auto_increment primary key,
name varchar(32),
department_id int,
constraint fk_user_depart foreign key (“department_id”) references department(‘id’),
constraint fk_user_depart foreign key (“department_id”) references department(‘id’) 可以多个外键
) engine=innodb default charset=utf8;

create table department(
	id int auto_increment primary key,
	title varchar(15), 
) engine=innodb default charset=utf8;

唯一索引：
create table t1(
id int,
num int,
xx int,
unique uq1 (num ,xx) 两列合起来唯一
)
PS:唯一：（加速查找）约束不能重复（可以为空） 主键不能重复且不能为空。

外键的变种：
一对多
一对一 外键+唯一
多对多

SQL语句数据行操作补充：
增：
insert into tb11(name,age) values(‘shiqian’,12);
insert into tb11(name,age) values(‘shiqian’,12),(‘root’,18);
insert into tb12(name,age) select name,age from tb11;
删：
delete from tb11;
delete from tb11 where id != 2;
delete from tb11 where id != 2 and name=‘shiqian’;
改：
update tb11 set name=‘alex’ where id=2 and name=‘shiqian’;
update tb11 set name=‘alex’,age=11 where id=2 and name=‘shiqian’;
查：
单表查询操作
select * from tb11;
select id ,name from tb11 where id>2;
select id ,name as cname from tb11 where id<>2;
select id ,name,‘加额外一列’ from tb11;
select * from tb11 where id in (1,5,12);
select * from tb11 where id not in (1,5,12);
select * from tb11 where id between 5 and 12; 闭区间
select * from tb11 where id in (select id from tb12);
通配符：like % _
select * from tb11 where name like ‘a%’; 查询开头为a的任意个字符
select * from tb11 where name like ‘a_’; _代表一个字符
分页：limit
select * from tb11 limit 10; 从第0条开始，往后取10条
select * from tb11 limit 10 10; 从第10条开始，往后取10条
select * from tb11 limit 10 offset 10; 从第10条开始，往后取10条
排序：
select * from tb11 order by id desc; 从大到小
select * from tb11 order by id asc; 从小到大
select * from tb11 order by id asc,age asc; 多个列排序，前面的优先
select * from tb11 order by id desc limit 10; 取后10条数据
分组：
select part_id,count(id) from tb11 group by part_id;
聚合函数：count max min sum avg
对于聚合函数的结果进行二次筛选，必须用having
select part_id,count(id) as count_id from tb11 group by part_id having count(id)>1;

单表的语法顺序与执行顺序

语法顺序：select distinct 字段一，字段二，字段三 from 库.表名
where 条件
group by 分组条件
having 过滤
order by 排序字段
limit n;

执行顺序： from找表
where条件过滤
group by 分组
having过滤 与where的区别是having针对分组之后的表，条件可以是聚合函数，而where不能是聚合函数
distinct去重
order by 排序
limit 分页
打印

连表查询操作

	连表操作：
	select * from tb11,tb12;	笛卡尔积
	select * from tb11,tb12 where tb11.part_id=tb12.department.id;
	连表操作本质上就是对笛卡尔积的条件过滤，但是我们一般不用where，mysql提供了连表查询的语法。

	内链接（只取两张表共同的部分）
	select * from tb11 inner join tb12 on tb11.part_id=tb12.department.id; 共同的记录显示
	左链接
	select * from tb11 left join tb12 on tb11.part_id=tb12.department.id; 左边的表全部显示
	右链接
	select * from tb11 right join tb12 on tb11.part_id=tb12.department.id; 右边的表全部显示
	全外链接(左和右中间加一个union)
	select * from tb11 left join tb12 on tb11.part_id=tb12.department.id union select * from tb11 right join tb12 on tb11.part_id=tb12.department.id; 

多表链接查询

navicat:
图形化客户端

转储SQL文件：
备份：数据表结构+数据
mysqldump -u root db1 > db1.sql -p;
备份：数据表结构
mysqldump -u root -d db1 > db1.sql -p;

还原：
create database db5;
mysqldump -u root db5 < db1.sql -p;

pymysql:
python模块，对数据库进行操作。
1、连接数据库
2、游标
3、增删改查
4、关闭游标
5、关闭连接

import pymysql

user = input('账号：')
pwd = input('密码：')

conn = pymysql.connect(host='localhost', root='root', password='123456', database='')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = "select * from userinfo where username=%(u)s and password=%(p)s"
cursor.execute(sql, user, pwd)           # 防止注入，在这里传入参数作为字符串拼接
cursor.execute(sql, [user, pwd])
cursor.execute(sql, {'u': user, 'p': pwd})
result = cursor.fetchone()    # 只拿一条
result = cursor.fetchone()    # 再拿下一条
result = cursor.fetchmany(3)    # 一次取几条数据
result = cursor.fetchall()# 全部数据，得到一个元组，可以修改成字典cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.close()
conn.close()

conn = pymysql.connect(host='localhost', root='root', password='123456', database='')
cursor = conn.cursor()
sql1 = "insert into userinfo(username,password) values('shiqian','123456')"
r = cursor.execute(sql1)    # 返回值r表示受影响的行数
conn.commit()      # 增删改需要commit，事物

sql1 = "insert into userinfo(username,password) values(%s,%s)"
r = cursor.executemany(sql1, [('alex', 'sb'), ('egon', 'sb')])       # 插入多条数据
conn.commit()

cursor.close()
conn.close()

# 新插入数据的自增ID
cursor.lastrowid()   # 插入数据的最后一条ID