MYSQL常识

 1.      MySQL安装与配置

1.1.      安装

将mysql-5.0.27-win32.rar压缩文件解压

 

运行Setup.exe

 

 

Typical: 常用模式, 包含一些常用的模块

Complete: 完整模式, 包含所有模块

Custom: 自定义, 根据需要选择安装

 

选择所有组件, 安装到本地硬盘

 

选择安装路径

 

确定所选内容, 开始安装

 

选择跳过登陆注册

 

安装完成进入配置页面

1.2.      配置

开始配置

Detailed: 详细配置

Standard: 标准配置

我们需要根据用途选择, 所以选详细配置

 

服务器类型

关系到内存, 硬盘空间, 以及CPU使用

Developer: 程序员机器, 占用尽量小的资源

Server: 服务器, 占用中等资源

Dedicated: 数据库专用服务器, 占用所有资源

 

数据库用途

 

数据存放路径

默认为安装目录

 

并发连接数

根据实际需要选择

 

网络设置

启用TCP/IP连接: 如果不启动, 只能本地操作

启用精确模式: 不允许任何语法错误, 推荐开启

 

默认编码

Latin1, 不支持中文

UTF-8为国际通用码表

由于我们Windows本地码表为GBK, 也可选择GBK, 省去乱码的麻烦

 

系统选项

将MySQL安装为Windows服务, 自动启动

将MySQL目录添加到PATH环境变量, 以后可以直接从命令行启动

 

权限选项

设置root用户的密码

 

运行配置项

1.3.      运行

在cmd.exe中输入mysql –uroot –proot

或者mysql –uroot –p 回车之后输入密码, 密码不可见

2.      数据库概念

2.1.      数据库服务器、数据库和表之间的关系

所谓安装数据库服务器, 只是在机器上安装了一个数据库管理程序, 这个管理程序可以管理多个数据库.

一般开发人员在设计项目的时候会针对每一个应用创建一个数据库.

为了保存每一类实体, 在一个数据库中创建多个表.

 

客户端

服务器

数据库程序

表

数据库

数据库

表

表

2.2.      数据在数据库中的存储方式

id=1                      id=2

name=Tom            name=Jerry

age=18                  age=20

 

列(column)              列(column)        列(column)

id	name	age
1	Tom	18
2	Jerry	20

 

行(row)

行(row)

 

 

每一行(一条记录）对应Java中的一个对象

每一列对应对象的一个属性

3.      操作库

创建库

CREATE DATABASE [IF NOT EXISTS] 数据库名 [参数[ 参数] [ 参数]...];

参数:

       CHARACTER SET 码表名

       COLLATE 校对规则名   参见帮助文档第10章

命令：show character set;可以显示MySQL支持的字符集和校对规则

 

Show collation like ‘latin1%’;   显示latin1字符集可以采用的校对规则

 

显示库

       SHOW DATABASES;

显示数据库创建语句

       SHOW CREATE DATABASE 数据库名; 查看数据库所采用的字符集

修改库

       ALTER DATABASE 数据库名[ 参数[ 参数][ 参数]...];

       注意: 不能改数据库的名字

删除库

       DROP DATABASE [IF EXISTS] 数据库名;

练习:

       创建一个数据库db1

创建一个数据库db2指定字符编码, 校对规则

显示所有数据库

显示db2的字符编码

将db2的字符编码设置为gbk

删除db2数据库

4.      操作表

操作表之前使用需要先确定使用哪个数据库

       USE 数据库名

创建表

CREATE TABLE 表名(列名 类型[,列名 类型][,列名 类型]...);

具体类型说明可参见MySQL文档第11章

查看所有表

       SHOW TABLES;

查看表的创建语句

       SHOW CREATE TABLE 表名;

显示表结构

       DESC 表名;

修改表名

       RENAME TABLE 原表名 TO 新表名;

修改字符集

       ALTER TABLE 表名 CHARACTER SET 字符集名;

删除表

       DROP TABLE 表名;

练习

       创建表employee, 包含以下字段:

id                          整型                            主键, 自动生成

name                     字符                            非空

gender                   字符或bit                    非空

birthday                 日期                           

entry_date             日期                            非空

position                 字符                           

salary                    小数                           

resume                  大文本

将表名改为user

显示表结构

5.      操作列

追加列

    ALTER TABLE 表名 ADD 列名 类型[,列名 类型][,列名 类型]...;

修改列类型

       ALTER TABLE 表名 MODIFY 列名 类型[,列名 类型][,列名 类型]...;

修改列

       ALTER TABLE 表名 CHANGE COLUMN 原列名 新列名 类型;

删除列

       ALTER TABLE 表名 DROP 列名;

练习

       添加一列用来存储照片

将name长度修改为30

将name改名为username

删除photo列

 

 

Insert into

Update

delete

select

6.      操作数据(增删改)

6.1.      插入

语法:

       INSERT INTO 表名[(列名[,列名]...)] VALUES(值[,值]...);

注意事项:

       插入值类型必须与对应列的数据类型一致

       数据不能超出长度

       插入值得为之必须与列名顺序一致

       字符和日期数据要放在单引号中

       插入空值使用null

       如果不指定插入哪一列, 就是插入所有列

中文数据

       由于默认码表是utf8, 而cmd.exe的码表是gbk, 在插入中文数据的时候会报错, 所以我们需要修改客户端码表

       先查看系统变量: SHOW VARIABLES LIKE 'character%';

       修改客户端码表: SET character_set_client=gbk;

       这样就解决了中文插入的问题, 但在查询数据的时候仍然显示为乱码, 这是因为mysql向cmd传输数据的时候使用的是utf8

       修改输出数据的码表: SET character_set_results=gbk;

练习

       向user表中插入3条包含中文的数据

insert into user(id,username,gender,birthday,position,salary,resume)

values(1,'张三','男','1990-9-9','程序员',6000,'介绍');

 

insert into user(id,username,gender,birthday,position,salary,resume)

values(2,'李四','男','1990-9-9','程序员',6000,'介绍');

 

insert into user(id,username,gender,birthday,position,salary,resume)

values(3,'王五','男','1990-9-9','测试',6000,'介绍');

 

insert into user(id,username,gender,birthday,position,salary,resume)

values(4,'赵六','男','1990-9-9','美工 ',6000,'介绍');

 

6.2.      修改

语法

       UPDATE 表名 SET 列名=值[,列名=值]...[WHERE 条件语句];

注意事项

       WHERE子句选择满足条件的行进行更新, 如果不写, 则更新所有行

练习

       将所有员工的薪水改为5000

       将姓名为张三的员工薪水改为7000

       给李四加薪3000, 并且职位改为经理

6.3.      删除

语法

       DELETE FROM 表名 [where 条件语句]

注意事项

       如果不加where子句, 将删除表中所有记录

       delete只能用作删除行, 不能删除某一列的值, 需要用update

       在delete和update的时候需要注意表与表之间的关联关系

       删除表中所有数据可以使用: TRANCATE 表名, 这种方式会删除旧表重新创建, 在数据较多的时候使用

练习

       删除所有美工

       删除表中所有数据

7.      备份恢复数据库

备份数据库

       输入quit退出mysql, 在cmd.exe中输入:

mysqldump –u用户名 –p密码 数据库名 > 文件名

恢复数据库

       进入mysql.exe之后, 使用数据库之后

       source 文件名

练习:

       备份指定数据库, 删除数据库, 恢复

8.      操作数据(查询)

8.1.      DISTINCT

语法

       SELECT [DISTINCT] 列名[, 列名]... FROM 表名

注意事项

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

       DISTINCT为过滤重复记录

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

练习

       查询employee表中所有记录

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

       查询employee表中所有人的薪水

select username,salary from user;

       查询employee表中包含哪些岗位

8.2.      列名表达式

语法

       SELECT 列名|表达式[,列名|表达式]... FROM 表名

注意事项

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

练习

       导入student.sql

source .....

查询所有学生的总分

select name,chinese+math+english from student;

       查询employee表, 将所有员工薪水*2显示

       select username,salary*2 from user;

8.3.      AS

       SELECT 列名 AS 别名 FROM 表名

注意事项

       起别名时AS可以省略

       不会改变数据库中的值

练习

       查询所有学生总分, 别名为总分

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

 

8.4.      WHERE

语法

       SELECT 列名 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 '李%';

 

8.5.      ORDER BY

语法

       SELECT 列名 FROM 表名 ORDER BY 列名 ASC|DESC;

注意事项

       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;

8.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;

8.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;

 

8.8.      AVG函数

语法

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

注意事项

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

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

练习

       计算英语平均分

       select avg(english) from student;

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

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

8.9.      MAX / MIN函数

语法

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

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

注意事项

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

练习

       统计总分最高分和最低分

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

8.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;

 

9.      函数

9.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');

9.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   '), '--');

9.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();

10. 表的约束

约束的作用

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

10.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;

10.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;

10.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

10.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)

11. 表的设计

11.1.  单独的实体

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

 

user

 

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

 

11.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);

11.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);

11.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);

12. 多表查询

12.1.  连接查询

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

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

 

注意:

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

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

 

 

练习:

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

查询出开发部所有员工名

查询出张三所在部门名称

12.2.  多表连接

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

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

 

准备工作:

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

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

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

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

 

练习:

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

查询北京的所有员工

12.3.  自连接

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

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

 

准备工作:

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

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

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

 

练习:

查询王五的经理姓名

查询赵六手下的员工名

12.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来实现.

 

练习:

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

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

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

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

查询张三的经理姓名

12.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.       统计员工人数, 显示如下格式

13. 子查询

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

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

 

子查询在主查询之前完成

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

子查询要包含在括号内

子查询放在条件右侧

 

练习:

查询工资最高的员工是谁

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

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

查询公司所有经理的信息

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

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

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

 

多行子查询

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

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

IN 相当于 =ANY

14. 中文乱码问题

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

 

client:             客户端使用的字符集

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

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

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

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

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