基本概念

连接数据库

1
2
3
4
5
-- username 则为自己的用户名，一般为root，password为密码
	mysql -u[username] -p[password]
	mysql -u[username] -p
-- 断开连接，即退出数据库操作页面
	exit

MySQL的关键字是不区分大小写的，这里我全部用大写来记录。

DDL数据定义语言

数据库操作

创建数据库

1
2
CREATE DATABASE [数据库名];
CREATE DATABASE IF NOT EXISTS [数据库名];

删除数据库

1
2
DROP DATABASE [数据库名];
DROP DATABASE IF EXISTS [数据库名];

查看数据库

1
2
3
4
-- 查看所有的数据库
	SHOW DATABASES;
-- 查看创建库使用的语句
	SHOW CREATE DATABASES [库名];

切换数据库

1
2
3
-- 切换到指定的数据库
	USE [数据库名];
--  数据库名和表名如果是特殊的词，就需要用``来区分

表中的数据类型

数值类型

类型描述大小
tinyint很小的一个数据1个字节
smallint较小的一个数据2个字节
mediumint中等大小的一个数据3个字节
int标准大小的数据(整数常用)4个字节
bigint较大的数据8个字节
float单精度浮点数4个字节
double双精度浮点数8个字节
decimal字符串形式的浮点数(用来解决浮点数精度问题)

字符串类型

类型描述大小
char固定大小的字符串0~255
varchar可变的字符串，对应Java的String0~65535
tinytext微型文本2^8-1
text文本串2^16-1

时间与日期

类型描述格式
date日期YYYY-MM-DD
time时间HH:mm:ss
datetime常用的时间格式，即date+timeYYYY-MM-DD HH:mm:ss
timestamp1970.1.1到现在时间的毫秒数
year年份表示

MM与mm分大小写是为了区分月份和分钟。
HH大写是为了表示是24小时制，hh为12小时制。
YYYY表示week-based-year,即只要本周跨年，那这周就算入下一年。
yyyy就表示真实年份。

null

未知的数据类型，不建议参与运算。

字段属性

关键字描述
NULL该数据列可以包含NULL值
NOT NULL该数据列不允许为NULL值
DEFAULT为数据列指定默认值
PRIMARY KEY设数据列为主键
AUTO_INCREMENT设数据列为自动递增，设用于整数
UNSIGNED设数据列为无符号
CHARACTER SET [name]为数据列指定一个字符集
COMMENT注释

表的操作

查看表

1
2
3
4
-- 进入数据库后查看该数据库所有的表
	SHOW TABLES [表名];
-- 查看创建表使用的语句
	SHOW CREATE TABLE [表名];

查看表中的信息

1
2
3
-- 查看表中的具体信息，即结构
	DESCRIBE [表名];
	DESC [表名];

创建表

1
2
3
4
5
6
7
8
9
10
11
12
	CREATE TABLE IF NOT EXISTS `[表名]`(属性)[表类型][设置字符集]
-- 例子
	CREATE TABLE IF NOT EXISTS `user`(
		`id` INT(5) NOT NULL AUTO_INCREMENT COMMENT 'id号',
        `username` VARCHAR(20) NOT NULL COMMENT '用户名',
        `password` VARCHAR(20) NOT NULL COMMENT '密码',
        `sex` VARCHAR(2) COMMENT '性别',
        `birthday` DATETIME DEFAULT NULL COMMENT '生日',
        `address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
        `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
        PRIMARY KEY(`id`)
    )ENGINE=INNODB DEFAULT CHARSET=utf8

修改表

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 修改表名
	ALTER TABLE [旧表名] RENAME AS [新表名];
-- 增加表的字段
	ALTER TABLE [表名] ADD [属性];
	ALTER TABLE user1 ADD age INT(10) DEFAULT NULL COMMENT '年龄';
-- 修改表的约束
-- CHANGE可以更改列名和列类型，而MODIFY只能修改列类型
-- CHANGE只修改列类型的时候，也要写两个相同的列名
-- 所以只修改列类型建议用MODIFY
	ALTER TABLE [表名] CHANGE [旧列名] [新列名] [类型];
	ALTER TABLE [表名] MODIFY [列名] [类型];
-- 删除表的字段
	ALTER TABLE [表名] DROP [列名];

删除表

1
2
DROP TABLE [表名];
DROP TABLE IF EXISTS [表名];

添加外键

1
ALTER TABLE [当前表名] ADD CONSTRAINT `FK_[列名]` FOREIGN KEY([列名]) REFERENCES [对象表名]([列名]);

DML数据操纵语言

INSERT

1
2
3
4
5
6
-- 插入一条数据
	INSERT INTO [表名]([列名1]，[列名2]，[列名3]) VALUES([值1]，[值2]，[值3]);
	INSERT INTO [表名] VALUES([值1]，[值2]，[值3]);
-- 插入多条语句
	INSERT INTO [表名]([列名1]，[列名2]，[列名3]) VALUES([值1]，[值2]，[值3]),([值1]，[值2]，[值3]);
	INSERT INTO [表名] VALUES([值1]，[值2]，[值3]),([值1]，[值2]，[值3]);

UPDATE

1
2
3
-- 条件一般使用WHERE
	UPDATE [表名] SET [列名]=[值] [条件];
	UPDATE [表名] SET [列名1]=[值1],[列名2]=[值2] [条件];

DELETE

1
2
3
4
5
-- 删除数据
	DELETE FORM [表名] [条件];
-- 清空表中数据
	TRUNCATE [表名];
-- TRUNCATE会让自增计数器清零

DQL数据查询语言

1
2
3
4
5
6
7
8
9
10
CREATE TABLE IF NOT EXISTS `user`(
	`id` INT(5) NOT NULL AUTO_INCREMENT COMMENT 'id号',
    `username` VARCHAR(20) NOT NULL COMMENT '名字',
    `password` VARCHAR(20) NOT NULL COMMENT '密码',
    `sex` VARCHAR(2) COMMENT '性别',
    `birthday` DATETIME DEFAULT NULL COMMENT '生日',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

建个表好进行之后的例子

简单的查询

1
2
3
4
5
6
7
8
9
10
11
12
-- 查询表中所有信息
	SELECT * FROM [表名];
	SELECT * FROM `user`;
-- 查询表中指定信息
	SELECT [列名1],[列名2] FROM [表名];
	SELECT `id`,`name` FROM `user`;
-- 给结果的字段及表器别名,AS可以省略
	SELECT [列名1] AS [别名1],[列名2] AS [别名2] FROM [表名] AS [别名3];
	SELECT `username` AS '用户名',`password` AS '密码' FROM `user` AS '用户表';
-- 处理结果的信息，简单的字符串拼接
	SELECT CONCAT([message],[列名]) FROM [表名];
	SELECT CONCAT('用户名为',`username`) FROM `user`;

去重

1
2
-- 去除同样的数据
	SELECT DISTINCT `username` FROM `user`;

一些常用连接符

AND&&A AND B
OR\\A OR B
NOT!NOT A

LIKE

1
2
3
4
5
6
-- %代表任意字符,_代表一个字符
	SELECT [列名] FROM [表名] LIKE '[通配符]';
-- 查询用户名第一个字陈的人
	SELECT `username` FROM `user` LIKE '陈%' ;
-- 查询用户名第一个字陈且名字中第三个字为明
	SELECT `username` FROM `user` LIKE '陈_明%' ;

WHERE

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 查询id为300的用户信息，大于小于同理
	SELECT * FROM `user` WHERE id=300;
-- 查询id在300到400之间的用户信息
	SELECT * FROM `user` WHERE id>=300 AND id<=400;
	SELECT * FROM `user` WHERE id BETWEEN 300 AND 400;
-- 查询id为300，301，302的用户信息
	SELECT * FROM `user` WHERE id=300 AND id=301 AND id=302;
	SELECT * FROM `user` WHERE id IN (300,301,302);
-- 查询邮箱为空的用户信息
	SELECT * FROM `user` WHERE email='';
	SELECT * FROM `user` WHERE email IS NULL;
-- 查询邮箱不为空的用户信息
	SELECT * FROM `user` WHERE email IS NOT NULL;

JOIN

1
2
3
4
5
6
7
8
9
10
-- 首先假设有张部门表depart，有id和department两个属性
-- 查询用户的id，userrname和department这三个属性,相同的属性需要通过别名来划分
-- INNER JOIN
	SELECT s.id,username,departmen FROM student AS s INNER JOIN depart AS d ON s.id=d.id
-- RIGHT JOIN
	SELECT s.id,username,departmen FROM student AS s RIGHT JOIN depart AS d ON s.id=d.id
-- LEFT JOIN
	SELECT s.id,username,departmen FROM student AS s LEFT JOIN depart AS d ON s.id=d.id
-- FULL JOIN
	SELECT s.id,username,departmen FROM student AS s FULL JOIN depart AS d ON s.id=d.id
  • INNER JOIN
    如果表中有至少一个匹配，则返回行。
  • RIGHT JOIN
    即使左表中没有匹配，也从右表返回所有的行。
    假如depart有id=1000而user表中没有id=1000的数据也会查到id=1000数据，只是在user表的列数据为null。
    不会查到user表中有而depart表中没有的数据。
  • LEFT JOIN
    即使右表中没有匹配，也从左表返回所有的行。
    假如user有id=1000而depart表中没有id=1000的数据也会查到id=1000数据，只是在depart表的列数据为null。
    不会查到depart表中有而user表中没有的数据。
  • FULL JOIN
    只要其中一个表中存在匹配，则返回行。

排序

1
2
3
# ASC升序,DESC降序
	SELECT * FROM user ORDER BY id ASC
	SELECT * FROM user ORDER BY id DESC

分页

1
2
3
4
-- limit n,m
-- n代表第一个值的坐标(从0开始)，m代表一页的数据多少
-- 查询user表中第一到第五条数据
	SELECT * FROM user LIMIT 0,5

GROUP BY

1
2
3
# 按一个或多个列对结果集进行分组
	SELECT birthday,COUNT(*) FROM user GROUP BY birthday
# 查出每个生日的用户人数多少

HAVING

1
2
3
# HAVING可以让筛选分组后的各组数据
	SELECT birthday,COUNT(*) FROM user GROUP BY birthday HAVING SUM(COUNT(*)) > 100
# 查出大于100个人的生日是同一天的生日