Mysql必知必会总结
基础概念
sql(Structured query language)结构化查询语言
不区分大小写,对所有SQL关键字使用大写,而对所有列和表名使用小写便于阅读
MySQL版本
4 - innoDB引擎,增加事务处理,改进全文本搜索4.1- 对函数库,子查询,集成帮助的支持5- 存储过程,触发器,游标,视图
mysqll中的数据类型
串数据类型,存储如名字,地址,电话,邮编等串。
定长串,如char,创建表时指定
变长串,如TEXT,虽然灵活但是会影响性能
数组数据类型,有符合,可以存储正/负,
decimal(精度可变浮动值)double 双精度浮点值boolea 0/1
日期时间数据类型
二进制数据类型,可存储任何数据,如图像,多媒体等
常用语句
建库删库/建表删表
建表,可以定义引擎,主键自增AUTO_INCREMENT 给表添加/删除列ALTER TABLE student ADD stu_id CHAR(20);ALTER TABLE student DROP stu_id CHAR(20);
删表/重命名
DROP TABLE STUDENTRENAME TABLE STUDENT TO STIDENT2
查询语句 select
标准: select * from db_name (* 可为 row_name)distinct 检索不同的列 select DISTINCT id from db_name;
limit 限制行数 select name from db LIMIT 5;(limit 1 , 5 😉
拼接字段Concat(),别名,算术计算
子句顺序
select > from > where > group by > having > order by>limit
ORDER BY排序
例 SELECT name from db_name ORDER BY name;
ORDER BY包含一个或多个列的名字
排序方向(升序/降序)ASC/DESC ,指定列排序
SELECT id,name,price from DB ORDER BY price DESC,name
GROUP BY 分组
所有列都必须在GROUP BY 子句中给出,必须在where之后,order by 之前
HAVING 过滤分组,类比where
WHERE 过滤行
标准:
SELECT id from db WHERE id = 1;
ORDER BY 位于 WHERE 之后
where子句操作符: = ,!= , <>=,between
and/or 操作符,存在优先级问题,优先处理and语句,可以使用括号解决。
in范围操作符,not
where id in (1,10)
like,%,_ 通配符
SELECT id,name from db WHERE name LIKE '%jet%';where name LIKE '_id';
正则表达式匹配关键字:PEGEXP
WHERE name PEGEXP '[1-5]Ton';^ $ {n} * 等
函数
文本处理函数 Rtrim(),去重列值右边的空格Upper()大写Lenght()串长度 日期时间处理函数数值处理函数WHERE Date(order_date) ='-09-01'
3. 数值处理函数 abs() cos() sin() sqrt() 等 3. 聚集函数 AVG() count() max() min() sum()
高级查询
子查询,列必须匹配,可以多层嵌套
联结(join),要有where子句
内联结,用inner join 代替where
可以使用别名来缩短联结的sql语句
外联结,用right/left指定没有关联的行
组合查询 UNION/union all(包含所有行),可以使用order
全文本搜索Match()he Against(),需要在见表时启用
插入语句
insert,需要列出完整的列名
intsert into db_name (name,email) VALUES ('ZXC','ZZZ');
插入多行:提交多条insert或如果列名次序都相同,可以组合插入
intsert into db_name (name,email) VALUsqES ('ZXC','ZZZ') VALUES ('aa','vv');
INSERT SELECT插入检索出来的数据
更新/删除数据
UPDATE,设置多个列时,只用1给set,逗号隔开
UPDATE db_name SET name = 'ccc' ,email = 'zzz' where id = 1;
DELETE
视图存储过程游标触发器
视图(大量部署会降低性能)
作用:复用sql语句,简化sql,保护数据,更改数据格式创建:CREATE VIEW,删除:DROP VIEW view_name存储过程
简单,安全,高性能,(一定程度上的封装)
创建
CREATE PROCEDURE productprice1 () //无参beginSELECT avg(price) from db_name ;endCREATE PROCEDURE productprice2 ( //有参IN onnumber INT,OUT ototal DECIMAL(8,2)) beginSELECT avg(price) from db_name ;end
调用CALL
CALL productprice2(@pricelow) CALL productprice1()
删除DROP PROCEDURE productprice1;
游标(mysql 5后支持,mysql中只能用于存储过程)
作用:用于交互,滚动或浏览结果集并且修改
创建
CREATE PROCEDURE productprice1 () beginDECLARE ordernumber CURSOR FOR //创建SELECT avg(price) from db_name ;OPEN ordernumberCLOSE ordernumberend
触发器(mysql 5之后支持,仅支持表)
作用:在数据发生改地时自动处理,响应以下语句,delete,insert,update,保证数据一致性创建,需要给出1.唯一触发器名,关联的表,响应的语句,何时触发(before/after)给出了名,在insert之后执行,关联的表名删除 DROP TRIGGER newproduct
事务处理,安全,性能,维护
事务,维护数据完整性,要么完全执行,或完全不执行
MyISAM不支持明确的事务处理InnoDB支持明确的事务相关概念,事务(transaction)一组sql语句 回退(rollback)撤销sql语句, 提交(commit)写入数据库,保留点(savepoint) 回退到指定位置(部分事务)SET AUTOCOMMIT = 0;设置不自动提交
安全:登录,增加/删除用户,备份/还原
CREATE USER ben DROP USER benGRANT SELECT ON db.* TO ben//允许ben对db的所有表只有只读的权限REVOKE SELECT ON db.* from ben //撤销操作
备份,保证数据都写入再备份,用FLUSH TABLES 语句 命令行mysqldump,转存到外部文件,备份前保证正常运行mysql的BACKUP TABLES 或 SELECT INTO OUFILE 转存到外部文件。 日志 错误日志:hostname.err,位于data目录查询日志:记录所有的mysql活动,名为hostname.log二进制日志hostname-bin,记录所有更新过数据的语句,在mysql 5 中添加,之前使用的是更新日志。缓慢查询日志:hostname-slow.log 记录执行缓慢的查询,对优化有帮助
性能
使用一段时间后需要调整内存分配,缓冲区大小等
show variables; show status //查看当前设置
mysql是多用户多线程的DBMS,因此经常同时处理多个任务,当一个执行缓慢时,使用请求执行都会缓慢,可以用show processlist 显示所有进程,也可以用KILL结束某个进程
一般而言,存储过程比一条条执行sql语句快
不要检索不需要的数据,即要多少查多少
建立索引,如果一个查询语句执行时间过长,可以根据其常使用的列建立索引。
索引改善检索,但不利于删改增,根据表的常用操作判断是否添加索引
LIKE很慢,多用FULL TEXT
重视采用的数据类型,错误的数据类型会严重影响性能