mysql数据库的基本操作(增删改查、字符集、校对集)
目录:
1.数据库课程体系
2.数据库基础知识
3.关系型数据库
4.关系型数据库的一些关键字
5.SQL 语言
6.mysql数据库
7.mysql服务器数据对象—mysql的结构
8.SQL基本操作 增删改查CRUD–库操作
9.表操作
10.数据操作
11.中文数据问题(字符集问题)
12.校对集
1.数据库课程体系
* 基础阶段(就业班6天):mysql数据库的基本操作,以及一些高级操作(视图、触发器、函数、存储过程等)和PHP操作mysql数据库
* 优化阶段:如何提高数据库效率,如索引,分表等
* 部署阶段:如何搭建真实的环境系统,如服务器集群,负载均衡
2.数据库基础知识
* 什么是数据库?
数据库:database,存储数据的仓库;高效地存储和处理数据的介质(磁盘和内存)
* 数据库的分类
基于存储介质的不同:分为关系型数据库(SQL)和非关系型数据库(NoSQL,Not only SQL)
* 数据库产品:
关系型:
大型:oracle,DB2
中型:SQL server,MySQL等
小型:access等
非关系型:
memcached,mongodb,redis
* 两种数据库阵营的区别?
关系型数据库:安全(保存磁盘基本不可能丢失),容易理解,比较浪费空间(二维表存储)
非关系型数据库:效率高,不安全(断电丢失)
3.关系型数据库
* 定义:是一种建立在关系模型(数学模型)上的数据库
* 关系模型:一种所谓建立在关系上的模型。关系模型包含三个方面:
数据结构:数据存储的问题,二维表(有行和列)
操作指令集合:SQL语句
完整性约束:表内数据约束,表与表之间约束(外键)
* 关系型数据库的设计:
关系型数据库:从需要存储的数据需求中分析,如果是一类数据(实体)应该设计成一张二维表:表是由表头(字段名:用来规定数据的名字)和数据部分组成(实际存储的数据单元)
二维表:行和列
表头
字段名1
字段名2
数据单元
数据1
数据2
以实际案例来进行处理:分析一个教学系统,讲师负责教学,教学生,在教室教学生
(1)找出系统中所有存在的实体:讲师表、学生表、班级表
(2)找出实体中应该存在的数据信息
讲师:姓名,性别,年龄,工资
学生:姓名,性别,学号,学科
班级:班级名称,教室编号
关系型数据库:维护实体内部,实体与实体之间的联系
实体内部联系:每个学生由姓名,性别,学号,学科信息
姓名
性别
学号
学科
年龄
张三
男
001
java
20
李四
男
007
java
21
第二行的所有字段,都是在描述张三这个学生(内部联系);第二列只能存储性别(内部约束)。
关系型数据库的特点之一:如果表中对应的某个字段没有值,但是系统依然要分配空间。所以关系型数据库比较浪费空间。
实体与实体之间的联系:每个学生肯定属于某个班级,每个班级一定由多个学生(一对多)
班级表
班级编号
班级名称
A001
java教学一班
A002
java教学二班
学生表(在学生表中添加一个班级字段来指向班级)
姓名
性别
学号
学科
年龄
班级编号
张三
男
001
java
20
A001
李四
男
007
java
21
A002
4.关系型数据库的一些关键字
* 数据库:database
* 数据库系统:DBS(Database System):是一种虚拟系统,将多种内容关联起来的称呼
DBS = DBMS +DB
* DBMS(Database Management System)数据库管理系统
* DBA(Database Administrator)数据库管理员
* 行/记录 row/record,本质是一个东西,都是指表中一行(一条记录);
* 列/字段:column/field
5.SQL语言
* SQL:Structured Query Language,结构化查询语言(数据以查询为主,99%都是在进行查询操作)
* SQL分为三个部分:
– DDL:Data Definition Language,数据定义语言,用来维护存储数据的结构(数据库和表),
代表指令:create,drop,alter等 【操作数据库和表结构】
– DML:Data Manipulation Language,数据操作语言,用来对数据进行操作【操作表记录】
代表指令:insert,delete,update等;
DML内部又单独有一个分类DQL,Data Query Language数据查询语言,如select
– DCL:Data Control Language,数据控制语言,主要负责权限管理,代表指令:grant,revoke等
6.mysql数据库
* mysql数据库是一种c/s(客户端/服务器)结构的软件
* 交互方式:
1)客户端连接 mysql[.exe] [-hlocalhost] [-p3306] -uroot -p123
2)发送SQL指令
3)服务器接收SQL指令,处理SQL指令,返回操作结果
4)客户端接收结果,显示结果
5)断开连接 (exit quit \q)
7.mysql服务器数据对象—mysql的结构
* 将mysql服务器内部对象分成了四层:
系统 DBMS –> 数据库 DB –> 表 table –> 字段 field
8.SQL基本操作 增删改查CRUD create read update delete
* 将SQL的基本操作根据操作对象进行分类,分为三类:库操作,表操作(字段),数据操作
* — 双中划线+空格:单行注释,也可以使用 #
* 库操作:对数据库的增删改查
==================新增数据库====================
– 新增数据库 create database [if not exists] 数据库名 [库选项];
** 库选项:用来约束数据库
** 字符集设定:charset/character set 具体字符集
** 校对集设定:collate 具体校对集(数据比较的规则)
** 例子:create database mydb1 charset utf8;
** 创建中文数据库(建议不要使用中文命名数据库名)
set names gbk;
create database 中文 charset utf8;
** 如果数据库名字和关键字(或保留字)重复,使用反引号
create database `database` charset utf8;
– 创建新的数据库后,会在mysql安装目录的data文件夹下创建一个文件夹,名字就是数据库名
每个数据库下,有一个db.opt文件,保存了库选项
default-character-set=utf8 ==> 字符集
default-collation=utf8_general_ci ==>校对集,依赖字符集
==================查询数据库====================
– 查看所有数据库 show databases;
– 查看指定部分的数据库:模糊查询
show databases like ‘mydb_’; 下划线_匹配一个字符
show databases like ‘my%’; %匹配任意个字符
show databases like ‘information\_%’; 使用\转义
– 查看数据库的创建语句
show create database 数据库名;
==================修改数据库====================
– 数据库名字不可以修改
– 可以修改数据库的库选项:字符集和校对集(校对集依赖字符集)
alter database 数据库名 charset [=] utf8;
alter database 数据库名 [default] character set [=] utf8;
==================删除数据库====================
– 删除数据库 drop database [if exists] 数据库名;
9.表操作(操作表结构)
==================新增数据表====================
* 新增数据表
create table [if not exists] [数据库名.]表名(
字段名字 数据类型,
字段名字 数据类型
)[表选项];
– if not exists:如果表不存在,创建之;如果已经存在,不创建;
– 表选项:
字符集:charset/character set 具体字符集;
校对集:collate 具体校对集;
存储引擎:engine 集体引擎;
– 例子:显示指定表所属的数据库
CREATE TABLE [IF NOT EXISTS] test1.`tb_stu` (
`username` varchar(20) DEFAULT NULL,
`age` int DEFAULT NULL,
`password` varchar(50) DEFAULT NULL
) [ENGINE=MyISAM DEFAULT CHARSET=utf8];
– 例子:先进入某个数据库环境,即隐式指定表所属数据库
use test1;
create table tb_stu1 (
username varchar(20),
age int
)charset utf8;
==================查看数据表====================
* 查看所有表 show tables;
* 模糊查询 show tables like ‘%s’;
* 查看表创建语句 show create table 表名;
show create table 表名\g
show create table 表名\G 将查到的结果旋转90度变成纵向
* 查看表结构
desc/describe 表名;
show columns from 表名;
==================修改数据表====================
* 修改表名 rename table tb_student to tb_stu
* 修改表选项
alter table tb_stu charset [=] utf8;
* 新增字段
alter table tb_stu add [column] (字段名 数据类型) [列属性][位置];
– 位置:
first:第一个位置
after 字段名:
alter table tb_stu add (
sid int
)first;
* 修改字段类型 alter table 表名 modify 字段 数据类型 [列属性][位置];
alter table tb_stu modify id varchar(32) after username;
* 修改列名和列类型 alter table 表名 change 原列名 新列名 列类型 [列属性][位置]; 可以同时修改列名和列类型
* 删除字段
alter table 表名 drop 字段名;
==================删除数据表====================
* drop table [if exists] 表名1[,表名2…];
10.数据操作(操作表记录)
* 新增数据
insert into tb_stu(username,password) values(‘zhangsan’,’123′),(‘lisi’,’123′);
insert into tb_stu values(‘zhangsan’,’123′),(‘lisi’,’123′);
* 查看数据
select * from tb_stu;
select username, password from tb_stu;
select * from tb_stu where username = ‘zhangsan’;
* 更新数据
update tb_stu set password = ‘456’ where username = ‘zhangsan’;
* 删除数据
delete from tb_stu [where …];
11.中文数据问题(字符集问题)
* show character set; 查看服务器识别哪些字符集
* 查看mysql数据库编码:show variables like ‘char%’;
* set names gbk; 相当与下面三句 ===>
SET character_set_client=’utf8′;
SET character_set_connection=’utf8′;
SET character_set_results =’utf8′;
* character_set_connection ;连接层字符集,是字符集转变的中间者,如果统一了效率更高,不统一也没问题
12.校对集
* 校对集:数据比较的方式
* 校对集有三种格式:
_bin:binary二进制比较,取出二进制位,一位一位比较,区分大小写
_cs:case sensitive 大小写敏感
_ci:case insensitive 大小写不敏感
* 查看所有校对集 show collation;
* 校对集应用:只有当数据产生比较的时候,校对集才会生效
对比:使用utf_bin和utf8_general_ci两种不同校对集的效果
— 创建表,使用不同的校对集
create table tb_collate_bin(
name varchar(20)
)charset utf8 collate utf8_bin;
create table tb_collate_ci(
name varchar(20)
)charset utf8 collate utf8_general_ci;
— 插入数据
insert into tb_collate_bin values(‘a’),(‘A’),(‘B’),(‘b’);
insert into tb_collate_ci values(‘a’),(‘A’),(‘B’),(‘b’);
— 排序查找
select * from tb_collate_bin order by name; ABab 区分大小写
select * from tb_collate_ci order by name; aABb 不区分大小写
* 当表已经有数据了,那么校对集修改无效
—