2-1数据库介绍
为什么要学习数据库?
通常,我们存储数据,直接用本地文件即可。但是,本地文件不利于存放海量数据,也不利于用程序对文件的数据进行查询与管理。那么为了解决这些弊端,才有数据库的出现,那么数据库也是每个程序员必须掌握的技术。
数据库介绍
数据库(database)简称DB,实际上就是一个文件集合,是一个存储数据的仓库,本质就是一个文件系统,数据库是按照特定的格式把数据存储起来,用户可以对存储的数据进行增删改查等操作。
数据库存储数据特点
- 持久化存储
- 读写速度极高
- 保证数据的有效性
- 对程序支持非常好,容易扩展
数据库的分类
- 关系型数据库:可以保存现实生活中的各种关系数据,数据库中存储数据以表为单位;主流关系型数据库:MySQL,Oracle,SQLServer等
- 非关系型数据库:通常用来解决某些特定的需求,比如高并发访问。主流非关系型数据库:Redis,Mongodb,memacache等
2-2MySQL介绍
MySQL介绍
MySQL是一种开放源代码的关系型数据库管理系统(RDBMS),使用最常用的数据库管理语言——结构化查询语言(SQL)进行数据库管理。MySQL因为其速度、可靠性和适应性而备受关注。大多数人都认为在不需要事务化处理的情况下,MySQL是管理内容最好的选择。
图形界面管理工具
常用工具
SQLyog介绍
SQLyog 是一个快速而简洁的图形化管理MYSQL数据库的工具,它能够在任何地点有效地管理数据库,由业界著名的Webyog公司出品。也是一个开源软件。
开源软件(open source software),简称为OSS,公开源代码的软件。因此开源软件具备可以免费使用和公布源代码的特征。
注意:SQLyog只是一个连接数据库的工具,并不是数据库
2-3语法基础
SQL
SQL介绍
SQL是结构化查询语言,是一种用来操作RDBMS(关系型数据库管理系统)的数据库语言,当前关系型数据库都支持使用SQL语言进行操作,也就是说可以通过SQL操作oracle,sql server,mysql等关系型数据库
SQL语句主要分为
- DQL:数据查询语言,用于对数据进行查询
- DML:数据操作语言,对数据进行增加、修改、删除
- DDL:数据定义语言,进行数据库、表的管理等
数据完整性
在表中为了更加准确的存储数据,保证数据的正确有效,可以在创建表的时候,为表添加一些强制性的验证,包括数据字段的类型、约束。
数据类型
常见数据类型
- 整数:int
- 小数:decimal
- 字符串:varchar,char
- 日期时间:date, time, datetime
- 枚举类:enum
数值类型
整数类型 | 有符号范围 | 无符号范围 |
---|---|---|
TINYINT(size) | -128~127 | 0~255 |
SMALLINT(size) | -32768~32767 | 0~65535 |
MEDIUMINT(size) | -8388608~8388607 | 0~16777215 |
INT(size) | -2147483648~2147483647 | 0~4294967295 |
BIGINT(size) | -9223372036854775808~9223372036854775807 | 0~18446744073709551615 |
小数类型 | 描述 |
---|---|
FLOAT(size,d) | 带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 |
DOUBLE(size,d) | 带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 |
DECIMAL(size,d) | 作为字符串存储的 DOUBLE 类型,允许固定的小数点。(定点数类型) |
字符串类型
字符串类型 | 字节大小 | 示例 |
---|---|---|
CHAR(size) | 0-255 | char(3)输入’ab’,实际存储为’ab ‘,输入’abcd’,实际存储为’abc’ |
VARCHAR(size) | 0-65535 | varchar(3)输入’ab’,实际存储为’ab’,输入’abcd’,实际存储为’abc’ |
枚举类型
枚举类型英文为ENUM,对1255个成员的枚举需要1个字节存储;对于25565535个成员,需要2个字节存储。最多允许65535个成员。创建方式:enum(“M”,“F”);
日期类型
数据类型 | 描述 |
---|---|
DATE() | 日期。格式:YYYY-MM-DD 注释:支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’ |
DATETIME() | 日期和时间的组合。格式:YYYY-MM-DD HH:MM:SS 注释:支持的范围是从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ |
TIME() | 时间。格式:HH:MM:SS 注释:支持的范围是从 ‘-838:59:59’ 到 ‘838:59:59’ |
YEAR() | 2 位或 4 位格式的年。 4 位格式:1901 到 2155。2 位格式:70 到 69,表示从 1970 到 2069 |
TIMESTAMP() | 时间戳。格式:YYYY-MM-DD HH:MM:SS 注释:支持的范围是从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC |
注意
- decimal表示定点小数,如decimal(5,2)表示共存5位数,小数占2位。不写则默认为decimal(10,0)
- char表示固定长度的字符串,如char(3),如果填充’ab’时会补一个空格为’ab ’
- varchar表示可变长度的字符串,如varchar(3),填充’ab’时就会存储’ab’
- 对于图片、音频、视频等文件,不存储在数据库中,而是上传到某个服务器上,然后在表中存储这个文件的保存路径
- 枚举类型不区分大小写
详细数据类型可参考:
http://blog.csdn.net/anxpp/article/details/51284106
约束
- 主键primary key:物理上存储的顺序
- 非空not null:此字段不允许填写空值
- 惟一unique:此字段的值不允许重复(身份证号,银行卡号等)
- 默认default:当不填写此值时会使用默认值,如果填写时以填写为准
- 外键foreign key:对关系字段进行约束,当为关系字段填写值时,会到关联的表中查询此值是否存在,如果存在则填写成功,如果不存在则填写失败并抛出异常
2-4SQL语句
SQL语法
操作数据库(DDL)
创建数据库
CREATE DATABASE 数据库名;
CREATE DATABASE 数据库名 charset=utf8;
注意
- charset:指定编码为utf8
查看创建数据库的命令
SHOW CREATE DATABASE 数据库名;
查看已经创建的数据库
SHOW DATABASES;
查看当前使用的数据库
SELECT DATABASE();
使用数据库
USE 数据库名;
删除数据库
DROP DATABASE 数据库名;
练习
- 创建数据库:School
- 查看已创建的数据库
- 查看当前使用的数据库是否是School
- 若不是,则切换使用School数据库
- 删除School数据库
注意
- SQL语句不区分大小写
- 每行必须以英文状态下分号作为结束
- 当数据库名称有特殊字符时,使用``包括。如:
练习代码如下:
CREATE DATABASE School; -- 创建数据库 1.不完全区分大小写 2.以英文状态下分号结束
SHOW DATABASES; -- 查看已经创建的数据库
SELECT DATABASE(); -- 查看正在使用的数据库
USE `Shcool`; -- 使用School数据库
DROP DATABASE School; -- 删除School数据库
操作数据表
查看当前数据库中的所有表
SHOW TABLES;
创建表
CREATE TABLE 数据表名(字段 类型 约束[, 字段 类型 约束])
查看表的创建语句
SHOW CREATE TABLE 数据表名;
查看表描述信息
DESC 数据表名;
练习
- 使用SQL代码创建Student表
- 且表结构如下图
- 查看表的描述信息
练习代码如下:
CREATE TABLE students(
id INT PRIMARY KEY NULL AUTO_INCREMENT,
NAME VARCHAR(30),
age TINYINT(3) DEFAULT 0,
gender ENUM("男","女","保密") DEFAULT "保密"
); -- 创建students表
DESC students; -- 查看表的描述信息
添加表字段
ALTER TABLE 数据表名 ADD 字段 类型;
修改表字段
ALTER TABLE 数据表名 MODIFY 字段 类型; – 不重命名
ALTER TABLE 数据表名 CHANGE 原字段名 新字段名 类型及约束; – 将字段重命名
删除表字段
ALTER TABLE 数据表名 DROP 字段;
练习
- 添加字段
- 字段名称:birthday
- 字段类型:DATETIME
- 修改字段
- 字段类型:DATE
- 修改字段
- 字段名称:birth
- 字段类型:DATE
- 删除该字段
练习代码如下:
ALTER TABLE students ADD birthday DATETIME; -- 添加字段
ALTER TABLE students MODIFY birthday DATE; -- 修改字段
ALTER TABLE students CHANGE birthday birth DATE; -- 修改字段
ALTER TABLE students DROP birth; -- 删除字段
操作数据
新增数据
整行插入
INSERT INTO 数据表名 VALUES(值1,值2,值3…);
指定列中插入数据
INSERT INTO tabname (字段1, 字段2,…) VALUES (值1,值2,…);
指定列中插入多条数据
INSERT INTO 数据表名 (字段1, 字段2,…) VALUES (值1,值2,…),(值1,值2,…);
练习
- Student表中添加整行数据
- Student表中添加name,gender两个字段的数据
- Student表中同时添加多行数据
练习代码如下:
-- 插入数据
INSERT INTO students VALUES(1,"amy",18,"女"); -- 插入整行数据
INSERT INTO students (NAME,gender) VALUES("灰太狼","男"); -- 插入name,gender两个字段的数据
INSERT INTO students (NAME) VALUES("灰太狼"); -- 字段一定要用()括起来 否则会报错
INSERT INTO students (NAME,gender) VALUES("hehe",1),("rose",2) -- 同时添加多行数据 枚举类型可以用索引表示 但是注意从1开始
修改数据
UPDATE 数据表名 SET 字段1=新值,字段2=新值 [WHERE 条件];
练习
- 将姓名全部修改为jack
- 将性别为女的名字修改为rose
- 将id为3的年龄修改为22,并且性别改为男
练习代码如下:
-- 修改数据
UPDATE students SET NAME="jack"; -- 将姓名全部改为jack
UPDATE students SET NAME="rose" WHERE gender="女"; -- 将性别为女的名字修改为rose
UPDATE students SET age=22,gender="男" WHERE id=3; -- 将id为3的年龄修改为22,并且性别改为男
删除数据
DELETE FROM 数据表名 [WHERE 条件];
练习
- 将姓名为rose的数据删除
- 清空表数据
练习代码如下:
-- 删除数据
DELETE FROM students WHERE id=2; -- 将id=2的数据删除 物理删除 真正意义上的删除数据
-- 逻辑删除 通过判断一个字段的值 给用户呈现是否删除的现象 实际上这个数据并没有删除
-- is_del 默认值 0 未删除 is_del=1 已删除
ALTER TABLE students ADD is_del INT DEFAULT 0; -- 添加字段
UPDATE students SET is_del=1 WHERE id=4; -- 将id=4的用户逻辑删除(注销)
DELETE FROM students; -- 清空表数据
查询数据
查询整个表数据
SELECT * FROM 数据表名;
查询指定字段数据
SELECT 字段1,字段2 FROM 数据表名;
查询指定字段数据,并给字段起别名
SELECT 字段1 as 别名,字段2 as 别名 FROM 数据表名;
查询指定字段并去重
SELECT DISTINCT 字段1 FROM 数据表名;
练习
- 查询Student表所有数据
- 查询Student表中name与gender字段的数据
- 查询Student表中name字段的数据并且去重
练习代码如下:
-- 查询数据
SELECT * FROM students; -- 查询students表所有数据 不建议使用通配符*
SELECT NAME,gender FROM students; -- 查询students表中name与gender字段的数据
SELECT (NAME,gender) FROM students; -- 报错
SELECT (NAME) FROM students; -- 可以
SELECT NAME AS "姓名",gender AS "性别" FROM students; -- 重命名 但是原本的名字未改变
SELECT s.`name`,s.`gender` FROM students AS s; -- 给表重命名,原名也为改变
SELECT DISTINCT NAME FROM students; -- 查询students表中name字段的数据并且去重
SELECT DISTINCT NAME,age FROM students; -- 一行一行的比较的,两者都一样的才去重
SELECT DISTINCT (NAME,age) FROM students; -- 报错
where子句
where子句通常结合增删改查使用,用于做筛选的条件。
比如,查询当id=1的数据
select * from Student where id=1
不仅如此,经常结合运算符使用。
比较运算符
运算符 | 描述 |
---|---|
= | 等于 |
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
!=或<> | 不等于 |
练习
- 查询id大于3的数据
- 查询年龄大于18岁的信息
- 查询姓名不是Amy的数据
练习代码如下:
-- 查询语句
SELECT * FROM students WHERE id>3; -- 查询id大于3的数据
SELECT * FROM students WHERE age>18; -- 查询年龄大于18岁的信息
SELECT * FROM students WHERE NAME!="Amy"; -- 查询姓名不是Amy的数据
逻辑运算符
运算符 | 描述 |
---|---|
a and b | 当同时满足a,b时,则为True |
a or b | 当满足a或b其中一个时,则为True |
not a | 否定 |
练习
- 查询age18~22之间的所有学生信息
- 查询id大于3的女同学
- 查询id小于2或者id大于4的学生信息
- 查询年龄不是18的女同学
练习代码如下:
-- 查询语句
SELECT * FROM students WHERE age>=18 AND age<=22; -- 查询age18~22之间的所有学生信息
SELECT * FROM students WHERE id>3 AND gender="女"; -- 查询id大于3的女同学
SELECT * FROM students WHERE id<2 OR id>4; -- 查询id小于2或者id大于4的学生信息
SELECT * FROM students WHERE (NOT age=18) AND gender="女"; -- 查询年龄不是18的女同学
模糊查询
like关键字用来进行模糊查询,并且结合%以及_使用。
- % 表示任意多个任意字符
- _ 表示一个任意字符
练习
- 查询名字以a开始的学生信息
- 查询名字含有a的学生信息
- 查询名字仅有2个字符的学生信息
- 查询名字至少有2个字符的学生信息
练习代码如下:
-- 查询语句
SELECT * FROM students WHERE NAME LIKE "a%"; -- 查询名字以a开始的学生信息
SELECT * FROM students WHERE NAME LIKE "%a%"; -- 查询名字含有a的学生信息
SELECT * FROM students WHERE NAME LIKE "__"; -- 查询名字仅有2个字符的学生信息
SELECT * FROM students WHERE NAME LIKE "__%"; -- 查询名字至少有2个字符的学生信息
范围查询
关键字 | 描述 |
---|---|
in | 表示在一个非连续的范围内 |
between…and… | 表示在一个连续的范围内 |
练习
- 查询id是1或者4或者6的学生信息
- 查询年龄为18或者20的学生信息
- 查询年龄不是18,20的学生信息
- 查询id是2至4的学生信息
- 查询id是3-5的男同学信息
- 查询年龄不在18至20之间的学生信息
练习代码如下:
-- 查询语句
SELECT * FROM students WHERE id IN(1,4,6); -- 查询id是1或者4或者6的学生信息
SELECT * FROM students WHERE age IN(18,20); -- 查询年龄为18或者20的学生信息
SELECT * FROM students WHERE age NOT IN(18,20); -- 查询年龄不是18,20的学生信息
SELECT * FROM students WHERE id BETWEEN 2 AND 4; -- 查询id是2至4的学生信息
SELECT * FROM students WHERE (id BETWEEN 2 AND 4) AND gender="男"; -- 查询id是3-5的男同学信息
SELECT * FROM students WHERE NOT age BETWEEN 18 AND 20; -- 查询年龄不在18至20之间的学生信息
空判断
关键字 | 描述 |
---|---|
is null | 判断空 |
is not null | 判断非空 |
练习
- 查询没有填写年龄的学生
练习代码如下:
SELECT * FROM students WHERE NAME IS NULL;
2-5Python操作mysql
安装pymysql
- Python3:pip install pymysql
Python操作MySQL步骤
connection对象
connection对象用于建立与数据库的连接
pymysql.connect(host=None, port=0, database=None, user=None, password="",charset=’’)
- host:连接数据库地址
- port:连接端口
- database:连接数据库
- user:连接用户名
- password:连接密码
- charset:字符集编码
import pymysql
conn = pymysql.connect(host="localhost", port=3306, database="Logic_edu", user="root", password="root", charset="utf8")
实际上,我们可以通过conn这个对象进行关闭连接,提交等。具体方法如下
- conn.close() 关闭连接
- conn.commit() 提交
- conn.cursor() 返回cursor对象,用于执行sql语句并获得结果
cursor对象
conn.cursor()从连接对象中获取游标对象
代码如下
cs = conn.cursor()
实际上,我们可以通过cs这个对象关闭游标,执行sql语句等。具体方法如下
- cs.close() 关闭游标
- cs.execute(operation [, parameters ]) 执行语句,返回受影响的行数
- cs.fetchone() 执行查询语句时,获取结果集第一行数据,且返回元组
- fetchall() 执行查询语句时,获取结果集的所有行,且为二维元组
查询数据
import pymysql
conn = pymysql.connect(host="localhost", port=3306, database="Logic_edu", user="root", password="root", charset="utf8")
cs = conn.cursor()
res = cs.execute("select * from student")
# res1 = cs.fetchall() # 获取所有
# res2 = cs.fetchone() # 获取一行
res3 = cs.fetchmany(2) # 获取多行
cursor.close()
conn.close()
新增数据
import pymysql
conn = pymysql.connect(host="localhost",port=3306,database="logic_edu",user="root",password="root",charset="utf8")
sql = 'insert into student (name) values("张三")'
cursors = conn.cursor()
res = cursors.execute(sql)
conn.commit()
修改数据
import pymysql
conn = pymysql.connect(host="localhost",port=3306,database="logic_edu",user="root",password="root",charset="utf8")
sql = 'update student set name = "老王" where id = 1'
cursors = conn.cursor()
cursors.execute(sql)
conn.commit()
删除数据
import pymysql
conn = pymysql.connect(host="localhost",port=3306,database="logic_edu",user="root",password="root",charset="utf8")
sql = 'delete from student where id=1'
cursors = conn.cursor()
cursors.execute(sql)
conn.commit()