MySQL 数据库 2019-04-13

MySQL 数据库

MySQL 教程

  • 什么是数据库?

    数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,

    每个数据库都有一个或多个不同的API用于创建,访问,管理,搜索和复制所保存的数据。

    我们也可以将数据存储在文件中,但是在文件中读写数据速度相对较慢。

    所以,现在我们使用关系型数据库管理系统(RDBMS)来存储和管理的大数据量。所谓的关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。

  • RDBMS即关系数据库管理系统(Relational Database Management System)的特点:

    1.数据以表格的形式出现

    2.每行为各种记录名称

    3.每列为记录名称所对应的数据域

    4.许多的行和列组成一张表单

    5.若干的表单组成database

  • 数据库作用

    1. 持久保存

    2. 方便查询并提取满足条件的数据, 数据访问速度快

    3. 处理并发访问

    4. 权限管理

  • 常见数据库:

    关系型数据库 MySQL ->库->表->数据 MySQL Oracle PostgreSQL SQL Server ....

    非关系型数据库 Redis ->数据 MongoDB ... 概念:

    数据: data

    数据库: DB

    数据库管理系统:DBMS

    数据库系统:DBS

一、MySQL基础操作

数据库管理系统中, 可以有很多库, 每个数据库中可以包括多张数据表

  • 操作数据库的步骤

    连接, 打开库, 操作, 关闭退出

  • SQL ( Structure query language ) 结构化查询语言

    SQL语言分为4个部分:
        DDL(Data Definition Languages) :数据的定义
        DML(Data Manipulation Language):数据的操作        
        DQL(Data Query Language)       :数据的查询操作
        DCL(Date Control Language)     :数据控制语句
    
  • SQL语句中的快捷键

    \G 格式化输出(文本式,竖立显示)

    \s 查看服务器端信息

    \c 结束命令输入操作

    \q 退出当前sql命令行模式

    \h 查看帮助

  • MySQL 语法特点

    1. SQL 语句可以换行, 要以分号结尾
    2. 命令不区分大小写. 关键字和函数建议用大写
    3. 如果提示符为 '> 那么需要输入一个'回车
    4. 命令打错了换行后不能修改, 可以用 \c 取消

二、MySQL 数据类型

1. 数值类型

类型 大小 范围(有符号) 范围(无符号) 用途
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) 极大整数值
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的值 小数值

2. 日期和时间类型

类型 大小(字节) 范围 格式 用途
DATA 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:00/2037 年某时 YYYYMMDD HHMMSS 混合日期和时间值,时间戳

3. 字符串类型

类型 大小 用途
CHAR 0-255字节 定长字符串
VARCHAR 0-65535 字节 变长字符串
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65 535字节 二进制形式的长文本数据
TEXT 0-65 535字节 长文本数据
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据
LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据

三、MySQL 运算符

算术运算符:+ - * / %

比较运算符:= > < >= <= <> !=

数据库特有的比较:in,not in, is null,is not null,like, between and

逻辑运算符:and or not

like: 支持特殊符号%和_ ; 其中 %表示任意数量的任意字符,_表示任意一位字符。


四、MySQL 库操作

  1. 查看数据库 show databases;
  2. 创建数据库 create database 库名 default charset=utf8;
  3. 打开数据库 use 库名;
  4. 查看当前库 select database();
  5. 删除数据库 drop database 库名;

五、MySQL 表操作

1. 字段约束

unsigned : 无符号

int(4) : 显示宽度

zerofill : 零填充, 位数不够的时候用前导零填充

not null : 不能为空 在操作数据库时如果输入该字段的数据为NULL ,就会报错

default : 设置默认值

auto_increment : 定义列为自增的属性,一般用于主键,数值会自动加1。

primary key : 主键 不能为空 唯一性

unique : 唯一索引 可以为空 唯一性

index : 普通索引 可以为空 可以重复

2. 基本操作

查看当前库中所有表:show tables;

创建表 :create table 表名(字段1 类型 [字段约束],字段2 类型 [字段约束]...)default charset=utf8;

查看表结构:desc 表名;

查看建表语句:show create table 表名\G

删除表:drop table 表名;

查看当前表中所有数据: select * from 表名;

3. 表数据操作

3.1 增删改

数据的DML操作:添加数据,修改数据,删除数据

  • 插入:格式 insert into 表名[(字段列表)] values(值列表...);

    ​ 1. 标准添加: insert into 表名(所有字段) values(对应的值);

​ 2. 指定部分字段添加值: insert into 表名 (部分字段) value (对应的值);

​ 3. 不指定字段添加值: insert into 表名 value(值1,值2,...);

​ 4. 批量添加值: insert into 表名 values(a值1,a值2,...),(b值1,b值2,...);

  • 删除:delete from 表名 where 字段=某个值;

  • 修改:update 表名 set 字段=某个值 where 条件;

update 表名 set 字段1=值1,字段2=值2 where 条件;

update 表名 set 字段=字段+值 where 条件;

3.2 查询

书写流程:where group by -->having order by limit

数据的DQL操作:数据查询

 select [字段列表]  /*  from 表名

    [where 搜索条件]

    [group by 分组字段 [having 子条件]]

    [order by 排序 asc|desc]

    [limit 分页参数];
  • 基础查询 : select * from 表名;

  • where条件查询

    • 你可以在 WHERE 子句中指定任何条件。
    • 你可以使用 AND 或者 OR 指定一个或多个条件。
    • WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
    • WHERE 子句类似于程序语言中的 if 条件,根据 MySQL 表中的字段值来读取指定的数据。
    查询lamp138和lamp94期的女生信息
    mysql> select * from stu where classid in('lamp138','lamp94') and sex='w';
    
  • like子句

    • LIKE 通常与 % 一同使用,类似于一个元字符的搜索。
    • 你可以使用 AND 或者 OR 指定一个或多个条件。
    • 你可以在 DELETE 或 UPDATE 命令中使用 WHERE...LIKE 子句来指定条件。
    • 可以使用regexp正则来代替 like
    查询name字段值是以zh开头的所有信息
    mysql> select * from stu where name like "zh%";
    
  • MySQL的统计函数(聚合函数): select max(),min(),sum()avg(),count(*) from 表名;

  • GROUP BY 语句 分组 和 having

    • GROUP BY 语句根据一个或多个列对结果集进行分组。
    • 在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
    • having 对分组后的数据进行删选
    MySQL> select classid,sex,count(*) from stu  group by classid,sex;
    
  • ORDER BY 排序 ------ asc 升序 / desc 降序

    • 可以使用任何字段来作为排序的条件,从而返回排序后的查询结果。
    • 可以设定多个字段来排序。
    • 可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认为asc 按升序排列
    • 可以添加 WHERE...LIKE 子句来设置条件。
    SELECT field1, field2,...fieldN table_name1, table_name2...
    ORDER BY field1, [field2...] [ASC [DESC]]
    
  • limit 关键字 查询部分数据

    • limit m; 查询数据只显示前m条
    • limit m,n; 排除前m条,然后再查询出前n条

4. 表结构操作

格式: alter table 表名 action(更改选项);

添加字段:alter table 表名 add 字段名 类型 [字段约束];

删除字段:alter table 表名 drop 字段名;

修改字段:change 可改字段名和类型; modify 可改字段类型, 不可改字段名
        alter table 表名 change 原字段名 修改后的字段名 类型 [字段约束];
        alter table 表名 modify 字段名 修改后的类型 [字段约束];

添加唯一性索引:alter table user add unique uni_name(name);

添加普通索引:alter table user add index index_email(email);

删除索引:alter table user drop index index_email;

修改表名:alter table 表名 rename as 新表名;

更改auto_increment初始值:alter table 表名称 auto_increment=数值;

更改表类型:alter table 表名 engine=‘innodb’;

          alter table 表名 engine='myisam';

六、数据的导入和导出

1. 数据导出

-- 导出整个数据库
mysqldump -u 用户名 -p 数据库名 >./导出的文件名.sql
Enter password:
-- 导出一个表
mysqldump -u 用户名 -p 数据库名 表名 >./导出的文件名.sql
Enter password:

2. 数据导入

-- 将数据库导入
-- 需在本地数据库中创建相对应同名的数据库
mysql -u 用户名 -p 数据库名<./文件名.sql
Enter password:


七、授权、改密

1. 授权

格式:grant 允许操作 on 库名.表名 to 账号@来源 identified by '密码';

实例:创建zhangsan账号,密码123,授权lamp61库下所有表的增/删/改/查数据,来源地不限
 mysql> grant select,insert,update,delete on lamp61.* to zhangsan@'%' identified   by '123';
 mysql> grant all on *.* to zhangsan@'%' identified by '123';
 Query OK, 0 rows affected (0.00 sec)

 添加用户, 并授予在lamp189的数据库中可以对user表进行 查询和添加数据操作
 GRANT select,insert ON lamp189.user TO 'xxoo'@'%' IDENTIFIED BY 'abcd'

 删除用户
 drop user 'xxoo'@'%'

2. 改密

1.找到mysql配置文件 C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
2.在[mysqld] 下面添加 skip-grant-tables 保存
3.重启mysql服务
4.打开命令行 mysql -uroot -p  不用输入密码
5.use mysql 切换到权限库
6.执行修改密码 Update user set authentication_string=password('1234567') where user='root';
7:执行刷新权限 flush privileges;
修改成功后:要把配置文件改回来 然后在重启服务
使用新密码链接数据库

八、Python 连接数据库

Python3 MySQL 数据库连接

1. PyMySQL安装

pip install pymysql

2. 连接数据库

通过如下代码测试数据库连接

 import pymysql
 
 # 打开数据库连接
 db = pymysql.connect("localhost","root","123456","mydb" )

 # 使用 cursor() 方法创建一个游标对象 cursor
 cursor = db.cursor()

 # 使用 execute()  方法执行 SQL 查询 
 cursor.execute("SELECT VERSION()")

 # 使用 fetchone() 方法获取单条数据.
 data = cursor.fetchone()

 print ("Database version : %s " % data)

 # 关闭数据库连接
 db.close()

3. 执行数据添加

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","root","123456","mydemo" )

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# SQL 插入语句
sql = "INSERT INTO stu(name,sex,age,classid) values('%s','%c','%d','%s')" % ('uu142','m',22,'lamp180') 

try:
   # 执行sql语句
   cursor.execute(sql)
   # 执行sql语句
   db.commit()
   print("ok: %d " % (cursor.rowcount))
except:
   # 发生错误时回滚
   db.rollback()

# 关闭数据库连接
db.close()

4. 执行删除操作

 import pymysql

 # 打开数据库连接
 db = pymysql.connect("localhost","root","","mydemo" )

 # 使用 cursor() 方法创建一个游标对象 cursor
 cursor = db.cursor()

 # SQL 删除语句
 sql = "delete from stu where id = '%d'" % (13)
 try:
    # 执行SQL语句
    cursor.execute(sql)
    # 提交修改
    db.commit()
 except:
    # 发生错误时回滚
    db.rollback()

 # 关闭数据库连接
 db.close()

5. 执行数据修改/更新

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 更新语句
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
                          WHERE SEX = '%c'" % ('M')
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

# 关闭数据库连接
db.close()

6. 执行数据查询

Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。

fetchone(): 该方法获取下一个查询结果集。结果集是一个对象

fetchall(): 接收全部的返回结果行.

rowcount: 这是一个只读属性,并返回执行execute()方法后影响的行数。

 import pymysql

 # 打开数据库连接
 db = pymysql.connect("localhost","root","","mydemo" )

 # 使用 cursor() 方法创建一个游标对象 cursor
 cursor = db.cursor()

 # SQL 查询语句
 sql = "select * from stu limit %d" % (3)
 #sql = "select * from stu"

 try:
    # 执行SQL语句
    cursor.execute(sql)
    # 获取所有记录列表
    results = cursor.fetchall()
    for row in results:
       id = row[0]
       name = row[1]
       sex = row[2]
       age = row[3]
       classid = row[4]
        # 打印结果
       print ("id=%d,name=%s,sex=%s,age=%d,classid=%s" % (id,name,sex,age,classid))
 except:
    print ("Error: unable to fetch data")

 # 关闭数据库连接
 db.close()
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 204,684评论 6 478
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 87,143评论 2 381
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 151,214评论 0 337
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 54,788评论 1 277
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 63,796评论 5 368
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,665评论 1 281
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 38,027评论 3 399
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,679评论 0 258
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 41,346评论 1 299
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,664评论 2 321
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,766评论 1 331
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,412评论 4 321
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 39,015评论 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,974评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,203评论 1 260
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 45,073评论 2 350
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,501评论 2 343

推荐阅读更多精彩内容