CREATE TABLE 表名( 属性名 数据类型 [完整性约束条件],
                          属性名 数据类型 [完整性约束条件],
                          .
                          .
                          属性名 数据表格 [完整性约束条件]);

PRIMARY KEY            标识该属性为该表的主键，可以唯一的标识对应的记录
FOREIGN KEY            标识该属性为该表的外键，与某表的主键关联
NOT NULL            标识该属性不能为空
UNIQUE                标识该属性的值是唯一的
AUTO_INCREMENT        标识该属性的值自动增加
DEFAULT                为该属性设置默认值

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。简单来说：unique=primary key+not null

区别：

1、primary key只能有一个，unique可以有多个同时存在；
2、primary key不能为空，而unique可以为空；
primary key一般在设计逻辑中用作记录标识，而unique只是用来保证唯一性

UNIQUE (bookId)//新建表时将bookId设为唯一

2.添加唯一约束

ALTER TABLE Persons ADD UNIQUE (Id_P)

CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)//设置Id_P和LastName都为唯一并命名为uc_PersonID

3.删除唯一约束

ALTER TABLE Persons DROP INDEX uc_PersonID

4.建表时创建外键

FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)//设置本表Id_p字段和Persons表的Id_p主键关联

5.查看表结构：

查看基本表结构： DESCRIBE(或DESC） 表名；//返回表字段及其详细信息表格
查看表详细结构： SHOW CREATE TABLE 表名；//返回结果是创建表的sql

6.删除/清空表：

drop table  表名； //彻底删除表格，删除字段数据，释放内存空间

truncate 表名；  //删除表格数据，不删除字段，释放内存空间

delete from 表名；或delete * from 表名;// 删除内容不删除定义，不释放空间,系统一行一行地删,效率较truncate低

7.修改表：

修改表名  ALTER TABLE  旧表名  RENMAE 新表名 ；

修改字段  ALTER TABLE 表名  CHANGE  旧属性名  新属性名  新数据类型

增加字段  ALTER TABLE  表名 ADD 属性名  数据类型 [完整性约束条件] [FIRST | AFTER 属性名 2]

删除字段  ALTER TABLE  表名  DROP 属性名

三.单表查询

1.条件查询 WHERE：

SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 字段 [NOT] IN (元素 1，元素 2，元素 3)；//查找属性（不为）为元素1、元素2和元素3的结果

SELECT 字段 1，字段 2...FROM 表名 WHERE 条件表达式 1 AND 条件表达式 2 [...AND 条件表达式 n] //and多条件查询

SELECT 字段 1，字段 2...FROM 表名 WHERE 条件表达式 1 OR 条件表达式 2 [...OR 条件表达式 n] //or多条件查询

2.范围查询 BETWEEN AND：

SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 字段 [NOT] BETWEEN 取值 1 > 指定值；//大于小于等符号

SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 字段 [NOT] BETWEEN 取值 1 AND 取值 2；//两端都包含

3.模糊查询 LIKE：

SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 字段 [NOT] LIKE ‘字符串’；

“%”代表任意字符；
“_” 代表单个字符；

4.空值查询 NOT NULL：

SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 字段 IS [NOT] NULL；

5.去重复查询 DISTINCT ：

SELECT DISTINCT 字段名 FROM 表名；

6.排序 ORDER BY

SELECT 字段 1，字段 2...FROM 表名 ORDER BY 属性名 [ASC|DESC] //ASC升序（默认） DESC降序

7.分组查询 GROUP BY

GROUP BY 属性名 [HAVING 条件表达式][WITH ROLLUP]

(1).单独使用(毫无意义)；

(2).与 GROUP_CONCAT()函数一起使用；

SELECT locus,GROUP_CONCAT(id) FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus; 的返回结果为

| locus    | GROUP_CONCAT(id) |
| AB086827 | 1,2              |
| AF040764 | 23,24            |

即将查询到的括号内字段的结果合并到一起并用逗号隔开可以将逗号更换成其他符号，如：

SELECT locus,GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus

| locus    | GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') |
| AB086827 | 2_1                                                      |
| AF040764 | 24_23                                                    |

(3).与聚合函数一起使用,与 HAVING 一起使用(限制输出的结果)；

聚集函数：平均值（avg）、最小值（min）、最大值（max）、总和（sum）、计数（count）

注：出现在select语句中但没有被聚集的属性只能是出现在group by子句中的属性。
    任何出现在having子句中，但没有被聚集的属性必须出现在group by子句中。

select dept_name,avg(salary) as avg_salary from instructor2 group by dept_name having avg(salary)>3000

(4).与 WITH ROLLUP 一起使用(最后加入一个总和行)；

WITH ROLLUP会在返回结果的最后加上一行将查询出的

    如果是数字结果则将所有结果数字相加
    如果是字符结果则将所有字符合并

SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;

8.分页查询 limit：

SELECT 字段 1，字段 2...FROM 表名 LIMIT 初始位置，记录数；

9.CONCAT函数

将查询结果连成一个字符串，但是查询的字段中有null时则整个结果为null

10.去重操作（distinct）

select distinct 属性名 from 关系名 where 条件

11.更名操作（as）

属性别名：    select 属性 as 属性别名 from 关系名 where 条件
关系别名：   select 属性 from 关系名 as 关系别名 where 条件

四.多表查询

1.连接查询

(1).内连接查询：内连接查询可以查询两个或者两个以上的表；

    SELECT * from t_book b,t_booktype t where t.id=b.id;
    或SELECT * from t_book,t_booktype where t_book.id=t_booktype.id;

(2).外连接查询:外连接可以查出某一张表的所有信息；(左连接查询,右连接查询)

    SELECT 属性名列表 FROM  表名 1 LEFT|RIGHT JOIN 表名 2 ON 表名 1.属性名 1=表名 2.属性名 2；

2.子查询

(1).带 In 关键字的子查询

    一个查询语句的条件可能落在另一个 SELECT 语句的查询结果中。

(2).带比较运算符的子查询

    子查询可以使用比较运算符。

(3).带 （not）Exists 关键字的子查询

    假如子查询查询到记录，则进行外层查询，否则，不执行外层查询；not exists表示内层没有内容时才查询外层

    如：SELECT * from t_book where EXISTS(select * from t_booktype);//表示t_booktype中返回的有内容时才执行外层t_book的查询

(4).带 Any 关键字的子查询,ANY 关键字表示满足其中任一条件；

    如：SELECT * FROM t_book WHERE price>= ANY (SELECT price FROM t_pricelevel);//表示筛选价格大于任意t_pricelevel表中选出的价格的结果，即比最小的大的价格就满足条件

(5).带 All 关键字的子查询,ALL 关键字表示满足所有条件；

    如：SELECT * FROM t_book WHERE price>= ALL (SELECT price FROM t_pricelevel);//表示筛选价格大于所有t_pricelevel表中选出的价格的结果，即比最大的价格大的才满足条件

3.合并查询

(1).UNION

     使用 UNION 关键字是，数据库系统会将所有的查询结果合并到一起，然后去除掉相同的记录；

(2).UNION ALL

     使用 UNION ALL，不会去除掉相同的记录；

五.索引

1.索引分类

(1).普通索引 : 这类索引可以创建在任何数据类型中；

(2).唯一性索引 : 使用 UNIQUE 参数可以设置，在创建唯一性索引时，限制该索引的值必须是唯一的；

(3).单列索引 : 在表中可以给单个字段创建索引，单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引；

(4).多列索引 : 多列索引是在表的多个字段上创建一个索引；

2.创建索引

(1)创建表的时候创建索引

CREATE TABLE 表名 (属性名 数据类型 [完整性约束条件],
                              属性名 数据类型 [完整性约束条件],
                              ....
                              属性名 数据类型
                              [UNIQUE | FULLTEXT | SPATIAL ]  INDEX| KEY
                              [别名] (属性名 1 [(长度)] [ASC | DESC])
);

CREATE TABLE mytable(
    id INT PRIMARY KEY AUTO_INCREMENT,
    bookId INT,
    bookName VARCHAR(20) NOT NULL,
    price DECIMAL(6,2),
    INDEX(bookId)   //创建普通索引
    //UNIQUE INDEX (bookId)   //创建唯一性索引
    //UNIQUE INDEX bokId(bookId)   //创建唯一性索引并起别名为bokId
    //UNIQUE INDEX bokId(bookId，id)   //创建多列索引并起别名为bokId
);

(2)已经存在的表上创建索引

CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名  ON 表名  (属性名 [(长度)] [ ASC | DESC])；

如：CREATE INDEX bookId ON mytable(bookId); //创建普通索引
   CREATE UNIQUE INDEX bookId ON mytable(bookId); //创建唯一索引
   CREATE UNIQUE INDEX bookId_price ON mytable(bookId,price); //创建多列索引

(3)用 ALTER TABLE 语句来创建索引

ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (属性名 [(长度)] [ ASC | DESC])；

如：ALTER TABLE mytable ADD INDEX index_bookId(bookId); //创建普通索引
   ALTER TABLE mytable ADD UNIQUE INDEX index_bookId(bookId); //创建唯一索引
   ALTER TABLE mytable ADD UNIQUE INDEX index_bookId_price(bookId,price); //创建多列索引

3.删除索引

DROP INDEX 索引名 ON 表名 ； 

如：drop index index_bookId_price on mytable;

六.视图

1.创建视图

CREATE [ ALGORITHM ={ UNDEFIEND | MERGE | TEMPTABLE }]
               VIEW 视图名 [ ( 属性清单) ]
               AS SELECT 语句  [ WITH [  CASCADED | LOCAL ] CHECK OPTION ]；

注：
（1）.ALGORITHM 是可选参数，表示视图选择的算法；

        UNDEFINED 选项表示 MySQL 将 自动选择所要使用的算法；

        MERGE 选项表示将使用视图的语句与视图定义合并起来，使得视图定义的某一部分 取代语句的对应部分；

        TEMPTABLE 选项表示将视图的结果存入临时表，然后使用临时表执行语句；

(2).“视图名”参数表示要创建的视图的名称；

(3).“属性清单”是可选参数，其指定了视图中各种属性的名词，默认情况下与 SELECT 语句中查询的属性相同；

(4)SELECT 语句参数是一个完整的查询语句，标识从某个表查出某些满足条件的记录，将这些记录导入视图中；

(5).WITH CHECK OPTION 是可选参数，表似乎更新视图时要保证在该视图的权限范围之内；

    CASCADED 是可选参数，表示更新视图时要满足所有相关视图和表的条件，该参数为默认值；
    LOCAL 表示更新视图时，要 满足该视图本身的定义条件即可；

2.查看视图

(1).DESCRIBE 视图名称； //查看视图字段属性信息

(2).SHOW TABLE STATUS LIKE ‘视图名称’；// 查看视图基本信息，因为视图是虚表，故查询结果字段都为空；此sql也可以查询普通表的基本信息；后面没有like指定视图时表示查询库中所有表和视图的基本信息

(3).SHOW CREATE VIEW 视图名称；//查看视图详细信息

3.修改视图

(1).CREATE OR REPLACE VIEW 语句修改视图

CREATE  OR  REPLACE  [ ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }]
                                         VIEW 视图名 [( 属性清单 )]
                                         AS SELECT 语句  [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]；
如：create or replace  view v1 as select * from mytable where bookId < 30;

(2).ALTER 语句修改视图

ALTER  [ ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }]
             VIEW 视图名 [( 属性清单 )]
             AS SELECT 语句
             [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]；
如：alter view v1 as select * from mytable;

4.更新视图

更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。因为视图是一个虚拟的表，其中没有数据。

通过视图更新时，都是转换基本表来更新。更新视图时，只能更新权限范围内的数据。
超出了范围，就不能更新。

(1).插入(INSERT)

 CREATE VIEW v1(b,p) AS select bookId,bookName from mytable;
 INSERT into v1 values(44,'think in java');

插入视图，再次查看视图v1时会看到插入的数据，视图操作实际是对表的操作，可以看到mytable表中增加了一条数据，
但是其他没有插入的字段则是显示默认值

(2).更新(UPDATE)

update v1 set b=8,p='设计模式' where b=44

(3).删除(DELETE)

delete from v1 where b=44;

5.删除视图

删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据；

DROP VIEW [ IF EXISTS ] 视图名列表 [ RESTRICT | CASCADE ]
如： DROP VIEW  IF EXISTS  v1;

六.触发器

(1).触发器的引入

触发器（TRIGGER）是由事件来触发某个操作。

这些事件包括 INSERT 语句、UPDATE 语句和 DELETE 语句。

当数据库系统执行这些事件时，就会激活触发器执行相应的操作。

(2).创建与使用触发器

创建只有一个执行语句的触发器

CREATE TRIGGER 触发器名  BEFORE | AFTER 触发事件
                              ON 表名  FOR EACH ROW 事件发生时要执行的语句

如：CREATE TRIGGER trig_book AFTER INSERT
             ON t_book FOR EACH ROW
             UPDATE t_bookType SET bookNum=bookNum+1 WHERE new.bookTypeId=t_booktype.id;
表示t_book表中国执行插入操作时触发t_bookType的更新操作，将bookNum加1

注：new是中间量，表示插入（更新）时的那条数据，如果是删除时用old，表示删除的那条数据

创建有多个执行语句的触发器

CREATE TRIGGER 触发器名  BEFORE | AFTER 触发事件
                              ON 表名  FOR  EACH  ROW  
                              BEGIN  
                                   执行语句列表  
                              END

如：
DELIMITER |
CREATE TRIGGER trig_book2 AFTER DELETE
    ON t_book FOR EACH ROW
    BEGIN
       UPDATE t_bookType SET bookNum=bookNum-1 WHERE old.bookTypeId=t_booktype.id;
       INSERT INTO t_log VALUES(NULL,NOW(),'在book表里删除了一条数据');
       DELETE FROM t_test WHERE old.bookTypeId=t_test.id;
    END
|
DELIMITER ;

注：DELIMITER | 包裹起来避免触发多条sql时遇到分号中断执行的情况

(3).查看触发器

SHOW TRIGGERS; 语句查看触发器信息

在 triggers 表中查看触发器信息

(4).删除触发器

DROP TRIGGER 触发器名；

七.mysql常用函数（部分举例）

1.日期和时间函数

(1).CURDATE()  返回当前系统日期； 如：select CURDATE() ,CURTIME(),MONTH(created_at) from theme13；
(2).CURTIME()  返回当前系统时间；

(4).Year(time)  返回日期time中的 年份值 ，
(3).MONTH(time)  返回日期 time 中的月份值，范围是 1~12
(4).DAY(time)  返回日期time中的 天值 ，

2.字符串函数

(1).CHAR_LENGTH(s)   计算字符串 s 的字符数；
(2).UPPER(s)  把所有字母变成大写字母；
(3).LOWER(s)   把所有字母变成小写字母；

3.数学函数

(1).A BS(x)   求绝对值
(2).SQRT(x)   求平方根
(3).MOD(x,y)   求余

4.加密函数

(1).PASSWORD(str)     一般对用户的密码加密 不可逆
(2).MD5(str)    普通加密 不可逆
(3).ENCODE(str，pswd_str)     加密函数，结果是一个二进制数，必须使用 BLOB 类型的字段来保存它；
(4).DECODE(crypt_str，pswd_str)     解密函数；

八.数据备份与还原

(1).数据备份

备份数据可以保证数据库中数据的安全，数据库管理员需要定期的进行数据库备份；

mysqldump -u username -p dbname table1 table2 ... > BackupName.sql

dbname 参数表示数据库的名称；table1 和 table2 参数表示表的名称，没有该参数时将备份整个数据库；
BackupName.sql 参数表示备份文件的名称，文件名前面可以加上一个绝对路径。通常以 sql 作为后缀。

如：
mysqldump -u root -p db_book > c:\db_book.sql     //将整个db_book数据库备份到c盘db_book.sql文件中

(2).数据还原

Mysql -u root -p [dbname] < backup.sql

dbname 参数表示数据库名称。该参数是可选参数，可以指定数据库名，也可以不指定。
指定数据库名时，表示还原该数据库下的表。
不指定数据库名时，表示还原特定的一个数据库。而备份文件中有创建数据库的语句。

MySql数据库基础语法

一.数据库操作

1.显示所有数据库：

2.创建数据库：

3.删除数据库：

二.表操作

1.建表语句

2.添加唯一约束

3.删除唯一约束

4.建表时创建外键

5.查看表结构：

6.删除/清空 表：

7.修改表：

三.单表查询

1.条件查询 WHERE：

2.范围查询 BETWEEN AND：

3.模糊查询 LIKE：

4.空值查询 NOT NULL：

5.去重复查询 DISTINCT ：

6.排序 ORDER BY

7.分组查询 GROUP BY

(1).单独使用(毫无意义)；

(2).与 GROUP_CONCAT()函数一起使用；

(3).与聚合函数一起使用,与 HAVING 一起使用(限制输出的结果)；

(4).与 WITH ROLLUP 一起使用(最后加入一个总和行)；

8.分页查询 limit：

9.CONCAT函数

10.去重操作（distinct）

11.更名操作（as）

四.多表查询

1.连接查询

2.子查询

3.合并查询

五.索引

1.索引分类

2.创建索引

(1)创建表的时候创建索引

(2)已经存在的表上创建索引

(3)用 ALTER TABLE 语句来创建索引

3.删除索引

六.视图

1.创建视图

2.查看视图

3.修改视图

(1).CREATE OR REPLACE VIEW 语句修改视图

(2).ALTER 语句修改视图

4.更新视图

(1).插入(INSERT)

(2).更新(UPDATE)

(3).删除(DELETE)

5.删除视图

六.触发器

(1).触发器的引入

(2).创建与使用触发器

创建只有一个执行语句的触发器

创建有多个执行语句的触发器

(3).查看触发器

(4).删除触发器

七.mysql常用函数（部分举例）

1.日期和时间函数

2.字符串函数

3.数学函数

4.加密函数

八.数据备份与还原

(1).数据备份

(2).数据还原

6.删除/清空表：