MySQL数据库操作教程

--对一个数据列建立的约束，就是列级约束
--对多个数据列建立的约束，就是表级约束
--列级约束既可以在列定义时声明，也可以在列定义后声明，
--表级约束只能在列定义后声明

--注意！
--NOT NUll和DEFAULT没有表级约束

CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY);
--主键：PRIMARY KEY
--主键约束，每张数据表只可以有一个主键，
--主键保证记录的唯一性，且其自动为NOT NULL

CREATE TABLE users(name VARCHAR(20) NOT NULL UNIQUE KEY);
--唯一约束：UNIQUE KEY
--唯一约束可以保证记录的唯一性，且唯一约束字段可以为空
--每张数据库表可以存在多个唯一约束

CREATE TABLE users(sex ENUM('1','2','3') DEFAULT '3');
--默认约束：DEFAULT
--默认值，当插入记录时，如果没有明确为字段赋值，则自动赋予默认值
--ENUM是枚举，表示用户只能从3个选项中选一个字段赋值，详细内容可自行搜索

CREATE TABLE provinces(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20) NOT NULL
);
--父表，省份信息
CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY (pid) REFERENCES provinces (id)
);
--子表，用户信息，用户pid 对应 省份id

--外键约束：FOREIGN KEY

--外键约束要求：
--1.父表和子表必须使用相同的存储引擎，而且禁止使用临时表
--2.数据表的存储引擎只能为InnoDB
/*
3.外键列和参照列必须具有相似的数据类型。
  其中数字的长度或者是否有符号位都必须相同
  但是，若是字符的长度，则可以不同
*/
--4.外键列和参照列必须创建索引，如果外键列不存在索引。！MySQL将自动创建索引！

--例子
CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE
);
--ON DELETE 意思为删除操作发生时

--添加主键约束
ALTER TABLE 表名 ADD [CONSTRAINT [symbol(约束名)]] PRIMARY KEY [index_type](index_col_name);
--约束名为这个约束的名称，可略
--index_col_name，即为添加约束的列名，指定其进行添加操作，注意有小括号。

--删除主键约束(一张表主键只有一个，不需指定)
ALTER TABLE 表名 DROP PRIMARY KEY;

--添加主键约束 例(假设前置条件都已定义)：
ALTER TABLE users ADD CONSTRAINT PRIKEY_users_id PRIMARY KEY (id);

--添加唯一约束
ALTER TABLE 表名 ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,..);

--删除唯一约束
ALTER TABLE 表名 DROP {INDEX|KEY} index_name;
--为什么指定的是索引名，是因为一张表会有多个唯一约束，且需要注意的是，删除的是唯一约束这个属性，而不是整个字段！

--添加唯一约束 例(假设前置条件都已定义)：
ALTER TABLE 表名 ADD UNIQUE (name);

--删除唯一约束 例(假设前置条件都已定义)：
SHOW INDEXES FROM 表名\G;
--找到要删除唯一约束属性的列，找到key_name,即索引名
ALTER TABLE 表名 DROP INDEX 索引名;

--添加外键约束
ALTER TABLE 表名 ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,..) reference_definition;

--删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY fk_symbol;

--添加外键约束 例(假设前置条件都已定义)：
ALTER TABLE　users ADD FOREIGN KEY (vid) REFERENCE VIP (id);
--VIP是父表，users是子表

--删除外键约束 例(假设前置条件都已定义)：
SHOW CREATE TABLE 表名;
--查看外键约束名称(若没有设置则系统自动设置)，在CONSTRAINT后，有个名称即为外键约束名
ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;
SHOW CREATE TABLE 表名;
--发现外键约束已经删除，但是还会存在索引，若想删除索引，则：
ALTER TABLE 表名 DROP INDEX 索引名;

--添加/删除默认约束
ALTER TABLE 表名 ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT};

--添加默认约束 例(假设前置条件都已定义)：
ALTER TABLE users ALTER age SET DEFAULT 16;
--DROP则无需指定默认值，直接后接DEFAULT

--对一个数据列建立的约束，就是列级约束
--对多个数据列建立的约束，就是表级约束
--列级约束既可以在列定义时声明，也可以在列定义后声明，
--表级约束只能在列定义后声明

--注意！
--NOT NUll和DEFAULT没有表级约束

CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY);
--主键：PRIMARY KEY
--主键约束，每张数据表只可以有一个主键，
--主键保证记录的唯一性，且其自动为NOT NULL

CREATE TABLE users(name VARCHAR(20) NOT NULL UNIQUE KEY);
--唯一约束：UNIQUE KEY
--唯一约束可以保证记录的唯一性，且唯一约束字段可以为空
--每张数据库表可以存在多个唯一约束

CREATE TABLE users(sex ENUM('1','2','3') DEFAULT '3');
--默认约束：DEFAULT
--默认值，当插入记录时，如果没有明确为字段赋值，则自动赋予默认值
--ENUM是枚举，表示用户只能从3个选项中选一个字段赋值，详细内容可自行搜索

CREATE TABLE provinces(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20) NOT NULL
);
--父表，省份信息
CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY (pid) REFERENCES provinces (id)
);
--子表，用户信息，用户pid 对应 省份id

--外键约束：FOREIGN KEY

--外键约束要求：
--1.父表和子表必须使用相同的存储引擎，而且禁止使用临时表
--2.数据表的存储引擎只能为InnoDB
/*
3.外键列和参照列必须具有相似的数据类型。
  其中数字的长度或者是否有符号位都必须相同
  但是，若是字符的长度，则可以不同
*/
--4.外键列和参照列必须创建索引，如果外键列不存在索引。！MySQL将自动创建索引！

--例子
CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE
);
--ON DELETE 意思为删除操作发生时

--添加列
ALTER TABLE 表名 ADD [COLUMN] 添加列名 添加列类型 [FIRST|AFTER 列名称];
--FIRST即为添加至最前，AFTER即为在指定列的后面
--若不填写位置，则默认添加至最后方

--例(假设前置条件都已定义)：
ALTER TABLE users ADD name VARCHAR(20) NOT NULL AFTER id;

--添加多列与添加单列有两个不同：
--1.要在添加列名与列类型时候，加上小括号()隔开
--2.添加多列的时候无法指定位置

--删除列
ALTER TABLE 表名 DROP [COLUMN] 列名;

--删除单列与多列例(假设前置条件都已定义)：
ALTER TABLE users DROP name;
ALTER TABLE users DROP name,DROP age;

--注意！删除同时也可以添加，只要使用逗号","隔开就好了

--修改列定义
ALTER TABLE 表名 MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name];

--修改列位置例，也可以更换类型(假设前置条件都已定义)：
ALTER TABLE 表名 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;

--修改列名称(CHANGE既可以修改列定义，也可以修改列名称)
ALTER TABLE 表名 CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name];

--修改列名称例(假设前置条件都已定义)：
ALTER TABLE 表名 CHANGE pw password TINYINT UNSIGNED NOT NULL;

--修改数据表名称
--方法1.
ALTER TABLE 表名 RENAME[TO|AS] new_tb_name
--方法2.
RENAME TABLE 表名 TO new_tb_name [,tb_name2 TO new tb_name2]...

/*第一种插入方式*/

--语法结构
INSERT [INTO] 表名 [(列名,..)] {VALUES|VALUE} ({expr | DEFAULT},..),(..),..;
/*
1.列名可以省略，当列名省略时，即所有字段都需要赋值；
2.插入值时，可以用表达式、函数等；
3.同时插入多条记录，记得逗号；
*/

--实例(假设前提条件已满足)
INSERT users VALUES(DEFAULT,'lucy',6*6),(NULL,'jack',md5('36'));
/*
1.有默认值时，可以使用DEFAULT;
2.可以书写表达式、函数等，来写入值
3.如有自动编号等属性时，可以使用NULL；
4.插入多条语句，记得逗号
*/

/*第二种插入方式*/

--语法结构
INSERT [INTO] tb_name SET col_name={expr|DEFAULT},..;
/*
与第一种方式的区别在于:
1.此方法可以使用子查询(SubQuery);
2.一次只可以插入一条记录;
*/

--实例(假设前提条件已满足)
INSERT users SET name='Tom',age=13;

/*第三种插入方式*/

--语法结构
INSERT [INTO] tb_name [(col_name,..)] SELECT ...;
/*此方法可以将查询的结果插入到指定数据表*/

--实例(假设前提条件已满足)
INSERT list SELECT name FROM users WHERE age >= 18;

--将查询结果写入数据表，例(假设前提条件已满足)：
INSERT new_tb(new_name) SELECT old_name FROM old_tb GROUP BY old_name;

--更新记录(单表更新)

--语法格式
UPDATE [LOW_PRIORITY] [IGNORE] table_referrence SET col_name1={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}].. [WHERE where_condition];
--若是省略where条件，那么所有记录全部更新


--实例(假设前提条件已满足)：

UPDATE users SET mon = mon + 1;
--单列，表中记录全部更新

UPDATE users SET mon = mon + age,sex = 0 WHERE id>3;
--多列，并且有WHERE条件

--删除记录(单表删除)

--语法格式
DELETE FROM tb_name [WHERE where_condition];
--若是省略where条件，那么所有记录全部删除


--实例(假设前提条件已满足)：
DELETE FROM users WHERE id = 1;

--语法格式
SELECT select_expr [,select_expr ..]
[
 FROM table_referrences
 [WHERE where_condition]
 [GROUP BY {col-name|position} [ASC|DESC],..]
 [HAVING where_condition]
 [ORDER BY {col_name|expr|position} [ASC|DESC],..]
 [LIMIT {[offset,] row_count|row_count OFFSET offset}]
]

--实例(假设前提条件已满足)

--指定属性查询
SELECT name,age FROM users;
SELECT age,name FROM users;
--上面两个例子的属性顺序颠倒，同时也会影响输出的顺序不同

--有时出现FROM多张表的情况时，使用以下格式，可以不容易出错
SELECT users.name,users.age FROM users;

--取别名
SELECT id AS userId, name AS userName FROM users;
--注意以下情况

SELECT id name FROM users;
--这种情况不会打印两列数据，而是给id取别名为name，输出一列

--语句格式
[GROUP BY {col_name|position} [ASC|DESC],..]
/*
ASC:升序(默认)
DESC:降序
*/

--实例(假设前提条件已满足):
SELECT age FROM users GROUP BY age;
--指定名称
SELECT age FROM users GROUP BY 1;
--指定位置

--语句格式
[HAVING where_condition]
/*
HAVING条件要么为一个聚合函数(如最大、小值，平均值等)，
要么保证HAVING后的判断属性，必须出现在SELECT后的条件中
*/

--实例(假设前提条件已满足)
SELECT sex FROM users GROUP BY sex HAVING age > 18;
--错误示例
SELECT sex,age FROM users GROUP BY sex HAVING age > 18;
--在SELECT后加入age即可
SELECT sex,age FROM users GROUP BY sex HAVING count(id) > 2;
--在后面加入聚合函数，也可以

--语句格式
[ORDER BY {col_name|expr|position} [ASC|DESC],..]

--实例(假设前提条件已满足)
SELECT * FROM users ORDER BY id DESC;
--单个属性

SELECT * FROM users ORDER BY age,id DESC;
--多个属性(若是第一个条件可排列好，则忽略后面条件，以此往后类推)

--语句格式
[LIMIT {[offset,] row_count|row_count OFFSET offset}]


--实例(假设前提条件已满足)

SELECT * FROM users LIMIT 2;
--限制返回数量

SELECT * FROM users ORDER BY id DESC LIMIT 3,2;
--从3开始，返回2行结果
--注意，SELECT编号是从0开始的(即第一行编号为0)，这里要特别注意

--例
SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);
/*
1.SELECT * FROM t1，称为Outer Query/Outer Statement(外层查询/外层声明)
2.SELECT col2 FROM t2，称为SubQuery
*/

--语法结构
operand comparison_operator subquery;

--示例(假设所有前提条件都已满足)

SELECT ROUND(AVG(price),2) FROM Store;
--求出价格平均数，且保留小数点后两位
SELECT id,name,price FROM Store WHERE price >= (SELECT ROUND(AVG(price),2) FROM Store);
--使用子查询，求出大于等于平均价格的商品

SELECT * FROM Store WHERE categ = '彩电'\G;
--注意，查询可以使用中文

--ANY,SOME,ALL
operand comparison_operator ANY (subquery);
operand comparison_operator SOME (subquery);
operand comparison_operator ALL (subquery);

--示例(假设子查询有多个返回值)
SELECT name,price FROM Store WHERE price > ANY (SELECT price FROM Store WHERE categ = '彩电');
--对照上表，可以看出彩电返回的是返回值中的最小值

--语法结构
operand comparison_operator [NOT] IN (subquery);
--=ANY运算符与IN等效
--!=ALL或<>ALL运算符与NOT IN等效

--基本格式
UPDATE table_references SET col_name1 = {expr1|DEFAULT} [,col_name2 = {expr2|DEFAULT}]... [WHERE where_condition];

--语法结构
table_reference {[INNER|CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN} table_reference ON conditional_expr;

--连接类型
/*
INNER JOIN,内连接
  在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等价的
LEFT [OUTER] JOIN，左外连接
RIGHT [OUTER] JOIN，右外连接
*/

--实例(假设前提条件已满足)
UPDATE tb1 INNER JOIN tb2 ON tb1_att = tb2_att SET tb1_att = tb2_id;
--tb1内连接tb2，若tb1_att=tb2_att，那么设置tb1_att为tb2_id

--创建数据表同时将查询结果写入到数据表
CREATE TABLE [IF NOT EXISTS] tb_name [(create_definition,..)] select_statement;

--实例(假设前提条件已满足)
CREATE TABLE new_tb
(
  id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(40) NOT NULL
)
SELECT name FROM old_tb GROUP BY old_name;

--语法结构
DELETE tb_name[.*][,tb_name[.*]]...
FROM table_references
[WHERE where_condition];

--删除时，有时会用到查找重复的数据
--查找重复至少一次的数据(假设前提条件已满足)
SELECT goods_id,goods_name FROM tb_goods GROUP BY goods_name HAVING count(goods_name) > 1;

--连接语法结构
table_reference {[INNER|CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN} table_reference ON conditional_expr;
/*
ps.在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等价的
LEFT [OUTER] JOIN，左外连接
RIGHT [OUTER] JOIN，右外连接
ON关键字后面设定连接条件，也可以使用WHERE来替代
(通常使用ON关键字来设定连接条件，使用WHERE关键字来进行结果集记录的过滤)
*/

--数据表参照
table_reference tb_name [[AS] alias] | table_subquery [AS] alias;
--数据库可以使用tb_name AS alias_name
--或者tb_name alias_name 赋予别名
--table_subquery 可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名

--内连接，显示左表及右表符合连接条件的记录

--示例操作(假设前提条件已满足)
SELECT book_id,book_name,cate_name FROM tb_book INNER JOIN tb_book_cates ON tb_book.cate_id = tb_book_cates.cate_id;

--外连接
/*
A LEFT JOIN B join_condition(拿左外连接作说明)
数据表B的结果集依赖数据表A
数据表A的结果集根据左连接条件依赖所有数据表(B表除外)
左外连接条件决定如何检索数据表B(在没有指定WHERE条件的情况下)
如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行
*/
--左外连接，显示左表的全部记录及右表符合连接条件的记录
--示例操作(假设前提条件已满足)
SELECT book_id,book_name,cate_name FROM tb_book LEFT JOIN tb_book_cates ON tb_book.cate_id = tb_book_cates.cate_id;

--右外连接，显示右表的全部记录及左表符合连接条件的记录
--示例操作(假设前提条件已满足)
SELECT book_id,book_name,cate_name FROM tb_book RIGHT JOIN tb_book_cates ON tb_book.cate_id = tb_book_cates.cate_id;

--多表连接(两张表以上)

--示例操作(假设前提条件已满足)
SELECT stu_id,stu_name,stu_sex,stu_cid,stu_pid FROM tb_stu AS s
INNER JOIN tb_stu_class AS c ON s.stu_cid = c.class_id
INNER JOIN tb_stu_province AS p ON s.stu_pid = p.pro_id;