MYSQL:总结

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MYSQL:总结

主要就是连接方式;

SELECT *
FROM tb_students
where id=2 and id =3
group by id
HAVING id=1
order by sex desc ,age asc
limit 0,2
 
inner join
left join
right join
左 union 右
mysql命令:


登陆mysql
mysql -uroot -p123456

查看当前用户:
select user() ;

退出
exit

设置密码
mysqladmin -u -p旧密码 password“123456”

有验证地 开启/关闭服务
net start/stop MySQL


如何跳过验证,开启服务
mysqld --skip-grant-tables

然后登陆root账户,
mysql -uroot -p

然后更新密码:
update mysql.user set password=password("123456") where user="root" and host="localhost":
flush.privileges:
exit


查看编码格式
\s


统一编码
目录下创建my.ini文件
#1. 修改配置文件
[mysqld]
default-character-set=utf8
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
#mysql5.5以上:修改方式有所改动
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
#2. 重启服务
#3. 查看修改结果:
\s
show variables like '%char%'


SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型:
1、DDL语句    数据库定义语言: 数据库、表、视图、索引、存储过程,例如CREATE DROP ALTER
2、DML语句    数据库操纵语言: 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
3、DCL语句    数据库控制语言: 例如控制用户的访问权限GRANT、REVOKE

SQL语句:

#1. 操作文件夹
        增:create database db1 charset utf8;
        查:show databases;
            show create database db1
            select database()
        改:alter database db1 charset latin1;
        删除: drop database db1;
#2. 操作文件
    先切换到文件夹下:use db1
        增:create table t1(id int,name char);
        查:show tables
            desc t1
            show create table t1
        改:alter table t1 modify name char(3);
              alter table t1 change name name1 char(2);
        删:drop table t1;
#3. 操作文件中的内容/记录
                加不加into都一样
        增:insert into t1 values(1,'egon1'),(2,'egon2'),(3,'egon3');
        查:select * from t1;
        改:update t1 set name='sb' where id=2;
        删:delete from t1 where id=1;










什么是存储引擎

存储引擎就是表的类型
show ENGINES
常用的就是 InnoDB
不指定默认就是innodb
create table wusen(name char(8))engine=innodb;


复制表:就是把查询结果赋值给他
create table t1 select * from t2 where 1=2

unsigned无正负号的意思

int()参数是显示宽度,而不是存储宽度,不要去设置这参数,会显得你很low
create table t3 (id int(5) unsigned zerofill)
insert into t3 values(11),(22),(33),(22222)
select * from t3

小数类型
m=最大宽度  d是小数的最大宽度
float(m,d)
double(m,d)
decimal(m,d) 基于字符串,精度最高


日期类型
create table t4(
id int,
name char(8),
a year,
b date,
c time,
d datetime
)
insert into t4 values(1,"w",now(),now(),now(),now())
select * from t4
2020
2020-08-06
18:04:56
2020-08-06 18:04:56


字符串
char
varchar
默认会把数据后面的空格去掉
length:查看字节数
char_length:查看字符数

集合与枚举
create table t5(
id int,
name char(8),
sex enum("男","女"),
hobby set("打篮球","吹牛逼","喝汽水")
)

insert into t5 values(1,"吴森","男","吹牛逼,喝汽水")

select * from t5

约束性条件
null 和 default
create table t6(
id int unsigned zerofill,
name char(8),
sex enum("男","女") not null default "男"
)

insert into t6(id,name)values(1,"张三")

select * from t6


unique key:
create table t7(
id int unique
unique(name char(8)) # 两种方式

)
insert into t7 values(1)
select * from t7


联合唯一
create table t8(
    ip char(15),
    port int,
    unique(ip,port)
)

主键
primary key
不为空 且 唯一
单列主键
create table t8(
    id int primary key
)
复合主键
create table t8(
    ip char(15),
    port int,
    primary key(ip,port)
)



自增
create table t9(
id int primary key auto_increment,
name char(8)
);
insert into t9(name) values("baba"),("heihei")
select * from t9


查看偏移量和步数
show variables like "auto_inc%"
auto_increment_increment=1
auto_increment_offset=1

show variables like "auto_inc%";
set session auto_increment_increment=1;
set session auto_increment_offset=1;
set global auto_increment_offset=1;

show variables like "auto_inc%";

起始偏移量《=步长

清空表的数据
delete from t9
清空表这样玩,不要用delete
truncate t9
删除表
drop table t9


外键:
通过谁,去找谁, == x下的外键去找y
create table teacher(
id int primary key,
name char(8)

);
create table student(
id int primary key,
name char(8),
t_id int,
foreign key(t_id) references teacher(id) on delete cascade on update cascade
);

insert into teacher values(1,"王老师"),(2,"李老师"),(3,"刘老师");
insert into student values(1,"学生1",1),(2,"学生1",3),(3,"学生1",1),(4,"学生1",2),(5,"学生1",1),(6,"学生1",2)


select student.name,teacher.name from student,teacher where student.t_id = teacher.id

select x.name as a,y.name as b from student as x,teacher as y where x.t_id = y.id



做程序的时候,最好不要用外键,用再逻辑上去搞

三种逻辑关系:
思考:竟然没有一对多
多对一
多对多
一对一


多对多:多弄出一张表出来就行,在新表里两边链接
create table student(
id int primary key auto_increment,
name char(8)
);
create table course(
id int primary key auto_increment,
name char(8)
);
create table score(
id int primary key auto_increment,
student_id int,
course_id int,
s int,
foreign key(student_id) references student(id),
foreign key(course_id) references course(id)
);

insert into student(name) values("张三"),("李四"),("王五");
insert into course(name) values("语文"),("数学"),("英语");
insert into score(student_id,course_id,s) values(1,1,1),
(1,1,10),
(1,2,22),
(1,3,33),
(2,1,43),
(2,2,454),
(2,3,32),
(3,1,13),
(3,2,68),
(3,3,54)


select student.name,course.name,score.s from course,student,score where student.id = score.student_id and course.id = score.course_id


破外键,垃圾外键,写了外键自己还得写where,我也是呵呵


一对一:把外键值设置成unique不就行啦
desc是降序,orderby 支持多个列,但是多个列降序,我这好像没实现
create table girl(
id int primary key auto_increment,
name char(8),
age int
);
create table boy(
id int primary key auto_increment,
name char(8),
age int,
girl_id int unique,
foreign key(girl_id) references girl(id)

);

insert into girl(name,age) values
("小美",18),
("小黑",38),
("小红",28),
("小丽",48),
("小小",68),
("铁锤",22);
insert into boy(name,age,girl_id) values
("黑老大",66,1),
("洪涝二",52,2),
("贴老三",72,3),
("朱老四",35,4),
("冯老五",28,5),
("刘老六",19,6);
select boy.name,boy.age,girl.name,girl.age from boy,girl
where boy.girl_id = girl.id ORDER BY boy.age desc;





不搞乱七八糟的了,
直接来个复杂的,自己看去吧。
"""
where
group by
having
order by 
asc/desc
limit 5
聚合函数:avg() sum() max() min() first() last() count()
"""
select * from boy,girl
where boy.girl_id = girl.id
group by boy.age
having avg(boy.age)>20
order by boy.age asc/desc
limit 2


效率:count(*)约等于 count(1) > count(id) > count(字段)


正则名字  :regexp

三种链接方式:
内连接:
select *
from
a inner join b on a.id = b.id

左连接:再内链接的基础上保留左边的数据库

select *
from
a left join b on a.id = b.id

有链接:

select *
from
a right join b on a.id = b.id

外链接:

select *
from
a left join b on a.id = b.id
union
select *
from
a right join b on a.id = b.id





三个表一起玩:
反正三个表都要连起来,怎么写都无所谓,哈哈哈
select *
from
score  inner join student on student.sid = score.student_id
inner join course on score.course_id = course.cid



权限管理
创建本地帐号:
create user "wusen"@"localhost" identified by "123456" #mysql -uwusen -p123456
远程账号
                    客户端IP
create user "wusen"@"192.168.31.36" identified by "123456" #mysql -uwusen -p123456 -h 服务端IP
create user "wusen"@"192.168.31.%" identified by "123456" #mysql -uwusen -p123456 -h 服务端IP
create user "wusen"@"%" identified by "123456" #mysql -uwusen -p123456 -h 服务端IP

授权:力度越来越小
user
db
tables_priv
columns_priv

grant all # all 除了授权意外都可以赋予
给所有库的查询权限给wusen这个账户
grant select on *.* to "wusen"@"localhost"

收回权限
revoke select on *.* from  "wusen"@"localhost"

grant select on db1.* to "wusen"@"localhost"

grant select on db1.t1 to "wusen"@"localhost"

grant select(name),update(name) on db1.t1 to "wusen"@"localhost"




创建账户,加授权
create user "wu"@"%" identified by "123456" #mysql -uwusen -p123456 -h 服务端IP
grant all  on *.* to 'wu'@'%'




视图:虚拟表,没有数据
但是不建议使用
create view select *....


触发器
CREATE TRIGGER tri_ before_ insert_ tb1 BEFORE INSERT ON tb1 FOR EACH ROW
begin
...
end

CREATE TRIGGER tri_ after_ insert_ tb1 AFTER INSERT ON tb1 FOR EACH ROW
begin
...
end

CREATE TRIGGER tri_ before_ delete_ _tb1 BEFORE DELETE ON tb1 FOR EACH ROW
begin
...
end

CREATE TRIGGER tri_ after_ delete_ tb1 AFTER DELETE ON tb1 FOR EACH ROW
begin
...
end

触发器
delimiter //
CREATE TRIGGER tri_ after_ delete_ tb1 AFTER DELETE ON tb1 FOR EACH ROW
begin
    if new.sucess = "no" then
        INSERT INTO errlog(err_ cmd, err_ time) VALUES(NEW. cmd, NEW.sub_ time) ; #必须加分号
    end if
end //
delimiter ;

算了触发器还是自己应用程序写


存储过程:
无参数
delimiter //
CREATE procedure p1()
begin
    select * from t1
end //
delimiter ;

call p1() #mysql执行存储过程

cursor.call_proc("p1")


有参数:
delimiter //
CREATE procedure p1(int a,int b ,out int res)
begin
    select * from t1;
    set res = 1;
end //
delimiter ;

set @x=0
call p1(1,2,@x) #mysql执行存储过程
select @x

cursor.call_proc("p1",(1,2,0)) #@__p1_0=1 @__p1_1=2 @__p1_2=0




应用程序与数据库结合使用
方式一:
python:调用存储过程
MySQL:编写存储过程

方式二:
python:编写sql
MySQL:什么都不用干

方式三:
python:ORM框架写sql
MySQL:啥也不干



事务:
start transaction;
sql1
sql2
sql3
commit;

try
    事务
execpt:
    rollback



索引:索引的数据结构就是b+树

View Code

1.第一个_和%

SELECT *
FROM tb_students
where name like '%老_'

_代表一个字符
%代表多个字符
这两个和like使用

View Code

2.=  like

= 这就i是完全等于

like 去配合通配符使用

View Code

3 in   not in 子查询 他不是数组是括号]


SELECT *
FROM tb_students
where id
IN
(
select id
from tb_students
where id=1
)
=======================
SELECT *
FROM tb_students
where id in (1,2,3)

View Code

4.distinct 去重

SELECT DISTINCT(age)
FROM tb_students

View Code

5.left(str,3)    函数获取字符的从左边数三个字符,还有right

 

SELECT left(name,1)
FROM tb_students


View Code

6.top  == limit


SELECT top 2 *
FROM tb_students


View Code

7.cast 转换数据类型

SELECT CAST(sex AS int)
FROM tb_students

View Code

8.替换函数replace

SELECT replace(name,"old","new")
FROM tb_students

View Code

9.字段拼接concat,         还可以自己加参数,岂不是和字符串格式化f"sssssssss{a}"相似

SELECT CONCAT(name,sex,"asdas")
FROM tb_students

View Code

10.if 函数

select t.name,if(t.weight<80,'正常','肥胖') 体重 from t_customer t

View Code

11.case when then else end    条件语句函数

select class.cid,class.caption,class_grade.gname,
(
        CASE 
        WHEN class_grade.gname="一年级" or class_grade.gname="二年级"   THEN
            '低年级'
        WHEN class_grade.gname="三年级" or class_grade.gname="四年级"  THEN
            '中年级'
        ELSE
            '高年级'
        END
    ) 年纪级别

from class,class_grade
where class.grade_id = class_grade.gid

View Code

12.起别名  as 或者空格

SELECT name 姓名
FROM tb_students
=================
SELECT name as 姓名
FROM tb_students

View Code

13.内外左右连接

select *
from tb_students LEFT JOIN auth_user on tb_students.id = auth_user.id

=============================================================================
select *
from tb_students as t1 LEFT JOIN auth_user as t2 on t1.id = t2.id



left join  ....  on  
right join ....  on 
inner join ....  on


左  union  右

View Code

14.count  sum  函数

select COUNT(id),sum(id)
from tb_students
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
存储 关系型数据库 MySQL
Mysql全面总结
本文全面总结了MySQL的相关知识,涵盖思维导图、架构、存储引擎、数据类型、索引、查询、事务、锁机制、调优、分区与分表分库、主从复制及其他问题。MySQL采用插件式存储引擎架构,支持多种存储引擎,如InnoDB和MyISAM,每种引擎具备不同的特性。文章详细介绍了InnoDB和MyISAM的对比,包括事务支持、行级锁定、索引类型等。此外,还探讨了MySQL的查询优化、性能调优、主从复制等内容,适合数据库开发者和运维人员阅读。如涉及版权问题,请联系作者删除。
Mysql全面总结
|
2月前
|
SQL 关系型数据库 MySQL
JavaWeb基础1——MySQL
SQL语句、DDL、DML、DQL(分组查询、子查询等)、Navicat、约束、 一对多等数据库设计、多表查询(联合查询/连接查询)、事务、函数
JavaWeb基础1——MySQL
|
2月前
|
SQL NoSQL 关系型数据库
|
6月前
|
存储 关系型数据库 MySQL
mysql(下)
mysql(下)
67 0
|
SQL 存储 关系型数据库
mysql总结(上)
mysql总结
76 0
|
关系型数据库 MySQL 索引
MySQL为什么不推荐使用in
MySQL为什么不推荐使用in
|
SQL 存储 缓存
|
SQL 关系型数据库 MySQL
|
SQL 安全 关系型数据库
|
关系型数据库 MySQL
MySQL一行变多行,多行变一行
MySQL一行变多行,多行变一行
196 1
MySQL一行变多行,多行变一行