MySQL数据库

mysql -uroot -p

mysqladmin -uroot -p原密码 password 新密码

net stop mysql
mysqld --skip-grant-table
update mysql.user set password=password('123') where Host='localhost' and User='root';
最后关闭服务端然后正常运行方式启动即可解决忘记密码的问题

create database 库名;

alter database 库名 charset='utf8';

drop database 库名;

use 库名;

create table 表名(字段名 字段类型,字段名 字段类型);

alter table 表名 rename 新表名;

drop table 表名;

nsert into 表名 values(数据值1,数据值2);

select * from 表名;

update 表名 set 字段名=新数据 where 筛选条件;

delete from 表名 where id=2;

show databases;  

show tables;

select * from mysql.user;

查看系统服务 services.msc
启动系统服务 net start mysql
先关闭服务端 net stop mysql	
移除系统服务 mysqld --remove

先找到默认编码配置文件my-default.ini
拷贝改文件并把文件名改为my.ini
清楚原有的内容再把以下代码CV即可
操作完之后需要重启服务端

[mysqld]
	character-set-server=utf8mb4
	collation-server=utf8mb4_general_ci
[client]
	default-character-set=utf8mb4
[mysql]
	default-character-set=utf8mb4

show engines;

show variables like '%mode%';

set session sql_mode='strict_trans_tables';在当前客户端有效
set global sql_mode='strict_trans_tables';在当前服务端有效

create table 表名(
	字段名 字段类型(数字) 约束条件,
	字段名 字段类型(数字) 约束条件,
 	字段名 字段类型(数字) 约束条件
);

tinyint		   1bytes		   正负号(占1bit)
smallint	   2bytes		   正负号(占1bit)
int		       4bytes		   正负号(占1bit)
bigint		   8bytes		   正负号(占1bit)

float(20,10)总共存储20位数 小数点后面占10
double(20,10)总共存储20位数 小数点后面占10
decimal(20,10)总共存储20位数 小数点后面占10

float	<	double  < 	decimal

char(4)  最多存储四个字符 超出就报错 不够四个空格填充至四个
varchar(4) 最多存储四个字符 超出就报错 不够则有几位存几位

set global sql_mode='strict_trans_tables,pad_char_to_full_length';

枚举
	多选一
	create table t15(
    	id int,
      	name varchar(32),
       gender enum('male','female','others')
    );
 	insert into t15 values(1,'tony','猛男');
  	insert into t15 values(2,'jason','male');
 	insert into t15 values(3,'kevin','others');

集合
	多选多(多选一)
	create table t16(
    	id int,
      	name varchar(16),
       hobbies set('basketabll','football','doublecolorball')
    );
 	insert into t16 values(1,'jason','study');
 	insert into t16 values(2,'tony','doublecolorball');
	insert into t16 values(3,'kevin','doublecolorball,football');

datetime		年月日时分秒
date			年月日
time			时分秒
year			年

create table t17(
	id int,
  	name varchar(32),
 	register_time datetime,
 	birthday date,
 	study_time time,
 	work_time year
);
insert into t17 values(1,'jason','2000-11-11 11:11:11','1998-01-21','11:11:11','2000');

id int unsigned

id int(5) zerofill 

所有字段类型不加约束条件的情况下默认都是可以为空
create table table1(
	id int,
 	name varchar(16) not null
);

create table table2(
	id int default 666,
	name varchar(16) default '匿名'
);

create table table3(
	id int unique,
	name varchar(32) unique
);

create table table4(
	id int primary key
    name varchar(32)
);

主键相当于非空且唯一not null and unique
如果没有明确主键那么第一个字段默认升级为主键
创表时应该有一个字段用来表示数据的唯一性通常用id字段
自增单独是无效、只能与主键配合着使用且只能出现一次

# 一对一
create table user(
	id int primary key auto_increment,
  	name varchar(32),
 	detail_id int unique,
  	foreign key(detail_id) references userdetail(id)
  	on update cascade
  	on delete cascade
);

create table userdetail(
	id int primary key auto_increment,
  	phone bigint
);

# 一对多
create table emp(
	id int primary key auto_increment,
 	name varchar(32),
 	age int,
 	dep_id int,
	foreign key(dep_id) references dep(id)
);

create table dep(
	id int primary key auto_increment,
 	dep_name varchar(32),
  	dep_desc varchar(64)
);


create table emp1(
	 id int primary key auto_increment,
 	 name varchar(32),
 	 age int,
 	 dep_id int,
	 foreign key(dep_id) references dep1(id) 
    on update cascade 
    on delete cascade
);

create table dep1(
	id int primary key auto_increment,
 	dep_name varchar(32),
  	dep_desc varchar(64)
);

# 多对多
create table book(
	id int primary key auto_increment,
 	title varchar(32),
  	price float(5,2)
);
create table author(
	id int primary key auto_increment,
 	name varchar(32),
  	phone bigint
);
create table book2author(
	id int primary key auto_increment,
   author_id int,
 	foreign key(author_id) references author(id)
  	on update cascade
  	on delete cascade,
   book_id int,
  	foreign key(book_id) references book(id)
  	on update cascade
  	on delete cascade
);

create table emp(
  id int primary key auto_increment,
  name varchar(20) not null,
  gender enum('male','female') not null default 'male', #大部分是男的
  age int(3) unsigned not null default 28,
  hire_date date not null,
  post varchar(50),
  post_comment varchar(100),
  salary double(15,2),
  office int, #一个部门一个屋子
  depart_id int
);

#插入记录
#三个部门：教学，销售，运营
insert into emp(name,gender,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','浦东第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);

select 后面要跟指定的字段名信息写*号默认差所有的字段信息
from 后面要跟指定的表名
select * from userinfo;  # 从userinfo表里面查询所有的字段
select id, name, from userinfo;  # 从userinfo表里面查询id，name字段
编写的时候先写select再写from，代码执行的时候恰好相反

# 1.查询id大于等于3小于等于6的数据
select * from emp where id >= 3 and id <= 6; 
select * from emp where id between 3 and 6;

# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary=20000 or salary=18000 or salary=17000;
select * from emp where salary in (20000,18000,17000); 

# 3.查询id小于3大于6的数据
select * from emp where id<3 or id>6;
select * from emp where id not between 3 and 6;

# 4.查询员工姓名中包含字母o的员工姓名与薪资(模糊查询用like)
select * from emp where name like '%o%';

# 5.查询员工姓名是由四个字符组成的员工姓名与其薪资
select * from emp where name like '____';
select * from emp where char_length(name) = 4;

# 6.查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is
select * from emp where post_comment is NULL;

聚合函数 专门用于分组之后的数据统计
 max\min\sum\avg\count 最大值、最小值、求和、平均值、计数
      
# 1.将员工数据按照部门分组
select * from emp group by post;
# 2.获取每个部门的最高工资 
select post,max(salary) from emp group by post;
select post as '部门',max(salary) as '最高薪资' from emp group by post;
# 3.一次获取部门薪资相关统计
select post,max(salary) '最高薪',min(salary) '最低薪',avg(salary) '平均薪资',sum(salary) '月支出' from emp group by post;
# 4.统计每个部门的人数
select post,count(id) from emp group by post;
#5.统计每个部门的部门名称以及部门下的员工姓名
'''分组以外的字段无法直接填写 需要借助于方法'''
select post,name from emp group by post;
select post,group_concat(name) from emp group by post;
select post,group_concat(name,age) from emp group by post;
select post,group_concat(name,'|',age) from emp group by post;
select post,group_concat(name,'_NB') from emp group by post;
select post,group_concat('DSB_',name,'_NB') from emp group by post

having与where本质是一样的 都是用来对数据做筛选只不过where用在分组之前(首次筛选)having用在分组之后(二次筛选)
  
# 1.统计各部门年龄在30岁以上的员工平均工资 并且保留大于10000的数据
步骤1:先筛选出所有年龄大于30岁的员工数据
select * from emp where age > 30;
步骤2:再对筛选出来的数据按照部门分组并统计平均薪资
select post,avg(salary) from emp where age > 30 group by post;
步骤3:针对分组统计之后的结果做二次筛选
select post,avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;

数据必须一模一样才可以去重
select distinct id,age from emp; 关键字针对的是多个字段组合的结果
select distinct age from emp; 
select distinct age,post from emp;

select * from emp order by age;  默认升序 asc升序 desc降序
select * from emp order by age,salary desc; 先按照年龄升序排 相同的情况下再按照薪资降序排

select * from emp limit 5;  直接限制展示的条数
select * from emp limit 5,5;  从第5条开始往后读取5条

SQL语句的模糊匹配如果用不习惯 也可以自己写正则批量查询
select * from emp where name regexp '^j.*?(n|y)$';

create table dep(
  id int primary key auto_increment,
  name varchar(20) 
);

create table emp(
  id int primary key auto_increment,
  name varchar(20),
  sex enum('male','female') not null default 'male',
  age int,
  dep_id int
);

#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'财务');

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

select * from emp inner join dep on emp.dep_id=dep.id;内连接
该方法只连接两张表共有的部分，此外还有left、right、union join

将一条SQL语句用括号括起来当成另外一条SQL语句的查询条件
这个只能用例子来展示

题目:求姓名是jason的员工部门名称
子查询类似于我们日常生活中解决问题的方式>>>:分步操作
步骤1:先根据jason获取部门编号
select dep_id from emp where name='jason';
步骤2:再根据部门编号获取部门名称
select name from dep where id=200;
总结select name from dep where id=(select dep_id from emp where name='jason');

查询所有的课程的名称以及对应的任课老师姓名
# 1.先确定需要用到几张表  课程表 分数表
# 2.预览表中的数据 做到心中有数
select * from course;
select * from teacher;
# 3.确定多表查询的思路 连表 子查询 混合操作
 SELECT
 	teacher.tname,
 	course.cname
 FROM
 	course
INNER JOIN teacher ON course.teacher_id = teacher.tid;


查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先确定需要用到几张表 学生表 分数表
# 2.预览表中的数据
select * from student;
select * from score;
# 3.根据已知条件80分 选择切入点 分数表
# 求每个学生的平均成绩 按照student_id分组 然后avg求num即可
select student_id,avg(num) as avg_num from score group by student_id having avg_num>80;
# 4.确定最终的结果需要几张表 需要两张表 采用连表更加合适
 SELECT
 	student.sname,
 	t1.avg_num
 FROM
 	student
 INNER JOIN (
 	SELECT
 		student_id,
 		avg(num) AS avg_num
 	FROM
 		score
 	GROUP BY
 		student_id
 	HAVING
 		avg_num > 80
 ) AS t1 ON student.sid = t1.student_id;


查询没有报李平老师课的学生姓名
# 1.先确定需要用到几张表
老师表 课程表 分数表 学生表
# 2.预览每张表的数据
# 3.确定思路 思路1:正向筛选 思路2:筛选所有报了李平老师课程的学生id 然后取反即可
# 步骤1 先获取李平老师教授的课程id
select tid from teacher where tname = '李平老师';
select cid from course where teacher_id = (select tid from teacher where tname = '李平老师');
# 步骤2 根据课程id筛选出所有报了李平老师的学生id
select distinct student_id from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname = '李平老师'))
# 步骤3 根据学生id去学生表中取反获取学生姓名
 SELECT
 	sname
 FROM
 	student
 WHERE
 	sid NOT IN (
 		SELECT DISTINCT
 			student_id
 		FROM
 			score
 		WHERE
 			course_id IN (
 				SELECT
 					cid
 				FROM
 					course
 				WHERE
 					teacher_id = (
 						SELECT
 							tid
 						FROM
 							teacher
 						WHERE
 							tname = '李平老师'
 					)
 			)
 	)


查询没有同时选修物理课程和体育课程的学生姓名(报了两门或者一门不报的都不算)
# 1.先确定需要的表
学生表 分数表 课程表
# 2.预览表数据
# 3.根据给出的条件确定起手的表
# 4.根据物理和体育筛选课程id
select cid from course where cname in ('物理','体育');
# 5.根据课程id筛选出所有跟物理 体育相关的学生id
select * from score where course_id in (select cid from course where cname in ('物理','体育'))
# 6.统计每个学生报了的课程数 筛选出等于1的
select student_id from score where course_id in (select cid from course where cname in ('物理','体育'))
group by student_id
having count(course_id) = 1;
# 7.子查询获取学生姓名即可
 SELECT
 	sname
 FROM
 	student
 WHERE
 	sid IN (
 		SELECT
 			student_id
 		FROM
 			score
 		WHERE
 			course_id IN (
 				SELECT
 					cid 				FROM
 					course
 				WHERE
 					cname IN ('物理', '体育')
 			)
 		GROUP BY
 			student_id
 		HAVING
 			count(course_id) = 1
 	) 


查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先确定涉及到的表
分数表 学生表 班级表
# 2.预览表数据
select * from class
# 3.根据条件确定以分数表作为起手条件
# 步骤1 先筛选掉大于60的数据
select * from score where num < 60;
# 步骤2 统计每个学生挂科的次数
select student_id,count(course_id) from score where num < 60 group by student_id;
# 步骤3 筛选次数大于等于2的数据
select student_id from score where num < 60 group by student_id having count(course_id) >= 2;
# 步骤4 连接班级表与学生表 然后基于学生id筛选即可
SELECT
	student.sname,
	class.caption
FROM
	student
INNER JOIN class ON student.class_id = class.cid
WHERE
	student.sid IN (
		SELECT
			student_id
		FROM
			score
		WHERE
			num < 60
		GROUP BY
			student_id
		HAVING
			count(course_id) >= 2
	);

需要下载pymysql第三方模块模块 pip3 install pymysql
import pymysql


# 1.连接MySQL服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    db='db4_03',
    charset='utf8mb4'
)
# 2.产生游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # [{},{}]
# 3.编写SQL语句
sql = 'select * from score;'
# 4.发送SQL语句
affect_rows = cursor.execute(sql)  # execute也有返回值 接收的是SQL语句影响的行数
print(affect_rows)
# 5.获取SQL语句执行之后的结果
res = cursor.fetchall()
print(res)



1.获取数据
fetchall()  获取所有的结果
fetchone()	获取结果集的第一个数据
fetchmany() 获取指定数量的结果集
 
cursor.scroll(1,'relative')  # 基于当前位置往后移动
cursor.scroll(0,'absolute')  # 基于数据的开头往后移动
 
2.增删改查
autocommit=True  # 针对增 删 改 自动确认(直接配置)
conn.commit()  # 针对 增 删 改 需要二次确认(代码确认)

事务的四大特性(ACID)
A:原子性 事务中的各项操作是不可分割的整体 要么同时成功要么同时失败
C:一致性 使数据库从一个一致性状态变到另一个一致性状态
I:隔离性 多个事务之间彼此不干扰
D:持久性 也称永久性，指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的
      
create table user(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)
values
('jason',1000),
('kevin',1000),
('tank',1000);

# 修改数据之前先开启事务操作
start transaction;

# 修改操作
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='kevin'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元

# 回滚到上一个状态
rollback;

# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;