Mysql基础学习一之SQL入门

mysql查询不同用户的最新一条记录
法一：

SELECT * from (

SELECT * from oct_hr_user_clock ORDER BY clock_time desc limit 10000

) v GROUP BY v.user_id

注意：limit必须要加

方法二：

SELECT * from oct_hr_user_clock a

join

(

SELECT max(clock_time) as clock_time,user_id from oct_hr_user_clock GROUP BY user_id

) b

on a.user_id=b.user_id

and a.clock_time=b.clock_time

MySQL 查询每个人的最新一条记录 group by + order by?
思路
原本是这样想的查询出这批人的数据然后排个序, 然后再按personId聚合到一起, 取第一个
结果并不是想象中的这样的…
order by 不生效
好像是MySQL 5.7之后的版本sql语句优化改了

查询语句
select t.person_id, t.evaluation_time from (
select person_id, evaluation_time from evaluation_risk_statistic s where s.person_id in (
‘p170807171522173’, ‘p160504081110285’, ‘p180117173210001’, ‘p160307185810006’
) order by s.evaluation_time desc
) as t
group by t.person_Id;

查询结果

正确做法
按personId取每个人最新的数据

查询条件
SELECT t1.*
FROM evaluation_risk_statistic AS t1
INNER JOIN
(
SELECT t2.person_id, MAX(t2.evaluation_time) AS maxdate
FROM evaluation_risk_statistic AS t2
where t2.person_id in (
‘p170807171522173’, ‘p160504081110285’, ‘p180117173210001’, ‘p160307185810006’
)
GROUP BY t2.person_id
) AS t3 ON t1.person_id = t3.person_id AND t1.evaluation_time = t3.maxdate;

查询结果

本文实例讲述了MySQL 多表关联一对多查询实现取最新一条数据的方法
数据测试初始化SQL脚本

DROP TABLE IF EXISTS customer;
CREATE TABLE customer (
id BIGINT NOT NULL COMMENT ‘客户ID’,
real_name VARCHAR(20) NOT NULL COMMENT ‘客户名字’,
create_time DATETIME NOT NULL COMMENT ‘创建时间’,
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET = UTF8 COMMENT ‘客户信息表’;

– DATA FOR TABLE customer
INSERT INTO demo.customer (id, real_name, create_time) VALUES (‘7717194510959685632’, ‘张三’, ‘2019-01-23 16:23:05’);
INSERT INTO demo.customer (id, real_name, create_time) VALUES (‘7718605481599623168’, ‘李四’, ‘2019-01-23 16:23:05’);
INSERT INTO demo.customer (id, real_name, create_time) VALUES (‘7720804666226278400’, ‘王五’, ‘2019-01-23 16:23:05’);
INSERT INTO demo.customer (id, real_name, create_time) VALUES (‘7720882041353961472’, ‘刘六’, ‘2019-01-23 16:23:05’);
INSERT INTO demo.customer (id, real_name, create_time) VALUES (‘7722233303626055680’, ‘宝宝’, ‘2019-01-23 16:23:05’);
INSERT INTO demo.customer (id, real_name, create_time) VALUES (‘7722233895811448832’, ‘小宝’, ‘2019-01-23 16:23:05’);
INSERT INTO demo.customer (id, real_name, create_time) VALUES (‘7722234507982700544’, ‘大宝’, ‘2019-01-23 16:23:05’);
INSERT INTO demo.customer (id, real_name, create_time) VALUES (‘7722234927631204352’, ‘二宝’, ‘2019-01-23 16:23:05’);
INSERT INTO demo.customer (id, real_name, create_time) VALUES (‘7722235550724423680’, ‘小贱’, ‘2019-01-23 16:23:05’);
INSERT INTO demo.customer (id, real_name, create_time) VALUES (‘7722235921488314368’, ‘小明’, ‘2019-01-23 16:23:05’);
INSERT INTO demo.customer (id, real_name, create_time) VALUES (‘7722238233975881728’, ‘小黑’, ‘2019-01-23 16:23:05’);
INSERT INTO demo.customer (id, real_name, create_time) VALUES (‘7722246644138409984’, ‘小红’, ‘2019-01-23 16:23:05’);
INSERT INTO demo.customer (id, real_name, create_time) VALUES (‘7722318634321346560’, ‘阿狗’, ‘2019-01-23 16:23:05’);
INSERT INTO demo.customer (id, real_name, create_time) VALUES (‘7722318674321346586’, ‘阿娇’, ‘2019-01-23 16:23:05’);
INSERT INTO demo.customer (id, real_name, create_time) VALUES (‘7722318974421546780’, ‘阿猫’, ‘2019-01-23 16:23:05’);

DROP TABLE IF EXISTS order_info;
CREATE TABLE order_info (
id BIGINT NOT NULL COMMENT ‘订单ID’,
industry VARCHAR(255) DEFAULT NULL COMMENT ‘所属行业’,
nature_tax VARCHAR(255) DEFAULT NULL COMMENT ‘纳税性质’,
customer_id VARCHAR(20) NOT NULL COMMENT ‘客户ID’,
create_time DATETIME NOT NULL COMMENT ‘创建时间’,
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET = UTF8 COMMENT ‘订单信息表’;

– DATA FOR TABLE order_info
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7700163609453207552’, ‘餐饮酒店类’, ‘小规模’, ‘7717194510959685632’, ‘2019-01-23 16:54:25’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7700163609453207553’, ‘餐饮酒店类’, ‘小规模’, ‘7717194510959685632’, ‘2019-01-23 17:09:53’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7700167995646615552’, ‘高新技术’, ‘一般纳税人’, ‘7718605481599623168’, ‘2019-01-23 16:54:25’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7700167995646615553’, ‘商贸’, ‘一般纳税人’, ‘7718605481599623168’, ‘2019-01-23 17:09:53’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7700193633216569344’, ‘商贸’, ‘一般纳税人’, ‘7720804666226278400’, ‘2019-01-23 16:54:25’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7700193633216569345’, ‘高新技术’, ‘一般纳税人’, ‘7720804666226278400’, ‘2019-01-23 17:09:53’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7700197875671179264’, ‘餐饮酒店类’, ‘一般纳税人’, ‘7720882041353961472’, ‘2019-01-23 16:54:25’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7700197875671179266’, ‘餐饮酒店类’, ‘一般纳税人’, ‘7720882041353961472’, ‘2019-01-23 17:09:53’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7703053372673171456’, ‘高新技术’, ‘小规模’, ‘7722233303626055680’, ‘2019-01-23 16:54:25’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7703053372673171457’, ‘高新技术’, ‘小规模’, ‘7722233303626055680’, ‘2019-01-23 17:09:53’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7709742385262698496’, ‘服务类’, ‘一般纳税人’, ‘7722233895811448832’, ‘2019-01-23 16:54:25’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7709742385262698498’, ‘服务类’, ‘一般纳税人’, ‘7722233895811448832’, ‘2019-01-23 17:09:53’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7709745055683780608’, ‘高新技术’, ‘小规模’, ‘7722234507982700544’, ‘2019-01-23 16:54:25’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7709745055683780609’, ‘进出口’, ‘小规模’, ‘7722234507982700544’, ‘2019-01-23 17:09:53’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7709745249439653888’, ‘文化体育’, ‘一般纳税人’, ‘7722234927631204352’, ‘2019-01-24 16:54:25’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7709745249439653889’, ‘高新技术’, ‘一般纳税人’, ‘7722234927631204352’, ‘2019-01-23 17:09:53’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7709745453266051072’, ‘高新技术’, ‘小规模’, ‘7722235550724423680’, ‘2019-01-24 16:54:25’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7709745453266051073’, ‘文化体育’, ‘小规模’, ‘7722235550724423680’, ‘2019-01-23 17:09:53’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7709745539848413184’, ‘科技’, ‘一般纳税人’, ‘7722235921488314368’, ‘2019-01-24 16:54:25’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7709745539848413185’, ‘高新技术’, ‘一般纳税人’, ‘7722235921488314368’, ‘2019-01-23 17:09:53’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7709745652603887616’, ‘高新技术’, ‘一般纳税人’, ‘7722238233975881728’, ‘2019-01-24 16:54:25’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7709745652603887617’, ‘科技’, ‘一般纳税人’, ‘7722238233975881728’, ‘2019-01-23 17:09:53’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7709745755528568832’, ‘进出口’, ‘一般纳税人’, ‘7722246644138409984’, ‘2019-01-24 16:54:25’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7709745755528568833’, ‘教育咨询’, ‘小规模’, ‘7722246644138409984’, ‘2019-01-23 17:09:53’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7709745892539047936’, ‘教育咨询’, ‘一般纳税人’, ‘7722318634321346560’, ‘2019-01-24 16:54:25’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7709745892539047937’, ‘进出口’, ‘一般纳税人’, ‘7722318634321346560’, ‘2019-01-23 17:09:53’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7709746000127139840’, ‘生产类’, ‘小规模’, ‘7722318674321346586’, ‘2019-01-24 16:54:25’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7709746000127139841’, ‘农业’, ‘一般纳税人’, ‘7722318674321346586’, ‘2019-01-23 17:09:53’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7709746447445467136’, ‘农业’, ‘一般纳税人’, ‘7722318974421546780’, ‘2019-01-24 16:54:25’);
INSERT INTO demo.order_info (id, industry, nature_tax, customer_id, create_time) VALUES (‘7709746447445467137’, ‘生产类’, ‘小规模’, ‘7722318974421546780’, ‘2019-01-23 17:09:53’);
按需求写的SQL语句：
1
UPDATE order_info SET create_time = NOW();
尝试解决问题

SELECT
cr.id,
cr.real_name,
oi.industry,
oi.nature_tax
FROM
customer AS cr
LEFT JOIN (
SELECT a.industry, a.nature_tax, a.customer_id, a.create_time FROM order_info AS a
LEFT JOIN (
SELECT MAX(create_time) AS create_time, customer_id FROM order_info GROUP BY customer_id
) AS b ON a.customer_id = b.customer_id
WHERE a.create_time = b.create_time
) AS oi ON oi.customer_id = cr.id
GROUP BY cr.id;
数据重复嘛，小意思，加个 GROUP BY 不就解决了吗？我怎么会这么机智，哈哈哈！！！但是当我执行完SQL的那一瞬间，我又懵逼了，查询出来的结果中 所属行业，纳税性质 仍然不是最新的；看来是我想太多了，还是老老实实的解决问题吧。。。

找出重复数据

SELECT
cr.id,
cr.real_name,
oi.industry,
oi.nature_tax
FROM
customer AS cr
LEFT JOIN (
SELECT a.industry, a.nature_tax, a.customer_id, a.create_time FROM order_info AS a
LEFT JOIN (
SELECT MAX(create_time) AS create_time, customer_id FROM order_info GROUP BY customer_id
) AS b ON a.customer_id = b.customer_id
WHERE a.create_time = b.create_time
) AS oi ON oi.customer_id = cr.id
GROUP BY cr.id HAVING COUNT(cr.id) >= 2;
执行结果如下：

SELECT
cr.id,
cr.real_name,
oi.industry,
oi.nature_tax
FROM
customer AS cr
LEFT JOIN (
SELECT a.industry, a.nature_tax, a.customer_id, a.create_time FROM order_info AS a
LEFT JOIN (
SELECT MAX(id) AS id, customer_id FROM order_info GROUP BY customer_id
) AS b ON a.customer_id = b.customer_id
WHERE a.id = b.id
) AS oi ON oi.customer_id = cr.id;
哎，终于解决了。。。

mysql 分组之后 取分组之后最新的数据

二、查询场景

统计每门课的考试次数、最新一次考试的时间、最新一次考试的录入成绩的老师

1、统计没门课的考试次数

#考试次数统计
select project ‘科目’,count(project) ‘考试次数’ from score a group by project

2、最新一次考试的时间

#考试次数统计 最新一次考试的时间
select project ‘科目’,count(project)
‘考试次数’ ,max(create_time) from score a group by project

3、分组统计最新的录入成绩的老师

当我们分组去查询最新的录入成绩的老师或者分组查询最新一次各科的成绩时确发现数据不是最新的。

SELECT
a.id,
a.edit_teacher,
a.project,
a.create_time,
a.score,
count(project) ‘考试次数’,
max(create_time) ‘最新数据时间’
FROM
score a
GROUP BY
a.project
但是很显然我们需要查询的数据id应该是4、8、12
可以看出分组聚合的时候默认查询的是分组之后的第一条数据，那么我们想要查询最新的数据需要新对我们的数据进行排序

SELECT
*,
count( project ) ‘考试次数’,
max(create_time) ‘最新数据时间’
FROM
(
SELECT
a.id,
a.edit_teacher,
a.project,
a.create_time,
a.score
FROM
score a
ORDER BY
a.id DESC
) b
GROUP BY
b.project

我们发现数据并不是我们想要的结果，子查询里面的排序失效了

网上查找各种资料发现

子查询生成的临时表（派生表derived table）中使用order by且使其生效，必须满足三个条件：

1、外部查询禁止分组或者聚合

2、外部查询未指定having，HAVING， order by

3、外部查询将派生表或者视图作为from句中唯一指定源

显然我们没有满足，那么如何解决order by失效呢？

我们外部表使用了group by，那么临时表将不会执行filesort操作（即order by会被忽略），所以我们可以在临时表中加上（distinct(a.id)）。

SELECT
	*,
	count( project ) '考试次数' ,
	max(create_time) '最新数据时间' 
FROM
	(
SELECT DISTINCT
	a.id,
	a.edit_teacher,
	a.project,
	a.create_time,
	a.score 
FROM
	score a 
ORDER BY
	a.id DESC 
	) b 
GROUP BY
	b.project

结果正确。

用户

# 新建用户
CREATE USER name IDENTIFIED BY 'ainiyou';
# 更改密码
SET PASSWORD FOR name=PASSWORD('ainiyou');
权限管理 
 //查看name用户权限
SHOW GRANTS FOR name;   
 //给name用户db_name数据库的所有权限
GRANT SELECT ON db_name.* TO name;　　　　
 //GRANT的反操作，去除
REVOKE SELECT ON db_name.* TO name;　　　　
``
# 操作数据库
```clike
# 查看所有的数据库
SHOW DATABASES ;
# 创建一个数据库
CREATE DATABASE k;
# 删除一个数据库
DROP DATABASE k;
# 使用这个数据库
USE k;

表

# 查看所有的表
SHOW TABLES ;
# 创建一个表
CREATE TABLE n(id INT, name VARCHAR(10));
CREATE TABLE m(id INT, name VARCHAR(10), PRIMARY KEY (id), FOREIGN KEY (id) REFERENCES n(id), UNIQUE (name));
CREATE TABLE m(id INT, name VARCHAR(10));
# 直接将查询结果导入或复制到新创建的表
CREATE TABLE n SELECT * FROM m;
# 新创建的表与一个存在的表的数据结构类似
CREATE TABLE m LIKE n;
# 创建一个临时表
# 临时表将在你连接MySQL期间存在。当断开连接时，MySQL将自动删除表并释放所用的空间。也可手动删除。
CREATE TEMPORARY TABLE l(id INT, name VARCHAR(10));
# 直接将查询结果导入或复制到新创建的临时表
CREATE TEMPORARY TABLE tt SELECT * FROM n;
# 删除一个存在表
DROP TABLE IF EXISTS m;
# 更改存在表的名称
ALTER TABLE n RENAME m;
RENAME TABLE n TO m;
# 查看表的结构(以下五条语句效果相同）
DESC n;   # 因为简单，所以建议使用
DESCRIBE n;
SHOW COLUMNS IN n;
SHOW COLUMNS FROM n;
EXPLAIN n;
# 查看表的创建语句
SHOW CREATE TABLE n;

1.创建表：
 
　　　　>CREATE TABLE table_name(
 
　　　　>id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,　　　　//id值，无符号、非空、递增——唯一性，可做主键。
 
　　　　>name VARCHAR(60) NOT NULL
 
　　　　>score TINYINT UNSIGNED NOT NULL DEFAULT 0,　　　　//设置默认列值
 
　　　　>PRIMARY KEY(id)
 
　　　　>)ENGINE=InnoDB　　　　//设置表的存储引擎，一般常用InnoDB和MyISAM；InnoDB可靠，支持事务；MyISAM高效不支持全文检索
 
　　　　>DEFAULT charset=utf8;　　//设置默认的编码，防止数据库中文乱码
 
　　　　如果有条件的创建数据表还可以使用   >CREATE TABLE IF NOT EXISTS tb_name(........
 
　　2、复制表：
 
　　　　>CREATE TABLE tb_name2 SELECT * FROM tb_name;
 
　　　　或者部分复制：
 
　　　　>CREATE TABLE tb_name2 SELECT id,name FROM tb_name;
 
　　3、创建临时表：
 
　　　　>CREATE TEMPORARY TABLE tb_name(这里和创建普通表一样);
 
　　4、查看数据库中可用的表：
 
　　　　>SHOW TABLES;
 
　　5、查看表的结构：
 
　　　　>DESCRIBE tb_name;
 
　　　　也可以使用：
 
　　　　>SHOW COLUMNS in tb_name; 　　　　//from也可以
 
　　6、删除表：
 
　　　　>DROP [ TEMPORARY ] TABLE [ IF EXISTS ] tb_name[ ,tb_name2.......];
 
　　　　实例：
 
　　　　>DROP TABLE IF EXISTS tb_name;
 
　　7、表重命名：
 
　　　　>RENAME TABLE name_old TO name_new;
 
　　　　还可以使用：
 
　　　　>ALTER TABLE name_old RENAME name_new;

表的结构

# 添加字段
ALTER TABLE n ADD age VARCHAR(2) ;
# 删除字段
ALTER TABLE n DROP age;
# 更改字段属性和属性
ALTER TABLE n CHANGE age a INT;
# 只更改字段属性
ALTER TABLE n MODIFY age VARCHAR(7) ;
　1、更改表结构：
 
　　　　>ALTER TABLE tb_name ADD[CHANGE,RENAME,DROP] ...要更改的内容...
 
　　　　实例：
 
　　　　>ALTER TABLE tb_name ADD COLUMN address varchar(80) NOT NULL;
 
　　　　>ALTER TABLE tb_name DROP address;
 
　　　　>ALTER TABLE tb_name CHANGE score score SMALLINT(4) NOT NULL

表的数据

# 增加数据
INSERT INTO n VALUES (1, 'tom', '23'), (2, 'john', '22');
INSERT INTO n SELECT * FROM n;  # 把数据复制一遍重新插入
# 删除数据
DELETE FROM n WHERE id = 2;
# 更改数据
UPDATE n SET name = 'tom' WHERE id = 2;
# 数据查找
SELECT * FROM n WHERE name LIKE '%h%';
# 数据排序(反序)
SELECT * FROM n ORDER BY name, id DESC ;

条件控制：

1、WHERE 语句：

>SELECT * FROM tb_name WHERE id=3;

2、HAVING 语句：

>SELECT * FROM tb_name GROUP BY score HAVING count(*)>2

3、相关条件控制符：

=、>、<、<>、IN(1,2,3…)、BETWEEN a AND b、NOT

AND 、OR

Linke()用法中 % 为匹配任意、 _ 匹配一个字符（可以是汉字）

IS NULL 空值检测

八、MySQL的正则表达式：

1、Mysql支持REGEXP的正则表达式：

>SELECT * FROM tb_name WHERE name REGEXP ‘¹’ //找出以A-D 为开头的name

2、特殊字符需要转义。

九、MySQL的一些函数：

1、字符串链接——CONCAT()

>SELECT CONCAT(name,‘=>’,score) FROM tb_name

2、数学函数：

AVG、SUM、MAX、MIN、COUNT；

3、文本处理函数：

TRIM、LOCATE、UPPER、LOWER、SUBSTRING

4、运算符：

+、-、*、\

5、时间函数：

DATE()、CURTIME()、DAY()、YEAR()、NOW()…

十、分组查询：

1、分组查询可以按照指定的列进行分组：

>SELECT COUNT() FROM tb_name GROUP BY score HAVING COUNT()>1;

2、条件使用Having；

3、ORDER BY 排序：

ORDER BY DESC|ASC　　　　=>按数据的降序和升序排列

十一、UNION规则——可以执行两个语句（可以去除重复行）

十二、全文检索——MATCH和AGAINST

1、SELECT MATCH(note_text)AGAINST(‘PICASO’) FROM tb_name;

2、InnoDB引擎不支持全文检索，MyISAM可以；

十三、视图

1、创建视图

>CREATE VIEW name AS SELECT * FROM tb_name WHERE ~~ ORDER BY ~~;

2、视图的特殊作用：

a、简化表之间的联结（把联结写在select中）；

b、重新格式化输出检索的数据（TRIM，CONCAT等函数）；

c、过滤不想要的数据（select部分）

d、使用视图计算字段值，如汇总这样的值。

十四、使用存储过程：

个人理解，存储过程就是一个自定义函数，有局部变量参数，可传入参数，可以返回值，不过这语法够呆滞的~~~

1、创建存储过程：

>CREATE PROCEDURE pro(

>IN num INT,OUT total INT)

>BEGIN

>SELECT SUM(score) INTO total FROM tb_name WHERE id=num;

>END;

***这里的 IN (传递一个值给存储过程)，OUT（从存储过程传出一个值），INOUT（对存储过程传入、传出），INTO（保存变量）

2、调用存储过程：

>CALL pro(13,@total)　　　　　　//这里的存储过程两个变量，一个是IN一个是OUT，这里的OUT也是需要写上的，不写会出错

>SELECT @total　　　　　　　　　//这里就可以看到结果了；

3、存储过程的其他操作：

>SHOW PROCEDURE STATUS;　　　　　　//显示当期的存储过程

>DROP PROCEDURE pro;　　　　　　　　　//删除指定存储过程

十五、使用游标：

对这个理解不是很懂，朋友多多指点哦~~~

1、游标的操作

>CREATE PROCEDURE pro()

>BEGIN

>DECLARE ordername CURSOR FOR

>SELECT order_num FROM orders;

>END;

>OPEN ordername;　　　　//打开游标

>CLOSE ordername;　　　　//关闭游标

十六、触发器：

触发器是指在进行某项指定操作时，触发触发器内指定的操作；

1、支持触发器的语句有DELETE、INSERT、UPDATE,其他均不支持

2、创建触发器：

>CREATE TRIGGER trig AFTER INSERT ON ORDERS FOR EACH ROW SELECT NEW.orser_name;

>INSERT语句，触发语句，返回一个值

3、删除触发器

>DROP TRIGGER trig;

十七、语法整理：

1、ALTER TABLE（修改表）

ALTER TABLE table_name

(　　ADD　　　　column　　datatype 　　[ NULL | NOT NULL ]　　[ CONSTRAINTS ]

CHANGE　　column 　　datatype 　　COLUMNS　　[ NULL | NOT NULL ]　　 [ CONSTRAINTS ]

DROP　　　 column，

。。。。

)

2、COMMIT(处理事务)

>COMMIT;

3、CREATE INDEX(在一个或多个列上创建索引)

CREATE INDEX index_name ON tb_name (column [ ASC | DESC ] , …);

4、CREATE PROCEDURE (创建存储过程)

CREATE PROCEDURE pro([ parameters ])

BEGIN

…

END

5、CREATE TABLE(创建表)

CREATE TABLE tb_name(

column_name　　datetype　　[ NULL | NOT NULL ] 　　[ condtraints] ,

column_name　　datetype　　[ NULL | NOT NULL ] 　　[ condtraints] ,

…

PRIMARY KEY( column_name )

)ENGINE=[ InnoDB | MyiSAM ]DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

6、CREATE USER(创建用户)

CREATE USER user_name [ @hostname ] [ IDENTIFIED BY [ PASSWORD ] ‘pass_word’ ];

7、CREATE VIEW （在一个或多个表上创建视图）

CREATE [ OR REPLACE ] VIEW view_name AS SELECT。。。。。。

8、DELETE (从表中删除一行或多行)

DELETE FROM table_name [WHERE …]

9、DROP(永久删除数据库及对象，如视图、索引等)

DROP DATEBASE | INDEX | PROCEDURE | TABLE | TRIGGER | USER | VIEW name

10、INSERT （给表添加行）

INSERT INTO tb_name [ ( columns,… ) ] VALUES(value1,…);

使用SELECT值插入：

INSERT INTO tb_name [ ( columns,… ) ]

SELECT columns , … FROM tb_name [ WHERE … ] ;

11、ROLLBACK（撤销一个事务处理块）

ROLLBACK [ TO savapointname ];

12、SAVEPOINT(为ROLLBACK设置保留点)

SAVEPOINT sp1;

13、SELECT (检索数据，显示信息)

SELECT column_name,…FROM tb_name [ WHERE ] [ UNION ] [ RROUP BY ] [ HAVING ] [ ORDER BY ]

14、START TRANSACTION (一个新的事务处理块的开始)

START TRANSACTION

15、UPDATE(更新一个表中的一行或多行)

UPDATE tb_name SET column=value,…[ where ]

键

# 添加主键
ALTER TABLE n ADD PRIMARY KEY (id);
ALTER TABLE n ADD CONSTRAINT pk_n PRIMARY KEY (id);   # 主键只有一个，所以定义键名似乎也没有什么用
# 删除主键
ALTER TABLE n DROP PRIMARY KEY ;
# 添加外键
ALTER TABLE m ADD FOREIGN KEY (id) REFERENCES n(id);    # 自动生成键名m_ibfk_1
ALTER TABLE m ADD CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES n(id);   # 使用定义的键名fk_id
# 删除外键
ALTER TABLE m DROP FOREIGN KEY `fk_id`;
# 修改外键
ALTER TABLE m DROP FOREIGN KEY `fk_id`, ADD CONSTRAINT fk_id2 FOREIGN KEY (id) REFERENCES n(id);    # 删除之后从新建
# 添加唯一键
ALTER TABLE n ADD UNIQUE (name);
ALTER TABLE n ADD UNIQUE u_name (name);
ALTER TABLE n ADD UNIQUE INDEX u_name (name);
ALTER TABLE n ADD CONSTRAINT u_name UNIQUE (name);
CREATE UNIQUE INDEX u_name ON n(name);
# 添加索引
ALTER TABLE n ADD INDEX (age);
ALTER TABLE n ADD INDEX i_age (age);
CREATE INDEX i_age ON n(age);
# 删除索引或唯一键
DROP INDEX u_name ON n;
DROP INDEX i_age ON n;

视图

# 创建视图
CREATE VIEW v AS SELECT id, name FROM n;
CREATE VIEW v(id, name) AS SELECT id, name FROM n;
# 查看视图(与表操作类似)
SELECT * FROM v;
DESC v;
# 查看创建视图语句
SHOW CREATE VIEW v;
# 更改视图
CREATE OR REPLACE VIEW v AS SELECT name, age FROM n;
ALTER VIEW v AS SELECT name FROM n ;
# 删除视图
DROP VIEW IF EXISTS v;

联接

# 内联接
SELECT * FROM m INNER JOIN n ON m.id = n.id;
# 左外联接
SELECT * FROM m LEFT JOIN n ON m.id = n.id;
# 右外联接
SELECT * FROM m RIGHT JOIN n ON m.id = n.id;
# 交叉联接
SELECT * FROM m CROSS JOIN n;   # 标准写法
SELECT * FROM m, n;
# 类似全连接full join的联接用法
SELECT id,name FROM m
UNION
SELECT id,name FROM n;

函数

# 聚合函数
SELECT count(id) AS total FROM n;   # 总数
SELECT sum(age) AS all_age FROM n;   # 总和
SELECT avg(age) AS all_age FROM n;   # 平均值
SELECT max(age) AS all_age FROM n;   # 最大值
SELECT min(age) AS all_age FROM n;   # 最小值
# 数学函数
SELECT abs(-5);   # 绝对值
SELECT bin(15), oct(15), hex(15);   # 二进制，八进制，十六进制
SELECT pi();   # 圆周率3.141593
SELECT ceil(5.5);   # 大于x的最小整数值6
SELECT floor(5.5);   # 小于x的最大整数值5
SELECT greatest(3,1,4,1,5,9,2,6);   # 返回集合中最大的值9
SELECT least(3,1,4,1,5,9,2,6);    # 返回集合中最小的值1
SELECT mod(5,3);    # 余数2
SELECT rand();    # 返回０到１内的随机值，每次不一样
SELECT rand(5);   # 提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
SELECT round(1415.1415);   # 四舍五入1415
SELECT round(1415.1415, 3);   # 四舍五入三位数1415.142
SELECT round(1415.1415, -1);    # 四舍五入整数位数1420
SELECT truncate(1415.1415, 3);    # 截短为3位小数1415.141
SELECT truncate(1415.1415, -1);   # 截短为-1位小数1410
SELECT sign(-5);    # 符号的值负数-1
SELECT sign(5);    # 符号的值正数1
SELECT sqrt(9);   # 平方根3
SELECT sqrt(9);   # 平方根3
# 字符串函数
SELECT concat('a', 'p', 'p', 'le');   # 连接字符串-apple
SELECT concat_ws(',', 'a', 'p', 'p', 'le');   # 连接用','分割字符串-a,p,p,le
SELECT insert('chinese', 3, 2, 'IN');    # 将字符串'chinese'从3位置开始的2个字符替换为'IN'-chINese
SELECT left('chinese', 4);   # 返回字符串'chinese'左边的4个字符-chin
SELECT right('chinese', 3);   # 返回字符串'chinese'右边的3个字符-ese
SELECT substring('chinese', 3);   # 返回字符串'chinese'第三个字符之后的子字符串-inese
SELECT substring('chinese', -3);   # 返回字符串'chinese'倒数第三个字符之后的子字符串-ese
SELECT substring('chinese', 3, 2);   # 返回字符串'chinese'第三个字符之后的两个字符-in
SELECT trim(' chinese ');    # 切割字符串' chinese '两边的空字符-'chinese'
SELECT ltrim(' chinese ');    # 切割字符串' chinese '两边的空字符-'chinese '
SELECT rtrim(' chinese ');    # 切割字符串' chinese '两边的空字符-' chinese'
SELECT repeat('boy', 3);    # 重复字符'boy'三次-'boyboyboy'
SELECT reverse('chinese');    # 反向排序-'esenihc'
SELECT length('chinese');   # 返回字符串的长度-7
SELECT upper('chINese'), lower('chINese');    # 大写小写 CHINESE    chinese
SELECT ucase('chINese'), lcase('chINese');    # 大写小写 CHINESE    chinese
SELECT position('i' IN 'chinese');    # 返回'i'在'chinese'的第一个位置-3
SELECT position('e' IN 'chinese');    # 返回'i'在'chinese'的第一个位置-5
SELECT strcmp('abc', 'abd');    # 比较字符串，第一个参数小于第二个返回负数- -1
SELECT strcmp('abc', 'abb');    # 比较字符串，第一个参数大于第二个返回正数- 1
# 时间函数
SELECT current_date, current_time, now();    # 2018-01-13   12:33:43    2018-01-13 12:33:43
SELECT hour(current_time), minute(current_time), second(current_time);    # 12  31   34
SELECT year(current_date), month(current_date), week(current_date);   # 2018    1   1
SELECT quarter(current_date);   # 1
SELECT monthname(current_date), dayname(current_date);   # January  Saturday
SELECT dayofweek(current_date), dayofmonth(current_date), dayofyear(current_date);    # 7   13  13
# 控制流函数
SELECT if(3>2, 't', 'f'), if(3<2, 't', 'f');    # t f
SELECT ifnull(NULL, 't'), ifnull(2, 't');    # t 2
SELECT isnull(1), isnull(1/0);    # 0 1 是null返回1，不是null返回0
SELECT nullif('a', 'a'), nullif('a', 'b');    # null a 参数相同或成立返回null，不同或不成立则返回第一个参数
SELECT CASE 2
       WHEN 1 THEN 'first'
       WHEN 2 THEN 'second'
       WHEN 3 THEN 'third'
       ELSE 'other'
       END ;     # second
# 系统信息函数
SELECT database();    # 当前数据库名-test
SELECT connection_id();   # 当前用户id-306
SELECT user();    # 当前用户-root@localhost
SELECT version();   # 当前mysql版本
SELECT found_rows();    # 返回上次查询的检索行数

用户

# 增加用户
CREATE USER 'test'@'localhost' IDENTIFIED BY 'test';
INSERT INTO mysql.user(Host, User, Password) VALUES ('localhost', 'test', Password('test'));    # 在用户表中插入用户信息，直接操作User表不推荐
# 删除用户
DROP USER 'test'@'localhost';
DELETE FROM mysql.user WHERE User='test' AND Host='localhost';
FLUSH PRIVILEGES ;
# 更改用户密码
SET PASSWORD FOR 'test'@'localhost' = PASSWORD('test');
UPDATE mysql.user SET Password=Password('t') WHERE User='test' AND Host='localhost';
FLUSH PRIVILEGES ;
# 用户授权
GRANT ALL PRIVILEGES ON *.* TO test@localhost IDENTIFIED BY 'test';
# 授予用'test'密码登陆成功的test@localhost用户操作所有数据库的所有表的所有的权限
FLUSH PRIVILEGES ;   # 刷新系统权限表,使授予权限生效
# 撤销用户授权
REVOKE DELETE ON *.* FROM 'test'@'localhost';   # 取消该用户的删除权限

存储过程

# 创建存储过程
DELIMITER //    # 无参数
CREATE PROCEDURE getDates()
  BEGIN
    SELECT * FROM test ;
  END //
CREATE PROCEDURE getDates_2(IN id INT)    # in参数
  BEGIN
    SELECT * FROM test WHERE a = id;
  END //
CREATE PROCEDURE getDates_3(OUT sum INT)    # out参数
  BEGIN
    SET sum = (SELECT count(*) FROM test);
  END //
CREATE PROCEDURE getDates_4(INOUT i INT)    # inout参数
  BEGIN
    SET i = i + 1;
  END //
DELIMITER ;
# 删除存储过程
DROP PROCEDURE IF EXISTS getDates;
# 修改存储过程的特性
ALTER PROCEDURE getDates MODIFIES SQL DATA ;
# 修改存储过程语句（删除再重建）略
# 查看存储过程
SHOW PROCEDURE STATUS LIKE 'getDates';    # 状态
SHOW CREATE PROCEDURE getDates_3;   # 语句
# 调用存储过程
CALL getDates();
CALL getDates_2(1);
CALL getDates_3(@s);
SELECT @s;
SET @i = 1;
CALL getDates_4(@i);
SELECT @i;    # @i = 2

其他语句

# 查看所有的表信息（包括视图）
SHOW TABLE STATUS;

其他

# 数据库备份
mysqldump -u root -p db_name > file.sql
mysqldump -u root -p db_name table_name > file.sql
# 数据库还原
mysql -u root -p < C:\file.sql

Mysql自带的日期函数

> date_format(a.create_date,'%Y-%m-%d') 只取日期部分 
> select curdate();--获取当前日期 
> select last_day(curdate()); --获取当月最后一天。 
> select DATE_ADD(curdate(),interval -day(curdate())+1 day); --获取本月第一天  
> select date_add(curdate()-day(curdate())+1,interval 1 month); -- 获取下个月的第一天 
> select DATEDIFF(date_add(curdate()-day(curdate())+1,interval 1 month),DATE_ADD(curdate(),interval -day(curdate())+1 day)) from dual;--获取当前月的天数
> select now(); -- 如: 2020-03-31 17:03:00
> select curdate();   -- 获取当前日期，如：2021-03-31
> select year(now())  --当前年数 如：2020-2-3 返回 2020
> select month(now())  -- 当前月数 如：2020-2-3 返回 2
> select day(now()) -- 当前日数  如：2020-2-3 返回 3
> SELECT Right(100 + MONTH(curdate()), 2) -- 月份数不够两位，前面补零
> select date_add(curdate()-day(curdate())+1,interval -1 month); -- 获取上月第一天
> select last_day(date_add(curdate()-day(curdate())+1,interval -1 month)) -- 获取上月最后一天;  

使用

#查询本季度数据  
select * from `ht_invoice_information` whereQUARTER(create_date)=QUARTER(now());  

#查询上季度数据  
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));  

#查询本年数据  
select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());  

#查询上年数据  
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));  

查询当前这周的数据   
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());  

查询上周的数据  
SELECT name,submittime FROM enterprise WHEREYEARWEEK(date_format(submittime,'%Y-%m-%d')) =YEARWEEK(now())-1;  

查询当前月份的数据  
select name,submittime from enterprise   where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')  

查询距离当前现在6个月的数据  
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();  

#当年第一天：  
SELECT DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY);  
  
#当年最后一天：  
SELECT concat(YEAR(now()),'-12-31');    
  
#当前week的第一天：    
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 1 DAY);  
  
#当前week的最后一天：    
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) - 5 DAY);  
  
#前一week的第一天：    
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 8 DAY);  
  
#前一week的最后一天：    
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 2 DAY);  
  
#前两week的第一天：    
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 15 DAY);  
  
#前两week的最后一天：    
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 9 DAY);  
  
#当前month的第一天：    
SELECT concat(date_format(LAST_DAY(now()),'%Y-%m-'),'01');  
  
#当前month的最后一天：    
SELECT  LAST_DAY(now());  
  
#前一month的第一天：    
SELECT concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01');  
  
#前一month的最后一天：    
SELECT LAST_DAY(now() - interval 1 month);  
  
#前两month的第一天：    
SELECT concat(date_format(LAST_DAY(now() - interval 2 month),'%Y-%m-'),'01');  
  
#前两month的最后一天：    
SELECT  LAST_DAY(now() - interval 2 month);  
  
#当前quarter的第一天：    
select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM  CURDATE()),1) + interval QUARTER(CURDATE())*3-3 month),'%Y-%m-'),'01');   
  
#当前quarter的最后一天：    
select LAST_DAY(MAKEDATE(EXTRACT(YEAR  FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-1 month);  
  
#前一quarter的第一天：    
select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-6 month),'%Y-%m-'),'01');  
  
#前一quarter的最后一天：    
select  LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-4 month);  
  
#前两quarter的第一天：    
select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-9 month),'%Y-%m-'),'01');  
  
#前两quarter的最后一天：    
select LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-7 month);

#查询今天的数据语法为：
select * from 表名 where to_days(时间字段名) = to_days(now());

#昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1

#近7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)

#近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)

#本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

查询上个月的数据  
select name,submittime from enterprise   where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')  
select * from ` user ` where DATE_FORMAT(pudate, ' %Y%m ' ) = DATE_FORMAT(CURDATE(), ' %Y%m ' ) ;  
select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now())  
select *   
from user
where MONTH (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = MONTH (now())  
select *   
from [ user ]   
where YEAR (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = YEAR (now())  
and MONTH (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = MONTH (now())  
select *   
from [ user ]   
where pudate between 上月最后一天  
and 下月第一天  
where date(regdate)   =   curdate();  
select   *   from   test   where year(regdate)=year(now())   and month(regdate)=month(now())   and day(regdate)=day(now())  
SELECT date( c_instime ) ,curdate( )  
FROM `t_score`  
WHERE 1  
LIMIT 0 , 30 

#上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1

Mysql增删改查SQL语句

查询

select * from 表名

带条件查询

select * from 表名 where 字段=#{你要查询的值}

带条件的分页查询

select * from 表名 where 字段=#{你要查询的值} limit 0,10

提示:0,10是数据的0条到10条

升序、倒序

升序

select * from 表名 order by 字段

降序

select * from 表名 order by 字段 desc 

根据数据库字段名排序

select * from 数据库名 WHERE 1=1 order by CONVERT( 字段 USING gbk ) COLLATE gbk_chinese_ci ASC

首先，对name字段进行gbk编码，然后，对编码后的内容根据gbk_chinese_ci进行整理排序。这样得到的结果，英文是排在中文前面的，而且是根据拼音排序的。
数据库：数据库表名
字段名：排序字段名
CONVERT：提供一个在不同字符集之间转换数据的方法。
COLLATE：COLLATE是一个算法语句，主要用于对字符进行排序，经常出现在表的创建语句中。sql语句里面的COLLATE主要用于对字符进行排序。

---

1. A-D ↩︎