Mysql常用操作笔记

登录

• mysql -u用户 -p密码
• mysql -hIP地址 -u用户 -u密码

退出

• exit

Sql语句分类

• DDL 数据定义语言，用来定义数据库对象， 关键字：create,drop,alter等
• DML 数据操作语言，用来对数据库中表的数据进行增删改，关键字：insert,delete,update等
• DQL 数据查询语言，用来查询数据库中表的数据，关键字：select,where等
• DCL 数据控制语言，用来定义数据库的访问权限和安全级别，关键字: grant,revoke等

DDL操作数据库

1.创建数据库

• 直接创建数据库
  CAREATE DATABASE 数据库名;
• 判断是否存在并创建数据库
  CREAET DATABASE IF NOT EXISTS 数据库名;
• 创建数据库并指定字符集
  CREATE DATABASE 数据库名 CHARACTER SET 字符集;

2.查看数据库

• 查看所有数据库
  SHOW DATABASES;
• 查看某个数据的定义信息
  SHOW CREATE DATABASE 数据库名;

3.修改数据库

• 修改数据的字符集
  ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;

4.删除数据库

• 删除指定的数据库
  DROP DATABASE 数据库名;

5.使用数据库

• 使用/切换数据库
  USE 数据库;
• 查看当前正在使用的数据库
  SELECT DATABASE();

6.创建表

• 直接创建表
  CREATE TABLE 表名(字段名1 字段类型1, 字段名2 字段类型2, ...);
• 创建一个表结构相同的表
  CREATE TABLE 新表名 LIKE 旧表名;

7.查看表

• 查看所有表
  SHOW TABLES;
• 查看表结构
  DESC 表名;
• 查看创建表的SQL语句
  SHOW CREATE TABLE 表名;

8.删除表

• 直接删除表
  DROP TABLE 表名;
• 判断表存在并删除表
  DROP TABLE IF EXISTS 表名;

9.修改表

• 添加列（字段)
  ALTER TABLE 表名 ADD 列名 类型;
• 修改列类型
  ALTER TABLE 表名 MODIFY 列名 新类型;
• 修改列名
  ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
• 删除列
  ALTER TABLE 表名 DROP 列名;
• 修改表名
  RENAME TATBLE 旧表名 TO 新表名;
• 修改表字符集
  ALTER TABLE 表名 CHARACTER SET 字符集;

10.常用字段类型

类型	描述	说明
int(integer)	整数	integer默认11
double	双精度浮点数
float	单精度浮点数
varchar(M)	字符串	M为0-255之间整数
char(M)	字符串	M为0-255之间整数
date	日期类型	yyyy-MM-dd
datetime	日期时间类型	yyyy-MM-dd HH:mm:ss

DML操作数据库

1.表插入数据

• 插入全部字段
  INSERT INTO VALUES(值1, 值2, 值3,...);
• 插入部分字段
  INSERT INTO 表名(字段1, 字段2, 字段3, ...) VALUES(值1, 值2, 值3, ...);

2.复制表

• 全部复制
  INSERT INTO 新表 SELECT * FROM 旧表;
• 部分复制
  INSERT INTO 新表(字段1, 字段2, ...) SELECT 字段1,字段2,... FROM 旧表;

3.更新表

• 不带条件修改表数据
  UPDATE 表名 SET 字段名1=值,字段名2=值;
• 带条件修改表数据
  UPDATE 表名 SET 字段名1=值,字段名2=值 WHERE 字段名=值;

4.删除表数据

• 不带条件删除表数据
  DELETE FROM 表名;
• 带条件删除表数据
  DELETE FROM 表名 WHERE 字段名=值;
• 整表销毁，重新创建一个同名新表
  TRUNCATE TABLE 表名;

DQL操作数据库

1.简单查询

• 查询表所有数据
  SELECT * FROM 表名;
• 查询表部分数据
  SELECT 字段名1,字段名2,... FROM 表名;
• 别名查询（AS可省略）
  SELECT 字段名1 AS 别名1, 字段名2 AS 别名2,... FROM 表名;
• 筛选重复值查询
  SELECT DISTINCT 字段名1,字段名2,... FROM 表名;
• 查询结果参与运算（字段值必须为数值类型）
  SELECT 字段名 + 值 FROM 表名;
SELECT 字段名1 + 字段名2 FROM 表名;

2.条件查询

SELECT 字段名 FROM 表名 WHERE 条件;

• 比较运算符条件
  SELECT * FROM table WHERE age>=18;
• 逻辑运算符条件(and or not)
  SELECT * FROM table WHERE age>=19 AND gender='男';
• 范围运算符条件(in between)
  SELECT * FROM table WHERE name in('小红','小明');
SELECT * FROM table WHERE age BETWEEN 18 AND 30;

3.模糊查询

SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';

• 通配符字符串
  %: 任意个字符
_: 一个字符
• 查询名字'jordan'开头的数据
  SELECT * FROM table WHERE name LIKE 'jordan%';
• 查询名字包含'jordan'的数据
  SELECT * FROM table WHERE name LIKE '%jordan%';
• 查询名字'乔丹'开头，结尾两个字长度的数据
  SELECT * FROM table WHERE name LIKE '乔丹__';

4.查询结果排序

SELECT 字段名 FROM 表名 WHERE 字段名=值 ORDER BY 字段名 [ASX|DESC];

• ORDER BY 排序
• ASC 升序
• DESC 降序
• 查询年龄大于18的数据，并升序排列
  SELECT * FROM table WHERE age>18 ORDER BY age ASC;
• 查询年龄大于18，升序排列， 如果年龄相同，则按体重大小降序排列
  SELECT * FROM table WHERE age>18 ORDER BY age ASC,weight DESC;

5.聚合函数

聚合函数为纵向查询，即对表的一列值（Null值会被过滤掉）进行计算并返回结果。

• 统计指定列记录条数 COUNT
  SELECT COUNT(字段名) FROM 表名;
• 计算指定列数值总和 SUM
  SELECT SUM(字段名) FROM 表名;
• 计算指定列最大值 MAX
  SELECT MAX(字段名) FROM 表名;
• 计算指定列最小值 MIN
  SELECT MIN(字段名) FROM 表名;
• 计算指定列平均值 AVG
  SELECT AVG(字段名) FROM 表名;

6.分组查询

分组查询是指使用GROUP BY语句对查询信息进行分组，相同条件数据作为一组，通常与聚合函数一起使用
SELECT * FROM 表名 GROUP BY 分组字段 [HAVING条件];

• 查询根据性别分组，统计男女人数各总数
  SELECT COUNT(*),sex FROM table GROUP BY sex;
• 查询年龄大于18岁，根据性别分组，统计男女人数各总数
  SELECT COUNT(*),sex FROM table WHERE age>18 GROUP BY sex;
• 查询年龄大于18岁，根据性别分组，统计男人人数各总数，并只显示总数大于10的数据
  SELECT COUNT(*),sex FROM table WHERE age>18 GROUP BY sex HAVING COUNT(*)>10;

HAVING 与 WHERE 的区别

• HAVING是在数据分组后再进行过滤，WHERE是在数据分组前进行过滤。
• HAVING后面可以使用聚合函数，WHERE不可以。

7.分页LIMIT

LIMIT offset,length; offset为偏移量，默认为0,length是返回的数据条数

• 查询第二页数据，返回10条数据
  SELECT * FROM table LIMIT 1,10;

8.查询语句总和

SELECT *|字段名 [AS 别名] FROM 表名 [WHERE语句] [GROUP BY语句] [HAVING 语句] [ORDER BY 语句] [LIMIT 语句];

DCL操作数据库

1.DCL约束种类

• 主键：PRIMARY KEY
• 唯一：UNIQUE
• 非空：NOT NULL
• 默认：DEFAULT
• 外键：FOREIGN KEY
• 用法：
  CREATE TABLE 表名(字段名1 [DCL约束], 字段名2 [DCL约束], ...);

2.主键

• 特点：唯一，非NULL
• 创建person表，包含字段（id,name) 设定id为主键
  CREATE TABLE person(id int PRIMARY KEY, name varchar(10));
• 主键自增 AUTO_INCREMENT，插入数据，主键自行递增
  CREATE TABLE person(id int PRIMARY KEY AUTO_INCREMENT);
• 删除主键
  ALTER TABLE 表名 DROP PRIMARY KEY;

3.约束总和举例

• 创建一张person表, 包含（id, name, age, sex, session_id）id为递增主键，name唯一非空，age非空默认为0，sex非空，session_id为外键。

CREATE TABLE person(
    id int PRIMARY KEY AUTO_INCREMENT,
    name varchar(10) UNIQUE NOT NULL,
    age int NOT NULL DEFAULT 0,
    sex char(4) NOT NULL,
    session_id int FOREIGN KEY
)