MySQL基础(二)

MySQL基础(二)

数据表的引擎对比

MySQL支持MyISAM(速度快、查询多) ,InnoDB(安全、事务处理), HEAP, BDB, ARCHIVE, CSV等多种数据表引擎类型

1.InnoDB 是较新的事务安全型存储引擎，用于事务处理应用程序，支持BDB的几乎所有特性，并具有众多新特性，包括ACID事务支持。

InnoDB表类型 安全

特点: InnoDB给MySQL提供了具有提交,回滚和崩溃回复能力的事务安全存储引擎.InnoDB也支持外键机制,InnoDB类型的表与去他MySQL的表类型混合起来,在同一查询中也可以混合

	缺点: InnoDB数据表的空间占用量要比同样内容的MyISAM数据表大很多,另外,该类型不支持全文索引

特性：

1.事务处理机制
2.支持外链
3.崩溃后能立即恢复
4.支持外键功能，级联删除
5.支持并发能力
6.在硬盘上的存储方式：InnoBDB frm
7.最新版本的Mysql已经计划移除对BDB的支持，转而全力发展InnoDB。InnoDB对Mysql有更好的特性支持，而且开发社区活跃。

2.MyISAM 是默认的MySQL插件式存储引擎，它是基于ISAM类型，但它增加了许多有用的扩展，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。注意，通过更改STORAGE_ENGINE配置变量，能够方便地更改MySQL服务器的默认存储引擎

MyISAM表类型

优点：

1.比ISAM表更小，所占资源更少
2.可以在不同平台间二进制移植表的类型在创建表时指定。	

特点: 成熟,稳定,和易于管理,他使用一种表格锁定的机制来优化多个并发的读/写操作,MyISAM强调了快速的读写操作,所以在web前端会有大量的数据要进行读取操作

MYISAM和InnoDB的总结: 如果希望以最节约空间和时间挥着响应速度快的方式来管理数据表,MyISAM数据表就应该是首选,如果应用程序需要用到事务,使用外键或需要更高的安全性,以及需要允许很多用户同时修改某张数据表的数据,那么InnoDB更值得考虑

关于存储引擎的命令

查看默认的引擎

show variables like 'default_storage_engine';

查看支持的引擎

show engines

查看当前库里面所有的表引擎

show table status from 库名

修改表引擎

alter table 表名 engine = 新的表引擎

查看当前选择的数据库

select database()

查看当前的数据库的版本号

select version()

创建一个表结构和另一个表一样的结构

create table t3 like t1;

那么现在t3表的结构和t1表的结构是一样的,但是要注意数据是没有的

数据表创建索引

索引在数据库开发中起着非常重要的作用,通过在表字段中建立索引可以优化查询,确保数据的唯一性.并且可以对任何全文索引字段中大量文本的所搜进行优化,在MySQL中有4类索引:主键索引(primary key)、唯一索引(UNIQUE)、常规索引(INDEX)、全文索引(FULLTEXT).

查看表中都有哪些索引

show index from 表名\G

如果在创建索引的时候,不添加索引名的话 默认会把字段名当做索引名

常规索引

添加表字段的常规索引
    create index 索引名 on 表名(字段名)
    alter table 表名 add index 索引名(字段名)    

在创建表的时候也可以进行添加
    create table t2(
            id int(10) unsigned auto_increment primary key,
            name varchar(255), index  索引名(字段名)
        );

删除表字段常规索引
    drop index 索引名 on 表名
    alter table 表名 drop index 索引名

唯一索引

添加表字段的唯一索引
    create unique index 索引名 on 表名(字段名)
    alter table 表名 add unique (字段名)

在创建表的时候也可以进行添加
    create table t2(
            id int(10) unsigned auto_increment primary key,
            name varchar(255), unique  索引名(字段名)
        );

删除唯一索引
    drop index 索引名 on 表名

主键索引

添加表字段的主键索引
    alter table 表名 add  primary key (字段名)
添加自增
    alter table 表名 modify id int(4) auto_increment
删除主键索引
    如果字段有了auto_increment 和 primary key的时候,需要删除主键的话,
    先删除自增,然后再删除主键    
    删除自增
    	alter table 表名 change 字段名  字段名 类名
    删除主键
    	alter table 表名 drop primary key 

在数据库中唯一索引和主键索引的区别

唯一索引是可以给每个字段进行添加的,添加完了之后字段里面的值就不可以重复了,主键索引和唯一索引类似,但是数据表里的主键索引只能加在一个字段里(一般都加在id上),id是自增的,索引不会有重复的时候出现

全文索引 只支持英文分词(只能支持myisam、text字段)

添加表字段的全文索引

alter table 表名 add fulltext (字段名)

删除全文索引

drop index (索引名) on 表名
alter table 表名 drop index 索引名

修改表的字段信息

	修改表的字符集
			alter table 表名 character set utf-8
	修该字段的类型
			alter table 表名 modify 字段名 字段类型 
	修改字段名字的同时也修改字段类型
			alter table 表名 change 旧字段名 新字段名 新字段类型
	修改字段的字符集
			alter table 表名 modify 字段名 类型 character set utf8
	添加新字段
			alter table 表名 add 字段名 类型
	删除字段
			alter table 表名 drop 字段名
	修改表名
			alter table 旧表名 rename as 新表名										

左连接、右连接、内连接

left join(左联接) 返回包括左表中的所有记录和右表中关联字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中关联字段相等的记录
inner join(等值连接) 只返回两个表中关联字段相等的行

举例:
left join
	select * from p1 left join p2 on p1.id = p2.id;

right join
	select * from p1 right join p2 on p1.id = p2.id;

inner join
	select * from p1 inner join p2 where p1.id = p2.id;

子查询

1.单行子查询
    select name,age,score
    from user
    where age=(select age from stu where id = 1)；
2.多行子查询
	select name,age,class 
	from user 
	where age in (select age from stu where id < 10);
	
	select name,age.class 
	from user 
	where age in (select age from stu where name like '李%');	

插入数据

1. 插入指定字段
	insert into t1(字段1,字段2,字段3...) values ('值1','值2','值3',....);
2. 插入所有的字段
	insert into t1 values ('值1','值2','值3',....);
3. 插入多条数据
	insert into t1(字段1,字段2,字段3...) values ('值1','值2'),('值1','值2')....;
4. 插入结果
	insert into t1(字段1,字段2,字段3...) select 字段1,字段2,字段3... from t1;
	insert into t1 select * from t1;
5. 插入单条数据
	insert into t1 set 字段1=值1,字段2=值2....;

分组group by

1. 查询各个班级中年龄大于20，性别为男的人数姓名和班级 (数据表: stu) 
 为了防止班级里面有重名的现象发生(例如 两个男生都是20岁 以上的)  所以会有count(*)

  	select group_concat(name),classid,count(name) from stu where sex = "男" and age > 20 group by classid; 
  	
2. 学校评选先进学生,要求平均成绩大于等于90分的学生,并且语文课必须在95分以上，请列出有资格的学生  (数据表: score)

  	select classid,name from sc where (yw+sx+en)/3 >= 90 and yw>=95 group by classid,name;
     
    select classid,group_concat(name) from sc where (yw+sx+en)/3>=90 and yw>=95 group by classid;

3. 用一条sql语句查询出每门课都大于80分的学生姓名 (数据表: courseinfo)
   	select name from courseinfo group by name having min(score) > 80;

  	select DISTINCT  name from courseinfo where name not in (select distinct name from courseinfo where score < 80);