sql语句

1.查询select语句
select <列的集合> from <表名>
where <条件>
group by <列名>
having <条件>
order by <排序字段和方式>
limit startRow,number;

group by：根据给定列的值进行分组统计，可指定多列，列之间以逗号分割，列必须是分组列或列函数（列有重复值）。

having：跟随group by后面使用，对分组的结果添加限制条件。

order by：列名 ASC(升序-默认)或DESC(降序)

limit startRow,number 等同 limit number offset startRow:设置了select语句返回的记录数,
startRow:表示第一个返回记录行的偏移量，从0开始
number:表示返回记录行的最大数目
limit 5,10:表示返回记录行6-15
limit 95,-1：表示返回记录行96-last
limit 5:表示返回前5个记录行

提取特定列特定值+别的列的数据
select id,class,theme,labels from t_products where secrecy=1;

条件可以是=,包含部分内容
select * from urls where content like "%内容%";
%在mysql中表示字符串通配符

例子：
SELECT DEPT, MAX( SALARY ) AS MAXIMUM, MIN( SALARY ) AS MINIMUM #AS MAXIMUM：把函数生成的结果输出在这个列里
FROM staff
GROUP BY DEPT
HAVING COUNT( * ) >2 #表示根据DEPT列分组后选择组内数据个数大于2的才返回输出
ORDER BY DEPT

2.mysql函数
字符串函数：char_length(s)返回字符串s的字符数，concat(s1,s2,..)合并字符串
数字函数：avg(expression)返回表达式的平均值
max(exp),min(exp)
sum(expression):求和
日期函数
高级函数：cast(x as type)转换数据类型
coalesce(exp1,exp2,..)返回参数中第一个非空的表达式
distinct 列:独特的，去重作用
ifnull(v1,v2):v1值不为空，则返回v1,不然返回v2
isnull(exp):判断表达式是否为null

2.创建数据库-scraping
CREATE DATABASE scraping;

指定用的数据库
USE scraping;

3.创建数据表
CREATE TABLE urls(
id INT NOT NULL AUTO_INCREMENT,
url VARCHAR(1000) NOT NULL,
content VARCHAR(1000) NOT NULL,
created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);

创建数据表必须指明每一列数据的名称和类别，以及可以设置一些属性。

查看数据表结构
DESCRIBE urls;

4.在数据表中插入数据
INSERT INTO urls (url,content) VALUES ("www.baidu.com","这是内容");
同时插入多条数据
INSERT INTO urls (url,content) VALUES ("www.baidu.com","这是内容"),("",""),("",""),...;

5.删除数据
delete from urls where url="www.baidu.com";
dbcc checkident(你的表名,reseed,0) --重新置位identity属性的字段，让其下个值从1开始

truncate table tbname; #这个表再赋值的ID就从1开始了，删除表中所有行

6.修改数据
update urls set url="www.google.com",content="google" where id=1;

7.查询表中列不重复值个数
查询艺术品数据表中名称列的不重复值个数
select count(distinct 名称) as num from 艺术品;

8.查询表中列单独值及其个数
select 作者,COUNT(*) as count from 艺术品 group by 作者;

9.更新数据库列信息
UPDATE 艺术品 SET 尺寸=substring_index(艺术品.`尺寸`, '价格', 1) WHERE id>28483 AND id<34263 AND `艺术品`.`尺寸` LIKE "%价格%" ;
UPDATE 艺术品 SET 价格='议价' WHERE id>28483 AND id<34263 AND `艺术品`.`价格` LIKE "%议价%" ;
UPDATE 艺术品 SET `艺术品`.`下载图片名称`= substring_index(substring_index(`下载图片url`,'/', -1),
'.',(LENGTH(substring_index(`下载图片url`,'/', -1)) - LENGTH(REPLACE(substring_index(`下载图片url`,'/', -1),'.',''))))
WHERE id>1;
UPDATE `art` SET img_name=CONCAT('zhaopianguan|',img_name) WHERE id>6105 and id<8982;

10.字符串操作函数
SUBSTRING_INDEX(str, delim, count)
str: 要处理的字符串
delim: 分割符
count: 计数 如果为正数，则从左开始数，如果为负数，则从右开始数
UPDATE new_prd_products_add_clean_all SET genre = substring_index(genre,'|', 1) WHERE genre LIKE '%|%';
例：
str = 'www.baidu.com';
SELECT substring_index('www.baidu.com','.', 1);??? #www
SELECT substring_index('www.baidu.com','.', 2);??? #www.baidu
SELECT substring_index('www.baidu.com','.', -1);?? #com
SELECT substring_index('www.baidu.com','.', -2);?? #baidu.com
SELECT substring_index(substring_index('www.baidu.com','.', -2), '.', 1);? #baidu

-拼接
concat('test',str)
-替换
replace(str,s1,s2)用s2替换s中的s1
UPDATE new_prd_products_add_clean SET theme_ch = REPLACE(theme_ch, '-', '、');
-小写，大写lower(), upper()
UPDATE artist_ch SET personName = LOWER(personName);
-转数据类型
int转string：ltrim(列名)
UPDATE dict_year SET yearID = CONCAT('year', ltrim(id)) WHERE id>1946;
-取字符串最后一个字符
RIGHT(createdYear,1)
-删除最后一个字符
SUBSTRING(years,0,LENGTH(years)-1)
UPDATE data_art_prd SET years=SUBSTRING(years,0,LENGTH(years)-1) WHERE RIGHT(years,1)='年';

11.查询不重复的记录
select id,name from user group by name;

12.查重
select * from table where name in (select name from table group by name having count(name)>1);

13、根据一张表的字段更新到另一张表update join on set
update table1 s join table2 k on (s.关联字段=k.关联字段) set s.要改变的字段=k.对应的字段

update new_prd_products_add_clean s join new_prd_products_add k on (s.athena=k.athena) set s.size=k.size WHERE k.athena = '黄礼攸';

14、两个表合并成一个表,合并行
CREATE TABLE new_prd_products_add_clean_all select * from new_prd_products_add_clean
UNION ALL
select * from new_prd_products11_add_clean;

UNION与UNION ALL的区别：
1、对重复结果的处理：UNION在进行表链接后会去重，UNION All不会。
2、对排序的处理：Union将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。
UNION ALL 要比UNION效率高，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。

15.根据一个表的一个列信息查询另一个表的信息
SELECT * FROM new_prd_products_add_clean_all WHERE athena not in (SELECT name_ch FROM artist_prd);

16.判断一个表是否存在
select table_name from information_schema.TABLES where table_name='表名';
select count(*) from information_schema.TABLES where table_name='表名';

17.判断表的一个字段是否存在
select count(*) from information_schema.columns where table_name='表名' and column_name='列名';