前言

记录一下常用的 Mysql 语句,防止以后需要的时候忘记!~

常用的语句

"SELECT * FROM Persons"  // 从 "Persons" 表中选取所有的列
"UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson'"  // 修改地址(address),'并添加'城市名称(city)
"DELETE FROM Person WHERE LastName = 'Wilson' "  // 删除"LastName"内容为"Wilson"的整一行
"DELETE FROM Person WHERE LastName = 'Wilson'and id='1' "  // 删除"LastName"内容为"Wilson"且"id"为"1"的整一行
"SELECT LastName,FirstName FROM Persons"  // 取名为 "LastName" 和 "FirstName" 的列的内容(从名为 "Persons" 的数据库表)
"delete from table_name where id='5'and user='ceshi'"  // 多信息定位删除
"SELECT * FROM Persons WHERE City='Beijing'"  // 单信息查询
"SELECT * FROM table_name where id='5' and user='ceshi'"  // 多信息查询一
"SELECT * FROM table_name WHERE user='Thomas' OR id='1'"  // 多信息查询二
"SELECT * FROM table_name where (user = ceshi or user = admin) and id > 200"  // or加and的用法
"INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)"  // 插入数据方法一
"INSERT INTO 表名称 VALUES (值1, 值2,....)"  // 插入数据方法二
"SELECT Company FROM Orders"  // 从"Orders"表中 "Company" 列中所有的值
"SELECT DISTINCT Company FROM Orders "  // 从 "Orders"表中Company" 列中仅选取唯一不同的值
"SELECT LastName,FirstName FROM Persons"  // 获取名为 "LastName" 和 "FirstName" 的列的内容(从名为 "Persons" 的数据库表)
"SELECT * FROM Persons"  // 从 "Persons" 表中选取所有的列(用符号 * 取代列的名称)

Top Like 通配符

"SELECT TOP 2 * FROM Persons"  // 从 "Persons" 表中选取头两条记录
"SELECT TOP 50 PERCENT * FROM Persons"  // 从 "Persons" 表中选取 50% 的记录
"SELECT * FROM Persons WHERE City LIKE 'N%'"  // 从"Persons" 表中选取居住在以 "N" 开始的城市里的人
"SELECT * FROM Persons WHERE City LIKE '%g'"  // 从 "Persons" 表中选取居住在以 "g" 结尾的城市里的人
"SELECT * FROM Persons WHERE City LIKE '%lon%'"  // 从 "Persons" 表中选取居住在包含 "lon" 的城市里的人
"SELECT * FROM Persons WHERE City NOT LIKE '%lon%'"  // 从 "Persons" 表中选取居住在不包含 "lon" 的城市里的人
"SELECT * FROM Persons WHERE City LIKE 'Ne%'"  // 从 "Persons" 表中选取居住在以 "Ne" 开始的城市里的人
"SELECT * FROM Persons WHERE FirstName LIKE '_eorge'"  // 从"Persons" 表中选取名字的第一个字符之后是 "eorge" 的人
"SELECT * FROM Persons WHERE LastName LIKE 'C_r_er'"  // 从 "Persons" 表中选取的这条记录的姓氏以 "C" 开头,然后是一个任意字符,然后是 "r",然后是任意字符,然后是 "er"
"SELECT * FROM Persons WHERE City LIKE '[ALN]%'"  // 从 "Persons" 表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人
"SELECT * FROM Persons WHERE City LIKE '[!ALN]%'"  // 从 "Persons" 表中选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人
###In Betweem Aliases

"SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')"  // 从表中选取姓氏为 Adams 和 Carter 的人
"SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter'"  // 以字母顺序显示介于 "Adams"(包括)和 "Carter"(不包括)之间的人
"SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Adams' AND 'Carter'"  // 用上面的例子显示范围之外的人,请使用 NOT 操作符
"SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p, Product_Orders AS po WHERE p.LastName='Adams' AND p.FirstName='John'"  // 假设我们有两个表分别是:"Persons" 和 "Product_Orders"。我们分别为它们指定别名 "p" 和 "po"。现在,我们希望列出 "John Adams" 的所有定单。

SQL join 用于根据两个或多个表中的列之间的关系,从这些表中查询数据

"SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons, Orders WHERE Persons.Id_P = Orders.Id_P"  // 通过引用两个表的方式,从两个表中获取数据
"SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P = Orders.Id_P ORDER BY Persons.LastName"  // 除了上面的方法,我们也可以使用关键词 JOIN 来从两个表中获取数据
"SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName"  // 列出所有人的定购,内连接(INNER JOIN)
' --LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。
"SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName"  // 列出所有的人,以及他们的定购 - 如果有的话
' --RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行,即使在左表 (table_name1) 中没有匹配的行。
"SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName"  // 列出所有的定单,以及定购它们的人 - 如果有的话
"SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name"  // 只要其中某个表存在匹配,FULL JOIN 关键字就会返回行
"SELECT E_Name FROM Employees_China UNION SELECT E_Name FROM Employees_USA"  // 列出所有在中国和美国的不同的雇员名‘注释:这个命令无法列出在中国和美国的所有雇员。在上面的例子中,我们有两个名字相同的雇员,他们当中只有一个人被列出来了。UNION 命令只会选取不同的值。
"SELECT E_Name FROM Employees_China UNION ALL SELECT E_Name FROM Employees_USA"  // UNION ALL 命令和 UNION 命令几乎是等效的,不过 UNION ALL 命令会列出所有的值。
"CREATE DATABASE my_db"  // 创建一个名为 "my_db" 的数据库
"CREATE TABLE 表名称 (列名称1 数据类型,列名称2 数据类型,列名称3 数据类型,)"  // CREATE TABLE Persons(Id_P int,LastName varchar(255),FirstName varchar(255),Address varchar(255),City varcha(255))‘创建名为 "Person" 的表,该表包含 5 个列,列名分别是:"Id_P"、"LastName"、"FirstName"、"Address" 以及 "City"
"ALTER TABLE Persons ADD Birthday date"  // 在表 "Persons" 中添加一个名为 "Birthday" 的新列
"ALTER TABLE Person DROP COLUMN Birthday"  // 删除 "Person" 表中的 "Birthday" 列

查询某字段值重复的数据

查询
查询 tableName 表中,data 字段值重复的数据及重复次数

select `data`,count(*) as count from `tableName` group by `data` having count>1;

替换sql语句

将cdb_pms表subject字段中的Welcom to替换成 欢迎光临

UPDATE `cdb_pms` 
SET `subject` = REPLACE(`subject`, 'Welcome to', '欢迎光临') 
WHERE INSTR(`subject`,'Welcome to') > 0 

替换cdb_posts表的message字段,将“viewthread.php?tid=3989”替换成“viewthread.php?tid=16546”

UPDATE `cdb_posts` 
SET `message`= REPLACE(`message`, 'viewthread.php?tid=3989', 'viewthread.php?tid=16546') 




WHERE INSTR(`message`,'viewthread.php?tid=3989') > 0 ; 

删除所有的空格

UPDATE `es_product` SET `pro_pub_time` = TRIM(`pro_pub_time`) 
###删除所有饱含'['或者']'或者'.'的字符

UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '[','') WHERE INSTR(`pro_pub_time`,'[') > 0
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, ']','') WHERE INSTR(`pro_pub_time`,']') > 0
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '.','-') WHERE INSTR(`pro_pub_time`,'.') > 0

替换所有含中文'-'的为英文'-'

UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '-','-') WHERE INSTR(`pro_pub_time`,'-') > 0 

将所有的年月都替换成'-'

UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '年','-') WHERE INSTR(`pro_pub_time`,'年') > 0
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '月','-') WHERE INSTR(`pro_pub_time`,'月') > 0

将所有'2005-04-'这种类型的替换成'2005-04-01'

UPDATE `es_product` SET `pro_pub_time` = CONCAT( `pro_pub_time`, '01') WHERE SUBSTRING_INDEX( `pro_pub_time`, '-', -1) = '' AND LENGTH(`pro_pub_time`) > 0 AND LENGTH(`pro_pub_time`) > 5 

将所有'2005-'这种类型替换成'2005-01-01'

UPDATE `es_product` SET `pro_pub_time` = CONCAT( `pro_pub_time`, '01-01') WHERE INSTR(`pro_pub_time`,'-') > 0 AND LENGTH(`pro_pub_time`) = 5

将所有 饱含'-',但是位数小于8的改成追加'-01'

UPDATE `es_product` SET `pro_pub_time` = CONCAT( `pro_pub_time`, '-01') WHERE INSTR(`pro_pub_time`,'-') > 0 AND LENGTH(`pro_pub_time`) < 8

将所有'2005'这样的改成'2005-01-01'

UPDATE `es_product` SET `pro_pub_time` = CONCAT(`pro_pub_time`,'-01-01') WHERE INSTR(`pro_pub_time`,'-') = 0 AND LENGTH(`pro_pub_time`) = 4 

最后将所有'2005-01-01'格式化成'2005年01月'

UPDATE `es_product` SET `pro_pub_time` = DATE_FORMAT(`pro_pub_time`,'%Y年%m月') WHERE INSTR(`pro_pub_time`,'-') > 0

随机获取几条数据的方法

在mysql中带了随机取数据的函数,在mysql中我们会有rand()函数,很多朋友都会直接使用,如果几百条数据肯定没事,如果几万或百万时你会发现,直接使用是错误的。下面我来介绍随机取数据一些优化方法。

SELECT * FROM table_name ORDER BY rand() LIMIT 5;

rand在手册里是这么说的:

RAND()
RAND(N)

返回在范围0到1.0内的随机浮点值。如果一个整数参数N被指定,它被用作种子值。

mysql> select RAND();
-> 0.5925
mysql> select RAND(20);
-> 0.1811
mysql> select RAND(20);
-> 0.1811
mysql> select RAND();
-> 0.2079
mysql> select RAND();
-> 0.7888

你不能在一个ORDER BY子句用RAND()值使用列,因为ORDER BY将重复计算列多次。然而在MySQL3.23中,你可以做: SELECT * FROM table_name ORDER BY RAND(),这是有利于得到一个来自

SELECT * FROM table1,table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000

的集合的随机样本。注意在一个WHERE子句里的一个RAND()将在每次WHERE被执行时重新评估。
网上基本上都是查询max(id) * rand()来随机获取数据。

SELECT *
FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id ASC LIMIT 5;

但是这样会产生连续的5条记录。解决办法只能是每次查询一条,查询5次。即便如此也值得,因为15万条的表,查询只需要0.01秒不到。
上面的语句采用的是JOIN,mysql的论坛上有人使用

SELECT *
FROM `table`
WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` )
ORDER BY id LIMIT 1;

我测试了一下,需要0.5秒,速度也不错,但是跟上面的语句还是有很大差距
后来请教了baidu,得到如下代码
完整查询语句是:

SELECT * FROM `table`
WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECT MIN(id) FROM `table`)))
ORDER BY id LIMIT 1;

SELECT *
FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;

最后在php中对这两个语句进行分别查询10次,
前者花费时间 0.147433 秒
后者花费时间 0.015130 秒

执行效率需要0.02 sec.可惜的是,只有mysql 4.1.*以上才支持这样的子查询.
注意事项 查看官方手册,也说rand()放在ORDER BY 子句中会被执行多次,自然效率及很低。

以上的sql语句最后一条,本人实际测试通过,100W数据,瞬间出结果。

其他几种方法

SELECT * FROM tablename ORDER BY RAND() LIMIT 想要获取的数据条数;

SELECT *FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id LIMIT 想要获取的数据条数;

SELECT * FROM `table`  AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2 WHERE t1.id >= t2.id
ORDER BY t1.id ASC LIMIT 想要获取的数据条数;

SELECT * FROM `table`WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `table`))) ORDER BY id LIMIT 想要获取的数据条数;

SELECT * FROM `table` WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECT MIN(id) FROM `table`))) ORDER BY id LIMIT 想要获取的数据条数;

SELECT * FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id LIMIT 想要获取的数据条数;

1的查询时间>>2的查询时间>>5的查询时间>6的查询时间>4的查询时间>3的查询时间,也就是3的效率最高。

批量修改、增加、删除字段内容

在MYSQL中使用替换语句可以整批替换某字段的内容,也可以批量在原字段内容上加上或去掉字符。

命令总解:update 表的名称 set 替换字段=REPLACE(替换字段,原来内容,新内容)

举例说明:

1)批量替换

例一:
把“backupfile”表里“url”的字段内容为“http://google.com/”的全部改为“http://google.cn/

update backupfile set url=REPLACE(url,'http://google.com/','http://google.cn/')

例二:
把“wp_posts”表里的“post_content”字段里的“PJBlog”全部替换成“WordPress”

update wp_posts set post_content=REPLACE(post_content,'PJBlog','WordPress')

例三:
把“wp_comments”表里的“comment_content”字段里的“PJBlog”全部替换成“WordPress”

update wp_comments set comment_content=REPLACE(comment_content,'PJBlog','WordPress')

执行完了这段代码之后就会有提示多少条被替换了:

影响列数: 16 (查询花费 0.1297 秒)


2)根据条件增加字段的内容,如把file_number=1的记录的logical_name字段的内容前面加上tmp,后面加上end。

update backupfile set logical_name=REPLACE(logical_name,logical_name,'tmp'+logical_name+' end ') where file_number=1

3)根据条件去掉指定记录的前面2个字符。

update backupfile set logical_name=REPLACE(logical_name,logical_name,SUBSTRING(logical_name,3,len(logical_name)-2)) where file_number=1

4)根据条件去掉指定记录的后面4个字符。

update backupfile set logical_name=REPLACE(logical_name,logical_name,SUBSTRING(logical_name,1,len(logical_name)-4)) where file_number=2

原文地址:http://www.16hg.com/article.asp?id=181

最后修改:2022 年 01 月 19 日
如果觉得我的文章对你有用,请随意赞赏