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`);