连接服务器

mysql -h 127.0.0.1 -P 3306 -u root -p 

-h后接的是要连接的部署了mysql的主机，127.0.0.1指的是单机访问，如果没有指令则直接连接本地
-P后接的是端口号 一般是3306
-u后接的是要登入的用户
-p指要登陆密码

如果要退出可以直接quit

mysql和mysqld

mysql是数据库服务的客户端，mysqld是数据库服务的服务器端，所以MySQL本质上是基于CS模式的一种网络服务，既然是网络服务，那我们就可以查到端口号，数据库一般指的是未来我们在磁盘或者内存中存储的有特定结构或者组织的数据，而数据库服务一般指的是mysqld

MySQL的架构

第一层：做链接安全管理
第二层：词法语法分析
第三层：用用户指定的存储引擎去操作文件

第四层：内存/磁盘

sql语句分类

DDL

数据定义语言，用来维护存储数据的结构，比如说创建表或者修改表结构

DML

数据操纵语言，用来对数据进行操纵，比如说插入一条数据，删除一条数据

DCL

数据控制语言，主要负责权限管理和事务，比如说commit

数据库的操作

查看数据库

show databases;

如果我们想看数据库存放的文件，可以通过

vim /etc/my.cnf

查看datadir可以看到自己的数据库存储的内容放在哪一个目录下

如果我们想查看自己现在处于哪个数据库里，可以使用

select database();

如果想查看数据库的属性，可以使用

show create database d1;

如果想看此时的连接情况

show processlist;

创建数据库

本质上是在/var/lib/mysql目录下创建一个目录

create database [if not exists] databasename [specification];
sepcification:character set charset_name #字符集collate collation_name #校验规则#这两个字符集都符合就近原则

在创建数据库的时候有两个编码集，一个是数据库编码集，用于数据库未来存储数据，一个是数据库校验集，支持数据库字段比较使用的编码，这两个编码必须匹配，不同的校验规则作用不一样，有的导致大小写敏感，有的导致大小写不敏感等

如果想查看相应的编码集可以用以下指令

show variables like 'character_set_database';

如果想查看校验集可以使用

show variables like 'collation_database';

如果我们想查看所有数据库支持的编码集

show charset;

如果想查看所有校验集

show collation;

create database if not exists db1 charset=utf8 collate utf8_general_ci;

可以看到文件里也同步了

使用数据库

use databasename;

删除数据库

本质上是在/var/lib/mysql删除一个目录，删除之后有两个结果，第一是数据库内部看不到对应的数据库，第二个是数据库文件夹被删除，里面的数据表也全部被删除

drop database [if exists] databasename;

drop database db1;

修改数据库

alter database d1 charset=gbk;

/*!40100 DEFAULT CHARACTER SET gbk */这句话表示MySQL版本大于4.01，就执行后面这一句

数据库备份

备份

mysqldump -P 3306 -u root -p -B databasename > databasename.sql

-B指明数据库
如果备份的时候忘记带上-B，就可以先创建一个新数据库，然后再use这个数据库，再source还原
如果只是备份其中的表

mysqldump -P 3306 -u root -p -B databasename 表1 表2 > databasename.sql

如果要同时备份多个数据库

mysqldump -P 3306 -u root -p -B databasename1 databasename2 > databasename.sql

这里面也可以看到我们过去的动作

还原

可以看到现在是没有d1 的

source /var/lib/mysql/test1.sql

这样就有d1了

表的操作

创建表

创建的表是在磁盘中以二进制存放的普通文件

create table student(file1 datatype1 comment '注释',file2 datatype2,file3 datatype3...
)character set 字符集 collate 校验规则 engine 存储引擎;

create table student (id int,name varchar(20) comment '姓名',password varchar(50) comment '密码',age int,sex varchar(1),birthday timestamp,amout decimal(13,2),resume text
)character set utf8 engine MyISAM;#也可以用等号

而且我们可以看到不同的存储引擎的文件个数是不一样的

查看表

如果我们想查看表结构

desc tablename;

也可以

show create table tablename\G

修改表

修改表名

alter table tablename1 rename to tablename2;

新增一列

alter table tablename1 add column datatype;

修改一列的属性

alter table tablename1 modify column 新属性;

是把这一列创建的所有字段全部覆盖掉

删除一列

alter table tablename1 drop column;

修改列名称

alter table tablename1 change columnold columnnew 新定义;

数据类型

范围问题
float会自动四舍五入

这边创建一个表

关于tinyint有符号

关于tinyint无符号

如果我们插入的数据范围超过了指定的类型，mysql会直接阻止插入

add:如果是set和enum，可以用数字代替

如果需要找到set里的一部分，下图表示1在1，2这个集合里


也可以直接用数字

表的约束

空属性null和默认值default

一般不参与计算，包括null和not null

当在表中建立约束为not null的时候，在插入的时候这个字段必须有值，不能为空


如果设置了默认值，再执行一次刚才的插入

下图是修改后的表结构

如果我们没有指定一列要插入，默认使用的是default，但如果没有default为null，且有加not null约束，那就会报错

列描述comment

comment类似于c/c++里的注释

zerofill

可以看到int后面是有数字的

但如果加入zerofill


在插入数据的时候可以看到a和b是有区别的，zerofill的意思是，插入数据的宽度小于规定的宽度，如果多于规定的宽度则不变，mysql会自动补零进行显示，未来显示的表格是等宽的，存储的数据是不变的，只是显示有区别：

主键primary key

primary key不能为空，不能重复，一张表中只能有一个，通常是整数类型，在建表的时候自动加上not null非空约束

以下是不能为空和重复的证明

也可以这样创建

当表建好了之后可以添加主键

alter table 表名 add primary key(字段名);

也可以删除主键

alter table 表名 drop primary key;

也可以做一个复合主键，表示这几个合起来为主键，合起来不重复即可

自增长auto_increment

当对应的字段不给值会被自动触发，会选择表中字段最大的值+1，自增长字段必须有值，key列必须有值，也就是说本身必须是一个索引，一张表最多只能有一个自增长，在删除表中所有数据的时候，auto_increment不变


如果id修改了，最大值为1000，会从新的起始值开始插入

下图可以看到auto_increment是在表外的

唯一键unique key

唯一键不能重复，但可以为空，一个表中可以有多个唯一键

如果要删除唯一键，可以先用以下查找索引名称

外键foreign key

外键用于定义主表和从表的关系，主表必须有主键约束或者unique约束，在主表中的数据必须存在或者为null


不能插入主表没有的值，主表不能删除从表还在的值，比如说班级表必须保证学生表不能存在隶属于一个班级的学生后才能删除这个班级

表的增删改查

表的插入

insert into 表名 (列名) values (cloumn1,column2...);

如果要全列插入可以不写列名

也可以多行插入

插入否则更新


如果遇到主键冲突了可以变成更新操作，两行被影响表示冲突并修改，但不能和其他的主键唯一键冲突

如果不冲突是一行受影响，表示直接插入

如果插入的数据是一样的，则0行受影响

可以使用select row_count()查看有哪几行受影响

表的替换

insert into 表名 (列名) values (cloumn1,column2...);

如果不出现冲突，相当于直接插入

如果出现主键或者唯一键冲突，则删除后再插入，这里的主键是有设置auto_increment的，所以证明这是删除后重新插入的结果

两行受影响表示冲突后替换

表的查询

select
[distinct]{*|column1,column2...}
from table_name
where{...}
order by ...{asc|desc}
limit...

全列查询

但一般不会全部查询，因为数据传输量太大，也会影响索引的使用
所以可以用limit限定

也可以重命名，重命名不能用于where的筛选，但可以用于order by

如果想去重可以

where子句

null不参与比较运算

in（option1，option2…,）

select* from class where class_id in(1,2,3,4);

模糊匹配like

%表示可以匹配多个，_只能匹配一个

也可以使用not like

排序

order by用于对结果做排序，默认升序，asc表示升序，desc表示降序

也可以先对class_id做完排序后再对class_pid做排序，表示在class_id相等的情况下对class_pid做排序

如果null参与排序，它比任何值都要小

limit

limit n;取前几行
limit s n;取从s行开始的n行
limit n offset s;取从s行开始的n行

聚合函数

统计一个表中有多少个记录

统计有效数据

也可以直接去重

也可以先where在聚合函数

group by分组

在select中使用group by可以对指定列进行分组查询

select 聚合函数/分组列 as 重命名 from table where{...} group by 列1，列2，列3...;

经常配合having 配合使用,having类似于where

[select 聚合函数/分组列 as 重命名 from table where {...}] group by {列1，列2，列3..}{having...}

having是对分组后的结果进行筛选，where是对原来的表的任意行进行筛选，having也可以像where一样使用，因为一切都是表

表的更新

update tablename set column={exp...}
where{...}#如果没有where则会全列修改
order by{...}
limit{...} 

关于order by和limit
比如说我们需要找到class_id倒数三名的行，把class_pid设置为5

表的删除

```bash
delete from tablename
where{...}#如果没有where则会全列修改
order by{...}
limit{...} 

清空表

如果想删除整表数据，可以直接

截断表

无法针对性的对某数据做出删除，只能对整表使用，比delete更快，不经过事物，所以无法回滚，会重置auto_increment

truncate tablename;

但如果我们只是清空表，auto_increment是不会清0的，而truncate是会清0的
这张是delete

这张是truncate

删除表中重复记录

我们有一张这种表，需要去重，但不能直接用select，因为直接select只能查询，不能改变表内容，所以我们可以创建一张空的表，空的表要和原本的表结构一模一样，可以直接创建create，也可以直接用like语句创建，把去重后的结果插入这张表中，最后进行rename重命名

MySQL函数

日期函数

select 函数#在mysql中，select可以执行对应的函数

字符串函数

数学函数

其他函数

user()查询当前用户
md5（str）对一个字符串进行md5摘要，得到一个32位字符串
database()显示当前正在使用的数据库
ifnull(val1，val2)如果val1为空，返回val2，否则返回val1
password(） MySQL使用这个函数对用户加密