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+树

 1.第一个_和%

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

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

2.=  like

= 这就i是完全等于

like 去配合通配符使用

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)

4.distinct 去重

SELECT DISTINCT(age)
FROM tb_students

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

SELECT left(name,1)
FROM tb_students

6.top  == limit

SELECT top 2 *
FROM tb_students

7.cast 转换数据类型

SELECT CAST(sex AS int)
FROM tb_students

8.替换函数replace

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

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

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

10.if 函数

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

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

 12.起别名  as 或者空格

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

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  右

 14.count  sum  函数

select COUNT(id),sum(id)
from tb_students