mysql基本操作

LiP
216 阅读9分钟

之前学习mysql的时候都是在平板上做的笔记，查阅的时候不太方便，所以现在来回顾一下顺便把笔记记在这里。

数据库的启动

  • 法一：windows+R输入services.msc后找到mysql80启动；
  • 法二：命令行内输入net start mysql80启动，net stop mysql80停止。
  • 关系型数据库（RDBMS） 概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

图形化操作界面配置(datagrip)

安装完成且新建一个新工程后，点击左上角的加号添加源 image.png

选择mysql，输入user和password测试连接即可 image.png

测试连接成功 image.png

SQL通用语法

  • 单行或多行书写，分号结尾；
  • 不区分大小写
  • 注释
    • 单行注释：--或#
    • 多行注释：/**/

DDL（数据定义语言）

数据库操作

查询
  • 查询所有数据库SHOW DATABASES;
  • 查询当前数据库SELECT DATABASE();
创建
  • CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]
删除
  • DROP DATABASE [IF EXISTS] 数据库名
使用
  • USE 数据库名

表操作

查询
  • 查询当前数据库所有表SHOW TABLES;
  • 查询表结构DESC 表名
  • 查询指定表的建表语句SHOW CREATE TABLE 表名;
创建
CREATE TABLE 表名(
    字段 字段类型 [COMMENT 注释],
    ...,
    字段 字段类型 [COMMENT 注释]
) [COMMENT 表注释];
修改
  • 添加字段ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
  • 修改数据类型ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
  • 修改字段名和字段类型ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
  • 删除字段ALTER TABLE 表名 DROP 字段名;
  • 修改表名ALTER TABLE 表名 RENAME TO 新表名;
删除
  • 删除表DROP TABLE [IF EXISTS] 表名;
  • 清空表TRUNCATE TABLE 表名;

数据类型

  • 数值类型 | 类型 | 大小(byte) | | --- | --- | | TINYINT | 1 | |SAMLLINT|2| |MEDIUMINT|3| |INT或INTEGER|4| |BIGINT|8| |FLOAT|4| |DOUBLE|8(最长长度,小数位数)| |DECIMAL|(精度,标度)| 如果要使用无符号则在后面加上UNSIGNED即可。

  • 字符串类型 | 类型 | 大小(byte) | 描述 | | --- | --- | --- | | CHAR | 0-255 | 定长字符串 | | VARCHAR|0-65535|变长字符串，后面跟着数字代表最多存储的长度| |TINYBLOB|0-255|不超过255个字符的二进制数据| |TINETEXT|0-255|短文本字符串| |BLOB|0-65,535|二进制形式长文本数据| |TEXT|0-65,535|长文本数据| |MEDIUMBLOB|0-16,777,215|二进制形式中长文本数据| |MEDIUMTEXT|0-16,777,215|中长文本数据| |LONGLOB|0-4,294,967,295|二进制形式极大文本数据| |LONGTEXT|0-4,294,967,295|极大文本数据|

  • 日期类型 | 类型 | 大小(byte) | 格式 | 描述 | | --- | --- | --- | --- | | DATE | 3 | YYYY-MM-DD |日期值| |TIME|3|HH:MM:SS|时间值或持续时间| |YEAR|1|YYYY|年份| |DATETIME|8|YYYY-MM-DD HH:MM:SS|混合日期和时间值| |TIMESTAMP|4|YYYY-MM-DD HH:MM:SS|混合日期和时间值，时间戳|

DML（数据操作语言）

添加数据

  • 给指定字段添加数据INSERT INTO 表名(字段1,字段2...) VALUES(值1,值2...);
  • 给全部字段添加数据(需要按顺序)INSERT INTO 表名 VALUES(...);
  • 批量添加数据，两种方式都同上，但在VALUES后要由多个括号，之间逗号隔开。
  • 注意：字符串和日期型数据应该包含在单引号中。

修改数据

UPDATE 表名 SET 字段1 = 值1, 字段名2 = 值2... [WHERE 条件];

删除数据

DELETE FROM 表名 [WHERE 条件];

DQL（数据查询语言）

  • SELECT字段列表
  • FROM表名列表
  • WHERE条件列表
  • GROUP BY分组字段列表
  • HAVING分组后条件
  • ORDER BY排序字段列表
  • LIMIT分页参数
  • 聚合函数count,max,min,avg,sum
  • 执行顺序：FROM > WHERE > GROUP BY > 聚合函数 > HAVING > SELECT > ORDER BY > LIMIT

基本查询

SELECT 字段1 [AS 别名1],字段2... FROM 表名;

  • 去除重复记录：在SELECT加上DISTINCT

条件查询

比较运算符功能
>大于
>=大于等于
<小于
<=小于等于
=等于
<>或!=不等于
BETWEEN...AND...在某范围稚嫩，含最大最小
IN(...)在in之后的列表中的值，多选一
LIKE 占位符模糊匹配(_匹配单个字符，%匹配任意个字符)
IS NULL为空
AND或&&并且
OR或||或
NOT或!非

聚合函数

  • 将一列数据作为一个整体进行纵向计算
  • 常见聚合函数
函数功能
count统计数量
max最大值
min最小值
avg平均值
sum求和
  • 语法SELECT 聚合函数(字段列表) FROM 表名;
  • 注意：NULL不参与所有聚合函数运算

分组查询

  • 语法 SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY [HAXING 分组后过滤条件];
  • WHERE和HAVING区别：
    • 执行时机不同：where是在分组之前过滤，不满足where条件的不参与分组；而having是在分组之后对结构进行过滤
    • 判断条件不同：where不能对聚合函数进行判断，而having可以
  • 注意：
    • 执行顺序：where>聚合函数>having
    • 分组之后，查询字段一般为聚合函数和分组字段，查询其他字段无意义

排序查询

  • 语法： SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2...;
  • DESC降序，ASC升序。

分页查询

  • 语法：SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

多表查询

  • 分类：
  • 连接查询：
    • 内连接：查询A,B表交集部分数据
    • 左外连接：左表所有+左右交集
    • 右外连接：右表所有+左右交集
    • 子链接：当前表与自身连接查询，必须使用表别名
  • 子查询
内连接
//隐式
SELECT 字段列表 FROM 表1,表2 WHERE 条件;

//显式
SELECT 字段列表 FROM 表1 JOIN 表2 ON 连接条件;
外连接
//左外连接
SELECT 字段列表 FROM 表1 LEFT JOIN 表2 ON 条件;

//右外连接
SELECT 字段列表 FROM 表1 RIGHT JOIN 表2 ON 条件;
自连接
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件;
联合查询

把多次查询的结果合并起来形成一个新的查询结果集，会对合并之后的数据去重。

SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;

联合查询的多张表列数保持一致，字段类型也需要保持一致

子查询
  • SQL语句中嵌套SELECT语句，称为嵌套查询或子查询 SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
  • 子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个。
  • 根据子查询结果不同，分为：
    • 标量子查询（子查询结果为单个值）
    • 列子查询（结果为一列）
    • 行子查询（结果为一行）
    • 表子查询（结果为多行多列）
  • 根据子查询位置你分为：WHERE之后，FROM之后，SELECT之后
  • 列子查询
操作符描述
IN在指定的集合内，多选一
NOT IN不在指定的集合内
ANY子查询返回列表中，有任意一个满足即可
SOME与ANY相同，用SOME的地方都可以用ANY
ALL子查询返回列表的所有值都必须满足
  • 行子查询 常用操作符：=，<>，IN，NOT IN
  • 表子查询
  • 常用操作符IN

DCL（数据控制语言）

主机名用localhost表示只能在本机访问，若想在任意主机访问该数据库可用%

用户

  • 查询用户
USE mysql;
SELECT * FROM user;
  • 创建用户CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
  • 修改用户密码ALTER USER '用户名'@'主机名' IDENTIFIED TITH mysql_native_password BY '新密码';
  • 删除用户DROP USER '用户名'@'主机名';

权限控制

权限说明
ALL,ALL PRIVILEGES所有权限
SELECT查询数据
INSERT插入数据
UPDATE修改数据
DELETE删除数据
ALTER修改表
DROP删除数据库/表/视图
CREATE创建数据库/表
  • 查询权限SHOW GRANTS FOR '用户名'@'主机名';
  • 授予权限GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
  • 撤销权限REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

函数

字符串函数

函数功能
CONCAT(s1,s2,...sn)字符串拼接
LOWER(str)全转为小写
UPPER(str)全转为大写
LPAD(str,n,pad)用字符串pad对str进行左填充，达到n个字符串长度
RPAD(str,n,pad)用字符串pad对str进行右填充，达到n个字符串长度
TRIM(str)去掉字符串首尾空格
SUBSTRING(str,start,len)返回从start位置其len个长度的字符串

数值函数

函数功能
CEIL(x)向上取整
FLOOR(x)向下取整
MOD(x,y)返回x/y的模
RAND()返回0~1内的随机数
ROUND(x,y)返回x四舍五入值，保留y为小数

日期函数

函数功能
CURDATE()当前日期
CURTIME()当前时间
NOW()当前日期和时间
YEAR(date)获取年份
MONTH(date)获取月份
DAY(date)货期日期
DATE_ADD(date,INTERVAL num type)返回一个日期/时间加上时间间隔后的时间值
DATEDIFF(date1,date2)返回两日期之间天数(date1-date2)

流程函数

常用于在SQL语句中试下条件筛选，从而提高语句效率

函数功能
IF(VALUE,t,f)如果value为true，返回t，否则返回r
IFNULL(value1,value2)如果value1不为空则返回1，否则返回2
CASE WHEN [val1] THEN [res1] ... ELSE [default] END如果val1为true则返回res1,...否则返回default
CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END如果expr等于val1则返回res1,...否则返回default
注意：空字符串不是NULL，且同一语句中when可以有多个

eg:

SELECT name,
    (CASE workaddress WHEN '北京' THEN '一线城市' WHEN '上海' THEN '一线城市' ELSE '其他城市' END) AS '工作地址' 
    FROM emp;

约束

约束描述关键字
非空不能为NULLNOT NULL
唯一数据唯一UNIQUE
主键唯一标识，非空且唯一且自增PRIMARY KEY, AUTO_INCREMENT
默认指定默认值DEFAULT
检查保证字段值满足某一条件CHECK
外键两张表建立连接FOREIGN KEY

外键约束：

  • 添加
//在创建表时最后一个字段列出后：
CONSTRAINT [外键名称] FOREIGEN KEY(外键字段名) REFERENCES 主表(主表列名)

//已存在表进行修改
ALYER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGEN KEY(外键字段名) REFERENCES 主表(主表列名)；

image.png

  • 删除ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

事务

由于项目有rpc的需求，所以暂时要鸽掉数据库这部分的学习，先去学rpc了，以后有空再继续更新..(2023/3/31)

avatar
文章
阅读
粉丝