Mysql入门之路

MySQL架构

SQL语句分类 ● DDL：数据定义语句[create 表，库…]
● DML:数据操作语句[增加 insert、修改update、删除delete]
● DQL:数据查询语句[select]
● DCL:数据控制语句[管理数据库]

创建数据库

1. CHARACTER SET:指定数据库采用的字符集，如果不指定字符集，默认utf8
2. COLLATE:指定数据库字符集的校对规则（常用的utf8_bin[区分大小写]、utf8_general_ci[不区分大小写]注意默认是utf8_general_ci）

备份数据库

● 备份数据库（注意：在DoS执行）命令行
mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > d：文件名.sql
● 恢复数据库（注意：进入MySql命令行再执行） Source 文件名.sql ● 备份数据库的表（注意：在DoS执行）命令行
● mysqldump -u 用户名 -p 数据库1 数据库2 数据库n >  d：文件名.sql

# 使用指令创建数据库
CREATE DATABASE db01;
# 删除数据库 
#DROP DATABASE db01;
# 创建一个使用utf8字符集的db02数据库
CREATE DATABASE db02 CHARACTER SET utf8
#创建一个使用utf8字符集，并带校对规则的db03数据库
CREATE DATABASE db03 CHARACTER SET utf8 COLLATE utf8_bin
#校对规则 utf8_bin 区分大小写，默认utf8_general_ci 不区分大小写
SELECT * FROM t1  WHERE NAME = 'tom'
#演示删除和查询数据库
#查看当前数据库服务器中的所有数据库
SHOW DATABASES
#查看当前数据库服务器中的所有数据库
SHOW CREATE DATABASE db01
#在创建数据库、表的时候，为了规避关键字，可以使用反引号解决
# 删除前面创建的db01数据库

Mysql 列类型

• Mysql 列类型即mysql的数据类型

1.mysql 数值型（整数）的基本使用
说明：使用规范，在能够满足需求的情况下，尽量选择占用空间小的类型

# 老韩使用tingint来演示范围 有符号 -128 ~ 127 如果没有符号 0 ~ 255
# 说明：表的字符集、校验规则、存储引擎、老师使用默认
# 1.如果没有指定 UNSIGNED 则 TINYINT 就是有符号
# 2.如果指定 UNSIGNED 则 TINYINT 就是无符号
CREATE TABLE t3 (
   id TINYINT );
CREATE TABLE t4  (
   id TINYINT UNSIGNED );
INSERT INTO t3 VALUES (-128);
INSERT INTO t3 VALUES(-1);
SELECT * FROM t3
INSERT INTO t4 VALUES(255);
SELECT * FROM t4 

2.数值型（bit）的使用

# 演示bit类型的使用
# 说明
# 1.BIT_COUNT(N) N 在 1-64
# 2.添加数据范围按照你给的位数来确定，比如 n = 8 表示一个字节 0 ~ 255
# 3.显示按照bit
# 4.查询时，仍然可以按照数来查询

CREATE TABLE t05 (num BIT(8));  
INSERT INTO  t05 VALUES (255);
SELECT * FROM t05 
SELECT * FROM t05 WHERE num = 1;

3.数值型（小数）的基本使用
● FLOAT/DOUBLE(UNSIGNED) Float 单精度精度 Double 双精度
● DECIMAL[M,D][UNSIGNED] a.可以支持更加精确的小数位，M是小数位数（精度）的总数，D是小数点（标度）的位数
b.如果D是0，则值没有小数点或分数部分。M最大是65，D最大是30，如果D被省略，默认是0，如果M被省略，默认是10
建议：如果希望小数的精度高，推荐使用decimal

# 演示 DECIMAL 类型，FLOAT,DOUBLE的使用
# 创建表
CREATE TABLE t06(
num1 FLOAT,
num2 DOUBLE,
num3 DECIMAL(30,20)
);
# 添加数
INSERT INTO t06 VALUES(88.1234567891234,88.1234567891234,88.1234567891234);
SELECT * FROM t06 
# DECIMAL 可以存放很大的数

CREATE TABLE t07(
num DECIMAL(65));
INSERT INTO t07 VALUES (89999999999333333333335555555555677777777777777);
SELECT * FROM t07
CREATE TABLE t08(
num BIGINT UNSIGNED
)
INSERT INTO t08 VALUES (89999999999333333333335555555555677777777777777);
SELECT * FROM t08

4.字符串的基本使用
● CHAR（size） 固定长度字符串，最大255字符
● VARCHAR(size)   0 ~ 65535 可变长度字符串 最大65532字节,[utf8编码最大21844字符 1-3个字节用于记录大小]

# 演示字符串类型CHAR  VARCHAR
# 注释的快捷键 Navicat(ctrl+/)  SQLyog(shift+ctrl+c)
-- CHAR（size）
-- 固定长度字符串，最大255字符
-- VARCHAR(size)   0 ~ 65535
-- 可变长度字符串 最大65532字节,[utf8编码最大21844字符 1-3个字节用于记录大小]
-- 如果表的编码是 utf8 VARCHAR(size) size = (65535-3)/3 = 21844
-- 如果表的编码是 gbk  VARCHAR(size) size = (65535-3)/2 = 32766 
CREATE TABLE t09 (
`name` CHAR(255));
CREATE TABLE t10 (
`name` VARCHAR(21844));
DROP TABLE t10 

5.演示字符串使用细节
● 细节一：CHAR(4) 和 VARCHAR(4) 这个4表示的是字符，而不是字节，不区分字符是汉字还是字母
● 细节二：CHAR(4) 是定长（固定的大小），就是说，即使你插入’aa’,也会占用分配的4个字符空间VARCHAR(4) 是变长（变化的大小），就是说，即使你插入’aa’,实际占用空间大小并不是4个字符，而是按照实际占用空间来分配（VARCHAR 本身还需要占用1-3个字节来记录存放内容长度）L(实际数据大小)+（1-3）字节
● 细节三：
什么时候使用char，什么时候使用varchar
a.如果数据是定长，推荐使用char，比如MD5的密码，邮编，手机号，身份证号码等.char(32)
b.如果一个字段的长度是不确定，我们使用varchar，比如留言，文章，
c.查询速度：char>varchar
● 细节四：在存放文本时也可以用Text数据类型，可以将text列视为varchar列，注意Text不能有默认值。大小0 ~ 2^16字节，如果希望存放更多的数据，可以选择 MEDIUMTEXT 0 ~2^24,或者LONGTEXT 0 ~2^32

# 演示字符串使用细节
# CHAR(4) 和 VARCHAR(4) 这个4表示的是字符，而不是字节，不区分字符是汉字还是字母
CREATE TABLE t11(
`name` CHAR(4));
INSERT INTO t11 VALUES('韩顺平好');
SELECT * FROM t11 
CREATE TABLE t12 (
`name`VARCHAR(4));
INSERT INTO t12 VALUES('ab北京')
SELECT * FROM t12 
# 如果verchar不够用，可考虑使用mediuntext或者longtext
# 如果想简单点，可以直接使用text

CREATE TABLE t13(content TEXT,content2 MEDIUMTEXT,content3 LONGTEXT);
INSERT INTO t13 VALUES('韩顺平教育','韩顺平教育100','韩顺平教育100~~');

SELECT * FROM t13 

6.演示时间相关类型

# 演示时间相关类型
# 创建一张表，date，datetime,datestamp
CREATE TABLE t14 (
 birthday DATE , -- 生日
 job_time DATETIME, -- 记录年月日 时分秒
 login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
       ON UPDATE CURRENT_TIMESTAMP); -- 如果希望login_time列自动更新,需要配置
INSERT INTO t14(birthday,job_time) VALUES('2022-11-11','2022-11-11 10:10:10')
SELECT * FROM t14 
# 如果我们更新t14表的某条记录，login_time列会自动的以当前时间进行更新

创建表练习
创建一个员工表emp，选用适当的数据类型

-- 创建一个emp表课堂练习
-- 字段 属性
-- Id 整型
-- name 字符型
-- sex 字符型
-- birthday 日期型（date）
-- entry_date 日期型（date）
-- jod 字符型
-- salary 小数型
-- resume 文本型
CREATE TABLE `emp`(
id INT,
`name` CHAR(32) ,
sex CHAR(1),
birthday DATE,
entry_date DATETIME,
job CHAR(32),
sarary DOUBLE,
`resume` TEXT) CHARSET utf8  COLLATE utf8_bin ENGINE INNODB;
DROP TABLE `emp`
INSERT INTO `emp` VALUES(
  100,'小妖怪','男','2000-11-11','2010-11-11 11:11:11','巡山的',3000,'大王叫我来巡山');
SELECT *  FROM `emp`

修改表练习

# 修改表的操作练习
-- 员工表emp上增加一个image列，VARCHAR 类型（要求在resume后面）
ALTER TABLE emp ADD image VARCHAR(32) NOT NULL DEFAULT '' AFTER resume; 
DESC emp -- 显示表结构,可以查看表的所有行
-- 修改job列，使其长度为60
ALTER TABLE emp MODIFY job VARCHAR(60) NOT NULL DEFAULT '';
-- 删除 sex 列
ALTER TABLE emp DROP sex ;
-- 表名改为enmployee
RENAME TABLE emp TO employee;
-- 修改表的字符集为utf8
ALTER TABLE employee CHARACTER SET utf8;
-- 列名 `name` 修改为user name
ALTER TABLE employee CHANGE `name` user_name VARCHAR(32) NOT NULL DEFAULT '';
DESC employee;

数据库CRUD语句

1.insert语句
● 使用insert语句向表中插入数据

-- 创建一张商品表goods(id int,goods_name VARCHAR(10),price DOUBLE)
-- 添加2条记录
CREATE TABLE `goods`(
id INT,
goods_name VARCHAR(10),
price DOUBLE);
INSERT INTO `goods`(id,goods_name,price) VALUES(100,'华为手机',8888);
INSERT INTO `goods`(id,goods_name,price) VALUES(20,'苹果手机',9999);
SELECT *  FROM `goods`;
# 说明 INSERT 语句的细节
-- 1.插入的数据应该与字段的数据类型相同
--   比如把'abc'添加到int类型会报错
-- 2.数据的长度应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。
-- 3.在values中列出的数据位置必须与被加入的列的的排列位置相对应
-- 4.字符和日期型数据应包含在单引号中
-- 5.列可以插入空值[前提是该字段允许为空]，insert into table value (null)
-- 6.insert into tab_name(列名..),VALUES(),(),()添加多条记录
INSERT INTO `goods`(id,goods_name,price)
 VALUES(30,'小米手机',2000),(40,'三星手机',3000),(50,'vivo手机',4000);
 -- 7.如果是给表中的所有字段添加数据，可以不写前面的字段名称 -- 8.默认值的使用，当不给某个字段值时，如果有默认值就会添加默认值，否则报错
  -- 如果某个列没有指定not null，那么在当添加数据时，没有给定值，则会默认给null
INSERT INTO `goods`(id,goods_name)
 VALUES(30,'格力手机');

2.update语句

# 演示update语句
-- 要求；在上面创建的employee表中修改表的记录[如果没有带where会修改所有记录，所以要小心]
UPDATE employee SET sarary = 5000;
-- 1.将所有员工薪水修改为 5000
-- 2.将员工姓名为小妖怪的员工薪水修改为3000
UPDATE employee SET sarary = 3000 WHERE user_name = '小妖怪';
-- 2.将老妖怪的薪水在原有基础上加1000
INSERT INTO employee VALUES(200,'老妖怪','1990-10-10','2000-11-11 10:10:10','捶背的',5000,'给大王捶背','d：\a.jpg');

UPDATE employee SET sarary = sarary+1000 
       WHERE user_name = '老妖怪';
SELECT * FROM employee 

● 使用细节
a.UPDATE 语句可以用新值更新原有表行中的各列
b.SET子句指示要修改的哪些列和要给予哪些值
c.WHERE 子句指定要更新哪些行。如果没有WHERE子句则更新所有行
d.如果需要修改多个字段，可以通过 set 字段1 = 值1，字段2 = 值2…

3.delete语句
● 使用细节
a.如果不使用where子句，将删除表中所有数据
b.delete不能删除某一列的值（可使用updtate 设为null或 ”）
c.使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table 语句。drop table 表名；

# DELETE 语句演示
-- 删除表中称为'老妖怪'的记录
DELETE FROM employee WHERE user_name = '老妖怪'; 
-- delete不能删除某一列的值（可使用updtate 设为null或 ''）
UPDATE employee SET job = '' WHERE user_name = '小妖怪';
-- 删除表中所有记录
DELETE FROM employee
-- 删除表
DROP TABLE employee 

4.select语句
● 注意事项 a.select指定查询哪些列的数据
b.column指定列名
c.*代表查询所有列
d.from指定查询那张表
e.distinct可选，指显示结果时，是否去掉重复数据

# SELECT语句【重点 难点】
CREATE TABLE student(
 id INT NOT NULL DEFAULT 1,
 `name` VARCHAR(20) NOT NULL DEFAULT '',
 chinese FLOAT NOT NULL DEFAULT 0.0,
 english FLOAT NOT NULL DEFAULT 0.0,
 math FLOAT NOT NULL DEFAULT 0.0
);
INSERT INTO student(id,`name`,chinese,english,math)VALUES(1,'韩顺平',89,78,90);
INSERT INTO student(id,`name`,chinese,english,math)VALUES(2,'张飞',67,98,56);
INSERT INTO student(id,`name`,chinese,english,math)VALUES(3,'宋江',87,78,77);
INSERT INTO student(id,`name`,chinese,english,math)VALUES(4,'关羽',88,98,90);
INSERT INTO student(id,`name`,chinese,english,math)VALUES(5,'赵云',82,84,67);
INSERT INTO student(id,`name`,chinese,english,math)VALUES(6,'欧阳锋',55,85,45);
INSERT INTO student(id,`name`,chinese,english,math)VALUES(7,'黄蓉',75,65,30);
INSERT INTO student(id,`name`,chinese,english,math)VALUES(8,'韩信',45,65,99);
SELECT * FROM student
-- 查询表中所有学生的信息
-- 查询表中所有学生和对应的英语成绩
SELECT  `name`, english  FROM student ;
-- 过滤表中重复数据
SELECT DISTINCT english  FROM student;
-- 要查询的记录，每个字段都相同，才会去重
SELECT DISTINCT `name`, english  FROM student;
# SELECT 语句的使用
-- 统计每个学生的总分
SELECT `name`,(chinese+english+math)  FROM student;
-- 在所有学生加10 分的情况
SELECT `name`,(chinese + english + math +10)  FROM student;
-- 使用别名表示学生分数
SELECT `name` AS '名字',(chinese + english + math +10) AS total_score  FROM student;

在where语句中经常使用的运算符

# SELECT 语句
-- 查询姓名为赵云的学生成绩
SELECT * FROM student WHERE `name` ='赵云';
-- 查询英语成绩大于90的同学
SELECT * FROM student WHERE english >90;
-- 查询总分大于200分的所有同学
SELECT * FROM student WHERE chinese + english + math > 200 ;
-- 查询math大于60，并且（and）id 大于4 的学生成绩
SELECT *FROM student WHERE math > 60 and id > 4;
-- 查询英语成绩大于语文成绩的同学
SELECT * FROM student WHERE english > chinese ;
-- 查询总分大于200分，并且数学成绩小于语文成绩的姓赵的同学
-- 赵% 表示名字以韩开头的就可以
SELECT * FROM student WHERE
chinese + english + math > 200 AND math < chinese AND `name` LIKE '赵%';
-- 查询英语分数在 80 - 90 之间的同学
SELECT * FROM student WHERE english BETWEEN 80 AND 90;
-- 查询数学成绩在89 ，90，91的同学
SELECT * FROM student WHERE math  = 80 or math = 90 or math = 91;
SELECT * FROM student WHERE math  IN (80,90,91);
-- 查询所有姓李的学生成绩
SELECT * FROM student WHERE `name` LIKE '韩%';
-- 查询数学分>80，语文分>80的同学
SELECT * FROM student WHERE math > 80 AND chinese > 80;
SELECT * FROM student
-- 课堂练习：
-- 查询语文分数在70 -80之间的同学
SELECT *FROM student WHERE chinese BETWEEN 70 AND 80; 
-- 查询总分为189，190，191同学
SELECT *FROM student 
 WHERE (chinese + english + math) =189 
 OR(chinese + english + math) =190 OR(chinese + english + math) =190;
-- 查询所有姓李或者姓宋的学生成绩
SELECT *FROM student WHERE `name` LIKE '李%' or `name`  LIKE '宋%';
-- 查询数学比语文多30分的同学
SELECT *FROM student WHERE(math-chinese) > 30 ;
# 演示GROUP BY 使用
-- 对数学成绩排序后输出【升序】
SELECT *FROM student  ORDER BY math;
-- 对总分从高到低的顺序输出
SELECT `name`,(chinese + english + math) AS total_score 
 FROM student  ORDER BY total_score DESC ;
-- 对姓韩的学生成绩[总分]升序输出
SELECT `name`,(chinese + english + math) AS total_score 
 FROM student WHERE `name` LIKE '韩%' ORDER BY total_score;
  
  # 演示 mysql 的统计函数的使用
-- 统计一个班级有多少学生
SELECT COUNT(*) FROM student
-- 统计数学成绩大于 90 的学生有多少？
SELECT COUNT(*) FROM student WHERE math >90;
-- 统计总分大于250 的人数有多少？
SELECT COUNT(*) FROM student WHERE (chinese + english + math) >250;
-- count(*)和count（列）的区别
-- 解释：count(*) 返回满足条件的记录的行数
-- count(列)：统计满足条件的某列有多少个，但是会排除 为null 的情况
CREATE TABLE t15 (
 `name` VARCHAR(20));
INSERT into t15 VALUES('tom');
INSERT into t15 VALUES('jack');
INSERT into t15 VALUES('mary');
INSERT into t15 VALUES(NULL);
SELECT * FROM t15;
SELECT count(*) FROM t15; -- 4
SELECT count(`name`) FROM t15; -- 3
-- 演示sum函数的使用
-- 统计一个班级数学总成绩
SELECT sum(math) FROM student;
-- 统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(math),SUM(english),SUM(chinese)FROM student;
-- 统计一个班级语文、英语、数学的成绩总和
SELECT SUM(math+english+chinese)FROM student
-- 统计一个班级语文成绩平均分
SELECT sum(chinese)/count(*) FROM student;
-- 演示avg的使用
-- 求一个班级数学平均分
SELECT AVG(math) FROM student;
-- 求一个班级总分平均分
SELECT AVG(math+english+chinese) FROM student;
-- 演示max和min的使用
-- 求班级最高分和最低分（数值范围在统计中特别有用）
SELECT MAX(math+english+chinese),MIN(math+english+chinese) FROM student;
-- 求出数学最高分，最低分
SELECT MAX(math) AS'数学最高分',MIN(math) AS'数学最低分' FROM student;

分组统计

CREATE TABLE dept (
 deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
 dname VARCHAR(20) NOT NULL DEFAULT "",
 loc VARCHAR(13) NOT NULL DEFAULT "");
 INSERT INTO dept VALUES
 (10,'ACCOUNTING','NEW YORK'),
 (20,'RESEARCH','DALLAS'),
 (30,'SALES','CHIVAGO'),
 (40,'OPERATIONS','BOSTON');
SELECT * FROM dept 
-- 员工表
CREATE TABLE `emp` (
  `empno` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `ename` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT "",
  `job` varchar(9) COLLATE utf8_bin NOT NULL DEFAULT "",
  `mgr` mediumint(8) unsigned DEFAULT NULL,/*上级编号*/
  `hiredate` date NOT NULL,/*入职时间*/
  `sal` decimal(7,2) NOT NULL,
  `comm` decimal(7,2) DEFAULT NULL,/*红利 奖金*/
  `deptno` mediumint(8) unsigned NOT NULL
) ;
-- 添加测试数据

INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1990-12-17',800.00,NULL,20),
    (7499,'ALLEN','SALESMAN',7698,'1991-2-20',1600.00,300.00,30),
    (7521,'WARD','SALESMAN',7968,'1991-2-22',1250.00,500.00,30),
    (7566,'JONES','MANAGER',7839,'1991-4-2',2975.00,NULL,20),
    (7654,'MARTIN','SALESMAN',7968,'1991-9-28',1250.00,1400.00,30),
    (7698,'BLAKE','MANAGER',7839,'1991-5-1',2850.00,NULL,30),
    (7782,'CLARK','MANAGER',7839,'1991-6-9',2450.00,NULL,10),
    (7788,'SCOTT','ANALYST',7566,'1991-4-19',3000.00,NULL,20),
    (7839,'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),
    (7844,'TURNER','SALESMAN',7698,'1991-9-8',1500.00,NULL,30),
    (7900,'JAMES','CLERK',7698,'1991-12-3',950.00,NULL,30),
    (7902,'FORD','ANALYST',7566,'1991-12-3',3000.00,NULL,20),
    (7934,'MILLER','CLERK',7782,'1991-1-23',1300.00,NULL,10);
SELECT * FROM emp 
-- 工资级别表
CREATE TABLE salgrade(
    grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*工资级别*/
    losal DECIMAL(17,2) NOT NULL,/*工资级别的最低工资*/
    hisal DECIMAL(17,2) NOT NULL/*工资级别的最高工资*/
);
INSERT INTO salgrade VALUES(1,700,1200),
    (2,1201,1400),
    (3,1401,2000),
    (4,2001,3000),
    (5,3001,9999);
SELECT * FROM salgrade;

# 演示GROUP BY + HAVING 使用
-- GROUP BY 用于对查询的结果分组统计
-- HAVING 子句用于限制分组显示结果
-- 如何显示每个部门的平均工资和最高工资
-- 按照部门分组查询
SELECT AVG(sal),MAX(sal),deptno FROM emp  GROUP BY deptno;

-- 显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal),MIN(sal),deptno,job FROM emp  GROUP BY deptno,job;
-- 显示平均工资低于2000 的部门号和它的平均工资 //别名
-- 1.显示各个部门的平均工资和部门号
SELECT AVG(sal),deptno FROM emp GROUP BY deptno;
-- 2.在1的结果基础上进行过滤，保留 AVG(sal)<2000；
-- 3.使用别名进行过滤
SELECT AVG(sal),deptno FROM emp GROUP BY deptno HAVING AVG(sal)<2000;
SELECT AVG(sal) AS avg_sal,deptno FROM emp GROUP BY deptno HAVING avg_sal<2000;

相关函数

1.字符串相关函数

# 演示字符串相关函数的使用 使用emp 表来演示
-- CHARSET(str) 返回字串字符集
SELECT CHARSET(Ename) FROM emp;
-- CONCAT(string2 [,...]) 连接字串
SELECT CONCAT(ename,'工作是',job) FROM emp;
-- INSTR(string，substring) 返回substring在string中返回的位置，没有返回0
SELECT INSTR('hanshunping','ping') FROM DUAL;
-- UCASE(string2) 转换成大写
SELECT UCASE(ename) FROM emp;
-- LCASE(string2) 转换成小写
SELECT LCASE(ename) FROM emp;
-- LEFT(string2,length) 从string2的左边起取length个字符
SELECT LEFT(ename,2) FROM emp;
-- LENGTH(string) string长度（按照字节）
SELECT LENGTH('hanshunping') FROM DUAL;
SELECT LENGTH('韩顺平') FROM DUAL;
-- REPLACE(str,search_str,replace_str) 在str中用replace_str 替换 search_str
-- 如果是manager就替换成经理
SELECT ename, REPLACE(job,'MANAGER','经理') FROM emp;
-- STRCMP(string1,string2) 逐字符比较两字串大小
SELECT STRCMP('hsp','jsp') FROM DUAL;
-- SUBSTRING(str,position[,length]) 从str的position开始【从1开始计算】取length字符
SELECT SUBSTR(ename,1,2) FROM emp;
-- LTRIM（string2）RTRIM(string2)trim 去除前端空格或者后端空格
SELECT LTRIM('  韩顺平教育')  FROM DUAL;
SELECT RTRIM('韩顺平教育          ')  FROM DUAL;
SELECT TRIM('  韩顺平教育   ')  FROM DUAL;
# 练习：以首字母小写的方式显示所有员工 emp表的姓名
-- 方法1：
-- 思路先取出ename的第一个字符，
-- 把它和后面的字符串进行拼接输出即可
SELECT CONCAT(LCASE(SUBSTR(ename,1,1)),SUBSTR(ename,2)) AS new_name FROM emp;
SELECT CONCAT(LCASE(LEFT(ename,1)),SUBSTR(ename,2)) AS new_name FROM emp;

2.数学相关函数

-- ABS（num） 绝对值
SELECT ABS(-10) FROM DUAL;
-- BIN(decimal_number) 十进制转二进制
SELECT BIN(10) FROM DUAL;
-- CEILING(number2) 向上取整，得到比num2大的最小整数
SELECT CEILING(1.1) FROM DUAL;
-- CONV(number2,from_base,to_base) 进制转换
SELECT CONV(8,10,2) FROM DUAL;
-- FLOOR(number2) 向下取整，得到比num2小的最大整数
SELECT FLOOR(1.1) FROM DUAL;
-- FORMAT(number,decimal_places) 保留小数位数
SELECT FORMAT(2.7134572,3) FROM DUAL;
-- HEX（decimal number） 转十六进制
SELECT HEX(10) FROM DUAL;
-- LEAST(number,number2 [,...]) 求最小值
SELECT LEAST(10,9,23) FROM DUAL;
-- MOD(numberator,denomintor) 求余
SELECT MOD(10,3) FROM DUAL;
-- RAND([seed]) RAND([seed]) 其范围为 0 <= v <=1.0
SELECT RAND(2) FROM DUAL;

3.时间日期相关相关函数

细节
● DATE ADD(date2,INTERVAR,d_value,d_type)中的intervar可以是year minute second day等
● DATE SUB(date2,INTERVAR,d_value,d_type)中的intervar可以是year minute second hour day等
● DATEDIFFY(date1,date2)得到的是天数，而且是date1-date2的天数，因此可以取负数
● 这四个函数的日期类型可以是date,datetime或者是 timestamp

-- CURRENT_DATE() 当前日期
SELECT CURRENT_DATE() FROM DUAL;
-- CURRENT_TIME() 当前时间
SELECT CURRENT_TIME() FROM DUAL;
-- CURRENT_TIMESTAMP 当前时间戳
SELECT CURRENT_TIMESTAMP() FROM DUAL;
SELECT NOW() FROM DUAL;
CREATE TABLE mes (
 id INT,
 content VARCHAR(30),
 send_time DATETIME);
   INSERT INTO mes VALUES(1,'北京新闻',CURRENT_TIMESTAMP());
  INSERT INTO mes VALUES(2,'上海新闻',NOW());
  INSERT INTO mes VALUES(3,'广州新闻',NOW());
 SELECT * FROM mes;
 -- 显示所有新闻信息，发布日期只显示日期，不显示时间
 -- 显示所有新闻信息，发布日期只显示日期，不显示时间
 SELECT DATE(send_time) FROM mes;
 -- 查询在十分钟内发布的新闻
 SELECT * FROM mes WHERE DATE_ADD(send_time,INTERVAL 10 MINUTE) > NOW();
  SELECT * FROM mes WHERE  send_time >= DATE_SUB(NOW(),INTERVAL 30 MINUTE) ;
  -- 求出2011-11-11和1990-1-1相差多少天
 SELECT DATEDIFF('2011-11-11','1990-1-1') FROM DUAL;
  SELECT DATEDIFF('2021-12-23','1996-7-12') FROM DUAL; 
 SELECT DATEDIFF(NOW(),'1996-7-12') FROM DUAL;
-- 如果你能活80岁，求出你还能活多少天？1986-11-11 出生
-- 先求出活80岁时，是什么日期
-- 然后再使用datediff(x,NOW())
 SELECT DATEDIFF(DATE_ADD('1966-11-11',INTERVAL 80 YEAR),NOW()) FROM DUAL;
 -- INTERVAL 80 YEAR  YEAR可以是年月日，时分秒
 -- '1966-11-11' 可以是date，datetime,TIMESTAMP
 SELECT TIMEDIFF('11:10:10','08:25:12') FROM DUAL;
 -- YEAR|MONTH|DAY|DATE (DATETIME)
 SELECT YEAR(NOW()) FROM DUAL;
 SELECT MONTH(NOW()) FROM DUAL;
 SELECT DAY(NOW()) FROM DUAL;
 SELECT DATE(NOW()) FROM DUAL;
 SELECT YEAR('2021-11-23') FROM DUAL;
 SELECT  MONTH('2021-11-23') FROM DUAL;
-- UNIX_TIMESTAMP(): 返回的是1970-1-1 到现在的毫秒数
 SELECT UNIX_TIMESTAMP() FROM DUAL;
-- FROM_UNIXTIME():可以把一个 UNIX_TIMESTAMP()[秒数]转换成指定格式的日期
-- %Y-&m-%d 格式表示年月日
-- 在开发中常用一个int保存unix时间戳，使用 FROM_UNIXTIME()进行转换
 SELECT FROM_UNIXTIME(1640225550,'%Y-%m-%d %H：%i：%s') FROM DUAL;

4.加密函数和系统函数

-- 演示加密函数和系统函数
-- USER() 查询用户
-- 可以查看登录到mysql的有哪些用户，以及登录的ip
SELECT USER() FROM DUAL;
-- DATABASE() 数据库名称
SELECT DATABASE();
-- MD5(str) 为字符串算出一个MD5 32的字符串，（用户密码）加密
-- root 密码是 hsp -> 加密MD5 -> 数据库中存放的是加密后的密码
SELECT MD5('hsp') FROM DUAL;
SELECT LENGTH(MD5('hsp')) FROM DUAL;

-- 演示用户表，存放密码时，是MD5
CREATE TABLE `hsp_user`(
 id INT,
 `name` VARCHAR(32) NOT NULL DEFAULT '',
 pwd VARCHAR(32) NOT NULL DEFAULT '');
INSERT INTO hsp_user VALUES(100,'韩顺平',MD5('hsp'));
SELECT * FROM hsp_user; -- csdn
SELECT * FROM hsp_user 
       WHERE `name` ='韩顺平' AND pwd =MD5('hsp'); -- SQL注入问题        
-- PASSWORD(str) 从原文密码str计算并返回密码字符串，通常用于对mysql数据库的用户密码加密
SELECT PASSWORD('hsp') FROM DUAL;
SELECT * FROM mysql.`user`

5.流程控制函数

-- # 演示流程控制语句
-- IF（expr1,expr2,expr3） 如果expr1为true，则返回expr2，否则返回expr3
SELECT IF(TRUE,'北京','上海')FROM DUAL;
-- IFNULL(expr1,expr2) 如果expr1不为null，则返回expr1，否则返回expr2
SELECT IFNULL(NULL,'韩顺平教育')FROM DUAL;
-- SELECT CASE WHEN expr1 THEN expr2
SELECT CASE 
    WHEN TRUE THEN 'jack'
    WHEN FALSE THEN 'tom'
    ELSE 'mary' END;  
-- WHEN expr3 THEN expr4 ELSE EXPR5 END;[类似多重分支] 
-- 如果expr1为true，则返回expr2，如果expr2为true，则返回expr4,否则返回expr5
-- 1.查询 emp 表，如果 comm 是null 则显示0.0 
-- 判断是否为null 用 is null 判断不为空 用is not
SELECT ename,IF(comm IS NULL,0.0,comm) FROM emp;
SELECT ename,IFNULL(comm ,0.0) FROM emp;
-- 如果 emp 表的 job 是 clerk 则显示职员，如果 是 manager 则显示经理，
-- 如果是salesman则显示销售人员，其他正常显示 
 SELECT ename,(SELECT CASE 
    WHEN job = 'CLERK' THEN '职员'
    WHEN job = 'MANAGER' THEN '经理'
    WHEN job = 'SALESMAN' THEN '销售人员'
    ELSE job END) AS job,job
    FROM emp;

查询增强

-- 查询加强
-- 使用where子句，如何查找1992-1-1 后入职的员工
-- 在mysql中日期类型可以直接比较，需要注意格式
SELECT * FROM emp WHERE hiredate > '1991-01-01';
-- 如何使用like操作符
-- %：表示0到多个字符，_:表示单个字符
-- 如何显示首字母为s的员工姓名和工资
SELECT ename,sal FROM emp WHERE ename LIKE 'S%';
-- 如何显示第三个字符为o的所有的员工姓名和工资
SELECT ename,sal FROM emp WHERE ename LIKE '__O%';
-- 如何显示没有上级的雇员的情况
SELECT * FROM emp WHERE mgr IS NULL;
DESC emp 
-- 使用order by子句
-- 如何按照工资的从低到高顺序【升序】，显示雇员的信息
SELECT * FROM emp ORDER BY sal;
-- 如何按照部门号【升序】而雇员的工资降序排序，显示雇员的信息
 SELECT * FROM emp ORDER BY deptno ASC,sal DESC;
  
  # 分页查询
 -- 按雇员的id 升序取出，每条显示3条记录，请分别显示第一页，第一页，第3页
 -- 第一页
 SELECT * FROM emp ORDER BY empno LIMIT 0,3;
  -- 第二页
 SELECT * FROM emp ORDER BY empno LIMIT 6,3;
  -- 第三页
 SELECT * FROM emp ORDER BY empno LIMIT 9,3;
 -- 推导一个公式
 -- SELECT * FROM emp 
 -- ORDER BY empno LIMIT 每页显示记录数*（第几页-1）,每页显示记录数;
  
  # 增强 GROUP BY 的使用
 -- （1）显示每种岗位的雇员总数、平均工资
 SELECT COUNT(*), AVG(sal),job FROM emp GROUP BY job;
 -- （2）显示雇员总数以及获得补助的雇员数
 SELECT COUNT(*),COUNT(IF(comm IS NULL,1,NULL)) FROM emp;
  SELECT COUNT(*),COUNT(*)-COUNT(comm) FROM emp;
 -- （3）显示管理者的总人数
 SELECT COUNT( DISTINCT mgr) FROM emp;
 -- （4）显示雇员工资的最大差额
 SELECT MAX(sal)-MIN(sal)  FROM emp;
  -- 统计各个部门 GROUP BY 平均工资 avg
-- 并且是大于1000 的 HAVING ,并且按照平均工资从高到低排序，ORDER BY
-- 取出前两行记录
SELECT deptno,AVG(sal) AS avg_sal FROM emp 
   GROUP BY deptno HAVING avg_sal > 1000  
   ORDER BY avg_sal
   DESC LIMIT 0,2;

● 数据分组的总结：
如果select 语句同时包含有group by，having,limit,order by,那么他们的顺序是 group by，having，order by,limit
多表查询

# 多表查询
-- 显示雇员名、雇员工资，以及所在部门的名字【笛卡尔积】
/*
  1.雇员名、雇员工资来自emp表
  2.部门的名字来自dept表
  3.需求对emp和dept表查询
  4.当我们需要指定显示某个表的列是，需要表.列表
 */
 SELECT ename,dname,sal,emp.deptno 
  FROM emp,dept
  WHERE emp.deptno = dept.deptno;
-- 如何显示部门号为10的部门名、员工名和工资
 SELECT ename,dname,sal,emp.deptno 
  FROM emp,dept
  WHERE emp.deptno = dept.deptno AND emp.deptno = 10;
-- 显示各个员工的姓名，工资，及其工资的级别
SELECT * FROM salgrade;
SELECT ename,sal FROM emp ORDER BY (SELECT * FROM salgrade);
-- 写sql先写一个简单的再过滤
SELECT ename,sal,grade
 FROM emp,salgrade
 WHERE sal BETWEEN losal AND hisal;
 -- 练习：显示雇员名、雇员工资及所在部门名字，并按部门降序排序
SELECT ename,sal,dname,emp.deptno
 FROM emp,dept
 ORDER BY emp.deptno DESC;

● 自连接
自连接是在同一张表中的连接查询【将同一张表看做两张表】

# 显示公司员工和他的上级的名字
-- 员工名字在emp，上级的名字在emp
-- 员工和上级是通过emp表的mgr列关联

-- 自连接的特点：1.把同一张表当做两张表使用
       -- 2.需要给表取别名 表名 表别名
       -- 2.列名不明确，可以指定列的别名，列名 AS 列的别名
SELECT woker.ename AS '职员名',boss.ename AS '上级名'
 FROM emp woker,emp boss
 WHERE woker.mgr = boss.empno;

● 表子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
● 单行子查询
单行子查询是指只返回一行数据的子查询语句
● 多行子查询
多行子查询是指返回多行数据的子查询语句，使用关键字 in
● 子查询临时表
ALL 和 ANY 的使用

-- ALL 和 ANY 的使用
-- 思考：显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
SELECT * FROM emp
    WHERE deptno = 30;
SELECT ename,sal,deptno
 FROM emp
 WHERE sal > ALL(
  SELECT sal FROM emp
    WHERE deptno = 30);    
    SELECT ename,sal,deptno
SELECT ename,sal,deptno 
 FROM emp
 WHERE sal > (
  SELECT MAX(sal) FROM emp
    WHERE deptno = 30);    
-- 显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号
SELECT ename,sal,deptno
 FROM emp
 WHERE sal > ANY(
  SELECT sal FROM emp
    WHERE deptno = 30);
SELECT ename,sal,deptno
 FROM emp
 WHERE sal > ANY(
  SELECT MIN(sal) FROM emp
    WHERE deptno = 30);

多列子查询

# 多列子查询
-- 思考：如何查询与ALLEN部门和岗位完全相同的所有雇员（并且不含ALLEN本人）
-- （字段 1，字段 2 ...） =（ SELECT 字段 1，字段 2 FROM...）
-- 分析：1.得到 ALLEN的部门和岗位
SELECT deptno,job FROM emp WHERE ename = 'ALLEN';
SELECT * FROM emp
 WHERE(deptno,job)=(
  SELECT deptno,job 
  FROM emp 
  WHERE ename = 'ALLEN'
 )AND ename != 'ALLEN';
# 查询与宋江 数学、英语、语文成绩相同的同学
SELECT chinese,english,math FROM student
 WHERE `name` = '宋江'; 
SELECT * FROM student
  WHERE (chinese,english,math)=(
   SELECT chinese,english,math 
   FROM student
   WHERE `name` = '宋江'
  );
-- 练习
-- 查找每个部门工资高于本部门平均工资的人的资料
SELECT deptno,AVG(sal) AS avg_sal 
 FROM emp
 GROUP BY deptno
SELECT ename,sal,temp.avg_sal,emp.deptno
 FROM emp,(SELECT deptno,AVG(sal) 
      AS avg_sal 
      FROM emp
      GROUP BY deptno 
) temp 
WHERE emp.deptno = temp.deptno AND emp.sal > temp.avg_sal;
-- 查找每个部门工资最高的的人的资料
SELECT deptno,MAX(sal) AS max_sal 
 FROM emp
 GROUP BY deptno
SELECT ename,sal,temp.max_sal,emp.deptno
 FROM emp,(SELECT deptno,MAX(sal) AS max_sal 
      FROM emp
 GROUP BY deptno) temp 
 WHERE emp.deptno = temp.deptno AND emp.sal = temp.max_sal; 
 # 查询每个部门的信息（包括：部门名，编号，地址）和人员数量
 -- 思路：1.先将人员信息与部门信息关联
       -- 2.然后统计
 SELECT * FROM emp
 SELECT deptno,count(empno) FROM emp 
    GROUP BY deptno    
SELECT dept.deptno,dname,loc,temp.num
 FROM dept,(SELECT deptno, count(*) AS num
          FROM emp 
          GROUP BY deptno) temp
  WHERE dept.deptno = temp.deptno;
-- 还有一种写法 表.* 表示将该表所有列都显示出来
-- 在多表查询中，当多个表的列不重复时，才可以直接写列名 
 SELECT dept.deptno,dname,loc,temp.*
 FROM dept,(SELECT deptno, count(*) AS per_num
          FROM emp 
          GROUP BY deptno) temp
  WHERE dept.deptno = temp.deptno;

演示表复制

 # 演示表复制
-- 为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此方法为表创建海量数据
CREATE TABLE my_tab01(
 id INT,
 `name` VARCHAR(32),
 sal DOUBLE,
 job VARCHAR(32),
 deptno INT);
DESC my_tab01
SELECT * FROM my_tab01
-- 1.把emp表的记录复制到my_tab01
INSERT INTO my_tab01(
id,`name`,sal,job,deptno
)SELECT empno,ename,sal,job,deptno FROM emp;
-- 2.自我复制
INSERT INTO my_tab01
  SELECT * FROM my_tab01
# 如何删除一张表重复记录
-- 1.先创建一张表重复记录my_tab02
-- 2.让my_tab02有重复记录
 CREATE TABLE my_tab02 LIKE emp;  -- 这个语句把emp表的结构（列）复制到my_tab02
 DESC my_tab02 
 INSERT INTO my_tab02
      SELECT * FROM emp;    
 SELECT * FROM my_tab02 
 -- 考虑去重 my_tab02 的记录
 /*
  思路：
  （1）先创建一张临时表tmp,该表的结构和my_tab02 一样
  （2）把my_tmp记录通过 DISTINCT 处理后，把记录复制到my_tmp
   (3)清除my_tab02的记录
   (4)把 my_tmp的记录复制到my_tab02
   (5)DROP 掉临时表 my_tmp 
 */
-- （1）先创建一张临时表tmp,该表的结构和my_tab02 一样
CREATE TABLE my_tmp LIKE my_tab02;
-- （2）把my_tmp记录通过 DISTINCT 处理后，把记录复制到my_tmp
INSERT INTO my_tmp
     SELECT DISTINCT * FROM my_tab02;
-- (3)清除my_tab02的记录
DELETE FROM my_tab02;
-- 把 my_tmp的记录复制到my_tab02
INSERT INTO my_tab02 
     SELECT * FROM my_tmp;    
-- (5)DROP 掉临时表 my_tmp
DROP TABLE my_tmp;  
SELECT * FROM my_tab02; 

合并查询

 # 演示合并查询
 SELECT ename,sal,job FROM emp WHERE sal > 2500
 UNION ALL
 SELECT ename,sal,job FROM emp WHERE job = 'MANAGER';

外连接

# 外连接
-- 比如：列出部门名称和这些部门的员工名称和工作
-- 同时要求 显 示出哪些没有员工的部门
-- 使用我们学习过的多表查询，看看效果
SELECT ename,dname,job FROM emp,dept
   WHERE emp.deptno = dept.deptno
   ORDER BY dname;
-- 创建stu
 CREATE TABLE stu (
 id INT,
 `name` VARCHAR(32)
 ); 
INSERT INTO stu VALUES(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');  
-- 创建 exam 
 CREATE TABLE exam (
 id INT,
 grade INT
 );
INSERT INTO exam VALUES(1,56),(2,76),(11,8); 
-- 使用左连接
-- 显示所有人的成绩，如果没有成绩也要显示该人的姓名和id号，成绩显示为空
SELECT `name`,stu.id,grade 
   FROM stu,exam
   WHERE stu.id = exam.id;
-- 改成左外连接
SELECT `name`,stu.id,grade
   FROM stu LEFT JOIN exam
   ON stu.id = exam.id;
-- 使用右外连接（显示所有成绩，如果没有名字匹配，显示空）
-- 即右表 exam 和左表没有匹配的记录，也会把右表的记录显示出来
 SELECT `name`,stu.id,grade
   FROM stu RIGHT JOIN exam
   ON stu.id = exam.id;     
-- 列出部门名称和这些部门的员工信息（名字和工作）同时列出哪些没有员工的部门名
 SELECT dname,ename,job
  FROM emp RIGHT JOIN dept
  ON emp.deptno = dept.deptno; 
 SELECT dname,ename,job
  FROM dept LEFT JOIN emp
  ON emp.deptno = dept.deptno;

主键使用

CREATE TABLE t17 (
id INT PRIMARY KEY,
`name` VARCHAR(32),
email VARCHAR(32));
INSERT INTO t17 VALUES(1,'jack','jack@sohu.com');
INSERT INTO t17 VALUES(2,'tom','tom@sohu.com');
INSERT INTO t17 VALUES(1,'hsp','hsp@sohu.com');  
SELECT * FROM t17 
-- 主键使用的细节讨论
-- 主键不能重复且不能为null
INSERT INTO t17 VALUES(NULL,'jack','jack@sohu.com');
-- 一张表最多只能有一个主键，但可以是复合主键（比如 id,NAME）
CREATE TABLE t17 (
id INT PRIMARY KEY,
`name` VARCHAR(32)  PRIMARY KEY, -- 错误
email VARCHAR(32));  
CREATE TABLE t18 (
id INT,
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY(id ,`name`)); -- 这里指定复合主键
INSERT INTO t18 VALUES(1,'jack','jack@sohu.com');
INSERT INTO t18 VALUES(1,'tom','jack@sohu.com');
INSERT INTO t18  VALUES(1,'jack','jack@sohu.com'); -- 违反了复合主键
SELECT * FROM t18 
-- 主键的指定方式有两种
-- 1.直接在字段名后指定 字段名 PRIMARY KEY
-- 2.在表定义最后写 PRIMARY KEY(列名)
CREATE TABLE t19 (
id INT,
`name` VARCHAR(32) PRIMARY KEY,
email VARCHAR(32));
CREATE TABLE t20 (
id INT,
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY(`name`)); -- 在表定义最后写 PRIMARY KEY(列名)
DESC t20 -- 查看t20表结构会显示主键情况
-- 实际开发中，每个表往往都会设计一个主键

unique 使用

-- UNIQUE 的使用
CREATE TABLE t21 (
id INT UNIQUE, -- 表示id列是不可以重复的
`name` VARCHAR(32),
email VARCHAR(32));
INSERT INTO t21 VALUES(1,'jack','jack@sohu.com');
INSERT INTO t21 VALUES(1,'tom','jack@sohu.com');
-- UNIQUE 使用细节
-- 如果没有指定 NOT null,则 UNIQUE 可以有多个null
-- 如果一个列（字段）是unique not null 使用效果类似 PRIMARY KEY
INSERT INTO t21 VALUES(NULL,'tom','jack@sohu.com');
-- 一张表可以有多个unique字段
CREATE TABLE t22 (
id INT UNIQUE, -- 表示id列是不可以重复的
`name` VARCHAR(32) UNIQUE,-- 表示name 列是不可以重复的
email VARCHAR(32));

外键使用

-- 外键演示
-- 创建 主表 my_calss
CREATE TABLE my_class(
 id INT PRIMARY KEY,
 `name` VARCHAR(32) NOT NULL DEFAULT ''
);
-- 创建从表 my_stu
CREATE TABLE my_stu(
 id INT PRIMARY KEY, -- 学生编号
 `name` VARCHAR(32) NOT NULL DEFAULT '',
 class_id INT, -- 学生所在班级的编号
 -- 指定外键关系
 FOREIGN KEY (class_id) REFERENCES my_class(id)
);
-- 测试数据
INSERT into my_class VALUES(100,'java'),(200,'web');
INSERT into my_class VALUES(300,'php');
INSERT into my_stu VALUES(1,'tom',100);
INSERT into my_stu VALUES(2,'jack',100);
INSERT into my_stu VALUES(3,'hsp',300);
INSERT into my_stu VALUES(4,'mary',400); -- 这里会失败... 因为400 班级不存在
INSERT into my_stu VALUES(5,'mary',NULL); -- 可以 外键没有写 NOT NULL
-- 一旦建立主外键关系，数据不能随意删除了
DELETE TABLE my_class
    WHERE id = 100; -- 违反外键约束
SELECT * FROM my_class 
SELECT * FROM my_stu

细节：
a.外键指向的字段，要求是primary key 或者是unique
b.表的类型是innodb，这样的表才支持外键
c.外键字段的类型要和主键字段的类型一致（长度可以不同）
d.外键字段的值必须在主键字段出现过，或者为null【前提是外键字段允许为null】
e.一旦建立主外键关系，数据不能随意删除了。
check的使用

# 演示check的使用
-- mysql 5.7目前还不支持check，只做语法校验，但不会生效
-- 测试
CREATE TABLE t23(
id INT PRIMARY KEY,
`name` VARCHAR(32),
sex VARCHAR(6) CHECK (sex IN('man','woman')),
sal DOUBLE CHECK (sal > 1000 AND sal < 2000));
INSERT INTO t23 VALUES (1,'jack','mid',1)
SELECT * FROM t23

商店表设计

-- 现有一个商店的数据库 shop_db,记录客户及其购物情况，由下面三个表组成
-- 商品goods（商品号goods_id,商品名goods_name，单价unitprice，商品类别 category,供应商provider）
-- 客户coustomer(客户号coustomer_id,姓名name，住址address，电邮email，性别sex 身份证card_id)
-- 购买purchase（购买订单号，客户号customer_id，商品号goods_id，购买数量nums）
-- 1，建表 在定义中要求声明【进行合理设计】
-- （1）每个表的主外键
-- （1）客户的姓名不能为空值
-- （1）电邮不能重复
-- （1）客户的性别[男|女] 枚举
-- （1）单价unitprice在1.0-9999.99之间 CHECK 
CREATE TABLE goods (
goods_id INT PRIMARY KEY,
goods_name VARCHAR(32) NOT NULL DEFAULT '',
unitprice DECIMAL(10,2) NOT NULL DEFAULT 0 CHECK (unitprice >= 1.0 AND unitprice <= 9999.99),
category INT NOT NULL DEFAULT 0,
provider VARCHAR(64) NOT NULL DEFAULT ''
);
CREATE TABLE coustomer(
coustomer_id CHAR(8) PRIMARY KEY,
`name` VARCHAR(64) NOT NULL DEFAULT '',
address VARCHAR(64) NOT NULL DEFAULT '',
email VARCHAR(64) UNIQUE NOT NULL,
sex enum('男','女') NOT NULL,
card_id CHAR(18)
);
CREATE TABLE purchase(
order_id INT UNSIGNED NOT NULL PRIMARY KEY,
customer_id CHAR(8) NOT NULL DEFAULT '',
goods_id INT NOT NULL DEFAULT 0,
FOREIGN KEY (customer_id)  REFERENCES coustomer(coustomer_id), 
FOREIGN KEY (goods_id)  REFERENCES goods(goods_id),
num INT);
DESC goods 
DESC coustomer
DESC purchase

自增长的使用

-- 演示自增长的使用
-- 创建表
CREATE TABLE t24(
 id INT PRIMARY KEY AUTO_INCREMENT,
 email VARCHAR(32) NOT NULL DEFAULT '',
 `name` VARCHAR(32) NOT NULL DEFAULT ''
 );
DESC t24
INSERT INTO t24 VALUES(NULL,'jack@qq.com','jack')
INSERT INTO t24(email,`name`) VALUES('tom@qq.com','tom')
INSERT INTO t24(email,`name`) VALUES('hsp@qq.com','hsp')
SELECT * FROM t24 
-- 修改默认的自增长开始值
CREATE TABLE t25(
 id INT PRIMARY KEY AUTO_INCREMENT,
 email VARCHAR(32) NOT NULL DEFAULT '',
 `name` VARCHAR(32) NOT NULL DEFAULT ''
 );
ALTER TABLE t25 AUTO_INCREMENT = 100;
INSERT INTO t25 VALUES(NULL,'jack@qq.com','jack')
SELECT * FROM t25

自增长使用细节
a.一般来说自增长是配合primary key配合使用
b.自增长也可以单独使用【但是需要配合一个unique】
c.自增长修饰的类型是整数型的（虽然小数也可以但是非常少这样使用）
d.自增长默认是从1开始，你可以通过如下命令修改 alter table 表名 auto_increment = 新的开始值
e.如果你添加数据时，给自增长字段（列）指定的有值，则以指定的值为准

索引

# 演示mysql的索引的作用，
-- 创建索引
CREATE TABLE t25(
 id INT,
 `name` VARCHAR(32)
 );
-- 查询表是否有索引
SHOW INDEX FROM t25;
-- 添加普通索引方式1
CREATE  INDEX id_index  ON t25(id);
-- 添加唯一索引
CREATE UNIQUE INDEX id_index  ON t25(id);
-- 如何选择
-- 1.如果某列的值，是不会重复的，则优先考虑使用unique索引，否则使用普通索引
-- 添加普通索引方式2
ALTER TABLE t25 ADD INDEX id_index (id);
-- 主键索引
CREATE TABLE t26(
 id INT,
 `name` VARCHAR(32)
 );
ALTER TABLE t26 ADD PRIMARY KEY (id);

SHOW INDEX FROM t26;
-- 删除索引
 DROP INDEX id_index ON t25 ;
-- 删除主键索引
 ALTER TABLE t26 DROP PRIMARY KEY;
-- 查询索引
-- 1.方式
SHOW INDEX FROM t25;
-- 2.方式
SHOW INDEXES  FROM t25;
-- 3.方式
SHOW kEYS  FROM t25;
-- 4.方式
DESC t25
-- 哪些列上适合使用索引
-- 1.较频繁的作为查询条件字段应该创建索引
-- 2.唯一性太差的的字段不适合单独创建索引，即使频繁作为查询条件
-- 3。更新非常平凡的字段不适合创建索引
-- 4.不会出现在where子句中字段不该创建索引

事务

-- 事务的具体操作
-- 1.创建一张测试表
DROP TABLE t27
CREATE TABLE t27 (
id  INT,
`name` VARCHAR(32)
);
-- 2. 开始事务
START TRANSACTION;
-- 3.设置保存点
SAVEPOINT a;
-- 执行到dml操作
INSERT INTO t27 VALUES(100,'tom')
-- 3.设置保存点
SAVEPOINT b;
INSERT INTO t27 VALUES(200,'jack')
 SELECT * FROM t27 
 -- 回退到b
 ROLLBACK TO b
 -- 继续回退 a
  ROLLBACK TO a
 
-- 如果这样
ROLLBACK 
COMMIT
-- 回退事务
-- 在介绍回退事务前，先介绍一下保存点（SAVEPOINT），保存点是事务中的点，
-- 用于取消部分事务，当结束事务（COMMIT）时，会自动删除事务所定义的所有保存点，
-- 当执行回退事务事，通过指定保存点可以回退到指定的点
-- 提交事务
-- 使用commit 语句可以提交事务，当执行了commit语句后，会确认事务的变化、结束事务、删除保存点、释放锁、数据生效
-- 当使用commit语句结束字事务，其他会话将可以查看到事务变化后的新数据，
NSERT INTO t27 VALUES(300,'milan'); -- 自动提交
SELECT * FROM t27;

ROLLBACK 
-- 2.如果开始一个事务，你没有创建保存点，你可以执行 ROLLBACK,
-- 默认就是回退到你事务开始的状态
 START TRANSACTION 
 INSERT INTO t27 VALUES(400,'king'); 
 INSERT INTO t27 VALUES(500,'scott'); 
 ROLLBACK -- 直接回退到事务开始的状态
 -- 3.你可以在这个中（还没有提交时），创建多个保存点，比如：SAVEPOINT
 -- 执行 dml，SAVEPOINT, bbb
 -- 4.你可以在事务没有提交前选择回退到哪个保存点
 -- 5.INNODB 支持事务，myisam不支持
 
  -- 1.自动提交, 不能回滚
INSERT INTO t27 VALUES(300,'milan');
SELECT * FROM t27;
ROLLBACK 
COMMIT
-- 2.如果开始一个事务，你没有创建保存点，你可以执行 ROLLBACK,
-- 默认就是回退到你事务开始的状态
 START TRANSACTION 
 INSERT INTO t27 VALUES(400,'king'); 
 INSERT INTO t27 VALUES(500,'scott'); 
 ROLLBACK -- 直接回退到事务开始的状态
 -- 3.你可以在这个中（还没有提交时），创建多个保存点，比如：SAVEPOINT
 -- 执行 dml，SAVEPOINT, bbb
 -- 4.你可以在事务没有提交前选择回退到哪个保存点
 -- 5.INNODB 支持事务，myisam不支持
 -- 6.开始一个事务 START TRANSACTION,SET autocommit = off
 SET autocommit = off;

天赋如同自然花木，要用学习来修剪!