MySQL 基础

主要是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) 数据定义语言
–用来建立数据库、数据库对象和定义其列;
CREATE TABLE
ALTER TABLE
DROP TABLE

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

DCL(Data Control Language数据控制语言)
–用来控制存取许可、存储权限等;
GRANT
REVOKE

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

(1.4)  MySql优点

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

(1.5)  MySql不足

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

(2) MySql数据类型

整形
tinyint(m) 1个字节 范围(-128~127)
smallint(m) 2个字节 范围(-32768~32767)
mediumint(m) 3个字节 范围(-8388608~8388607)
int(m) 4个字节 范围(-2147483648~2147483647)
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

创建表

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`is_deleted` char(1) DEFAULT 'N' COMMENT '是否删除 Y 删除 N 未删除',
`gmt_create` datetime DEFAULT '' COMMENT '创建时间',
`gmt_modified` datetime DEFAULT '' COMMENT '更改时间',
`creator_id` varchar(32) DEFAULT '' COMMENT '创建人id',
`modifier_id` varchar(32) DEFAULT '' COMMENT '更改人id',
`emp_id` int(11) NOT NULL COMMENT '员工id',
`user_name` varchar(32) DEFAULT '' COMMENT '用户名',
`sex` char(1) DEFAULT '' COMMENT '性别',
PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='员工表';

查询所有的表 show tables;

显示表的基本结构 describe employee;

删除表 drop table if exists employee ;

(3.2) ALTER

(3.2.1) 表重命名

1
ALTER TABLE `employee` rename to `emp` ;

表增加字段

1
2
3
ALTER TABLE `db_test`.`employee` 
ADD COLUMN `address` varchar(255) DEFAULT '' COMMENT '地址' AFTER `sex` ,
ADD COLUMN `remark` varchar(255) DEFAULT '' COMMENT '备注' AFTER `address` ;
1
2
3
4
5
6
ALTER TABLE `employee` CHANGE `remark` `remark_new` VARCHAR(255)
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci
NULL
DEFAULT ''
COMMENT '备注'

(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 = 2001111;

表添加外键 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

插入数据 insert into department(did,dname)values(1,‘技术部’);

批量插入 insert 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;

连接查询

(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 多表查询详解