准备工作:
查看是否已经安装mysql或MariaDB(安装包可私信获取)
配置环境变量
检测环境变量是否安装成功
SQL脚本~db10-db40
这四个数据库是研究需要用到的数据库创建语句,为了防止你们测试数据库有差异,所以直接在前面全都写上
在已登录mysql的DOS窗口下,直接复制这一堆代码就可以
-- -----------------------------------
-- 创建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,'李四','男','1995-10-25','开发部','项目经理','1800','300');
insert into emp values(null,'王五','男','1994-11-6','开发部','项目经理','2500','600');
insert into emp values(null,'赵六','女','1996-09-14','开发部','项目经理','1400','300');
insert into emp values(null,'张三','男','1991-05-18','开发部','部门总监','4200','500');
insert into emp values(null,'大猴','男','1993-11-18','开发部','项目经理','1600','500');
insert into emp values(null,'王克','女','1998-07-18','美工部','项目经理','3700','600');
insert into emp values(null,'苍老师','男','1995-08-18','美工部','部门总监','4850','400');
insert into emp values(null,'范传奇','男','1999-09-18','美工部','项目经理','3200','600');
insert into emp values(null,'刘涛','男','1990-10-18','美工部','项目经理','2700','400');
insert into emp values(null,'韩大仙','男','1980-12-18',null,'CEO','5000',null);
-- -----------------------------------
-- 创建db20库、dept表、emp表并插入记录
-- -----------------------------------
-- 删除db20库(如果存在)
drop database if exists db20;
-- 重新创建db20库
create database db20 charset utf8;
-- 选择db20库
use db20;
-- 删除部门表, 如果存在
drop table if exists dept;
-- 重新创建部门表, 要求id, name字段
create table dept(
id int primary key auto_increment, -- 部门编号
name varchar(20) -- 部门名称
);
-- 往部门表中插入记录
insert into dept values(null, '财务部');
insert into dept values(null, '人事部');
insert into dept values(null, '科技部');
insert into dept values(null, '销售部');
-- 删除员工表, 如果存在
drop table if exists emp;
-- 创建员工表, 要求id, name, dept_id
create table emp(
id int primary key auto_increment, -- 员工编号
name varchar(20), -- 员工姓名
dept_id int -- 部门编号
-- ,foreign key(dept_id) references dept(id)
);
insert into emp values(null, '张三', 1);
insert into emp values(null, '李四', 2);
insert into emp values(null, '老王', 3);
insert into emp values(null, '赵六', 4);
insert into emp values(null, '刘能', 4);
-- -----------------------------------
-- 创建db30库、dept表、emp表并插入记录
-- -----------------------------------
-- 删除db30库(如果存在)
drop database if exists db30;
-- 重新创建db30库
create database db30 charset utf8;
-- 选择db30库
use db30;
-- 删除部门表, 如果存在
drop table if exists dept;
-- 重新创建部门表, 要求id, name字段
create table dept(
id int primary key auto_increment, -- 部门编号
name varchar(20) -- 部门名称
);
-- 往部门表中插入记录
insert into dept values(null, '财务部');
insert into dept values(null, '人事部');
insert into dept values(null, '科技部');
insert into dept values(null, '销售部');
-- 删除员工表, 如果存在
drop table if exists emp;
-- 创建员工表(员工编号、员工姓名、所在部门编号)
create table emp(
id int primary key auto_increment, -- 员工编号
name varchar(20), -- 员工姓名
dept_id int -- 部门编号
);
-- 往员工表中插入记录
insert into emp values(null, '张三', 1);
insert into emp values(null, '李四', 2);
insert into emp values(null, '老王', 3);
insert into emp values(null, '赵六', 5);
-- -----------------------------------
-- 创建db40库、dept表、emp表并插入记录
-- -----------------------------------
-- 删除db40库(如果存在)
drop database if exists db40;
-- 重新创建db40库
create database db40 charset utf8;
-- 选择db40库
use db40;
-- 创建部门表
create table dept( -- 创建部门表
id int primary key, -- 部门编号
name varchar(50), -- 部门名称
loc varchar(50) -- 部门位置
);
-- 创建员工表
create table emp( -- 创建员工表
id int primary key, -- 员工编号
name varchar(50), -- 员工姓名
job varchar(50), -- 职位
topid int, -- 直属上级
hdate date, -- 受雇日期
sal int, -- 薪资
bonus int, -- 奖金
dept_id int, -- 所在部门编号
foreign key(dept_id) references dept(id)
);
-- 往部门表中插入记录
insert into dept values ('10', '开发部', '北京');
insert into dept values ('20', '美工部', '上海');
insert into dept values ('30', '大数据部', '广州');
insert into dept values ('40', '销售部', '深圳');
-- 往员工表中插入记录
insert into emp values ('1001', '王克', '办事员', '1007', '1980-12-17', '800', 500, '20');
insert into emp values ('1003', '王五', '分析员', '1011', '1981-02-20', '1900', '300', '10');
insert into emp values ('1005', '李四', '推销员', '1011', '1981-02-22', '2450', '600', '10');
insert into emp values ('1007', '苍老师', '经理', '1017', '1981-04-02', '3675', 700, '20');
insert into emp values ('1009', '张小弟', '推销员', '1011', '1981-09-28', '1250', '1400', '10');
insert into emp values ('1011', '张三', '经理', '1017', '1981-05-01', '3450', 400, '10');
insert into emp values ('1013', '张三炮', '办事员', '1011', '1981-06-09', '1250', 800, '10');
insert into emp values ('1015', '程叫兽', '分析员', '1007', '1987-04-19', '3000', 1000, '20');
insert into emp values ('1017', '韩大仙', '董事长', null, '1981-11-17', '5000', null, null);
insert into emp values ('1019', '赵六', '推销员', '1011', '1981-09-08', '1500', 500, '10');
insert into emp values ('1021', '范传奇', '办事员', '1007', '1987-05-23', '1100', 1000, '20');
insert into emp values ('1023', '赵子龙', '经理', '1017', '1981-12-03', '950', null, '30');
insert into emp values ('1025', '婷婷', '分析员', '1023', '1981-12-03', '3000', 600, '30');
insert into emp values ('1027', '静静', '办事员', '1023', '1982-01-23', '1300', 400, '30');
-- ------------------- 执行完毕 -----------------------
【正文】
一、数据库概述
1、什么是数据库
所谓的数据库就是指存储和管理数据的仓库;
扩展内容1:数据库有哪些分类??
早期:层次型数据库、网络型数据库现在
现在:关系型数据库、非关系型数据库
2、什么是关系型数据库
底层以二维表的形式保存数据的库就是关系型数据库
stu - 学生表
学生编号 | 姓名 | 年龄 |
---|---|---|
1001 | 张三 | 35 |
1002 | 李四 | 18 |
扩展内容2:常见的关系型数据库有哪些?(了解)
Sql Server:微软提供,收费,适用于一些中型或大型的项目中,在java中的使用占比不高(.NET中使用的较多)
Oracle:甲骨文公司提供,收费,适用于一些大型或者超大型的项目中,在java中的使用占比非常高
mysql:瑞典MySQLAB公司提供,免费开源,适用于一些小型或者中型的项目中,在Java中的使用占比较高(小巧轻量)
mariadb其实就是MySQL的一个分支,用法和MySQL完全一样。
DB2:IBM公司提供,收费,在一些银行、金融等行业中使用较多。在java中的使用占比也不高。
Sqlite:迷你数据库,嵌入式设备中(安卓、苹果手机、pad)
3、数据库相关概念
3.1 什么是数据库服务器?
数据库服务器就是一个软件(比如mysql软件)将数据库软件安装在电脑上,当前电脑就是一个数据库服务器。就可以对外提供存取数据的服务;
在一个数据库服务器中可以创建多个数据库(dataBases),每一个数据库都是一个单独的仓库。
3.2 什么是数据库?
数据库就是存储和管理数据的仓库,通常情况下,一个网站的中的所有数据会存放在一个数据库中。例如:
jd.com db_jd(数据库)
taobao.com db_taobao(数据库)
3.3 什么是表?
一个数据库中可以创建多张表,每张表用于存储一类信息(数据库);
例如:
jd.com中的用户数据 tb_user(表)
jd.com中的商品数据 tb_product(表)
jd.com中的订单数据 tb_order(表)
3.4 什么是表记录?·
一张表中可以包含多行表记录,每一行表记录用于存储某一个具体的数据
学生编号 | 姓名 | 年龄 |
---|---|---|
1001 | 张三 | 35 |
1002 | 李四 | 18 |
… | … | … |
4、什么是SQL语言
SQL是一们用于操作关系型数据库的通用的语言(使用SQL可以操作所有的关系型数据库);
使用SQL可以操作数据库、表、表记录
(1)创建数据库、删除数据库、修改数据库、查询数据库
(2)创建表、删除表、修改表、查询表
(3)新增表记录、删除表记录、修改表记录、查询表记录
使用SQL也可以操作存储过程/视图/索引等;
提示:
SQL是一个标准通用的操作关系型数据库的语言(普通话),每个数据库厂商为了增强自己数据库的功能,都提供了支持自己数据库的语言,称之为数据库的方言。方言不通用!
二、连接MySQL服务器
通过命令行工具可以登录MySQL客户端,连接MySQL服务器,从而访问服务器中的数据
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
4、FAQ:常见问题:
解决方法:复制mysql安装目录下的bin目录的路径,将bin目录的路径添加到path环境变量中!!
扩展内容3:
(1) 在cmd中连接mysql服务器之后,可以使用 #、/**/、-- 等符号添加注释,例如:
(2) 在cmd中连接mysql服务器之后,在书写SQL语句时,可以通过 \c 取消当前语句的执行。例如:
三、数据库及表操作
1、创建、删除、查看数据库
提示:
(1) SQL语句对大小写不敏感。推荐关键字使用大写,自定义的名称(库名,表名,列名等)使用小写。
SHOW DATABASES; -- 查看当前数据库服务器中的所有库
CREATE DATABASE mydb1; -- 创建mydb1库
(2) 并且在自定义名称时,针对多个单词不要使用驼峰命名,而是使用下划线连接。(例如:tab_name,而不是 tabName )
1.查看mysql服务器中所有数据库
show databases;
- 进入某一数据库(进入数据库后,才能操作库中的表和表记录)
语法:USE 库名;
use test;
查看已进入的库
SELECT DATABASE();
3.查看当前数据库中的所有表
-- 先进入某一个库,再查看当前库中的所有表
show tables;
4.删除mydb1库
语法:DROP DATABASE 库名;
drop database mydb1;
---- 思考:当删除的库不存在时,如何避免错误产生?
drop database if exists mydb1;
5.重新创建mydb1库,指定编码为utf8
语法:CREATE DATABASE 库名 CHARSET 编码;
需要注意的是,mysql中不支持横杠(-),所以utf-8要写成utf8
-- 如果存在mydb1,则先删除,再重建
create database mydb1 charset utf8;
如果不存在则创建mydb1;
create database if not exists mydb1 charset utf8;
6.查看建库时的语句(并验证数据库库使用的编码)
语法:SHOW CREATE DATABASE 库名;
show create database mydb1; -- 查看建库时的语句
show create table stu; -- 查看建表时的语句
2、创建、删除、查看表
- 进入mydb1库,删除stu学生表(如果存在)
语法:DROP TABLE 表名;
use mydb1;
drop table if exists stu;
8.创建stu学生表(编号[数值类型]、姓名、性别、出生年月、考试成绩[浮点型]),建表的语法:
CREATE TABLE 表名(
列名 数据类型,
列名 数据类型,
...
列名 数据类型
);
SQL语句:
drop table if exists stu;
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; 再插入记录!
insert into stu(id,name,gender,birthday,score) value (null,'tom','male','1985-1-1',78);
insert into stu value(null,'张三','female','1985-2-2',88);
insert into stu value(null,'李四','male','1999-3-3',68);
提示:
(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 表名 SET 列=值,列=值,列=值…[WHERE子句];
mysql里面不支持 +=,所以score+=10不允许使用
update stu set score = score + 10;
修改stu表中编号为1的学生成绩,将成绩改为83分。
update stu set score = 83 where id = 1;
提示:where子句用于对记录进行筛选过滤,保留符合条件的记录,将不符合条件的记录剔除。
14 删除stu表中所有的记录
删除记录语法: DELETE FROM 表名 [where条件]
-- 删除整个表的数据
delete from stu;
仅删除符合条件的
-- 删除id大于1的数据
delete from stu where id > 1;
五查询表记录
基础查询
SELECT 语句用于从表中选取数据。结果被存储在一个结果表中(称为结果集);
语法:SELECT 列名称 | * FROM 表名
提示:
(1) *(星号)为通配符,表示查询所有列;
(2)但使用 *(星号)有时会把不必要的列也查出来了,并且效率不如直接指定列名
15查询emp表中的所有员工,显示姓名,薪金,奖金
select name,sal,bonus from emp;
16查询emp表中的所有部门和职位
select dept,job from emp;
思考:
如果查询的结果中,存在大量重复的记录,如何剔除重复记录,只保留一条? */
在select之后、列名之前,使用DISTINCT 剔除重复的记录
select distinct dept,job from emp;
2、 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;
查询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;
查询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);