mysql

转载请注明出处https://blog.csdn.net/weixin_45163516

ubuntu下面安装mysql

• 安装：sudo apt-get install mysql-server
  • 需要设置root用户的密码,后面登录的时候需要使用
  • 安装完以后，会自动启动，若没有启动可以手动操作：sudo service mysql start
  • ubuntu18.0.4在安装mysql的时候是没有提示输入密码的，密码文件/etc/mysql/debain.cnf
  • 链接数据库：mysql -h localhost -u root -p

数据库的简介

• 用途：用于存储生活中的一切数据，，如身份证，住宿，票务，网站，银行

• 分类：

  • 关系型数据库：MySQL,oracle,SQLserver , sqlite… …
  • 非关系型数据库：Redis,MongoDB

• 概念：数据库的服务器，数据库，数据表,数据，字段

• SQL:structured query language,结构化查询语言

• 命令：

  • 数据定义语言（DDL）：创建，删除，修改（definition 定义）
  • 数据操作语言（DML）:增， 删， 改 （manipulation 操作）
  • 数据查询语言（DQL）:查 （query 查询）
  • 数据控制语言（DCL）:授权，取消授权 （control 控制）
  • 数据事务语言（DTL）:开启事务，提交事务，回滚 （transaction 事务）

• 数据定义语言

• 查看库：show databases;, 会显示连接的数据库的服务器上面的额所有的数据库

• 创建数据库：create database test（库名）;,创建一个叫test的数据库

• 删除数据库：drop database goudan(库名)；，删除goudan这个数据库（在使用的时候想好）

• 选择数据库：use test;,选择一个叫test的数据库

  • 查看当前的数据库
    • show tables; 显示数据库里面的所有的表；

• 创建表：create table user(username varchar(20), password char(32));

• 查看表的结构：desc user;

• 查看服务器的字符集和存储引擎

  • 存储引擎：innodb,myisam

  • 查看当前的字符集：show variables like “character%”;(utf8)

  • 查看存储引擎：show varables like “%storage%”;(InnoDB)

  • ubuntu 下面的修改配置文件:/etc/mysql/mysql.conf.d/mysqld.cnf

    ....
    [mysqld]
    
    #字符集
    character-set-server = utf8
    #存储引擎
    default-storage-engine = InnoDB
    
    #重启才能生效
    

• 查看创建语言

  • 查看库：show create database test;(几乎不用)

  • 查看表的创建语言： show create table user;(几乎不用 )

• 删除表：drop table user;

• 修改表结构（alter）

  • 修改字段类型：alter table user modify useranme varchar(20);

  • 修改字段名字：alter table user change password pwd char(32);

  • 添加新的字段：

    • 默认在末尾添加：alter table user add age int;
    • 在指定的字段后面添加：alter table user add email varchar(64) after pwd;
    • 在开头进行字段添加：alter table user add id int first;
    • after和first也适用于modify和change

  • 删除指定的字段：alter table user drop age;

  • 修改表的名字:alter table user rename new_user;

注意：在数据库的命令中一定要加英文的半角的分号

数据类型

• 整型

  类型	说明
  tinyint	1字节
  smallint	2个字节
  mediumint	3个字节
  int（常用）	4个字节
  bigint	8个字节

• 浮点类型

  类型	说明
  float（m, d）	单精度浮点数，4个字节，m表示总位数，d表示的是小数点后面的几位
  double(m, d)	双精度浮点数，8个字节，同上
  decimal(m, d)	以字符串类型的形式存储浮点数，多用于金融行业

• 字符串类型

  类型	说明
  varchar	变长的字符串，0-65535字节
  char	定长的字符串，0-255字节
  text	文本类型比如：博客项目中的博文

• 时间类型

  类型	说明
  date	日期，格式：“2019-08-12”，3个字节
  time	时间：格式：“15:01:37”，3个字节
  datetime	日期时间，格式：“2019-08-12 15:01:37 ”，8个字节
  timestamp	时间戳，4个字节，1970-现在
  year	年，只占1个字节

• 字段的修饰

类型	说明
unsigned	无符号
zerofill	高位填充0，可以防止负数的出现
default	默认值
not null	不能为空
auto_increment	自动增加1，用于整形的字段，常和主键一起使用

• 字符集：

  • 查看系统的字符集：show character set;

• 存储引擎

  • 查看系统支持的引擎：show engines;

  • 常用的存储引擎有：

    存储引擎	说明
    InnoDB	适合多写的操作 ，支持的事务的(常用)
    MyISAM	适合多读的操作

• 索引管理（重要）

  • 说明：简单理解就是一本书的目录，可以读取效率，但是也不是越多越好

  • 分类：

    索引	说明
    普通索引	index，最基本的索引
    唯一索引	unique,修饰的字段不能重复
    主键索引	primary key,是一个特殊的唯一索引，一张表只能有一个主键（很常用）
    全文索引	fulltext，对全局的数据添加索引（很少用）

  • 添加索引：alter table new_user add index(email);

  • 删除索引：alter table new_user drop index email;

  • 创建表的时候，最标准写法：

    create table user(
    	id int auto_increment,
    	name varchar(20),
    	primary key(id),
    	unique(name)
    	)engine=InnoDB default charset=UTF8;
    

数据操作语言（DML）

Data Manipulation Language

• 说明：在大多数操作数据库中都是增删改

• 准备：一张用于测试的表

   create table star(
   id int auto_increment,
   name varchar(20) not null,
   money float not null,
   province varchar(20) default null,
   age tinyint unsigned not null,
   sex tinyint not null,
   primary key(id)
   )engine=innodb default charset=utf8;
  

• 插入数据

  • 方式1：不指定字段,添加数据的时候需要指定所有的字段值，

    insert into star values(1,'蒋龙',2556783,'湖南',22,0)
    

• 方式2：指定字段，只需要传递指定字段值，通常使用这个方式，如：

    insert into star (name, money, age, sex, province)
    values("周杰伦", 78278278, 42, 0, "台湾"),
    ("胡歌", 72636633,39,0,"上海");
  

  注释：插入数据字段的顺序，与前面指定一定要一致，与数据库里面的字顺序无关。

• 说明：插入数据不用传值的字段

  • 自增的字段
  • 有默认值
  • 可以为空

• 修改数据：

  • 示例：update star set money=888 where id=3;
  • 警告：修改的时候，一定不要忘了添加条件，否则后果自负

• 删除数据：

  • 示例：delete from star where name=“胡歌”;
  • 警告：删除操作一定不要了忘了加条件，后果很严重
  • 说明：在真实的项目中，不会真的删除，大多使用逻辑删除

数据查询语言（DQL）

• 基本查询：select * from star;

• 指定字段查询：select name, money FROM STAR;

• 过滤重复的记录：select distinct province from star;

• 说明：使用distinct 指定的额字段不能重复，指定多个字段

• 条件查询：

  • 条件

    条件	说明
    >	大于
    <	小于
    >=	大于等于
    <=	小于等于
    =	等于
    ！=或 <>	不等于
    and	并且
    or	或者
    [not] between m and n	[不]在[m,n]的区间
    [not] in ()	[不]在指定的集合中
    [not] like 条件	模糊匹配，%表示任意字符
    is [not] NULL	是否为空

  • 示例：

    select * from star where id > 2;
    select * from star where id >5 and age >30;
    select * from star where age between 30 and 40;
    select * from star where id in (2,4,6);
    select * from star where province like "湖%";
    select * from star where province is null;
    

• 结果集排序（order by）

  select name, money from star order by money desc;
  select name, money, age from star order by age asc,money desc;
  

  • 说明:
    • 默认是升序asc，降序desc
    • 多个字段进行排序的时候，先安排第一个排序，相同的话再按照第二个进行排序

• 限制结果集（limit）

   select * from star limit 3;#提取3条数据
   select * from star limit 3 offset 2;#跳过两条提取3条数据
   select * from star limit 2,3;#同上
  

  • 分页：

    每页 pagesize=10,page表示页码数，请写出对应的查询语句
    第一页：limit 0,10
    d第二页：limit 10,10
    第三页：limit 20,10
    第4页：limit 30,10
    
    page页：limit (page - 1)*pagesize,pagesize
    

• 常用的聚合函数

  函数	说明
  count	统计个数
  sum	求和
  max	最大值
  min	最小值
  avg	平均值

  • 示例：

    select count(*)  as c from star;    #可以给查询的字段起别名
    select max(money) as max_money from star;
    

• 分组及过滤（重要）（group by）

  select sex from star group by sex;    #以性别进行分组
  select count(*) as c, sex from star group by sex;# 分组以后并统计
  select count(*) as c, province from star group by province having c=1;# 分组统计以后过滤
  
  

多表联合查询

https://blog.csdn.net/huaxiawudi/article/details/82288044      作业     

• 创建两张表

  create table user (
  id int(11) not null auto_increment,
  username varchar(20) default null,
  gid int(11) ,
  primary key(id));
  
  create table goods (
  id int(11) not null auto_increment,
  name varchar(40) default null,
  price float default null,
  category varchar(20) default null,
  primary key(id));
  

• 隐式内连接

  • 说明：查询哪个用户购买了什么商品
  • 示例： select username,name from user,goods where user.gid =goods.id;

• 显示内连接

  • 说明：功能和隐式内连接一样，使用的关键字是join，一定要注意不是where，是on。
  • 示例：select username,name from user join goods on user.gid=goods.id;

• 左外连接

  • 说明：以左边的表为主，主要显示左边的表，右边的表有对应的数据就显示，没有话就显示null
  • 示例：select username,name from user left join goods on user.gid=goods.id;

• 右外连接

  • 说明：会显示右边的所有数据，左表有对应的数据的话就显示，没有的话就是null
  • 示例：select username,name from user right join goods on user.gid=goods.id;

• 记录联合

  • 格式：select 语句1 union select 语句2

  • 示例：select username,name from user right join goods on user.gid=goods.id union

    select username,name from user left join goods on user.gid=goods.id;

  • union all:将两边的查询结果直接拼接到一起

  • union:去重之后进行拼接

• 联合更新

  • 示例： update user u, goods g set u.gid=3,g.price=g.price+0.1 where u.gid=g.id and u.id=2;

• 子（嵌套）查询

  • select * from user where gid in (select id from goods);
  • 说明：条件是一个sql语句

数据事务语言（DTL）

• 说明：用于测试的表他的存储引擎必须是InnoDB

• 开启事务：禁止自动提交

  set autocommit = 0;
  
  

• 提交事务：整个事务过程没有出现问题

  commit;
  

• 操作回滚：事务出现了问题，然后进行回滚，事务开始之前的状态

  rollback;
  

• 代码

  #开启事务
  try:
  #执行相关的操作
  except  Exception as e:
  #操作回滚
  else:
  #提交事务
  

数据控制语言（DCL）

• 查看授权

  • 格式：show grants [for ‘user’@‘localhost’]
  • 示例：show grants [for ‘root’@‘localhost’]
  • 说明：查看指定的用户权限，若不指定用户，则查看的是当前登录的用户

• 创建用户

  • 格式：create user ‘user’@‘host’ identified by ‘password’
  • 示例：create user ‘user’@‘10.20.159.%’ identified by ‘123456’
  • 说明：%是通配符

• 用户授权

  • 格式：grant 权限 privilages on 库.表 to “user”@‘host’ identified by ‘password’
  • 示例： grant all privileges on test.user to ‘user’@‘10.20.159.%’ identified by '123456‘
  • 说明：
    • 权限可以是：insert,delete,update,select, all是所有的权限
    • %表示的是任意主机
    • *表示的是所有的库和表

• 刷新权限：flush privileges

• 取消权限

  • 格式：revoke 权限 privileges on 库.表 from ‘user’@‘host’

  • 示例：revoke all privileges on test.* from ‘user’@‘10.20.159.%’;

• 删除用户

  • 格式：drop user ‘user’@‘localhost’
  • 示例：drop user ‘user’@‘10.20.159.%’;

备份和恢复

• 备份：

  • 将数据库中的数据保存到文件中
  • 示例：mysqldump -u root -p test > test.sql
  • navicate:右键->转储sql文件的

• 恢复：

  • 从保存的sql、文件中，解析执行sql语句

  • 示例：mysql -uroot -p test2 < test.sql

    就是练习

python操作mysql数据库

import pymysql
#pip install pymysql

#1.连接数据库服务器   mysql -h localhost -u root -p 123456
db = pymysql.connect(host="localhost", user="root", password="123456")
print(db)

#2.选择数据库 use test

db.select_db("test")
#3。设置字符集
db.set_charset("utf8")

#3.1创建游标对象，
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
#4.准备sql语句
sql = 'select id,name,age from star'
#5.执行sql语句
rowcount = cursor.execute(sql)
#结果集中的数量
print(rowcount)
print(cursor.rowcount)
print(cursor.rownumber)
#获取所有的数据(默认的是元祖)
#print(cursor.fetchall())

#获取一条数据
#print(cursor.fetchone())
print(cursor.fetchmany(1))


#关闭游标对象
cursor.close()
#关闭数据库的链接
db.close()

• 事务代码

  import pymysql
  
  db = pymysql.connect(host="localhost", user="root", password="123456")
  
  db.select_db("test")
  
  db.set_charset("utf8")
  
  cursor = db.cursor()
  
  #开启事务
  db.begin()
  try:
      sql = "insert into user (username) values('goudan')"
      #对于DML:返回一个受影响的行数
      rowcount = cursor.execute(sql)
  except Exception as e:
      print(e)
      #操作回滚
      db.rollback()
  
  else:
      #开始真正的提交事务
      db.commit()
      print(rowcount)
      #最后插入数据的自增的id
      print(cursor.lastrowid)
  
  cursor.close()
  db.close()