mysql

来源:互联网 发布:淘宝怎么去掉虚拟商品 编辑:程序博客网 时间:2024/06/22 23:34

关系型数据库管理系统 、开源(MariaDB)、隶属Oracle数据库
官网地址:http://www.mysql.com/
关系型数据库
—— MySql Mssql Oracle等都属于关系型数据库
用关系模型来组织管理数据。关系模型可以理解为二维表的模型,由二维表及表之间的联系所组成的数据组织。
非关系型数据库
—— NoSQL:Mongodb Redis……
是一种新的数据结构存储方式的集合。存储方式有文档、键值对、图片形式等,结构不固定,每一个元祖可以有不一样的字段或键值对,约束少等特点。
对比
1、对并发量较大的系统,读写较高的情况下,关系型数据库将数据存储在硬盘中,非关系数据库将数据存储在缓存中,执行效率的差别
2、存储类型:NOSQL存储格式有文档、键值对、图片等形式,可存储除了基础类型外还有以及对象等格式类型,Mysql只能存储基础类型数据
3、可扩展性
My.ini配置文件:
端口号 3306
数据存放位置:mysql安装目录下的data文件夹下
修改XAMPP集成环境 root用户的密码:
//方式1:
UPDATE user SET password=PASSWORD(‘123456’) WHERE user=’root’;
flush privileges;

//方式2:
mysqladmin -uroot -p123456 password 000000
Mysql安装后自带的数据库有:
information_schema:当前mysql实例中所有数据库的信息,主要存储了系统中的一些数据库对象信息:如用户表信息、列信息、权限信息、字符集信息、分区信息等。
mysql:是mysql的核心数据库,存储了系统的用户权限信息及帮助信息。
performance_schema:主要用于收集数据库服务器性能参数。
test:系统自动创建的测试数据库,任何用户都可以使用。
常用命令:
1、连接数据库:
mysql –h主机地址 -u用户名 -p密码
mysql –u root –p 123456;

2、显示数据库:
show databases;

3、操作数据库:
use 数据库名称;

4、显示数据表:
show tables;

5、创建数据库:
create database 数据库名称;
或:
create database if not exists 数据库名;

设置数据库编码UTF-8:
CREATE DATABASE IF NOT EXISTS 数据库名 default character set utf8 COLLATE utf8_general_ci;

5、修改数据库编码:
alter database 数据库名 character set latin1;

6、建表:
create table 表名 (字段名 varchar(20), 字段名 int(1));
create table Tables(
id int(4) not null primary key auto_increment,
name char(20) not null,
sex int(4) not null default ‘0’,
age double(16,2));

7、修改表:
删除列:ALTER TABLE tablename DROP COLUMN names;
添加列:ALTER TABLE Tablename ADD COLUMN names char(10);
修改字段:ALTER TABLE tablename CHANGE names idd BIGINT;
修改字段类型:ALTER TABLE tablename MODIFY idd BIGINT NOT NULL;
修改字段编码:ALTER TABLE tablename CHANGE name name varchar(255) CHARACTER SET utf8;

8、删除表:
drop table 表名;

9、删库:
drop database 库名;
用图形化管理工具备份数据库、还原数据库操作;
导出:mysqldump -u root -p dbname > dbname.sql
导入:use dbname; source /var/db/news.sql;
Mysql存储引擎
Mysql5.5后默认采用InnoDB存储引擎。
1、MyISAM引擎:
不支持事务、外键,对访问速度快要求的(SELECT)使用。每个MyISAM在磁盘上存储成3个文件,其中文件名和表名都相同,但是扩展名分别为: .frm(存储表定义) MYD(MYData,存储数据) MYI(MYIndex,存储索引)
2、InnoDB(默认引擎)
提供了具有提交、回滚和崩溃恢复能力的事务安全。MySQL支持外键的存储引擎只有InnoDB
3、MEMORY引擎:memory使用存在内存中的内容来创建表。
4、CSV:存储引擎把数据以逗号分隔的格式存储在文本文件中。
Mysql常用数据类型:
Int 整形 (-2 147 483 648,2 147 483 647)
Bigint 极大整数值 (-9 233 372 036 854 775 808,9 223 372 036 854 775 807)
FLOAT 单精度浮点数值
DOUBLE 双精度浮点数值
DATE 日期值 YYYY-MM-DD
DATETIME 日期和时间值 YYYY-MM-DD HH:MM:SS
CHAR 定长字符串 0-255字节
VARCHAR 变长字符串 0-65535 字节
TEXT 长文本数据
char(M)类型的数据列里,每个值都占用M个字节,如果某个长度小于M,MySQL就会在它的右边用空格字符补足.(在检索操作中那些填补出来的空格字符将被去掉)
在varchar(M)类型的数据列里,每个值只占用刚好够用的字节再加上一个用来记录其长度的字节(即总长度为L+1字节)
decimal类型常用于存储金额数据
DECIMAL(M,D) M是数字的最大数(精度),其范围为1~65(在较旧的MySQL版本中,允许的范围是1~254),M 的默认值是10。D是小数点右侧数字的数目(标度)。其范围是0~30,但不得超过M。
FLOAT 可以表示绝对值非常小、小到约 1.17E-38 (0.000…0117,小数点后面有 37 个零) 的小数,而 DOUBLE 更是可以表示绝对值小到约 2.22E-308 (0.000…0222,小数点后面有 307 个零) 的小数。FLOAT 类型和 DOUBLE 类型占用存储空间分别是 4 字节和 8 字节。如果需要用到小数的字段,精度要求不高的,当然用 FLOAT 了。

SQL语句
Structured Query Language 结构化查询语言
DML — Data Manipulation Language数据操作语言 如:select update insert delete语句
DCL — Data Control Language数据控制语言 如:GRANT、REVOKE 等(权限)
DDL — Data Definition Language数据定义语言 如:CREATE TABLE 、DROP TABLE、ALTER TABLE 等

通配符:
% : a%b 以a开头 以b结尾的内容
_ : a_b 以a开头 以b结尾的三个字符
逻辑运算符:
AND &&
OR ||
NOT !
INSERT 语句
Insert into 表名 (字段1,字段2…) values(内容1,内容2,内容3)

Delete 语句
Delete from 表名 where 条件

Update语句
Update 表名 set 字段1=值1, 字段2=值2 where 条件

Select语句
Select 字段,,,* from 表名 where 条件
Order by * Desc 或 * Asc 可多条排序方法用逗号分割
Limit limit 0,10 从0开始取10条记录出来
Group by 、having: 分组查询 having分组查询条件

SELECT A ,COUNT(B) FROM TABLE GROUP BY A HAVING COUNT(B)>2
Mysql内置函数
Count(字段名) 总计
Sum(字段名) 求和
Avg(字段名) 平均
Max(字段名) 最大值
Min(字段名) 最小值
MySql谓词
Between and 范围
Not between and
In 特定的集合中
Like 匹配一个模式
Is null 、 is not null

主键与外键
外键仅适用InnoDB引擎
主键:帮助MySQL以最快的速度把一条特定的数据记录的位置确定下来。主键必须是唯一的。主键应该是紧凑的,因此整数类型比较适合。
外键:引用另外一个数据表的某条记录。外键列类型尽可能与主键列类型保持一致。外键列应该加上NOT NULL
注意:
1、当主表中没有对应的记录时,不能将记录添加到子表
2、不能更改主表中的值而导致子表中的记录孤立
3、子表存在与主表对应的记录,不能从主表中删除该行
4、删除主表前,先删子表
外键类型:

1、Cascade 在父表上update/delete记录时,同步update/delete掉子表的匹配记录

2、No Action 如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作

3、Restrict 不允许对父表对应候选键进行update/delete操作,同上

4、Set null 在父表上update/delete记录时,将子表上匹配记录的列设为null,注意子表的外键列不能为not null

连接查询
同时涉及多个表的查询
普通连接
内连接( INNER JOIN )
外连接( 左外联结—LEFT JOIN 右外联结—RIGHT JOIN 全外连接—FULL JOIN)
注:普通连接操作只输出满足连接条件的元组,外连接操作以指定表为连接主体,将主体表中不满足连接条件的元组一并输出
普通连接:

SELECT Students.SName, Sc.CourseID, Sc.Grade
FROM Students,Sc
WHERE Students.SNo = Sc.StudentID

内连接:Inner Join 表名 on (条件)
SELECT
S.SName AS 姓名, CS.CName AS 课程, C.Grade AS 成绩
FROM Students AS S
INNER JOIN Sc AS C ON (S.SCode = C.StudentID)

外连接:LEFT/RIGHT Join 表名 on (条件)
SELECT
S.SName AS 姓名, CS.CName AS 课程, C.Grade AS 成绩
FROM Students AS S
LEFT/RIGHT/FULL JOIN Sc AS C ON (S.SCode = C.StudentID)

联合查询:union/union all 合并多查询结果(union会过滤重复记录、union all不会过滤)

SELECT prodinfo.pname, ptype.type_name
FROM prodinfo
RIGHT JOIN ptype
ON prodinfo.ptype = ptype.tid

UNION

SELECT prodinfo.pname, ptype.type_name
FROM prodinfo
LEFT JOIN ptype
ON prodinfo.ptype = ptype.tid
视图
虚拟表,内容由查询结果决定(筛选)。视图是不以数据值的形式存在数据库中,只有视图被引用的时候才会动态生成。
创建和使用视图:
CREATE
VIEW testdb.testView
AS
(SELECT id,val FROM tset_tab1 WHERE id IS NOT NULL);
使用视图的好处:
1、简化操作,虚拟表
2、更方便重构表数据结构
3、加强数据结构的安全

存储过程
SQL语句预编译的集合,存是数据库中,可以由应用程序调用执行,可以带相关参数,可以理解为是将SQL语句进行了封装并放在数据库中,并且是已经编译好了,调用即可立即执行,所以执行效率要比直接执行sql语句的效果要高。
优点或特性:
1、执行sql效率更高;(第一次效率相同,后面直接从内存中调用)
2、减少流量
3、安全性更高(都是预先检查语法问题的)
DELIMITER

CREATE/[DEFINER=user|CURRENTUSER]/PROCEDUREtestdb.()/LANGUAGESQL|[NOT]DETERMINISTIC|CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA|SQLSECURITYDEFINER|INVOKER|COMMENTstring/BEGINSQLCURDEND

DELIMITER ;
参数: [in | out | inout ] 参数名 类型
In :该参数调用存储过程时指定的, 进
Out:该参数在是返回用的, 出
Inout:调用时指定,并可改变并返回 进出
调用存储过程:
Call 存储过程名(参数)
Call test();
Call test(2);
Call test(27, @num); //@num为变量的意思
CALL test2(1,@counts);
SELECT @counts;

索引
InnoDB引擎和MyISAM引擎。
数据表中建立索引可以很大程度上提高查询的速度(大数据量),也是优化mysql的其中一种办法。在查表(多表联合)查询中建立索引比较常用到。
索引分为:单列索引和组合索引
单列索引:只有一个字段的索引。
组合索引:可以包含多个字段。
Mysql索引类型:
1、PRIMARY 主键索引 建立在主键上。表的主键会自动创建主键索引
2、FULLTEXT全文索引 WHERE name LIKE “%keyword%”,限制在CHAR、VARCHAR ,TEXT 类型的列(文本)上可以创建全文索引
3、UNIQUE 唯一索引 该字段的值在表中是有唯一性的
如:身份证号、注册邮箱、注册手机等
4、INDEX 默认索引(普通索引)
例外:索引虽然可以提高查询速度,但会降低更新表的速度,因为在更新表时,不仅要更新表数据,还要更新索引文件。 索引文件会占用硬盘空间,如果一个表的索引建的过多,会导致表文件更大。
使用索引需注意:
1、 索引不包括NULL值
2、 where条件中如果已经使用索引,order by的字段不会使用索引。尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
触发器
触发器是一种特殊的存储过程,它在插入,删除或修改特定表中的数据时触发执行,它比数据库本身标准的功能有更精细和更复杂的数据控制能力。
INSERT、UPDATE、DELETE触发器
DELIMITER

CREATE/[DEFINER=user|CURRENTUSER]/TRIGGERtestdb.testAFTERINSERTONtestdb.testtab1FOREACHROWBEGINUPDATEtesttab3SETprices=prices+NEW.tabpriceWHEREidISNOTNULL;END

DELIMITER ;
AFTER触发就是在执行命令操作之后执行触发操作; BEFORE触发就是在执行操作命令之前执行触发操作。
事件调度器
MySQL中通过定时操作,完成某些对数据的修改删除等操作,可以定时精准至秒来执行操作任务。
在使用这个功能之前必须确保event_scheduler已开启:
set GLOBAL event_scheduler = 1; 或et GLOBAL event_scheduler = ON;
查看当前是否已开启事件调度器:
SHOW VARIABLES LIKE ‘event_scheduler’
创建定时任务:
DELIMITER $$

CREATE EVENT testdb.tests_event

ON SCHEDULE EVERY 1 SECOND STARTS TIMESTAMP ‘2017-07-23 15:58:55’

DO
BEGIN
CALL testss(); //或自定义SQL语句
END$$

DELIMITER ;
MySql数据库设计 — 三大范式
第一范式:确保每列的原子性.
如果每列(或者每个属性)都是不可再分的最小数据单元(也称为最小的原子单元),则满足第一范式.
例如:产品信息表(产品名、产品价格、库存量、产品类别),其中产品类别还可以细分为(产品大类、产品子类)
第二范式:在第一范式的基础上,目标是确保表中的每列都和主键相关.
如果一个关系满足第一范式,并且除了主键以外的其它列,都依赖于该主键,则满足第二范式.
例如:订单表(订单编号[主键]、产品ID、订购时间、订购数量、总价),其中产品ID和订单编号没有关系,不依赖于主键,则不满足第二范式。
第三范式:在第二范式的基础上,确保每列都和主键列直接相关,而不是间接相关.
如果一个关系满足第二范式,并且除了主键以外的其它列都不依赖于主键列,则满足第三范式.
假设A、B和C是关系R的三个属性,如果A-〉B且B-〉C,则从这些函数依赖中,可以得出A-〉C,如上所述,依赖A-〉C是传递依赖,而不是直接依赖。
例如:订单表(订单编号,定购日期,顾客编号,顾客姓名,……),初看该表没有问题,满足第二范式,每列都和主键列”订单编号”相关,再细看你会发现”顾客姓名”和”顾客编号”相关,”顾客编号”和”订单编号”又相关,最后经过传递依赖,”顾客姓名”也和”订单编号”相关。为了满足第三范式,应去掉”顾客姓名”列,放入客户表中。
MYSQL事务
MySQL 事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务!
在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务;
事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行;
事务用来管理insert,update,delete语句。
事务是必须满足4个条件(ACID)Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)
1、事务的原子性:一组事务,要么成功;要么撤回。
2、稳定性 : 有非法数据(外键约束之类),事务撤回。
3、隔离性:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。
4、可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得, innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。

原创粉丝点击