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 右
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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.第一个_和%
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT * FROM tb_students where name like '%老_' _代表一个字符 %代表多个字符 这两个和like使用
2.= like
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
= 这就i是完全等于
like 去配合通配符使用
3 in not in 子查询 他不是数组是括号
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 去重
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT DISTINCT(age)
FROM tb_students
5.left(str,3) 函数获取字符的从左边数三个字符,还有right
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT left(name,1) FROM tb_students
6.top == limit
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT top 2 * FROM tb_students
7.cast 转换数据类型
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT CAST(sex AS int) FROM tb_students
8.替换函数replace
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT replace(name,"old","new") FROM tb_students
9.字段拼接concat, 还可以自己加参数,岂不是和字符串格式化f"sssssssss{a}"相似
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT CONCAT(name,sex,"asdas") FROM tb_students
10.if 函数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select t.name,if(t.weight<80,'正常','肥胖') 体重 from t_customer t
11.case when then else end 条件语句函数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 或者空格
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT name 姓名 FROM tb_students ================= SELECT name as 姓名 FROM tb_students
13.内外左右连接
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 函数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select COUNT(id),sum(id) from tb_students
-----------------------------------------------------------------------------------------------------------------------------------------