【MySQL】数据库概述与SQL语句

一、数据库基本概念

数据库：DateBase，简称：DB

数据库特点：

1. 持久化存储数据 -> 文件系统
2. 方便存储和管理数据
3. 使用了统一方式管理数据库 -> SQL

常见的数据库软件：

1. Oracle
2. MySQL
3. Microsoft SQL Server
4. DB2
5. SQLite

二、MySQL

默认端口号：3306

配置环境变量(Mac)：="$PATH":/usr/local/mysql/bin

本地登录

mysql -uroot -p密码

指定IP登陆

mysql -hIP地址 -uroot -p密码

登出

exit
quit

MySQL目录结构

1. 安装目录
   1. bin：*.exe
   2. my.ini：MySQL的配置文件
2. 数据目录：数据库(文件夹) -> 表(文件) -> 数据

三、SQL语句

1、基本概念

SQL：结构化查询语言 -> 定义了操作所有关系型数据库的规则

方言：每种数据库操作的方式存在差异

2、SQL语法

SQL语句能单行或多行书写，以分号结尾

不区分大小写，关键字建议大写

注释：

1. -- 注释内容(必须加空格)
2. #注释内容：MySQL特有
3. /*注释内容*/

1）DDL：操作数据库和表

1. 操作数据库：CRUD

Create：创建

-- 创建数据库
create database 数据库名称;
-- 若不存在，创建数据库
create database if not exists 数据库名称;
-- 创建数据库并指定gbk编码
create database 数据库名称 character set gbk;

Retrieve：查询

-- 1.查询所有数据库的名称
show databases;
-- 2.查询某个数据库的创建语句(字符集)
show create database 数据库名称;

Update：修改

-- 修改数据库的字符集
alter database 数据库名称 character set 字符集名称;
alter database db character set utf8;

Delete：删除

-- 删除数据库
drop database 数据库名称;
-- 判断存在再删除数据库
drop database if exists 数据库名称;

2. 使用数据库

-- 1.查询当前正在使用的数据库名称;
select database();
-- 2.使用数据库
use 数据库名称;

3. 操作表

Create：创建

-- 创建表	最后一个不写逗号
create table student(
	id int,
    name varchar(32),
    age int,
    score double(4,1),
    birthday date,
    insert_time timestamp
);

Retrieve：查询

-- 1.查询数据库下表的名称
show 数据库名称;
-- 2.查询表结构
desc 表名;

Update：修改

-- 1.修改表名
alter table 表名 rename to 新的表名;
-- 2.修改表的字符集
alter table 表名 character set 字符集名;
-- 3.添加一列
alter table 表名 add 列名 数据类型;
-- 4.修改列名称 类型
alter table 表名 change 列名 新列名 新数据类型;
-- 5.删除列
alter table 表名 drop 列名;

Delete：删除

drop table 表名;
drop table if exists 表名

4. SQL的数据类型

1. int：整数类型
2. double(最大位数, 小数点后位数)：小数类型
3. date：日期类型（yyyy-MM-dd）
4. datetime： 日期时间类型（yyyy-MM-dd HH:mm:ss）
5. timestamp：时间戳类型（yyyy-MM-dd HH:mm:ss）（不给赋值会自动用系统时间）
6. varchar(最大字符数)：字符串

2）DML：数据的增删改

1. 添加数据

insert into 表名(列名1,列名2,...列名n) values (值1,值2,...值n);

列名要和值一一对应
可以不写列名进行完全添加

insert into 表名 value (值1,值2,...值n);

除了数字类型，均需要引号

2. 删除数据

delete from 表名 [where 条件];

删除所有元素

delete from 表名;			-- 删除表的所有数据
truncate table 表名;		-- 删除表，并创建新表

3. 修改数据

update 表名 set 列名1 = 值1,列名2 = 值2,...[where 条件];

不加条件会改所有行的数据

3）DQL：数据的查询

1. 基础查询

多个字段的查询

select 字段名1,字段名2... from 表名;
select * from 表名;

去除重复distinct

select distinct 字段名 from 表名;

计算列

1. 一般使用四则运算计算列的数值
2. ifnull(表达式1, 表达式2)
3. 表达式1：可能出现null的列的字段名
4. 表达式2：null的替换值

select name,math,chinese,math + chinese from stu;
select name,math,chinese,ifnull(math + chinese) from stu;

起别名as

select name as 姓名 from stu;
select name 姓名 from stu;   -- 可以省略

2. 条件查询

where子句后跟条件

运算符

####### < > <= >= = != < >

select * from stu where age > 20;	-- 大于
select * from stu where age = 20;	-- 等于
select * from stu where age != 20;	-- 不等于
select * from stu where age <> 20;	-- 不等于

####### && (and) || (or) ! (not)

select * from stu where age >= 20 && age <= 30;
select * from stu where age >= 20 and age <= 30;

####### between ... and ...

select * from stu where age between 20 and 30;

####### in(集合)

select * from stu where age = 22 || age = 18 or age = 19;
select * from stu where age in (22,18,19);

####### is null

null值不能使用=判断

select * from stu where math is null;
select * from stu where math is not null;

模糊查询：like

占位符

• _：单个任意字符
• %：多个任意字符

select * from stu where name like '王%';
select * from stu where name like '_铭%';

3. 排序查询

select * from stu order by 排序字段1 排序方式1,排序字段2 排序方式2 ...;

排序方式：

1. ASC：升序，默认的
2. DESC：降序

4. 聚合函数

将一列作为整体进行纵向计算

计算个数：count

1. 选非空的列：主键
2. count(*)

select count(name) from student;

计算最大值：max

select max(math) from student;

计算最小值：min

select min(math) from student;

计算求和：sum

select sum(math) from student;

计算平均值：avg

select avg(math) from student;

ifnull

聚合函数会排除null值

1. 选不包含null的列
2. ifnull函数

select count(ifnull(name,0)) from student;

计算结果特点：单行单列

5. 分组查询

group by + 分组字段

分组之后查询的字段：分组字段、聚合函数

where和having的区别

限定时间

1. where在分组前限定，不满足条件不参与分组
2. having在分组后限定，不满足条件不被查询

聚合函数

1. where后面能加聚合函数
2. having后面不能加聚合函数

select sex,avg(math),count(id) from stu group by sex;
select sex,avg(math),count(id) from stu where math >= 70 group by sex;
select sex,avg(math),count(id) from stu where math >= 70 group by sex having count(id) > 2;
select sex,avg(math),count(id) 人数 from stu where math >= 70 group by sex having 人数 > 2;

6. 分页查询

语法： limit 开始的索引, 每页的条数

select * from stu limit 0,3;

开始的索引 = (当前的页码 - 1) * 每页显示的页数

limit是MySQL的"方言"

3、约束

对表的数据进行限定，保证数据的正确性、有效性和完整性

1）非空约束

非空约束not null：值不为null

1. 创建表时添加约束

create table stu(
	id int,
    name varchar(20) not null
);

2. 删除表的约束

alter table stu modify name vachar(20);

3. 后期添加约束

alter table stu modify name vachar(20) not null;

2）唯一约束

1. 唯一约束unique：值不重复
2. 能保存null值，但是只能保存一个

1. 创建表时添加约束

create table stu(
	id int,
    phone varchar(20) unique
);

2. 删除表的约束

alter table stu drop index phone;

3. 后期添加约束

只有在没有重复数据才能添加成功

alter table stu modify phone vachar(20) unique;

3）主键约束

主键约束primary key：非空且唯一

一张表只能有一个主键，是表中记录的唯一标识

1. 创建表时添加约束

create table stu(
	id int primary key,
    phone varchar(20)
);

2. 删除表的约束

alter table stu drop primary key;

3. 后期添加约束

只有在没有重复数据才能添加成功

alter table stu modify id int primary key;

4）自动增长auto_increment

1. 创建表时添加约束

create table stu(
	id int primary key auto_increment,
   phone varchar(20)
);

2. 添加数据

insert into stu value(null,'wmh');

3. 删除自动增长(不影响主键)

alter table stu modify id int;

5）外键约束

外键约束forign key：让表于表产生关系，从而保证数据的正确性

1. 在创建表时，可以添加外键

create table 表名(
	...
    外键列
    constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);

2. 删除外键

alter table 表名 drop foreign key 外键名称;

3. 创建表之后，添加外键

alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称);

4. 级联操作

添加级联操作

alter table 表名 add constraint 外键名称 foreign key (外键字段名称) on update cascade on delete cascade;

分类

级联更新：on update cascade

级联删除：on delete cascade