SQL常用语句

在navicat中执行多条insert数据时，需要分号结尾。mysql通过分号判断sql语句是否结束。如果要在android中写sql可以先在navicat中创建，再复制粘贴即可。

1、根据日期查询

SELECT * FROM site WHERE create_time > ‘2018-09-18 08:59:11’ AND create_time < ‘2019-03-18’

具体的日期还可以加上时间，其中create_time是datetime类型的字段。

2、查询null 或者 非空

SELECT * FROM site WHERE delete_tag is (not) NULL

3、使用一个表来填充另一个表。

INSERT INTO simpleUser SELECT user.name, user.addr FROM user

其中，simpleUser表的列名可省略，但要与后面的列对应。

如果simpleUser中有一列test，但是user表中没有，可以通过如下方式赋予默认值

INSERT INTO simpleUser SELECT user.name, user.addr, 1 as test FROM user

将test列给予默认传值1。

在navicat中试验发现，select后的列名不能带单引号，否则被当作字符插入了而不是表名。 insert后如果带表名的话，有无单引号均不影响。

insert into blist_copy(SITE_URL, SITE_TYPE, OPERATE_TYPE, SITE_NAME, IS_TEMP) SELECT SITE_URL, SITE_TYPE, OPERATE_TYPE, SITE_NAME, IS_TEMP from blist

4、insert语句

INSERT INTO user value (‘shenxing’, 28, ‘狮城名居’, ‘18520390187’)

5、limit用法

SELECT * FROM student LIMIT 1,3 查询从第一条数据开始，总共3条数据

SELECT * FROM student LIMIT 10 查询前10条数据

6、delete语句

DELETE FROM site WHERE site_name = ‘repeatCom’

7、update语句

UPDATE site set site_name = ‘nonoCom’, site_url = ‘nono.com’ WHERE site_name = ‘noCom’

8、as用法

SELECT COUNT(*) as ‘条数’ FROM site

注意，表的列名可以在where子句中使用，但列的别名不能在where子句中使用。

9、where的用法

在where中可以使用的>, <, =, like, not, is not, in, between等。

SELECT * FROM site WHERE delete_tag is not null

SELECT * FROM site WHERE creator_type in(1, 2)

SELECT * FROM site WHERE creator_type BETWEEN 2 and 3

SELECT * FROM site WHERE site_name like ‘si%’。 在where中用%进行模糊搜索。

10、group by 分组

select class_name as ‘班级’, count(*) as ‘总数’ from user GROUP BY class_name

group by 要放在where后，order by 前。select子句的列必需要是分组列 或 列函数。 此例句中class_name是分组列，count是分组函数。

11、having，对分组的结果进行过滤

SELECT school_id as ‘学校编号’, count(school_id) as ‘账号个数’ from user group by school_id HAVING count(school_id) > 10

having 要紧跟在group by 后， 在order by前。

12、alter语句

ALTER TABLE testsql ADD prodnum varchar(255) 增加列

ALTER TABLE testsql DROP COLUMN prodnum 删除列，注意这里与上面多了一个关键字column。

ALTER TABLE databasename.表名 RENAME TO 新表名 （其中databasename可省略）

有时删除列的时候会报Can’t DROP ‘prodnum’; check that column/key exists错误，要注意是否存在某些约束。

13、子查询

SELECT * from score where stu_id = (select id from student where name = ‘李四’)

14、 两张表连接

select student.id, name, sex, birth, department, address, c_name, grade from student, score where student.id = score.stu_id

15、时间戳函数

select UNIX_TIMESTAMP(‘2019-03-22 13:30:00’) 及 SELECT UNIX_TIMESTAMP(NOW())

返回时间戳，注意返回的是秒数。如果换算成毫秒需要乘以1000。千万注意。

根据时间戳，返回时间，如下

SELECT FROM_UNIXTIME(1553232600) , 将时间戳转换为时间。其中参数的单位是秒。

16、查看数据库的用户

use mysql; // 使用mysql数据库

select * from user; // 查看用户表，其中有用户及权限信息

create user 用户名 identified by 密码

创建一个用户，刚创建的用户是没有任何权限的。可以通过grant语句授权。但可以通过修改user表相应的字段快速的授权。

drop user 用户名

删除相关的用户。

17、删除重复的数据

delete from xxx where id not in (select min(id) from xxx group by id.

先用聚合函数，统计重复的id，然后取出相同id中的较小值，作为删除条件。

---

关于select需要注意的：
from子句是最先执行的，而不是select。因为系统要将磁盘中的数据加载到缓存中，以便执行接下来的操作。

select语句是在大多数语句之后执行的，在尤其是from与group。

select a.x + a.y as z from a where z = 10

这样的语句是会报错的，如Unknown column ‘z’ in ‘where clause’。

在表连接时尽量使用join关键字，而不是逗号。

如将select * from a, b where clause 改写成
select * from a join b on a.x = b.y;(注意，其中将on写成where也是可以运行的，但是后面的其它的连接类型不支持where关键字)。

等值连接分为内连接和外连接，外连接以可以分为左连接（left join）、右连接（right join）、全连接（full join）。
内连接也即最普通的join。

左连接：右表中没有匹配，也从左表返回所有的行

右连接: 左表中没有匹配，也从右表返回所有的行

全连接：只要其中一个表中存在匹配，就返回行

使用join多张表的语法如下

... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON condition INNER|LEFT|RIGHT JOIN table3 ON condition where ... group by ... order by ...

使用join语法能很好的区分连接条件与过滤条件。所以应该尽量使用这种语法。

---

索引是一种加快查询的手段，避免了对全表进行搜索，从而提高查询的效率。类似于字典查字的过程。索引是建立在列上的数据结构，常用的结构是b-tree。如某张表有字段名name，并且在name上创建了索引，可以理解为以name为key，以这一条记录为value，存储在map中。在查询时直接在map数据结构中查询key为目标值的记录即可。这个数据结构有b-tree，哈希表等。

数据库的索引包含了指针，指向你在SQL中想要查询的值所在的行。

参考，索引。

---

sql语句中and的优先级比or要高，所以在处理同时存在and与or的sql语句时，需要适当的使用括号。
如where id = 1002 or id = 1003 and count > 10,则会过滤id=1002 和 id=1003并且count>10的行。

使用正则时，需要用REGEXP关键字，如下：

// 匹配以a或者b开头的login_name
select * from user where login_name REGEXP '^[a-b]'

// 特殊字符要加转义，如\\-查找-，\\.查找.