数据库.1

数据库

SQL：结构化查询语言

​ 关系型数据库的标注操作

分类：

一.SQL语言的分类

1.数据查询语言（Data Query Language , DQL）

DML主要用于对数据 增加、修改和删除的操作。

SELECT	选择要检索的列或表达式。
FROM	指定要检索数据的表。
WHERE	指定筛选条件，限制所检索的数据集。
ORDER BY	指定结果集的排序方式。
HAVING	在 GROUP BY 语句中使用，指定在聚合操作后对结果集进行筛选。

2.数据操作语言（Data Manipulation language, DML）

DDL主要用针对是数据库对象（数据库、表、索引、视图）进行创建, 修改和删除操作。

INSERT	增加数据
UPDATE	修改数据
DELETE	删除数据

3.数据控制语言（Data Control Language，DCL）

DCL用来授予或回收访问数据库的权限。

GRANT	授予用户权限
REVOKE	回收授予的权限

4.事务控制语言（Transaction Control Language，TCL）

TCL用于数据库的事务管理。

START TRANSACTION	开启事务
COMMIT	提交事务
ROLLBACK	回滚事务

二.DDL操作数据库

1.1查看数据库

show databases;

1.2创建数据库

create database bjsxt01;

1.3选择数据库

use bjsxt01;

1.4 删除数据库

drop database bjsxt01;

2.1MySQL中常见的数据类型

类型	描述
int	整型
double	浮点型
varchar	字符串型
date	日期类型，格式为：yyyy-MM-dd

2.2 整数类型

整数类型	大小	表数范围(有符号)	表数范围(无符号)	作用
TINYINT	1字节	(-128，127)	(0，255)	小整数值
SMALLINT	2字节	(-32768，32767)	(0，65535)	大整数值
MEDIUMINT	3字节	(-8388608，8388607)	(0，16777215)	大整数值
INT	4字节	(-214748364，2147483647)	(0，4294967295)	大整数值
BIGINT	8字节	(-9233372036854775808，9223372036854775807)	（0，18446744073709551615)	极大整数值

2.2.2 浮点数类型

浮点数类型	大小	作用
FLOAT(m,d)	4字节	单精度浮点数值, m总个数，d小数位
DOUBLE(m,d)	8字节	双精度浮点数值, m总个数，d小数位

2.2.3 字符串类型

字符串类型	大小	描述
CHAR(M)	0~255字符	允许长度0~M个字符的定长字符串
VARCHAR(M)	0~65535字符	允许长度0~M个字符的变长字符串
TINYTEXT	0~255字节	短文本数据
TEXT	0~65535字节	长文本数据
MEDIUMTEXT	0~16777215字节	中等长度文本数据
LONGTEXT	0~4294967295字节	极大文本数据
BLOB(M)	最大65K	以二进制存储图片，文件等数据

2.2.4 日期和时间类型

类型	格式	取值范围
TIME	‘HH:MM:SS’	(‘-838:59:59’, ‘838:59:59’)
DATE	‘YYYY-MM-DD’	(‘1000-01-01’, ‘9999-12-31’)
YEAR	YYYY	(1901, 2155), 0000
DATETIME	‘YYYY-MM-DD HH:MM:SS’	(‘1000-01-01 00:00:00’, ‘9999-12-31 23:59:59’)
TIMESTAMP	‘YYYY-MM-DD HH:MM:SS’	(‘1970-01-01 00:00:01’ UTC, ‘2038-01-19 03:14:07’ UTC)

3.总结练习（前期知识点结合）

--想到什么些什么，咱们写什么

--创建一个学生表格，并且定义三个属性（就写三个简单点但是和多属性意思一样）
CREATE TABLE student(
  aname VARCHAR(20),
	sex  VARCHAR(20),
	age INT(5)
)
SHOW TABLES;
--创建一个手机价格表（定义两个属性）
CREATE TABLE shouji(
	aa INT(3),
	bb VARCHAR(10)
)
--展示所有的表格看看结果（一步一步地来，写完一段定义代码运行一次以方便查看代码的作用）
SHOW TABLES;
--删除手机的表格
DROP TABLE shouji;
SHOW TABLES;#剩下一个表，前提是在你完成这些东西的时候是一个新的数据库

--修改学生表格的名字改为stu
#修改表名
RENAME TABLE student TO stu;
--显示表格查看是都修改成功
SHOW TABLES;

--显示学生表的结构 desc 表名
DESC stu;


#  上面是大写，但是为了看着方便下面写写小写

--向学生表中添加两个字段
alter table stu add bb varchar(5);
alter table stu add aa varchar(10);

--查看结果
desc stu;

--删除其中一个字段
alter table stu drop aa;
 
--查看结果
desc stu;
--修改表格中的字段类型定义
alter table stu modify bb int(5);
desc stu;

--修改表格中的字段名称
alter table stu change bb class int(5);
desc stu;
--插入数据
insert into stu values('高大王','男',22,2301);

--查看结果
#去看存储的表

--插入全部字段，将所有字段名都写出来
insert into stu(aname,sex,age,class) values('高小王','男',22,2301);#跟上面一样
--查看结果
#略
--一次插入多条数据
insert into stu values ('高王','男',22,2301),('高小','男',22,2301)

--插入指定字段的值
insert into stu(aname,sex,age) values('高','男',22)

/*上面的注意事项：

1. 值与字段必须要对应，个数相同、数据类型相同 
2. 值的数据大小，必须在字段指定的长度范围内
3. varchar char date类型的值使用单引号或者双引号包裹
4. 如果要插入空值，可以忽略不写或者写null 
5. 如果插入指定字段的值，必须要上写列名
6. 字符串日期类型支持  "2000-12-23"   "2000/12/23"  "2000.12.23"
*/

--修改数据
update stu set  sex='女',age=30,clazz=2302 where aname='高小';

--删除数据
#删除一条数据
delete from stu where aname='高';
#删除所有数据
delete from stu;

-- 添加主键约束（在实验第二种的时候不要忘了删除表）
--   设置主键 非空唯一
--   指定主键为eid字段
--   创建表时不指定主键, 然后通过DDL语句进行设置
	create table emp(
	#1.直接添加主键约束
	eid int primary key;		 -- 设置主键 非空唯一(逐渐的特性不能时空的同时不能有第二个)
	/*primary key(eid)    #2. 指定主键为eid字段*/
	ename varchar(10),
	sex char(1)
)

#3.-- 创建表时不指定主键, 然后通过DDL语句进行设置

drop table emp; -- 删除emp表
create table emp(
	eid int,
	ename varchar(10),
	sex char(1)
);

#下面是测试主键是否生效，围绕着定义去测试
-- 创建表时不指定主键, 然后通过DDL语句进行设置
alter table emp add primary key(eid);

-- 插入正常数据
insert into emp values(1,"宋江","男");

-- 插入一条数据, 主键为空
insert into emp values(null,"李逵","男");
-- Column 'eid' cannot be NULL 主键不能为空 

-- 插入一条数据, 主键重复
insert into emp values(1,"孙二娘","女");
-- Duplicate entry '1' for key 'emp.PRIMARY' 主键冲突

-- 使用DDL 删除表中的主键约束
alter table emp drop primary key;

-- 查看表结构
desc emp;


-- 自己添加数据可能出现重复, 我们通常希望在每次插入新记录时, 数据库自动生成主键字段的值。
-- --主键自增
drop table emp;
create table emp(
	eid int primary key auto_increment,
	ename varchar(10),
	sex char(1)
);
-- 测试主键自增，这里去查看表就会发现，没有设置键值他也会自己想下自己增加
insert into emp values(null,'张三','男');
insert into emp values(default,'李四','男');
insert into emp(ename, esex) values('王五', '男');

-- 约束内容不能为空
drop table emp;
create table emp(
	eid int primary key auto_increment,
	ename varchar(10) not null,
	sex char(1)
);
-- 测试非空约束
insert into emp values(default,null,'n');
-- Column 'ename' cannot be null  ename不能为空

-- 唯一约束
-- 案例：emp表 eid 主键约束,自增  ename 非空约束 esex唯一约束
drop table emp; -- 删除存在的emp表 
create table emp(
	eid int primary key auto_increment,
	ename varchar(10) not null,
	sex char(1) unique
);
-- 测试唯一约束 添加两个性别为男
insert into emp values(default,'zs','男');
insert into emp values(default,'lisi','男');

-- 默认值约束
-- 案例：emp表 eid 主键约束,自增  ename 非空约束 esex默认值'男'
-- 删除存在的emp表 
drop table emp;
create table emp(
	eid int primary key auto_increment,
	ename varchar(10) not null,
	sex char(1) default '男'
);
-- 查看emp表结构 
desc emp;
-- 测试默认值约束
insert into emp values(default,'zs',default);
-- default在数据库于艳丽的意思是默认值，
-- 因为主键和最后的都设定了默认值所以可以使用default来代表数据

-- 错误用法（前面逐渐可以为空还会调用但是后面不行）
insert into emp values(default,'zs',null); 


-- 检查约束
-- 删除存在的emp表 
drop table emp;
create table emp(
	eid int primary key auto_increment,
	ename varchar(10) not null,
	esex char(1) default '男' check(esex='男' or esex='女')
);
-- 查看emp表结构 
desc emp;
-- 测试检查约束
insert into emp values(default,'lisi','n');
--  报错 Check constraint 'emp_chk_1' is violated 不符合字段要求
insert into emp values(default,'高','女');
-- 成功

3.2综合案例

1. 需求

需求1: 创建数据库bjsxt01

需求2: 创建数据库表student

• 建立一张用来存储学生信息的表
• 字段包含学号、姓名、性别，年龄、入学日期、班级，email等
• 学号是主键 = 不能为空 + 唯一
• 年龄 不能为空 检查 年龄在1-150岁之间
• 姓名不能为空
• 性别默认值是男 检查 男|女
• Email唯一

需求3: 添加五条测试数据

• 实现一次插入一条数据
• 实现一次插入多条数据

需求4: 删除编号为1的学生信息

需求5: 修改编号为2, 性别改为相反

需求6: 验证表中的约束

2. 实现

-- 创建数据库
create database bjsxt01;

-- 切换到指定的数据库
use bjsxt01; 

-- 删除学生表
drop table student;

-- 创建表Student
create table student(
   id int primary key auto_increment comment '学号',
   name varchar(30) not null comment '姓名',
   age int not null check(age>=1 and age<=150) comment '年龄',
   sex char(1) default '男' check(sex='男' or sex='女') comment '性别',
   hiredate date comment '入学日期',
   email varchar(50) unique comment '邮箱'
);

-- 查看表结构
desc student;

-- 插入数据
insert into student values(default, '吕布', 20, '男', '2000-02-02', 'lvbu@qq.com');

-- 插入多条数据
insert into student values(default, '宋江', 30, '男', '2001-02-02', 'songjiang@qq.com'),
(default, '貂蝉', 30, '女', '2010-03-02', 'diaochan@qq.com')

-- 查看数据
select * from student;

-- 删除编号为1的数据
delete from student where id=1;

-- 修改编号为2的数据，性别改为相反的
update student set sex='女' where id=2;

-- 验证主键约束  1062 - Duplicate entry '3' for key 'PRIMARY'
insert into student values(3, 'tom', 30, '男', '2001-02-02', 'tom@qq.com');

-- 验证非空约束  1048 - Column 'name' cannot be null
insert into student values(default, null, 30, '男', '2001-02-02', 'lucy@qq.com');

-- 验证检查约束  3819 - Check constraint 'student_chk_2' is violated.
insert into student values(default, '关羽', 30, '中', '2001-02-02', 'guanyu@qq.com');

-- 验证唯一约束  1062 - Duplicate entry 'tom@qq.com' for key 'email'
insert into student values(default, '伊藤舞雪', 30, '男', '2001-02-02', 'tom@qq.com');