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 drop primary key;
  
  

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

  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.将计算机考试成绩按从高到低进行排序

作者：西门奄
链接：https://www.jianshu.com/u/77035eb804c3
來源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。