数据库的了解及使用

数据库：

• 概念：
  数据存储的仓库，用于存储和管理数据
  常见数据库产品：
  (1) Oracle：oracle公司 大型的关系型数据库 收费的
  (2) DB2：IBM公司 大型的关系型数据库 收费的
  (3) Mysql：mysql公司 中小型关系型数据库 免费的。后来被oracle收购，mysql6之后收费（社区版(免费)，旗舰版(收费)）
  (4) Sql Server：微软公司 中小型关系型数据库 收费的
  (5) Sqllite：安卓系统内置数据库 微型关系型数据库 免费的
  (6) NoSql系列数据库：not only sql 非关系型数据库

• Mysql：数据库软件

  • 安装：
    MySQL安装教程

  • 卸载：
    1.关闭mysql服务
    - (1).手动关闭：我的电脑–>右键–>管理
    - (2).cmd–>services.msc,打开服务面板
    - (3).cmd–>net stop mysql。(注意获取cmd管理员权限)
    2.卸载
    3.删除数据存储文件
    打开mysql的安装目录中my.ini文件,在文件里找datadir的路径（如：datadir=“C:/ProgramData/MySQL/MySQL Server 5.5/Data/”），删除该路径下的内容

  • 使用：

    • 登陆数据库：
      1. (明文登陆)cmd–>mysql -uroot -p你自己的密码
      2. (暗文登陆)cmd->mysql -uroot -p(回车)
         然后会让你Enter password：(然后输入你自己的密码即可，这块密码会显示成*，看不到输入的密码是什么)
    • 服务：系统后台进程
      • 启动服务： net start mysql
      • 停止服务： net stop mysql
      • 删除服务 sc delete mysql

  • 配置：
    修改mysql的密码步骤：

    1. 停止mysql服务 运行输入services.msc 停止mysql服务
       或者 cmd – net stop mysql
    2. 在cmd下 输入 mysqld --skip-grant-tables 启动服务器 光标不动 （不要关闭该窗口）
    3. 新打开cmd 输入mysql -u root -p 旧密码
       use mysql;
       update user set password=password(‘123456’) WHERE User=‘root’;
    4. 关闭两个cmd窗口 在任务管理器结束mysqld 进程
    5. 在服务管理页面 重启mysql 服务

• SQL：结构化查询语言

  • 概念：通过sql语言可以操作所有的关系型数据库。每种数据库之间会存在差异，称为 “方言”

  • sql分类：
    DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；
    DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（增、删、改）
    DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；
    DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。

  • DDL：操作数据库 和 表

    • 操作数据库：

      • 创建：create database 数据库名称;
      • 删除：drop database 数据库名称;
      • 修改数据库编码：alter database mydb character set=‘gbk’;（set的值就是编码的名字，这块是改成了gbk编码）
      • 查询：show databases; – 查询所有数据库的名称
        show create database 数据库名称; – 查询数据库的创建语句

    • 操作表：

      • 创建：

        • 列的数据类型：
          (1). int：整型 id int,
          (2). double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；
          money double(5,2)
          (3). char：固定长度字符串类型；
          name char(10) “张三”
          (4). varchar：可变长度字符串类型；
          name varchar(10) “张三”
          (5). text：字符串类型；存大格式的文本 比如存个小说 一般不用
          (6). blob：字节类型；存字节类型的数据 比如电影字节 图片字节 但是一般不会把字节数据存到数据库当中
          (6). date：日期类型，格式为：yyyy-MM-dd；
          (7). time：时间类型，格式为：hh:mm:ss
          (8). datetime：日期时间类型 yyyy-MM-dd hh:mm:ss，如果该类型的字段不给赋值，则默认是null
          (9). timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss，如果该类型的字段不给赋值，则默认当前时间

        • 语法：
          create table 表名(
          列名1 类型1,
          列名2 类型2,
          …
          列名n 类型n

          );
          例如：
          create table student(
          classname varchar(20),
          age int,
          gender varchar(6)
          );

      • 删除：drop table 表名;
        例如：
        drop table student;

      • 修改：

        1. 修改之添加列：给stu表添加classname列：
           alter table student add (classname varchar(100));
        2. 修改之修改列类型：修改stu表的gender列类型为char(2)：
           alter table student modify gender char(2);
        3. 修改之修改列名：修改stu表的gender列名为sex：
           alter table student change gender sex char(2);
        4. 修改之删除列：删除stu表的classname列：
           alter table student drop classname;
        5. 修改之修改表名称：修改stu表名称为student：
           alter table student rename to student;

      • 查询：
        show tables; – 查询该数据库下所有表名称
        desc 表名; – 查询表结构

        • 注意：操作表之前，应该先选择一个数据库使用：
          use 数据库名称;
          例如：use mydb;（选择mydb数据库使用）

  • DML:操作表中的数据

    • 增：

      • 语法：
        • insert into 表名(列名1,列名2,…) values(值1,值2,…);
          • 注意：如果表的所有列都添加值，则列名可以省略
            insert into stu(classname,age) values(“lisi”,23);
            insert into stu(classname,age,sex) values(“zhangsan”,23,‘1’);
            省略后的写法
            insert into stu values(“ww”,23,‘2’);

    • 删：

      • 语法：
        • delete from 表名 where 条件;
          delete from stu where sname = ‘lisi’
        • truncate table 表名;-- 删除所有记录（效率比上面的方法快）
          • 先删除表，再创建表。

    • 改：

      • 语法：
        • update 表名 set 列名1 = 值1 , 列名2 = 值2… where 条件
          update student set sex = “1” where classname = “lisi”;
          update customer set money = 500000 where id = xxx;

  • DQL:查询

    • 语法：
      (1). SELECT
      selection_list /要查询的列名称/
      (2). FROM
      table_list /要查询的表名称/
      (3). WHERE
      condition /行条件/
      (4). GROUP BY
      grouping_columns /对结果分组/
      (5). HAVING
      condition /分组后的行条件/
      (6). ORDER BY
      sorting_columns /对结果分组/
      (7). LIMIT
      offset_start, row_count /结果限定/

    • 条件查询：where 子句
      =、!=、<>(不等于)、<、<=、>、>=；
      between…and； 在什么范围之间
      in(set)；
      is null；为空
      is not null；不为空
      and； 并且
      or； 或者
      not；非

---

练习表
字段名称 字段类型 说明
sid char(6) 学生学号
sname varchar(50) 学生姓名
age int 学生年龄
gender varchar(50) 学生性别

create table stu (
	sid char(6),
	sname varchar(50),
	age	int,
	gender varchar(50)
);
insert into stu values('S_1001', 'liuYi', 35, 'male');
insert into stu values('S_1002', 'chenEr', 15, 'female');
insert into stu values('S_1003', 'zhangSan', 95, 'male');
insert into stu values('S_1004', 'liSi', 65, 'female');
insert into stu values('S_1005', 'wangWu', 55, 'male');
insert into stu values('S_1006', 'zhaoLiu', 75, 'female');
insert into stu values('S_1007', 'sunQi', 25, 'male');
insert into stu values('S_1008', 'zhouBa', 45, 'female');
insert into stu values('S_1009', 'wuJiu', 85, 'male');
insert into stu values('S_1010', 'zhengShi', 5, 'female');
insert into stu values('S_1011', 'xxx', NULL, NULL);

---

雇员表：emp
字段名称 字段类型 说明
empno int 员工编号
ename varchar(50) 员工姓名
job varchar(50) 员工工作
mgr int 领导编号
hiredate date 入职日期
sal decimal(7,2) 月薪
comm decimal(7,2) 奖金
deptno int 部分编号

create table emp(
	empno int,
	ename varchar(50),
	job varchar(50),
	mgr int,
	hiredate date,
	sal decimal(7,2),
	comm decimal(7,2),
	deptno int
) ;
insert into emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
insert into emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
insert into emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
insert into emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
insert into emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
insert into emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
insert into emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
insert into emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
insert into emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

---

• 模糊查询：like

  • 通配符

    • :匹配单个任意字符
      比如: 我要查询姓名是3个任意字符组成的
      select * from student sname like=’__’;(三个下划线)
      例如:我要查询第二个字符是m的
      select * from student where sanme like ‘_m%’;

    • %:匹配多个任意字符
      例如：我要查询名字中包含m的 select * from student where sname like ‘%m%’;
      例如：我要查询名字是a开头的 select * from student where sname like ‘a%’;
      例如：我要查询名字是b结尾的 select * from student wher like ‘%b’;

    • 字段控制：

      • 修改字段的别名：as (可以省略)

        • 给字段起别名：
          例如: select sname as 姓名, sage 年龄 from student;

        • 给运算字段起别名：
          例如: select sname as 姓名, (工资+奖金) as 总收入 from student;

        • 给表起别名：给每一张表起一个别名。简化书写
          例如: select stu.sname, stu.sage from student as stu;

      • 字段运算：

        • null参与的运算，结果都为null
          比如 工资 是100 奖金是null 我们让这两个字段运算 那结果就为null
        • 一般会将null替换为0： ifnull(字段名称,如果是null的替换值) ifnull 是mysql的方言

        所以如果奖金字段为null 一般我们把奖金替换为0
        例如: select sname,(工资+ifnull(奖金,0)) as 总收入 from student;

      • 去除重复记录 比如我查询工资是3000的 出现了多条工资为3000 的记录 那我只想展示一条3000的记录 所以可以用 distinct 去除重复记录

        • DISTINCT
          例如: select distinct 工资 from student;

    • 排序:
      order by 默认升序排列 ASC (默认值) ------DESC 降序排列
      按工资从小到大排
      select * from student order by 工资 asc;
      按工资从大到小排
      select * from student order by 工资 desc
      如果出现多条工资一样的 那我们可以指定第二排序条件
      select * from student order by 工资 desc,奖金desc;

  • 聚合函数：

    • 聚合函数是用来做纵向运算的函数：
      count()：统计指定列不为null的记录行数； 统计个数的 比如我统计有多少个学生
      select count(sid) from student; 或者传个星 号 select count(*) from student; 一般不要传有null 值的字段

      max()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
      例如 查询工资最大值 select max(工资) as 最高工资 from student;

      min()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
      例如 查询 最少工资 select min(工资) as 最小工资 from student;

      sum()：计算指定列的数值和，如果指 定列类型不是数值类型，那么计算结果为0；
      例如 计算总工资 select sun(工资) as 总支出 from student;

      avg()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
      例如:计算平均 工资 select avg(工资) as 平均工资 from student;

  • 分组查询：group by 一般配合聚合函数使用 查出的数据才有意义

    • 查询的字段：
      1.分组字段本身
      2.聚合函数
      比如我按部分编号分组 比如有三个部门 然后我求每个部门的平均工资 那展示出来的数据应该有三条
      例如: select 部门编号 avg(工资) from student group by 部门编号;

    • 1.例如查询 每个部门的部门编号 以及每个部门工资大于1500的人数

      select 部门编号, count(*) from student where 工资>1500 group by 部门编号;

      注意这里的 where 是对分组前的条件限定 也就是说不满足条件的 不参与分组

      • where和having
        • where：在分组之前对条件进行限定。不满足条件，就不会参与分组
        • having：在分组之后，对结果集的筛选

    • 2.例如 我要查询 各个部门平均工资 大于2000 的部门

      select 部门编号,avg(工资) from student group by 部门编号 having avg(工资)>2000;

    • 3.例如：我要查询 各个部门 员工工资大于1500 的平均工资 并且平均工资 大于2000的部门

      上面这句话怎么理解呢? 首先查询各个部门 那肯定要按部门编号分组 分组前的条件限定是 员工工资大于1500的才参与分组
      计算出平均工资 然后对结果再进行筛选 筛选出 平均工资 大于2000的部门

      select 部门编号,avg(工资) from student group by 部门编号 where avg(工资)>1500 having avg(工资)>2000;

  • 分页查询：limit

    • limit 0,5 开始的记录索引, 每一页显示的条数 索引从0开始
      开始的记录索引 = (页码-1)*每一页显示的条数

    • 例如 我显示第一页 每页5条记录
      select * from student limit 0,5 ; 这是第一页 5 条记录
      select * from student limit 5,5 ; 这是第二页 5条记录
      select * from student limit 10,5 这是第三页 5条记录

    • oracle：rownum 分页方言

    • sqlserver：top 分页方言