MySQL重点知识归纳整理

数据库管理系统：按照一定的数据模型科学的组织和存储数据，提供数据的高效获取与维护。

定义语言DDL操纵语言DML数据库的运行与管理，数据库的建立和维护功能，提供方便，有效存取数据库信息的接口和工具。

数据库管理员DBA数据库系统：数据库，数据库管理系统，应用开发工具，应用系统，数据库管理员和用户构成。

模式（逻辑模式或概念模式）：全体数据的逻辑结构和特征的描述。

外模式（子模式或用户模式）是模式的子集。

内模式（存储模式DDL）对数据库物理结构和存储方式的描述。

数据库的概念结构（逻辑结构和特征的描述）独立于内外模式。

内外模式依赖概念模式，独立于存储设备。

数据库设计的6阶段，需求分析，概念结构设计，逻辑结构设计，物理结构设计，数据库实施，数据库运行与维护。

概念（信息结构）通过数据模型（逻辑）Create database db_test;use db_test;Create table content(content_id int primary key,subject varchar(200),words varchar(1000),username varchar(50),face varchar(50),email varchar(50),createtime datetime);Insert into content(content_id,subject,words,username,face,email,createtime) values(null,’mysql问题请教’,’mysql中对数据表的操作有哪些？’,’mysql初学者’,’face.jpg’,’tom@’,now());Update content set words=‘如何使用insert语句?’where uername=’mysql 初学者’;修改数据库Alter database db_testDefault character set gb2312字符集Default collate gb2312;校对规则查看Show databases;更新表Alter table db_test add column userdz char(10) not null default ‘wuhan’ after face;修改列名和数据类型Alter table db_test change column sex新face 旧char(1) null default ‘m’;删除和修改指定列的默认值Alter table db_test alter column userdz set defaullt ‘beijing’;修改指定列的数据类型而不影响列名Alter table db_test modify username char(20) first;Alter tale db drop userdz重命名表Rename table db_test to db;复制结构Create table user like db;结构和数据但是索引和完整性不会复制Create table user as db;Drop table if exists;Show tables;Desc db;删除数据Delete from db where userdz=’beijing’;完全替代userid 相同的数据Replace into db values(..........);Select*from content where username=’mysql初学者’;数据库的查询使用别名Select subject,username as biaoti ,uname from db;对查询结果分析而不是原始数据Select语句替换这些列Select uname case when sex=’m’then’女’else ‘男’ end as ‘性别’from db; Select 语句也可以输出队列值计算之后的值Select uname,face, userid+100 from db;连接表1）交叉连接笛卡尔积，没有连接即两张表记录的乘积Select *from tb1 cross jion tb2 或者select*from tb1,tb2;2)内连接select smcolumns from tb1 inner join tb2 on smcondition;Select smcolunmns from tb1 straight_join tb2 using smcondition;3)相等连接一对一关系的两张表on *=*4)自连接从表中找具有相同列值的行将表指定两个不同的别名5)自然连接两张表的连接的列在两张表的的名称都相同时采用natural join6)内连接实在交叉连接的结果集上返回满足条件的记录；外连接将表分为基表和参考表，再依据基表返回满足和不满足条件的记录Left outer join right outer join7)like 运算进行字符串匹配中用escape和一个指定的转义字符，使其成为匹配字符而不是通配符select userdz from db where userdz like’%#_%’;1)正则表达式，进行文本串的匹配比较，定义一些复杂过滤条件的要求。

Like是用于匹配整个列，pegexp是在列值内进行匹配如若区分大小写用binarySelect distinct userdz from db where userdz pegexp’市’;2)选择匹配用|分隔相当于多个or select userdz from db where userdz pegexp’北京|武汉|上海’;3)范围匹配[所包含的字符集和数字集]4)特殊字符匹配·表示用于匹配任意字符集在正则表达式中用\\为转义字符5)字符类匹配6)重复匹配*0或多个+一个或多个？0 或一7)定位符匹配^开始$结尾<词的开始>词的结尾Select*from db where userid between903 and912 或者userid in (903,906,908);Select userdz,sex count(*)as’人数’ from db group by userdz,sex;Having 和where相类似，having子句支持where子句所有操作符和句法；但是where主要用于过滤数据行，having过滤分组；having子句可以包含聚合函数where不行；select userdz,uname from db having count(*)<=3; Select userdz,uname from db order by uname abs,userdz desc;Select userid,uname from db order by userid limit 4,3;即从第五位客户开始取3位客户的userid 和uname;Union 语句联合查询和where中多个语句是等同的，但是查询结果允许有重复行时用union all ;索引B-树，哈希HASH,普通，唯一unique,主键，全文fulltext,空间RTREE 常用地理geometry（单列，组合，聚集，覆盖）Create index index_db on db (uname (3) asc);Alter table db add index index_name (uname);Create index index_subject on content(subjiect (3) asc);Create view v_customers-view as select*from db where sex=’m’with check option;Show create view v_name; alter view v_db as selct with check option; Create view content_view as select*from db_contest where username=’mysql初学者’ with check option;一．实体完整性：1）实体完整性主键，候选键unique约束2）参照完整性即定义主码与外码之间的引用规则，某一列reference definition，所有列foreign key，restrict 限制，cascade级联，set null 置空，no action不采取实施。

3）用户定义的完整性，非空，check命名完整性constraint更新必须先删除再增加新约束二．表维护1，analysis table （no_write_to_binlog 取消写入）2，check table(fast quik upgrade changedd medium连接的正确性extended 最慢）3，checksum 4，optimize table 5，repair table （no_write_to_binlog 取消写入，use_frm 用于丢失后重建）Create trigger content_delete_trigger after delete on content for each row set@str=’old content deleted!’;触发器补充练习Tb1增加值时自动增加tb2Drop table if exists tb1;Create table tb1(tb1_id varchar(11);Drop table if exists tb2;Create table tb2(tb2_id varchar(11);Drop trigger if exists t_afterinsert_on_tb1;Delimiter //Create tigger t_afterinsert_on_tb1After insert on tb1For each rowBegin insert into tb2(tb2_id)values(new.tb1_id);End//Delimiter;例2: 创建触发器:t_afterdelete_on_tab1作用：删除tab1表记录后自动将tab2表中对应的记录删去Drop trigger if exists t_afterdelete_on_tb1;Delimiter//Create trigger t_afterdelete_on-tb1After delete on tb1For each rowBeginDelete from tb2 where tb2_id=old.tb1_id;End//Delimiter;假设系统中有两个表：班级表class(班级号classID, 班内学生数stuCount)学生表student(学号stuID, 所属班级号classID)要创建触发器来使班级表中的班内学生数随着学生的添加自动更新，代码如下：Drop table if exists class;Create table class(calssID char(4),stuCount int);Insert into class values(‘0001’,1),(‘0002’,1),(‘0003’,1);Drop table if exists student;Create table student(stuID char(5),classID char(4));Insert into student(‘10001’,’0001’),(‘20001’,’0002’),(‘30001’,’0003’); Drop trigger if exists trg_afterinsert;Delimiter//Create procedure trg_afterinsertAfter insert on studentFor each rowBeginDeclare c int;Set c=(select stuCount from class where classID=new.class ID);Update class set stuCount=c+1 where classID=new.classID;End//Delimiter;事件Create event if not exists event event_delete_contentOn schedule every 1 monthStarts curdate()+interval 1 monthEnds ‘2013-12-31’DoBeginIf year(curdate())<2013 thenDelete from content where username=’mysql初学者’;End if;End//Delimiter;MySQL中常用的数据备份与恢复的常用方法。