学会MySQL数据库【上】

前言

MySQL被设计为一个可移植的数据库，具有性价比高，使用灵活，支持良好和应用广泛的特点。本篇面向MySQL数据库管理系统初学者，介绍了MySQL中最常用的内容。

目录

操作数据库和数据表

数据库 

 数据表

约束 

数据类型和运算符

数据类型

运算符

算术运算符

比较运算符

逻辑运算符

位运算符

运算符的优先级

总结

---

操作数据库和数据表

数据库 

1. 创建数据库 ：create database 数据库名；
2. 查看数据库：
   show database;-- 查看所有数据库
   show create database 数据库名；-- 查看指定数据库
3. 删除数据库：drop database 数据库名；
4. 备份数据库：
   1.备份数据库（在DOS执行）命令行：mysqldump -u 用户名 -p -B数据库1 数据库2 数据库n >文件名.sql
   2.恢复数据库(在SQLyog执行）：Source 文件名.sql

#创建一个名称为db01的数据库
-- 1.右键创建数据库（数据库名+utf8+utf8_general_ci)
-- 2.使用指令创建数据库

-- 创建数据库指令
CREATE DATABASE db01
-- 创建一个使用utf8字符集的db02的数据库
CREATE DATABASE db02 CHARACTER SET utf8
-- 创建一个使用utf8字符集，并带校对规则的db03数据库
CREATE DATABASE db03 CHARACTER SET utf8 COLLATE utf8_bin
-- 校对规则 utf8_bin区分大小写 默认utf8_general_ci不区分大小写

# 查看数据库
-- 查看所有数据库
SHOW DATABASE
-- 查看指定的数据库(db01)
SHOW CREATE DATABASE db01

#删除前面创建的db01数据库
DROP DATABASE db01

#备份数据库
-- 要在Dos下执行mysqldump指令（在mysql安装目录\bin)
mysqldump -u root -p -B db02 db03 > d:\\bak.sql
-- 备份库的表
mysqldump -u 用户名 -p密码 数据库 表1 表2 表n > d:\\文件名.sql

#恢复数据库
-- （注：进入Mysql命令行再执行）
-- 方法1.命令行：mysql -u root -p=》source d:\\bak.sql
sorce d:\\bak.sql
-- 方法2.新建一个表，建备份.sql的内容全部复制到查询编辑器中全选执行

# 注：给数据库，表等取名时，可使用`反引号`来规避关键字

 数据表

创建数据表之前，需要使用“USER <数据库名>”指定操作在哪个数据库，没有选择会抛错。

创建表：create table <表名>{字段名1，数据类型[列约束条件][默认值]，... ... [表约束条件]}

#指令创建表
-- 创建数据库
CREATE DATABASE db01;
USE db01;-- 使用该数据库
-- 创建表
CREATE TABLE `user`(
	id INT,
	`name` VARCHAR(255),
	`password` VARCHAR(255),
	`birthday` DATE)
	CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
-- character set字符集 collate校对规则 engine存储引擎
-- field指定列表 datatype指定列类型（字段类型）
-- character set如不指定则为所在数据库字符集
-- collate如不指定则为所在数据库校对规则
-- engine引擎

查看表：DESC 表名；

-- 查看表基本结构
DESCRIBE `user`;
DESC `user`;

-- 查看表详细结构
SHOW CREATE TABLE `user`;
# 在命令终端中，表名后加‘\G’参数会让显示结果更加直观，易于查看

修改表：alter table <旧表名> rename [to] <新表名>;

# 修改

-- 修改表名
ALTER TABLE `user` RENAME TO tb1;
-- 修改字段的数据类型
ALTER TABLE tb1 MODIFY `name` CHAR(30);

-- 修改字段名
ALTER TABLE tb1 CHANGE `name` users VARCHAR(20);

-- 添加字段名
-- alter table 表名 add 字段名 数据类型 [约束条件][first |after 已存在字段名]
ALTER TABLE tb1 ADD class INT AFTER users;

-- 删除字段
ALTER TABLE tb1 DROP class
-- 修改字段的排列位置
ALTER TABLE tb1 MODIFY users VARCHAR(20) FIRST;

-- 更改表的存储引擎
ALTER TABLE db1 ENGINE=INNODB;

删除表:drop table[IF EXISTS] 表1，表2....；

DROP TABLE `user`;

注意：删除的时候要考虑清楚，一经删除，所有数据都没了。。。 

约束 

主键约束：用于唯一的标识表行的数据，当定义主键约束后，该列不能重复

基本语法：字段名 字段类型 primary key

使用细节：
1. primary key 不能重复并且不能为null；
2.一张表中最多只能由一个主键，但可以是复合主键

# 方式一：
CREATE TABLE t1(id INT PRIMARY KEY,-- 表示id列是主键
		`name` VARCHAR(32),
		email VARCHAR(32))
#方式二：
CREATE TABLE t3(id INT,
		`name` VARCHAR(32)PRIMARY KEY,
		emial VARCHAR(32)
		PRIMARY KEY(`name`))

外键约束：外键是用来在两个表的数据之间建立链接，可以是一行，也可以是多列。

基本语法：
[constraint<外键名>]foreign key 字段名1[，字段名2，...]
references <主表名>主键列1[主键列2，...]

使用细节：

1. 外键指向的表的字段要求是primary key或unique；
2. 表的类型是innodb才支持外键；
3. 外键字段的类型必须要和主键字段的类型一致（长度可以不同）
4. 外键字段的值必须在主键字段中出现过，或者为null（前提是外键字段允许为null）；
5. 一旦建立主外键的关系，数据不能随意删除；

#foreign key（外键）
-- 创建 主表 class
CREATE TABLE class(
id INT PRIMARY KEY, -- 班级编号
name VARCHAR(32) NOT NULL DEFAULT '')

-- 创建 从表 stu
CREATE TABLE stu(
id INT PRIMARY KEY, -- 学生编号
name VARCHAR(32) NOT NULL DEFAULT '',
class_id INT,
-- 指定外键关系
FOREIGN KEY (class_id) REFERENCES class(id))

INSERT INTO class
VALUES(100,'java'),(200,'C++')
SELECT * FROM class

INSERT INTO stu
VALUES(1,'tom',100),(2,'jack',200)
SELECT * FROM stu

INSERT INTO stu(3,'john',300)-- Error,300在主表中不存在

非空约束：如果在列上定义了not null，插入数据时必须为列提供数据；

基本语法：字段名 字段类型 not null

唯一约束：当定义了唯一约束后，该列值是不能重复的

基本语法：字段名 字段类型 unique

使用细节：
1.没有指定not null，则unique可以有多个null（unique not null使用效果类似于主键primary key)
2. 一张表可以有多个unique字段

默认约束：指定某列是默认值

基本语法：字段名  数据类型  default 默认值 

CREATE TABLE t22(
	id INT UNIQUE, -- 表示id列不能重复
	`name` VARCHAR(32) NOT NULL,-- 表示name列不能为空
	class INT DEFAULT 1 -- 表示class默认为1
	)
INSERT INTO t22(id,`name`) VALUES(1,'mary')

SELECT * FROM t22

check

用于强制行数据必须满足的条件 

使用细节：Oracle和sql server均支持check ，但是mysql5.7目前还不支持check，只做语法校验，但不会生效

设置表的属性值自动增长

基本语法：字段名 数据类型 auto_increment;

使用细节：

• 自增长一般和primary key配合使用，单独使用需要配合unique
• 自增长默认从1开始，通过alter命令可修改

CREATE TABLE t3(
	id INT PRIMARY KEY AUTO_INCREMENT,-- 自增长
	`name` VARCHAR(32),
	sex VARCHAR(6) CHECK (sex IN('boy','girl')),
	sal DOUBLE CHECK(sal>3000 AND sal<4000))
	

INSERT(1,'jack','mid',1)-- Error，第三个参数必须是boy或girl

INSERT INTO t3 (`name`,sex,sal)VALUES('jack','boy',2500) 
 -- id 默认为1，下次插入数据，id将自动+1
	

数据类型和运算符

数据类型

MySQL支持多种数据类型，主要有数值类型，日期/时间类型和字符串类型

MySQL数据类型

 数值类型

# 演示整型
-- tinyint演示范围 有符号-128~127，没有符号0~255
-- 没有指定unsigned，则TINYINT默认有符号
CREATE TABLE t4(
	id TINYINT UNSIGNED); -- 无符号
CREATE TABLE t3(
	id TINYINT,
    num INT(11)); -- int后面的11表示该数据类型指定的显示宽度，插入位数小于显示宽度以空格填充

二进制类型

#数值型(bit)的使用
-- bit(m) m在1-64，显示为二进制
-- 查询时仍然按照十进制数来查询
CREATE TABLE t1 (num BIT(8));
INSERT INTO t1 VALUES(255);
SELECT * FROM t1 WHERE num=255;

字符串类型

 使用细节：

1. 存放的数据是指字符，表示字节。
2. char(size)是固定的长度，会造成空间浪费，但是查询速度快
3. varchar(size)是变长，空间大小是实际占用空间分配（实际数据大小+存放内容长度1-3个字节）

#CHAR固定长度字符串 最大255字符
#VARCHAR（size） 0~65535字节，可变长度字符串 最大65532字节
#表的编码是utf8 varchar(size) size=(65535-3)/3=21844
CREATE TABLE t5(
	`name`CHAR(255));
CREATE TABLE t6(
	`name` VARCHAR(21844))

日期类型

#演示时间相关的类型
CREATE TABLE t7(
	birthday DATE , -- 生日
	job_time DATETIME , -- 记录年月日 时分秒
	login_time TIMESTAMP 
		NOT NULL DEFAULT CURRENT_TIMESTAMP 
		ON UPDATE CURRENT_TIMESTAMP); -- 登录时间（自动更新）

enum类型 【只能取单个值】
（字符串对象）语法格式（字段名 enum（’值1’，’值2’….））

set类型【可取多个值】
与enum类型相同，插入重复set列值就会自动删除重复的值

CREATE TABLE t9( enm ENUM('first','second','third'));
INSERT INTO t9 VALUES('first');

CREATE TABLE t10( s SET('a','b','c','d'));
INSERT INTO t10 VALUES('a'),('a,b,c'),('c,a,d');

运算符

算术运算符

算术运算符用于各类数值运算，包括：加（+）减（-）乘（*）除（/）求余（%）。

注：数学运算中除数为0是无意义的，故返回结果的是NULL。

比较运算符

一个比较运算符的结果总是1、0或NULL，比较运算符经常在SELECT的查询条件子句中使用，用来查询满足条件的记录。

运算符	作用
=	等于
<=>	安全的等于（区别：可以判断NULL值）
<>  (!=)	不等于
<=	小于等于
>=	大于等于
>	大于
IS NULL	判断一个值是否为NULL
IS NOT NULL	判断一个值是否不为NULL
LEAST	在有两个或多个参数时，返回最小值
GREATEST	当有两个或多个参数时，返回最大值
BETWEEN AND	判断一个值是否落在两个值之间
ISNULL	与 IS NULL的作用相同
IN	判断一个值是IN列表中的任意一个值
NOT IN	判断一个值不是IN列表中的任意一个值
LIKE	通配符匹配【%：匹配任何数目的字符；_:只能匹配一个字符】
REGEXP	正则表达式匹配

逻辑运算符

在SQL中，所有逻辑运算符的求值所得结果均为TRUE、FALSE或NULL。

运算符	作用
NOT或者！	逻辑非[0→1;非零值→0;NULL→NULL]
AND或者&&	逻辑与[均为不为NULL的非零值→1;一个或多个操作数为0→0;其余为NULL]
OR或者||	逻辑或[非NULL且任一个非零值→1,否则为0；一个为NULL，另一个非零→1,否则为NULL；两个均为NULL→NULL]
XOR	逻辑异或[任一为NULL→NULL；非NULL：均为非零值或零值→ 0；一零值一非零：1]

位运算符

位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数编程二进制数，然后进行位运算，最后将计算结果从二进制转成十进制数。

运算符	作用
|	位或
&	位与
^	位异或
<<	位左移
>>	位右移
~	位取反，反转所有比特

运算符的优先级

运算符按优先级由低到高排列

优先级	运算符
最低	=（赋值运算符）、：=
	||、OR
	XOR
	&&、AND
	NOT
	BETWEEN、CASE、WHEN、THEN、ELSE
	=（比较运算符）、<=>、>=、>、<=、<、<>、!=、IS、LIKE、REGEXP、IN
	|
	&
	<<、>>
	-、+
	*、/（DIV）、%（MOD）
	＾
	－（负号）、～（位取反）
最高	！

无法确定优先级的情况下，可以使用圆括号“（）”来改变优先级。

总结

本篇详细介绍了ＭｙＳＱＬ数据库的创建、查看、删除、备份；数据表的创建、查看、修改、删除、以及数据表的约束；几种数据类型以及运算符。感谢您垂阅此文，敬请斧正。