MySQL 基础（1-7）

1. 管理数据库

创建数据库： CREATE DATABASE [IF NOT EXISTS] database_name;

ex. CREATE DATABASE  IF NOT EXISTS testdb; 

ex. CREATE DATABASE testdb;

展示数据库：SHOW DATABASES;

连接到某一数据库：USE database_name;

删除数据库：DROP DATABASE [IF EXISTS] database_name;

CREATE DATABASE IF NOT EXISTS test_database;
SHOW DATABASES;
DROP DATABASE IF EXISTS test_database;

2. MySQL支持的表类型

MyISAM  

5.5以前是MySQL默认表类型，大小能够达到256TB，兼容多平台；

InnoDB    从5.5.开始做为MySQL默认表类型，大小能够达到64TB，支持外键、提交、回滚和前滚的操作，兼容多平台；
MERGE    虚拟表，有多个MyISAM表，没有自己的索引，提高连接多表时的性能， 只能执行SELECT, DELETE, UPDATE 和 INSERT，若执行DROP，只删掉MERGE，下面的表仍然存在；
MEMORY (HEAP)    存在于内存中，hash索引，因此读取速度比MyISAM快，生命周期与数据库服务器起着的时间相关；
ARCHIVE 可存储大量数据，并压缩以节省硬盘空间，insert时压缩，读取时解压， 只执行INSERT 和 SELECT，无索引，读取时全表搜索；
CSV   逗号分隔值文件，能够将数据移植到非SQL应用，比如电子表格，不支持NULL，读取时全表搜索；
FEDERATED  能够远程管理MySQL服务器中的数据，而不需要集群和复制技术，本地FEDERATED表无数据，读取时从远程FEDERATED表提取数据。

ex. create table Employee(id int(3) primary key auto_increment,name varchar(20),age int, salary int, sex char)Engine MYISAM;

3. MySQL数据类型

数值：

Type	Size
TINYINT
SMALLINT
MEDIUMINT
INT
BIGINT
DECIMAL
FLOAT
DOUBLE
BIT

字符：

CHARA fixed-length non-binary (character) string 【ex. sex CHAR(1)，固定长度】
VARCHAR A variable-length non-binary string【ex. name VARCHAR(20) , 变长】
BINARY A fixed-length binary string
VARBINARY A variable-length binary string
TINYBLOB A very small BLOB (binary large object)
BLOB A small BLOB
MEDIUMBLOB A medium-sized BLOB
LONGBLOB A large BLOB
TINYTEXT A very small non-binary string
TEXT A small non-binary string
MEDIUMTEXT A medium-sized non-binary string
LONGTEXT A large non-binary string
ENUM An enumeration; each column value may be assigned one enumeration member
SET A set; each column value may be assigned zero or more set members

时间：

DATEA date value in ‘CCYY-MM-DD’ format 【日期】
TIME A time value in ‘hh:mm:ss’ format
DATETIME A date and time value in ‘CCYY-MM-DD hh:mm:ss’ format
TIMESTAMP A timestamp value in ‘CCYY-MM-DD hh:mm:ss’ format
YEAR A year value in CCYY or YY format

空间：

GEOMETRYA spatial value of any type 任何形式的空间值
POINT A point (a pair of X Y coordinates) 【点，0维】
LINESTRING A curve (one or more POINT values) 【线，1维】
POLYGON A polygon 【面，2维】
GEOMETRYCOLLECTION A collection of GEOMETRY values
MULTILINESTRING A collection of LINESTRING values
MULTIPOINT A collection of POINT values
MULTIPOLYGON A collection of POLYGON values

4. MySQL建表

建表语句：

CREATE TABLE [IF NOT EXISTS] table_name(
        column_list
        ) engine=table_type;

if not exists table_name：当表不存在时，建表，防止在表已存在的情况下，建新表；

column_list：以逗号分隔

engine：选择存储引擎，InnoDB（default）, MyISAM, HEAP, EXAMPLE, CSV, ARCHIVE, MERGE FEDERATED 以及 NDBCLUSTER。

列：

column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] [AUTO_INCREMENT]

列名 Not NULL | NULL：该列是否接受NULL Default Value：该列默认值；Auto_Increment：当新增一行时，该列值增1；每个表只能有一个auto_increment；

例子

CREATE TABLE IF NOT EXISTS tasks (
  task_id int(11) NOT NULL AUTO_INCREMENT,
  subject varchar(45) DEFAULT NULL,
  start_date DATE DEFAULT NULL,
  end_date DATE DEFAULT NULL,
  description varchar(200) DEFAULT NULL,
  PRIMARY KEY (task_id)  #设置主键，主键：not null 且 unique，一个表只能有一个主键，主键不能索引；
) ENGINE=InnoDB; #设置表类型

另一种添加主键的方法：ALTER TABLE tasks ADD PRIMARY KEY (task_id); #更新表结构时，加上主键；

结果：

5. MySQL设置自增长序列

To create a sequence in MySQL automatically, you set the AUTO_INCREMENT attribute to a column, which typically is primary key column.

在MySQL中建立一个自增长序列，将一列设置AUTO_INCREMENT，该列通常是主键；

使用AUTO_INCREMENT规则：

• 每个表只有一个AUTO_INCREMENT，其值通常为int或者float；
• 该列必须为索引，也就是主键或者唯一索引（unique）；
• 该列必须Not NULL；

CREATE TABLE employees(
 emp_no INT(4) AUTO_INCREMENT PRIMARY KEY,
 first_name VARCHAR(50),
 last_name  VARCHAR(50)
)ENGINE = INNODB;

Auto_increment列属性:

• 列开始值为1；
• 使用函数LAST_INSERT_ID()获得最后一个生成的自增长序列号；
• 插入新行并为自增序列赋值时，若值已存在，会产生错误，如果定义一个比Last_Insert更大的值，下一个自增序列值将比当前值大，序列值之间产生跨越；
• 通过update更新序列值，若值存在，产生duplicate-key error，若当前为3，更新为10，下一个值将从4开始；
• 如果删掉其中一行，MyISAM表不会重用已用过删掉的数值，InnoDB会重用；

INSERT INTO employees(first_name,last_name)
VALUES('Jane','Doe'),
      ('Mary','July');

UPDATE Employee
SET first_name = 'Jane',
    emp_no = 1
WHERE emp_no = 2;

6. MySQL主键

• 唯一；
• Not Null;
• 每个表仅有一个主键；

MySQL对Int类型工作的更快；主键一般为INT，BIGINT，如果是TINYINT或SMALLINT，保证数据行数不超出主键范围；
方法一:

CREATE TABLE users(
   user_id INT AUTO_INCREMENT PRIMARY KEY,
   username VARCHAR(40),
   password VARCHAR(255),
   email VARCHAR(255)
);

方法二:

CREATE TABLE roles(
   role_id INT AUTO_INCREMENT,
   role_name VARCHAR(50),
   PRIMARY KEY(role_id)
);

如果主键有多列：

CREATE TABLE userroles(
   user_id INT NOT NULL,
   role_id INT NOT NULL,
   PRIMARY KEY(user_id,role_id),
   FOREIGN KEY(user_id) REFERENCES users(user_id),
   FOREIGN KEY(role_id) REFERENCES roles(role_id)
);

用Alter添加主键：

ALTER TABLE table_name
ADD PRIMARY KEY(primary_key_column);

PRIMARY KEY vs. UNIQUE KEY vs. KEY

KEY为Index的同义词，用于建立新的index；

UNIQUE，该列值必须是唯一的，允许NULL，一个表可以有多个；

ALTER TABLE users
ADD UNIQUE INDEX username_unique (username ASC) ;

7. MySQL外键

CustomerNumber为Orders的外键，Customer表为父表或者referenced table, orders表为子表或者referencing table.

外键还能够在同一个表中创建。

CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action

constraint：定义，约束名和外键约束条件；

foreign key：可定义外键名，或由MySQL生成，column在子表中；

references：父表以及对应列；注：column在父表和子表中的数量一致。

On delete action：在父表中删除在子表中有记录的外键，mysql将根据action决定是否拒绝这一操作；

 ON DELETE CASCADE：父表中记录被删除，子表中的记录也会相对被删除；

ON DELETE SET NULL：父表记录删除，子表记录被设为Null，前提是子表该列接受Null值；

ON DELETE NO ACTION / ON DELETE RESTRICT：MySQL拒绝删除；

On update：当父表数据被更改时，子表的结果；

ON UPDATE CASCADE：允许交叉列表的子表对应行更新；

ON UPDATE SET NULL：父表记录被更新，子表值设为null；

其他，更新被拒绝。

例子，创建表products时添加外键：

CREATE DATABASE IF NOT EXISTS dbdemo;
 
USE dbdemo;
 
CREATE TABLE categories(
   cat_id int not null auto_increment primary key,
   cat_name varchar(255) not null,
   cat_description text
) ENGINE=InnoDB;
 
CREATE TABLE products(
   prd_id int not null auto_increment primary key,
   prd_name varchar(355) not null,
   prd_price decimal,
   cat_id int not null,
   FOREIGN KEY fk_cat(cat_id)
   REFERENCES categories(cat_id)
   ON DELETE RESTRICT
   ON UPDATE CASCADE
)ENGINE=InnoDB;

表已创建，添加外键的语法：

ALTER table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name(columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action

创建新表vendors，并在表products中加入一列vdr_id：

USE dbdemo;
 
CREATE TABLE vendors(
    vdr_id int not null auto_increment primary key,
    vdr_name varchar(255)
)ENGINE=InnoDB;
 
ALTER TABLE products 
ADD COLUMN vdr_id int not null AFTER cat_id;

在表products中添加外键：

ALTER TABLE products
ADD FOREIGN KEY fk_vendor(vdr_id)
REFERENCES vendors(vdr_id)
ON DELETE NO ACTION
ON UPDATE CASCADE;

现在表products中有两个外键。

删除外键语法：

ALTER TABLE table_name 
DROP FOREIGN KEY constraint_name

查看constraint_name：

SHOW CREATE TABLE table_name

禁止外键检查：

SET foreign_key_checks = 0

参考：

MySQL Tutorial：http://www.mysqltutorial.org/

浅谈MySQL主键：http://www.cnblogs.com/mydomain/archive/2012/08/26/2657182.html

mysql中key 、primary key 、unique key 与index区别：http://zccst.iteye.com/blog/1697043

空间数据：http://www.cnblogs.com/marsprj/archive/2013/02/08/2909452.html