yuiyake

MySQL基础

6/14/2021

# MySQL期末复习

# 数据库概述

# 一、概念

  • 数据库是指长期存储在计算机内，有组织、可共享给的数据集合。

# 数据库特点

  • 实现了数据独立性
  • 实现了数据共享
  • 减少数据冗余度
  • 实现了数据的集中控制
  • 维护了数据完整性
  • 提高数据可恢复性

# 数据库类型

  • 层次模型
  • 网状模型
  • 关系模型，就是管理关系型数据库，并将数据组织为相关的行和列的系统

# 数据模型

  • 概念：对现实世界数据的模拟和抽象
  • 类型：概念、逻辑、物理数据模型
  • 三要素： 数据结构、数据操作、完整性约束条件

# 数据库系统（DBS）构成

  • 硬件
  • 软件
  • 数据库
  • 用户

# MySQL优势

  1. 速度快
  2. 连接性和安全性
  3. 可移植性
  4. 支持SQL语言
  5. 成本优势

# 二、安装

# MySQL服务的基本操作

  • 启动sql： cmd打开services.msc，在里面输入net start mysql
  • 停止sql： cmd打开services.msc，在里面输入net stop mysql
  • 登录sql： mysql -h localhost -u root -p
  • 退出sql：1. exit 2. quit 3. \q
  • foo

# 三、数据库基本操作

# 数据库构成

  • 数据库可看作存储数据库对象的容器
  • 两类，系统数据库和用户数据库
  • 数据库对象是指存储、管理和使用数据的不同结构形式，主要包括表、索引、视图、 缺省值、规则、触发器、存储过程和函数等

# 数据库相关命令

// 创建
CREATE DATABASE database_name;  //database_name是数据库名字

// 查看
SHOW DATABASES

// 选择
USE database_name

// 删除
DROP DATABASE db_shop
1
2
3
4
5
6
7
8
9
10
11

# 四、存储引擎、数据类型和字符集

# 存储引擎

  • 命令
//查看默认引擎
SHOW VARIBALES LIKE 'default_storage_engine';

// 修改默认引擎
dafault_storage_engine = MyISAM
1
2
3
4
5
  • 常用的存储引擎及特点
    • InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行）， 要求实现高并发控制（比如售票），数据频繁更新，那选择InnoDB有很大的优势。

    • MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高 效率。如果应用的完整性、并发性要求比较低，也可以使用

    • MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低， 数据更新不频繁，存活周期不长 可以选择MEMOEY。

# 数据类型

  • mysql提供的数据类型：数值类型，日期和时间类型，字符串类型，JSON类型

  • 数值类型， 包括整数类型(TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT)，浮点数类型(FLOAT,DOUBLE)，定点数类型(DECIMAL)
    • 保证精度用decimal，占用字节M+2
    • 浮点数和定点数都可以用(M,D)定义。M是表示数值的位数（整+小），D为标度，就是小数点后的位数
    • 浮点数可定点数的区别：定点表示法运算直观，但数的表示范围较小，不同的数运算时要考虑比例因子的选取，以防止溢出。浮点表示法运算时可以不考虑溢出， 但浮点运算，编程较难。要掌握定、浮点数的转换方法及浮点数规格化方法。

  • 日期和时间类型，有TEAR,DATE,TIME,DATETIME,
    • timestamp 和 datetime的区别是，datetime的数据是定值，timestamp的时间可以根据时区转换
    • 2017/10/10，2017.10.10，2017#10#10都会被转换成2017-10-10
    • 当数据为TIME类型的缩写值时，如果没有分隔符，MySQL会认为值的最右边两位表示秒，例如，’1010’会被转换成00:10:10；如果有分隔符， MySQL会认为值的最左边两位表示小时，例如，’10:10’会被转换为10:10:00。

  • 字符串，有CHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM,SET等
    • varchar和char：都储存较短的字符串，区别是存储方式不同。char长度是固定的，varchar创建时括号里的数字是允许的最大长度
    • BINARY和VARBINARY：存储较短的二进制字符串，BINARY长度是固定的，当数据长度不足时，系统将自动在它的右边填充“\0”，直至指定长度。 VARBINARY是可变的，不会补充\0.

# 字符集

  • 四个级别：服务器级，数据库级，数据表级，字段级
  • 常用的字符集：latin1，GBK和UTF-8
  • 字符集包括字符集和排序规则两个概念
  • 字符集的选择：
    • 如果数据库要处理不同地区的文字，就应选择Unicode字符集，在MySQL中，就是utf8。
    • 如果数据库只需要支持一般中文，并且数据量很大，性能要求很高，可以使用GBK字符集。
    • 如果数据库需要做大量运算，就应该选择定长字符集，如latin1和GBK等。
  • 查看默认字符集（Latin1）
SHOW VARIABLES LIKE 'character_set_server';
1

# 五、数据表基本操作

# 创建数据表

  • 基本语句
USE db_shop;
CREATE TABLE goods;
1
2



  • 约束
  1. 主键约束 PRIMARY KEY
  2. 自增约束 AUTO_INCREMENT
  3. 非空约束 NOT NULL
  4. 唯一性约束 UNIQUE
  5. 无符号约束 UNSIGNED
  6. 默认约束 DEFAULT xxx
  7. 外键约束
// 用法像这样
CREATE TABLE goods(
id INT(11) PRIMARY KEY
);
1
2
3
4

# 表相关操作

// 查看表结构
DESCRIBE table_name;

// 查看建表语句
SHOW CREATE TABLE table_name \G;

// 修改表名
ALTER TABLE old_table RENAME new_table;

// 修改字段名
ALTER TABLE table_name CHANGE old_name new_name VARCHAR(30);

// 修改字段类型
ALTER TABLE tableName 
ALTER COLUMN xxx TYPE 要修改成的类型;

// 修改数据类型
ALTER TABLE tableName
ALTER COLUMN fName nvarchar(30) null;

// 添加字段名(在字段的最后一列)
ALTER TABLE table_name ADD xxx INT(10);

// 添加字段名(在指定列后)
ALTER TABLE table_name ADD xxx INT(10) AFTER xxx;

// 删除字段
ALTER TABLE table_name DROP xxx;

// 修改表顺序
ALTER TABLE table_name MODIFY xxx1 INT(10) AFTER/FIRST xxx2;

// 修改表的存储引擎
ALTER TABLE table_name ENGINE=InnoDB;

// 删除表
DROP TABLE IF EXISTS xxx;

// 删除列
ALTER TABLE tableName DROP COLUMN colName; 

// 增加列
ALTER TABLE tableName ADD COLUMN colName;

// 删除表中主键
ALTER TABLE tableName DROP PRIMARY KEY;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

# 六、数据的插入修改和删除

# 插入数据

INSERT INTO tableName(id, type, name) VALUES(1, '书籍', '西游记');

// 将其他表中的数据插入到表中(table1是本表，table2是其它表)
INSERT INTO table1(id INT(11) parmary key, ...)
SELECT id, type, ... FROM table2;

// 设置自增键其实质
ALTER TABLE tableName
ADD id int identity;
1
2
3
4
5
6
7
8
9
10

# 修改语句

// 修改所有
UPDATE table SET num=0, type='xxx',...;

// 修改指定
UPDATE table SET num=0 WHERE type='xxx';
1
2
3
4
5

# 删除语句

// 删除所有
DELETE FROM table；

// 删除指定
DELETE FROM table WHERE id=5;

// 清除表（自增id，id不会重新开始）
DELETE TABLE table_name;

// 清除特定数据
DELETE TABLE table_name WHERE condition;

// 清除表（自增id，id会重新开始）
TRUNCATE TABLE table_name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 七、单表数据记录查询

# 基本查询语句

// 条件查询sql？
SELECT col_name FROM table_name WHERE condition;
// 多条件用AND，OR，IN连接条件

// 排序sql？ORDER BY
// 从小到大排
SELECT *FROM tableName ORDER BY id;

// 从大到小排
SELECT * FROM tableName ORDER BY id DESC;

// 先遵循列1从大到小排，遇到相同数据按列2从小到大排
SELECT * FROM tableName ORDER BY id DESC, num;

// 分组操作sql
SELECT * FROM table_name GROUP BY col_name;

// 分组操作后筛选
SELECT * FROM table_name GROUP BY col_name HAVING condition;

// 连表操作sql
// 左连接LEFT JOIN
SELECT col_name1, col_name2 FROM table_name1 
LEFT JOIN table_name2 ON condition;

// 右链接RIGHT JOIN
SELECT col_name1, col_name2 FROM table_name1 
RIGHT JOIN table_name2 ON condition;

// 内连接INNER JOIN
SELECT col_name1, col_name2 FROM table_name1 INNER JOIN table_name2;

// 临时表sql？
SELECT * FROM table_name AS t_name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

# 十 MySQL常用函数

# 数值函数

  • 用于处理数值方面的运算
  • foo

# 十一 索引

# 操作

// 普通索引sql：加速查找INDEX
CREATE TABLE table_name(
Id int(11),
Name varchar(30),
INDEX n_index(name)
);

// 已有
ALTER TABLE table_name ADD INDEX n_index col_name;
CREATE INDEX n_index ON table_name col_name

// 主键索引sql：加速查找；约束（主键不能为空，不能重复）
ALTER TABLE test ADD CONSTRAINT test_PrimaryKey PRIMARY KEY (PrimaryKey);

// 唯一索引sql：加速查找；约束（索引不能重复）UNIQUE
CREATE TABLE table_name(
Id int(11),
Name varchar(30),
UNIQUE m_unique(id)
);

// 组合索引sql：多列组成一个索引（联合主键索引，唯一索引，联合普通索引）
CREATE TABLE table_name(
Id int(11),
Name varchar(30),
INDEX n_index(name,id)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

# 十二 视图

# 概念

  • 与操作基表相比，视图主要优势是简单安全数据独立。
  • 视图的表结构和数据都能修改，且基表会随视图数据改变而改变。

# 用法

// 创建视图
CREATE VIEW v_name[(column_list)] AS Select_statment;

// 查看视图结构
DESC vname

// 查看视图定义语句
SHOW CREATE VIEW;

// 修改视图
CREATE OR REPLACE VIEW
ALTER

// 删除视图
DROP view
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Last Updated: 10/17/2021, 8:21:50 PM