mysqljson类型数据设置默认值 mysql默认值怎么设置性别

增

/* 创建表 */
CREATE TABLE `table_user_clone1` (
  `user_id` smallint DEFAULT NULL COMMENT '用户id',
  `user_name` varchar(50) DEFAULT NULL COMMENT '用户姓名',
  `user_birthday` date DEFAULT NULL COMMENT '生日',
  `user_idcard` char(18) DEFAULT NULL COMMENT '身份证信息',
  `user_sex` char(1) DEFAULT NULL COMMENT '性别',
  `user_age` tinyint unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='user information';

/* 添加新列 */
ALTER TABLE table_user ADD user_sex CHAR(1) COMMENT '性别';
ALTER TABLE table_user ADD user_age INT COMMENT '年龄';

/* 增加数据 */
INSERT INTO table_user(user_id, user_name, user_birthday, user_idcard, user_sex)
                        VALUES (1,'woshku','19990615','34240119990000000X','男');  /* 写全所有列名 （没必要） */
INSERT INTO table_user VALUES (5, 'hui', '19990829', '342401199908290000','女'); /* 不写列名 （默认全部列） */
INSERT INTO table_user(user_id, user_name) VALUES (6, 'Secret');                 /* 插入部分数据 */

删

/* 删除表 */
DROP TABLE table_user_clone1;
/* 删除表 */
DROP TABLE table_user_clone1;
/* 使用 DELETE 语句从单个表中删除数据 */
/* DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句] */
DELETE FROM account where name = '张三';

改

/* 修改表名 */
ALTER TABLE table_user RENAME TO table_test;
ALTER TABLE table_test RENAME TO table_user;
/* 改变某列的数据类型 */
ALTER TABLE table_user MODIFY COLUMN user_age TINYINT UNSIGNED;
/* 更新表中某列的值 */
/* 【用法】 update 表名 set 列名 = 改成的值 where 限制条件 */
/* 【注意】 不加限制条件将改变整个选定的列 */
UPDATE table_user SET user_id = 1 WHERE user_id = 8; /* 把表中user_id = 8 的值 设置为 1 */
UPDATE table_user set user_idcard = LPAD(user_idcard, 18, '0'); /* 将表中没有18位的身份证号前全部填充为0 */

查

/* 通常所用到的： */
/* SELECT 要查的字段(列的名字) [as(关键字可以不写) 取别名(通常排序为了方便会用到)] FROM 表名 [WHERE 范围] [LIKE 范围内特定的字符] [GROUP BY 排序的依据] [DESC(是否是逆序，是就加上，不是省略)] */
/* 查看自己所在的仓库 */
/* 【注意】 加括号 */
SELECT DATABASE();
/* 查看当前仓库的所有的表 */
SHOW TABLES;
/* 查看表中的数据 */
DESC table_user;
/* 查询 table_user 中user_id中大于等于 3 的 小于 5 的 */
SELECT * FROM table_user WHERE user_id >= 3 && user_id < 5;
SELECT * FROM table_user WHERE user_id >= 3 and user_id < 5;
SELECT * FROM table_user WHERE user_id between 3 and 5;
/* 通过列数值查询 */
SELECT * FROM table_user WHERE user_id = 3 OR user_id = 4 OR user_id = 5;
SELECT * FROM table_user WHERE user_id IN (3,4,5);
/* 查询 不为空 */
SELECT * FROM table_user WHERE user_birthday is not null;
/* 查询 ------------ _ : 单个任意字符
                    % : 多个任意字符 */
SELECT * FROM table_user WHERE user_name LIKE 'wosh%';
/* 查询 返回唯一不同的值 */
SELECT DISTINCT user_birthday FROM table_user;
/* 排序查询 */
SELECT * FROM table_user ORDER BY user_id;      /* 升序 */
SELECT * FROM table_user ORDER BY user_id desc; /* 降序 */

/* 分组查询 GROUP BY 后面加分组的依据 */
/* 通过性别分组 显示年龄的平均值 */
SELECT user_sex, AVG(user_age) FROM table_user GROUP BY user_sex;
/* 通过性别分组 显示年龄的平均值及个数 年龄高于70的不参与分组 */
SELECT user_sex,AVG(user_age),COUNT(user_age) FROM table_user WHERE user_age <= 70 GROUP BY user_sex;
/* 通过性别分组 显示年龄的平均值及个数 年龄高于70的不参与分组，显示count（）结果要大于，否则不显示 */
SELECT user_sex,AVG(user_age),COUNT(user_age) FROM table_user WHERE user_age <= 70 GROUP BY user_sex HAVING COUNT(user_age) > 1;
/* 聚合排序后面可以加备注 */
SELECT user_sex , AVG(user_age) 平均年龄,COUNT(user_age) 人数 FROM table_user WHERE user_age < 70 GROUP BY user_sex;

/* 分页查询 参数 A,B 显示第 A 条记录后的 B 条记录 */
SELECT * FROM table_user LIMIT 0,3;        /* 显示第 0 条记录后的 3 条记录 （显示第1,2,3条数据） */
SELECT * FROM table_user LIMIT 3,2;        /* 显示第 3 条记录后的 2 条记录 （显示第4,5条数据） */
SELECT * FROM table_user LIMIT 6,1;        /* 显示第 6 条记录后的 1 条记录 （显示第7条数据） */

/* 内连接查询 */
/* 隐式 内连接 (明确提出查看的列) */
/* 通过他两张表的相同部分user_id 查询 table_user的name 和 table_user_clone的user_grade */
SELECT table_user.user_name,
       table_user_clone.user_grade
FROM table_user,table_user_clone
WHERE table_user.user_id = table_user_clone.user_id;
/* 显式 内连接 (查看所有相关联的列) */
/* select 字段列表 from 表名1 [inner] join 表名2 on 条件 */
SELECT * FROM table_user JOIN table_user_clone ON table_user.user_id = table_user_clone.user_id;
SELECT * FROM table_user INNER JOIN table_user_clone ON table_user.user_id = table_user_clone.user_id;

/* 外连接查询 */
/* 左外连接 查询的是左表的所有数据及其交集部分 */
SELECT table_user.*,
       table_user_clone.user_grade
FROM table_user
    LEFT JOIN table_user_clone
ON table_user.user_id = table_user_clone.user_id;
/* 右外连接 查询的是右表的所有数据及其交集部分 */
SELECT table_user.user_birthday,
       table_user_clone.*
FROM table_user_clone
    RIGHT JOIN table_user
ON table_user.user_id = table_user_clone.user_id;

/* 子查询(查询中的嵌套查询) */
SELECT MAX(user_age) FROM table_user_clone;
SELECT * FROM table_user_clone WHERE table_user_clone.user_age = 78;
/* 将上面两个结合在一起用一条解决就是子查询 */
SELECT * FROM table_user_clone WHERE table_user_clone.user_age = (SELECT MAX(user_age) FROM table_user_clone);
/* 使用子查询查看table_user中年龄大于平均值的 */
SELECT * FROM table_user WHERE table_user.user_age > (SELECT AVG(user_age) FROM table_user);

/* 自连接 必须其别名 把一张表看作两张表 这两张表进行外连接，内连接，子查询        */
/* 查询员工对应领导的名字 */
SELECT a.emp_name '员工',b.emp_name '领导' FROM table_emp a,table_emp b WHERE a.emp_mangerid = b.emp_id;
/* 查询领导对应员工的名字 */
SELECT a.emp_name '员工',b.emp_name '领导' FROM table_emp a LEFT JOIN table_emp b ON a.emp_mangerid = b.emp_id;

/* 联合查询 */
/* UNION/UNION ALL 查询 把多次查询结果合并出来，形成一个新的查询结果集 */
/* UNION 会将查询的数据去重 */
/* 注意：多张表的列数必须保持一致，字段类型也一致 */
SELECT * FROM table_emp WHERE emp_salary < 5000
UNION ALL
SELECT * FROM table_emp WHERE emp_age > 15;

克隆表

/* 首先获取创建 table_user 的建表语句 */
SHOW CREATE TABLE table_user;
/* 然后通过 table_user 的建表语句创建出表 table_user_clone */
CREATE TABLE `table_user_clone1` (
  `user_id` smallint DEFAULT NULL COMMENT '用户id',
  `user_name` varchar(50) DEFAULT NULL COMMENT '用户姓名',
  `user_birthday` date DEFAULT NULL COMMENT '生日',
  `user_idcard` char(18) DEFAULT NULL COMMENT '身份证信息',
  `user_sex` char(1) DEFAULT NULL COMMENT '性别',
  `user_age` tinyint unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='user information';
/*  查询出 table_user 的数据，并复制给table_user_clone */
INSERT INTO table_user_clone(`user_id`,`user_name`,`user_birthday`,`user_idcard`,`user_sex`,`user_age`)
            SELECT * FROM table_user;

用户管理

/* 查看所有的用户 */
SELECT user,host FROM mysql.user;
/* 添加用户 */
CREATE USER woshku@localhost IDENTIFIED BY '123456';
/* 删除用户 */
DROP USER woshku@localhost;
/* 授予权限 */
/* 特定的数据库特定的表 */
GRANT ALL ON test.table_user TO woshku@localhost;
/* 给woshku用户最大的权限 */
GRANT ALL ON *.* TO woshku@localhost;
/* 查询权限 */
SHOW GRANTS FOR woshku@localhost;
/* 撤销权限 */
REVOKE ALL ON test.table_user FROM woshku@localhost;

函数

/* 聚合函数 */
SELECT AVG(user_id)   avg_user_id   FROM table_user; #计算平均值 AVG(列名) 标志 FROM 表名
SELECT MAX(user_id)   max_user_id   FROM table_user; #计算最大数
SELECT MIN(user_id)   min_user_id   FROM table_user; #计算最小数
SELECT COUNT(user_id) count_user_id FROM table_user; #计算个数
SELECT SUM(user_id)   sum_user_id   FROM table_user; #计算和

/* 字符串函数 */
/* 字符串拼接 */
SELECT CONCAT('hello','mysql');
/* 将字符串全部转为小写、大写 */
SELECT LOWER('Hello MySQL');
SELECT UPPER('hello MySQL');
/* 左,右填充 */
SELECT LPAD('01', 5, '-'); /* ---01 目标字符串，目标长度，填充字符 */
SELECT RPAD('01', 5, '-'); /* 01--- 目标字符串，目标长度，填充字符 */
/* 去除空格 */
SELECT TRIM(' hello mysql '); /* 去除左右空格，中间的不去除 */
/* 截取字符串 */
SELECT SUBSTRING('HelloMYSQL',1,5); /* Hello 从第 1 个开始，截取 5 个 */

/* 数值函数 */
/* 向上，下取整 */
SELECT CEIL(1.1);
SELECT FLOOR(1.9);
/* 取模 */
SELECT MOD(6, 4); /* 6 % 4 = 2 */
/* 取随机数 0 ~ 1 之间 */
SELECT RAND();
SELECT RAND() * 9 + 1; /* 1 ~ 10 : A ~ B 之间 SELECT RAND() * (B-A) + A; */
/* 四舍五入 */
SELECT ROUND(5.6317, 3); /* 目标数， 保留的小数位数 */

/* 日期函数 */
/* 返回当前日期 yyyy-MM-dd */
SELECT CURDATE();
/* 返回当前时间 hh:mm:ss */
SELECT CURTIME();
/* 返回日期加时间 yyyy-MM-dd hh:mm:ss */
SELECT NOW();
/* 返回年月日 */
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
/* 指定时间 加上 指定数字(可以负数) 指定单位 返回一个新时间 */
SELECT DATE_ADD(NOW(),INTERVAL 70 DAY);
/* 返回两个指定时间之间相差的天数（含负数） 第一个时间减去第二个时间 */
SELECT DATEDIFF('2021-2-12','2021-01-31');

/* 流程函数 */
/* A,B,C 如果A为真，返回字符串B，否则C */
SELECT IF(false,'ok','error');
/* A,B 如果A为NULL，返回B，否则A */
SELECT IFNULL('ok','default');
/* case ；case when then else end */
/* 查询表中每人的住址 （北京、上海 ----> 一线城市 否则二线） */
SELECT
       user_name,
       (CASE user_addr WHEN '北京' THEN '一线城市' WHEN '上海' THEN '一线城市'
        ELSE '二线城市' END) as '工作地址'
FROM table_user;

约束

/* 根据约束要求 完成表的创建 */
/**
  字段名    字段含义      字段类型        约束条件              约束关键字
  id       id唯一标识    int           主键且自动增长         PRIMARY KEY,AUTO_INCREMENT
  name     姓名         varchar(10)   不为空，且唯一         NOT NULL,UNIQUE
  age      年龄         int           大于0，且 <=120       CHECK
  status   状态         char(1)       如果没有指定 默认为1    DEFAULT
  gender   性别         char(1)       无
  */
CREATE TABLE table_user_detail (
    user_id int PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    user_name varchar(10) NOT NULL UNIQUE COMMENT '姓名',
    user_age int CHECK ( user_age > 0 && user_age <= 120 ) COMMENT '年龄',
    user_status char(1)  DEFAULT '1' COMMENT '状态',
    user_gender char(1) COMMENT '性别'
) COMMENT '用户表' ;

/* 外键约束 <保证数据的一致性和完整性> */
/* 添加外键 */
/* 【用法】 ALTER TABLE 需要添加外键的表名 ADD CONSTRAINT fk_需要添加外键的表名_字段 FOREIGN KEY (需要添加外键的表的字段) REFERENCES table_dept(另一张表的字段); */
ALTER TABLE table_emp ADD CONSTRAINT fk_table_emp_dept_id FOREIGN KEY (dept_id) REFERENCES table_dept(dept_id);
/* 删除/更新 外键  */
/* 【用法】 */
ALTER TABLE table_emp DROP FOREIGN KEY fk_table_emp_dept_id;

/* 创建时指定删除和更新行为 直接在创建时后面加 ON UPDATE 在更新时的行为 ON DELETE 在删除时的行为 */
/* NO ACTION/RESTRICT  在父表中删除/更新时先检查是否有对应外键 有则不允许 默认是这个 */
/* CASCADE 删除/更新时 检查记录是否有对应外键，有则一起更新/删除 */
ALTER TABLE table_emp ADD CONSTRAINT fk_table_emp_dept_id FOREIGN KEY (dept_id) REFERENCES table_dept(dept_id) ON UPDATE CASCADE ON DELETE CASCADE ;
/* SET NULL 删除/更新时 检查记录是否有对应外键，有则设置为NULL（要求该外键允许改为NULL）*/
ALTER TABLE table_emp ADD CONSTRAINT fk_table_emp_dept_id FOREIGN KEY (dept_id) REFERENCES table_dept(dept_id) ON UPDATE SET NULL ON DELETE SET NULL;

事务

/* 事务 ：　一组操作的集合，是不可分割的工作单位，事务会将所有的操作作为一个整体一起向系统提交或撤销操作请求，这些操作要么同时成功，要么同时失败　*/
/* 方式一 */
/* 查看事务的提交方式 ：１：自动提交　0：手动提交 */
SELECT @@AUTOCOMMIT;
SET @@AUTOCOMMIT = 1;
/* 提交事务 */
COMMIT ;
/* 回滚事务 */
ROLLBACK ;
/* 方式二 */
/* 开启事务 START TRANSACTION / BEGIN */
START TRANSACTION ;
/* 提交事务 */
COMMIT ;
/* 回滚事务 */
ROLLBACK ;

/* 并发事务问题 */
/* 脏读 ：一个事务读到另外一个事务还没有提交的数据 */
/* 不可重复读 ：一个事务先后读取同一条记录，但两次读取到的记录不同，称为不可重复读　*/
/* 幻读 ：查询数据时没有查到，插入时又发现数据已经存在 */

/* 事务隔离级别 */
/* READ UNCOMMITTED 三个并发问题都会出现 （性能最高） */
/* READ COMMITTED （oracle默认） 脏读不会出现*/
/* REPEATABLE READ （mysql默认） 脏读，不可重复读不会出现 */
/* SERIALIZABLE 三个问题都不会出现 （性能最差）*/

/* 查看事务隔离级别 */
SELECT @@TRANSACTION_ISOLATION;
/* 设置事务隔离级别 */
/* SET [SESSION(会话级别，当前窗口有效)/GLOBAL(所有客户端窗口有效)] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ / SERIALIZABLE] */
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;

/* 事务的隔离级别越高，数据越安全，性能越低 */

案例

/* 生成六位随机验证码 */
SELECT LPAD(ROUND(RAND() * 1000000, 0), 6, '0');
/* 通过每个人的出生日期 返回距离当前时间的天数 并逆序排序*/
SELECT user_name,DATEDIFF(NOW(),user_birthday) as 天数 FROM table_user ORDER BY 天数 DESC;
/* 统计table_user成绩 展示时 >= 85 优秀  >= 60 及格 否则 不及格 */
SELECT user_name,
       (CASE WHEN user_grades >= 85 THEN '优秀'
           WHEN user_grades < 60 THEN '不及格'
           ELSE '及格' END )
           AS 成绩
FROM table_user;

MYSQL存储引擎

/* 存储引擎 通过查询建表语句可以看见mysql的默认建表的存储引擎是innoDB */
/* 体系结构 ：连接层　服务层　引擎层　存储层 */
/* 存储引擎就是存储数据，建立索引，更新、查询数据等技术的实现方式，存储引擎是基于表而非数据库，所以存储引擎又被成为表的类型 */
/* 查看当前数据库支持的存储引擎 */
SHOW ENGINES;
/* 创建表 指定存储引擎 */
CREATE TABLE my_memary (
    id   int,
    name varchar(20)
)ENGINE = MyISAM;

CREATE TABLE my_memary (
    id   int,
    name varchar(20)
)ENGINE = MEMORY; /* MEMORY 表储存在内存中 通常用于临时表 */

索引

先二级索引再聚集索引称为回表查询

性能分析

/* 查看sql执行效率 */
/* 查看服务器状态信息、[SESSION（当前会话） | GLOBAL（全局）] 查看当前数据库的增删改查的访问频次） 7个空格 */
SHOW STATUS ; /* 查看服务器状态信息 */
SHOW GLOBAL STATUS LIKE 'com_______'; /* 查看当前数据库的增删改查的访问频次） 7个空格 */

/* 慢查询日志 记录了所有执行时间超过指定参数（long_query_time 单位秒，默认10秒）的sql语句 */
/* MySql的慢查询默认没有开启 */
SHOW VARIABLES LIKE 'show_query_log';

/* profile */
/* 查看是否支持profile */
SELECT @@have_profiling;
/* 默认profiling是关闭的，设置在session/global级别开启profiling */
SET PROFILING = 1;
/* 查看当前会话中的所有sql语句的执行耗时情况 */
SHOW PROFILES ;
/* 查看指定query_id的sql语句各个阶段的耗时情况 */
SHOW PROFILE FOR QUERY 143;
/* 查看指定query_id的sql语句各个阶段的耗时情况 */
SHOW PROFILE cpu FOR QUERY 143;

/* explain执行计划 */
/* 使用方式为直接在letect语句之前加上explain / desc */
EXPLAIN SELECT * FROM table_emp WHERE emp_salary < 5000;
DESC SELECT * FROM table_emp WHERE emp_salary < 5000;