grade是什么类型 mysql_MYSQL 总结

数据基础

SQL语法顺序

1.建库

create database mydatabase;

也可以做个判断

create database if not exists mydatabase;

2.删库

drop database mydatabase ;

也可以做个判断

drop database if exists mydatabase;

mysql 中两个字符串拼接

select concat(username，password) 结果 from table ； 结果是全别名as可省略

3.默认值 default默认值，comment注释 engine引擎

create table if not exists student(

id int(4) not null auto_increment comment '学号id',

name varchar(20) not null default '匿名' comment '姓名',

age int(4) not null comment '年龄',

primary key(id)

) ENGINE=INNODB DEFAULT CHARSET=utf8

4.查看建库，建表语句，表结构

show create database mydatabase; 查看建库语句

show create table mytable; 查看建表语句

desc mytable 查看表结构

5.查看mysql中文件位置

show variables like '%dir%';

6.修改表

1.alter table user rename to user1 //修改表名 user-->user12.alter table user1 comment 'user表' //修改表注释

3.alter table user1 add passwd varchar(20) not null //增加passwd列

4.alter table user1 modify column passwd int(20)//修改字段类型

5.alter table user1 change passwd passwd1 varchar(20)//修改字段名字(要指定字段类型)

6.alter table user1 add sex int(1) default 0 after name;//在某个字段后面加列

7.alter table user1 change sex sex int(1) after passwd1 //调整字段顺序 sex出现了两次

8.alter table user1 add primary key(id)//增加主键

9.alter table user1 modify id int(10) not null auto_increment; //添加自增 前提是主键

10.alter table drop sex//删除某个字段

11.drop table user1 //删除表

7.插入

insert into student(id，name) values(1,'wh');

insert into student values(1,'wh'); //如果列名一一对应可以不同写列名

insert into student(id，create_time) values(2,current_time);//插入当前时间 字段类型是 datatime 可以插入current_time 表示当前时间

8.删除

delete from student；//student是表名 清空数据

delete from student where id =1 //清楚某条数

TRUNCATE

truncate table student;//删除学生表

delete，truncate，drop区别？ delete 和truncate 都是删除表中的数据，不删除表，表结构索引不变。 delete可以具体到删除哪行，truncate 直接删完。 delete保留自增计时器，truncate直接将自增计数器清空 如果在开启了一个事务，delete删了之后如果取消还可以回滚， truncate直接删了，和事务无关 droptable student 是直接把表删了。

9.更新

update mytable set age=18 where id between 2 and 4;//将id2,4的age变为18岁

update mytable set age=18,username='wx123' where id =1;//将id=1的，名字，年龄变为 wx123,18

10.查询

select * from student ;//查全部数据

select concat('姓名：'，name) as '新名字' from student；//查出name 在前面加个姓名： 列名改为concat

select distinct name from b;//去重name 相同的不要。但这样只有name字段，如果要其他字段不可以；

例如 select distinct name,id from b;//这样 虽然显示id，name，但是去重条件是name,id都相等才去重

所有用下面语句 group by分组，以什么为一组

select id,name from b group by name;//以name为分组，也可以实现去重

select name,age+1 as '加一岁 ' from b；//查询name age 并将age+1

where子句 条件 and or not

select * from a where id<10 or id>20; //选择出id<10或者id>10

select * from a where id between 10 and 20;//10~20

select * from a where id>10 and age<18;//id>10且age<18

select * from a where age !=10; //查年龄不是10岁的

select * from a where not age=10;

模糊查询null，like，in

like

select * from a where name like '王_' //一个下划线代表一个字 这里是查 王X

select * from a where name like '王%' //第一个字是王的

select * from a where name like '%王%' //查名字中有王的

in 里面根具体的值，不能%模糊查

select * from b where id in(1,2,3) //查出id 是1,2,3的

null

select * from b where id is null;//id为空

select * from b where id is not null;//ID不为空

join 多表联查

JOIN ..ON 固定语法

inner join ：

SELECT a.*,u.user_name FROM article as a inner join user as u on a.user_id=u.user_id;join1

left join ：

SELECT a.*,u.user_name FROM article as a left join user as u on a.user_id=u.user_id;join2

right join ：

SELECT a.*,u.user_name FROM article as a right join user as u on a.user_id=u.user_id;join3

上面三种是最常见的。区别

inner join 是他们通过条件匹配的 a.user_id=u.user_id; 只显示两个相等的行

left join 即使他们的不符合匹配条件 也会将左表中的数据全部查出，如图join2所示 即使右表中没有与之对应的那行数据。

right join同理，右表中的数据全部查出，即使不匹配，所有如图join3所示，左表没有与之对应的那行数据

JOIN ON 有的后面还会跟where 。 on是判断条件，where 时等值查询，可以理解为在on判断条件之后，在进行一次筛选。

SELECT a.*,u.user_name FROM article as a right join user as u on a.user_id=u.user_id where starts !=null ;

扩展3表查询 查出文章名，下面的评论人的姓名和评论内容。

user article comment 三个表 先分为两个表，查出数据，在和第三个表查询

select a.article_title,u.user_name as '评论人',c.content

from article as a

inner JOIN comment as c

ON c.article_id=a.article_id

上面先查出文章下面的评论id，和内容

INNER JOIN user as u

on c.comment_userid=u.user_id

通过评论查出对应的用户名

where a.article_id=1 //这里意思是查出article_id 为1的文章

自连接

用一张表的某行的某字段，去这张表其他行的某字段去查询值。

我们现在有个评论表

评论id，评论用户的id，评论用户的姓名，被评论用户的id，我们现在想在这张表中 查出被评论用户的姓名怎么查。 关键点在于把这张表看成两张表。这样可以理解a表中的tocommentid 要去B表中的 通过commentid 查询到comment_id(被评论用户的姓名) ，实际上是一张表，通过取别名的方式当做两张表而已。

SELECT

c.comment_id,

c.comment_userid,

c.comment_username,

c.to_userid AS to_commentid

FROM

`comment` c

上图查询中的sql语句。 而现在，我们要再多查出一列"被评论用户的姓名"

所以看下面的sql，增加了一列即可。

SELECT

c.comment_id,

c.comment_userid,

c.comment_username,

c.to_userid AS to_commentid,

c2.comment_username AS '被评论人姓名'

FROM

`comment` c,

`comment` c2

where c.to_userid=c2.comment_id

where条件 表1中的to_userid去第二张‘comment’表找字段相等的即可

排序 order by

ascend 升序(默认 )，descend降序

select * from student order by grade asc;//查询出学生表中所有信息 按成绩排序，默认升序 asc可省略

select * from student order by grade desc;//降序

分页 limit 还有一种瀑布流，往下拉一直有

select student_name,grade from gradetable where subject='数学' order by grade desc limit 0,10;//在成绩表里查出前10学生的姓名和成绩

前面的0代表从起始位从0开始 ，后面的10，是往后查询条数 为10

如果limit 10,5 意思是起始位是10，往后查询5条

子查询 (重点)

之前像select * from b where id=10; =10是写死的

而子查询是where里面嵌套一个查询， where后面根的值是计算出来的，不是写死的

eg1.现在我们要查出 爱好游泳学生的全部信息 ，首先爱好游泳的同学的全部信息 都在student表里

我们可以直接 select * from student 获得

爱好游泳需要在hobby_table表里查 select student_id from hobby_table where hobby='游泳';

后来通过 student表中的student_id 和hobby_table表中的student_id 将他们连起来

select * from student where student_id=(

select student_id from hobby_table where hobby='游泳'

)

用之前将的连表查询是

SELECT s.* FROM student AS s

INNER JOIN hobby_table AS h

ON s.student_id = h.student_id

WHERE h.hobby = '游泳'

通过join多表联查，和where子查询 我们不难发现，如果只是需要多经过几次的判断，(子查询里面嵌套一个查询)，用子查询，多表联查都可以，逻辑上 可能子查询好理解一点，但是如果我们还需要查询显示出另一张表其他字段，只能用多表联查。例如多个 hobby字段，select s.* 加上 h.hobby即可，而子查询只是经过判断 哪个student_id符合，不能加上字段。

一些函数

group by分组

查某个科目的平均成绩

select subject_name，AVG(student_grade)

from result as r

inner join subject as s

on r.subjectno = s.subjectno

group by r.subjectno;

如果此时有个判断条件平均分大于80，之前这么写

select subject_name，AVG(student_grade)

from result as r

inner join subject as s

on r.subjectno = s.subjectno

where AVG(student_grade)>80

group by r.subjectno;

在这里会发现报错了 原因是过滤分组查询的次要条件需要用having

改为如下sql

select subject_name，AVG(student_grade)

from result as r

inner join subject as s

on r.subjectno = s.subjectno

group by r.subjectno

having AVG(student_grade)>80；

MD5 加密 不可逆 相当于一个字典

insert into b(id,name) values(md5(1213131),'xx'); //插入 将id MD5加密一下

select * from b where id =md5(1213131);//查到对应数据

进阶

下面的内容某些讲的可能没有什么关联。

索引

MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

添加索引ALTER table [表明] add [索引种类] index [索引名] (`column`)

create [可加索引种类] index [索引名] on [表名](字段)

删除 DROP INDEX `indexname`

主键索引

主键 : 能唯一标识一条记录

特点 :

最常见的索引类型

确保数据记录的唯一性

确定快速定位到数据

唯一索引

作用 : 避免同一个表中某数据列中的值重复

与主键索引的区别

主键索引只能有一个

唯一索引可能有多个

普通索引

全文索引

插入100w条数据

DELIMITER $$

CREATE FUNCTION mock_data()

RETURNS INT

BEGIN

DECLARE num INT DEFAULT 1000000;

DECLARE i INT DEFAULT 0;

WHILE i < num DO

INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`,

`age`)

VALUES(CONCAT('用户', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*

(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));

SET i = i + 1;

END WHILE;

RETURN i;

END;

SELECT mock_data();

设计一个数据库

为什么要好好设计数据库？

糟糕的数据库设计 :数据冗余,存储空间浪费

数据更新和插入的异常

程序性能差

良好的数据库设计 :节省数据的存储空间

能够保证数据的完整性

方便进行数据库应用系统的开发

数据库设计遵守三大范式：第一范式 ：每一列不可再分

第二范式 ：满足第一范式，每个字段都和主键有关，每张表只描述一件事。(比如订单表，里面只能有订单方面信息，不可以有其他，例如用户名，职位等)

第三范式 ：满足第二范式，每个字段和主键直接相关

规范化和性能的关系在数据规范化的同时 , 要综合考虑数据库的性能

通过在给定的表中添加额外的字段,减少查询用时(连表变为了单表查询)

数据库中可能会听到sql注入,那什么是sql注入，就是通过字符串拼接的方式是用户密码始终为true，继而暴力登录。

解决 java中用PreparedStatement对象，预编译，将sql 中参数变为?占位符，将参数当做字符，直接替换(假如遇到特殊字符会将他转义掉，例如引号)，不存在拼接问题了select * from b where id=?

#，$区别

#{} 会预编译，对传入参数加上引号，特殊字符如引号会转义，

传入wh(字符串)

select * from b where name=#{name}

--->select * from b where name="wh"//将传入的wh加上引号

${}

传入wh(字符串)简单字符串拼接

select * from b where name=${name}

--->select * from b where name=wh //报错

应该为

select * from b where name="${name}"

例如表名，列名，order by后面等 动态参数用$

我们mysql逻辑图是什么样的呢？

首先客户端通过身份验证连接上mysql连接器，当我们执行sql时，先查询缓存，如果有命中，直接返回，没有，去分析器：进行词法分析，语法分析，构建一颗语法执行树。优化器：构建执行计划(选择合适的索引)，最后上执行器：去执行，返回结果 如果是insert语句，需要将数据放到存储引擎中。

在解析层中有三大缓存操作：1.sql语句缓存 select * from b where id =1构建的语法执行树

2.sql执行计划缓存 select AVG(grade) from b group by ...order by..concat....等一些动态的数据，无法缓存他的缓存树，所以只能缓存他的执行计划：执行顺序，是否要重写等。 找缓存先3后1.找不到在2

3.结果集缓存 上一次的查询结果k---v 最没用mysql逻辑图

mysql 为每个client 分配一个session(理解为socket)，在执行事务，会申请一个独立空间(memory allocation)，在里面进行解析，优化，执行等操作。

MYSQ为什么用B+？

linux 1页4KB ，cpu每次拿4页，mysql 默认16kb，一次4页 B+一个索引中的一个节点最多8字节，加上指针6字节，最多14b 16kb/14b=1170,如果是数据的话就放的少一点，所以说mysql用B+，是因为B+一个索引Node最大可放16kb，恰好是cpu一次拿的最大值，而索引越节点放的数据越多，相对二叉树，整颗树的高度，就不会太高。(节点放在磁盘上，每次查找都需要去磁盘拿，高度小磁盘IO次数就少)磁盘IO利用率高

undo log是回滚用的，在执行事务的，如果回滚了，事务会顺着undo log日志，回到最初的数据。

redo log是innodb特有的 在innodb引擎层，记录新数据的日志，为事务中每次的变更记录日志，大小是固定的，理解成一个环形，write_pos逆时针写数据，check_point擦数据，将输入写入硬盘中。

Bin log在mysql服务层，它记录了数据库上的所有改变，并以二进制的形式保存在磁盘中；它可以用来查看数据库的变更历史、数据库增量备份和恢复、Mysql的复制(主从数据库的复制)。语句以“事件”的形式保存，它描述数据更改。

binlog有三种模式：ROW(行模式), Statement(语句模式), Mixed(混合模式)。

row

优点：日志内容会非常清楚的记录每条数据详细的变更细节，即使只更新了一个字段，binlog里也会记录所有字段的数据。

缺点：binlog日志会非常大，mysql主从同步时，会产生大量磁盘IO。

statement记录的只是执行语句。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。

缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题。

Mixed(混合模式)：在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。