又一次对mysql的汇总

闲暇时间 又一次对mysql练习，环境虚拟机安装mysql。

 

SELECT * FROM brand where id =2;

 

UPDATE brand set name = 'xiaomi' , description = 'phone' where id = 2;

 

UPDATE brand set name = CONCAT('华为',6) , description = 'phone' where id = 3;

 

INSERT into brand (name,description) VALUES ('三星','韩国品牌'),('oppo','国产品牌');

 

DESC brand;

 

CREATE table B (id int PRIMARY key AUTO_INCREMENT,name VARCHAR(255)); 

SELECT * from brand;

 

INSERT into B (name,description) VALUES ('XX','XXX'),('YY','YYY');

 

ALTER table a add birthday DATETIME DEFAULT NULL;

 

update a set birthday="2001-2-14 14:20:34" where id = 5;

 

ALTER TABLE a add TIME2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP on UPDATE CURRENT_TIMESTAMP;

 

select YEAR(time) as time,YEAR(birthday) as birthday,  MONTH(time) ,DAY(time), HOUR(time) , MINUTE(time),    SECOND(time) from a;

 

SELECT NOW();

SELECT DAYOFYEAR(NOW());

SELECT DAYOFMONTH(NOW());

SELECT DAYOFWEEK(NOW());

SELECT WEEKDAY(NOW());

SELECT DAYOFYEAR("2010-2-23 18:20:23");

 

SELECT DATEDIFF(birthday,NOW()) from a; 

SELECT TIMEDIFF(TIME(birthday),TIME(NOW())) from a;

 

#通过拿出来生日的最小的那个，解决多个人同时最小的情况

SELECT * from a ORDER BY birthday DESC  LIMIT 1;

SELECT *  from a WHERE birthday =(SELECT birthday from a ORDER BY birthday DESC  LIMIT 1);

 

SELECT * from a WHERE id  IN(1,2,3) and YEAR(birthday)=1995;

 

SELECT * from a WHERE id  BETWEEN 1 and 5 ;

 

ALTER TABLE a add class_id TINYINT DEFAULT 1;

INSERT INTO a (class_id) VALUES (1);

UPDATE a set class_id = 2 WHERE id =5;

 

SELECT COUNT(description) as DE, COUNT(name) ,class_id from a 

GROUP BY class_id

ORDER BY DE DESC;

 

SELECT NOW();

SELECT TIME(NOW()) > "22:30:26";

SELECT DATE(NOW());

SELECT DATE_ADD(NOW(),INTERVAL 7 YEAR);

SELECT DATE_ADD(NOW(),INTERVAL -7 DAY);

 

SELECT LAST_DAY(NOW());

SELECT DATE_SUB(NOW(),INTERVAL  (DAYOFMONTH(NOW())-1) DAY);

SELECT DATE(DATE_SUB(NOW(),INTERVAL  (DAYOFMONTH(NOW())-1) DAY));

SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 3 MONTH),"%Y-%m-01");

 

#随机排序

SELECT * from a ORDER BY RAND() DESC LIMIT 1;

 

SELECT MAX(birthday) from a;

SELECT MIN(birthday) from a;

SELECT YEAR(MIN((birthday))) from a;

SELECT MAX([DISTINCT] expr)

 

#处理only_full_group_by问题

set sql_mode=(SELECT REPLACE(@@sql_mode,"only_full_group_by",""));

#选出 根据 name 分组后 birthday最大的那行信息 ,返回来的name 与 max不关联，错了

SELECT name, MAX(birthday) as birthday ,time  from a ;

GROUP BY name

HAVING YEAR(max(birthday))>2000;

 

#报错了，where中不能有SUM, COUNT, MAX, AVG等对一组(多条)数据操作的函数

SELECT *  from a 

WHERE max(birthday)>"2020-01-22 15:23:34";

 

SELECT name, max(birthday)  from a;

SELECT * from a

WHERE birthday =(SELECT max(birthday) from a);

 

SELECT *  from a 

WHERE YEAR(max(birthday))>2000 ;

GROUP BY name

ORDER BY class_id;

 

 

SELECT @@sql_mode;

 

#查询事物

SELECT @@tx_isolation;

INSERT INTO a (name,description,birthday,time,class_id) VALUES ('vivvo','guochan','2001-01-23 15:35:23','1990-01-23 12:45:23',2);

 

 

#子查询方式

SELECT name from a

WHERE YEAR(birthday) = (SELECT YEAR(birthday) from a WHERE name = 'pinguo')

AND name != 'pinguo';

 

#自链接方式 使的找与a1表中 同一年的 pinguo的名字 和上面的子查询方式实现的功能一样，但性能好些

SELECT a2.name from a as a1

INNER JOIN a as a2

on YEAR(a1.birthday) = YEAR(a2.birthday)

WHERE a1.name = 'pinguo' 

AND a2.name != 'pinguo';

   

 

BEGIN;

SELECT * from a;

INSERT INTO a (name,description,birthday,time,class_id) VALUES ('lianxiang','guochan','1990-01-23 12:45:23','1990-01-23 12:45:23',2);

COMMIT; 

 

BEGIN;

UPDATE a set name = 'macc' WHERE name = 'mac';

commit;

 

show index from a;

 

BEGIN;

SELECT * from a where id =1 for UPDATE;

commit;

 

 

CREATE table B 

(id int PRIMARY key AUTO_INCREMENT,name VARCHAR(255)); 

 

CREATE TABLE IF NOT EXISTS class (

  id TINYINT PRIMARY KEY AUTO_INCREMENT,

  cname VARCHAR(11) DEFAULT "幼儿园"

);

 

INSERT INTO class (cname) VALUES ('xiaoxue'),('中学');

 

#创建外键

ALTER TABLE a ADD

CONSTRAINT a_class

FOREIGN KEY(class_id)

REFERENCES class(id)

ON DELETE CASCADE;