javaWeb-day01-MySQL(一)

数据库概述

什么是数据库？

存储和管理数据的仓库
早期: 层次式数据库、网络型数据库
现在：关系型数据库、非关系型数据库

什么是关系数据库？

底层以二维表的形式保存数据的库就是关系型数据库

学生编号	姓名	年龄
1001	重庆森林	35
1002	花样年华	36

常见关系型数据库

Sql Server：微软提供，收费，适用于一些中型或大型的项目中，在java中的使用占比不高（.NET中使用的较多）
Oracle：甲骨文公司提供，收费，适用于一些大型或者超大型的项目中，在java中的使用占比非常高
mysql：瑞典MySQLAB公司提供，免费开源，适用于一些小型或者中型的项目中，在Java中的使用占比较高（小巧轻量）
DB2：IBM公司提供，收费，在一些银行、金融等行业中使用较多。在java中的使用占比也不高。
Sqlite：迷你数据库，嵌入式设备中（安卓、苹果手机、pad）

数据库相关概念

1、什么是数据库服务器
数据库服务器就是一个软件（比如mysql软件）将数据库软件安装在电脑上，当前电脑就是 一个数据库服务器。就可以对外提供存取数据的服务
在一个数据库服务器中可以创建多个数据库（dataBases），每一个数据库都是一个单独的仓库。
2、什么是数据库
数据库就是存储和管理数据的仓库，通常情况下，一个网站的中的所有数据会存放在一个数据库中。
3、什么是表
一个数据库中可以创建多张表，每张表用于存储一类信息（数据库），例如：
jd.com中的用户数据 tb_user(表)
jd.com中的商品数据 tb_product(表)
jd.com中的订单数据 tb_order(表)
4、什么表记录·
一张表中可以包含多行表记录，每一行表记录用于存储某一个具体的数据

学生编号	姓名	年龄
1001	重庆森林	35
1002	花样年华	36

什么是 SQL 语言

SQL是一门用于操作关系型数据库的通用的语言（使用SQL可以操作所有的关系型数据库）
使用SQL可以操作数据库、表、表记录
(1)创建数据库、删除数据库、修改数据库、查询数据库
(2)创建表、删除表、修改表、查询表
(3)新增表记录、删除表记录、修改表记录、查询表记录
使用SQL也可以操作存储过程/视图/索引等。
提示：SQL是一个标准通用的操作关系型数据库的语言（普通话），每个数据库厂商为了增强自己数据库的功能，都提供了支持自己数据库的语言，称之为数据库的方言。方言不通用！

连接 MySQL 服务器

以下操作在 dos 窗口下完成:
1、连接 MySQL 服务器：

mysql -uroot -p密码

-u：后面的root是用户名，这里使用的是超级管理员 root；
-p：(小写的p)后面的root是密码，这是在安装MySQL时就已经指定的密码；
2、连接 mysql 服务器并指定IP和端口:

mysql -uroot -proot -h127.0.0.1 -P3306;

-h：后面给出的127.0.0.1是服务器主机名或ip地址，可以省略的，默认连接本机；
-P：(大写的P)后面的3306是连接端口，可以省略，默认连接3306端口；
3、退出客户端命令
quit 或 exit 或 \q
注：(1) 在 cmd 中连接 MySQL 服务器之后，可以使用 #、/**/、-- 等符号添加注释
(2) 在 cmd中连接MySQL 服务器之后，在书写SQL语句时可以通过 \c 取消当前语句的执行

数据库及表操作

创建、删除、查看数据库

提示： (1)SQL语句对大小写不敏感。推荐关键字使用大写，自定义的名称（库名，表名，列名等）使用小写。

show databases;         --查看当前数据库服务器中的所有库
create database mydb1;  --创建 mydb1 库

(2)并且在自定义名称时，针对多个单词不要使用驼峰命名，而是使用下划线连接。(例如：tab_name，而不是 tabName )
01 查看 MySQL 服务器中所有数据库

show databases;

02 进入某一数据库（进入数据库后，才能操作库中的表和表记录）

use test;

03 查看当前数据库中的所有表

show tables;

04 删除mydb1库

drop database mydb1;

当删除的库不存在时，如何避免错误产生?

drop database if exists mydb1;

05 重新创建mydb1库，指定编码为 utf8
需要注意的是， MySQL 中不支持横杠 (-) ，所以 utf-8 要写成 utf8

create database mydb1 charset utf8;

如果存在mydb1,则先删除,再重建

drop database if exists mydb1;
create database mydb1;

如果不存在则创建mydb1

create database if not exists mydb1;

06 查看建库时的语句（并验证数据库库使用的编码）
语法：SHOW CREATE DATABASE 库名;

show create database mydb1;

创建、删除、查看表

07 进入mydb1库，删除 stu 学生表(如果存在)
语法：DROP TABLE 表名;

use mydb1;
drop table if exists stu;

08 创建 stu学生表（编号[数值类型]、姓名、性别、出生年月、考试成绩[浮点型]），建表的语法：

CREATE TABLE 表名(
 列名 数据类型,
 列名 数据类型,
 ...
 列名 数据类型
);

SQL语句:

create table stu(
  id int,
  name varchar(50),
  gender varchar(10),
  birthday date,
  score double
);

09 查看 stu学生表结构
语法：desc 表名

desc stu;

新增、更新、删除表记录

10 往学生表( stu)中插入记录(数据)
语法：INSERT INTO 表名(列名1,列名2,列名3…) VALUES(值1,值2,值3…);

--如果是在 cmd 中执行插入记录的语句,先 set names gbk; 再插入记录!
set names gbk;
insert into stu(id,name,gender,birthday,score) values(1001,'重庆森林','male','1995-1-1',100);
insert into stu values(1002,'mary','female','1994-1-1',60);
insert into stu values(1003,'tom','female','2005-1-1',70);
insert into stu values(1,'男','male','1990-9-0',20);

提示：

(1)当为所有列插入值时，可以省写列名，但值的个数和顺序必须和声明时列的个数和顺序保持一致！
(2)SQL语句中的值为字符串或日期时，值的两边要加上单引号（有的版本的数据库双引号也可以，但推荐使用单引号）。
(3)(针对 cmd 窗口)在插入数据之前，先设置编码：set names gbk;

或者用一下命令连接 MySQL 服务器：

mysql --default-character-set=gbk -uroot -proot

等价于：

mysql -uroot -proot 
set names gbk;

11 查询 stu 表所有学生的信息
语法：SELECT 列名 | * FROM 表名

select * from stu;

12 修改 stu 表中所有学生的成绩，加10分特长分

update stu set  score=score+10;

13 修改 stu表中编号为1的学生成绩，将成绩改为83分

update stu set score=83 where id=1;

提示：where子句用于对记录进行筛选过滤，保留符合条件的记录，将不符合条件的记录剔除。
14 删除 stu表中所有的记录
删除记录语法: DELETE FROM 表名 [where条件]

delete from stu;

仅删除符合条件的

delete from stu where score>100;

MySQL 的数据类型

数值类型
tinyint：占用1个字节，相对于 java 中的 byte
smallint：占用2个字节，相对于java中的short
int：占用4个字节，相对于java中的int
bigint：占用8个字节，相对于java中的long
float：4字节单精度浮点类型，相对于java中的float
double：8字节双精度浮点类型，相对于java中的double
字符串类型
char(n)
定长字符串，最长255个字符。n表示字符数
所谓的定长，是当插入的值长度小于指定的长度时，剩余的空间会用空格填充。
varchar(n)
变长字符串，最长不超过65535个字节，n表示字符数
所谓的不定长，是当插入的值长度小于指定的长度时，剩余的空间可以留给别的数据使用
大文本(长文本)类型
最长65535个字节，一般超过255个字符列的会使用text
另，text也分多种，其中 bigtext 存储数据的长度约为4GB。
char(n)、varchar(n)、text都可以表示字符串类型，其区别在于：
char(n)在保存数据时，如果存入的字符串长度小于指定的长度n，后面会用空格补全，因此可能会造成空间浪费，但是char类型的存 储速度较varchar和text快
因此char类型适合存储长度固定的数据，这样就不会有空间浪费，存储效率比后两者还快！
varchar(n)保存数据时，按数据的真实长度存储，剩余的空间可以留给别的数据用，因此varchar不会浪费空间
因此varchar适合存储长度不固定的数据，这样不会有空间的浪费
text是大文本类型，一般文本长度超过255个字符，就会使用text类型存储
日期类型
date: 年月日
time: 时分秒
datetime: 年月日时分秒
timestamp:
时间戳(实际存储的是一个时间毫秒值)，与datetime存储日期格式相同。两者的区别是:
timestamp 最大表示2038年，而datetime范围是1000~9999
timestamp在插入数据、修改数据时，可以自动更新成系统当前时间

MySQL 的字段约束

主键约束：
如果为一个列添加了主键约束，那么这个列就是主键，主键的特点是唯一且不能为空。
作为一个唯一标识，唯一的表示一条表记录（作用类似于人的身份证号，可以唯一的表示一个人一样。）
添加主键约束，例如将id设置为主键：

create table stu(
 id int primary key,
 ...
);

如果主键是数值类型，为了方便插入主键（并且保证插入数据时，主键不会因为重复而报错），可以设置一个主键自增策略。
将id主键设置为自增

create table stu(
 id int primary key auto_increment,
 ...
);

非空约束：
如果为一个列添加了非空约束，那么这个列的值就不能为空，但可以重复。
例如为password添加非空约束：

create table user(
 password varchar(50) not null,
 ...
);

唯一约束：
如果为一个列添加了唯一约束，那么这个列的值就必须是唯一的（即不能重复），但可以为空。例如为username添加唯一约束及非空约束：

create table user(
 username varchar(50) unique not null,
 ...
);

将 stu 表的 id 设为 主键自增，name 设为唯一：

drop table if exists stu;
create table stu(
 id int primary key auto_increment, -- 将id设置为主键(唯一且不能为空)，并且设置为自增
    name varchar(20) unique,
    gender varchar(10),
    birthday date,
    score double
);

而后往 stu 表中插入记录：（id 值都设为 null ，系统自动会为 id 值从1 开始递增）

insert into stu(id,name,gender,birthday,score) 
value(null,null,'male','1985-1-1',78);
insert into stu value(null,null,'male','1994-10-1',88);
insert into stu value(null,'花样年华','female','1995-10-1',68);

基础查询

准备数据：

-- -----------------------------------
-- 创建db10库、emp表并插入记录
-- -----------------------------------
-- 删除db10库(如果存在)
drop database if exists db10;
-- 重新创建db10库
create database db10 charset utf8;
-- 选择db10库
use db10;
-- 删除员工表(如果存在)
drop table if exists emp;
-- 创建员工表
create table emp(
 id int primary key auto_increment, -- 员工编号
 name varchar(50),     -- 员工姓名
 gender char(1),      -- 员工性别
 birthday date,      -- 员工生日
 dept varchar(50),     -- 所属部门
 job varchar(50),     -- 所任职位
 sal double,       -- 薪资
 bonus double      -- 奖金
 );
 -- 往员工表中插入记录
insert into emp values(null,'aa','男','1995-10-25','市场部','销售人员','1800','300');
insert into emp values(null,'bb','男','1994-11-6','市场部','销售人员','2500','600');
insert into emp values(null,'cc','女','1996-09-14','市场部','销售人员','1400','300');
insert into emp values(null,'dd','男','1991-05-18','市场部','部门总监','4200','500');
insert into emp values(null,'ee','男','1993-11-18','市场部','销售人员','1600','500');
insert into emp values(null,'ff','女','1998-07-18','就业部','销售人员','3700','600');
insert into emp values(null,'ii','男','1995-08-18','就业部','部门总监','4850','400');
insert into emp values(null,'gg','男','1999-09-18','就业部','销售人员','3200','600');
insert into emp values(null,'kk','男','1990-10-18','就业部','销售人员','2700','400');
insert into emp values(null,'ll','男','1980-12-18',null,'CEO','5000',null);

SELECT 语句用于从表中选取数据。结果被存储在一个结果表中（称为结果集）
语法：SELECT 列名称 | * FROM 表名
提示：(1) *（星号）为通配符，表示查询所有列。
(2)但使用 *（星号）有时会把不必要的列也查出来了，并且效率不如直接指定列名
15 查询 emp表中的所有员工，显示姓名，薪资，奖金

select name,sal,bonus from emp;

16 查询emp表中的所有部门和职位

select dept,job from emp;

如果查询的结果中，存在大量重复的记录 使用DISTINCT 剔除重复的记录

select distinct dept,job from emp;

Where 子句查询

WHERE子句查询语法：SELECT 列名称 | * FROM 表名称 WHERE 列 运算符 值WHERE子句后面跟的是条件，条件可以有多个，多个条件之间用连接词（or | and）进行连接
下面的运算符可在 WHERE 子句中使用：

17 查询 emp 表中薪资大于3000的所有员工，显示员工姓名、薪资

select name,sal from emp where sal>3000;

18 查询 emp 表中总薪资(薪资+奖金)大于3500的所有员工，显示员工姓名、总薪资

select name,sal+bonus from emp where sal+bonus>3500;

ifnull(列, 值)函数: 判断指定的列是否包含null值，如果有null值，用第二个值替换null值

select name,ifnull(sal,0)+ifnull(bonus,0) from emp where ifnull(sal,0)+ifnull(bonus,0)>3500;

注意查看上面查询结果中的表头，如何将表头中的 sal+bonus 修改为 “总薪资”
使用as可以为表头指定别名

select name as 姓名,ifnull(sal,0)+ifnull(bonus,0) as 总薪资 from emp where ifnull(sal,0)+ifnull(bonus,0)>3500;

另外as可以省略

select name 姓名,ifnull(sal,0)+ifnull(bonus,0) 总薪资 from emp where ifnull(sal,0)+ifnull(bonus,0)>3500;

19 查询 emp表中薪资在3000和4500之间的员工，显示员工姓名和薪资

select name,sal from emp where sal >=3000 and sal <=4500;

提示: between…and… 在…之间

select name,sal from emp where sal between 3000 and 4500;

20 查询 emp表中薪资为 1400、1600、1800的员工，显示员工姓名和薪资

select name,sal from emp where sal=1400 or sal=1600 or sal=1800;

或者

select name,sal from emp where sal in(1400,1600,1800);

21 查询薪资不为1400、1600、1800的员工
方式一:select *from emp where not(sal=1400 or sal =1600 or sal=1800);
方式二:select *from emp where sal not in(1400,1600,1800);
方式三:select *from emp where sal!=1400 and sal!=1600 and sal!=1800;
22 查询 emp表中薪资大于4000和薪资小于2000的员工，显示员工姓名、薪资。

select name,sal from emp where sal>4000 or sal <2000;

23 查询emp表中薪资大于3000并且奖金小于600的员工，显示员工姓名、薪资、奖金

select name,sal,bonus from emp where sal>3000 and bonus <600;

处理null值
24 查询没有部门的员工（即部门列为null值）

select *from emp where dept is null;

思考：如何查询有部门的员工（即部门列不为null值）

select *from emp where dept is not null;
select *from emp where not(dept is null);