Mysql的基本命令

一、操作数据库基本命令

1、数据库的基本命令

mysql -u root -p123456 --连接数据库

update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; --修改用户密码

flush privileges; --刷新权限

show databases; --查看所有数据库

use 数据库名 --切换数据库 use命令
Database change

show tables; --查看数据库中的所有表

describe 数据库名; --显示数据库中所有的表的信息

create database 数据库名; --创建一个数据库

exit; --退出连接

-- 单行注释（sql原本的注释）
/* sql的多行注释 */

2、数据库的操作命令

mysql的命令是不区分大小写的，中括号中的代码是可选部分，可填写也可以不填写

CREATE DATABASE [IF NOT EXISTS] `数据库名`; --创建数据库

DROP DATABASE IF EXISTS `数据库名`; --删除数据库	

--`student`反引号的作用：如果表名或字段名是一个特殊字符，那就需要带上反引号 `tableName`
USE `数据库名`; --使用数据库

SHOW DATABASES; --查看所有的数据库

SHOW CREATE DATABASE `数据库名`; -- 查看创建数据库的语句

SHOW CREATE TABLE `表名`; -- 查看创建数据表的语句

DESC `表名`; -- 显示数据表的结构

二、数据库的列类型

数值

tinyint 十分小的数据 1个字节
smallint 比较小的数据 2个字节
mediumint 中等大小的数据 3个字节
int 标准的整数 4个字节最常用
bigint 较大的数据 8个字节
float 单精度浮点数 4个字节
double 双精度浮点数 8个字节（浮点数存在精度问题）
decimal 字符串形式的浮点数金融计算一般使用decimal

字符串

char 字符串固定大小的 0~255
varchar 可变字符串 0~65535 常用，与java中的string一起使用
tinytext 微型文本 2^8-1
text 文本串 2^16-1 保存大文本例如论文，博客

时间日期

date YYYY-MM-DD 年-月-日日期格式
time HH:mm:ss 时分秒时间格式
datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
timestamp 时间戳 1970.1.10到现在的毫秒数较为常用
year 年份表示

null

没有值，未知的值
注意，不要使用null进行运算，结果为null

三、数据库字段属性

Unsigned: 无符号的整数声明了该列不能声明为负数，否则报错

zerofill: 0填充把不足的为数用0填充

自增：

通常理解为自增，自动在上一条的记录的基础上+1（默认）
通常用来设计唯一的主键 index，必须是整数类型
可以自定义设计主键自增的起始值和步长

非空 NULL not null

假设设置为非空，如果不给它赋值，就会报错
默认为null时，如果不填值，默认为null

默认：

设置默认的值！
sex，默认值为男，如果不指定该列的值，则会有默认值

四、扩展

每张表必须存在的字段

id 主键
version 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间

五、创建数据库表

需要注意的单词

-- AUTO_INCREMENT 自增
-- COMMENT 注释
-- DEFAULT 默认
-- PRIMARY KEY 主键，唯一的
-- ENGINE 引擎，一般使用INNODB
-- CHARSET 字符集 utf8

-- 目标：创建一个school数据库
-- 创建一个学生表（列、字段） 使用sql语法创建
-- 学号 int   登录密码varchar（20） 姓名varchar（5） 性别varchar（2） 出生日期（datatime） 家庭住址varchar（20） email varchar（20）

-- 注意点： 使用英文()  表的名称和字段尽量使用``反引号括起来

-- AUTO_INCREMENT 自增
-- COMMENT 注释
-- DEFAULT 默认
-- PRIMARY KEY  主键，唯一的
-- ENGINE 引擎，一般使用INNODB
-- CHARSET 字符集 utf8

-- 字符串使用单引号括起来！
-- 所有的语句后面加上英文的逗号，最后一句不用添加逗号

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学生id',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '学生姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

创建数据库表格式

CREATE TABLE [IF NOT EXISTS] `表名`(
	`字段名` 列类型(必填) [属性] [索引] [注释],
    `字段名` 列类型(必填) [属性] [索引] [注释],
    `字段名` 列类型(必填) [属性] [索引] [注释],
    `字段名` 列类型(必填) [属性] [索引] [注释],
    PRIMARY KEY(`字段名`)
)[ENGINE=INNODB DEFAULT CHARSET=utf8]
[表类型][字符集设置][注释]

六、数据库引擎的区别（INNODB和MYISAM）

-- 关于数据库引擎
/*
	INNODB 默认使用
	MYISAM 早些年使用
*/

区别	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持，但支持表锁定	支持效率高
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小（表的内存）	较小	越为MYISAM的两倍

常规使用操作

MYISAM 节约空间，速度较快
INNODB 安全性高，事务的处理，多表多用户操作

所有的数据库文件都存在 data 目录下

MySQL引擎在物理文件上的区别

INNODB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
MYISAM对应文件

*.frm 表结构的定义文件

*.MYD 数据文件（data）

*.MYI 索引文件（index）

CHARSET=utf8 不设置的话，会是mysql的默认字符集编码（不支持中文）

七、修改表

有关于表的操作用 ALTER 进行操作

所有创建和删除都应该加上判断，以免报错

ALTER TABLE `原来的表名` RENAME AS `修改后的表名`; -- 修改表名 ALTER TABLE `dda` RENAME AS `test`;


ALTER TABLE `表名` ADD `[字段名]` 字段的列属性; -- 在表中添加字段  ALTER TABLE `test` ADD `age` INT(11);

ALTER TABLE `表名` MODIFY `需要修改的字段名` 字段的属性; -- 修改表中字段的属性  ALTER TABLE `test` MODIFY `age` VARCHAR(11); -- 修改表中字段的属性

ALTER TABLE `表名` CHANGE `旧的字段名` `新的字段名` 字段的列属性; -- 修改字段名  ALTER TABLE `test` CHANGE `age` `sex` INT(11); -- 修改字段名

ALTER TABLE `表名` DROP `字段名`; -- 删除表中的字段  ALTER TABLE `test` DROP `sex`;

DROP TABLE IF EXISTS `表名`;  -- 删除表  DROP TABLE IF EXISTS `dda`;  -- 删除表

八、外键

外键的创建和引用

物理外键，数据库级别的外键，不建议使用（避免数据库过多而造成困扰）

方式一：

-- 创建两个表  学生表和年级表
/*
学生表的 gradeid 字段要引用年级表的 gradeid
	1、定义外键 key
	2、给这个外键添加约束（执行引用） references 引用  constraint 约束
*/
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学生id',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '学生姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`gradeid` INT(10) NOT NULL COMMENT '学生年级',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`),
	KEY `FK_gradeid` (`gradeid`),
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 创建年级表

CREATE TABLE IF NOT EXISTS `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradename` VARCHAR(20) NOT NULL COMMENT '年级名称',
	PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

引用外键的关键代码

KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)

/*
KEY `FK_引用的外键名` (`需要引用的外键名`), 
CONSTRAINT `FK_引用的外键名` FOREIGN KEY (`需要引用的外键名`) REFERENCES `引用的外键来源表` (`引用的外键来源表的主键名`)
*/

方式二：

-- 使用alter操作添加外键约束
ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`);

/*
ALTER TABLE `需要外键的表名` ADD CONSTRAINT `约束名` FOREIGN KEY(`作为外键的列`) REFERENCES `被引用外键的表名` (`被引用外键的表名的字段`);
*/

注意点：

删除存在外键关系的表时，必须先删除引用的表（从表），再删除被引用的表（主表），例如，要删除年级表时，首先删除学生表。

九、insert 插入数据语句

公式：

注意点：

当字段被设置为非空时，要注意将字段的值插入，否则会报错
向表中插入数据，要注意插入的值与字段名一一对应，否则插入不成功
省略字段名插入时，要注意将值与表中的字段一一对应

-- insert into `表名`([字段名1,字段名2,字段名3,...]) values('值1'),('值2'),('值3'),('值4'),....

-- 向年级表插入数据
-- insert 插入语句

INSERT INTO `grade`(`gradename`) VALUES('大一');
-- 由于主键自增，可以省略主键的值（如果不写字段名，那么久会报错，插入语句的值是一一对应的）
-- insert into `grade` values('大三');

-- 插入多个值
INSERT INTO `grade`(`gradename`) VALUES('大二'),('大一');

向学生表中插入数据：

-- 向学生表中插入数据
INSERT INTO `student`(`name`,`gradeid`)VALUES('张三',1);

-- 向学生表插入对应字段的值
INSERT INTO `student`(`name`,`pwd`,`sex`,`gradeid`,`birthday`,`address`,`email`)VALUES('李四','789456','男',1,'2020-01-01','广州','123456@qq.com');

-- 向学生表中插入多条数据
INSERT INTO `student`(`name`,`pwd`,`sex`,`gradeid`,`birthday`,`address`,`email`)
VALUES('李四','789456','男',1,'2020-01-01','广州','123456@qq.com'),('王五','789456','男',1,'2020-01-01','广州','123456@qq.com');

-- 省略字段名插入数据
INSERT INTO `student`
VALUES(5,'李四','789456','男',1,'2000-01-01','广州','123456@qq.com'),
(6,'王五','789456','男',1,'2000-02-02','广州','123456@qq.com');

十、update修改语句

-- 语法
UPDATE `表名` SET colnum_name = value,[colnum_name = value,colnum_name = value,....] WHERE [条件]

-- 修改学生名字
UPDATE `表名` SET `字段名` = '需要修改的值' WHERE 需要指定的条件; --UPDATE `student` SET `name` = 'hello' WHERE id = 1;

-- 不指定条件修改，除非是批量修改同一个字段，否则要添加指定条件
UPDATE `表名` SET `字段名` = '需要修改的值'; -- UPDATE `student` SET `name` = 'hello';

-- 修改多个属性，需要指定属性名和属性值，中间用英文逗号隔开，加上判断条件
UPDATE `student` SET `name` = '张三',`pwd` = 'adfdafd',`sex` = '男',`email` = '4515@qq.com' WHERE id = 1;

-- 通过多个判断条件修改
UPDATE `student` SET `name` = '李四' WHERE `name` = '张三' AND `sex` = '女';
UPDATE `student` SET `name` = 'lll' WHERE id BETWEEN 2 AND 5;

-- 通过对value设置值，修改数据，value也可以是一个变量  CURRENT_TIME 当前时间变量
UPDATE `student` SET `birthday` = CURRENT_TIME WHERE `name` = '李四' AND `sex` = '女';

操作符	含义	范围	结果
=	等于	假如条件为5=6	false
<> 或 !=	不等于	假如 5<> 6或 5!=6	false
>	大于	5>6	false
<	小于	5<6	true
>=	大于或等于	5>=6	false
<=	小于或等于	5<=6	true
BETWEEN....AND...	区间	[2,5]	true
AND	&&	5>1 AND 1>3	false
OR	\|\|	5>1 AND 1>3	true

十一、delete删除数据

-- 避免这样使用（这样使用会导致数据库被清空）
DELETE FROM `表名`; -- DELETE FROM `student`;

-- 清空数据库表数据
TRUNCATE `表名`; -- TRUNCATE `student`;

-- 需要加上判断条件
DELETE FROM `表名` WHERE [判断条件]; -- DELETE FROM `student` WHERE id = 6;

DELETE 和 TRUNCATE的区别：

相同点：都能删除数据，但不会删除表的结构

不同点：

TRUNCATE 重新设置自增列的计数器，计数器会归零
TRUNCATE 不会影响事务

DELETE删除的问题，当使用DELETE删除数据之后，存在的现象

使用INNODB引擎，重启数据库后，自增列会从1开始（数据存在内存中，断电即失）
使用MyISAM引擎，重启数据库后，继续从上一个自增量开始（存在文件中，不会丢失）

十二、query查询语句

准备数据和表格

-- 创建学校数据库
CREATE DATABASE IF NOT EXISTS `school`;

-- 使用学校数据库
USE `school`;

-- 删除学生表
DROP TABLE IF EXISTS `student`;

-- 创建学生表
CREATE TABLE IF NOT EXISTS `student`(
	`studentno` INT(4) NOT NULL COMMENT '学号',
	`loginpwd` VARCHAR(20) DEFAULT NULL,
	`studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生名字',
	`sex` TINYINT(1) DEFAULT NULL COMMENT '性别,0或1',
	`gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
	`phone` VARCHAR(11) NOT NULL COMMENT '联系电话,允许为空',
	`address` VARCHAR(255) NOT NULL COMMENT '地址,允许为空',
	`borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
	`email` VARCHAR(50) NOT NULL COMMENT '邮箱账号允许为空',
	`identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
	PRIMARY KEY(`studentno`),
	UNIQUE KEY `identitycard`(`identitycard`),
	KEY `email`(`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8


-- 删除年级表
DROP TABLE IF EXISTS `grade`;
-- 创建年级表
CREATE TABLE IF NOT EXISTS `grade`(
	`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级姓名',
	PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 删除科目表
DROP TABLE IF EXISTS `subject`;
-- 创建科目表
CREATE TABLE IF NOT EXISTS `subject`(
	`subjectno` INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
	`subjectname` VARCHAR(20) DEFAULT NULL COMMENT '课程名称',
	`classhour` INT(4) DEFAULT NULL COMMENT '学时',
	`gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
	PRIMARY KEY(`subjectno`)
)ENGINE=INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET=utf8


-- 删除成绩单
DROP TABLE IF EXISTS `result`;

-- 创建成绩单
CREATE TABLE `result`(
	`studentno` INT(4) NOT NULL COMMENT '学号',
	`stubjectno` INT(4) NOT NULL COMMENT '课程编号',
	`examdate` DATETIME NOT NULL COMMENT '考试日期',
	`studentresult` INT(4) NOT NULL COMMENT '考试成绩',
	KEY `subjectno`(`stubjectno`) 
)ENGINE=INNODB DEFAULT CHARSET=utf8


-- 向科目表subject插入数据
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)
VALUES(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);

-- 向学生表插入数据
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233'),
(1002,'123456','张dd',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011235'),
(1003,'123456','非伟',0,2,'13800001234','北京朝阳','1930-1-1','text123@qq.com','123456198001011534'),
(1004,'123456','张发',0,2,'13800001234','北京朝阳','1970-1-1','text123@qq.com','123456198201011234');

INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1005,'123456','王伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456798001011234'),
(1006,'123456','值强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456899001011233'),
(1007,'123456','黄的',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456998001011235'),
(1008,'123456','菲菲',0,2,'13800001234','北京朝阳','1930-1-1','text123@qq.com','123450198001011534'),
(1009,'123456','发发',0,2,'13800001234','北京朝阳','1970-1-1','text123@qq.com','123451198201011234');

INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1010,'123456','王伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456798001011237'),
(1011,'123456','值强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456899001011273'),
(1012,'123456','黄的',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456998701011235'),
(1013,'123456','菲菲',0,2,'13800001234','北京朝阳','1930-1-1','text123@qq.com','123450797001011534'),
(1014,'123456','发发',0,2,'13800001234','北京朝阳','1970-1-1','text123@qq.com','123471198201011234');

INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1015,'123456','王伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123156798001011234'),
(1016,'123456','值强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','183456899001011233'),
(1018,'123456','黄的',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','623456998001011235'),
(1019,'123456','菲菲',0,2,'13800001234','北京朝阳','1930-1-1','text123@qq.com','125450198001011534'),
(1020,'123456','发发',0,2,'13800001234','北京朝阳','1970-1-1','text123@qq.com','133451198201011234');

-- 插入科目
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);

-- 插入年级数据
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

-- 插入学生成绩
INSERT INTO `result`(`studentno`,`stubjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58),
(1002,1,'2013-11-11 16:00:00',84),
(1002,2,'2013-11-12 16:00:00',80),
(1002,3,'2013-11-11 09:00:00',60),
(1002,4,'2013-11-13 16:00:00',90),
(1002,5,'2013-11-14 16:00:00',50),
(1000,1,'2013-11-11 16:00:00',86),
(1003,2,'2013-11-12 16:00:00',75),
(1003,3,'2013-11-11 09:00:00',66),
(1003,4,'2013-11-13 16:00:00',95),
(1003,5,'2013-11-14 16:00:00',56),
(1004,1,'2013-11-11 16:00:00',85),
(1004,2,'2013-11-12 16:00:00',70),
(1004,3,'2013-11-11 09:00:00',68),
(1004,4,'2013-11-13 16:00:00',98),
(1004,5,'2013-11-14 16:00:00',58),
(1005,1,'2013-11-11 16:00:00',85),
(1005,2,'2013-11-12 16:00:00',70),
(1005,3,'2013-11-11 09:00:00',68),
(1005,4,'2013-11-13 16:00:00',98),
(1005,5,'2013-11-14 16:00:00',58),
(1006,1,'2013-11-11 16:00:00',85),
(1006,2,'2013-11-12 16:00:00',70),
(1006,3,'2013-11-11 09:00:00',68),
(1006,4,'2013-11-13 16:00:00',98),
(1006,5,'2013-11-14 16:00:00',58),
(1007,1,'2013-11-11 16:00:00',85),
(1007,2,'2013-11-12 16:00:00',70),
(1007,3,'2013-11-11 09:00:00',68),
(1007,4,'2013-11-13 16:00:00',98),
(1007,5,'2013-11-14 16:00:00',58),
(1008,1,'2013-11-11 16:00:00',85),
(1008,2,'2013-11-12 16:00:00',70),
(1008,3,'2013-11-11 09:00:00',68),
(1008,4,'2013-11-13 16:00:00',98),
(1008,5,'2013-11-14 16:00:00',58),
(1009,1,'2013-11-11 16:00:00',85),
(1009,2,'2013-11-12 16:00:00',70),
(1009,3,'2013-11-11 09:00:00',68),
(1009,4,'2013-11-13 16:00:00',98),
(1009,5,'2013-11-14 16:00:00',58),
(1010,1,'2013-11-11 16:00:00',85),
(1010,2,'2013-11-12 16:00:00',70),
(1010,3,'2013-11-11 09:00:00',68),
(1010,4,'2013-11-13 16:00:00',98),
(1011,5,'2013-11-14 16:00:00',58),
(1011,1,'2013-11-11 16:00:00',84),
(1011,2,'2013-11-12 16:00:00',80),
(1011,3,'2013-11-11 09:00:00',60),
(1012,4,'2013-11-13 16:00:00',90),
(1012,5,'2013-11-14 16:00:00',50),
(1012,1,'2013-11-11 16:00:00',86),
(1012,2,'2013-11-12 16:00:00',75),
(1013,3,'2013-11-11 09:00:00',66),
(1013,4,'2013-11-13 16:00:00',95),
(1013,5,'2013-11-14 16:00:00',56),
(1014,1,'2013-11-11 16:00:00',85),
(1014,2,'2013-11-12 16:00:00',70),
(1014,3,'2013-11-11 09:00:00',68),
(1014,4,'2013-11-13 16:00:00',98),
(1014,5,'2013-11-14 16:00:00',58),
(1015,1,'2013-11-11 16:00:00',85),
(1015,2,'2013-11-12 16:00:00',70),
(1015,3,'2013-11-11 09:00:00',68),
(1015,4,'2013-11-13 16:00:00',98),
(1015,5,'2013-11-14 16:00:00',58),
(1016,1,'2013-11-11 16:00:00',85),
(1016,2,'2013-11-12 16:00:00',70),
(1016,3,'2013-11-11 09:00:00',68),
(1016,4,'2013-11-13 16:00:00',98),
(1016,5,'2013-11-14 16:00:00',58),
(1017,1,'2013-11-11 16:00:00',85),
(1017,2,'2013-11-12 16:00:00',70),
(1017,3,'2013-11-11 09:00:00',68),
(1017,4,'2013-11-13 16:00:00',98),
(1017,5,'2013-11-14 16:00:00',58),
(1018,1,'2013-11-11 16:00:00',85),
(1018,2,'2013-11-12 16:00:00',70),
(1018,3,'2013-11-11 09:00:00',68),
(1018,4,'2013-11-13 16:00:00',98),
(1018,5,'2013-11-14 16:00:00',58),
(1019,1,'2013-11-11 16:00:00',85),
(1019,2,'2013-11-12 16:00:00',70),
(1019,3,'2013-11-11 09:00:00',68),
(1019,4,'2013-11-13 16:00:00',98),
(1019,5,'2013-11-14 16:00:00',58),
(1020,1,'2013-11-11 16:00:00',85),
(1020,2,'2013-11-12 16:00:00',70),
(1020,3,'2013-11-11 09:00:00',68),
(1020,4,'2013-11-13 16:00:00',98),
(1020,5,'2013-11-14 16:00:00',58);

1、查询全部学生

-- 查询语句 查询表内全部信息
SELECT * FROM `表名`;

-- 查询指定字段
SELECT `字段名`,`字段名` FROM `表名`; -- select 和 from 之间可以放置多个字段，不只是两个字段

-- 别名 AS 给查询出来的表格的字段起一个名字，也可以给表起一个别名
SELECT `字段名` AS 字段别名,`字段名` AS 字段别名 FROM `表名` AS 表的别名;

-- 函数 例如拼接字符串函数：CONCAT(a,b) ab为参数
SELECT CONCAT('需要添加的字符串：',`属性的值`) AS 别名 FROM `表名`;


-- 去重 DISTINCT 去重关键字  重复的数据只显示一条
SELECT DISTINCT `需要去重的字段名` FROM `表名`;

SELECT VERSION(); -- 查询系统版本（函数）
SELECT 100*3-1 AS 计算结果; -- 用来计算 （表达式）
SELECT @@auto_increment_increment; -- 查询自增的步长（变量）

-- 查询结果 +1 处理
SELECT `字段名`,`可以计算的字段` +1 AS 别名 FROM `表名`;

2、where 条件子句

逻辑运算符：

运算符	语法	描述
and &&	a and b a&&b	逻辑与，两个为真，结果为真
or \|\|	a or b a \|\| b	逻辑或，其中一个为真，结果为真
not ！	not a ! a	逻辑非，真为假，假为真

-- where 条件查询
SELECT * FROM `表名` WHERE 判断条件 
-- 例如：SELECT `studentno`,`studentresult` FROM `result` WHERE `studentresult`>=90 AND `studentresult`<=100;
-- SELECT `studentno`,`studentresult` FROM `result` WHERE `studentresult`>=90 && `studentresult`<=100; 

-- 区间查询  BETWEEN 最小值 AND 最大值;  在 AND 两边填入区间值
SELECT * FROM `表名` WHERE `需要判断的字段名` BETWEEN 最小值 AND 最大值;
-- 例如：SELECT `studentno`,`studentresult` FROM `result` WHERE `studentresult` BETWEEN 95 AND 100;

3、模糊查询

比较运算符

运算符	语法	描述
IS NULL	a is null	如果操作符
IS NOT NULL	a is not null
BETWEEN	a between b and c
LIKE	a like b
IN	a in (a1,a2,a3)

-- 模糊查询
-- like结合 %（代表0到任意字符）  _（代表一个字符）
SELECT `字段名`,`字段名` FROM `表名` WHERE `字段名` LIKE '条件%' -- %后面可以跟0个或多个字符
SELECT `字段名`,`字段名` FROM `表名` WHERE `字段名` LIKE '条件_' -- _后面只能代表一个字符

4、联表查询

七种join理论，联表查询

INNER JOIN 连接方式查询

SELECT s.`studentno`,`studentname`,`stubjectno`,`studentresult` --需要查询的字段
FROM `student` AS s INNER JOIN `result` AS r  --需要连接的表
WHERE s.`studentno` = r.`studentno`  -- 两张表中相同的值

RIGHT JOIN 连接方式

-- right join
SELECT s.`studentno`,`studentname`,`stubjectno`,`studentresult`
FROM `student` AS s RIGHT JOIN `result` AS r
ON s.`studentno` = r.`studentno`

LEFT JOIN 连接方式

-- left join
SELECT s.`studentno`,`studentname`,`stubjectno`,`studentresult`
FROM `student` AS s LEFT JOIN `result` AS r
ON s.`studentno` = r.`studentno`

对比：

操作	说明
INNER JOIN	如果表中至少有一个匹配，就返回查询结果
LEFT JOIN	会从左表中返回所有的值，即使在右表中没有匹配
RIGHT JOIN	会从右表中返回所有的值，即使在左表中没有匹配

三表联表查询

-- 查询了参加考试的学生信息  学号，学生姓名，科目名，分数  三表查询
SELECT s.`studentno`,`studentname`,u.`subjectname`,r.`studentresult`
FROM `student` s INNER JOIN `subject` u RIGHT JOIN `result` r
ON s.`studentno` = r.`studentno` AND u.`subjectno` = r.`stubjectno`

5、自连接

一张表进行自我拆分，自己对自己进行连接查询

CREATE TABLE `category`(
	`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
	`pid` INT(10) NOT NULL COMMENT '父id',
	`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
	PRIMARY KEY(`categoryid`)
)ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

INSERT INTO `category`(`categoryid`,`pid`,`categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息')

父类

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

子类

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps技术

操作：查询父类对应的子类关系

这个表是将父类的对应的子类选项同时输入一张表中，通过categoryid和pid进行区别分类

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

-- 查询父子关系
SELECT a.`categoryName` '父栏目',b.`categoryName` '子栏目'
FROM `category` a,`category` b
WHERE a.`categoryid` = b.`pid`

6、分页和排序

排序：升序 ASC 降序 DESC

-- 查询参加了 数据库结构-1 考试的学生信息  学号，名字，科目名，分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
WHERE sub.`subjectname` = '数据库结构-1'
ORDER BY `studentresult` DESC

-- 语法  在where判断后加上 order by `字段名` 排序方式（ASC、DESC）

分页

SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
WHERE sub.`subjectname` = '数据库结构-1'
ORDER BY `studentresult` DESC
LIMIT 0,5

-- 语法  limit 起始位置,需要查询的条数

-- 查询C语言-1课程成绩排名前十的学生，并且分数要大于80分的学生信息
-- 学号，姓名，课程，分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno` = r.`stubjectno` 
WHERE `subjectname` = 'C语言-1' AND `studentresult` >=80
ORDER BY `studentresult` DESC
LIMIT 0,10

7、子查询

本质： 在where语句中嵌套一个子查询语句

子查询的查询顺序是由里及外的

-- 先查询result表中的信息
-- 查询所有的   C语言-1  的课程编号
SELECT `studentno`,`stubjectno`,`studentresult` 
FROM `result`
WHERE `stubjectno` = (
	SELECT `subjectno` FROM `subject` 
	WHERE `subjectname` = 'C语言-1'
)

-- 联表查询中嵌套子查询

-- 分数不小于0分的学生的学号和名字
-- 去重   DISTINCT
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE `studentresult` > 0 
AND `stubjectno` = (
	SELECT `subjectno` FROM `subject`
	WHERE `subjectname` = 'C语言-1'
)

无限套娃写法：

执行顺序是由里及外

-- 子查询再改造
SELECT `studentno`,`studentname` FROM `student` WHERE `studentno` IN (
	SELECT `studentno` FROM `result` WHERE `studentresult` > 0 AND `stubjectno` = (
		SELECT `subjectno` FROM `subject` WHERE `subjectname` = 'C语言-1'
	)
)
-- 在where的判断条件后再添加需要进行的查询语句

十三、函数

常见函数

-- 函数
SELECT ABS(-10) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个0~1之间的随机数
SELECT SIGN(10) -- 判断一个数的符号  负数返回-1，正数返回1

-- 字符串函数
SELECT CHAR_LENGTH('hello world') -- 字符串长度
SELECT CONCAT('hello','wor','ld') -- 拼接字符串
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱') -- 查询，从某个位置开始替换某个长度
SELECT LOWER('HelloWorld') -- 小写字母
SELECT UPPER('helloworld') -- 大写字母
SELECT INSTR('helloworld','h') -- 返回第一次出现该字符的索引
SELECT REPLACE('helloworld','h','H') -- 替换中间出现的指定字符
SELECT SUBSTR('helloworld',4,6) -- 返回指定的子字符串（原字符串，截取位置，截取长度）
SELECT REVERSE('helloworld') -- 反转
-- 时间函数
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前时间
SELECT NOW() -- 获取当前时间，带有时分秒
SELECT LOCALTIME() -- 获取本地时间
SELECT SYSDATE() -- 获取系统时间

-- 获取时分秒
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

-- 系统
SELECT SYSTEM_USER() -- 系统用户
SELECT USER() -- 当前用户
SELECT VERSION() -- 当前版本-- 函数
SELECT ABS(-10) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个0~1之间的随机数
SELECT SIGN(10) -- 判断一个数的符号  负数返回-1，正数返回1

-- 字符串函数
SELECT CHAR_LENGTH('hello world') -- 字符串长度
SELECT CONCAT('hello','wor','ld') -- 拼接字符串
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱') -- 查询，从某个位置开始替换某个长度
SELECT LOWER('HelloWorld') -- 小写字母
SELECT UPPER('helloworld') -- 大写字母
SELECT INSTR('helloworld','h') -- 返回第一次出现该字符的索引
SELECT REPLACE('helloworld','h','H') -- 替换中间出现的指定字符
SELECT SUBSTR('helloworld',4,6) -- 返回指定的子字符串（原字符串，截取位置，截取长度）
SELECT REVERSE('helloworld') -- 反转
-- 时间函数
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前时间
SELECT NOW() -- 获取当前时间，带有时分秒
SELECT LOCALTIME() -- 获取本地时间
SELECT SYSDATE() -- 获取系统时间

-- 获取时分秒
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

-- 系统
SELECT SYSTEM_USER() -- 系统用户
SELECT USER() -- 当前用户
SELECT VERSION() -- 当前版本

聚合函数分组和过滤

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值
...	...

-- 查询不同课程的平均分，最高分，最低分
-- 核心（根据不同课程分组）
SELECT `subjectname`,AVG(`studentresult`),MAX(`studentresult`),MIN(`studentresult`)
FROM `result` r
INNER JOIN `subject` sub
ON r.`stubjectno` = sub.`subjectno`
GROUP BY r.`stubjectno` -- 对什么字段进行分组
HAVING AVG(`studentresult`) > 60 -- 判断条件   在分组之后不能使用where，需要用HAVING 进行判断

十四、事务

事务的理解

同时两条sql一起执行，要么都成功，要么都失败

例子，加入A给B转账，A已经转账了，但是B没有收到，而且A的钱减少了，B的钱没有增加，这个就是事务需要解决的问题，要么都成功，要么都失败

事务就是将一组sql放在一个批次中执行

事务原则：ACID原则原子性，一致性，隔离性，持久性（脏读，幻读.....）

原子性：要么都成功，要么都失败，不允许一个成功一个失败

一致性：无论如何操作，到最后的所有值总和都不会改变

持久性：表示事务结束后数据不会随着外界的原因导致数据丢失

隔离性：针对多个用户同时操作，主要是排除其他事务对本次事务的影响

脏读：一个事务读取了另一个事务未提交的数据

幻读（虚读）：一个事务内读取到了别的事务刚插入的数据，导致前后读取不一致

过程

-- 手动处理事务
SET autocommit = 0 /*关闭*/
-- 事务的开启
START TRANSACTION -- 标记一个事务的开始，从这之后的sql都在同一个事务中
-- 提交：持久化（成功）
COMMIT
-- 回滚：回到原来的样子（失败）
ROLLBACK

-- 事务的结束
SET autocommit = 1 /*开启（默认的）*/

扩展点

 SAVEPOINT 保存点 -- 设置一个事务的保存点，相当于存档
 ROLLBACK TO SAVEPOINT 保存点 -- 回滚到保存点
  
 RELEASE SAVEPOINT 保存点 -- 将事务的保存点删除

理解事务：模拟转账

-- 模拟转账
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开启一个事务

UPDATE account SET money=money-500 WHERE `name` = 'A'; -- A减500
UPDATE account SET money=money+500 WHERE `name` = 'B'; -- B加500

COMMIT; -- 提交事务，数据持久化，将不会被回滚
ROLLBACK; -- 回滚
SET autocommit = 1; -- 开启自动提交

注意点：当关闭自动提交（SET autocommit = 0;）后，在后面要使用数据库，要将自动提交（SET autocommit = 1;）重新开启，否则将不会保存数据

十五、索引

在一个表中，主键索引只能有一个，唯一索引可以后很多个

主键索引（PRIMARY KEY）
- 唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引（UNIQUE KEY）
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
常规索引（KEY/INDEX）
- 默认的，index和key关键字来设置
全文索引（FullText）
- 快速定位数据

创建索引

-- 显示一个表中的所有索引
SHOW INDEX FROM `student`

-- 增加一个全文索引
ALTER TABLE school.`student` ADD FULLTEXT INDEX `studentname`(`studentname`)

-- id_表名_字段名
-- CREATE INDEX 索引名 on 表（字段名）
CREATE INDEX id_app_user_name ON app_user(`name`); -- 给name这个字段创建一个索引

插入100百万条数据函数

-- 插入100万条数据
-- sql 编程
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		-- 插入语句
		INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'541511681@test.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
		SET i = i+1;
	END WHILE;
	RETURN i;
END;

索引原则

索引不是越多越好
不要对经常变动的数据添加索引
小数据量的表不需要添加索引
索引一般加载常用来查询的字段上

十六、jdbc的连接和使用

一、导入驱动包

二、测试代码

import java.sql.*;

//我的第一个jdbc程序
public class JdbcTest01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1、加载驱动
        Class.forName("com.mysql.jdbc.Driver");  //固定写法，加载驱动
        // 2、用户信息和url
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String username = "root";
        String password = "123456";

        // 3、连接成功，数据库对象  connection相当于数据库
        Connection connection = DriverManager.getConnection(url, username, password);

        // 4、执行sql的对象   statement sql对象
        Statement statement = connection.createStatement();

        // 5、执行sql对象去执行sql
        String sql = "SELECT * FROM users";
        ResultSet resultSet = statement.executeQuery(sql);//执行查询使用executeQuery    ResultSet结果集

        //循环输出结果集
        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("birthday="+resultSet.getObject("birthday"));
        }
        // 6、释放连接
        resultSet.close();
        statement.close();
        connection.close();


    }
}

connection对象：

//connection代表数据库
//数据库设置自动提交
connection.setAutoCommit(); //参数为布尔值
//事务提交
connection.commit();
//事务回滚
connection.rollback();

statement对象：

statement.executeQuery();//执行查询的sql语句
statement.execute();//可以执行任何的sql
statement.executeUpdate();//更新，插入，删除都是用该方法

resultSet 结果集映射，指定类型获取数据库中的数据，如果不知道数据库的类型，则使用resultSet.getObject();

posted @ 2022-07-15 01:50 花椒蛋炒饭阅读(268) 评论(0) 收藏举报

刷新页面返回顶部

花椒蛋炒饭