|
|
一、数据库介绍
1、数据库的由来
我们之前所学，数据要想永久保存，都是保存于文件中，毫无疑问，一个文件仅仅只能存在于某一台机器上，这样就带来了许多问题：
（1）程序所有的组件就不可能运行在一台机器上
（2）数据安全问题
（3）并发问题
总结：我们在编写任何程序之前，都需要事先写好基于网络操作一台主机上文件的程序（socket服务端与客户端程序），于是有人将此类程序写成一个专门的处理软件，这就是mysql等数据库管理软件的由来，mysql不仅仅解决的是数据共享的问题，还有查询效率，安全性等一系列问题，总之，把程序员从数据管理中解脱出来，专注于自己的程序逻辑的编写。
2、数据库说明
数据库服务器 -：运行数据库管理软件
数据库管理软件：管理 - 数据库
数据库：即文件夹，用来组织文件 / 表
表：即文件，用来存放多行内容 / 多条记录
3、mysql介绍
MySQL是一个关系型数据库管理系统
（1）分两大类：
关系型：如sqllite，db2，oracle，access，sql server，MySQL，注意：sql语句通用
非关系型：mongodb，redis，memcache
（2）可以简单的理解为：
关系型数据库需要有表结构
非关系型数据库是key-value存储的，没有表结构
二、操作
1、mysql数据库环境准备
环境：window7_X86_64
版本：mysql-5.6.38-winx64.zip
下载地址：https://dev.mysql.com/downloads/mysql/
文件路径：E:\mysql-5.6.38-winx64
添加到环境变量：计算机---->属性---->高级系统设置---->环境变量---->系统变量---->path---->E:\mysql-5.6.38-winx64\bin\;
2、登录和设置密码
C:\Users\Administrator> mysqld --install
C:\Users\Administrator> net start MySQL #启动mysql数据库
C:\Users\Administrator> mysql #默认用户不用密码可以直接登录
mysql> select user(); #查看当前登录用户，显示的是ODBC用户
C:\Users\Administrator> mysqladmin -uroot -p password "123" #设置root账号的密码是123
3、重置密码
C:\Users\Administrator> net stop MySQL
C:\Users\Administrator> mysqld --skip-grant-tables
新打开一个终端操作：
C:\Users\Administrator> mysql -uroot -p
mysql > update mysql.user set password=password("") where user='root' and host="localhost";
mysql > flush privileges;
mysql> exit;
C:\Users\Administrator>tasklist |findstr mysql #查看mysql服务的进程号
mysqld.exe 6316 Console 1 454,544 K
C:\Users\Administrator>taskkill /F /PID 6316 #结束mysql服务
C:\Users\Administrator>net start MySQL #启动mysql服务
C:\Users\Administrator> mysql -uroot -p #登录不需要密码了
4、同一字符编码
#mysql5.5以上的配置文件配置
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
mysql> show variables like '%char%'; #查看字符编码
5、SQL语言介绍
SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型：
#1、DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP> #2、DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
#3、DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE
（1）操作文件夹
增：create database db1 charset utf8;
查：show databases;
改：alter database db1 charset latin1;
删除: drop database db1;
（2）操作文件
先切换到文件夹下：use db1
增：create table t1(id int, name char);
查：show tables
改：alter table t1 modify name char(3);> 删：drop table t1;
（3）操作文件中的内容/记录
增：insert into t1 values(1, 'egon1'), (2, 'egon2'), (3, 'egon3');
查：select * from t1;
改：update t1 set name = 'sb' where>
删：delete from t1 where> 清空表：
delete from t1; # 如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。
truncate table t1; 数据量大，删除速度比上一条快，且直接从零开始，
auto_increment 表示：自增
primary key 表示：约束（不能重复且不能为空）；加速查找
6、库操作
（1）查看数据库
show databases;
show create database db1;
select database();
（2）选择数据库
USE 数据库名
（3） 删除数据库
DROP DATABASE 数据库名;
（4）修改数据库
alter database db1 charset utf8;
7、表操作
（1）创建表
#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的
mysql> show engines; #显示所有的存储引擎
mysql> use db1
mysql> create table t1(id int)engine=innodb;
mysql> create table t2(id int)engine=myisam;
mysql> create table t3(id int)engine=memory;
mysql> create table t4(id int)engine=blackhole;
mysql> show tables; #查看db1库下的所有的表名
mysql> desc t1;
#查看t1表的表结构
#memory存储引擎，在重启mysql或者重启机器后，表内数据清空
#blackhole存储引擎，往表内插入任何数据，都相当于丢入黑洞，表内永远不存记录
mysql> create table db1.f2 select user,host from mysql.user; #复制表
mysql> create table db1.f3 select user,host from mysql.user where 1=2; #只复制表结构（查询的结果不存在，没有值）
mysql> create table db1.f4 like mysql.user; #只复制表结构
(2)日期类型
create table student(
id int,
name char(16),
born_year year,
birth_date date,
class_time time,
reg_time datetime
);
insert into student values(1,'wang',now(),now(),now(),now()); #插入当前时间
insert into student values(2,'wang','1999','1999-11-11','11:11:11',"1990-11-11 11:11:11"); #插入指定时间
(3)字符类型
#char类型：定长，简单粗暴，浪费空间，存取速度快
字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）
#varchar类型：变长，精准，节省空间，存取速度慢
字符长度范围：0-65535（如果大于21845会提示用其他类型 。mysql行最大限制为65535字节，字符编码为utf-8)
#常用字符串系列：char与varchar
注：虽然varchar使用起来较为灵活，但是char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡
#宽度代表的是字符的个数
create table t6(name char(5));
create table t7(name varchar(5));
(4)修改表ALTER TABLE
语法：
#1.修改表名
ALTER TABLE 表名 RENAME 新表名;
#2.增加字段
ALTER TABLE 表名
ADD 字段名 数据类型[完整性约束条件…],
ADD 字段名 数据类型[完整性约束条件…];
ALTER TABLE 表名
ADD 字段名 数据类型[完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型[完整性约束条件…] AFTER 字段名;
#3.删除字段
ALTER TABLE 表名
DROP 字段名;
#4.修改字段
ALTER TABLE 表名
MODIFY 字段名 数据类型[完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型[完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型[完整性约束条件…];
#5.删除表
DROP TABLE 表名;
示例：
#1.修改存储引擎
mysql >> #2.添加字段
mysql > alter table student10
-> add name varchar(20) not null,
-> add age int(3) not null default 22;
mysql > alter table student10 add stu_num varchar(10) not null after name; // 添加name字段之后
mysql > alter table student10 add sex enum('male', 'female') default 'male' first; // 添加到最前面
#3.删除字段
mysql >>
mysql >> #4.修改字段类型modify
mysql >>
mysql >> #5.增加约束（针对已有的主键增加auto_increment）
mysql >> #6.对已经存在的表增加复合主键
mysql >> #7.增加主键
mysql >> #8.增加主键和自动增长
mysql >> #9.删除主键
a.删除自增约束
mysql >> b.删除主键
mysql >> (5)枚举类型与集合类型
字段的值只能在给定范围中选择，如单选框，多选框
enum 单选只能在给定的范围内选一个值
set 多选 在给定的范围内可以选择一个或一个以上的值
create table employee(
id int,
name char(10),
sex enum('male','female','other'),
hobbies set('play','eat','music','read')
);
insert into employee values(1,'li','male','music,read');
insert into employee values(2,'wang','xxxx','music,read');
三、mysql表的完整性约束
1、约束条件
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT 为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充
2、not null与default
（1）是否可空，null表示空，非字符串
not null - 不可空
null - 可空
（2）默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
create table tb1(nid int not null defalut 2,num int not null);
#设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值
（3）练习
mysql> create table student(
-> name varchar(20) not null,
-> age int(3) unsigned not null default 18,
-> sex enum('male','female') default 'male',
-> hobby set('play','study','read','music') default 'play,music'
-> );
mysql> desc student;
+-------+----------------------------------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------------------------+------+-----+------------+-------+
| name | varchar(20) | NO | | NULL | |
| age | int(3) unsigned | NO | | 18 | |
| sex | enum('male','female') | YES | | male | |
| hobby | set('play','study','read','music') | YES | | play,music | |
+-------+-----------------------------------------+------+-----+------------+-------+
mysql> insert into student(name) values('wang');
mysql> select * from student;
+------+-----+------+------------+
| name | age | sex | hobby |
+------+-----+------+------------+
| wang | 18 | male | play,music |
+------+-----+------+------------+
3、唯一约束 UNIQUE
（1）UNIQUE唯一
方法一：
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);
方法二：
create table department2(
id int,
name varchar(20),
comment varchar(100),
constraint uk_name unique(name)
);
mysql> insert into department1 values(1,'IT','技术');
（2）not null+unique会被作为主键
（3）联合唯一
create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #联合唯一
);
mysql> insert into service values
(1,'nginx','192.168.0.10',80),
(2,'haproxy','192.168.0.20',80),
(3,'mysql','192.168.0.30',3306)
;
mysql> select * from service;
+----+---------+--------------+------+
| id | name | host | port |
+----+---------+--------------+------+
| 1 | nginx | 192.168.0.10 | 80 |
| 2 | haproxy | 192.168.0.20 | 80 |
| 3 | mysql | 192.168.0.30 | 3306 |
+----+---------+--------------+------+
mysql> insert into service values (4,'httpd','192.168.0.10',80);
ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host' #联合唯一报错
4、primary key 主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，一张表中必须有且只有一个主键。
一个表中可以：
单列做主键
多列做主键（复合主键）
（1）单列做主键
#方法一：not null+unique
create table department1(id int not null unique,name varchar(20) not null unique,comment varchar(100));
#方法二：在某一个字段后用primary key
create table department2(id int primary key,name varchar(20),comment varchar(100));
#方法三：在所有字段后单独定义primary key
create table department3(id int,name varchar(20),comment varchar(100),constraint pk_name primary key(id); #创建主键并为其命名pk_name
（2）多列做主键create table service(ip varchar(15),port char(5),service_name varchar(10) not null,primary key(ip,port));
mysql> desc service;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ip | varchar(15) | NO | PRI | NULL | |
| port | char(5) | NO | PRI | NULL | |
| service_name | varchar(10) | NO | | NULL | |
+--------------+-------------+------+-----+---------+-------+
mysql> insert into service values('172.16.45.10','3306','mysqld'),('172.16.45.11','3306','mariadb');
mysql> insert into service values ('172.16.45.10','3306','nginx');
ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'
5、auto_increment 约束字段为自动增长，被约束的字段必须同时被key约束
（1）不指定id，则自动增长
create table student(id int primary key auto_increment,name varchar(20),sex enum('male','female') default 'male');
mysql> insert into student(name) values('li'),('wang');
（2）也可以指定id
mysql> insert into student values(5,'wsb','female');
mysql> select * from student;
+----+------+--------+
| id | name | sex |
+----+------+--------+
| 1 | li | male |
| 2 | wang | male |
| 5 | wsb | female |
+----+------+--------+
（3）对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
mysql> delete from student where name='wsb';
mysql> insert into student(name,sex) values('wsb','female');
mysql> select * from student;
+----+------+--------+
| id | name | sex |
+----+------+--------+
| 1 | li | male |
| 2 | wang | male |
| 6 | wsb | female |
+---+------+--------+
（4）应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
mysql> truncate student;
mysql> insert into student(name) values('wang');
mysql> select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | wang | male |
+----+------+------+
（5）步长和偏移量
show variables like 'auto_incre%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
# 基于会话级别
set session auth_increment_increment=2 # 修改会话级别的步长
# 基于全局级别的
set global auth_increment_increment=2 # 修改全局级别的步长（所有会话都生效）
mysql> set global auto_increment_offset=3; # 修改全局级别的偏移量
如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略，修改全局级别的偏移量和步长时，需要退出重新登录才生效
6、外键 foreign key
#表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一
create table department(id int primary key,name varchar(20) not null)engine=innodb;
#dpt_id外键，关联父表（department主键id），同步更新，同步删除
create table employee(
id int primary key,
name varchar(20) not null,
dpt_id int,
constraint fk_name foreign key(dpt_id)
references department(id)
on delete cascade
on update cascade
)engine=innodb;
#先往父表department中插入记录
insert into department values(1,'综合事业部'),(2,'人力资源部'),(3,'销售部');
#再往子表employee中插入记录
insert into employee values(1,'wang',1),(2,'wang1',2),(3,'wang2',2),(4,'wang3',2),(5,'wang4',3),(6,'wang5',3);
#删父表department，子表employee中对应的记录跟着删
mysql> delete from department where id=3;
mysql> select * from employee;
#更新父表department，子表employee中对应的记录跟着改
mysql> update department set id=22222 where id=2;
mysql> select * from employee;
7、找出两张表之间的关系
（1）先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）
（2）再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）
（3）总结：
#多对一：
如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表
create table press(id int primary key auto_increment,name varchar(20));
create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade
);
insert into press(name) values('北京工业出版社'),('人民教育出版社'),('北方出版社');
insert into book(name,press_id) values('九阳神功',1),('九阴真经',2),('九阴白骨爪',2),('独孤九剑',3),('降龙十巴掌',2),('葵花宝典',3);
#多对多
如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系
create table author(id int primary key auto_increment,name varchar(20));
#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);
#插入四个作者，id依次排开
insert into author(name) values('zhao'),('qian'),('sun'),('li');
insert into author2book(author_id,book_id) values(1,1),(1,2),(1,5),(1,6),(2,1),(2,6),(3,4),(3,5),(3,6),(4,1);
#一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
#一定是student来foreign key表customer，这样就保证了：
#1 学生一定是一个客户，
#2 客户不一定是学生，但有可能成为一个学生
create table customer(id int primary key auto_increment,name varchar(20) not null,qq varchar(10) not null,phone char(16) not null);
create table student(
id int primary key auto_increment,
class_name varchar(20) not null,
customer_id int unique, #该字段一定要是唯一的
foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
on delete cascade
on update cascade
);
#增加客户
insert into customer(name,qq,phone) values
('zhao','31811231',13811341220),
('qian','123123123',15213146809),
('sun','283818181',1867141331),
('li','283818181',1851143312),
('zhou','888818181',1861243314),
('wu','112312312',18811431230)
;
#增加学生
insert into student(class_name,customer_id) values('脱产3班',3),('周末19期',4),('周末19期',5);
|
|
QQ群⑧：
窥视卡
雷达卡
发表于 2018-10-6 11:03:17
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡