【数据库】MySQL基础篇

背景

主要内容：对MySQL基础知识进行梳理
操作平台：WSL（版本如下）

MySQL安装：略

MySQL启动及退出

启动MySQL：

sudo service mysql start

mysql -u root -p 

退出MySQL：

quit 或者 exit

创建，查看，插入，删除

注意事项：
MySQL命令末尾不要漏掉“ ; ”号

创建数据库：

create database <数据库名>;

例：
create database university;

查看数据库：

show databases;

连接数据库：

use <数据库名>;

创建数据表：

create table <数据表名>
(
列名1 数据类型(数据长度),
列名2 数据类型(数据长度),
列名3 数据类型(数据长度)
);

例：
create table student
(
id int(10),
name char(20),
phone int(12)
);

注：
1.一般SQL不区分大小写，所以“CREATE DATABASE;”和“create database;”效果一样，本文做小写处理，但建议保留字大写以跟变量区分;
2.输入命令时可以换行，换行时终端自动产生“->”符号，类似python

查看数据表：

show tables;

向数据表中插入数据：

insert into <数据表名>(列1,列2,列3) values(值1,值2,值3);

例：
insert into student
(id,name,phone)
values(01,'Jack',123);

查阅数据表：

select * from <数据表名>;	// 查看表中的所有内容

例：
select * from student;

删除数据库：

drop database <数据库名>;

例：
drop database university;

删除数据表：

drop table <数据表名>;

例：
drop table student;

修改数据表

修改表结构：

1. 重命名表：

rename table <原表名> to <新表名>;
或者 alter table <原表名> rename <新表名>;
或者 alter table <原表名> rename to <新表名>;

2. 重命名一列：

alter table <数据表名> change <原列名> <新列名> 数据类型 约束;

例：
alter table student change height weight int(4) default 110; 

注意事项：
当原列名和新列名相同时，指定新的数据类型或约束，可以修改数据类型或约束；
修改数据类型可能会导致数据丢失，要慎重使用。

3. 增加一列：

alter table <数据表名> add <列名> 数据类型 约束;

例：
alter table student add height int(4) default 170;	// int(4)指显示宽度
alter table student add height int(4) after age;	// after指定新增列在age列后面
alter table student add height int(4) first;		// first指定第一列位置

4. 删除一列：

alter  table <数据表名> drop <列名>;

例：
alter table student drop height;

5. 修改数据类型：

alter table <数据表名> modify <列名> 新数据类型;

例：
alter table student modify weight float;

修改表内容：

1. 修改表中某个值：

update <表名> set 列1=值1,列2=值2 where 条件;

例：
update student set id=02,phone=456 where name='Jack';

删除一行记录：

delete from <数据表名> where 条件;

例：
delete from student where name='Jack'; 

添加约束

添加主键：

// 定义1
create table student
(
id int(10) primary key,
name char(20),
phone int(12)
);

// 定义2
create table student
(
id int(10),
name char(20),
phone int(12),
constraint stu_pk primary key(name)	// stu_pk为自定义的主键名
);

// 定义3
create table student
(
id int(10),
name char(20),
phone int(12),
constraint stu_pk primary key(id,name)	// id和name为复合主键
);

默认值约束：

create table student
(
id int(10),
name char(20) default 'error',
phone int(12),
primary key(id)
);

唯一约束：

create table student
(
id int(10),
name char(20) default 'error',
phone int(12),
unique (phone),
primary key(id)
);

外键约束：

create table student
(
id int(10),
name char(20) default 'error',
phone int(12),
unique (phone),
primary key(id)
);

create table score
(
id int(5),
num int(5) default 0,
to_stu char(20),		// to_stu为外键，参考列为student表的name列
constraint sc_fk foreign key (to_stu) references student (name), 
primary key(id)
);

注：
由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。

非空约束：

create table student
(
id int(10) not null,
name char(20) default 'error',
phone int(12),
unique (phone),
primary key(id)
);

select语句

基本格式：

select <要查询的列名> from <数据表名> where 条件;

例：
// 基本查询
select name,phone from student;	// 查询 name 和 phone 列
select * from student;			// 查询所有列

// 数学符号
select name,phone from student where id=2;
select name from student where score_math>90 or score_physics>90;
select name from student where age>16 and age<18;	
select name from student where between 16 and 18;	// 包括16和18

// in 和 not in
select name,phone from student where name in ('Jack','Jane');	// 查询student表中姓名在括号内范围的人
select name,age,class from student where class not in ('1','2');// 查询student表中班级不在括号范围的人

// 模糊查询
select name,age,phone from student where phone like '123_5_';	// 定长通配符
select name,age,phone from student where name like 'J%';		// 不定长通配符

// 排序
select name,score from student order by score desc;		// 降序
select name,age from student order by age asc;			// 升序（默认）

// 内置函数
select max(score) as max_score,min(score) from student;			// max和min函数，as为重命名关键字
select count(name) as child_num from student where age<18;		// 计数
select sum(score_math,score_physics) as sum_score from score;	// 求和
select ave(score_math,score_physics) as ave_score from score;	// 求平均

// 子查询
select name from student group by name
having name in						// having 关键字用于对分组后的数据进行筛选
(select to_stu from score where name='Jack');

// 连接查询
select id,name,score				// 连接student表和score表
from student,score
where student.name = score.to_stu
order by id;

select id,name,score				// join on语法形式
from student join score
on student.name = score.to_stu
order by id;

其他基本操作

索引

加快查询速度

alter table <表名> add index <索引名>(列名);
或者
create index <索引名> on <表名>(列名);

例：
alter table student add index idx_id (id); // 在student表的id列上建立名为idx_id的索引
create index idx_name on student (name);   // 在student表的name列上建立名为idx_name的索引

视图

虚拟存在的表

create view 视图名(列a,列b,列c) as 列1,列2,列3 select from <数据表名>;

例：
create view v_stu (v_name,v_age,v_phone) as select name,age,phone from student;

导入，导出

导入：文件中数据 -> 表
导出：表中数据 -> 文件

数据文件导入和SQL文件导入的区别：
数据文件导入方式只包含数据，导入规则由数据库系统完成；SQL 文件导入相当于执行该文件中包含的 SQL 语句，可以实现多种操作，包括删除，更新，新增，甚至对数据库的重建。

导入：

导入导出的文件必须在指定的路径下进行，在 mysql 终端中查看路径变量：

show variables like '%secure%';

要导入数据文件，需要将该文件移动到安全路径下，即secure_file_priv 变量指定安全路径。

sudo cp -a /path/to/SQL/file.txt /var/lib/mysql-files/

load data infile '文件路径和文件名' into table <数据表名>;

例：
load data infile '/var/lib/mysql-files/SQL/file.txt' into table student;

导出：

select 列1，列2 into outfile '文件路径和文件名' from <数据表名>;

例：
select * into outfile '/var/lib/mysql-files/out.txt' from student;

备份，恢复

备份：mysqldump数据库 -> 文件
恢复：文件 -> 数据库

备份与导出的区别：
导出的文件只是保存数据库中的数据；备份则是把数据库的结构，包括数据、约束、索引、视图等全部另存为一个文件。

备份：

mysqldump -u root 数据库名>备份文件名;   	   // 备份整个数据库

mysqldump -u root 数据库名 表名>备份文件名;   // 备份整个表

例：
mysqldump -u root test1 > bak1.sql;
mysqldump -u root test1 student > bak2.sql;

注：该命令在终端执行

恢复：
方式一

source /path/to/*.sql

方式二

create database test;			// 新建空数据库test
mysql -u root test < bak.sql	// 备份到空数据库

不定期完善，欢迎指正。