SQL

SQL

完成对MYSQL数据库中数据库,表及数据的CURD操作

目标

掌握MYSQL的基本数据库,表及表中记录的操作

1.完成对分类表的CURD的操作

需求分析:

使用CMD命令来创建一个数据库,并对数据库中的一张分类表进行增删改查操作;

数据库概述:

  • 什么是数据库:
    • 数据库就是一个文件系统,只不过我们需要通过命令(SQL)来操作这个文件系统
    • 数据库(Database)是按照数据结构来组织、存储和管理数据的建立在计算机存储设备上的仓库。
    • 数据库是长期存储在计算机内、有组织、可共享的数据集合。数据库中的数据指的是以一定的数据模型组织、描述和存储在一起、具有尽可能小的冗余度、较高的数据独立性和易拓展性的特点并可在一定范围内为多个用户共享。
  • 数据库的作用:
    • 储存数据,数据的仓库,带有访问权限限制不同人可以有不同的操作
  • 为什么要学习数据库:
    • 能持久化的存储数据,对数据的规模和类型有更好的管理。
    • 操作的都是后台数据,取到后台数据进行封装,然后交给前端去展现

常见的数据库:

mysql:开源免费的适用于中小型企业的免费数据库,sun公司收购了mysql,sun公司被oracle收购了,oracle收购之后,开始收费了

mariadb:由mysql创始人开发,是mysql开源版本的一个分支,基本上所有的命令与原来一致

oracle:甲骨文公司,商业软件,收费软件,适用于大型电商网站,收购lsun公司

db2:IBM公司,解决方案:软件和硬件,服务器架构,银行系统大多采用db2

sqlserver:windows里面,政府网站asp.net,并且大学教学通常都是采用SQLserver,图形化工具。

NOSQL非关系型数据库: key:value

mongodb

redis

关系型数据库:

主要是用来描述实体与实体之间的关系,

实实在在的事物:男生和女生 学生和班级 员工和部门

E-R关系图:要求必须掌握

实体:方框

属性:椭圆

关系:菱形

MYSQL数据库服务器

MYSQL数据库:数据库管理软件

服务器:就是一台电脑,这台电脑安装相关的服务器软件,这些软件会监听不同的端口号,根据用户访问的端口号,提供不同的服务

MYSQL的SQL语句

SQL:Structure Query Language 结构化查询语言

DDL:数据定义语言:定义数据库,数据表它们的结构:

  • create
  • drop
  • alter

DML:数据操纵语言:主要是用来操作数据

  • insert
  • update
  • delete

DCL:数据控制语言:定义访问权限,取消访问权限,安全设置

  • grant

DQL:数据查询语言:

  • select
  • from
  • where

数据库的CURD的操作

  • 首先要登录数据库服务器

创建数据库

1
2
3
4
5
6
7
8
9
create database 数据库的名字;
create database test;

-- 创建数据库的时候,指定字符集
create database 数据库的名字 character set 字符集;
create database test character set utf8;

create database 数据库的名字 character set 字符集 collate 校对规则;
create database test character set utf8 collate utf8_bin;

查看数据库

1
2
3
4
5
6
-- 查看数据库定义的语句
show create database 数据库名字;
show create database test;

-- 查看所有数据库
show databases;

修改数据库的操作

1
2
3
-- 修改数据库的字符集
alter database 数据库的名字 character set 字符集;
alter database test character set gbk;

删除数据库

1
2
drop database 数据库名字;
drop database test;

其他数据库操作命令

1
2
3
4
5
6
-- 切换数据库(选中数据库)
use 数据库名字;
use test;

-- 查看当前正在使用的数据库
select database();

表的CRUD操作

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
create database 数据库的名字;

create table 表名(
列名 列的类型 约束,
列名2 列的类型 约束
);


列的类型;
java sql
int int
char/string char/varchar
char:固定长度
varchar:可变长度
长度代表的是字符的个数
double double
float float
boolean boolean
date date: YYYY-MM-DD
datetime:YYYY-MM-DD hh:mm:ss 默认值是null
timestamp:YYYY-MM-DD hh:mm:ss 默认使用当前时间

text:主要用来存放文本
blob:存放的是二进制

列的约束:
主键约束:primary key
唯一约束:unique
非空约束:not null

创建表:
1.分析实体:学生
2.学生ID
3.姓名
4.性别
5.年龄

create table student(
sid int primary key,
sname varchar(31),
sex int,
age int
);

查看表

1
2
3
4
5
6
7
8
-- 查看所有的表
show tables;

-- 查看表的创建过程
show create table student;

-- 查看表的结构
desc student;

修改表

添加列(add),修改列(modify),修改列名(change),删除列(drop),修改表名(rename),修改表的字符集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 添加列(add)
alter table 表名 add 列名 列的类型 列的约束
alter table student add score int not null;

-- 修改列(modify)
alter table student modify sex varchar(2);

-- 修改列名(change)
alter table student change sex gender varchar(2);

-- 下面两种比较少用

-- 修改表名(rename)
rename table student to s;

-- 修改表的字符集
alter table s character set gbk;

删除表

1
drop table s;

Sql完成对表中数据的CRUD操作

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
insert into 表名(列名1,列名2,列名3) values(值1,值2,值3);
insert into student(sid,sname,sex,age)valuse(1,'zhangsan',1,23);

-- 简单写法
insert into student values(2,'zhangsan',1,23);

-- 注意:如果是插入部分的话,列名不能省略
insert into student(sid,sname)values(3,'lisi');
insert into student values(3,'lisi'); //这种写法错误

-- 批量插入
insert into student values
(4,'zhangsan',1,23),
(5,'zhangsan',1,23),
(6,'zhangsan',1,23),
(7,'zhangsan',1,23),
(8,'zhangsan',1,23);

-- 单条插入和批量插入的效率
批量插入效率高,设计关键字少
但是批量插入一条出错,全部都会受到影响。

-- 查看表中数据
select * from student;

命令行下插入中文的问题:insert into student values(11,’李四’,1,24);

  • 临时解决方案:set name gbk; 相当于是告诉mysql服务器软件,我们当前在命令行下输入的内容是GBK编码,当命令窗口关闭之后,它再输入中文就会存在问题

  • 永久解决办法:修改my.ini配置(在mysql软件的安装路径里)

    • 暂停mysql的服务
    • 在mysql安装路径中找到my.ini配置文件
    • 将57(具体版本自行寻找)行的编码改成gbk
    • 保存文件退出
    • 启动mysql服务

    删除记录

1
2
3
4
5
6
7
8
9
10
11
delete from 表名 [where 条件]

delete from student where sid=10;
delete from student; //如果没有指定条件,会将表中数据一条一条的全部删除掉

-- 面试问题:请说一下 delete 删除数据和 truncate 删除数据有什么区别
delete:DML 一条一条删除表中数据
truncate:DDL 先删除表再重建表
关于哪条执行效率高:具体看表中的数据量
如果数据比较少,delete比较高效
如果是数据比较多,truncate比较高效

更新表记录

1
2
3
4
5
6
7
update 表名 set 列名=列的值,列名2=列的值2 [where 条件]

-- 将sid为5的名字改成李四
-- 如果参数是字符串,日期要加上单引号
update student set sname='李四' where sid=5;

update student set sname='李四',sex=0; //全部改为

查询记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
select [distinct] [ * ] [列名,列名2] from 表名 [where 条件]
distinct:去除重复的数据
select:选择显示那些列的内容

-- 商品分类:手机数码,鞋靴箱包
1.分类的ID
2。分类的名称
3.分类的描述

create table category(
cid int primaty key auto_increment,
cname varchar(10),
cdesc varchar(31)
);

insert into category values(null,'手机数码','电子产品');
insert into category values(null,'鞋靴箱包','江南皮革厂打造');
insert into category values(null,'香烟酒水','中华,茅台,二锅头');
insert into category values(null,'酸奶饼干','哇哈哈,蒙牛酸酸乳');
insert into category values(null,'馋嘴零食','瓜子花生,八宝粥,辣条');

select * from category;
select canme,cdesc from category;

-- 所有商品
1.商品的ID
2.商品的名称
3.商品的价格
4.生产日期
5.商品分类ID

商品和商品的分类:所属关系
create table product(
pid int primary key auto_increment,
pname varchar(10),
price double,
pdate timestamp,
cno int
);

insert into product values(null,'小米mix4',998,null,1);
insert into product values(null,'锤子',2888,null,1);
insert into product values(null,'阿迪王',99,null,2);
insert into product values(null,'老村长',88,null,3);
insert into product values(null,'劲酒',35,null,3);
insert into product values(null,'小熊饼干',1,null,4);
insert into product values(null,'卫龙辣条',1,null,5);
insert into product values(null,'旺旺大饼',1,null,5);

-- 简单查询
-- 查询所有的商品
select * from product;

-- 查询商品名称和商品价格
select panme,price from product;

-- 别名查询,as的关键字,as关键字可以省略
-- 表别名:select p.pname, p.price from product p;(主要用于多表查询)
select p.pname,p.price from product as p;

-- 列别名:select pname as 商品名称,price as 商品价格 from product;
省略as关键字
select pname 商品名称,price 商品价格 from product;

-- 去掉重复的值
-- 查询商品所有的价格
select price from product;
select distinct price from product;

-- select运算查询:仅仅在查询结果上做运算 + - * /
select *,price*1.5 from product;
select *,price*1.5 as 折后价 from product;

-- 条件查询[where关键字]
指定条件,去顶要操作的记录

-- 查询商品价格>60元的所有商品信息
select * from product where price > 60;

-- where后的条件写法
-- 关系运算符:> >= < <= = != <>
<>:不等于:标准SQL写法
!=:不等于:非标准SQL写法

-- 查询商品价格不等于88的所有商品
select * from product where price <> 88;
select * from product where price != 88;

-- 查询商品价格在10到100之间
select * from product where price > 10 and peice < 100;

between ...and...

select * from product where price between 10 and 100;

-- 逻辑运算:and,or,not

-- 查询出商品价格,小于35或者商品价格大于900
select * from product where price < 35 or price > 900;

-- like:模糊查询
_:代表的是一个字符
%:代表的是多个字符

-- 查询出名字中带有饼的所有商品 '%饼%'
select * from product where pname like '%饼%';

-- 查询第二个名字中是熊的所有商品
select * from product where pname like '_熊%';

-- in在某个范围中获得值

-- 查询出商品分类ID在 1,4,5里面的所有商品
select * from product where cno in (1,4,5);

-- 排序查询:order by 关键字
asc:ascend 升序
desc:descend 降序

-- 0.查询所有商品,按价格进行排序
select * from product order by price;

-- 1.查询所有的商品,按价格进行降序排序(asc-升序 desc-降序);
select * from peoduct order by price desc;

-- 2.查询名称有 老 的商品,安价格降序排序
1.查询名称有 小 的商品
select * from product where pname like '%小%';
2.进行排序得出结果
select * from product where pname like '%小%' order by price asc;

--聚合函数:
sum():求和
avg():求平均值
count():统计数量
max():最大值
min():最小值

-- 1.获得所有商品价格的总和:
select sum(price) from product;

-- 2.获得所有商品的平均价格:
select avg(price) from product;

-- 3.获得所有商品的个数:
select count(*) from product;

-- 注意:where 条件后面不能加聚合函数

-- 查出商品价格大于平均价格的所有商品
查出所有的商品
select * from product;
大于
平均价格
select avg(price) from product;
-- 子查询
select * from product where price > (select avg(price) from product);

-- 分组:group by

-- 1.根据cno字段分组,分组后统计商品个数
select cno,count(*) from product group by cno;

-- 2.根据cno分组,分组统计每组商品的平均价格 并且商品平均价格 > 60
select cno,avg(price) from product group by cno having avg(price) > 60;

-- having 关键字 可以接聚合函数 出现在分组之后
-- where 关键字 它是不可以接聚合函数 出现在分组之前

-- 编写顺序
-- S..F..W..G..H..O
select .. from .. where .. group by .. having .. order by

-- 执行顺序
F..W..G..H..S..O
from .. where .. group by .. having .. select .. order by