存储引擎
MyISAMMySQL 5.0 之前的默认数据库引擎,最为常用。拥有较高的插入,查询速度,但不支持事务
InnoDB事务型数据库的首选引擎,支持ACID事务,支持行级锁定, MySQL 5.5 起成为默认数据库引擎
——MyISAM表中在插入式整个表被锁定.InnoDB只锁定该记录.所有如果执行大量的update和insert,应该使用InnoDB;如果主要用来执行select,应该是MyISAM.
BDB源 自 Berkeley DB,事务型数据库的另一种选择,支持Commit 和Rollback 等其他事务特性
Memory所有数据置于内存的存储引擎,拥有极高的插入,更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在 MySQL 重新启动时丢失
Merge将一定数量的 MyISAM 表联合而成一个整体,在超大规模数据存储时很有用
Archive非常适合存储大量的独立的,作为历史记录的数据。因为它们不经常被读取。Archive 拥有高效的插入速度,但其对查询的支持相对较差
Federated将不同的 MySQL 服务器联合起来,逻辑上组成一个完整的数据库。非常适合分布式应用
Cluster/NDB高冗余的存储引擎,用多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大,安全和性能要求高的应用
CSV: 逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个 .csv 文件。这是一种普通文本文件,每个数据行占用一个文本行。CSV 存储引擎不支持索引。
BlackHole:黑洞引擎,写入的任何数据都会消失,一般用于记录 binlog 做复制的中继
EXAMPLE 存储引擎是一个不做任何事情的存根引擎。它的目的是作为 MySQL 源代码中的一个例子,用来演示如何开始编写一个新存储引擎。同样,它的主要兴趣是对开发者。EXAMPLE 存储引擎不支持编索引。
mysql数据类型
(1) 数值类型
(2)字符串类型
(3)日期和时间型
sql语言
约束
约束类型:主键默认值唯一外键非空
关键字:
PRIMARY KEY
DEFAULT
UNIQUE
FOREIGN KEY
NOT NULL
主键(PRIMARY KEY)是用于约束表中的一行,作为这一行的标识符,在一张表中通过主键就能准确定位到一行,因此主键十分重要。主键要求这一行的数据不能有重复且不能为空。
还有一种特殊的主键——复合主键。主键不仅可以是表中的一列,也可以由表中的两列或多列来共同标识
默认值约束(DEFAULT)规定,当有DEFAULT约束的列,插入数据为空时该怎么办。
DEFAULT约束只会在使用INSERT语句(上一实验介绍过)时体现出来,INSERT语句中,如果被DEFAULT约束的位置没有值,那么这个位置将会被DEFAULT的值填充
唯一约束(UNIQUE)比较简单,它规定一张表中指定的一列的值必须不能有重复值,即这一列每个值都是唯一的。
当INSERT语句新插入的数据和已有数据重复的时候,如果有UNIQUE约束,则INSERT失败.
外键(FOREIGN KEY)既能确保数据完整性,也能表现表之间的关系。
一个表可以有多个外键,每个外键必须REFERENCES(参考)另一个表的主键,被外键约束的列,取值必须在它参考的列中有对应值。
在INSERT时,如果被外键约束的值没有在参考列中有对应,比如以下命令,参考列(department表的dpt_name)中没有dpt3,则INSERT失败
非空约束(NOT NULL),听名字就能理解,被非空约束的列,在插入值时必须非空。
在MySQL中违反非空约束,不会报错,只会有警告.
CREATE DATABASEmysql_shiyan;usemysql_shiyan;CREATE TABLEdepartment
(
dpt_nameCHAR(20) NOT NULL,
people_numINT(10) DEFAULT '10',CONSTRAINT dpt_pk PRIMARY KEY(dpt_name) ##约束名 dpt_pk 、主键约束
);CREATE TABLEemployee
(
idINT(10) PRIMARY KEY,
nameCHAR(20),
ageINT(10),
salaryINT(10) NOT NULL,
phoneINT(12) NOT NULL,
in_dptCHAR(20) NOT NULL,UNIQUE(phone),CONSTRAINT emp_fk FOREIGN KEY (in_dpt) REFERENCESdepartment(dpt_name) ##外键约束
);CREATE TABLEproject
(
proj_numINT(10) NOT NULL,
proj_nameCHAR(20) NOT NULL,
start_date DATENOT NULL,
end_date DATEDEFAULT '2017-08-15',
of_dptCHAR(20) REFERENCESdepartment(dpt_name),CONSTRAINT proj_pk PRIMARY KEY(proj_num,proj_name)
);
示例
表的操作
1、创建表
describe 表名
3、查看表结构
showcreate table 表名
showcreate table 表名 \G
4、修改表名
alter table 旧表名 rename [to] 新表名;
5、修改字段的数据类型
alter table 表名modify 属性名 数据类型;
6、修改字段名
alter table 表名 change 旧属性名 新属性名 [新数据类型];
7、增加字段
alter table 表名add 属性名1 数据类型 [完整性约束条件] [first]after 属性名2;
8、删除字段
alter table 表名drop 属性名;
9、删除关联表
(1)删除表的外键约束外键是一个特殊字段,其将某一个表与其父表建立关联关系。在创建表的时候,外键约束就已经设定好了。去掉他们之间的关联关系需要用到下面语句。
alter table 表名drop foreign key 外键别名;
(2)删除没有被关联的普通表
drop table 表名;
(3)删除被其他表关联的父表
当删除有关联关系的表时,用drop table example1 会报错,原因是有外键依赖于该表
例如创建了一个example4表依赖于example1表,example4表的外键stu_id依赖于example1表的主键。example1表时example4表的父表。
如果要删除example4表,必须先去掉这种依赖关系。最简单的办法是先删除子表example4,然后删除父表example1。但这样可能会影响子表的其他数据。
另一种方法是先删除子表的外键约束,然后删除父表。这种方法不会影响子表的其他数据,可以保证数据库的安全。
比如,example4表的外键别名是d_fk,删除example4的外键约束
alter table example4drop foreign key d_fk;
可以通过show create table example4 \G来查看是否已删除。
然后再执行 drop table example1;
执行成功则表示操作成功。
帮助
\h 查看帮助
\s 查看当前环境
\c 废弃当前输入命令
创建用户并授权
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
GRANT {ALL | SELECT | INSERT | UPDATE } ON databasename.tablename TO 'username'@'host'
工作中使用的示例:
BEGIN
DECLARE win_id VARCHAR(20);DECLARE win_count VARCHAR(20);SELECT id FROM windows_vps_log ORDER BY id DESC LIMIT 1 intowin_id;SELECT count(id) FROM windows_vps_log intowin_count;IF win_count>5000000 THEN
delete from windows_vps_log where id <= win_id-3000000;END IF;END
存储过程
SELECTmachine_host,
CONCAT(lan_ip,':', `port`) AS"问题机",
dial_timeFROMclient_infoWHEREmacIN(SELECTmacFROMwindows_vps_logWHEREinvalid= 0
AND TIMESTAMPDIFF(MINUTE, create_time, now()) > 180)ORDER BYmachine_hostdesc;
多表查询
SELECTc.province,
b.provinceid,
b.city,
b.cityid,count(a.dial_type) AS"上独下混"FROMclient_info a,
area_cities b,
area_provinces cWHEREa.machine_area=b.cityidand b.provinceid=c.provinceidGROUP BY b.city,a.dial_type;
多表复杂查询
mysql日志类型:
1.错误日志 记录启动、运行或停止mysqld时出现的问题
log-error=d:/mysql_log_err.txt
2.查询日志 记录建立的客户端连接和执行的所有语句(包括错误的)。
log=d:/mysql_log.txt
3、二进制日志 记录所有更改数据的语句、还用于主从复制
log-bin=d:/mysql_log_bin
4.慢日志 记录所有执行时间超过long_query_time秒的所有查询
long_query_time =1 #时间
log-slow-queries= d:/mysql_log_slow.txtmy:
mysql不区分表名大小写:
1、用root登录,修改 /etc/my.cnf;
2、在[mysqld]节点下,加入一行: lower_case_table_names=1
3、重启MySQL即可;
修改表的字符编码:
alter table `tablename` convert to character set utf8;