8/4 MySQL串讲

数据库基本操作

1、创建数据库：create database 数据库名；

数据库名称唯一，不可重复。

2、查看数据库：show databases;

查看创建好的数据库的信息：

show create database 数据库名;

3、修改数据库：

修改数据库的编码方式：alter database 数据库名称 default character set 编码方式 ;

4、删除数据库：drop database 数据库名称;

数据库的数据类型：

需要注意：

1、int类型的数据无符号的取值范围是十位数。

2、decimal(M,D)类型的有效取值范围是由M和D决定的，其中，M表示的数据的长度，D表示的小数点后的长度；比如将数据类型为decimal（6,2）的数据3.1415插入数据库后，显示的结果为3.14。

常用类型：

1、char：用于表示固定长度的字符串；
2、varchar：用于表示可变长度的字符串；
3、text：用于表示大文本数据。

数据表的基本操作：

创建数据表指的是在已存在的数据库中建立新表。

注意：在操作数据库之前，应该使用“use 数据库名”指定操作是在哪个数据库中进行，否则会出错。

创建数据表：

为了验证数据表是否创建成功，需要使用show tables语句查看数据库中所有的数据表：
Show tables;

查看数据表中的内容：
方式①：desc 数据表名;
方式②：show create table 表名;

修改数据表：

1.修改表名
Alter table 旧表名 rename [to] 新表名；

2.修改字段名
Alter table 表名 change 旧字段名 新字段名 新数据类型；

3.修改字段的数据类型
Alter table 表名 modify 字段名 数据类型;

4.添加字段
Alter table 表名 add 新字段名 数据类型;

5.删除字段
Alter table 表名 drop 字段名;

6.修改字段的排列位置
Alter table 表名 modify 字段名 1 数据类型 first|after 字段名2;

删除数据表

注意：创建数据表时，表和表之间可能会存在关联，要删除这些被其他表关联的表比较复杂。这里的删除是指没有关联关系的数据表。
Drop table 表名;

表的约束：

主键约束：primary key (包含唯一且不为空）

非空约束：not null

唯一约束：unique

默认约束：default

外键约束：foreign key

1、空、RESTRICT、NO ACTION（立即检查外键约束） 删除：从表记录不存在时，主表才可以删除。删除从表，主表不变 更新：从表记录不存在时，主表才可以更新。更新从表，主表不变；
2、CASCADE 删除：删除主表时自动删除从表。删除从表，主表不变 更新：更新主表时自动更新从表。更新从表，主表不变；
3、SET NULL 删除：删除主表时自动更新从表值为NULL。删除从表，主表不变 更新：更新主表时自动更新从表值为NULL。更新从表，主表不变；

数据的基本操作：

添加数据：insert

更新数据：update

update 表名 set 字段名1=值1[，字段名2=值2，…] [where 条件表达式]

删除数据：delete

方式①、delete from 表名 [where 条件表达式]
方式②、Truncate [table] 表名
二者的区别：
1、delete语句后面可以跟where子句，通过指定where子句中的条件表达式只删除满足条件的部分记录，而truncate语句只能用于删除表中的所有记录。
2、使用truncate语句删除表中的数据后，再次向表中添加记录时，自动增加字段默认初始化值重新从1开始，而使用delete语句删除表中的所有记录后，再次向表中添加记录时，自动增加字段的值为删除时该字段的最大值加1。

查询

单表查询：

简单查询：

查询所有字段：Select * from 表名；
查询指定字段：Select 字段名1，字段名2，… from 表名；

按条件查询：where字句指定查询条件；

（in，bewteen…and…，is null，distinct，like，and，or）
and的优先级比or的优先级高。

单表高级查询：聚合函数

1、count()函数
2、sum()函数
3、avg()函数
4、max（）函数
5、min（）函数
6、分组函数：
①、单独使用group by分组
每个分组只返回一条结果
②、group by和聚合函数一起使用
Group by和聚合函数一起使用，可以统计出某个或者某些字段在一个分组中最大值、最小值、平均值等
③、group by和having关键字一起使用
Having关键字和where关键字的作用相同，都用于设置条件表达式对查询结果进行过滤，两者的区别在于，having关键字后可以跟聚合函数，而where不能，通常情况下having关键字都和group by 一起使用，用于对分组后的结果进行过滤。

7、limit限制查询条数：
Select 字段名1，字段名2，… from表名 limit[offset，] 记录数
Offset表示偏移量，如果偏移量为0，则查询结果从第1条记录开始。Offset为可选值，如果不指定默认为0，第二个参数“记录数”表示返回查询记录的条数。

函数大全(列表)：

http://www.cnblogs.com/xuyulin/p/5468102.html

多表查询

外连接

外连接可分为：左连接、右连接、完全外连接。

如何解决mysql不支持完全外连接？

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。

UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。

内连接

交叉连接

没有 WHERE 子句的交叉联接将产生连接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。

多表查询练习

首先创建两张表student、score，并插入如下数据：

student表：（id属性自增，且为主键）

score表：（其中id属性为主键，自增，插入数据时，插入null，其自动生成）

创建完成后，对两张表进行如下操作：

1、查询student表的第2条到4条记录；
单表查询：
select * from student limit 1,3;
（使用limit关键字，第一个数字为偏移量，默认为0，从第一条数据开始；第二个数为查询记录数）

2、从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息；
单表查询：简单无条件查询。
select id,name,department from student;

3、student表中查询计算机系和英语系的学生的信息；
单表查询：查询条件（院系为计算机系和英语系）
select * from student where department in(‘计算机系’,’英语系’);

4、从student表中查询年龄27~30岁的学生信息；
刚开始，我自己写的语句只适用于今年，语句和结果如下：
select * from student where birth between 1987 and 1990;

可是这条语句过了今年就不再适用，后来在老师的指导下，查询函数后，对上述语句进行修改，如下：
select *,year(sysdate())-birth age from student where year(sysdate())-birth between 27 and 30;
select *,year(now())-birth age from student where year(now())-birth between 27 and 30;
上面两条语句，一共使用了三个函数，now()、sysdate()的方法的功能一样，都是返回当前时间，年-月-日 时-分-秒；而year(date)函数用来返回当前日期的年份。

查询结果如下：

5、从student表中查询每个院系有多少人；
统计每个院系有多少人，需要使用聚合函数count(*)用于统计人数，查询结果按院系分组；
select department,count(*) from student group by department;

6、从score表中查询每个科目的最高分；
使用了聚合函数max(grade)，求最高分数，并用课程名(c_name)分组。
select c_name,max(grade) from score group by c_name;

多表查询：

7、查询李四的考试科目（c_name）和考试成绩（grade）；
从两个表中查询，条件式student.id=score.id；且查询的学生名称为“李四”。
select name,c_name,grade from student s,score s1 where s.id=s1.stu_id and name=’李四’;

8、用连接的方式查询所有学生的信息和考试信息；
用左连接将两个表连接起来。
select * from student left join score on student.id = score.stu_id;

9、计算每个学生的总成绩
使用sum()函数，sum(grade)查询总成绩，以学生名(学生id)分组，我使用的having函数，where条件句不能放在group by语句的后面，而having可以；

方法①：select s.id,name,sum(grade) from student s,score sc group by stu_id,name having s.id=sc.stu_id;

方法②：select student.id,name,sum(grade) from student,score where student.id=score.stu_id group by id;
使用where条件句，where条件查询后，再用group by将其分组。

10、计算每个考试科目的平均成绩；
查询平均成绩，使用avg()函数，group by分组。
select c_name,avg(grade) from score group by c_name;

11、查询计算机成绩低于95的学生信息；
方式①：从两个表中查询，连接条件是sc.stu_id=s.id and sc.c_name=’计算机’ and sc.grade<95;

select s.id,name,sex,birth,department,address,c_name,grade from student s,score sc where sc.stu_id=s.id and sc.c_name=’计算机’ and sc.grade<95;

方式②：使用嵌套查询：
先从score中查出计算机成绩低于95分的学生的stu_id；然后通过查询出来的stu_id来查询对应的学生的信息。
select * from student where id in(select stu_id from score where c_name=’计算机’ and grade<95);

12、将计算机考试成绩按从高到低进行排序；
select s.id,name,c_name,grade from student s,score sc where c_name=’计算机’ and s.id=sc.stu_id order by grade desc;

13、查询姓张或者姓王的同学的姓名、院系和考试科目及成绩；
使用模糊查询：like
select s.id,name,department,c_name,grade from student s,score sc where name like ‘张%’ and s.id=sc.stu_id or name like ‘王%’ and s.id=sc.stu_id;