9-13课堂笔记MySQL

 

课堂笔记

操作数据(查询)

1.1.    distinct

语法

         Select[distinct] 列名[,列名]….from 表名

注意事项

         *可以替代列名, 表示所有列, 但是通常我们为了提高代码的可读性, 不使用*

         DISTINCT为过滤重复记录

         如果DISTINCT后面跟多列, 是过滤掉多列合并之后的重复

练习

         查询employee表中所有记录

select id,username,gender,birthday,position,salary,resume from user;

         查询employee表中所有人的薪水

select username,salary from user;

1.2.         列名表达式

语法

         Select 列名|表达式[,列名|表达式]….from 表名

注意事项

         表达式只是显示时起作用, 不会改变数据库中的值

1.3.    as

Select 列名 as 别名 from 表名

注意事项

         起别名时AS可以省略

         不会改变数据库中的值

1.4.    where

语法

         Selet 列名 from 表名[where 条件语句] WHERE子句中的运算符

比较运算符	>, <, >=, <=, =, <>	注意不等于和Java中不同, 是<>
	Between…and…	某一区间内的值, 从 ... 到 ...
	In(列表)	在列表之中, 例: in(1,2,3) 代表1或2或3
	Like(表达式)	模糊查询, %代表多个字符, _代表单个字符
	Is null	判断是否为NULL
逻辑运算符	and &&	与, 两边都为TRUE结果为TRUE
	or||	或, 一边为TRUE结果就为TRUE
	not !	非, 将表达式结果取反

练习

         查询英语分数在80-90分之间的

         select name,english from student where english>=80 and english<=90;

查询语文分数为81,82,83的学生

select name,english from student where english in(80,90,82);

查询所有姓张的学生的成绩

select name,english,math,chinese from student where name like '张%';

查询除了姓张和姓李的学生总分

         select name,english,math,chinese

from student

where name not like '张%'

and name not like '李%';

 

select name,english,math,chinese

from student

where name like '张%'

or name like '李%';

 

1.5.    order by

语法

         Select 列名 from 表名 order by 列名as|dese

注意事项

         ORDER BY 指定排序的列名可以是表中的列名, 也可以是SELECT语句后面起的别名

         ASC为升序, DESC为降序

         ORDER BY应在查询语句的结尾

练习

         对数学成绩排序后输出

         select name,math from student order by math;

         查询总分, 从高到低显示

         select name '姓名',chinese+math+english '总分' from student order by 总分 desc;

         选择所有姓张的学生的英语成绩, 并从高到低排序

         select name,english from student where name like '张%' order by english desc;

         查询学生成绩, 按照语文从高到低排序, 如果语文相同, 按照英语从高到低排序

         select * from student order by chinese desc,english desc;

1.6.    count函数

语法

Select count(*)|count(列名) from 表名[where 条件语句]

注意事项

         COUNT(列名)的方式是统计指定列中有多少条记录, 不包括值为NULL的

         COUNT(*)则是统计表中有多少条数据

         COUNT(DISTINCT 列名) 统计不重复的记录数

         如果加上WHERE子句, 则是统计满足条件的记录

练习

         统计student表中有多少条记录

         select count(*) from student;

         统计学生语文成绩大于80的有多少人

         select count(*) from student where chinese>80;

         统计总分大于250的有多少人

         select count(*) from student where english+math+chinese>250;

         统计参加英语考试的有多少人

         select count(english) from student;

1.7.    SUM函数

语法

         SELECT SUM(列名) FROM 表名 [WHERE 条件语句];

注意事项

         计算指定列中所有记录的和, 如果有WHERE子句则计算满足条件的记录

练习

         计算所有学生的数学成绩总和

         select sum(math) from student;

         显示所有学生的语文成绩总和, 数学成绩总和, 英语成绩总和

         select sum(chinese),sum(math),sum(english) from student;

         计算所有学生的分数总和

         select sum(chinese)+sum(math)+sum(english) from student;

         统计英语平均分

         select sum(english)/count(*) from student;

select sum(english)/count(english) from student;

 

1.8.    AVG函数

语法

         SELECT AVG(列名) FROM 表名 [WHERE 条件语句];

注意事项

         计算指定列的平均值, 如果有WHERE子句, 则计算满足条件的记录

         AVG()统计平均数不包含NULL值

练习

         计算英语平均分

         select avg(english) from student;

         计算总分平均分, MySQL不支持组函数嵌套使用.

         select sum(english+math+chinese)/count(*) from student;

1.9.    MAX / MIN函数

语法

         SELECT MAX(列名) FROM 表名 [WHERE 条件语句];

         SELECT MIN(列名) FROM 表名 [WHERE 条件语句];

注意事项

         获取指定列最高/最低值, NULL不参与统计

练习

         统计总分最高分和最低分

         select max(english+math+chinese),min(english+math+chinese) from student;

1.10. Group by (重点)

语法

         Select 列名 from表名 group by 列名[having 条件语句]

注意事项

         按照某列归类

         HAVING和WHERE类似, 但HAVING是作用于组, 其中可以使用组函数

SELECT列表中未包含在组函数中的列名, 只能是GROUP BY中的列名

HAVING中可以使用组函数, WHERE不能.

练习

         导入order.sql

         对订单表归类, 显示购买过哪些商品

         select product from orders group by product;

         select distinct product from orders;

         对订单表归类, 显示购买过哪些商品, 并显示每种购买了几个, 以及总价

         select product,count(product),sum(price) from orders group by product;

         查询总价大于5000的商品有哪几类

         select product,count(product),sum(price) sum_price from orders group by product having sum_price>5000;

2.      函数

2.1.    时间函数

注意date, datetime, timestamp之间的区别

 

ADDTIME(原时间, 增加值)   在某个时间上增加一段时间

         select addtime('18:23:01', '01:01:01');

select addtime(now(),'3:0:0');

CURRENT_DATE()                      当前日期

         select current_date();

CURRENT_TIME()                       当前时间

         select current_time();

CURRENT_TIMESTAMP()                   当前时间戳

         select current_timestamp();

DATE(时间)                                           返回制定时间的日期部分

         select date('2011-02-14 18:00:00');

DATE_ADD(日期,INTERVAL 增加值 类型)            在指定日期上对某个字段增加

         select date_add('2011-02-14 23:00:00', interval 10 month);

DATE_SUB(日期,INTERVAL 减少值 类型)             在指定日期上对某个字段减少

         select date_sub('2011-02-14 23:00:00', interval 1 year);

DATEDIFF(日期1, 日期2)               计算两个日期之间的差值

         select datediff('2000-02-14', '2001-02-14');

NOW()               当前时间

         select now();

YEAR|MONTH|DATE|HOUR|MINUTE|SECOND(时间)           获取指定时间的某个字段

         select year('2011-02-14 23:00:00');

         select hour('2011-02-14 23:00:00');

2.2.    字符串函数

CHARSET(字符串)                                                           返回字符串字符集

         select charset(name) from student;

CONCAT(字符串1[, 字符串2]... )                                     连接字符串

         select concat('aaa', 'bbb', 'ccc');

INSTR(字符串, 子字符串)                                                    查找子字符串出现位置, 注意序号从1开始

         select instr('abc', 'a');

UCASE(字符串)                                                                         将字符串转为大写

         select ucase('aBc');

LCASE(字符串)                                                                          将字符串转为小写

         select lcase('aBc');

LEFT(字符串, 长度)                                                                从字符串左边取指定长度个字符

         select left('aBc',2);

LENGTH(字符串)                                                                      计算字符串长度

         select length('aBc');

REPLACE(字符串, 搜索字符串, 替换字符串)               将字符串中指定字符串替换为其他字符串

         select replace('abbcbbd', 'bb', 'ee');

STRCMP(字符串1, 字符串2)                                              逐个字符比较两个字符串, 如果是包含关系, 则返回长度差值

         select strcmp('abcc', 'abde');

         select strcmp('abc', 'ab');

SUBSTRING(字符串, 开始坐标[, 个数])                 从字符串中截取

         select substring('abcdef', 3);

         select substring('abcdef', 3, 2);

LTRIM(字符串)                                                                          去掉左边空白

         select ltrim('    abc   ');

         select concat('--', ltrim('    abc   '), '--');

RTRIM(字符串)                                                                         去掉右边空白

         select concat('--', rtrim('    abc   '), '--');

TRIM(字符串)                                                                  去掉左右两边空白

         select concat('--', trim('    abc   '), '--');

2.3.    数学函数

ABS(数字)                                                       求绝对值

         select abs(10);

         select abs(-10);

BIN(十进制数)                                              将十进制转换为二进制

         select bin(5);

HEX(十进制数)                                              将十进制转换为十六进制

         select hex(10);

CONV(数字, 原进制, 目标进制)  转换进制

         select conv(12, 10, 16);

         select conv(12, 10, 2);

         select conv(12, 16, 2);

CEILING(小数)                                               向上取整

         select ceiling(3.4);

FLOOR(小数)                                         向下取整

         select floor(3.4);

ROUND(小数)                                       四舍五入

         select round(3.4);

select round(3.5);

FORMAT(小数, 保留位数)                        保留小数位

         select format(3.1415926, 2);

LEAST(值,值[,值]...)                                     取最小值

         select least(1,2,3,4);

         select least('a', 'b', 'c', 'd');

GREATEST(值,值[,值]...)                    取最大值

         select greatest(1,2,3,4);

         select greatest('a', 'b', 'c', 'd');

MOD(数字, 数字)                                       取余

         select mod(3,2);

         select 3%2;

RAND()                                                             生成随机数, 14位小数, 0 <= n <= 1

         select rand();

3.      表的约束

约束的作用

限定某一列上的数据, 阻止非法数据录入, 提高程序健壮性.

3.1.    唯一约束 unique

unique约束的字段在整张表中唯一, 不可重复, 不包括多个NULL

 

创建表时设置唯一

create table test (

         id int,

         name varchar(20) unique

);

 

删除唯一约束

show create table test; 发现唯一索引名叫name

alter table test drop index name;

 

添加唯一约束

alter table test change name name varchar(20) unique;

3.2.    非空约束 not null

not null约束的字段不能为空

 

创建表时设置非空

create table test1 (

         id int,

         name varchar(20) not null

);

 

删除非空约束

alter table test1 change name name varchar(20);

 

添加非空约束

alter table test1 change name name varchar(20) not null;

3.3.    主键约束 primary key

通常我们在设计表的时候需要给每一条记录一个独有的标识, 我们就用主键来约束这个标识.

primary key用来标识一个字段, 这个字段是非空且唯一的.

 

创建表时设置主键

create table test2(

id int primary key,

name varchar(20)

);

 

删除主键

alter table test2 drop primary key;

 

在制定列上添加主键

alter table test2 change id id int primary key;

alter table test2 add primary key(id);

 

设置主键自动增长

create table test3(

id int primary key auto_increment,

name varchar(20)

);

 

删除自增长

alter table test3 change id id int;

 

设置自增长

alter table test3 change id id int auto_increment;

 

UUID主键

128位的2进制, 32位16进制加上4个-

java.util.UUID.randomUUID().toString()

3c2372a4-da2a-4470-b17a-f2e50ac79636

3.4.    外键约束 foreign key

foreign key约束某一列的值是参照另外一列

 

创建表时添加外键约束

create table husband(

         id int primary key,

         name varchar(20) not null

);

create table wife(

         id int primary key,

         name varchar(20) not null,

         husband_id int,

         constraint husband_id_fk foreign key(husband_id) references husband(id)

);

wife表的husband_id的值必须是husband表中的id

 

被外键引用的记录不能删除, 如果想要删除某条被引用的记录, 需要找到引用这条记录的记录, 解除关联

 

被外键引用的表不能删除, 如果想要删除被引用的表, 需要删除所有引用此表的外键

 

删除外键约束

alter table wife drop foreign key husband_id_fk;

 

添加外键约束

alter table wife add constraint husband_id_fk foreign key(husband_id) references husband(id)

 

4.      表的设计

4.1.    单独的实体

public class User{     private int id;     private String name;     private int age; }

 

user

 

id	name	age
1	张三	18
2	李四	20
3	王五	19

 

4.2.    一对多、多对一

foreign key

public class Employee {     private int id;     private String name;     private Department department; }

public class Department {     private int id;     private String name;     private Set<Employee> employees; }

一对多

多对一

 

 

department                                               employee

 

id	name
1	开发部
2	市场部

id	name	department_id
1	张三	1
2	李四	1
3	王五	2

 

多的一方设置外键

create table department(

         id int primary key auto_increment,

         name varchar(20)

);

 

create table employee(

         id int primary key auto_increment,

         name varchar(20),

         department_id int,

         constraint department_id_fk foreign key(department_id) references department(id)

);

 

insert into department(name) values('开发部');

insert into department(name) values('市场部');

insert into employee(name, department_id) values('张三',1);

insert into employee(name, department_id) values('李四',1);

insert into employee(name, department_id) values('王五',2);

4.3.    一对一

public class Husband {     private int id;     private String name;     private Wife wife; }

public class Wife {     private int id;     private String name;     private Husband husband; }

一对一

 

foreign key

 

 

unique

husband                                                 wife

 

id	name
1	张三
2	李四

id	name	husband_id
1	冰冰	2
2	志玲	1

 

独立外键, 没有依赖关系, 两个表的对象都可以独立存在.

create table husband(

         id int primary key auto_increment,

         name varchar(20)

);

 

create table wife(

         id int primary key auto_increment,

         name varchar(20),

         husband_id int,

         constraint husband_id_fk foreign key(husband_id) references husband(id)

);

 

insert into husband(name) values('张三');

insert into husband(name) values('李四');

insert into wife(name, husband_id) values('冰冰',2);

insert into wife(name, husband_id) values('志玲',1);

 

 

public class Person {     private int id;     private String name;     private IdCard idCard; }

public class IdCard {     private int id;     private String num;     private Person person; }

一对一

 

foreign key

 

person                                                 idcard

 

id	name
1	张三
2	李四

id	num
2	110123199009091234
1	120123200001011234

 

主键即外键, 分为主表和从表, 从表依赖于主表, 从表中的对象不能单独存在.

注意从表的主键, 不能自动增长.

 

create table person (

         id int primary key auto_increment,

         name varchar(20)

);

 

create table idcard (

         id int primary key,

         num varchar(20),

         constraint id_fk foreign key(id) references person(id)

);

 

insert into person(name) values('张三');

insert into person(name) values('李四');

insert into idcard(id,num) values(2,'110123199009091234');

insert into idcard(num,id) values('110123199009091234', 1);

4.4.    多对多

public class Student {     private int id;     private String name;     private Set<Teacher> teachers; }

public class Teacher {     private int id;     private String name;     private Set<Student> students; }

多对多

 

foreign key

foreign key

 

 

student                                                                        student_teacher                                teacher

 

id	name
1	张三
2	李四
3	王五

student_id	teacher_id
1	1
2	1
2	2
3	2

id	name
1	张孝祥
2	黎活明

 

 

 

用一张关系表保存多对多的关系, 有两列分别引用两张表的主键, 并且这两列组合起来成为联合主键

 

create table student (

         id int primary key auto_increment,

         name varchar(20)

);

 

create table teacher (

         id int primary key auto_increment,

         name varchar(20)

);

 

create table student_teacher(

         student_id int,

teacher_id int,

primary key(student_id, teacher_id),

constraint student_id_fk foreign key(student_id) references student(id),

constraint teacher_id_fk foreign key(teacher_id) references teacher(id)

);

 

insert into student(name) values('张三');

insert into student(name) values('李四');

insert into student(name) values('王五');

insert into teacher(name) values('zxx');

insert into teacher(name) values('lhm');

insert into student_teacher values(1,1);

insert into student_teacher values(2,1);

insert into student_teacher values(2,2);

insert into student_teacher values(3,2);

 

5.      多表查询

5.1.    连接查询

当我们要插叙的数据不只是在一张表中, 我们就需要使用多表连接查询.

例如: 查询员工所在的部门名称, 查询部门中员工名称, 都需要查询两张表.

 

注意:

在多表连接查询的时候, 如果没有有效的连接条件, 所有表中的行会互相连接, 形成笛卡尔集.

为了避免笛卡尔集, 可以再where后加入有效的连接条件

 

 

练习:

查询出公司所有员工姓名, 所在部门名

查询出开发部所有员工名

查询出张三所在部门名称

5.2.    多表连接

多张表连接查询, 一张表外键引用另外一张表, 另外一张表再引用其他表.

例如: 员工表引用部门, 部门表引用城市表. 这时如果想根据员工查城市, 或者根据城市查员工就需要将三张表连接查询

 

准备工作:

创建城市表, id主键自动生成, 带有名称

插入两条记录, 北京和上海

在部门表添加city_id, 外键引用城市表的id

将开发部的地址改为北京, 市场部的地址改为上海

 

练习:

查询所有员工, 员工所属部门以及部门所在城市

查询北京的所有员工

5.3.    自连接

自己和自己连接, 当前表的外键引用自己的主键.

例如: 员工的经理也是员工, 应该在员工表中添加一列经理id. 之后添加一个外键, 引用员工表的主键.

 

准备工作:

在员工表中添加manager_id, 外键引用员工表id

插入赵六, 孙七. 分别属于开发部和市场部.

将张三和李四的经理设置为赵六, 王五经理设置为孙七.

 

练习:

查询王五的经理姓名

查询赵六手下的员工名

5.4.    内连接、左外连接、右外连接(SQL99)

准备工作:

插入记录周八, 部门为空

插入部门财务部

 

1. 内连接

 

之前我们使用逗号和where子句进行的连接就是内连接. 标准语法应使用 inner join 和 on, 例如:

select e.name,d.name from employee e,department d where e.department_id=d.id;

select e.name,d.name from employee e inner join department d on e.department_id=d.id;

内连接会将两张表完全匹配连接条件的记录查询出来, 不满足的不会显示

 

2. 左外连接

 

使用 left outer join 和 on 关键字进行连接查询, 这时左表中不满足条件的记录也会被查询出来

例如: 查询所有员工的部门, 要将没有部门的周八查询出来. 这时需要查询出员工表中所有记录, 即使不满足连接条件, 周八也要显示.

 

3. 右外连接

 

使用 right outer join 和 on 关键字进行连接查询, 这时右表中不满足条件的记录也会被查询出来

例如: 查询每个部门下都有哪些员工, 要将没有员工的财务部显示出来.

 

4. 全外连接

MySql不支持全外连接full outer join, 可以使用union distinct来实现.

 

练习:

使用左外连接查询每个部门下都有哪些员工

使用右外连接查询所有员工所属部门

查询所有员工名和所在部门, 以及部门所在城市.

查询在上海工作的所有员工

查询张三的经理姓名

5.5.    使用组函数的多表查询

准备工作:

给员工表添加工资列, 并添加数据(5000-10000)

给员工表添加年龄列, 并添加数据(25-35)

 

练习:

查询每个部门的平均工资

查询每个部门的平均工资(包括没有部门的). 

查询每个城市的平均工资(包括没有城市的).

查询每个城市的平均工资(包括没有城市的), 只显示高于7000的, 并且按平均工资从高到低排序

查询北京市年龄30岁以上员工的平均工资

 

select > from > join on > where > group by > having > order by

 

组函数练习题:

1.       组函数处理多行返回一行吗?

2.       组函数是否计算空值?

3.       where子句是否可为组函数进行过滤?

4.       查询公司员工工资最大值, 最小值, 平均值, 总和.

5.       查询公司各部门的工资最大值, 最小值, 平均值, 总和.

6.       查询各个城市的员工人数.

7.       查询员工最高工资和最低工资的差距.

8.       查询各个经理手下员工的平均工资.

9.       统计员工人数, 显示如下格式

6.      子查询

一条查询语句需要使用另一条查询语句的结果.

例如: 查询工资比张三高的员工

 

子查询在主查询之前完成

子查询结果被主查询所使用

子查询要包含在括号内

子查询放在条件右侧

 

练习:

查询工资最高的员工是谁

查询所有城市中的最高平均工资是多少

查询平均工资高于公司平均工资的部门有哪些

查询公司所有经理的信息

查询平均工资最低的部门中的最高工资

查询平均工资最低的部门的经理的详细信息

查询25岁以上工资最高的员工的详细信息

 

多行子查询

ANY 集合中只要有一个元素满足条件, 结果就成立.

ALL 集合中所有元素都满足条件, 结果才成立

IN 相当于 =ANY

7.      中文乱码问题

MySQL有六处使用了字符集, 分别为:

 

client:                客户端使用的字符集

connection:      连接数据库的字符集, 如程序没有指明, 就按照服务器端默认字符集.

database:                  数据库服务器中库的字符集, 如建库时没有指明, 将使用服务器安装时指定的字符集.

result:                数据库给客户端返回时使用的字符集, 如没有指明, 使用服务器默认的字符集.

server:               服务器安装时指定的默认字符集.

system:             数据库系统使用的字符集.