MySQL数据库笔记

数据库分类和常见数据库

• 关系型数据库和非关系型数据库
  关系型： 采用关系模型（二维表）来组织数据结构的数据库
  非关系型： 不采用关系模型组织数据结构的数据库
• 开源数据库和非开源数据库
  开源：MySQL、SQLite、MongoDB
  非开源：Oracle、DB2、SQL_Server
• 常见的关系型数据库
  MySQL、Oracle、SQL_Server、DB2 SQLite

认识关系型数据库和MySQL

1. 数据库结构
   数据元素 --> 记录 -->数据表 --> 数据库
2. 数据库概念解析
   数据表（table） ： 存放数据的表格
   字段（column）： 每个列，用来表示该列数据的含义
   记录（row）： 每个行，表示一组完整的数据
3. MySQL特点

• 是开源数据库，使用C和C++编写
• 能够工作在众多不同的平台上
• 提供了用于C、C++、Python、Java、Perl、PHP、Ruby众多语言的API
• 存储结构优良，运行速度快
• 功能全面丰富

SQL语句

什么是SQL：
结构化查询语言(Structured Query Language)，一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
SQL语句使用特点

• SQL语言基本上独立于数据库本身
• 各种不同的数据库对SQL语言的支持与标准存在着细微的不同
• 每条命令必须以 ; 结尾
• SQL命令（除了数据库名和表名）可以不区分字母大小写

建立数据库和数据表

数据库操作

1. 查看已有库

show databases;

2. 创建库

# 创建stu数据库，编码为utf8
create database stu character set utf8;
create database stu charset=utf8;

3. 查看创建库的语句
   show create database 库名;

# 查看stu创建方法
show create database stu;

4. 查看当前所在库

select database();

5. 切换库

# use 库名;使用stu数据库
use stu;

6. 删除库

# drop database 库名;删除test数据库
drop database test;

7. 库名的命名规则

• 数字、字母、下划线,但不能使用纯数字
• 库名区分字母大小写
• 不能使用特殊字符和mysql关键字

数据表的管理

表结构设计初步

【1】 分析存储内容
【2】 确定字段构成
【3】 设计字段类型

数据类型支持

1. 数字类型：

• 整数类型（精确值） - INTEGER，INT，SMALLINT，TINYINT，MEDIUMINT，BIGINT
• 定点类型（精确值） - DECIMAL
• 浮点类型（近似值） - FLOAT，DOUBLE
• 比特值类型 - BIT
  对于精度比较高的东西，比如money，用decimal类型提高精度减少误差。列的声明语法是DECIMAL(M,D)。
  M是数字的最大位数（精度）。其范围为1～65，M 的默认值是10。
  D是小数点右侧数字的数目（标度）。其范围是0～30，但不得超过M。
  比如 DECIMAL(6,2)最多存6位数字，小数点后占2位,取值范围-9999.99到9999.99。
  比特值类型指0，1值表达2种情况，如真，假

2. 字符串类型：

• CHAR和VARCHAR类型
• BLOB和TEXT类型
• ENUM类型和SET类型
  char 和 varchar
  char：定长，效率高，一般用于固定长度的表单提交数据存储，默认1字符
  varchar：不定长，效率偏低 ，但是节省空间。
  text 和blob
  text用来存储非二进制文本
  blob用来存储二进制字节串
  enum 和 set
  enum用来存储给出的一个值
  set用来存储给出的值中一个或多个值

3. 表的基本操作
   创建表
   create table 表名(
   字段名 数据类型,
   字段名 数据类型,
   …
   字段名 数据类型
   );

• 如果你想设置数字为无符号则加上 unsigned
• 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
• DEFAULT 表示设置一个字段的默认值
• AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
• PRIMARY KEY 关键字用于定义列为主键。主键的值不能重复,且不能为空。

# 创建班级表
create table class_1 (
id int primary key auto_increment,
name varchar(32) not null,
age tinyint unsigned not null,
sex enum('w','m'),
score float default 0.0);
# 创建兴趣班表
create table interest (
id int primary key auto_increment,
name varchar(32) not null,
hobby set('sing','dance','draw'),
level char not null,price decimal(6,2),
remark text);

# 查看数据表
show tables；
# 查看已有表的字符集
show create table 表名;
# 查看表结构
desc 表名;
# 删除表
drop table 表名;

数据操作基础

插入(insert)

insert into 表名 values(值1),(值2),...;
insert into 表名(字段1,...) values(值1),...;
e.g:
insert into class_1 values (2,'Baron',10,'m',91),(3,'Jame',9,'m',90);

查询(select)

select * from 表名 [where 条件];
select 字段1,字段2 from 表名 [where 条件];
e.g:
select * from class_1;
select name,age from class_1;

where子句

where子句在sql语句中扮演了重要角色，主要通过一定的运算条件进行数据的筛选
MySQL 主要有以下几种运算符：

• 算术运算符
• 比较运算符
• 逻辑运算符

算数运算符

select * from class_1 where age % 2 = 0;

比较运算符

select * from class_1 where age > 8;
select * from class_1 where between 8 and 10;
select * from class_1 where age in (8,9);

逻辑运算符

select * from class_1 where sex='m' and age>9;

更新表记录(update)

update 表名 set 字段1=值1,字段2=值2,… where 条件;
注意:update语句后如果不加where条件,所有记录全部更新

update class_1 set age=11 where name='Abby';

删除表记录（delete）

delete from 表名 where 条件;
注意:delete语句后如果不加where条件,所有记录全部清空

delete from class_1 where name='Abby';

表字段的操作(alter)

语法 ：alter table 表名 执行动作;

• 添加字段(add)
  alter table 表名 add 字段名 数据类型;
  alter table 表名 add 字段名 数据类型 first;
  alter table 表名 add 字段名 数据类型 after 字段名;
• 删除字段(drop)
  alter table 表名 drop 字段名;
• 修改数据类型(modify)
  alter table 表名 modify 字段名 新数据类型;
• 修改字段名(change)
  alter table 表名 change 旧字段名 新字段名 新数据类型;
• 表重命名(rename)
  alter table 表名 rename 新表名;

alter table interest add tel char(11) after name;

时间类型数据

时间和日期类型:
日期DATE，日期时间DATETIME，时间戳TIMESTAMP
时间TIME
年份YEAR

时间格式

date ：“YYYY-MM-DD”
time ：“HH:MM:SS”
datetime ：“YYYY-MM-DD HH:MM:SS”
timestamp ：“YYYY-MM-DD HH:MM:SS”
注意
1、datetime ：以系统时间存储
2、timestamp ：以标准时间存储但是查看时转换为系统时区，所以表现形式和datetime相同

create table marathon (
id int primary key auto_increment,
athlete varchar(32),
birthday date,
registration_time datetime,
performance time);

日期时间函数

• now() 返回服务器当前日期时间,格式对应datetime类型
• curdate() 返回当前日期，格式对应date类型
• curtime() 返回当前时间，格式对应time类型

时间操作

查找操作

select * from marathon where birthday>='2000-01-01';
select * from marathon where birthday>="2000-07-01" and performance<="2:30:00";

高级查询语句

模糊查询和正则查询

LIKE用于在where子句中进行模糊查询，SQL LIKE 子句中使用百分号 %来表示任意0个或多个字符，下划线_表示任意一个字符。
使用 LIKE 子句从数据表中读取数据的通用语法：
SELECT field1, field2,…fieldN
FROM table_name
WHERE field1 LIKE condition1

mysql> select * from class_1 where name like '