01-数据库MySQL[Python]

一、数据库系统

• 数据库
  DataBase【DB】，指的是长期保存到计算机上的数据，按照一定顺序组织，可以被各种用户或者应用共享的数据集合。【用于存储数据的地方，可以视为存储数据的容器】

• 数据库管理系统
  DataBase Management System【DBMS】,能够管理和操作数据库的大型的软件。用于建立、使用和维护数据库，对数据库进行统一的管理和控制，为了保证数据库的安全性和完整性。用户可以通过数据库管理系统访问数据库中的数据，或对数据库进行维护。

数据库和数据库管理系统之间的关系?
数据库：存储，维护和管理数据的集合;
数据库管理系统: 就是数据库管理软件，通过它可以进行数据库的管理和维护工作;

• 常见的数据库管理系统
  1.Oracle(甲骨文): 目前比较成功的关系型数据库管理系统，运行稳定，功能齐全，性能超群，技术领先，主要应用在大型的企业数据库领域。
  2.DB2: IBM(国际商业机器公司)的产品，伸缩性比较强。
  3.SQL Server: Microsoft的产品，软件界面友好，易学易用，在操作性和交互性方面独树一帜。
  4.MySQL: 免费的数据库系统，被广泛引用于中小型应用系统，体积小，速度快，总体拥有成本低，开发源代码，2008年被SUN收购，2009年SUN被Oracle收购。

二、数据库的安装

• 安装

# 添加
sudo add-apt-repository 'deb http://archive.ubuntu.com/ubuntu trusty main universe'

# 更新
sudo apt-get update

# 搜索(从返回的结果查看有哪些版本可以安装，用下边的命令安装相应的版本)
sudo apt-cache search mysql | grep mysql-server

# 安装5.6版本
sudo apt install mysql-server-5.6

# 设置开机自启动
sudo systemctl enable mysql.service

• 服务开启和停止

// 方式一
sudo service mysql status  // 查看状态
sudo service mysql start    // 开启服务
sudo service mysql stop    // 停止服务

// 方式二
sudo systemctl stauts mysql.service    // 查看状态  
sudo systemctl start mysql.service      // 开启服务
sudo systemctl stop mysql.service      // 停止服务器
sudo systemctl restart mysql.service  // 重启服务
sudo systemctl enable mysql.service  // 开启服务开机自启动
sudo systemctl disable mysql.service  // 关闭服务开机自启动

• 数据库连接

语法:
  mysql -u 用户名 -p
例如:
  // 默认用户名root， 密码123456
  mysql -u root -p
  mysql -uroot -p
  mysql -uroot -p123456

退出数据库: quit
MySQL端口号是3306!!!

• 远程服务器中的数据库

建立远程连接用户:
$  mysql –uroot –p123456  // 连接数据库，-u用户名 -p密码
mysql> use mysql;
mysql> select * from user;
// 第一个admin表示用户名，%表示所有的电脑都可以连接，也可以设置某个ip地址运行连接，第二个admin123表示密码
// 为了方便其实也可以设置为root
mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'admin123' WITH GRANT OPTION;    
mysql> select * from user;  // 查看远程连接用户admin是否新增成功，若存在则成功
mysql> exit;    // 退出

默认mysql的root用户只能是本地连接。如果远程服务器的数据，需要建立远程连接用户，否则连接不了!!!

三、SQL概述

• 概述
  Structure Query Language，结构化查询语言。是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

• 数据库服务器、数据库、表单之间的关系

  
  关系
• 分类
  1.DDL【Data Definition Language】数据定义语言，用户创建、修改、删除表结构
  2.DML【Data Manipulation Language】数据操作语言，用于对数据表进行增删改的操作
  3.DQL【Data Query Language】数据查询语言，用于负责数据表的查询工作
  4.DCL【Data Control Language】数据控制语言，用来定义访问权限和安全级别

四、数据库之DDL

一般情况下，mysql关键字都是大写的，但是为了方便就小写。

• 显示所有数据库（当前数据库服务器中）

mysql> show databases;                
+--------------------+    
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

• 选择数据库

格式: 
  use 数据库名称;
例如: 
  use zyz;

注意: 在创建表单、添加数据、删除数据、修改数据...操作的前提是要进入到对应的数据中，否则是操作不了的。

• 创建数据库

格式：
  create database 数据库名字;
例如：
  create database zyz;
  // 创建时指定编码类型
  create database pythondb charset=utf8;

注意: 命令行操作，要注意中英文状态，都是要使用英文的标点符号！！！

• 删除数据库

格式：
  drop database 数据库名字;
例如：
  drop database zyz;

• 查看数据库中的表

show tables；

• 创建表

格式:
  create table 表名(
    字段1 字段类型[列级别约束条件][默认值]，
    字段2 字段类型[列级别约束条件][默认值]，
    ….
    字段n 字段类型[列级别约束条件][默认值]
    [表级别约束条件]
  )
例如：
  create table students(id int(4),name char(20),age int(4));
  create table students(id int(4) primary key,name char(20),age int(4));

创建表之前，一定要先进入对应的数据库中！！！
显示创建表的详细信息: show create table student;

• 删除表

格式:
  drop table 表名;
例如：
  drop table students;

• 修改表名

格式：
  alter table 老表名 rename 新表名;
例如：
  alter table students_base rename students;

• 查看表的结构

格式：
  desc 表名;
例如：
  desc students;

Field:域/字段/列  
Type：类型
Null:是否为空
Key：主键 
Default：默认值
Extra：额外属性

查看表的结构

• 增加一个字段

格式：
  alter table 表名 add 新字段的名字 字段属性
例如：
  alter table students add sex char(10); 

如果要添加的某个字段不能为空，就是not null;
例如: alter table students add sex char(10) not null;

• 删除一个字段

格式：
  alter table 表名 drop 字段名;
例如：
  alter table students drop sex;

• 修改某个字段 --- 添加属性

格式：
  alter table 表名 change 老字段名 新字段名 字段属性;
例如：
  alter table students change id id int(4) auto_increment;  //给id添加自动增长的属性

修改某个字段

• 修改某个字段 --- 修改属性

//修改id的属性，去除auto_increment属性
alter table students change id id int(4);

修改属性

• 修改某个字段 --- 添加主键

alter table students add primary key(id);
alter table students change id id int(4) primary key;

一张表中只能有一个字段是自动增长的，并且被设定为自动增长的这个字段一定要设置为主键;
如果一个主键字段有自增长属性，如果想要直接删除主键，这是操作不了的；必须是先将自增长属性去除，然后才能删除主键；

• 常用数据类型

1.数字数据类型
- INT - 正常大小的整数，可以带符号。如果是有符号的，它允许的范围是从-2147483648到2147483647。如果是无符号，允许的范围是从0到4294967295。 可以指定多达11位的宽度。
- TINYINT - 一个非常小的整数，可以带符号。如果是有符号，它允许的范围是从-128到127。如果是无符号，允许的范围是从0到255，可以指定多达4位数的宽度。
- SMALLINT - 一个小的整数，可以带符号。如果有符号，允许范围为-32768至32767。如果无符号，允许的范围是从0到65535，可以指定最多5位的宽度。
- MEDIUMINT - 一个中等大小的整数，可以带符号。如果有符号，允许范围为-8388608至8388607。 如果无符号，允许的范围是从0到16777215，可以指定最多9位的宽度。
- BIGINT - 一个大的整数，可以带符号。如果有符号，允许范围为-9223372036854775808到9223372036854775807。如果无符号，允许的范围是从0到18446744073709551615. 可以指定最多20位的宽度。
- FLOAT(M,D) - 不能使用无符号的浮点数字。可以定义显示长度(M)和小数位数(D)。这不是必需的，并且默认为10,2。其中2是小数的位数，10是数字(包括小数)的总数。小数精度可以到24个浮点。
- DOUBLE(M,D) - 不能使用无符号的双精度浮点数。可以定义显示长度(M)和小数位数(D)。 这不是必需的，默认为16,4，其中4是小数的位数。小数精度可以达到53位的DOUBLE。 REAL是DOUBLE同义词。
- DECIMAL(M,D) - 非压缩浮点数不能是无符号的。在解包小数，每个小数对应于一个字节。定义显示长度(M)和小数(D)的数量是必需的。 NUMERIC是DECIMAL的同义词。[decimal]
2.日期和时间类型
- DATE - 以YYYY-MM-DD格式的日期，在1000-01-01和9999-12-31之间。 例如，1973年12月30日将被存储为1973-12-30。
- DATETIME - 日期和时间组合以YYYY-MM-DD HH:MM:SS格式，在1000-01-01 00:00:00 到9999-12-31 23:59:59之间。例如，1973年12月30日下午3:30，会被存储为1973-12-30 15:30:00。
- TIMESTAMP - 1970年1月1日午夜之间的时间戳，到2037的某个时候。这看起来像前面的DATETIME格式，无需只是数字之间的连字符; 1973年12月30日下午3点30分将被存储为19731230153000(YYYYMMDDHHMMSS)。
- TIME - 存储时间在HH:MM:SS格式。
- YEAR(M) - 以2位或4位数字格式来存储年份。如果长度指定为2(例如YEAR(2))，年份就可以为1970至2069(70〜69)。如果长度指定为4，年份范围是1901-2155，默认长度为4。
3.字符串类型
虽然数字和日期类型比较有意思，但存储大多数数据都可能是字符串格式。 下面列出了在MySQL中常见的字符串数据类型。
- CHAR(M) - 固定长度的字符串是以长度为1到255之间个字符长度(例如：CHAR(5))，存储右空格填充到指定的长度。 限定长度不是必需的，它会默认为1。
- VARCHAR(M) - 可变长度的字符串是以长度为1到255之间字符数(高版本的MySQL超过255); 例如： VARCHAR(25). 创建VARCHAR类型字段时，必须定义长度。 [varchar]
- BLOB or TEXT - 字段的最大长度是65535个字符。 BLOB是“二进制大对象”，并用来存储大的二进制数据，如图像或其他类型的文件。定义为TEXT文本字段还持有大量的数据; 两者之间的区别是，排序和比较上存储的数据，BLOB大小写敏感，而TEXT字段不区分大小写。不用指定BLOB或TEXT的长度。
- TINYBLOB 或 TINYTEXT - BLOB或TEXT列用255个字符的最大长度。不指定TINYBLOB或TINYTEXT的长度。
- MEDIUMBLOB or MEDIUMTEXT - BLOB或TEXT列具有16777215字符的最大长度。不指定MEDIUMBLOB或MEDIUMTEXT的长度。
- LONGBLOB 或 LONGTEXT -  BLOB或TEXT列具有4294967295字符的最大长度。不指定LONGBLOB或LONGTEXT的长度。
- ENUM - 枚举，这是一个奇特的术语列表。当定义一个ENUM，要创建它的值的列表，这些是必须用于选择的项(也可以是NULL)。例如，如果想要字段包含“A”或“B”或“C”，那么可以定义为ENUM为 ENUM(“A”，“B”，“C”)也只有这些值(或NULL)才能用来填充这个字段。

主要使用的数据类型：
   数字型数据类型: int float double
   日期类: date
   字符串: char varchar

char 和 varchar 的区别
char(M)是固定长度的字符串， 在定义时指定字符串列长。当保存数据时如果长度不够在右侧填充空格以达到指定的长度。M 表示列的长度，M 的取值范围是0-255个字符;
varchar(M)是长度可变的字符串，M 表示最大的列长度。M 的取值范围是0-65535。varchar的最大实际长度是由最长的行的大小和使用的字符集确定的，而实际占用的空间为字符串的实际长度+1

五、数据库之DML

• 增加数据

格式：insert into 表名(字段1,字段2,.....) values(值1,值2.....);
例如：
  insert into students(id,name,age) values(1001,"张三",20);
  insert into students(name,age) values("王六",22);
  insert into students(name,age,sex) values("八妹",25,"女");
  insert into students(name,sex) values("九妹","女");

  // 注意值必须是和默认结构与之对应
  insert into students values(1006,"老七",18,'男');

一次性写入多条数据
格式: 
  insert into 表名(属性1，属性2..) values(值1.1，值2.1...),(值1.2，值2.2,....);
例如: 
   insert into students(id,name,sex,age) values('1001','张三','男',20),('1002','李四','男',21);

插入的是日期，和字符串一样，使用引号括起来。

• 更新数据

格式：
  update 表名 set 字段=值 where 条件;
例如：
  update students set address="江西"  where name="王老五";
  update students set math=98,chinese=93 where id=1002;

如果where子句没有指定，则默认将表中的数据全部更新。

• 删除数据

格式：delete from 表名 where 条件 
例如：
  // 删除整个表单数据
  delete from students;
  // 删除名字为李四的学生信息
  delete from students where name="李四";
  // 删除id为1003的学生信息
  delete from students where id=1003;

如果where子句没有指定，则默认将表中的数据全部删除。

六、数据库之DQL

数据库执行DQL语言不会对数据库中的数据发生任何改变，而是让数据库发送查询结果到客户端，查询返回的结果其实是一张虚拟表。
基本语法: SELECT 列名 FROM 表名 [WHERE --> GROUP BY -->HAVING--> ORDER BY]

• 基础查询

// 查询所有字段(列)
select * from students;

// 查询指定字段(列)
select id,name,age from student;

// as添加别名 【后续用于子查询】
select name as "姓名" from studnets;

as不是给表里的字段取别名，而是给查询的结果字段取别名。其目的是让查询的结果展现更符合人们观看习惯，在多张表查询的时候可以直接的区别多张表的同名的字段。

• 条件查询

1、模糊查询数据
  %: 表示任意个或多个字符，可匹配任意类型和长度的字符
  _: 匹配任意一个字符
 例如: 查询出学生姓王的学生信息；
   select * from students where name like "王%";

2、and并且
  例如: 显示math和chinese成绩大于90的学生信息;
    select * from students_test where math>=90 and chinese>=90;

3、or或者
   例如: 显示math或chinese成绩大于90的学生信息;
    select * from students_test where math>=90 or chinese>=90;
    
4、between x and y //在x与y之间的  
   例如: 显示math成绩在90~100之间的学生信息;
    select * from students_test where math between 90 and 100;

5、in在..里面，允许规定多个值
  例如: 显示1703和1704两个班级学生信息;
    select * from students where class="1704" or class="1703";
    select * from students_test where class in ("1703","1704‘’);

6、limit指定显示多少行，limit后面2个数字，用逗号隔开，第一个表示数字后。第二数字表示显示几行
  例如: 
    显示前面10行数据:
      select * from students limit 10;
    显示4到7行的数据:
      select * from students limit 3,4;
    按chinese排序，显示4,5行数据:
      select * from students limit 3,2;
    显示english成绩在70~90之间的学生信息，显示开头2行:
      select * from students where english between 70 and 90 limit 0,2;
    
7、逻辑运算符
  > 大于
  < 小于
  >= 大于等于
  <= 小于等于
  =  等于
  != 不等于

 8、算术运算符
  + 加法
  - 减法
  * 乘法
  / 除法
  % 取余

• 其他（聚合函数）

1、count() 统计数量
  select class,count(class) from students group by class;
2、avg()    求平均分
  select avg(math) from students;
3、sum() 求和
  select sum(math) from students;
4、max()最大值
 select max(math) from students;
5、min()最小值
 select min(math) from students;
6、distinct()  去重复
 select distinct(goods_number) from students ;
7、order by 排序
 select * from students order by id;    // 默认是升序
 select * from students order by id desc;  // 降序
8、group by  分组
 select class,count(class) from students group by class;
9、having 表示条件(类似where)
  虚拟字段是不可以放在where后面的，例如where avg(math)其中math是实际字段，但avg(math)是求出来的平均分是虚拟字段；
  需要进行条件处理的虚拟字段，可以放在having后面；

练习
1.创建student表
CREATE TABLE student ( id INT(10) NOT NULL PRIMARY KEY , name CHAR(20) NOT NULL, sex CHAR(4), birth YEAR, department CHAR(20), address CHAR(50) );
2.向student表插入记录的INSERT语句如下：
INSERT INTO student VALUES( 901,'张老大', '男',1984,'计算机系', '北京市海淀区'); INSERT INTO student VALUES( 902,'张老二', '男',1987,'中文系', '北京市昌平区'); INSERT INTO student VALUES( 903,'张三', '女',1991,'中文系', '湖南省永州市'); INSERT INTO student VALUES( 904,'李四', '男',1993,'英语系', '辽宁省阜新市'); INSERT INTO student VALUES( 905,'王五', '女',1990,'英语系', '福建省厦门市'); INSERT INTO student VALUES( 906,'王六', '男',1989,'计算机系', '湖南省衡阳市'); INSERT INTO student VALUES( 907,'老七', '男',1991,'计算机系', '广东省深圳市'); INSERT INTO student VALUES( 908,'老八', '女',1990,'英语系', '山东省青岛市');
4.查询student表的第2条到4条记录
5.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息
6.从student表中查询计算机系和英语系的学生的信息
7.从student表中查询年龄24~26岁的学生信息
8.从student表中查询每个院系有多少女生
9.把张老大的出生日期修改为1988，院系修改成中文系
10.把所有学生的年纪增加5岁；
练习
1.创建score表
CREATE TABLE score ( id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT , name CHAR(20) NOT NULL , c_name CHAR(20) , grade INT(10) );
2.向score表插入记录的INSERT语句如下：
INSERT INTO score VALUES(NULL,'张老大', '计算机',98); INSERT INTO score VALUES(NULL,'张老大', '英语', 80); INSERT INTO score VALUES(NULL,'张老二', '计算机',65); INSERT INTO score VALUES(NULL,'张老二', '中文',88); INSERT INTO score VALUES(NULL,'张三', '中文',95); INSERT INTO score VALUES(NULL,'李四', '计算机',70); INSERT INTO score VALUES(NULL,'李四', '英语',92); INSERT INTO score VALUES(NULL,'王五', '英语',94); INSERT INTO score VALUES(NULL,'王六', '计算机',90); INSERT INTO score VALUES(NULL,'王六', '英语',85); INSERT INTO score VALUES(NULL,'老七', '计算机',98);
3.查询李四的考试科目（c_name）和考试成绩（grade）
4.查询计算机成绩低于95的学生信息
5.计算每个考试科目的平均成绩
6.计算每个学生的总成绩
7.查询每个科目的最高分与平均分
8.将计算机考试成绩按从高到低进行排序