MySQL__速成一套（part2）

代码参考

---

创建表

// 查询表结构
SHOW CREATE TABLE 表名;

// 创建表格式
CREATE TABLE 表名(
       字段1 字段1类型[COMMENT 字段1注释],
       字段2 字段2类型[COMMENT 字段2注释],
       字段3 字段3类型[COMMENT 字段3注释](没有逗号)
)[COMMENT 表注释];

// 例子1
create table tb_user(
          id int comment "编号",
          name varchar(50) comment "姓名",
          age int comment "年龄",
          gender varchar(1) comment "性别"
) comment "用户表 ;

// 例子2
create table user (
     id int primary key auto_increment comment '主键',
     name varchar(10) not null unique comment '姓名',
     age int check( age > 0 && age <= 120 ) comment '年龄'
     status char(1) default '1' comment '状态'
     gender char(1) comment '性别'
) comment '用户表';

// 注释:
ENGINE（存储引擎） = InnoDB（默认值）
AUTO_INCREMENT（id设置为自增）
DEFAULT CHARSET（当前表的字符集）
// 查看当前数据库所支持的引擎:
MyISAM   早期默认的存储引擎
InnoDB   目前默认的存储引擎

---

数据类型的推荐用法

数据类型的相关例子
// 例子-年龄
age 年龄 (0~150)
数值小 所以使用TINYINT类型 / 不可能为负数 所以使用无符号范围 (取值范围0~255)
>>> age TINYINT UNSIGNED
// 例子-分数
score 分数 (0~100)
分数有小数点,推荐使用双精度DOUBLE
double 参数1:长度位数 参数2,:小数点位数
double(4,1) 长度4位 小数点1位
>>> score double(4,1)
// 例子-字符串
char(10)    存储10个字符
varchar(10) 存储10个字符

---

终端用户操作

创建用户         CREATE USER 'piter'@'localhost' IDENTIFIED BY '123456';
本地用户登录     终端:mysql -u piter -p
修改用户密码     ALTER USER 'tony'@'%' IDENTIFIED WITH mysql_native_password by '000000';
删除用户         DROP USER 'tony'@'%';
任意用户都可以访问数据库     CREATE USER 'tony'@'%' IDENTIFIED BY '123456';

---

SQL性能优化

【SQL性能优化】
一条一条插入数据,每次都进行连接,性能慢

方法1-批量插入数据
insert into 表名 values(值1),(值2)...;

方法2-手动提交事务
// 例子:
默认是自动提交事务,需要手动开启
start transaction; // 开启
insert into ... // 插入数据1
insert into ... // 插入数据2
insert into ... // 插入数据3
commit // 提交;

---

SQL文件备份

【sql文件-导出和导入】
方法1:第三方软件进行备份 (如: Navicat 16 for MySQL)
方法2:Windows 终端下进行数据备份
【导入】
 1.终端: mysql -u 用户名 -p
 2.切换数据库: use 数据库名
 3.导入sql文件: source SQL文件路径
【导出】
 1.进入mysql下的bin目录->开启终端 : 参考路径( C:\Program Files\MySQL\MySQL Server 8.0\bin\打开终端: )
 2.终端输入 : mysqldump -u 用户名 -p 数据库名 > sql文件路径
 参考命令(mysqldump -u root -p items > C:\Users\Administrator\Desktop\test.sql)

---

字符串函数

select concat('hello','mysql');
select lower('hello');
select upper('hello');
select lpad('01',5,'-');
select trim('  hello  ');
select substring('hello-mysql',1,5);
UPDATE person SET teststr=UPPER('jack');
UPDATE person SET workno = lpad(workno,5,'0');

---

日期函数

// 推迟当前日期
select date_add(now(),INTERVAL 1 month); // 推迟1个月(应用:cookie截至日期)
select date_add(now(),INTERVAL 1 year); // 推迟1年
// 两个日期之间的天数
select datediff('2021-12-01','2021-11-01');

---

流程函数

select if(false,'OK','ERROR');
select ifnull('','Default');
select ifnull(null,'Default');

// 复杂的CASE方法
// select name,gender from person;
select name,(
             CASE gender 
             WHEN '男' then '男士' 
             WHEN '女' then '女士' 
             ELSE '其它结果...' 
             END
            ) AS '测试内容' 
from person;
// 注释:
// 如果gender是男就输出男士 
// 如果是女就输出女士 
// 其它就输出其它结果 
// 最后把搜索的结果起个标题名:测试内容

// 更复杂的CASE方法
select name,
    (
     CASE gender 
     WHEN '男' then '男士' 
     WHEN '女' then '女士' 
     ELSE '其它结果...' 
     END
    ) AS '性别',
    (
     CASE 
     WHEN age<30 then '少年' 
     WHEN age>=30 AND age<=50 then '中年' 
     ELSE '其它结果...' 
     END
    ) AS '年龄'
from person;

---

外键约束

// 创建表
CREATE TABLE emp(
   id int auto_increment comment 'ID' primary key ,
   name varchar(50) not null comment '姓名',
   age int comment '年龄',
   dept_id int comment '部门ID'
) COMMENT '员工表';

// 创建-dept表
CREATE TABLE dept(
      id INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,
      name VARCHAR(50) NOT NULL COMMENT '部门名称'
) COMMENT '部门表';
 
INSERT INTO dept (
    id,`name`
) VALUES (1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办');

// 例子-添加或删除外键
// 添加外键
ALTER TABLE emp ADD 
CONSTRAINT fk_emp_dept_id 
FOREIGN KEY(dept_id) 
REFERENCES dept(id);
// 注释: emp表->dept_id字段 与dept表->id字段 关联

// 删除外键
ALTER TABLE emp 
DROP FOREIGN KEY fk_emp_dept_id;

//例子-同步更新:更复杂的添加外键
ALTER TABLE emp 
ADD CONSTRAINT fk_emp_dept_id 
FOREIGN KEY(dept_id) 
REFERENCES dept(id) 
ON UPDATE CASCADE 
ON DELETE CASCADE;

// 注释1:dept表->删除某个id  emp表->dept_id->数值也会删除
// 注释2:删除dept表->某个id->值为2  emp表->dept_id->数值为2的数据条也会被删除掉

---