小白学习之路（数据库篇）之 MySQL（上）_查找公司所在的楼层(去重)dept

MySQL数据库

数据库

常用关系型数据库

• SQL Server
• Oracle
• MySQL
• DB2
• Postgress
• SyBase

使用命令行窗口连接MySQL数据库实例

​ mysql –u用户名 –p密码

​ mysql -hlocalhost –u用户名 –p密码

DBMS和DB

​ DBMS，数据库管理系统。数据库软件称为DBMS

​ DB是通过DBMS创建和操作的容器。

数据库服务器、数据库和表的关系

• 所谓数据库服务器， 是指在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，般开发人员会针对每一个应用创建一个数据库。

• 为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。

• 数据库服务器、数据库和表的关系如图所示：
  

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

MySQL数据库

• DDL(Data Definition Language 数据定义语言)

  用于操作对象和对象的属性。

  Create/Drop/Alter

• DCL(Data Control Language 数据控制语句)

  对数据库对象的权限，这些操作的确定使数据更加的安全。

  Grant/Revoke

• DML(Data Manipulation Language 数据操控语言)

  用于操作数据库对象中包含的数据，也就是说操作的单位是记录。

  Insert/Update/Delete

• DQL(Data Query Language 数据查询语言)

  查询

  Select

• 语法规范

  >不区分大小写

  >MySQL的注释:

  • 单行注释:#

  • 单行注释：–

  • 多行注释：/* */

  >每个完整的SQL语句用;结束

  >sql语句可以写在一行或多行，各子句一般分行写

  >关键字不能分行写

• Windows CMD窗口

  命令：mysql -uroot -p

• 数据库连接工具

  Navicate

  SQLyog

显示所有数据库

show databases;
1

​ 返回数据库的名称列表。

使用数据库

use <数据库名称>;
1

创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name    
	[create_specification [, create_specification] ...] 

create_specification:    

    [DEFAULT] CHARACTER SET charset_name  
    [DEFAULT] COLLATE collation_name 
1
2
3
4
5
6
7

• CHARACTER SET：指定数据库采用的字符集
• COLLATE：指定数据库字符集的比较方式(校对规则)

查看数据库

SHOW CREATE DATABASE db_name;
1

修改数据库

DROP DATABASE [IF EXISTS] db_name;
1

MySQL常用数据类型

• VARCHAR、BLOB和TEXT类是变长类型。每个类型的存储需求取决于列值的实际长度。

创建表

CREATE TABLE table_name
(
	field1  datatype,
	field2  datatype,
	field3  datatype
);
1
2
3
4
5
6

field：指定列名 datatype：指定列类型

• 注意：创建表前，要先使用use db语句使用库。

  ​ 创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。

修改表

• 使用 ALTER TABLE 语句追加, 修改, 或删除列的语法

ALTER TABLE table
ADD		   (column datatype [DEFAULT expr]
		   [, column datatype]...);
1
2
3

ALTER TABLE table
MODIFY	   (column datatype [DEFAULT expr]
		   [, column datatype]...);
1
2
3

ALTER TABLE table
CHANGE    (old_column new_column datatype);
1
2

ALTER TABLE table
DROP	      (column);
1
2

• 使用 ALTER TABLE 语句修改表名的语法

ALTER TABLE table
RENAME TO	new_table;
1
2

RENAME TABLE table
TO	   new_table;
1
2

MySQL约束

• 约束

  ​ 数据类型告诉我们需要存储什么样子的数据，而约束告诉我们这些数据具体需要满足的规则。如：age int, 我们可以存储负整数，price float，我们可以存储负小数，但是负数是不满足实际规则的，因此我们需要约束。

• 常见约束

  • 检查约束check(MySQL目前不支持)
  • 非空约束not null
  • 唯一约束unique
  • 主键约束primary key ：唯一 且非空，主表
  • 外键约束foreign key ：在从表

主键约束（以dept表为例）

• 设置主键

​ 建表时一起设置主键

CREATE TABLE dept (
  deptno int(11) ,
  dname varchar(14) ,
  loc varchar(13) ,
  PRIMARY KEY (deptno)
) ;
#or
CREATE TABLE dept (
  deptno int(11) PRIMARY KEY  ,
  dname varchar(14) ,
  loc varchar(13)
) ;
1
2
3
4
5
6
7
8
9
10
11
12

​ 建表后，再设置主键

ALTER TABLE dept ADD PRIMARY KEY(deptno) ;
1

​ 删除主键约束：

ALTER TABLE dept DROP PRIMARY KEY;
1

• 主键值自增长

主键，是确定表中每一行数据唯一性的标识。主键常伴随自增。

​ 为主键建立自增：

​ >建表时，设置主键且自增

CREATE TABLE dept (
  deptno int(11)  auto_increment,
  dname varchar(14) ,
  loc varchar(13) ,
  PRIMARY KEY (deptno)
) ;
#or
CREATE TABLE dept (
  deptno int(11) PRIMARY KEY  auto_increment,
  dname varchar(14) ,
  loc varchar(13)
) ;
1
2
3
4
5
6
7
8
9
10
11
12

​ >设置主键后，再添加主键自增

ALTER TABLE table MODIFY column datatype auto_increment;
1

​ >删除主键且自增：

ALTER TABLE table MODIFY column datatype ;
ALTER TABLE tablename drop primary key ;
1
2

非空约束

NOT NULL

​ 添加非空约束：

ALTER TABLE table MODIFY  column datatype NOT NULL;
1

​ 删除非空约束：

ALTER TABLE table MODIFY [COLUMN] column datatype;
1

唯一约束

UNIQUE 保证约束的列的值唯一且不重复

​ 添加非空约束：

ALTER TABLE table ADD UNIQUE [unique_index_name] (column[,column2,column3,……]);
1

注： unique_index_name 唯一约束名可省略不写。省略不写生成的约束索引名与列名相同。

​ 删除非空约束：

ALTER TABLE table DROP index unique_index_name;
1

外键约束

• FOREIGN KEY

• 处理表之间关系

• 外键用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键

• 外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键的值必须等于另一个表中主键的某个值。

• 外键是表的一个字段，不是本表的主键，但对应另一个表的主键。定义外键后，不允许删除另一个表中具有关联关系的行。

• 外键的主要作用是保持数据的一致性、完整性。

  >主表（父表）：主键所在的表就是主表。
  >从表（子表）：外键所在的表就是从表。

​ 定义外键约束

​ 在建表语句中设置外键：

CREATE TABLE tablename(
	id int,
	……
	column  datatype,
	constraint <外键名> foreign key(column) references tablename2(column));
1
2
3
4
5

​ 建表后设置外键：

ALTER TABLE tablename ADD CONSTRAINT <外键名> foreign key (column) references tablename2(column);
1

​ 删除外键约束

alter table tablename drop foreign key  <外键名>;
1

处理级联问题

• on delete cascade

  主表某数据删除时，与主表数据相关的从表的数据也一起删除

• on update cascade

  主表某数据修改时，与主表数据相关的从表的数据也一起修改

• on delete set null

  主表某数据删除时，与主表数据相关的从表的数据的外键列为NULL

索引

• 索引:
  使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。是一种快速查询表中内容的机制，类似于新华字典的目录

• 索引的按个数分：

  • 单列索引是指基于单个列所创建的索引；
  • 复合索引是指基于两列或多列创建的索引。

• 索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度

• 在删除一个表时, 所有基于该表的索引会自动被删除

• 通过快速定位数据的方法，减少磁盘 I/O

• 自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建索引

• 手动创建: 用户可以在其它列上创建索引，以加快表查询速度，提高效率。

• 查询数据表索引

  SHOW INDEX FROM table;
  1

• 创建索引

CREATE INDEX index
ON table (column[, column]...);
1
2

​ 普通索引

ALTER TABLE tbl_name ADD INDEX index_name (column list);
1

​ 唯一索引

ALTER TABLE tbl_name ADD UNIQUE index_name (column list);
1

​ 主键索引

ALTER TABLE tbl_name ADD PRIMARY KEY index_name (column list);
1

• 删除索引

DROP INDEX index on tablename;
#or
ALTER TABLE tablename drop INDEX index;
1
2
3

​ 删除普通索引

DROP INDEX index_name ON name ;
#or
ALTER TABLE tbl_name DROP INDEX index_name;
1
2
3

​ 删除唯一索引

ALTER TABLE tbl_name DROP INDEX index_name ;
1

​ 删除主键索引

ALTER TABLE tbl_name DROP PRIMARY KEY index_name ;
1

什么时候要创建索引

• 列中数据值分布范围很广
• 列经常在 WHERE 子句或连接条件中出现
• 表经常被访问而且数据量很大

什么时候不要创建索引

• 表很小

• 列不经常作为连接条件或出现在WHERE子句中

• 表经常更新

主键、外键、索引的区别

数据操作语句

• Insert语句 (增加数据)

• Update语句 (更新数据)

• Delete语句 (删除数据)

• Select语句　(查找数据)

C : create R: Retrieve U:update D: delete
CRUD：描述软件系统中数据库或者持久层的基本操作功能。

Insert语句

使用 INSERT 语句向表中插入数据。

INSERT INTO	table [(column [, column...])]
VALUES		(value [, value...]);
1
2

​ 插入的数据应与字段的数据类型相同

​ 数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。

​ 在values中列出的数据位置必须与被加入的列的排列位置相对应。

​ 字符和日期型数据应包含在单引号中。

​ 当要对表中的所有字段添加数据时，表中的字段可省略不写；

​ 当写明表字段时，其值要与字段的位置一致。

例如：

INSERT INTO dept
VALUES
	(
		10,
		'财务部',
		'创意大厦10层',
		'0532-88123456'
	),
	(
		20,
		'研发部',
		'创意大厦8层',
		'0532-88123451'
	),
	(
		30,
		'销售部',
		'创意大厦9层',
		'0532-88123453'
	),
	(
		40,
		'行政部',
		'创意大厦10层',
		'0532-88123454'
	);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

Update语句

使用 update语句修改表中数据。

UPDATE 	tbl_name    
	SET col_name1=expr1 [, col_name2=expr2 ...]    
	[WHERE where_definition]
1
2
3

UPDATE语法可以用新值更新原有表行中的各列。
SET子句指示要修改哪些列和要给予哪些值。
WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。

例如：

#将deptno=40的地址loc值改为创意大厦11层
UPDATE dept
SET loc = '创意大厦11层'
WHERE
	deptno = 40;
1
2
3
4
5

Delete语句

使用 delete语句删除表中数据。

delete from tbl_name       
	[WHERE where_definition] ;
1
2

如果不使用where子句，将删除表中所有数据。

Delete语句不能删除某一列的值（可使用update）

使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。

删除表中数据也可使用TRUNCATE TABLE 语句，它和delete有所不同，原理是先删除表再创建新表。

例如：

#删除eptno=40的部门信息
DELETE
FROM
	dept
WHERE
	deptno = 40;
1
2
3
4
5
6

Select语句

• 完整的select语句

SELECT [DISTINCT] */{column1, column2. column3..}
FROM	table
WHERE  	condition
GROUP BY column1
HAVING    condition
ORDER　BY  column1  [asc/desc]
LIMIT  startPos, len;
1
2
3
4
5
6
7

执行顺序：from -> where -> group by -> 聚集函数 -> having -> select -> order by -> limit

• 基本select语句

SELECT [DISTINCT] */{column1, column2. column3..}
		FROM	table;
1
2

Select 指定查询哪些列的数据。

column指定列名。

*号代表查询所有列。

From指定查询哪张表。

DISTINCT可选，指显示结果时，是否剔除重复数据

• DISTINCT

  去除重复

  用在select后，对其后的列的数据进行去重。

例如：

#查找公司所在的楼层（去重）dept
SELECT DISTINCT
	loc
FROM
	dept;
1
2
3
4
5

• 在select语句中可使用表达式对查询的列进行运算

SELECT *|{column1｜expression, column2｜expression，..}
		FROM	table;
1
2

• 在select语句中可使用起别名（使用as语句）

SELECT column as 别名 from 表名;
1

别名，相当于列的别称，类似于人的小名或绰号。别名的另一个用处可以对函数的列或表达式的列，一个虚拟的列名称。

别名的示例
Select deptno as ‘部门编号’,dname as ‘部门名称’ from dept;
Select deptno ‘部门编号’,dname ‘部门名称’ from dept;
注：as 可以省略 写。

例如：

#查询员工的姓名、薪资和薪资提升5%的金额，输出的列名显示“员工姓名”、“现有薪资”和“上调整后薪资”。
SELECT
	ename '员工姓名',
	sal '现有薪资',
	sal * 1.05 '上调整后薪资'
FROM
	emp;
1
2
3
4
5
6
7

• 在where子句中经常使用的运算符
  

• < >= <= = <>
  <>在 SQL 的一些版本中，也可被写成 !=

• BETWEEN… AND …
  可与 column >= M and column <= N对换使用

• Like——模糊查询
  % 代表零个或多个任意字符；
  _ 代表一个任意字符，例first_name like ‘_a%’;

例如：

#查找EMP表中hiredate在1981-05-01到1981-10-31的员工信息
SELECT
	*
FROM
	emp
WHERE
	hiredate BETWEEN '1981-05-01'
AND '1981-10-31';

#查找EMP表中名字中带“一”字的员工
SELECT
	*
FROM
	emp
WHERE
	ename LIKE '%一%';

#查找EMP表中名字的第二个字是“一”字的员工
SELECT
	*
FROM
	emp
WHERE
	ename LIKE '_一%';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

• AND
  与
  查找条件都满足的记录
• OR
  或
  查询条件中至少满足一种情况的记录

例如：

#查找女性且名字中带“十”字的员工
SELECT
	*
FROM
	emp
WHERE
	sex = '女'
AND ename LIKE '%十%';

#查找女性或名字中带“十”字的员工
SELECT
	*
FROM
	emp
WHERE
	sex = '女'
OR ename LIKE '%十%';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

• IN
  指定针对某个列的多个可能值，在…范围内
  有时可与OR互换
  与之相反，NOT IN，不在…范围内
• IS NULL——为空
  判断字段值为空
  与之相反的是IS NOT NULL

例如：

#查找EMP表中员工编写为7521、7566、7782的员工
SELECT
	*
FROM
	emp
WHERE
	empno IN (7521, 7566, 7782);

#查找EMP表中工资为空的员工信息
SELECT
	*
FROM
	emp
WHERE
	sal IS NULL;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• 使用order by 子句排序查询结果。

SELECT column1, column2. column3..
		FROM	table
		order by column asc|desc
1
2
3

Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的列名。

Asc 升序、Desc 降序 （默认是升序）

ORDER BY 子句应位于SELECT语句的结尾。

例如：

#查询emp表的信息按工资+提成总和从高到低的顺序输出
SELECT
*
FROM emp
	ORDER BY sal + IFNULL(commission,0) DESC;
1
2
3
4
5

• Count(列名)返回某一列，行的总数

Select count(*)|count(列名) from tablename
		[WHERE where_definition]
1
2

例如：

#统计岗位是职员的员工的个数？
SELECT
	COUNT(*) 职员人数
FROM
	emp
WHERE
	job = '职员';
1
2
3
4
5
6
7

• Sum函数返回满足where条件的行的和

Select sum(列名)｛,sum(列名)…｝ from tablename
		[WHERE where_definition] 
1
2

注意：sum仅对数值起作用，否则会报错。
注意：对多列求和，“，”号不能少。

例如：

#统计部门编号为10的的各岗位的员工的工资总和？
SELECT SUM(sal)
FROM emp
WHERE deptno = 10
GROUP BY job;
1
2
3
4
5

• AVG函数返回满足where条件的一列的平均值

Select avg(列名)｛,avg(列名)…｝ from tablename
		[WHERE where_definition]   
1
2

例如：

#统计部门编号为10的员工的平均工资
SELECT AVG(sal)	部门编号为10的员工的平均工资
FROM emp
WHERE deptno = 10;
1
2
3
4

• Max/min函数返回满足where条件的一列的最大/最小值

Select max(列名)　from tablename
		[WHERE where_definition]   
1
2

例如：

#求部门编号为10的员工的最高工资和最低工资
SELECT
	MAX(sal) 最高工资,
	MIN(sal) 最低工资
FROM
	emp
WHERE
	deptno = 10;
1
2
3
4
5
6
7
8

• 使用group by 子句对列进行分组

SELECT column1, column2. column3.. FROM	table;
		group by column
1
2

• 使用having 子句过滤

SELECT column1, column2. column3..
		FROM	table;
		group by column having ...
1
2
3

Having和where均可实现过滤，但在having可以使用合计函数,having通常跟在group by后，它作用于组。

• limit是mysql的语法，实现分页

select * from tablename
[where 条件]
limit n,m;
1
2
3

第一个参数n：起始位置（默认从0开始)，当i=0时可省略i ）；
第二个参数m：记录数；

select * from student
limit 0,3;
1
2

,avg(列名)…｝ from tablename
[WHERE where_definition]

例如：

```mysql
#统计部门编号为10的员工的平均工资
SELECT AVG(sal)	部门编号为10的员工的平均工资
FROM emp
WHERE deptno = 10;
1
2
3
4
5
6
7
8

• Max/min函数返回满足where条件的一列的最大/最小值

Select max(列名)　from tablename
		[WHERE where_definition]   
1
2

例如：

#求部门编号为10的员工的最高工资和最低工资
SELECT
	MAX(sal) 最高工资,
	MIN(sal) 最低工资
FROM
	emp
WHERE
	deptno = 10;
1
2
3
4
5
6
7
8

• 使用group by 子句对列进行分组

SELECT column1, column2. column3.. FROM	table;
		group by column
1
2

• 使用having 子句过滤

SELECT column1, column2. column3..
		FROM	table;
		group by column having ...
1
2
3

Having和where均可实现过滤，但在having可以使用合计函数,having通常跟在group by后，它作用于组。

• limit是mysql的语法，实现分页

select * from tablename
[where 条件]
limit n,m;
1
2
3

第一个参数n：起始位置（默认从0开始)，当i=0时可省略i ）；
第二个参数m：记录数；

select * from student
limit 0,3;
1
2

…未完待续