MySQL学习笔记3——MySQL进阶操作

MySQL进阶

多表查询

连接查询,将两个或两个以上的表按照某个条件连接起来,从中选取需要的数据,通过表中有意义相同的字段连接起来,分内连接和外连接内连接,表中有意义相同的字段使用=,或者>,<,!=等作为连接条件连接两表    select * |col1,col2,...etc         from tb1 [inner] join tb2 on tb1.coln =| !=| >| < tb2.colm        [where where_definition ]    或者使用,连接,where接连接条件    select  * |col1,col2,...etc from tb1,tb2 where tb1.coln =| !=| >| < tb2.colm外连接,分为左连接,右连接,大体同内连接,区别在做左连接会查询左表所有记录查询右边匹配记录,而右表查询右表所有记录只查左表所有记录,    左连接select col1,col2,...etc  from tb1 left join tb2 on tb1.coln = tb2.colm     右连接select col1,col2,...etc  from tb1 right join tb2 on tb1.coln = tb2.colm 复合连接查询,多个表连接查询    select * |col1,col2,...etc         from tb1 [inner] join tb2 on tb1.coln =| !=| >| < tb2.colm                 [inner] join tb2 on tb3.coln =| !=| >| < tb3.colm        [where where_definition ]

子查询,将一个查询语句作为子句嵌套到另一个查询语句中,子查询语句的结果作为外层查询到的条件     select * |col1,col2,...etc from tb1      where [coln] >|<|=|!=|[not]in |[not] exists |any |all (select col1,...form tb2);常用的子查询条件:比较运算符:=,!=,>,<,[not]in, [not]exists ,子查询有结果则where为真,执行外层查询,反之则反any,外层where条件满足任何一个子查询的结果,则条件满足,any一般和比较运算符一起用all,外层where条件满足所有一个子查询的结果,则条件满足,all也是一般和比较运算符一起用

合并查询,将多个select查询结果合并到一起union,将多个select查询结果合并到一起,并去掉重复值union all,将多个select查询结果简单合并到一起,不会去掉重复值

别名(as)表别名：当表的名称特别长时，在查询中直接使用表名很不方便，这时可以为表取一个别名，用别名来代替表的名称    SELECT * FROM salgrade as s where s.grade>2;字段别名：查询数据时，默认的情况下MySQL会显示创建表是定义的列名或是加函数得字段名,可加直观的名字来表示这一列     SELECT sum(coln) as sum(总数) FROM salgrade;as 可以省略,如table_name tb   ||    column col

视图

视图(view),可视化表,是一个、多个表,或者是从已存在的视图导出的虚拟表,其行为和表很相似,可以使用select,delete,update的操作.视图的作用简单化：那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件安全性：通过视图用户只能查询和修改他们所能见到的数据逻辑数据独立性：视图可以帮助用户屏蔽真实表结构变化带来的影响

创建视图(CREATE VIEW)    create [algorihm={undefined | meegee |temptable}]    view view_name(column_list)    as select statement    [with [cascaded | local ] check option]查看视图    show tables ;   //查看当前数据库里的所有表,包括虚拟表---视图    describe(desc) view_name;   //查看视图结构    show create view view_name; //查看视图创建语句    show table status like '%view_name%'; //like 跟匹配字符,可模糊查询所需的视图    select *  from information_schema.views;//查看所有的视图    //MySQL将所有的视图信息存放在数据库information下的views表内,包括系统和自建的视图修改视图create or replace [algorihm={undefined | meegee |temptable}]    view view_name(column_list)    as select statement    [with [cascaded | local ] check option]//若视图不存在,则创建(create),若存在则替换(replace)alter [algorihm={undefined | meegee |temptable}]    view view_name(column_list)    as select statement    [with [cascaded | local ] check option]删除视图drop view if exists view_name [restrict | cascade]

更新视图数据    update view_name    set col1=val1,col2=val2,...    where where_definition删除视图数据    delete from view where where_definition视图更新删除数据时,会把它对应基本表的上的信息也更新/删除掉    //插入也是,但视图字段一般不全,插入不进去,字段全的视图没啥意思以下情况视图无法删除    视图中包含sum(),count(),max(),min()等函数    视图中包含union[all],distinct,group by,having 等关键字    视图中包含子查询    有不可更新视图导出的视图    创建视图时,algorihm设为temptable(临时表)的    视图对应的表上存在没有默认值的列,而且该列没有包含在视图里

事务

事务是一种运行机制,由一组数据库操作命令组成的操作序列,具有以下特性    原子性(atonmicity),事务是一个不可分割的逻辑单元,一组SQL语句要么全部执行,要么全部不执行    一致性(consistency),事务发生前后,数据具有一致性    隔离性(isolation),事务执行过程是不可见的    持久性(durability),事务一旦提交,则不可撤销    Atonmicity+Consistency+Isolation+Durability=ACID事务语句:    start transaction;|begin;   //开始事务    commit; | rollback;         //提交/回滚事务MySQL的事务是由存储引擎决定的    MyISAM:不支持事务,用于只读程序提高性能    InnoDB:支持ACID事务

表设计

构造数据库(表)必须遵循一定得规则,在关系型数据中这些规则称为范式第一范式(1NF):原子性,即字段不能或者不应该再被分割,所有关系型数据库都必须遵循第一范式第二范式(2NF):用单一主键标记整条记录,不要也不能出现多个主键第三范式(3NF):先满足前两个范式,若不能则可新建一个表    规范化的表中没有重复的数据,但也不能为了遵守范式而非要拆分为多个表,有事本分重复可提高效率需求处理    a,收集分析,用户在数据管理中的信息要求,处理要求,安全性要求,完整性要求;整理出数据字典        数据字典:数据项---数据结构---数据流---数据存储---处理过程        简版数据字典:字段名---数据类型---主键/外键    b,ER图(Entily Relationship Digram),即需求分析---逻辑设计        一对多,多对一,(1:N N:1)        一对一,(1:1)        多对多,(M:N) 通过设置中间表转为多个一对多关系(1:M M:N N:1)

主键/外键关联

主键关联:通过两个表的主键建立关联联系,要求两个表的主键必须完全一致

外键关联:通过表(子表)的一列参照关联另一表的(父表)主键,形成的参照关系,称为外键关联,用来保证数据表的完整性和一致性    a,被引用的主键表通常称为父表    b,外键与它引用的主键名可不相同    c,外键值不要求唯一外键的创建,建表时创建:    CREATE TABLE tb_name(col1,col2,...coln, foreign key (colm) references parent_tb_name(coli);        在已建好的表上添加: ALTER TABLE tb_name add foreign references tb2(coli) ;删除外键,alter table tb1 drop foreign key key_name;     //创建时未定义键名时,可用show create table 语句查看外键名    有了外键之后，两个表之间的记录有严格的约束关系,在插入子表时,外键列不许有值,或是NULL,在删除父表记录时,要保证父表该记录主键的    值没有对应子表的外键存在,否则无法删除,当然这些关系可以通过以下关键词调整:    on delete | update     cascade | set null | restrict | no action    cascade  父表删除时,子表删除所对应外键的记录    set null 父表删除时,子表设置所对应外键的记录字段为NULL    restrict同no action 拒绝删除/更新操作(默认值)外键需要注意的细节:外键指向的字段必须是primary key 或者unique                 子表和父表的存储类型必须都是InnoDB,否则不支持外键                 外键字段的值必须在主键中出现过,或者为NULL

索引

索引是一种特殊的数据结构(可称为加在表上的索引列),用来快速查询数据表中的特定记录,能大幅提高数据库性能常见索引:普通索引,index         唯一索引,unique [index] ,索引列的值必须唯一,可以有null        主键索引,primary key ,特殊的唯一索引,不允许有空,null        全文索引,fulltext [index],全文索引,只能用在char/varchar/text类型的列上索引设计原则    选择唯一索引    为经常需要排序,分组和联合操作的字段建立索引    为常作为查询条件的字段建立索引    限制索引的数目    尽量使用数量少的字段作为索引    删除不再或者很少使用的索引

创建索引在建表是创建索引,create table tb_name(        col1,        col2,        ...        [unique|fulltext] index index_name(col1[,colm])           //给col1加上名为index_name的索引,也可创建多列索引);eg:    create table tb_name(        id int(10) not null primary key auto_increment,        name varchar(20) not null unique,        age int(3),        unique index index_name(name)           //给colname加上名为index_name的唯一索引);创建多列索引    create table tb_name(        id int(10) not null primary key auto_increment,        name varchar(20) not null unique,        age int(3),        unique index index_name(name,age)           //在name,age加上名为index_name的多列唯一索引);已建表上创建索引    1, create [unique|fulltext] index index_name on table_name (col_name);    2,alter table table_name add [unique|fulltext] index index_name(co_name);    3,alter table table_name add primary key (id);    //给字段id加主键索引,不能也不用给主键去索引名删除索引    drop index index_name on tb_name;    alter table tb_name drop index index_name;使用索引的注意事项    a,查询索引时不要使用not,<>,和!=;    b,查询时,条件中带有or时,索引是不起作用的    c,用索引时,不建议使用like语句,当like条件以"%"开头时,索引不会起作用    d,列中null值不会包含在索引中,所以定义列是一般都是设置为not null,默认值为空字符串或者0    e,过于集中的值不要使用索引,例如性别    f,不要过度使用索引,索引会带来更新,删除,插入动作的变慢(每次都要更新索引)