2.mysql基础
2.1 常识
2.1.1 注释
#查询当前系统中的所有数据库(单行注释)
-- 查询当前系统中的所有数据库(单行注释)（-- 后面必须空格）
/*查询当前系统中的所有数据库(多行注释)*/
SHOW DATABASES; #查询数据库
SHOW DATABASES; -- 查询数据库
SHOW DATABASES; /*查询数据库*/2.1.2 SQL分类
DDL(Data Definition Language)数据定义语言
用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
DML(Data Manipulation Language)数据操作语言
用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
DQL(Data Query Language)数据查询语言
用来查询数据库中表的记录(数据)。关键字：select, where 等
DCL(Data Control Language)数据控制语言
用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等
2.2 操作数据库
2.2.1创建数据库
创建数据库
CREATE DATABASE 数据库名称;创建数据库，判断不存在，再创建
CREATE DATABASE IF NOT EXISTS 数据库名称;创建数据库，并指定字符集, 默认采用拉丁文, 指定为utf8可以表示汉字
CREATE DATABASE IF NOT EXISTS 数据库名称 CHARACTER SET 字符集名;2.2.2 修改数据库
修改数据库的字符集
ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称;2.2.3 删除数据库
删除数据库
DROP DATABASE 数据库名称;判断数据库存在，存在再删除
DROP DATABASE IF EXISTS 数据库名称;2.2.4 使用数据库
查询当前正在使用的数据库名称
SELECT DATABASE();使用数据库
USE 数据库名称;2.2.5 查询数据库
查询所有数据库名称
SHOW DATABASES;查询某个数据库的字符集：查询某个数据库的创建语句
SHOW CREATE DATABASE 数据库名称;2.3 操作表
2.3.1 创建
CREATE TABLE 表名(
列名称 数据类型,
列名称 数据类型,
列名称 数据类型
);MySql数据类型
类型 | MySql | 说明 |
整数类型 | int | |
小数类型 | double | double(总长度，小数位长度) |
日期类型 | date | 表示日期(只包含年月日) yyyy-MM-DD |
日期时间类型 | datetime | 表示日期(包含年月日时分秒) yyyy-MM-dd HH:mm:ss |
时间戳 | timestamp | 时间戳类型(包含年月日时分秒) yyyy-MM-dd HH:mm:ss 如果将来不给这个字段赋值,或者赋值为null,则默认使用当前的系统时间来自动赋值. |
字符串类型 | varchar | varchar(20) 存储最大字符数为20 |
2.3.2 删除
复制表
CREATE TABLE 新表名 LIKE 被复制的表名;删除表
DROP TABLE 表名判断表存在，存在则删除
DROP TABLE IF EXISTS 表名;2.3.3 修改
修改表名称
ALTER TABLE 原表名 RENAME TO 新表名;修改表字符集
SHOW CREATE TABLE 表名; -- 查看表的字符集
ALTER TABLE 表名 CHARACTER SET 字符集名称;修改表的列
添加列
ALTER TABLE 表名 ADD 列名 数据类型;修改列
修改列的名称和数据类型
ALTER TABLE 表名 CHANGE 修改前的列名 修改后的列名 修改后的数据类型;修改列的数据类型
ALTER TABLE 表名 MODIFY 列名 修改后的数据类型;删除列
ALTER TABLE 表名 DROP 列名;2.3.4 查询
查询某个数据库中所有的表名称
SHOW TABLES;查询表结构
DESC 表名;2.4 操作数据
2.4.1 新增
语法
INSERT INTO 表名(列名1,列名2,列名3,...列名n) values (值1,值2,值3...值n);注意
列名和值要一一对应。
如果表名后，不定义列名，则默认给所有列添加值
INSERT INTO 表名 values(值1,值2,...值n);除了数字类型，其他类型需要使用引号(单双都可以)引起来
2.4.2 删除
条件删除表中数据
语法
DELETE FROM 表名 WHERE [条件] limit 1;注意
如果不加条件，则删除表中所有记录。
DELETE FROM 表名;删除表中所有数据
语法
TRUNCATE TABLE 表名;*如果真的要将表中的数据全部删除,使用DELETE效率比较低,使用TRUNCATE语句会将表删除然后再创建一张空表,效率更高.
2.4.3 修改
语法
UPDATE 表名 SET 列名1 = 值1,列名2 = 值2,列名n = 值n WHERE [条件]注意事项
*如果不添加判断条件,则会修改本表中的所有数据.
2.4.4 单表查询
2.4.4.1 简单查询
语法结构
SELECT 字段名称
FROM 表名列表
WHERE 条件列表
GROUP BY 分组字段
HAVING 分组之后的条件
ORDER BY 排序规则
LIMIT 分页限定;基础查询
查询所有列数据
SELECT * FROM 表名;查询指定列的数据
SELECT 列名1,列名2...列明n From 表名;查询记录去重
SELECT DISTINCT 列名,列名2...列名n FROM 表名;计算列数据
#计算student表中的chinese与math值的和
SELECT name,chinese+math FROM student;*如果参与计算的列有NULL参与运算,计算结果都为NULL.
如果想要为NULL的值按照某种值进行计算,那么可以使用IFNULL函数进行判断.
IFNULL(可能出现NULL值的列,值)#计算student表中的chinese与math值的和,如果有数据为NULL,则按照0分计算.
SELECT name, chinese, math, IFNULL(chinese, 0) + IFNULL(math, 0) FROM student;查询结果别名，关键字AS
#计算student表中的chinese与math值的和,如果有数据为NULL,则按照0分计算,按照别名查询(AS版)
SELECT name AS "姓名", chinese as "语文成绩", math AS "数学成绩", IFNULL(chinese, 0) + IFNULL(math, 0) AS "总成绩" FROM student;AS 关键子可以省略
2.4.4.2条件查询
WHERE字句后跟条件
>、<、<=、>=、=、<> | <>在 SQL 中表示不等于，在 mysql 中也可以使用!= 没有== |
BETWEEN...AND | 在一个范围之内，如：between 100 and 200 相当于条件在 100 到 200 之间，包头又包尾 |
IN(集合) | 集合表示多个值，使用逗号分隔 |
AND、&& | 并且 |
OR、|| | 或者 |
IS NULL | 查询某一列为 NULL 的值，注：不能写=NULL |
LIKE '张%' | 模糊查询，配合占位符"_","%"使用 |
聚合函数 | count(col): 表示求指定列的总行数 max(col): 表示求指定列的最大值 min(col): 表示求指定列的最小值 sum(col): 表示求指定列的和 avg(col): 表示求指定列的平均值 |
分组 | GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP] 说明: 列名: 是指按照指定字段的值进行分组。 HAVING 条件表达式: 用来过滤分组后的数据。 WITH ROLLUP：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果 |
null值不能使用 = 和 != 判断
2.4.4.3分组查询
group by的使用
group by可用于单个字段分组，也可用于多个字段分组
-- 根据gender字段来分组
select gender from students group by gender;
-- 根据name和gender字段进行分组
select name, gender from students group by name, gender;group by + group_concat()的使用
group_concat(字段名): 统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割
-- 根据gender字段进行分组， 查询gender字段和分组的name字段信息
select gender,group_concat(name) from students group by gender;group by + 聚合函数的使用
-- 统计不同性别的人的平均年龄
select gender,avg(age) from students group by gender;
-- 统计不同性别的人的个数
select gender,count(*) from students group by gender;group by + having的使用
having作用和where类似都是过滤数据的，但having是过滤分组数据的，只能用于group by
-- 根据gender字段进行分组，统计分组条数大于2的
select gender,count(*) from students group by gender having count(*)>2;group by + with rollup的使用
with rollup的作用是：在最后记录后面新增一行，显示select查询时聚合函数的统计和计算结果
-- 根据gender字段进行分组，汇总总人数
select gender,count(*) from students group by gender with rollup;
-- 根据gender字段进行分组，汇总所有人的年龄
select gender,group_concat(age) from students group by gender with rollup;2.4.5 多表查询
2.4.5.1 内连接
隐式内连接
*隐式内连接使用WHERE条件消除无用的数据.
-- 内连接-隐氏内连接(查询所有的员工信息和部门信息)
SELECT * FROM EMP,DEPT WHERE EMP.DEPT_ID = DEPT.ID;-- 内连接-隐式内连接(查询两张表的对应字段,使用表名.字段名区分)
SELECT EMP.NAME,EMP.GENDER,DEPT.NAME FROM DEPT,EMP WHERE DEPT.ID = EMP.DEPT_ID;-- 内连接-隐式内连接(查询两张表的对应字段,使用表名的别名.字段名区分)
SELECT
e.NAME,
e.GENDER,
d.NAME
FROM
EMP e,
DEPT d
WHERE
e.DEPT_ID = d.ID;显示内连接
语法
SELECT 字段列表 FROM 表名1 [INNER] JOIN 表名2 ON 条件;示例
-- 显示内连接(查询所有的员工信息和对应部门信息)
SELECT * FROM EMP e INNER JOIN DEPT d on e.DEPT_ID = d.ID;-- 显示内连接(查询员工的姓名,性别,部门信息)不使用INNER
SELECT e.NAME,e.GENDER,d.NAME FROM EMP e JOIN DEPT d on e.DEPT_ID = d.ID;2.4.5.2 外连接
左外连接
语法
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;*左外连接查询的是左表的所有数据以及交集的部分.
示例
-- 查询所有员工的姓名,性别以及部门信息,如果员工有部门则显示部门,如果员工没有部门则不显示.
SELECT e.NAME,e.GENDER,d.NAME FROM emp e LEFT JOIN dept d ON e.`DEPT_ID` = d.`ID`右外连接
语法
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;*右外连接查询的是右表的所有数据以及交集的部分.
示例
SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;2.4.5.3 子查询
在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句，外部那个select语句则称为主查询.
主查询和子查询的关系:
子查询是嵌入到主查询中
子查询是辅助主查询的,要么充当条件,要么充当数据源
子查询是可以独立存在的语句,是一条完整的 select 语句
例1. 查询大于平均年龄的学生:
select * from students where age > (select avg(age) from students);例2. 查询学生在班的所有班级名字:
select name from classes where id in (select cls_id from students where cls_id is not null);例3. 查找年龄最大,身高最高的学生:
select * from students where (age, height) = (select max(age), max(height) from students);相关文章
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
