MySQL数据库知识点

1.MySQL数据库的常用命令

--启动MySQL服务
net start mysql
--连接数据库
mysql -u root -p123456
--创建数据库
create database 数据库名
--查看所有的数据库
show databases
--切换数据库
use 数据库名
--删除数据库
drop database [if exists] 数据库名
--创建数据库表
CREATE TABLE [IF NOT EXISTS] '表名' (
    '字段名' 列类型 [属性][索引][注释],
    '字段名' 列类型 [属性][索引][注释],
    ...
    '字段名' 列类型 [属性][索引][注释],
    [PRIMARY KEY ('字段名')]
)[表类型][字符串设置][注释]--其中[表类型]ENGINE=INNODB [字符串设置]DEFAULT CHARSET=utf8[]表示可选操作
--查看数据库表
show tables
--退出连接
exit
--终止MySQL服务
net stop mysql

2.数据库的列类型

　　(1)数值

　　tinyint 十分小的数据 1个字节

　　smallint 较小的数据 2个字节

　　mediumint 中等大小的数据 3个字节

　　int 标准的整数 4个字节 常用的

       bigint　较大的数据　８个字节

       float 浮点数　４个字节

       double 浮点数　８个字节

       decimal 字符串形式的浮点数　金融计算的时候，一般用decimal

　　(2)字符串

　　char　字符串固定大小的　０－２５５

　　varchar　可变字符串　　０－６５５３５　　常用的变量　String

　　tinytext　微型文本　２＾８－１

　　text　文本串　２＾１６－１　　保存大文本

　　(3)时间日期

　　java.util.Date

　　date YYY-MM-DD 日期格式

　　time HH:mm:ss 时间格式

　　datetime　YYY－ＭＭ－ＤＤ　HH：ｍｍ：ss　最常用的时间格式

　　timestamp　时间戳　1970.１.１到现在的毫秒数　

　　year年份的表示

　　(4)null

　　没有值，未知

　　注意：不能使用null进行运算，结果为null

3.数据库的字段属性

　　(1)Unsigned

　　无符号的整数

　　声明了该列不能声明为负数

　　(2)zerofill

　　0填充

　　不足的位数，使用0来填充，int(3)， 5 ----005

　　(3)自增

　　通常理解为自增，自动在上一条记录的基础上+1（默认）

　　通常用来设计唯一的主键--index,必须是整数类型

　　可以自定义设计主键自增的起始值和步长

　　(4)非空：null、 not null

　　假设设置为not null,如果不给它赋值，就会报错

　　NUll，如果不填写值，默认就是null

　　(5)默认

　　设置默认的值

　　sex,默认值为男

4.数据表的类型

　　关于数据库引擎

　　INNODB 默认使用

　　MYISAM 早些年使用

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为2倍

　　常规使用操作

　　MYISAM 节约空间 速度较快

　　INNODB 安全性高，事务的处理，多表多用户操作

　　在物理空间存在的位置

　　所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库

　　本质还是文件的存储

5.删除修改表

　　(1)修改

　　修改表名 ：alter table 旧表名 rename as 新表名

　　例子：alter table teacher rename as teacher1

　　增加表的字段：alter table 表名 add 字段名 列属性

　　例子：alter table teacher1 add age int(11)

　　修改表的字段：

　　1)修改约束：alter table 表名 modify 字段名 列属性[]

　　例子：alter table teacher1 modify age varchar(11)

　　2)字段重命名：alter table 表名 change 旧名字 新名字 列属性[]

　　例子：alter table teacher1 change age age1 int(1)

　　删除表的字段：alter table 表名 drop 字段名

　　例子：alter table teacher1 drop age1

　　(2)删除

　　删除表(如果表存在再删除)：drop table if exists 表名

　　例子：drop table if exists teacher1

6.DML语言

　　数据库操作语言

　　insert、updata、delete

　　(1)insert(添加)

--插入语句
insert into ‘表名’（‘字段名1’，‘字段名2’，...）
values（‘值1’），（‘值2’），...
--例：insert into ‘grade’ (‘gradename’) values('大四')
--一般写插入语句，一定要数据和字段一一对应
--插入多个字段
insert into 'grade'('gradename')
values('大二'),('大三')
insert into 'student'('name','pwd','sex')
values('张三','123456','男'),('李四','aaaa','男')

　　(2)updata(修改)

--修改学员的名字，带了条件
updata 'student' set 'name'='张三' where id=1;

--不指定条件的情况下，会改动所有表
updata 'student' set 'name'='狂神'；

--语法
updata '表名' set '属性'=‘new_value’,... [where 条件]

--修改多个属性，用,隔开
updata 'student' set 'name'='狂神'，‘email’='152462455@qq.com' where id=1;

　　条件：where 子句 运算符 id等于某个值，大于某个值，在某个区间内修改

操作符	含义	范围	结果
=	等于
<>或！=	不等于
>
<
<=
>=
between...and...	在某个范围内	[2,5]
and	&&与
or	||或

--通过多个条件定位数据
updata 'student' set 'name'='长江七号' where 'name'='狂神' and 'sex'=‘女’；

--修改的新值，可以是一个具体的值，也可以是一个变量
updata 'student' set 'birthday'=current_time where 'name'='长江七号' 
and 'sex'=‘女’；

　　(3)delete(删除)

　　语法：

delete from '表名' [where 条件]

--删除数据，没有条件，会全部删除
delete from 'student'

--删除指定数据
delete from 'student' where id=1;

　　truncate命令

　　作用：完全清空一个数据库表，表的结构和索引约束不会变　　

　　语法：truncate table ‘表名’

　　delete和truncate的区别

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

　　不同点：truncate重新设置自增列，计数器会归零

　　　　　　truncate不会影响事务

　　　　　　delete不会影响自增

7.DQL语言

　　数据查询语言

　　所有的查询操作都用它 select

　　简单的查询，复杂的查询都能做

　　数据库中最核心的语言，最重要的语句

　　使用频率最高的语言

　　(1)指定查询字段

--查询全部的学生 select 字段 from 表
select * from student
    
--查询指定字段
select 'StudentNo','StudentName' from student

--别名， 给结果取一个名字 as 可以给字段起别名，也可以给表起别名
select 'StudentNo' as 学号,'StudentName' as 学生姓名 from student as s
    
--函数 concat(a,b)(连接字符串)
select concat('姓名：',StudentName) as 新名字 from student

语法：

select 字段，... from 表名

有的时候，列名字不是那么见名知意，我们会起别名，字段名 as 新名字或表名 as 新名字

　　(2)distinct(去重)

　　作用：去除查询出来的结果中重要的数据，只显示一条

--查询一下有哪些学生参加了考试，成绩
select * from result  --查询全部的考试成绩
select 'StudentNo' from result --查询有哪些学生参加了考试
select distinct 'StudentNo' from result  --发现重复数据，去重

select version()  --查询系统版本（函数）
select 100*3-1 as 计算结果  --用来计算表达式（表达式）
select @@auto_increment   --查询自增的步长（变量）
    
--学员考试成绩+1分查看
select 'StudentNo','StudentResult'+1 as '提分后' from result

数据库中的表达式：文本值，列，null, 函数，计算表达式，系统变量...

select '表达式' from 表名

　　(3)where条件子句

　　作用：检索数据中符合条件的值
　　搜索的条件由一个或者多个表达式组成！结果为布尔值

　　逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	逻辑与
or ||	a or b a||b	逻辑或
not !	not a !a	逻辑非

--where
  
select 'StudentNo','StudentResult' from result
    
--查询考试成绩在95-100分之间的
select 'StudentNo','StudentResult' from result
where StudentResult>=95 and studentResult<=100
    
--and &&
select 'studentNo','studentresult' from result
where studentResult>=95 && studentResult<=100
    
--模糊查询
select 'studentNo','studentResult' from result
where studentResult between 95 and 100
    
--除了1000号学生以外的同学的成绩
select 'studentNo','studentName' from result
where studentNo!=1000
    
select 'studentNo','studentName' from result
where not studentNo=1000

　　模糊查询：比较运算符

运算符	语法	描述
is null	a is null
is not null	a is not null
between	a between b and c	若a在b和c之间，结果为真
like	a like b	SQL匹配，如果a 匹配b，则结果为真
in	a in(a1,a2,a3,....)	假设a在a1，或者a2...其中的一个值中，结果为真

 --模糊查询
 --查询姓刘的同学
 --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 '%嘉%'
    
 --in(具体的一个或者多个值)
 --查询1001，1002，1003号学员
   select 'studentNo','studentName' from student
   where studentNo in(1001,1002,1003)
        
 --查询学生
   select 'studentNo','studentName' from student
   where address in ('安徽','河南洛阳');

--null not null 
--查询地址为空的学生  为空
  select 'studentNo','studentName' from student
  where address='' or address is null
    
--查询有出生日期的同学  不为空
  select 'studentNo','studentName' from student
  where BornDate is not null
        
--查询没有出生日期的同学，为空 
  select 'studentNo','studentName' from student
  where BornDate is null

　　(4)联表查询

--链表查询 join

--查询参加了考试的同学（学号，姓名，科目编号，分数）
select * from student
select * from result

/*思路
1.分析需求，分析查询的字段来自那些表，（多个表——连接查询）
2.确定使用那种连接查询 7种
确定交叉点（两个表中那个数据是相同的）
判断条件：学生表中的studentNo=成绩表中的studentNo
*/
--内连接
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 as r
on s.studentNo=r.studentNo

--left join 左连接
select s.studentNo,studentName,subjectNO,studentResult
from student as s
left join result as 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

--思考题（查询参加考试的同学信息，学号，学生姓名，科目名，分数）
/*思路
1.分析需求，分析查询的字段来自那些表,student, result , subject(连接查询)
2.确定使用那种连接查询
确定交叉点
判断条件
*/

select s.studentNo,studentName,subjectName,studentResult
from student as s
right join result as r
on r.studentNo=s.studentNo
inner join subject as sub
on r.subjectNo=sub.subjectNo

　　(5)排序

--排序：升序 asc, 降序 desc
--order by 通过那个字段排序
--查询的结果根据成绩降序排序
select s.studentNo,studentName,subjectName,studentResult
from student as s
inner join result as r
on r.studentNo=s.studentNo
inner join subject as sub
on r.subjectNo=sub.subjectNo
where subjectName='数据库结构-1'
order by studentResult desc

　　select完整的语法

select [all | distinct]
(*| table.* | [table.field1[as alias1][.table.field2][as alias2]][...])
from table_name[as table_alias]
    [left|right|inner join table_name]  --联表查询
    [where....] --指定结果需满足的条件
    [group by...] --指定结果按照哪几个字段来分组
    [having]  --过滤分组的记录必须满足的次要条件
    [order by...]  --指定查询记录按什么排序
    [limit ([offset.]row_count | row_countOFFSET offset)];
    --指定查询的记录从哪条至哪条