MySQL语法笔记——经常回顾一下

---

前言

MySQL语法还是很重要的，有时候会经常忘记怎么写，只知道大概的，细节有点忘记，现在记录如下，方便以后查询

MySQL语法

登录

mysql -uroot -p

语法

-- 显示和使用
SHOW DATABASES;
USE ins_cashier;
SHOW TABLES;

-- 创建数据库
CREATE DATABASE jd;
-- 删除数据库
drop DATABASE jd;

-- 新建表
-- PRIMARY KEY 主键 每个表只能定义一个主键，可以是一个，也可以是组合。标识唯一行
CREATE TABLE `qsm` (
	`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
	`pin` varchar(6) NOT NULL COMMENT '京东pin',
	`name` char(16) DEFAULT NULL COMMENT '姓名',
	`dept` varchar(64) NOT NULL DEFAULT '1' COMMENT '部门',
	`dept` varchar(64) NOT NULL DEFAULT '1' COMMENT '部门',
	`flag` int(11) DEFAULT '0' COMMENT '是否同意金融授权 0 否 1 是',
	`email` varchar(64) DEFAULT NULL COMMENT '电子邮件地址',
	`delete` TINYINT(1) DEFAULT '0' COMMENT '是否删除，0未删除，1 已删除',
	`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
	PRIMARY KEY (`id`),
	UNIQUE KEY `uk_pin` (`pin`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARSET = utf8 COMMENT '客户信息表';

CREATE TABLE department (
		id int(11) NOT NULL COMMENT '部门id',
		dept_name VARCHAR(16) NOT NULL DEFAULT '财务部' COMMENT '部门名称',
		dept_address VARCHAR(16) DEFAULT NULL COMMENT '部门地址',
		PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='部门信息表';

create table qsm2 select * from qsm;

create table qsm2 like qsm;
INSERT INTO qsm2 SELECT * FROM qsm; 


-- 删除表
DROP TABLE qsm2;

-- 清空表
DELETE FROM qsm;
TRUNCATE  qsm;

-- crud

SELECT * FROM qsm;
SELECT pin, name FROM qsm;
SELECT pin, name FROM qsm WHERE id = 1;
SELECT pin jd_pin, name as jd_name FROM qsm WHERE id = 1;
SELECT pin jd_pin, name as jd_name , 1 FROM qsm WHERE id = 1;
SELECT * FROM qsm  WHERE id != 1;
SELECT * FROM qsm  WHERE id in (2,3);
SELECT * FROM qsm WHERE id in (
	SELECT id FROM qsm2
);
SELECT * FROM qsm  WHERE id not in (2,3);
SELECT * FROM qsm WHERE id BETWEEN 1 and 3;
SELECT * FROM qsm WHERE name LIKE 'est%';
SELECT * FROM qsm WHERE name LIKE '%te%';
SELECT * FROM qsm WHERE name LIKE 'test_';
SELECT * FROM qsm LIMIT 2 OFFSET 3;
SELECT * FROM qsm LIMIT 3, 2;
SELECT * FROM qsm ORDER BY create_time ASC;
SELECT * FROM qsm ORDER BY create_time DESC;
SELECT * FROM qsm ORDER BY  `name` DESC, create_time ASC;

-- 每组聚合成一条; 聚合之后再次筛选，使用having
-- 实则先where添加之后，是先排序再分组，之后having过滤，得到每组的数据之后，在order By 排序
SELECT dept, MIN(id) id FROM qsm GROUP BY dept;
SELECT dept, AVG(id) id FROM qsm GROUP BY dept;
SELECT dept, sum(id) id FROM qsm GROUP BY dept;
SELECT dept, COUNT(*)  num FROM qsm GROUP BY dept;
SELECT dept, COUNT(1)  num FROM qsm GROUP BY dept;
SELECT dept, COUNT(pin)  num FROM qsm GROUP BY dept;
SELECT create_time, MAX(id) id FROM qsm GROUP BY create_time;
SELECT dept, MIN(id) min_id, MAX(id) max_id FROM qsm GROUP BY dept;
SELECT dept, COUNT(*) count_Num , MIN(id) min_id, MAX(id) max_id FROM qsm GROUP BY dept HAVING count_Num > 2;
SELECT dept, COUNT(*) count_Num , MIN(id) min_id, MAX(id) max_id FROM qsm WHERE id >2 GROUP BY dept HAVING count_Num > 1;
SELECT dept, COUNT(*) count_Num , MIN(id) min_id, MAX(id) max_id FROM qsm WHERE id >2 GROUP BY dept HAVING count_Num > 1 ORDER BY max_id desc;

SELECT a.id, a.pin , a.name, a.dept ,b.dept_name  FROM qsm a , department b WHERE a.dept = b.id;
SELECT a.id, a.pin , a.name, a.dept ,b.dept_name  FROM qsm a JOIN department b on a.dept = b.id;


DELETE FROM qsm 
DELETE FROM qsm WHERE pin = 4;

UPDATE qsm SET email='qsm@qq.com';
UPDATE qsm SET email='qsm@qq.com',flag = 2 WHERE id =1;
UPDATE qsm a JOIN department b ON a.dept = b.id SET email='qsm@qq.com',flag = 2 WHERE a.id =1;
UPDATE qsm a LEFT JOIN department b ON a.dept = b.id SET email='qsm@qq.com',flag = 2 WHERE b.id = null;

INSERT INTO qsm(pin,name) VALUES (3,3);
INSERT INTO qsm VALUES( 4, 4, 4, 0, '4@qq.com', 0, NOW( ), NOW( ) );
INSERT INTO qsm(`id`, `pin`, `name`, `flag`) VALUES (1, '1', '1', 0);
INSERT INTO qsm(`pin`, `name`, `flag`) VALUES ('test', 'test', 0) ,( 'test6', 'test6', 0);
INSERT INTO qsm(`pin`, `name`, `flag`) SELECT pin, name, flag FROM qsm2;
INSERT INTO qsm(`id`, `pin`, `name`, `flag`, `email`, `delete`, `create_time`, `update_time`) VALUES (2, '2', '2', 0, NULL, 0, '2020-10-19 19:44:37', NOW( ));

-- 函数
SELECT now() ;
SELECT SLEEP(4);
SELECT CURRENT_TIMESTAMP()
SELECT str_to_date('2016-01-02 23:33:31', '%Y-%m-%d %H:%i:%s'); 
select DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');



-- 索引 index / unique / primary key
CREATE unique INDEX idx_qsm_c12 ON `qsm`(`name`,`dept`);
ALTER TABLE qsm ADD INDEX idx_qsm_c12(`name`,`dept`,`flag`);
SHOW INDEX FROM qsm;
DROP INDEX idx_qsm_c12 on qsm;


-- 修改表名 
alter table qsm rename new_qsm; 
-- 添加表列
alter table qsm add column phone varchar(10) DEFAULT NULL  COMMENT "手机号" AFTER email ; 
ALTER TABLE `ssp_user_credentials` ADD COLUMN `source` INT(11) NULL DEFAULT '0' COMMENT '证件来源 0 京东金融 1 手动输入' AFTER `oss_key`;
-- 删除表列
alter table qsm drop  column phone; 
-- 修改表列类型
alter table qsm modify phone char(10) 

总结

这些语法都是比较简单的，以后要是经常写的sql，会继续记录下来。

【完】

正在去往BAT的路上修行