mysql基本特性

约束

主键约束

“主键（PRIMARY KEY）”的完整称呼是“主键约束”，是一个列或者列的组合，其值能唯一地标识表中的每一行。

create table account(id BIGINT(20 ) PRIMARY KEY, name VARCHAR(255));
create table acount(id BIGINT(20), name VARCHAR(255), PRIMARY KEY(id, name));

PRIMARY KEY(列名,...)

外键约束

外键约束（FOREIGN KEY）用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键
一个表的外键可以为空值，若不为空值，则每一个外键的值必须等于另一个表中主键的某个值
定义外键后，不允许删除另一个表中具有关联关系的行
主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表。
从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表。

FOREIGN KEY (deptID) REFERENCES tab_dept(id)

//主表
create table tab_dept(id INT(11) PRIMARY KEY, nane VARCHAR(22) NOT NULL);
//从表
create table tab_emp(id INT(11), deptID INT(11), PRIMARY KEY(id), FOREIGN KEY (deptID) REFERENCES tab_dept(id));

唯一约束

该列唯一，允许为空，但只能出现一个空值 <字段名> <数据类型> UNIQUE

create table tab_dept(id INT(11) PRIMARY KEY, name VARCHAR(22) UNIQUE); //name唯一.

非空约束

<字段名> <数据类型> NOT NULL;指字段的值不能为空。

create table tab_dept(id INT(11) PRIMARY KEY, name VARCHAR(22) NOT NULL); //name唯一.

基本数据类型

字符串类型

CHAR(M) 固定长度字符串 255
VARCHAR(M) 变长字符串 255
TEXT 二进制字符串 65535 2^16
LONGTEXT 2^32

整形

TINYINT 1字节
SMALLINT 2字节
INT 4字节
BIGINT 8字节
DOUBLE 8字节

时间日期

DATETIME YYYY-MM-DD HH:MM:SS 8 个字节
TIMESTAMP YYYY-MM-DD HH:MM:SS 4 个字节
DATETIME 在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；
而 TIMESTAMP 值的存储是以 UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区

基本命令

数据库操作

mysql -u root -p
show databases;
create database gc;
drop database gc;
use gc;

表操作

show tables;
create table account(id BIGINT(20), name VARCHAR(25), age INT);
desc account;
drop table account;
show create table account; //显示创建account表的语句可以查看约束
// alter table biao rename change drop add 修改表
alter table account rename login; //修改表名字
alter table account change id uid BIGINT(20); //修改字段名字类型
alter table account drop   id; //删除字段
alter table account add    url VARCHAR(64); //添加字段

INSERT插入

INSERT…VALUES语句

INSERT INTO <表名> [ <列名1> [ , … <列名n>] ] VALUES (值1) [… , (值n) ];

INSERT INTO tb_courses (id,name) VALUES(1,'Network');

INSERT…SET语句(mysql可用)

INSERT INTO <表名> SET <列名1> = <值1>, <列名2> = <值2>, …

INSERT INTO tb_account SET id=1, name='Network';

DELETE删除

DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

DELETE FROM tb_account;
DELETE FROM tb_account WHERE id = 1;

UPDATE修改

UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ][ORDER BY 子句] [LIMIT 子句]

UPDATE tb_account SET name='学习 C++' WHERE id=1;

SELECT查询

SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]

SELECT * FROM tb_account;           //检索所有列
SELECT name FROM tb_account;        //检索单列
SELECT id, name FROM tb_account;     //检索多列

SELECT id FROM tb_account LIMIT 5;           //限制返回结果为5行
SELECT id FROM tb_account LIMIT 3, 4;        //从3行开始取4行
SELECT id FROM tb_account LIMIT 4 OFFSET 3;  //从3行开始取出4行 

SELECT id, name FROM tb_account ORDER BY id, name;      //检索结果按照id和name排序
SELECT id, name FROM tb_account ORDER BY name DESC;     //检索结果按照name降序
SELECT id, name FROM tb_account ORDER BY id DESC, name; //检索结果按照降序排列，再按照名字排序

SELECT id, name FROM tb_account ORDER BY id DESC LIMIT 1; //返回id最大的行

SELECT name FROM tb_account WHERE inamed = 'sb';  //指定搜索条件
SELECT name FROM tb_account WHERE id = 1;         //指定搜索条件
SELECT name FROM tb_account WHERE id < 1;         //指定搜索条件
SELECT name FROM tb_account WHERE id <= 1;        //指定搜索条件
SELECT name FROM tb_account WHERE id != 1;        //指定搜索条件
SELECT name FROM tb_account WHERE name IS NULL;   //name为空

SELECT name FROM tb_account WHERE id BETWEEN 5 AND 10;// BETWEEN AND指定范围

SELECT name FROM tb_account WHERE age = 20 AND price <=10; //AND组合单一的where条件
SELECT name FROM tb_account WHERE age = 20 or age =30;     //OR组合单一的where条件
SELECT name FROM tb_account WHERE (gae = 10 OR age = 30) AND id >= 10; //AND优先级高于OR
SELECT name FROM tb_account WHERE id IN (10,20,30) ORDER BY name; //IN指定范围
SELECT name FROM tb_account WHERE id NOT IN (10,20) ORDER BY name; //NOT 否定后跟条件

索引

索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，
实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表
数据库中表的行数据有两种访问方式：顺序访问和索引访问。
索引访问是通过遍历索引来直接访问表中记录行的方式。使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据
索引底层使用B-树索引和哈希索引.按照用途分为普通索引，唯一性索引，主键索引，空间索引和全文索引。

普通索引

普通索引是最基本的索引类型，唯一任务是加快对数据的访问速度，没有任何限制。创建普通索引时，通常使用的关键字是 INDEX 或 KEY。

唯一性索引

唯一性索引是不允许索引列具有相同索引值的索引。如果能确定某个数据列只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE 把它定义为一个唯一性索引。
创建唯一性索引的目的往往不是为了提高访问速度，而是为了避免数据出现重复。

主键索引

主键索引是一种唯一性索引，即不允许值重复或者值为空，并且每个表只能有一个主键。主键可以在创建表的时候指定，也可以通过修改表的方式添加，必须指定关键字 PRIMARY KEY。注意每个表只能有一个主键。

空间索引

空间索引主要用于地理空间数据类型 GEOMETRY。

全文索引

全文索引只能在 VARCHAR 或 TEXT 类型的列上创建，并且只能在 MyISAM 表中创建。

创建索引

在height上创建一般索引

CREATE TABLE tb_stu_info
    -> (
    -> id INT NOT NULL,
    -> name CHAR(45) DEFAULT NULL,
    -> dept_id INT DEFAULT NULL,
    -> age INT DEFAULT NULL,
    -> height INT DEFAULT NULL,
    -> INDEX(height)
    -> );

CREATE INDEX indexName ON tb_stu_info(height);

在该表的 id 字段上使用 UNIQUE 关键字创建唯一索引

CREATE TABLE tb_stu_info2
    -> (
    -> id INT NOT NULL,
    -> name CHAR(45) DEFAULT NULL,
    -> dept_id INT DEFAULT NULL,
    -> age INT DEFAULT NULL,
    -> height INT DEFAULT NULL,
    -> UNIQUE INDEX(id)
    -> );
REATE UNIQUE INDEX indexName ON tb_stu_info2(id)

删除索引

   DROP INDEX height ON tb_stu_info

显示索引

SHOW INDEX FROM tb_stu_info;

事务

事务具有原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持续性（Durability）。 BEGIN, ROLLBACK, COMMIT 处理事务的方法。

1. 原子性 事务必须是原子工作单元，事务中的操作要么全部执行，要么全都不执行
2. 一致性 在事务开始之前和事务结束以后，数据库的完整性没有被破坏
3. 隔离性 多个并发事务同时对其数据进行读写和修改的能力，防止多个事务并发执行时导致数据的不一致
4. 持久性 事务处理结束后，对数据的修改就是永久的

use gc;
CREATE TABLE tb_account(id int(2), PRIMARY KEY(id));
SELECT * FROM tb_account;
BEGIN; # 开始事务
INSERT INTO tb_account (id) VALUES(1);
INSERT INTO tb_account (id) VALUES(2);
SELECT * FROM tb_account;
COMMIT; # 提交
BEGIN;  # 开启事务
INSERT INTO tb_account (id) VALUES(2);
ROLLBACK; # 回滚
SELECT * FROM tb_account;

引擎和锁

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁
InnoDB实现了以下两种类型的行锁：

共享锁（s）：又称读锁

允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

排他锁（Ｘ）：又称写锁

允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

InnoDB锁

mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。
共享锁（S）：SELECT FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁（X）：SELECT FROM table_name WHERE ... FOR UPDATE