1.数据类型
数值
类型 | 大小(所占字节) |
---|---|
tinyint | 1 |
smallint | 2 |
mediumint | 3 |
int | 4 |
bigint | 8 |
float(浮点数) | 4 |
double(浮点数) | 8 |
decimal(字符串形式浮点数) |
字符串
类型 | 大小(所占字节数) |
---|---|
char | 0~255 |
varchar(可变字符串) | 0~65535 |
tinyint(微型文本) | 2^9-1 |
text(文本串) | 2^16-1 |
时间日期
类型 | 格式 |
---|---|
date | YYYY-MM-DD,日期格式 |
time | HH:mm:SS,时间格式 |
datetime | YYYY-MM-DD HH:mm:SS |
timestamp | 时间戳 |
year | 年份表示 |
null
没有值,未知
用NULL值计算,结果一定是NULL
2.增删改语句
CREATE TABLE
IF
NOT EXISTS `student` (
`id` INT ( 4 ) NOT NULL COMMENT '学号',
`name` VARCHAR ( 30 ) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`password` 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
SHOW CREATE DATABASE school;--查看创建数据库语句
SHOW CREATE TABLE student; --查看数据表语句
DESC student;--显示表结构
-- 修改表
ALTER TABLE teacher RENAME AS teacher1;
-- 增加表的字段
ALTER TABLE teacher1 ADD age INT(11);
-- 修改表的字段(重命名,修改约束)
ALTER TABLE teacher1 MODIFY age VARCHAR(11); --修改约束
ALTER TABLE teacher1 CHANGE age2 age1 int(1); --字段重命名
-- 删除表的字段
ALTER TABLE teacher1 DROP age1;
-- 删除表
DROP TABLE IF EXISTS teacher1;
3.Mysql数据管理
外键
-- 定义外键key
-- 给外键添加约束
CREATE TABLE IF NOT EXISTS `student` (
`id` INT ( 4 ) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR ( 30 ) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`password` VARCHAR ( 20 ) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR ( 2 ) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '生日',
`gradeid` INT(10) NOT 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 `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT'年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
----------------------------------------------------------------
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT'年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
CREATE TABLE IF NOT EXISTS `student` (
`id` INT ( 4 ) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR ( 30 ) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`password` VARCHAR ( 20 ) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR ( 2 ) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '生日',
`gradeid` INT(10) NOT NULL COMMENT '学生年级',
`address` VARCHAR ( 100 ) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR ( 50 ) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY ( `id` )
) ENGINE = INNODB DEFAULT CHARSET = utf8
-- 创建表时没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`);
DML语言:数据操作语言
增加
-- 插入语句(添加)
-- insert into 表名 ([字段名1,字段名2,字段名3])values('值1'),('值2'),('值3'),(....)
INSERT INTO `grade`(`gradename`) VALUES('大四')
-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES('大一'),('大二')
INSERT INTO `student`(`name`,`password`,`sex`)
VALUES('张三','12212','男'),('李四','41413','男')
修改
-- 修改名字
UPDATE `student` SET `name`='peanut' WHERE id = 1;
-- 修改多个属性
UPDATE `student` SET `name`='peanut',`email`='27960@qq.com' WHERE id = 1;
-- 通过多个条件定位数据
UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `name`='peanut1' AND `sex`='男' ;
删除
TRUNCATE `student` -- 完全清空一个数据库表,结构和索引约束不变,自增会归零
DELETE FROM `student`-- 不影响自增
-- 删除指定数据
DELETE FROM `student` WHERE id=2;
DQL查询数据***
-- 查询所有学生
SELECT * FROM `student`
-- 查询指定字段
SELECT `studentno`,`studentname` FROM student
-- 给结果起名
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student
--函数 CONCAT(str1,str2,...)
SELECT CONCAT('姓名:',studentname) AS 新名字 FROM student
CONCAT(str1,str2,…)
-- 查询全部的成绩
SELECT * FROM result;
-- 查询参加考试的学生
SELECT `studentno` FROM result;
-- 去重
SELECT DISTINCT `studentno` FROM result;
-- 查询系统版本
SELECT VERSION();
-- 查询自增的步长
SELECT @@auto_increment_increment;
-- 考试成绩+1分查看
SELECT `studentno`,`studentresult`+1 AS '加分后' FROM result;
where条件子句
-- 逻辑运算符
-- 查询成绩在80~100分之间 &&
SELECT `studentno`,`studentresult` FROM result
WHERE studentresult>=80 AND studentresult<=100;
-- 模糊查询(区间)
SELECT `studentno`,`studentresult` FROM result
WHERE studentresult BETWEEN 80 AND 100;
-- 除了1000号学生之外的同学成绩 not
SELECT `studentno`,`studentresult` FROM result
WHERE studentno !=1000;
模糊查询
-- 模糊查询 比较运算符
-- 查询张姓学生 like %(代表0-任意个字符) _(一个字符)
SELECT `studentno`,`studentname` FROM `student`
WHERE studentname LIKE '张%';
-- 查询张姓学生 名字后面只有一个字 %X% 中间
SELECT `studentno`,`studentname` FROM `student`
WHERE studentname LIKE '张_';
-- 查询1001,1002,1003号学员 in(具体的一个或多个值)
SELECT `studentno`,`studentname` FROM `student`
WHERE studentno IN (1001,1002,1003);
-- 查询地址为空的学生 null ''
SELECT `studentno`,`studentname` FROM `student`
WHERE address='' OR address IS NULL;
-- 查询有出生日期的学生 not null
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NOT NULL;
联表查询
-- 联表查询
-- 查询参加了考试的同学(学号、姓名、科目、成绩)
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno =r.studentno
-- RIGHT JOIN
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
RIGHT JOIN result r
ON s.studentno=r.studentno
-- LEFT JOIN
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.studentno=r.studentno
RIGHT JOIN
LEFT JOIN
-- 参加考试的同学信息:学号、姓名、科目名、分数 (查不到数据,有问题)
SELECT s.studentno,studentname,subjectname,`studentresult`
FROM `student` s
RIGHT JOIN `result` r
ON r.studentno=s.studentno
INNER JOIN `subject` t
ON r.studentno=t.subjectno
自连接
-- 自连接数据库:
CREATE TABLE
IF
NOT EXISTS `category` (
`categoryid` INT ( 3 ) NOT NULL COMMENT 'id',
`pid` INT ( 3 ) NOT NULL COMMENT '父 id 没有父则为 1',
`categoryname` VARCHAR ( 10 ) NOT NULL COMMENT '种类名字',
PRIMARY KEY ( `categoryid` )
) ENGINE = INNODB CHARSET = utf8 COLLATE = utf8_general_ci;
INSERT INTO `category` ( `categoryid`, `pid`, `categoryname` )
VALUES
( 2, 1, '信息技术' );
INSERT INTO `CATEGOrY` ( `categoryid`, `pid`, `categoryname` )
VALUES
( 3, 1, '软件开发' );
INSERT INTO `category` ( `categoryid`, `PId`, `categoryname` )
VALUES
( 5, 1, '美术设计' );
INSERT INTO `category` ( `categoryid`, `pid`, `categorynamE` )
VALUES
( 4, 3, '数据库' );
INSERT INTO `category` ( `CATEgoryid`, `pid`, `categoryname` )
VALUES
( 8, 2, '办公信息' );
INSERT INTO `category` ( `categoryid`, `pid`, `CAtegoryname` )
VALUES
( 6, 3, 'web开发' );
INSERT INTO `category` ( `categoryid`, `pid`, `categoryname` )
VALUES
( 7, 5, 'ps技术' );
-- 查询父子信息
SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`
4.分页和排序
-- ASC升序 DESC 降序
-- ORDER BY `字段名` ASC/DESC
-- 分页
-- 第一页 LIMIT0,5 (1-1)*5
-- 第二页 LIMIT5,5 (2-1)*5
-- 第N页 LIMIT(N-1)*pageSize,pageSize
子查询
-- 分数不低于80分的学生的学号和姓名
-- 查询高等数学-2的编号
SELECT DISTINCT s.`studentno`,`studentname`
FROM student s
INNER JOIN result r
ON r.studentno =s.studentno
WHERE `studentresult`>=80 AND `subjectno`=(
SELECT `subjectno` FROM `subject`WHERE subjectname='高等数学-2'
)
-- 查询C语言-1前五名同学的成绩信息(学号、姓名、分数)
SELECT s.studentno,studentname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
WHERE`studentresult`>=0 AND `subjectno`=(
SELECT `subjectno` FROM `subject`WHERE subjectname='C语言-1'
)
ORDER BY `studentresult` DESC
LIMIT 0,5
分组和过滤
查询mysql 1055错误码:在mysql的配置中如果设置了sql_mode包含ONLY_FULL_GROUP_BY值得话,在进行查询时需要将select的字段都包含在group by 中。
-- 查询不同课程的平均分最高最低分 平均分>80 (分组)
SELECT subjectname, AVG(studentresult)AS 平均分 , MAX(studentresult)AS最高分,MIN(studentresult)AS最低分
FROM result r
INNER JOIN `subject` s
ON r.subjectno=s.subjectno
GROUP BY r.studentno,subjectname,studentresult
HAVING 平均分>80
5.MySQL函数
-- 查询mysql服务器版本:
select @@version
-- 查看sql_mode的语法
select @@GLOBAL.sql_mode;
select @@SESSION.sql_mode;
5.1 常用函数
5.2 聚合函数
count(*) || count(字段名) || count(1) 区别
5.3 MD5加密
-- testmd5
CREATE TABLE `testtestmd5md5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(20)NOT NULL,
`pwd` VARCHAR(50)NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 明文密码
INSERT INTO testmd5 VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1
-- 加密全部的密码
UPDATE testmd5 SET pwd=MD5(pwd)
-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'))
-- 校验:将用户传递进来的密码,进行md5加密,然后对比加密后的值
SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')
6.事务
事务的基本要素(ACID)
原子性(Atomicity)
一致性(Consistency)
隔离性(Isolation)
持久性(Durability)
脏读,不可重复读,幻读
-- mysql是默认开启事务自动提交的
SET autocommit=0 /*关闭*/
SET autocommit=1 /*开启(默认的)*/
-- 手动处理事务
SET autocommit=0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始
INSERT xx
INSERT xx
-- 提交
COMMIT
-- 回滚
ROLLBACK
-- 事务结束
SET autocommit=1 -- 开启自动提交
-- 转账
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 CHARSET=utf8
INSERT INTO account(`name`,`money`)
VALUES('A',2000.00),('B',10000.00)
-- 模拟转账:事务
SET autocommit=0; -- 关闭自动提交
START TRANSACTION -- 开启事务
UPDATE account SET money=money-500 WHERE `name`='A'
UPDATE account SET money=money+500 WHERE `name`='B'
SELECT *FROM `account`
COMMIT; -- 提交事务
ROLLBACK; -- 回滚
SET autocommit=1; -- 恢复默认值
7.索引
-- 显示所有的索引信息
SHOW INDEX FROM student;
-- 增加一个全文索引
ALTER TABLE school.student ADD FULLTEXT INDEX `studentname`(`studentname`);
-- 分析sql执行情况
EXPLAIN SELECT * FROM student; -- 非全文索引
SELECT * FROM student WHERE MATCH(studentname) AGAINST('张');