Mysql常用操作
目录
基础操作
Mysql 登录 mysql -h 1270.0.0.1 -P 3306 -uroot -p
登录本地：mysql -uroot -p
退出：quit;exit;\q;
修改密码
mysqladmin -uroot -p password "123"
# 第一次修改完密码以后，之后还修改密码需要
mysqladmin -uroot -p123 password "123456"
# 为了后续调试方便，这里把密码设为空值
mysqladmin -uroot -p123 password "新密码"
破解管理员密码
1.先关闭mysqld服务端
2.以跳过授权表的方式在命令行中启动mysqld服务端
mysqld --skip-grant-tables
3.客户端直接以无密码的方式登陆root用户修改密码
mysql -uroot -p
4.修改管理员密码
update mysql.user set password=password("123") where user="root" and host="localhost";
flush privileges; # 刷新权限
5.命令行杀死mysqld服务
tasklist |findstr mysqld
taskkill \F \PID 'PID'
6.服务管理正常启动mysqld
针对文件夹的(库)
增：
create database name charset=utf8;
创建 数据库
删：
drop databases name；
删除 数据库(总不能全删所以不是复数)
改：
alter database name charset=utf8;
修改 指定数据库 设置为~~~
查：
show databases;
查看 数据库(数据库复数所以加s)
show create database name;
查看 创建 数据库 name(查看创建表详细信息)
针对文件的(表)
# 文件首先需要在文件夹下面，所以在操作文件之前，应该先确定操作的是哪个文件夹下面的文件
use database_name;
切换 数据库
select database();
选择 数据库 (查看当前所在的数据库)
增：
create table name(
id int(11),
name char(10),
);
创建 表 表名
字段 类型
删：
drop table name;
删除 表 表名
改：
alter table name modify 字段名 类型；
改变 表 修改
查：
show tables;
显示 所有表
show create table name;
显示 创建 表 名字(创建表的时候的详细信息)
describe 表名 = desc 表名；
描述 表名(查看表结构)
show columns from 表名
要查看的表; # 查询指定表的结构
针对(记录,表内数据的操作)
增：
insert 表名 values (1，'xxx');
插入 表名 值 (需要插入的数据)
insert 表(列名) values(值)；
插入 表名 值
删:
delete from 表名 where 条件;
删除 来自 表名 条件 (删除表内指定条件的数据)
delete from 表名；
删除 来自 表(删除表内所有的数据)
改：
update 表名 set 列名=值;
更新 表名 设置 (改变表内所有的数据)
update 表名 set 列名=值 where 条件
更新 表名 设置值 条件(改变表内指定条件的数据)
修改所有为空的
update 表名 set 列名=值 where 表名 is null;
更新 表名 设置 列名=值 条件
不为空
update 表名 set 列名=值 where 表名 not is null;
查：
select * from 表名;
查找 来自 表(查找来自表内所有的数据)
select id,name from 表名
创建表的完整语法
#语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
#注意：
1. 在同一张表中，字段名不能相同
2. 宽度和约束条件可选，字段名和类型是必须的
3. 最后一个字段后不能加逗号！
# 补充：
# 1.宽度指的是对存储数据的限制
create table userinfo(name char);
insert into userinfo values('jason');
"""
1.没有安全模式的数据库版本，能够存放数据但是只会存进去一个j
2.最新数据库版本直接报错提示无法存储:Data too long for column 'name' at row 1
"""
约束条件
# 总结 类型与约束条件区别
# 类型：限制字段必须以什么样的数据类型存储
# 约束条件：约束条件是在类型之外添加一种额外的限制
primary key (pk) 标识该字段为该表的主键，可以唯一的标识记录
foreign key (FK) 标识该字段为该表的外键
unique key(uk) 标识该字段的值是唯一的
auto_increment 标识该字段的值自动增长（整数类型，而且为主键）
default 标识该字段设置默认值
not null 标识该字段不能为空
unsigned 无符号
zerofill 使用0填充
null 与 not null
# 1. null:可以空
# 2. not null:不可以为空
# 创建
create table t1(id int,name char not null); # name字段不可以为空
# 增
insert into t1 values(1,'j'); # 正常存储
insert into t1 values(2,null); # 报错，设置为不可以为空，不传入数据会报错
枚举：enum
# 注：选择枚举时候要加引号
# 创建
create table user(
id int,
name char(16),
gender enum('male','female','other')# 多选择一
);
# 插入
insert into user values(1,'lmd','xxxx'); # 报错
insert into user values(2,'lmd','male'); # 正确
集合多选多：set
# 注：选择集合时候要加引号
# 创建：
create table user(
id int,
name char(16),
gender enum('male','female','other'),# 多选择一
hobby set('read','sleep','dbj')# 多选多
);
# 插入
insert into user values(1,'lmd','male','read,sleep'); # 集合也可以选择一个
not null+default：不能为空+默认值
# 创建表 无默认值
create table stu1(
id int,
name char(16)
);
# 添加数据
insert into stu1 values (1,null); # 成功，可以插入空
# 修改name字段
alter table stu1 modify name char(16) not null; # 修改name字段不可以为空,在表内有空的数据修改失败
# 添加数据
insert into stu1 values(1,null); # 报错，应为name字段不可以为空
# 创建表设置不可以为空与默认值
create table stu1(
id int,
name char(16) not null,# 不可以为空
hobby enum('read','sleep') default 'read' # 设置默认值为 xx
);
# 添加数据
insert into stu1(id,name) values(1,'lmd'); # 正确，有默认值的可以不传,但是必须指定列名插入
unique：设置唯一|联合唯一
# 唯一：只能有一个
# 联合唯一：俩个以上字段，可以重复，但是不能组合重复
# 创建表
create table stu1(
id int unique,# 设置唯一
name char(16) not null # 设置不可以为空
);
insert into stu1 values(1,'lmd'),(1,'lmd'); # 报错，因为id设置了唯一不可以重复！
insert into stu1 values(1,'lmd'),(2,'zpp'); # 成功
# 创建表
create table stu1(
id int,
name char(10) not null,
age int not null,
unique(name,age) # name age 联合唯一
);
# 添加数据
insert into stu1 values (1,'lmd',18); # 正确
insert into stu1 values (1,'lmd',19); # 正确
insert into stu1 values (1,'lmd',18); # 错误，应为存在 相同组合
primary key+auto_increment|主键+自增
# 一张表中必须有且只有一个主键，如果你没有设置主键
# 那么会从上到下搜索直到遇到一个非空且唯一的字段自动将其设置为主键
# 如果表里面没有指定任何的可以设置为主键的字段，那么innodb会采用自己默认的一个隐藏字段作为主键，隐藏意味着你在查询的时候无法根据这个主键字段加速查询了
# 索引：类似于书的目录，没有主键就相当于一页一页翻着查
# 一张表中通常都应该有一个id字段，并且通常将改id字段作成主键
# primary key: 主键 唯一且不可以为空，innodb引擎的搜索依据，提升查询效率
# auto_increment:自增 每次自动加
# 创建表
create table stu1(
id int primary key
);
# 添加数据
insert into stu1 values(1),(1); # 报错！ 应为主键值是唯一
insert into stu1 values(1),(2); # 成功！
# 联合主键 多个字段联合起来作为表的一个主键，本质还是一个主键
create table stu1(
id int,
name char(16),
primary key(id,name)
);
# 自增，主键id作为数据的编号，每次最好能自动递增
注意：auto_increment通常都是加在主键上，并且只能给设置为key的字段加
create table stu2(
id int primary key auto_increment,
name char(16)
);
# 添加数据
insert into stu2(name) values('lmd'),('zpp');
如果要清空表，使用truncate tb1；
作用：将整张表重置,id重新从0开始记录
一对多(Foreign Key)
# 1、在创建表时，先建被关联的表dep，才能建关联表emp
# 创建 表1 dep表
create table dep(
id int primary key auto_increment,
dep_name char(10),# 名字
dep_comment char(40) # 介绍
);
# 创建 表2 emp表
create table emp(
id int primary key auto_increment,
name char(16),
gender enum('male','female') not null default 'male',
dep_id int,
foreign key(dep_id) references dep(id) # 本身字典dep_id关联dep的id
);
# 2、在插入记录时，必须先插被关联的表dep，才能插关联表emp
# dep添加数据
insert into dep(dep_name,dep_comment)values
('教学部','辅导学生学习，教授python课程'),
('外交部','老男孩上海校区驻张江形象大使'),
('技术部','技术能力有限部门');
# 给emp添加数据
insert into emp(name,dep_id) values
('alex',1),
('egon',2),
('lxx',1),
('wxx',1),
('wenzhou',3);
# 当我想修改emp里的dep_id或dep里面的id时返现都无法成功
# 当我想删除dep表的教学部的时候，也无法删除
# 方式1:先删除教学部对应的所有的员工，再删除教学部
# 方式2:受限于外键约束，导致操作数据变得非常复杂，能否有一张简单的方式，让我不需要考虑在操作目标表的时候还需要考虑关联表的情况，比如我删除部门，那么这个部门对应的员工就应该跟着立即清空
# 更新于删除都需要考虑到关联与被关联的关系>>>同步更新与同步删除
create table dep(
id int primary key auto_increment,
dep_name char(10),
dep_comment char(50)
);
create table emp(
id int primary key auto_increment,
name char(16),
gender enum('male','female') not null default 'male', # 设置唯一 默认为男
dep_id int,
foreign key(dep_id) references dep(id) # 绑定
on update cascade # 同步更新
on delete cascade # 同步删除
);
# 插入数据
# dep添加数据
insert into dep(dep_name,dep_comment)values
('教学部','辅导学生学习，教授python课程'),
('外交部','老男孩上海校区驻张江形象大使'),
('技术部','技术能力有限部门');
# 给emp添加数据
insert into emp(name,dep_id) values
('alex',1),
('egon',2),
('lxx',1),
('wxx',1),
('wenzhou',3);
# 删除部门后，对应的部门里面的员工表数据对应删除
# 更新部门后，对应员工表中的标示部门的字段同步更新
多对多：
# 图书表与作者表之间的关系
"""
仍然站在两张表的角度：
1.站在图书表：一本书可不可以有多个作者，可以！那就是书多对一作者
2.站在作者表：一个作者可不可以写多本书，可以！那就是作者多对一书
双方都能一条数据对应对方多条记录，这种关系就是多对多！
"""
# 先来想如何创建表？图书表需要有一个外键关联作者，作者也需要有一个外键字段关联图书。问题来了，先创建谁都不合适！如何解决？
# 建立第三张表，该表中有一个字段fk左表的id，还有一个字段是fk右表的id
# 创建表author作者；
create table author(
id int primary key auto_increment, # id
name char(10) # 作者名字
);
# 创建book 图书
create table book(
id int primary key auto_increment,
bname char(16), # 书名
price int # 价格
);
# 添加作者数据
insert into author(name) values('egon'),('qqqq'),('qqqwww');
# 添加图书数据
insert into book(bname,price) values
('python从入门到入土',200),
('葵花宝典切割到精通',800),
('九阴真经',500),
('九阳神功',100);
# 创建组合表
create table author2book(
id int primary key auto_increment,
author_id int, # 作者id
book_id int, # 图书id
foreign key(author_id) references author(id) # 绑定
on update cascade
on delete cascade,
foreign key(book_id) references book(id) # 绑定
on update cascade
on delete cascade
);
# 给组合表插入数据
insert into author2book(author_id,book_id) values
(1,3),
(1,4),
(2,2),
(2,4),
(3,4),
(3,3);
一对一
客户表和学生表(老男孩的客户与学生之间,报名之前都是客户，只有报了名的才能是学生)
# 左表的一条记录唯一对应右表的一条记录，反之也一样
create table customer( # 客户
id int primary key auto_increment, #主键自增
name char(20) not null,
qq char(10) not null,
phone char(16) not null
);
create table student( # 学生
id int primary key auto_increment, # 主键自增
class_name char(20) not null,
customer_id int unique, # 字段唯一
foreign key(customer_id) references customer(id) # 外键的字段一定唯一
on delete cascade
on update cascade
);
修改表：
# mysql对大小写不敏感！！！
语法：
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
3. 删除字段
ALTER TABLE 表名
DROP 字段名;
4. 修改字段 # modify只能改字段数据类型完整约束，不能改字段名，但是change可以！
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
复制表:
# 查询语句执行的结果也是一张表，可以看成虚拟表
# 复制表结构＋记录 （key不会复制: 主键、外键和索引）
create table new_service select * from service;
# 只复制表结构
select * from service where 1=2; //条件为假，查不到任何记录
create table new1_service select * from service where 1=2;
create table t4 like employees;
语法执行顺序
select id,name from emp where id>3 and id <=6;
#先后顺序
from
where
group by
select
Wheret条件约束
"""
先是查哪张表 from emp
再是根据什么条件去查 where name like ‘%o%’
再是对查询出来的数据筛选展示部分 select name,salary
"""
案例一:
# 查询id大于等于3小于等于6的数据
# 第一步先找表
select id from emp;
# 第二步在想着加条件
select id from emp where id=>3 and id<=6
案例二：
# 查询薪资是20000或者18000或者17000的数据
select * from emp where salary=20000 or salary=18000 or salary=17000;
select * from emp where salary in (20000,18000,16000);
案例三:
# 查询员工姓名中包含o字母的员工姓名和薪资
select name,salary from emp where name like'%o%';
案例四：
# 4.查询员工姓名是由四个字符组成的员工姓名与其薪资
select name,salary from where name like '____';
select name,salary from where char_lenght(name)=4;
案例五：
# 5.查询id小于3或者大于6的数据
select * from emp where id not between 3 and 6;
案例六：
# 6.查询薪资不在20000，18000，17000范围的数据
select * from emp where salary not in(20000,18000,17000);
案例七：
# 7.查询岗位描述为空的员工名与岗位名 针对null不能用等号，只能用is
select name,post from emp where post_comment is NULL;
group by:分组及聚合函数
# 数据分组应用场景：每个部门的平均薪资，男女比例等
案例一：
# 1.按部门分组
select post from emp group by post;
# 强调:只要分组了，就不能够再“直接”查找到单个数据信息了，只能获取到组名
案例二：
# 2.获取每个部门的最高工资
# 以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)
# max 查询最大值
# 每个部门的最高工资
select post,max(salary) from emp group by post;
# min 查询最小值
select post,min(salary) from emp group by post;
# avg平均值
select post,avg(salary) from emp group by post;
# sum总和
select post,sum(salary) from emp group by post;
# count计数
select post,count(id) from emp group by post;
案例三：
# 查询分组之后的部门名称和每个部门下所有的学生姓名
group_concat（分组之后用）不仅可以用来显示除分组外字段还有拼接字符串的作用
1.select post,group_concat(name) from emp group by post;
2.select post,group_concat(name,'_sb') from emp group by post;
3.select post,group_concat(name,'|',salary) from emp group by post;
案例四：as
concat（不分组时用）拼接字符串达到更好的显示效果 as语法使用
select name as 姓名,salary as 薪资 from emp;
select concat("NAME: ",name) as 姓名,concat("SAL: ",salary) as 薪资 from emp;
# as语法 即可以给字段起别名也可以给表起
select emp.id,emp.name from emp as t1; # 报错 因为表名已经被你改成了t1
select t1.id,t1.name from emp as t1;
# 查询四则运算
# 查询每个人的年薪
select name,salary*12 as annual_salary from emp;
select name,salary*12 annual_salary from emp; # as可以省略
多表查询
# 当初为什么我们要分表，就是为了方便管理，在硬盘上确实是多张表
# 但是到了内存中我们应该把他们再拼成一张表进行查询才合理
#建表
create table dep(
id int,
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,'运营');
insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',38,201),
('nick','female',28,202),
('owen','male',18,200),
('jerry','female',18,204)
;
⭐️表查询
select * from emp,dep; # 左表一条记录与右表所有记录都对应一遍>>>笛卡尔积
# 将所有的数据都对应了一遍，虽然不合理但是其中有合理的数据，现在我们需要做的就是找出合理的数据
案例1：
# 和表之后只能通过表.查询字段
# 查询员工及所在部门的信息
select * from emp,dep where emp.dep_id = dep.id;
# 查询部门为技术部的员工及部门信息
select * from emp,dep where emp.dep_id = dep.id and dep.name = '技术';
# 将两张表关联到一起的操作，有专门对应的方法
# 1、内连接：只取两张表有对应关系的记录
select * from emp inner join dep on emp.dep_id = dep.id;
select * from emp inner join dep on emp.dep_id = dep.id where dep.name = '技术';
# 2、左连接: 在内连接的基础上保留左表没有对应关系的记录尽可能的满足左边的需求
select * from emp left join dep on emp.dep_id = dep.id;
# 3、右连接: 在内连接的基础上保留右表没有对应关系的记录
select * from emp right join dep on emp.dep_id = dep.id;
# 4、全连接：在内连接的基础上保留左、右面表没有对应关系的的记录
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;
子查询
# 就是将一个查询语句的结果用括号括起来当作另外一个查询语句的条件去用
# 1.查询部门是技术或者人力资源的员工信息
"""
先获取技术部和人力资源部的id号，再去员工表里面根据前面的id筛选出符合要求的员工信息
"""
select * from emp where dep_id in (select id from dep where name = "技术" or name = "人力资源");
# 2.每个部门最新入职的员工 思路：先查每个部门最新入职的员工，再按部门对应上联表查询
select t1.id,t1.name,t1.hire_date,t1.post,t2.* from emp as t1
inner join
(select post,max(hire_date) as max_date from emp group by post) as t2
on t1.post = t2.post
where t1.hire_date = t2.max_date
;
"""
记住一个规律，表的查询结果可以作为其他表的查询条件，也可以通过其别名的方式把它作为一张虚拟表去跟其他表做关联查询
"""
select * from emp inner join dep on emp.dep_id = dep.id;
宁可清贫自乐，不可浊富多忧
浙公网安备 33010602011771号