MYSQL数据库基本操作

• 基础命令

  • mysqld install - 安装数据库
  • net start mysql - 启动数据库
  • mysql -uroot -p - 登录数据库
  • mysql -uroot -p -h192.168.14.106 -- 远程登录数据库
  • select users() - 查看当前用户
  • create user 'alex'@'192.168.14.106' identified by '123' - 创建其他用户及其密码
  • grant all on 数据库名.* to 'alex'@'%' - 给用户所有权限
  • grant all on 数据库名称 .* to 'alex'@'%' identified by '123' - 创建用户并授权

• SQL基础规范

  1. SQL 常用种类
     • DDL 数据定义语言 create、alter、drop、truncate
     • DCL 数据控制语言 grant、ROLLBACK、COMMIT
     • DML 数据操作语言 insert，update，delete
     • DQL 数据查询语言 select
  2. DDL 开发规范
     • 建库需要显示添加字符集和校对规则
     • 库名要和业务有关。
     • 库名不要大写字母，数字开头。
     • 普通用户禁用drop操作
     • 表名：和业务有关、不要大写字母和数字开头、不要太长
     • 必须显式设定存储引擎。
     • 字符集一般建议utf8mb4
     • 表要有注释
     • 必须要有主键列，建议是数字自增列。
     • 数据类型： 合适的、简短的、足够的。
     • 尽量每个列非空。
     • 每个列要加注释。
     • 表大做alter语句，业务不繁忙期间做。如果必须做，建议pt-osc 。
  3. DML 开发规范
     • 大的insert事务，要拆分为小事务进行。也可以用load data infile方式进行批量录入。
     • 导入大量数据时，可以将索引先禁用，导入成功后再创建。
     • update 必须要加where条件。经常做update操作的列，不建索引。更新范围尽量小。
     • delete 必须要加where条件。有必要的话，可以使用update替代delete。

• SQL - 结构化查询语言

  • 查询：select
  • 操作：insert，update，delete
  • 定义：create，drop，alter

  # 数据库操作
  create database 数据库名;
  drop database 数据库名;
  show databases;
  use 数据库名;
  # 表操作
  create table 表名(
  列名 数据类型
  )
  show tables;
  desc 表名;
  drop table 表名;
  rename table 表名 to 新表名
  show create table 表名
  	# 列操作
      alter table 表名 add 列名 数据类型;
      alter table 表名 change 旧列名 新列名 数据类型;
      alter table 表名 drop 列名;
      alter table 表名 modify 列名 新数据类型
      # 数据操作
      insert into 表名 (列名,...) values (值,...);
      insert into 表名 value (值,...),(值,...),(值,...);
    	delete from 表名 where 条件;
      update 表名 set 列名='新值' where 条件;
      select * from 表名
  

• 数据类型

  • 整数
    • tinyint -- 一个字节 -- -128~127
    • int -- 4个字节
  • 小数
    • float(m,d) -- 单精度 -- 4字节
    • double(m,d) -- 双精度 -- 8字节
  • 日期 - values(now()) 当前时间
    • time ~~~ -838:59:59 - 838:59:59
    • date ~~~ 1000-01-01 ~ 9999-12-31
    • datetime ~~~ 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
  • 字符串
    • char : 最多255，定长，
    • varchar : 最多65535，变长
  • 枚举/集合
    • 枚举 -- enum
      • 添加值只能添加一个
    • 集合 -- set
      • 添加值可以添加多个，且会自动去重和去掉不存在的

• 主键 - 列的唯一标识

  # 设置主键
  create table t1(
  id int primary key
  )
  alter table t1 add primary key(id)
  # 设置自动更新的主键
  create table t3(
  id int primary key auto_increment
  )
  

• select查询

  select * from 表名;
  select 列名,列名,列名 from 表名;
  select 列名,列名,列名+50 from 表名; 数据类型必须是整形
  select age+50 as '年龄' from ti;
  select distinct 列名,... from 表名;
  select 列名 from 表名 order by 列名;
  select 列名 from 表名 order by 列名 desc;
  # 限制查询条件
      select 列名 from 表名 where 条件;
      select 列名 from 表名 where 条件 and 条件;
      select 列名 from 表名 where 条件 or 条件;
      select 列名 from 表名 where 列名 like '吕%乔'
  # 分页
  	select * from 表名 limit 0,3;
  # 分组
      select 列名 from 表名 group by 列名;
      select 列名 from 表名 where 条件 group by 列名;
      select 列名 from 表名 group by 列名 having 聚合函数;
  # where子句操作符
  select * from t1 where num between 2 and 4;
  select * from t1 where num is not null;
  select * from t1 where num num in(2,4)
  

• 聚合函数

  • count() - 次数
  • max() - 最大值
  • min() - 最小值
  • sum() - 求和
  • avg() - 平均值

• 内置函数

  • concat() - 拼接字符串
  • substring() - 截取指定位置的字符串
  • trim() - 去除两边空格

• having 和 where

  • having过滤分组 - 用在分组之后
  • where过滤行 - 用在分组之前

• 表的约束 - 保证数据的安全性和完整性

  • 主键约束 - 唯一，不能为空

    create table t1(
    id int primary key
    );
    

  • 唯一约束

    create table t1(
    name varchar(32) unique
    );
    

  • 非空约束

    create table t1(
    name varchar(32) not null
    );
    

  • 外键约束

    create table t1(
    id int primary key,
    name varchar(32)
    );
    create table t2(
    id int,
    money int
    foreign key(id) references t1(id)	# 设置外键
    )engine=innodb;		# 设置搜索引擎
    insert into t1 values (1,'alex'),(2,'meet');
    insert into t2 values (1,100),(2,200);
    # 作用
    1.对子表(外键所在的表)的作用：子表在进行写操作的时候，如果外键字段在父表中找不到对应的匹配，操作就会失败
    2.对父表的作用：对父表的主键字段进行删和改时，如果对应的主键在子表中被引用，操作就会失败
    
    

  • 索引

    create index 索引名 on 表名(列名);
    create table t1(
    id int,
    name varchar(32),
    index index_1(name)
    )
    drop index 索引名 on 表名
    # 使用
    给需要经常查询的列名创建索引
    

• 关联关系

  • 一对一 ：表一的一条数据对应表二的一条数据

  • 一对多(多对一) ：一个表有一个外键

  • 多对多 ：一个表有多个外键

  • 关联查询

    select 表一.列名,表二.列名 from 表一,表二 where 表一.id=表二.id
    

• 子查询

  select * from (select * from 表名)as 表名;
  select * from 表名 where age>(select avg(age) from 表名);
  

• 多表查询

  # 要把两个表中相互关联的字段作为条件
  select * from t1,t2 where t1.id=t2.id;
  

• 内连接查询

  # 查询两张表的交集
  select * from t1 inner join t2 on t1.id=t2.id;
  

• 左外连接查询

  # 优先显示左边表的全部数据，右边表和左边表有连接的会显示，没有连接的显示null
  select * from t1 left join t2 on t1.id=t2.id;
  

• 右外连接查询

  # 优先显示右边表的全部数据，左边表和右边表有连接的会显示，没有连接的显示null
  select * from t1 right join t2 on t1.id=t2.id;
  

• 全连接查询

  select * from t1 left join t2 on t1.id=t2.id
  union
  select * from t1 right join t2 on t1.id=t2.id;
  # union 回去掉重复的数据
  # union all 显示的是全部结果（笛卡尔乘积）
  

• python连接MySQL数据库

  # 查询数据库
  import pymysql
  conn = connect(host='localhost',user='root',password='123',db='数据库名',charset='utf8')
  cur = conn.cursor()
  sql = 'select * from t1'
  cur.execute(sql)
  data = cur.fetchall()
  print(data)
  # 修改数据库
  sql = 'update 表名 set 列名="值" where 条件'
  cur.execute(sql)
  sql1 = 'select * from t1'
  cur.execute(sql1)
  data = cur.fetchall()
  print(data)
  conn.commit()
  cur.close()
  conn.close()
  

• pycharm中播放音乐

  import pygame
  import time
  # 使用mixer播放音效
  pygame.mixer.init() #初始化
  pygame.mixer.music.load("E:\\music\\洛天依投食歌.mp3") #通过路径path加载
  pygame.mixer.music.play() #在程序运行中会播放
  time.sleep(100) #让程序保持运行状态