MySQL 基础

发表于 2017-04-22 更新于 2022-12-11 分类于 mysql 阅读次数： Waline：本文字数： 9.1k 阅读时长 ≈ 8 分钟

MySQL的一些基础知识。

对比	MySQL	Excel
数据库	database	一个excel
表	table	一个sheet
行	row	一个sheet里的一行数据
字段	column	一列

（1） MySQL介绍

MySQL是一个小型关系型数据库管理系统，开发者为瑞典MySql AB公司。2009年被Oracle公司收购。目前MySql被广泛地应用在Internet上的中小型网站中。

(1.1)　什么是数据库

　可以认为是一个excel
　数据库由一批数据构成的有序集合，这些数据被分门别类地存储放在一些结构化的数据表（table）里，而数据表之间又往往存在交叉引用的关系，这种关系使数据库又被成为关系型数据库

(1.2)　什么是SQL

　SQL是Structured Query Language（结构化查询语言）的缩写。SQL是专门为数据库而建立的操作命令集，是一种功能齐全的数据语言。

(1.3)　DML DCL DDL

DDL(Data Definition Language) 数据定义语言
–用来建立数据库、数据库对象和定义其列；类似设置excel的列的类型(是文本还是数字)
CREATE TABLE
ALTER TABLE
DROP TABLE

DML(Data Manipulation Language) 数据操作语言
查询、插入、修改和删除数据库中的数据类似操作excel
SELECT
INSERT
UPDATE
DELETE

DCL(Data Control Language数据控制语言)
–用来控制存取许可、存储权限等；类似设置excel谁可以访问 (加密码)
GRANT
REVOKE

功能函数
日期函数、数学函数、字符函数、系统函数；

(1.4)　 MySql优点

性能快捷、优化Sql语言
容易使用
多线程和可靠性
多用户支持
可移植性和开放源代码
遵循国际标准和国际化支持
为多种编程语言提供API

(1.5)　 MySql不足

不能直接处理XML数据
一些功能上支持的不够完善和成熟
不能提供任何OLAP（实时分析系统）功能

(2)　MySql数据类型

整形
tinyint(m) 1个字节范围(-128127)
smallint(m) 2个字节范围(-3276832767)
mediumint(m) 3个字节范围(-83886088388607)
int(m) 4个字节范围(-21474836482147483647)
bigint(m) 8个字节范围(+-9.22*10的18次方)

浮点型
float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d) 双精度浮点型 16位精度(8字节) m总个数，d小数位

字符串
char(n) 固定长度，最多255个字符
varchar(n) 固定长度，最多65535个字符
tinytext 可变长度，最多255个字符
text 可变长度，最多65535个字符
mediumtext 可变长度，最多2的24次方-1个字符
longtext 可变长度，最多2的32次方-1个字符

char和varchar：
1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型
存储的字符串末尾不能有空格，varchar不限于此。
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实
际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入3个字符将占
用4个字节。
3.char类型的字符串检索速度要比varchar类型的快。

二进制数据
1.BLOB和TEXT存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而BLOB是以二进制方式存储，不分大小写。
2.BLOB存储的数据只能整体读出。
3.TEXT可以指定字符集，BLOB不用指定字符集。

日期时间类型

date 日期 ‘2008-12-2’
time 时间 ‘12:25:36’
datetime 日期时间 ‘2008-12-2 22:06:44’
timestamp 自动存储记录修改时间

若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。

数据类型的属性
NULL 数据列可包含NULL值
NOT NULL 数据列不允许包含NULL值
DEFAULT 默认值
PRIMARY KEY 主键
AUTO_INCREMENT 自动递增，适用于整数类型
UNSIGNED 无符号
CHARACTER SET name 指定一个字符集

(3) DDL

新建数据库 create database db_test;

查看所有数据库 show databases;

使用数据库 use db_test;

删除数据库 drop database db_test ;

(3.1) CREATE

创建表

CREATE TABLE test_user (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '用户记录创建的时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户资料修改的时间',
`user_id` bigint(11) NOT NULL COMMENT '用户id',
`username` varchar(45) NOT NULL COMMENT '真实姓名',
`email` varchar(30) NOT NULL COMMENT '用户邮箱',
`nickname` varchar(45) NOT NULL COMMENT '昵称',
`avatar` int(11) NOT NULL COMMENT '头像',
`birthday` date NOT NULL COMMENT '生日',
`sex` tinyint(4) DEFAULT '0' COMMENT '性别',
`short_introduce` varchar(150) DEFAULT NULL COMMENT '一句话介绍自己，最多50个汉字',
`user_resume` varchar(300) NOT NULL COMMENT '用户提交的简历存放地址',
`user_register_ip` int NOT NULL COMMENT '用户注册时的源ip',
`user_review_status` tinyint NOT NULL COMMENT '用户资料审核状态，1为通过，2为审核中，3为未通过，4为还未提交审核',
PRIMARY KEY (`id`),
UNIQUE KEY `uq_idx_user_id` (`user_id`),
KEY `idx_username`(`username`),
KEY `idx_create_time`(`create_time`,`user_review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='网站用户基本信息';

查询所有的表 show tables;

显示表的基本结构 describe employee;

删除表 drop table if exists employee ；

(3.2) ALTER

表重命名

ALTER TABLE `test_user` 
  rename to `test_user_2` ;

表增加字段

ALTER TABLE `db_test`.`employee` 
    ADD COLUMN `address`  varchar(255) NOT NULL DEFAULT '' COMMENT '地址' AFTER `sex` ,
    ADD COLUMN `remark` varchar(255) NOT NULL DEFAULT '' COMMENT '备注' AFTER `address` ;

表更改字段

ALTER TABLE `employee` CHANGE `remark` `remark_new` VARCHAR(255)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_general_ci
    NOT NULL
    DEFAULT ''
    COMMENT '备注'

在ALTER TABLE时，可以使用以下三种方法


ALTER TABLE t CHANGE a b BIGINT NOT NULL ;

ALTER TABLE t MODIFY b INT NOT NULL ;

ALTER TABLE t  ALTER COLUMN `is_deleted` DROP DEFAULT ;
ALTER TABLE t  ALTER COLUMN `is_deleted` SET DEFAULT 'N' ;

** CHANGE MODIFY ALTER 对比 **
CHANGE 重命名列并更改定义，简单得说，CHANGE可以改字段类型，还可以改字段名。功能最强大。
MODIFY 更改列定义但不能更改其名称，简单地说，MODIFY可以改字段类型，不能改字段名。
ALTER 仅用于更改列默认值，只能改默认值。

5.7/en/alter-table.html-dev.mysql.com

Renaming, Redefining, and Reordering Columns

The CHANGE, MODIFY, and ALTER clauses enable the names and definitions of existing columns to be altered. They have these comparative characteristics:

** CHANGE: **
Can rename a column and change its definition, or both.
Has more capability than MODIFY, but at the expense of convenience for some operations. CHANGE requires naming the column twice if not renaming it.
With FIRST or AFTER, can reorder columns.

** MODIFY: **
Can change a column definition but not its name.
More convenient than CHANGE to change a column definition without renaming it.
With FIRST or AFTER, can reorder columns.

** ALTER: **
Used only to change a column default value.

CHANGE is a MySQL extension to standard SQL. MODIFY is a MySQL extension for Oracle compatibility.

To alter a column to change both its name and definition, use CHANGE, specifying the old and new names and the new definition. For example, to rename an INT NOT NULL column from a to b and change its definition to use the BIGINT data type while retaining the NOT NULL attribute, do this:
ALTER TABLE t1 CHANGE a b BIGINT NOT NULL;
To change a column definition but not its name, use CHANGE or MODIFY. With CHANGE, the syntax requires two column names, so you must specify the same name twice to leave the name unchanged. For example, to change the definition of column b, do this:
ALTER TABLE t1 CHANGE b b INT NOT NULL;

MODIFY is more convenient to change the definition without changing the name because it requires the column name only once:
ALTER TABLE t1 MODIFY b INT NOT NULL;
To change a column name but not its definition, use CHANGE. The syntax requires a column definition, so to leave the definition unchanged, you must respecify the definition the column currently has. For example, to rename an INT NOT NULL column from b to a, do this:
ALTER TABLE t1 CHANGE b a INT NOT NULL;
For column definition changes using CHANGE or MODIFY, the definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE. Attributes present in the original definition but not specified for the new definition are not carried forward. Suppose that a column col1 is defined as INT UNSIGNED DEFAULT 1 COMMENT ‘my column’ and you modify the column as follows, intending to change only INT to BIGINT: ALTER TABLE t1 MODIFY col1 BIGINT;
That statement changes the data type from INT to BIGINT, but it also drops the UNSIGNED, DEFAULT, and COMMENT attributes. To retain them, the statement must include them explicitly: ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';
For data type changes using CHANGE or MODIFY, MySQL tries to convert existing column values to the new type as well as possible.

(3.2.3) auto_increment

SELECT auto_increment 
FROM information_schema.tables 
WHERE table_schema = database_test 
AND table_name = table_test ;

ALTER TABLE database_test.table_test 
  auto_increment = 100;

表添加外键 add constraint 外建名 foreign key(列名) references 表名(列名);

删除外键 drop foreign key 外建名

可以更改指定列默认值 alter table employee alter is_deleted default 'N';

删除字段 alter table employee drop is_deleted;

更改表的字符集 alter table employee character set UTF8

修改字段名称/类型 alter table employee change 旧字段名新字段名新字段的类型;

(4) dml

插入数据

INSET INTO department ( did, dname ) 
values 
(1,‘技术部’) ;

批量插入

 INSET INTO department ( did, dname ) 
 values 
 (1,’技术部’), 
 (2,’教务部’) ;

只查询表中的某列：

select id, emp_id, user_name, sex form employee ;

更新

update employee set user_name = ‘tom’ where id = 1 ;

更新多个字段 update employee set user_name = 'tom', sex='男' where id = 1 ;

删除 delete from employee where id < 2;

查询所有语法 select * from employee;

(4) 连接查询

(4.1) 交叉连接(笛卡尔积): cross join

SELECT * FROM table_1, table_2 ;
SELECT * FROM table_1 cross join table_2 ;
SELECT * FROM table_1 cross join table_2 cross join table_3;

“没有任何限制条件的连接方式”称之为”交叉连接”，”交叉连接”后得到的结果跟线性代数中的”笛卡尔乘积”一样。

(2) 内连接：inner join

内连接-等值连接
内连接-不等连接
内连接-自连接

SELECT * FROM table_1 t1, table_2 t2 WHERE t1.id = t2.id ;

SELECT * FROM table_1 t1 JOIN table_2 t2 ON t1.id = t2.id ;

SELECT * FROM table_1 t1 INNER JOIN table_2 t2 ON t1.id = t2.id ; (内连接等值连接)

SELECT * FROM table_1 t1 INNER JOIN table_2 t2 ON t1.id > t2.id ; (内连接不等连接)

SELECT * FROM table_1 t1 INNER JOIN table_1 t2 ON t1.id t2.user_id ; (内连接自连接)

“内连接”理解成”两张表中同时符合某种条件的数据记录的组合”

注意：inner join 不带条件时就成了 cross join 交叉连接(笛卡尔积)

(3) 外连接

(3.1) 左连接 left join

SELECT * FROM table_1 t1 LEFT OUTER JOIN table_2 t2 ON t1.id = t2.id ;

SELECT * FROM table_1 t1 LEFT JOIN table_2 t2 ON t1.id = t2.id ;

左外连接不仅会查询出两表中同时符合条件的记录的组合，同时还会将”left outer join”左侧的表中的不符合条件的记录同时展示出来，由于左侧表中的这一部分记录并不符合连接条件，所以这一部分记录使用”空记录”进行连接。

(3.2) 右连接 right join

SELECT * FROM table_1 t1 RIGHT OUTER JOIN table_2 t2 ON t1.id = t2.id ;

SELECT * FROM table_1 t1 RIGHT JOIN table_2 t2 ON t1.id = t2.id ;

(4) 联合查询：union 与 union all

当使用union连接两个查询语句时，两个语句查询出的字段数量必须相同，否则无法使用union进行联合查询。
使用union将两个结果集集中显示时，重复的数据会被合并为一条。

SELECT * FROM table_1 t1 UNION SELECT * FROM table_2 t2 ;

使用union all进行联合查询时，如果两条sql语句存在重复的数据，重复的记录会被展示出来。

SELECT * FROM table_1 t1 UNION ALL SELECT * FROM table_2 t2 ;

(5) 全连接：full join

“全连接”的英文原文为full join，但是在mysql中并不支持”全连接”，更准确的说，mysql中不能直接使用”full join”实现全连接，不过，我们可以变相的实现”全连接”,在mysql中，我们可以使用”left join”、”union”、”right join”的组合实现所谓的”全连接”。

SELECT * FROM table_1 t1 LEFT  JOIN table_2 t2 ON t1.id = t2.id 
UNION ALL
SELECT * FROM table_1 t1 RIGHT JOIN table_2 t2 ON t1.id = t2.id ;


SELECT * FROM table_1 t1 LEFT  JOIN table_2 t2 ON t1.id = t2.id WHERE t2.id IS NOT NULL 
UNION ALL
SELECT * FROM table_1 t1 RIGHT JOIN table_2 t2 ON t1.id = t2.id WHERE t1.id IS NOT NULL ;

其它

MySQL更改密码

在MySql安装目录下：mysql\bin下输入如下命令：mysqladmin –u root –p旧密码 password 新密码

导出数据库

mysqldump -u 用户名 -p 数据库名 > 导出的文件名
例如：在mysql/bin目录下执行 mysqldump -u root -p oa > oa.sql
这个时候会提示要你输入root用户名的密码,输入密码后dataname数据库就成功备份在mysql/bin/目录中，后面的路径可以自己定义，可以使用绝对路径。

导出表

语法：
mysqldump –u 用户名 –p 数据库名表名>导出的文件名
举例：
mysqldump –u root –p oa dep > dep.sql

导入数据

进入mysql数据库控制台，
如mysql -u root -p
mysql>use 数据库
然后使用source命令，后面参数为脚本文件（如这里用到的.sql）
mysql>source d:\oa.sql

创建用户

语法
Grant 操作权限（select,insert,update,delete或者all） on 数据库名.* to 新用户名@访问地址 identified by “密码”;
举例：
grant all on oa.* to xu@localhost identified by ’root’;
切换用户 :mysql –u xu -p

References

[1] MySQL基础语法
[2] mysql/mariadb知识点总结（16）：select语句总结之三：多表查询
[3] Mysql 多表查询详解
[4] 5.7/en/alter-table.html-dev.mysql.com
[5] 5.7/en/alter-table-examples