0042 MySQL学习笔记-入门--01

2022-10-15 14:50:06

基本概念：

数据库DB(database)：
- 数据的仓库，数据的集合，是数据的一种结构化的存储
数据库管理系统DBMS(database management system)：
- 管理数据库的一套软件
- 比如Oracle、MySQL、SQL server、DB2
- 介于应用程序和操作系统之间
数据库应用程序：
- 使用数据库应用程序与DBMS进行通信，访问和管理数据库中的数据
数据库服务器：
- 计算机+数据库管理系统
- 一个数据库服务器可以管理多个数据库，一个数据库下包含多个表
SQL语言
- 全称：Structured Query Language，结构化查询语言
- 由IBM在70年代开发出来，80年代被确定为关系型数据库语言的标准
- 主要包含，对数据的定义、操作、查询、控制，四大类操作
常见的数据库产品
- Oracle公司的Oracle
- 微软公司的SQL Server
- IBM公司的DB2
- 10gen公司的MongoDB：
- Oracle公司的MySQL，早期为瑞典的MySQL AB公司开发
数据库引擎：
- 用于存储、处理、保护数据的核心服务
- 参见：mysql 数据库引擎
- MySQL主要有MyISAM和InnoDB两种：参见：MySQL存储引擎MyISAM与InnoDB的主要区别对比

SQL语言

数据定义语言：DDL(Data Definition Language)
- 主要进行对数据库、表的定义，比如CREATE、ALTE、RDROP等操作
数据操作语言：DML(Data Manipulation Language)
- 向数据库中添加、删除、修改数据，比如INSERT、UPDATE、DELETE等操作
数据查询语言：DQL(Data Query Language)
- 从数据库中查询数据，主要是SELECT操作
- 用得最多的操作
数据控制语言：DCL(Data Control Language)
- 用于控制用户对数据库的访问权限，比如GRANT、REVOKE、COMMIT、ROLLBACK等操作
有一个标准的SQL语言（当前最新版本是SQL:2016），在各家DBMS上通用，但各个DBMS又对标准的SQL进行了扩展，学习的时候要注意

MySQL安装、初步使用的注意事项

版本的下载：
- 下载地址：Download MySQL Community Server：http://dev.mysql.com/downloads/mysql/
- 有msi（只有个32位的，64位的机子也装这个）和Zip（32位和64位）两种，都是可以的
- 下载的时候，注意左下角『No thanks, just start my download.』不用注册登录也能下载
安装方式：
- 典型安装Typical：只包含服务器、命令行客户端、命令行使用程序
- 定制安装Custom：
- 完全安装Complete：各种组件全部安装
配置：
- 包括详细配置和标准配置
- 详细配置：
- 服务器类型：会影响到对内存、磁盘、过程等的决策
  - 开发者类型：适用于开发者，内存占用少
  - 服务器类型：专门用提供服务
  - 专用MySQL服务器：该机器专门用来作为MySQL的服务器，资源开销最大
- 数据库用途：
  - 多功能数据库：同时使用InnoDB和MyISAM引擎
  - 事务处理数据库：同时使用InnoDB和MyISAM引擎，但InnoDB能分配到更多的资源
  - 非事务处理数据库：只是用MyISAM引擎
- 最大并发连接数
  - 决策支持(Decision Support)：不需要大量的并发连接
  - 联机事务处理(Online Transaction Processing)：需要大量的并发连接，最大500
  - 人工设置(Manual Setting)：默认连接数15
- 默认端口3306
- 字符集：
  - 标准字符集(Standard Character Set)：支持西欧语言
  - 支持多语言(Best Support For Multilingualism)：UTF-8字符集
  - 人工选择(Manual Selected Default Character Set/Collection)：自己选择，GBK；GB2312；UTF-8
配置文件：
- my.ini：正在使用的配置文件，一定会被读取
- 其他：my-huge/large/medium/small/template/innodb-heavy-4G.ini等可能会被读取到
缺少根元素
- 删除：C:\Users\用户名\AppData\Roaming\Oracle\MySQL Notifier下的配置文件

数据库的基本操作

# 命令行下登录到数据库：开始菜单中找到“MySQL 5.7 Command Line Client” 打开

# 输入root用户的密码，即可登录

# 在MySQL workbench中操作更加方便

# MySQL Workbench中建立连接，输入connection name; connection method; hostname port; username password等，下次直接点击这个连接就能连上数据库

# 在建立好的连接上面点击右键，“Copy JDBC Connection String to Clipboard”，可以用在将来的jdbc编程中

# 点击该连接，连接上数据库

SHOW DATABSASES;                           # 查看有哪些数据库。系统默认有information_scheme;mysql;performance_scheme;sys四个；另有sakila和world两个示例库

CREATE DATABASE newDB;                     # 创建一个数据库，名叫newDB

SHOW CREATE DATABASE newDB;                # 查看数据库newDB的信息，可以看到是Character Set UTF-8

ALTER DATABASE newDB DEFAULT CHARACTER SET gbk COLLATE gbk_bin;   # 将newDB的默认字符集改为gbk

DROP DATABASE newDB;                       # 删除newDB这个数据库

MySQL的数据类型

整数类型：
- TINYINT：1个字节；0～2⁸-1；-2⁷～2⁷-1；
- SMALLINT：2个字节；0～2¹⁶-1；-2¹⁵～2¹⁵-1；
- MEDIUMINT：3个字节；0～2²⁴-1；-2²³～2²³-1；
- INT：4个字节；0～2³²-1；-2³¹～2³¹-1；
- BIGINT：8个字节；0～2⁶⁴-1；-2⁶³～2⁶³-1；
浮点数与定点数
- FLOAT：4个字节；
- DOUBLE：8个子ie；
- DECIMAL(M,D)；M+2个字节；M表示总长度，D表示小数点后的长度
日期与时间
- YEAR：
  - 1个字节；1901～2155；YYYY；0000
  - 四位字符串或数字：'2016'和2016皆可；
  - 两位字符串：'16'表示2016；'69'表示2069；'70'表示1970；'99'表示1999；'00'表示2000
  - 两位数字：1表示2001；69表示2069；70表示1970；99表示1999；0表示0000
- DATE：
  - 4个字节；1000-01-01～9999-12-03；YYYY-MM-DD；0000-00-00
  - 完整字符串'2016-12-16'和'20161216'都表示2016年12月16日
  - 年简写字符串：'16-12-16'和'161216'都表示2016年12月16日
  - 年简写数字：161216和12-12-16，都表示2016年12月16日
  - 函数与常量：NOW()和CURRENT_DATE都表示当前系统日期
- TIME：
  - 3个字节；-838:59:59～838:59:59；HH:MM:SS；00-00-00
  - 'D HH:MM:SS'：D表示天数，可以是[0,34]；比如'3 12:24:36'表示84:24:36
  - 'HHMMSS'或者HHMMSS：295416表示29:54:16
  - 常量与函数：CURRENT_TIME和NOW()表示当前系统时间
- DATETIME：8个字节；1000-01-01 00:00:00～9999-12-31 23:59:59；YYYY-MM-DD HH:MM:SS；0~0
- TIMESTAMP：
  - 4个字节；1970-01-01 00:00:01～2038-01-19 03:14:07；YYYY-MM-DD HH:MM:SS；0~0
  - 与DATETIME相同，只是取值范围更小
  - CURRENT_TIMESTAMP和NULL和无输入，都表示当前系统时间
- 如果插入的数据非法，那么会把零值插入
字符串与二进制
- CHAR：
  - 固定长度字符串：CHAR(4)表示最大可存储4个字节，并且始终占用4个字节
- VARCHAR
  - 可变长度字符串：VARCHAR(4)表示最大可存储4个字节，实际占用实际的字节数+1，实际最大可占用5个字节
- BINARY
  - 固定长度二进制数据；实际长度不足时，用\0补齐
- VARBINARY
  - 可变长度二进制数据；
- BLOB
  - 数据量很大的二进制数据，大小与下面的TEXT相同
  - 按照二进制编码进行比较和排序；
  - TINYBLOB：
  - BLOB：
  - MEDIUMBLOB：
  - LONGBLOB：
- TEXT
  - 表示大文本数据，如文章评论和内容等
  - 按照文本模式进行比较和排序
  - TINYTEXT：[0,255]个字节；0.25K
  - TEXT：[0,65535]个字节；64K
  - MEDIUMTEXT：[0,16777215]个字节；16M
  - LONGTEXT：[0,4294967295]个字节；4096M
- ENUM
  - 枚举类型；定义一个枚举类型：ENUM('枚举值1','枚举值2','枚举值3','枚举值4','枚举值5')
  - 存入数据库的实际是这个枚举值的编号，而不是值本身
- SET
  - 与ENUM类似，表示的是字符串对象；SET('值1','值2','值3','值4','值5')
  - 存入数据库的也是其编号，而不是字符串本身
- BIT
  - 表示二进制数据；定义BIT(M)，M表示值的位数，[1,64]
  - 如果长度小于M，那么左边用0补齐

约束

约束：约束就是一个条件，表示被约束的数据应当满足约束条件
为什么有约束：因为为了后续的各种计算和操作，数据库中的数据不能是杂乱无章的，要符合一定的规则
常见约束：
- 主键约束：PRIMARY KEY
- 外键约束：FOREIGN KEY
- 非空约束：NOT NULL
- 唯一性约束：UNIQUE
- 默认值约束：DEFAULT
主键约束PRIMARY KEY
- 用主键约束的列的数据可以唯一的表示这一条记录(这一行数据)
- 主键约束的数据不能重复，不能为空，每张表只能有一个主键约束
- 是否可重用，得看具体的数据库
- 单字段主键：
  - 主键约束在一个字段上，这个字段的值唯一标识一条记录
- 多字段主键：
  - 主键约束在多个字段上，这几个字的的值拼接起来唯一标识一条记录
外键约束FOREIGN KEY
非空约束
- 非空约束的字段的值不能为空，必须填写
唯一约束UNIQUE
- 唯一约束的字读的值不能重复
默认约束DEFAULT
- 默认约束的字段，如果没有插入数据，那么会自动插入默认值

表的基本操作

USE newDB;                                 # 进入newDB数据库，后续的操作都在这个数据库中

CREATE TABLE teacher(                      # 创建一个名为teacher的表

	tea_id TINYINT PRIMARY KEY,            # 教师编码，主键约束，唯一且非空

    tea_ID_num CHAR(18),                   # 身份证号

	tea_name VARCHAR(10),                  # 姓名

	tea_gender ENUM('男','女'),             # 性别

    tea_birth DATE                         # 出生日期

);

SHOW TABLES;                               # 查看这个数据库下的表

SHOW CREATE TABLE teacher;                 # 查看teacher这个表

DESCRIBE teacher;                          # 查看teacher这个表的字段信息

DESC teacher;                              # DESCRIBE的简写

ALTER TABLE teacher RENAME TO tea;         # 修改表名：teacher--->tea

DESC tea;

ALTER TABLE tea CHANGE tea_gender tea_sex ENUM('男','女');     # 修改字段名：tea_gender--->tea_sex，数据类型为ENUM('男','女')，注意数据类型不可省略，即使一样也不可省略

ALTER TABLE tea MODIFY tea_sex ENUM('男','女','其他');         # 修改数据类型：tea_sex的新数据类型为ENUM('男','女','其他')

ALTER TABLE tea DROP tea_birth;                              # 删除字段：删除tea_birth字段

ALTER TABLE tea ADD tea_birth DATE NOT NULL FIRST;           # 添加字段：添加tea_birth字段，非空，位置为first，就是第一个字段，(也可以是是AFTER 字段名)，

ALTER TABLE tea MODIFY tea_birth DATE AFTER tea_sex;         # 修改字段位置：修改tea表下的tea_birth字段的位置为tea_sex之后

DROP TABLE tea;                                              # 删除数据表。特别小心，表跟表之间往往存在关联

CREATE TABLE teacher(                                        # 新建一张表

	tea_id TINYINT PRIMARY KEY AUTO_INCREMENT,  # tea_id字段为主键，且自增(AUTO_INCREMENT，适合于任何整数类型)，即每添加一条记录，这个字段的值就增加1

    tea_ID_num CHAR(18),

	tea_name VARCHAR(10),

	tea_gender ENUM('男','女','其他'),

    tea_birth DATE

);

CREATE TABLE teacher(

	tea_id TINYINT,

    tea_ID_num CHAR(18),

	tea_name VARCHAR(10),

	tea_gender ENUM('男','女','其他'),

    tea_birth DATE,

    PRIMARY KEY(tea_id,tea_ID_num)                # 设置多字段主键

);

添加、更新、删除数据

CREATE TABLE teacher(                        # 创建个teacher表

	tea_id TINYINT,

    tea_ID_num CHAR(18),

	tea_name VARCHAR(10),

	tea_gender ENUM('男','女','其他'),

    tea_birth DATE,

    PRIMARY KEY(tea_id)                       # tea_id为主键

);

INSERT INTO teacher(tea_id,tea_ID_num,tea_birth,tea_name,tea_gender)

			values(1,'102315198102215425','1981-02-21','王洪','女');  # 添加一条记录，值的顺序跟字段名的顺序匹配，字段名的顺序跟表里面的字段顺序无关

INSERT INTO teacher VALUES(2,'102315198503136216','李刚','男','1985-03-13');  # 添加一条记录：省略字段名，值的顺序跟表中字段的顺序一致

INSERT INTO teacher(tea_id,tea_name,tea_gender)

			VALUES (3,'张华','男');                       # 添加一条记录，只给部分字段添加，被忽略的字段，不能有非空约束或者得有默认值

INSERT INTO teacher VALUES(4,'102315197601213162','王和','女','1976-01-21'),

						  (5,'102315198205168435','阿Q','男','1982-05-16');

						  # 一次添加两条记录，省略了字段名

INSERT INTO teacher

		SET tea_id=6,                   # 字段=值的形式

            tea_name='李飞',

            tea_gender='男';

SELECT * FROM teacher;                  # 查询teacher中的所有的记录

UPDATE teacher                          # 更新一条信息

	SET tea_ID_num='102315197001207511',tea_birth='19700120'

    WHERE tea_id=3;                     # 特别注意，不能少了WHREE字句

UPDATE teacher

	SET tea_gender=null                 # 删除一条记录某个字段的值，可将其UPDATE为null

    WHERE tea_id=1;

DELETE FROM teacher WHERE tea_id=1;     # 删除符合特定条件的记录：使用DELETE的时候，对条件的限定千万不能少，否则可能会删除整张表的记录

UPDATE teacher

	SET tea_gender='其他';              # 没有限定条件，会导致tea_teacher的值全部改为'其他'

DELETE FROM teacher;                    # 删除teacher表的所有记录

TRUNCATE TABLE teacher;                 # 清空teacher表

DELETE与TRUNCATE的区别

二者都能将整个表的数据全部删除
DELETE是DML语句；TRUNCATE是DDL语句
DELETE后面一般要跟WHERE字句来限定要删除的记录，TRUNCATE则不能跟WHERE字句，而是删除整张表的记录
对于有AUTO_INCREMENT约束的字段，DELETE后，从原来的记录的值开始自增；TRUNCATE则重新从1开始

简易的单表查询

SELECT tea_name,tea_gender FROM teacher;                 # 查询2个字段

SELECT tea_id,

	   tea_ID_num,

       tea_birth,

       tea_name,

       tea_gender FROM teacher;                          # 查询5个字段，结果按照这个顺序排列

SELECT * FROM teacher;                                   # 用通配符查询所有字段。注意效率较低

SELECT * FROM teacher WHERE tea_id>3;                    # 单条件查询所有字段

SELECT * FROM teacher WHERE tea_id BETWEEN 2 AND 6;      # BETWEEN X AND Y。[X,Y]且X<Y

SELECT * FROM teacher WHERE tea_id NOT BETWEEN 2 AND 5;  # 对BETWEEN AND 取反

SELECT * FROM teacher WHERE tea_id IN (1,3,4);           # IN查询，相等匹配多个值

SELECT * FROM teacher WHERE tea_id_num IS NULL;          # 匹配NULL

SELECT * FROM teacher WHERE tea_id_num IS NOT NULL;      # 匹配NOT NULL

SELECT DISTINCT tea_gender FROM teacher;                 # 查询结果去重

SELECT DISTINCT tea_name,tea_gender FROM teacher;        # DISTINCT可用于多个字段

SELECT * FROM teacher WHERE tea_name LIKE '王_';         # LIKE模煳匹配，下划线匹配单个字符，不包括空字符串，用\转义

SELECT * FROM teacher WHERE tea_ID_num LIKE '102315197%';  # %匹配若干个字符，包括空字符串，用\转义

SELECT * FROM teacher WHERE tea_id>2 AND tea_id<6;       # AND连接多个条件，交集

SELECT * FROM teacher WHERE tea_id>3 OR tea_birth>'1982-01-01';  # OR连接两个条件，并集

SELECT * FROM teacher WHERE tea_name LIKE '李_' AND tea_gender='男';

SELECT * FROM teacher WHERE

		tea_id<5 AND

		tea_name LIKE '王_' AND

		tea_birth>'1980-01-01';                          # AND连接3个条件

SELECT * FROM teacher WHERE

		tea_name LIKE '李_' AND tea_birth>'1980-01-01'   # AND 与 OR联用，AND优先级高于OR

        OR

        tea_name LIKE '王_' AND tea_birth<'1980-01-01';

码农公寓

基本概念：

SQL语言

MySQL安装、初步使用的注意事项

数据库的基本操作

MySQL的数据类型

约束

表的基本操作

添加、更新、删除数据

DELETE与TRUNCATE的区别

简易的单表查询

相关文章