MySQL笔记2
创建计算字段
1、拼接字段
拼接（concatenate）：将值联结到一起（将一个值附加到另一个值）构成单个值。
SELECT CONCAT(a,"+",b,"+",c) AS d FROM <table_name1>, <table_name2>, ...;
其中a,b,c为表中的字段，他们以字符串的形式拼接在一起，起了一个别名d。
2、执行算术运算
SELECT a*b+c AS d FROM <table_name1>, <table_name2>, ...;
字段与字段之间可以发生加减乘除运算。
数据处理函数
MySQL内建函数很多，就不介绍了，这里是官方文档的链接。
分组数据
总是报错：ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.roles.role' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 有大神知道为什么吗？
查询语句的顺序
SELECT ... FROM ... [WHERR ...] [GROUP BY ... HAVING ...] [ORDER BY ...];
使用子查询
查看不同表相关联的信息，比如查看Orders.id = Customers.id相同的订单数目。
SELECT name,state,(SELECT COUNT(*) FROM Orders WHERE Orders.id = Customers.id) AS orders FROM Customers ORDER BY name;
联结
叉联结
从多张表中检索数据：
SELECT * FROM <table_name1>, <tablename2>, ...;
你会发现行数变得很多，这是因为MySQL会把第一张表的每一行数据和第二张表的每一行数据分别组合成一行，形成笛卡尔积，这就是我们称的叉联结。
内联结
通过使用 WHERE 语句，我们将意义上有关系的行组合起来，而没有意义的关联的行过滤掉：
SELECT * FROM <table_name1>, <tablename2> WHERE <table_name1.id> = <table_name2.id>;
这样我们只会检索出两个表id相同的列，并把结果组合成一行。
自联结
使用表别名的一个主要原因是能在一条SELECT语句中不止一次引用相同的表，因此我们可以为同一张表去不同名字，从而多次使用同一张表。
SELECT * FROM <table_name> AS t1, <table_name> AS t2 WHERE t1.name = t2.name AND t2.contact = 1;
试验看结果，很难描述。
自然联结
保证相同的字段不重复，只能自己通过选择检索的字段避免重复，MySQL不会自动完成这项工作。
外联结
许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行。
SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;
这条SELECT语句使用了关键字OUTER JOIN来指定联结类型（而不是在WHERE子句中指定）。但是，与内联结关联两个表中的行不同的是，外联结还包括没有关联行的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。上面的例子使用LEFT OUTER JOIN从FROM子句左边的表（Customers表）中选择所有行。为了从右边的表中选择所有行，需要使用RIGHT OUTER JOIN。
组合查询
可用UNION操作符来组合数条SQL查询。利用UNION，可给出多条SELECT语句，将它们的结果组合成一个结果集。
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';
可以看到，UNION非常容易使用，但在进行组合时需要注意几条规则。
1、UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合四条SELECT语句，将要使用三个UNION关键字）。
2、UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）。
3、列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）。如果遵守了这些基本规则或限制，则可以将UNION用于任何数据检索操作。
UNION从查询结果集中自动去除了重复的行；换句话说，它的行为与一条SELECT语句中使用多个WHERE子句条件一样。因为Indiana州有一个Fun4All单位，所以两条SELECT语句都返回该行。使用UNION时，重复的行会被自动取消。
这是UNION的默认行为，如果愿意也可以改变它。事实上，如果想返回所有的匹配行，可使用UNION ALL而不是UNION。
插入数据
插入完整的一行：
INSERT INTO <table_name> (keyword1, keword2, ...) value (value1, value2, ...);
插入的顺序按照字段的声明顺序，一一对应！
插入完整的多行：
INSERT INTO <table_name> (keyword1, keword2, ...) value (value1, value2, ...), (value1, value2, ...), ...;
省略值
如果表的定义允许，则可以在INSERT操作中省略某些列。省略的列必须满足以下某个条件。
1、该列定义为允许NULL值（无值或空值）。
2、在表定义中给出默认值。这表示如果不给出值，将使用默认值。
如果对表中不允许NULL值且没有默认值的列不给出值，DBMS将产生错误消息，并且相应的行插入不成功。
插入查询值
INSERT INTO <table_name1> (keyword1, keyword2, ...) (SELECT keyword1, keyword2, ... FROM <table_name2>);
将表二的对应字段的值插入到表一对应字段中。
注意这里没有value，只有使用具体值作为插入数据，才需要使用value。
复制表到表
新建一个表，复制已存在的表数据到其中：
CREATE TABLE <new_table> AS (SELECT * FROM <old_table>);
# 不加AS也可以
CREATE TABLE <new_table> (SELECT * FROM <old_table>);
在使用SELECT INTO时，需要知道一些事情：
1、任何SELECT选项和子句都可以使用，包括WHERE和GROUP BY；
2、可利用联结从多个表插入数据；
3、不管从多少个表中检索数据，数据都只能插入到一个表中。
更新和删除数据
更新数据
使用UPDATE语句非常容易，甚至可以说太容易了。基本的UPDATE语句由三部分组成，分别是：
1、要更新的表；
2、列名和它们的新值；
3、确定要更新哪些行的过滤条件。
UPDATE <table_name> SET <keyword> = <value> [WHERE ...] ...;
一般UPDATE配合WHERE使用，精确修改某一行或者满足一定条件的多行的字段值，如果不加条件，就会修改整列的值，非常危险！
删除数据
从一个表中删除（去掉）数据，使用DELETE语句。有两种使用DELETE的方式：
1、从表中删除特定的行；
2、从表中删除所有行。
DELETE FROM <table_name> WHERE <keyword> = <value>;
如果不加WHERE条件，删除表中的所有数据。
用TRUNCATE和DELETE的区别：
-- 清空全部数据，不写日志，不可恢复，速度极快
TRUNCATE TABLE <table_name>;
-- 清空全部数据，写日志，数据可恢复，速度慢
DELETE FROM <table_name>;
更新和删除的指导原则
使用的UPDATE和DELETE语句都有WHERE子句，这样做的理由很充分。如果省略了WHERE子句，则UPDATE或DELETE将被应用到表中所有的行。换句话说，如果执行UPDATE而不带WHERE子句，则表中每一行都将用新值更新。类似地，如果执行DELETE语句而不带WHERE子句，表的所有数据都将被删除。
下面是许多SQL程序员使用UPDATE或DELETE时所遵循的重要原则。
1、除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
2、保证每个表都有主键（如果忘记这个内容，请参阅第12课），尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。
3、在UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
4、使用强制实施引用完整性的数据库（关于这个内容，请参阅第12课），这样DBMS将不允许删除其数据与其他表相关联的行。
5、有的DBMS允许数据库管理员施加约束，防止执行不带WHERE子句的UPDATE或DELETE语句。如果所采用的DBMS支持这个特性，应该使用它。
若是SQL没有撤销（undo）按钮，应该非常小心地使用UPDATE和DELETE，否则你会发现自己更新或删除了错误的数据。
更新表
使用ALTER TABLE更改表结构，必须给出下面的信息：
1、在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）；
2、列出要做哪些更改。
新增一列：
ALTER TABLE Vendors
ADD vend_phone CHAR(20);
没有指定默认值，默认为NULL。
删除一列：
ALTER TABLE Vendors
DROP COLUMN vend_phone;
更改字段类型：
ALTER TABLE Vendors
MODIFY vend_phone CHAR(10);
亲自尝试：
int类型数据可以转换为varchar，数据不会丢失；
字符串类型的数据转换为int，就会报错：ERROR 1366 (HY000): Incorrect integer value: 'abcd' for column 'name' at row 1
修改数据类型的同时，我们可以设定 NOT NULL (不为空) 和 DEFAULT (默认值)。
重命名表：
ALTER TABLE <old_name> RENAME <new_table>;
# 或者直接使用RENAME
RENAME TABLE <old_name> TO <new_table>;
# 交换名字
RENAME TABLE a TO temp, b TO a, temp TO b;
删除表
DROP TABLE <table_name>;
