java学习-mysql基本使用笔记

DDL 操作 数据库

创建数据库

/*指定数据库名称 默认数据库字符集为:latin1*/
CREATE DATABASE db1;
/*指定数据库名称 指定数据库的字符集 一般都指定为 utf8*/
CREATE DATABASE db1_1 CHARACTER SET utf8;

查看与选择

-- 切换数据库 从db1 切换到 db1_1
USE db1_1;
-- 查看当前正在使用的数据库
SELECT DATABASE();
-- 查看Mysql中有哪些数据库
SHOW DATABASES;
-- 查看一个数据库的定义信息
SHOW CREATE DATABASE db1_1;

修改数据库

-- 将数据库db1 的字符集 修改为 utf8
ALTER DATABASE db1 CHARACTER SET utf8;
-- 查看当前数据库的基本信息，发现编码已更改
SHOW CREATE DATABASE db1;

删除数据库

-- 删除数据库 db1_!
DROP DATABASE db1_1;

DDL 操作 数据表

创建表

CREATE TABLE 表名(
    字段名称1 字段类型(长度)，
    字段名称2 字段类型 注意 最后一列不要加逗号
);

快速创建一个结构相同的表

CREATE TABLE 新表明 LIKE 旧表名

查看表/表结构

-- 查看当前数据库中有哪些表
SHOW TABLES;
-- 查看表结构
DESC 表名;
-- 查看创建表的SQL语句
SHOW CREATE TABLE 表名;

删除表

-- 直接删除 test1 表
DROP TABLE test1;
-- 先判断 再删除test2表
DROP TABLE IF EXISTS test2;

修改表

修改表名

RENAME TABLE 旧表名 TO 新表名;

修改表字符集

ALTER TABLE 表名 CHARACTER SET 字符集

追加列

ALTER TABLE 表名 ADD 字段 字段类型;

修改列型或类型长度

ALTER TABLE 表名 MODIFY 字段名称 字段类型

修改列名称

ALTER TABLE 表名 CHANGE 旧列名 新列名 类型(长度);

删除列

ALTER TABLE 表名 DROP 列名;

DML 操作表中数据

SQL 中的 DML 用于对表中的数据进行增删改操作

插入数据

INSERT INTO 表名(字段1,字段2,...) VALUES(值1,值2,...)

注意

• 值与字段必须要对应，个数相同&数据类型相同
• 值的数据大小，必须在字段指定的长度范围内
• varchar char date 类型的值必须使用单引号，或者双引号 包裹。
• 如果要插入空值，可以忽略不写，或者插入 null
• 如果插入指定字段的值，必须要上写列名

更改数据

UPDATE 表名 SET 列名 = 值;
UPDATE 表名 SET 列名 = 值 WHERE 条件;
UPDATE 表名 SET 字段1=值1,字段2=值2,... WHERE 条件;

删除数据

-- 删除表中所有数据
DELETE FROM 表名;
-- 删除表中指定条件的数据
DELETE FROM 表名 WHERE 条件;
-- 删除表推荐（先删除整张表, 然后再重新创建一张一模一样的表. 效率高）
truncate table 表名

DQL 查询 数据表

基础查询

SELECT 列名 FROM 表名
-- 使用 * 表示所有列
SELECT * FROM 表名
-- 查询部分字段
SELECT 列名1, 列名2, ... FROM 表名
-- 查询部分字段并起别名
SELECT 列名1 AS 别名1, 列名2 AS 别名2, ... FROM 表名
-- 查询某个字段并去重
SELECT DISTINCT 列名 FROM 表名
-- 查询某个字段

条件查询

SELECT 列名 FROM 表名 WHERE 条件;

比较运算符

运算符	描述
=	等于
>	大于
<	小于
>=	大于等于
<=	小于等于
<>	不等于
LIKE	包含 例如：LIKE '\测%' LIKE '%测%'
BETWEEN	区间 例如：BETWEEN 2000 and 10000
IN	包含
IS	等于
IS NOT	不等于

逻辑运算符

运算符	描述
AND	与
OR	或
NOT	非

查询排序

-- 默认排序 ASC
SELECT 列名 FROM 表名 ORDER BY 列名;
-- 排序 DESC
SELECT 列名 FROM 表名 ORDER BY 列名 DESC;

组合排序

SELECT 列名 FROM 表名 ORDER BY 列名1 ASC, 列名2 DESC;

聚合函数

SELECT 函数名(列名) FROM 表名;

聚合函数	描述
COUNT	计数
SUM	和
AVG	平均
MIN	最小
MAX	最大

-- 使用某一个字段
SELECT COUNT(列名) FROM 表名;

-- 使用 *
SELECT COUNT(*) FROM 表名;

-- 使用 1,与 * 效果一样
SELECT COUNT(1) FROM 表名;

-- 下面这条SQL 得到的总条数不准确,因为count函数忽略了空值 -- 所以使用时注意不要使用带有null的列进行统计
SELECT COUNT(列名) FROM 表名;

分组

分组查询指的是使用 GROUP BY 语句,对查询的信息进行分组,相同数据作为一组语法格式

SELECT 分组字段/聚合函数 FROM 表名 GROUP BY 分组字段 [HAVING 条件];

limit 关键字

• limit 是限制的意思,用于 限制返回的查询结果的行数 (可以通过 limit 指定查询多少行数据)
• limit 语法是 MySql 的方言,用来完成分页

SELECT 列名 FROM 表名 LIMIT 开始行数, 查询行数;

SQL 约束

对表中的数据进行进一步的限制，从而保证数据的正确性、有效性、完整性.违反约束的不正确数据,将无法插入到表中

常见约束

约束名称	描述
PRIMARY KEY	主键约束
UNIQUE	唯一约束
FOREIGN KEY	外键约束
CHECK	检查约束
NOT NULL	非空约束
DEFAULT	默认值约束
AUTO_INCREMENT	自增约束

主键约束

• 特点
  • 不可重复 唯一 非空
• 作用
  • 用来表示数据库中的每一条记录

添加主键约束

-- 方法一 创建表设置主键唯一
CREATE TABLE 表名(
    -- 创建表设置主键唯一
    字段名 字段类型 PRIMARY KEY
);
-- 方法二 创建表设置主键唯一
CREATE TABLE 表名(
    字段名 字段类型
    -- 指定主键
    PRIMARY KEY( 字段名)
);

-- 修改列设置主键唯一
ALTER TABLE 表名 ADD PRIMARY KEY (列名);

外键约束

-- 创建表设置外键约束
CREATE TABLE 表名(
    字段名 字段类型,
    -- 创建外键约束
    FOREIGN KEY( 字段名) REFERENCES 关联表名( 关联字段名)
);
-- 已有表增加外键约束
ALTER TABLE 表名 ADD FOREIGN KEY( 字段名) REFERENCES 关联表名( 关联字段名);

-- 可以自定义外键约束名称
CONSTRAINT 外键约束名称 FOREIGN KEY( 字段名) REFERENCES 关联表名( 关联字段名)

删除外键约束

-- 删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY( 字段名);

多表联合查询

内关联查询

通过指定的条件去匹配两张表中的数据, 匹配上就显示,匹配不上就不显示

SELECT 表1.字段1, 表2.字段2 FROM 表1, 表2 WHERE 表1.字段1 = 表2.字段2;

显式内关联

INNER 可以隐藏

SELECT 表1.字段1, 表2.字段2 FROM 表1 [INNER] JOIN 表2 ON 表1.字段1 = 表2.字段2;

左关联查询

• 以左表为基准, 匹配右边表中的数据,如果匹配的上,就展示匹配到的数据
• 如果匹配不到, 左表中的数据正常展示, 右边的展示为 null.

SELECT 表1.字段1, 表2.字段2 FROM 表1 LEFT JOIN 表2 ON 表1.字段1 = 表2.字段2;

右关联查询

• 以右表为基准，匹配左边表中的数据，如果能匹配到，展示匹配到的数据
• 如果匹配不到，右表中的数据正常展示, 左边展示为 null

SELECT 表1.字段1, 表2.字段2 FROM 表1 RIGHT JOIN 表2 ON 表1.字段1 = 表2.字段2;

子查询

概念:一条 select 查询语句的结果, 作为另一条 select 语句的一部分

• 特点
  • 子查询必须放在小括号中
  • 子查询一般作为父查询的查询条件使用
• 常见分类
  • where 型 子查询: 将子查询的结果, 作为父查询的比较条件
  • from 型 子查询 : 将子查询的结果, 作为 一张表,提供给父层查询使用
  • exists 型 子查询: 子查询的结果是单列多行, 类似一个数组, 父层查询使用 IN 函数 ,包含子查 询的结果

-- 作为父查询的比较条件
SELECT 字段1 FROM 表1 WHERE 字段1 > (
  SELECT MAX(字段2) FROM 表2
);
-- 作为一张表
SELECT 表1.字段1,表2.字段2  FROM 表1 LEFT JOIN (
	SELECT 字段1,字段2 FROM 表2
) AS 表2别名 ON 表1.字段1 = 表2别名.字段2
-- 单列多行, 类似一个数组
SELECT 字段1 FROM 表1 WHERE 表1.字段1 IN (
    SELECT 表2.字段2 FROM 表2
);