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;
- ALTER TABLE table_name CHANGE column_name column_name_new column_type
- 修改表中数据
- 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 ;