MySQL 基础操作总结：数据库、数据表和 CRUD

MySQL 基础操作总结：数据库、数据表和 CRUD

这篇文章主要整理 MySQL 中常见的基础操作，包括数据库操作、数据表操作、数据类型、增删改查、条件查询、排序分页、聚合查询以及简单约束。

MySQL 的基础操作可以先按照三个层次理解：

数据库操作：创建、查看、修改、删除数据库；
数据表操作：创建表、查看表结构、修改表结构、删除表；
数据操作：对表中的数据进行新增、查询、修改和删除，也就是常说的 CRUD。

在正式操作数据表之前，需要先选择要使用的数据库：

use 数据库名;

与数据库有关的操作

1.查看所有数据库

showdatabases;

2.创建数据库

createdatabase数据库名;

3.修改数据库

alterdatabase数据库名charsetutf8mb4--设置字符集collateutf8mb4_general_ci--设置排序规则/校对规则

注：alter 主要是改数据库属性，比如字符集、排序规则

4.删除数据库

dropdatabase数据库名;

和数据表有关的操作

数据类型

注：decimal(10,2) decimal(总位数, 小数位数),10表示这个数最多有10位数字，2表示小数点后保留两位

操作表结构（sql操作时要选中数据库 use 数据库名； ）

1.查看所有表

showtables;

2.创建表（表名不与关键词重复，若重复用反引号` 引用）

createtable表名(列名 数据类型,列名 数据类型,...);

3.查看表结构

desc表名;

4.修改表(低频操作)

altertable表名addclass_idintaftergender;--(列名 数据类型)--

altertablestudentmodifynamevarchar(200);--modify是关键字 修改 student 表中已经存在的 name 字段，把它的数据类型改成 VARCHAR(200)。

altertablestudentrenamecolumnidtostudent_id;

altertable表名dropclassId;

注：修改表结构是低频操作，很危险，实际操作时修改前查一下。

5.删除表

droptable表名;

C:新增表内容

insertintostudentvalues(1,'张三'，1);

insertintostudent(id,name)values(1,'张三');

insertintostudentvalues(5,'王五',0),(6,'赵六',1),(7,'田七',0);--多个数据之间要用英文逗号隔开

insertintostudentsselect*fromstudent;

注：第三种方式效率更高

列名命名要蛇形命名：student_name

sql里字符串用’’ ""都可以。

sql是弱类型，会存在隐式类型转换：int->varchar,varchar->int

R: 查询

select 查询不会修改数据库中的原始数据，它只是根据表中的数据生成一个查询结果集。这个结果集可以理解为临时看到的一张结果表

select*from表名;--危险操作，容易吃满网卡

select列名,列名...from表名;

select表达式from表名;

select表达式as别名from表名;

selectdistinctmathfromexam;

去重条件：指定列必须完全相同。distinct 会根据 select 后面指定的所有列一起去重。如果只查询 math，就只按 math 去重；如果查询 name, math，就要求 name 和 math 都相同才会被去重。如下:

selectname,mathfromexamwhereenglish>60--条件中写的列和select查询的列是没有关系的

select列名from表名where条件;

• = 表示比较相等（在条件查询里） 不能正确处理 NULL = NULL
• <=> ：NULL 安全等于，可以判断 NULL 和 NULL 相等
• <>:不等于
• between A and B A<=value<=B (闭区间，连续区间)
• value in(option,…) 多选一 （一个离散的集） where 字段名 in （）
• and
• or
• not 取反/不是/不满足这个条件 （not age =18;这个可以 age not =18 这样不可以）not后面更多的是条件
• is not不是用来判断是否等于某个字符的，一般用is not null

whereuniversity!='复旦大学';whereuniversuty<>'北京大学';

条件查询过程

1. 遍历每一行的数据
2. 把这一行的数据带入到条件里
3. 条件成立将这一行加入到集合里，不成立的话跳过
4. 完成所有遍历过程后得到了结果集合，再根据select指定的列/表达式/别名/去重操作对集合处理

selectname,chinese+mathastotalfromexamwheretotal<200;--报错 注意上面写的条件查询过程

注：and or优先级：建议手动加（）保证调整优先级尽量不要写复杂条件。

selectnamefromexamwherenamelike'孙%';//'%'包含多个任意字符selectnamefromexamwherenamelike'孙_';//'_'一个任意字符

select列名from表名orderby列名selectnamefromexamorderbychinesedesc;selectnamefromexamwherechineseisnotnullorderbychineseselectdevice_id,gpa,agefromuse_profileorderbygpadesc,agedesc;--order by 后的顺序不能反，gpa相等后，再按照age降序输出。

• 没有order by ，查询结果的顺序是不一定
• null在排序中默认最小，相同值之间彼此顺序是不一定的
• order by 子句 默认排序是升序 在结尾加desc(descond)降序

selectname,chinesefromexamwherechineseisnotnullandenglishisnotnullorderbymathdesc,chinses,english;--先按 math 降序排序--如果 math 相同，再按 chinese 升序排序--如果 chinese 也相同，再按 english 升序排序

limitnum;--返回num条记录limit起始位置,查询条数;limit查询条数OFFSET起始位置;

U:修改操作（危险操作，不可撤销）

update表名set列名=值,列名=值;

注：修改的前提是先查询到，不加条件的修改就是作用于全部行。

D：delete 删除数据中数据行

deletefromexamwherename='孙';--如果不指定条件的话会删除掉表内所有数据剩下一个空表

truncate 清空整张表里的所有数据

truncatetablestudent;truncatestudent;--上面两个都可以，这个表示表结构还在但表里的数据全没有了--如果表里有自增主键，TRUNCATE 通常会把自增编号重置。--再插入新数据，id 通常会从 1 重新开始。

delete：删除表中的数据行
truncate：清空整张表的数据
drop：删除数据库、表等对象本身

聚合查询（对查询结果进行统计 行级别的）

聚合函数：

聚合函数通常会忽略 NULL，但表达式里出现 NULL 时，整个表达式可能变成 NULL。

selectcount(*)fromexam;--count 和（）之间不能有空格，先执行select 再count 统计查询结果行数selectcount(name)fromexam;--有一行name是null就不被统计，字符串和数字有隐式转换count(*)用来统计查询结果中的行数。实际理解时可以认为：先从表中取数据，再根据where条件过滤，最后统计剩余行数。

selectsum(math)fromexam;--sum 操作会直接排除null值selectsum(math+english+chinese)astotalfromexam;--算总分--如果一行里面 math、english、chinese 只要有一个是 NULL，那么这一行的结果就是 NULL，这一行就不会被 sum 正常累计。

selectavg(math+chnese+english)fromexam;

selectmax(english),min(english)fromexam;--字符串可以比大小

selectrole,count(*)fromempgroupbyrole;

注：一条 SQL 语句中通常只有一个 group by 子句，但 group by 后面可以写多个分组字段。分组后，每一组通常通过聚合函数产生一行统计结果。

非group by的列不能直接作为被查询的列，但通过聚合函数来进行一些分组查询。

selectuniversity,avg(question_cnt)asavg_question_cntfromuser_profilegroupbyuniversityhavingavg(question_cnt)>10;

• where 是在聚合以前的使用条件，写在group by前
• having是在聚合以后使用的条件，写在group by后

createtablestudent(idint,namevarchar(20),gendervarchar(20))check(gender='男'orgender='女');--后续修改插入数据都会自动带入条件，条件成立才能修改插入