数据库MySQL(带你零基础入门MySQL)

时间:2024-02-01 13:08:24


(一)认识数据库

什么是数据库?

数据库的英文单词:data base,简称DB

数据库实际上就是一个文件集合,是一个存储数据的仓库,本质就是一个文件系统,数据库是按照特定的格式把数据存储起来,用户可以对存储的数据进行增删改查操作。

(总而言之,数据库就是用于存储和管理数据的仓库。)

数据库分为两大类:

  • 关系型数据库:是建立在关系模型基础上的数据库。(比如:MySQL、Oracle、DB2、SQL Server)
  • 非关系型数据库(NO SQL):通常指数据之间无关系的数据库。(比如:monggodb、redis(键值对的方式存储、redis默认端口:6379))

MySQL简介

  • MySQL是一个关系型数据库管理系统,由瑞典MySQL AB公司开发。
  • 世界上最流行的几款数据库之一。
  • 优点:是一款轻量级数据库、免费、开源、适用于中大型网站。
  • MySQL默认端口号:3306。

数据库服务器、数据库和表的关系:

  • 所谓安装数据库服务器,只是在机器上装一个数据库管理系统(比如: MySQL),用来管理多个数据库,一般开发人员会针对每一个应用创建一个数据库。

  • 为保存应用中实体的数据,一般会在数据库创建多个表,以保存程序中实体的数据。

Xshell、Xftp、Navicat:

==Xshell或Xftp连接Linux服务器默认端口:22==

SQL介绍

Structured Query Language:结构化查询语言。

SQL是专门为数据库而建立的操作命令集,是一种功能齐全的数据库语言。在使用它时,只需要发出“做什么”命令,“怎么做”是不用使用者考虑的。

SQL语法特点

  1. 不区分大小写。
  2. 关键字、字段名、表名需要用空格或逗号隔开。
  3. 每一个SQL语句是用分号结尾。
  4. 语句可以写一行也可以分开写多行。

(二)数据库常用操作命令

1.MySQL登录

  1. mysql -uroot -p密码

  2. mysql -uroot -p(回车)
    Enter password: (输入密码)

  3. mysql -hip -P端口 -uroot -p

  4. mysql --host=ip --user=root --password=密码

2.MySQL退出

  1. exit

  2. quit

3.创建、查看、删除、使用数据库

  1. 创建数据库命令

    • 创建数据库:

      格式:create database [数据库名称];

      例如:create database chs;

    • 创建数据库,并指定字符集:

      格式:create database [数据库名称] character set [字符集名];

      例如:create database chs character set utf8;

    • 创建数据库,并指定字符集、排序规则:

      格式:create database [数据库名称] character set [字符集名] collate [排序规则];

      例如:create database chs character set utf8 collate utf8_general_ci;

  2. 显示数据库命令

    • 显示数据库:

      show databases;

    • 模糊查询数据库:

      show databases like \'%heshen%\';

    • 查看查询某个数据库的创建语句、字符集:

      格式:show create database [数据库名称];

      例如:show create database hesheng;

  3. 删除数据库命令

    格式:drop database [数据库名称];

    例如:drop database chs;

  4. 选中某个数据库

    格式:use [数据库名称];

    例如:use hesheng;

    PS:这个命令可以不加分号。

4.创建、查看、删除、修改数据表

数据在数据库中的存储方式:

image-20210115184201422

表中列的数据类型——数值型

类型 大小 范围(有符号) 范围(无符号) 用途
TINYINT 1字节 (-128,127) (0,255) 小整数值
SMALLINT 2字节 大整数值
MEDIUMINT 3字节 大整数值
INT或INTEGER 4字节 大整数值
BIGINT 8字节 极大整数值
FLOAT 4字节 单精度浮点数值
DOUBLE 8字节 双精度浮点数值
DECIMAL 对于DECIMAL(M,D),如果M>D则为M+2,否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

有符号和无符号(UNSIGNED)

在计算机中,可以区分正负的类型,称为有符号类型。

无正负的类型,称为无符号类型。

简单的理解为就是,有符号值可以表示负数、0、正数,无符号值只能为0或者正数。

FLOAT、DOUBLE、DECIMAL

FLOAT(10,2):总长度为10,小数点后有2位。

DOUBLE和DECIMAL也类似。

超出范围会四舍五入。

表中列的数据类型——字符型

  • CHAR:定长字符串。CHAR(4) -- \' d\'
  • VARCHAR:变长字符串。VARCHAR(4) -- \'d\'
  • CHAR的查询效率要高于VARCHAR。

表中列的数据类型——日期型

类型 大小(字节) 范围 格式 用途
DATE
TIME
YEAR
DATETIME
TIMESTAMP 1970-01-01 00:00:00 结束时间是第2147483647秒,北京时间2038-1-19 11:14:07,格林尼治时间2038年1月19日 凌晨03:14:07

TIMESTAMP和DATETIME的异同:

  • 相同点:两者都可以用来表示YYYY-MM-DD HH:MM:SS类型的日期。
  • 不同点:
    • 两者的存储方式不一样:
      • 对于TIMESTAMP,它把客户端插入的时间从当前时区转化为UTC(世界标准时间)进行存储。查询时,又将其转化为客户端当前时区进行返回。(PS:中国时区为+8区)
      • 而对于DATETIME,不做任何改变,基本上是原样输入和输出。
    • 两者所能存储的时间范围不一样:
      • TIMESTAMP所能存储的时间范围为:\'1970-01-01 00:00:01.000000\'到\'2038-01-19 03:14:07.999999\'
      • DATETIME所能存储的时间范围为:\'1000-01-01 00:00:00.000000\'到\'9999-12-31 23:59:59.999999\'

操作命令:

  1. 创建表

    1. 创建表基础命令

      -- 格式:
      CREATE TABLE [表名] ([列名 1 ] [列类型],[列名 2 ] [列类型],[列名 3 ] [列类型] );
      -- 例如:
      CREATE TABLE STU (ID INT,NAME VARCHAR ( 20 );AGE INT );
      

      注意:

      • 创建表前,要先选中某个数据库(use [数据库名称]);
      • 列和列之间用逗号隔开,列内用空格隔开;
      • 创建表时,要根据需保存的数据创建相应的列,并根据要存储数据的类型定义相应的列类型。
    2. 约束条件

      约束条件就是给列加一些约束,使该字段存储的值更加符合我们的预期。

      约束条件 含义
      UNSIGNED 无符号,值从0开始,无负数
      ZEROFILL 零填充,当数据的显示长度不够的时候可以使用填补0的效果填充至指定长度,字段会自动添加UNSIGNED
      NOT NULL 非空约束,表示该字段的值不能为空
      DEFAULT 表示如果插入数据时没有给该字段赋值,那么就使用默认值
      PRIMARY KEY 主键约束,表示唯一标识,不能为空,且一个表只能有一个主键
      AUTO_INCREMENT 自增长,只能用于数值列,默认起始值从1开始,每次增长1
      UNITQUE KEY 唯一值,表示该字段下的值不能重复,可以为空,可以有多个
      COMMENT 描述

      注意:

      如果一列同时有UNSIGNED、ZEROFILL、NOT NULL这几个约束,UNSIGNED、ZEROFILL必须在NOT NULL前面,否则会报错。

      例子:

      create table user_info_tab(
      user_id int not null auto_increment,
      user_name char(10),
      password varchar(10),
      user_nick varchar(10),
      card_num bigint,
      primary key(user_id)
      );
      
  2. 查看表

    1. 显示当前数据库中所有表的名字

      格式:show tables;

    2. 显示某张表每一列的属性(列名、数据类型、约束)

      格式:desc [数据表名称];

      例如:

      desc user_info_tab;
      
  3. 删除表

    格式:drop table [数据表名称];

    例如:

    drop table hesheng;
    
  4. 修改表

    1. 向数据表中添加一列

      格式:ALTER TABLE [数据表名称] ADD [列名] [列的数据格式] [约束];

      例如:

      ALTER TABLE user_info_tab ADD phone VARCHAR(11);
      

      PS:

      • 默认自动添加到数据表字段的末尾;

      • 如果要加在第一列在最后加个FIRST;

      • 如果要加在某一列的后面,在最后面加个AFTER某一列列名。

    2. 删除数据表的某一列

      格式:ALTER TABLE [数据表名称] DROP [列名];

    3. 修改列的类型和名称

      ALTER TABLE [表名] MODIFY [列名] [数据格式];(列名不变,其他要变)

      ALTER TABLE [表名] CHANGE [旧列名] [新列名] [数据格式];(列名也要改变)

5.数据表的增删改查

增(insert)

-- 插入数据
INSERT INTO user_info ( user_id, user_name, PASSWORD, user_nick, card_num )
VALUES
	( 1, \'zhangsan\', \'abc123\', \'zhangsanfeng\', 124567894651329785 ),
	( 2, \'lisi\', \'122bbb\', \'limochou\', 124567894651324567 ),
	( 3, \'wangwu\', \'123aaa\', \'wangbaiwan\', 214567894651324567 ),
	( 4, \'liuqi\', \'12aaa\', \'liuchuanfeng\', 214563356651324567 ),
	( 5, \'zhangliu\', \'12aaa\', \'zhangwuji\', 214563356658966567 );

删(delete)

语法:

  1. delete from 表名 where 条件
  2. delete语句不能删除某一列的值。(可以使用update 表名 set username = "" where userid = 1
  3. 使用delete语句仅删除符合where条件的行的数据,不删除表中其他行和表本身。
  4. truncate user_info_table(直接把数据清空掉)

drop和delete的区别❓(面试题)

  • drop是删除数据库、数据表、数据表中的某一列。
  • delete是删除某一行数据。

改(update)

语法:

  1. update 表名 set 列名=新值 where 列名=某值;
  2. update语法可以新增、更新原有表行中的各列。
  3. set子句指示要修改哪些列和要给予哪些值。
  4. where子句指定应更新哪些行。如果没有where子句,则更新所有的行。
update user_info set username = "poopoo" where userid = 1;

查(select)

具体详情查看后面实践

6.数据表的排序、聚合命令、分组

  1. 排序(order by)

    • 使用order by子句,对查询结果进行排序。

    • order by 指定排序的列 asc(升序)/desc(降序)。

    • order by 子句一般位于select语句的结尾。

    SELECT product_name,weight FROM products_info ORDER BY weight DESC;
    
  2. 聚合命令

    1. distinct:对某一列数据去重。

      语句:select distinct 列名 from 表名; -- 显示此列不重复的数据

    2. count:统计总行数。

      • count(*):包括所有列,返回表中的总行数,在统计结果的时候,不会忽略值为Null的行数。
      • count(1):包括所有列,1表示一个固定值,没有实际含义,在统计结果的时候,不会忽略列值为Null的行数,和count(*)的区别是执行效率不同。
      • count(列名):只包括列名指定列,返回指定列的行数,在统计结果的时候,不统计列值为Null,即列值为Null的行数不统计在内。
      • count(distinct 列名):返回指定列的不重复的行数,在统计结果的时候,会忽略列值为NULL的行数(不包括空字符和0),即列值为NULL的行数不统计在内。
      • count(*)、count(1)、count(列名)执行效率比较:
      1. 如果列为主键,count(列名)优于count(1)
      2. 如果列不为主键,count(1)优于count(列名)
      3. 如果表中存在主键,count(主键列名)效率最优
      4. 如果表中只有一列,则count(*)效率最优
      5. 如果表中有多列,且不存在主键,则count(1)效率优于count(*)
    3. MAX:最大值

    4. MIN:最小值

    5. AVG:平均值

    6. SUM:求和

      select max(列名) from 表名;
      select min(列名) from 表名;
      select avg(列名) from 表名;
      select sum(列名) from 表名;
      - 也可以跟where子句
      
    7. limit

      语法:

      • select * from 表名 limit m,n;

      • 其中m是指从哪行开始,m从0取值,0表示第一行。

      • n是指从第m+1条开始,取n条。

      • select * from 表名 limit 0,2(从第一行开始,显示两行结果)

      • 如果只给定一个参数,它表示返回最大的行数目:

        select * from table limit 5;查询前5行

      • limit n 等价于 limit 0,n

  3. 分组

    GROUP BY

    • 使用group by子句对列进行分组。

    • 然后还可以使用having子句过滤,having通常跟在group by后,它作用于组。

    • 不加having过滤:

      select 列名,聚合函数 from 表名 where 子句 group by 列名;

    • 加上having过滤:

      select 列名,聚合函数 from 表名 where 子句 group by 列名 having 聚合函数 过滤条件;

    • 注意:使用group by后只能展示分组的列名+聚合函数结果,因为其余列已经基于分组这一列合并。

    select sum(price), count(user_id), product_id from order_info_table group by product_id having count(user_id) > 2;
    

7.数据表的连接查询、子查询

  1. 两张表连接查询

    INNER JOIN(内连接):获取两个表中字段匹配关系的行的所有信息。

    语法:SELECT * FROM [表名] a INNER JOIN [表名] b ON a.[列名] = b.[列名];

    例如:

    SELECT * FROM user_info_table a INNER JOIN order_info_table b ON a.user_id = b.user_id;
    SELECT * FROM user_info_table a INNER JOIN order_info_table b ON a.user_id = b.user_id WHERE b.user_id IS NULL;
    

    LEFT JOIN(左连接):获取左表所有行的信息,即使右表没有对应匹配的行的信息。右表没有匹配的部分用NULL代替。

    语法:SELECT * FROM [表名] a LEFT JOIN [表名] b ON a.[列名] = b.[列名];

    SELECT * FROM products_info a LEFT JOIN suppliers_info b ON a.supplier_id = b.supplier_id;
    

    RIGHT JOIN(右连接):与左连接相反,用于获取右表所有记录,及时左表没有对应匹配的行的所有信息,左表没有匹配的部分用NULL代替。

    语法:SELECT * FROM [表名] a RIGHT JOIN [表名] b ON a.[列名] = b.[列名];

    SELECT * FROM products_info a RIGHT JOIN suppliers_info b ON a.supplier_id = b.supplier_id;
    
  2. 子查询(嵌套查询)

    • 嵌套在其他查询中的查询。
    • 语句:select 列名1 from 表1 where 列名2 in (select 列名2 from 表2 where 列名3 = 某某某);
    • 注意:一般在子查询中,程序先运行嵌套在最内层的语句,再运行外层。因此在写子查询语句时,可以先测试一下内层的子查询语句是否输出了想要的内容,再一层一层往外测试,增加子查询的正确率。