MySQL数据库的基本操作

最新推荐文章于 2024-10-06 23:19:05 发布

张蔓蔓蔓蔓

最新推荐文章于 2024-10-06 23:19:05 发布

阅读量441

点赞数

分类专栏： MYSQL

本文链接：https://blog.csdn.net/manmanoy/article/details/81333167

版权

MYSQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

查看默认引擎：
show variables like 'storage_engine%';
修改数据库默认引擎：修改my.ini配置文件
default-storage-engine=InnoDB---可改为其它存储
创建表时设置引擎：
语法： CREATE TABLE 表名（。。。代码）ENGINE=存储引擎
MyISAM类型表文件：
*.frm 表结构定义文件
*.myd 数据文件
*.myi 索引文件

InnoDB类型表文件：
*.frm 表结构定义文件
ibadata1 文件

存储位置：
   因操作系统因人而异，可查my.ini
   datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
   innodb_data_home_dir="D:/MySQL Datafiles

mysql：系统默认4个数据库：
Information_schema
Performance_schema
mysql
test

默认端口号3306，默认字符集utf8

启动数据库：
如果没有默认启动可以在计算机管理里服务查看右键启动，
1 ：dos 命令：net start mysql来启动，默认自带一个客户端，直接输入密码
也可操作数据库
mysql-uroot-p 输入密码也可链接
mysql -h 输入IP地址 -uroot-p 输入密码也可链接
2：使用MySQL Command Line Client 登录
修改root 密码的语句:
1 alter user root@localhost identified with mysql_native_password by '654321';
2 mysqladmin -uroot -p123456 password ；不用进入mysql内部

查看所有的进程：
ps - ef
并筛选出MYSQL：
ps - ef | grep mysql （筛选 mysql）
可看到 mysql进程号
删掉mysql进程：
kill 进程号
如果没有启动：
mysqld_safe& 可以启动
想关闭 mysql服务器：
mysqladmin -u root -p shutdown
添加用户：
use mysql ;进入数据库，
查看已有用户：
select 字段 from 表名；
插入用户：
grant all on test .* to 'xxx'@'%' identified by '密码xxx';
#授权所有在 test数据库下 .*所有的表 ‘xxx用户’ @从 %所有机器访问给它的密码 by ‘设置的密码’

grant 授权
all 所有
on 在哪里
test = 要设置的数据库名
. * 数据库里所有的表
‘ 用户 ’ 添加的用户
@ 从哪访问
% 所有
identified 密码
by'xxx' 设置的密码
注释：COMMENT

自己创建数据库;
语法：
CREATE DATABASE 数据库名；（分号结束/不区分大小写)
Query OK,1 row affected <0.00 sec>
#执行成功，操作影响的行数，<操作执行的时间>
创建数据库时后面可加 charset'utf8'; 让其支持中文
创建数据库设定表结构每个字段都要指定类型，规定长度，
规定是否为空，必须按照字段类型存储

创建表：
语法：
CREATE TABLE [ IF NOT EXISTS ]表名（字段1 数据类型[字段属性约束][索引][注释]..
字段n 数据类型 [ 字段属性约束 ] [ 索引 ] [ 注释 ]）[表类型] [表字符集] [注释]；
示例：
CREATE TABLE 'student'( 'studentNo' INT(4) PRIMARY KEY, 'name' CHAR(10)....;
[ ]为可选部分，
IF NOT EXISTS如果不存在,
多字段都用逗号分割，
保留字用撇号括起来
单行注释#
多行注释/* */
示例：
CREATE TABLE type ( 'sid' INT (4) ZEROFILL) ;
# 表名（‘字段名’ 类型（宽度）以0填充）

删除数据库：
语法：DROP DATABASE数据库名；
Query OK, 0 rows affected (0.02 sec)
#执行成功，操作影响的行数，<操作执行的时间>
查看my.ini配置文件，可查看更改配置（MySQL装在哪就去哪找）
删除表：
DROP TABLE [ IF EXISTS ] 表名；[如果表名存在]
示例：
USE XXX；
DROP TABLE IF EXISTS 'XXX'；（删除之前验证表是否存在）

删除数据记录：
1 DELETE FROM 表名 [ where 条件] ; 不写条件会删除所有表记录
2 TRUNCATE TABLE 表名; 会删除所有表记录
truncate 语句删除后将重置自增列，表结构及其字段、约束、索引保持不变，
执行比DELETE快
删除字段：
ALTER TABLE 表名 DROP 字段名；

修改表：
修改表名：
Alter table 旧表名 RENAME [TO] 新表名；
修改字段：
Alter table 表名 CHANGE 原字段名新字段名数据类型 [ 属性]；
Alter table xxx drop REGISTER_DATE; 修改删除字段
Alter table xxx drop name; 删除name字段
修改数据类型不能为空:
Alter table xxx modify sexe enum('f ','m')not null;enum （‘F’ ，M’ ）字段
modify 只是修改字段的数据类型
Chenge：全部改名字类型
语法：
Chenge 要修改的字段名新的字段名类型
添加字段：
Alter table 表名 ADD 字段名数据类型 [属性]；
Alter table xxx add phone int(11) not null ;添加字段
添加枚举字段:
Alter table xxx add sex enum(' f ' , ' m ');
添加主键：
Alter table 表名 ADD CONSTRAINT主键名 PRIMARY KEY 表名（主键字段）；
添加外键：
Alter table 表名 ADD CONSTRAINT外键名 FOREIGN KEY (外键字段)
REFERENCES 关联表名（关联字段）；

插入单条数据记录：
语法：
INSERT INTO 表名[(字段名列表)] VALUES (值列表)；
（数值类型都不加单引号其他都加单引号，字段名省略默认插入所有字段）
示例：
INSERT INTO 'type1' VALUE(12) ,(1234),(123456) ;
# 表名中插入值 3 个记录
插入多条：
INSERT INTO 表名[(字段名列表)] VALUES (值列表1),(值列表2),(值列表3)；
示例：
INSERT INTO STUDENT（字段名） VALUES(3,'123',’李静‘， 2 ‘13’，
‘地址’‘日期’，‘邮箱’，‘身份证号码’)；如果是自动增长 3写成NULL

从学生表提取姓名手机号两列数据到通讯库表中：（相当于备份）
create table 通讯录表（select ' 数列'，‘数列’FROM '学生表'）；
（表必须不存在，如新表已存在将会报错）
在已有的表中引用其他表中的数据进行添加：（也属于备份）
insert into xxx select 数列1，数列2 from 表名；（表必须存在）

更新数据记录：
Update表名 set 字段1=值1，字段N=值n [where条件]；
改谁的条件就写谁的，如果不写条件会改变所有的
update xxx set sex=‘女’where sex='男‘

查看所有的数据库：
语法： show databases ;
查看所有表：
进入数据库：
use mysql ;
SHOW TABLES ；
查看表是否存在
USE XX ; SHOW TABLES ；
查看表定义
DESCRIBE 表名；或 DESC 表名；
（避免DOS窗口乱码，可执行 SET NAMES GBK；）
看表里面的结构：
desc 表名字；
可以查看表的具体内容：
select * from user \G；（*号查所有）加 \G 会变竖列可读性好

查询：HELP 查询内容（一般在DOC命令下使用）
语法：
select * from ；查询所有
或 select 表名 From 表名；查询某个，可加where 条件
使用AS命名列、查询-列别名：
1 SELECT `studentNo` AS 学生编号,`studentName` AS 学生姓名,
`address `AS 学生地址
2 SELECT `firstName` + '.' + `lastName` AS 姓名
FROM `employees`;
注意：
1. + 连接的数据类型必须兼容
2. 如果 + 连接字符型数据，结果为字符串数据的连接
3. 如果 + 连接数值型数据，结果为数值的和

加WHERE 条件查询：
SELECT * FROM XXX WHERE ID>3 and age<20;
按照日期查询:
SELECT * FROM XXX WHERE REGISTER_DATE LIKE '2018-03%' ；
查询空行：
示例：
SELECT `studentName` FROM `student` WHERE `email` IS NULL;
条件为空 is null 不是空的 is not null
原来有数据，但数据被清除的列如何查询：使用常量列
示例：
SELECT `studentName` AS 姓名，`address` AS 地址，
’北京信息中心’ AS 学校名称
select 1,可直接输出1信息，可以加函数
如：select now(); 输出当前系统时间
FROM `student`;
exists 子查询：
查询中嵌套一个查询
select * xxx where 列名 > (select xxx)
in 子查询：
not in :
if exists 如果存在 is not exists 如果不存在
where exists 判断是否存在返回真假
相关子查询非相关子查询
排序：
ORDER BY 排序的列名； ASC 默认正序 DESC 倒序
按设定顺序查询：
SELECT * FROM XXX ORDER BY REGISTER_DATE ;
分组统计：
GROUP BY ：设定顺序 selest from where group by LIMIT
(如果 group by 后面在有条件用 having)
按照名字进行分组查询：
SELECT NAME ,COUNT(*)as lieming FROM XXX GROUP BY NAME;
count( )计数，可以给count as 起个新字段名字
按照日期进行分组查询：
SELECT REGISTER_DATE ,COUNT(*) FROM XXX GROUP BY REGISTER_DATE;
按照名字的 age总和进行分组查询：
SELECT NAME ,SUM (age) FROM XXX GROUB BY NAME ;
sum( 按照什么)求和：
（With rollup 求加起来的总数 ,COALESCE 可以设置一个代替null的名称：）
按照名字的 age总和进行分组查询，并求出一个总和，
在给加起来的总和起一个名字sum_name，代替null：
SELECT COALESCE (name ,'sum_name 'SUM (age)FROM XXX GROUB BY NAME WITH ROLLUP ;

可以使用OFFSET 指定SELECT 语句开始查询的数据偏移量，默认情况下偏移量为0
LIMIT 分页：
用LIMIT属性设定返回的记录数:Limit 起始，结束；
Limit m,n
m起始，0开始
n页的大小；
p 页码（1-）
m=(p-1)*n
查前两条数据
SELECT * FROM XXX LIMIT 2；
显示第一页：
LIMIT 0,5; 从1之后，第5条结束，显示5条
显示第二页：
LIMIT 5，10 从6之后，第10条结束，显示5条
显示第三页：
LIMIT 10，15 从11之后，第15条结束，显示5条
第一条之后查两条数据，
SELECT * FROM XXX LIMIT 2 OFFSET 1;
(OFFSET不能单独用，要和LIMIT一起用)

链接：
left join 左链接以左表为基准，没有对应的值为null
right join右链接以右表为基准，没有对应的值为null
cross join完全链接能对应上的链接对应不上的为null
inner join 内连接只链接能对应上的
如果不写on 会造成笛卡尔乘积，所有表的行列都会对应相乘的结果m行*n行
on 链接条件
示例：
   select * from 表名 inner join 表名on 表名.字段=表名.字段；
   select a.id,titlt,b.id,b.name
   from 表名 as a inner jion 表名 as b on a.字段名=b.字段名 order by a.id;

视图：（子查询）是从一个或多个表导出的虚拟的表，其内容由查询定义

单表视图一般用于查询和修改，会改变基本表的数据，

多表视图一般用于查询，不会改变基本表的数据。

作用：

①简化了操作，把经常使用的数据定义为视图。

②安全性，用户只能查询和修改能看到的数据。

③逻辑上的独立性，屏蔽了真实表的结构带来的影响。

view 对查询的结果做一个快捷方式，视图根据表的数据实时更新，

创建：

create view 起的名字 as 查询...

从视图中检索数据：

select * from 视图名字;

删除视图：

drop view 名字；

索引：

数据库系统满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

优点：可以大大提高MySQL的检索速度。缺点：增加了数据库的存储空间，在插入和修改数据时要花费较多的时间，

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。但过多的使用索引将会造成滥，虽然索引提高了查询速度，同时却会降低更新表的速度，

二分查找，有序数据，字符串汉字转换UNICODE 编码比较 ord()函数

创建索引：create index 名字 on 表具体的字段

删除索引：DROP INDEX [indexName] ON表具体的字段 ;

唯一索引：UNIQUE INDEX indexName ON表具体的字段 ;索引列的值必须唯一，但允许有空值

显示索引信息：SHOW INDEX FROM

存储过程：

存储过程 (Stored Procedure) 是在大型数据库系统中 , 一组为了完成特定功能的 SQL 语句集 , 存储在数据库中 , 经过第一次编译后再次调用不需要再次编译 , 用户通过指定存储过程的名字并给出参数 (如果该存储过程带有参数) 来执行它 , 存储过程是数据库中的一个重要对象 ; 存储过程中可以包含逻辑控制语句和数据操纵语句 , 它可以接受参数 , 输出参数 , 返回单个或多个结果集以及返回值 ;

存储过程就像函数一样，有名字有参数，有语句，要把结束符改掉，否则可能会遇到结束符会语句提交，最后在替换回来，可以把常用的SQL语句写成存储过程，存储过程可以避免sql注入，(所有 MySql 变量都必须以 @ 开始)

获得最后一个ID ：select LAST_INSERT_ID()

select@@identity

创建存储过程：

create procedure xxx（);

调用存储过程：

call xxx();

删除存储过程：

drop procedure xxx;

例：

delimiter $$ #更改结束符

create procedure open_order(