数据库管理系统（DBMS）的概述

数据库管理系统（DBMS）的概述

置顶 2018年03月12日 17:36:39

阅读数：77

数据库管理系统（DBMS）的概述

数据库

1 数据库的概念（了解）

1.1 什么是数据库

对数据进行管理的仓库

DBMS:database  manage system

仓库（database）  =   若干个表（table）

1.2 常见的数据库

MySQL、Oracle（神喻、神谕）、SqlServer(微软专用)、DB2、

1.3 理解数据库

库：多个表

表：多个字段（列）

字段：属性

列：表的结构

行：一条数据（在数据库中不存在半条数据的说法）

姓名	年龄	性别	班级
高景洋	18	男	1

 

2 安装mysql数据库

参考对应文档

 

3 基本命令

mysql –uroot –p123   回车     进入mysql数据库

show databases；

 

数据库中的数据类型

int 整型   1000

double : 双浮点类型   double(5,2)  最多是5位，其中必须有2位小数，最大值是：999.99

decimal：单浮点类型，之所以用它，是因为它能避免精度丢失，一旦涉及到钱，必须使用该类型

char: 字符串，长度不可变   char(50)

varchar:字符串，长度可变    varchar(10)  最长是10

date：日期格式    yyyy-MM-dd

time: 时间格式   hh:mm:ss

datetime：  日期+时间

 

 

SQL语句

1 SQL概述

1.1 什么是SQL

结构化查询语句  structure  query  language

1.2 语法要求

A 可以单行书写，也可以多行书写

B 对大小写不敏感

C 结束标志是分号，不能省略

2 分类

DDL：数据定义语言，用来定义数据库对象：库、表、列

DML：数据操作语言，用来定义数据库记录（数据）

DCL：数据控制语言，用来定义权限和安全级别的

DQL：数据查询语言，用来查询记录（数据）

 

 

DDL（对库、表的操作，增、删、改）

 

查看当前所有的数据库

 

创建一个自己的数据库：create database 库名；

 

使用某一个数据库： use 库名；

 

查看当前数据库中所有的表名  show tables;

 

创建一个表

create table 表名（

列名 列类型，

列名 列类型，

……

列名 列类型，

列名 列类型

）；

 

 

查看表结构： desc 表名；

 

删除表 ： drop table 表名；

 

修改表：

添加一列

alter table 表名 add(

列名 列类型，

列名 列类型，

……

列名 列类型

);

 

 

修改列类型（不建议去该列类型，避免的方法，建表的时候考虑周全）

alter table 表名 modify 列名 列类型；

 

修改列名

alter table 表名 change 原列名 新列名 列类型；

 

删除列

alter table 表名 drop 列名；

 

修改表名

alter table表名 rename to 新表名；

 

 

 

 

DML(对表的记录/数据的操作，增删改)

DQL：select * from 表名；

1、 插入数据

insert into 表名 （列名1，列名2，……列名N） values （值1，值2，……值N）；

 

注意：

中文乱码：参考

 

1：在表名认是插入一后跟的是列名，如果没有指定列名，默整行数据，不允许插入半行数据

 

2：在插入数据的时候，指定了列名，但是没有全部指定，则插入的指定列的数据，没有指定的列默认为null

 

3：插入数据的时候，数据的顺序必须跟指定列的顺序保持一致，如果没有指定列名，则插入的顺序必须跟表结构的顺序一样

 

2、 修改数据

update 表名 set 列名1=列值1，列名2=列值2……列名N=列值N；

 

以上的SQL语句，更改的是所有列的数据的值，很显然不合适，需要更改个别数据。

update 表名 set 列名1=列值1，列名2=列值2，……列名N=列值N where 条件；

一般来讲，条件用唯一标识，id

 

 

 

 

总结：条件（where子句），一定是一个boolean类型的表达式

在where子句中能够使用那些运算符

=   !=  <   >   <=   >=  and  or    between…and

is null    is not null    专门用来判断某字段是否为null的

 

 

 

 

 

 

练习：把性别为null的数据的名字改为fff

 

 

 

练习：把性别不为null的数据的性别改成人妖

 

3、 删除数据（慎用）

delete from 表名；   //删除全部的数据

delete * from 表名；  //删除全部的数据

delete  from 表名 where 条件；//注意，删除的整条数据

练习：删除sex为null的数据

 

删除全部数据：

 

 

DCL（创建用户并授权）（理解就好）

一般来讲，一个项目，只有一个用户，不是root，是root创建出来的。对应的数据库也只有一个，这个用户只能操作这个数据库，不能去操作其他数据库。

1、 创建一个用户

create user 用户名@IP地址 identified by ‘密码’；

假如说创建成功了，那么这个用户只能够在这个IP地址上登录。

 

 

上面的192.168.1.117是个动态地址，不允许使用本机动态地址

本机地址127.0.0.1是本机静态地址，也可以使用localhost

 

 

2、 给用户授权

grant 权限1，权限2……权限N on 库名.* to 用户名@IP地址；

权限包括：create、alter、drop、insert、update、delete、select等

 

验证是否授权成功

 

没有删除权限，所以删除失败（并不是SQL语句有问题）

 

3、 撤销权限

revoke 权限1，权限2……权限N on 库名.* from 用户名@IP地址

 

验证是否已经撤销添加数据的权限

 

4、 查看权限

show grants for 用户名@IP地址；

 

5、删除用户

drop user 用户名@IP地址；

 

验证

 

 

DQL（查询——不会改变数据（表的记录））

创建一个表students(建表和插入数据的SQL，在作业1中)

一、 基础查询

1、 查询所有列

select * from 表名；

2、 查询指定列

select id，name from 表名；

二、 条件查询

1、 where子句能够使用哪些运算符（补充）

   =   !=  <   >   <=   >=  and  or    between…and

is null    is not null    专门用来判断某字段是否为null的

%   /  *  +  -   <>(不等于)   in     not in

 

2、 查询学号为1004、 1005、 1009 、 1011的数据

SELECT * FROM students WHERE id IN('1004','1005','1009','1011');

3、 查询学号不为1004、 1005、 1009 、 1011的数据

SELECT * FROM students WHERE id NOT IN(1004,1005,1009,1011);

4、 查询性别非男的数据

SELECT * FROM students WHERE sex != 'nan';

 

SELECT * FROM students WHERE sex <>'nan';

5、 查询成绩非空的数据

SELECT * FROM students WHERE score IS NOT NULL;

 

三、 模糊查询like

1、 查询姓名由6个字母构成的学生记录

SELECT * FROM students WHERE NAME LIKE '______';

一个_，代表一个字符（一个英文一个汉字都算一个字符）

 

2、 查询姓名由11个字母构成，并且第2个字母为i的学生记录

SELECT * FROM students WHERE NAME LIKE '_i_________';

 

3、 查询姓名以w开头的学生记录

SELECT * FROM students WHERE NAME LIKE 'w%';

%，代表0—N个字符

 

4、 查询姓名中第3个字母为a的学生记录

SELECT * FROM students WHERE NAME LIKE '__a%';

 

5、 查询姓名中包含a字母的学生记录

SELECT * FROM students WHERE NAME LIKE '%a%';

 

四、 字段控制查询

1、 去除重复记录

两行或者两行以上的记录中，出现的数据相同，比如students表中的age、sex和score字段就存在相同的数据，如果想去掉重复的记录，就需要使用distinct。

SELECT DISTINCT score FROM students;

 

2、 （使用emp表，已共享）查看雇员的月薪与奖金之和

SELECT *,sal+comm FROM emp;

上面的结果是不对的，sal和comm是数字类型，可以做加减法，但是因为comm有null值，所以加完之后，得到的是null值

SELECT *,sal+IFNULL(comm,0) FROM emp;

3、 改列名——添加别名

SELECT *,sal+IFNULL(comm,0) AS 钱 FROM emp;

SELECT *,sal+IFNULL(comm,0) 钱钱 FROM emp;

as可以省略

五、 排序  order by

1、 查询所有员工记录，按工资sal升序排序

SELECT * FROM emp ORDER BY sal ASC;

ASC代表升序，可以省略

2、 查询所有的员工记录，按工资sal降序排序

SELECT * FROM emp ORDER BY sal DESC;

desc代表降序，不能省略

3、 查询所有的员工，按工资降序排序，如果工资相同时，按编号升序排序

SELECT * FROM emp ORDER BY sal DESC,empno ASC;

六、 聚合函数

聚合函数是用来做纵向运算的函数

COUNT()：统计指定列不为NULL的记录行数；（纵向统计）

MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

1、 count

1、查询emp表中有多少条记录（记录数）

SELECT COUNT(empno) FROM emp;

SELECT COUNT(*) FROM emp;

括号里能放  1 、非空字段   2、*

 

2、查询emp表中有奖金的人数

SELECT COUNT(comm) FROM emp;

3、查询emp表中月薪大于25000的人数

SELECT COUNT(*) FROM emp WHERE sal > 25000;

4、统计月薪与奖金之和大于25000的人数

SELECT COUNT(*) 人数 FROM emp WHERE sal+IFNULL(comm,0) > 25000;

5、查询有奖金的人数，以及有领导的人数

SELECT COUNT(comm),COUNT(mgr) FROM emp

2、 sum和avg

1、查询所有员工月薪的和

SELECT SUM(sal) FROM emp;

2、查询所有员工月薪和，以及所有员工的奖金和

SELECT SUM(sal),SUM(comm) FROM emp;

3、查询所有员工月薪+奖金的和

SELECT SUM(sal),SUM(comm) FROM emp;

4、统计所有员工的月薪+奖金的平均值

SELECT AVG(sal+IFNULL(comm,0)) FROM emp;

3、 max和min

查询最高工资和最低工资

SELECT MAX(sal),MIN(sal) FROM emp ;

 

七、 分组查询  group by

当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要按照部门来分组。

1、 分组查询

查询每个部门的部门编号和每个部门的工资和：

SELECT deptno,SUM(sal)

FROM emp

GROUP BY deptno;

查询每个部门的部门编号以及每个部门的人数：

SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;

查询每个部门的部门编号以及每个部门工资大于15000的人数：

SELECT deptno,COUNT(*) FROM emp WHERE sal > 15000 GROUP BY deptno;

语法：where子句必须放在from表名  的后面

2、 having子句

查询工资总和大于90000的部门编号以及工资和：

SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 90000;

知识点：同样作为子句，where必须放在from表名的后面

having子句必须放在最后

where子句后面不能加聚合函数，如果使用聚合函数作为条件，必须使用having子句进行引领。

 

注意：where是对分组前记录进行限制的条件，如果某条记录不满足where的条件，这条记录就不参与分组。

having是对分组之后的数据进行限制的。

查询出某部门员工编号大于1005的员工的工资和

SELECT *,SUM(sal) FROM emp WHERE empno>1005 GROUP BY deptno HAVING SUM(sal)>20000;

八、 方言limit，只能够在mysql数据中使用

limit用来限定查询结果的起始行，以及总行数。

1、 查询5行记录，起始行从0开始

SELECT * FROM emp LIMIT 0,5;

2、 查询10行记录，起始行从3开始

SELECT * FROM emp LIMIT 2,10;

 

注意：只要查询行数不是从第0行还是，则指的的我们视觉上的行数

3、 分页查询（有了sqlyog，一般没必要用）

如果一页记录为10条，希望查看第3页记录应该怎么查呢？

0—9

10---19

20---29

SELECT * FROM emp LIMIT 20,10;