mysql的入门

1 MySQL数据库

1.1数据库概述

1.1.1什么是数据库？

数据库：英文为Database，简称DB
数据库是按照数据结构来组织、存储和管理数据的仓库，简而言之，数据库就是存储数据的仓库。
数据库是一个专业存储和管理数据的软件系统，相比传统人工记录数据和直接使用文件保存数据，数据库具有更安全、更可靠、效率更高的优势。

扩展内容1：数据库有哪些分类？(了解)
早期：层次式数据库、网络型数据库(已过时)
现在：关系型数据库、非关系型数据库

1.1.2什么是关系型数据库？

底层以二维表的形式保存数据的数据库，就是关系型数据库，例如：下面有一个学生表。
编号 姓名 年龄
1 张三 20
2 李四 22

扩展内容2：常见的关系型数据库有哪些？(了解)
SQL Server：微软提供，适用于中型、大型项目，收费，在java中的使用占比不高，在.NET语言中使用较多。
Oracle：甲骨文公司提供，适用于大型、超大型项目，功能强大，性能优异，收费，在Java中使用占比很高。
mysql：瑞典MySQLAB公司提供，适用于小型、中型项目，免费开源，小巧轻量，性能也不差。在Java中使用占比较高。
DB2：IBM公司提供，适用于大型项目，收费，在Java中使用占比不高。
Sqlite：迷你，嵌入式设备，智能家居，手机，ipad等

扩展内容3：什么是非关系型数据库？(了解)
非关系数据库的底层结构是以Key-Value、列等结构存储数据的
常见的非关系型数据库有：redis、mongodb

1.1.3数据库相关概念

1、什么是数据库服务器
服务器软件，mysql 软件，将服务器软件装在电脑上，就可以作为一台服务器对外提供服务器。（存取数据）

2、什么是数据库
在每一个数据库服务器中，可以有很多个仓库（数据库），通常情况下，一个网站中的所有数据会存放在一个数据库中。
京东 db_jd数据库
百度 db_baidu数据库
淘宝 db_taobao数据库
…

3、什么是表
一个数据库中可以创建多张表，而一张表用于存放一类信息。
（Java中的类对应数据库中的表）
商品信息 tb_product表
购物车信息 tb_cart表
用户信息 tb_user表
订单信息 tb_order表
…

4、什么表记录
一张表中可以包含多条表记录，一个表记录用于存放某一条具体的信息

1.1.4什么是SQL语言？

SQL：(Structured Query Language) 结构化查询语言
SQL是一种ANSI（美国国家标准化组织） 的标准计算机语言，用于访问和操作数据库。
SQL是用于操作关系型数据库的通用的语言，即通过SQL语言可以操作所有的关系型数据库。
SQL语言可以用于操作数据库（创建、删除、查看）、操作数据表（创建、删除、修改、查看）、操作表记录（新增、更新、删除、查询）等。

1.2连接mysql服务器

通过命令行工具可以登录MySQL客户端，连接MySQL服务器，从而访问服务器中的数据。
1、连接mysql服务器：
mysql -uroot -proot
-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环境变量中!!

扩展内容4：
(1)在cmd中连接mysql服务器之后，可以使用 #、/**/、-- 等符号添加注释
例如:

(2)在cmd中连接mysql服务器之后，在书写SQL语句时，可以通过 \c 取消当前语句的执行。例如：

1.3数据库及表操作

1.3.1创建、删除、查看数据库

提示： (1)SQL 语句对大小写不敏感。推荐关键字使用大写，自定义的名称（库名，表名，列名等）使用小写。
(2)并且在自定义名称时，针对多个单词不要使用驼峰命名，而是使用下划线连接。(例如：tab_name，而不是 tabName)
– 01.查看mysql服务器中所有数据库

– 02.进入某一数据库（进入数据库后，才能操作库中的表和表记录）
– 语法：USE 库名;

– 查看已进入的库
SELECT DATABASE();

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

– 04.删除mydb1库
– 语法：DROP DATABASE 库名;

– 思考：当删除的表不存在时，如何避免错误产生?

– 05.重新创建mydb1库，指定编码为utf8
– 语法：CREATE DATABASE 库名 CHARSET 编码;

– 如果不存在则创建mydb1;

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

1.3.2创建、删除、查看表

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

– 08.创建stu学生表（编号[数值类型]、姓名、性别、出生年月、考试成绩[浮点型]），建表的语法：
CREATE TABLE 表名(
列名 数据类型,
列名 数据类型,
…
);

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

1.4新增、更新、删除表记录
– 10.往学生表(stu)中插入记录(数据)
– 语法：INSERT INTO 表名(列名1,列名2,列名3…) VALUES(值1,值2,值3…);

提示：
(1)当为所有列插入值时，可以省写列名，但值的个数和顺序必须和声明时列的个数和顺序保持一致！
(2)SQL语句中的值为字符串或日期时，值的两边要加上单引号（有的版本的数据库双引号也可以，但推荐使用单引号）。
(3)在插入数据之前，先设置编码设置编码：set names gbk;
或者用一下命令连接mysql服务器：
mysql --default-character-set=gbk -uroot -proot

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

– 12.修改stu表中所有学生的成绩，加10分特长分
– 修改语法: UPDATE 表名 SET 列=值,列=值,列=值…[WHERE子句];

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

提示：where子句用于对记录进行筛选过滤，保留符合条件的记录，将不符合条件的记录剔除。

– 14.删除stu表中所有的记录
– 删除记录语法: DELETE FROM 表名 [where条件]

– 仅删除符合条件的

1.5查询表记录

– 准备数据： 以下练习将使用db10库中的表及表记录，请先进入db10数据库!!!

1.5.1基础查询

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

– 15.查询emp表中的所有员工，显示姓名，薪资，奖金

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

思考：如果查询的结果中，存在大量重复的记录，如何剔除重复记录，只保留一条？ */
– 在select之后、列名之前，使用DISTINCT 剔除重复的记录

1.5.2WHERE子句查询

WHERE子句查询语法：SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
下面的运算符可在 WHERE 子句中使用：

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

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

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

– 注意查看上面查询结果中的表头，如何将表头中的 sal+bonus 修改为 “总薪资”

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

– 提示: between…and… 在…之间

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

– 21.查询薪资不为1400、1600、1800的员工

– 22.查询emp表中薪资大于4000和薪资小于2000的员工，显示员工姓名、薪资。

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

– 处理null值

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

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

1.5.3模糊查询

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
可以和通配符（%、）配合使用，其中"%“表示0或多个任意的字符。”"表示一个任意的字符。
语法：SELECT 列 | * FROM 表名 WHERE 列名 LIKE 值
示例：
– 25.查询emp表中姓名中包含"涛"字的员工，显示员工姓名。

– 26.查询emp表中姓名中以"刘"字开头的员工，显示员工姓名。

– 27.查询emp表中姓名以"刘"开头，并且姓名为两个字的员工，显示员工姓名。

1.5.4多行函数查询

多行函数也叫做聚合(聚集)函数，根据某一列或所有列进行统计。
常见的多行函数有：
COUNT( 列名|* )：统计结果集中某一列或记录行的行数。
MAX( 列 )：统计结果集中某一列值中的最大值
MIN( 列 )：统计结果集中某一列值中的最小值
SUM( 列 )：统计结果集中某一列所有值的和
AVG( 列 )：统计结果集中某一列值的平均值
提示：多行函数不能用在where子句中
– 28.统计emp表中薪资大于3000的员工个数）

– 29.求emp表中的最高薪资

– 30.统计emp表中所有员工的薪资总和(不包含奖金)

– 31.统计emp表员工的平均薪资(不包含奖金)

1.5.5分组查询

GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT，SUM，AVG，MAX，MIN等函数。
语法：SELECT 列 | * FROM 表名 [WHERE子句] GROUP BY 列;
– 32.对emp表按照部门对员工进行分组，查看分组后效果。

– 33.对emp表按照职位进行分组，并统计每个职位的人数，显示职位和对应人数

– 34.对emp表按照部门进行分组，求每个部门的最高薪资(不包含奖金)，显示部门名称和最高薪资

1.5.6排序查询

使用 ORDER BY 子句将结果集根据指定的列排序后再返回
语法：SELECT 列名 FROM 表名 ORDER BY 列名 [ASC|DESC]
ASC(默认)升序，即从低到高；DESC 降序，即从高到低。
– 35.对emp表中所有员工的薪资进行升序(从低到高)排序，显示员工姓名、薪资。

– 36.对emp表中所有员工奖金进行降序(从高到低)排序，显示员工姓名、奖金。

1.5.7分页查询

在mysql中，通过limit进行分页查询：
limit (页码-1)*每页显示记录数，每页显示记录数
– 37.查询emp表中的所有记录，分页显示：每页显示3条记录，返回第 1 页。

– 38.查询emp表中的所有记录，分页显示：每页显示3条记录，返回第 2 页。

1.5.8其他函数

curdate() 获取当前日期 年月日
curtime() 获取当前时间 时分秒
sysdate() 获取当前日期+时间 年月日 时分秒
year(date) 返回date中的年份
month(date) 返回date中的月份
day(date) 返回date中的月份
hour(date) 返回date中的小时
minute(date) 返回date中的分钟
second(date) 返回date中的秒
CONCAT(s1,s2…) 将s1,s2 等多个字符串合并为一个字符串
CONCAT_WS(x,s1,s2…) 同CONCAT(s1,s2,…) 函数，但是每个字符串之间要加上x，x是分隔符
– 39.查询emp表中所有在1993和1995年之间出生的员工，显示姓名、出生日期。

– 40.查询emp表中本月过生日的所有员工

– 41.查询emp表中员工的姓名和薪资（薪资格式为: xxx(元) ）

1.6mysql的数据类型

1.6.1数值类型

MySQL中支持多种整型，其实很大程度上是相同的，只是存储值的大小范围不同而已。
tinyint：占用1个字节，相对于java中的byte
smallint：占用2个字节，相对于java中的short
int：占用4个字节，相对于java中的int
bigint：占用8个字节，相对于java中的long
其次是浮点类型即：float和double类型
float：4字节单精度浮点类型，相对于java中的float
double：8字节双精度浮点类型，相对于java中的double

1.6.2字符串类型

1、char(n) 定长字符串，最长255个字符。n表示字符数，例如：
– 创建user表，指定用户名为char类型，字符长度不超过10
create table user(
username char(10),
…
);
所谓的定长，是当插入的值长度小于指定的长度时，剩余的空间会用空格填充。（这样会浪费空间）

2、varchar(n) 变长字符串，最长不超过 65535个字节，n表示字符数，一般超过255个字节，会使用text类型，例如：
– 创建user表，指定用户名为varchar类型，长度不超过10
create table user(
username varchar(10)
);
所谓的不定长，是当插入的值长度小于指定的长度时，剩余的空间可以留给别的数据使用。（节省空间）
3、大文本（长文本）类型
最长65535个字节，一般超过255个字符列的会使用text。
– 创建user表：
create table user(
resume text
);
另，text也分多种，其中bigtext存储数据的长度约为4GB。

扩展内容3：(面试题)char(n)、varchar(n)、text都可以表示字符串类型，其区别在于：
(1)char(n)在保存数据时, 如果存入的字符串长度小于指定的长度n，后面会用空格补全，因此可能会造成空间浪费，但是char类型的存储速度较varchar和text快。
因此char类型适合存储长度固定的数据，这样就不会有空间浪费，存储效率比后两者还快！
(2)varchar(n)保存数据时, 按数据的真实长度存储, 剩余的空间可以留给别的数据用，因此varchar不会浪费空间。
因此varchar适合存储长度不固定的数据，这样不会有空间的浪费。
(3)text是大文本类型，一般文本长度超过255个字符，就会使用text类型存储。

1.6.3日期类型

date：年月日
time：时分秒
datetime：年月日 时分秒
timestamp：时间戳(实际存储的是一个时间毫秒值)，与datetime存储日期格式相同。两者的区别是:
(1)timestamp最大表示2038年，而datetime范围是1000~9999
(2)timestamp在插入数、修改数据时，可以自动更新成系统当前时间(后面用到时再做讲解)

1.7mysql的字段约束

1.7.1主键约束

主键约束：如果为一个列添加了主键约束，那么这个列就是主键，主键的特点是唯一且不能为空。
添加主键约束，例如将id设置为主键：
create table stu(
id int primary key,
…
);

1.7.2非空约束

非空约束：如果为一个列添加了非空约束，那么这个列的值就不能为空，但可以重复。
添加非空约束，例如为password添加非空约束：
create table user(
password varchar(50) not null,
…
);

1.7.3唯一约束

唯一约束：如果为一个列添加了唯一约束，那么这个列的值就必须是唯一的（即不能重复），但可以为空。
添加唯一约束，例如为username添加唯一约束及非空约束：
create table user(
username varchar(50) unique not null,
…
);

1.7.4外键约束

外键其实就是用于通知数据库两张表数据之间对应关系的这样一个列。
这样数据库就会帮我们维护两张表中数据之间的关系。
(1) 创建表的同时添加外键
create table emp(
id int,
name varchar(50),
dept_id int,
foreign key(dept_id) references dept(id)
);

(2) 如果在创建表时没有指定外键，那么后期该如何指定外键？以及如何删除外键？

1.8表关系

常见的表关系分为以下三种：
一对多(多对一)、一对一、多对多

1.9多表查询

– 准备数据： 以下练习将使用db30库中的表及表记录，请先进入db30数据库!!!

1.9.1连接查询

– 42.查询部门和部门对应的员工信息

1.9.2左外连接查询

– 43.查询所有部门和部门下的员工，如果部门下没有员工，员工显示为null

1.9.3右外连接查询

– 44.查询部门和所有员工，如果员工没有所属部门，部门显示为null

1.9.4子查询

– 准备数据：以下练习将使用db40库中的表及表记录，请先进入db40数据库!!!
– 45.列出薪资比’王海涛’薪资高的所有员工，显示姓名、薪资

– 46.列出与’刘沛霞’从事相同职位的所有员工，显示姓名、职位。

– 47.列出薪资比’大数据部’部门(已知部门编号为30)所有员工薪资都高的员工信息，显示员工姓名、薪资和部门名称。

1.9.5多表查询练习

– 48.列出在’培优部’任职的员工，假定不知道’培优部’的部门编号， 显示部门名称，员工名称。
– 关联查询两张表

– 求出在培优部的员工

– 49.(自查询)列出所有员工及其直接上级，显示员工姓名、上级编号，上级姓名

– 50.列出最低薪资大于1500的各种职位，显示职位和该职位最低薪资

– 51.列出在每个部门就职的员工数量、平均工资。显示部门编号、员工数量，平均薪资。

– 52.查出至少有一个员工的部门，显示部门编号、部门名称、部门位置、部门人数。

– 53.列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。

– 54.列出每个部门薪资最高的员工信息，显示部门编号、员工姓名、薪资

1.10数据库备份与恢复

1.10.1备份数据库

在cmd窗口中(未登录的状态下)，可以通过如下命令对指定的数据库进行备份：
mysqldump -u用户名 -p 数据库的名字 > 备份文件的位置
例如: 对db40库中的数据(表，表记录)进行备份，备份到 d:/db40.sql文件中
mysqldump -uroot -p db40 > d:/db40.sql
键入密码，如果没有提示，即表示备份成功！

也可以一次性备份所有库，例如：
对mysql服务器中所有的数据库进行备份，备份到 d:/all.sql文件中
mysqldump -uroot -p --all-database > d:/all.sql
键入密码，如果没有提示，即表示备份成功！

1.10.2恢复数据库

在cmd窗口中(未登录的状态下)，可以通过如下命令对指定的数据库进行恢复：
mysql -u用户名 -p 数据库的名字 < 备份文件的位置
例如: 将d:/db40.sql文件中的数据恢复到db60库中
– 在cmd窗口中（已登录的状态下）， 先创建db60库，进入db60库：
create database db60 charset utf8;
– 在cmd窗口中（未登录的状态下）
mysql -uroot -p db60 < d:/db40.sql

或 例如: 将d:/db40.sql文件中的数据恢复到db80库中
– 在cmd窗口中（已登录的状态下），先创建db80库，进入db80库：
create database db80 charset utf8;
use db80;
– 再通过source执行指定位置下的sql文件：
source d:/db40.sql

1.11扩展内容

现创建学生表：
use test; – 进入test库
drop table if exists stu; – 删除学生表(如果存在)
create table stu( – 创建学生表
id int, – 学生id
name varchar(20), – 学生姓名
gender char(1), – 学生性别
birthday date – 出生年月
);

1.11.1修改表—新增列

语法：ALTER TABLE tabname ADD col_name datatype [DEFAULT expr][,ADD col_name datatype…];
1、往stu表中添加score列，double类型
alter table stu add score double;

1.11.2修改表—修改列

语法：ALTER TABLE tabname MODIFY (col_name datatype [DEFAULT expr][,MODIFY col_name datatype]…);
1、修改id列，将id设置为主键
alter table stu modify id int primary key;

2、修改id列，将id主键设置为自动增长
alter table stu modify id int auto_increment;

1.11.3修改表—删除列

语法：ALTER TABLE tabname DROP [COLUMN] col_name;
1、删除stu表中的score列
alter table stu drop score;

1.11.4添加或删除主键及自增

思考：a) 在建表时，如何为id指定主键约束和自增?
b) 建好的表，如何通过修改添加主键约束和自增?
c) 如何删除表中的主键约束和自增?
1、创建stu学生表，不添加主键自增, 查看表结果
use mydb1; – 切换到mydb1库
drop table if exists stu; – 删除stu学生表(如果存在)
create table stu( – 重建stu学生表，没有主键自增
id int,
name varchar(20),
gender char(1),
birthday date
);
desc stu; – 查看表结构
表结构如下: 没有主键约束和自增。

2、如果表没有创建，或者要删除重建，在创建时可以指定主键或主键自增
drop table if exists stu; – 删除stu表
create table stu( – 重新创建stu表时，指定主键自增
id int primary key auto_increment,
name varchar(20),
gender char(1),
birthday date
);
desc stu; – 查看表结构
表结构如下: 已经添加了主键约束和自增。

3、如果不想删除重建表，也可以通过修改表添加主键或主键自增
再次执行第1步，创建stu学生表，不添加主键自增，查看表结果
– 例如: 将stu学生表中的id设置为主键和自动增长
alter table stu modify id int primary key auto_increment;
desc stu; – 查看表结构

如果只添加主键约束，不设置自增
alter table stu modify id int primary key;

如果已经添加主键约束，仅仅设置自增，但需注意:
(1)如果没有设置主键，不可添加自增
(2)只有当主键是数值时，才可以添加自增
alter table stu modify id int auto_increment;

4、如果想删除主键自增
– 删除主键自增时，要先删除自增
alter table stu modify id int;
– 再删除主键约束
alter table stu drop primary key;
desc stu; – 查看表结构

1.11.5添加或删除外键约束

添加外键方式一：建表时添加外键
现有部门表如下：
– 创建部门表
create table dept(
id int primary key auto_increment, – 部门编号
name varchar(20) – 部门名称
);
要求创建员工表，并在员工表中添加外键关联部门主键
– 创建员工表
create table emp(
id int primary key auto_increment, – 员工编号
name varchar(20), – 员工姓名
dept_id int, – 部门编号
foreign key(dept_id) references dept(id) – 指定dept_id为外键
);

添加外键方式二：建表后添加外键
现有部门表和员工表：
– 创建部门表
create table dept(
id int primary key auto_increment, – 部门编号
name varchar(20) – 部门名称
);

– 创建员工表
create table emp(
id int primary key auto_increment, – 员工编号
name varchar(20), – 员工姓名
dept_id int – 部门编号
);
如果表已存在，可以使用下面这种方式：
alter table emp add constraint fk_dept_id foreign key(dept_id) references dept(id);

其中 add constraint fk_dept_id 表示新增列，名为fk_dept_id (名字由自己定义)，是指外键约束名称。将来可以通过该名称删除外键。
foreign key(dept_id)中的dept_id为外键

1.11.6删除外键约束

1、首先通过 “show create table 表名”语法，查询含有外键表的建表语句，例如：
show create table emp;
显示结果如下：

其中，emp_ibfk_1是在创建表时，数据库为外键约束指定的一个名字，删除这个名字即可删除外键关系，例如：
alter table emp drop foreign key emp_ibfk_1;

外键删除成功！

1.11.7where中不能使用列别名

SQL语句的书写顺序:
select * | 列名 – 确定要查询的列有哪些
from 表名 – 确定查询哪张表
where 条件 – 通过筛选过滤，剔除不符合条件的记录
group by 分组的列 – 指定根据哪一列进行分组
having 条件 – 通过条件对分组后的数据进行筛选过滤
order by 排序的列 – 指定根据哪一列进行排序
limit (countPage-1)*rowCount, rowCount – 指定返回第几页记录以及每页显示多少条

SQL语句的执行顺序:
from 表名 – 确定查询哪张表
where 条件 – 通过筛选过滤，剔除不符合条件的记录
select * | 列名 列别名 – 确定要查询的列有哪些，
group by 分组的列 – 指定根据哪一列进行分组
having 条件 – 通过条件对分组后的数据进行筛选过滤
order by 排序的列 – 指定根据哪一列进行排序
limit (countPage-1)*rowCount, rowCount

** 关于where中不能使用列别名但是可以使用表别名?
是因为，表别名是声明在from中，from先于where执行，先声明再使用没有问题，但是列别名是声明在select中，where先于select执行，如果先使用列别名，再声明，这样执行会报错!!