Mysql
创建连接 标签(空格分隔): Python学习
数据库介绍
mysql数据库存储数据的方式与excel类似,都是以表格的形式来存储数据。 excel一般用一张表来存储少量的数据,数据库可以用多个表来存储大量的数据。
用其他方式存储数据,如果数据量少,读取的时候会很快,但是如果数据量过大,读取数据的速度就比较慢了;但是使用数据库就能高效的读取数据。
什么是数据库?
数据库(Database)是按照数据结构来组织、存储和管理数据的仓库, 每个数据库都有一个或多个不同的API(接口)用于创建,访问,管理,搜索和复制所保存的数据。 我们也可以将数据存储在文件中,但是在文件中读写数据速度相对较慢。 所以,现在我们使用关系型数据库管理系统(RDBMS)来存储和管理的大数据量。所谓的关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。 RDBMS即关系数据库管理系统(Relational Database Management System)的特点: 1.数据以表格的形式出现 2.每行为各种记录名称 3.每列为记录名称所对应的数据域 4.许多的行和列组成一张表单 5.若干的表单组成database
下图就是一个数据库
可以看到多张表的关联组合,就形成了数据库;多张表之间有关联可称之为关系型数据库。
常用的关系型数据库:
oracle(收费)
Mysql(开源):稳定性可能不如Oracle,但是因为开源和免费所以是最常见的。
SqlServer(微软)
#以上三种为常见的
DB2
Postgresql
Sqlite(轻量级开源免费)
RDBMS 术语
在我们开始学习MySQL 数据库前,让我们先了解下RDBMS的一些术语:
数据库: 数据库是一些关联表的集合。.
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
行:一行(=元组,或记录)是一组相关的数据,例如一条用户订阅的数据。
冗余:存储两倍数据,冗余可以使系统速度更快。(表的规范化程度越高,表与表之间的关系就越多;查询时可能经常需要在多个表之间进行连接查询;而进行连接操作会降低查询速度。例如,学生的信息存储在student表中,院系信息存储在department表中。通过student表中的dept_id字段与department表建立关联关系。如果要查询一个学生所在系的名称,必须从student表中查找学生所在院系的编号(dept_id),然后根据这个编号去department查找系的名称。如果经常需要进行这个操作时,连接查询会浪费很多的时间。因此可以在student表中增加一个冗余字段dept_name,该字段用来存储学生所在院系的名称。这样就不用每次都进行连接操作了。)
主键:主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据(主键是用来标识数据的,假如有多个相同的数据,那么就需要用主键来区分数据;好比同名同姓,可以使用×××号来区分,这个主键是唯一的,在数据库中不会重复。)。
外键:外键用于关联两个表(当前表中的这个键连接了其他表的别的键,当前表的这个键就叫做外键)。
复合键:复合键(组合键)将多个列作为一个索引键,一般用于复合索引(比如将多列的数据字段,变成一个索引)。
索引:使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录(比如在5万条数据中查找一个值,不需要遍历所有数据来查找,可以类似书籍目录的方式,直接定位到数据)(查找到数据不是一下就找到,而是通过一种算法来查找,但查找的次数比较少)。
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件,目的是保证数据的一致性。
Mysql数据库
Mysql是最流行的关系型数据库管理系统,在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。由瑞典MySQL AB公司开发,目前属于Oracle公司。MySQL是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
Mysql是开源的,所以你不需要支付额外的费用。
Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
MySQL使用标准的SQL数据语言形式。
Mysql可以允许于多个系统上,并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
Mysql对PHP有很好的支持,PHP是目前最流行的Web开发语言。
MySQL支持大型数据库,支持5000万条记录的数据仓库,32位系统表文件最大可支持4GB,64位系统支持最大的表文件为8TB。
Mysql是可以定制的,采用了GPL协议,你可以修改源码来开发自己的Mysql系统。
MYSQ数据库的安装使用
Linux/UNIX上安装Mysql
Linux平台上推荐使用RPM包来安装Mysql,MySQL AB提供了以下RPM包的下载地址:
MySQL - MySQL服务器。你需要该选项,除非你只想连接运行在另一台机器上的MySQL服务器。
MySQL-client - MySQL 客户端程序,用于连接并操作Mysql服务器。
MySQL-devel - 库和包含文件,如果你想要编译其它MySQL客户端,例如Perl模块,则需要安装该RPM包。
MySQL-shared - 该软件包包含某些语言和应用程序需要动态装载的共享库(libmysqlclient.so*),使用MySQL。
MySQL-bench - MySQL数据库服务器的基准和性能测试工具。
以下安装Mysql RMP的实例是在SuSE Linux系统上进行,当然该安装步骤也适合应用于其他支持RPM的Linux系统,如:Centos。
安装步骤如下:
Linux
- 安装
CentOS7默认数据库是mariadb,配置等用着不习惯,因此决定改成mysql,但是CentOS7的yum源中默认好像是没有mysql的。为了解决这个问题,我们要先下载mysql的repo源。
1.卸载 集成的MySQL 经常有问题,所以最好是卸载重新安装
rpm -qa|grep -i mysql //查看所有相关mysql的文件
用命令 yum -y remove
yum -y remove mysql-community-client-5.6.38-2.el7.x86_64
卸载不掉的用 rpm -ev
依次卸载 直到没有
2.下载mysql的repo源
cd /usr/local/src/
wget http://repo.mysql.com/mysql57-community-release-el7-8.noarch.rpm //下载
rpm -ivh mysql57-community-release-el7-8.noarch.rpm //升级
3.安装
yum -y install mysql-server
根据提示安装就可以了,不过安装完成后没有密码,需要重置密码
4.重置mysql密码
$ mysql -u root
登录时有可能报这样的错:ERROR 2002 (HY000): Can‘t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock‘ (2),原因是/var/lib/mysql的访问权限问题。下面的命令把/var/lib/mysql的拥有者改为当前用户:
$ sudo chown -R root:root /var/lib/mysql
重启mysql服务
$ service mysqld restart
接下来登录重置密码:
$ mysql -u root //直接回车进入mysql控制台 mysql > use mysql;
mysql > update user set password=password('123456') where user='root'; //如果这里提示ERROR 1054 (42S22): Unknown column 'password' in 'field list' 下面忘记密码有提示怎么操作
mysql > exit;
如果以上操作无法生效,请使用下面忘记mysql密码中的方式。
- 忘记mysql密码
1、修改MySQL的登录设置:
[root@localhost ~]#vim /etc/my.cnf
在[mysqld]的段中加上一句:skip-grant-tables
例如:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-grant-tables
保存并且退出vi。
2、重新启动mysqld
[root@localhost ~]#service mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]
3、登录并修改MySQL的root密码
[root@localhost ~]# mysql
mysql> USE mysql ;
mysql> UPDATE user SET Password = password ( 'new-password' ) WHERE User = 'root' ;
##如果这里提示ERROR 1054 (42S22): Unknown column 'password' in 'field list' 输入:mysql> describe user; 来查看输出内容下方是否有authentication_string内容,如果有的话就使用下面的命令修改密码:
#mysql> update user set authentication_string=password('123456') where user='root';
mysql> flush privileges ;
mysql> quit
4、将MySQL的登录设置修改回来
[root@localhost ~]# vim /etc/my.cnf
将刚才在[mysqld]的段中加上的skip-grant-tables删除
保存并且退出vim
5、重新启动mysqld
[root@localhost ~]# service mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]
- 删除mysql
首先查询安装包:
rpm -qa|grep mysql
查询到的一个结果为:mysql-community-libs-5.7.13-1.el6.x86_64
yum 删除
yum -y remove mysql-community-libs-5.7.13-1.el6.x86_64
rpm 删除(仅用 "-e" 判断依赖)
rpm -e mysql-community-libs-5.7.13-1.el6.x86_64
Window上安装Mysql
Window上安装Mysql相对来说会较为简单,你只需要载 MySQL 下载中下载window版本的mysql安装包,并解压安装包。
双击 setup.exe 文件,接下来你只需要安装默认的配置点击"next"即可,默认情况下安装信息会在C:\mysql目录中。
接下来你可以通过"开始" =》在搜索框中输入 " cmd" 命令 =》 在命令提示符上切换到 C:\mysql\bin 目录,并输入一下命令:
mysqld.exe --console
如果安装成功以上命令将输出一些mysql启动及InnoDB信息。
进入mysql并查看
如果登录时出现如下错误:
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
需要通过 alter user 'root'@'localhost' identified by '111111'; 重新设置密码。
但是又可能出现:ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
这说明密码复杂度不够,需要大写、小写、数字、特殊字符
mysql> alter user 'root'@'localhost' identified by 'QWE!@#qwe123';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
[root@localhost ~]# mysql -uroot -p123qwe
#显示数据库列表
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
#当前有3个数据库
'''
【INFORMATION_SCHEMA 数据库】 是MySQL自带的,它提供了访问数据库 元数据 的方式。什么是 元数据 呢?元数据是关于数据的数据,如数据库名或表名,列的数据类型,或访问权限等。
有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。
在MySQL中,把【INFORMATION_SCHEMA】 看作是一个数据库,确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名,数据库的表,表栏的数据类型与访问权限等。
在 【INFORMATION_SCHEMA 】中,有数个 只读 表。它们实际上是 视图 ,而不是基本表,因此,你将无法看到与之相关的任何文件。
'''
'''
【mysql数据库】
mysql库很重要它里面有MYSQL的系统信息,我们改密码和新增用户,实际上就是用这个库进行操作。
'''
'''
【PERFORMANCE_SCHEMA数据库】
MySQL 5.5开始新增一个数据库:PERFORMANCE_SCHEMA,主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE_SCHEMA,而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。
'''
#进入数据库并显示库中的数据表
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
28 rows in set (0.00 sec)
mysql> desc user;
查看表结构如下:
Field相当于excel中第一行的标题
Type是数据的类型(数字、字符串等等。。。)
char表示字符串,括号中的数字表示定义这个字符串的长度
enum表示枚举
Null表示是否可以为空;NO的话就是必须要赋值,不能为空
Key中的PRI表示 primary key(主键),之前说过主键代表唯一的值(相当于×××)。
default 表示可以填写默认值
extra 可以填写额外的内容,没有要求
select * from user;
查看表内容:
内容较多,显示的比较乱
select * from user\G;
查看表内容,通过\G来整理内容的显示格式,以竖着的方式来显示。
设置linux系统启动时 启动mysql
/etc/init.d/mysqld start
需要将mysqld二进制文件添加到/etc/init.d/目录中
查看mysql进程
#查看所有进程
ps -ef
#查看mysql进程
ps -ef | grep mysql
创建新mysql账号 之前通过desc user; 看到过user中的字段,其中就有User字段,这里包含了当前已有的账号。
mysql> select User from user;
当前的用户只有root
下面是mysql的常用增删改查命令:
SELECT查找
INSERT插入
UPDATE修改
DELETE删除
CREATE创建
DROP删除
#授予查找与插入权限
mysql> grant select,insert
#创建账号授予全部权限
grant all on test.* to 'aubrey'@'%' identified by '123qwe';
'''授予aubrey全部权限访问test这个数据库;
@表示指定访问源,%表示所有,也就是指定所有源都可以访问。
访问的密码为:123qwe。'''
Query OK:表示查询,因为这里并没有进行查询,所有没有异常,就是OK。
0 rows affected:因为是新建,没有插入,所以显示有0行收到影响。
select * from user\G
查看user字段的内容,可以看到有关user:aubrey的账户信息;
不过这里权限都为N
show grants for aubrey;
查看账号信息
我们通过新账号进入mysql,但是提示被localhost拒绝,%虽然是可以指定访问源,但不包括localhost。
指定访问源为localhost
指定localhost后可以访问mysql了。
在root下是可以看到3个的,这里当前只能看到information_schema 是默认就可以看到的,但是这里没有看到test数据库,是因为还没有建立test数据库
#创建test数据库
create database test;
'''
我们在xshell中回到root登录的mysql来创建test数据库。
'''
回到aubrey用户登录的mysql,再次查看,就可以看到test数据库了。
使用test数据库,并查看表项,表项为空。
/etc/my.cnf 文件配置
一般情况下,你不需要修改该配置文件,该文件默认配置如下:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
管理MySQL的命令
以下列出了使用Mysql数据库过程中常用的命令:
USE 数据库名 :选择要操作的Mysql数据库,使用该命令后所有Mysql命令都只针对该数据库。
SHOW DATABASES: 列出 MySQL 数据库管理系统的数据库列表。
SHOW TABLES: #显示指定数据库的所有表,使用该命令前需要使用 use命令来选择要操作的数据库。
SHOW COLUMNS FROM 数据表: #显示数据表的属性,属性类型,主键信息 ,是否为 NULL,默认值等其他信息。
create database testdb charset "utf8"; #创建一个叫testdb的数据库,且让其支持中文
drop database testdb; #删除数据库
SHOW INDEX FROM 数据表:显示数据表的详细索引信息,包括PRIMARY KEY(主键)。
与desc user;一样
show create database test;
DEFAULT CHARACTER SET latin1表示默认字符集为拉丁语系,只支持英文,所以默认mysql只支持英文的。
但是修改语系,必须在创建数据库的时候指定,所以下面需要先删掉数据库。(注意:现实环境中不要随意删除数据库,因为里面的数据也会被删掉)
create database test charset utf8;
这次在show就可以看到是utf8,这样以后test数据库中的表就可以写中文数据了。
#MySQL数据类型
MySQL中定义数据字段的类型对你数据库的优化是非常重要的。
MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。
数值类型
MySQL支持所有标准SQL数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。
BIT数据类型保存位字段值,并且支持MyISAM、MEMORY、InnoDB和BDB表。
作为SQL标准的扩展,MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。
常用的是INT、SMALLINT、FLOAT
日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性,将在后面描述。
字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
TINYBLOB可以存二进制,图片也可以通过二进制来存储,不过一般不会再数据库中存储图片,通常是存储图片的连接。
MySQL中char、varchar和text的区别
它们的存储方式和数据的检索方式都不一样。 数据的检索效率是:char > varchar > text 空间占用方面,就要具体情况具体分析了。
char:存储定长数据很方便,CHAR字段上的索引效率级高,必须在括号里定义长度,可以有默认值,比如定义char(10),那么不论你存储的数据是否达到了10个字节,都要占去10个字节的空间(自动用空格填充),且在检索的时候后面的空格会隐藏掉,所以检索出来的数据需要记得用什么trim之类的函数去过滤空格。
varchar:存储变长数据,但存储效率没有CHAR高,必须在括号里定义长度,可以有默认值。保存数据的时候,不进行空格自动填充,而且如果数据存在空格时,当值保存和检索时尾部的空格仍会保留。另外,varchar类型的实际长度是它的值的实际长度+1,这一个字节用于保存实际使用了多大的长度。
text:存储可变长度的非Unicode数据,最大长度为2^31-1个字符。text列不能有默认值,存储或检索过程中,不存在大小写转换,后面如果指定长度,不会报错误,但是这个长度是不起作用的,意思就是你插入数据的时候,超过你指定的长度还是可以正常插入。
总结起来,有几点:
经常变化的字段用varchar
知道固定长度的用char
尽量用varchar
超过255字符的只能用varchar或者text
能用varchar的地方不用text
MySQL 常用命令使用
MySQL 创建数据表
语法
CREATE TABLE table_name (column_name column_type);
创建一个student表
#在aubrey账号下创建一个student表;类型INT ,NOT NULL不能为空 AUTO_INCREMENT对主键自动增加编号;name 32个字节,不能为空;age 不能为空; 注册日期不能为空;PRIMARY KEY(stu_id)将stu_id设置为主键,设置主键后就是唯一。
create table student(
stu_id INT NOT NULL AUTO_INCREMENT,
name CHAR(32) NOT NULL,
age INT NOT NULL,
register_date DATE,
PRIMARY KEY ( stu_id )
);
创建表项
查看已经有的表
通过insert into 插入了三次数据
三个同样的数据,但是id不同
我们这里指定id来插入数据,可以看到报错,保证了id的唯一性。
MySQL 增删改查
语法
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[OFFSET M ][LIMIT N]
查询语句中你可以使用一个或者多个表,表之间使用逗号(,)分割,并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
你可以使用星号(*)来代替其他字段,SELECT语句会返回表的所有字段数据
你可以使用 WHERE 语句来包含任何条件。
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0(偏移量就是偏移几个数据开始查询)。
你可以使用 LIMIT 属性来设定返回的记录数(显示多少条)。
- limit & offset
select * from student limit 2 offset 1;
limit限制显示数量;offset偏移1,也就是从第2个开始显示。
- WHERE
语法
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
以下为操作符列表,可用于 WHERE 子句中。
下表中实例假定 A为10 B为20
mysql> select * from student where id > 3;
通过where来过滤查看id大于3的条目。
mysql> select * from student where age < 22;
mysql> select * from student where register_date = "2033-03-03";
查看日期条目
- like
select * from student where register_date like "2033-03%";
只查询3月份的条目
- update
语法
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
1
update student set age=22 ,name="Alex Li" where stu_id>3;
update student set name = "LiSi",age = 10 where id=4;
修改id=4的条目
可以看到id 4的条目已经被修改。
mysql> update student set name="Amy" where id >4;
- delete
mysql> delete from student where name="Amy";
删除name 等于 Amy的条目
排序
- order by
SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。
select *from student where name like binary "%Li" order by stu_id desc;
select * from student order by stu_id;
默认是升序
select * from student order by stu_id desc;
后面使用desc后,排序为降序。
update student set register_date="2030-01-01" where stu_id = 1;
update student set register_date="2031-02-02" where stu_id = 2;
update student set register_date="2032-02-08" where stu_id = 3;
#修改一下时间
select * from student order by register_date;
select * from student order by register_date desc;
- group by
insert into student (name,age,register_date) values("LiSi",22,"2014-03-21");
新增加一条数据
select name,count(*) from student group by name;
使用group by来统计 name的信息。
可以看到统计了ZhangSan和LiSi的数量。
select name只是表示显示name的内容
group by name以name为一组数据进行统计
select name,count(*) as JiShu from student group by name;
将count(*)重命名为JiShu
select name,sum(age) from student group by name;
select name:显示的时候,显示name这一列的信息。
sum(age)和group by name:以相同name为一组进行对age进行综合计算,如:把相同name(ZhangSan)的age数字进行综合统计。
select name,sum(age) from student group by name with rollup;
统计所有组的总数; 所有组的名字默认为NULL
select coalesce(name,"Total Age"), sum(age) from student group by name with rollup;
使用coalesce来修改NULL的名称
#MySQL ALTER
MySQL ALTER用于修改表本身
add
alter table student add money int(11);
#alter table表示要修改哪个表,这里修改student表。
#add表示增加字段,这里增加一个叫money的字段,类型为int,括号里的11表示11个字节。
可以看到新增加的字段数据默认为空。
insert into student (name,age,register_date,money) values("WangEr",67,"2018-9-9",999);
插入一条新数据
drop
alter table student drop age;
#删掉age字段及所有内容
已经看不到删除的age字段及内容了
desc student;
可以看到默认如果不设置,那么money的null为YES,也就是可以为空。
alter table student drop money;
alter table student add money int(11) not null;
#我们删掉money字段,然然后在重新增加money字段,然后设定为not null
当前Null为NO
modify
alter table student modify money int(123) null;
修改null为YES(修改字段的类型)
alter table student modify money int(123) not null;
在修改回为 not null
change
alter table student change money new_money char(32) not null default "X";
#将money改成新名字new_money;
#类型改为char(32),not null
#默认值为X
名字已被修改
类型为char(32),默认值为X
alter table student change stu_id id int(11) not null ;
修改主键的名称,从stu_id修改为id
#外键
CREATE TABLE `study_record` (
`id` int(11) NOT NULL,
`day` int NOT NULL,
`status` char(32) NOT NULL,
`stu_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_student_key` (`stu_id`),
CONSTRAINT `fk_student_key` FOREIGN KEY (`stu_id`) REFERENCES `student` (`id`)
)
;
# `stu_id` int(11) NOT NULL 这里定义的是外键
#KEY `fk_student_key` (`stu_id`):是定义一个外键的名称为fk_student_key,关联外键stu_id
#CONSTRAINT `fk_student_key`:使用名为fk_student_key的外键,外键stu_id关联student表中的id值(主键)
查看study_record的表结构;
MUL就表示外键
删掉student表多余数据
更新id2的数据
alter table study_record modify id int auto_increment;
#创建study_record表时,没有设置为自动自增 id数。这里修改为自动自增。
这里新插入数据,如果不设置id自动自增的话,这里就无法成功插入数据,因为id没有定义(也就是没有自动新增)
因为外键stu_id关联了student表中的id,所以这里对应stu_id的id 值实际写的是student表中的id
可以看到成功插入数据并关联外键
这里stu_id关联的是5,但是在student表中并没有id 5,所以会报错。
study_record这个有外键的表,也是可以通过delete来删除数据的。
study_record引用了student表,所以只删除study_record的数据是没问题的,但是反过来删除student的数据是不可以的,因为student的数据被关联引用了 ,删除的话会影响study_record。
删除student表中的数据时就报错了,因为被引用了。
#多表查询(mysql 连接)
MySQL NULL 值处理
我们已经知道MySQL使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时,该命令可能就无法正常工作。 为了处理这种情况,MySQL提供了三大运算符: IS NULL: 当列的值是NULL,此运算符返回true。 IS NOT NULL: 当列的值不为NULL, 运算符返回true。 <=>: 比较操作符(不同于=运算符),当比较的的两个值为NULL时返回true。 关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。 在MySQL中,NULL值与任何其它值的比较(即使是NULL)永远返回false,即 NULL = NULL 返回false 。 MySQL中处理NULL使用IS NULL和IS NOT NULL运算符。
Mysql 连接(left join, right join, inner join ,full join)
我们已经学会了如果在一张表中读取数据,这是相对简单的,但是在真正的应用中经常需要从多个数据表中读取数据。
本章节我们将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。
你可以在SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。
JOIN 按照功能大致分为如下三类:
INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。 LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。 RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。 Suppose you have two tables, with a single column each, and data as follows:
mysql> create table A(
-> a int not null );
mysql>
mysql> create table B( b int not null );
#创建一个A、一个B表用于测试查询
查看表结构;且当前表为空。
创建A表的数据
创建B表的数据
当前两个表的数据内容
- INNER JOIN 内连接
寻找两个表中相同的内容,类似交集
select * from A inner join B on A.a = B.b ;
#A inner B:A内连接B;
#on A.a = B.b:用A表中的a字段来比较B表中的b字段。
取出了两个表中相同的数据
select A.*, B.* from A,B where A.a = B.b;
#等同于select * from A inner join B on A.a = B.b ;
- LEFT JOIN 左连接
寻找两个表的不同,类似差集
select * from A left join B on A.a = B.b ;
#获取左表所有记录,即使右表没有对应匹配的记录
先找两个表相同的,不同的右表则会以NULL显示。
select * from B left join A on A.a = B.b ;
#把A和B调换位置
- RIGHT JOIN 右连接
与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录
select * from A right join B on A.a = B.b ;
- 并集
select * from A left join B on A.a = B.b UNION select * from A right join B on A.a = B.b;
#通过UNION将两个语句联合起来使用
将两个结果合并一起展现出来
#事务
MySQL 事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务!
在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务 事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行 事务用来管理insert,update,delete语句 一般来说,事务是必须满足4个条件(ACID): Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)
1、事务的原子性:一组事务,要么成功;要么撤回;
所做的操作,要么成功,要么撤回。
2、稳定性 : 有非法数据(外键约束之类),事务撤回。
比如说:两个关联的表,当你删除时,是要将两个表关联的数据都删除,可是当你操作后只删除其中一个表的数据,数据库就卡死了,导致没有完全删除,就会撤回。
3、隔离性:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。
4、可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得, innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里
当执行命令到一半时就宕机了,因为宕机也无法撤回,但是当重启后会查看日志,根据日志进行撤回。
-
在Mysql控制台使用事务来操作
begin; #开始一个事务 insert into a (a) values(555); rollback; 回滚 , 这样数据是不会写入的 commit; 提交,提交之后就不能撤回了。
查看A表中的数据
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into A (a) values (6);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into A (a) values (7);
Query OK, 1 row affected (0.00 sec)
mysql> select * from A;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+---+
7 rows in set (0.00 sec)
#查看当前A表的内容,数据已经成功的插入。
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from A;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---+
5 rows in set (0.00 sec)
#使用回滚后,就恢复了插入数据之前的数据内容。
mysql> insert into A (a) values (6);
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> insert into A (a) values (7);
Query OK, 1 row affected (0.02 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from A;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+---+
7 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from A;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+---+
7 rows in set (0.01 sec)
#commit提交以后,就无法再回滚了
#索引
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。
打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。
索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索包含多个列。
创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。
show index from student;
#查看student表的索引
默认表的主键就是索引
索引操作
- 创建索引
create index index_name on student (name(32));
#创建索引,索引名温蒂index_name;
#on student (name(32)):是针对student这个表来创建索引,引用student的name字段来创建索引,索引hash长度不能超过32(这个长度一般建议使用与字段相同的长度)
可以看到新创建的索引
- 创建表指定索引
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
- 删除索引
drop index index_name on student;
已经成功删除了索引
- 唯一索引
它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。 比如主键就可以当做唯一索引,因为主键的值是唯一的。
create unique index index_name on student (id);
#通过id建立唯一索引; 不要使用name等会出现重复值的字段来建立唯一索引。
#Mysql Python交互
python-mysqldb
- python-mysqldb安装
python-mysqldb最后更新时间位2014年,且不能被python3所支持,可以不学习mysqldb。 不过mysqldb与pymysql的操作命令几乎一样。
linux: yum install MySQL-python
window: http://files.cnblogs.com/files/wupeiqi/py-mysql-win.zip
pymysql
http://www.cnblogs.com/wupeiqi/articles/5713330.html 参考
- 下载安装
pip3 install pymysql
- 使用操作
#授权
grant all on *.* to 'root'@'%' identified by '123qwe';
'''我们通过root进入mysql,然后给root授权,所有源都可以登录(除本地)'''
#更新配置
flush privileges;
#在centos7中关闭防火墙
systemctl stop firewalld.service
systemctl disable firewalld.service
systemctl mask firewalld.service
#!/usr/bin/env python
# -*- coding:utf-8 -*-
#示例
import pymysql
# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
# 创建游标
cursor = conn.cursor()
'游标就是MySQL中的:"mysql>" 位置'
# 执行SQL,并返回收影响行数
effect_row = cursor.execute("update hosts set host = '1.1.1.2'")
# 执行SQL,并返回受影响行数
#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
# 执行SQL,并返回受影响行数
#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
# 提交,不然无法保存新建或者修改的数据
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
#在pycharm中执行代码来连接linux
import pymysql
# 创建连接
conn = pymysql.connect(host='192.168.2.202', port=3306, user='aubrey', passwd='123qwe', db='test')
# 创建游标
cursor = conn.cursor()
'''游标就是MySQL中的:"mysql>" 光标位置'''
'''相当于创建一个实例'''
# 执行SQL,并返回收影响行数
effect_row = cursor.execute("select * from student")
#读取数据
print (cursor.fetchone())
'''cursor.fetchone读取一条数据,'''
print (cursor.fetchone())
'''cursor.fetchone接着上一条数据,再往下读取一条数据,'''
执行结果
import pymysql
# 创建连接
conn = pymysql.connect(host='192.168.2.202', port=3306, user='aubrey', passwd='123qwe', db='test')
# 创建游标
cursor = conn.cursor()
'''游标就是MySQL中的:"mysql>" 光标位置'''
'''相当于创建一个实例'''
# 执行SQL,并返回收影响行数
effect_row = cursor.execute("select * from student")
#读取数据
# print (cursor.fetchone())
# '''cursor.fetchone读取一条数据,'''
# print (cursor.fetchone())
# '''cursor.fetchone接着上一条数据,再往下读取一条数据,'''
print ('----------------------------')
print (cursor.fetchall())
'''读取所有数据'''
print (cursor.fetchmany())
'''获取多条数据'''
- 插入数据
drop table study_record;
'''因为之前student表被关联了study_record表,导致不能修改student表,所以这里删除study_record表'''
alter table student modify id int auto_increment;
'''让student表的id可以自增'''
这是当前student表的数据内容
import pymysql
# 创建连接
conn = pymysql.connect(host='192.168.2.202', port=3306, user='aubrey', passwd='123qwe', db='test')
# 创建游标
cursor = conn.cursor()
'''游标就是MySQL中的:"mysql>" 光标位置'''
'''相当于创建一个实例'''
data = [
("N1","2015-05-22",'M'),
("N2","2015-05-21",'M'),
("N3","2015-05-23",'F')
]
#执行SQL,并返回受影响行数,执行多次
cursor.executemany("insert into student (name,register_date,new_money) values(%s,%s,%s)",data)
'''将data数据插入到student表中'''
conn.commit()
'''提交才能生效'''
成功插入数据到student表中