CSS Ribbon

Reproducing the GitHub Ribbon in CSS

Fork me on GitHub

路飞学城Python-Day35

08-初识SQL语句
数据库客户端操作的内容（增查改删）：
1.操作数据库
操作数据库
增(本质上就是创建一个本地文件夹)
create database db1 charset utf8;
查
查看单个数据库的内容
show create database db1 ;
查看所有的数据库
show databases;
改(没有改数据库名字的方式)
alter database db1 charset utf8;
删
drop database db1;
2.操作库里的表
操作文件(表)
切换数据库
use db1;
查看当前所在的数据库名称
select database();
增(创建一个新的表)
mysql创建一张表就是在硬盘上创建文件, 一张表对应的可能不止一个文件, 是多个文件组合成了一张表, frm就是表结构, ibd就是表内的数据
create table t1(id int,name char);
查
查看单表
show create table t1;
友好的查看方式
desc t1;
查看所有的表
show tables;
改
将名字的字符长度改为6个
alter table t1 modify name char(6);
修改表的表头内容(将name->Name)
alter table t1 change name Name char(7);
删
drop table t1;
3.操作表里的内容（记录）
操作文件内容(记录)
增
values 可以插入一条记录也可以用逗号隔开同时插入多条记录(默认顺序就是字段顺序)
insert t1(id,name) values(1,'panda'),(2,'boy'),(3,'zombie');
查
查表是在本数据库内查,如果不在数据库内就要加指定数据库
select id,name from db1.t1;
查询所有的字段(不推荐使用星号)
select * from t1;
改(需要指定改哪一个字段)
update db1.t1 set name='SB';
update db1.t1 set name='panda' where id = 2;
删
删除全表
delete from t1;
删除表中某个记录
delete from t1 where id = 1;
09-库操作
SQL语言主要分为3种
DDL语句 数据库定义语言：数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

一 系统数据库

nformation_schema： 虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等
performance_schema： MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象
mysql： 授权库，主要存储系统用户的权限信息
test： MySQL数据库系统自动创建的测试数据库

二 创建数据库

1 语法(help create database)
CREATE DATABASE 数据库名 charset utf8;
2 数据库命名规则：
可以由字母、数字、下划线、＠、＃、＄
区分大小写
唯一性
不能使用关键字如 create select
不能单独使用数字
最长128位

三 数据库相关操作

查看数据库
show databases;
show create database db1;
select database();
选择数据库
USE 数据库名
删除数据库
DROP DATABASE 数据库名;
修改数据库
alter database db1 charset utf8;
10-存储引擎介绍

一 什么是存储引擎

mysql每建立一个库就是一个文件夹，每建立一张表就是一个文件，如果自己建立一张表，应该根据数据类型的不同存储在不同的表中，存储引擎就是表的类型
SQL 解析器、SQL 优化器、缓冲池、存储引擎等组件在每个数据库中都存在,但不是每 个数据库都有这么多存储引擎。MySQL 的插件式存储引擎可以让存储引擎层的开发人员设 计他们希望的存储层,例如,有的应用需要满足事务的要求,有的应用则不需要对事务有这 么强的要求 ;有的希望数据能持久存储,有的只希望放在内存中,临时并快速地提供对数据 的查询
查看mysql支持的存储引擎
show engines;
MySQL支持多种数据类型的数据引擎，像其他的数据库（oracle,sqlserver）只支持一种
InnoDB 存储引擎
支持事务,其设计目标主要面向联机事务处理(OLTP)的应用。其
特点是行锁设计、支持外键,并支持类似 Oracle 的非锁定读,即默认读取操作不会产生锁。 从 MySQL 5.5.8 版本开始是默认的存储引擎。
 
查看mysql支持的存储引擎
show engines;
指定表类型(存储引擎)
真实数据存储在硬盘里
CREATE TABLE t1(id int)engine = innodb;
真实数据存储在内存里,内存清空就没了
CREATE TABLE t2(id int)engine = memory;
真实数据一旦插入就放入黑洞
CREATE TABLE t3(id int)engine = blackhole;
CREATE TABLE t4(id int)engine = myisam;
尝试传入值
INSERT t1 VALUES (1);
INSERT t2 VALUES (1);
INSERT t3 VALUES (1);
INSERT t4 VALUES (1);
11-表的增删改查
一张表应该有自己的标题（字段），对于一张表来说，字段是必须要有的，而且字段必须要有自己的数据类型
插入记录要严格按照字段的顺序来插入，并且遵循该字段的数据类型
创建表的语法
注意：创建表的时候，字段与字段之间要加逗号分隔，但是最后一个字段就不要再加逗号了，不然就会报语法错误
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
 
#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的
show create table t1\G; #查看表详细结构，可加\G(一行一行显示)
修改表结构
语法：
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
 
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
 
3. 删除字段
ALTER TABLE 表名
DROP 字段名;
 
4. 修改字段
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
练习
示例：
1. 修改存储引擎
mysql> alter table service
-> engine=innodb;
 
2. 添加字段
mysql> alter table student10
-> add name varchar(20) not null,
-> add age int(3) not null default 22;
 
mysql> alter table student10
-> add stu_num varchar(10) not null after name; //添加name字段之后
 
mysql> alter table student10
-> add sex enum('male','female') default 'male' first; //添加到最前面
 
3. 删除字段
mysql> alter table student10
-> drop sex;
 
mysql> alter table service
-> drop mac;
 
4. 修改字段类型modify
mysql> alter table student10
-> modify age int(3);
mysql> alter table student10
-> modify id int(11) not null primary key auto_increment; //修改为主键
 
5. 增加约束（针对已有的主键增加auto_increment）
mysql> alter table student10 modify id int(11) not null primary key auto_increment;
ERROR 1068 (42000): Multiple primary key defined
 
mysql> alter table student10 modify id int(11) not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
 
6. 对已经存在的表增加复合主键
mysql> alter table service2
-> add primary key(host_ip,port);
 
7. 增加主键
mysql> alter table student1
-> modify name varchar(10) not null primary key;
 
8. 增加主键和自动增长
mysql> alter table student1
-> modify id int not null primary key auto_increment;
 
9. 删除主键
a. 删除自增约束
mysql> alter table student10 modify id int(11) not null;
 
b. 删除主键
mysql> alter table student10
-> drop primary key;

五、复制表

复制表结构＋记录 （key不会复制: 主键、外键和索引）
mysql> create table new_service select * from service;
 
只复制表结构
mysql> select * from service where 1=2; //条件为假，查不到任何记录
Empty set (0.00 sec)
mysql> create table new1_service select * from service where 1=2;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
 
mysql> create table t4 like employees;

六、删除表

DROP TABLE 表名;
12-整数类型
  • 整型
         整型通常用来表示人的年龄，等级，id号，身份证号等等
         不同的整型类型用来表示不同的范围，存储的字节不同，占用的空间不同
         如果有人在mysql的整型里面加了整型的宽度，其实整型的宽度是不受约束的，所以不要去对整型加宽度限制，加上的宽度限制表示的是显示宽度。
类型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1 字节 (-128，127) (0，255) 小整数值
SMALLINT 2 字节 (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
  • 浮点型
作用：存储薪资、身高、体重等参数
DECIMAL的底层原理实际上是字符串的形式保存的，所以数据精度非常高，缺点是整数的范围小
float和double的精度虽然不够高，但是整数表示的范围足够
类型 大小 范围（有符号） 范围（无符号） 用途
FLOAT 4 字节 (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38)
单精度
浮点数值
DOUBLE 8 字节 (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
双精度
浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值
 
13-日期类型
datetime与timestamp的区别
在实际应用的很多场景中，MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，但在某些情况下，会展现出他们各自的优劣。
下面就来总结一下两种日期类型的区别。
1.DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。
2.DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。在mysql服务器，
操作系统以及客户端连接都有时区的设置。
3.DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。
4.DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP），
如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。
CREATE TABLE student(
id int ,
name char(6),
born_year YEAR ,
birth_date date,
class_time TIME ,
reg_time datetime
);
insert into student VALUES
(1,'panda',now(),now(),now(),now());
14-字符类型
字符类型就是描述性数据的存储数据类型
  • char
char：定长类型
  • varchar
varchar：变长类型
字符类型的宽度指的是字符的个数
存储数据是一种机制，取数据的时候对于char和varchar在取的时候只会按照值取，不会管具体有几个空格的，末尾的空格在查询时并没有实际的意义
MySQL在实际查询的过程中，做比较的时候是忽略末尾的空格的，前部分或者中间部分的空格是不去的
但是用like去查询的时候，空格是不去的，这个时候的char就是要写具体的字符规定的长度
CREATE TABLE t13(name char(5));
CREATE TABLE t14(name VARCHAR (5));
insert into t13(name) VALUES ('panda'),('gg');
insert into t14(name) VALUES ('pand'),('g');
SELECT CHAR_LENGTH (name) from t13;
SELECT CHAR_LENGTH (name) from t14;
设置取值时不需要取消空格
SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
char类型和varchar类型的存储原理
1.char类型如果存储的一个值是空值，char就存4个bytes；varchar类型对于空值就存1个byte表示这个数据的长度
2.假设传入的数值的长度正好是传入的宽度的限制，那么这个时候char类型是节省空间的
3.假设传入的数值的长度比传入的宽度的限制小的时候，那么这个时候varchar类型是节省空间的
4.一般来说，varchar类型的一个bytes不是固定只有一个byte，如果你的数据已经超过了255个字节，那么varchar就会使用2个bytes（可以用来表示65535）来传入数据头，mysql对单个的字符长度是不能超过65535的
5.mysql里不应该把视频或者大图片放入，因为mysql内部应该保证数据越精简越好，如果真要存储大文件，不应该把大的文件存为字符类型，只需要存储一个地址路径就行了
【总结】
char类型
优点：简单粗暴，不管多少数据，按照固定格式存储，存储和提取数据都快
缺点：浪费存储的空间
varchar类型：
优点：更加节省空间，存储数据更精准
缺点：存储和提取数据慢，因为要先读取数据的头，判断有多少个字节以后再提取数据
大部分情况下都可以使用char类型，目前的技术水平对于硬件的存储容量已经足够了，所以大部分情况下使用char类型的存储方式就足够满足需求了，或者对于存储完成后无关的数据，或者长时间都不用于查找的数据，使用varchar类型更合适
对于建表的过程中，应该尽量的把定长的数据往表的结构前放，把变长的数据往后放，而且一张表中不要同时使用char和varchar类型的混合使用
其他的字符类型无非是支持的字符的宽度更宽而已，但是尽量不要去使用，因为在数据库中尽量使用精简的数据去保存数据。
15-枚举类型与集合类型
字段的值只能在给定的范围中选择，比如单选框或者多选框
enum单选，只能在给定的范围内选一个值，如性别、名族等
set多选，在给定的范围里可以选择一个或一个以上的值（爱好1、爱好2、爱好3等）
16-约束条件not null与default
什么是约束条件？
用于保证数据的完整性和一致性
CREATE TABLE t15(
id int(11) unsigned zerofill
);
CREATE TABLE t16(
id int,
name char(6),
sex enum('male','female') not null default 'male'
);
17-约束条件unique key
限制字段传入的值是唯一的，不重复的
单列唯一
方式一
CREATE TABLE department(
id int,
name char(10) UNIQUE
);
方式二
CREATE TABLE department(
id int,
name char(10),
UNIQUE (id),
UNIQUE (name)
);
insert into department VALUES
(1,'IT'),
(2,'IIT');
####################################################3
联合唯一
几个字段组合在一起,只要不重复就可以,单独的字段重复没有关系
CREATE TABLE services(
id int,
ip char(15),
port int,
UNIQUE (id),
UNIQUE (ip,port)
);
insert into services VALUES
(1,'127.0.0.1',8080),
(2,'127.0.0.1',8081),
(3,'127.0.0.1',8082);
18-约束条件primary key
从约束的角度来说，就是不为空且唯一
默认的存储引擎Innodb，对于Innodb的引擎来说，一张表内必须有一个主键，如果不指定主键是可以的
主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，一张表中必须有且只有一个主键。
如果没有明确的指定primary key的话，会指定一个不为空且唯一的字段作为主键
#单列主键(通常是id字段设置为主键)
CREATE TABLE t17(
id int PRIMARY KEY ,
name char(16)
);
insert into t17 VALUES
(1,'panda'),
(2,'boy');
CREATE TABLE t18(
id int not null UNIQUE ,
name char(16)
);
#复合主键
CREATE TABLE t19(
ip char(15),
port int,
PRIMARY KEY (ip,port)
);
insert into t19 VALUES
('127.0.0.1',8080),
('127.0.0.1',8081);
19-约束条件auto_increment
auto_increment->自增长
清空表(如果表内是有自增长的约束的时候,即使删除了表的内容,但是表的自增记录也没有设置为1)
清空表不要用delete,清空表要使用truncate t20;
delete使用在和条件查询的时候的where语句上
CREATE TABLE t20(
id int PRIMARY KEY auto_increment,
name char(16)
);
insert into t20(name) VALUES
('panda'),
('boy');
 
insert INTO t20(name) VALUES
('panda'),
('1111');
#清空表(如果表内是有自增长的约束的时候,即使删除了表的内容,但是表的自增记录也没有设置为1)
清空表不要用delete,清空表要使用truncate t20;
delete使用在和条件查询的时候的where语句上
 
#了解
#查看mysql自己的变量(%代表任意的字符)
show variables like 'auto_inc%';
#步长
auto_increment_increment
#起始位置(偏移量)
auto_increment_offset
#自定义
#自定义步长
#本次连接有效
set SESSION auto_increment_increment = 5;
#全局有效(所有的会话要全部再加载一次才能设置成功)
set GLOBAL auto_increment_increment = 5;
#自定义偏移量(起始偏移量一定是要小于步长的,第一次的偏移量要小于等于设置的步长)
set GLOBAL auto_increment_offset = 5;
20-约束条件之foreign key
[非常重要]外键
建立表之间的关系
写一个项目是不可能把所有的数据全部存放在一张表里的，容易出现数据重复，浪费存储空间，如果修改数据也是十分麻烦的，可扩展性极差
外键就是把表和表之间建立硬性的关系
写项目的时候，最好不要把表直接建立硬性关联关系
不要真正的把表之间耦合到一起
如果数据库要修改，所有的项目很有可能都会变动
ON DELETE CASCADE ->删除同步
ON UPDATE CASCADE->更新同步
外键 foregin key
先建被关联的表(保证被关联的字段唯一)
CREATE TABLE dep(
id int PRIMARY KEY ,
name char(16),
comment char(50)
);
 
CREATE TABLE emp(
id int PRIMARY KEY ,
name char(10),
sex enum('male','female') not null DEFAULT 'male',
dep_id int,
FOREIGN KEY (dep_id) REFERENCES dep(id)
);
insert into dep(id,name,comment) VALUES
(1,'IT部门','唯一的一个技术部门'),
(2,'销售部门','唯一的一个会说话的部门'),
(3,'财务部门','唯一的一个会花钱的部门');
insert into emp(id,name,sex,dep_id) VALUES
(1,'panda','male',1),
(2,'boy','female',3),
(3,'zombie','male',2);
外键 foregin key
先建被关联的表(保证被关联的字段唯一)
CREATE TABLE dep(
id int PRIMARY KEY ,
name char(16),
comment char(50)
);
 
CREATE TABLE emp(
id int PRIMARY KEY ,
name char(10),
sex enum('male','female') not null DEFAULT 'male',
dep_id int,
FOREIGN KEY (dep_id) REFERENCES dep(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
insert into dep(id,name,comment) VALUES
(1,'IT部门','唯一的一个技术部门'),
(2,'销售部门','唯一的一个会说话的部门'),
(3,'财务部门','唯一的一个会花钱的部门');
insert into emp(id,name,sex,dep_id) VALUES
(1,'panda','male',1),
(2,'boy','female',3),
(3,'zombie','male',2);
#删除外键关联的表数据
delete from emp where dep_id = 1;
delete from dep where id = 1;
21-表关系之多对一
表之间建立关系都是两两之间建立
找表的关系要站在两个表的角度分别考虑
多条记录对应一条记录
#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）
#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）
#3、总结：
#多对一：
如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表
create table t2(
id int primary key ,
name char(15)
);
insert into t2 values
(1,'乱七八糟出版社'),
(2,'随便什么出版社'),
(3,'emmm出版社');
 
create table t3(
id int primary key ,
name char(10),
p_id int,
foreign key (p_id) references t2(id)
on update cascade
on delete cascade
 
 
);
insert into t3 values
(1,'独孤九剑',2),
(2,'神明绝学',1),
(3,'少林铁头功',2),
(4,'南山拳',3);
 
22-表关系之多对多
作者和书的关系就是典型的多对多关系
双向的多对一关系就是多对多关系
多对多的关系表建立需要第三张表去存储两张表之间的关系
23-表关系之一对一
假设有两张表，一张客户表，一张学生表，这就是一对一关系
 
 

posted on 2018-08-04 07:15  pandaboy1123  阅读(141)  评论(0)  编辑  收藏  举报

导航