MySQL从0到1基础部分必须记牢

MySQL从0到1基础

数据库 事务 的四种特性：

1、原子性（Atomicity）

2、一致性（Consistency）

3、隔离性（Isolation）

4、持久性（Durability）

简称ACID特性

数据库操作

1、显示数据库

show databases;

2、创建数据库

create database lxyker default charset utf8 collate utf8_general_ci;

lxyker表示自己要创建的数据库名称

collate utf8_general_ci：

数据库的校验规则，ci是case insensitive的缩写，意思是大小写不敏感；

相对的是cs，即case sensitive，大小写敏感；

还有一种是utf8_bin，是将字符串中的每一个字符用二进制数据存储，区分大小写。

3、使用数据库

use lxyker

4、用户管理

创建用户
create user 'cat'@'localhost' identified by '123456'; 
删除用户
drop user '用户名'@'IP地址';
修改密码
set password for '用户名'@'IP地址' = Password('新密码')

数据表操作

1、创建表

create table if not exists lxyker(
	nid int not null auto_increment primary key,
    num int null default 1
)

主键、外键写法：

create table lxyker1(
	nid int not null,
    string char(10) not null,
    primary key(nid, string)
);

create table lxyker2(
	nid int not null primary key,
    num int not null,
    constraint fk_1 foreign key (num) references lxyker1(nid)
);
-- constraint fk_1可以省略，fk_1是这个外键的名字，不能重复

2、删除表

drop table lxyker1;

3、清空表

delete from lxyker;  -- 原有的主键id不清空，新插入数据时继续递增
truncate table lxyker;  -- 新插入数据时 主键 重新从头开始

4、修改表

添加列：alter table 表名 add 列名 类型
删除列：alter table 表名 drop column 列名
修改列：
        alter table 表名 modify column 列名 类型;  -- 类型
        alter table 表名 change 原列名 新列名 类型; -- 列名，类型
  
添加主键：
        alter table 表名 add primary key(列名);
删除主键：
        alter table 表名 drop primary key;

5、数据类型

MySQL 的数据类型大致分为：数字、时间 和 字符串。

数据操作

1、增

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );
如果数据是字符型，必须使用单引号或者双引号，如："value"。

2、删

delete from lxyker  --删除所有
delete from lxyker where id＝1 and name＝'alex'

3、改

update lxyker set name ＝ 'xiaoming' where id>1

4、查

select * from 表
select * from 表 where id > 1
select nid, name, gender as gg from lxyker where id > 1
-- 用*效率低

5、其他查询操作

a、条件
    select * from 表 where id > 1 and name != 'lxyker' and num = 12;
 
    select * from 表 where id between 5 and 16;
 
    select * from 表 where id in (11,22,33)
    select * from 表 where id not in (11,22,33)
    select * from 表 where id in (select nid from 表)
 
b、通配符
    select * from 表 where name like 'lxy%'  - lxy开头的所有（多个字符串）
    select * from 表 where name like 'lxy_'  - lxy开头的所有（一个字符）
 
c、限制
    select * from 表 limit 5;            - 前5行
    select * from 表 limit 4,5;          - 从第4行开始的5行
    select * from 表 limit 5 offset 4    - 从第4行开始的5行
 
d、排序
    select * from 表 order by 列 asc              - 根据 “列” 从小到大排列
    select * from 表 order by 列 desc             - 根据 “列” 从大到小排列
    select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序

客户端和服务端

Mysql是一个c/s架构的软件，所以有服务端和客户端，服务端一般在机房长期运行，客户端在需要使用的时候才启动，想要访问服务器必须要在客户端进行连接和授权认证过程。

1、客户端连接服务端并认证

2、客户端发送SQL指令

3、服务端接收指SQL指令，处理指令，发送结果

4、客户端接收结果，显示结果

5、断开连接，释放资源

本质上客户端与服务端是通过socket链接

---

唯一索引

索引：目标就是为了加速查找

create table tb1(
    id int not null auto_increment primary key,
    num int,
    xx int,
    unique uq1 (num, xx)
)
-- 两个字段联合唯一
-- 其中num、xx都可以有一个是空

外键变种

一对多：

公司 员工表 和 部门表

员工表：
1	dalong	1
2	Mr1		1
3	Mr3		2
4	Boss	3
部门表：
1	人力部
2	学习部
3	打野部

一对一：

博客园 用户表 和 博客表。其中每个用户只能开通一个博客

用户表：
1 xiaoming
2 xiaohua
3 datou
4 pipi

博客表：
				FK()　　＋　唯一
1 /xiaoming/	1
2 /xiaohua111/	2
3 /datou520/	3
4 /pipideboke/	4
5 /pipi_second/	4

-- 此时通过FK外键关联，但是如果pipi还想创建一个博客呢？
-- 外键增加唯一约束，第五条记录就不能创建。这就是一对一

用途情况：

假设 用户表 中，只有管理级别用户才可以设置登录密码：

多对多：

情景假设1：用户表 和 约会表

每个用户相互之间可以多次约会

情景假设2：用户表 和 主机表

规定一台主机可以被哪些特定的人使用

上图中，如果使用方式一，在“查出c1都被哪些人使用？”的需求中十分不便（需要遍历整个数据表）

方式二用另一张表记录用户和主机之间的使用关系，再增加联合唯一索引（user, hostid）。

---

数据操作补充

1、增

insert into tb1 (name, age) values ('xiaoming', 12);	-- 单条数据插入

insert into tb1 (name, age) values ('xiaohua', 13), ('xiaolong', 14);	-- 多条数据插入

insert into tb2 (name, age) select name, age from tb1;	-- 将从表1中查询到的结果插入到表2

2、删

delete from tb1;	-- 清空
delete from tb1 where id >= 2 or id != 1;
-- 结合条件语句操作

3、改

update tb1 set name='xiaoming' where id>10 and name='xiaohua';

update tb1 set name='xiaolong', age=12 where id>10 and name='xiaoming';

4、查

• as 设置表头别名
• 11 新增一列常量

• where条件后面的in、between…and…

  select * from tb1 where id not in (1, 2, 10);
  
  select * from tb1 where id between 1 and 10;	-- 左右均闭合 
  

分组

group by

select max(id), part_id from userinfo group by part_id;
-- 根据part_id，将重复的合成一条
-- max(id)是为了确定查询出来的结果唯一不冲突

类似的聚合函数：

min()、count()、sum()、avg()

如果对于聚合函数的结果进行二次筛选，就需要用到having

select count(id), max(id), part_id from userinfo group by part_id having count(id)>1;

连表操作

如图，假如要让department表中的汉字对应到userinfo表中：

-- 连表操作
select * from userinfo, department where userinfo.part_id = department.id

其他写法：

select * from userinfo left join department on userinfo.part_id=department.id
-- 左边的全部显示

select * from userinfo right join department on userinfo.part_id=department.id
-- 右边的全部显示

还有一个inner join。它的作用是 如果出现空，那这一整条都不显示。

---

存储过程

就是保存在MySQL上的一个别名，这个别名代指一堆SQL语句。

然后使用这个别名就可以执行这个SQL语句。

作用：替代程序员写SQL语句。

-- 设置如下：
delimiter //
create procedure p1()
begin
	select * from student;
	insert into teacher (tname) values ('Mr3');
end //
delimiter ;

-- 调用时：
call p1();

存储过程（部分）（in，out，inout）：

-- 设置如下：
dilimiter //
create procedure p2(
	in n1 int,		-- 传递俩参数n1、n2
    in n2 int
)
begin
	select * from student where id>n1;		-- 传递进去的参数可以不使用
end //
dilimiter ;

-- 调用如下：
call p2(12, 2)