时间类型
DATE：格式为YYYY-MM-DD，范围1000-01-01到9999-12-31
TIME：格式为hhh:mm:ss
DATETIME：格式为YYYY-MM-DD hh:mm:ss，范围1000-01-01 00:00:00到9999-12-31 23:59:59；
TIMESTAMP：储存时间戳，格式与DATETIME相同，范围1970-01-01 00:00:01到2038-01-19 03:14:07；
TIMESTAMP 自动变换时区，在存储时会转成UTC时间，在取出时转换为服务器的所在时区的时间
CREATE TABLE test_date(
id int(11) NOT NULL AUTO_INCREMENT primary key COMMENT '主键',
num int,
date1 DATE,
time1 TIME,
time_num TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
sumbit_data datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '提交时间'
);
mysql> select NOW(),CURDATE()
+---------------------+------------+
| NOW() | CURDATE() |
+---------------------+------------+
| 2023-02-07 11:28:00 | 2023-02-07 |
+---------------------+------------+
1 row in set (0.00 sec)
mysql> insert into test_date(num,date1,time1) values(1,CURDATE(),NOW());
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_date;
+----+------+------------+----------+---------------------+---------------------+
| id | num | date1 | time1 | time_num | sumbit_data |
+----+------+------------+----------+---------------------+---------------------+
| 1 | 1 | 2023-02-07 | 11:29:32 | 2023-02-07 11:29:32 | 2023-02-07 11:29:32 |
+----+------+------------+----------+---------------------+---------------------+
1 row in set (0.00 sec)
看上去没什么区别，推荐使用DATETIME
mysql> select NOW(),SYSDATE(),SLEEP(3),NOW(),SYSDATE();
+---------------------+---------------------+----------+---------------------+---------------------+
| NOW() | SYSDATE() | SLEEP(3) | NOW() | SYSDATE() |
+---------------------+---------------------+----------+---------------------+---------------------+
| 2023-02-07 11:32:09 | 2023-02-07 11:32:09 | 0 | 2023-02-07 11:32:09 | 2023-02-07 11:32:12 |
+---------------------+---------------------+----------+---------------------+---------------------+
1 row in set (3.00 sec)
mysql 字符串转日期
mysql> SELECT DATE_FORMAT("2023-02-07", "%Y-%m-%d") date_str;
+------------+
| date_str |
+------------+
| 2023-02-07 |
+------------+
1 row in set (0.00 sec)
cast('2023-02-06 14:15:00' as datetime)
str_to_date('2023-02-06 14:15:00','%Y-%m-%d %H:%i:%s')
一次插入多笔数据
insert into students(student_id,student_name,college_major,status,sumbit_data,score)
values('100001','耀然天成','海洋学','1',cast('2023-02-06 14:15:00' as datetime),88.5),
('100002','柯尔莫哥洛夫','数学，大气力学','1',str_to_date('2023-02-01 09:15:00','%Y-%m-%d %H:%i:%s'),100.0),
('100003','牛顿','物理学','1',str_to_date('2023-02-06 14:15:00','%Y-%m-%d %H:%i:%s'),100.0),
('100004','高斯','数学，天文学','1',str_to_date('2023-02-03 11:15:00','%Y-%m-%d %H:%i:%s'),100.0);
注意:
student_id本是int,插入的是字符串；
时间使用了cast与str_to_date两个方法
批量插入数据
create database vodb character set utf8;
create user 'automng'@'localhost' identified by 'rootroot';
GRANT ALL PRIVILEGES ON *.* TO 'automng'@'localhost' WITH GRANT OPTION;
use vodb;
drop table if exists test;
create table test ( tid int,tname varchar(12),test_id int NOT NULL AUTO_INCREMENT COMMENT '主键',PRIMARY KEY (test_id),tvalue varchar(90)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `test`
ADD COLUMN `CreateTime` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ;
use vodb;
DROP PROCEDURE IF EXISTS `p_addtest`;
DELIMITER ;;
CREATE DEFINER=`automng`@`localhost` PROCEDURE `p_addtest`(IN n int)
BEGIN
DECLARE i int;
SET i = 0;
WHILE i < n DO
insert into test(tid,tname,tvalue) values(i,'aaabbb',concat('有张有驰有分寸',i));
set i = i + 1;
END WHILE;
END
;;
DELIMITER ;
use vodb;
call p_addtest(5);
常用测试语句
select * from test limit 3;
select a.tvalue from vodb.test a,vodb.test b,vodb.test c where a.tid = b.tid and a.createtime = c.createtime limit 3;
select a.tvalue from vodb.test a,vodb.test b where a.tid = b.tid and a.createtime = b.createtime limit 3;
select a.tvalue from vodb.test a,vodb.test b,vodb.test c where a.tid = b.tid and a.createtime = c.createtime limit 3;
--180 sec
select a.tvalue from vodb.test a,vodb.test b,vodb.test c where a.tid = b.tid and a.tid = c.tid limit 3;
--32 sec
select a.tvalue from vodb.test a,vodb.test b,vodb.test c where a.tid = b.tid and a.tid = c.tid and a.tid > 30000 and a.tid < 35000 limit 3;
--6 sec
select a.tvalue from vodb.test a,vodb.test b,vodb.test c where a.tid = b.tid and a.tid = c.tid and a.tid > 30000 and a.tid < 31000 limit 3;
mysql> select distinct student_name from students; +--------------------+ | student_name | +--------------------+ | 柯尔莫哥洛夫 | | 耀然天成 | | 牛顿 | | 高斯 | +--------------------+ 4 rows in set (0.01 sec)
存在就跳过/忽略，即有唯一索引冲突时就不插入了
INSERT IGNORE INTO students (id) VALUES ('8');
存在就更新旧列，新列的值是多少都没有关系了，因为更新时没用到它
INSERT INTO students (id,score) VALUES (8,18) ON DUPLICATE KEY UPDATE score=score+1;
多值插入,经测试一次插入100W行记录没问题，约30个列
insert into students(student_id,student_name,college_major,status,sumbit_data,score)
values('100001','耀然天成','海洋学','1',cast('2023-02-06 14:15:00' as datetime),88.5),
('100002','柯尔莫哥洛夫','数学，大气力学','1',str_to_date('2023-02-01 09:15:00','%Y-%m-%d %H:%i:%s'),100.0),
('100003','牛顿','物理学','1',str_to_date('2023-02-06 14:15:00','%Y-%m-%d %H:%i:%s'),100.0),
('100004','高斯','数学，天文学','1',str_to_date('2023-02-03 11:15:00','%Y-%m-%d %H:%i:%s'),100.0);
存在就先delete再insert, 不存在就直接插入，依据唯一索引，
REPLACE INTO ...
如果有多个唯一索引，那么会delete所有唯一索引列对应的值后再删除，
意思是存在删除多行才插入一行的可能
INSERT IGNORE INTO，多值插入下的存在就跳过测试
mysql> desc students;
+---------------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+-------------------+-----------------------------------------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| student_id | int | YES | MUL | NULL | |
| student_name | varchar(30) | YES | | NULL | |
| college_major | varchar(15) | YES | | NULL | |
| status | char(1) | YES | | NULL | |
| sumbit_data | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| score | double(25,3) | NO | | 0.000 | |
+---------------+--------------+------+-----+-------------------+-----------------------------------------------+
mysql> ALTER TABLE students ADD UNIQUE (student_id);
Query OK, 0 rows affected (0.41 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+---------------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+-------------------+-----------------------------------------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| student_id | int | YES | UNI | NULL | |
| student_name | varchar(30) | YES | | NULL | |
| college_major | varchar(15) | YES | | NULL | |
| status | char(1) | YES | | NULL | |
| sumbit_data | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| score | double(25,3) | NO | | 0.000 | |
+---------------+--------------+------+-----+-------------------+-----------------------------------------------+
7 rows in set (0.00 sec)
mysql> select student_id,student_name from students;
+------------+--------------------+
| student_id | student_name |
+------------+--------------------+
| NULL | 柯尔莫哥洛夫 |
| NULL | 柯尔莫哥洛夫 |
| NULL | 柯尔莫哥洛夫 |
| NULL | 柯尔莫哥洛夫 |
| NULL | 柯尔莫哥洛夫 |
| 100001 | 耀然天成 |
| 100002 | 柯尔莫哥洛夫 |
| 100003 | 牛顿 |
| 100004 | 高斯 |
+------------+--------------------+
9 rows in set (0.00 sec)
直接插入，因为建立了唯一索引，所以直接报错
mysql> insert into students(student_id,student_name,college_major,status,sumbit_data,score)
-> values('100001','耀然天成','农业','1',cast('2023-02-06 14:15:00' as datetime),88.5),
-> ('100005','扁鹊他哥','医学','1',str_to_date('2023-09-01 09:15:00','%Y-%m-%d %H:%i:%s'),80.0);
ERROR 1062 (23000): Duplicate entry '100001' for key 'students.student_id_2'
不仅重复的没有插入，扁鹊他哥，是个不重复的数据，也没有插入
mysql> select student_id,student_name,college_major from students;
+------------+--------------------+-----------------------+
| student_id | student_name | college_major |
+------------+--------------------+-----------------------+
| NULL | 柯尔莫哥洛夫 | NULL |
| NULL | 柯尔莫哥洛夫 | NULL |
| NULL | 柯尔莫哥洛夫 | NULL |
| NULL | 柯尔莫哥洛夫 | NULL |
| NULL | 柯尔莫哥洛夫 | NULL |
| 100001 | 耀然天成 | 海洋学 |
| 100002 | 柯尔莫哥洛夫 | 数学，大气力学 |
| 100003 | 牛顿 | 物理学 |
| 100004 | 高斯 | 数学，天文学 |
+------------+--------------------+-----------------------+
9 rows in set (0.00 sec)
进入正题，忽略式插入
mysql> insert ignore into students(student_id,student_name,college_major,status,sumbit_data,score)
-> values('100001','耀然天成','农业','1',cast('2023-02-06 14:15:00' as datetime),88.5),
-> ('100005','扁鹊他哥','医学','1',str_to_date('2023-09-01 09:15:00','%Y-%m-%d %H:%i:%s'),80.0);
Query OK, 1 row affected, 1 warning (0.02 sec)
Records: 2 Duplicates: 1 Warnings: 1
扁鹊他哥这条记录已经插入，重复的100001没有插入
mysql> select student_id,student_name,college_major from students;
+------------+--------------------+-----------------------+
| student_id | student_name | college_major |
+------------+--------------------+-----------------------+
| NULL | 柯尔莫哥洛夫 | NULL |
| NULL | 柯尔莫哥洛夫 | NULL |
| NULL | 柯尔莫哥洛夫 | NULL |
| NULL | 柯尔莫哥洛夫 | NULL |
| NULL | 柯尔莫哥洛夫 | NULL |
| 100001 | 耀然天成 | 海洋学 |
| 100002 | 柯尔莫哥洛夫 | 数学，大气力学 |
| 100003 | 牛顿 | 物理学 |
| 100004 | 高斯 | 数学，天文学 |
| 100005 | 扁鹊他哥 | 医学 |
+------------+--------------------+-----------------------+
10 rows in set (0.01 sec)
数据准备
mysql> select * from students; +----+------------+--------------------+-----------------------+--------+---------------------+---------+ | id | student_id | student_name | college_major | status | sumbit_data | score | +----+------------+--------------------+-----------------------+--------+---------------------+---------+ | 1 | NULL | 柯尔莫哥洛夫 | NULL | NULL | 2023-02-01 09:15:00 | 0.000 | | 2 | NULL | 柯尔莫哥洛夫 | NULL | 1 | 2023-02-01 09:15:00 | 0.000 | | 3 | NULL | 柯尔莫哥洛夫 | NULL | 1 | 2023-02-01 09:15:00 | 0.000 | | 4 | NULL | 柯尔莫哥洛夫 | NULL | 1 | 2023-02-01 09:15:00 | 0.000 | | 5 | NULL | 柯尔莫哥洛夫 | NULL | 1 | 2023-02-01 09:15:00 | 0.000 | | 6 | 100001 | 耀然天成 | 海洋学 | 1 | 2023-02-06 14:15:00 | 88.500 | | 7 | 100002 | 柯尔莫哥洛夫 | 数学，大气力学 | 1 | 2023-02-01 09:15:00 | 100.000 | | 8 | 100003 | 牛顿 | 物理学 | 1 | 2023-03-30 15:53:14 | 101.000 | | 9 | 100004 | 高斯 | 数学，天文学 | 1 | 2023-02-03 11:15:00 | 100.000 | +----+------------+--------------------+-----------------------+--------+---------------------+---------+ 9 rows in set (0.01 sec) mysql> create table s2 as select * from students; Query OK, 9 rows affected, 1 warning (1.00 sec) Records: 9 Duplicates: 0 Warnings: 1 mysql> mysql> delete from s2 where id < 7; Query OK, 6 rows affected (0.05 sec)
left join
mysql> select s1.student_id,s2.score from students s1 left join s2 on s1.student_id = s2.student_id;
+------------+---------+
| student_id | score |
+------------+---------+
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
| 100001 | NULL |
| 100002 | 100.000 |
| 100003 | 101.000 |
| 100004 | 100.000 |
+------------+---------+
9 rows in set (0.00 sec)
mysql> select s1.student_id,if(s2.score is null,0,1) flag from students s1 left join s2 on s1.student_id = s2.student_id;
+------------+------+
| student_id | flag |
+------------+------+
| NULL | 0 |
| NULL | 0 |
| NULL | 0 |
| NULL | 0 |
| NULL | 0 |
| 100001 | 0 |
| 100002 | 1 |
| 100003 | 1 |
| 100004 | 1 |
+------------+------+
9 rows in set (0.00 sec)
读3 行 ,只能放句尾
limit 3
|
从索引为0的行开始，读取3 行 limit 0,3 这个索引可以认为是表的index，从0开始 |
|
mysql分页查询 select * from students limit 3,7; select * from students order by id desc limit 3,7 ; |
|
|
|
判断C2!=1,
本意是判断C2不为1的数据，但若C2为null，它的确不为1，但该结果却返回为空
完成的判断应为
C2 is null or C2 !=1
|
|
|
|
|
|
|
|
with tmp as (select s1.student_id from students s1, s2 where s1.student_id = s2.student_id) select * from tmp;
带with的update:两表相连，存在设置状态为1
with tmp as (select s1.student_id from students s1, s2 where s1.student_id = s2.student_id) update s2,tmp set s2.status = 1 where s2.student_id = tmp.student_id and s2.status = 0; with tmp as (select s1.student_id from students s1, s2 where s1.student_id = s2.student_id) update s2,tmp set s2.status = 1,s2.score = 1 where s2.student_id = tmp.student_id and s2.status = 0 ;
|
|
|
|
|
|
|
|
|