MySQL学习笔记----创建修改删除表

数据类型

mysql中什么数据类型可以存储路径?
char, varchar, text, 等数据类型都可以存储路径。但是如果路径中使用”\”符号就会被过滤，所以应该使用”/”或”\”替换”\”。
其中varchar类型的长度是在范围内可变的，因此占用的空间比char类型小。虽然如此char处理的速度比varchar快，对变化长度不大，而查询速度要求较高的字符串类型，最好选择char类型。

mysql如何使用布尔类型？
为了支持SQL标准，定义了bool和boolean类型，但是最后转换成tinyint(1)。在mysql中，布尔类型等同于tinyint(1)。如果在创建表时字段定义成”bool”或”boolean”，数据库的真实定义是”tinyint(1)”.

mysql中如何存储jpg图片和mp3音乐？
数据库不宜直接存储图片和音频文件，而是存储图片和音频文件的路径。实在需要的话，可以考虑用blob类型，因为blob类型可以用来存储二进制文件。

操作数据库

创建数据库
create database 数据库名;
显示已经存在的数据库
show databases;
删除数据库
drop database 数据库名;

存储引擎
存储引擎就是指表的类型，数据库的存储引擎决定了表在计算机中的存储方式。
查询方法为：
show engines;
mysql 支持的存储引擎包括：MyISAM、 MEMORY、InnoDB、ARCHIVE、MRG_MYISAM
其中InnoDB是默认的存储引擎。

查询默认的存储引擎：
show variables like ‘storage_engine’;

InnoDB 存储引擎
提供了事务、回滚、崩溃修复能力和多版本并发控制的事务安全。支持自动增长序列AUTO_INCREMENT,自动增长列的值不能为空，且值必须唯一，自动增长的列必须为主键。
优点：提供了良好的事务管理、崩溃修复能力和并发控制
缺点：读写效率较差，占用的数据空间相对比较大。

MyISAM 存储引擎
曾经是MySQL默认的存储引擎，存储3个文件，文件名字和表名相同，frm（扩展名的文件存储表的结构）、MYD(MYData，扩展名的文件存储数据)、MYI（MYIndex的缩写,扩展名的文件存储索引）。
优点在于：占用空间小，处理速度快，缺点在于不支持事务的完整性和并发性。

MEMORY存储引擎
使用存储在内存中的内容来创建表，每个基于MEMORY存储引擎的表实际上对应一个磁盘文件，该文件的文件名和表名相同，类型为frm类型，该文件中只存储表的结构，而数据类型都存储在内存中。所以，服务器需要有足够的内存来维持MEMORY存储引擎表的表的使用。
优点：默认使用HASH索引，其速度比B型树索引快。这类表处理速度非常快。
缺点：数据易丢失，生命周期短。数据安全性不高，只使用相对较小的数据库表。

创建、修改和删除表

创建表：

create table 表名(属性名 数据类型 [完整性约束],
                            属性名 数据类型 [完整性约束],
                            ...
                            );

设置表的主键

单字段主键，主键是单字段时，直接在该字段后面加上PRIMARY KEY。

create table example(stuid INT PRIMARY_KEY,
                                    stu_name VARCHAR(20)，
                                    stu_sex  BOOLEAN）；

多字段主键，主键由多个属性组合而成，在属性定义完后统一设置主键

create table example2(stu_id INT,
                                    course_id INT,
                                    grade FLOAT,
                                    PRIMARY KEY(stu_id,course_id); 
                                );

设置表的外键

外键是表的一个特殊字段，如果字段sno是表A的属性，且依赖于表B的主键，那么表B为父表，表A为子表，sno为表A的外键。
外键的作用是建立该表与其父表的关联关系。父表中删除某条信息时，子表中与之对应的信息也需要相应改变。
比如stu_id是student表的主键，同时是grade表的外键。如果stu_id 为123的人退学，需要从student表中删除该学生的信息，grade表中stu_id为123的信息也需要相应删除。

constraint 外键别名 foreign key(属性1.1，属性1.2，...,属性1.n)
                 references 表名(属性2.1，属性2.2，...,属性2.n)

举个例子：

create table example3(id INT PRIMARY KEY,
stu_id INT,
course_id INT,
CONSTRAINT c_fk FOREIGN KEY(stu_id, course_id）
REFERENCES example2(stu_id,course_id)
);

stu_id和course_id字段为外键，c_fk为外键的别名，examples3依赖于父表的example3的主键 stu_id和course_id.
外键关联的必须是父表的主键，而且数据类型必须一致

设置表的非空约束

保证所有数据该字段为有值，否则数据库系统会报错。

 属性名 数据类型 NOT NULL

设置表的唯一约束

唯一性是指所有记录中该字段的值不能重复出现，比如在id字段加上唯一性约束，那么每条记录id的值都是唯一的，不能出现重复的情况。

create table example5(id INT PRIMARY KEY,
                      stu_id INT UNIQUE,
                      name VARCHAR(20) NOT NULL);

设置表的属性值自动增加

为表中插入的新记录自动生成唯一的ID。一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须为主键的一部分。

create table example6(id INT PRIMARY KEY AUTO_INCREMENT,
                                    stu_id INT UNIQUE,
                                    name VARCHAR(20) NOT NULL); 

id字段为主键，且插入一条新记录id的值会自动增加，stu_id字段为唯一值，该字段的值不能重复，name为非空字段。

设置表的属性的默认值

create table example6(id INT PRIMARY KEY AUTO_INCREMENT,
                      stu_id INT UNIQUE,
                      name VARCHAR(20) NOT NULL
                      English VARCHAR(20) DEFAULT  'zero'
                      Computer FLOAT DEFAULT 0);    

查看表基本结构
describe example1;
也可以用它的缩写形式：
desc example1;

查看表详细结构:
show create table 表名;

在后面加上”\G”,可以更加美观的显示内容。

修改表

修改表名

修改表名是通过SQL语句ALTER TABLE实现的。其语法形式如下：
alter table 旧表名 rename [to] 新表名;

修改字段的数据类型

alter table 表名 modify 属性名 数据类型;

ALTER TABLE user MODIFY name VARCHAR(30);

修改字段名

ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型;

增加字段

ALTER TABLE 表名 ADD 属性名1 数据类型 [完整性约束条件] [FIRST|ALTER 属性名2];

默认情况下，新增字段为表的最后一个字段，如果加上FIRST参数，可以将新增字段设置为表的第一个字段.

ALTER TABLE user ADD num INT(8) PRIMARY KEY FIRST；

同理，在user表的phone字段后增加address字段。

ALTER TABLE user ADD address VARCHAR(30) NOT NULL AFTER phone;

删除字段

ALTER TABLE 表名 DROP 属性名;

从user表中删除id字段：

 ALTER TABLE user DROP id;

修改字段的排列位置

指定属性名1插入在属性名2之后。
ALTER TABLE 表名 MODIFY 属性名1 数据类型 FIRST|AFTER 属性名2；

字段修改到指定位置。
ALTER TABLE user MODIFY sex TINYINT(1) AFTER age;

更改表的存储引擎

ALTER TABLE 表名 ENGINE=存储引擎名

删除表的外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键别名；

例如上式中：

alter table exmaple3 drop foreign key c_fk;

---

参考文献
mysql 入门很简单