MySQL基础之表操作与表联结

MySQL 表数据类型

SQL语句创建表

• 语句解释
  • CREATE TABLE table_name (column_name column_type);
  • 新表的名字，在关键字CREATE TABLE之后给出；

create table courses(
student varchar(10),
class varchar(10));

• 设定列类型 、大小、约束
  • 约束：
    • not null 不允许 NULL值的列不接受没有列值的行，换 句话说，在插入或更新行时，该列必须有值。
    • default xx 允许指定默认值，在插入行时如果不给出值，DBMS将自动采用默 认值。默认值在CREATETABLE语句的列定义中用关键字DEFAULT指定。默认当前时间 DEFAULT CURRENT_DATE()
    • unique 唯一约束 PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
      • 每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。
    • foreign key 外键约束 一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
      • foreign key (colname) references table(colname（主键）)
• 设定主键
  • PRIMARY KEY （colname）

SQL语句向表中添加数据

• 语句解释
• 多种添加方式（指定列名；不指定列名）
  • insert into table values (全部)
  • insert into table(列名) values (对应列名值)

SQL语句删除表

• 语句解释
• DELETE
  • 删除表内数据
  • delete from table_name where condition
• DROP
  • drop table xxx 全部删除 包括表
• TRUNCATE
  • 清除表内数据，保存表结构，用 truncate
  • truncate table 表名;
• 不同方式的区别
  • 不同点
    • truncate 和 delete 只删除数据不删除表的结构(定义) ，drop 语句将删除表的结构被依赖的约束(constrain), 触发器(trigger), 索引(index); 依赖于该表的存储过程/函数将保留, 但是变为 invalid 状态。
    • delete 语句是 dml, 这个操作会放到 rollback segement 中, 事务提交之后才生效; 如果有相应的 trigger, 执行的时候将被触发。 truncate, drop 是 ddl, 操作立即生效, 原数据不放到 rollback segment 中, 不能回滚。 操作不触发 trigger。
    • delete 语句不影响表所占用的 extent, 高水线(high watermark)保持原位置不动。 显然 drop 语句将表所占用的空间全部释放 。 truncate 语句缺省情况下见空间释放到 minextents 个 extent, 除非使用 reuse storage; truncate会将高水线复位(回到最开始)。
    • 速度：一般来说: drop > truncate > delete 。
    • 安全性: 小心使用 drop 和 truncate, 尤其没有备份的时候。否则哭都来不及。
    • 使用上, 想删除部分数据行用 delete, 注意带上 where 子句。 回滚段要足够大。
    • 想删除表, 当然用 drop。
    • 想保留表而将所有数据删除。如果和事务无关, 用 truncate 即可。 如果和事务有关, 或者想触发 trigger, 还是用 delete。
    • 如果是整理表内部的碎片, 可以用 truncate 跟上 reuse stroage, 再重新导入/插入数据。

SQL语句修改表

• 修改列名
  • ALTER TABLE table_name CHANGE column_name column_name_new column_type
    • alter table courses change class classes varchar(10);
  • alter table student modify column sname varchar(20);
    • alter table courses modify column class text;
• 修改表中数据
  • UPDATE table_name SET field=value where condition
  • update courses set class=‘Math’ where student=‘A’;
• 删除行
  • DELETE FROM table_name WHERE some_column=some_value;
• 删除列
  • ALTER TABLE table_name DROP COLUMN column_name
• 新建列
  • ALTER TABLE table_name ADD column_name datatype;
• 新建行
  • insert into

超过5名学生的课（难度：简单）

• 编写一个 SQL 查询，列出所有超过或等于5名学生的课。
• 去重

select class from courses group by class having count(distinct student) >=5;

交换工资（难度：简单）

• 交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。

update salary set sex= if(sex='f','m','f');

表联结

• MySQL别名
  • as 可省略 但最好还是写一下
  • 列别名
    • SELECT column_name AS alias_name FROM table_name;
  • 表别名
    • SELECT column_name(s) FROM table_name AS alias_name;
• INNER JOIN
  • 它基于两个表之间的相 等测试

SELECT vend_name, prod_name, prod_price 
FROM Vendors INNER JOIN Products  
ON Vendors.vend_id = Products.vend_id; 

• 自然联结
  • 无论何时对表进行联结，应该至少有一列不止出现在一个表中（被联结的列）。标准的联结（前一课中介绍的内联结）返回所有数据，相同的列 甚至多次出现。自然联结排除多次出现，使每一列只返回一次。
  • 一般通过对一个表使用通配符 （SELECT*），而对其他表的列使用明确的子集来完成
• LEFT JOIN
  • 与内联结关联 两个表中的行不同的是，外联结还包括没有关联行的行。在使用 OUTER JOIN语法时，必须使用 RIGHT或 LEFT关键字指定包括其所有行的表 （RIGHT指出的是OUTERJOIN右边的表，而LEFT指出的是OUTERJOIN 左边的表）

select c.customerNumber,o.orderNumber 
from customers c 
left join orders o on c.customerNumber=o.customerNumber;

• CROSS JOIN
  • 返回笛卡儿积的联结

select xxx,xxx
from table1,table2
where table1.xx=table2.xx

• 自连接
  • 同一种表 使用别名 进行自连接

SELECT c1.cust_id, c1.cust_name, c1.cust_contact 
FROM Customers AS c1, Customers AS c2 
WHERE c1.cust_name = c2.cust_name  AND c2.cust_contact = 'Jim Jones';  

• UNION
  • 执行多个查询（多条 SELECT语句），并将结果作为一 个查询结果集返回。这些组合查询通常称为并（ union）或复合查询 （compound query）。
  • 在一个查询中从不同的表返回结构数据；
  • 对一个表执行多个查询，按一个查询返回数据。

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'; 

可查询得到相同结果

SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state IN ('IL','IN','MI')  OR cust_name = 'Fun4All'; 

DBMS在运行时关联指定的每个表，以处理联结。这种处理可能非常 耗费资源，因此应该注意，不要联结不必要的表。联结的表越多，性 能下降越厉害。

组合两张表 （难度：简单）

• 编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State

select FirstName,lastname,city,state 
from person p 
left join address a on p.personid=a.personid;

删除重复的邮箱（难度：简单）

• 编写一个 SQL 查询，来删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。

select e1.id,e1.email 
from email e1 ,email e2 
where not e1.email = e2.email and e1.id<e2.id ;