实用数据库教程mysql期末-1

1.DDL

建立数据库

create {database|schema} database_name [[default] character set 字符集名|
[default] collate 校对规则名]

建表方式：

drop table if exists tb1;
create tb1 as select * from tb2;--表数据和表结构都创建了

drop table if exists tb1;
create tb1 like  tb2; --只创建了与tb2表结构相同表，tb1为空表
insert into tb1 select * from tb2;--插入数据

2.DML

（1）insert 三种方式：

insert into table_name (column1,column2...) values(value1,value2...);
insert into table_name(column1,column2...) 
	select column1,column2...from table_name where 条件;
insert into table_name set column1=value1,column2=value2.....

update：

update table_name set column1 = vaule1,column2=value2...where condition

delete：

delete from table_name where condition 

truncate table table_name;--清除表中所有数据等同于 delete from table_name;
--参与索引和视图的表不能用truncate 

3. group by

使用with rollup 进行对每个分组进行汇总。
【若是使用mysql5.7.需要修改@@sql_mode系统全局配置变量，因5.7默认设置sql模式中对group by 实行的是ONLY_FULL_GROUP_BY，它要求 select columns from table_name group by list 中 column 必须在list中或是聚集函数，否则过不了ONLY_FULL_GROUP_BY检测，报“ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘testdatabase.student.Sno’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by”或“Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘testdatabase.student.Sname’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by”这样的错误】–> 修改session级别的sql_mode系统变量

SET sql_mode
='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
--- 就是把原来sql_mode的值中去掉了ONLY_FULL_GROUP_BY

用以下例子分析rollup的是怎样汇总的

表结构如下

表结构下载提取码 ：0dut

统计每个学生的各科的总分

	SELECT student.`Sname`, SUM(sc.`score`) 
	FROM sc, course,student 
	WHERE sc.`Cno` = course.`Cno` AND sc.`Sno`=student.`Sno` 
	GROUP BY student.Sname  WITH 	ROLLUP

在一个group By 条件下，rollup是对每个分组结果进行了汇总，并且在后一行添加汇总结果，分组条件那列是null。在group by多条件下也是类似的。只是先汇总最内层的分组，就像双重循环，内循环执行完毕后，外循环才会进入一次循环。在这里，内分组统计一次，外分组再汇总统计。group by c1，c2；现在按c1组内按c2分组，在c1内，会根据c2产生不同组，在这些不同的组间rollup会进行一次分组。按c1全部分组完后，按c1分组也会有不同的分组产生汇总统计。
比如

SELECT student.`Sname`,course.`Cname` , SUM(sc.`score`) 
FROM sc, course,student 
WHERE sc.`Cno` = course.`Cno` AND sc.`Sno`=student.`Sno` 
GROUP BY student.Sname,course.`Cname`  WITH ROLLUP

4. Limit 字句

Limit 【起始行（从0行开始的）,】行数
eg.输出第6-10行，第六行是5行（从0行开始的），到第十行是第六行的后面5行，limit 5,5

5.索引

5.1 一个表中 最多有16个索引，一个索引最多有15个列构成

5.2 索引类型

   查看索引： show index from table_name;
   删除索引：drop index index_name on table_name;

5.3 constraint 用以指定完整性约束的名字

在不符合完整性约束下会带有约束名的错误信息，也在删除时方便引用。

eg. 表级定义主键
constraint pk1 primary key（sno）

6.多表连接

join连接语法：from + tb1 [inner|cross] {left|right}[outer] join tb2 on 条件

join 多表连接：from + (tb1 join tb2 on tb1.no = tb2.no) join tb3 on tb3.no = tb2.no

eg. select * from (xs join xs_kc on xs.学号= xs_kc.学号）join kc on xs_kc.课程号=kc.课程号

---

S表：

A	B
1	2
3	3
5	9

R表：

B	C
2	1
7	2
3	5

s.A=R.c等值连, 等值连接不要求列有相同的字段名，列的数据类型同即可

A	S.B	R.B	C
1	2	2	1
5	9	3	5

s.B=R.B等值连, 尝试相同的

A	S.B	R.B	C
1	2	2	1
3	3	3	5

自然连接 S.B=R.B 两列的字段名必须相同，这样才会将两个同列自然合并到一列

A	B	C
1	2	1
1	3	5

连接方式区分：

	等值连接：根据表S和表R相同值进行连接，不要求相同值所在的列有相同的字段名
	
	自然连接：在等值连接基础上，要求连接列的字段名必须相同，会将两个相同的连接列自然合并到一列
	
	外连接：是能显示不满足条件的记录，其一方或两方不满足的记录会显示出来，
	一方的是左连接(left join)、右连接(right join)。两方的是全外连接(outer join)
	
	内连接: 是相对外连接而言，它是满足条件的结果集
	
	内连接与等值连接：内连接语句中可以设置不等的条件(inner join +条件)，而等值连接的条件只是相等条件(inner join + 等值条件），
	所以在结果集上看，内连接可以查到不等的情况下的结果，内连接结果范围更大，等值连接的结果是内连接结果的子集。