MySQL基础

阅读量：5138 次

发布时间：2019-06-13

本文共 10755 字，大约阅读时间需要 35 分钟。

一、数据库概述

1、SQL分类

SQL，Structure Query Language，结构化查询语言，主要有下面三种：

一、数据定义语言 Data Defination Language (DDL)

数据库定义语言定义了数据库涉及的各种对象，定义数据的完整性约束、保密限制等约束。

二、数据操作语言 Data Manipulation Language (DML)

数据操作语言实现了对数据的操作。基本数据操作有两类：检索（查询）和更新（插入、删除和修改）。

三、数据控制语言 Data Control Language (DCL)

数据库控制语言实现对数据库的控制，包括数据完整性控制、数据安全性控制和数据库恢复等。

2、MySQL历史发展

1985年，David Axmark 、Allan Larsson 、 Michael Widenius 成立公司，该公司为 MySQL AB 的前身。

2000年4月，MySQL对搜索引擎进行整理，命名为MyISAM。

2001年，InnoDB集成到MySQL。

2004年10月，MySQL 4.1 版本发布。

2005年10月，MySQL5.0版本发布。5.0加入了游标、存储过程、触发器、视图和事务支持。

2008年1月16日，MySQL被SUN收购。

2009年，SUN被Oracle收购。

... ...

3、MySQL优点

1、体积小

2、速度快

3、拥有成本低

4、开放源码

4、数据库设计三大范式

1、原子性，字段不可再分割

比如：在user中有“联系方式”字段，存储内容：“ 64558@163.com,15854215521 ”，就不符合原子性，要设计成：email、phone对邮箱和手机号分别进行存储。

不符合第一范式：

符合第一范式：

2、完全依赖，没有部分依赖

比如：学生选课表的设计：

这里可以通过（学生,课程）可以确定老师、教师、时间，可以把（学生,课程）作为主键。但是，教材并不完全依赖主键，教材可以根据课程直接确定。这就是出现了不完全依赖、部分依赖，这样的设计就不符合第二范式。

符合第二范式的设计：

选课表:

教材表：

3、没有传递依赖

每个属性要跟主键直接联系，不能有传递关系。

比如，学生表：

学校的地址和学校的电话，就是和college有依赖关系，和student是没有直接依赖关系，不符合第三范式。

符合第三范式的修改如下：

学生表：

学校表：

一般来讲，范式越复杂，性能会越差。我们工作中满足以上三大范式即可。

二、MySQL安装和配置

1、MySQL下载

1、MySQL官方网站：

2、MySQL版本分类：社区版 Community 和企业版 Enterprise。

社区办支持只有下载，并且完全免费，官方不提供技术支持；

企业版不能自由下载并且收费，提供了更多的功能，享受完备的技术支持。

3、版本标识说明

GA：通用版本，官方推荐

RC：发布版

Alpha：内测版

Bean：公测版

4、社区版下载：

进入网站首页——>Downloads——>Community——MySQL on Windows ——>MySQL Installer,进入Windows版本下载页面。

其中 1.7 M大小的 mysql-installer-web-community 为在线安装文件；

385M大小的 mysql-installer-community 为本地安装。

2、MySQL安装

Typical：默认安装

Complete：完全安装

Custom：自定义安装

3、MySQL配置

配置文件为：C：ProgramData/MySQL/MySQL 5.6/my.ini

常用配置项：

lower_case_table_names = 0 // 0 ,表名区分大小写；1 , 表名不区分大小写

default_character_set = utf-8 // 客户端字符编码

character_set_server = utf-8 //服务端字符编码

port = 3306 //端口号

default_storage_engin = INNODB //默认存储引擎

三、MySQL对象操作

1、数据库操作

（1）创建数据库：

create database db_name;

（2）查看数据库：

show databases ;

（3）使用数据库：

use db_name;

（4）删除数据库：

drop database db_name;

注意： SQL语句可以用 ; \g \G 三种方式结尾，其中 ; 和 \g 效果相同； \G 优化结果显示。

2、表操作

（1）创建表:

create table company ( company_id  INT , company_name  VARCHAR(20) , company_address  VARCHAR(64));

（2）查看表结构：

describe company;     --查看表定义 show create table company;      --查看表详细定义

（3）删除表：

drop table company;

（4）修改表：

alter table company rename mycompany; --修改表名alter table company add  tel varchar(20); --增加字段alter table company add  desc varchar(32) first;--在表的第一个位置增加字段alter table company add create_time varchar(32) after company_name; --在指定位置增加字段alter table company drop company_name;--删除字段 alter table company modify company_name varchar(128);--修改字段类型 alter table company modify company_name first;--调整字段顺序到第一位 alter table company modify company_name after desc;--调整字段顺序到desc后面 alter table company change company_name new_name varchar(128);--修改字段名和字段类型

（5）操作表约束：

数据的完整性是指数据的准确性与一致性，完整性检查是指检查数据的准确性和一致性。MySQL提供一致机制来检查数据库表中的数据是否满足规定的条件，以保证数据库表中数据的准确性和一致性，这种机制就是约束。

MySQL支持的完整性约束：

NOT NULL（NK）：约束字段的值不能为空

DEFAULT：设置字段的默认值

UNIQUE KEY（UK）：约束字段的值唯一

PRIMARY KEY（PK）：约束字段为标的主键，作为表记录的唯一标识

AUTO_INCREMENT：约束字段的值为自动增加

FOREIGN KEY（FK）：约束字段为表的外键

a、设置非空约束

create table member(member_id INT(11) NOT NULL, member_name VARCHAR(32) NOT NULL);

如果增加的记录中，设置NK的字段没有值，则数据库管理系统会报错。

命令行错误信息：

ERROR 1048 (23000) : Column 'member_name' cannot be null

管理工具错误提示：

b、设置字段的默认值

create table member(member_id INT(11) NOT NULL, member_name VARCHAR(32) DEFAULT 'huiyuan');

c、设置唯一约束（UK）

create table member(member_id INT(11) NOT NULL, member_name VARCHAR(32) UNIQUE);

对唯一字段插入相同记录时，提示信息：

d、设置主键约束

主键字段要满足：唯一、非空，可以是单一字段，也可以是多个字段。

单字段主键的两种方法：

create table member(member_id INT(11) PRIMARY KEY, member_name VARCHAR(32) UNIQUE, create_time varchar(32));  --（1）设置主键create table member(member_id INT(11), member_name VARCHAR(32) UNIQUE, create_time varchar(32),              CONSTRAINT pk_menberid PRIMARY KEY (member_id)); -- （2）设置主键并定义主键名称

多字段主键：

create table member(member_id INT(11), member_name VARCHAR(32) UNIQUE, create_time varchar(32),                    CONSTRAINT pk_id_name PRIMARY KEY (member_id,member_name)); -- 设置主键并定义主键名称

e、自增（AUTO INCREMENT）

一个数据库只能有一个字段使用自增约束，而且该字段类型必须是整数类型。自增约束一般设置在主键上。

create table t_dept (deptno INT PRIMARY KEY auto_increment, dname VARCHAR(20) , loc VARCHAR(40));

f、外键（FOREIGN KEY）

描述两个表之间的约束。子表中的某个字段的取值范围由父表决定。比如部门和雇员表。

create table t_dept (deptno INT PRIMARY KEY auto_increment, dname VARCHAR(20) , loc VARCHAR(40)); --部门表create table t_employee(employee_id INT PRIMARY KEY,e_name VARCHAR(32),create_time VARCHAR(32),            dept_no INT(11), CONSISTENT fk_deptno FOREIGN KEY(dept_no) REFERENCES t_tept(deptno));--雇员表

3、索引操作

数据库的索引对象和书的目录类似，主要是为了提高从表中检索数据的速度（其次还可以保证字段唯一性，实现数据库表的完整性）。按索引的存储类型分类，可以分为：B型树索引（BTREE）和哈希索引（HASH），其中，InnoDB和MyISAM存储引擎支持BTREE索引，MEMORY支持HASH索引。

MySQL支持的索引有6种：普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引

（1）索引的适用场景

以下情况适合创建索引：

经常被查询的字段，即在WHERE子句中出现的字段。

在分组的字段，即在 GROUP BY 子句中出现的字段。

存在依赖关系的子表和父表之间的联合查询，即主键和外键字段。

设置唯一完整性约束的字段。

以下情况不适合创建索引：

在查询中很少被使用的字段。

拥有许多重复值的字段。

（2）创建索引

a、普通索引

普通索引：在创建索引时，不附加任何限制条件（唯一、非空等限制）。该类型的索引可以创建在任何数据类型的字段上。

在创建索引时，可以指定索引的长度。这是因为不用的存储引擎定义了表的最大索引数和最大索引长度。MySQL每个表至少支持16个索引，总索引长度为256字节。

在创建索引时，可以定义索引的长度和排序。ASC 升序排列； DESC 降序排列

-- 创建表时创建普通索引create table t_dept(deptno INT PRIMARY KEY,dname VARCHAR(20),loc VARCHAR(40),INDEX index_deptno(deptno));-- 在已经存在的表上创建索引create INDEX index_deptno ON t_dept(deptno(16) ASC);-- 通过Alter table 创建索引alter table t_dept add INDEX index_deptno(deptno);

b、唯一索引

唯一索引：创建索引时，限制索引的值必须是唯一的。唯一索引可以更快速的查询某条记录。

-- 创建表时创建 唯一 索引create table t_dept(deptno INT PRIMARY KEY,dname VARCHAR(20),loc VARCHAR(40),UNIQUE INDEX index_deptno(deptno));-- 在已经存在的表上创建 唯一 索引create UNIQUE INDEX index_deptno ON t_dept(deptno(16) ASC);-- 通过Alter table 创建 唯一 索引alter table t_dept add UNIQUE INDEX index_deptno(deptno);

c、全文索引

全文索引主要是关联在数据类型为:CHAR 、VARCHAR 和 TEXT 的字段上，以便能够更加快速的查询数据量较大的字符串类型的字段。只能在存储引擎为MyISAM的数据库表上创建全文索引。默认情况下，全文索引的搜索执行方式不区分大小写，如果全文索引所关联的字段为二进制数据类型，则以区分大小写的搜索方式执行。

-- 创建表时创建 全文 索引create table t_dept(deptno INT PRIMARY KEY,dname VARCHAR(20),loc VARCHAR(40),FULLTEXT INDEX index_deptno(deptno)) ENGINE = MyISAM;-- 在已经存在的表上创建 全文 索引create FULLTEXT INDEX index_deptno ON t_dept(deptno(16) ASC);-- 通过Alter table 创建 全文 索引alter table t_dept add FULLTEXT INDEX index_deptno(deptno);

d、多列索引

多列索引：创建索引时，所关联的字段不是一个字段，而是多个字段。只有查询条件中使用了所关联字段中的第一个字段，多列索引才被使用。

-- 创建表时创建  多列 索引create table t_dept(deptno INT PRIMARY KEY,dname VARCHAR(20),loc VARCHAR(40),INDEX index_deptno(deptno,loc));-- 在已经存在的表上创建 多列 索引create INDEX index_deptno ON t_dept(deptno,loc);-- 通过Alter table 创建 多列 索引alter table t_dept add INDEX index_deptno(deptno,loc);

（3）删除索引

-- 删除索引DROP INDEX index_dname_loc ON t_dept;

4、视图操作

（1）视图的特点

视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。

视图是由基本表（实表）产生的表（虚表）。

视图的建立和删除不影响基本表。

对视图内容的更新（添加、删除和修改）直接影响基本表。

当视图来自多个基本表时，不允许添加和删除数据。

（2）创建视图

基本表结构：

创建视图：

CREATE VIEW view_selectproduct AS SELECT id,name FROM t_product;

注意：在SQL语句命名规范中，视图一般用 view_xxx 或者 v_xxx 命名。

（2）使用视图

视图的使用和正常的表查询一样：

SELECT * FROM view_selectproduct;

（3）不同种类的视图

a、常量视图

CREATE VIEW view_pi AS SELECT 3.1415926;

b、封装聚合函数（SUM/MIN/MAX/COUNT等）

CREATE VIEW view_count_name AS SELECT COUNT(name) FROM t_product;

c、封装排序（order by）

CREATE VIEW view_order_by AS SELECT name FROM t_product ORDER BY id DESC;

d、封装内连接

CREATE VIEW view_inner_join AS SELECT s.name FROM t_student AS s,t_group as g WHERE s.group_id = g.id;

e、封装外连接

CREATE VIEW view_left_join AS SELECT s.name FROM t_student AS s LEFT JOIN t_group AS g ON s.group_id = g.id;

其他子查询、联合查询的视图创建方式相似。

（4）查看视图

SHOW TABLES; SHOW TABLES STATUS FROM DB_NAME; -- 查看视图的详细信息 SHOW CREATE VIEW viewname; -- 查看视图的定义信息

（5）删除视图

DROP VIEW view_name1,view_name2; -- 可以同时删除多个视图

（6）视图操作基本表数据

INSERT INTO view_product (id, name, price ,order_id) VALUES (11,"apple",6,54); -- 插入数据DELETE FROM view_product WHERE name = "apple"; -- 删除数据UPDATE view_product SET price = 4 WHERE name = "apple"; -- 修改数据

5、触发器操作

触发器：在表发生更改时，自动进行处理。

可以触发器执行的语句： delete 语句、insert 语句、update 语句。

-- 触发器CREATE TRIGGER tri_diarytime     BEFORE INSERT ON t_dept FOR EACH ROW         INSERT INTO t_diary VALUES(null,"t_dept",NOW());

四、MySQL数据操作

1、增、改、删操作

（1）插入数据

-- 插入一条完整记录INSERT INTO t_dept(deptno,dname,loc) VALUES (5,'开发部','程序开发'); -- 推荐使用INSERT INTO t_dept VALUES (5,'开发部','程序开发');-- 缺省格式，不推荐使用-- 插入一条部分记录INSERT INTO t_dept(dname) VALUES ('设计部');-- 插入多条记录INSERT INTO t_dept(dname) VALUES ('设计部'),('产品部'),('市场部'),('行政部');-- 插入查询的结果INSERT INTO t_dept(dname) SELECT dname FROM t_company;

（2）更新数据

UPDATE t_dept SET dname = '测试部门' ,loc = '' where deptno = 1;

（3）删除

DELETE FROM t_dept WHERE deptno < 10;

2、单表查询

原始表：

（1）简单数据记录查询

SELECT item_name,brand_id FROM z_item; -- 查询商品名称和品牌idSELECT * FROM z_item; -- 查询商品表中的所有字段

a、去重 DISTINCT:

SELECT DISTINCT  item_name ,brand_id FROM z_item;  -- 去重会取并集，即  去重的 所有字段 都重复 才进行 去重。

b、四则运算：

MySQL支持的数学运算符：

+ 加法； - 减法； * 乘法； / 除法；% 取余

SELECT price,(price+0.2) AS 'price+0.2',(price -0.2) AS 'price-0.2' , (price * 2) AS 'price * 2 ' ,(price / 2) AS 'price/2',(price % 2 ) AS 'price % 2' FROM z_item ;

c、字符串连接CONCAT()

SELECT CONCAT('商品名称是',item_name,'的商品，价格为',price) FROM z_item;

（2）条件查询

a、MySQL支持的比较运算符和逻辑运算符

> 大于; < 小于 ; = 等于 ; != (<>) 不等于 ; >= 大于等于; <=小于等于;

AND(&&) 逻辑与 OR(||) 逻辑或 XOR 逻辑异或 NOT(!) 逻辑非

b、简单条件查询

SELECT * FROM z_item WHERE item_name = '鞋子';SELECT * FROM z_item WHERE item_id <= 10 AND price > 1000;SELECT * FROM z_item WHERE item_id <= 10 && price > 1000;

c、BETWEEN AND 关键字

SELECT * FROM z_item WHERE price BETWEEN 100 AND 1000;SELECT * FROM z_item WHERE price NOT BETWEEN 100 AND 1000;

d、NULL 和 NOT NULL

SELECT * FROM z_item WHERE item_name IS NULL;SELECT * FROM z_item WHERE item_name IS NOT NULL;

e、IN

SELECT * FROM z_item WHERE item_name = '冰箱' OR item_name = '空调' OR item_name = '洗衣机';SELECT * FROM z_item WHERE item_name IN('冰箱','空调','洗衣机');SELECT * FROM z_item WHERE item_name NOT IN('冰箱','空调','洗衣机');

注意：在具体使用IN 关键字时，查询的结果如果存在NULL，则不会影响查询；如果使用关键字 NOT IN ，查询的集合中如果存在NULL，则不会有任何的查询结果。

f、LIKE

通配符：用来实现匹配部分值的特殊符号。

SELECT * FROM z_item WHERE item_name LIKE '%机';SELECT * FROM z_item WHERE NOT item_name LIKE '%机';SELECT * FROM z_item WHERE item_name NOT LIKE '%机';SELECT * FROM z_item WHERE price LIKE '_9%';

g、ORDER BY

ASC 升序 DESC 降序

SELECT * FROM z_item ORDER BY price DESC , item_id ASC;

h、LIMIT

SELECT * FROM z_item LIMIT 1;SELECT * FROM z_item LIMIT 3,2; -- 第四条记录开始显示，显示2条

i、统计函数和分组函数

-- 统计函数经常和分组一起使用-- 统计条数   COUNT(*),统计所有值，包括null值和非null值； count(price)指定之后不统计null值。SELECT COUNT(*) FROM z_item ;-- 平均值SELECT AVG(price) FROM z_item;-- 求和SELECT SUM(price) FROM z_item;-- 最大值、最小值SELECT MAX(price) FROM z_item;SELECT item_name,MIN(price) FROM z_item;-- 分组SELECT *,SUM(price) FROM z_item WHERE is_delete = 0 GROUP BY brand_id;SELECT GROUP_CONCAT(item_name),SUM(price) FROM z_item WHERE is_delete = 0 GROUP BY brand_id,is_delete;SELECT GROUP_CONCAT(item_name),SUM(price) FROM z_item WHERE is_delete = 0 GROUP BY brand_id,is_delete HAVING AVG( price) > 1000;

注：资料来源为书籍《MySQL数据库应用从入门到精通》，本文为个人学习笔记。

转载于:https://www.cnblogs.com/lashou/p/6140135.html

你可能感兴趣的文章

LiveNVR稳定RTSP流媒体服务器软件支持网络摄像机Onvif探测接入并进行云台控制

vue2.0 资源文件assets和static的区别

查看>>

是否手机访问