mysql常用操作

用户管理相关

• root具有所有的权限
• 用户的信息存放在mysql.user中
• 创建一个用户
  • create user '用户名' 【@[主机名]】 【indentified by '密码'】成功后会保存在mysql.user中去，且这种方式创建的用户无任何权限
  • 创建用户并赋予权限grant 权限 on 数据库.数据表 to '用户名'@"主机" indentified by ‘密码‘ 如果该用户不存在，那么会自动创建
• 权限 : create ,update ,delete, select , index, all(所有权限等)
• revoke 权限 on 数据库.数据表 from ‘用户名’@’主机‘ 撤销一个用户的权限
• 删除用户 delete from mysql.user where username='用户名'，删除以后需要刷新权限(flush priviledge) , 不然不能够在创建同名用户，即使数据表中不存在这个用户
• 修改密码：update mysql.user set password=md5('新密码') where User='用户名'
• 查看权限
  • 查看当前：show grants
  • 查看其他用户：show grants for 用户名@主机名 主机名默认时%=》其他主机

数据库操作

• 显示所有数据库 show databases
• 切换数据库use database_name
• 创建数据库create database database_name
• 删除数据库drop database database_name

数据表操作

• 创建表
  • 直接创建 create table table_name(表内容)
  • 表复制create table table_name select * from other_table， 可以全部复制，也可以加入限定条件，部分复制
• 查看当前数据库的所有表show tables
• 删除表drop table table_name)
• 查看表结构desc table_name
• 重命名表rename table old_name to new_name
• 修改表结构
  • 增加一个字段alter table_name add column (字段定义)
  • 修改一个字段alter table_name change 字段名 字段名 varchar(20) null
  • 删除一个字段alter table_name drop 字段名

增删改查

• 增加 insert into table_name (字段名) values (字段内容)，（字段内容）...
• 删除delete from table_name where (条件语句)
• 修改update table_name set '字段名' =’‘字段内容 where '条件语句'
• 查询select 字段名 from table_name (条件语句)

条件控制

• 一个范围内(包括a和b)：between a and b
• 固定范围：in (a,b,c)
• and or

• < ， >，=， !=，or , and

• 模糊查询name like "%p%" %是匹配任意多个字符，_是匹配一个字符
• 排序显示order by 字段名 desc/asc 倒叙或者是正序排列

mysql内置函数

• 字符连接concat ，大写upper 小写lower 字符串分割substrint 去空格trim
• 平均数avg 和sum 最大值max 最小值min 记录个数count

分组查询

• 以一个字段分组select * from table_name group by 字段名 having 条件控制

连表查询

• union连接两个查询语句并去掉相同的记录select * from table_name 条件语句 union select * from table_name 条件语句
  • eg： select * from stu where id=2 union select * from stu where id=1;会输出两条记录
  • eg；select * from stu where id=1 union select * from stu where id=1;输出一条
  • eg: select * from stu where id=1 union select name from stu where id=1;显示错误
  • eg: select id from stu where id=1 union select name from stu where id=1;两条记录，但是字段名是id
• 普通连表查询select 字段名 from 表1 ， 表2 where 表1.字段=表2.字段
• 使用inner join查询select 字段名 from 表1 inner join 表2 on 表1.字段=表2.字段，这个查询跟普通查询没什么不同
• 使用left join查询select 字段名 from 表1 left join 表2 on 表1.字段=表2.字段，该连接是如果左边的表符合条件即使右边的表不符合也输出该条记录，右边的字段置空
• • 使用right join查询select 字段名 from 表1 inner join 表2 on 表1.字段=表2.字段 与左查询类似
• 使用full join查询select 字段名 from 表1 inner join 表2 on 表1.字段=表2.字段，无论左边的表还是右边的表，一个成功就可以

视图

• 特点：
  • 简化查询操作，过滤掉一些不用的字段或者记录
  • 增加安全性，因为该表的查询能够隐藏部分信息
  • 对单表所建视图的修改会同步到基础表，多表不行
  • 因为他是一个不存在的虚表，所以查询还是需要去找到基础表，所以性能较差
• 建立视图create view 视图名 as select * from 基础表 where 条件语句 感觉有点像表的复制
• 查询是和普通表一样的
• 删除视图 drop view 视图名
• 显示所有视图show tables 系统视图当作数据表来用的

索引：也是一个表，存放的是索引键和文件位置

1. 优点：使用B+树的数据结构，将随机I/O变成了顺序I/O，提高数据查询的速度
2. tips：创建索引的字段应该是经常使用的字段创建索引的字段应该是经常使用的字段,因为索引太多会加大存储空间消耗，而且对数据表的增删改会更加麻烦
3. 分类

• 普通索引
  • create index 索引名 on 数据表名 （字段名（长度））长度表示使用该字段的前length个字符作为索引
  • alter table 数据表名 add index 索引名 （字段）
• 唯一索引：该索引定义的字段不能够重复,但是允许有多个空值
  • create unique index 索引名 on 数据表名 （字段名）
  • alter table 数据表名 add unique index 索引名 （字段）
• 主键索引：不允许有空值，也不允许重复
  • alter table 数据表名 add primary key (字段）
• 全文索引（仅支持MyISAM），适用于文本字段的模糊查询，mysql会把该字段所有出现的单词创建一份清单，然后去匹配
  • alter table 数据表名 add fulltext（字段名）
  • 查询where match(字段名) against （单词1，单词2 ，…，单词n）
• 组合索引：左前缀原则，如果定义的索引左边的字段没有出现，那么查询不使用索引。
  • create index 索引名 on 数据表名 （字段名1，字段名2，，，n）
  • alter table 数据表名 add index 索引名 （字段名1，字段名2，，，n）

4. 删除索引drop index 索引名 on 数据表名

存储引擎（MyISAM和InnoDB）

1. MyISAM一共有三个文件，.frm（form :保存的是表结构定义）.MYD（my data 保存的是数据表内容）.MYI（my index：保存的是数据表索引信息），因为.MYI文件是可以压缩的，所以所用的内存空间会更小
2. InnoDB一共有两个文件，.frm和表空间文件，他的索引文件的数据文件是捆绑在一起的
3. MyISAM的优点（默认的存储引擎）
   • 因为索引文件和数据文件分离，且索引文件能够被压缩，所以可以占用更少的空间
   • 支持表级锁，在进行操作的时候会自动加上表级锁，所以更安全，但是会影响速度
   • 查询速度更快
   • 会保存总行数，所以查询一个表的总行数时，非常快，但是加上where条件查询时速度就和InnoDB一样了
   • 不会出现死锁现象
4. InnoDB的优点（Engine=InnoDB）
   • 支持事务操作和外键等高级操作
   • 具有行级锁，支持多用户高并发
   • 因为数据和索引文件都放在表空间中，所以跨平台可以直接拷贝使用
5. MyISAM和InnoDB在B+数据结构上实现的区别
   • MyISAM中索引树中叶子节点存放的的对应数据记录的物理位置，这种索引和记录分离的方式叫做非聚簇索引
   • InnoDB中索引文件和数据文件是捆绑的，所以找到该文件也就找到了该记录。

index和key的区别

• key在数据表中有两个作用，一个是约束作用（primary key ， foreign key和 unique ）,另外一个是辅助查询的作用
• index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等；