python数据库进阶

第1节 MySQL基础

 

一，说明

1，认识MySQL与创建用户

MySQL是最流行的关系型数据库管理系统之一，由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关联数据管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
​
- 关系型数据库：采用关系模型来组织数据的数据库
- 关系：一张二维表，每个关系都有一个关系名，就是表名
- 模型：行和列（二维），具体指字段跟字段信息

2，关系型数据库和非关系型数据库的区别

• 关系型数据库通过外键关联来建立表与表之间的关系，

• 非关系型数据库通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定

       比如 有一个学生的数据：
​
             姓名：张三，性别：男，学号：12345，班级：二年级一班
​
     还有一个班级的数据：
​
           班级：二年级一班，班主任：李四
​
# 关系型数据库中，我们创建学生表和班级表来存这两条数据，并且学生表中的班级存储的是班级表中的主键。
​
# 非关系型数据库中，我们创建两个对象，一个是学生对象，一个是班级对象，

3，常见数据库

数据库类型	特性	优点	缺点
关系型数据库SQLite、Oracle、mysql	1、关系型数据库，是指采用了关系模型来组织数据的数据库；2、关系型数据库的最大特点就是事务的一致性；3、简单来说，关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。	1、容易理解：二维表结构是非常贴近逻辑世界一个概念，关系模型相对网状、层次等其他模型来说更容易理解；2、使用方便：通用的SQL语言使得操作关系型数据库非常方便；3、易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率；4、支持SQL，可用于复杂的查询。	1、为了维护一致性所付出的巨大代价就是其读写性能比较差；2、固定的表结构；3、高并发读写需求；4、海量数据的高效率读写；
非关系型数据库MongoDb、redis、HBase	1、使用键值对存储数据；2、分布式；3、一般不支持ACID特性；4、非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合。	1、无需经过sql层的解析，读写性能很高；2、基于键值对，数据没有耦合性，容易扩展；3、存储数据的格式：nosql的存储格式是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，而关系型数据库则只支持基础类型。	1、不提供sql支持，学习和使用成本较高；2、无事务处理，附加功能bi和报表等支持也不好；

注1：数据库事务必须具备ACID特性，ACID是Atomic原子性，Consistency一致性，Isolation隔离性，Durability持久性。

注2：数据的持久存储，尤其是海量数据的持久存储，还是需要一种关系数据库。

---

 

二，登录MySQL

不区分大小写，但是系统默认大写为系统代码，小写为程序员代码

1，通过root用户进入数据库

mysql -uroot -ppassword    # 登录数据库
exit     # 退出
\q       # 退出

2，创建用户名及密码

# 创建用户三个步骤
create user 'lucky'@'%' identified by 'password'; # 创建用户名（%表示远程连接，可用此用户名登陆）
grant all on *.* to 'lucky'@'%';    # 赋予所有权限
flush privileges;                   # 使权限生效
​
# 此时以root用户登陆，登录lucky用户：
\q                                  # 退出root
mysql -ulucky -ppassword;           # 登录lucky用户
select user();                      # 查看当前用户
select database();                  # 查看当前位于哪个数据仓库

3，创建数据库

show databases;       # 查看当前有哪些数据库
create database mydb; # 创建数据库（数据库已存在会报错，如果没有，则创建）
create database if not exists mydb;# 数据库已存在不会报错，如果没有会创建
​
# 对于系统内部的，默认的数据仓库，不需要也不能去修改
drop database mydb;   # 删除数据库
show databases;
create database if not exists mydb;
use mydb;             # 进入数据库
select database();

4，创建数据表

create table student(        # student为创建表的name 
    id int，                 # id 为字段编号，int为其数据类型
   name varchar(20)，       # name为字段名，varchar为类型，20为长度
   );                       # 建表，分号结尾
show tables;                 # 查看当前数据仓库内有哪些表格

5，查看数据表结构

desc student;               # 查看student数据表的结构
show create table student;  # 查看创建表时有哪些命令
(drop table student;        # 删除表格) 

6，常用方法

# 增
​
# 方法一
insert into student (id,name) value (1,'张三'); # 向数据表内增加内容into 可以省略
insert into student value(2,'李四');    # ()中的字段名可写可不写,默认按顺序添加
insert into student value(3)          # 也可只插入id
insert into student values(3,'王五'),(4,'赵六')
​
select * from student;   # 查询当前数据表
​
# 方法二
insert student set id=5,name='钱七';
​
-------------------------------------------------------------
​
# 查
​
select * from student;                # 查看student
select * from student where id<=4;    # 查看student 并且id小于4的内容
select name from student where id<=4; # 查看student 内的name 段，并且id小于4的内容
​
-------------------------------------------------------------
​
# 改
​
update student set name='name1' where id=5;# 将id=5的那一行的name 改为'name1'
                                           # 如果不加where 限制条件，则表中的name全被更改
                                          
------------------------------------------------------------                                      
# 删
​
delete from student where name='钱七';     # 删除所有name=钱七的数据

7，mysql数据类型

数据类型	代表内容	事例
int	整型	id int
varchar(20)	指定长度，最多65535个字符。变长（超出会自动截掉）	name varchar(20)（插入20个字符）
char(4)	指定长度，最多255个字符。定长	sex char(4)（不管插入几个字符都会占4个）
double(4,2)	双精度浮点型，m总个数，d小数位	price double(4,2)
text	可变长度，最多65535个字符	detail text
datetime	日期时间类型YYYY-MM-DD HH:MM:SS	dates datetime
enum('good','not good')	枚举，在给出的value中选择	ping enum('good','not good')

• 流程

creat table tb2(
id INT,
name VARCHAR(20),
sex CHAR(4),
price DOUBLE(4,2),
detail TEXT,
datae DATATIME,
ping ENUM('好评','差评')
);
insert intotb2 value(1,'裤子','男',20.0,'这条裤子超级好！',now(),'好评')；

 

三、小结

1，常用方法

功能	用法
增	insert [into] student (id,name) value (1,'王一凡');	增的第一个语法
	insert into tanzhou value(2,'name');	()中的字段名可写可不写,默认按顺序添加
	insert into student value(3)	也可只插入id
	insert into tanzhou set id=4,name='name1';	增的第二个语法
查	select * from student;	查看student
	select * from student where id<4;	查看student 并且id小于4的内容
	select name from studen where id<4;	查看student 内的name 段，并且id小于4的内容
改	update student set name='name1' where id=5;	将id=5的那一行的name 改为'name1'
	update tanzhou set name='name1'	如果不加where 限制条件，则表中的name全被更改
删	delete from student where name='钱七';	删除所有name=钱七的数据

2，常用命令

功能	用法	注释
登录数据库	mysql -uroot -ppassword
退出	exit 或 \q
创建用户	create user 'lucky'@'%' identified by 'password';	%表示远程连接，可用此用户名登陆
赋予所有权限	grant all on *.* to 'lucky'@'%';
使权限生效	flush privileges;	flush写入
查看当前用户	select user();
查看当前位于哪个数据库	select database();
查看当前有哪些数据库	show databases;
创建数据库	create database mydb; 或 create database [if not exists] mydb;	创建以mydb为名建立一个数据库,[]为可选参数
删除数据库	drop database mydb;
进入数据库	use mydb;
创建数据表	create table [if not exists] student(<br />id int ，<br />name varchar(10)<br />);	student为创建表的name <br /> id 表示(元组)字段编号，数据为int型 <br /> name为字段名，varchar为类型，20为长度
查看当前数据仓库内有哪些表	show tables;
查看表的结构	desc student;
查看创建表时的命令	show creat table tanzhou;
删除表格	DROP TABLE tablename;

第2节 表约束，表关系

 

---

 

一、表约束：

1.非空约束 ((not null))

例子：
create table tb1(
id int not null,     #非空约束字段，insert 的时候，必须添加字段，不能省略，空字符不等于null
name varchar(20) not null  
);

# 如果在创建表时没有添加非空约束
# 后续手动添加非空约束（必须这个字段，没有NULL值）
alter table tb1      # 修改表结构
modify id int not null;
​
# 取消非空约束
alter table tb1
modify id int；

 

2.唯一约束 (unique key)

• 确保字段中的值的唯一

例子：
create table tb2(
id int unique key,  # 防止id相同
)

# 如果在创建表时没有添加非空约束
# 后续手动添加唯一约束
alter table tb1      # 修改表结构
add unique key(name);
​
# 删除唯一约束
alter table tb1
drop key name；

 

3.自增长 (auto_increment)

• auto_increment:自动编号，一般与主键组合使用。一个表里只有一个自增默认情况下，起始值为1，每次的增量为1。

create table stu3(
id int primary key auto_increment, # 自增长,输错时在终端不会占用数据，在其他连接工具如pycharm中就会占用一条数据
name varchar(10)
)auto_increment 100
​
insert into stu3 value('a'); 
insert into stu3(name) value('bc'); 
select * from stu3;

# 如果在创建表时没有添加自动增长
# 后续手动添加自动增长
alter table stu3      # 修改表结构
modify id int auto_increment;
​
# 删除自动增长
alter table stu3
modify id int；

 

4.默认约束 (default)

• 初始值设置，插入记录时，如果没有明确为字段赋值，则自动赋予默认值

create table stu4(
id int primary key auto_increment,
name varchar(20) not null,
age int not null default 18      # 默认约束
);
​
insert into stu4 value(1,'张三','18');
insert into stu4 value(2,'李四','');        #会报错
insert into stu4(id,name) value(2,'玲玲');  #设置要输入的字段值，age可为空字符串
insert into stu4 value(6,'',19);  #空字符串 != null

# 如果在创建表时没有添加默认约束
# 后续手动添加默认约束
alter table stu4      # 修改表结构
modify age int default 20;
​
# 删除默认约束
alter table stu4
modify age int；

 

5.主键约束 (primary key)

• 主键作用：可以唯一标识一条数据，每张表里只能有一个主键

• 主键特性：非空且唯一，表没有主键时，第一个出现的非空且唯一

create table stu2(
id int primary key,  # id是主键，主键不能为空
name varchar(10)
);
​
desc stu2            # 此时可以看到id的key变为pri，且Null下变为NO

# 如果在创建表时没有添加主键约束
# 后续手动添加主键约束
alter table stu2      # 修改表结构
add primary key(id)；
​
# 删除主键约束
alter table stu2      # 修改表结构
drop primary key;

 

6.外键约束 (foreign key)

• 保持数据的一致性、完整性，实现一对一或一对多关系。

• 外键必须关联到键上面去，一般情况是，关联到另一张表的主键。

• 因为一个表只在一类信息。用外键来做参照，保证数据的一致性，可以减少数据冗余。

# 表a
create table a(
a_id int primary key auto_increment,
a_name varchar(20) not null
);
​
# 表b
create table b(
b_id int primary ke,
b_name varchar(20)not null,
fy_id int not null,
constraint AB_id foreign key(fy_id) references a(a_id)  # 外键约束定义
);
​
# 说明：
1，通过(fy_id)关联到(a_id)中，然后重新命名为 AB_id（其中constraint 可不写）
2，外键约束定义，B表中的fy_id，只能添加a_id中已有的数据。A表中的a_id 被参照的数据，不能被修改和删除。

# 如果在创建表时没有添加外键约束
# 后续手动添加外键约束
alter table b      # 修改表结构
constraint AB_id foreign key(fy_id) references a(a_id)；
​
# 删除外键约束
alter table stu2      # 修改表结构
drop foreign key AB_id;

---

 

二、表关系

• 单位表，学生表，课程表，三张表，多对多

• 一对一为主键和主键关联，一对多是主键和外键关联，加中间表就是多对多

1，一对一：

• 用外键的方式，把两个主键关联

• 举例，学生表中有学号、姓名、学院，但学生还有写比如电话，家庭地址等信息，这些信息不会放在学生表中，会新建一个学生的详细信息表来储存。

• 这时的学生表和学生的详细信息两者的关系就是一对一的关系，因为一个学生只有一条详细信息，**用主键加主键的方式**来实现这种关系

# 建立学生表
create table student(
id int primary key,
name varchar(19) not null
);
insert into student value(1);
​
--------------------------------------------------------------------------------
​
#建立详细学生表
create table student_details(
id_x int primary key,
sex varchar(20) not null,
age int,
address varchar(20) comment'家庭住址',
parents varchar(20),
home_num varchar(20),
foreign key(id_x) references student(id)
);
​
insert into student_details values(1,'赵六')  
​
# 说明：
1，此时student中的id1，对应student_details中的id1。。student中没有插入的name,对应student_details中插入的赵六
2，学生表和学生详情表通过student.id 和student_deatails.id_x关联，形成一对一关系,只能插入主键相同的数据

 

2，一对多：

• 通常情况下，学校中一个学院可以有很多学生，而一个学生只属于某一个学院

• 学院与学生之间的关系就是一对多的关系，通过**外键关联**来实现这种关系

# 创建学院表
create table department(
id int primary key,
subject varchar(10) not null
);
​
insert into department values(001,'Python'),(002,'JAVA');
​
​
#创建学生表                                 
create table vip(
id_v int primary key,          # 学生的id
name_v varchar(10) not null,   # 学生名字
id_w int not null              # 所属学院id，只能添加已有的学院id
constraint SD_id foreign key(id_w) references department(id)  # 外键
);
​
insert into vip value(001,'王五'，1);  # (001(id_v), 名字，1(department表的 ID）)

 

3，多对多：

• 举例，学生要报名选修课，一个学生可以报名多门课程，一门课程有很多学生报名，那么学生表和课程表两者就形成了多对多关系

• 对于多对多关系，需要创建中间表实现

#创建学生表
create table sst(
id int primary key auto_increment,
name varchar(10) not null
);
​
​
#创建选课表（中间表）
create table course(
s_id int primary key,         #记录学生的id
course_id int,                #用来记录课程id
primary key(s_id,course_id)   #联合主键（此组合为唯一的，防止一个id重复报一个课程）
foreign key(s_id) references student(id_v),  #关联学生id 外键
foreign key(course_id) references(course_id) #关联课程id 外键
name varchar(10) not null,
);
​
insert into cours values(001,'py'),(002,'java'),(003,'japan');
insert into ssh values(11,'a'),(22,'b'),(33,'c');
insert into course values(22,3),(11,2);    # 用中间表来多对多
select * from course;

 

4,作业

（1）建立选课系统中的5张表：（学院表，学生表，学生详情表，课程表，选课表），并每张表插入4条数据。

（2）用文字描述：这5张表之间，对应关系。并说明如何实现这个对应关系。

 

第3节 MySql 基础三

 

 

补充 alter 命令，修改表结构命令

 

create table tb1(
id int,
name char(4));
#修改表结构，增加字段 年龄
alter table tb1 add age int first; #增加年级字段到第一列;
alter table tb1 drop age ; #删除年龄字段;
alter table tb1 add age int after id;#在id后面增加一个字段age,age在第个字段;
​
#修改已经存在字段的类型
alter table tb1 modify age char(4)  #把age字段修改成char类型;
​
#修改列名（字段名）
alter table tb1 change age sex char(4); #将age字段修改为sex;
​
#修改表名 
alter table tb1 rename tb2 #将表tb1修改为tb2
​

第4节 MySQL 单表查询 子查询 关联查询

 

一、MySQL单表查询

mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| choose         |
| course         |
| details       |
| student       |
| tanzhou       |
+----------------+
mysql> desc tanzhou;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type       | Null | Key | Default | Extra         |
+---------+-------------+------+-----+---------+----------------+
| tz_id   | int(11)     | NO   | PRI | NULL   | auto_increment |
| tz_name | varchar(10) | NO   |     | NULL   |               |
+---------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> select * from tanzhou;
+-------+--------------+
| tz_id | tz_name     |
+-------+--------------+
|     1 | 软件学院     |
|     2 | 语言学院     |
|     3 | 市场营销     |
|     4 | 养殖学院     |
+-------+--------------+
4 rows in set (0.00 sec)
mysql> desc student;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type       | Null | Key | Default | Extra         |
+---------+-------------+------+-----+---------+----------------+
| st_id   | int(11)     | NO   | PRI | NULL   | auto_increment |
| st_name | varchar(10) | NO   |     | NULL   |               |
| ts_id   | int(11)     | NO   | MUL | NULL   |               |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> select * from student;
+-------+-----------+-------+
| st_id | st_name   | ts_id |
+-------+-----------+-------+
|     1 | 三花     |     1 |
|     2 | 贾梅     |     1 |
|     3 | 余玲玲   |     3 |
|     4 | 王涛     |     2 |
+-------+-----------+-------+
4 rows in set (0.00 sec)
mysql> desc course;
+--------+-------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra         |
+--------+-------------+------+-----+---------+----------------+
| c_id   | int(11)     | NO   | PRI | NULL   | auto_increment |
| c_name | varchar(10) | YES |     | NULL   |               |
| tzc_id | int(11)     | NO   | MUL | NULL   |               |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> desc choose;
+-------+---------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| sc_id | int(11) | NO   | PRI | NULL   |       |
| cc_id | int(11) | NO   | PRI | NULL   |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
​
1、查询所有记录
select * from student;#查询student表中的所有内容；
2、查询选中列记录
select s_name from student;#查询表中所在字段的内容；
3、查询指定条件下的记录
select s_name from student where s_id>4;#查询学号s_id 大于4的学生的姓名；
4、查询后为列取别名
select s_name as 姓名 from student; #将s_name 字段查询并命名为姓名(as可加可不加)
select s_name 姓名 from student; #可以省略 as 在做联合查询时要加上别名，方便区分不同的表
5、模糊查询
select * from student where s_name like '三%';#查询姓名中带有 '三'的信息
select * from student where s_name like '%三';#查不出
select * from student where s_name like '三_';
select * from student where s_name like '三__'#两条下划线表示两个字符
​
6、排序：order by (:ASC 升序（默认升序))
select * from student order by ts_id asc; #升序，默认
select * from student order by ts_id desc;#降序

转载于:https://www.cnblogs.com/wangshixiong576422/p/9710535.html