MySQL的基本操作

最新推荐文章于 2023-07-25 16:54:29 发布

youshang520i

最新推荐文章于 2023-07-25 16:54:29 发布

阅读量1.5k

点赞数

分类专栏：数据库文章标签： Mysql的基本操作

本文链接：https://blog.csdn.net/qq_40694145/article/details/84033271

版权

数据库专栏收录该内容

12 篇文章 3 订阅

订阅专栏

Char(10)，定义长度为10，存入2个字符，占的空间还是10

Varchar(10)，定义长度为10，存入2个字符，占的空间是2个字节，比Char更的弹性。

但char在搜索上比varchar更有优势。

TEXT，当新闻内容和论文内容时使用。

ENUM和SET基本上没什么使用。

1.4 二进制类型

常用BLOB，二进制是存些图片和视频。

一般来说我们将图片和视频等放在项目下，将其路径存入数据库。

除非图片和视频是具有机密性的，不能随便暴露的。

2.数据库的基本操作

1.mysql的注释符

#  注释一行

/*
*  注释多行
*/

2.mysql简介（关系型数据库管理系统）

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。

数据库（Database）是按照数据库结构来组织、存储和管理数据的仓库。

一个项目至少有一个数据库。

3.显示所有的数据库

Show databases;

在DOM界面也可以使用show databases查询所有的数据库

单纯显示是否存在数据库：

Show databases like ‘dbname’;

4. 创建数据库

Createa database 数据库名;

判断数据库是否存在：

SELECT * FROM information_schema.SCHEMATA where SCHEMA_NAME='dbname';

判断数据库存在，则删除：

drop database if exists dbname;

5. 删除数据库

Drop database 数据库名；

6. 显示所有的数据库

Show databases;

在DOM界面也可以使用show databases查询所有的数据库

单纯显示是否存在数据库：

Show databases like ‘dbname’;

7. 创建数据库

Createa database 数据库名;
判断数据库是否存在：
SELECT * FROM information_schema.SCHEMATA where SCHEMA_NAME='dbname';
判断数据库存在，则删除：
drop database if exists dbname;

8. 删除数据库

Drop database 数据库名；

3.数据库表操作

3.1创建表

表是数据库存储数据的基本单位。一个表包含若干字段或记录。

CREATE TABLE 表名（字段名 数据类型 [完整性约束条件],

                  字段名 数据类型 [完整性约束条件],

                  ……

                  字段名 数据类型 [完整性约束条件]

）[engine = InnoDB  Default   Charset=utf8];
将表的时候可以选择添加或者不加[engine = InnoDB  Default   Charset=utf8];意思是设置mysql的引擎InnoDB 默认编号格式为utf8

完整性约束条件

1.直接写在字段名后面

Id int primary key auto_increment,

2.单独编写约束

uid int,

Constraint `fk` foreign key (`uid`) references `主表名`(`uid`)

自动增长（标识列）：

AUTO_INCREMENT

3.2 查看表结构

查看基本表结构：describe 表名; 或 desc 表名;

查看表详细结构：show create table 表名;

4.查询数据

4.1单表查询

1.查询所有字段
Select *  from 表名；

2.查询指定字段
Select 字段1，字段2，…… from 表名；

3.Where条件查询
Select 字段1，字段2，…… from 表名 where 条件表达式；

4.带IN关键字查询
Select 字段1，字段2，…… from 表名 where 字段 [not] in（值1，值2，……）；

5.带between and的范围查询
Select 字段1，字段2，…… from 表名 where 字段 [not] between 值1 and 值2；

4.2 带like的模糊查询--只有_、%，其他的全没了

Select 字段1，字段2，…… from 表名 where 字段 [not] like ‘字符串’;

_代表单个字符

%代表任意字符

4.3 空值查询

Select 字段1，字段2，…… from 表名 where 字段 is [not] null

案例：select * from tb_ta where tname is not null 其中tname 为空

4.4 带and的多条件查询

Select 字段1，字段2，…… from 表名 where 条件表达式1 and 条件表达式2[…… and 条件表达式n]

4.5 带or的多条件查询

Select 字段1，字段2，…… from 表名 where 条件表达式1 or 条件表达式2[ ……or 条件表达式n]

4.6 Distinct去重复查询

Select Distinct 字段1  from 表名

4.7 对查询结果排序

Select 字段1，字段2，…… from 表名 order by 字段名 [asc|desc]；

4.8 Group by 分组查询

Select 字段1，字段2，…… from 表名
Group by 字段名 [having 条件表达式][with rollup]

4.8.1 单独使用

4.8.2 与group_concat函数一起使用（将分组后的某列一起串起，比较常用）

4.8.3 与聚合函数一起使用

4.8.4 与having一起使用（限制输出的结果）

4.8.5 与with rollup一起使用（最后加入一个总和行）

4.8.6 Limit分页查询 Select 字段1，字段2，…… from 表名 limit 初始位置，记录数；

Select * from tb_a limit 5, 3;

4.2 使用聚合函数查询

4.2.1 COUNT()函数

统计记录的条数。常与GROUP BY一起使用，也可单独使用。

4.2.2 SUM()函数

求和。常与GROUP BY一起使用，也可单独使用。

4.2.3 AVG()函数

求平均值。常与GROUP BY一起使用，也可单独使用。

4.2.4 MAX()函数

求最大值。常与GROUP BY一起使用，也可单独使用。

4.2.5 MIN()函数

求最小值。常与GROUP BY一起使用，也可单独使用。

4.3 连接查询 -- 多表联查

连接查询是将两个或两个以上的表按照某个条件连接起来，从中选取需要的数据。

4.3.1. 内连接查询

内连接查询是一种最常见的连接查询。内连接查询可查询两个或两个以上的表共有的信息。

SELECT 字段列表 FROM 表名1,表名2 WHERE 表名1.字段名=表名2.字段名;

SELECT 字段列表 FROM 表名1 INNER JOIN 表名2 ON 表名1.字段名=表名2.字段名;

4.3.2. 外连接查询

SELECT 字段名列表 FROM 表名1 LEFT|RIGHT JOIN 表名2 ON 表名1.字段名=表名2.字段名;

4.3.2.1 左连接查询

可以查询出“表名1”的所有记录，而“表名2”中，只能查询出匹配的记录。

4.2.3.2 右连接查询

可以查询出“表名2”的所有记录，而“表名1”中，只能查询出匹配的记录。

4.4子查询

4.4.1 带比较运算符的子查询

子查询可以使用比较运算符。即子查询的结果做为父查询的条件，而该条件用比较运算符。

4.4.2 带IN关键字的子查询

子查询的结果返回多个时使用IN包含。

4.4.3 带EXISTS关键字的子查询

EXISTS表示是否存在，具有判断的意思。
子查询的结果返回一条或多条时，EXISTS子查询返回结果为TRUE；返之为FALSE。
[NOT EXISTS]
SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_booktype);
--如果t_booktype表存在，就查询t_book表。

4.4.4 带ANY关键字的子查询

表示满足其中任一条件。（找案例)
--查询价格便宜和贵的书（即满足其中一个就行）
SELECT * FROM t_book WHERE price >= ANY(SELECT price FROM t_pricelevel);
--SELECT price FROM t_pricelevel表示查询出所有的价格
-- WHERE price >= ANY() 表示满足所有价格中的任意一个都打印

4.5 合并查询结果

4.5.1 UNION

使用UNION关键字，数据库系统会将所有的查询结果合并到一起，然后去除掉相同的记录。
--合并两张表，相同的数据去除掉
SELECT id FROM t_book
UNION
SELECT id FROM t_booktype;

4.5.2 UNION ALL

使用UNION ALL，不会去除掉重复的记录。

5.索引

5.1索引引入

索引定义：索引是由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度。

类似于图书的目录，方便快速定位，寻找指定的内容。

5.2索引的优缺点

优点：提高查询数据的速度（如果一次性要查询30%以上的数据时不推荐使用索引）。

缺点：创建和维护索引的时间增加了，同时占用硬盘空间。

5.3索引分类 -- 重点（可以提升查询速度，面试中遇到的问题也多）

5.3.1.普通索引：这类索引可以创建在任何数据类型中；

5.3.2.唯事索引：使用UNIQUE参数可以设置，在创建唯一索引时，限制该索引的值必须是唯一的。

5.3.3.全文索引：使用FULLTEXT参数可以设置，全文索引只能创建在CHAR,VARCHAR,TEXT类型的字段上。主要作用是提高查询较大字符串类型的速度；只有MyISAM引擎支持该索引，MySQL默认引擎不支持。（到innodb5.5的时候好像支持全部索引了）

5.3.4.单列索引：在表中可以给某个字段创建索引，单列索引可以是普通索引，也可以是唯一索引，还可以是全文索引。

5.3.5.多列索引：多列索引是在表的多个字段上创建一个索引。

5.3.6.空间索引：使用SPATIAL参数可以设置空间索引。空间索引只能建立在空间数据类型上，这种可以提高系统获取空间数据的效率。

5.4创建索引

1.创建表时创建索引
Create table tb_index(
Iid int,
Index(id)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
2.创建普通索引
create table tb_a(
	aid int,
	aname varchar(210),
	key aid(aid)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
3.创建唯一索引
Create table tb_test(
aid int,
	aname varchar(210),
	unique key aid(aid)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
4.创建全文索引
Create table tb_test(
Aid  int,
	Aname  varchar(210),
	FULLTEXT  key aid(aid)
)ENGINE = MYISAM DEFAULT CHARSET = utf8;

5.创建单列索引
create table tb_a(
	aid int,
	aname varchar(210),
	key index_aname(aname)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;

6.创建多列索引
create table tb_a(
	aid int,
	aname varchar(210),
	key index_aname(aname,aid)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;

7.创建空间索引
create table tb_a(
	Aid  int,
	aname  varchar(210),
	SPATLIAL  key index_aname(aname,aid)
)ENGINE = MYISAM DEFAULT CHARSET = utf8;


5.2 在已经存在的表上创建索引
Alter  table  tb_test  add index  index_tid(tid)
修改表tb_test 添加一个index（索引）名为index_tid（添加到tid）

5.3 用ALTER TABLE语句来创建索引
ALTER  TABLE 表名 ADD[UNIQUE | FULLTEXT | SPATIAL]  INDEX
索引名（字段名 [(长度)][ASC | DESC]）;

5.6删除索引

DROP INDEX 索引名 ON 表名;

6.视图

6.1. 视图的引入

6.1.1 视图是一种虚拟的表，是从数据库中一个或多个表中导出来的表。

6.1.2 数据库中存放了视图的定义，而并没有存放视图中的数据，这些数据存放在原来的表中。

6.1..3 使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。

6.2 视图的作用

6.2.1 使操作简便化。

6.2.2 增加数据的安全性。

6.2.3 提高表的逻辑独立性。

6.3 创建视图

CREATE  VIEW  v1  AS  SELECT  *  FROM  t_book;

6.4 查看视图

6.4.1 DESCRIBE 语句查看视图基本信息

6.4.2 SHOW TABLE STATUS 语句查看视图基本信息

6.4.3 SHOW CREATE VIEW 语句查看视图详细信息

6.4.4 在views表中查看视图详细信息

6.5 修改视图

修改视图的定义。

1.CREATE OR REPLACE VIEW 语句修改视图
CREATE OR REPLACE [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE} ]
VIEW 视图名 [(字段清单)]
AS  SELECT 语句
[WITH [CASCADED | LOCAL ] CHECK  OPTION];

2.ALTER 语句修改视图
ALTER  [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE} ]
VIEW 视图名 [(字段清单)]
AS  SELECT 语句
[WITH [CASCADED | LOCAL ] CHECK  OPTION];

第1种具有创建和修改功能，第2种只具有修改功能。

6.6 更新视图

更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（INSERT）表中的数据。因为视图是一个虚拟的表，其中没有数据。通过视图更新时，都是转换基本表来更新。更新视图时，只能更新权限范围内的数据。超出了范围，就不能更新。
与表操作是一样的，即将视图的操作变成了对基表的操作：
1.插入（INSERT）
INSERT  INTO  b1  VALUES(‘A’,’B’,’C’);
2.更新（UPDATE）
UPDATE  b1  SET  v=’X’  WHERE v=’A’;
3.删除（DELETE）
DELETE FROM b1 WHERE v=’X’;

6.7 删除视图

删除视图是指删除数据库中已存在的视图。

删除视图时，只能删除视图的定义，不会删除数据。

DROP VIEW [IF EXISTS] 视图名列表 [RESTRICT | CASCADE]

7.触发器

触发器是一个特殊的存储过程

触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象

7.1 创建触发器

CREATE TRIGGER  trigger_name
trigger_time  trigger_event
ON tbname
FOR EACH ROW
trigger_stmt
说明：
tbl_name必须引用永久性表。不能将触发程序与临时表表或视图关联起来。
trigger_time是触发程序的动作时间。它可以是BEFORE或AFTER，以指明触发程序是在激活它的语句之前或之后触发。
trigger_event指明了激活触发程序的语句的类型。trigger_event可以是下述值之一：
·INSERT：将新行插入表时激活触发程序，例如，通过INSERT、LOAD DATA和REPLACE语句。
·UPDATE：更改某一行时激活触发程序，例如，通过UPDATE语句。
·DELETE：从表中删除某一行时激活触发程序，例如，通过DELETE和REPLACE语句。
请注意，trigger_event与以表操作方式激活触发程序的SQL语句并不很类似，这点很重要。

创建一个单执行语句的触发器：
#创建表
CREATE TABLE account(
acct_num INT ,
amount DECIMAL(10,2)
);
#创建触发器
CREATE TRIGGER ins_sum 
BEFORE INSERT
ON tbname
FOR EACH ROW 
SET @SUM=@SUM+new.amount;
创建有多个执行语句的触发器，语法如下：
DELIMITER $$
CREATE TRIGGER testref 
BEFORE INSERT 
ON tbname
FOR EACH ROW BEGIN
INSERT INTO tbname  SET a2 = NEW.a1;
    DELETE FROM tbname  WHERE a3 = NEW.a1;  
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END $$

7.2查看触发器

SHOW TRIGGERS 

在TRIGGERS 表中查看触发器信息：
information_schema数据库的TRIGGERS 表中，可以通过查询查看触发器信息
SELECT * FROM `information_schema`.`TRIGGERS` WHERE `TRIGGER_NAME`='ins_sum'

7.3删除触发器

案例：删除ins_sum触发器
DROP TRIGGER `school`.`ins_sum`
#删除对应的表也会删除该表的触发器

8.常用函数

8.1日期和时间函数

1.CURDATE()		返回当前日期；
2.CURTIME()		返回当前时间；
3.MONTH(d)		返回日期d中的月份值，范围是1~12；
4.DAY()			返回日期的天（日）；
5.YEAR()			返回日期的年
6.MONTH()		返回日期的月
7.SYSDATE()		返回当前系统日期
8.DATEDIFF()		返回两个DATE值之间的天数
9.DATE_ADD()	将时间值添加到日期值
10.NOW()		返回当前日期和时间
11.TIMEDIFF()	计算两个TIME或DATETIME值之间的差值
12.WEEK()		返回一个日期的星期数值

8.2字符串函数

1.CHAR_LENGTH(s)	计算字符串s的字符数；
2.UPPER(s)			把所有字母变成大写字母；
3.LOWER(s)			把所有字母变成小写字母；
4.CONCAT()			将两个或多个字符串组成一个字符串；
5.LEFT()				获取指定长度的字符串的左边部分；
6.REPLACE()			搜索并替换字符串中的子字符串；
7.SUBSTRING()		从具有特定长度的位置开始的最一个子字符串；
8.TRIM()			从字符串中删除不需要的字符；
9.LPAD()/RPAD()		左填充与右填充
10.FIND_IN_SET(n, h)		在逗号分隔的字符串列表中找到一个字符串；
N是要查找的字符串；H是要搜索的逗号分隔的字符串列表
SELECT  FIND_IN_SET(‘y’, ‘x,y,z’);
--返回：2	表示在第二个位置
SELECT  FIND_IN_SET(‘a’, ‘x,y,z’);
--返回：0
11.FORMAT(n, d, locale)	格式化具有特定区域设置的数字，舍入到小数位数。
N是要格式化的数字；D是要舍入的小数位数；locale可选，用于确定千个分隔符和分隔符之间的分组（不常用）。
SELECT  FROMAT(14500.2018,  2); 
--返回：14,500.20
SELECT  FROMAT(14500.2018,  0);
--返回：14,500

8.3数学函数

1.ABS(x)		求绝对值；
2.SQRT(x)	求平方根；
3.MOD(x,y)	求余。

8.4 加密函数

1.PASSWORD(str)			一般对用户的密码加密，不可逆；
2.MD5(str)				普通加密，不可逆；--在添加数据的时候MD5（data）
3.ENCODE(str, pswd str)	加密函数，结果是一个二进制数，必须使用BLOB类型	的字段来保存它。
4.DECODE(crypt_str, pswd_str)	解密函数。

8.5比较函数

1.Coalesce()		返回一个非NULL参数，适用于将值替换为NULL；
2.GREATEST()		使用N个参数，分别返回n个参数的最大值和最小值。
3.ISNULL()		如果参数为NULL，则返回1否则返回0。

8.6转换函数

1.CAST()			将任何类型的值转换为具有指定类型的值。
CAST(expression AS TYPE);
SELECT  CAST(‘123’  AS  INT);

9.存储过程和函数

优点（为什么要用存储过程？）：
(1)将重复性很高的一些操作，封装到一个存储过程中，简化了对这些SQL的调用。
(2)批量处理：SQL+循环，减少流量，也就是“跑批”。
(3)统一接口，确保数据的安全。
相对于Oracle数据库来说，MySQL的存储过程相对功能较弱，使用较少。

9.1创建存储过程和函数

1.创建存储过程
CREATE PROCEDURE sp_name([proc_parameter[,……]])
[characteristic……] routine_body

Sp_name参数是存储过程的名称；
Proc_parameter表示存储过程的参数列表；
Characteristic参数指定存储过程的特性；
Routine_body参数是SQL代码的内容，可以是BEGIN……END来标志SQL代码的开始和结束。

示例：创建一个存储过程，删除给定球员参加的所有比赛
delimiter $$　　#将语句的结束符号从分号;临时改为两个$$(可以是自定义)
CREATE PROCEDURE delete_matches(p_playerno INT)
BEGIN
DELETE FROM matches
WHERE playerno = p_playerno;
END$$
delimiter ;   #将语句的结束符号恢复为分号

9.2调用存储过程

call delete_matches(57);

带IN的存储过程：
delimiter $$
create procedure in_param(in p_in int)
begin
 　　select p_in;
 　　set p_in=2;
    select P_in;
end$$
delimiter ;

set @p_in=1;
call in_param(@p_in);
Select @p_in;
带OUT的存储过程：
delimiter //
create procedure out_param(out p_out int)
begin
    select p_out;
    set p_out=2;
    select p_out;
end
//
delimiter ;

set @p_out=1;
call out_param(@p_out);

#因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null

select @p_out;
#调用了out_param存储过程，输出参数，改变了p_out变量的值
带INOUT的存储过程：

delimiter $$
create procedure inout_param(inout p_inout int)
begin
    select p_inout;
    set p_inout=2;
   select p_inout;
end
$$
delimiter ;

set @p_inout=1;
call inout_param(@p_inout);
select @p_inout;
#调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量

10 帐户管理

10.1 mysql命令的常用参数

-h：主机名或ip，默认是localhost，最好指定-h参数
-u：用户名
-p：密码，注意：该参数后面的字符串和-p不能有空格
-P：端口号，默认为3306
数据库名：可以在命令最后指定数据库名
-e：执行SQL语句，如果指定该参数，将在登录后执行-e后面的命令或sql语句并退出

10.2创建用户

CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
    [, user [IDENTIFIED BY [PASSWORD] 'password']] 

1.新建普通用户
CREATE USER 'jeffrey'@'localhost' identified BY 'mypass';
用户名部分为“jeffrey”，主机名默认为“%”（即对所有主机开放权限）

如果指定用户登录不需要密码，则可以省略identified BY部分


2.使用GRANT语句创建新用户

GRANT USER语句可以用来创建帐户，通过该语句可以在user表中添加一条新记录
比起CREATE USER语句创建的新用户，还需要使用GRANT语句赋予用户权限
使用GRANT语句创建新用户时必须有GRANT权限。


使用GRANT语句创建一个新用户testUser，密码为testpwd，并授予用户对所有数据表的SELECT和UPDATE权限

GRANT SELECT ,UPDATE ON *.* TO 'testUser'@'localhost' identified BY 'testpwd'
SELECT `Host` ,`User` ,`Select_priv` ,`Update_priv`  FROM mysql.user WHERE `User` ='testUser';

10.3直接操作MYSQL用户表

不管是CREATE USER还是GRANT USER，在创建用户时，实际上都是在user表中添加一条新记录。
使用INSERT语句向mysql.user表INSERT一条记录来创建一个新用户
插入的时候必须要有INSERT权限
INSERT INTO mysql.user(host,user,password,[privilegelist])
VALUES ('host','username',password('password'),privilegevaluelist)
 
使用INSERT创建一个新用户，其用户名称为customer1，主机名为localhost，密码为customer1
INSERT INTO mysql.user(host,user,password)
VALUES ('localhost','customer1',password('customer1'))

10.4 删除普通用户

使用DROP USER语句删除用户，也可以直接通过DELETE从mysql.user表中删除对应的记录来删除用户
DROP USER语句用于删除一个或多个MYSQL帐户。要使用DROP USER，必须拥有MYSQL数据库的全局
CREATE USER 权限或DELETE权限。

删除testUser这个用户
DROP user 'testUser'@'localhost';

使用delete语句删除用户
DELETE FROM mysql.user WHERE `Host`='localhost' and `User`='testUser'

有疑问或优化操作可以添加QQ俩系：304808680

如果看这个不习惯的话可以去下载本人上传的文档。。之前本人是习惯性写文档，没有写博客的习惯，最近突然喜欢上写博客了

积分多的可以选择下载文档

youshang520i

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
MySQL的基本操作

目录1. mysql的数据类型1.1 整数类型、浮点数类型和定点数类型1.1.1 整数类型 1.1.2浮点数据类型和定点数据类型1.2 时间类型1.3字符串类型1.4 二进制类型2.数据库的基本操作1.mysql的注释符2.mysql简介（关系型数据库管理系统）3.显示所有的数据库4. 创建数据库5. 删除数据库6. 显示所有的数据库7....
复制链接

扫一扫