MySQL的基本操作

    技术2025-01-15  16

    MySQL的基本操作

    记录学习MySQL过程中记录的学习笔记

    1 初始访问及设置

    1.1 进入MySQL的用户

    1.1.1 本机登陆

    打开cmd,输入 mysql -u 用户名 -p

    执行命令时可能会遇见无法识别’mysql’这个命令,这是因为在系统环境变量中没有相应路径导致的,添加类似于:‘C:\Program Files\MySQL\MySQL Server 5.7\bin’,默认路径。添加完成重启cmd即可。

    按提示输入密码,在-p后直接输入密码也可登陆成功

    1.1.2 联机登陆

    与本机登陆相同,要添加上要登陆的账号所存放的IP地址

    mysql -h IP -u 用户名 -p

    1.2 设置MySQL用户

    具体的账号生成可看下面的 “2 用户管理”完成账号的相关设置后,运行(更新): flush privileges; 查看用户是否生成: select user,host from user;

    2 用户管理

    2.1 用户账号、IP、密码创建

    create user 'name'@'IP' identified by 'password'; name:输入你的账号名字IP:输入运行登陆的IP地址,如:

    ‘196.168.1.1’:输入完整的IP地址,则只允许在此IP地址上登陆使用 ‘196.168.1.%’,‘196.%’:输入不完整的IP地址,其余用%填充,表示允许在相同前缀的IP下使用 ‘%’:仅有%表示允许在任何地址下使用

    password:输入设置的密码

    2.2 权限设置

    # 授予权限 grant select,insert on db.t to 'name'@"196.168.%"; # grant 权限 on 区域 to 用户 # 取消权限 revoke select,insert on db.t from 'name'@'196.168.%'; # grant 权限 on 区域 from 用户 grant:授予权限命令权限: all privileges:除了grant外的所有权限select:查询insert:添加update:更新 区域:允许操作的数据库或表

    数据库.表:指定表 数据库.*:数据库下所有表 数据库.存储过程: *.*:所有数据库所有表

    用户:被授予权限的用户,格式是’name’@‘IP’,IP的设置同上。表示允许哪个用户在哪可操作的权限

    2.3 其他操作

    # 删除用户 drop user 'name'@'IP'; # 修改用户名' rename user 'name1'@'IP' to 'name2'@'IP'; # 修改用户密码' set password for 'name'@'IP' = new password;

    3 数据库及数据表操作

    3.1 查询操作

    # 显示所有数据库 show databases; # 使用数据库 ues database_name; # 显示表 show tables; # 查询表数据(列) select col_name from table_name; # col_name 可以是 * 表全部, 逗号隔开,如:user,host # 查询设置的表的设置(如:自增、非空、数据类型等) desc table_name;

    3.2 基础操作

    3.2.1 数据库操作

    # 创建数据库 create database name; create database name default charset=utf8; # 显示所有数据库 show databases; # 删除数据库 drop database name; name: 创建/删除的数据库名字default: 设置charset: 文本格式,使用utf8可以使用中文字符,使用默认会报错

    3.2.2 表操作

    3.2.2.1 基本操作
    # 创建表 create table table_name( # 列名 类别 null/not null, # 列名 类别 null/not null auto_increment primary key, id int, name char(123) )engine=innodb default charset=utf8; # 查看所有表 show tables; # 清空表 delete from table_name; truncate table table_name; # 删除表 drop table table_name; table_name: 要操作的表名设置表的内容:一个逗号代表一列,最基础设置可以直接就是列名,后面皆是可以选

    null/not null: 表示数据可以为空/不可以为空 auto_increment: 表示当前列的数据将会自动增加(1、2、3、4……),一般要与后面primary key一起出现 primary key: 表约束(不能重复、不能为空),还有加速查找的功能(会生产一个文件辅助查找)

    engine:引擎

    innodb:支持事务(原子性),减少因为表在数据交换的过程中出现的数据错误 myisam:支持全局索引,存储速度快

    清空表:

    delete: 清空数据但保留数据的顺序,如上面的auto_increment,新增一个数据时会在原来的编号后加1 truncate: 完全清空

    3.2.2.2 数据类型
    数字型: 整型:长度由小到大,tinyint(-128~127),int(-2147483648~2147483647),bigint(-9223372036854775808~9223372036854775807)带小数: 浮点型:FLOAT,DOUBLE,数字不精确decimal:精确的小数,设置格式是:decimal(num1,num2),其中num1是数字的总位数(整数与小数),num2是小数点后的数字位数 无符号:在类型后面加unsigned 字符型: 255字符: char:设置格式:char(num),其中num是字符型的长度,如果输入的长度比设置的要短,会自动用空字符补齐,最后此数据的长度是固定的,因此使用这个查找速度快,但较占空间varchar:设置格式:varchar(num),其中num是设置长度,这个设置不会自动补齐长度,因此输入的字符长度是多少,数据占的空间就是多少,因此使用的空间少,但在查找时使用的是遍历较耗时 更长: text:65,535(2^16 - 1)mediumtext:166,777,215 (2^16 - 1)longtext:4,294,967,295(2^24 - 1) 时间: DATE:YYYY-MM-DDTIME:HH:MM:SSYEAR:YYYYDATETIME:YYYY-MM-DD HH:MM:SSTIMESTAMP:YYYYMMDD HHMMSS 二进制:TinyBlob,Blob,MediumBlob,LongBolb限定选择: enum:枚举,输入的数据只能在设置的范围内进行选择其中一个 # 创建 create table table_name( type_name enum('a','b','c') )engine=innodb default charset=utf8; # 使用(行操作,看后面) insert into table_name(type_name) values('b'); set:集合,输入的数据只能在设置的范围内进行选择任意个 # 创建 create table table_name( type_name set('a','b','c') )engine=innodb default charset=utf8; # 使用(行操作,看后面) insert into table_name(type_name) values('a, b'); 其他数据:如大数据和图片等,可以将数据存入文件,数据格内填写路径
    3.2.2.3 唯一索引

    限制值不能重复,单列、多列联合

    create table table_name( id int, unique uq_name (id) # unique 添加的唯一索引的名称 (约束列名1(,约束列名2……)) )engine=innodb default charset=utf8; )
    3.2.2.4 外键

    可以实现类似枚举的功能,实现对数据的限制输入,与主键不同的是可以为空

    create table table_name( id int, type_name_id int, constraint constraint_name foreign key ('type_name_id',) references table_name_2('id') # constraint 添加的约束的名称 foreign key ('被约束的列名',) references 约束来源的表名('约束来源的列名') )engine=innodb default charset=utf8; create table table_name_2( id int auto_increment primary key, type_name varchar(15) )engine=innodb default charset=utf8;

    3.2.3 行操作

    # 插入数据 # 单组数据的插入 insert into table_name(col1, col2) values(data1, data2); # 多组数据的插入 insert into table_name(col1, col2) values(data1, data2),(data3,data4); # 其他表的数据 insert into table_name(col1, col2) select colx,coly from table2_name; # 删除数据 delete from table_name where col1<10; # 修改数据 update from table_name set col2=3; update from table_name set col2=3 where col2<3; # update from table_name set col2=3(,col3=22……) where col2<3; # 查看数据 # 全部满足条件的数据查看 select col from table_name; select col from table_name where col1=10; select col as other_col from table_name where col1<10; # 可以修改显示出来的列名为other_col select col,2 from table_name where col1<10; # 在选择列名处填写数字,会在显示的每行后面显示出这个数字 # select col from table_name where col in (10); 等效果,括号内可以逗号隔开多个,否定可以在in前加not,连续区域也可以是between num1 and num2(左右闭区间) select col from table_name where col like '%2'; # 目标是字符时,可以使用'%'或'_'统配来查找,如:'%2'、'_2'是以2为结尾的所有字符串 # 选择展现数据 select col from table_name limit num1; # 只有一个数字时,第0条开始的num1条数据 select col from table_name limit num1,num2; # 两个数字时,从num1条开始的num2条数据 # 数据显示顺序改变,不改变表本身的顺序 select * from table_name order by col_name desc; # 大到小 select * from table_name order by col_name asc; # 小到大 select * from table_name order by col_name desc, col_name2 asc; # 分组 select max(col) from table_name group by col_name; # 在第二个参数处是解决同组的显示问题,使用聚合函数:count(计算同组的个数),max,min,sum,avg。可以逗号隔开一同出现 select max(col) from table_name group by col_name having max(col)>4; # 对聚合函数的结果进行二次筛选需要用having而不能用where # 连表 # 左右连表 select * from table1,table2 where table1.col=table2.col; select * from table1 left join table2 on table1.col=table2.col; # left:table1全显示,right:table2全显示。显示方无论另一个表有没有对应数据都显示,没有对应数据则显示NULL。inner:只显示有对应数据的信息。 # 上下连表 select * from table1 union select * from table2; # union会自动将完全重复的数据去除不显示,union all则全部直接显示。 table_name: 操作的表名colx: 操作的列名,可以不按顺序datax: 添加的数据,添加顺序与colx相同set: 设置相关区域的数据的改变select colx:查看的列名,逗号隔开,*为全部where: 添加选择范围的条件,类似于if

    4 数据库的保存与加载

    4.1 保存

    mysqldump -u root databases_name > path\name.sql -p; # 有所有数据的数据库(insert) mysqldump -u root -d databases_name > path\name.sql -p; # 不包含数据的数据库

    4.2 加载

    create databases databases_name; mysqldump -u root -d databases_name < path\name.sql -p;
    Processed: 0.009, SQL: 9