MySQL 学习笔记(一)基本操作

一、关键字说明

数据库:database 数据库系统:DBS DBMS:数据库管理系统 DBA:数据库管理员

  • 行、记录(record):都是指表中的一条记录,行是从结构角度出发,记录是从记录角度出发
  • 列、字段(field):数据

二、结构化查询语言(数据以查询为主:99%是在进行查询操作)

SQL 分为三个部分:

  • DDL:数据定义语言,用来维护数据的结构(数据库、表),代表指令:create、drop、alter
  • DML:数据操作语言,用来对数据进行操作,代表指令:insert、delete、update
  • DCL:数据控制语言,主要是负责权限管理(用户),代表指令:grant、revoke等

SQL 是关系型数据库的操作指令,SQL 是一种约束,但不强制,不同的数据库可能内部会有细微的区别。

三、 MySQL 数据库

MySQL 是一种c/s(客户端/服务器)结构的软件:客户端/服务器,若想访问服务器必须通过客户端(服务器一直运行,客户端在需要使用的时候运行)

客户端与服务器之家的交互方式

  1. 客户端连接认证:连接服务器,认证身份:mysql -u root -p
  2. 客户端发送SQL指令
  3. 服务器接收SQL指令:处理SQL指令返回操作结果
  4. 客户端接收结果:显示结果show databases;
  5. 断开连接(释放资源:服务器并发限制):exit、quit、\q

MySQL 服务器对象:由于编译是不可逆的,所以无法完全了解服务器内部内容,只能粗略分析服务器的内部结构。可以将服务器内部对象分成四层:操作系统(3306端口)=》数据库(DBMS)=》数据表(Table)=》字段(filed)。

四、 SQL 基本操作

基本操作:CRUD(增删改查),根据操作对象分为三类:库操作、表操作、数据操作

库操作

对数据库的增删改查

新增数据库

1
2
3
4
5
6
# 库选项:用来约束数据库,分为两个选项
# 字符集设定:charset/character set(数据存储的编码格式)
# 校对集设定:collate
# 其中数据库名字不能用关键字
# 如果一定要使用关键字或保留字,必须使用反引号`
Create database 数据库名字 [库选项];

SQL 语句报错只会提示大概错误位置,不会说明错误的原因(静默模式)。中文名的数据库是可以创建的,但是前提是保证服务器能够识别(建议不用)。

查看数据库

  1. 查看指定部分的数据库:模糊查询,%匹配多个字符,_匹配单个字符
  2. 查看数据库的创建语句:show create database [数据库名字]
  3. 数据库在执行SQL语句之前会优化SQL:系统保存的结果是优化的结果

更新数据库

数据库名字不可以修改(不安全),数据库的修改仅限库选项:字符集和校对集(校对集依赖于字符集,但是不要随便改)。

删除数据库

数据库删除了之后,发生了什么?

  1. 在数据库中看不到对应的数据库
  2. 在对应的数据库存储的文件夹内,数据库名字的文件夹也被删除,数据表也被删除了
  3. 不要随意删除数据库,应该先进行备份后操作(删除不可逆的)。

表操作

表与字段是密不可分的。

新增数据表

任何一个表的设计都必须指定数据库,一般有显式和隐式两种方案,显式的采用数据库名.表名办法;隐式的先进入某个数据库环境,然后将创建的表自动归属到某个指定的数据库。

1
2
3
4
5
6
7
8
create table [if not exists] 数据库名.表名( -- 显视地将表放置在数据库下
字段名字 数据类型,
字段名字 数据类型 -- 最后一行不需要逗号
)[表选项];
# if not exists 检查功能:如果表名不存在数据表才会被创建
# 字符集:具体字符集
# 存储引擎:engine (innodb 和 mysian)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 新增数据表(显式的)
create table if not exists mydatabase.student(
name varchar(10),
gender varchar(10),
number varchar(10),
age int
)charset utf8;
-- 新建数据表(隐式的)
-- 进入数据库
use mydatabase;
-- 创建表
create table class(
name varchar(10),
room varchar(10)
)charset utf8;

创建SQL 执行指令之后,发生了什么?

  1. 指定数据库下存在对应的表
  2. 在数据库对应的文件夹下,会产生对应表的结构文件

查看数据表

1
2
-- 查看数据库
show databases;

数据库能查看的方式,表都可以查看

查看所有表

1
2
-- 查看所有表
show tables;

查看表中的字段信息: desc、 describe 、show column from 表名

1
2
3
4
-- 查看表结构
desc class;
describe class;
show columns from class;

修改数据表

表本身存在,还包含字段,表的的修改分为两个部分:修改表本身和修改字段。

修改表本身:表名和表选项

  • 修改表名:rename table 旧表名 to 新表名
1
2
-- 重命名表:student表 -> my_student(取数据库名字的前两个字母)
rename table student to my_student;
  • 修改表选项:字符集、校对集和存储引擎
1
2
-- 修改表选项:字符集
alter table my_student charset = GBK;

修改字段:新增、修改、重命名、删除

1
2
3
4
5
6
7
8
新增字段:alter table 表名 add[column] 字段名 数据类型 [列属性][位置];
位置:字段名可以存放在表中的任意位置
first:第一位置
after:在哪个字段之后
修改字段:修改通常是修改属性或者数据类型:alter table 表名
重命名字段:alter table 表名 change 旧字段 数据类型 [属性][位置];
1
2
3
4
-- 给学生表增加一个id放到第一位置
alter table my_student
add column id int
first; -- mysql 会自动寻找分好作为语句结束符
1
2
3
-- 将学生表中的number学号字段变成固定长度,且放到第二位置
alter table my_student
modify number char(10) after id;
1
2
3
-- 修改学生表中的gender字段为sex
alter table my_student
change gender sex varchar(10);

删除字段

alter table 表名 drop 字段名;字段名下的数据都会被删除

删除数据表

drop table 表名1,表名2...;

当删除数据表之后发生了什么?

  1. 表空间中,没有了指定的表(数据也没有了)
  2. 数据库对应的文件夹下,表对应的文件也会被删除
  3. 删除有危险,操作需谨慎(操作不可逆)

数据操作

新增数据

  • 方案1: 给全表段插入数据,不需要指定字段列表:要求数据的值出现的顺序必须与表中设计的字段出现的顺序一致:凡是非数值数据,都需要使用引号(建议是单引号)包裹
1
insert into 表名 value(值列表) -- 可以一次性插入多条记录
1
2
3
-- 插入数据
insert into my_student values(1,'itcast0001','Jim','male'),
(2,'itcast0002','Hanmeimei','female');
  • 方案2: 给部分字段插入数据,需要选定字段列表,字段列表出现的顺序与字段的顺序无关,但是值列表的顺序必须与选定的字段的顺序一致
1
inssert into 表名(字段列表)values(值列表)[,(值列表)];

查看表中的数据

1
select */字段列表 from 表名 [where条件];

查看指定字段,指定条件的数据

1
2
-- 查看指定字段,指定条件数据
select id,number,sex,name from my_student where id = 1; -- 查看满足ID为1的学生信息

更新数据

1
2
-- 更新数据
update my_student set sex = 'female' where name = 'Jim';

update 表名 set 字段 = 值 [where 条件]

不是说执行了SQL 语句就是成功了,需要看数据字段是否有改变

删除数据

删除是不可逆的,需谨慎删除:delete from 表名 where

1
2
-- 删除数据
delete from my_student where sex = 'male';

五、中文数据问题

中文数据问题本质是字符集问题,计算机只识别二进制,人类更多的是识别符号,所以需要有一个二进制与字符的对应关系(字符集)

场景:客户端向服务器插入中文数据,但是没有成功

原因:中文字符在当前在当前编码(字符集)下对应的二进制转十六进制:两个汉字=》四个字节(GBK),服务器没有识别对应的四个字节:服务器认为数据是utf-8,在utf-8编码下一个汉字有三个字节,读取三个字节转换成汉字失败,剩余的再读一个字节不够。

查看服务器到底识别哪些字符集,支持39种数据集,基本上什么字符集都可以支持

问题根源是客户端数据是gbk而服务器认为是utf8,解决方案是改变服务器默认接收字符集为gbk,set 变量 = 值,会话级别,只对当前会话有效,关闭之后重启还是一样

设置服务器对客户端的字符集的认识,可以使用快捷方式:

1
set names gbk;

连接层是字符集转变的中间者,如果统一了效率更高,不统一也没问题。

六、校对集问题

校对集:数据比较的方式,一共有197种,不过校对集用的频率不多。

校对集有三种格式 - _bin:binary,二进制比较,取出二进制位,一位一位比较,区分大小写 - _cs: case sensitive,大小写敏感,区分大小写 - _ci: case insensitive,大小写不敏感,不区分大小写

只有对数据产生比较的时候校对集才会产生作用。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 使用不同的校对集创建表
create table my_collate_bin(
name char(1)
)charset utf8 collate utf8_bin;
create table my_collate_ci(
name char(1)
)charset utf8 collate utf8_general_ci;
-- 插入数据
insert into my_collate_bin values('a'),('A'),('b'),('B');
insert into my_collate_ci values('a'),('A'),('b'),('B');
-- 比较:根据某个字段进行排序:order by 字段名 [asc/desc]
select * from my_collate_bin order by name;
select * from my_collate_ci order by name;
使用不同的校对集生成的表排序方式完全不同

使用不同的校对集生成的表排序方式完全不同

校对集必须再数据之前生成好,如果生成好了则对数据无效。

七、Web 乱码问题

动态网站由浏览器、Apache服务器、数据库服务器三个部分组成,三个部分都有自己的字符集(中文),数据在这三者之间的传递很容易导致乱码。

如何解决???

觉得还不错?帮我赞助点域名费吧:)