JavaWeb第二天

数据库

1、概念

数据库的基本概念
　　用于存储和管理数据的仓库
　　特点：
　　　　（1）持久化存储数据，数据库其实就是一个文件系统
　　　　（2）方便地存储和管理数据
　　　　（3）使用统一地方式对数据库进行操作 SQL
　　常见的数据库软件
　　　MySQL 开源的免费的数据库 小型数据库 母公司Sun被Oracle公司收购 6.x版本之后开始收费
　　　Oracle 收费的大型数据库
　　　DB2 IBM公司的收费数据库 常用于银行系统
　　　SQL Server 微软公司的中型收费数据库 C# .net语言经常使用
　　　SQLite 嵌入式的小型数据库 应用在手机端 常内置于软件或系统中（如内置于浏览器，内置于Android系统）

2、MySQL

MySQL安装
　　注意：最后一步卡住，不要使用默认的服务名 这里用mysql5.5.40
MySQL卸载
　（1）在设置中卸载
　（2）删除C盘ProgramData文件中的MySQL文件夹
　（3）删除注册表的mysql相关文件
MySQL服务的打开和关闭
　　手动或使用命令行 net start/stop mysql5.5.40(注意使用管理员权限打开命令行)
MySQL登录
　　连接本地mysql用户 命令行 mysql -uroot -proot 退出 exit/quit
　　连接远程用户
　　　　mysql -h+IP -u用户名 -p密码(用户名和密码是远程用户的)
　　　　mysql -h127.0.0.1 -uroot -proot
　　　　mysql --host-127.0.0.1 --user=root --password=root
MySQL目录结构
　　MySQL安装目录
　　　　bin 二进制可执行文件 包括很多命令
　　　　data 数据目录 包括日志文件和数据文件
　　　　include c语言的头文件
　　　　lib MySQL运行需要的库文件 相当于jar包
　　　　share MySQL的错误信息
　　　　my.ini MySQL的配置文件
MySQL数据目录 C:\ProgramData\MySQL
　　data
　　　　information_schema 描述MySQL中的信息，如有哪些数据库，有哪些表 其中存放的表不是真正的表，是视图。该数据库不存在真正的物理文件。
　　　　mysql MySQL服务器的核心数据库，存放了很多核心数据
　　　　performance_schema 对性能提升做一些操作的数据库
　　　　test
计算机上安装了MySQL数据库服务器软件，可以称计算机为一个MySQL服务器
　　一个MySQL数据库服务器软件中可以有多个数据库，数据库在计算机中就是一个文件夹
　　一个数据库中可以有多张表，表在计算机中就是一个文件
　　一个表中可以有多条数据记录

3、SQL语言

SQL Structured Query Language 结构化查询语言
　　操作所有关系型数据库的规则
　　每一种数据库的操作的方式存在不一样的地方，成为"方言"
SQL通用语法
　（1）可以单行或多行书写，以分号结尾
　（2）可使用tab或缩进增强语句的可读性
　（3）不区分大小写，建议大写
　（4）3种注释
　　　　单行 – 注释内容 横杠后必须加空格
　　　　# 注释内容 #后可以不加空格
　　　　多行 /*
SQL分类
　（1）DDL 操作数据库和表
　（2）DML 增删改表中的数据（数据记录）
　（3）DQL 查询表中的数据（数据记录）
　（4）DCL 管理用户 授权 安全访问
SQL中的数据类型
　int 整数
　　　age int
　double 小数
　score double(5,2)总共5位，小数点后2位
　date 日期，只包含年月日 yyyy-MM-dd
　datetime 日期 包含年月日 时分秒 yyyy-MM-dd HH:mm:ss
　timestamp 时间戳
　　　包含年月日 时分秒
　　　如果不给这个字段赋值或赋值为null，默认使用当前的系统时间来赋值
　varchar 字符串
　name varchar(20)最长20个字符

4、DDL

操作数据库和表
　　操作：CRUD 增删改查
操作数据库
　C(Create)：创建
　　　创建数据库
　　　　　CREATE DATABASE 数据库名;
　　　创建数据库之前先判断是否存在,不存在则创建，存在则不创建
　　　　　CREATE DATABASE IF NOT EXISTS 数据库名;
　　　创建数据库时指定字符集
　　　　　CREATE DATABASE 数据库名 CHARACTER SET GBK;
　　　创建数据库之前先判断是否存在,并指定字符集
　　　　　CREATE DATABASE IF NOT EXISTS 数据库名 CHARACTER SET GBK;
　R(Retrieve)：查询
　　　查询所有数据库的名称
　　　　　SHOW DATABASES;
　　　查询创建数据库mysql的语句(会显示该数据库的字符集)
　　　　　SHOW CREATE DATABASE MYSQL
　U(Update)：修改
　　　修改数据库的字符集
　　　　　ALTER DATABASE 数据库名 CHARACTER SET UTF8;
　D(Delete)：删除
　　　删除数据库
　　　　　DROP DATABASE 数据库名;
　　　删除数据库之前先判断数据库是否存在
　　　　　DROP DATABASE IF EXISTS 数据库名;
　使用数据库
　　　查询当前使用的数据库名
　　　　　SELECT DATABASE();
　　　使用数据库
　　　　　USE 数据库名;
　　　　　相当于进入了这个数据库对应的文件夹，才可以对其中的数据进行操作
操作表
　C(Create)：创建
　　　创建表
　　　　　CREATE TABLE 表名(
　　列名1 数据类型1,
列名2 数据类型2,
…
列名n 数据类型n
);
　　　复制表
　　　　　CREATE TABLE 表名 LIKE 要复制的表名;
　R(Retrieve)：查询
　　　查询数某个数据库中所有表的名称 SHOW TABLES;
　　　　　查询表结构
　　　　　　　DESC 表名;
　　　　　　　DESC是description的缩写
　　　查询表的字符集
　　　　　SHOW CREATE TABLE ***;
　U(Update)：修改
　　　修改表名
　　　　　ALTER TABLE 表名 RENAME TO 新表名;
　　　修改表的字符集
　　　　　ALTER TABLE 表名 CHARACTER SET UTF8;
　　　添加一列
　　　　　ALTER TABLE STU ADD 列名 数据类型;
　　　修改列名 类型
　　　　　ALTER TABLE STU CHANGE 列名 新列名 新数据类型;
　　　　　ALTER TABLE STU MODIFY 列名 新数据类型;
　　　删除列
　　　　　ALTER TABLE DROP 列名;
　　D(Delete)：删除
删除表
　　DROP TABLE 表名;
删除表之前先判断表是否存在
　　DROP TABLE IF EXISTS 表名;

5、DML

增删改表中的数据记录
添加数据
　　INSERT INTO 表名(列名1,列名2,…,列名n) VALUES(值1,值2,…,值n);
　　注意：
　　（1）列名和值要一一对应，包括数量和数据类型
　　（2）如果表名后不指定列名，默认给所有列名赋值，不想赋值的可以赋值NULL，但时间戳类型的列名赋值NULL会被当前系统时间替换
　　（3）除了数字类型，其它类型需要加上引号（单双都可以）赋值，日期类型的格式是"yyyy-MM-dd"
删除数据
　　DELETE FROM 表名 WHERE 条件
　　注意：
　　　　如果不加条件，则删除所有数据记录 DELETE FROM 表名;不建议这么做，因为这样做的话
　　表中有多少条记录，就会执行多少次删除操作，效率比较低
　　　　删除表中所有记录，建议执行：TRUNCATE FROM 表名;先删除表，再创建一个一模一样
　　的空表，不管表中有多少条数据记录，只会执行这两次操作，效率更高
更新数据
UPDATE 表名 SET 列名1=值1,列名2=值2,…,列名n=值n WHERE 条件;
　　　　如UPDATE STU SET SCORE=100 WHERE ID=1;
　　　　注意：如果不加条件，则会修改表中所有数据记录

6、DQL

查询表中数据记录
　　SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段 HAVING 分组之后对结果集的限定条件 ORDER BY 排序字段 ASC/DESC LIMIT分页限定
基础查询
　　查询表中所有数据记录
　　　　SELECT * FROM 表名;
　　对结果集去重
　　　　SELECT DISTINCT 列名 FROM 表名;
　　计算两列之和/差/积/商
　　　　SELECT NAME,MATH+ENGLISH FROM 表名;
　　　　注意：有NULL参与的计算，计算结果都为NULL
　　　　解决：SELECT NAME,IFNULL(MATH,0)+IFNULL(ENGLISH,0) FROM 表名;
　　起别名
　　　　SELECT NAME,IFNULL(MATH,0)+IFNULL(ENGLISH,0) AS 总分 FROM 表名;
　　　　SELECT NAME,IFNULL(MATH,0)+IFNULL(ENGLISH,0) 总分 FROM 表名;
条件查询
　　WHERE 条件列表
　　运算符：>= <= = !=(<>) AND(&&) OR(||)
　　在什么和什么之间 BETWEEN AND
　　　　SELECT NAME FROM STUDENT WHERE AGE BETWEEN 10 AND 20;
　　在什么范围内 IN
　　　　SELECT NAME FROM STUDENT WHERE AGE IN (19,22,25);
　　查询某个字段为NULL的数据记录 IS NULL不能使用=或!=判断
　　　　SELECT NAME FROM STUDENT WHERE ENGLISH IS NULL;
　　　　不为NULL SELECT NAME FROM STUDENT WHERE ENGLISH IS NOT NULL;
　　模糊查询 LIKE
　　　　占位符 单个任意字符
　　　　　　%0或多个任意字符
　　　　查询姓马的人
　　　　　　SELECT * FROM STUDENT WHERE NAME LIKE ‘马%’;
　　　　查询第二个字是小的人
　　　　　　SELECT * FROM STUDENT WHERE NAME LIKE '小%’;
　　　　查询姓名是3个字的人
　　　　　　SELECT * FROM STUDENT WHERE NAME LIKE '_’;
　　　　查询姓名中有马的人
　　　　　　SELECT * FROM STUDENT WHERE NAME LIKE ‘%马%’;
排序查询
　　ORDER BY 排序字段1 排序方式1,排序字段2 排序方式2.。。
　　当排序字段1相等时才会使用排序字段2的排序，如数学成绩相等才会对两个学生的英语成绩进行排序
　　排序方式 ASC升序默认 DESC 降序
聚合函数
　　将一列数据作为一个整体，进行纵向的计算 结果都是单行单列的值
　　count max min sum avg
　　SELECT COUNT(列名) FROM 表名;
　　注意：进行计算时会排除NULL值，如按英语成绩计算成绩表有几个人，有一个人的英语成绩为NULL，则不会计算在里面
　　　　　SELECT COUNT(NAME) FROM SCORE;
　　解决：
　　法一：将为NULL的英语成绩换为0，就会将该学生算在里面
　　　　　SELECT COUNT(IFNULL(ENGLISH,0)) FROM SCORE;
　　法二：选择不含NULL的列
　　　　　主键
　　　　　COUNT(*) 返回行数 只要每一行中有一个非NULL值就算在里面 不推荐
分组查询
　　GROUP BY 分组字段
　　注意：
　　　　分组之后查询的字段只能是分组字段或聚合函数
　　　　WHERE和HAVING的区别？
　　　　　　WHERE在分组之前进行限定，不满足条件的不会参与分组；
　　　　　　HAVING在分组之后进行限定，不满足条件不会被查询出来
　　　　　　WHERE之后不能跟聚合函数，HAVING之后可以
　　按性别分组，查询分组后的性别，人数和数学平均分
　　　　SELECT SEX,AVG(MATH),COUNT(ID) FROM SCORE GROUP BY SEX;
　　按性别分组，数学成绩低于80分的不参与分组，查询分组后的性别，人数和平均分
　　　　SELECT SEX,AVG(MATH),COUNT(ID)
　　　　FROM SCORE
　　　　WHERE MATH>80
　　　　GROUP BY SEX;
　　按性别分组，数学成绩低于80分的不参与分组，查询分组后的性别，人数和平均分,只查询人数>2的组
　　　　SELECT SEX,AVG(MATH),COUNT(ID)
　　　　FROM SCORE
　　　　WHERE MATH>80
　　　　GROUP BY SEX 　　
　　　　HAVING ,COUNT(ID)>2;
　　　　给查询的数据加上别名（一般用英文）
　　　　SELECT SEX,AVG(MATH),COUNT(ID) 人数
　　　　FROM SCORE
　　　　WHERE MATH>80
　　　　GROUP BY SEX
　　　　HAVING ,人数>2;
分页查询
　　LIMIT 起始索引,每页查询的条数 该语法是MySQL的一个方言
　　　　SELECT * FROM SCORE LIMIT 0,3;第一页
　　　　SELECT * FROM SCORE LIMIT 3,3;第二页
　　每页显示的条数不变，只需要改变起始索引
　　　　起始索引=(当前页码-1)*每页显示的条数 页码会从浏览器的点击传递过来