Mysql常用方法简单整理

连接数据库

1
mysql -uroot -p123123 -h127.0.0.1

创建数据库

1
CREATE DATABASE MyDb;

选择数据库

1
USE MyDb;

创建表

1
2
3
4
5
CREATE TABLE t_class
(
  id INT PRIMARY KEY,  
  name VARCHAR(32)
);

外键约束

1
CONSTRAINT 外键名 FOREIGN KEY 字段名 REFERENCES 主表名(主键名)

常用约束

唯一约束

唯一约束（Unique Constraint）要求该列唯一，允许为空，但是只能有一个空值。唯一约束可以确保一列或者几列不出现重复值。

1
关键词 UNIQUE

非空约束

1
关键词 NOT NULL

默认约束

默认约束：即给字段一个默认值。

1
关键词 DEFAULT

设置表的属性值自动增加

1
关键词 AUTO_INCREMENT

常用约束总结

以下是在 MySQL 中常用的约束。

NOT NULL 约束：确保某列不能有 NULL 值。

DEFAULT 约束：当某列没有指定值时，为该列提供默认值。

UNIQUE 约束：确保某列中的所有值是不同的。

PRIMARY Key 约束：唯一标识数据库表中的各行/记录。

CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。

删除表

1
DROP TABLE 表名;

查看数据表基本结构

1
DESCRIBE 表名；

查看数据表详细结构

1
SHOW CREATE TABLE 表名\G;

使用 SHOW CREATE TABLE 语句，不仅仅可以返回给我们建表时所写的详细语句，而且还可以查看存储引擎和字符编码。

改变表名

1
ALTER TABLE 旧表名 RENAME 新表名;

修改字段名

1
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;

tip： 如果不需要修改字段的数据类型，可以把新字段的数据类型设置为和原来一样，但是！千万不要空着它！

修改字段数据类型

1
ALTER TABLE 表名 MODIFY 字段名 数据类型;

添加与删除字段

添加字段

1
ALTER TABLE 表名 ADD 新字段名 数据类型 [约束条件] [FIRST|AFTER] 已存在字段名;

在表的最后一列添加字段

只要不做[FIRST|AFTER]的位置说明，在添加字段时MySQL会默认把新字段加入到表的最后一列。

在表的第一列添加字段

如果我们想在第一列添加新的字段，只需做FIRST的位置说明。

举个例子：
现在我们要把字段prod_country添加到表Mall_products的第一列。

输入命令：

1
ALTER TABLE Mall_products ADD prod_country varchar(30) FIRST;

在表的指定列后添加字段

如果我们想在某一列后面添加新的字段，只需做AFTER的位置说明，然后注明你想让它添加在哪个字段的后面即可。

举个例子：
现在我们要把字段prod_country添加到表Mall_products的 prod_name字段的后面。

输入命令：

1
ALTER TABLE Mall_products ADD prod_country varchar(30) AFTER prod_name;

删除字段

1
ALTER TABLE 表名 DROP 字段名;

修改字段的排列位置

修改字段的排列位置

我们可以通过ALTER TABLE来改变表中字段的相对位置。

1
ALTER TABLE 表名 MODIFY 字段1 数据类型 FIRST|AFTER 字段2;

其中，字段1指要修改位置的字段，FIRST与AFTER 字段2为可选参数。

修改字段为表的第一个字段

如果我们想把字段的位置调整到第一列，只需做FIRST的位置说明。

举个例子
现在我们要把字段prod_price调整到表Mall_products的第一列。Mall_products表结构

输入命令：

ALTER TABLE Mall_products MODIFY prod_price FLOAT FIRST;

修改字段到表的指定列之后

还有一种位置调整的方法可以让你把想调整的字段放在除了第一列的任何位置。调整的时候需要做AFTER 字段2的位置说明。

删除表的外键约束

1
ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;

插入数据

为表的所有字段插入数据

向表中插入数据最简单的方法就是使用INSERT语句。INSERT语句需要你声明要插入内容的表(table)名和内容(values)。

1
INSERT INTO 表名 (字段名) VALUES (内容);

为表的指定字段插入数据

为表的指定字段插入数据，就是在INSERT中只向部分插入值，而其他字段的值为表定义时的默认值。

example:

假设我们现在有一张空表 MyUser 如下

1

我们忽略字段 age，为表 MyUser 插入内容:

1
insert into MyUser(name) values('lisi'),('fawaikuangtu'),('zhangsan');

更新数据

更新表中指定的内容

需要更新的表（table）名；

需要更新的字段（column）名和它的新内容（value）；

决定更新哪一条内容（value）的过滤条件。

1
UPDATE 表名 SET 字段名1 = 内容1, 字段名2 = 内容2, 字段名3 = 内容3 WHERE 过滤条件; 。

example:

有一张表Mall_products2，内容如下图

2

把Span换成Pakistan，地区代码也换为92 

1
2
3
UPDATE Mall_products2
SET country_name = "Pakistan", country_id = 92
WHERE id = 2;

删除数据

删除表中的指定行

从数据表中删除数据内容需要使用DELETE语句，它需要WHERE语句来配合它来指定我们究竟应该删除哪些数据内容。

1
DELETE FROM 表名 WHERE 条件语句;

example:

1
2
DELETE FROM Mall_products2 
WHERE id=2 OR id=3;

删除表中的所有行

1
DELETE FROM Mall_products2;

基本查询语句

查询数据表中指定字段的内容

MySQL从数据表中查询数据的基本语句为SELECT语句。

SELECT语句的可选参数比较多，让我们先从最简单的开始，带大家一步一步的深入SELECT语句的使用。

1
SELECT 字段名 FROM 表名;

若需要查询多个字段下的内容。这时，我们只需要在字段之间加入逗号,即可。

1
SELECT 字段名1, 字段名2 FROM 表名;

查询数据表中的所有内容

如果我们不记得字段名字了，我们还可以查看整张表的内容。这时候，只需要我们用星号*来代替字段的名字，就会得到一整张表的内容。

1
SELECT * FROM 表名;

带IN关键字的查询

IN关键字被用在WHERE语句的后边，用来过滤你所需要查询的内容。更形象的说，IN关键字的使用情形就像点名，点到谁谁就要站出来。

1
SELECT 字段名 FROM 表名 WHERE 字段名 IN (n1,n2,n3,...);

其中，括号内的数字必须为INT格式的数字。被“点到名”的这些括号里数字对应的内容，都要乖乖的站到前边来展示给大家看。

带 BETWEEN AND 的范围查询

BETWEEN AND需要两个参数支持，一个是范围的开始值，另一个就是结束值了。如果字段值满足指定的范围查询条件，就返回这些满足条件的数据内容。

1
SELECT 字段名 FROM 表名 WHERE 字段名 BETWEEN n1 AND n2;

带 LIKE 的字符匹配查询

SQL语句支持很多种通配符，其中可以和LIKE一起搭配使用的就是通配符%和_

使用通配符%模糊匹配数据内容

百分号通配符%可以匹配任意长度的字符，甚至包括零字符。

1
SELECT 字段名 FROM 表名 WHERE 字段名 LIKE '字符%';

其中 % 的位置可以根据需要在字符间变化。

example:

假设我们现在有一张表Mall_products，内容如下：

3

使用LIKE关键字和通配符%检索出所有商品名称带ir的商品信息。

1
2
3
SELECT *
FROM Mall_products
WHERE prod_name LIKE '%ir%';

使用通配符_模糊匹配数据内容

下划线通配符_与百分号通配符%类似，也用于模糊匹配。但是区别在于下划线通配符_只能模糊匹配1个字符。

1
SELECT 字段名 FROM 表名 WHERE 字段名 LIKE '字符_';

查询空值与去除重复结果

查询空值

在数据表创建之初，创建者可以指定某个字段是否为空值NULL。注意了，这个NULL既不代表0，也不代表空字符，而是代表一种未知的状态

1
SELECT 字段名 FROM 表名 WHERE 字段名 IS NULL;

去除重复结果

1
SELECT DISTINCT 字段名 FROM 表名;

带 AND 与 OR 的多条件查询

1
SELECT 字段名 FROM 表名 WHERE 表达式1 AND/OR 表达式2;

对查询结果进行排序

如果我们需要对读取的语句进行排序，我们就可以使用Order By子句来设定你想要按照的字段进行排序并返回结果。

1
SELECT 字段名 FROM 表名 ORDER BY 字段名 [ASC[DESC]];

ASC 升序关键字 ; DESC 降序关键字

在默认情况下，它是按升序排列的。

注:若要添加WHERE等语句，需要加在ORDER BY之前。

分组查询

分组查询的单独使用(做了解，实际少用)

分组查询的关键字是Group By，查询的是每个分组中 首次出现的一条记录。

1
SELECT 字段名 FROM 表名 GROUP BY 字段名;

使用 LIMIT 限制查询结果的数量

LIMIT的使用

1
SELECT 字段名 FROM 表名 LIMIT [OFFSET,] 记录数;
  1. 第一个参数，OFFSET，可选参数，表示偏移量，如果不指定默认值为0，表示从查询结果的第一条记录开始，若偏移量为1，则从查询结果中的第二条记录开始，以此类推。
  2. 第二个参数，记录数，表示返回查询结果的条数。

内连接查询

仅将两个表中满足连接条件的行组合起来作为结果集，称为内连接；

关键字：[inner] join … on。

1
表1 [inner] join 表2 on 表1.字段=表2.字段

从表1中取出每一条记录，去表2中与所有的记录进行匹配，匹配必须是某个条件在表1中与表2中相同，最终才会保留结果，否则不保留。inner 关键字可省略不写；on 表示连接条件：条件字段就是代表相同的业务含义（如下面两张表中的 employee.dept_id 和 department.id），大多数情况下为两张表中的主外键关系。

现在我们有两张表，数据如下

employee表数据

id name dept_id
1 Nancy 4
2 Tod 2
3 Carly 1
4 Allen 2
5 Mary NULL

department表数据：

id name
1 开发部
2 测试部
3 运维部
4 销售部

现在想要查询出员工姓名以及其对应的部门名称，我们就使用内连接来进行查询。

我们可以将关联查询思路分为三步：
1.确定所连接的表，
2.确定所要查询的字段，
3.确定连接条件与连接方式。

4

其中，没有部门的员工和部门没有员工的部门都没有被查出来，这就是内连接的特点，只查询在连接表中有对应的记录，其中dept.id=emp.dept_id是连接条件。

example:

1
2
3
select tb_student.name as studentName,tb_class.name as className 
from tb_class join tb_student 
on tb_class.id=tb_student.class_id;

外连接查询

  1. 以某张表为主，取出里面的所有记录，然后每条与另外一张表进行连接，不管能不能匹配上条件，最终都会保留。能匹配，正确保留；不能匹配，其它表的字段都置空（null），称为外连接。

  2. 外连接查询分为左外连接查询和右外连接查询；

  3. 关键字：left/right [outer] join … on。

又如上面那两张表

若查询所有员工姓名以及他所在部门，在内连接中Mary没有被查出，因为他没有对应的部门，现在想把Mary也查出来，就要使用左外连接。

5

此查询语句以employee为主表查询，因此最终记录至少不少于主表已有的记录数。

右外连接是同理的，只是基准表的位置变化了而已。

我们在这里只要将left修改成了right,但是基准表变化了，是以department表的数据去匹配employee表，所以左外连接能做到的查询，右外连接也能做到，仅展现的效果有所不同。

6