目录
1.数据库类型:
数值
- tinyint 1个字节 十分小
- smallint 2个字节 较小
- mediumint 3个字节 中等
- int 4字节 标准数据
- bigint 8个字节 较大的数据
- float 4个字节 浮点数
- double 8个字节 浮点数
- decimal 字符串形式的浮点数,金融计算
字符串:
- char 字符串固定大小 0~2
- varchar 可变字符串 0~65535
- tinytext 微型文本 2的8次方-1
- text 文本串 2的16次方-1
时间日期
- date YYYY-MM-DD 日期格式
- time HH:mm:ss 时间格式
- datetime 日期加格式
- timestamp 时间戳 1970.1.1到现在
- year 年份表达
null:运算里面有null 结果都为null
Unsigned:无符号整数(无负数)
Zerofill:0填充
注意:每个表都必须存在一下五个字段
- id 主键
- version 乐观锁
- is_delete 伪删除
- gmt_create 创建时间
- gmt_update 修改时间
2.创建表:
-- AUTO_INCREMENT 自增
-- 所有的语句后面加 ,(英文的),最后一个不用加
-- default 默认值
-- 注释
-- primary key 主键,一般一个表只有一个唯一的主键;
-- 设置引擎ENGINE=INNODB
-- CHARSET 编码
CREATE TABLE teacher(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`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
show create database school 查看创建数据库的语句
show create table student 查看student数据表的定义语句
desc student 显示表的结构
3.修改表:
ALTER TABLE teacher RENAME AS teacher1 -- 修改表名
ALTER TABLE teacher ADD age INT(1) -- 添加字段
ALTER TABLE teacher MODIFY age VARCHAR(11) -- 修改约束
ALTER TABLE teacher CHANGE age age1 INT(1) -- 字段重命名 ;alter table teacher1 drop age1 -- 删除表的字段
alter table 表名 drop 字段名
删除
drop table teacher1
drop table 表名
4.管理表:
插入insert
外键(了解即可):通过外键链接另一张表。
数据库就是单纯的表,只用来存储行和列,只有行(数据)和列(字段)
我们想使用多账表,想使用外键(程序去实现)。
insert into 表名([字段一,字段二,字段三]) values ("值一","值二","值三")
-- 由于主键自增,可以填null,或者不写该字段
insert into grade(gradename) values("打打打")
insert into grade(gradeid,gradename) values(NULL,"大一")-- 一般插入数据,一定要数据和字段 一一对应
-- 插入多个字段
insert into grade(gradename) values("大三"),("大四")
修改:update 修改谁 (条件) set 原来的值 = 新值
-- 修改学员名字,带了条件
update `student` set name="小明" where id=1-- 修改多个值
update `student` set name="小明",`email`="bloghut@163.com" where id=1
-- 修改学员名字,不带条件
update student set name="小明" //会全改
-- 语法 update 表名 set column_name=value,...[column_name=value] where [条件]
删除:delete
语法:delete from 表名 [where 条件]
-- 删除数据(避免这样写,会全部删除)
delete from student-- 删除指定数据
delete from student where id = 1delete from test -- 不会影响自增
truncate test -- 自增会归零
-- 清空表
truncate `student`
4.查询表:select
- 查询所有:select * from 表明
- 查询指定字段:select 字段 as 别名 from 表明
- select distinct studentno from result 发现重复属性,去重
- SELECT CONCAT('姓名',StudentName) AS '新名字' FROM student 拼接
select version() -- 查询系统版本(函数)
select 100*3-1 as 计算结果 -- 查询计算结果(表达式)
select @@AUTO_INCREMENT_INCREMENT -- 查询自增的步长(变量)
-- 学员成绩提分后查看
select studentno,studentresult+1 as 提分后 from result
where 条件子句
模糊查询;
-- 查询姓刘的同学
-- like 结合, %(代表0到任意个字符) _(一个字符)
select * from student where studentname like "刘%"-- 查询姓刘的同学,后面只有一个字
select * from student where studentname like "刘_"-- 查询名字中间有佳字的同学
select * from student where studentname like "%佳%"-- ===================in(具体的一个或多个值)===================
-- 查询1001 1002 1003 号学员
select * from student where studentno in(1001,1002,1003)-- 查询在北京的学生
select * from student where address in ('安徽','河南洛阳')-- 查询地址为空的学生
select * from student where address is null or address = ''-- 查询有出生日期的同学,不为空
select * from student where borndate is not null
联表查询:
七种联表
判断的条件:学生表的中 studentNo = 成绩表 studentNo
join (连接的表) on (判断的条件) 连接查询
where 等值查询
-- ============== 链表查询 ==================
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
select s.studentno,studentname,subjectno,studentresult
from student as s inner join result as r
on s.studentno = r.studentno
-- right join
select s.studentno,studentname,subjectno,studentresult
from student as s right join result r
on s.studentno = r.studentno
-- left join
select s.studentno,studentname,subjectno,studentresult
from student as s left join result r
on s.studentno = r.studentno
-- 查询缺考的同学
select s.studentno,studentname,subjectno,studentresult
from student as s left join result as r
on s.studentno = r.studentno
where studentresult is null
inner join | 如果表中至少有一个匹配,就返回行 |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |
left join | 会从左表中返回所有值,即使右表中没有匹配 |
自连接
自己的表和自己的表连接,核心:一张表拆为两张一样的表
-- 查询父子信息:把一张表拆为两张表
select a.categoryName as 父栏目,b.categoryName as 子栏目
from category as a,category as b
where a.categoryid = b.pid
-- 查询学生所属的年级信息(学号,姓名,年级名称)
select studentno,studentname,gradename
from student,grade
where student.gradeid = grade.gradeid
-- 查询科目所属的年级(科目名称,年级名称)
select subjectname,gradename
from `subject`,grade
where `subject`.gradeid = grade.gradeid
-- 查询参加了 数据库结构-1考试的同学信息:学号,姓名,科目名,分数
-- 查询来数据来自哪些表 student , result ,subject
select student.studentno,studentname,subjectname,studentresult
from student
inner join result
on student.studentno = result.studentno
inner join `subject`
on result.subjectno = `subject`.subjectno
where subjectname = '数据库结构-1'
分页和排序:
排序(order by):排序:升序 asc 降序 desc
分页(limit):limit 0,5 1~5 ,语法: limit 起始值,页面大小
子查询:
-- 查询分数不小于80分的学生信息(学号,姓名)
-- 使用连接查询
select distinct student.studentno,studentname
from student inner join result
on student.studentno = result.studentno
where studentresult >= 80
-- 查询分数不小于80分的学生信息(学号,姓名)
-- 在这个基础上 增加一个科目 高等数学-2
-- 方式一 使用子查询
select distinct student.studentno,studentname
from student inner join result
on student.studentno = result.studentno
where studentresult >= 80 and
subjectno = (select subjectno from `subject` where subjectname = "高等数学-2")
-- 方式二 使用嵌套子查询
select studentno,studentname
from student
where studentno in (
select studentno
from result
where studentresult >= 80 and subjectno = (
select subjectno
from `subject`
where subjectname = "高等数学-2"
)
)
-- 方式三 使用内连接查询
select student.studentno,studentname
from student INNER JOIN result
on student.studentno = result.studentno
INNER JOIN `subject`
on result.subjectno = `subject`.subjectno
where subjectname = "高等数学-2" and studentresult >= 80
分组和过滤:
-- 查询不同课程的平均分、最高分、最低分
-- 核心(根据不同的课程)
select subjectname,avg(studentresult),max(studentresult),min(studentresult)
from result inner join `subject`
on result.subjectno = `subject`.subjectno
GROUP BY result.subjectno -- 通过什么来分组
-- 查询不同课程的平均分、最高分、最低分、平均分大于80分的
select subjectname,avg(studentresult),max(studentresult),min(studentresult)
from result inner join `subject`
on result.subjectno = `subject`.subjectno
GROUP BY result.subjectno -- 通过什么来分组
having avg(studentresult) > 80