MySQL语句功能、数据类型和常用语句收集整理

功能分类

MySQL语句按照不同功能可分为四大类：

1. DDL-数据定义语言：用来定义数据库对象：创建库，表，列等。
2. DML-数据操作语言：用来操作数据库表中的记录，对表中数据进行增删改的操作
3. DQL-数据查询语言：用来查询数据
4. DCL-数据控制语言：用来定义访问权限和安全级别

SQL数据类型

double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；
char：固定长度字符串类型； char(10) 即 'abc       '，不满10位空格补齐
varchar：可变长度字符串类型；varchar(10) 即输出'abc'
text：字符串类型;
blob：二进制类型；
date：日期类型，格式为：yyyy-MM-dd；
time：时间类型，格式为：hh:mm:ss
datetime:日期时间类型 yyyy-MM-dd hh:mm:ss

 

DDL-数据定义语言

• 创建数据库：create database 数据库名  character set utf8;
• 修改数据库：alter database 数据库名 charactor set gbk;
• 创建一个表：先进入某个数据库然后 CREATE TABLE 表名(列名1  列的类型  [约束],  列名2  列的类型  [约束],  列名3  列的类型  [约束]);  例： CREATE TABLE student(name varchar(10), age int, sex varchar(2));
• 添加一列：ALTER TABLE 表名 ADD 列名 数据类型;
• 查看表的字段信息：DESC 表名;
• 修改一个表的字段类型：ALTER TABLE 表名 MODIFY 字段名  数据类型;
• 删除一列：ALTER TABLE 表名 DROP 列名;
• 修改表名：RENAME TABLE 原始表名 TO  要修改的表名; 
• 查看表的创建细节：SHOW CREATE TABLE 表名;
• 修改表的字符集：ALTER TABLE 表名 CHARACTER SET 字符集名称;
• 修改表的列名：ALTER TABLE 表名 CHANGE 原始列名 新列名 数据类型;
• 删除表：DROP TABLE 表名;

 

DML-数据操作语言

• 查询表中的所有数据：SELECT * FROM 表名;
• 插入操作：INSERT INTO 表名（列名1，列名2 ...）VALUE (列值1，列值2...);
• 批量插入：INSERT INTO 表名（列名1，列名2 ...）VALUES (列值1，列值2...)，(列值1，列值2...);       注意：列名与列值的类型、个数、顺序要一一对应，值不要超出列定义的长度，插入的日期和字符一样，都使用引号括起来。
• 更新操作：UPDATE 表名 SET 列名1=列值1，列名2=列值2，···  WHERE 字段=字段名;
• 删除操作：DELETE FROM 表名  WHERE  字段=字段名;   或   TRUNCATE TABLE 表名;      注意：DELETED 与TRUNCATE的区别：1、DELETE 删除表中的数据，表结构还在;删除后的数据可以找回；2、TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。删除的数据不能找回。执行速度比DELETE快。

 

DQL-数据查询语言

注意：数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。查询返回的结果集是一张虚拟表，通过查询语句查询出来的数据以表的形式展示，我们就称这个表为虚拟结果集。存放在内存中。

• 查询所有列：SELECT * FROM 表名;
• 查询指定列的数据：SELECT 列名1，列名2... FROM 表名;
• 条件查询：SELECT * FROM 表名 WHERE 条件1  条件查询运行符或关键字  条件2;

条件查询运行符或关键字：

=（等于）、!=（不等于）、<>（不等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于）；

BETWEEN…AND...；值在什么范围

IN(set)；固定的范围值

IS NULL；（为空） IS NOT NULL（不为空）   

AND；与

OR；或

NOT； 非

• 模糊查询：模糊查询必须使用LIKE关键字，其中 “_”匹配任意一个字母，n个“_”表示n个任意字母；“%”表示任意0~n个字符。例：查询姓名中第2个字母为“u”的学生记录：SELECT * FROM  students  WHERE  name  LIKE  '_u%';
• 字段控制查询：

1. 去除重复记录：SELECT DISTINCT name FROM students;
2. 把查询字段的结果进行运算，必须都要是数据型：SELECT *,字段1+字段2 FROM 表名;或SELECT *,age+IFNULL(score,0) FROM students;（需要把NULL当作0计算时）
3. 对查询结果起别名：在上面查询中出现列名为sx+IFNULL(yw,0)，这很不美观，现在我们给这一列给出一个别名，为total：SELECT *, yw+IFNULL(sx,0) AS total FROM score;

• 排序：SELECT  *  FROM  employee  ORDER  BY  salary  ASC;    注意：ASC升序，DESC降序
• 常用聚合函数：

1. COUNT()：统计指定列不为NULL的记录行数；
2. MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
3. MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
4. SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
5. AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

• 分组查询：

注意事项：在使用分组时，select后面直接跟的某个列名一般都出现在group by 后，如SELECT sex FROM employee GROUP BY sex;

1. group by + group_concat()组合使用：group_concat(列名) 表示分组之后，根据分组结果，使用group_concat(列名)来放置每一组的某列名下的值的集合。例：SELECT sex, GROUP_CONCAT(name) FROM employee GROUP BY sex; 输出的是按sex分组后，不同sex分组中的字段的name集合。
2. group by + 聚合函数：在select分组后加上相关聚合函数。例：1）查询每个部门的部门名称和每个部门的工资和：SELECT department,SUM(salary) FROM employee GROUP BY department;    2）查询每个部门的部门名称以及每个部门的人数：SELECT department,COUNT(*) FROM employee GROUP BY department;    3）查询每个部门的部门名称以及每个部门工资大于1500的人数：SELECT department,COUNT(salary) FROM employee WHERE salary > 1500 GROUP BY department; （注意，where紧随FROM其后）
3. group by + having：用来分组查询后指定一些条件来输出查询结果。having作用和where一样，但having只能配合group by使用。例：查询工资总和大于9000的部门名称以及工资和：SELECT department,SUM(salary) FROM employee GROUP BY department HAVING SUM(salary)>9000;

注：having与where的区别：

1. having是在分组后对数据进行过滤.
2. where是在分组前对数据进行过滤
3. having后面可以使用分组函数(统计函数)
4. where后面不可以使用分组函数
5. WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

• LIMIT用法：select * from 表名 limit x,y;       LIMIT 参数1，参数2； 参数1：从哪一行开始查，参数2：一共要查几行。

注：查询方法书写顺序：

执行顺序：