MySQL的常见操作命令
MySQL数据库的CRUD
前言:常见操作命令很简单,有时又容易遗忘,简单总结一下以利于复习回忆
DDL:database,create/ show/ alter/ drop/ select/ use
1、操作数据库:
1、CREATE DATABASE 数据库名;
2、CREATE DATABASE IF NOT EXISTS 数据库名;
3、CREATE DATABASE 数据库名 CHARACTER SET 字符集
2、查看数据库:
1、show databases;
2、show create database 数据库名;
3、修改数据库:
1、ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集
4、删除数据库:
1、DROP DATABASE 数据库名;
5、使用/切换数据库:
1、SELECT DATABASE(); 查看正在使用数据库
2、USE 数据库名;
DDL:table,create / alter / drop,show/ desc/ rename
1、创建表:服气了,这里是()小括号啊啊啊啊啊啊啊啊
1、CREATE TABLE 表名 (
字段名 1 字段类型 1 附加条件,
字段名 2 字段类型 2 附加条件
)引擎,默认字符集;
2、CREATE TABLE 新表名 LIKE 旧表名;
2、查看表:
1、SHOW TABLES;
2、DESC 表名;
3、SHOW CREATE TABLE 表名; 创建表的SQL语法
3、修改表结构/表名/表字符集:
1、ALTER TABLE 表名 ADD 列名 类型;
2、ALTER TABLE 表名 MODIFY 列名 新的类型; 更改列类型
3、ALTER TABLE 表名 CHANGE 旧列名 新列名 类型; 更改列名与列类型
4、RENAME TABLE 表名 TO 新表名;
5、ALTER TABLE 表名 character set 字符集;
4、删除表/列:
1、DROP TABLE 表名;
2、DROP TABLE IF EXISTS 表名;
3、ALTER TABLE 表名 DROP 列名;
DML:table,insert / update / delete/ truncate
1、插入:
1、INSERT INTO 表名 (字段名 1, 字段名 2, 字段名 3…) VALUES (值 1, 值 2, 值 3); 插入全部字段,写上所有字段名
2、INSERT INTO 表名 VALUES (值 1, 值 2, 值 3…); 插入全部字段,不写字段名
3、INSERT INTO 表名 (字段名 1, 字段名 2, ...) VALUES (值 1, 值 2, ...); 插入部分数据,写上部分字段名
蠕虫复制:将一张已经存在的表中的数据,全部或部分,复制到另一张表中
1、INSERT INTO 表名 1 SELECT * FROM 表名
2、INSERT INTO 表名 1(列 1, 列 2) SELECT 列 1, 列 2 FROM 表名
2、更新:
1、UPDATE 表名 SET 字段名=值; -- 修改所有的行
2、UPDATE 表名 SET 字段名1=值1, 字段名2=值2, ... WHERE [条件表达式] -- 按条件修改部分字段
3、删除表或删除表中记录:
1、DELETE FROM 表名; 不带条件删除,即删除所有数据记录,但是主键自增有影响
2、DELETE FROM 表名 WHERE 字段名=值; 带条件删除
3、TRUNCATE TABLE 表名;
注:与delete区别在于,delete删除表的所有数据,truncate多了删除表结构再按原来结构创建新表,多使用这种,即truncate自增仍是从默认起始值开始
DQL:table,组合查询操作很灵活,也考验技术性,select
多表查询还包括内连接join,左连接left join,右连接right join,查询结果是笛卡尔乘积
1、简单查询, */ as
1、SELECT * FROM 表名;
2、SELECT 字段名 1, 字段名 2, 字段名 3, ... FROM 表名;
3、SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名;
4、SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名 AS 表别名;
2、清除重复值,distinct
5、SELECT DISTINCT 字段名 FROM 表名;
3、查询结果参与运算 ±
6、SELECT 列名 1 + 固定值 FROM 表名;
4、 条件查询 where
7、SELECT 字段名 FROM 表名 WHERE 条件;
条件中运算符:
# 比较运算符
1、>、 <、 <=、 >=、 =、 <>
2、BETWEEN...AND
3、IN(集合)
4、LIKE '张%' -- 模糊查询,搭配通配符使用
% 匹配任意多个字符串
- 匹配一个字符
5、IS NULL 不能写不能写不能写=NULL
# 逻辑运算符,SQL 中建议使用前者,后者并不通用
1、and 或 &&
2、or 或 ||
3、not 或 !
5、排序 order by 默认升序
# 单列排序
1、SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];
#组合排序
2、SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 1 [ASC|DESC], 字段名 2 [ASC|DESC];
6、聚合,max/ min/ avg/ count/ sum
1、SELECT 聚合函数(列名) FROM 表名;
# 这里列名可以为*
# 聚合函数分为max, min, avg, count, sum
# 这里count不会统计null, 如果要统计,可以 count(IFNULL(列名,默认值) )
7、分组 group by
1、SELECT 字段 1,字段 2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];
# having可以认为是对分组后结果过滤
# having与where的区别
子名 | 作用 |
---|---|
where 子句 | 1) 对查询结果进行分组前, 将不符合 where 条件的行去掉, 即在分组之前过滤数据, 即先过滤再分组。2) where 后面不可以使用聚合函数 |
having 子句 | 1) having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,即先分组再过滤。2) having 后面可以使用聚合函数 |
8、限制查询记录的条数 limit,limit前若有order by则必须放在其后
注意:下面可以看出指令放置顺序
1、SELECT *|字段列表 [as 别名] FROM 表名 [WHERE 子句] [GROUP BY 子句][HAVING 子句][ORDER BY 子句][LIMIT 子句];
# LIMIT offset,length;
# offset:起始行数,从 0 开始计数,如果省略,默认就是 0
# length: 返回的行数,这个重要
DCL,grant / revoke/ show/ drop,mysqldump/ use/ source,mysqladmin / set
1、备份数据库
mysqldump -u 用户名 -p 密码 数据库 > 文件的路径
2、还原/导入数据库文件
USE 数据库;
SOURCE 导入文件的路径;
3、创建用户,用户创建之后,默认是没什么权限,需要给用户授权。
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
注: usage 是指连接(登陆)权限,建立一个用户,就会自动授予其 usage 权限(默认授予)。要是刚创建新用户,登录权都没有那就太可怜了。
4、给用户授权,一般普通用户给个查询权限就可以
GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名';
5、撤销授权
REVOKE 权限 1, 权限 2... ON 数据库.表名 revoke all on test.* from 'user1'@'localhost'; '用户名'@'主机名';
6、查看权限
SHOW GRANTS FOR '用户名'@'主机名';
7、删除用户
DROP USER '用户名'@'主机名';
8、修改管理员密码
mysqladmin -uroot -p password 新密码
注意:需要在未登陆 MySQL 的情况下操作,新密码不需要加上引号。
9、修改普通用户密码
set password for '用户名'@'主机名' = password('新密码');
注意:需要在登陆 MySQL 的情况下操作,新密码要加单引号。
数据库约束
目的
1、对表中的数据进行限制, 保证数据的正确性、 有效性和完整性。
2、 一个表如果添加了约束, 不正确的数据将无法插入到表中。
3、约束在创建表的时候添加比较合适
约束种类
四大约束
1、primary key,
2、unique,
3、not null,
4、foreign key,
(check检查约束,mysql没有)
1、创建主键两种方式与删除
主键隐含了非空not null和唯一unique两种含义
1、在创建表的时候给字段添加主键,一般设计表阶段处理,表中允许无主键,有就仅有一个主键。
CREATE TABLE 表名{
字段名 字段类型 PRIMARY KEY
}
2、在已有表中添加主键
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
3、在已有表中删除主键
ALTER TABLE 表名 DROP PRIMARY KEY; # 删除主键不需要别名,也根本不存在别名,有就仅有一个,直接删除
4、主键自增,可以指定起始值,也可以不指定,不指定时默认为1起始
CREATE TABLE 表名(
列名 int primary key AUTO_INCREMENT
) AUTO_INCREMENT=起始值;
5、值得注意:DELETE 和 TRUNCATE 对自增长存在不同影响
DELETE: 删除所有的记录之后,自增长没有影响,意思不是从起始值1重新开始,而是接着前面删除的主键自增长
TRUNCATE:删除以后,自增长又重新从1开始
2、唯一约束,一般创建表阶段添加
CREATE TABLE 表名{
字段名 字段类型 UNIQUE
}
注意:唯一约束是指某一列不能出现重复值,并不是null也不能重复
3、非空约束,一般创建表阶段添加
CREATE TABLE 表名{
字段名 字段类型 NOT NULL
}
4、外键约束的创建、删除、级联
外键:在从表中与主表主键对应的那一列
主表: 一方,用来约束别人的表。这里主表约束的列只要满足唯一约束就行,即主表约束的列值不允许重复。
从表: 多方,被别人约束的表。从表的外键是受到别人约束,所以从表的外键的列值可以允许重复,
1、新建表时增加外键,一般会定义一个外键约束名称,因为一张表可能有多个外键,而主键没必要,一张表中最多一个主键,所以主键直接删除就好
CREATE TABLE 表名{
字段名 字段类型
外键字段名 外键字段类型
CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
}
注:这里从表外键和主表主键有一个先后顺序,否则无法绑定主外键。
2、已有表增加外键
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
3、删除外键,通过查找外键名称
ALTER TABLE 从表 drop foreign key 外键名称;
4、外键的级联,存在级联更新ON UPDATE CASCADE与级联删除ON DELETE CASCADE。
注意,级联更新, 只能是创建表的时候创建级联关系。
5、不叫约束的约束,default, 一般在创建表阶段添加
意思如果这一列没有值,使用默认值。
和乱码相关
1、查看 MySQL 内部设置的编码
show variables like 'character%';
修改 client、 connection、 results 的编码为 GBK,保证和 DOS 命令行编码保持一致
2、单独设置3项:
1、set character_set_client=gbk;
2、set character_set_connection=gbk;
3、set character_set_results=gbk;
3、同时设置3项:
1、set names gbk;
数据库范式
目前,关系数据库有六种范式: 第一范式(1NF)、 第二范式(2NF)、 第三范式(3NF)、 巴斯-科德范式(BCNF) 、
第四范式(4NF)和第五范式(5NF,又称完美范式)。
1NF:每一列都是不可分割的原子数据项。即不可再拆分
2NF:在1NF的基础上所有列完全依赖于主键列。意思是,消除部分函数依赖,全部是完全函数依赖,即不产生局部依赖,一张表只描述一件事情
3NF:在满足 2NF 的基础上, 任何非主列不得传递依赖于主键。意思是,消除传递函数依赖,即表中每一列都直接依赖于主键,而不是通过其它列间接依赖于主键
事务待补充
事务的并发访问3大问题:脏读、不可重复读、幻读
事务的4大特性 ACID
事务的4种隔离级别