MySQL基本操作

用户管理

用户分为两种：

• 超级管理员，即root用户，拥有所有权限；
• 普通用户，由root分配权限。

超级用户在安装MySQL的时候已经创建，通过登录root账户，可以执行创建用户的操作。

用户操作

• 创建用户

  1. 创建普通用户：

  CREATE USER '用户名'[@'主机名'][INDENTIFIED BY '密码'];

  语句中[]包裹的内容为非必须，即创建用户时可以不包含主机名和密码，这样创建出来的用户没有任何权限，不需要密码即可登录。

  1. 授权方式创建用户

  GRANT 权限 on 数据库.表 to '用户名'@'主机' [IDENTIFIED BY '密码']；

  授权语句执行时，如果用户不存在会创建用户，可通过此方式直接创建并赋权给用户

• 删除用户

  DELETE FROM MYSQL.USER WHERE USER='用户名' AND HOST='主机名';       

  删除用户后，需要同步一下权限表的内容，执行：

  FLUSH PRIVILEGES  

  刷新权限后，方可创建刚删除的用户名用户。

• 修改密码

  root用户可以修改任何用户的密码，普通用户只能修改自己的密码。

  SET PASSWORD=PASSWORD('新密码')； 
  
  # 用户登录后修改自己的密码
  
  SET PASSWORD FOR '用户名'@'%'=PASSWORD('密码');
  UPDATE mysql.user SET password=PASSWORD('密码') WHERE user='用户名';
  
  # root账户更改自己或其他用户的密码
  

  权限操作

• 添加权限

  通用的权限有：

  SELECT, UPDATE, DELETE, INSERT, CREATE, ALTER, DROP, REFERENCES, CREATE TEMPORARY TABLES, INDEX, CREATE VIEW, SHOW VIEW, CREATE routine, ALTER ROUTINE, EXECUTE, ALL, ALL PRIVILEGES

  MySQL中使用*表示所有，可以表示所有数据库、所有数据表等。

  举个栗子：

  GRANT SELECT,DELETE ON dbtest.table_test TO 'oldman'@'localhost';

  除了对整张表进行权限控制外，MySQL的权限还可以精确到字段：

  GRANT SELECT(id, name),DELETE(title) ON dbtest.table_test TO 'oldman'@'localhost';

  这样设置后，oldman用户只能对dbtest下的table_test表下的id和name字段进行查询和删除。

• 撤销权限

  REVOKE 权限 ON 数据库.表名 FROM '用户名'@'主机';

  同GRANT，REVOKE对权限的撤销也可以精确到字段级别。

数据库管理

库级操作

• 连接数据库

  在命令行输入：

  >> mysql -h主机名 -u用户名 -p密码
  或
  >> mysql -h主机名 -u用户名 -p密码
  password:

  第一种密码以明文方式显示，第二种则以密文方式输入。其中-h后跟host参数，-u后跟着的是username，-p后跟的是password。

• 创建数据库

  CREATE DATABASE 数据库名[DEFAULT CHARACTER SET 编码 [COLLATE 编码_ci]];

  []中的语句可不加，只是通过[]中的语句来设定数据库的默认编码，例如utf8的COLLATE后跟utf8_general_ci，gbk的COLLATE为gbk_chinese_ci。

• 查看数据库

  SHOW DATABASES;

• 删除数据库

  DROP DATABASE 数据库名;

  删库的下一步就是跑路。

• 查看数据库编码

  SHOW VARIABLES LIKE 'CHARACTER_SET_DATABASE';
  SHOW CREATE DATABASE 数据库名;
  SHOW CREATE SCHEMA 数据库名;

  会显示当前数据库的编码类型

• 修改数据库编码

  ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 编码 [COLLATE 编码_bin];
  ALTER DATABASE 数据库名 CHARACTER SET=字符类型;

表级操作

• 切换数据库

  USE 数据库名;

  通过USE语法来切换当前操作的数据库

• 查看数据表

  SHOW TABLES;
  
  # 查看所有表
  
  DESCRIBE 表名;
  SHOW CREATE TABLE 表名;
  
  # 查看某张表的结构
  

• 创建数据表

  CREATE TABLE 表名(字段1 类型,字段2 类型,....);

  创建过程中可以给字段添加属性，常用属性有：

  • primary key：设置主键

  • 追加主键：

    ALTER TABLE 表名 modify 字段名 primary key;
    ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
    ALTER TABLE 表名 ADD UNIQUE KEY(字段);

  • 删除主键：

    ALTER TABLE 表名 DROP PRIMARY KEY(主键);

  • 复合主键：

    primary key(字段1,字段2,...)

  • default：设置默认值

  • null/not null：设置空/非空

  • foreign key：外键

  • unique key：唯一

  • auto_increment：自增

  • 修改自增长步长：

    SET AUTO_INCREMENT_INCREMENT=2;   

  • comment：注释，用来描述字段

  较为完整的举例说明：

  CREATE TABLE 'student'(
  'id' int NOT NULL AUTO_INCREMENT ,
  'name' varchar(18),
  'age' int DEFAULT 18,
    PRIMARY KEY('id')
  )ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

• 删除数据表

  DROP TABLE 表名;

  删表需谨慎

• 修改数据表

  • 修改表名

  ALTER TABLE 旧表名 rename [to] 新表名

  • 添加字段

  ALTER TABLE 表名 ADD COLUMN 字段名 类型;
  
  # 添加字段到最后
  
  ALTER TABLE 表名 ADD COLUMN 字段名 类型 FIRST;
  
  # 添加字段到开头
  
  ALTER TABLE 表名 ADD COLUMN 字段名 类型 AFTER 已存在的某个字段名；
  
  # 添加字段到指定字段后
  

  • 修改字段

  ALTER TABLE 表名 MODIFY COLUMN 字段名 类型;
  
  # 将表中的某个字段更改为给出的类型
  
  ALTER TABLE 表名 CHANGE COLUMN 旧字段名 新字段名 DOUBLE;

  • 删除字段

  ALTER TABLE 表名 DROP 字段名;

数据操作

• 插入数据

  INSERT INTO 表名 VALUES(字段1值,字段2值,字段3值,....)
  
  # 插入全部字段的数据，字段不能有缺失，表中有几个字段填写几个字段
  
  INSERT INTO 表名(字段1,字段2,字段3,...) VALUES(值1,值2,值3,...)
  
  # 可以用来插入部分字段，其中字段和值时一一对应的
  

• 修改数据

  UPDATE 表名 SET 字段1='值1',字段2='值2';
  
  # 不带条件的修改，会把所有数据的字段都做更新，谨慎使用
  
  UPDATE 表名 SET 字段1='值1' WHERE id=1;
  
  # 准确修改符合条件的数据，也可以多字段修改
  

• 删除数据

  DELETE FROM 表名;
  
  # 删除所有数据
  
  DELETE FROM 表名 where 条件;
  
  # 删除表中符合条件的数据
  
  TRUNCATE TABLE 表名;
  
  # truncate不允许带查询条件
  
  
  # delete和truncate的主要区别是：1.truncate不可以带查询条件而delete可以；2.delete删除的数据可以回滚而truncate不可以；3.delete只删除数据，而truncate不光删除数据，还删除约束
  

• 查询数据

  SELECT * FROM 表名;
  
  # 查询表中所有数据
  
  SELECT 字段1,字段2,字段3,... FROM 表名;
  
  # 查询表中某些字段
  
  SELECT 字段1,字段2,字段3,...,'常量值' AS '常量字段' FROM 表名
  
  # 查询字段数据，加入常量字段并设置常量值
  
  SELECT DISTINCT 字段1,字段2,字段3,... FROM 表名;
  
  # 查询结果并去重
  

  • 数字运算

  对于数字类型的字段，可以在查询的同时可以进行运算

  SELECT (字段1+字段2)/2 AS '新字段名' FROM 表名；

  • where条件查询

  MySQL通过WHERE语句来增加数据筛选条件

  条件运算	描述
  AND, OR, NOT, XOR	逻辑条件，与、或、非、异或。通常用来设置复合条件
  >, <, >=, <=, = <>	通常用来设置数字字段的比较条件
  is null, is not null, =”“, <>”“	判断字段数据是否为空(null或空字符)
  between, like	between 与and连用，表示落在某个区间;like用来进行模糊匹配，通常与通配符搭配使用。%表示匹配多个，_表示匹配一个。当需要匹配通配符时，可在通配符前加转义字符\，或通过ESCAPE语句指定一个通配符。

  • limit限制

  通过limit语句限制返回的数据条数

  SELECT 字段... FROM 表名 LIMIT [起始行],查询行数;
  
  # 从某行开始查询，并返回想要的数据量。起始行可以缺失，默认从第一行开始，默认值为0
  
  SELECT 字段... FROM 表名 LIMIT 行数 OFFSET 起始行;
  
  # 同一种结果不同语法
  

  • order by排序

  顺序分为正序和倒序，ASC为正序(0->9, a->z)，DESC为倒序(9->0, z->a)

  SELECT 字段... FROM 表名 ORDER BY 字段1 ASC/DESC,字段2 ASC/DESC;
  
  # 查询的默认顺序为正序,可设置对多个字段进行排序
  

  • group by分组

  SELECT ... FROM 表名 GROUP BY 字段1,字段2,...;
  
  # 输出结果按字段分组，结果类似聚合
  
  SELECT ... FROM 表名 WHERE 条件 GROUP BY 字段...;
  
  # 对符合条件的数据进行分组
  
  SELECT GROUP_CONTACT(字段) FROM 表名 GROUP BY 字段;
  
  # GROUP_CONTACT会把聚合后的某些字段里都有哪些数据显示出来
  

  GROUP BY必须在WHERE之后，ORDER BY之前。可以通过HAVING语句给分组加查询条件：

  SELECT ... FROM 表名 GROUP BY 字段1,字段2,... HAVING 条件；
  
  # 通过HAVING来连接WHERE语句做条件查询
  

  • 连表操作

  • 普通连表

    SELECT 表1.字段1,表2.字段2 FROM 表1,表2 WHERE 表1.id = 表2.nid;
    
    # 如果两个表无对应关系则不返回数据
    

  • inner join

    SELECT 表1.字段1,表2.字段2 FROM 表1 INNER JOIN 表2 ON 表1.id = 表2.nid;
    
    # 无对应关系返回空
    

  • left join

    SELECT 表1.字段1,表2.字段2 FROM 表1 LEFT JOIN 表2 ON 表1.id = 表2.nid;
    
    # 左边的表为主表，无对应关系则只显示表1数据，同时表2字段全为null
    

  • right join

    SELECT 表1.字段1,表2.字段2 FROM 表1 RIGHT JOIN 表2 ON 表1.id = 表2.nid;
    
    # 右边的表为主表，无对应关系则只显示表2数据，同时表1字段全为null
    

  • union

    SELECT 字段1 FROM 表1 UNION SELECT 字段2 FROM 表2;
    
    # 合并查询结果集，并去掉重复数据
    
    SELECT 字段1 FROM 表1 UNION ALL SELECT 字段2 FROM 表2;
    
    # 合并查询结果集，但不去重