MySql总结

目录

 

一、索引

二、数据库通过函数获取id

三、常用sql语句

（一）查询语句

（二）插入

（三）复制

（四）count

（五）嵌套子语句

（六）Join

（七）其他

---

一、索引

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。

组合索引，即一个索引包含多个列

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

查询的时候，先通过索引找到主键，然后通过主键查询这一行数据。

索引缺点：

1.索引可以大大提高查询速度，同时会降低更新表的速度。

因为更新表的时候，MYSQL不仅要保存数据，还要保存一下索引文件。

2.建立索引会占用磁盘空间的索引文件。

二、数据库通过函数获取id

1.新建 l3_squence_transport
sequence_name= transport
value= 57258110  起始值=l2_transport最大id*10
CREATE TABLE `l3_squence_transport` (
  `sequence_name` varchar(64) NOT NULL COMMENT '序列名称',
  `value` bigint(20) NOT NULL DEFAULT '0' COMMENT '当前值',
  PRIMARY KEY (`sequence_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.新建一个方法，更新 l3_squence_transport 的value值，每次加1
CREATE FUNCTION `NEXT_VAL_TRANSPORT`(
        sequence_name VARCHAR(64)
) 
    RETURNS bigint(20) 
BEGIN
    UPDATE l3_squence_transport t, (SELECT @current_val:=`value` FROM l3_squence_transport t2 WHERE t2.sequence_name=sequence_name) t3 SET t.value = t.value + 1 WHERE t.sequence_name =sequence_name AND @current_val=t.value;
    RETURN @current_val+1;
END 
3.获取value
SELECT NEXT_VAL_TRANSPORT('transport')

三、常用sql语句

（一）查询语句

where查询语句：
sql = 'select house_id from l2_house_temp where address ="{}"'.format(address)
查询去重之后的数据
select distinct(community_name)  from l2_house_self WHERE src_type ="6788"
查询范围
SELECT latitude,longtitude FROM  l2_house_scrapy  `h1` WHERE  `h1`.`city_id` = 802  AND district_id = 806  AND (    h1.latitude >= '31.220354197131247' AND h1.latitude <= '31.220354197131247'      AND h1.longtitude >= '121.4796917059789'     AND h1.longtitude <= '121.4796917059789')
group by 分组，取前100条，重复数据大于1的；
SELECT * from business_tile_2 GROUP BY hash_coor HAVING count(hash_coor)>1 limit 0,100
从分组里面求总数
select count(*) FROM (SELECT `hash_coor` ,count(*)as num from business_tile_1988 GROUP BY hash_coor HAVING count(hash_coor)=5)tt
查询每个学生成绩的最大分数值
SELECT a.stuname,MAX(a.score) AS score FROM stuscore a  GROUP BY a.`stuname` ;
查询前十条信息
select * from table_name limit 0,10 
查询重复数据
select business_name,business_locate,count(*) as count from l2_business_locate_v2 group by business_locate having count>1;

（二）插入

INSERT INTO  items(name,city,price,number,picture)  VALUES ('耐克运动鞋','广州',500,1000,'003.jpg'),('耐克运动鞋2','广州2',500,1000,'002.jpg');

（三）复制

复制表数据
INSERT INTO clone_tbl (runoob_id, runoob_title,
 runoob_author,submission_date) 
SELECT runoob_id,runoob_title,runoob_author,submission_date FROM runoob_tbl;
复制表中所有数据
insert into clone_tb select * from tb limit 0,10000

（四）count

count(*) 返回所有数据的数量，但是不会过滤其中的NULL值，
select count(*) from person;
如果有NULL值，在返回的结果中会被过滤掉
select count(country) from person;
过滤掉重复的查询key值和空值
SELECT COUNT(DISTINCT phone_num)  AS 列1 FROM house_self 
As是给列命名的 结果是：列1  为2

（五）嵌套子语句

SELECT语句中嵌套子查询
1.在WHERE子句里面使用SELECT语句。
 SELECT cust_name,cust_contact FROM customers WHERE cust_id IN(SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems
2.在SELECT语句里面使用SELECT语句。
查询每个客户的订单总数。
 SELECT cust_name,(SELECT COUNT(*) FROM orders WHERE orders.cust_id=customers.cust_id) AS orders FROM customers ORDER BY cust_name;

（六）Join

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
三张表关联查询
SELECT a.house_oid, d.business_id_v2 FROM `l3_house_interv` a
INNER JOIN `l3_house_relative` b ON a.`relative_id_main` = b.`relative_id` INNER JOIN l2_house_sp_802 d ON d.`house_uid` = b.`house_uid` WHERE a.`business_id_v2` = 0 AND a.city_id = 802
SELECT COUNT(a.`house_uid`)  FROM l2_house_sp_802 a    LEFT JOIN `l2_business_v2` b ON a.city_id = b.city_id AND a.business_id_v2 = b.id 
WHERE    a.district_id != b.district_id

（七）其他

清空表：delete from table_name

删除表：drop table table_name

BETWEEN 子句可以代替“大于等于XX，并且小于等于XX”这样的条件。

SELECT * FROM employee_tbl WHERE daily_typing_pages BETWEEN 170 AND 300;

AVG 函数用来在不同记录中找出某一字段的平均值。

如果想根据该表计算字段 dialy_typing_pages 的平均值，可以使用如下命令：

SELECT AVG(daily_typing_pages) FROM employee_tbl;

还可以使用 GROUP BY 子句来计算多种记录集的平均值。下面这个范例将计算每个人的所有记录的平均值，

SELECT name, AVG(daily_typing_pages) FROM employee_tbl GROUP BY name;

CONCAT 函数用于将两个字符串合并为一个字符串

如果需要将id、name以及work_date这三个字段值合并起来，可以使用如下命令：

SELECT CONCAT(id, name, work_date) FROM employee_tbl;

COUNT 函数的用法很简单，就是为了统计记录数。

假设要根据上表统计行的总数，当然可以这样做：

SELECT COUNT(*) FROM employee_tbl ;

SELECT COUNT(*) FROM employee_tbl WHERE name=”Zara”;

可以使用 GROUP BY 子句对列中的值进行分组。

如果你愿意，还可以对列实施某种计算。可以对分组的列使用COUNT、SUM以及AVG等函数。

根据上面这张表，我们来计算一下每位员工的工作天数。

SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;

可以使用 IN 子句代替许多 OR 条件。

SELECT * FROM employee_tbl WHERE daily_typing_pages IN ( 250, 220, 170 );

这条语句类似于

SELECT * FROM employee_tbl WHERE daily_typing_pages= 250 OR daily_typing_pages= 220 OR daily_typing_pages= 170;

MAX 函数就是用来寻找记录集中的最大值的。

SELECT MAX(daily_typing_pages) FROM employee_tbl;

还可以结合利用 GROUP BY 子句，找出每个名字的最大值：

SELECT id, name, MAX(daily_typing_pages) FROM employee_tbl GROUP BY name;

可以组合使用 MIN 和 MAX 函数找出最小值

SELECT MIN(daily_typing_pages) least, MAX(daily_typing_pages) max FROM employee_tbl;

MIN 函数用于寻找记录集中拥有最小值的记录。

SQRT 函数用来计算任何数字的平方根

可以使用 SELECT 语句来计算任何数字的平方根

要根据上表计算 dialy_typing_pages 字段的平方根

SELECT name, SQRT(daily_typing_pages) FROM employee_tbl;

mysql as

判断某个数据库种某个表是否存在

select count(*) from information_schema.TABLES t where t.TABLE_SCHEMA =”数据库名” and t.TABLE_NAME =”数据库表名”;

查询两条数据重复的sql

SELECT from (SELECT , CONCAT(name,code) as nameAndCode from tb_table) t WHERE t.nameAndCode in

(

SELECT nameAndCode from (SELECT CONCAT(name,code) as nameAndCode from tb_table) tt GROUP BY nameAndCode HAVING count(nameAndCode) > 1

)

​