主要就是连接方式；
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
View Code
-----------------------------------------------------------------------------------------------------------------------------------------
