MySQL学习笔记

一、MySQL产品的介绍和安装

1、MySQL服务的启动与停止

方式一：Windows服务管理；
方式二：通过管理员运行cmd
        net start 服务名       （启动服务）
        net stop  服务名       （停止服务）

2、MySQL服务的登录和退出

方式一：通过MySQL自带的客户端（只限root）；
方式二：通过cmd命令行登录
  mysql [-h主机名 -P端口] -u用户名 -p密码
进入后提示符为 mysql>
退出
  exit或ctrl+c  

3、MySQL的常见命令

1.查看当前所有的数据库
  show database;
2.打开指定的库
    use 库名;
3.查看当前库的所有表
    show tables;
4.查看其它库的所有表
    show tables from 库名;
5.创建表
    create table 表名(
        列名 列类型,
        列名 列类型,
      ...
  );
6.查看表结构
    desc 表名;
7.查看服务器的版本
    方式一：mysql提示符下输入
        SELECT version();
    方式二：命令提示符下
        mysql -- version
        或
        mysql -V
        #Linux下显示的内容：
        /*
        [root@localhost ~]# mysql --version
        mysql  Ver 14.14 Distrib 5.6.50, for Linux (x86_64) using  EditLine wrapper
        Windows下显示的内容：
        C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -V
        mysql  Ver 14.14 Distrib 5.6.50, for Win64 (x86_64)
        */

4、Mysql语法规范

MySQL的语法规范
1.不区分大小写，但建议关键字大写，表名、列名小写；
2.每条命令最好用分号结束；
3.每条命令根据需要，可以进行缩进或换行
    #示例：
    SELECT
    姓名,性别,民族,二级学院名
    FROM
    students
    WHERE 民族 <> '汉族';   
4.注释
        单行注释：#注释文字
        单行注释：-- 注释文字
        多行注释：/* 注释文字 */

二、DML语言的学习

工作原理

一：MySQL查询

#单表查询
根据where条件过滤表中的记录，每过滤一次形成一张中间表（中间表对用户是不可见的）。然后根据select的选择列返回最终的结果。

#两张表连接查询
对两表求积（笛卡尔积：行相乘，列相加）并用on条件和连接类型进行过滤形成中间表，然后根据where条件过滤中间表的记录，并且根据select指定的列返回查询结果。

#多表连接查询
先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据where条件过滤中间表的记录，并根据select指定的列返回查询结果。

二：MySQL查询语法顺序

1：  select
2：  from
3：  left … join
4：  on
5：  where
6：  group by
7：  having
8：  order by
9：  limit

三：MySQL执行顺序

#示例：
    select * 
    from students 
    left join course c 
    on s.id = c.student_id 
    where s.id >10 
    group by s.id 
    having count(1) > 3 
    order by s.id desc 
    limit 0,100
#执行顺序：
1：form(将最近的两张表进行笛卡尔积) 形成中间表 … VT1
2：on (将VT1按照它的条件进行过滤) …VT2
3：left join (保留左表的记录) …VT3
4：where (过滤VT3中的记录,没过滤一次形成一张中间表) VT4 … VTn
5：group by (对VT4的记录进行分组) …VT5
6：having (对VT5中的记录进行过滤) …VT6
7：select (对VT6中的记录选取指定的列) …VT7
8：order by (对VT7的记录进行排序) …游标
9：limit(对排序后的值进行分页)
#注意：
where 条件执行顺序（影响性能）
1：MySQL从左向右去执行where条件
2：Oracle从右向左去执行where条件
所以，写where条件的时候，优先级高的部分要去编写过滤力度最大的条件语句。

1、基础 查询

# 基础查询
/*
语法：
SELECT 查询列表 from 表名;
特点：
1、查询列表可以是：表中的字段、常量、表达式、函数
2、查询的结果是一个虚拟的表格
*/
#1.查询表中的单个字段
SELECT last_name FROM employees;
#2.查询表中的多个字段
SELECT first_name,last_name FROM employees;
#3.查询表中的所有字段
方式一：
SELECT `emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date` FROM employees;
方式二：
SELECT * FROM employees;
注意：字段名和关键字相同时加着重号 ``    例如：SELECT `NAME` FROM employees;
#4.查询常量值
SELECT 100;
SELECT 'HongHong';  字符串加单引号
#5.查询表达式
SELECT 100%98;
SELECT 100*99;
#6.查询函数
SELECT version();
SELECT CONCAT(last_name,".",first_name) 姓名 FROM employees;
#7.起别名
方式一：使用 AS
SELECT 100%98 as 结果;
方式二：使用空格
SELECT last_name 姓,first_name 名 FROM employees;
#8.去重
案例：查询部门员工表中涉及到的所有部门编号
SELECT DISTINCT dept_no FROM dept_emp;
#9.+号的作用
MySQL中的+号：
仅仅只有一个功能：运算符
SELECT 100+90;两个操作数都为数值型，则做加法运算
SELECT '123'+90;只要其中一方为字符型，试图将字符型数值转换成数值型，如果转换成功，则继续做加法运算；
SELECT 'john'+99;如果转换失败，则字符型数值转换成0
SELECT null+100;    只要其中一方为null，则结果肯定为null

2、条件查询

#进阶2：条件查询
/*
语法：
        select 
                    查询列表
        from
                    表名
        where
                    筛选条件;
分类：
        ①、按条件表达式筛选
*/
分类：
        一、按条件表达式筛选
            简单条件运算符：
           >    <   = != <> >= <=
        二、按逻辑表达式筛选
            逻辑运算符：
                and 两个条件都为true,结果为true,反之为false 
                or  只要有一个条件为true，结果为true，反之为false
                not 如果连接的条件本身为false，结果为true，反之为false
        三、模糊查询
                like
                between and
                in 
                is null |  is not null

一、按条件表达式筛选
#案例1：查询工资大于150000的员工信息
select * from employees where salary >150000;
#案例2：查询部门编号不等于90的员工名和部门编号
select * last_name,dept_no from employees where dept_no <>90;
二、按逻辑表达式筛选
#案例1：查询工资在10000到20000之间的员工名、工资以及奖金
select 
            last_name,
            salary,
            commission_pct
from
            employees
where
            salary>=10000 and salary <=20000;
#案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
三、模糊查询
1. like 特点：
①一般和通配符搭配使用
            通配符：
            %   任意多个字符，包含0个字符
            _ 任意单个字符
#案例1：查询员工名中包含字符a的员工信息
select * from employees where last_name like '%a%';
#案例2：查询员工名中第三个字符为e，第五个字符为a的员工信息
select * from employees where last_name like '__e_a%';
#案例3：查询员工名中第二个字符为_的员工名
select last_name from employees where last_name like '_\_%';#用\转义一下
select last_name from employees where last_name like '_$_%' escape '$'; #escape 转义
2、 between and
/*
①使用between and提高语句的简洁度
②包含临界值
③两个临界值不能调换顺序
*/
#案例1：查询员工编号在100到120之间的员工信息
select * from employees where employee_id >=100 and employee_id <=120;
-----------------
select * from employees where employee_id between 100 and 120;
3、 in
/*
含义：判断某字段的值是否属于in列表中的某一项
特点：
        ①使用in提高语句的简洁度
        ②in列表的值类型必须一致或兼容
        ③不支持通配符
*/
#案例：查询员工的工种编号是ad_vp、it_prog、FR_MGR中的一个的员工名和工种编号
select last_name，job_id from employees where job_id = 'ad_vp' or job_id = 'it_prog' or job_id = 'FR_MGR';
-----------------
select last_name，job_id from employees where in ('ad_vp','it_prog','FR_MGR');
4、 is null/ is not null
/*
=或<>不能用于判读null值
is null或is not null 可以判断null值
*/
#案例1：查询没有奖金的员工名和奖金率
select last_name,commission_pct from employees where commission_pct is null;
#案例2：查询有奖金的员工名和奖金率
select last_name,commission_pct from employees where commission_pct is not null;
5、安全等于 <=>
#案例1：查询没有奖金的员工名和奖金率
select last_name,commission_pct from employees where commission_pct <=> null;

#案例
#查询在职员工人数
SELECT COUNT(*) FROM dept_emp WHERE to_date = '9999-01-01'; 
SELECT COUNT(*) FROM salaries WHERE to_date = '9999-01-01';
SELECT COUNT(*) FROM titles WHERE to_date = '9999-01-01';               240124人

2.1 MySQL LIKE 子句

以下是 SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法：

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

• 你可以在 WHERE 子句中指定任何条件。
• 你可以在 WHERE 子句中使用LIKE子句。
• 你可以使用LIKE子句代替等号 =。
• LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
• 你可以使用 AND 或者 OR 指定一个或多个条件。
• 你可以在 DELETE 或 UPDATE 命令中使用 WHERE...LIKE 子句来指定条件。

2.2 MySQL WHERE 子句

语法

以下是 SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法：

SELECT field1, field2,...fieldN 
FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

• 查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。
• 你可以在 WHERE 子句中指定任何条件。
• 你可以使用 AND 或者 OR 指定一个或多个条件。
• WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
• WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

以下为操作符列表，可用于 WHERE 子句中。

下表中实例假定 A 为 10, B 为 20

3、排序查询

#进阶3：排序查询
/*
引入
        select * from employees； #排序
语法：
            select 查询列表
            from 表
            【where 筛选条件】
            order by 排序列表【asc|desc】
特点：
            1、asc代表升序，desc代表的是降序，如果不写，默认为升序
            2、order by子句中可以支持单个字段、多个字段、表达式、函数、别名
            3、order by一般放在查询语句的最后面，limit子句除外
*/
#案例1：查询员工信息，要求按工资从高到低排序
select * from employees order by salary desc;
#案例2：查询部门编号>=90的员工信息，按入职时间的先后进行排序【添加筛选条件】
select * from employees where department_id >=90 order by hiredate asc;
#案例3：按年薪的高低显示员工的信息和年薪【按表达式排序】
select *,salary*2*(1+ifnull(commission_pct,0)) as 年薪
from    employees
order by salary*2*(1+ifnull(commission_pct,0)) desc；
#案例4：按年薪的高低显示员工的信息和年薪【按别名排序】
select *,salary*2*(1+ifnull(commission_pct,0)) as 年薪
from    employees
order by 年薪 desc；  
#案例5：按姓名的长度显示员工的姓名和工资【按函数排序】
select length(last_name) 字节长度,last_name,salary
from employees
order by length(last_name) desc;
#案例6：查询员工信息，要求先按工资排序，再按员工编号排序【按多个字段排序】
select * 
from employees
order by salary asc,employee_id desc;

4、常见函数

#进阶4：常见函数
/*
概念：类似java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节 2、提高代码的重要性
调用：select 函数（实参列表） 【from 表】;
特点：
        ● 叫什么（函数名）
        ● 干什么（函数功能）
分类：
        1、单行函数
        ● 字符函数
        ● 数学函数
        ● 日期函数
        ● 其他函数【补充】
        ● 流程控制函数【补充】
        2、分组函数
        功能：做统计使用，又称为统计函数、聚合函数、组函数
*/

一、字符函数

1. length（str）字符串长度
select length('john');
select length('Hello！Mr.张');
show variables like '%char%' #中文字节的长度与字符集有关
2. concat 拼接字符串
select concat(last_name,'_',fist_name) 姓名 from employees;
3. upper、lower
select upper('john');
select lower('Rose');
#示例：将姓变大写，名变小写，然后拼接
select concat(upper(last_name),lower(first_name)) 姓名 from employees;
4. substr、substring
select substr('元芳你怎么看？',3) as 结果;
#截取从指定索引处后面所有字符
select substr('李莫愁爱上了陆展元',1,3) as 结果;
#截取从指定索引处指定字符长度的字符
#示例：姓名中首字母大写，其他字符小写然后用_拼接，显示出来
select concat(upper(sbustr(last_name,1,1)),'_',lower(substr(last_name,2))) 姓 from employees；
5. instr 返回字符串第一次出现的索引，如果找不到返回0
select instr('我是菜鸟，我爱学习','我爱') as out_put；
6. trim 掐头去尾
select trim('    去除前后空格    ') as out_put;
select trim('a' from 'aaaaa去除前后字符aaaaaaaaaaaaa') as out_put;
7. lpad 用指定的字符实现左填充指定长度
select lpad('我是菜鸟',10,'*') as out_put;
8. rpad 用指定的字符实现左填充指定长度
select rpad('我是菜鸟',12,'ab') as out_put;
select rpad('我是菜鸟',2,'0') as out_put;
9. replace 替换
select replace('我是菜鸟，我爱学习','菜鸟','新手') as 结果;
select replace('我是菜鸟，我爱学习','我','你') as 结果;

二、数学函数

1. round 四舍五入
select round(1.65);
select round(-1.45);
select round(1.567,2);
2. ceil 向上取整,返回>=该参数的最小整数
select ceil(1.52);
select ceil(1.000001);
select ceil(-1.02);
3. floor 向下取整，返回<=该参数的最大整数
select floor(9.99);
select floor(-9.99);
4. truncate 截断
select truncate(1.69999,1);
5. mod 取余
mod(a,b) : a-a/b*b
mod(-10,-3)
mod(10,-3) 
select mod(10,3);
6. cast 十六进制转十进制
SELECT CAST(0xC2A0 AS UNSIGNED);

三、日期函数

1. now 返回当前系统日期+时间
select now();
2. curdate 返回当前系统日期，不包含时间
select curdat();
3. curtime 返回当前系统时间，不包含日期
select curtime();
4. year 获取指定部分的年、月、日、小时、分钟、秒
select year(now()) 年;
select year('1998-1-1') 年;
select year(hiredate) 年 from employees;
select month(now()) 月;
select monthname(now()) 月;
5. str_to_date 将字符转换成日期
select str_to_date('2020-3-3','%Y-%c-%d') as out_put;
#案例：查询入职日期为1992-4-3的员工信息
select * from employees where hiredate ='1992-4-3';
select * from employees where hiredate = str_to_date ('4-3 1992','%c-%d %Y');
6. date_format将日期转换成字符
select date_format(now(),'%Y年%m月%d日') as out_put;
select date_format('2021/9/3','%Y年%m月%d日') as out_put;
#案例：查询有奖金的员工名和入职日期（xx月/xx日 xxxx年）
select last_name date_format(hiredat,'%m月/%d日 %Y年') 入职日期
from employees
where commission_pct is not null;

日期格式符对照表：

5、分组函数

一、聚合函数（aggregation function）---也就是组函数

在一个行的集合（一组行）上进行操作，对每个组给一个结果。

常用的组函数：

①每个组函数接收一个参数

②默认情况下，组函数忽略列值为null的行，不参与计算

③有时，会使用关键字distinct剔除字段值重复的条数

注意：

1）当使用组函数的select语句中没有group by子句时，中间结果集中的所有行自动形成一组，然后计算组函数；

2）组函数不允许嵌套，例如：count(max(…))；

3）组函数的参数可以是列或是函数表达式；

4）一个SELECT子句中可出现多个聚集函数。

min()

max()

count()

6、分组查询

//语法：
            SELECT 分组函数，列（要求出现在group by的后面）
            from 表
            [where 筛选语句]
            group by  分组的列表 #支持字段、多字段、表达式或函数
            [having ]
            [order by 子句]
注意：
            查询列表必须特殊，要求是分组函数和group by后出现的字段
特点：
            1、分组查询中的筛选条件分为两类
                                     数据源                        位置                              关键字
            分组前筛选           原始表                     group by 子句的前面      where
            分组后筛选           分组后的结果集         group by 子句的后面      having
1.按字段名分组
#查询全名重名的员工全名和人数
方法一：
SELECT CONCAT(first_name,'.',last_name) AS quanming,COUNT(*) AS 人数
FROM employees
GROUP BY quanming
HAVING 人数>1
ORDER BY 人数 DESC
LIMIT 100;
方法二：
SELECT first_name,last_name,COUNT(*)
FROM employees
GROUP BY first_name,last_name
HAVING COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 100;
#查询每个职位的人数
SELECT title,COUNT(*)
FROM titles
GROUP BY title
ORDER BY COUNT(*) DESC
LIMIT 100;
#查询调换过部门的员工编号和次数  通过查询可以看到没有超过调换3次数部门的员工
SELECT emp_no,COUNT(*)
FROM dept_emp
GROUP BY emp_no
HAVING COUNT(*)>1
ORDER BY emp_no;
2.按表达式或函数分组
#案例1：按照员工姓名长度分组，查询每一组的人数，筛选人数大于
SELECT LENGTH(CONCAT(first_name,last_name)) len_name ,COUNT(*)
FROM employees
GROUP BY LENGTH(CONCAT(first_name,last_name))
HAVING COUNT(*)>0
ORDER BY COUNT(*) ;
#案例1验证
SELECT * 
FROM employees
WHERE LENGTH(CONCAT(first_name,last_name))=27;

7、连接查询

7.1 SQL92语法

只支持内连接
        等值连接
     非等值连接
     自连接 

#示例
1、等值连接
#查询在职员工编号为10001——10009之间的部门信息
SELECT emp_no,d.dept_no,d.dept_name,from_date,to_date
FROM dept_emp de,departments d
WHERE de.`dept_no`=d.`dept_no` AND to_date = '9999-01-01' AND emp_no BETWEEN 10001 AND 10009
ORDER BY de.emp_no;
2、非等值连接

3、自连接

7.2 SQL99语法

/*语法
            select 查询列表
            from 表1 别名 【连接类型】
            join 表2 别名
            on 连接条件
            【where 筛选条件】
            【group by 分组】
            【having 筛选条件】
            【order by 排序列表】
*/
连接类型分类：
1.内连接           inner
        等值连接
     非等值连接
     自连接 
2.外连接
         左外连接       left    【outer】 左边的是主表
         右外连接       right 【outer】   右边的是主表
         全外连接       full    【outer】  mysql不支持
3.交叉连接      cross
#————————————————————————————————————————————————————#
/*外连接
#应用场景：用于查询一个表中有，另一个表没有的记录
特点：
1、外连接的查询结果为主表中的所有记录
        如果从表中有和它匹配的，则显示匹配的值
        如果从表中没有和它匹配的，则显示null
        外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join 左边的是主表
   右外连接，right join 右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
*/

7.3 示例

示例
1.内连接
#案例1：
SELECT emp_no,d.dept_no,d.dept_name,from_date,to_date
FROM dept_emp de
JOIN departments d
ON de.`dept_no`=d.`dept_no` 
WHERE to_date = '9999-01-01' AND emp_no BETWEEN 10001 AND 10009
ORDER BY de.emp_no;
#案例2：查询现任部门经理编号
SELECT emp_no,dept_name,to_date
FROM dept_manager m
JOIN departments d
ON m.`dept_no`=d.`dept_no`
WHERE m.`to_date` = '9999-01-01';
#案例3：查询现任部门经理全名
SELECT dept_name 部门名,CONCAT(first_name,'.',last_name) 部门经理
FROM dept_manager m
JOIN departments d
JOIN employees e
ON m.`dept_no`=d.`dept_no` AND m.`emp_no`=e.`emp_no`
WHERE m.`to_date` = '9999-01-01';
#案例4：查询编号为10010的员工全名，性别、部门名称、部门经理、薪酬
SELECT CONCAT(e.first_name,'.',e.last_name) 员工名,e.gender 性别,d.dept_name 部门,CONCAT(em.first_name,'.',em.last_name) 部门经理,salary 工资
FROM employees e
JOIN dept_emp de
JOIN departments d
JOIN salaries s
JOIN dept_manager m
JOIN employees em
ON e.`emp_no`=de.`emp_no` AND de.`dept_no`= d.`dept_no`AND e.`emp_no`=s.`emp_no` AND m.`dept_no`= d.`dept_no` AND m.`emp_no`=em.`emp_no`
WHERE s.`to_date` = '9999-01-01' AND m.`to_date`  = '9999-01-01' AND de.`to_date` ='9999-01-01' AND e.emp_no = 10010;
2、自连接
#案例1：查询编号为10010的员工全名及部门经理
SELECT CONCAT(e.first_name,'.',e.last_name) 员工名,CONCAT(em.first_name,'.',em.last_name) 部门经理
FROM employees e
JOIN dept_emp de
JOIN dept_manager m
JOIN employees em
ON e.`emp_no`=de.`emp_no` AND de.`dept_no`= m.`dept_no`AND m.`emp_no`=em.`emp_no` 
WHERE  m.`to_date`  = '9999-01-01' AND de.`to_date` ='9999-01-01' AND e.emp_no = 10010;
3、交叉连接
#表1和表2笛卡尔积
SELECT *
FROM departments
CROSS JOIN dept_manager;
4、左外连接
#查询没有男友的女主
SELECT b.`name` 女主,boys.`boyname` 男神
FROM `beauty` b
LEFT JOIN boys 
ON b.`boyfriend_id`=boys.`id`
WHERE boys.`boyname` IS NULL
ORDER BY boys.`boyname`;
5、右外连接
#查询打光棍的男神
SELECT b.`name` 女主,boys.`boyname` 男神
FROM `beauty` b
RIGHT JOIN boys 
ON b.`boyfriend_id`=boys.`id`
WHERE b.`name`IS NULL
ORDER BY boys.`boyname`;

7.4 girls数据库

#创建girls数据库
/*
SQLyog Ultimate
MySQL - 5.6.50-log : Database - girls
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`girls` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */;

USE `girls`;

/*Table structure for table `beauty` */

DROP TABLE IF EXISTS `beauty`;

CREATE TABLE `beauty` (
  `id` int(11) NOT NULL,
  `name` varchar(50) COLLATE utf8_bin NOT NULL,
  `sex` char(1) COLLATE utf8_bin DEFAULT NULL,
  `borndate` datetime DEFAULT NULL,
  `phone` varchar(11) COLLATE utf8_bin DEFAULT NULL,
  `photo` blob,
  `boyfriend_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `beauty` */

insert  into `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`) values 
(1,'盛明兰','女',NULL,NULL,NULL,1),
(2,'盛墨兰','女',NULL,NULL,NULL,12),
(3,'盛如兰','女',NULL,NULL,NULL,13),
(4,'盛老太太',NULL,NULL,NULL,NULL,NULL),
(5,'盛淑兰',NULL,NULL,NULL,NULL,NULL),
(6,'小秦氏',NULL,NULL,NULL,NULL,5),
(7,'朱曼娘',NULL,NULL,NULL,NULL,1),
(8,'盛华兰',NULL,NULL,NULL,NULL,NULL),
(9,'王若弗',NULL,NULL,NULL,NULL,2),
(10,'平宁郡主',NULL,NULL,NULL,NULL,10),
(11,'小桃',NULL,NULL,NULL,NULL,14),
(12,'卫小娘',NULL,NULL,NULL,NULL,2),
(13,'白氏',NULL,NULL,NULL,NULL,5),
(14,'沈皇后',NULL,NULL,NULL,NULL,15),
(15,'刘贵妃',NULL,NULL,NULL,NULL,15),
(16,'林噙霜',NULL,NULL,NULL,NULL,2),
(17,'常嬷嬷',NULL,NULL,NULL,NULL,NULL),
(18,'周雪娘',NULL,NULL,NULL,NULL,NULL),
(19,'孔嬷嬷',NULL,NULL,NULL,NULL,NULL),
(20,'盛品兰',NULL,NULL,NULL,NULL,NULL),
(21,'丹橘',NULL,NULL,NULL,NULL,NULL),
(22,'申和珍',NULL,NULL,NULL,NULL,16);

/*Table structure for table `boys` */

DROP TABLE IF EXISTS `boys`;

CREATE TABLE `boys` (
  `id` int(11) NOT NULL,
  `boyname` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `userCP` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `boys` */

insert  into `boys`(`id`,`boyname`,`userCP`) values 
(1,'顾廷烨',0),
(2,'盛紘',NULL),
(3,'盛长柏',NULL),
(4,'盛长枫',NULL),
(5,'顾偃开',NULL),
(6,'顾廷煜',NULL),
(7,'贺弘文',NULL),
(8,'顾四爷',NULL),
(9,'顾五爷',NULL),
(10,'齐国公',NULL),
(11,'顾廷炳',NULL),
(12,'梁晗',NULL),
(13,'文炎敬',NULL),
(14,'石头',NULL),
(15,'新帝赵宗全',NULL),
(16,'齐衡',NULL);

/*Table structure for table `roles` */

DROP TABLE IF EXISTS `roles`;

CREATE TABLE `roles` (
  `id` int(11) NOT NULL,
  `rolename` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `roles` */

insert  into `roles`(`id`,`rolename`) values 
(1,'盛名兰'),
(2,'顾廷烨'),
(3,'齐衡'),
(5,'盛如兰'),
(6,'盛老太太'),
(7,'盛紘'),
(8,'林噙霜'),
(9,'盛长柏'),
(10,'小秦氏'),
(11,'朱曼娘'),
(12,'盛华兰'),
(13,'王若弗'),
(14,'盛长枫'),
(15,'平宁郡主'),
(16,'顾偃开'),
(17,'顾廷煜'),
(18,'常嬷嬷'),
(19,'周雪娘'),
(20,'小桃'),
(21,'贺弘文'),
(22,'卫小娘'),
(23,'孔嬷嬷'),
(24,'梁晗'),
(25,'丹橘'),
(26,'盛淑兰'),
(27,'赵宗全'),
(28,'沈皇后'),
(29,'盛品兰'),
(30,'刘贵妃');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

8、子查询

定义：出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
分类：
按子查询出现的位置：
        select 后面
                        仅支持标量子查询
        from 后面
                        支持表子查询
        where 或 having 后面
                        标量子查询
                        列子查询
                        行子查询
        exists 后面  #（又称相关子查询）
                        表子查询
按结果接的行列数不同分为：
            #标量子查询（结果集只有一行一列）
            #列子查询（结果集只有一列多行）
            #行子查询（结果集有一行多列）
            #表子查询（结果集一般为多行多列）

#案例
1、标量子查询

2、列子查询

3、行子查询

4、表子查询

9、分页查询

#语法
select 查询列表
from 表
【连接类型】 join 表
on 连接条件
where 筛选条件
group by 分组列表
having 分组后筛选条件
order by 排序字段
limit 【offset,】size;    
/*
注释：offset要显示条目的起始索引（起始索引从0开始）
             size要显示的条目个数  
*/
#特点
        ①limit语句放在查询语句的最后
        ②offset计算公示
        要显示的页数 page，每页显示的条目数size
        
        select 查询列表
        from 表
        limit (page-1)*size,size

#案例：查询第100到150的员工信息
SELECT
  *
FROM
  employees
LIMIT 99, 51;

10、union联合查询

#案例1：
SELECT id,`name` FROM beauty
UNION ALL
SELECT id,boyname FROM boys
ORDER BY id DESC;

11、示例集

1.查询调过部门的在职员工编号及次数

2.
3.

三、DML语言的学习

1、插入语句

/*语法：
方式一：
insert into 表名(列名,...) values(值1,...)
①支持插入子查询
②支持插入多行
方式二：
insert into 表名
set 列名=值,列名=值,...
*/

#插入记录方式一：
#1.插入的值的类型必须与列的类型一致或兼容 
insert  into `roles`(`id`,`rolename`) values 
(1,'盛名兰'),
(2,'林噙霜'),
(3,'盛长柏');
#2.不可以为null的列必须插入值，可以为null的列插入值方式
方式一：
insert  into `boys`(`id`,`boyname`,`userCP`) values 
(1,'顾廷烨',0),
(2,'盛紘',NULL);
方式二：
insert  into `boys`(`id`,`boyname`) values 
(1,'顾廷烨'),
(2,'盛紘');
#3.插入子查询语句
INSERT INTO zhuanye (SELECT DISTINCT `专业代码`,`专业` FROM students ORDER BY `专业代码`);

#插入记录方式二：
insert  into `roles`
set `id`=1,`rolename`='盛名兰',`userCP`='2';

2、修改语句

/*UPDATE语句语法*/
#单表的MySQL UPDATE语句：
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]  
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]
多表的UPDATE语句：
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
/*UPDATE语法可以用新值更新原有表行中的各列。SET子句指示要修改哪些列和要给予哪些值。WHERE子句指定应更新哪些行。如果没有WHERE子句，则更新所有的行。如果指定了ORDER BY子句，则按照被指定的顺序对行进行更新。LIMIT子句用于给定一个限值，限制可以被更新的行的数目。*/

#示例：
UPDATE zhuanye SET zhuanye.`from_date`= 2000;       #修改默认值
UPDATE zhuanye SET zhuanye.`from_date`= 2002  WHERE zy_id LIKE '560%';
UPDATE zhuanye SET zhuanye.`from_date`= 2010  WHERE zy_id LIKE '6_0%';
UPDATE zhuanye SET zhuanye.`from_date`= 2012 WHERE zy_id = 520504;
#修改专业为‘临时’
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
UPDATE students SET students.`专业` = '临时';
#根据专业代码批量修改专业名
UPDATE students s
INNER JOIN zhuanye AS z
ON s.`专业代码` = z.`zy_id`
SET s.`专业` =z.`zy_name`;
#修改学生表中身份证号少一位X的信息（多表连接）
1.语句1：
UPDATE hnjd.`students` hs
INNER JOIN hnjd.`students` hstu
ON hs.`身份证号`=hstu.`身份证号` AND LENGTH(hs.`身份证号`)=17
SET hs.`身份证号`=CONCAT(hstu.`身份证号`,'X');
2.语句2：
UPDATE hnjd.`students` hs
INNER JOIN hnjd.`students` hstu
ON hs.`身份证号`=hstu.`身份证号` 
SET hs.`身份证号`=CONCAT(hstu.`身份证号`,'X')
WHERE LENGTH(hs.`身份证号`)=17;
#去掉身份证号的'X'
UPDATE hnjd.`students` hs
SET hs.`身份证号`=SUBSTR(hs.`身份证号`,1,17)
WHERE hs.`身份证号` LIKE '%X';
#身份证号末尾加'X'
UPDATE hnjd.`students`hs
SET hs.`身份证号`=CONCAT(hs.`身份证号`,'X')
WHERE LENGTH(hs.`身份证号`)=17;
#修改学院信息
UPDATE hnjd.`students` hs
INNER JOIN hnjd.`minzu` hm
ON hs.`民族` = hm.`民族`
SET hs.`民族`=hm.`代码`;
#
UPDATE zhuanye z
INNER JOIN students s
ON s.`专业代码`=z.`zy_id`
SET z.`学制`= s.`学制`,z.`层次`=s.`层次`,z.`学习形式`=s.`学习形式`;
SELECT * FROM zhuanye;

3、删除语句

#单表删除语法
delete  from 表名
where 筛选条件
order by 
limit 
#多表删除语法
1. SQL92语法：
delete 表1的别名,表2的别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件
order by 
limit 
2. SQL99语法：
delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名
on 连接条件
where 筛选条件
order by 
limit 

DELETE FROM students 
WHERE students.`考生号` IN 
(SELECT temptab.`考生号` FROM temptab);

四、DDL语言的学习

DDL 数据定义语言

对库和表的管理：创建、修改、删除

#语句
创建： create
修改： alter
删除： drop

1、库的管理

1.创建数据库
CREATE DATABASE books;
CREATE DATABASE IF NOT EXISTS books; #为了防止系统报错添加if not exists 判断条件

2.修改数据库
#不能修改库名，可以修改字符集集
ALTER DATABASE `books` CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; 

3.删除数据库
DROP DATABASE books; 
DROP DATABASE IF EXISTS books;  #为了防止系统报错添加 if exists判断条件

2、表的管理

1.表的创建
/*语法
create table 表名（
            列名 列的类型[(长度) 约束],
            列名 列的类型[(长度) 约束],
            列名 列的类型[(长度) 约束],
            ....
            列名 列的类型[(长度) 约束],
 )
*/
#案例：创建表book
CREATE TABLE IF NOT EXISTS book(
                id INT,#编号
            bookname VARCHAR(20),#图书名
            price DOUBLE,#价格
            authorID INT,#作者编号
            publishDate DATETIME#出版日期

);
CREATE TABLE IF NOT EXISTS author(
        id INT,
        author_name VARCHAR(20),
        nation VARCHAR(10)
);

2.表的修改
/*
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
*/
#①修改列名
alter table book change column pulishdate pubDate datetime;
ALTER TABLE `hnjd`.`zhuanye` CHANGE COLUMN `专业代码` `zhuaye_id` INT(11) NOT NULL; 
ALTER TABLE zhuanye CHANGE COLUMN `专业` `zhuanye_name` VARCHAR(20);
#②修改列的类型或约束
alter table book modify column pubdate timestamp;
ALTER TABLE `students`.`xueyuan` CHANGE `id` `id` INT(11) NOT NULL, ADD PRIMARY KEY (`id`); 
#③添加新列
alter table author add column annual double;
#④删除列
alter table author drop column annual;
#⑤修改表名
alter table author rename to book_author;

3.表的删除
drop table book；
drop table if exists book;
show tables;

4.表的复制
#1.仅仅复制表的结构
create table copy like book;
#2.复制表的结构+数据
create table copy2 select * from author;
create table copy3 select id,author_name from author;
#3.仅仅复制某些字段（不含数据）
create table copy4
select id,au_name
from author
where 0;

4、数据类型

5、数据约束

五、TCL语言的学习

示例：

mysql>SELECT 姓名,学号,性别,民族,二级学院名,`出生日期` from students where 民族 <> '汉族' and 性别 = '女' and 出生日期 > '2000-01-01'; 
mysql>SELECT 姓名,学号,性别,民族,出生日期 from students where 民族 <> '汉族' and 民族 <> '回族';
mysql> SELECT 姓名,学号,性别,民族,出生日期 from students where 民族 <> '汉族' and 性别 like '女' and 民族 <> '回族';
mysql> SELECT 姓名,学号,性别,民族,出生日期 from students where 民族 <> '汉族' and 民族 <> '回族' and 出生日期 > '2000-01-01';
mysql> SELECT 姓名,学号,性别,民族,出生日期 from students where 民族 <> '汉族' and 民族 <> '回族' and 出生日期 > '1998-01-01' and 出生日期 < '2001-12-31';

创建表：

DROP TABLE IF EXISTS `huizu`;
CREATE TABLE `huizu`  (
  `身份证号` bigint(18) UNSIGNED NOT NULL,
  `考生号` bigint(14) NOT NULL,
  `学号` bigint(11) NOT NULL,
  `姓名` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `性别` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `出生日期` date NOT NULL,
  `民族` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `专业代码` int(11) NOT NULL,
  `专业` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `二级学院名` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `班级` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `层次` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `学制` int(1) NOT NULL,
  `学习形式` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `入学日期` date NOT NULL,
  `当前年级` int(11) NOT NULL,
  `学籍状态` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `预计毕业日期` date NOT NULL,
  PRIMARY KEY (`身份证号`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

创建表从原表：

create table xxgcxy as SELECT * from students ; //从原表复制结构及数据
create table xxg as SELECT * from students limit 1; //从原表复制结构及1条记录数据
truncate table xxgcxy；  //截断表
create table xxg as SELECT * from students limit 1; 

从其他表中插入数据：

//创建表
create table 身份证号少于18位 as SELECT * from students limit 0;
//插入数据从其他表
insert into 身份证号少于18位 SELECT * from students where length(身份证号) < 18;
//删除数据记录
delete from students where length(身份证号) < 18 ;

更新数据：

//更新数据-字符串字段中添加字符
update table01 set 身份证号=CONCAT(身份证号,'X')
//mysql向表中某字段后追加一段字符串:
update table_name set field=CONCAT(field,'str')
//mysql 向表中某字段前加字符串
update table_name set field=CONCAT('str',field)

练习

DROP TABLE IF EXISTS xueyuan;
CREATE TABLE `students`.`xueyuan` ( `xy_id` INT NOT NULL, `xy_name` VARCHAR(20) NOT NULL, PRIMARY KEY (`xy_id`) );

1、去除不可见字符

通过HEX( )函数查询出不可见字符串，
HEX( )函数：返回十六进制值的字符串表示形式。

SELECT
    hnjd.`中国各民族代码`.`民族`,
    HEX('土家族'),
  HEX(hnjd.`中国各民族代码`.`民族`)
FROM
  hnjd.`中国各民族代码`
WHERE hnjd.`中国各民族代码`.`代码` = 15; 
#不可见字符为“C2A0”,转换为十进制为“49824”
SELECT CAST(0xc2a0 AS UNSIGNED);
#使用replace函数和trim函数修改字段
UPDATE 
  hnjd.`中国各民族代码`
SET
  hnjd.`中国各民族代码`.`民族` = TRIM(REPLACE(hnjd.`中国各民族代码`.`民族`,CHAR('49824'),''));

2、去重删除重复数据

DROP TABLE test;
CREATE TABLE test(id INT NOT NULL PRIMARY KEY, DAY DATE NOT NULL);   
INSERT INTO test(id, DAY) VALUES(1, '2006-10-08');  
INSERT INTO test(id, DAY) VALUES(2, '2006-10-08');  
INSERT INTO test(id, DAY) VALUES(3, '2006-10-09');   
SELECT * FROM test;  

DROP TABLE to_delete;
CREATE TEMPORARY TABLE to_delete (DAY DATE NOT NULL, min_id INT NOT NULL);   
INSERT INTO to_delete(DAY, min_id)  
  SELECT DAY, MIN(id) FROM test GROUP BY DAY HAVING COUNT(*) > 1; 
SELECT * FROM to_delete;

DELETE FROM test  
  WHERE EXISTS(  
     SELECT * FROM to_delete
     WHERE to_delete.day = test.day AND to_delete.min_id <> test.id  
  );
SELECT * FROM test;