MySQL的基本操作指令

###MySQL

1. 不区分大小写，但建议关键字大写，表名列名小写
2. 每条命令最好用分号结尾，可以进行缩进或换行
3. 注释：单行： #注释文字 或-- 注释文字(–后要加一个空格)
   多行：/* … */

1.//启动/停止mysql服务
net start/stop mysql80

2.//h 需要连接的主机 //P 端口号 默认是3306 //u 用户名称
mysql -h localhost -P（大写）3306 -u root -p

3.//展开所有数据库
SHOW DATABASES

4.//打开一个指定库
use +库名

5.//展开库里的表 //use后，就在那个库中，可以在那个库里查看其他库的表
SHOW TABLES FROM + 库名

6.//查看当前所在的库
SELECT DATABASE()

7.//查询数据库版本
SELECT VERSION()
或退出客户端后输入：
mysql --version
mysql -V
8.创建表
create table 表名（
列名 列类型，
列名 列类型，
…
）

9.查看表结构
DESC 表名

10.查看表数据
SELECT * FROM + 表名

DQL(Query)和DML(Manipulation)语言的学习

基础查询

1.SELECT 基本语法
SELECT + 字段（列）FROM +表
查询出来的是一个虚拟表

2.执行顺序
例：
SELECT a’’ FROM B
先查看有没有B表，若有再去筛选字段a

3查询列表可以是字段，表达式，常量，函数等

3.1查询常量
SELECT 100;

3.2查询表达式
SELECT 100%3；

3.3查询单个字段
SELECT ‘a’ FROM B;

3.4查询多个字段，中间用逗号隔开
SELECT ‘a’，‘b’，‘c’ FROM B；

3.5查询所有字段
SELECT * FROM A

3.6 查询函数

SELECT DATABASES（）； 查看当前数据库
SELECT VERSION() 查看当前数据库版本
SELECT USER（） 查看当前用户
LENGTH（str） 字符串长度函数

常见函数

字符函数

1.CONTACT(‘s1’,‘s2’) 拼接字符串

2.LENGTH（str） 获取字节长度，一个汉字三个字节，utf8

3.CHAR_LENGTH（str）获取字符长度

4.SUBSTRING (str,起始索引，截取的字符长度) 截取子串， mysql中起始是从1开始
SUBSTRING（str，起始索引） 直接将起始索引后面的都截取出来
SELECT SUBSTRING(‘三打白骨精’，3)；

5.INSTR（str，substr），获取substr第一次出现的索引
SELECT（“三打白骨精aa白骨精bb白骨精”，“白骨精”）

6.TRIM 去前后指定的字符，默认去空格
SELECT TRIM(’ 空格空格虚竹空格’)；
SELECT TRIM(‘x’ FROM ‘xxxxxx虚xxx竹xxxx’)；

7.LPAD/RPAD 左右填充
SELECT LPAD(‘虚竹’，10，‘a’) 左边填充10 个字符a

8.UPPER/LOWER 变大写小写
UPPER(str)

9.STRCMP 比较两个字符的大小
SELECT STRCMP(‘str1’,‘str2’)； 返回1前面大，-1后面大，0同样大

10.LEFT/RIGHT 截取子串
SELECT LEFT(‘鸠摩智’，2) 从左边截取2个字符

11.REPLACE（str, a, b）用字符串b替换字符串str中所有出现的字符串a

数学函数

1.ABS 绝对值
SELECT(-2.3);

2.CEIL /FLOOR 向上取整/向下取整 >=该数的最小整数、<=该数的最大整数
SELECT CEIL(1.1) 输出2
SELECT FLOOR(-1.01) 输出-2

3.ROUND 四舍五入
SELECT ROUND(1.5) 输出2
SELECT ROUND(1.2138,2) 保留两位小数，对后面四舍五入判断

4.TRUNCATE 截断
SELECT TRUNCATE(1.2387239,2) 直接将小数点两位后扔掉

5.MOD 取余
SELECT MOD(-10,3) -10对3取余，结果正负与被除数一致

6.RAND(x) 返回0~1的随机值

7.SQRT(x) 返回x的平方根

8.POW(x,y) 返回x的y次方

日期函数

1.NOW 获取当前日期时间
SELECT NOW();

2.CURDATE 获取日期
SELECT CURDATE();

3.CURTIME 获取当前时间
SELECT CURTIME();

4.DATEDIFF 日期之差
SELECT DATEDIFF(日期1，日期2) 两日期之间的天数，前-后

5.DATE_FORMAT
SELECT DATE_FORMAT(‘1997-10-15’,’%Y年%m月%d日 %H时%i分%s秒’)；

6.STR_TO_DATE 按指定格式解析字符串为日期类型
SELECT STR_TO_DATE(3/15 1998,"%m/%d %Y");

格式符 说明 格式符 说明
%Y 4位数字表示年份 %y 表示两位数字表示年份
%M 月名表示月份（January,…） %m 两位数字表示月份（01,02,03。。。）
%b 缩写的月名（Jan.，Feb.，…） %c 数字表示月份（1,2,3,…）
%D 英文后缀表示月中的天数（1st,2nd,3rd,…） %d 两位数字表示月中的天数(01,02…)
%e 数字形式表示月中的天数（1,2,3,4,5…）
%H 两位数字表示小数，24小时制（01,02…） %h和%I 两位数字表示小时，12小时制（01,02…）
%k 数字形式的小时，24小时制(1,2,3) %l 数字形式表示小时，12小时制（1,2,3,4…）
%i 两位数字表示分钟（00,01,02） %S和%s 两位数字表示秒(00,01,02…)
%W 一周中的星期名称（Sunday…） %a 一周中的星期缩写（Sun.，Mon.,Tues.，…）
%w 以数字表示周中的天数(0=Sunday,1=Monday…)
%j 以3位数字表示年中的天数(001,002…) %U 以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天
%u 以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天
%T 24小时制 %r 12小时制
%p AM或PM %% 表示%

流程控制函数

1.IF函数
SELECT(100>9,‘真’,‘假’) 类似于？：三目运算符

2.CASE函数
例1：类似于switc语句
SELECT 部门ID，奖金，
CASE 部门ID
WHEN 30 THEN 奖金2
WHEN 60 THEN 奖金3
WHEN 90 THEN 奖金*4
ELSE 奖金
END
FROM 员工表；
例2：类似多重if else
SELECT 奖金，
CASE
WHEN 奖金>10000 THEN “A”
WHEN 奖金>20000 THEN “B”
WHEN 奖金>30000 THEN “C”
ELSE “D”
END
FROM 员工表；

分组函数

说明：分组函数往往用于实现将一组数据进行统计计算，弄得一个最终值，又称为聚合函数或统计函数
1.SUM（字段名）：求和

2.AVG（字段名）：求平均数

3.MAX （字段名）：求最大值

4.MIN（字段名）：求最小值

5.COUNT（字段名）：计算非空字段数值的个数

SELECT COUNT(*) FROM 表名 经常用于统计行数；

搭配DINTINCT实现去重的统计

分组查询

SELECT 查询列表
FROM 表名
WHERE 分组前筛选条件
GROUP BY 分组列表(可以有多个)
HAVING 分组后筛选
ORDER BY 排序列表
执行顺序
FROM->WHERE->GROUP BY->HAVING->SELECT->ORDER BY

例：
SELECT SUM(奖金)，部门ID
FROM 员工表
GROUP BY 部门ID；

在员工表中通过部门ID分组，求和奖金

分组后查询HAVING和分组前查询WHERE

在GROUP BY 前面的WHERE条件叫分组前查询
认识的表只是FROM 的一开始的表

在GROUP BY 后面的WHERE条件叫分组后查询
关键字HAVING,认识的表是分组后的表
例：查询哪个部门的员工个数>5
SELECT COUNT(*) 员工个数， 部门ID
FROM 员工表
GROUP BY 部门ID
HAVING COUNT(*)>5
不能在GROUP BY前面加WHERE COUNT(*) ,有顺序问题

3.7起别名
1）使用AS关键字
SELECT USER（） AS + 用户名
SELECT USER（） AS + “用户名”
SELECT USER（） AS + ‘用户名’
2）使用空格
将上面的AS去掉换成空格

3.8拼接姓和名，最终起别名为姓名 使用CONCAT函数
SELECT CONCAT（first_name,lASt_name） AS “姓 名” FROM 表名
mysql中的+的作用
1）两个操作数都是数值型
10+1.3

2）其中一个操作数为字符型，将字符型强制转换成数值型，如果无法转换，则当做0处理
'hekk’ + 100 为100

3）其中一个操作数为NULL
只要有NULL 就为NULL
如果有NULL的显示其他的，不为NULL的就是啥就显示啥

ifNULL(1,2) 第一个是可能为NULL的参数，2是如果是NULL想要显示的数；

3.9 DISTINCT的使用
例：查询员工涉及的不么编号：编号有重复，去重
SELECT DISTINCT department_id FROM 表名

3.10查看表的结构
1）DESC 表名
2） SHOW COLUMNS FROM 表名

条件查询

语法：SELECT 查询列表 FROM 表名 WHERE 筛选条件
执行顺序
1）FROM
2）WHERE 表达式 只有true和false
3）SELECT
筛选条件：

1）按条件表达式进行筛选
< ,>,>=,<=,= ,(<>或=），可以使用！= 但不建议
2）按逻辑表达式进行筛选
逻辑运算符 AND or NOT，也可以用&&，|| ，！ 但不建议
3）模糊查询

like 和 NOT like

一般和通配符搭配使用，对字符型数据进行匹配查询
常见的通配符:
_ 任意单个字符
% 任意多个字符
例：查询姓名中包含最后一个字符为e的员工信息
SELECT *
FROM 表名
WHERE 名字 like ‘%e’
查询姓名中包含第三个字符为e的员工信息
SELECT *
FROM 表名
WHERE 名字 like ‘__e%’
like __$_’ escape ‘’ 双引号中在加那个字符代表 mysql中的转义字符

IN NOT IN

a IN (常量1，常量2，常量3)
查询某字段的值是否属于指定的列表之内
例：查询部门编号是30、50、90、的员工号，部门编号
SELECT 员工号，部门编号
FROM 员工表
WHERE 部门编号 IN（30,50,90）

BETWEEN AND 或 NOT BETWEEN AND

判断某个字段的值是否介于xxx之间

查询部门编号是30到90的员工号，部门编号
SELECT 员工号，部门编号
FROM 员工表
WHERE 部门编号 BETWEEN 30 AND 90

IS NULL 和 IS NOT NULL

例：查询没有奖金率的员工信息
SELECT *
FROM 员工表
WHERE 奖金 IS NULL

=只能判断普通的内容
IS NULL 只能判断NULL

<=> 既能判断普通的也可以判断NULL

排序查询

SELECT
FROM
WHERE
ORDER BY
默认升序 ASC
降序 DESC

执行顺序
FROM
WHERE
SELECT
ORDER BY

按多个字段进行排序
例：查询员工的姓名，工资，部门编号，先按工资升序，再按部门编号降序
SELECT 员工姓名，工资，部门编号
FROM 员工表
ORDER BY 工资 ASC，部门编号 DESC

按列数查询

SELECT * FROM 表名
ORDER BY 2；

SQL99语法

内连接

SELECT 查询列表
FROM 表名1 别名1
INNER JOIN 表名2 别名
ON 连接条件
WHERE 筛选条件
FROUP BY 分组列表
HAVING 分组后筛选
ORDER BY 排序列表

1等值连接

语法：

特点：
1.为了解决多表中的字段名的重名问题，往往为表起别名，提高语义性
2.表的顺序无要求
例：
查询员工名和部门名
SELECT e.name,d.department_name
FROM employees e,departments d
WHERE e.‘name’ = d.‘department_name’;

2非等值连接

3自连接

外连接

说明：查询结果为主表中所有的记录，如果从表中有匹配项，则显示匹配项；
若没有匹配项，则显示NULL
应用场景：一般用于查询主表中有但从表中没有的记录
特点：
1.分主从表两表顺序不能随便调换
左连接的话，左边为主表，右连接同理
语法：
SELECT 查询列表
FROM 表1 别名1
LEFT/RIGHT/FULL OUTER JOIN 表2 别名2 (FULL全外连接，两张表内容全部显示出来，没有匹配的显示为NULL)
ON 连接条件
WHERE 筛选条件；

子查询

说明：当一个查询语句中又嵌套了另一个完整的SELECT语句，则嵌套的SELECT语句称为子查询或内查询，外面的SELECT 语句称为主查询或外查询
分类：按子查询出现的位置进行分类
1 SELECT 后面：
要求：子查询的结果为单行单列（标量子查询）
2.FROM 后面
要求：子查询的结果可以多行多列
3.WHERE或HAVING 后面
要求：子查询到结果必须为单列
单行子查询
多行子查询
4.EXISTS 后面

SELECT EXISTS(
SELECT *
FROM 表名
WHERE NAME = ‘张三丰’
） 有无张三丰；
0为无，1为有

要求：子查询结果必须为单列（相关子查询）
特点：
1）子查询放在条件中，要求必须放在条件的右侧
2）子查询一般放在小括号中
3）子查询的执行优先于主查询
4）单行子查询对应了 单行操作符：> ,<,>=,<=,=,<>
多行子查询对应了 多行操作符：any ， in ，all in

分页查询

应用场景：当页面上的数据，一页显示不全，则需要分页显示，分页查询的sql命令请求数据库服务器->服务器响应查询到的多条数据->前台页面

语法：
SELECT 查询列表
FROM 表名
WHERE 筛选条件
GROUP BY 分组
HAVING 分组后筛选
ORDER BY 排序列表
LIMIT 起始条目索引，显示的条目数 ---- LIMIT 执行放在最后

特点：
起始条目索引从0开始，如果不写，默认从0开始显示

例：
SELECT *
FROM 表名
LIMIT 0,5;
显示表的前5名

公式：
假如要显示的页数是PAGE,每页显示的条目数为size
SELECT *
FROM 表名
LIMIT (PAGE-1)*size，size；

联合查询

说明：当查询结果来自于多张表，但多张表之间没有关联，这个时候往往使用联合查询，也称为UNION查询，UNION自动去重，若想不去重，则使用UNION ALL关键字

语法：
SELECT 查询列表
FROM 表名1
WHERE 筛选条件
UNION
SELECT 查询列表
FROM 表名2
WHERE 筛选条件；

DDL语言

说明：Data Define Language 数据定义语言，用于对数据库和表的管理和操作

库的管理

一.创建数据库

CREATE DATABASE 数据库名；

CREATE DATABASE IF NOT EXISTS 数据库名；
如果不存在数据库就创建

二.删除数据库

DROP DATABASE 数据库名

DROP DATABASE IF EXISTS 数据库名；
如果存在数据库名就删除

表的管理

一.创建表

语法：
CREATE TABLE 表名（
字段名1 字段类型，【字段约束】，
字段名2 字段类型，【字段约束】，
字段名3 字段类型，【字段约束】，
）；
例：
CREATE TABLE stuinfo（
stuid INT,
stuname VARCHAR(20),
stusex CHAR,
borndate DATETIME
）；

常见约束

说明： 用于限制表中字段数据的，从而进一步保证数据是一致的、准确的、可靠的！
NOT NULL:非空， 用于限制该字段为必填项

DEFAULT:默认，用于限制该字段没有显示插入值，则直接显示默认值

PRIMARY KEY:主键， 用于限制该字段值不能重复，且设置为主键的字段默认不能为空

UNIQUE:唯一，用于限制该字段的值不能重复，可以为空

CHECK:检查， 用于限制该字段的值必须满足指定的条件
语法：
CHECK (字段名 条件)

FOREING KEY;外键， 用于限制两个表的关系，要求外键列的值必须来自主表的关联列
要求：
1）主表的关联列和从表的关联列的类型必须一致，意思一样，名称无要求
2）主表的关联列要求必须是主键
在最后添加
CONSTRAINT 新名 FOREING KEY (从表的字段名) REFERENCES 主表表名（字段名）

常见的数据类型

int：整型
double/float：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；
decimal：浮点型，在表示钱方面使用该类型，因为不会出现精度缺失问题；
char：固定长度字符串类型；char(4) 范围是0-255
varchar：可变长度字符串类型；
text：字符串类型；表示存储较长文本
FLOAT/DOUBLE/DECIMAL(m，n)，m总共几位数，小数点后几位数

blob：字节类型；//jpg mp3 avi 图片数据
date：日期类型，格式为：yyyy-MM-dd；
time：时间类型，格式为：hh:mm:ss
timestamp/datetime：时间戳类型；日期+时间 yyyyMMdd hhmmss

保存范围 所占字节
Datetime 1900-1-1~xxxx年 8
Timestamp 1970-1-1~2038-12-31 4


二.修改表

语法：ALTER TABLE 表名 ADD | MODIFY | CHANGE | DROP COLUMN 字段名 字段类型 【字段约束】

修改表名

ALTER TABLE 表名 RENAME TO 新表名；

添加字段

ALTER TABLE 表名 ADD COLUMN 字段名 类型 （约束）；

修改字段名

ALTER TABLE 表名 CAHNGE COLUMN 旧字段名 新字段名 类型 （约束）；

修改字段类型

ALTER TABLE 表名 MODIFY COLUMN 字段名 新类型；

删除字段

ALTER TABLE 表名 DROP COLUMN 字段名；

三.删除表

DROP TABLE IF EXISTS 表名；

四.复制

复制表结构

CREATE TABLE 新表名 LIKE 旧表名；

复制表结构加内容

CREATE TABLE 新表名 SELECT * FROM 表名

只复制部分表结构，不包含数据
CREATE TABLE 新表名
SELECT 需要的字段名
FROM 需要复制的表
WHERE 1=2;

数据的插入

语法：
插入单行：
INSERT INTO 表名（字段1，字段2，…）VALUES (值1，值2，…)；
插入多行：
INSERT INTO 表名 （字段1，字段2，…）VALUES(值1，值2，…)，（值1，值2，…）;
特点：
1)字段和值列表一一对应，包含类型，约束等必须匹配
2）数值型的值，不用单引号，
非数值型的值，必须使用单引号
3）字段顺序无要求

可以为空的字段的插入：
1）字段名和值都省略
2）值写为NULL

默认字段的插入：
1)直接不写，自动默认
2）值写成DEFAULT

当省略所有字段名，则默认所有的字段，且值要按表中顺序进行填写

设置自增长列

直接在主键或唯一键后面 加AUTO_INCREMENT，
且键的类型要为数值型，赋值是用NULL赋值或不写
一个表至多有一个自增长列

数据的修改

UPDATE 表名
SET 字段 = 新值，字段 = 新值，…
WHERE 筛选条件；
执行顺序 UPDATE ->WHERE -> SET

修改多表记录
UPDATE 表1 别名
INNER |LEFT | RIGHT JOIN 表2 别名
ON 连接条件
SET 字段 = 新值，字段 = 新值，…
WHERE 筛选条件；

数据的删除

一.DELETE 语句
语法：
DELETE FROM 表名
WHERE 筛选条件

二.TRUNCATE 语句

语法：
TRUNCATE TABLE 表名
删除表中所有的数据。

虽然TRUNCATE和DELETE都可以删除表的所有记录，但有原理不同。DELETE的效率没有TRUNCATE高！
TRUNCATE其实属性DDL语句，因为它是先DROP TABLE，再CREATE TABLE。而且TRUNCATE删除的记录是无法回滚的，但DELETE删除的记录是可以回滚的.

DELETE 和TRUNCATE 的区别

1.DELETE 可以添加WHERE 条件
TRUNCATE 不能添加WHERE 条件，一次性清除所有的数据

2.TRUNCATE 的效率高

3.当删除带自增长列的表时
使用DELETE 删除后，重新插入数据后，记录从断点处开始
使用TRUNCATE删除后，重新插入数据，记录从1开始

4.DELETE 删除数据后，会返回受影响的行数
TRUNCATE 删除数据后，不会返回受影响的行数

5.DELETE 删除数据后，可以支持事务回滚
TRUNCATE 删除数据后，不支持事务回滚

事务的四大特性（ACID）

原子性（Atomicity）：事务中所有操作是不可再分割的原子单位。事务中所有操作要么全部执行成功，要么全部执行失败。
一致性（Consistency）：事务执行后，数据库状态与其它业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账号余额之和应该是不变的。
隔离性（Isolation）：隔离性是指在并发操作中，不同事务之间应该隔离开来，使每个并发中的事务不会相互干扰。
持久性（Durability）：一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证通过某种机制恢复数据。

事务：
概念： 由一条或多条sql语句组成，要么都成功，要么都失败
分类：
隐式事务：
没有明显的开启和结束标志，比如DML语句中的INSERT、UPDATE、DELETE语句本身就是一条事务

显式事务：
具有明显的开启和结束标志，一般由多条sql语句组成，必须具有明显的开启和结束标志

步骤：
开启事务前取消事务自动开启的标志
SHOW VARIABLES LIKE ‘%auto%’
SET autocommit = 0；
1.开启事务
START TRANSACTION;
2.编写事务需要的sql语句

3.结束事务

#提交
COMMIT
#回滚
ROLLBACK