day06-python数据库-mysql之基础

vlambda
2020-11-20

day06-python数据库-mysql之基础

SQL语言

SQL（Structured Query Language 即结构化查询语言），SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统，SQL语言由IBM开发。

SQL语言分为3种类型：

DDL语句：数据库定义语言：数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER。
DML语句：数据库操纵语言：插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT。
DCL语句：数据库控制语言：例如控制用户的访问权限GRANT、REVOKE。

创建数据库

数据库命名规则：

# 可以由字母、数字、下划线、＠、＃、＄# 区分大小写# 唯一性# 不能使用关键字如 create select# 不能单独使用数字# 最长128位# 区分大小写# 唯一性# 不能使用关键字如 create select# 不能单独使用数字# 最长128位create database db1

数据库相关操作

注意：数据库就相当于文件夹，表就相当于文件

数据库的增删改查

增：create database db1;删：drop database db1;改：alter database db1 charset utf8查：show databases;#查看所有的数据库 show create database db1;查看指定的数据库设置默认的utf8,在配置文件中：写上character_set_server = utf8use db2 #代表你鼠标双击切换目录select database() #查看你当前在哪个文件夹

表的增删改查

增：create table t1(id int,name char(10)) engine=innodb;删：drop table t1;改：alter table t1 add age int; alter table t1 modify name char(12);查：show tables; #查看所有表 show create table t1; #查看t1表 desc t1; #查看表结构 show create table t1\G; #查看表详细结构，可加\G select * from t1; #查看所有的表数据

操作文件的一行行内容（记录）

增：insert into db1.t1 values(1,'haiyan'),(2,'yaling'),(3,'xiaoxiao');  #如果t1不给参数，默认按照位置参数依次传参删：delete from t1 where id = 2; #对于清空记录有两种方式，但是推荐后者 delete from t1; truncate t1; #当数据量比较大的情况下，使用这种方式，删除速度快改：update t1 set name = 'SB' where id=3; update t1 set name= 'SB' where name = 'xiaoxiao'; alter table t7 modify id int primary key auto_increment;  # 修改id为主键并且自增查：select * from t1; #查看t1里所有的数据 select name from t1; #查看t1里所有的name select id,name from t1; #查看t1里所有的id,name

自增id的方法

create table t5(id int primary key auto_increment,name char(10));#create table t4(id int not null unique auto_increment,name char(10)); # (不空且是唯一的)#这个和上面的是一回事insert into xx(name) values ('haiyan1'),                            ('haiyan2'),                            ('haiyan3'),                            ('haiyan4'),                            ('haiyan5');

拷贝表结构

create table t7(id int,name char(10));create table t8 select * from t7; #拷贝表结果（如果有数据就把数据一起拷贝了）create table t8 select * from t5 where 1=2; #拷贝表结构，不拷贝表数据（条件为假时，查不到任何记录）alter table t7 modify id int primary key auto_increment; # 修改id为主键并且自增insert into t7(name) values ('egon1'), ('egon1'), ('egon1'), ('egon1');

创建账号

select user() #查看当前用户select * from mysql.user; 查看所有的用户# 创建账号 identifitycreate user 'test1'@'localhost' identified by '147852' # 名为test1的本机账号create user 'alex'@'%' identified by '123' #代表只要ip地址能拼通，那么所有的用户都可以远程登录alexcreate user 'susan'@'192.168.20.%' identified by '123' #创建远程账号，只要是192.168.20.？开头的ip都可以登录susan#如果你要远程登录alex的账户，那么客户端得这样登录 ：mysql -h192.168.20.97 -ualex -p123

数据库的权限操作

#insert ,select ,update,delete #有这么几个可以设置权限的操作，那么我们先以select为例吧。# 分四个级别：# 级别1：对所有的库，下的所有的表，下的所有的字段，*.*代表所有的库下的所有的表# 同意select权限开放，开放的是*.*的select权限开放给用户grant select on *.* to 'zhang'@'localhost' identified by '123'; #让创建用户的时候赋予权限# 级别2：对db1库，下的所有的表，下的所有的字段grant select on db1.* to 'wang'@'localhost' identified by '123';# 级别3：对表db1.t1，下的多有字段grant select on db1.t1 to 'li'@'localhost' identified by '123';# 级别4：对表db1.t1，下的id,name,字段grant select (id ,name) on db1.t1 to 'zhao'@'localhost' identifitied by '123';grant select (id ,name),update(name) on db1.t1 to 'zhao'@'localhost' identifitied by '123';# 修改完权限后要记得刷新权限flush privileges;# 删除权限：revoke select on *.* from 'zhang'@'localhost'revoke select on db1.* from 'wang'@'localhost'revoke select on db1.t1 from 'li'@'localhost'revoke select (id ,name),update(name) on db1.t1 from 'zhao'@'localhost'

解决乱码问题

#1. 修改配置文件[mysqld]default-character-set=utf8 [client]default-character-set=utf8 [mysql]default-character-set=utf8
#mysql5.5以上：修改方式有所改动[mysqld]character-set-server=utf8collation-server=utf8_general_ci[client]default-character-set=utf8[mysql]default-character-set=utf8
#2. 重启服务#3. 查看修改结果：show variables like '%char%'

数据库存储引擎

存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）

SQL 解析器、SQL 优化器、缓冲池、存储引擎等组件在每个数据库中都存在，但不是每个数据库都有这么多存储引擎。MySQL 的插件式存储引擎可以让存储引擎层的开发人员设计他们希望的存储层，例如，有的应用需要满足事务的要求，有的应用则不需要对事务有这么强的要求；有的希望数据能持久存储，有的只希望放在内存中，临时并快速地提供对数据的查询。一种类型表明的是一种存储方式。

存储引擎：就是表的类型，不同的类型就会对应不同的处理机制去处理它
事务：就是要么同时成功，要么同时不成功

使用存储引擎的方法

方法1：建表时指定存储引擎

1.create table t1(id int) engine = innodb # 会出现2个文件2.create table t2(id int) engine = myisam # 会出现3个文件（速度比上面的快），但是我们还是用innodb存储 3.create table t3(id int) engine = memory # 只有表结构没有表数据,是创建到内存中的4.create table t4(id int) engine = blackhole # 黑洞，吃数据（数据存进去就没有了，存进去就没有了）

方法2：在配置文件中指定默认的存储引擎

cat /etc/my.cnf[mysqld]default-storage-engine=INNODBinnodb_file_per_table=1

mysql的数据类型

存储引擎决定了表的类型，而表内存放的数据也要有不同的类型，每种数据类型都有自己的宽度，但宽度是可选的

1.数字(默认是有符号的)

整型

tinyint（小整型）：一个字节，数据类型用于保存一些范围的整数数值范围：有符号：-128 ～ 127，无符号：0～ 255，MySQL中无布尔值，使用tinyint(1)构造。格式：tinyint[(m)] [unsigned] [zerofill]
int（整型）：四个字节。数据类型用于保存一些范围的整数数值范围：有符号：-2147483648 ～ 2147483647，无符号：0～ 4294967295，格式：int[(m)][unsigned][zerofill]，注意：int的宽度指的是显示的宽度，与存储无关，
bigint（大整型）：八个字节，数据类型用于保存一些范围的整数数值范围：有符号：-9223372036854775808 ～ 9223372036854775807，无符号：0～ 18446744073709551615，格式：bigint[(m)][unsigned][zerofill]

小数

float：在位数比较短的情况下不精准（**** 数值越大，越不准确 ****）
double：在位数比较长的情况下不精准（**** 数值越大，越不准确 ****）
decimal：如果是小数，则推荐使用decimal，因为精准，内部原理是以字符串的形式去存

 # 先创建一个数据库： 1 create datdabase test; 2 -----------验证1：int，tinyint,bigint---- 3 create table t1(id tinyint); 4 create table t1(id int); 5 create table t1(id bigint); #如果数字比较大的时候就用bigint 6 # 1.如果没有指定符号。默认的是有符号的 7 # 2.insert into t1(-129) #就会报错了，因为范围是-128~127 8 # 3.将有符号的修改为无符号的：alter table t1 modify unsigned;(注意，如果里面有值了，得把里面的值清空了再修改) 9 # 4.alter table t2 modify id int(10) zerofill; 如果显示不够，就用zerofill填充10 # 5.宽度：跟存的没有关系,指的是显示的宽度11 13 ----------验证2：float，double------14 create table t3(salary float(5,2)) #5代表salary总共多宽，2代表小数点后保留2位，那么整数部分有3位15 insert into t3 values(3.725454);16 insert into t3 values(-3.725454);17 insert into t3 values(1111.725454); #像这个就会报错了18 insert into t3 values(111.725454);19 20 # bit类型了解就好了21 # bit类型:代表二进制的类型22 ----------验证3：bit--------23 create table t3(x bit);24 insert into t3 values(0),(1);25 insert into t3 values(0),(2)); #只能存二进制的，这样的话就会报错26 select * from t3;

2.字符

char：简单粗暴，不够就用空格凑够固定长度存放起来，浪费空间，但是存储速度快(牺牲空间，提高速度)

varchar：你有几个就存几个，精准，计算出待存放数据的长度，节省空间，存取速度慢（牺牲速度，提高效率）

1.-----------------create table t6(name char(4));insert into t6 values('alexsb');insert into t6 values('欧德博爱');insert into abc values('艾利克斯a');2.-------------create table t7(x char(5),y varchar(5));insert into t7 values('sff','aaaaa');select char_length(x),char_length(y) from t7; # 查看字符长度set sql_mode='pad_char_to_full_length';   #打回原形insert into t7 values('你好啊','好好好！');#utf-8里面一个汉字代表三个字节，那'你好啊'就代表九个，加上两个空格就是11个字节
select length(x),length(y) from t7; 查看字节长度

3.日期

作用：存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等，有下面几种类型：

datatime：2017-09-06 10:30:22
data：2017-09-06
time：10:30:22
year：2017
timeatamp：和datatime是一样的，就是支持的范围比datatime的大

---------------验证：日期类型-------create table stu(id int,name char(5),born_data date,born_year year,reg_time datetime,class_time time);
insert into stu values(1,'ao',now(),now(),now(),now());insert into stu values(1,'xiao','2017-09-06','2017','2017-09-06 10:39:00','08:30:00');
#了解insert into stu values(1,'alex','2017-09-06',2017,'2017-09-06 10:39:00','08:30:00');insert into stu values(1,'alex','2017/09/06',2017,'2017-09-06 10:39:00','08:30:00'); 没有-的可以不用加引号insert into stu values(1,'alex','20170906',2017,'20170906103900','083000'); 也可以吧符号取了连写
============注意啦，注意啦，注意啦===========1. 单独插入时间时，需要以字符串的形式，按照对应的格式插入2. 插入年份时，尽量使用4位值3. 插入两位年份时，<=69，以20开头，比如50, 结果2050  >=70，以19开头，比如71，结果1971MariaDB [db1]> create table t12(y year);MariaDB [db1]> insert into t12 values  -> (50), -> (71);MariaDB [db1]> select * from t12;+------+| y |+------+| 2050 || 1971 |+------+

在实际应用的很多场景中，MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，但在某些情况下，会展现出他们各自的优劣。下面就来总结一下两种日期类型的区别。

DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。
DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。在mysql服务器，操作系统以及客户端连接都有时区的设置。
DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。
DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP），如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。

4.枚举与集合

字段的值只能在给定范围中选择，如单选框，多选框

enum枚举：规定一个范围：这个范围可以有多个，但是为该字段传值时，只能取规定范围内的其中一个。enum如果你不传值，默认是第一个值，或者为NUll

set集合：规定一个范围：这个范围可以有多个，但是为该字段传值时，可以取规定范围内的一个或多个

----------枚举和集合-----------create table stu1(id int primary key auto_increment,name char(5),sex enum('male','female'), #enum 代表枚举类型hobbies set('eat','play','study','coding') #set 代表集合类型);insert into stu1(name,sex,hobbies) values('haiyan','none','dsfdg');select * from stu1; #如果设置了sex是枚举类型，就的从设定的里面选其中的一个存insert into stu1(name,sex,hobbies) values('haiyan','female','play,study');select * from stu1;  #如果设置了hobbies是集合类型，就得从设定的里面选其中一个或者多个值来存储

mysql的完整性约束

约束条件与数据类型的宽度一样，都是可选参数

作用：用于保证数据的完整性和一致性

主要分为：

PRIMARY KEY (PK)：标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK)：标识该字段为该表的外键
NOT NULL：标识该字段不能为空
UNIQUE KEY (UK)：标识该字段的值是唯一的
AUTO_INCREMENT：标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT：为该字段设置默认值
UNSIGNED：无符号
ZEROFILL：使用0填充
FOREIGN KEY (FK)：标识该字段为该表的外键
NOT NULL：标识该字段不能为空
UNIQUE KEY (UK)：标识该字段的值是唯一的
AUTO_INCREMENT：标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT：为该字段设置默认值
UNSIGNED：无符号
ZEROFILL：使用0填充

说明：

1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值。

2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值。

3. 是否是key

主键 primary key
外键 foreign key
索引 (index,unique...)

not null 、null和default

not null：不可空

null：可空

default：默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

create table tb1(id int not null defalut 2 ,num int not null)

unique约束（唯一性约束）

单列唯一

-----1.单列唯一---------create table t2(id int not null unique,name char(10));insert into t2 values(1,'egon');insert into t2 values(1,'alex');#上面创建表的时候把id设置了唯一约束。那么在插入id=1，就会出错了

多列唯一

-----2.多列唯一---------#255.255.255.255create table server(id int primary key auto_increment,name char(10),host char(15), #主机ipport int, #端口constraint host_port unique(host,port) #constraint host_port这个只是用来设置唯一约束的名字的，也可以不设置默认就有了);insert into server(name,host,port) values('ftp','192.168.20.11',8080);insert into server(name,host,port) values('https','192.168.20.11',8081); #ip和端口合起来唯一select * from server;

primary key （主键约束）

primary key字段的值不为空且唯一

一个表中可以：单列做主键、多列做主键（复合主键），但一个表内只能有一个主键primary key

 1 ============单列做主键=============== 2 #方法一：not null+unique 3 create table department1( 4 id int not null unique, #主键 5 name varchar(20) not null unique, 6 comment varchar(100) 7 ); 8  9 mysql> desc department1;10 +---------+--------------+------+-----+---------+-------+11 | Field | Type | Null | Key | Default | Extra |12 +---------+--------------+------+-----+---------+-------+13 | id | int(11) | NO | PRI | NULL | |14 | name | varchar(20) | NO | UNI | NULL | |15 | comment | varchar(100) | YES | | NULL | |16 +---------+--------------+------+-----+---------+-------+17 rows in set (0.01 sec)18 19 #方法二：在某一个字段后用primary key20 create table department2(21 id int primary key, #主键22 name varchar(20),23 comment varchar(100)24 );25 26 mysql> desc department2;27 +---------+--------------+------+-----+---------+-------+28 | Field | Type | Null | Key | Default | Extra |29 +---------+--------------+------+-----+---------+-------+30 | id | int(11) | NO | PRI | NULL | |31 | name | varchar(20) | YES | | NULL | |32 | comment | varchar(100) | YES | | NULL | |33 +---------+--------------+------+-----+---------+-------+34 rows in set (0.00 sec)35 36 #方法三：在所有字段后单独定义primary key37 create table department3(38 id int,39 name varchar(20),40 comment varchar(100),41 constraint pk_name primary key(id); #创建主键并为其命名pk_name42 43 mysql> desc department3;44 +---------+--------------+------+-----+---------+-------+45 | Field | Type | Null | Key | Default | Extra |46 +---------+--------------+------+-----+---------+-------+47 | id | int(11) | NO | PRI | NULL | |48 | name | varchar(20) | YES | | NULL | |49 | comment | varchar(100) | YES | | NULL | |50 +---------+--------------+------+-----+---------+-------+51 rows in set (0.01 sec)

 1 ==================多列做主键================ 2 create table service( 3 ip varchar(15), 4 port char(5), 5 service_name varchar(10) not null, 6 primary key(ip,port) 7 ); 8  9 10 mysql> desc service;11 +--------------+-------------+------+-----+---------+-------+12 | Field | Type | Null | Key | Default | Extra |13 +--------------+-------------+------+-----+---------+-------+14 | ip | varchar(15) | NO | PRI | NULL | |15 | port | char(5) | NO | PRI | NULL | |16 | service_name | varchar(10) | NO | | NULL | |17 +--------------+-------------+------+-----+---------+-------+18 rows in set (0.00 sec)19 20 mysql> insert into service values21 -> ('172.16.45.10','3306','mysqld'),22 -> ('172.16.45.11','3306','mariadb')23 -> ;24 Query OK, 2 rows affected (0.00 sec)25 Records: 2 Duplicates: 0 Warnings: 026 27 mysql> insert into service values ('172.16.45.10','3306','nginx');28 ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

auto_increment （自增约束）

increment：步长

offset:auto_increment_increment,auto_increment_offset：起始偏移量

--------偏移量:auto_increment_offset---------==============没有设置偏移量的时候create table dep(id int primary key auto_increment,name char(10));insert into dep(name) values('IT'),('HR'),('EFO');select * from dep;
================设置自增的时候以10开头create table dep1(id int primary key auto_increment,name char(10))auto_increment = 10;insert into dep1(name) values('IT'),('HR'),('EFO');select * from dep1;
===============auto_increment_increment:自增步长create table dep3(id int primary key auto_increment, name char(10));会话：通过客户端连到服务端(一次链接称为一次会话)set session auto_increment_increment = 2; #会话级，只对当前会话有效set global auto_increment_increment=2; #全局，对所有的会话都有效insert into dep3(name) values('IT'),('HR'),('SALE'),('Boss');
-----------查看变量----------show variables like '%auto_in%';#查看变量。只要包含auto_in就都查出来了
=========auto_increment_offset:偏移量+auto_increment_increment:步长===========注意：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略
set session auto_increment_offset=2;set session auto_increment_increment=3;show variables like '%auto_in%';
create table dep4(id int primary key auto_increment,name char(10));insert into dep4(name) values('IT'),('HR'),('SALE'),('Boss');

foreign key （外键约束）

假设员工信息表有三个字段：工号、姓名、部门，公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费

解决方法：我们完全可以定义一个部门表，然后让员工信息表关联该表，如何关联，即foreign key，如下图简单的表示了一下员工表与部门表的关系，即员工表的(dep_id）要关联部门表的id字段

多对一（一个表多条记录的某一字段关联另一张表的唯一一个字段）：员工有部门，部门又有好多信息，所以分开建了一张部门表，部门表的id 和员工表里面的dep_id相关联。注意：

先建被关联的表，
被关联的字段必须唯一
先给被关联的表插入记录

#先建张部门表（被关联表）create table dep(id int not null unique,#id int primary key auto_increment,name varchar(50),comment varchar(100));
#再建张员工表（关联表）create table emp_info(id int primary key auto_increment,name varchar(20),dep_id int,constraint FK_depid_id foreign key(dep_id) references dep(id) #references :关联on delete cascade # 关联的表删了，被关联的表也删了on update cascade # 关联的表修改了，被关联的表也修改了);
#先给被关联的表初始化记录insert into dep values(1,'欧德博爱技术有限事业部','说的好...'),(2,'艾利克斯人力资源部','招不到人'),(3,'销售部','卖不出东西');
insert into emp_info values(1,'egon',1),(2,'alex1',2),(3,'alex2',2),(4,'alex3',2),(5,'李坦克',3),(6,'刘飞机',3),(7,'张火箭',3),(8,'林子弹',3),(9,'加特林',3);
#修改update dep set id =301 where id = 2;select * from dep;delect * from em_info;# 如果部门解散了，员工也就走吧，就是部门表没了，员工表也就没有了。