数据库基础

oddpro
327 阅读13分钟

mysql概述

数据库

image.png

表

image.png

mysql 基本操作

进入

image.png

查看所有库

show databases;

image.png

查看表

show tables; select * from table;select 关键词 代表查询 * 代表查询的列（字段）有哪些 *表示所有的列 from 关键字 表示从哪个表中查询 table 表示要查询的表 ; mysql默认语句结尾； image.png

查看表结构

dest table; show create table table\G;show 关键字 表示查看/显示 create 关键字 表示创建 table 关键字 表示表 task 查看的表 /G 格式化结果 image.png image.png

基本操作

使用方法

  • 方式一: 通过在命令行敲命令来操作 ( 有助于命令的掌握)
  • 方式二: 通过图型界面工具,如 Navicat 等(在熟练掌握后再使用)
  • 方式三：通过编程语言(python,php,java,go...)执行mysql命令

SQL ( Structure query language ) 结构化查询语言

  • SQL语言分为4个部分：DDL（定义）、DML（操作）、DQL（查询）、DCL（控制）

SQL语句中的快捷键

  • \G 格式化输出（文本式，竖立显示）
  • \s 查看服务器端信息
  • \c 结束命令输入操作
  • \q 退出当前sql命令行模式
  • \h 查看帮助

操作数据库步骤

命令行连接数据库

sudo mysql -u root -p -h 服务器地址 -u 登录账号 -p 回车后输入登录密码 -P（大写）端口号

SELCET * FROM task WHERE uid = 1; sql语句可以换行，要以分号结尾 命令不区分大小写，关键字和函数建议用大写 如果提示符'>'那么需要输入一个回车

数据库操作

查看数据库 show databases; 创建数据库create database 库名 default charset=utf8; 删除数据库 drop database 库名; 打开数据库 use 库名;

数据表操作

查看表: show tables; 创建表: create table 表名(字段名1 类型,字段名2 类型)engine=innodb default charset=utf8; 创建表: 如果表不存在,则创建, 如果存在就不执行这条命令 create table if not exists 表名(字段1 类型,字段2 类型);

 create table if not exists users( 
   id int not null primary key auto_increment, 
   name varchar(4) not null, 
   age tinyint, 
   sex enum('男','女') 
 )engine=innodb default charset=utf8;

删除表: drop table 表名; 表结构: desc 表名; 查看建表语句：show create table users;

数据操作 增删查改

插入
  • insert into 表名(字段1,字段2,字段3) values(值1,值2,值3);
  • insert into 表名(字段1,字段2,字段3) values(a值1,a值2,a值3),(b值1,b值2,b值3);

image.png

查询
  • select * from 表名;
  • select 字段1,字段2,字段3 from 表名;
  • select * from 表名 where 字段=某个值;

image.png

修改
  • update 表名 set 字段=某个值 where 条件;
  • update 表名 set 字段1=值1,字段2=值2 where 条件;
  • update 表名 set 字段=字段+值 where 条件;

image.png

删除
  • delete from 表名 where 字段=某个值;

image.png

mysql中的数据类型

数据类型是定义列中可以存储什么类型的数据以及该数据实际怎样存储的基本规则 数据类型限制存储在数据列列中的数据。例如，数值数据类型列只能接受数值类型的的数据 在设计表时，应该特别重视所用的数据类型。使用错误的数据类型可能会严重地影响应用程序的功能和性能。 更改包含数据的列不是一件小事（而且这样做可能会导致数据丢失）。 数据类型：整型、浮点型、字符串、日期等

字符串数据类型

不管使用何种形式的串数据类型，串值都必须括在引号内 有两种基本的串类型，分别为定长串和变长串

定长串 char

接受长度固定的字符串，其长度是在创建表时指定的。 定长列不允许存储多于指定长度字符的数据。 指定长度后，就会分配固定的存储空间用于存放数据

char(7) 不管实际插入多少字符，它都会占用7个字符位置

变长串 varchar

存储可变长度的字符串 varchar(7) 如果实际插入4个字符, 那么它只占4个字符位置,当然插入的数据长度不能超过7 个字符 既然变长数据类型这样灵活，为什么还要使用定长数据类型？ 回答：因为性能，MySQL处理定长列远比处理变长列快得多。

数值类型

decimal(5, 2) 表示数值总共5位, 小数占2位 tinyint 1字节(8位) 无符号0-255。有符号-128-127 int 4字节。0-42亿 ， -21亿，21亿。 MySQL中没有专门存储货币的数据类型，一般情况下使用DECIMAL(8, 2)

无符号与有符号数

所有数值数据类型（除BIT和BOOLEAN外）都可以有符号或无符号 有符号数值列可以存储正或负的数值 无符号数值列只能存储正数。 默认情况为有符号，但如果你知道自己不需要存储负值，可以使用UNSIGNED关键字

日期和时间类型

MySQL中的类型约束和运算

字段约束

1）unsigned 无符号(给数值类型使用，表示为正数，不写可以表示正负数都可以) 2）字段类型后面加括号限制宽度

  • char(5). varchar(7) 在字符类型后面加限制 表示 字符串的长度
  • int(4) 没有意义，默认无符号的int为int(11)，有符号的int(10)
  • int(4) unsigned zerofill只有当给int类型设置有前导零时，设置int的宽度才有意义。

3）not null 不能为空，在操作数据库时如果输入该字段的数据为NULL ，就会报错 4）default 设置默认值 5）primary key 主键不能为空,且唯一.一般和自动递增一起配合使用。 6）auto_increment 定义列为自增属性，一般用于主键，数值会自动加1 7）unique 唯一索引(数据不能重复:用户名)可以增加查询速度,但是会降低插入和更新速度

主键

1）表中每一行都应该有可以唯一标识自己的一列，用于记录两条记录不能重复，任意两行都不具有相同的主键值 2）应该总是定义主键 虽然并不总是都需要主键，但大多数数据库设计人员都应保证他们创建的每个表具有一个主键，以便于以后的数据操纵和管理。 要求

  • 记录一旦插入到表中，主键最好不要再修改
  • 不允许NULL
  • 不在主键列中使用可能会更改的值。 （例如，如果使用一个名字作为主键以标识某个供应商，当该供应商合并和更改其名字时，必须更改这个主键。）
  • 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键 重复，也不用自己预先生成主键
  • 可以使用多个列作为联合主键，但联合主键并不常用。使用多列作为主键时，所有列值的组合必须是唯一的

MySQL运算符

MySQL的运算符

  • 算术运算符： +、 -、 *、 /、 %
  • 比较运算符： =、 >、 <、 >=、 <=、!=
  • 数据库特有的比较： in、not in、is null、is not null、like、between、and
  • 逻辑运算符： and、or、not
  • like: 支持特殊符号%和_ ;

其中%表示任意数量的任意字符，_表示任意一位字符

数据表操作详解

创建表

语法格式： create table 表名(字段名，类型，【字段约束】，。。。)； 实例：

 # 以下创建一个 users 的表 
 create table users( 
   # 创建ID字段，为正整数，不允许为空 主键，自动递增 
   id int unsigned not null primary key auto_increment, 
 ​
   # 创建 存储 名字的字段，为字符串类型，最大长度 5个字符，不允许为空 
   username varchar(5) not null, 
 ​
   # 创建存储 密码 的字段，固定长度 32位字符， 不允许为空 
   # 数据库在存储用户密码时通常存储的是加密后的密码 使用md5加密算法后 密码均为32位 所以这里固定长度为32
   password char(32) not null, 
 ​
   # 创建 年龄 字段，不允许为空，默认值为 20 
   age tinyint not null default 20 
 ​
 )engine=innodb default charset=utf8; 
 ​
 # 查看表结构 
 desc users； 
 ​
 #查看建表语句 
 show create table users;

创建表的基本原则：

  • 表名和字段名 尽可能的符合命名规范，并且最好能够‘见名之意’
  • 表中数据必须有唯一标示，即主键定义。无特殊情况，主键都为数字并自增即可
  • 表中字段所对应的类型设置合理，并限制合理长度
  • 表引擎推荐使用innodb，并无特殊情况都要求为utf8或者utf8mb4的字符编码

修改表结构

语法格式：alter table 表名 action (更改的选项)

 # 语法：alter table 表名 add 添加的字段信息 
 # 在 users 表中 追加 一个 num 字段 
 alter table users add num int not null; 
 ​
 # 在指定字段后面追加字段 在 users 表中 age字段后面 添加一个 email 字段 
 # 由于数据一开始没有email字段 添加后会设置默认值 空字符串
 alter table users add email varchar(50) after age; 
 ​
 # 在指定字段后面追加字段，在 users 表中 age字段后面 添加一个 phone 
 alter table users add phone char(11) not null after age; 
 ​
 # 在表的最前面添加一个字段 
 alter table users add aa int first;
 ​

删除字段

 # 删除字段 alter table 表名 drop 被删除的字段名 
 alter table users drop aa;

修改字段 语法格式： alter table 表名 change | modify 被修改的字段信息

  • change: 可以修改字段名，
  • modify: 不能修改字段名。 alter table users modify num tinyint not null default 12;

修改表中的 num 字段 类型，使用 modify 不修改表名

修改表中的 num 字段 为 int并且字段名为 nn

alter table users change num nn int;

注意：一般情况下，无特殊要求，不要轻易修改表结构

修改表名

语法：alter table 原表名 rename as 新表名

添加外键

ALTER TABLE <数据表名> ADD CONSTRAINT <索引名> FOREIGN KEY (<列名>)  REFERENCES <主表名>(<列名>);

更改表中自增的值

alter table users auto_increment = 1000;

修改表引擎

删除表

drop table 表名

关键字

select 检索

检索单个列 select name from stu; 检索多个列 select name , uid from stu; 检索所有列 select * from stu; 检索不同的列 select distinct class_id from stu

limit 限制

select * from stu limit 3,4;

  • 如果数据量到达千万级别，一次select * 很可能会使服务器宕机，这时就需要使用limit控制一次取出的数据数量；
  • LIMIT 3, 4的含义是从行3开始的4行.(跳过前3行,取4行)
  • 替代语法LIMIT 4 OFFSET 3意为从行3开始取4行，就像LIMIT 3,4一样。

使用完全限定的表名

select stu.name from stu; 主要用于多表查询时，限定字段是从哪个表中取出的数据；

order by 排序数据

select * from stu order by age;

  • 在需要对多列数据进行排序时,使用逗号分隔列名,井会按照前后顺序依次对比排序
  • order by的排序默认升序,可以使用DESC设置降序排列select * from stu order by classid, age DESC;
按多列进行排序

select * from stu order by classid, age;

降序

select * from stu order by classid, age DESC;

注意

ORDER BY子句的位置，使用子句的次序不对将产生错误消息；

  • FROM子句位于ORDER BY子句之前。
  • LIMIT必须位于ORDER BY之后。

where 条件过滤

select name from stu where age = 22;

限定边界

select name from stu where age > 20 and age < 26;

使用is null

mysql * from stu where sex = NULL;

AND 与 OR 操作符

and > or select name from stu where (class_id=18 or class_id =19) and sex='m'

IN 与 NOT

IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。

IN WHERE子句中用来指定要匹配值的清单的关键字，功能与OR相当

select name from stu where class_id in (1,2) 为什么要使用IN操作符?其优点具体如下。

  • 在使用长的合法选项清单时，IN操作符的语法更清楚且更直观。
  • 在使用IN时，计算的次序更容易管理(因为使用的操作符更少)。
  • IN操作符一般比OR操作符清单执行更快。
  • IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。

NOT WHERE子句中用来否定后跟条件的关键字

select name from stu where class_id not in (18,19)

  • 为什么使用NOT?对于简单的WHERE子句，使用NOT确实没有什么优势。
  • 但在更复杂的子句中，NOT是非常有用的。
  • 例如，在与IN操作符联合使用时，NOT使找出与条件列表不匹配的行非常简单。

Like与通配符

字段计算

简介

存储在数据库表中的数据一般不是应用程序所需要的格式。下面举几个例子

  • 如果想在一个字段中既显示用户名，又显示班级号，但这两个信息一般包含在不同的表列中。
  • 同时姓名、手机号和地址存储在不同的列中(应该这样)，但快递单打印程序却需要把它们作为一个恰当格式的字段检索出来。
  • 列数据是大小写混合的，但报表程序需要把所有数据按大写表示出来。
  • 物品订单表存储物品的价格和数量，但不需要存储每个物品的总价格(用价格乘以数量即可)。为打印发票，需要物品的总价格。
  • 需要根据表数据进行总数、平均数计算或其他计算

计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的

字段拼接concat

stu表包含用户名和手机号码信息。假如要生成一个学生报表， 需要在学生的名字中按照name(phone)这样的格式列出。 解决办法是把两个列拼接起来。在MySQL的SELECT语句中，可使用concat()函数来拼接两个列

select concat(name, '(', phone')') from stu

使用别名AS

SELECT语句拼接字段可以完成。但此新计算列的名字是什么呢?实际上它没有名字，它只是一个值。 如果仅在SQL查询工具中查看一下结果， 这样没有什么不好。 但是，一个未命名的列不能用于客户机应用中，因为客户机没有办法引用它。 为了解决这个问题，SQL支持列别名。别名(alias) 是一个字段或值的替换名。别名用AS关键字赋予 select concat(name, '(', phone')') from stu as name_phone from stu

关于外键约束参考：juejin.cn/post/696584…

参考自：juejin.cn/post/697901…

avatar
文章
阅读
粉丝