MySQL数据库操作

MySQL 数据库操作

数据库操作

1.查看已有库

  show databases;

2.创建库(指定字符集)

  create database 库名 character set utf8;

  create database 库名 charset=utf8;

  create database 库名 default charset utf8 collate utf8_general_ci;

案例:创建stu数据库,编码为utf8

  create database stu character set utf8;

  create database stu charset=utf8;

3.查看创建库的语句(字符集)

  show create database 库名;

案例:查看stu创建方法  show create database stu;

4.查看当前所在库

  select database();

5.切换库

  use 库名;

案例:使用stu数据库  use stu;

6.删除库

  drop database 库名;

案例:删除test数据库   drop database test

 

7.库名的命名规则

  • 数字、字母、下划线,但不能使用纯数字
  • 库名区分字母大小写
  • 不能使用特殊字符和mysql关键字

库管理

代码

服务器状态

status;

查看整个数据库的编码集

show variables like 'charavter%';

创建库(制定字符集)

create database 库名 character set utf8;

create database 库名 charset=utf8;

create database 库名 default charset utf8 collate utf8_general_ci;

查看已有库

show databases;

切换库

use 库名;

查看创建库的语句

show create database 库名;

删除库

drop database 库名;

 

数据表的管理

1.表结构设计初步

【1】 分析存储内容
【2】 确定字段构成
【3】 设计字段类型

2.数据类型支持

数字类型:

  整数类型(精确值) - INTEGER,INT,SMALLINT,TINYINT,MEDIUMINT,BIGINT
  定点类型(精确值) - DECIMAL
  浮点类型(近似值) - FLOAT,DOUBLE
  比特值类型 - BIT

对于精度比较高的东西,比如money,用decimal类型提高精度减少误差。列的声明语法是DECIMAL(M,D)。

  M是数字的最大位数(精度)。其范围为1~65,M 的默认值是10。
  D是小数点右侧数字的数目(标度)。其范围是0~30,但不得超过M。
  比如 DECIMAL(6,2)最多存6位数字,小数点后占2位,取值范围-9999.99到9999.99。

比特值类型指0,1值表达2种情况,如真,假


字符串类型:

  • char和varchar类型
  • binary和varbinary类型
  • blob和text类型
  • enum类型和set类型

  • char 和 varchar

    char:定长,效率高,一般用于固定长度的表单提交数据存储,默认1字符
    varchar:不定长,效率偏低

  • text 和blob

    text用来存储非二进制文本
    blob用来存储二进制字节串

  • enum 和 set

    enum用来存储给出的一个值
    set用来存储给出的值中一个或多个值


表的基本操作:

创建表(指定字符集)

create table 表名(
字段名 数据类型,
字段名 数据类型,
...
字段名 数据类型
);

  • 如果你想设置数字为无符号则加上 unsigned
  • 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL, 在操作数据库时如果输入该字段的数据为NULL ,就会报错。
  • DEFAULT 表示设置一个字段的默认值
  • AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1。
  • PRIMARY KEY关键字用于定义列为主键。主键的值不能重复。

案例:创建班级表 

  create table class_1 (id int primary key auto_increment,name varchar(32) not null,age int not null,sex enum('w','m'),score float default 0.0);

案例:创建兴趣班表

  create table interest (id int primary key auto_increment,name varchar(32) not null,hobby set('sing','dance','draw'),course char not null,price decimal(6,2),comment text)

查看数据表

  show tables;

查看已有表的字符集

  show create table 表名;

查看表结构

  desc 表名;

删除表

  drop table 表名;

数据基本操作

插入(insert)

  insert into 表名 values(值1),(值2),...;

  insert into 表名(字段1,...) values(值1),...;

案例:insert into class_1 values (2,'Baron',10,'m',91),(3,'Jame',9,'m',90);

查询(select)

  select * from 表名 [where 条件];

  select 字段1,字段名2 from 表名 [where 条件];

案例:select * from class_1;

案例:select name,age from class_1

where子句

  where子句在sql语句中扮演了重要角色,主要通过一定的运算条件进行数据的筛选

  MySQL 主要有以下几种运算符:

    算术运算符
    比较运算符
    逻辑运算符
    位运算符

算数运算符

案例:select * from class_1 where age % 2 = 0;

比较运算符

案例:

  select * from class_1 where age > 8;

  select * from class_1 where between 8 and 10;

  select * from class_1 where age in (8,9);

逻辑运算符

案例:select * from class_1 where sex='m' and age>9;

位运算符

更新表记录(update)

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

案例:update class_1 set age=11 where name='Abby';

删除表记录(delete)

  delete from 表名 where 条件;

  注意:delete语句后如果不加where条件,所有记录全部清空

案例:delete from class_1 where name='Abby';

表字段的操作(alter)

  语法 :alter table 表名 执行动作;

* 添加字段(add)
  alter table 表名 add 字段名 数据类型;
  alter table 表名 add 字段名 数据类型 first;
  alter table 表名 add 字段名 数据类型 after 字段名; * 删除字段(drop) alter table 表名 drop 字段名; * 修改数据类型(modify) alter table 表名 modify 字段名 新数据类型; * 修改字段名(change) alter table 表名 change 旧字段名 新字段名 新数据类型; * 表重命名(rename) alter table 表名 rename 新表名;

案例:alter table interest add date Date after course;

表管理

代码

创建表

create table 表名(
  字段名 数据类型,
  字段名 数据类型,
  ...
  字段名 数据类型
);

查看数据表

show tables;

查看已有表的字符集

show create table 表名;

查看表结构

desc 表名;

插入(insert into)

insert into 表名 values(值1,值2,...),(值1,值2,...);

 

insert into 表名 (字段1,...) values(值1,...);

查询(select)

select 字段1,... from 表名 where 条件;

 

select 字段,字段2,... from 表名;(千万别用*)

更新表记录(update)修改

update set 字段1=值1,... where 条件;

删除表记录(delete)

delete from 表名 where 条件;

删除表(drop)

drop table 表名;

表字段的操作(alter)

 

添加字段(add)  默认末尾添加

alter table 表名 add 字段名 数据类型;

           第一处添加first

alter table 表名 add 字段名 数据类型 first;

     在哪个字段名后面添加after

alter table 表名 add 字段名 数据类型 after 字段名;

删除字段(drop)

alter table 表名 drop 字段名;

修改数据类型(modify)

alter table 表名 modify 字段名 新数据类型;

修改字段名(change)

alter table 表名 change 旧字段名 新字段名 新数据类型

(新旧字段名相同也可以修改数据类型)

表重命名(rename)

alter table 表名 rename 新表名;

 

时间类型数据

时间和日期类型:

  DATE,DATETIME和TIMESTAMP类型
  TIME类型
  年份类型YEAR

时间格式

  date :"YYYY-MM-DD"
  time :"HH:MM:SS"
  datetime :"YYYY-MM-DD HH:MM:SS"
  timestamp :"YYYY-MM-DD HH:MM:SS"
  注意
  1、datetime :不给值默认返回NULL值
  2、timestamp :不给值默认返回系统当前时间

日期时间函数

  • now() 返回服务器当前时间
  • curdate() 返回当前日期
  • curtime() 返回当前时间
  • date(date) 返回指定时间的日期
  • time(date) 返回指定时间的时间

时间操作

  • 查找操作

    select * from timelog where Date = "2018-07-02";

    select * from timelog where Date>="2018-07-01" and Date<="2018-07-31";

  • 日期时间运算

    • 语法格式

      select * from 表名 where 字段名 运算符 (时间-interval 时间间隔单位);

    • 时间间隔单位: 1 day | 2 hour | 1 minute | 2 year | 3 month

    案例:select * from timelog where shijian > (now()-interval 1 day);

高级查询语句

模糊查询和正则查询

LIKE用于在where子句中进行模糊查询,SQL LIKE 子句中使用百分号 %字符来表示任意字符。

  使用 LIKE 子句从数据表中读取数据的通用语法:

    select field1, field2,...fieldN

    from table_name

    where field1 LIKE condition1

  案例:mysql> select * from class_1 where name like 'A%';

  mysql中对正则表达式的支持有限,只支持部分正则元字符

    select field1, field2,...fieldN   

    from table_name   

    where field1 REGEXP condition1

  案例:select * from class_1 where name regexp 'B.+';

排序

ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序,再返回搜索结果。

使用 order by 子句将查询数据排序后再返回数据:

  select field1, field2,...fieldN from table_name1 where field1
  order by field1 [ASC [DESC]]

默认情况ASC表示升序,DESC表示降序

  select * from class_1 where sex='m' order by age;

分页

LIMIT 子句用于限制由 SELECT 语句返回的数据数量 或者 UPDATE,DELETE语句的操作数量

  带有 LIMIT 子句的 SELECT 语句的基本语法如下:

  select column1, column2, columnN   

  from table_name   

  where field   

  limit [num]

联合查询

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

  UNION 操作符语法格式:  

  SELECT expression1, expression2, ... expression_n

  FROM tables

  [WHERE conditions]

  UNION [ALL | DISTINCT]

  SELECT expression1, expression2, ... expression_n

  FROM tables

  [WHERE conditions];

 

  expression1, expression2, ... expression_n: 要检索的列。

  tables: 要检索的数据表。

  WHERE conditions: 可选, 检索条件。

  DISTINCT: 可选,删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据,所以 DISTINCT 修饰符对结果没啥影响。

  ALL: 可选,返回所有结果集,包含重复数据。

  要求查询的字段必须相同

  案例:select * from class_1 where sex='m' UNION ALL select * from class_1 where age > 9;

多表查询

多个表数据可以联合查询,语法格式如下

  select 字段1,字段2... from 表1,表2... [where 条件]

案例:

select class_1.name,class_1.age,class_1.sex,interest.hobby from class_1,interest where class_1.name = interest.name;

功能

代码

模糊查询

(like 'a%','_')

select * from 表名 where 字段名 like 'a%';

例句:select * from class_1 where name like 'A%';(查找class_1表中名字A开头的名字)

例句:select * from class_1 where name like '__';(一个'_'代表一个字符,查找class_1表中两个字符的名字)

正则查询(regexp)

例句:select * from class_1 where name regexp 'B.+';(regexp正则查询)

排序

(升序order by)

(降序order by  desc)

select * from 表名 where 条件 order by 字段名;

例句:select * from class_1 where sex='男' order by age;(查找class_1表中男性按年龄升序排序)

例句:select * from class_1 where sex='男' order by age desc;(查找class_1表中男性按年龄降序排序)

分页(limit)

select 字段名 from 表名 where 条件 order by 字段 limit offset,num;

条件:1.当前要看第多少页set @current=2(多少页)   2.每页显示多少条数据,@pageSize=2(页的条数)

select id,name,age,email from users limit (@current-1)*@pageSize,@pageSize;等于第二页第二条

select id,name,age,email from users limit 2,2;

例句:select * from class_1 where sex='男' order by age desc limit 1; (查找class_1表中男性按年龄降序排列  **显示一行**)

例句:select * from class_1 where sex='男' order by age desc limit 1; (查找class_1表中男性按年龄降序排列  **显示两行**)

联合查询

(union)

(union all)

例句:select * from student where sex='女' union select * from student where score >85;

(默认是或的意思 查找女生或85分以上的学生,默认去掉重复的数据)

例句:select * from student where sex='女' union all select * from student where score >85;

(查找女生和85分以上的学生,两次查找无关联,第二次会重新查找)

多表查询

select 表名.字段1,表名.字段2,... from 表1,表2,... where 条件;

select class_1.name,class_1.age,class_1.sex,interest.hobby from class_1,interest where class_1.name=interest.name;

(查找class_1表中的姓名,年龄,性别,interest表中的爱好 姓名相同的数据)

  

数据备份

1.备份命令格式

  mysqldump -u用户名 -p 源库名 > ~/***.sql     (要备份的库名  ***.sql 备份起的名 )

    --all-databases 备份所有库
    库名 备份单个库
    -B 库1 库2 库3 备份多个库
    库名 表1 表2 表3 备份指定库的多张表

2.恢复命令格式

  mysql -uroot -p 目标库名 < ***.sql    (目标库名是恢复到的库  ***.sql 备份的库 )
  从所有库备份中恢复某一个库(--one-database)

    mysql -uroot -p --one-database 目标库名 < all.sql

Python操作MySQL数据库

pymysql安装

  sudo pip3 install pymysql

pymysql使用流程

  1. 建立数据库连接(db = pymysql.connect(...))
  2. 创建游标对象(c = db.cursor())
  3. 游标方法: c.execute("insert ....")
  4. 提交到数据库 : db.commit()
  5. 关闭游标对象 :c.close()
  6. 断开数据库连接 :db.close()

常用函数

db = pymysql.connect(参数列表)

  host :主机地址,本地 localhost
  port :端口号,默认3306
  user :用户名
  password :密码
  database :库
  charset :编码方式,推荐使用 utf8

数据库连接对象(db)的方法

  db.commit() 提交到数据库执行
  db.rollback() 回滚
  cur = db.cursor() 返回游标对象,用于执行具体SQL命令
  db.close() 关闭连接

游标对象(cur)的方法

  cur.execute(sql命令,[列表]) 执行SQL命令
  cur.close() 关闭游标对象
  cur.fetchone() 获取查询结果集的第一条数据 (1,100001,"河北省")
  cur.fetchmany(n) 获取n条 ((记录1),(记录2))
  cur.fetchall() 获取所有记录

 1 import pymysql
 2 
 3 # 连接数据库
 4 db = pymysql.connect(host='localhost',
 5                      port=3306,
 6                      user='root',
 7                      passwd='123456',
 8                      database='stu',
 9                      charset='utf8')
10 
11 # 获取游标(用于进行数据操作的对象,承载操作结果)
12 cur = db.cursor()
13 
14 # 执行sql语句
15 sql = "insert into student \
16 (name,age,gender,score) values \
17 ('Lily',14,'w',79.5);"
18 cur.execute(sql)
19 
20 db.commit() # 将写操作提交到数据库
21 
22 # 关闭数据库
23 cur.close()
24 db.close()
操作数据库基本流程
 1 import pymysql
 2 
 3 # 连接数据库
 4 db = pymysql.connect(host='localhost',
 5                      port=3306,
 6                      user='root',
 7                      passwd='123456',
 8                      database='stu',
 9                      charset='utf8')
10 
11 # 获取游标(用于进行数据操作的对象,承载操作结果)
12 cur = db.cursor()
13 
14 # 存储文件
15 # with open('boy.jpg','rb') as fd:
16 #   data = fd.read()
17 #
18 # try:
19 #   sql="insert into Image values (1,'boy.jpg',%s)"
20 #   cur.execute(sql,[data])
21 #   db.commit()
22 # except Exception as e:
23 #   print(e)
24 #   db.rollback()
25 
26 # 获取图片
27 sql = "select * from Image \
28       where filename='boy.jpg'"
29 cur.execute(sql)
30 
31 # (1,name,xxxxx)
32 img = cur.fetchone()
33 with open(img[1],'wb') as f:
34   f.write(img[2])
35 
36 # 关闭数据库
37 cur.close()
38 db.close()
存储和获取二进制文件
 1 """
 2 select语句
 3 """
 4 
 5 import pymysql
 6 
 7 # 连接数据库
 8 db = pymysql.connect(host='localhost',
 9                      port=3306,
10                      user='root',
11                      passwd='123456',
12                      database='stu',
13                      charset='utf8')
14 
15 # 获取游标(用于进行数据操作的对象,承载操作结果)
16 cur = db.cursor()
17 
18 # 执行sql语句
19 sql = "select * from student where gender='m'"
20 cur.execute(sql) #执行查询后cur便会拥有查询结果
21 
22 # 获取一个查询结果
23 one_row = cur.fetchone()
24 print(one_row)
25 
26 # 获取多个查询结果
27 many_row = cur.fetchmany(2)
28 print(many_row)
29 
30 # 获取全部查询结果
31 all_row = cur.fetchall()
32 print(all_row)
33 
34 # 关闭数据库
35 cur.close()
36 db.close()
mysql数据库读操作演示
 1 """
 2 增删改为写操作
 3 """
 4 
 5 import pymysql
 6 
 7 # 连接数据库
 8 db = pymysql.connect(host='localhost',
 9                      port=3306,
10                      user='root',
11                      passwd='123456',
12                      database='stu',
13                      charset='utf8')
14 
15 # 获取游标(用于进行数据操作的对象,承载操作结果)
16 cur = db.cursor()
17 
18 # 执行sql语句
19 try:
20   # 插入数据
21   # name = input("name:")
22   # age = int(input('age:'))
23   # gender = input('gender:')
24   # score = float(input('score:'))
25 
26   # sql = "insert into student (name,age,gender,score) \
27   #       values ('%s',%d,'%s',%f)"%(name,age,gender,score)
28 
29   # sql = "insert into student (name,age,gender,score) \
30   #         values (%s,%s,%s,%s)"
31 
32 #   sql = "insert into interest values \
33 # (3,'Joy','draw','B',5488.0,'画的鸡蛋还行')"
34 #   cur.execute(sql,[name,age,gender,score])
35 
36   # 修改操作
37 #   sql = "update student set age=22 \
38 # where name = 'Lily'"
39 #   cur.execute(sql)
40 
41   # 删除操作
42   sql = "delete from student where score<60"
43   cur.execute(sql)
44 
45   db.commit()  # 可以执行多个sql语句一同提交
46 except Exception as e:
47   db.rollback() # 退回到commit之前的状态
48   print(e)
49 
50 # 关闭数据库
51 cur.close()
52 db.close()
mysql数据库写操作练习

 

posted @ 2019-07-07 19:34  maplethefox  阅读(1792)  评论(0编辑  收藏  举报