MySQL入门及库表管理

白日梦最可笑
96 阅读17分钟

数据库的概念

数据(Data)

  • 描述事物的符号记录
  • 包括数字，文字、图形、图像、声音、档案记录等
  • 以"记录"形式按统一的格式进行存储

表

  • 将不同的记录组织在一起
  • 用来存储具体数据

数据库

  • 表的集合，是存储数据的仓库
  • 以一定的组织方式存储的相互有关的数据集合

数据库管理系统(DBMS)

  • 是实现对数据库资源有效组织、管理和存取的系统软件
  • 数据库的建立和维护功能、数据定义功能、数据操纵功能、数据库的运行管理功能、通信功能

数据库系统

  • 是一个人机系统，由硬件、OS、数据库、DBMS、应用软件和数据库用户组成
  • 用户可以通过DBMS或应用程序操作数据库

image.png

数据库可以完成数据持久化保存+快速提取

常用的数据类型

类型含义
tinyint(n)1个字节，范围(-128~127)
smallint(n)2个字节，范围(-32768~32767)
mediumint(n)3个字节，范围(-8388608~8388607)
int(n)4个字节(32个比特位)，整数型，范围(-2147483648~2147483647)
bigint(n)8个字节，整数型，范围(+-9.22*10的18次方)
float(m,d)单精度浮点，8位精度，4字节32位。m数字总个数，d小数位
double(m,d)双精度浮点，16位精度，8字节64位 。m总个数，d小数位
char固定长度的字符类型
varchar可变长度的字符类型
text文本
image图片
decimal(5,2)5个有效长度数字，小数点后面有2位（例如123.56）

具体说明：

1、int(N)

int(N)中的N不是限制字段取值范围的，int的取值范围是固定的（0至4294967295）或（-2147483648至2147483647）。N这个值是为了zerofill在字段中的值不够时补零的。

int默认是signed（有符号），取值范围（-2147483648至2147483647）。如果加了unsigned（ 无符号）参数那么取值范围就为（0至4294967295）。

2、float(m,d)

设一个字段定义为float(6,3)，表示6个有效长度数字，小数点后面有3位。如果插入一个数123.45678，实际数据库里存的是123.457，但总个数还以实际为准，即6位。如果插入数12.123456，存储的是12.123，如果插入12.12，存储的是12.120。

整数部分最大是3位，如果插入1234.56，会插入失败。

3、char与varchar

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

下表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果，说明了CHAR和VARCHAR之间的差别：

值CHAR(4)存储需求VARCHAR(4)存储需求
''' '4个字节''1个字节
'ab''ab '4个字节'ab '3个字节
'abcd''abcd'4个字节'abcd'5个字节
'abcdefgh''abcd'4个字节'abcd'5个字节

字节大小：

  • char无论是否有值，都会占用固定长度的字节大小，保存在磁盘上都是4字节。
  • varchar在保存字符时，默认会加一个隐藏的结束符，因此结束符会多算一个字节。

优劣比较：

  • varchar比char节省磁盘空间。
  • 但varchar类型的数据读写速度比char慢，因为char是连续的磁盘空间，e而varchar在多次增删改查中会产生一些磁盘空间碎片。

数据库结构

mysql中的命令默认不区分大小写。表名和库名区分大小写。

在linux系统中的客户端，命令后一定要加分号。

1、查看当前服务器中的数据库

SHOW DATABASES;     #大小写不区分，分号“;"表示结束

image.png

2、查看数据中包含的表

 USE 数据库名；
 SHOW TABLES;
 
 SHOW TABLES FROM 数据库名；    #用于直接查看表

image.png 直接查看表

image.png

3、查看表的结构

 USE 数据库名；
 DESCRIBE 表名；
 可缩写成:DESC 表明；
 ________________________________________________
 #跳过直接查看表的结构
 DESCRIBE  [数据库名].[表名]
 ------------------------------------------------
 #以命令的形式查看表结构
 SHOW CREATE TABLE 表名；

切换到库再查看表

image.png 跳过库直接查看表的结构

image.png 以命令的形式查看表结构

image.png

SQL语句的类型

关系型数据库，都是使用SQL语句来管理数据库中的数据。

SQL，即结构化查询语言(Structured Query Language) 。

SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能。

1、SQL中的名词介绍

  • 数据库：database
  • 表：table，行：row 列：column
  • 索引：index
  • 视图：view
  • 存储过程：procedure
  • 存储函数：function
  • 触发器：trigger
  • 事件调度器：event scheduler，任务计划
  • 用户：user
  • 权限：privilege

2、SQL语言规范

  • 在数据库系统中，SQL 语句不区分大小写，建议用大写。
  • SQL语句可单行或多行书写，默认以 " ; " 结尾。
  • 关键词不能跨多行或简写。
  • 用空格和TAB 缩进来提高语句的可读性。
  • 子句通常位于独立行，便于编辑，提高可读性。

数据库对象和命名：

数据库的组件(对象)：

  • 数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等。

命名规则：

  • 必须以字母开头，后续可以包括字母，数字和三个特殊字符（# _ $）
  • 不要使用MySQL的保留字，如table、select、show、databases。
  • 数据库名、表名、用户名区分大小写

3、SQL语言分类

语句代表的意思
DDL数据定义语言，用于创建数据库对象，如库、表、索引等（create ）
DML数据操纵语言，用于对表中的数据进行管理(insert drop delete update )
DQL数据查询语言，用于从数据表中查找符合条件的数据记录(select )
DCL数据控制语言，用于设置或者更改数据库用户或角色权限(grant )

创建及删除数据库和表

1、创建新的数据库

 CREATE DATABASE 数据库名;

image.png

2、创建新的表

主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。

 CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型 [,...][,PRIMARY KEY (主键名)]); 
  use banji
  
  #实例：
 create table 18ban(id int,name char(10) not null,age int,sex char(4),score decimal(5,2))

image.png

3、删除指定的数据表

 use库名；
 drop table表名；
 ----------------------------------
 drop table 库名.表名；   #库外删除指定表

image.png drop table 库名.表名

image.png

4、删除指定的数据库

 drop database 库名；
 #删除了库后库下面的表也会被删除

image.png

5、管理表中的数据记录

向数据表中插入插入新的数据

 方法一：为所有字段插入值
 insert into 表名 values (所有字段的值);   #每个字段值用逗号相隔；
 
 方法二：为指定字段插入值
 INSERT INTO 表名(字段1,字段2[,...]) VALUES (字段1的值，字段2的值，...);    #注意字段的属性not null，则必须为该字段插入值
 
 
  #实例：
  #为表中所有字段插入值，此种方式密码会以明文显示。
 INSERT INTO class01 VALUES(2, '张三', 90.5, 654321);
 
 #为指定字段插入值
 INSERT INTO class01 (id, name, passwd) values (1, '李四', PASSWORD('123456'));
 #PASSWORD ('123456')：查询数据记录时，密码字串以加密形式显示。若不使用PASSWORD()，查询时以明文显示。.
 
 #下面这条命令不生效，因为name字段设置了not null，不允许为空值。
 INSERT INTO class01 (id, score, passwd) values (3, 88, 123123);
 
 SELECT * FROM class01;    #查询表的数据记录

image.png

image.png

image.png

image.png

查询数据记录

 SELECT * FROM 表名;      #查询表中所有记录
 ​
 #按条件查找数据记录，且只显示指定字段的值
 SELECT 字段名1,字段名2[,...] FROM 表名 [WHERE 条件表达式];
 ​
 #表中的记录默认是横向展示的，当字段很多时显示很乱，可以将";"换成"\G"，改成纵向展示每条记录。
 select * from 表名\G
 ​
 #mysql的分页语句：
 select * from 表名 limit 2; 
 select * from 表名 limit 2,3;       #显示第2行后的前3行（即显示第3~5行）
 select * from 表名 limit 19,11;     #查看第20行到第30行的记录

 select * from 表名 id>=10 and id<=20;    #查看id值在10到20之间的数据记录
 
 
 #实例：
 SELECT * FROM class01;     #查看表中所有数据记录
 ​
 SELECT id, name, score FROM class01 WHERE id=2;     #查看id为2的数据记录，显示id、name、score三个字段
 ​
 SELECT id, name, score FROM class01 WHERE id=1 or id=2;    #查询id为1或id为2的数据记录
 ​
 SELECT id, name, score FROM class01 WHERE name='王五' and score=88;   #查询name为“王五”且分数为88的数据记录。
 ​
 SELECT * FROM class01\G               #以列表方式竖向显示每个字段
 ​
 SELECT * FROM class01 limit 2;        #只显示前2行
 ​
 select * from class01 limit 2,3;      #显示第2行之后的前3行（即显示第3~5行）

image.png

image.png

image.png

image.png

image.png

image.png

image.png

修改/更新数据表中的数据记录（改）

修改字段一般要加上条件，不然会把所有行都修改了。

格式：
 UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];
 
 #实例：
UPDATE class01 SET passwd= PASSWORD(' ') WHERE name='张三';
 #修改name为“张三”的数据记录，将其密码修改为以密文显示。
 ​
 UPDATE class01 SET score=77,passwd=' ' WHERE id=1;
 #修改id为1的数据记录，将score字段值改为77，passwd字段改为无值。
 ​
 #建议使用唯一性比较好的字段作为指定条件，因为名称可能有重复，但id不会重复。

image.png

image.png

在数据表中删除指定的数据记录（删）

删除数据记录时，一定要加条件，否则会将整个表都删除！

 DELETE FROM 表名 [WHERE 条件表达式];     #删除一定要加条件，不然会删除整个表
 
 
 #实例:
 DELETE FROM class01 WHERE id=2;     #删除id字段值为2的数据记录

image.png

6、修改表名和表结构

修改表名

 ALTER TABLE 旧表名 RENAME 新表名;

image.png

扩展表结构（增加字段）

 ALTER TABLE 表名 ADD 字段名 数据类型 default 'default值';
 #default 'default值'：表示此字段设置默认值 未填写则自动填入默认值；可与 NOT NULL 配合使用

image.png

修改字段（列）名，添加唯一键

唯一键： unique key 主键： primary key

共同点：字段的值都是唯一性，不允许有重复的值 不同点：一个表中 只能有1个主键，但是可以有多个唯一键 主键字段中不允许有null值，唯一键是允许有null

 ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];

image.png

指定字段为主键

 ALTER TABLE 表名 ADD PRIMARY KEY (字段名);
 #设置为主键后字段的值得是唯一

image.png

删除字段

  ALTER TABLE 表名 drop 字段名;

image.png 使用 if 判断 创建表并测试自增和填充（案例）

按行创建表，更加有利于我们操作
 use class;
 ​
 create table if not exists info(
 id int(4) zerofill primary key auto_increment,
 name char(20) not null default '匿名',
 sid int(18) not null unique key,
 hobby varchar(50));
 ​
 ​
 -------------------------------------------------------------------------------------
 注释：
 - if not exists：表示检测要创建的表是否已存在，如果不存在就继续创建
 - int(4) zerofill：表示若数值不满4位数，则前面用“0”填充，例0001
 - auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增；自增长字段数据不可以重复；自增长字段必须是主键；如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次
 - unique key：表示此字段唯一键约束，此字段数据不可以重复；一张表中只能有一个主键, 但是一张表中可以有多个唯一键
 - not null：表示此字段不允许为NULL

创建表

image.png 测试自增和填充

image.png 重点：自增长字段数据不可以重复；自增长字段必须是主键；如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次

7、数据表高级操作

克隆表的结构

 CREATE TABLE 新表名称 like 旧表名称； #克隆表的结构

image.png

克隆表的数据

 INSERT INTO 要导入数据记录的表名 SELECT * FROM 想要的数据表名;

image.png

创建表的同时导入表结构和数据

 CREATE TABLE 新表名称 ( SELECT * FROM 想要的数据表名);

image.png

 方法1
 DELETE FROM 表名；
 #DELETE清空表后，返回的结果内有删除的记录条目；DELETE工作时是一行一行的删除记录数据的；如果表中有自增长字段，使用DELETE FROM 删除所有记录后，再次新添加的记录会从原来最大的记录 ID 后面继续自增写入记录。

image.png

  方法2
 TRUNCATE TABLE 表名；
 #TRUNCATE 清空表后，没有返回被删除的条目；TRUNCATE 工作时是将表结构按原样重新建立，因此在速度上 TRUNCATE 会比 DELETE 清空表快；使用 TRUNCATE TABLE 清空表内数据后，自增auto_increment会从 1 开始重新记录

image.png

创建临时表

临时表创建成功之后，使用SHOW TABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。 如果在退出连接之前，也可以可执行增删改查等操作，比如使用 DROP TABLE 语句手动直接删除临时表。

 CREATE TEMPORARY TABLE 表名 （字段1 数据类型，字段2 数据类型[,....] [,PRIMARY KEY (主键名)]）；
  #添加临时表test
 create temporary table test (
 id int(4) zerofill primary key auto_increment,
 name varchar(10) not null,
 cardid int(18) not null unique key,
 hobby varchar(50));
 
 #查看当前库中所有表
 show tables;    
 #在临时表中添加数据
 insert into test values(1,'老六',123456789,'running');    
 #查看当前表中所有数据
 select * from test;
 #退出数据库
 quit                        
 
 #重新登录后进行查看      
 mysql -u root -p
 use kgc;
 #查看之前创建的临时表中所有数据，发现已经被自动销毁
 select * from test;

image.png

image.png 临时表只有当前终端的用户用select * fr表明表名可以看到，其他终端的用户是看不到的

创建外键约束，保证数据的完整性和一致性

  • 外键的定义：如果同一个属性字段x在表一中是主键，而在表二中不是主键，则字段x称为表二的外键

*主键表和外键表的理解：

  • 以公共关键字作主键的表为主键表（父表、主表）
  • 以公共关键字作外键的表为外键表（从表、外表）

注意：与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主从表的字段具备相同的数据类型、字符长度和约束。

 create table kc (cid int,cname char(10));
 #创建主表名为kc字段1 cid int类型字段2 cname char类型10字节
 ​
 create table xy (xid int,xname char(10),age int,cid int);
 #创建从表名为xy 字段1 xid int类型 字段2 xname char类型10字节字段3 age int类型 字段4 cid int类型
 ​
 alter table kc add constraint pk_cid primary key(cid);
 #为主表添加一个主键约束，主键名一般以pk开头（不强求）
 ​
 alter table xy add constraint fk_cid foreign key(cid) references kc(cid);
 #为从表添加外键并将从表的cid字段与主表的cid字段建立关联，外键名一般以fk开头（不强求）
 ​
 #插入新的数据记录时要先插入主表的在插入从表的
 insert into kc values(1,'云计算');
 insert into kc values(2,'大数据');
 insert into kc values(3,'java');
 ​
 insert into xy values(2,'张三',18,1);
 insert into xy values(1,'李四',20,3);
 insert into xy values(3,'王五',22,2);
 ​
 #删除数据记录时要先删除从表在删主表，也就是说删除主键表必须先删除其他与之关联的表中记录
 delete from xy where cid=1;
 delete from kc where cname='云计算';
 select * from xy;
 select * from kc;
 ​

8、数据库用户管理

新建用户

‘用户名’：指定将创建的用户名

  • ‘来源地址’：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录可用通配符%

‘密码’：

  • 若使用明文密码，直接输入’密码’，插入到数据库时由Mysql自动加密；
  • 若使用加密密码，需要先使用SELECT PASSWORD(‘密码’); 获取密文，再在语句中添加 PASSWORD ‘密文’;
  • 若省略“IDENTIFIED BY”部分，则用户的密码将为空（不建议使用）

使用明文创建用户

 新建用户
 CREATE USER '用户名'@'来源地址' [ IDENTIFIED BY [PASSWORD] '密码']；

image.png

使用密文创建用户

 select password('123123');;    #获取密文
 #创建用户
 create user 'xixi'@'localhost' identified by '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1';
 #退出
 quit
 #验证登录
 mysql -u xixi -p123123

image.png

查看用户信息

  • 创建后的用户保存在 mysql 数据库的 user 表里

    use myaql;  #切换库
 #查看表结构
desc user;
#指定字段用户，登录认证字符串，登录主机，查看数据记录
select User,authentication_string,Host from user;

image.png

重命名用户

 rename user '原用户名'@'登录主机' to '新用户名'@'登录主机';

image.png

删除用户

 DROP USER '用户名'@'登陆主机'；

image.png

修改当前用户名密码

 SET PASSWORD = PASSWORD('123123');

image.png

修改其他用户密码

 set password for '用户名'@'登录主机'='password'（'新的密码'）

image.png

忘记 root 密码的解决办法

 #修改配置文件，添加配置，使登录mysql不使用授权表
 vim /etc/my.cnf
 ​
 #添加此行
 skip-grant-tables
 systemctl restart mysql
 mysql#登录数据库
 ​
 #使用update修改root密码，刷新数据库 
 update mysql.user set authentication_string = password('123456') where user='root';
 flush privileges; 
 #退出
 quit
 ​
 #编辑配置文件
 vim /etc/my.cnf
 #删除添加的配置
 skip-grant-tables
 #登录
 mysql -u root -p123456

临时修改配置文件，添加配置，使登录mysql暂时不需要使用授权表

image.png 重启服务，并尝试直接登录mysql

image.png 使用update修改root密码，刷新数据库

image.png 删除前面在/etc/my.cnf配置文件下添加的内容

image.png 使用新密码登录成功

image.png

总结

查看数据库和表：

  • show databases;
  • use 库名;
  • show tables;
  • desc 表名;
  • desc 表名\G
  • show create table 表名;

创建及删除数据库和表：

  • create database 库名;
  • create table 表名(字段1 数据类型 [属性], 字段2 .....);
  • DROP TABLE [数据库名.]表名;
  • DROP DATABASE 数据库名;

表数据的增删改查：

  • insert into 表名 values (所有字段的值);
  • insert into 表名 (指定字段名称) values(字段的值);
  • delete from 表名 [where ...];
  • truncate table 表名;     #删除表中所有数据。相当于格式化，速度很快
  • update 表名 set 字段=值[, 字段2=....] [where ...];
  • select 字段1[,字段2....] from 表名 [where ...];
  • select * from 表名;

修改表名和表结构：

  • alter table 表名 RENAME/ADD/CHANGE/DROP 字段名 数据类型 属性;
  • alter table 表名 ADD 键名(字段); //给指定字段添加键约束

注：零填充约束、非空约束、默认约束、自增长约束不能通过ADD方式添加，可以在建表时设置，或者CHANGE修改字段时添加。

主键和唯一键：

  • 主键：primary key
  • 唯一键：unique key

共同点：  字段的值都是唯一性，不允许有重复的值

不同点：

  • 一个表中 只能有1个主键，但是可以有多个唯一键
  • 主键字段中不允许有null值，唯一键是允许有null

添加主键字段的三种方法：

创建表时指定主键字段的两种方式：

  • create table 表名 (字段1 XXX,字段2 xxx,...,primary key(字段));
  • create table 表名 (字段1 XXX primary key, ....);

在现有表中添加主键：

  • alter table 表名 ADD primary key(字段);
avatar
文章
阅读
粉丝