MySQL数据库

目录

• 数据库
• MySQL数据库
  • 1、MySQL配置文件
  • 2、查看MySQL服务
  • 3、登录MySQL
  • 4、查看数据库
  • 5、创建数据库
  • 6、删除数据库
  • 7、表操作
    • • 增
      • 删
      • 改
      • 查
      • 约束
        • 非空约束
        • 唯一约束
        • 主键约束
        • 自增约束
        • 默认约束
        • 外键约束
        • 组合使用案例
      • 表关系
      • 数据三范式
      • 视图
  • 8、表数据操作
    • • 增
      • 删
      • 改
      • 查
        • 单表查询
        • 多表查询
        • 子表查询
        • 排序
        • 限制显示的行数
        • 分组查询
      • MySQL内置函数
      • MySQL执行顺序
  • 建议

数据库

数据库就类似于一个文件夹，里面放了很多Excel表格，但是数据库是专门用来管理数据的软件，所以效率要远远高于Excel或文本的存储方式

• 数据库管理系统由一个相互关联的数据的集合和一组用以访问这些数据的程序组成。这个数据集合通常叫做数据库
• 分类
  • 关系型数据库：关系型数据库基于关系模型，使用一系列表来表达数据以及这些数据之间的关系。MySQL就是关系型数据库。关系模型已经成为当今主要的数据类型，它比之前的网络模型和层次模型简化了编程者的工作。现在开始流行的NoSQL数据库，泛指非关系型数据库
  • 非关系型数据库：独立的列表

MySQL数据库

使用的是Linux系统

1、MySQL配置文件

Ubuntu通过apt安装的mysql配置文件级目录是/etc/mysql/mysql.conf.d

Windowns上面是安装目录下的my.ini，如果需要改MySQL的一些设置

2、查看MySQL服务

Linux上，通过：service mysql status/start stop

Windowns下通过任务管理器即可查看

3、登录MySQL

• 本地连接：

mysql -u用户名 -p

输入密码

• 远程连接：

mysql -hIP地址 -p端口 -u用户 -p

输入密码

4、查看数据库

MySQL语句规范

• 关键字和函数名称全部大写
• 数据库名称、表名称、字段名称全部小写，用反引号括起来
• SQL语句必须以分号结尾

查看有哪些数据库： SHOW DATABASES;

进入某个数据库：USE mysql;

判断是否在哪个数据库里面：SELECT DATABASE();

查看当前用户：SELECT user();

5、创建数据库

CREATE{DATABASE|SCHEMA}[IF NOT EXISTS] `name`;

用大括号的部分，就是多选一

用中括号的部分，是可选项

MySQL中，数据库名用反引号引起来

MySQL数据库中的DATABASE和SCHEMA等效，在其他数据库中可能会有不一样

6、删除数据库

DROP {DATABASE|SCHEMA}[IF EXISTS] `name`;

用大括号的部分，就是多选一

用中括号的部分，是可选项

7、表操作

增

1. 创建表

   CREATE TABLE [IF NOT EXISTS] table_name(
       column_name data_type
   );
   

   column_name是字段名，也就是列名

   data_type是字段类型，常见字段类型有整型(INT)和字符串(VARCHAR(字节数))

   用中括号的部分，是可选项

2. 添加字段

   ALTER TABLE table_name ADD (
       column_name data_type
   );
   

删

1. 删除表

   DROP TABLE table_name;
   

改

1. 修改表

   • 删除字段

   ALTER TABLE table_name DROP (
       column_name
   );
   

   • 修改字段类型

   ALTER TABLE table_name MODIFY column_name data_type;
   

   • 修改字段类型和名字

   ALTER TABLE table_name CHANGE old_column_name new_column_name data_type;
   

   • 修改表名

   ALTER TABLE table_name RENAME new_table_name;
   

查

1. 查看数据库中有哪些表

SHOW TABLES[FROM `库名`];

2. 查看表中有哪些信息

   • 查看表的创建信息

   SHOW CREATE TABLE `name`;
   

   • 查看表字段信息

   DESC table_name;
   SHOW COLUMNS FROM table_name;
   

约束

非空约束

数据库字段的某个值是否可以为空，NULL字段值可以为空，NOT NULL 字段值不可以为空。当字段设置为非空时，插入值就必须要插入值，否则就会报错

添加非空约束

ALTER TABLE `table_name` MODIFY column_name type NOT NULL; 

末尾加入NOT NULL可以进行非空约束

删除非空约束

ALTER TABLE `table_name` MODIFY column_name type NULL; 

唯一约束

字段添加唯一约束之后，该字段的值不重复，也就是该字段的值在该表中唯一unique key

添加唯一约束

ALTER TABLE `table_name` ADD CONSTRAINT `index_name` UNIQUE(column_name);

index_name为别名，便于删除

删除唯一约束

ALTER TABLE `table_name` DROP INDEX `index_name`;

主键约束

主键保证记录的唯一性，主键保存为NOT NULL，每张数据表只能存在一个主键（非空且唯一）

添加主键约束

ALTER TABLE `table_name` ADD PRIMARY KEY(column_name);

删除主键约束

ALTER TABLE `table_name` DROP PRIMARY KEY;

自增约束

AUTO_INCREMENT自动编号，且必须与主键组合使用，默认情况下，起始值为1，每次的增量为1

添加自增约束

ALTER TABLE `table_name` CHANGE `column_name` `column_name` INT NOT NULL AUTO_INCREMENT;

删除自增约束

ALTER TABLE `table_name` CHANGE `column_name` `column_name` INT NOT NULL;

默认约束

DEFAULT(默认约束)初始值的设置，插入记录时，如果没有明确为字段赋值，则自动赋予默认值

DEFAULT TABLE `table_name` ALTER `cloumn_name`{SET DEFAULT `default_name`|DROP DEFAULT}

外键约束

约束对象

之前讲到的约束，都是表内约束自身，而外键约束约束其他表

创建条件

数据表的存储引擎只能为InnoDB、外键列和参照列数据类型一致、外键必须关联到键上面去、外键名在数据库要唯一

创建外键约束

ALTER TABLE `table_name` ADD CONSTRANT `index_name` FOREIGN KEY (`column_name`) RRENCES `外键关联的表`(`column_name`);

在创建表时设置，只要ADD以后的内容

删除外键约束

ALTER TABLE `table_name` DROP FOREIGN KEY `index_name`;

组合使用案例

CREATE TABLE stu (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20) NOT NULL,
    sex VARCHAR(10) DAFAULT 'nan',
    phone VARCHAR(11) UNIQUE,
    d_id INT,
    CONSTRAINT fk_id FOREIGN KEY (d_id) REFERENCES department(d_id)
)AUTO_INCREMENT=100;

表关系

一对一关系，指的是一个实体的某个数据与另外一个实体的一个数据有关联关系

一对多关系，一对多与多对一是一个概念，指的是一个实体的某个数据与另外一个实体的多个数据有关联关系

多对多关系，一个实体的数据对应另外一个实体的多个数据，另外实体的数据也同样对应当前实体的多个数据

数据三范式

范式的作用是尽可能避免数据的冗余和插入/删除/更新的异常

• 第一范式（1NF）：符合1NF的关系中的每个属性都不可再分。1NF是所有关系型数据库的最基本要求
  简单来说，就是不能出现多个表头

• 第二范式（2NF）：2NF在1NF的基础之上，消除了非主属性对于码的部分函数依赖
  简单来说，只能描述一个对象（主键），其它列名（副键）与对象之间相互完全依赖

• 第三范式（3NF）：3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖
  简单的说，所有的非主属性只在整个数据库里面出现一次，副键与副键之间，不能存在依赖关系

数据库事务

访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位
简单来说：几个操作是绑定在一起的，要么都做完，要么一个都不做

视图

三范式让表查询变得复杂，对于常用的数据查询，反复写复杂的查询语句十分不方便，因此可以创建一个虚拟的表（不存数据），这个虚拟表的数据来源于数据库中存在的其他表，虚拟表的数据来源就在定义时给定

创建视图

CREATE VIEW `view_name`[(column_name_list)] AS select_statement;

删除视图

DROP VIEW `view_name`;

8、表数据操作

增

INSERT INTO table_name [(column_name,···)]{VALUES|VALUE}(···)

当不指定插入的字段名时，要全部插入

()里面要用,隔开

如果为VALUES插入，则(···),(···)

删

DELETE FROM table_name [WHERE 条件];

如果不添加条件，则删除表全部内容

where 可以用is null 或者is not null

<>其为不等于

改

1. 更新数据

UPDATA table_name SET column_detail_name=value [WHERE 条件];

如果没有WHERE，则修改全部数据

查

单表查询

SELECT {*|column_name} FROM table_name [WHERE 要添加的条件];

其可以使用and和or来匹配

取别名

SELECT column_name AS new_column_name FROM table_name AS new_table_name;

作用是防止表与表之间同名的冲突

其中AS可以用空格代替

可以通过table_name.column_name来查询表内容

多表查询

• 内连接

笛卡尔坐标系

把两张表想象成x轴和y轴上面的每个点，就是两个表里面的每行数的集合

SELECT * FROM `table_name1` INNER JOIN `table_name2`;
SELECT * FROM `table_name1`, `table_name2`;
SELECT * FROM `table_name1`CROSS JOIN `table_name2`;
SELECT * FROM `table_name1` JOIN `table_name2`;

以上这四个SQL的结果都是一样的，都是笛卡尔积，也叫无条件连接/交叉连接等

列数是两个表列的相加，行数是两个表行的乘积

添加条件查询

SELECT * FROM `table_name1` INNER JOIN `table_name2` ON 条件;
SELECT * FROM `table_name1`, `table_name2` WHERE 条件;
SELECT * FROM `table_name1`CROSS JOIN `table_name2`;
SELECT * FROM `table_name1` JOIN `table_name2`;

多表连接

SELECT * FROM `table_name1` INNER JOIN `table_name2` ON 条件 INNER JOIN `table_name3` ON 条件;
SELECT * FROM `table_name1`, `table_name2`, `table_name3` WHERE 条件;

• 外连接

右连接（左连接和右连接类似，只是作用相反）

SELECT * FROM `table_name1` RIGHT JOIN `table_name2`ON 条件;

左连接

SELECT * FROM `table_name1` LEFT JOIN `table_name2`ON 条件;

主要保留左边连接的数据，右边多则删，少则用null补

全连接

UNION用于合并两个或多个SELECT语句的结果集，并消去表中任何重复行

SELECT s.`name`,c.`name` FROM `students` s LEFT JOIN `select` se ON s.`s_id`=se.`stu_id` LEFT JOIN `course` c ON c.`id`=se.`coures_id`
UNION
SELECT s.`name`,c.`name` FROM `students` s LEFT JOIN `select` se ON s.`s_id`=se.`stu_id` LEFT JOIN `course` c ON c.`id`=se.`coures_id`;

保留两张表，没有则用null填充

子表查询

子查询不但可以放在JOIN 后面，也可以放在WHERE后面

SELECT * FROM `students` s WHERE s.`dept_id` = (SELECT `id` FROM `department` d WHERE d.`name` = '外国语学院’);

排序

ORDER BY对查询出来的结果进行排序，ASC 升序（默认）DESC降序

SELECT * FROM `table1_name` AS s JOIN `table2_name` sd ON s.`id`=sd.`stu_id`ORDER BY s.`id` DESC;

限制显示的行数

LIMIT对查询出来的结果限制显示的行数

SELECT * FROM `table1_name` AS s JOIN `table2_name` sd ON s.`id`=sd.`stu_id`ORDER BY s.`id` DESC LIMIT 2,3;

其中2,3表示显示2到3行

分组查询

分组是歌常见的操作，常用于分组统计，使用GROUP BY后，会按照GROUP BY后面的字段进行分组，且必须是明确的字段，不能是*，因此SELECT后面也不能是*，其次可以使用HAVING可以对分组之后的结果进行筛选，注意：HAVING后的字段必须是SELECT后出现过的

统计有多少数据

SELECT COUNT(*) FROM `table1_name`;

统计数据出现的次数

SELECT `column_name`,count(*) FROM `table_name` GROUP BY `column_name` HAVING 选择条件;

MySQL内置函数

1. 字段处理

SELECT s.`name`,IFNULL(sd.`id_card`,430) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;  # 处理NULL
SELECT DISTINCT `name` FROM `students`;  # 字段去重

2. 字符串截取

LEFT是从左边开始截取，RIGHT是从右边开始截取,SUBSTRING可以指定截取范围

SELECT LEFT(s.`name`,2),IFNULL(sd.`id_card`,430) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;
SELECT RIGHT(s.`name`,2),IFNULL(sd.`id_card`,430) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;
SELECT SUBSTRING(s.`name`,2,5),IFNULL(sd.`id_card`,430) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;

3. 字符串拼接

SELECT CONCAT(s.`name`,sd.`id_card`) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;

4. 类型转换

CAST CONVERT

SELECT CAST(sd.`id_card` AS CHAR) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;
SELECT CONVERT(s.`dept_id`,SIGNED) FROM `students` s ;

5. 时间函数

SELECT DAY('2017-08-18')-DAY('2017-08-01’);
SELECT NOW();

MySQL函数还有很多，许多也和我们平常使用的一样，聚合函数 比如：ABS/MAX/MIN/ROUND/AVG/SUM等，用法也是一样的，如果需要使用请自行查询

SELECT MAX(sd_age) FROM `table_name`;
SELECT sd_age,MAX(sd_age) FROM `table_name` GROUP BY sd_age;

MySQL执行顺序

1. FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1

2. ON: 对虚表VT1进行ON筛选，只有那些符合<join-condition>的行才会被记录在虚表VT2中

3. JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止

4. WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合<where-condition>的记录才会被插入到虚拟表VT4中

5. GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5

6. CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6

7. HAVING： 对虚拟表VT6应用having过滤，只有符合<having-condition>的记录才会被 插入到虚拟表VT7中

8. SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中

9. DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9

10. ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10

11. LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回

建议

1. 尽量避免整表扫描，如SELECT *

2. 建立合适的索引

3. 使用合适的存储引擎

4. 在JOIN中，尽量用小表LEFT JOIN 大表

5. 除非十分必要，尽量不要使用ORDER BY,GROUP BY 和 DISTINCT(去重)，尽量用索引来代替