MySQL 数据库 CRUD

2022-01-30 08:43:22

数据库基本操作

连接服务器

浏览器键入
- http://localhost/phpmyadmin/index.php
客户端
- MySQL Workbench
命令行接入
- XAMPP中 Shell面板
- 命令行名词
  - host -h 主机
  - port -p 端口号
  - user -u 用户名
  - password -p 密码
- 连接数据库
  - mysql -h127.0.0.1 -P3306 -uroot -proot 明文
  - mysql -uroot -proot 明文 (本地数据库端口号3306)
  - mysql -uroot -p 密文
- 退出登录
  - exit
  - quit
  - \q

数据库基本概念

数据库表相关

数据库：数据库中存放的是表，一个数据库中可以存放多个表
表：表是用来存放数据的
关系：两个表的公共字段
行：也称记录，也称实体
列：也称字段，也称属性
理解
- 就表结构而言，表分为行和列
- 就表数据而言，表分为记录和字段
- 就面向对象而言，一个记录就是一个实体，一个字段就是一个属性

数据相关

数据冗余：相同的数据存储在不同的地方
- 冗余只能减少，不能杜绝
- 减少冗余的方法是分表
数据完整性：正确性+准确性=数据完整性
- 正确性：数据类型正确
- 准确性：数据范围要准确

数据库执行过程

客户端
- 连接数据库
- 发送SQL指令
- 返回结果
- 发送SQL指令
- 返回结果
- 关闭连接
MySQL

MySQL数据库的目录

数据库保存的路径在安装MySQL的时候就配置好
在my.ini配置文件中更改数据库的保存地址
- datadir="F:/wamp/PHPTutorial/MySQL/data/"
一个数据库就对应一个文件夹，在文件夹中有一个db.opt文件
- 在此文件中设置数据库的字符集和校对集

MySQL数据库报错

如果创建的数据库已存在，会报错
- 创建数据库的时候判断一下数据库是否存在，如果不存在再创建
如果数据库名是关键字和特殊字符，要报错
- 在特殊字符、关键字行加上反引号
创建数据库的时候可以指定字符编码
- 创建数据库如果不指定字符编码，默认和MySQL服务器的字符编码是一致的

数据库的操作

创建数据库

语法
- create database [if not exists] 数据名 [选项];

-- 创建数据库时指定存储的字符编码
-- 如果不指定编码，数据库默认使用安装数据库时指定的编码
mysql> create database emp charset=gbk;
# `Query OK, 1 row affected (0.00 sec)`

-- 创建数据库
mysql> create database stu;
# `Query OK, 1 row affected (0.06 sec)`

-- 创建数据库时，如果数据库已经存在就要报错
mysql> create database stu;
# `ERROR 1007 (HY000): Can't create database 'stu'; database exists`

-- 在创建数据库时候，判断数据库是否存在，不存在就创建
mysql> create database if not exists stu;
# `Query OK, 1 row affected, 1 warning (0.00 sec)`

-- 特殊字符、关键字做数据库名，使用反引号将数据库名括起来
mysql> create database `create`;
# `Query OK, 1 row affected (0.04 sec)`

mysql> create database `%$`;
# `Query OK, 1 row affected (0.05 sec)`

显示所有数据库

语法
- show databases;

删除数据库

语法
- drop database [if exists] 数据库名;

显示创建数据库的语句

语法
- show create database 数据库名;

修改数据库

语法
- alter database 数据库名 charset=字符编码;
说明
- 修改数据库只能修改数据库的字符编码
- 在MySQL中utf字符编码之间没有横杆 utf8

选择数据库

语法
- use 数据库名

表的操作

数据表的文件

拓扑结构
- 一个数据库对应一个文件夹
- 一个表对应一个或多个文件
引擎
- myisam 一个表对应三个文件
  - .frm 存储的是表结构
  - .myd 存储的是表数据
  - .myi 存储的表数据的索引
- innodb 一个表对应一个表结构文件
  - innodb的都有表的数据都保存在ibdata1文件中
  - 如果数据量很大，会自动的创建ibdata2，ibdata3...
  - 如果不指定引擎，默认是innodb
- innodb和myisam的区别
  - myisam
    - 查询速度快
    - 容易产生碎片
    - 不能约束数据
  - innodb
    - 以前没有myisam查询速度快，现在已经提速了
    - 不产生碎片
    - 可以约束数据

表前环境创建

-- 创建数据库
mysql> create database data;
# `Query OK, 1 row affected (0.00 sec)`

-- 使用数据库
mysql> use data;
# `Database changed`

-- 设置客户端和服务器通讯的编码
mysql> set names gbk;  
# `Query OK, 0 rows affected (0.00 sec)`

创建表

语法

create table [if not exists] `表名`(
    `字段名` 数据类型 [null|not null] [default] [auto_increment] [primary key] [comment],
    `字段名` 数据类型 [null|not null] [default] [auto_increment] [primary key] [comment]
)[engine=存储引擎] [charset=字符编码]

语法说明
- null|not null 是否为空
- default 默认值
- auto_increment 自动增长,默认从1开始，每次递增1
- primary key 主键，主键的值不能重复,不能为空,每个表必须只能有一个主键
- comment 备注
- engine 引擎决定了数据的存储和查找 myisam、innodb
- 表名和字段名如果用了关键字，要用反引号引起来
- 如果不指定引擎，默认是innodb
- 如果不指定字符编码，默认和数据库编码一致
- varchar(20) 表示长度是20个字符

-- 创建简单的表
mysql> create table stu1(
    -> id int auto_increment primary key,
    -> name varchar(20) not null
    -> )engine=innodb charset=gbk;
# `Query OK, 0 rows affected (0.11 sec)`

-- 创建复杂的表
mysql> create table stu2(
    -> id int auto_increment primary key comment '主键',
    -> name varchar(20) not null comment '姓名',
    -> `add` varchar(50) not null default '地址不详' comment '地址',
    -> score int comment '成绩,可以为空'
    -> )engine=myisam;
# `Query OK, 0 rows affected (0.06 sec)`

显示所有表

语法
- show tables;

显示创建表的语句

语法
- show create table 表名称; 结果横着排列
- show create table 表名称\G; 结果竖着排列

查看表结构

语法
- desc[ribe] 表名;

复制表

语法
- create table 新表 select 字段 from 旧表;
  - 不能复制父表的键，能够复制父表的数据
- create table 新表 like 旧表;
  - 只能复制表结构，不能复制表数据
- * 表示所有字段

删除表

语法
- drop table [if exists] 表1，表2,… ;

修改表

语法
- alter table 表名;

添加字段

语法
- alter table 表名 add [column] 字段名数据类型 [位置];

-- 默认添加字段放在最后
mysql> alter table stu add `add` varchar(20);	
# `Query OK, 0 rows affected (0.05 sec)`

-- 在name之后添加sex字段
mysql> alter table stu add sex char(1) after name;  
# `Query OK, 0 rows affected (0.00 sec)`
# `Records: 0  Duplicates: 0  Warnings: 0`

-- age放在最前面
mysql> alter table stu add age int first;  
# `Query OK, 0 rows affected (0.00 sec)`
# `Records: 0  Duplicates: 0  Warnings: 0`

删除字段

语法
- alter table 表 drop [column] 字段名;

-- 删除age字段
mysql> alter table stu drop age;   
# `Query OK, 0 rows affected (0.00 sec)`
# `Records: 0  Duplicates: 0  Warnings: 0`

修改字段(改名)

语法
- alter table 表 change [column] 原字段名新字段名数据类型;

-- 将name字段更改为stuname varchar(10)
mysql> alter table stu change name stuname varchar(10);
# `Query OK, 0 rows affected (0.02 sec)`
# `Records: 0  Duplicates: 0  Warnings: 0`

修改字段(不改名)

语法
- alter table 表 modify 字段名字段属性字段属性;

-- 将sex数据类型更改为varchar(20)
mysql> alter table stu  modify sex varchar(20);
# `Query OK, 0 rows affected (0.00 sec)`
# `Records: 0  Duplicates: 0  Warnings: 0`

-- 将add字段更改为varchar(20) 默认值是‘地址不详’
mysql> alter table stu modify `add` varchar(20) default '地址不详';
# Query OK, 0 rows affected (0.00 sec)
# Records: 0  Duplicates: 0  Warnings: 0

修改引擎

语法
- alter table 表名 engine=引擎名;

mysql> alter table stu engine=myisam;
# `Query OK, 0 rows affected (0.01 sec)`
# `Records: 0  Duplicates: 0  Warnings: 0`

修改表名

语法
- alter table 表名 rename to 新表名;

-- 将stu表名改成student
mysql> alter table stu rename to student;
# Query OK, 0 rows affected (0.00 sec)

将表移动到其他数据库

语法
- alter table 表名 rename to 其他数据库.新表名;

-- 将当前数据库中的student表移动到php74数据库中改名为stu
mysql> alter table student rename to php74.stu;
# Query OK, 0 rows affected (0.00 sec)

数据操作

插入数据

插入所有字段

语法
- insert into 表名 (字段名, 字段名,…) values (值1, 值1,…);
说明
- 插入字段名的顺序和数据表中字段名的顺序可以不一致
- 插入值的个数、顺序必须和插入字段名的个数、顺序要一致
- 如果插入的值的顺序和个数与表字段的顺序个数一致，插入字段可以省略

-- 插入所有字段
mysql> insert into stu (id,stuname,sex,`add`) values (1,'tom','男','北京');
# `Query OK, 1 row affected (0.00 sec)`

-- 插入部分字段
mysql> insert into stu(id,stuname) values (2,'berry');

-- 插入的字段和表的字段可以顺序不一致。但是插入字段名和插入的值一定要一一对应
mysql> insert into stu(sex,`add`,id,stuname) values ('女','上海',3,'ketty');
# `Query OK, 1 row affected (0.00 sec)`

-- 插入字段名可以省略
mysql> insert into stu values(4,'rose','女','重庆');
# `Query OK, 1 row affected (0.00 sec)`

插入默认值和空值

语法
- insert into 表名 values (值1, 值2, null, default);
- default关键字用来插入默认值，null用来插入空值

mysql> insert into stu values (5,'jake',null,default);
# `Query OK, 1 row affected (0.05 sec)`

插入多条数据

mysql> insert into stu values (6,'李白','男','四川'),(7,'杜甫','男','湖北');
# `Query OK, 2 rows affected (0.00 sec)`
# `Records: 2  Duplicates: 0  Warnings: 0`

更新数据

语法
- update 表名 set 字段=值 [where 条件];

-- 将berry性别改为女
mysql> update stu set sex='女' where stuname='berry';
# `Query OK, 1 row affected (0.06 sec)`

-- 将编号是1号的学生性别改成女，地址改为上海。
mysql> update stu set sex='女',`add`='上海' where id=1;
# `Query OK, 1 row affected (0.00 sec)`

删除数据

语法
- delete from 表名 [where 条件];
delete from 表和truncate table 表的区别
- delete from 遍历表记录，一条一条的删除
- truncate table 将原表销毁，再创建一个同结构的新表。就清空表而言，这种方法效率高

-- 删除1号学生
mysql> delete from stu where id=1;

-- 删除名字是berry的学生
mysql> delete from stu where stuname='berry';
# `Query OK, 1 row affected (0.00 sec)`

-- 删除所有数据
mysql> delete from stu;
# `Query OK, 5 rows affected (0.00 sec)`

查询数据

语法
- select 列名 from 表名;

-- 查询id字段的值
mysql> select id from stu;

-- 查询id,stuname字段的值
mysql> select id,stuname from stu;、

-- 查询所有字段的值
mysql> select * from stu;

编码设置

语法
- set names gbk; 一次性设置客户端和服务器通讯的编码
- set character_set_client=gbk; 更改接受客户端指令的编码
- 设置什么编码取决于客户端的编码

校对集

语法
- collate=utf8_general_ci; 不区分大小写
- collate=utf8_bin; 按二进制编码比较，区别大小写

mysql> create table stu1(
    -> name char(1)
    -> )charset=utf8 collate=utf8_general_ci;
# `Query OK, 0 rows affected (0.05 sec)`

mysql> create table stu2(
    -> name char(1)
    -> )charset=utf8 collate=utf8_bin;
# `Query OK, 0 rows affected (0.05 sec)`

mysql> insert into stu1 values ('a'),('B');
# `Query OK, 2 rows affected (0.00 sec)`
# `Records: 2  Duplicates: 0  Warnings: 0`

mysql> insert into stu2 values ('a'),('B');
# `Query OK, 2 rows affected (0.00 sec)`
# `Records: 2  Duplicates: 0  Warnings: 0`

码农公寓

数据库基本操作

连接服务器

数据库基本概念

数据库 表相关

数据相关

数据库执行过程

MySQL数据库的目录

MySQL数据库报错

数据库的操作

创建数据库

显示所有数据库

删除数据库

显示创建数据库的语句

修改数据库

选择数据库

表的操作

数据表的文件

表前环境创建

创建表

显示所有表

显示创建表的语句

查看表结构

复制表

删除表

修改表

添加字段

删除字段

修改字段(改名)

修改字段(不改名)

修改引擎

修改表名

将表移动到其他数据库

数据操作

插入数据

插入所有字段

插入默认值和空值

插入多条数据

更新数据

删除数据

查询数据

编码设置

校对集

相关文章

数据库表相关