黑马程序员技术交流社区

标题: 【石家庄校区】MySQL笔记 [打印本页]

作者: 林志博 时间: 2018-10-18 17:49
标题: 【石家庄校区】MySQL笔记
本帖最后由小石姐姐于 2018-10-19 11:06 编辑

Day02 MySQL
1.       数据库(DataBase)的基本概念
数据库是存储和管理数据的仓库
数据库软件和数据库的区别:
●数据库大的范围,泛指所有储存和管理数据的数据库(数据库软件)
●数据库软件:
App:application(应用),就是一款软件(软件实现想要的功能)
数据库软件:管理和储存数据
●使用统一的方式(指的sql)来操作所有的数据库
2.       数据库的安装和卸载
第一步以管理员打开DOS
第二步:Misexec  /package  mysql所在路径
比如:misexec /package d:\soft\mysql
3.       mysql数据库服务器软件

4.       Sql指令(掌握)
4.1 sql概述
Structured Query Language:结构查询语言
其实就是定义了操作所有关系型数据库的规则.
每一种数据库操作的方式存在不一样的地方,称为”方言”

什么是sql:是一种结构化查询语言(sql不仅能从查询,还能进行添加删除和修改操作), 通俗的讲:sql就是一套规则,通过sql对数据库进行添加删除修改(crud)操作

SQL通用语法
1.       SQL语句可以单行书写可以多行书写以分号结束;
2.       通过空格来提高书写性
3.       MySQL数据库的SQL语句不区分大小写,关键字建议使用大写
4.       3种注释

4.2 Sql常见的分类
DDL:数据库定义语言,作用创建数据库和数据表以及修改数据库和数据表
DCL:数据库控制语言,作用给数据库和数据表进行授权操作(了解)
DML:数据库操作语言,作用对表中的记录(数据)进行添加,删除,修改操作.
DQL:数据库查询语言, 作用:表中的记录进行查询操作

4.3 DDL :数据库定义语言，操作数据库和表结构
4.3.1操作数据库:CRUD
1.       C(Create):创建
2.       R(Retrieve):查询
*查询所有的数据库名称;
*show datebases
3.       U(Update):修改
4.       D(Delete):删除
5.       使用数据库

4.4 DML：数据操作语言，更新表记录（添加，删除和修改）
添加语法： insert  into  表名（字段，---字段） values(值，----，值)
修改语法：update  表名 set 字段=值，----，字段=值 where 条件
删除语法： delete  from  表名 where 条件

4.5 DQL：数据查询语言，查询表记录
查询所有字段对应的值： select  * from 表名
查询某些字段对应的值： select 字段，---，字段 from  表名
去除重复数据： select distinct  字段 from  表名
起别名： select  字段 [ as ] 别名 from  表名

day03-mysql
1.       单表查询（掌握）
1.0       模糊查询
基本语法： where  字段 like ‘ %或者使用_’；
               字符： % 表示任意字符（0-N个字符），_ 表示一个任意字符。
1.1 条件查询
      ==字段 between 值1 and 值2  等同于  字段>=值1 and  字段<=值2
      ==字段 in(值1，----，值n)       等同于  字段=值1 or  字段=值2，---
      ==is  null  如果值为null,不能使用=，要使用is null
      ==is  not null 值不为null
1.2 排序查询
  基本语法：-------- order by 字段  desc (asc)
1.3聚合函数查询
       * sum(score)
       *avg(score)
       *count(score)
       *max(score)
       *min(score)
注意：
1.       聚合函数里面的参数：表的字段
2.       聚合函数在进行运算是，null值不参与运算。
3.       聚合函数一般是对 int类型的进行运算，其它类型没有意义。
4.       特别在使用count(字段)  字段一般是主键字段（非空，唯一）,
1.4 分组查询
==基本语法：group  by  分组字段
注意：
1.       分组字段数据要有共同特点。
2.       分组查询时， select  分组字段，聚合函数 from ----
分组字段和聚合函数都具有共同特点
==查询操作时，关键词的先后顺序：
1.       select  字段
2.       from  表名
3.       where  分组前条件判断
4.       group by 分组字段
5.       having 分组后的条件判断
6.       order  by  排序字段
如下图分析：

1.5分页查询：
分页：数据太多，一次性查询出来完，展示效果不好，就是用户的体验不好。
分页操作，不同的数据库分页基本原理不一样：
mysql:  limit 进行分页，其它数据库不能使用limit进行分页。
基本语法：
limit 参数1，参数2：
参数1 ：数据的起始位置（索引），从0 开始，表示第1条记录
参数2 ：每页显示的具体记录的个数。
规律  ：参数1 = （参数2-1）* 参数2
1.6综合案例(作业)
sql脚本：
--  员工表

[Java] 纯文本查看 复制代码

CREATE TABLE `employee` (

  `empno` int(11) NOT NULL,

  `ename` varchar(50) DEFAULT NULL,

  `job` varchar(50) DEFAULT NULL,

  `mgr` int(11) DEFAULT NULL,

  `hiredate` date DEFAULT NULL,

  `sal` decimal(7,2) DEFAULT NULL,

  `COMM` decimal(7,2) DEFAULT NULL,

  `deptno` varchar(11) DEFAULT NULL,

  PRIMARY KEY (`empno`),

  KEY `fk_emp` (`mgr`),

  CONSTRAINT `fk_emp` FOREIGN KEY (`mgr`) REFERENCES `emp` (`empno`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `employee` (

  `empno` int(11) NOT NULL,

  `ename` varchar(50) DEFAULT NULL,

  `job` varchar(50) DEFAULT NULL,

  `mgr` int(11) DEFAULT NULL,

  `hiredate` date DEFAULT NULL,

  `sal` decimal(7,2) DEFAULT NULL,

  `COMM` decimal(7,2) DEFAULT NULL,

  `deptno` varchar(11) DEFAULT NULL,

  PRIMARY KEY (`empno`),

  KEY `fk_emp` (`mgr`),

  CONSTRAINT `fk_emp` FOREIGN KEY (`mgr`) REFERENCES `emp` (`empno`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO emp VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO emp VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO emp VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO emp VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO emp VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO emp VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO emp VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO emp VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000, NULL, 20);
练习题：
--  查询薪水介于 8000到16000之间的员工的姓名和工作，薪水（两种方式）
SELECT ename , job,sal
FROM  emp
WHERE sal>=8000 AND sal<=16000;
SELECT ename , job,sal
FROM  emp
WHERE sal  BETWEEN  8000 AND 16000;
--  查询受雇日期是 2000-12-17，2001-02-20 的员工姓名和受雇日期（两种方式）
SELECT ename,hiredate
FROM emp
WHERE hiredate = '2000-12-17' OR hiredate='2001-02-20';

SELECT ename,hiredate
FROM emp
WHERE hiredate IN ('2000-12-17' ,'2001-02-20');
--  查询工作不是文员的薪水总和
SELECT  SUM(sal)
FROM emp
WHERE job!='文员';
--  查询受雇日期早于 2007-05-23的员工的薪水总和
SELECT SUM(sal)
      FROM emp
      WHERE hiredate <'2007-05-23';
--  查询每一种工作的薪水总和
SELECT job,SUM(sal)
FROM emp
GROUP BY job;

--  查询工作不是文员的每一种工作的薪水总和
SELECT job,SUM(sal)
FROM emp
WHERE job !='文员'
GROUP BY job;
--  查询查询受雇日期早于 2007-05-23的员工，并且按照部门分组，查询每个部门的薪水总和
SELECT SUM(sal),deptno
FROM emp
WHERE hiredate < '2007-05-23'
GROUP BY deptno
--  查询查询受雇日期早于 2007-05-23的员工，并且按照部门分组，查询每个部门的薪水总和，且部门的总薪水大于25000
SELECT SUM(sal),deptno
FROM emp
WHERE  hiredate <'2007-05-23' -- 分组前条件
GROUP BY deptno  --  分组字段
HAVING SUM(sal)>95000 -- 分组后条件，只有分完组后，才能求每个部门的总薪水
--  查询查询受雇日期早于 2007-05-23的员工，并且按照部门分组，查询每个部门的薪水总和，且部门的总薪水大于25000，最终按照总薪水的大小进行降序排列
SELECT SUM(sal),deptno
FROM emp
WHERE  hiredate <'2007-05-23' -- 分组前条件
GROUP BY deptno  --  分组字段
HAVING SUM(sal)>95000 -- 分组后条件，只有分完组后，才能求每个部门的总薪水
ORDER BY SUM(sal) DESC;-- 对最终符合条件的数据，进行排序

2.       约束（掌握）
2.1约束概述：
一种规范（限制），数据库约束就是对表中的数据进行规范，让数据具有某种特点。
2.2数据库的约束分类：
       主键约束
       非空约束
       唯一约束
       外键约束
2.3 外键约束：
==外键作用：
让表与表建立关系（关联性），保证数据的有效性和正确性。
==外键特点：
1.       外键的值不能随意写。
2.       外键的值引用于另一张表的主键值
3.       外键的值可以为null(但是为null,没有任何意义)
==外键设计原则：
       主表
       子表
多表之间的关联关系：
       一对一
比如：用户表  和  信息表
主表（子表）可以是任意一方。
-- 一对一：用户表和信息表
-- 分析：一个用户一个手机号，可以利用主键的唯一特点，可以把任何一方的主键设计成外键
-- 1. 用户表
CREATE  TABLE tab_user(
            uid INT PRIMARY KEY,
            username VARCHAR(32)
            );
-- 2.信息表
CREATE TABLE tab_info(
            info_id INT PRIMARY KEY,
            phone VARCHAR(11),
            CONSTRAINT info_user FOREIGN KEY(info_id) REFERENCES tab_user(uid)
            );
       一对多（多对一）
比如：部门表和员工表
主表：一方表,这里指的就是部门表
子表：多方表，这里指的就是员工表
外键设计在子表里面。
--  一对多的关联（多对一）外键的设计原则：外键放在多表里面
-- 1.设计一方表（主表）
CREATE  TABLE tab_dept(
                     did INT PRIMARY KEY,
                     dname VARCHAR(23)
                     );
-- 2. 设计多方表（子表），外键在多方表里面
CREATE  TABLE tab_emp(
                     eid INT PRIMARY KEY,
                     ename VARCHAR(32),
                     dept_id INT ,-- 外键：让 tab_emp 和 tab_dept
                     CONSTRAINT emp_dept FOREIGN KEY(dept_id) REFERENCES tab_dept(did)
                     );

       多对多（通俗来说，强化版的一对一）
比如：学生表和课程表
      主表（子表）可以是任意一方（不推荐这样区分和设计外键）
      外键设计原则：提供一张中间表  。
-- 多对多：学生和课程，设计原则提供一张中间表来关联学生和课程
-- 1. 设计多方表  学生
CREATE  TABLE tab_stu(
            sid  INT PRIMARY KEY,
            sname VARCHAR(32)
            );

-- 2. 设计多方表  课程
CREATE TABLE tab_course(
            cid INT PRIMARY KEY,
            cname VARCHAR(32)
            );
-- 3. 提供中间表：tab_stu_course
CREATE TABLE tab_stu_course(
      s_id INT ,--  外键，来关联 tab_stu
      c_id INT, -- 外键，来关联 tab_course
      CONSTRAINT s_c_1 FOREIGN KEY(s_id) REFERENCES tab_stu(sid),
      CONSTRAINT s_c_2 FOREIGN KEY(c_id) REFERENCES tab_course(cid)
      );

案例：黑马旅游商城项目-----设计多表之间的关联
设计表：
       用户表：tab_user
       旅游分类表：tab_category
       旅游线路（产品）表： tab_route
       用户收藏旅游线路表：tab_favorite
表与表之间的关联关联：
       tab_user  和  tab_favorite :  一对多
       tab_category 和 tab_route : 一对多
       tab_favorite  和 tab_route: 多对多
淘宝商城项目，设计表与表之间关联
       用户表： tab_taoUser
       商品分类表： tab_taoCategory
       商品表： tab_taoProduct
       购物车表：tab_taoCart
== 级联操作(了解)
       级联操作概述：只有表与表之间有关联关系，才能进行级联操作。
作用：当主表的数据发生变化时，那么子表的数据会级联发生变化。
       级联操作：一般情况下，操作是对update和delete.
       分两种情况：
            第一种情况：当主表的数据变化时，子表的数据和主表的数据保持一致。
                              语法：       on update cascade on delete cascade
            第二种情况：当主表的数据变化时，子表的数据（外键的数据）设置为null.
                        语法：on update set null on delete set null.
3.       多表关联（掌握）

3.1       如下图分析：

3.2 表的关联关系
       一对一（了解）
      比如：人和身份证，用户表和身份证表
       一对多（多对一）
比如：商品分类和商品，分类表和商品表
       多对多
比如：老师和学生，学生和课程
4.       数据库的设计范式（了解）
5.       数据库的备份和还原（简单）
day05-jdbc
今天的知识点介绍：
       JDBC概述
       JDBC常用的api
       JDBC工具类的封装
       综合案例：通过jdbc对数据库进行crud操作
       事务操作：通过jdbc完成事务的操作
jdbc常见的异常：
       java.sql.Exception:  Access denied for user ‘root@localhost’(use password yes):
提示数据库的用户名或者密码错误。
一．       jdbc（掌握）
1.       jdbc概述
原理：jdbc其实就是一套规范（接口），不同的数据库厂商实现这一套规范，通过jdbc一套规范，操作不同的数据库。
jdbc的一套接口：java.sql.*
不同的数据库厂商实现一套接口：
//mysqlConnection mysql数据库厂商提供的实现类
//Connection 就是sun官方提供的接口，通过操作接口，去调用相关的实现类的方法
// java.sql.Connection 接口，实现类：com.mysql.jdbc.MySqlConnection(mysql数据库提供的)
public MySqlConnection implements java.sql.Connection{
      ----------
}
操作Connection(多态)：
Connection con =  new MySqlConnection();

2.       jdbc的api
       1.java.sql.DriverManager(类)
作用1：注册驱动（不用），推荐使用：Class.forName(“com.mysql.jdbc.Driver”)
作用2：获取Connection  con = getConnection(url,user,password);
       2.java.sql.Connection(接口)
       3.java.sql.Statement(接口)
       4.java.sql.PreparedStatment(接口)
       5.java.sql.ResultSet(接口)
3.案例<1>添加，删除，修改操作：
       存在的问题：代码有冗余（重复度太高）
       解决问题方案：可以把重复的代码提取出来，封装一个方法或者一个类。
       冗余代码：
1.       第一部分：从注册驱动到获取连接对象，这些代码都是冗余
2.       第二部分：每次关闭资源（connection,statement）,这些代码也是冗余
package cn.itheima.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/*
解决：代码的冗余问题
1.从加载驱动到获取连接对象
2.关闭资源

[Java] 纯文本查看 复制代码

*/

public class JDBCUtils { //工具类：简单方便

    //1.提供一个方法： 获取连接对象

    public static Connection getMyConnection() throws Exception {

        //0.加载驱动

        Class.forName("com.mysql.jdbc.Driver");

        //1.直接通过DriverManager

        String url = "jdbc:mysql://localhost:3306/day04_mysql_moretab";// http://www.baidu.com:80

        String user="root";

        String password="root";

        Connection con = DriverManager.getConnection(url,user,password);

        return con;

    }

//2.提供一个关闭资源的方法： closeResource

[Java] 纯文本查看 复制代码

 // Connection, Statement

    public static void  closeResource(Statement stmt, Connection con) throws Exception {

            //1.关闭statement

            if(stmt!=null){

                stmt.close();

            }

            //2.关闭Conn

            if(con!=null){

                con.close();

            }

    }

}

上面的工具类：
问题1：获取连接对象的四个参数写死了。
*原因：四个参数写死了，后期在开发中，如果更换任意一个参数，需要修改源码，这种操作是不被允许的。
*细节：在开发中，遵循一个OOP原则。
      OOP原则：对代码的修改时关闭的，对代码的扩展是开放的。
*解决方案：
   步骤一：把四个连接参数配置到properties文件
   步骤二：通过读取配置文件的四个参数获取连接对象。
/**
*
* @author Administrator
*1.需要把四个连接参数配置到properties
*2.通过读取配置文件，获取四个参数，得到连接对象了
*步骤一：1.在src下面创建一个config.properties
*/

[Java] 纯文本查看 复制代码

public class JDBCUtlis2 {

        //为了避免每次都加载配置文件，获取四个连接参数，需要把这些操作放在静态代码块（只加载一次）

        private static String driver=null;

        private static String url=null;

        private static String user=null;

        private static String password=null;

        static{

               

            try{

                    //----读取配置文件，获取四个连接参数

                //创建Properties对象

                Properties pro = new Properties();

                //获取配置文件的字节输入流，让pro对象加载

                ClassLoader loader =JDBCUtlis2.class.getClassLoader();

                InputStream in = loader.getResourceAsStream("config.properties");

                pro.load(in);

                driver=pro.getProperty("driverUrl");

                url = pro.getProperty("urla");

            user=pro.getProperty("usera");

                password=pro.getProperty("passworda");

            }catch(Exception e){

                    e.printStackTrace();

            }

           

           }

//1.提供一个方法：获取连接对象

[Java] 纯文本查看 复制代码

 public static Connection getMyConnection() throws Exception {

            //0.加载驱动

        Class.forName(driver);

        //1.直接通过DriverManager

       Connection con = DriverManager.getConnection(url,user,password);

        return con;

    }

//2.提供一个关闭资源的方法： closeResource

[Java] 纯文本查看 复制代码

 // Connection, Statement

    public static void  closeResource(Statement stmt, Connection con) throws Exception {

            //1.关闭statement

            if(stmt!=null){

                stmt.close();

            }

            //2.关闭Conn

            if(con!=null){

                con.close();

            }

    }

   

    public static void main(String[] args) throws Exception{

                getMyConnection();

        }

}

欢迎光临黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2