基本操作命令,库、表、数据
数据库概念
数据库(DataBase,DB):指长期保存在计算机的存储设备上,按照一定规则组织起来,可以被各种用户或应用共享的数据集合。
数据库管理系统(DataBase Management System,DBMS):指一种操作和管理数据库的大型软件,用于建立、使用和维护数据库,对数据库进行统一管理和控制,以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。
RDBMS
数据库:存储、维护和管理数据的集合。
常见的数据库分为:
关系型数据库: Oracle、MySQL、SQLServer、Access
非关系型数据库: MongoDB、Redis、Solr、ElasticSearch、Hive、HBase
我们这里学习MySQL
MySQL介绍
MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。
SQL语句
定义
结构化查询语言(Structured Query Language)简称SQL(发音:/ˈes kjuː ˈel/ “S-Q-L”),是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件的扩展名。
分类
- DML(Data Manipulation Language)数据操纵语言如:insert,delete,update,select(插入、删除、修改、检索)简称CRUD操新增Create、查询Retrieve、修改Update、删除Delete
- DDL(Data Definition Language)数据库定义语言如:create table之类
- DCL(Data Control Language)数据库控制语言如:grant、deny、revoke等,只有管理员才有相应的权限
- DQL(Data Query Language)数据库查询语言如: select 语法
注意:SQL不区分大小写
常用操作
DDL主要对库和表的操作
库
-- 创建库
create database 库名;
-- 查看所有库
show databases;
-- 修改库的字符编码集,修改完数据库字符集,需要重启mysql数据库
alter database 数据库名 character set utf8;
-- 删除库
drop database 数据库名;
-- 使用数据库
use 数据库名;
表
常用数据类型
数据类型 | 解释 | 长度 |
---|---|---|
int | 整型 | 11,可以不写,默认11 |
varchar | 字符串 | 0-255 |
char | 字符串 | 0-255 |
double | 浮点型 | (5,2)总长5位,其中包含两位小数,999.99 |
date | 日期 | 没有长度 |
datetime | 日期时间 | 没有长度 |
timestamp | 时间戳 | 没有长度 |
数据库中,字符串不分单字符,所有字符串都是单引号括.使用char和varchar都可以代表字符串
varchar和char的区别
var是variable的缩写,就是可变化的字符串,例如varchar(10), 存入’张三’
char是定长,例如char(10),存入’张三 ’
-- 创建表
create table 表名(
字段1 数据类型(长度),
字段2 数据类型(长度),
...
);
-- 查看当前在那个库
select database();
-- 查看所有表
show tables;
-- 查看表结构
desc 表名;
-- 修改表名
rename table 旧表名 to 新表名;
-- 修改表中的某一列
alter table 表名 change 旧列 新列 数据类型(长度);
-- 添加一列
alter table 表名 add 列名 数据类型(长度);
-- 删除列
alter table 表名 drop 列名;
-- 删除表
drop table 表名;
DML主要是增、删、改
-- 增
insert into 表名(字段1,字段2,..)
values (值1,值2,...); -- 相应字段添加相应的值
insert into 表名 values (值1,值2,...); -- 直接对应字段添加
-- 删
delete
from 表名
where 字段 = 值; --条件
-- 改
update 表名
set 字段 = 值 -- 要修改的值
where 字段 = 值; -- 条件
DQL主要用于查询
-- 书写顺序
select 字段1,字段2,...
from 表名
where 字段 = 值 --条件
group by 字段 -- 分组
having -- 用于分组的条件
order by asc|desc; --排序,asc升序,desc降序
limit n; -- limit n或者limit x,y;
约束
对数据表中的数据进行限制的一种设置。
语法格式
create table 表名(
字段1 数据类型(长度) 约束,
字段2 数据类型(长度) 约束,
...
);
-- 对某个列进行约束
主键约束
最重要的约束,一般每个表都要至少一个主键.一般主键都是id列
使用primary key
来定义主键
create table emp(
id int primary key, -- 定义主键
name varchar(20),
hiredate date,
sal double(10,2)
);
自增约束
主键约束后加auto_increment
create table emp3(
id int primary key auto_increment, -- 主键自增
name varchar(20),
hiredate date,
sal double(10,2)
);
/*
1 当没值时,直接自增,从1开始
2 依次递增1
3 虽然设置递增,也可以自己再随意设置主键值,但是后续自增会按照当前值继续递增
4 执行delete删除数据,不会影响递增顺序
*/
非空约束
限制该列的值不能为空.
create table emp4(
id int primary key auto_increment, -- 主键自增
name varchar(20) not null, -- 不为空
hiredate date,
sal double(10,2)
);
-- 限制name列插入值时,不能为空
默认值
当给该列设置值时,没有值时就会使用默认值填充
create table emp5(
id int primary key auto_increment, -- 主键自增
name varchar(20) not null, -- 不为空
hiredate date,
sal double(10,2) default 3000.0 -- 默认值
);
-- 没有值时就会使用默认值填充,有值是不用默认值
唯一约束
唯一约束,限制该列的值不能重复,但是可以为空
create table emp6(
id int primary key auto_increment, -- 主键自增
name varchar(20) unique, -- 唯一
hiredate date,
sal double(10,2) default 3000.0 -- 默认值
);
-- 该值不能重复
-- 但是可以给null,且允许多个null
insert into emp6 (name) values('安琪拉');
insert into emp6 (name) values(null);
外键约束
外键约束必须是主键
-
先创建一个老师表
-- 老师表 CREATE TABLE `teacher` ( `tid` int(11) primary key, `tname` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
创建学生表加约束
-- 学生表 CREATE TABLE `stu3` ( `sid` int(11) DEFAULT NULL, `sname` varchar(20) DEFAULT NULL, `tid` int(11) DEFAULT NULL, -- 关联老师id constraint fk_stu_tea foreign key(tid) references teacher(tid) -- 约束 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
也可以使用alter命令来添加约束
alter table stu3 add constraint fk_stu_tea foreign key(tid) references teacher(tid);
小案例
准备数据表
-- 创建class表
CREATE TABLE `class` (
`cid` int(11) DEFAULT NULL,
`cname` varchar(255) COLLATE utf8_bin NOT NULL,
`caddress` varchar(255) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- 创建stu表
CREATE TABLE `stu` (
`sid` int(11) DEFAULT NULL,
`sname` varchar(25) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` char(6) DEFAULT NULL,
`score` int(11) DEFAULT NULL,
`cid` int(11) DEFAULT NULL,
`groupLeaderId` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DQL的基本使用
基本查询
-- 查询所有列
SELECT * FROM stu;
-- 查询指定列
SELECT sid,sname FROM stu;
条件查询
-- 查询学号为1001的学生信息
select * from stu where sid = 1001;
-- 查询学生成绩大于60的学生id 姓名,成绩
SELECT sid,sname,score FROM stu WHERE score > 60;
-- 查询学生性别为女,并且年龄小于50的记录
SELECT * from stu where sex = '女' AND age < 50;
-- 查询学生学号为1001,或者姓名为李四的记录
select * from stu where sid = 1001 or sname = '李四';
-- 查询学号为1001,1002,1003的记录
select * from stu where sid in (1001,1002,1003);
select * from stu where sid BETWEEN 1001 AND 1003;
-- 查询学号不是1001,1002,1003的记录
select * from stu where sid not in (1001,1002,1003);
-- 查询学生年龄在20到40之间的学生记录
select * from stu where age between 20 and 40;
-- 查询性别非男的学生记录
select * from stu where sex != '男';
模糊查询
/*
%:可以代表多个字
_:代表一个
*/
-- 查询姓名以“张”开头的学生记录
SELECT * from stu WHERE sname like '张%';
select * from stu where sname like '张_';
-- 查询姓名中包含“三”的学生记录
SELECT * FROM stu WHERE sname like '%三%';
排序查询
/*
asc:升序,默认为升序
desc:降序
*/
-- 查询所有学生记录,按年龄升序排序
select * from stu ORDER BY age asc;
select * from stu ORDER BY age;
-- 查询所有学生记录,按年龄降序排序
select * from stu order by age desc;
-- 查询所有学生记录,按年龄升序排序,如果年龄相同时,按编号降序排序
select * from stu order by age,sid desc;
-- 查询成绩大于60的学生id,姓名,成绩,并根据成绩降序
select sid,sname,score from stu where score > 60 order by score desc;
聚合函数
-- 查询stu表中记录数:
SELECT count(sid) FROM stu;
-- 查询stu表中有成绩的人数:
select count(score) from stu;
-- 查询stu表中成绩大于60的人数:
select count(score) from stu where score > 60;
-- 查询所有学生成绩和:
select sum(score) from stu;
-- 统计所有学生平均成绩
select avg(score) from stu;
select avg(score) as 平均分 from stu;
--as可以省略,代表别名
select sum(score)/count(sid) from stu;
-- 查询最高成绩和最低成绩:
select max(score) as 最高分,min(score) 最低分 from stu;
去重查询
-- 查询年龄不重复的共有多少人
select count(distinct age) from stu;
分组查询
-- 查询男生多少人,女生多少人
select sex,count(sex) from stu group by sex;
-- 查询每个班级的班级编号和每个班级的成绩和:
select cid,sum(score) from stu group by cid;
-- 查询每个班级的班级编号以及每个班级的人数:
select cid,count(cid) from stu group by cid;
-- 查询成绩总和大于200的班级编号以及成绩和:
select cid,sum(score) from stu group by cid having sum(score) > 200;
-- 查询成绩总和大于200的班级编号以及成绩和并根据成绩总和降序
select cid,sum(score) from stu group by cid having sum(score) > 200 order by sum(score) desc;
分页(limit)查询
/*
limit n;展示n条数据
limit x,y;展示从x索引开始y条数据
*/
select cid,sum(score)
from stu
group by cid
having sum(score) > 200
order by sum(score) desc
limit 1;
顺序
书写顺序
-- 书写顺序
select 字段1,字段2,...
from 表名
where 字段 = 值 --条件
group by 字段 -- 分组
having -- 用于分组的条件
order by asc|desc; --排序,asc升序,desc降序
limit n; -- limit n或者limit x,y;
执行顺序
from
where
group by
having
select
order by
limit