文章目录
前言
数据库基础详解
提示:以下是本篇文章正文内容,下面案例可供参考
1、SQL语言
结构化查询语言(Structured Query Language)其实就是定了操作所有关系型数据库的规则。
规则:
-
SQL语句可以单行或多行书写,以分号结尾。
-
MySQL不区分大小写
-
三种注释
- 单行注释:
-- 注释内容
或#注释内容
- 多行注释:
/* 注释内容 */
- 单行注释:
1.1、SQL分类
- DDL(Data Defination Language数据定义语言):操作数据库、表
- DQL(Data Query Language数据查询语言):查询表中数据
- DML(Data Manipulation Language数据操作操作):增删改表中的数据
- DCL(Data Control Language数据控制语言):授权
2、详细内容
2.1、DDL:操作数据库、表
-
操作数据库:CRUD
-
C(Create):创建
-
创建数据库
create database 数据库名称;
-
创建数据库,判断不存在,再创建
create database if not exists 数据库名称;
-
创建数据库,判断不存在,并指定字符集名称
create database if not exists 数据库名称 character set utf8;
-
-
R(Retrieve):查询
-
查询所有数据库的名称
show database;
-
查询某个数据库的字符集:查询某个数据库的创建语句
show create database 数据库名称;
-
-
U(Update):修改
-
修改数据库字符集
alter database 数据库名称 character set 字符集名称;
-
-
D(Drop):删除
-
删除数据库
drop database if exists 数据库名称;
-
-
使用数据库
-
查询当前正在使用的数据库名称
select database();
use 数据库名称;
-
-
-
操作表
-
C(Create):创建
-
语法
create table if not exists 表名( 列名1 数据类型1, 列名2 数据类型2, 列名3 数据类型3, ...... 列名n 数据类型n);
-
数据库类型
-
int
:整数类型- age, int
-
double
:小数类型- score double(5, 2)
-
date
:日期,只包含年月日, yyyy-MM-dd -
datetime
:日期,含年月日时分秒,yyyy-MM-dd HH:mm:ss -
timestamp
:时间戳类型,包含年月日时分秒,yyyy-MM-dd HH:mm:ss如果没有给这个字段赋值,或赋值为null,则默认使用当前的系统时间来自动赋值。
-
varchar
:字符串类型- name varchar(20)
-
-
-
R(Retrieve):查询
-
查询某个数据库中所有表的名称
show tables;
-
查询表的结构
desc 表名;
-
查询表中数据
select * from 表名;select 列名 from 表名;
-
-
U(Update):修改
-
修改表名
alter table 表名 rename to 新的表名;
-
修改表的字符集
alter table 表名 character set 字符集名称;
-
添加一列
alter table 表名 add 列名 数据类型;
-
修改列的名称 类型
alter table 表名 change 列名 新的名称 新的数据类型;
-
删除列
alter table 表名 drop 列名;
-
-
D(Drop):删除
drop table if exists 表名;
-
复制表
create table stu like student;
-
2.2、DML: 增删改表中数据
-
添加数据
insert into 表名(列名1,列名2,...) values(值1,值2,...);
列名和值要一一对应。
#给所有列添加数据insert into 表名 values(1, 2, 3);
除了数字类型,其他类型需要使用单双引号引起来。
-
删除数据
delete from 表名 [where 条件];
如果不加条件,则删除表中所有记录。
truncate table 表名;
删除表,然后创建一个一模一样的空表。
-
修改数据
update 表名 set 列名1 = 值1, 列名2 = 值2,....
update 表名 set 列名 = 值 [where 条件];
2.3、DQL:查询语句
-
排序查询
order by 排序字段1 排序方式1, 排序字段2 排序方式2, ...#只有当第一个条件相同时才会使用第二个条件
- 排序方式:
ASC
:升序,默认。DESC
:降序。
- 排序方式:
-
聚合函数:将一列数据作为一个整体,进行纵向计算。
-
count
:计算个数- 一般选择非空的列:主键
count(*)
:选择所有非空
SELECT count(title) FROM poems_by_type;#排除null #如果是空,就记为0算入 SELECT COUNT(IFNULL(type,0)) FROM poem_by_writer_name; #只要这一行有非空,就算入 SELECT COUNT(*) FROM poem_by_writer_name;
-
max
:计算最大值SELECT max(id) FROM poems_by_type;
-
min
:计算最小值SELECT min(id) FROM poems_by_type;
-
sum
:计算和SELECT sum(id) FROM poems_by_type;
-
avg
:计算平均值SELECT avg(id) FROM poems_by_type;
注意:聚合函数计算排除null值。
-
-
分组查询
- 语法:
group by 分组字段;
- 注意:分组之后查询的字段:分组字段,聚合函数
#按照朝代查询人数 SELECT dynasty, COUNT(writer_name) from poems_by_type GROUP BY dynasty; #按照朝代查询id>=10000的人数,id<10000的不参与分组 SELECT dynasty, COUNT(writer_name) from poems_by_type WHERE id >= 10000 GROUP BY dynasty; #按照朝代查询人数大于10 SELECT dynasty, COUNT(writer_name) from poems_by_type GROUP BY dynasty HAVING COUNT(writer_name) > 10;
where
和having
的区别:where
在分组之前进行限定,如果不满足,则不参与分组。having
在分组之后进行限定,如果不满足,则不会被查询出来where
后不可以跟聚合函数,having
可以进行聚合函数判断
- 语法:
-
分页查询
#每页显示3条记录SELECT * FROM poems_by_type LIMIT 0, 3;
公式:开始的索引 = (当前的页码 - 1)* 每页显示的条数
-
基础查询
-
语法
select 字段列表from 表名列表where 条件列表group by 分组字段having 分组后的条件order by 排序limit 分页限定
-
查询多个字段
SELECT name, -- 姓名 age -- 年龄FROM student;-- 学生表
-
去除重复
SELECT DISTINCT mathFROM student;
-
计算列(四则运算)
SELECT name, IFNULL(math,0) + IFNULL(english,0)as 总分 FROM student;
-
起别名(as 可以省略)
SELECT name 名字, math 数学, english 英语, IFNULL(math,0) + IFNULL(english,0)as 总分 FROM student;
ifnull(表达式1,表达式2)
:null参与运算,计算结果都为null表达式1
:填哪个字段需要判断是否为null表达式2
:填如果为null后的替换值 -
-
条件查询
-
where
字句后跟条件 -
运算符
and
&&
or
||
not
!
-
> \ < \ >= \ <= \ = \ != \ <>
SELECT * FROM student WHERE math > 70; SELECT * FROM student WHERE math = 90; SELECT * FROM student WHERE math != 90; SELECT * FROM student WHERE math <> 90;#不等于
-
between...and...
SELECT * FROM student WHERE math >= 80 and math <= 90; SELECT * FROM student WHERE math BETWEEN 20 and 90;
-
in(集合)
SELECT * FROM student WHERE english=80 or english=100;SELECT * FROM student WHERE english in (80, 100);
-
is null
:判断null值SELECT * FROM student WHERE english is null;SELECT * FROM student WHERE english is not null;
-
like
:模糊查询- 占位符
_
:占任意一个字符%
:多个任意字符
SELECT * FROM student WHERE name like 'a%';#查询名字第一个字母a的SELECT * FROM student WHERE name like '_a%';#查询名字第二个字母是aSELECT * FROM student WHERE name like '___';#查询名字是三个字的人SELECT * FROM student WHERE name like '%a%';#查询名字含a的人
- 占位符
-
-
2.4、DCL:管理用户及授权
作用:管理用户,授权。
管理用户:
-
添加用户
-- 创建用户create `user` '用户名'@'主机名' IDENTIFIED by '密码';CREATE user 'zhangsan'@'localhost' IDENTIFIED by '123';CREATE user 'lisi'@'%' IDENTIFIED by '123';
-
删除用户
-- 删除用户drop user '用户名'@'主机名';
-
修改用户密码
-- 修改密码-- 5.7版本前UPDATE user set password = password('新密码') where user='用户名';UPDATE user set password = password('abc') where user='lisi';-- 5.7版本后alter user '用户名'@'主机名' IDENTIFIED with mysql_native_password by 'x';alter user 'lisi'@'%' IDENTIFIED with mysql_native_password by 'abc';
-
查询用户
-- 1.切换到mysql数据库 use mysql; -- 2.查询user表 SELECT * FROM user;
通配符
%
表示可以在任意主机使用用户登录数据库。
3、其他
3.1、约束
概念:对表中数据进行限定,保证数据的正确性、有效性、完整性。
分类:
-
主键约束:
primary key
-
注意
- 含义:非空且唯一
- 一张表只能有一个字段为主键
- 主键是表中的唯一标识
-
创建表时添加唯一约束
CREATE TABLE stu( id int PRIMARY key, pn VARCHAR(20));
-
在创建表后,添加主键
ALTER TABLE stu MODIFY id int PRIMARY KEY;
-
删除主键
alter table stu drop primary key;
-
自动增长
概念:如果某一列是数值类型的,使用auto_increment可以完成自动增长。
CREATE TABLE stu( id int PRIMARY key auto_increment, pn VARCHAR(20));
- 删除自动增长
ALTER table stu MODIFY id int;
- 添加自动增长
alter table stu modify id int auto_increment;
-
-
非空约束:
not null
-
创建表时添加非空约束
CREATE TABLE stu( id int, name VARCHAR(200) not null -- name为非空);
-
创建表后,添加非空约束
ALTER TABLE stu MODIFY name VARCHAR(200) not null;
-
删除非空约束
ALTER TABLE stu MODIFY name VARCHAR(200);
-
-
唯一约束:
unique
-
创建表时添加唯一约束
CREATE TABLE stu( id int, pn VARCHAR(20) UNIQUE -- 添加唯一约束);
-
在创建表后,添加唯一约束
ALTER TABLE stu MODIFY pn VARCHAR(20) UNIQUE;
-
删除唯一约束
ALTER TABLE stu DROP INDEX pn;
-
-
外键约束:
foreign key
-
在创建表时,添加外键
create table 表名( ...... 外键列, constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称));
CREATE table if not exists department( id int PRIMARY KEY auto_increment, name VARCHAR(255), location VARCHAR(255));CREATE table if not exists employees( id int PRIMARY KEY auto_increment, name VARCHAR(25) not null, age int, dep_id int, CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id));
-
删除外键
alter table employees drop foreign key emp_dep_fk;
-
创建表后,添加外键
alter table employees add CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id);
-
添加外键,级联更新
alter table employee add constraint emp_dep_fk foreign key (dep_id) references department(id) on update cascade;
-
添加外键,级联删除
-
3.2、表的分类
-
一对一:
- 如:人和身份证
- 分析:一个人只有一个身份证,一个身份证对应一个人
-
一对多(多对一):
- 如:部门和员工
- 分析:一个部门有多个员工,一个员工只对应一个部门
-
多对多:
- 如:学生课程
- 分析:一个学生可以选择多门课程,一门课程也可以被很多同学选择
实现关系
- 一对多(多对一)
- 如:部门和员工
- 分析:一个部门有多个员工,一个员工只对应一个部门
- 实现方式:在多的一方建立外键,指向一的一方的主键
- 多对多
- 如:学生课程
- 实现方式:多对多关系实现需要借助第三张中间表。中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键。