当前位置：首页 > news >正文

学生做网站赚钱/seo整站优化新站快速排名

news 2025/6/30 17:52:39

学生做网站赚钱,seo整站优化新站快速排名,科协网站建设的建议,wordpress 页面分栏本文从开发层面全面梳理 MySQL 各个开发要点，以帮助大家准确理解、熟悉掌握相关技术。简要讲解 Table/View等各种数据库对象，并重点讲解 SELECT/INSERT/DELETE/UPDATE 等使用频率最高的语句，最后讲解了存储过程和记录去重的综合实战。作者&…

本文从开发层面全面梳理 MySQL 各个开发要点，以帮助大家准确理解、熟悉掌握相关技术。简要讲解 Table/View等各种数据库对象，并重点讲解 SELECT/INSERT/DELETE/UPDATE 等使用频率最高的语句，最后讲解了存储过程和记录去重的综合实战。

作者：王克锋
出处：https://kefeng.wang/2016/12/18/mysql-development/
版权：自由转载-非商用-非衍生-保持署名，转载请标明作者和出处。

1.概述

　　本文的目的在于，和大家一起梳理 MySQL 最常用的开发要点，以查漏补缺并准确理解，能信手拈来灵活运用。起因是笔者在面试应聘者、与周边同事沟通时，发现一些开发者对 MySQL 的某些常用语法似懂非懂，实际操作时无从下手。
　　笔者以为，最基础最常用的，才是最需要熟练掌握的，然后再去学更高级的内容。

2.数据定义语言(DDL)

　　下面列出的 View/Function/Procedure/Trigger, 这些机制给我们开发者带来很大的方便，但它们对数据库性能有影响，而数据库性能经常成为系统中的瓶颈。所以，如果对性能要求高，那么数据库中应该只保存数据，逻辑处理尽量移到应用程序中进行。

2.1 Database

SHOW DATABASES; ## 当前主机数据库列表
DROP DATABASE IF EXISTS db; ## 删除数据库[如果存在]
CREATE DATABASE IF NOT EXISTS dbCHARACTER SET UTF8; ## 创建数据库[如果不存在]
USE db; ## 切换db为默认数据库

2.2 Table

SHOW TABLES; ## 当前数据库中数据表TRUNCATE TABLE db.users; ## 彻底清空数据表（清除碎片）
DROP TABLE IF EXISTS db.users; ## 删除数据表[如果存在]
CREATE TABLE IF NOT EXISTS db.users( ## 新建数据表[如果不存在]id INT AUTO_INCREMENT COMMENT '用户ID',name VARCHAR(32) NOT NULL COMMENT '用户名',city VARCHAR(16) NOT NULL COMMENT '城市',birthday DATE DEFAULT '2000-01-01' COMMENT '生日',created TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '生成时间',updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',PRIMARY KEY(id), UNIQUE uni_users_name(name), INDEX idx_users_city(city), INDEX idx_users_birthday(birthday)
) ENGINE=INNODB DEFAULT CHARSET UTF8 COMMENT '用户表';

INSERT INTO db.users(name, city, birthday) VALUES ## 没指定的列，取值为默认值('张三', '北京', '2000-01-01'), ## id=1('李四', '上海', '2003-01-01'), ## id=2('王五', '广州', '2006-01-01'); ## id=3
SELECT * FROM db.users ORDER BY id;

id	name	city	birthday	created	updated
1	张三	北京	2000-01-01	2016-08-21 20:08:15	2016-08-21 20:08:15
2	李四	上海	2003-01-01	2016-08-21 20:08:15	2016-08-21 20:08:15
3	王五	广州	2006-01-01	2016-08-21 20:08:15	2016-08-21 20:08:15

CREATE TABLE IF NOT EXISTS db.orders(id INT AUTO_INCREMENT COMMENT '订单ID',uid INT NOT NULL COMMENT '用户ID',amount DECIMAL(10,2) NOT NULL COMMENT '金额',created TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '生成时间',updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',PRIMARY KEY(id), INDEX idx_orders_uid(uid), INDEX idx_orders_amount(amount)
) ENGINE=INNODB DEFAULT CHARSET UTF8 COMMENT '订单表';
INSERT INTO db.orders(uid, amount) VALUES(1, 100),(1, 200),(2, 400),(9, 500);
SELECT * FROM db.orders ORDER BY id;

id	uid	amount	created	updated
1	1	100.00	2016-08-21 20:10:28	2016-08-21 20:10:28
2	1	200.00	2016-08-21 20:10:28	2016-08-21 20:10:28
3	2	400.00	2016-08-21 20:10:28	2016-08-21 20:10:28
4	9	500.00	2016-08-21 20:10:28	2016-08-21 20:10:28

CREATE TABLE db.users_bak1 LIKE db.users;
CREATE TABLE db.users_bak2 SELECT * FROM db.users WHERE city IN ('北京','上海') ORDER BY id;DESC db.users; ## 查看表结构
SHOW CREATE TABLE db.users; ## 查看建表语句## 临时表（建在内存中，当前会话可见）
DROP TEMPORARY TABLE IF EXISTS db.tmp_users;
CREATE TEMPORARY TABLE IF EXISTS db.tmp_users ...## 修改表定义
ALTER TABLE db.usersADD temp VARCHAR(32) COMMENT '临时列',CHANGE temp temp VARCHAR(64) COMMENT '临时列2',ADD INDEX idx_users_temp(temp),DROP temp;

## 分区表
CREATE TABLE IF NOT EXISTS db.users(...)
ENGINE=INNODB DEFAULT CHARSET UTF8 COMMENT '用户表'
PARTITION BY RANGE COLUMNS(birthday) (PARTITION Y1960 VALUES LESS THAN ('1960-01-01'),PARTITION Y1970 VALUES LESS THAN ('1970-01-01'),PARTITION Y1980 VALUES LESS THAN ('1980-01-01'),PARTITION Y1990 VALUES LESS THAN ('1990-01-01'),PARTITION Y2000 VALUES LESS THAN ('2000-01-01'),PARTITION Y2010 VALUES LESS THAN ('2010-01-01'),PARTITION YMAXV VALUES LESS THAN MAXVALUE
);

2.3 View

CREATE OR REPLACE VIEW db.view_users ASSELECT id,name,city FROM db.users WHERE city IN ('北京','上海') ORDER BY id DESC;
SELECT * FROM db.view_users;

id	name	city
2	李四	上海
1	张三	北京

2.4 Function

DROP FUNCTION IF EXISTS db.func_users;DELIMITER $$ ## 设置定界符
CREATE FUNCTION db.func_users(v_id INT) RETURNS VARCHAR(32)
BEGINDECLARE v_name VARCHAR(32) DEFAULT NULL;SELECT name INTO v_name FROM users WHERE id=v_id;RETURN v_name;
END$$
DELIMITER ; ## 恢复定界符SELECT db.func_users(1); ## 张三

2.5 Procedure

DROP PROCEDURE IF EXISTS db.proc_users;DELIMITER $$ ## 设置定界符
CREATE PROCEDURE db.proc_users(IN v_id INT, OUT v_name VARCHAR(32))
BEGINSELECT name INTO v_name FROM users WHERE id=v_id;
END$$
DELIMITER ; ## 恢复定界符SET @name=NULL;
CALL db.proc_users(1, @name);
SELECT @name; ## 张三

2.6 Trigger

DROP TRIGGER IF EXISTS db.trig_users;DELIMITER $$ ## 设置定界符
CREATE TRIGGER db.trig_users AFTER UPDATE ON db.users FOR EACH ROW
BEGININSERT INTO db.users_bak1(id, NAME, city, birthday, created, updated)VALUES(OLD.id, OLD.name, OLD.city, OLD.birthday, OLD.created, OLD.updated);INSERT INTO db.users_bak2(id, NAME, city, birthday, created, updated)VALUES(NEW.id, NEW.name, NEW.city, NEW.birthday, NEW.created, NEW.updated);
END$$
DELIMITER ; ## 恢复定界符

3.数据操纵语言(DML)

3.1 SELECT 语句（难点）

3.1.1 基本

SELECT id, name, city, YEAR(CURDATE())-YEAR(birthday) AS ageFROM db.usersWHERE (name LIKE '张%' AND city IN ('北京', '上海'))OR birthday BETWEEN '1990-01-01' AND '2009-12-31'ORDER BY id DESCLIMIT 0,3; ## [m,]n

3.1.2 统计

SELECT MIN(birthday), MAX(birthday), COUNT(*) ## AVG(), SUM()FROM db.usersWHERE city IN ('北京', '上海') ## 分组前筛选GROUP BY city ## 分组依据HAVING COUNT(*)>0 ## 分组后筛选ORDER BY id DESC LIMIT 0,3;

3.1.3 子表

SELECT t.*, (SELECT birthday FROM db.users WHERE NAME='李四') AS birthday4FROM (SELECT id, NAME, city, birthday FROM db.users WHERE id>1) AS tWHERE t.birthday>(SELECT birthday FROM db.users WHERE NAME='张三')AND EXISTS(SELECT 1 FROM db.orders AS o WHERE o.uid=t.id);

3.1.4 JOIN（难点中的难点）

3.1.4.1 常规关联

左右表严格关联上的记录才返回。
特别注意：无关联条件时，结果为m*n条记录，切记不要漏写关联条件。

SELECT u.name, o.id AS oid, o.amountFROM db.users AS u, db.orders AS o WHERE u.id=o.uid;

oid	amount	name	city	birthday
1	100.00	张三	北京	2000-01-01
2	200.00	张三	北京	2000-01-01
3	400.00	李四	上海	2003-01-01

3.1.4.2 左连接

除了严格关联上的记录，“左表”存在而右表不存在的记录也返回。

SELECT o.id AS oid, o.amount, u.name, u.city, u.birthdayFROM db.users AS u LEFT JOIN db.orders AS o ON u.id=o.uid;## WHERE o.id > 2;

oid	amount	name	city	birthday
1	100.00	张三	北京	2000-01-01
2	200.00	张三	北京	2000-01-01
3	400.00	李四	上海	2003-01-01
NULL	NULL	王五	广州	2006-01-01

3.1.4.3 右连接

除了严格关联上的记录，“右表”存在而左表不存在的记录也返回。

SELECT o.id AS oid, o.amount, u.name, u.city, u.birthdayFROM db.users AS u RIGHT JOIN db.orders AS o ON u.id=o.uid;

oid	amount	name	city	birthday
1	100.00	张三	北京	2000-01-01
2	200.00	张三	北京	2000-01-01
3	400.00	李四	上海	2003-01-01
4	500.00	NULL	NULL	NULL

左关联的实际应用：统计每个用户的订单个数和总金额。
如果用普通连接，则无订单用户将不用列出。

SELECT u.id, u.name, COUNT(o.id) AS order_number, IFNULL(SUM(o.amount),0) AS total_amoumtFROM db.users AS u LEFT JOIN db.orders AS o ON u.id=o.uidGROUP BY u.id;

id	name	order_number	total_amoumt
1	张三	2	300.00
2	李四	1	400.00
3	王五	0	0.00

3.1.5 UNION [ALL]

各个子查询的字段的个数和类型必须一致；
UNION 自动去重，UNION ALL 不去重。

SELECT * FROM db.users WHERE city='北京' UNION
SELECT * FROM db.users_bak1 WHERE city='上海' UNION
SELECT * FROM db.users_bak2 WHERE city='广州';

3.1.6 索引

索引缺点: 占空间；INSERT/DELETE/UPDATE 时慢(要更新索引区)。
有用子句: WHERE, ORDER BY, GROUP BY, JOIN
索引禁忌: YEAR(birthday)=2016, INSTR(city,'上')>0, name LIKE '%ABC%'
注意: 每次执行新写的SELECT语句，务必考虑相关数据表的数据量、是否能用到索引，可考查WHERE, ORDER BY, GROUP BY, JOIN等子句，或者使用EXPLAIN查看执行计划。

3.2 INSERT

INSERT INTO users(nick,city) VALUES('张三', '北京'),('李四', '上海'),('王五', '广州'); ## 没指定的列，取值为默认值
INSERT INTO tmp_users(nick,city)SELECT nick, city FROM tmp_users;

唯一索引重复时的处理方法

INSERT INTO users(nick,city) VALUES('张三', '上海'); ## 默认用法，会报错
INSERT IGNORE INTO users(nick,city) VALUES('张三', '上海'); ## 冲突时忽略新记录
INSERT INTO users(nick,city) VALUES('张三', '上海')ON DUPLICATE KEY UPDATE city=VALUES(city); ## 覆盖原记录，未涉及列保持原值
REPLACE INTO users(nick,city) VALUES('张三', '上海'); ## 覆盖原记录，未涉及列全部更新为默认值

3.3 DELETE

DELETE FROM db.users WHERE id=8;

切忌 WHERE 条件有漏写而多删除数据！首次执行语句前，可改为 SELECT 确认涉及的数据；

3.4 UPDATE

UPDATE db.users SET updated=NOW() WHERE id<5;

切忌 WHERE 条件有漏写而多删除数据！首次执行语句前，可改为 SELECT 确认涉及的数据；

4.存储过程

4.1 使用游标

CREATE PROCEDURE db.proc_users(IN vi_id INT)
BEGINDECLARE v_id INT;DECLARE v_name VARCHAR(32);DECLARE v_logs VARCHAR(255) DEFAULT '';DECLARE done BOOLEAN DEFAULT FALSE;DECLARE cur_users CURSOR FOR (SELECT id,NAME FROM db.users WHERE id<=vi_id);DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;OPEN cur_users;START TRANSACTION;REPEATFETCH cur_users INTO v_id, v_name;IF !done THENSET v_logs = CONCAT(v_logs, v_id, ',');UPDATE db.users SET updated=NOW() WHERE id=v_id;END IF;UNTIL done END REPEAT;COMMIT; ## ROLLBACK;CLOSE cur_users;SELECT v_logs; ## 可返回记录集，用于调试或Java程序
END$$

4.2 调用方法

CALL db.proc_users(12);

4.3 优化原则

确保使用到索引；
多条记录批量处理；
多条记录批量提交。

5.综合实战（重复记录去重）

假设有张成绩数据表scores，每个学生的每个课程都有且只有一条记录，主要字段有：id(成绩ID) / sid(Student ID，学生ID) / cid(course ID, 课程ID) / score(成绩)。但现在某些学生的某些课程有多条记录，希望去除重复记录。

5.1 重复的组合，生成统计表

挑选有重复的 [sid, cid] 组合，存储于“统计表”， ## HAVING COUNT(1)>1
对于每个组合，记下其中最小的 id, 清除时要保留该id的记录 ## MIN(id)

INSERT INTO tmp_scores_stat(sid, cid, id_min, id_cnt)SELECT sid, cid, MIN(id), COUNT(1)FROM scoresGROUP BY sid, cidHAVING COUNT(1)>1;

5.2 多余的记录，生成备份表

如果将要删除的记录，日后需要使用，则需要此步备份，否则此步可省掉。

CREATE TABLE tmp_scores_extra ASSELECT s.*FROM scores sWHERE EXISTS (SELECT 1FROM tmp_scores_stat tWHERE t.sid=s.sidAND t.cid=s.cidAND t.id_min!=s.id); ## 每个组合中，保留id最小的那条记录

5.3 删除原表的多余记录

DELETE FROM scoresWHERE EXISTS (SELECT 1FROM tmp_scores_extra tWHERE t.id=scores.id);

5.4 注意事项

如果是对在线数据库操作，5.1 的大表统计很耗时，容易导致长时间锁表，应该按id段拆分成多批统计，最后再汇总；
由于涉及数据量大，每步务必用到索引，避免全表扫描；
增删改语句执行前，务必核对操作的数据准确无误，可先改为SELECT语句确认记录，至少确认记录条数一致。

查看全文

http://www.jmfq.cn/news/5041387.html

太原网站优化怎么做/临沂百度联系方式

档案网站建设优秀代表/网络推广公司加盟

旅游公司网站设计/南京网络推广平台

如何拿模板做网站/如何让百度快速收录网站文章

在线做网站视频在线观看/google搜索中文入口

做二手房的网站技巧/哈尔滨网络推广

临沂网站制作公司哪家好/成都网站建设

网站开发藏汉双语/怎样做一个网站平台

把收藏夹网站设置成主业怎么做/南宁seo教程

做网站的每天打电话咋办/怎么提高seo关键词排名

中文网站做google广告怎么样/百度竞价ocpc投放策略

wordpress wpdb insert/班级优化大师怎么用

厦门做网站哪家公司好/河北网站seo

药企做网站/优化关键词的步骤

建设网站的岗位/推广网站怎么制作