MySQL:常规操作示例

删除:DELETE、DROP、TRUNCATE

  • DELETE

删除表内数据。

DELETE FROM 表名 [WHERE 删除的条件]

示例:

DELETE FROM student WHERE name = 'LOLITA0164'
  • DROP

删除表,包括表数据和表结构。

DROP TABLE 表名

示例:

DROP TABLE student
  • TRUNCATE

清除表内所有数据,保留表结构。

TRUNCATE TABLE 表名

示例:

TRUNCATE TABLE student

插入:INSERT INTO

INSERT INTO 表名 ( field1, field2, ... filedN ) VALUES ( value1, value2, ... valueN ) 

示例:

INSERT INTO student
( name, age, sex)
VALUES
( 'LOLITA0164', 26, 'male')

查询

  • SELECT

基本的查询语句

SELECT 字段1,字段2,... 
FROM 表名
[WHERE 查询条件]
[LIMIT N] [OFFSET M]

LIMIT:限制数量
OFFSET:偏移量

示例:

SELECT id,name,age,sex
FROM student
WHERE name IS NOT NULL
LIMIT 2 OFFSET 1
  • WHERE :限制条件
SELECT 字段1, 字段2, ... , 字段N
FROM 表1, 表2, ... 
[WHERE 条件1 [AND [OR]] 条件2 ...

示例:

SELECT name,math,english,physical
FROM student,grade
WHERE student.number = grade.number
AND student.name IS NOT NULL

注:WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。

  • ORDER BY:排序

排序

SELECT 字段1, 字段2, ... 字段N 
FROM 表1, 表2...
[WHERE 条件1 [AND [OR]] 条件2 ...
ORDER BY 字段1, [字段2...] [ASC [DESC]]

示例:

SELECT s.name, g.math, g.english, g.physical
FROM student as s, grade as g
WHERE s.number = g.number
ORDER BY s.number DESC
  • GROUP BY:结果分组
SELECT 字段1, function(字段名)
FROM 表名
[WHERE 条件1 [AND [OR]] 条件2 ...
GROUP BY 用于分组的字段

注:group by 可以实现一个最简单的去重查询。

示例:

SELECT count(*) as total, sex
FROM student
GROUP BY sex

分组后的限定条件使用 HAVING 来设置,WHERE 则是对原始数据进行条件限制。几个关键字的使用顺序为 where、group by、having、order by:

SELECT count(*) as total, sex
FROM student
WHERE age > 10
GROUP BY sex
HAVING total > 1
ORDER BY name DESC
  • NULL:处理空值,过滤或者选择

a. IS NULL: 当列的值是 NULL,此运算符返回 true
b. IS NOT NULL: 当列的值不为 NULL, 运算符返回 true
c. <=>: 比较操作符(不同于=运算符),当比较的的两个值为 NULL 时返回 true

示例:

SELECT *
FROM student
WHERE sex IS [NOT] NULL

在 MySQL 中,可以使用 ifnull 来处理空值的情况
示例:

SELECT name, IFNULL(sex, '未知') as sex
FROM student

注:IFNULL 第一个参数表示匹配的字段,如果该字段为 NULL 时,则使用第二个字段替换

  • LIKE:匹配/模糊匹配
SELECT 字段1, 字段2, ... 字段N 
FROM 表名
WHERE 字段 LIKE 条件1 [AND [OR]] 条件2 ...

示例:

SELECT *
FROM student
WHERE student.name like 'xiao%'

like 会与 % 和 _ 结合使用

‘%a’ :以a结尾的数据
‘a%’ :以a开头的数据
‘%a%’ :含有a的数据
a’ :三位且中间字母是a的
‘_a’ :两位且结尾字母是a的
‘a_’ :两位且开头字母是a的

注:你可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。

  • UNION:合并查询

用于从多个 SELECT 语句的结果集合并到同一个结果集中,查询的字段必须相同

SELECT 字段1, 字段2, ... 字段N
FROM 表1
[WHERE 条件]
UNION [ALL | DISTINCT]
SELECT 字段1, 字段2, ... 字段N
FROM 表2
[WHERE 条件]

示例:

SELECT s.number
FROM student as s
UNION ALL
SELECT g.number
FROM grade as g

ALL:可选,返回所有结果集,包含重复数据。
DISTINCT:可选,删除结果集中重复的数据,默认可不写。

  • JOIN:连接查询

多张表查询

SELECT 字段1,字段2,... 
FROM 表1
[INNER、LEFT、RIGHT] JOIN 表2
ON 连接查询条件

示例:

SELECT name,math,english,physical
FROM student
INNER JOIN grade
ON student.number = grade.number

a. INNER JOIN (内连接):获取两张表中字段匹配关系的记录,等同于 JOIN

内连接

b. LEFT JOIN (左连接):左边为主表,无伦右表有无对应的数据

左连接

c. RIGHT JOIN (右连接):和左连接相反

右连接

注:你可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。


修改:ALTER

  • 修改表名
ALTER TABLE 表名 RENAME TO 新表名

示例:

ALTER TABLE student RENAME TO Student
  • 新增、删除、修改表结构

新增字段

ALTER TABLE 表 ADD 字段名 类型 ... 其他约束

示例:

ALTER TABLE student ADD address VARCHAR(45) FIRST
ALTER TABLE student ADD height INT AFTER name

删除

ALTER TABLE 表 DROP 字段 

示例:

ALTER TABLE student DROP address 

修改

a. 修改字段类型及名称 : MODIFY 和 CHANGE

MODIFY:关键字之后直接跟上字段和类型

ALTER TABLE student MODIFY sex VARCHAR(11)

CHANGE:关键字之后紧跟着的是你要修改的字段名,然后指定新字段名及类型

ALTER TABLE student CHANGE sex SEX VARCHAR(10)

b. 修改字段默认值

ALTER TABLE student ALTER sex SET DEFAULT '未知'

索引

  • 显示索引信息
SHOW INDEX FROM 表名
  • 主键:特殊的索引
ALTER TABLE 表名 ADD PRIMARY KEY (column_list)
  • 普通索引:索引值可出现多次
ALTER TABLE 表名 ADD INDEX 索引名 (column_list)

示例:

ALTER TABLE student ADD INDEX name_index (name)
  • 唯一索引:索引的值必须是唯一的
ALTER TABLE 表名 ADD UNIQUE 索引名 (column_list)
  • 全文索引:FULLTEXT
ALTER TABLE 表名 ADD FULLTEXT 索引名 (column_list)
  • 删除索引
ALTER TABLE 表名 DROP PRIMARY KEY		// 删除主键
DROP INDEX [字段名] ON 表名	       			 		// 删除索引

复制表数据

  • 只复制表结构
CREATE TABLE 新表 LIKE 旧表 
  • 复制表结构和数据
CREATE TABLE 新表 LIKE 旧表 
INSERT INTO 新表 SELECT 字段1, 字段2, ... FROM 旧表
[WHERE 条件]

重复数据处理

可以在MySQL数据表中设置指定的字段为 PRIMARY KEY(主键) 或者 UNIQUE(唯一) 索引来保证数据的唯一性。

  • 统计重复数据
SELECT COUNT(*) as total, age
FROM student
GROUP BY age
HAVING total > 1	

注:在 GROUP BY 之后的限制条件使用 HAVING 关键字。

  • 过滤重复的数据
SELECT DISTINCT age
FROM student

也可以使用上面提到的 GROUP BY 来实现:

SELECT age
FROM student
GROUP BY age
  • 删除重复数据

a. 我们提取出旧表中的不重复的数据新建表,将旧表删除,再重新命名为新表。

CREATE TABLE tmp LIKE student					// 创建新表,只保留表结构
INSERT INTO tmp SELECT * FROM student GROUP BY name	// 选择不重复数据填充新表
DROP TABLE student			// 删除旧表
ALTER TABLE tmp RENAME TO student		// 新表更名为旧表

b. 我们提取出重复的数据,在保留最小序列号的数据下删除其他数据。

DELETE FROM student 
WHERE name 
IN 		// 条件1:重复的部分
(
	SELECT a.name FROM (
		SELECT name FROM student GROUP BY name HAVING COUNT(*) > 1  // 重复数据
	) as a
)
AND id NOT IN 		// 条件2:序列号最小
(
	SELECT b.id FROM (
		SELECT MIN(id) as id FROM student GROUP BY name HAVING count(*) > 1  // 重复数据中最小序列号
	) as b
)
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值