学习目标

mysql第一天:
1.能够知道数据库的作用
	用来持久化存储和快速读取数据的
	
2.能够完成MySQL数据库的安装
	sudo apt-get install mysql  # ubuntu下服务器端的安装
	sudo apt-get install mysql-client
	
3.能够知道数据类型和数据约束的作用
	1.数据类型，可以指定存储的大小和字段类型。
	2.not null, default, primary key, foreign key
	
4.能够使用Navicat创建数据库并向表中添加数据

5.能够写出增、删、改、查的SQL语句
	insert into 表名 (字段列表) values(值列表);
	delete from 表名 where 条件;
	update 表名 set 字段=值 where 条件;
	select * from 表名 where 条件;
	
6.能够知道去除重复数据行的关键字
	distinct
	select 分组列 from 表名 group by 分组字段;
	
7.能够写出模糊查询的SQL语句
	like '%_'
	
8.能够知道升序查询和降序查询的关键字
	asc desc
	
9.能够使用limit关键字实现分页查询
	limit 偏移量(start), 显示数量(count);
	每页返回结果 = limit 每页数量*(页数-1), 每页数量;
	
10.能够写出查询总行数的SQL语句
	count(*)
	
11.能够知道分组查询的SQL语句
	select gender	from students group by gender;
	
12.能够写出内连接查询的SQL语句
	select * from students as s inner join classes as c on s.cls_id=c.id;
	
13.能够写出左连接查询的SQL语句
	left join
	
14.能够写出右连接查询的SQL语句
	right join
	
15.能够写出子查询的SQL语句
	select * from students where height > (select avg(height) from students);

16.知道使用一张表可以实现自连接查询 -- 明天学习

1.为什么学习数据库

解释: 做web应用，避免不了有很多数据需要长期存储，而用普通文件存储，效率低、麻烦。所以要用到数据库技术
在没学数据库之前能想到的保存方案:
	数据按照一定的格式保存在文本中，然后把数据读取到后，再按照一定的格式还原。
	比如使用,号分割数据。
	老王, 19
这种方式效率低，数据容易出错，还非常麻烦。

2.数据库介绍

解释: 数据库就是存储和管理数据的仓库，数据按照一定的格式进行存储，用户可以对数据库中的数据进行增加、修改、删除、查询等操作。

数据库分类:
	1.关系型数据库
		比如: Oracle, Microsoft SQL Server, MySQL, SQLite
	2.非关系型数据库
		比如: MongoDB，Redis

数据库的特点:
	1.持久化存储
	2.读写速度高
	3.保证数据的有效性

小结:
	1.数据库就是存储和管理数据的一个仓库，是用来持久化存储和快速读取数据的。
	2.数据库可以分为关系型数据库和非关系型数据库
	3.我们接下来要学习的就是关系型数据库中的mysql数据库。

3.关系型数据库管理系统的介绍

解释: 数据库管理系统（英语全拼：Relational Database Management System，简称RDBMS）是为管理关系型数据库而设计的软件系统，简单来说就是一个应用软件。

据库管理系统分为客户端和服务端：
	客户端的作用：
		1.连接数据库
		2.发送sql命令操作数据库
	服务端的作用：
		1.管理、存储数据
		2.分析sql命令，根据sql命令查询获取数据返回给客户端

SQL语言主要分为：
  DQL：数据查询语言，用于对数据进行查询，如select(重点)
  DML：数据操作语言，对数据进行增加、修改、删除，如insert、update、delete(重点)
  TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback
  DCL：数据控制语言，进行授权与权限回收，如grant、revoke
  DDL：数据定义语言，进行数据库、表的管理等，如create、drop

小结:
	1.关系型数据库管理系统是一个软件，它可以管理不同的数据库，想要对数据库进行操作安装对应的关系型数据库管理系统软件即可。
	2.SQL的作用是实现数据库客户端和数据库服务端之间的通信，好比是通信桥梁。

4.mysql数据库(数据库管理系统)

解释: MySQL是一个关系型数据库管理系统，是目前最流行的数据库管理系统之一。
特点:
	1.MySQL是开源的，所以你不需要支付额外的费用。
	2.MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
	3.MySQL使用标准的SQL数据语言形式。
	4.MySQL可以安装在不同的操作系统，并且提供多种编程语言的操作接口。这些编程语言包括C、C++、Python、Java、Ruby等等。

mysql服务端安装:
	sudo apt-get install mysql-server

mysql服务端管理命令:
	启动: sudo service mysql start
	停止: sudo service mysql stop
	重启: sudo service mysql restart
	查看状态: sudo service mysql status

配置文件:
	路径: /etc/mysql/mysql.conf.d/mysqld.cnf
	主要配置信息说明:
		port: 端口号，默认3306
		bind-address: 服务器绑定的ip，默认为127.0.0.1
		datadir: 数据库保存路径，默认为/var/lib/mysql
		log_error: 错误日志，默认为/var/log/mysql/error.log

mysql客户端安装:
	命令行客户端
		sudo apt-get install mysql-client
	

ps命令: 查看当前系统中的进程
  -a 表示所有用户
  -u 表示显示用户名
  -x 表示显示所有的执行程序
查找mysql进程: ps -aux | grep mysql

5.数据类型和约束的介绍

1.数据类型是指在创建表的时候，为表中字段指定数据类型，只有数据符合类型要求才能存储起来。
2.约束是指数据在数据类型限定的基础上，额外增加的要求.

常用的数据类型:
  整数：int，bit(tinyint)
  小数：decimal
  字符串：varchar,char
  日期时间: date, time, datetime
  枚举类型(enum)
  	经验: 一般使用整数类形替代
  	

数据类型说明:
  decimal表示浮点数，如 decimal(5, 2) 表示共存5位数，小数占 2 位. 180.00
  char表示固定长度的字符串，如char(3)，如果填充'ab'时会补一个空格为'ab '，3表示字符数
  varchar表示可变长度的字符串，如varchar(3)，填充'ab'时就会存储'ab'，3表示字符数
  字符串 text 表示存储大文本，当字符大于 4000 时推荐使用, 比如技术博客.

常见的约束:
	主键 primary key: 每条记录的唯一标识。 
		建议: MySQL 所有表的主键字段都叫 id, 类型为 bigint unsigned.
  非空 not null: 此字段不允许填写空值.
  惟一 unique: 此字段的值不允许重复.
  默认 default: 当不填写字段对应的值会使用默认值，如果填写时以填写为准.
  外键 foreign key: 对关系字段进行约束, 当为关系字段填写值时, 会到关联的表中查询此值是否存在, 如果存在则填写成功, 如果不存在则填写失败并抛出异常.

备注:
	数据类型原则:够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间。

6.navicat的使用

解压:
	tar -zxvf navicat121_mysql_cs_x64.tar.gz
图形化界面客户端Navicat(只做了解):
  安装步骤看课件
  解决中文乱码:
  vim ./start_navicat
  把export LANG="en_US.UTF-8"改为export LANG="zh_CN.UTF-8"

7.数据库的登录和登出

登录:
--mysql -uroot -p密码
mysql -uroot -pmysql
mysql -uroot -p (建议这种)

登出:
-- exit/quit/ctrl + d

查看当前时间:
select now();

终端清屏:
ctrl + l

备注:
	登录习惯，不要在命令中显式输入密码，因为在会记录在history命令中，不安全。

8.数据库操作

-- 查看所有数据库:
show databases;

-- 创建数据库python15:
create database python15 charset=utf8;

-- 使用数据库python15:
use python15;


-- 查看当前数据库:
select database();


-- 查看创建数据库sql语句:
show create database python15;


-- 删除数据库:
drop database python15;

备注：
	在mysql中，命令大小写不敏感。
	原因：在mysql中，执行的所有命令都会直接转成大写，在执行。
	如果有用到表情，使用charset=utf8mb4;

9.表结构操作

-- 查看当前数据库中所有表!!!
show tables;

-- 创建表
  -- int unsigned 无符号整形
  -- auto_increment 表示自动增长跟主键在一起
  -- not null 表示不能为空
  -- primary key 表示主键
  -- default 默认值
  -- create table 数据表名字 (字段 类型 约束[, 字段 类型 约束]);
  -- 注意：先创建一个数据库，并使用，再创建表
  -- 创建一个test表，有name, age字段
create table test(
	name varchar(50),
  age int unsigned
);


-- 查看test表结构(查看表的一些字段的信息)
  -- desc 数据表的名字; !!!
desc test;
 

-- 创建 classes 表(id、name)
create table classes(
	id int unsigned primary key auto_increment,
  name varchar(20) not null
);

 
-- 创建 students 表
  -- (id、name、age、height(decimal)、gender (enum)、cls_id)
create table students(
  id int unsigned primary key auto_increment,
  name varchar(20) not null,
  age int unsigned,
  height decimal(5, 2),
  gender enum('男', '女', '中性', '保密') default '保密',
  cls_id int unsigned
);

-- 查看表的创建语句
  -- show create table 表名字;
show create table students;

    
-- 修改表-添加字段 mascot (吉祥物)
  -- alter table 表名 add 列名 类型;
  -- 在班级表中添加  吉祥物  字段
alter table classes add mascot varchar(20);
   
-- 修改表-修改字段：不重命名版
  -- alter table 表名 modify 列名 类型及约束;
alter table classes modify mascot varchar(30) not null;

   
-- 修改表-修改字段：重命名版 jxw
  -- alter table 表名 change 原名 新名 类型及约束;
alter table classes change mascot jxw varchar(40);

   
-- 修改表-删除字段
  -- alter table 表名 drop 列名;
alter table classes drop jxw;

    
-- 删除表
  -- drop table 表名;
  -- drop database 数据库;
drop table classes;
drop database python15;

10.表操作(增删改查)(curd)

-- 增加
  -- insert into 表名(字段1,字段2) values(值1，值2);
  -- 主键字段 可以用 0  null   default 来占位
-- 向classes表中插入 一个班级
	-- 1.指定id，name. 插入班级python13, python15。
insert into classes (id, name) values (1, 'python15');
insert into classes (id, name) values (2, 'python13');


	-- 不指定id, 使用id自增长. 插入班级python16, python17, python18。
insert into classes (id, name) values (0, 'python16');
insert into classes (id, name) values (null, 'python17');
insert into classes (id, name) values (default, 'python17');
   -- 注意：id是主键,是唯一的,自动增长的,从1开始的,如果写0,那么相当于没写
        
-- 向students表插入 一个学生信息
-- 全部字段插入
  -- insert into students(字段名1,字段名2,....) values(数据1,数据2,....);
insert into students(id, name, age, height, gender, cls_id) values (1, '张三', 18, 175.00, '男', 1);
  

-- 部分字段插入
  -- insert into 表名(列1,...) values(值1,...)
insert into students (name, age) values('李四', 99);


-- 多行插入
  -- insert into students(字段名1,字段名2,....) values(数据1,数据2,....),(数据1,数据2,....);
insert into students (name, age) values('王五', 18), ('田七', 19);

-- 修改
  -- update 表名 set 列1=值1,列2=值2... where 条件;
  -- 全部修改
	update students set age=18, name='张三';
        
-- 按条件修改
	update students set age=19, name='李四' where id = 1;

        
-- 按条件修改多个值
  -- update students set gender ="",age = "" where name="xxx";
  	update students set age=20, height=180.00  where name ='李四';

        
-- 查询基本使用
 	-- 查询所有列
  -- select * from 表名;
select * from students;
select * from classes;

        
-- 指定条件查询  
-- 查看李四的信息
select * from students where name='李四';

        
-- 查询返回指定列
  -- select 列1,列2,... from 表名;
select name, age from students where id=1;

 
-- 可以使用as为列或表指定别名
  -- select 字段[as 别名] , 字段[as 别名] from 数据表;
select name as '姓名', age as '年龄' from students;

        
-- 字段的顺序
select age as '年龄', name as '姓名' from students;

        
-- 删除
  -- 物理删除
  -- delete from 表名 where 条件
delete from students where name='李四';

      
-- 逻辑删除
  -- 用一个字段来表示 这条信息是否已经不能再使用了
  -- 给students表添加一个 is_delete 字段 bit 类型(建议tinyint)
  -- alter table 表名 add 字段 类型 default 默认值;
alter table students add is_delete bit(1) default 0;

  -- update 表名 set is_delete=1 where id=;
update students set is_delete = 1 where id=2;

11.准备测试数据

-- 准备测试数据:
	-- 数据库操作前的准备
    -- 创建数据库
    create database python_test_15 charset=utf8;
  	-- 使用数据库
    use python_test_15;

    -- students表
    create table students(
        id int unsigned primary key auto_increment not null,
        name varchar(20) default '',
        age tinyint unsigned default 0,
        height decimal(5,2),
        gender enum('男','女','中性','保密') default '保密',
        cls_id int unsigned default 0,
        is_delete tinyint default 0
    );

    -- classes表
    create table classes (
        id int unsigned auto_icrement primary key not null,
        name varchar(30) not null
    );
    -- 准备数据
    -- 向students表中插入数据
insert into students values (0,'小明',18,180.00,2,1,0), (0,'小月月',18,180.00,2,2,1), (0,'彭于晏',29,185.00,1,1,0), (0,'刘德华',59,175.00,1,2,1), (0,'黄蓉',38,160.00,2,1,0), (0,'凤姐',28,150.00,4,2,1), (0,'王祖贤',18,172.00,2,1,1), (0,'周杰伦',36,NULL,1,1,0), (0,'程坤',27,181.00,1,2,0), (0,'刘亦菲',25,166.00,2,2,0), (0,'金星',33,162.00,3,3,1),(0,'静香',12,180.00,2,4,0),(0,'郭靖',12,170.00,1,4,0), (0,'周杰',34,176.00,2,5,0);

    -- 向classes表中插入数据
    insert into classes values (0, "python_01期"), (0, "python_02期");

12.as和distinct关键字

-- 使用 as 给字段起别名:
select name as '姓名', age as '年龄' from students;

-- 使用 as 给表起别名:
select s.name, s.age from students as s;


-- 查询练习
		-- 查询所有字段
    -- select * from 表名;
select * from students;
select * from classes;

    -- select 表名.字段 .... from 表名;
    -- 可以通过 as 给表起别名
    -- select 表别名.字段 .... from 表名 as 表别名;
select s.name as '姓名', s.age as '年龄' from students as s;


    -- 消除重复行(查性别)
    -- distinct 字段
    -- 查询students中的所有性别
    -- select gender from students;   --会有很多重复数据
	  -- 去掉重复数据
select distinct gender from students;    

13.where条件查询

-- 条件查询
    -- 比较运算符
        -- select .... from 表名 where .....
        -- >
        -- 查询年纪大于18岁的学生信息
        -- select * from students;  这样查询的是所有学生的所有信息
select * from students where age > 18;


        -- <
        -- 查询年纪小于18岁的学生信息
select * from students where age < 18;
        
        -- >=
        -- <=
        -- 查询小于或者等于18岁的学生信息
select * from students where age <= 18;


        -- =
        -- 查询年龄为18岁的所有学生信息
select * from students where age = 18;        


        -- != 或者 <>
        -- 查询年龄不为18岁的所有学生信息
select * from students where age != 18;  
select * from students where age <> 18;        

       
    -- 逻辑运算符
        -- and  /  between .. and ..
        -- 18和28岁之间的所以学生信息
select * from students where age >= 18 and age <=28;
select * from students where age between 18 and 28; 		-- 包含18和28


        -- 18岁以上的女性的学生信息
select * from students where age > 18 and gender='女';
select * from students where age > 18 and gender=2;


        -- or
        -- 18岁以上或者身高180以上的学生信息
select * from students where age >= 18 or height >= 180;

        
        -- not
        -- 不在 18岁以上的女性 这个范围内的信息
        -- select * from students where not (age>18 and gender=2);
select * from students where not (age>=18 and gender='女');


    -- 模糊查询(where 字段 like 要查询的数据)
        -- like 
        -- % 替换任意个任意字符
        -- _ 替换1个任意字符
        -- 查询姓名中 以 "小" 开始的名字的学生信息
select * from students where name like '小%';


        -- 查询姓名中 有 "小" 所有的名字的学生信息
select * from students where name like '%小%';

 
        -- 查询有2个字的名字的学生信息
select * from students where name like '__';

        
        -- 查询有3个字的名字的学生信息
select * from students where name like '___';


        -- 查询至少有2个字的名字的学生信息
select * from students where name like '__%';



    -- 范围查询
        -- in (1, 3, 8)表示在一个非连续的范围内
        -- 查询 年龄为18或34的姓名的学生信息
select * from students where age in (18, 34);
select * from students where age=18 or age=34;


        -- not in 不非连续的范围之内
        -- 年龄不是 18或34岁的学生信息
select * from students where age not in (18, 34);

select * from students where not age in (18, 34); -- 这种写法一般不会出现


        -- between ... and ...表示在一个连续的范围内
        -- 查询 年龄在18到34之间的学生信息
select * from students where age between 18 and 34;
select * from students where age>=18 and age <= 34;


        -- not between ... and ...表示不在一个连续的范围内
        -- 查询 年龄不在18到34之间的学生信息
select * from students where not (age between 18 and 34);
        
    -- 空判断
        -- 判空is null
        -- 查询身高为空的学生信息
select * from students where height is NULL;

        
        -- 判非空is not null
select * from students where height is not NULL;

select * from students where not height is NULL;

14.排序

-- 排序
    -- order by 字段
    -- asc
    -- asc从小到大排列，即升序
    -- desc
    -- desc从大到小排序，即降序
    -- 查询年龄在18到34岁之间的男性，按照年龄从小到大到排序-升序
select * from students where (age between 18 and 34) and gender='男' order by age asc; 

    -- 查询年龄在18到34岁之间的女性，身高从高到矮排序 - 降序
select * from students where (age between 18 and 34) and gender='女' order by height desc;
    
    -- order by 多个字段
    -- 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序
select * from students where (age between 18 and 34) and gender='女' order by height desc, age asc;
     

    -- 如果年龄也相同那么按照id从大到小排序 - 降序
select * from students where (age between 18 and 34) and gender='女' order by height desc, age asc, id desc;
   

15.分页查询

-- 分页(!!!!!!放在最后面(注意))
    -- limit start(偏移量), count(返回的数量)
    -- limit 放在最后面(注意)
    -- limit (要显示第几页-1) * 每页显示多少个, 每页显示多少个;

    -- 限制查询出来的数据个数
    -- 查询前5个数据(相当于 每页显示5个，要显示第1页)
select * from students limit 0, 5;
select * from students limit 5;


    -- 每页显示2个，要显示第1页 (显示前2个数据)
    -- select * from students limit 2;
select * from students limit 2;


    -- 每页显示2个，要显示第2页
select * from students limit 2, 2;

   
    -- 每页显示2个，要显示第3页
select * from students limit 4, 2;


    -- 每页显示2个，要显示第4页
select * from students limit 6, 2;
select * from students limit 2*(4-1), 2; -- mysql不支持此写法

    -- 每页显示2个，显示第6页的信息, 按照年龄从小到大排序
select * from students order by age asc;
select * from students order by age asc limit 10, 2;

小结：
	某页数据 = limit 每页数量*(页码-1), 每页数量

16.聚合函数

-- 聚合函数
    -- 总数
select count(*) from students;

    -- count
    -- 查询男性有多少人
select count(*) from students where gender='男';

    -- 最大值
    -- max
    -- 查询最大的年龄
select max(age) from students;


    -- 查询女性的最高 身高
select max(height) from students where gender='女';
select max(height) from students where gender=2;


    -- 最小值
    -- min
select min(height) from students where gender='女';

    -- 求和
    -- sum
    -- 计算所有人的年龄总和
select sum(age) from students;

 
    -- 平均值
    -- avg
    -- 计算平均年龄
select avg(age) from students;


    -- 计算平均年龄 sum(age)/count(*)
select sum(age)/count(*) from students;

    
    -- 四舍五入 round(123.23 , 1) 保留1位小数
    -- 计算所有人的平均年龄，保留2位小数
select round(avg(age), 2) from students;

    -- select round(avg(age), 3) from students;   -- 四舍五入
    
    -- 计算男性的平均身高 保留2位小数
select round(sum(height)/count(*), 2) from students where gender='男';

		-- avg 计算的时候不包含有值为null的数据
select round(avg(height), 2) from students where gender='男';
    
    -- 计算男性的平均身高 保留2位小数，包括null
select round(sum(height)/count(*), 2) from students where gender='男' and height is not null;

17.分组查询

-- 分组
    -- 分组的口诀
    -- select 分组的字段 from 表名 group by 分组字段;
    -- group by
    -- 按照性别分组,查询所有的性别
select gender from students group by gender;


    # 每种   每类   
    -- 计算每种性别中的人数
select gender, count(*) from students group by gender;

    -- group_concat(...)
    -- 查询同种性别中的姓名group_concat(name)
select gender, group_concat(name) from students group by gender;

     
    -- 查询每组性别的平均年龄
select gender, avg(age) from students group by gender;

    
    -- having(注意having和group by 连用 having后通常也要跟 聚合函数)
    -- 查询  平均年龄超过30岁  的这组性别，以及姓名
select gender, group_concat(name), avg(age) from students group by gender having avg(age) > 30;

    
    -- 查询每种性别中的人数  多于2个的信息
select gender, count(*) from students group by gender having count(*) > 2;
    

    -- with rollup 汇总的作用(了解)
    -- select gender,count(*) from students group by gender with rollup;
select gender, count(*) from students group by gender with rollup;
    

18.连接查询-内连接

-- (内)连接查询
    -- inner join ... on
    -- select ... from 表A inner join 表B;
    -- 查询 有能够对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id = classes.id;


    -- 按照要求只显示姓名、班级
select students.name, classes.name from students inner join classes on students.cls_id=classes.id;
    
    -- 给数据表起名字
select s.name, c.name from students as s inner join classes as c on s.cls_id=c.id;
    
    -- 查询 有能够对应班级的学生以及班级信息，显示学生的所有信息 students.*，只显示班级名称 classes.name.
select s.*, c.name from students as s inner join classes as c on s.cls_id=c.id;

    -- 在以上的查询中，将班级名显示在第1列
select c.name, s.*  from students as s inner join classes as c on s.cls_id=c.id;
    

    -- 查询 有能够对应班级的学生以及班级信息, 按照班级名进行排序
select c.name, s.*  from students as s inner join classes as c on s.cls_id=c.id order by c.name; -- 默认asc升序
    

    -- 当在同一个班级的时候，按照学生的id进行从小到大排序
select c.name, s.*  from students as s inner join classes as c on s.cls_id=c.id order by c.name, id asc;
    

19.连接查询-左连接

-- 左连接: 左边的表的数据不管在右边的表中有没有匹配到数据都显示
  -- left join
  -- 查询每位学生对应的班级信息
select * from students left join classes on students.cls_id=classes.id;

20.连接查询-右连接

-- 右连接,右边的表不管在左边有没有找到都显示 
  -- right join   on
select * from students right join classes on students.cls_id=classes.id;

-- 为了演示效果，插入一条students表没有的cls_id数据。
insert into classes (id, name) values (7, 'python17');

21.子查询

-- 子查询
    -- 子查询是一个完整的SQL语句，子查询被嵌入到一对小括号里面
    -- 查询出高于平均身高的信息(height)
    -- 1、先查平均身高
select avg(height) from students;
    -- 2、再查高于平均身高的学生信息
select * from students where height > (select avg(height) from students);


    -- 查询学生的班级号能够对应的 学生名字
    -- select * from students where cls_id in(1,2);
    -- select id from classes;
   
		-- 1、找出所有班级的id
select id from classes;
		
		-- 2、再过滤在id列表中的学生
select * from students where cls_id in (select id from classes);

22.自连接

-- 省市区三级联动  
    -- 数据操作前的准备
    -- 创建数据库表
    create table areas(
      aid int primary key,
      atitle varchar(20),
      pid int
    );

    -- 从sql文件中导入数据
    -- source 具体地址/areas.sql;
    -- 先把areas.sql复制到桌面目录下
    source /home/python/Desktop/areas.sql
    
    -- 查询一共有多少个省
    

    -- 例1：查询省的名称为“山西省”的所有城市
    
    -- 先查山西省id

    
    -- select * from areas where pid = 山西省aid



    -- 例2：查询市的名称为“广州市”的所有区县:


    -- select * from areas where pid = "广州市aid"



    -- 自连接
    -- select * from areas as a1 inner join areas as a2 on a1.aid=a2.pid where a1.atitle='广州市';