之前发现MySQL有些东西忘光了,稍微回顾一下。
U1 注意事项
- 在下面的语句中,凡是出现不加引号汉字在实际中都需要换成实际的名字,如:Use 数据库名; 在实际中可能需要换成 use student;等
- 这里的内容都是我参考 《MySQL5.7从入门到精通》刘增杰 清华大学出版社 这本书的内容而来的
- Mysql中大小写不敏感,因此下面的有些大写有些小写(navicat自动补全的都是大写)
- 在这里有些注释以//开始,只有单行注释
- 这里有些例子都是我自己想的,可能不是最优的,只是用来展示知识点的,如有比较优秀的语句可以实现希望多多指正,不胜感激。
- 有些知识点目前用不到,学的动力不大,以后补上
U2 数据库存储引擎
MySQL的默认存储引擎是innoDB存储引擎
U3数据库的基本操作
- navicat怎么查看操作日志
- 方法一:工具—>历史日志
- 方法二:CTRL+H
- 常见的表属性查看
Show databases; 查看现有哪些数据库
Use 数据库名; 更改使用数据库
Show tables; 查看现用数据库中的有哪些表
Describe 表名; 查看表基本结构,describe可以简写为desc
SHOW CREATE TABLE 表名; 查看表的详细信息(创建表时的语句,结果包括字符集和存储引擎)
3,常见表属性(或者称为字段,列)的修饰符:
Auto_increment 自增
Unique 唯一性声明
Default 设置默认值
Primary key 主键
Foreign key 外键
Not null 非空
- 修改数据表
(1)修改表名tb_test为tb_t
alter TABLE tb_test RENAME tb_t;
(2) 把表tb_t中的tb_name属性改为int(10)
ALTER TABLE tb_t MODIFY tb_name int(10);
(3) 把表tb_t中的tb_name属性改为int(10)类型的tb_age;
alter TABLE tb_t CHANGE tb_name tb_age INT(10);
(4) 在表tb_t中添加不为空的varchar(10)类型的属性tb_name,这里的修饰符有first(在第一列添加) after name (在name这一列添加)
ALTER TABLE tb_t ADD tb_name VARCHAR(10) NOT NULL;
(5)把某一字段调整到另一字段之后,这里把tb_id放到tb_name之后
ALTER TABLE tb_t MODIFY tb_id INT(10) AFTER tb_name;
(6)如果表之间有外键约束,在删除父表的时候会报错,可以先删除子表,逐层删除父表,或者取消约束条件
本节注意: (1)并不是每个表都需要有主键,但是多个表连接的时候需要用到主键,有的时候需要用到联合主键
(2)每个数据表都可以选择自己的存储引擎,但是外键约束不能跨引擎使用,因此存储引擎使用需谨慎
(3)自增字段可以设置初始值
U4常见数据的数据类型
- 货币 decimal类型
- 只记录日期year
- 只记录时间time
- 记录时间和日期timeStamp
- Char(定长字符串)varchar(变长字符串)
- Enum 多个值中取单值,如性别男女中取男
- Set多个值中可取多个值
本节注意:
- 在mysql中虽然可以存储媒体问价,但是出于性能的考虑一般选择存储这些文件的路径
- Mysql中大小写的区分,加上binary关键字
U5 mysql常见函数
数学函数
Abs(x)返回x的绝对值
Sqrt(x) 返回x的平方根(开方)
MOD(x,y)返回x%y
CEIL(x) 返回不小于x的最小整数
Rand(x)产生一个0到1的随机值,x是种子
Round(x)对x四舍五入
Sign(x)返回x的符号(-1,0,+1)
Pow(x,y)返回x的y次方
字符串函数
Char_length 计算字符串字符个数
日期时间函数
Curdate() 返回YYYY-MM-DD日期
Curtime() 返回系统当前时间
Current_timestamp()=localtime()=now()=sysdate() 返回当前的日期和时间
条件函数
If(expr(),a,b) 如果表达式expr()为true则返回a否则返回b
加解密函数
Password(str) 将str加密返回二进制字符串,不常用
MD5(str) 将str使用MD5加密返回二进制字符串
Encode(pwd,str)使用pwd作为密码加密str返回一个和str等长字符串a,常用
Decode(pwd,a) 和encode配对使用,用pwd作为密码解密a
U6,数据查询操作(重要)
包括对数据有选择的查询和按格式输出,最主要的关键字是select ,上面U3.3的调整字段的位置的语法有助于更好的展示数据
查询语句的基本形式:
Select distinct{*|字段列表} from <表1,表2>… //distinct表示数据唯一,重复//的都只显示一份
Where <表达式>
Group by <group by destination> // 按字段分组显示数据
Having expression
Order by <order by destination> //按什么顺序显示数据:升序(asc),降序(desc)
Limit <row count> //x显示每次查询显示的结果数
下面是基于本节而创建的表(我是直接从网上复制粘贴的插入代码再自己加上主外键实现的)
Course表
SC表
Student表
Teacher表
- 在查询中in可以指定范围
(1)查询成绩为70或者80或者76的人的学号和成绩
SELECT Sid ,score FROM sc WHERE score IN (70,80,76);
2,使用between…and … 来进行范围查询
SELECT Sid ,score FROM sc WHERE score BETWEEN 70 AND 90;
3 ,使用like来进行通配符查询
SELECT sname FROM student where sname like '李%';
一般的通配符有%和_,其中%不光后面有多少个字符,_只能替代一个字符
- 分组查询:(group by)
select score ,COUNT(score) as total from sc GROUP BY score having score >= 60;
上面语句的意思是从sc表中显示score和score的数量,其中score按照total为标题显示,按照score的大小分组,其中score必须是60分或之上的,分组之后相当于形成了单独的列
- 限制查询结果条数
SELECT * FROM sc ORDER BY score DESC LIMIT 5 ;
查询成绩前五
SELECT * FROM sc ORDER BY score DESC LIMIT 3,4 ;
从成绩第四(3+1)开始查询4条成绩记录
- 聚合查询
常见聚合函数
Avg() 返回某列的平均值
Count() 返回某列的行数
Max() 返回某列的最大值
Min()返回某列的最小值
Sum() 返回某列的和
- 连接查询(重要)
连接查询有内链接,外连接,内链接查询是在一个表中查不同的属性,而外连接是针对多个表的连接查询(重要)
比如下面连接了三个表,
select sname,Cname,score from student,course,sc where student.sid=sc.Sid AND course.Cid = sc.Cid;
外连接查询(重要),外连接查询又分为左连接查询(left join)与右连接查询,其中比较常用的是左外连接查询(left out join)(即:以左表为显示的基础进行完全显示,右表仅显示连接字段)
如之上的查询可以替代为
SELECT student.sname,course.Cname,sc.score FROM (student LEFT JOIN sc ON student.sid = sc.Sid)LEFT JOIN course ON course.Cid = sc.Cid;
其中left join 是left outer join 的简写,因此左连接就是左外连接的意思。
- 子查询
子查询是指嵌套在一个查询内部的查询,顺序是先计算子查询,其结果作为外部查询的条件或者结果继续进行查询,常见的关键字有:any(some)(任一条件即为True),all(满足所有条件为true),in (返回一个数据列),exists(存在,有一条就返回)
- 合并查询
将结果类型相同的多条select语句整合到一起,关键字是union all
- 正则表达式查询
这里我觉得书上给的图就比较明了
如 SELECT * from student WHERE sname REGEXP '.李';
查询某个叫什么李的人,这里.本来只是代表一个字符,但是在本例中代表查询名字中包含’某李’两个字的人的姓名。此外,regexp意为正则表达式,是使用正则表达式的关键字
U7 插入,删除,更新操作
- 插入
在course中插入一条新纪录
INSERT INTO course(Cid,Cname,Tid)VALUES('04','java','02');
在插入操作中要特别注意的是,你要知道你插入表的数据格式,可以使用desc 表名来查看,比如varchar类型需要加引号,日期类型要符合格式等,此外插入的时候还要注意主键不能重复等问题
- 更新
UPDATE sc SET score =79 WHERE Sid = '01' AND Cid = '01';
把学号为01的同学的课程号为01的成绩改为79
- 删除
delect FROM course WHERE cid = '04';
删除编号为04的课程
U8索引
索引是对某列进行快速查询的一种结构,索引的数量要适中,一个表中不要超过3个,索引是针对大量数据才见效果,如果几百条数据就不必要加什么索引了,索引可以在表创建的时候就创建,也可以在表创建完成之后更改表实现创建,索引创建完成之后在查询表的时候系统会自动调用来优化查询
普通索引:值不唯一,可为空
唯一索引:值唯一,不可为空
- 创建表时创建普通索引,索引名字默认
CREATE table test(tid VARCHAR(10) not NULL,tnum INT(10) NOT NULL,INDEX(tid));
- 在已有表上创建索引 tindex
ALTER TABLE test ADD INDEX tIndex(tnum);
- 显示现有索引
SHOW INDEX FROM test;
- 删除索引tid
alter TABLE test DROP INDEX tid;
U9存储过程(非常重要)
- 存储过程就是多个sql语句的集合,存储过程可以一次编译,多次调用,因此性能比普通语句好很多(在大量复杂业务情况下很明显),此外还可以防sql注入攻击,但是存储过程也有很多缺点,如sql本身不擅长处理业务,因此没必要搞这么多存储过程,此外存储过程出错很难调试,因此要适量使用存储过程。
- 存储过程和函数异同:
相同点
-
- 本质上都是存储程序
不同点
- 函数可以通过return返回单个值或者对象,存储过程没有return,但是可以通过out参数返回多个值
- 函数可以在select语句中调用,存储过程不可以,一般作为单独一个模块
- 注意事项:
- 存储过程不可以更改,,如果必须更改必须删除(drop)后重建
- 存储过程可以使用call调用其他存储过程,但是不能使用drop删除其他存储过程
- 存储过程的参数不要使用数据库的保留字段,如select等
U10 视图
视图是数据库中的一个虚拟表,属性从多个表中抽取,属性和操作和真实表相同,目的是为了让用户更加方便的查看数据
- 创建一个试图,使其存在student表中的姓名和性别(若视图存在则替代,修改视图结构也是使用这个语句)
CREATE OR REPLACE VIEW stuview(name,sex) as SELECT sname,ssex FROM student;在视图中查询
SELECT * from stuview;
- 视图是已经编译好的sql语句,不占用物理空间,视图的删除不影响实际表的内容
- 查看现有的所有视图
show table status where comment='view';
此外在information_schema数据库中的views表中也可以查看到所有的自建的视图
U11 触发器
触发器是特殊的存储过程,不需要使用call语句来调用,在进行事务的时候自动调用
- 格式
CREATE TRIGGER test AFTER DELETE ON student FOR EACH ROW
BEGIN
INSERT INTO stusize(id,size)VALUES(1,1);
END
- 显示所有的触发器
show TRIGGERS;
3,注意,相同的事件只能创建一个触发器,比如一个表的delete事件,否则没有办法识别,此外及时删除不必要的时间,否则会造成很多问题
U12 账户管理
1,在mysql数据库的user表中可以查看所有的用户,并修改其权限
U13数据备份与恢复
U14性能优化
U15 数据库设计
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------有些内容没动力看了,之后再补上