苗景云的博客-MySQL/MariaDB 常用SQL语句

MySQL/MariaDB 常用SQL语句

2018-10-26 浏览:1805

MySQL/MariaDB SQL单表个别字段ID查询

1	`select` `*` `from` `table` `where` `id` `in` `(1,3,5,7,9)`

MySQL/MariaDB SQL单表个别字段排除查询

1	`select` `*` `from` `table` `where` `id` `not` `in` `(2,4,6,8)`

示例
PHP中MySQL/MariaDB SQL单表个别字段ID查询的应用
1
<br>

MySQL/MariaDB SQL单表个别字段数据删除

1	`delete` `from` `table` `where` `id` `in` `(1,3,5,7,9)`

MySQL/MariaDB SQL单表个别字段数据排除删除

1	`delete` `from` `table` `where` `id` `not` `in` `(2,4,6,8)`

MySQL/MariaDB SQL查询字段中包含指定ID的数据

1	`select` `*` `from` `table` `where` `find_in_set(8,group);`

查询group里含有数字8的记录，group是varchar ,数据格式如:"1,12,8,18,5"

MySQL/MariaDB SQL查询字段中不包含指定ID的数据

1	`select` `*` `from` `table` `where` `!find_in_set(8,group);`

查询group里含有数字8的记录，group是varchar ,数据格式如:"1,12,8,18,5"

MySQL/MariaDB SQL多表联合查询

1 2	`SELECT` `` `FROM` `主表` `join` `表2 using(关联字段)` `SELECT` `` `FROM` `主表` `join` `表2 using(关联字段)` `join` `表3 using(关联字段)`

示例
1
select * from `table` join `table_data` using(`table_id`) join `table_info` using(`table_id`)
等价于
select 字段名 from 表1，表2 … where 表1.字段 = 表2.字段

MySQL/MariaDB SQL查询表是否存在

1	`select` `TABLE_NAME` `from` `INFORMATION_SCHEMA.TABLES` `where` `TABLE_SCHEMA='数据库名'` `and` `TABLE_NAME='表名'` `;`

MySQL/MariaDB SQL过滤指定重复字段

1	`select` `*,` `count(distinct` `name)` `from` `table` `group` `by` `name`

结果
1
2
3
4
id  name count(distinct name)
1    a      1
2    b      1
3    c      1

示例
1
SELECT * FROM `notes` WHERE `account_id`='75' group by `content_id`
1
select *,count(distinct content_id) from content join column using(`column_id`) join account using(`account_id`) join notes using(`content_id`) where content.content_id in (9,25,26) notes.account_id=75 group by content_id ORDER BY `notes_time` DESC limit 0,10