mysql的基础和进阶命令，建议学习的时候跟着敲一遍哦

Mysql数据库

1.1数据库概述

1.什么是数据库

​ 数据库：英文为Database，简称DB

​ 数据库是按照数据结构来组织、存储和管理数据的仓库，简而言之，数据库就是存储数据的仓 库。

​ 数据库是一个专业存储和管理数据的软件系统，相比传统人工记录数据和直接使用文件保存数 据， 数据库具有更安全、更可靠、效率更高的优势。

​ 早期：层次式数据库，网络型数据库(已过时)

​ 现在：关系型数据库，非关系型数据库

2.什么是关系型数据库

​ 底层以二维表的形式保存数据的数据库，就是关系型数据库。如下：

​ 常见的关系型数据库有：

​ SQL Server：微软提供，适用于中型、大型项目，收费，在java中的使用占比不高，在.NET语 言中使用较多。

​ Oracle：甲骨文公司提供，适用于大型、超大型项目，功能强大，性能优异，收费，在Java中 使 用占比很高。

​ mysql：瑞典MySQLAB公司提供，适用于小型、中型项目，免费开源，小巧轻量，性能也不 差。在Java中使用占比较高。

​ DB2：IBM公司提供，适用于大型项目，收费，在Java中使用占比不高。

​ Sqlite：迷你，嵌入式设备，智能家居，手机，ipad等(Django2.0开始以sqlite3为默认数据库)

​ 什么是非关系型数据库

​ 非关系数据库的底层结构是以key-value，列等结构存储数据的常见非关系型数据库有：

​ redis,mongodb

3.数据库的相关概念

​ 1什么是数据库服务器

​ 服务器软件，mysql软件，将服务器软件装在电脑上，就就可以作为一台服务器对外提供服 务器（存取数据）

​ 2什么是数据库

​ 在每一个数据库的服务器中，可以有很多个仓库（数据库），通常情况下，一个网站的所有 数据会存放在一个数据库中。

​ 3.什么是表

​ 一个数据库中可以创建多张表，而一张表用于存放一类信息（Java中的类对应数据库中的 表）

​ 4什么是表记录

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NDdIi5bb-1577761642736)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20191215184000534.png)]

4.什么是SQL语言

​ SQL：（Structured Query Language）结构化查询语言

​ SQL是一种ANSI（美国国家标准化组织）的标准计算机语言，用于访问和操作数据库。

​ SQL是用于操作关系型数据库的通用语言，即通过SQL语言可以操作所有的关系型数据库。

​ SQL语言可以用于操作数据库（创建，删除，查看），操作数据表（创建，删除，查看，修 改），操作表记录（新增，更新，删除，查询）等。

1.2连接mysql服务器

​ 通过命令行工具可以登录Mysql客户端，连接Mysql服务器，从而访问服务器中的数据

1.连接mysql服务器：

​ mysql -uroot -p

​ -u:后面的root是用户名，这里使用的是超级管理员root

​ -p:后面可以接密码

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.拓展内容

​ 1.在cmd中连接mysql服务器之后，可以使用#，/**/，–等符号添加注释

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

1.3数据库及表操作

1创建，删除，查看数据库

​ 提示：1.SQL语句对大小写不敏感。推荐关键字使用大写，自定义名称使用小写

​ 2.并且在自定义名称时，针对多个单词不要使用驼峰命名，而是使用下划线连接

​ 1.查看mysql服务器中所有的数据库

​ 语法：show databases；

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

​ 语法：USE 库名；

​ 查看已进入的库

​ SELECT DATABASE();

​ 3.查看当前数据库中所有的表

​ select table_name from information_schema.tables where table_schema=当前数据库;

​ 4删除mydb1库

​ 语法：DROP DATABASE 库名 ;

​ 5.重新 创建mydb1库，指定编码为utf-8

​ 语法：CREATE DATABASE 库名 CHARSET 编码；!!!utf-8在mysql中写出utf8

​ 6.查看建库时的语句

​ 语法：SHOW CREATE DATABASE 库名；

2创建，删除，查看表

​ 7.进入mydb1库，删除学生表（如果存在）

​ 语法：DROP TABLE 表名；

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

​ CREATE TABLE 表名(

​ 列名 数据类型，

​ 列名 数据类型，

​ …..

​ ) ;

​ 9.查看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 -p

​ 11.查询stu表所有的学生信息

​ 语法：SELECT * FROM 表名

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

​ 语法：UPDATE 表名 SET 列=值，列=值，列=值，…[WHERE子句]

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

​ UPDATE stu SET score=83 WHERE num=1;

​ 14.删除stu表中所有的记录

​ 语法：DELETE FROM 表名 [where条件]

1.5查询表记录

1.基础查询

​ SELET语句用于从表中选取数据。结果被存储

​ 语法：SELECT 列名称 FROM 表名

​ 提示：

​ 1.*为通配符，表示查询所有

​ 2.但使用*有时会把不必要的列也查出来了，并且效率不如直接指定列名

2.where子句查询

​ WHERE子句查询语法：SELECT 列名称 FROM 表名称 WHERE 列 运算符 值

​ 下面运算符可以在WHERE子句中使用

​ [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5Dt1MpoW-1577761642737)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20191216134314517.png)]

​ 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的员工，显示员工的姓名，薪资，奖金。

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

3.模糊查询

​ LIKE操作符用于在WHERE子句中搜索列中的指定模式。

​ 可以和通配符（%、_）其中"%“表示0或多个任意的字符。下划线表示一个任意字符

​ 语法:SELECT 列 | * FROM 表名 WHERE 列名 LIKE 值

​ 小%表示以小开头，而后面可以是任意字符，同样，%小，表示以”小”结尾，而%小%则表示包含“小”这个字符 (并一同包含”%小"和"小%“这两种情况)

​ 25.查询emp表中姓名包含“涛”字的员工，显示员工姓名

​ 26.查询emp表中姓名以“刘”字开头的员工，显示员工姓名

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

4.多行函数查询‘

​ 多行函数也叫做聚合（聚集）函数，根据某一列或所有列进行统计。

​ 常见的多行函数有：

​ COUNT(列名)：统计结果集中某一列或记录行的行数’ 语法：SELECT COUNT(列名) FROM 表名

​ MAX(列)：统计结果集中某一列值中的最大值

​ MIN(列)：统计结果集中某一列值中的最小值

​ SUM(列)：统计结果集中某一列所有值的和

​ AVG(列)：统计结果集中某一列值的平均值

​ 提示：多行函数不能用在where子句中

5.分组查询

​ GROUP BY 语句根据一个或多个列对结果集进行分组

​ 在分组的列上我们可以使用COUNT，SUM,AVG,MAX,MIN等函数

​ 语法：SELECT 列 | * FROM 表名 [WHERE子句] GROUP BY 列

6.排序查询

​ 使用ORDER BY 子句将结果根据指定的列排序后再返回

​ 语法：SELECT 列名 FROM 表名 ORDER BY 列名 ASC/DESC;

​ ASC(默认)升序，即从低到高；DESC降序，即从高到低

7.分页查询

​ 在mysql中，通过limit进行分页查询

​ limit(页码-1)*每页显示的记录数，每页显示记录数

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是分隔符

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中的long

2.字符串类型

​ 1.char(n) 定长字符串，最长255个字符。n表示字符数，例如：

​ –创建user表，指定用户名为char类型，字符长度不超过10

​ CREATE TABLE user(

​ username char(10),

​ …

​ );

​ 2.varchar(n)变长字符串最长不超过65535个字节，n表示字符数，一般超过255个字节，会使用text类型，例 如：

​ CREATE TABLE user(username varchar(10));

​ 所谓的不定长，是当插入的值长度小于指定的长度时，剩余的空间可以留给别的数据使用。

​ 3.大文本（长文本）类型

​ 最长65535个字节，一般超过255个字符列的会使用text

​ CREATE TABLE user(resume text);

​ text也分多种，其中bigtext存储数据的长度约为4GB

​ 拓展内容：（面试题）char(n)、varchar(n)、text都可以表示字符串类型，其区别在于：

​ (1)char(n)在保存数据时, 如果存入的字符串长度小于指定的长度n，后面会用空格补全，因此可能会造成空间 浪费，但是char类型的存储速度较varchar和text快。

​ 因此char类型适合存储长度固定的数据，这样就不会有空间浪费，存储效率比后两者还快！

​ (2)varchar(n)保存数据时, 按数据的真实长度存储, 剩余的空间可以留给别的数据用，因此varchar不会浪费空 间。因此varchar适合存储长度不固定的数据，这样不会有空间的浪费。

​ (3)text是大文本类型，一般文本长度超过255个字符，就会使用text类型存储。

​

3.日期类型

​ data:年月日

​ time:时分秒

​ datatime：年月日 时分秒

​ timestamp:时间戳（实际存储的是一个时间毫秒值），与datetime存储日期格式相同

​ 两者的区别是：

​ （1）timestamp最大表示2038年，而datatime范围是1000~9999

​ （2）timestamp在插入术，修改数据时，可以自动更新成系统当前时间

1.7mysql的字段约束

1.主键约束

​ 主键约束：如果一个列添加了主键约束，那么这个列就是主键，主键的特点是唯一且不能为空

​ 添加主键约束，例如将id设置为主键：

​ CREATE TABLE stu(id int primary key,…);

2.非空约束

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

​ 添加非空约束，例如为password添加非空约束：

​ CREATE TABLE user(password varchar(50) not null,…);

3.唯一约束

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

​ 添加唯一约束，例如为username添加唯一约束及非空约束：

​ CREATE TABLE user(username varchar(50) unique not null,…);

4.外键约束

​ 外键其实就是用于调整数据库两种表数据之间对应关系的这样的一个列

​ 这样数据库就会帮助我们维护两张表数据之间的关系

​ （1）创建表的同时添加外键

​ CREATE TABLE emp(id int,name varchar(50),dept_id int,FOREIGN key(dept_id) REFERENCES stu(id));

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

​ 语法：ALTER TABLE emp ADD CONSTRAINT fk_id FOREIGN KEY(stu_id) REFERENCES stu(id)

​ 上面的fk_id为外键的名称，自定义

1.8表关系

​ 常见的表关系分为以下三种：

​ 一对多（多对一），一对一，多对多

1.9多表查询

​ –准备数据：建立db30库

1.连接查询

2.左外连接查询

3.右外连接查询

4.子查询

5.多表查询

1.10数据库备份与恢复

1.备份数据库

​ 在cmd窗口中（未登录的状态下），可以通过如下命令对指定的数据库进行备份：

​ mysqldump -u 用户名 -p 数据库的名字 > 备份文件的位置

​ 例如：对db40库中的数据（表，表记录）进行备份，备份到d:/db40.sql文件中

​ mysqldump -uroot -p db40 > d:db40,sql

​ 输入密码，如果没有任何提示，即表示备份成功

2.回复数据库

​ 在cmd窗口中（未登录的状态下），可以通过如下命令对指定的数据库进行恢复：

​ mysql -u 用户名 -p 数据库的名字 < 备份文件的位置

​ 例如：将d:/db40.sql文件中的数据恢复到db60库中

​ –在cmd窗口中（已登录的状态下），先创建db60库，进入db60库：

​ CREATE DATABASE db60 CHARSET utf8;

​ use db60;

​ –在cmd窗口中（未登录的状态下）

​ mysql -uroot -p db60 < d:/db40.sql

​ 或例如：将d:/db40.sql文件中的数据恢复到db80库中：

​ CREATE DATABASE db80 CHARSET utf8;

​ use db80;

​ –再通过source执行指定位置下的sql文件：

sqldump -uroot -p db40 > d:db40,sql

​ 输入密码，如果没有任何提示，即表示备份成功

2.回复数据库

​ 在cmd窗口中（未登录的状态下），可以通过如下命令对指定的数据库进行恢复：

​ mysql -u 用户名 -p 数据库的名字 < 备份文件的位置

​ 例如：将d:/db40.sql文件中的数据恢复到db60库中

​ –在cmd窗口中（已登录的状态下），先创建db60库，进入db60库：

​ CREATE DATABASE db60 CHARSET utf8;

​ use db60;

​ –在cmd窗口中（未登录的状态下）

​ mysql -uroot -p db60 < d:/db40.sql

​ 或例如：将d:/db40.sql文件中的数据恢复到db80库中：

​ CREATE DATABASE db80 CHARSET utf8;

​ use db80;

​ –再通过source执行指定位置下的sql文件：

​ source d: /db40.sql