MySQL回放_mysql回顾

数据库

1.创建库

create database 库名 [库选项]

2.修改库

alter  database 数据库名称 [库选项]

3.删除库

drop database 库名

创建一张表：

1.创建表

create table 表名(

字段名1 字段类型1 属性，

字段名2 字段类型2 属性，

字段名3 字段类型3 属性

)[表选项];

例如：

create table student(

name varchar(10),

menuber int(5)

)engine=myisam charset=gbk;

默认情况下的存储引擎是InnoDB

表选项：字符集和存储引擎  charset  engine=InnoDB(支持事务)   / myisam(不支持事务)

2.修改表

a.修改表名：rename table 旧表名 to 新表名；

b.修改表选项：alter table 表名 [表选项];

c.修改字段信息：alter table 表名 add(添加字段)/drop(删除字段)/modify(修改字段类型)/change(修改字段名) [选项]

例子：

alter table student add age int(5);

alter table student drop age;

alter table student modify age varchar(10);

alter table student change age mage;

3.删除表

drop table 表名；

4.查看详细的表结构

show create table 表名；

desc 表名；

5.复制表结构创建新表

create table 表名 like 想复制的那个表名；

数据操作

1.新增数据

insert into 表名 [字段列表] values [值列表];

2.更新数据

update 表名 set 字段名=字段值 [,字段名=字段值];

3.查看数据

select [字段列表] from 表名 [where条件];

4.删除数据

delete  from 表名 [where条件];

字段类型

数值型：

整型

tinyint(1个字节)    smallint(2个字节)  mediumint(3个字节)  int(4个字节)  bigint(8个字节)

注意：

在desc 表名   的时候，我们查看到表结构，数值类型的默认会带一个如tinyint(4)  ,这个有什么作用呢？

答：其实这个4只是显示该字段的显示宽度，不代表数据长度。通常要配置0填充才可以显示他的显示宽度

零填充   zerofill    如   alter table 表名 age tinyint unsigned zerofill;   有疑问？0填充一定要无符号的才可以吗？

浮点型

float(4个字节)     double(8个字节)     decimal(M,D)

字符串型

char(255) 最大可以保存255个字符       varchar()  可以保存65532个字节   在gbk编码(汉字的基础上两个字节算一个字符)下可以保存32767个字符

在uft-8编码(两个字节算一个字符)下可以保存21845个字符

时间日期格式:

DATETIME    TIMESTAMP    DATE    TIME    YEAR

DATETIME和TIMESTAMP这两者之间的区别就是，保存的长度不同，DATETIME是从1000-01-01 00:00:00 到9999-12-31 23:59:59

而TIMESTAMP是从1970:01-01 00:00:00 到2038-01-19 03:14:07 结束。

注意上面两种格式必须是0000-00-00 00:00:00 这个格式

DATE 保存的长度是1000-01-01 到9999-12-31  他保存的格式是0000-00-00

TIME 保存的长度是-838:59:59 到 838:59:59   保存的格式00:00:00

YEAR 保存的长度是1901到2155    保存的格式0000    必须是一个年份

create table testTime(

id int auto_increment primary key,

beiginTime datetime not null comment '开始数据'

)charset=utf8;

insert into testTime values(1,'2015-02-13 23:03:30');

alter table testTime add test_timestamp timestamp;

insert into testTime values(2,'2015-02-13 23:03:30','2015-02-13 23:05:30');

alter table testTime add test_date date;

insert into testTime values(3,'2015-02-13 23:03:30','2015-02-13 23:05:30','2015-02-13');

字段属性

null(空)  primary key(主键)    auto_increment(自增长)  default(默认值)    comment(注释)        unique key(唯一键)

思考一个问题：如何把这些属性添加到到字段中去？

在一个已经有数据的表中，是不能够再为他添加主键的，必须先删掉数据才能在已经存在的表里添加主键

添加主键有三种方法：

1.在创建表的时候添加：如

create table student4(

id int auto_increment primary key,

name varchar(50) not null comment '学生名字',

fenshu tinyint not null default 0 comment '学生分数'

)engine=InnoDB charset=utf8;

2.写法和第二种不同而已

create table student4(

id int auto_increment,

name varchar(50) not null comment '学生名字',

fenshu tinyint not null default 0 comment '学生分数',

primary key (id)

)engine=InnoDB charset=utf8;

3.就是在已经存在的表中修改(注意：该表必须没有数据才行)

alter table 表名 modify 字段名 字段类型 primary key;

eg:    alter table student modify id int primary key;

删除主键

因为主键是唯一的，所以删除的时候，直接不需要加到字段名

alter table student drop primary key;

思考一个问题：

索引有什么作用？？？

第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

第四，在使用分组和排序子句进行数据检索时，同样可以显

著减少查询中分组和排序的时间。

第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。 6.1在MySQL中，BTREE，

二叉树 二叉树排序 35 17 39 9 28 65 56 87 6.2索引的优点： 加快查询速度。 6.2 索引的缺点： 占用大量的磁盘空间。但是对插入、删除

和更新有影响。 6.3使用索引时，有以下一些技巧和注意事项：

索引不会包含有NULL值的列 只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是

无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

外键

外键的定义：如果一个表中有一个字段指向另外一个表的主键，那么这个键就称为外键

如何创建外键：

foreign key(字段) references 表名(字段名)

思考一个问题：到底哪个是哪个的外键？看外键的定义就明白了

注意一个问题：

当在一个表中创建一个外键的时候，这个字段不能是该表的主键，如果是主键则创建不成功，会报错

create table class(

id int unsigned auto_increment primary key,

teachername varchar(20) default '',

tid int not null,

foreign key(tid) references student(id)

)charset=utf8;

如何在创建表后创建外键：

alter table 表名 add foreign key(字段名) references 表名(字段名)

不能修改外键

只能删除外键

alter table 表名 drop foreign key 字段名;

注意这里的字段名不是上面的tid了，而是通过show create table 表名;查询出来的，例如上面的例子查出来的是class_ibfk_1,

删除外键的例子：alter table class drop foreign key class_ibfk_1;

外键名是可以控制的，如何在修改的时候添加外键名：

alter table class add constraint 'class_tid' foreign key(tid) references student(id);  //测试会报错，有待验证

alter table class add foreign key(tid) references student(id);

外键有什么作用：限制了外键值的范围，这称为外键约束

父子关系：

父表：被外键指向的表

子表：拥有外键的表

外键约束有三种：严格模式(Restrict)，置空模式(Setnull)，级联模式(Cascade)

严格模式：不允许父表删除与字表关联的记录

置空模式：当删除父表记录时，子表与关联的外键设置成空

级联模式：与之相关的内容都被改变

范式

第一范式：满足原子性，就是字段不可再分割，比如这个字段就没有满足原子性：代课时间：30天

如果要使他满足原子性，就应该分割成‘代课开始时间’和‘代课结束时间’这两个字段

第二范式：在满足第一范式的前提下，不能存在部分依赖

第三范式：在满足第二范式的前提下，不能存在部分传递依赖

高级数据操作：

增：

思路：先从表中复制数据，再将数据插中

标准的语法：

insert into 表名 ([字段名1,字段名2....]) values (值1,值2,...);

高级操作(蠕虫复制)：

insert into 表名 select 字段名 from 需要从哪表中复制数据过来表；

create table testMainAdd(

id int unsigned auto_increment primary key,

name varchar(50) not null

)charset=utf8;

insert into testMainAdd (name) values('铭洋');

高级操作例子：

create table testAdd select * from testMainAdd;

1.思考：当一个表中有数据的时候，他不但能把他的数据复制过去，还能把表结构也复制过去，但是为什么不能把

auto_increment这个属性也复制过去呢？

2.思考：选择部分字段进行蠕虫复制？怎么去实现

答：只要在选择表字段的时候不要把所有的字段都选上就行了

3.思考：如何在复制的时候，想把某个字段重复的记录不要了呢？(去重)

去重(更新)插入(这个是什么意思？)

语法：insert into 表名 values(字段值) on duplicate key update 字段=值；

如果主键是业务逻辑主键：

语法：replace into 表名 (字段1，字段2....) values (字段值1,字段值2....);

eg:

replace into testAdd(id,name,age) values(6,'杨铭洋1',23);

删：

标准语法：delete from 表名 where条件；

高级语法：delete from 表名 [where条件] order by 字段 limit;

说明：delete在删除数据的时候不会改变数据结构和索引

思考：有没有办法重置表呢？

语法：truncate 表名；

truncate的原理：先把表给删了，然后再重新创建表

改：

标准语法：update 表名 set 字段名=字段值 where条件；

高级语法：update 表名 set 字段名=字段值 where条件 [order by条件][limit条件];

查：(最重要的)

标准语法：select * from 表名 where条件；

高级语法：select [select 选项] 字段列表(表达式) from 表名 [where子句][group by子句][having子句][order by子句][limit子句]；

select选项：分别有两个值all(全部)和distinct(去重)，默认情况下的select选项是all(全部)

思考一个问题：这里的去重是不是指完全一模一样的数据才叫重复呢？答案是肯定的

where子句：

in 的使用：where 字段 in(该字段所对应的值)

eg: select * from testAdd where id in(1,3,6);

between方式： where 字段 between 1 and 4;

注意：字段 between 左值(最小值)and 右值(最大值)

最小值和最大值是闭区间

group by子句：

分组的意思

思考：如何去理解分组这个概念呢？

答：按我自己的理解：那就是按某个字段去分，比如name这个字段，那么如果是要分组，name字段里有铭洋，小娇等名字，

那么他就会把铭洋作为一个大列，去分组，那么就会有铭洋，小娇一组一组的出来了。

1.group by子句默认情况下分组后是以升序排序的，我们也可以改变着这排序方式，那就在  group by 字段 desc

2.group by子句支持多字段分组

统计函数：

count()    sum()    avg()

having 子句：

基本上和where条件查询是一样的

与where区别：

1.字段别名，where子句不能在前面命了别名，然后就在where子句后面使用，反之，having子句则可以使用

2.分组函数的使用，where子句后面不能直接跟着group by子句，having子句则可以

order by 子句：

标准语法：order by 字段 asc/desc

排序：升序asc   降序 desc

limit 分页：

标准语法：limit offset,length

(重点)连接查询：

1.内连接查询

语法：inner join 表名 on

概念：只连接两边都相同的行

eg:select A.name,B.address from A inner join B  on A.id = B.A_id;

2.左外连接查询

概念：包含左边表的全部行(不管右边的表中是否存在与他们匹配的行)，以及右边表中全部匹配的行

语法：left join 表名 on

eg:select A.name,B.address from A left join B  on A.id = B.A_id;

3.右外连接查询

概念：包含右边表的全部行(不管左边表中是否存在与他们匹配的行)，以及左边表中全部匹配的行

语法：right join 表名 on

eg:select A.name,B.address from A right join B  on A.id = B.A_id;

4.全外连接查询

概念：在功能上，它等价于对这两个数据集合分别进行左外连接和右外连接，然后再使用消去重复行的并操作将上述两个结果集合并为一个结果集

语法：full join 表名

(注意：不带条件)

eg:select A.name,B.address from A full join B;

5.交叉连接(笛卡尔积)

语法：cross join 表名

(注意：不带条件)

eg:select A.name,B.address from A cross join B;

效率问题：一般情况下，内连接比左外连接查询的效率高