mysql简单介绍(commandline DDL DML DCL)

本人博客都是一行一行手敲，可能有手抖之处，一定要实际执行一下进行正误鉴别

• 登录

  • mysql -p -u root -h localhost

• 注释： – 注释（两个杠和注释内容之间一定要有空格）

• DDL:表的定义以及结构的修改。

  • 库:

    • create database name;
    • drop database db1
    • show databases;
    • use db1;

  • 表:

    • 创建表
      • create table name(
        age int(8),
        name char(9),
        realtime datetime
        );
      • desc tablename;
      • show create table table1 \G;
    • 删除表
      • drop table table1;
    • 修改表
      • alter table table1 modify age int(20) first/after name ;
      • alter table table1 add age int(9);
      • alter table table1 drop age ；
      • altler table table1 change age age1 int(4);
      • alter table table1 rename table2;
      • =rename table table1 to table2；

• DML:表的内部数据的操作。

  • 插入
    • insert into table1(age,name,realtime) values(7,‘wang’,now());
    • insert into table1values(8,‘wang1’,now()),(9,‘wang2’,now());
  • 更新
    • update table1 set age=10 where name=‘wang’;
  • 删除
    • delete from table1 where name=‘wang’;
  • 查询

    • 简单

      • select * from table1;

    • 不重复

      • select distinct depno from emp;

    • 条件查询

      • select * from emp where depno=1 and sal>3000;

    • 排序和限制(默认升序）

      • select * from emp order by deptno asc,sal desc;
      • select * from emp order by deptno ,sal ; = select * from emp order by deptno asc,sal asc;(按两个字段排序，都要加排序方式)
      • select * from emp order by sal limit 3;(前三条记录)
      • select * from emp order by sal limit 1,3;(第2-4条记录)

    • 聚合

      • select count(1) from emp;
      • select deptno,count(1) from emp group by deptno with rollup;
      • select deptno,count(1) from emp group by deptno having count(1)>1;

    • 表连接

      • 内连接
        • select ename,deptname from emp,dept where emp.deptno=dept.deptno;
      • 外连接
        • 左连接
          • select ename,deptname from emp left join dept on emp.deptno=dept.deptno;(所有左表中的记录以及右边表中相匹配的记录)
        • 右连接
          • select ename,deptname from emp right join dept on emp.deptno=dept.deptno;(所有右表中的记录以及左边表中相匹配的记录)

    • 子查询：查询需要的条件是另外一个select语句的结果。

      • select * from emp where deptno in(select deptno from dept);

    • 记录联合

      • union all:多张表直接联合
        • select deptno from emp
          union all
          select deptno from dept;
      • union:多张表distinct去重后联合
        • select deptno from emp
          union
          select deptno from dept;
      • union 和 union all ：对多个表中查出的数据汇总，要求字段别名和排序完全相同（即每个select结果集相同，然后合并）
      • 区别：
        • union: 对重复记录值显示一条，会根据主键排序
        • union all: 不查重，所有结果都会显示，不会排序
          示例：

            SELECT '1' AS name, id, 
        	CASE 
        	      WHEN  stu_name = 'xiaohua' THEN '小花'
        	      WHEN stu_name = 'xiaohong' THEN '小红'
        	      ELSE '翠花'
            END AS personName 
            FROM student_t WHERE ...
        	UNION ALL
        	SELECT '2' AS name, id, 
        	CASE 
        	      WHEN  teacher_name = 'xiaoli' THEN '李老师'
        	      WHEN teacher_name = 'xiaowang' THEN '王老师'
        	      ELSE '翠花老师'
        	END AS personName 
        	FROM teacher_t WHERE ...
        

    • 计数：select count(1) from (select w.id form …条件) x;

• DCL:DBA用来管理用户权限的语句

  • 授权语句:grant to
    • grant select ,insert on mydb.* to ‘wangchaunxin’@‘localhost’ identified by ‘123’;
  • 收权语句:revoke from
    • revoke insert on mydb.* from ‘wangchuanxin’@‘localhost’;

• ? create table;

常用脚本：

• 备份表数据
  • 复制表结构：create table tablea_bak20180201 like tablea;
  • 复制数据：insert into tablea_bak20180201 select * from tablea;
    当表结构中存在虚拟列时只能用：insert into tablea_bak20180201(id, name, code) select id, name, code from tablea;
  • 查看两张表的表数据是否相等：select ‘tablea’, count(1) from tablea union all select ‘table_bak20180201’ , count(1) from tablea_bak20180201;
• 执行-增加字段：alter table tablea add code varchar(100) default null comment ‘编码’ first/after <某个字段>;
• 回退：drop table tablea;
  rename table tablea_bak20180201 to tablea
• 修改表的注释：alter table tablea comment ‘table注释’;
• 修改表的字段的注释：alter table tablea modify column `type` tinyint(2) default null comment ‘类型’;
• modify 和 change的区别：前者只能改字段的类型、注释、字符集，后者可以改字段名
• 聚合函数
  • select group_concat(distinct task_name) from tablea;
    select group_concat(id), count(id) from tablea group by province_name having province_name = ‘jiangsu’;
  • subString(t.name, 1, LENGTH(t.name - 1)
  • substring(‘pandas’ from 4 for 2) : ‘da’
  • left(‘pandas’, 5) : ‘panda’
  • cast(value as type) 和 convert(value, type)仅支持如下类型转换：
    • binary 二进制类型
    • char 字符类型
    • date 日期类型
    • time 时间类型
    • datetime 日期时间类型
    • decimal 浮点型
    • signed 整型
    • unsigned 无符号整型
  • upper(‘hello’) 转成大写
  • lower(‘HELLO’) 转成小写
  • now() 和 sysdate() 区别：前者表示语句开始执行的时间，后者表示语句执行过程中的实时时间
  • date_format(now(), ‘%Y%m%d’) as … 转换日期格式
  • ifnull(expr1, expr2) expr1为null, 则结果为expr2
    实例：
    • select ifnull(1, 0); =1
    • select ifnull(null, 10); =10
    • select ifnull(1/0, 10); =1
    • select ifnull(1/0, ‘yes’); =yes
    • select ifnull(‘’, ‘yes’); =‘’
  • isnull(expr) 如果expr为null,那么isnull()的返回值为1，否则为0
    • select isnull(1 + 1); =0
    • select isnull(1/0); =1
  • nullif(expr1, expr2) 如果expr1 = expr2,结果为null,否则返回值为expr1,这和case when expr1 = expr2 then null else exp1 end 相同
    • select nullif(1, 1); =null
    • select nullif(1, 2); 1
  • replace 替换字符串 update tablea set province_code = replace(province_code, ‘江苏’, ‘JIANGSU’) where 1 = 1;
  • decimal(m, d)类型
    m是数字的最大位数，它的范围是1-65（包含小数位），默认值10，d是小数点后的位数，范围0-30，不能大于m,默认值0
    • decimal(5, 2) 范围：-999.99~999.99
    • decimal(7, 6) 范围：-9.999999~9.999999
  • 日期推迟、提前
    date_sub(sysdate(), interval -2 MONTH) AS date1
    date_sub(sysdate(), interval 2 MONTH) AS date2
    sysdate: 2018-10-08 13:51:03
    date1: 2018-12-08 13:51:03
    date2: 2018-08-08 13:51:03
  • adddate(sysdate(), interval -2 month) 后延两个月
    adddate(sysdate(), interval 2 month) 前推两个月
  • select TO_DAYS(now()) as days; 返回从0年开始的天数
    select TO_DAYS(‘2018-1-1’) as days; 格里高利历法（阳历）1582年之前的值不准
  • from_day(N) 根据天数返回日期
  • json_unquote 去掉引号
  • json_extract 提取json中的值（根据键取值）
    DDL:`code` text COMMENT ‘编码’,
    `code2` varchar(30) character set utf8 collate utf8_bin generated always as (json_unquote(json_extract(`code`，‘$.id’))) virtual comment ‘code2’,
    code列插入数据{“id” : “111”} 列code2自动插入值111
• 规则
  • 与mysql保留字、关键字相同的字符需要使用反引号`区分
  • call 调用存储过程
  • replace into 是insert into 的增强版：如果发现表中已经有此行数据（根据主键或唯一索引判断）则先删除此行数据，然后插入新的数据（更新）；否则直接插入数据，而insert into 主键唯一索引相同时直接报错
  • mul：代表普通索引（非唯一索引）
  • select a.id from tablea a left join tableb b on b.head_id = a.id where 1 = 1
    a : b = 1 : n
    若此时a表一条记录连接b表多条记录，则会出现b表是一条数据却查出多条记录，所以需要select distinct …
  • select a.id, b.id from tablea a, tableb b;
    这种情况若a有7中可能，b有10种可能，那么就会查出70行记录
  • 创建视图：create or replace algorithm = undefined view tablea_v as select a.* from tablea a inner join batleb b on a.id = b.head_id where …; 表改了视图会同步做相应更改
  • text 最多可容纳65535个字节，mysql中使用utf-8mb4字符集一个字符占用4个字节，最多可存储(65535-2)/4 = 16383个字符
  • collate(核对) utf8_bin/utf8mb4_bin 是以二进制值进行比较，区分大小写，
    utf8_general_ci/utf8mb4_general_ci 是一般默认比较，不区分大小写
  • GENERATED ALWAYS AS VIRTUAL 虚拟列，不支持insert 和 update，只能跟据别的列插入值时经过设定的逻辑计算出的值插入到这个虚拟列中，也就是说不能直接对它进行操作，只能间接进行。
  • DDL DEFAULT默认值问题，默认值只会在insert的时候插入一条默认值，至于历史数据以前没设置默认值的话并不会在查询的时候显示默认值，所以在后面才设置默认值，之前没值的情况想设为默认值则需要刷数（数据割接）
  • 使用<![CDATA[]]> 对&、<、>、” 、’ 这五个字符转义，<和&是必须的
  • 类型对应

mysql	jdbcType	javaType
text	LONGVARCHAR	String
varchar	VARCHAR	String
char	CHAR	String