MySQL笔记整理

1、基本结构

当数据库连接不上出现错误时，打开任务管理器将Mysql启动

1.1、数据库列类型

（1）   varchar 0~65535  可变字符串
（2）   int     4个字节  标准整数
（3）   text    2^16-1  文本串
（4）   datetime  YYYY-MM-DD HH:mm:ss     最常用的时间格式
（5）   timestamp    1970.1.1到现在的毫秒数，时间戳  

1.2、 数据库的字段属性

（1）   Unsigned     无符号数，声明了该列不能声明为负数
（2）   Zerofill       0填充，不足的位数用0填充，int(3), 5 -> 005
（3）   自增         自动在上一条记录的基础上加一，通常原来设计唯一的主键，index必须是整数类型，可以1自定义设计主键自增的起始值和步长
（4）   非空         如果不给他赋值则会报错，

1.3、 每个表都必须存在以下五个字段：

（1）id        主键
（2）`version`   乐观锁
（3）is_delete   伪删除
（4）gmt_create 创建时间
（5）gmt_updata 修改时间

1.4、五大基本操作

Show databases;         查看所有数据库

Use school;             切换数据库，use + 表明

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

Desc student;           显示数据库中所有表的信息

Create database 1        创建一个数据库，Create database +表明

	-- AUTO_INCREMENT 自增
-- 字符串使用，单引号括起来
-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键
/*
格式：
CREATE table [if not EXISTS] `表名`(
	`字段名`  列表类型[属性] [索引][注释],
	`字段名`  列表类型[属性] [索引][注释],
	......
	`字段名`  列表类型[属性] [索引][注释],
)[表类型][字符集设置]
*/
	CREATE table if not EXISTS `student2`(
	`id` int(4) NOt NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) not NULL DEFAULT '匿名' COMMENT '姓名',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	PRIMARY KEY(`id`))
	ENGINE = INNODB DEFAULT CHARACTER SET = utf8

SHOW CREATE DATABASE school -- 查看创建数据库的语句

SHOW CREATE TABLE student2  -- 查看student数据表的定义语句
DESC student2 							-- 显示表的具体结构

1.5、修改表名

ALTER TABLE 旧表名 RENAME AS 新表名

1.6、增加表的字段

--ALTER TABLE 表名 ADD 字段名 列属性
 ALTER TABLE student2 ADD age INT(11)

1.7、修改表的字段（重命名，修改约束）

--ALTER TABLE 表名 MODIFY 字段名 列属性[]     修改约束
ALTER TABLE student2 MODIFY age VARCHAR(11)

-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]     字段重命名
ALTER TABLE student2 CHANGE age age1 INT(1)

1.8、删除表的字段

ALTER TABLE 表名 DROP 字段名

1.9、删除表

DROP TABLE IF EXISTS 表名

2、DML语言（重点）

2.1、插入语句

-- INSERT INTO 表名（[字段1，字段2，字段3]）VALUES ('值1'),('值2'),('值3')
INSERT INTO `student2`(`name`) VALUES ('张三')

INSERT INTO `student2`(`name`) VALUES ('李四'),('王五');

INSERT INTO `student2`(`name`,`birthday`) VALUES ('小明','19990808'),('小李','19990909');

INSERT INTO `student2` VALUES ('10','小明','19990808'),('11','小李','19990909');

2.2、修改语句

-- UPDATE `表名` SET `列名` = 'value' WHERE [条件]
UPDATE `student2` SET `name` = '小张' WHERE `id` = 1;

UPDATE `student2` SET `name` = '小肖',`birthday` = '19991010' WHERE `id` BETWEEN 2 AND 5;

UPDATE `student2` SET `name` = '小王',`birthday` = '19991111' WHERE `name` ='小肖' AND `id` = 4;

UPDATE `student2` SET `name` = '小黄',`birthday` = CURRENT_TIME WHERE `name` ='小王' OR `id` = 4;

2.3、删除语句

DELETE FROM `student2` WHERE `id` = 1;

2.4、完全清空数据库

TRUNCATE `student2`;

3、DQL语言（数据查询语言）

create database if not exists `school`;
-- 创建一个school数据库
use `school`;-- 创建学生表
drop table if exists `student`;
create table `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(50) 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 `grade`(
	`gradeid` int(11) not null auto_increment comment '年级编号',
  `gradename` varchar(50) not null comment '年级名称',
    primary key (`gradeid`)
) engine=innodb auto_increment = 6 default charset = utf8;

-- 创建科目表
drop table if exists `subject`;
create table `subject`(
	`subjectno`int(11) not null auto_increment comment '课程编号',
    `subjectname` varchar(50) 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 '学号',
    `subjectno` int(4) not null comment '课程编号',
    `examdate` datetime not null comment '考试日期',
    `studentresult` int (4) not null comment '考试成绩',
    key `subjectno` (`subjectno`)
)engine = innodb default charset = utf8;


-- 插入学生数据 其余自行添加 这里只添加了2行
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');

-- 插入成绩数据  这里仅插入了一组，其余自行添加
insert into `result`(`studentno`,`subjectno`,`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);

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

-- 插入科目数据
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);

3.1、查询表的全部数据

-- SELECT 字段 FROM 表
SELECT * FROM `student`;

3.2、查询指定字段

SELECT `studentno`,`studentname` FROM student;

3.3、别名（AS）

-- 别名，给结果起一个名字，AS
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student;

3.4、拼接（CONCAT）

-- 函数 CONCAT(str1,str2,...),做拼接
SELECT CONCAT('姓名：',studentname) as 新名字 FROM student;
SELECT * FROM result;

3.5、去重（DISTINCT）

SELECT DISTINCT `studentno` FROM result;

SELECT `studentno`,`studentresult`+ 1 AS '提分后' FROM result;

SELECT `studentno`,`studentresult` FROM result WHERE `studentresult`<=95 AND `studentresult` >=70;

3.6、模糊查询（LIKE）

-- like		%代表0到任意个字符   _代表一个字符
SELECT `studentno`,`studentname` FROM `student` WHERE `studentname` LIKE '张%';

SELECT `studentno`,`studentname` FROM `student` WHERE `studentname` LIKE '张_';

SELECT `studentno`,`studentname` FROM `student` WHERE `studentname` LIKE '%紫%'; -- 查询名字中含‘紫’的同学

SELECT `studentno`,`studentname` FROM `student` WHERE `studentno` IN (1000,1001);

SELECT `studentno`,`studentname` FROM `student` WHERE `address` IN ('北京朝阳');

SELECT `studentno`,`studentname` FROM `student` WHERE `address` IS NOT NULL;

SELECT `studentno`,`studentname` FROM `student` WHERE `address` IS NULL;

3.7、 连表查询（JOIN）

-- INNER/LEFT/RIGHT JOIN (连接的表) ON (判断的条件)
-- INNER JOIN
SELECT s.studentno,studentname,subjectno,studentresult 
FROM student AS s 
INNER JOIN result AS r 
WHERE s.studentno = r.studentno
SELECT studentno,studentname,gradename
FROM student AS s
INNER JOIN grade AS g
ON s.gradeid = g.gradeid

3.7.1、RIGHT JOIN

-- 会从右表中返回所有的值，即使左表中没有匹配
--注意On和where的区别,WHERE只用在等值查询中
SELECT s.studentno,studentname,subjectno,studentresult 
FROM student AS s 
RIGHT JOIN result AS r 
ON s.studentno = r.studentno

3.7.2、LEFT JOIN

-- 会从左表student中返回所有的值，即使右表result中没有匹配
SELECT s.studentno,studentname,subjectno,studentresult 
FROM student AS s 
LEFT JOIN result AS r 
ON s.studentno = r.studentno

3.7.3、简单应用

-- 查询缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult 
FROM student AS s 
LEFT JOIN result AS r 
ON s.studentno = r.studentno
WHERE studentresult IS NULL

3.7.4、三表查询

-- 三表查询,学号、姓名、科目名、分数
SELECT s.studentno,studentname,subjectname,studentresult
FROM student AS s 
RIGHT JOIN result AS r 
ON s.studentno = r.studentno
INNER JOIN `subject` AS sub
ON r.subjectno = sub.subjectno

3.7.5、四表查询

-- 四表查询，学号、姓名、年纪、科目名、分数
SELECT s.studentno,studentname,subjectname,studentresult,gradename
FROM student AS s 
RIGHT JOIN result AS r 
ON s.studentno = r.studentno
INNER JOIN `subject` AS sub
ON r.subjectno = sub.subjectno
LEFT JOIN grade AS g
ON sub.gradeid = g.gradeid

3.8、排序

-- 升序：ASC,	降序：DESC
-- ORDER BY 字段名 ASC/DESC

3.9、分页

-- LIMIT 起始行,页面大小
SELECT s.studentno,studentname,subjectname,studentresult,gradename
FROM student AS s 
RIGHT JOIN result AS r 
ON s.studentno = r.studentno
INNER JOIN `subject` AS sub
ON r.subjectno = sub.subjectno
LEFT JOIN grade AS g
ON sub.gradeid = g.gradeid
LIMIT 1,3

-- 查询科目为‘高等数学-1’且分数高于80的学生姓名和学号
SELECT DISTINCT s.studentno,studentname -- ,subjectname,studentresult
FROM student AS s
INNER JOIN result AS r 
ON s.studentno = r.studentno
LEFT JOIN `subject` AS sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '高等数学-1' AND studentresult >80

4、聚合函数

SELECT COUNT(studentname) FROM student		-- COUNT(指定列),会忽略所有的null值
SELECT COUNT(*) FROM student		-- COUNT(*),不会忽略所有的null值
SELECT COUNT(1) FROM student		-- COUNT(1),不会忽略所有的null值
SELECT subjectname,AVG(studentresult) AS 平均分,MAX(studentresult) AS 最高分,MIN(studentresult) AS 最低分
FROM result AS r
INNER JOIN `subject` AS sub
ON r.subjectno = sub.subjectno
GROUP BY r.subjectno		-- 通过什么字段来分组
HAVING 平均分 > 80		-- 二次过滤

/*
SELECT语法：
SELECT [ALL | DISTINCT]  要查询的字段 FROM 表名
[INNER | LEFT | RIGHT] JOIN 要连接的表 ON 等值判断
WHERE (指定结果需满足的条件)
GROUP BY 字段		（通过哪个字段来分组）
HAVING 过滤分组后必须满足的次要条件	（位置）
ORDER BY 字段名 [ASC | DESC]		（升序，降序）
LIMIT startIndex,pagesize
*/

5、事务（ 转账）

CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
CREATE TABLE `account` (
	`id` INT(3) NOT NULL auto_increment,
	`name` VARCHAR(30) NOT NULL,
	`money` DECIMAL(9,2) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARACTER SET = utf8

INSERT INTO account(`name`,`money`) VALUES ('C',2000.00),('D',10000.00)

-- 模拟转账
SET autocommit = 0;  -- 关闭自动提交
START TRANSACTION -- 开启事务
UPDATE account SET money = money-500 WHERE `name` = 'C'
UPDATE account SET money = money+500 WHERE `name` = 'D'
COMMIT	-- 提交事务，一旦提交就被持久化
ROLLBACK  -- 回滚
SET autocommit = 1;  -- 恢复默认值

-- 显示所有索引信息
USE school
SHOW INDEX FROM student

6、索引例子

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'


-- 插入100万数据
delimiter $$
-- set global log_bin_trust_function_creators=TRUE;
-- 写函数之前必须要写，标志
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), CONCAT('100',i,'@qq.com'), CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
	SET i=i+1;
END WHILE;
RETURN i;
END;
SELECT mock_data() -- 执行此函数 生成一百万条数据


SELECT * FROM app_user WHERE `name` = '用户999999'

-- 索引名：id_表名_字段名
-- CREATE INDEX 索引名 on 表（字段名）
CREATE INDEX id_app_user_name on app_user(`name`);