数据库的安装

# linux 安装
# 添加
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 语言分类

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

常用命令

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

显示当前服务器中所有数据库：
mysql> show databases;

选择数据库：
use 数据库名称;

创建数据库（指定字符集）：
create database 数据库名称 charset=utf8;

删除数据库：
drop database 数据库名称;

查看数据库中的表：
show tables;

建表：
create table 表名(
字段1 字段类型[列级别约束条件][默认值] comment '注释'，
    字段2 字段类型[列级别约束条件][默认值]，
    ….
    字段n 字段类型[列级别约束条件][默认值]
    [表级别约束条件]
  ) enigne=innodb, charset=utf8；

查看建表语句：
show create table 表名;


删表：
drop table 表名;

修改表名：
alter table 旧表名 rename 新表名;

查看表结构：
desc 表名;

新增字段：
alter table 表名 add 新字段名  字段属性 【not null】

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

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

插数：
insert into 表名(字段1, 字段2 , ...) values(值1,, 值2);
一次性写入多条数据
格式: 
  insert into 表名(属性1，属性2..) values(值1.1，值2.1...),(值1.2，值2.2,....);

更新数据：
update 表名 set 字段=值 where 条件;

删除数据：
delete from 表名 where 条件;

查询数据：
select 列名 from 表名 【where子句】  -->   【group by子句】 --> 【having 子句】 --> 【order by  子句】
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、算术运算符
  + 加法
  - 减法
  * 乘法
  / 除法
  % 取余

9、聚合函数
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.数字数据类型
- 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

实体完整性

数据的完整性
作用：保证用户输入的数据保存到数据库中是正确的。
实质：创建表的时候给表中的字段添加约束。

实体完整性
实体：表中的一行或者一条记录代表一个实体
实体完整性的作用：标识每一行数据不重复
约束类型：主键约束【primary key】、唯一约束【unique】、自动增长列【auto_increment】

主键约束【primary key】
特点：数据唯一，且不能为null;
它的值用来唯一标识表中的某一条记录;
场景：在多个表的关联关系中;

// id设置为主键
create table student(id int, name varchar(50), primary key(id));
唯一约束【unique】
作用：在非主键列中不能输入重复的值;
create table student(id int primary key, name varchar(50) unique);
primary key和unique之间的区别?

二者都强调的是唯一性
在同一个表中，只能出现一个primary key，可以出现多个unique
primary key不允许为null，但是unique是允许的
自动增长【auto_increment】
给主键添加添加自动增长性，字段只能是整数类型；
场景: 一般添加给主键；
create table student(id int primary key auto_increment, name varchar(50) unique);

域完整性

域完整性
作用：限制单元格数据的正确性，域代表当前单元格；
约束类型：数据类型、非空约束【not null】、默认值约束【default】

数据类型

数字类型：int float doule decimal
日期类型：date
字符串类型：char varchar
非空约束【not null】

create table student( id int primary key auto_increment, name varchar(50) not null);
非空，即表示插入数据时不能为空；为空时，数据库立即报错。

默认值约束【default】


create table student(id int primary key auto_increment, name varchar(50), addr varchar(50) default "shenzhen")

// 不使用默认值
insert into student (id,name, addr) values(1,'aaa','guagnzhou');
// 使用默认值
insert into student(id,name, addr) values(2,'bbb',default);
insert into student(id,name) values(3,'ccc');

引用完整性

外键约束【foreign key】
注意: 添加外键必须先有主键，主键和外键的类型必须保持一致；
作用: 将两个甚至多个毫无关联的表产生联系
备注: 一张表中可以有多个外键
例如: 学生表，成绩表

// 创建学生表
create table student(stu_id int primary key, s_name varchar(50)) charset=utf8;

// 添加学生数据
insert into student  values(1001, '张三');
insert into student  values(1002, '李四');
insert into student  values(1003, '王五');
insert into student  values(1004, '赵六');
insert into student  values(1005, '田七');
insert into student  values(1006, '王八');
insert into student  values(1007, '老九');

// 创建成绩表
// 外键stu_id，外键约束对应的student的stu_id
create table score(sco_id int primary key, score int, stu_id int,foreign key(stu_id) references student(stu_id)) charset=utf8;
insert into score values(1, 89, 1001);
insert into score values(2, 97, 1002);
insert into score values(3, 99, 1003);
insert into score values(4, 82, 1004);
insert into score values(5, 86, 1005);

多表查询

表与表之间的关系

一对一, 通过嵌套的方式
一对多(多对一), 添加外键
多对多, 单独创建一张新的表
合并结果集
作用：将两个select语句的查询结果合并到一起;
两种方式：union去除重复记录【并集】、union all获取所有的结果；

// 创建表A和表B
create table A( name varchar(10), score int );
create table B( name varchar(10), score int );

// 插入数据
insert into A values('a',10),('b',20),('c',30);
insert into B values('a',10),('d',40),('c',30);

// union合并结果集
select * from A
union
select * from B;
+------+-------+
| name | score |
+------+-------+
| a    |    10 |
| b    |    20 |
| c    |    30 |
| d    |    40 |
+------+-------+

// union all合并结果集
select * from A
union all
select * from B;
+------+-------+
| name | score |
+------+-------+
| a    |    10 |
| b    |    20 |
| c    |    30 |
| a    |    10 |
| d    |    40 |
| c    |    30 |  
+------+-------+


// 问题: 如果遇到列数不相同的情况
create table C( name varchar(10), score int, age int);
insert into C values('a',100,29),('e',20,18),('c',300,10);
insert into C values('a',10,29),('e',20,18),('c',30,10);

select * from A
union
select name,score from C;
+------+-------+
| name | score |
+------+-------+
| a    |    10    |
| b    |    20   |
| c    |    30   |
| a    |    100 |
| e    |    20   |
| c    |    300 |
+------+-------+
注意：被合并的两个结果，字段、字段类型必须相同;

内连接inner join
内连接：查询左右表都有的数据，不要左右中空的那一部分；
内连接：左右连接的交集；
语法:
select 列1,列2,列N from
tableA 
inner join 
tableB
on tableA.列 = tableB.列  (此处表连接成一张大表，完全当成一张普通表看)
where,having,group by.... (条件照常写)

例如:
查询每个学生的具体信息
select 
tableA.*,tableB.score
from 
student tableA 
inner join 
score tableB
on 
tableA.stu_id=tableB.stu_id;

select 
tableA.*,score
from 
student tableA 
inner join 
score tableB
on 
tableA.stu_id=tableB.stu_id;

左连接left join
左连接1: 得到的是A的所有数据，和满足某一条件的B的数据;
左连接2: 得到的是A中的所有数据减去"与B满足同一条件 的数据"，然后得到的A剩余数据；
语法:
  select 
  列1,列2,列N 
  from
  tableA 
  left join 
  tableB
  on 
  tableA.列 = tableB.列  (此处表连接成一张大表，完全当成一张普通表看)
  where,having,group by.... (条件照常写)

  例如,左连接1:
  select 
  tableA.*,score
  from 
  student tableA 
  left join 
  score tableB
  on 
  tableA.stu_id=tableB.stu_id;

  例如,左连接2:
  select 
  tableA.*
  from 
  student tableA 
  left join 
  score tableB
  on 
  tableA.stu_id=tableB.stu_id
  where
  score is null;

右连接right join
右连接1: 得到的是B的所有数据，和满足某一条件的A的数据；
右连接2: 得到的是B中的所有数据减去 "与A满足同一条件的数据"，然后得到的B剩余数据;
语法:
select 
列1,列2,列N 
from
tableA 
right join 
tableB
on 
tableA.列 = tableB.列  (此处表连接成一张大表，完全当成一张普通表看)
where,having,group by.... (条件照常写)

例如:
select 
tableA.*,score
from 
student tableA 
right join 
score tableB
on 
tableA.stu_id=tableB.stu_id;
左连接：即以左表为基准，到右表找匹配的数据,找不到匹配的用NULL补齐；
推荐左连接来代替右连接，兼容性会好一些；

自然连接natural join
通过MySql自己的判断完成连接过程，不需要指定连接条件。MySql会使用表内的，相同的字段，作为连接条件。
select * from A natural join B;

找到表与表的对应关系;
如果多张表中有同一个属性名时必须标注是哪个表中的属性;

数据库备份和恢复

备份
生成SQL脚本，导出数据
语法:
$ mysqldump -u root -p  数据库名 > 生成sql脚本的路径

恢复
执行sql脚本，恢复数据。

前提: 必须先创建数据库【空的】
注意: 需要先登录数据库，然后进入指定的数据库，执行sql脚本
语法:
  mysql> source sql脚本的路径

MySQL与Python的交互

- 安装
    pip3 install pymysql

- 使用
import pymysql

# 链接数据库
db = pymysql.Connect(host='127.0.0.1', port=3306, user='root', password='123456', database='test08', charset='utf8')
# 数据库游标
cursor = db.cursor()

# 查询数据
db.begin()
cursor.execute("select * from students_info;")
db.commit()
# 获取所有数据
print(cursor.fetchall())
# 获取一个，根据下标取对应的数据
print(cursor.fetchall()[0])
# 注: 不能同时使用，因为游标会往后移动

# 插入数据
db.begin()
cursor.execute("insert into students_info values ('2000', '老李', '18', '男', '广东深圳', '1703', '89', '90', '81');")
db.commit()

# 更新数据
db.begin()
cursor.execute("update students_info set class='1807' where id=2000")
db.commit()

# 删除数据
db.begin()
cursor.execute("delete from students_info where id=2000")
db.commit()