mysql(笔记个人用)

随手写的一些常规语句，最近在使用go-zero高微服务，model用到mysql，所以来简单复习下mysql的语法，这些语法够了，如果想干DBA那就需要深入学
show databases;
use;
show tables;
show create database d1;
show create table t1;查看表结构，可以定义自增开始的值，表结构可以看到

ddl:data define langauge
create database database_name default character set utf8mb4;
create table t1(id int(10)primary key auto_increment,name varchar(10));自增必须是某个key,delete删除这个字段不会改变它对应的auto_increment值
alter table t1 add(age int(100));
aletr table t1 drop book;
alter table t1modify ;
truncate table t1;清空表的数据
drop table t1;还删除了表的结构
alter table t1 modify name varchar(100) NOT NULL;
alter table t1 change name name2 varchar(999);
alter table t1rename to t2;
alter table t1 add do after book;

column可加可不加:
alter table t1 add((c1 int1),(c2 int2);
alter table t1 add column ((c1 int1),(c2 int2);
alter table t1 add(c1 int1),add(c2 int2);
alter table t1 change (c1 c11 int11),change(c2 c22 int22);
alter table t1 drop c1,drop c2;
alter table t1modify(c1 int11 not null comment ‘qqq’),modify(c2 int22);
alter table t1auto_increment=1;重置,truncate也行，更直接drop在create，注意重置无效的情况，比如主键不可重复的约束

show engines;innodb会回收脏数据
show variables like ‘%storage_engine%’;mysql的通配符一般是%,*一般是命令设置主机等的时候才用作通配符

dml:data manipulate langauge
insert delete update
insert into (table) t1 values(v1,v2);
insert into t1(c1) values(v1);
delete from t1 where id=100;
update t1 set id=100,name=“name2” where id=1;不加where就全部修改

TCL事务控制语言

dql:data query langauge
写法顺序：select -from - where - group by - having - order by
执行顺序：from - where - group by - having - select - order by
= 等于

大于
< 小于
= 大于等于
<= 小于等于
<> 不等于
!> 不大于
!< 不小于
select * from t1;这里通配用*
select c1 as ‘nickname’ from t1;
select c1,c2,c1*c2 as c10 from t1;
select c1,c2,c3 from t1 where id=1 or id=2;
select c1,c2,c3 from t1 where id=1 and name=“yy”;
select all 字段1,字段2,… from 数据表名;默认就是all，允许有重复的数据
select distinct 字段1,字段2,… from 数据表名;去重
select top n 字段1,字段2,… from 数据表名;指定查询前几条

select * from employee where department in (‘市场部’,‘广告部’)
select * from employee where Id in (2,4,6) (数值型)
select * from employee where birthday between ‘1983’ and ‘1987’
select * from employee where name like ‘王%’
select * from employee where department is null;is not null
select * from employee order by id desc,name asc;desc降序，升序
分组查询中的筛选条件分为两类：
分组前筛选： 数据源是原始表，用where，放在group by前面，因为在分组前筛选
分组后筛选：数据源是分组后的结果集 ，用having，放在group by后面，因为在分组后进行筛选。
select max(salary),job_id from t1 group by job_id;avg,sum等方法
select avg(salary),job_id from t1 where email like ‘%a%’ group by job_id
select count(user_id),department_id from t1 group by department_id having count(user_id) > 2;
select count(user_id)as a,department_id from t1 group by department_id having a > 2;质疑，别名的使用，这种情况似乎where可以having不可以，很久之前的学习印象，懒得测试，你可以自己测试下
select count(user_id),department_id from t1 group by department_id having count(user_id) > 2 order count(user_id) asc;
不用别名就不会出错，顶多就是写得麻烦点，用别名方便，但我记得别名对where having order支持不一样，跟表的原始字段名和使用函数后的字段的使用也不一样，不知道现在版本如何，感兴趣你可以自己测试一下，写几条语句即可

多表联查
https://www.cnblogs.com/fangweicheng666/p/15168072.html
https://www.cnblogs.com/YorkQi/p/14801060.html

dcl:data control lanague
grant select,insert,update,delete,drop on t1 to username@ip;涵盖ddl,dql,dml
revoke select,insert,update,delete,drop from t1 to username@ip

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `user_data`;
CREATE TABLE `user_data` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `data` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户数据表';

SET FOREIGN_KEY_CHECKS = 1;

注意abc三步骤

(接36)