创建数据库表(重点)
--创建学生表(列,字段) 使用SQL创建
--学号int 登录密码varchar(20)姓名,姓名varchar(2),出生日期datatime,家庭住址 ,email
--auto_increment
--字符串使用 单引号括起来
--所有的语句后面加,(英文逗号),最后一个不用加
--primary key 主键 ,一般一个表只有一个唯一的主键
create table student
(
id int 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
)
总结创建创建表的格式:
create table 表名(
字段名 列类型 [属性] [索引] [注释],
字段名 列类型 [属性] [索引] [注释],
......
字段名 列类型 [属性] [索引] [注释]
)
常用命令
SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看创建数据库的语句
DESC student -- 查看表的结构
数据库表的结构
-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 之前使用的
*/
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,约为2倍 |
常规使用操作:
- INNODB:安全性高,事务的处理,多表多用户操作
- MYISAM:节约空间,速度较快
在物理空间存在的位置:
- 所有的数据库文件都存在data目录下,一个文件夹就对应一个数据库
- 本质还是文件的存储
MySQL引擎在物理文件上的区别
- INNODB在数据库表中只有一个 *.frm 文件,以及上级目录下的 ibdata1 文件
- MYISAM 对应的文件
- *.frm 表结构的定义文件
- *.MYD 数据文件(data)
- *.MYI 索引文件 (index)
设置数据库表的字符集编码:
charset=tf8
不设置的话,就是mysql默认的字符集编码(是不支持中文的,中文数据会乱码)
MySQL的默认编码是Latin1,不支持中文
可以在my.ini中配置默认的编码
character-set-server=utf8
修改删除表
- 修改
-- 修改表名: ALTER TABLE 旧表名 RENAME AS 新表明
ALTER TABLE student RENAME AS student001
-- 增加表的字段:ALTER TABLE 表明 ADD 列名 列属性
ALTER TABLE student001 ADD age INT(1)
-- 修改表的字段(重命名,修改约束!)
ALTER TABLE student1 MODIFY age VARCHAR(11) -- 修改约束
ALTER TABLE student1 CHANGE age age1 INT(1) -- 字段重命名(同时修改了约束)
-- 删除表的字段
ALTER TABLE student1 DROP age1
- 删除
-- 删除表
DROP TABLE IF EXISTS student1 -- 如果表存在就删除
所有的创建的删除操作尽量加上判断,以免报错( IF EXISTS )
- 注意点:
- `` 字段名,使用这个符号包裹!(Tab上面的按键)
- 注释符号: – 单行 /**/ 多行
- sql 关键字大小写不敏感
- 所有符号全用英文
MySQL数据管理
外键(了解即可)
- 方式一:在创建表的时候,增加外键约束 (比较复杂)
-- 创建年级表
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
-- 学生表的gradeid字段要去引用年纪表的gradeid
-- 定义外键key KEY `FK_gradeid` (`gradeid`),
-- 给这个外键添加约束(执行引用) references 引用 CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
CREATE TABLE `student` (
`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 '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '学生的年纪', -- 学生表的gradeid字段要去引用年纪表的gradeid
`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
删除有外键关系的表的时候,必须要先删除引用别人的表,在删除被引用的表。
- 方式二:表创建成功后,需要添加外键约束
-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
-- ALTER TABLE `表名` ADD CONSTRAINT `约束名` FOREIGN KEY (`作为外键的列名`) REFERENCES `哪各表` (`哪个字段`)
以上的操作都是物理外键,数据库级别的外键,不建议使用!(避免数据库过多造成困扰)
最好的实现外键的方式:
- 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
- 如果想使用多张表的数据,想使用外键(用程序去实现)
DML语言(全部记住)
**数据库意义:**数据存储,数据管理
DML语言:数据操作语言
- insert
- update
- delete
添加
- 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 ('张三','12345','男')
INSERT INTO `student` VALUES ( 2,'王五','12345','男','2001-06-08',1,'上海','email')
-- 一次插入多条数据
INSERT INTO `student` (`name`,`pwd`,`sex`)
VALUES ('张三','12345','男'),('李四','123456','男')
语法:insert into 表名
(字段名1
,字段名2
) VALUES (‘插入的值1’,‘插入的值2’)
注意事项:
- 字段和字段之间使用英文逗号 隔开
- 字段是可以省略的,但是后面的值必须要一一对应,不能少
- 可以同时插入多条数据,values 后面的值,需要使用,隔开即可。
修改
- update
-- 修改学员的名字 带了简介
UPDATE `student` SET `name` = '小明' WHERE id = 2
-- 不指定条件的情况下,会改动所有表!
UPDATE `student` SET `name` = '小航'
-- 修改多个属性,逗号隔开
UPDATE `student` SET `name` = '小明',`email` ='2597845QQ.com' WHERE id = 2;
-- 语法:
-- UPDATE 表名 set colnum_name = value,[colnum_name = value...] where 条件
条件:where 子句 运算符 id 等于某个值,在某个区间内修改…
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | 5=6 | false |
<> 或!= | 不等于 | 5<>6 | true |
> | |||
< | |||
<= | |||
>= | |||
BETWEEN…AND… | 在某个范围内 | [2.5] | |
AND | 我和你&& | 5>1 and 1>2 | false |
OR | 我或你|| | 5>1 or 1>2 | true |
-- 通过多个条件定位数据,无上限!
UPDATE `student` SET `name`='长江7号' WHERE `name` ='小明'AND `sex` = '女';
语法:UPDATE 表名 set colnum_name = value,[colnum_name = value…] where 条件
注意:
-
colnum_name 是数据库的列,尽量带上``
-
条件,筛选的条件,如果没有指定则会修改所有的列
-
value,是一个具体的值,也可以是一个变量
-
多个设置的属性之间,使用英文逗号隔开
-
UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `name` ='小明'AND `sex` = '男'; -- CURRENT_TIME 当前时间
删除
- delete 命令
语法:delete from 表名 [where 条件]
-- 删除数据
DELETE FROM `student` -- 避免这样删除,这样会全部删除
-- 删除指定数据
DELETE FROM `student` WHERE id = 1
- TRUNCATE 命令
作用:完全清空一个数据库表,表的结构和索引约束不会变!
-- 清空数据库表
TRUNCATE `student`
delete 和 TRUNCATE 的区别
- 相同点:是都能删除数据,都不会删除表结构
- 不同:
- truncate 重新设置 自增列,自增列的计数器的数据会归零
- truncate 不会影响事务
-- 测试delete 和 truncate 区别
CREATE TABLE `test`(
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `test` (`coll`) VALUES ('1'),('2'),('3')
DELETE FROM `test` -- 不会影响自增的计数
TRUNCATE `test` -- 自增的计数回归零
了解即可:delete 删除的问题 ,重启数据库,现象:
- INNODB : 自增列会重新开始(存在内存当中的,断电即失)
- MyLSAM:继续从上一个自增量开始 (存在文件中的,不会丢失)
DQL查询数据(最重点)
DQL
(Data Query LANGUAGE:数据查询语言)
- 所有的查询操作都用它 Select
- 简单的查询,复杂的查询它都能做
- 数据库中最核心的语言,最重要的语句
Select完整的语法:
指定查询字段
-- 查询全部的信息
SELECT * FROM student
-- 查询指定字段 select 字段1,字段2 from 表名
SELECT `id`,`name` FROM `student`
-- 别名,可以给结果起一个别名 ,不使用原列名,使用AS符号
-- 可以给字段起别名,也可以给表起别名
SELECT `id` ,`name` AS 学生姓名 FROM `student`
-- 函数 Concat (a,b) 拼接字符串
-- 在查询的 name 数据前 ,拼接上 姓名:
SELECT CONCAT('姓名:',`name`) AS 拼接的效果 FROM `student`
语法:select 字段1,字段2,… from 表名
有时候,起的列名不是那么见名知意。我们可以起别名 用 AS 关键字, 字段名 as 别名 表名 as 别名
去除重复数据
- distinct
- 作用去除查询结果中重复的数据,重复的数据只显示一条。
-- 查询一下有哪些同学参加考试,成绩
SELECT * FROM result -- 查询全部的考试成绩
SELECT `StudentNo` FROM result -- 查询有哪些同学参加了考试
-- 发现重复数据太多,影响观察,去重
SELECT DISTINCT `StudentNo` FROM result -- 去查询结果中的重复的数据
数据库的列可以做的事情
SELECT VERSION() -- 查询系统的版本(函数)
SELECT 100*3-2 AS 计算结果 -- 用来计算 (表达式)
SELECT @@auto_increment_increment -- 查询自增的步长 (变量)
-- 将查询的学院考试成绩 + 1分查看
SELECT `StudentResult` + 1 AS '加上分数后的结果' FROM result
数据库中的表达式:文本值,列,Null,函数,计算表达式,系统变量…
select 表达式 from 表名
where 条件子句
-
作用:检索数据中符合条件的值。
-
搜索的条件由一个或者多个表达式组成!结果 :布尔值
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a&&b | 逻辑与,两个都为真,结果为真 |
or || | a or b a||b | 逻辑或,其中一个为真,则结果为真 |
Not ! | not a !a | 逻辑非,真为假,假为真! |
尽量使用英文字母
-- ============================where===========================
-- 查询考试成绩在 95~100 分之间
SELECT StudentNo , StudentResult FROM result
WHERE StudentReault >=95 AND StudentReault<=100
-- and &&
SELECT StudentNo , StudentResult FROM result
WHERE StudentReault >=95 && StudentReault<=100
-- 模糊查询(区间)
SELECT StudentNo , StudentResult FROM result
WHERE StudentResult BETWEEN 95 AND 100
-- 除了1000号学生之外的同学的成绩
SELECT StudentNo , StudentResult FROM result
WHERE StudentResult != 1000;
-- != not
SELECT StudentNo , StudentResult FROM result
WHERE NOT StudentResult = 1000;
模糊查询
- 比较运算符
运算符 | 语法 | 描述 |
---|---|---|
is null | a is null | 如果操作符a为空,结果为真 |
is not null | a is not null | 如果操作符a不为空,结果为真 |
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 ==========
-- 查询姓刘的同学
-- like 结合 %(代表任意个字符) _(一个字符)
SELECT `id` ,`name` FROM `student`
WHERE `name` LIKE '刘%' -- %(代表任意个字符)
-- 查询姓刘的同学,名字后面只有一个字的
SELECT `id` ,`name` FROM `student`
WHERE `name` LIKE '刘_' -- _(一个字符)
-- 查询姓刘的同学,名字后面只有两个字的
SELECT `id` ,`name` FROM `student`
WHERE `name` LIKE '刘__' -- __(两个字符)
-- 查询名字中有天自的同学 %天%
SELECT `id` ,`name` FROM `student`
WHERE `name` LIKE '%天%'
-- ========== in (in后面填的是一个具体的一个或多个值)==========
-- 查询 1 ,2,3号的学生
-- 方式一:
SELECT `id` ,`name` FROM `student`
WHERE id =1 OR id =2 OR id =3
-- 方式二:
SELECT `id` ,`name` FROM `student`
WHERE id IN (1,2,3)
-- 查询在北京的学生
SELECT `id`, `name` FROM `student`
WHERE `address` IN ('上海','北京')
-- ======= NULL NOT NULL =======
-- 查询地址为空的学生 null ''
SELECT `id` ,`name` FROM `student`
WHERE address = '' OR address IS NULL
-- 查询有出生日期的同学 不为空
SELECT `id` ,`name` FROM `student`
WHERE `birthday` IS NOT NULL
联表查询
JOIN
-- ========== 联表查询 ==========
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
SELECT * FROM student
/*
思路:
1.分析需求,分析查询的字段来自哪些表,(连接查询)
2.确定使用哪种连接查询? 一共7种
确定交叉点(确定这两个表中哪个数据是相同的)
判断的条件:学生表中的 StudentNo = 成绩表中的 StudentNo
*/
-- join(连接的表) on(判断的条件)连接查询
-- where 等值查询
-- INNER JOIN
SELECT s.StudentNo, studentName,SubjectNo,StudentResult -- SubjectNo (科目编号在Result表中)
FROM student AS s
INNER JOIN result AS r
WHERE s.StudentNo = r.StudentNo
-- Right join (右联接:主要是右边表的信息)
SELECT s.StudentNo, studentName,SubjectNo,StudentResult -- SubjectNo (科目编号在Result表中)
FROM student s -- as 可以不写,空格也行
RIGHT JOIN result AS r
ON s.StudentNo = r.StudentNo
-- Left join (左联接:主要是左边表的信息)
SELECT s.StudentNo, studentName,SubjectNo,StudentResult -- SubjectNo (科目编号在Result表中)
FROM student s
LEFT JOIN result AS r
ON s.StudentNo = r.StudentNo
-- 查询缺考的同学
SELECT s.StudentNo, studentName,SubjectNo,StudentResult -- SubjectNo (科目编号在Result表中)
FROM student s
LEFT JOIN result AS r
ON s.StudentNo = r.StudentNo
WHERE StudentResult IS NULL
-- 查询参加了考试的同学信息:学号,学生姓名,科目名,分数
/*
思路:
1.分析需求,分析查询的字段来自哪些表,一共有三个表 student result subject(连接查询)
2.确定使用哪种连接查询? 一共7种
确定交叉点(确定这两个表中哪个数据是相同的)
判断的条件:学生表中的 StudentNo = 成绩表中的 StudentNo
*/
SELECT s.studentNo,studentName,SubjectName,StudentResult -- 查询的四个字段
FROM student s
RIGHT JOIN result r
ON r.studentNo = s.studentNo
INNER JOIN `subject` sub
ON r.SubjectNo = sub.SubjectNo
-- 要查询的数据 select ...
-- 从那几个表中查 from 表名 XXX join 连接的表 on 两个表交叉的条件
-- 假设存在多表查询,先从两张表开始,然后在依次增加
操作 | 描述 |
---|---|
Inner Join | 如果表中至少有一个匹配,就返回结果 |
Left Join | 会从左表中返回查询到的所有的值,即使右表中没有匹配 |
Right Join | 会从右表中返回查询到的所有的值,即使左表中没有匹配 |
自联接(了解)
- 自己的表和自己的表连接:核心:一张表拆为两张一样的表
-- 查询父子信息:把一张表看为两张一模一样的表
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a, `category` AS b
WHERE a.`categoryid` = b.`pid`
结果: