MySQL
所有的sql语句都需要用分号结尾
基本命令
show databases; -- 查看所有的数据库
use 数据库名; -- 切换数据库 use 数据库名
show tables; -- 查看数据库中所有的表
-- Ctrl + c 强行终止命令
describe 表名; -- 查看数据库中所有表的信息 describe 表名
create database 数据库名; -- 创建一个数据库
exit; -- 退出链接
-- 单行注释
/*
多行注释
*/
操作数据库
mysql的关键字不区分大小写
操作数据库
-- 创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名; -- IF NOT EXISTS可添加也可不添加
-- 使用数据库
use 数据库名; -- 切换数据库 use 数据库名
-- 查看数据库
SHOW DATABASE;
-- 删除数据库
DROP DATABASE IF EXISTS 数据库名; -- IF EXISTS可添加也可不添加
数据库的列类型
数值
- tinyint 十分小的数据 1个字节
- smallint 较小的数据 2个字节
- mediumint 中等大小的数据 3个字节
- int 标准的整数 4个字节 常用
- bigint 较大的数据 8个字节
- float 浮点数 4个字节
- double 浮点数 8个字节
- decimal 字符串形式的浮点数 金融计算时一般使用
字符串
- char 固定大小的 0~255
- varchar 可变字符串 0~65535 常用的变量
- tinytext 微型文本 2^8 - 1
- text 文本串 2^16 - 1 保存大文本
时间日期
- data YYYY-MM-DD 日期格式
- time HH:mm:ss 时间格式
- datatime YYYY-MM-DD HH:mm:ss 最常用的时间格式 常用
- timestamp 时间戳 1970.1.1到现在的毫秒数 常用
- year 年份表示
null
- 没有值,未知
- 不要使用null进行运算,其结果也为null
数据库的字段属性(重点)
每个表都需存在的五个字段:
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
Unsigned:
- 无符号的整数
- 声明了该列不能声明为负数
zerofill:
- 0填充
- 不足位数,使用0来填充,int(3),输入5,输出为 005
自增:
- 自动在上一条记录的基础上+1(默认)
- 通常用来设计唯一主键~ index,必须为整数类型
- 可自定义设计主键的起始值和步长
非空
- 假设设置为非空(not null),如果不给它赋值,就会报错
- 不声明非空(null),如果不填写值,默认就是null
默认
- 设置默认的值
- 如果不指定该列的值,则有默认的值
创建数据库表(重点)
-- 创建数据库表,数据库表名用``括起
CREATE TABLE IF NOT EXISTS `数据库表名`(-- IF NOT EXISTS可添加也可不添加
-- NOT NULL 非空;AUTO_INCREMENT 自增;COMMENT 注释 后接字符串,用单引号括起
-- 所有语句后加 , 最后一个字段不用加
-- DEFAULT 默认值 后接字符串
-- PRIMARY KEY 主键,一个表一般只有一个唯一的主键
`字段名` 列类型(如:int(4),代表长度为4的数值) NOT NULL DEFAULT '默认值' AUTO_INCREMENT COMMENT '注释',
PRIMARY KEY(`上述字段名`)
-- CHARSET=utf8,设置数据库表的字符集编码
)ENGING=INNODB DEFAULT CHARSET=utf8
DESC 数据库表名 -- 显示数据库表的结构
修改删除表
修改
-- 修改表名
ALTER TABLE 旧表名 RENAME AS 新表名
-- 添加字段
ALTER TABLE 表名 ADD 字段名 字段列属性
-- 修改字段,修改约束
ALTER TABLE 表名 MODIFY 字段名 新字段列属性
-- 修改字段,重命名
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新列属性
删除
- 所有创建和删除尽量都加上判断,以免报错
-- 删除字段
ALTER TABLE 表名 DROP 字段名
-- 删除表
DROP TABLE IF EXISTS 表名
MySQL数据管理
DML语言(重点)
insert语句
-- 插入语句
-- 一般写插入语句,数据与字段需一一对应
INSERT INTO `表名`(字段1,字段2,字段3,...) VALUE('值1','值2','值3'),('值a','值b','值c'),(...);
update语句
-- 修改字段内容
UPDATE `表名` SET `字段1`='值/变量',`字段2`='值/变量',... WHERE 条件;
-- where,之后设置修改字段的条件
-- 例
UPDATE `student` SET `name`='蝶梦生' WHERE sex='男' AND BETWEEN 4 AND 10;
操作符 | 含义 |
---|---|
= | 等于 |
<>或!= | 不等于 |
> | 大于 |
< | 小于 |
<= | 小于等于 |
>= | 大于等于 |
BETWEEN...AND | 区间范围,在两个数的范围之间 |
AND | 和,相当于&& |
OR | 或,相当于|| |
delete命令与truncate命令
delete命令
-- 删除数据
DELETE FROM `表名` WHERE 条件;
truncate命令
- 完全清空一个数据库表,表的索引和约束条件不会变
-- 清空数据库表
TRUNCATE TABLE `表名`;
DQL查询数据(最重点)
语法(顺序不可调换)
SELECT `字段1`,`字段2`,...
FROM `表名1` AS 别名1
INNER JOIN `表名2` AS 别名2
ON 条件 -- on,之后为连表查询;where,之后为同级查询
WHERE 条件
GROUP BY 以什么分组 -- 通过什么字段来分组,使用GROUP BY后不可使用WHERE,而是使用HAVING
HAVING 条件 -- 过滤分组的记录必须满足的次要条件
ORDER BY `字段名` ASC -- ORDER BY,通过哪个字段进行排序,ASC 升序,DESC 降序
LIMIT 起始值,页面显示数 -- 起始值从0开始
指定查询字段
-- 查询某表所有信息
SELECT * FROM `表名`;
-- 查询某表的某个信息
SELECT `字段1`,`字段2`,... FROM `表名`;
-- 别名,将字段换为方便阅读的字,也可以给表取别名
SELECT `字段1` AS 别名1,`字段2` AS 别名2,... FROM `表名`;
-- 函数 CONCAT(a,b) ,拼接字符串
SELECT CONCAT('姓名:',`字段`) FROM `表名`;
去重
-- 去除重复数据,使用DISTINCT
SELECT DISTINCT `字段` FROM `表名`;
where条件子句
- 检索数据中符合条件的值
逻辑运算符
运算符 | 意义 | 语法 |
---|---|---|
and 或 && | 逻辑与 | a and b a && b |
or 或 || | 逻辑或 | a or b 或 a || b |
not 或 ! | 逻辑非 | not a 或 !b |
模糊查询 比较运算符
运算符 | 意义 | 语法 |
---|---|---|
IS NULL | 如果操作符为null,结果为真 | A is null |
IS NOT NULL | 如果操作符为not null,结果为真 | A is not null |
BETWEEN...AND | 如果A在B和C之间则结果为真 | A between B and C |
Like | SQL匹配,如果A匹配B,则结果为真 | A like B |
IN | 如果A在A1,或A2,...其中的某一个值中,则结果为真,其中A1,A2,...应为一个具体的值 | A in (A1,A2,A3,...) |
- %,代表0到任意一个字符,只能在like中使用
- _,代表一个字符,只能在like中使用
联表查询
join 表名 on 条件
INNER JOIN
两表之中任意一表有匹配则返回该匹配字段
SELECT `字段1`,`字段2`,...
FROM `表名1` AS 别名1
INNER JOIN `表名2` AS 别名2
ON 条件 -- on,之后为连表查询;where,之后为同级查询
LEFT JOIN
从左表中返回所有的值,即使右表中没匹配
SELECT `字段1`,`字段2`,...
FROM `表名1` AS 别名1
LEFT JOIN `表名2` AS 别名2
ON 条件
RIGHT JOIN
从右表中返回所有的值,即使左表中没匹配
SELECT `字段1`,`字段2`,...
FROM `表名1` AS 别名1
RIGHT JOIN `表名2` AS 别名2
ON 条件
分页和排序
排序
SELECT `字段名`
FROM `表名`
WHERE 条件
-- ORDER BY,通过哪个字段进行排序,ASC 升序,DESC 降序
ORDER BY `字段名` ASC
分页
SELECT `字段名`
FROM `表名`
WHERE 条件
-- 起始值从0开始
LIMIT 起始值,页面显示数
子查询
SELECT `字段名`
FROM `表名`
WHERE `查询字段名` = (
-- 根据新的查询获得查询字段名的信息条件
SELECT `字段名` FROM `表名`
WHERE 条件
)
MySQL函数
常用函数
-- 数值运算
SELECT ABS(-1); -- 绝对值
SELECT CEILING(9.3); -- 向上取整
SELECT FLOOR(9.9); -- 向下取整
SELECT RAND(); -- 返回0~1之间的随机数
SELECT SIGN(10); -- 判断数值的符号 正数返回1 负数返回-1
-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航');; -- 宁符串长度
SELECT CONCAT("我","爱","你们"); -- 拼接字符串
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱'); -- 查询,从某个位置开始替换某个长度
SELECT LOWER("Kuangshen"); -- 小写宁母
SELECT UPPER('Kuangshen'); -- 大写字母
INSTR('kuangshen','h'); -- 返回第一次出现的子串的索引
SELECT REPLACE("狂神说坚持就能成功","坚持","努力"); -- 替换出现的指定字符串
SELECT SUBSTR('狂神说坚持就能成功',4,6); -- 返回指定的子字符串源字符串,截取的位置,截取的长度)
SELECT REVERSE('清晨'); -- 反转字符串
-- 时间和日期函数 (记住 )
SELECT CURRENT_DATE(); -- 获取当前日期
SELECT CURDATE();-- 张取当前日期
SELECT NOW(); -- 获取当前的时间
SELECT LOCALTIME(); -- 本地时时间
SELECT SYSDATE(); -- 系统时间
聚合函数
函数名 | 意义 |
---|---|
COUNT() | 计数 |
SUM() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
... | ... |
md5加密
-- 创建数据库表,数据库表名用``括起
CREATE TABLE IF NOT EXISTS `数据库表名`(-- IF NOT EXISTS可添加也可不添加
-- NOT NULL 非空;AUTO_INCREMENT 自增;COMMENT 注释 后接字符串,用单引号括起
-- 所有语句后加 , 最后一个字段不用加
-- DEFAULT 默认值 后接字符串
-- PRIMARY KEY 主键,一个表一般只有一个唯一的主键
`字段名` 列类型(如:int(4),代表长度为4的数值) NOT NULL DEFAULT '默认值' AUTO_INCREMENT COMMENT '注释',
PRIMARY KEY(`上述字段名`)
-- CHARSET=utf8,设置数据库表的字符集编码
)ENGING=INNODB DEFAULT CHARSET=utf8
DESC 数据库表名 -- 显示数据库表的结构
-- 插入语句
-- 一般写插入语句,数据与字段需一一对应
INSERT INTO `表名`(字段1,字段2,字段3,...) VALUE('值1','值2','值3'),('值a','值b','值c'),(...);
-- 给密码加密,不添加WHERE加密全部语句
UPDATE `表名` SET `pwd`=MD5(pwd) WHERE 条件;
事务
SET autocommit = 0; -- 关闭自动事务
START TRANSACTION;-- 开启事务
执行事务的内容
COMMIT; -- 提交事务,事务永久化,不可撤回
ROLLBACK; -- 回滚,返回事务开始阶段
SET autocommit = 1;-- 开启自动事务
/*以下了解*/
SAVEPOINT 保存点名 -- 设计事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 删除指定保存点
索引
索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
- 主键索引 (PRIMARY KEY )
- 唯一的标识,主键不可重复,只能有一个列作为主键
- 唯一索引 (UNIQUE KEY)
- 避免重复的列出现,唯一索引可以重复,多个列都可以标识位 唯一索引
- 常规索引 (KEY/INDEX)
- 默认的,index。key 关键字来设置
- 全文索引 (FulIText)
- 在特定的数据库引擎下才有,MyISAM
- 快速定位数据
基础语法
-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后,增加案引
-- 显示所有的索引信息
SHOW INDEX FROM `表名`
-- 增加一个全文索引 (索引名)列名,FULLTEXT可换为其他索引关键字,变为其他类型索引
ALTER TABLE 数据库.表名 ADD FULLTEXT INDEX `索引名`(`字段名`);
-- 添加一个索引
CREATE INDEX 索引名(id_表名_字段名) ON 表名(`字段名`);
-- EXPLAIN 分析sg1执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentame) AGAINST('刘'); -- 全文索引
索引的原则
- 索引不是越多越好
- 不要对进程变动数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上!
权限管理和备份
权限管理
用户表:mysql.user
本质:读取mysql.user进行增删改查
-- 修改密码(修改当前用户密码)
SET PASSWORD PASSWORD('新密码')
-- 修改密码(修改指定用户密码)
SET PASSWORD FOR 用户名 = PASSWORD('新密码')
-- 重命名
RENAME USER 原来名字 TO 新的名字
-- 用户授权 ALL PRIVILEGES 全部的权限
-- ALL PRIVILEGES 除了给别人授权,其他都能够干
GRANT ALL PRIVILEGES ON 数据库.表 TO 用户名 -- 授予所有权限
GRANT 权限名 ON 数据库.表 TO 用户名 -- 授予指定权限
-- 查询权限
SHOW GRANTS FOR kuangshen2 -- 查看指定用户的权限
SHOW GRANTS FOR rootalocalhost -- 查看最高权限用户的权限
-- 撤销权限
REVOKE ALL PRIVILEGES ON 数据库.表 FROM 用户名 -- 撤回所有权限
REVOKE 权限名 ON 数据库.表 FROM 用户名 -- 撤回指定权限
-- 删除用户
DROP USER 用户名
MySQL的备份
为什么要备份:
- 保证重要的数据不丢失
- 数据转移
MySQL 数据库备份的方式:
- 直接拷贝物理文件,从data文件中拷贝
- 在Sqlyog 这种可视化工具中手动导出
- 使用命令行导出 mysqldump 命令行使用
数据库设计规范
三大范式
第一范式 (1NF)
原子性: 保证每一列不可再分
第二范式(2NF)
前提:满足第一范式
每张表只描述一件事情
第三范式 (3NF)
前提:满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关
JDBC(重点)
用java操作数据库的规范
建立一个JDBC
//1.加截驱动
Class.forName("com.mysql.jdbc.Driver"); // 固定写法,加截驱动
//2。用户信息和urL
// localhost:3306 主机地址:端口,mysql默认端口3306
//jdbcstudy 数据库名
// ?useUnicode=true&characterEncoding=utf8&useSSl=true" 固定内容
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSl=true";
String username = "root";
String password ="123456";
//3。连接成功,数据库对象 Connection 代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//4.执行SOL的对象 Statement 行sgl的对象
Statement statement = connection.createStatement();
//5。执行SOL的对象 去 执行SOL,可能存在结果,查看返回结果
String sql ="SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql); //返的结果集,结集中封装了我们全部的查询出来的结果
while (resultSet .next()){
System.out.println("id=" + resultSet.getobject("id"));
System.out.println("name=" + resultSet.getobject("NAME"));
System.out.println("pwd="+ resultSet.getobject("PASSWORD"));
System.out.println("email=" + resultSet.getobject("email"));
System.out.println("birth=" + resultSet.getobject("birthday"));
System.out.println("-============-===================");
}//导出数据库信息
//6、释放连接
resultSet.close();
statement.close();
connection.close();
标签:--,数据库,MySQL,字段,字段名,表名,SELECT
From: https://www.cnblogs.com/zhao19811103/p/17190009.html