数据库简介
- 数据库
数据库就是存储数据的仓库。为了方便管理数据的存储和管理，将数据按照特定的规律存储在磁盘上。通过数据库管理系统，有效地组织和管理存储在数据库中的数据 - 数据库系统
数据库系统和数据库不是一个概念，数据库系统（DBS）比数据库大很多，由数据库、数据库管理系统、应用开发工具构成。 - 数据库管理系统
数据库管理系统（Database Management System，简称DBMS），是用来定义数据、管理和维护数据的软件。他是数据库系统的一种重要的组成部分。
常见的数据库系统
- 甲骨文Oracle数据库
- BM的DB2
- 微软SQL Server、Access
- PostgreSql
- MySQL
MySQL数据库
- MySQL是开放源代码的数据库
- MySQL的跨平台性
- 开源免费
- 功能强大使用方便
SQL
SQL：Structured Query Language简称SQL，结构化查询语言，数据库管理系统通过SQL语言来管理数据库中的数据
SQL语言的组成部分
- DDL(Data Defination Language)：数据定义语言，主要用于定义数据库、表、视图、索引和触发器等。像DROP、CREATE、ALTER等语句
- DML(Data Manipulation Language)：主要包括对数据的增删改。INSERT插入数据、UPDATE更新数据、DELETE删除数据
- DQL(Data Query Language)：数据检索语句，用来从表中获得数据，确定数据怎样在应用程序中给出，像SELECT查询数据
- DCL(Data Control Language)：数据控制语言，主要用于控制用户的访问权限。像GRANT、REVOKE、COMMIT、ROLLBACK等语句
数据库的安装及配置
MySQL目录
- bin目录，存储可执行文件
- data目录，存储数据文件
- include目录，存储包含的头文件
- lib目录，存储库文件
- docs目录，文档
- share目录，错误消息和字符集文件
- my.ini文件，MySQL的配置文件
设置字符集
客户端字符集[mysql]
default-character-set=utf8
服务器端字符集[mysqld]
character-set-server=utf8
开启和关闭MySQL服务
方法一
net start mysql
net stop mysql
方法二
右键计算机–管理–服务和应用程序–服务–找到MySQL–点击启动或关闭
登录MySQL
mysql -uroot -p
常用参数
- -u 用户名
- -p 密码
- -h 服务器名称
- -P 端口号
- -D 打开指定数据库
- –prompt=name,设置命令提示符
- –delimiter=name,指定分隔符
- -V,–version,输出版本信息并且退出
mysql -hlocalhost -uroot -p -P3306 --prompt=sql
退出
- exit
- quit
- \q
SQL语句规范
修改命令提示符
- 连接客户端时通过参数指定
mysql -uroot -proot --prompt 提示符 - 连接上客户端后，通过prompt命令修改
prompt 提示符 - 命令提示符常用参数
\D:完整的日期
\d:当前数据库
\h:服务器名称
\u:当前用户名
prompt \h~\u~\D~\d
5.MySQL常用命令
SELECT VERSION(); 显示当前版本
SELECT NOW(); 显示当前日期时间
SELECT USER(); 显示当前用户
\c 取消命令
DELIMITER 更改分隔符
\T 接路径，开始输出日志
\t 结束输出日程
6.MySQL语句的规范
- 关键字与函数名称全部大写
- 数据库名称、表名称、字段名称等全部小写
- SQL语句必须以分隔符结尾
- SQL语句支持折行操作，只要不把单词、标记或引号字符串分割为两部分，可以在下一行继续写
- 数据库名称、表名称、字段名称等尽量不要使用MySQL的保留字，如果需要使用的时候需要使用反引号(``)将名称括起来
数据库操作(DDL)
创建数据库
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [[DEFAULT] CHARACTER SET [=] charset_name]
{}必须
[]可选
SHOW WARNINGS 查看上一次操作产生的警告信息
注意
数据库名称在一个目录名允许的任何字符，除斜线或点等特殊字符，也不要起中文，最大长度64
创建一个数据库，相当于在data目录中创建一个目录
当已经存在数据库时再创建相同的数据库会提示error，可加上IF NOT EXISTS判断是否存在后再创建，这时语句可以执行，但是会提示存在warning，用SHOW WARNINGS可以查看上一次操作产生的警告信息
查看当前服务器下的数据库列表
SHOW {DATABASES|SCHEMAS}
其中information_schema，performance_schema，mysql这三个库是不可以删除的
查看指定数据库的定义
SHOW CREATE {DATABASE|SCHEMA} db_name
能查看到编码方式
修改指定数据库的编码方式
ALTER {DATABASE|SCHEMA} db_name [DEFAULT] CHARACTER SET [=] charset_name
打开指定数据库
USE db_name
SELECT DATABASE(); 可以得到当前打开数据库的名称
删除指定数据库
DROP {DATABASE|SCHEMA} [IF EXISTS] db_name
MySQL中支持的数据类型简介
数据表相关操作
什么是表：
数据表是数据库最重要的组成部分之一，是其它对象的基础
数据表是存储数据的数据结构
数据表是包含了特定实体类型的数据
数据表由行(row)和列(column)构成的二维网络
数据表一定先有表结构，再有数据
数据表至少有一列，可以没有行或者多行
数据表名称要求唯一，而且不要包含特殊字符
如何创建数据表：
CREATE TABLE [IF NOT EXISTS] tbl_name(
字段名称 字段类型 [完整性约束条件]
...
)ENGINE=引擎名称 CHARSET='编码方式';
完整性约束条件
- PRIMARY KEY主键
- AUTO_INCREMENT自增长
- FOREIGN KEY外键
- NOT NULL非空
- UNIQUE KEY唯一
- DEFAULT默认值
查看帮助手册
help tinyint
help CREATE DATABASE
? INT
\h INT
数据类型
- 整数类型
TINYIINT
SMALLINT
MEDIUMINT
INT
BIGINT
BOOL, BOOLEAN 等价于TINYINT(1) - 浮点类型
FLOAT[(M,D)]
DOUBLE[(M,D)]
DECIMAL[(M,D)] - 字符串类型
CHAR(M)
VARCHAR(M)
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT
ENUM(‘value1’,‘value2’,…)
SET(‘value1’,‘value2’,…) - 日期时间类型
TIME
DATE
DATETIME
TIMESTAMP
YEAR - 二进制类型
如何选择合适的数据类型
整数类型和浮点数类型
如果要表示小数只能用浮点数类型，整数类型不能表示小数
浮点类型DOUBLE精度比FLOAT类型高，如果需要精确到10位以上，就应该选择DOUBLE类型。
MySQL中的存储引擎
存储引擎
存储引擎就是指表的类型。数据库的存储类型决定了表在计算机中的存储方式。用户可以根据不同的存储方式、是否进行事务处理等来选择合适的存储引擎。
如何查看MySQL的存储引擎?
查看MySQL支持的存储引擎：SHOW ENGINES
查看显示支持的存储引擎信息：SHOW VARIABLES LIKE ‘have%’
查看默认的存储引擎：SHOW VARIABLES LIKE ‘storage_engine’
MySQL常用存储引擎
- InnoDB存储引擎
提供事物处理（回滚，修复，多版本并发控制）
支持外键
读写效率低，占用空间大 - MyISAM存储引擎
插入快，空间存储低 - MEMORY存储引擎
数据存储在内存中，处理快
MySQL数据表的创建
创建数据表
CREATE TABLE [IF NOT EXISTS] tbl_name(
字段名称 字段类型 [完整性约束条件]
...
)ENGINE=引擎名称 CHARSET='编码方式';
完整性约束条件
- PRIMARY KEY主键
- AUTO_INCREMENT自增长
- FOREIGN KEY外键
- NOT NULL非空
- UNIQUE KEY唯一
- DEFAULT默认值
需要输入中文时，需要临时转换客户端编码方式：SET NAME GBK;
字段注释，通过COMMENT 注释内容 给字段添加注释
-- 创建学员表(user)
-- 编号id
-- 用户名username
-- 年龄age
-- 性别sex
-- 邮箱email
-- 地址addr
-- 生日birth
-- 薪水salary
-- 电话tel
-- 婚姻状况married
CREATE TABLE IF NOT EXISTS user(
id SMALLINT,
username VARCHAR(20),
age TINYINT,
sex ENUM('男','女','保密'),
email VARCHAR(50),
addr VARCHAR(200),
birth YEAR,
salary FLOAT(8,2),
tel INT,
married TINYINT(1) COMMENT '0代表未婚，非0代表已婚'
)ENGINE= INNODB CHARSET=UTF8;
-- 创建课程表course
-- 编号cid
-- 课程名称courseName
-- 课程描述 courseDesc
CREATE TABLE IF NOT EXISTS course(
cid TINYINT,
courseName VARCHAR(50),
courseDesc VARCHAR(200)
);
查看数据库中的数据表以及表结构
查看数据库下的数据表
SHOW TABLES
查看指定表的表结构
DESC tbl_name
DESCRIBE tbl_name
SHOW COLUMNS FROM tbl_name
测试数据类型
测试整型
CREATE TABLE TEST1(
NUM1 TINYINT,
NUM2 SMALLINT,
NUM3 MEDIUMINT,
NUM4 INT,
NUM5 BIGINT
);
DESC TEST1;
-- 向表中插入记录INSERT TBL_NAME VALUE|VALUES(值,...);
INSERT TEST1 VALUES(-128, -32768, -8388608, -2147483648, -9223372036854775808);
-- 查询表中所有记录SELECT * FROM TBL_NAME;
SELECT * FROM TEST1;
-- INSERT TEST1 VALUES(-129, -32768, -8388608, -2147483648, -9223372036854775808);
-- 报错Out of range value for column 'NUM1' at row 1
测试无符号UNSIGNED
CREATE TABLE TEST2(
NUM1 TINYINT UNSIGNED,
NUM2 TINYINT
);
DESC TEST2;
-- 向表中插入记录INSERT TBL_NAME VALUE|VALUES(值,...);
INSERT TEST2 VALUES(0, -12);
-- 查询表中所有记录SELECT * FROM TBL_NAME;
SELECT * FROM TEST2;
-- INSERT TEST2 VALUES(-10, -12);
-- 报错Out of range value for column 'NUM1' at row 1
测试零填充ZEROFILL
显示美观，长度一致
CREATE TABLE TEST3(
NUM1 TINYINT ZEROFILL,
NUM2 SMALLINT ZEROFILL,
NUM3 MEDIUMINT ZEROFILL,
NUM4 INT ZEROFILL,
NUM5 BIGINT ZEROFILL
);
DESC TEST3;
-- 向表中插入记录INSERT TBL_NAME VALUE|VALUES(值,...);
INSERT TEST3 VALUES(1,1,1,1,1);
-- 查询表中所有记录SELECT * FROM TBL_NAME;
SELECT * FROM TEST3;
测试浮点类型
CREATE TABLE TEST4(
NUM1 FLOAT(6,2),
NUM2 DOUBLE(6,2),
NUM3 DECIMAL(6,2)
);
DESC TEST4;
-- 向表中插入记录INSERT TBL_NAME VALUE|VALUES(值,...);
INSERT TEST4 VALUES(3.1415,3.1415,3.1415);
INSERT TEST4 VALUES(3.2495,3.2495,3.2495);
-- 查询表中所有记录SELECT * FROM TBL_NAME;
SELECT * FROM TEST4;
SELECT * FROM TEST4 WHERE NUM1 = 3.14;
SELECT * FROM TEST4 WHERE NUM2 = 3.14;
SELECT * FROM TEST4 WHERE NUM3 = 3.14;
SELECT * FROM TEST4 WHERE NUM1 = '3.14';
SELECT * FROM TEST4 WHERE NUM3 = '3.14';
decimal精度较高，内部以字符串形式存储，不涉及四舍五入
测试字符串类型
CREATE TABLE IF NOT EXISTS test5(
str1 CHAR(5),
str2 VARCHAR(5)
);
INSERT test5 VALUES('1','1');
INSERT test5 VALUES('12345','12345');
SELECT * FROM test5;
长度超过无法插入
INSERT test5 VALUES('123456','123456');
-- Data too long for column 'str1' at row 1
INSERT test5 VALUES('1 ','1 ');
INSERT test5 VALUES(' a',' a');
SELECT * from test5;
CHAR类型和VARCHAR类型和TEXT类型
- CHAR定长字符串，占用空间大，速度快
- VARCHAR变长字符串，占用空间小，速度慢
- TEXT类型是一种特殊的字符串类型。只能保存字符数据，而且不能
- 它们3个存储和检索数据的方式都不一样，数据检索的效率CHAR>VARCHAR>TEXT
- CHAR在保存的时候，后面会用空格填充到指定的长度，在检索的时候后面的空格会去掉
- VARCHAR在保存的时候，不进行填充。当值保存和检索时尾部的空格仍保留
测试枚举类型
CREATE TABLE IF NOT EXISTS test6(
sex ENUM('男','女','保密 ')
);
INSERT test6 VALUES('男');
INSERT test6 VALUES('女');
INSERT test6 VALUES('保密');
SELECT * FROM test6;
INSERT test6 VALUES('保密1');
--[Err] 1265 - Data truncated for column 'sex' at row 1
INSERT test6 VALUES(2);
SELECT * FROM test6;
INSERT test6 VALUES(0);
--[Err] 1265 - Data truncated for column 'sex' at row 1
INSERT test6 VALUES(NULL);
SELECT * FROM test6;
INSERT test6 VALUES('');
--[Err] 1265 - Data truncated for column 'sex' at row 1
测试集合类型
CREATE TABLE IF NOT EXISTS test7(
fav SET('A','B','C','D')
);
INSERT test7 VALUES('A,C,D');
INSERT test7 VALUES('D,B,A');
SELECT * FROM test7;
集合类型是以二进制形式来保存的
INSERT test7 VALUES(3);
INSERT test7 VALUES(15);
SELECT * FROM test7;
测试日期时间类型
测试YEAR
CREATE TABLE IF NOT EXISTS test8(
birth YEAR
);
INSERT test8 VALUES(1901);
INSERT test8 VALUES(2155);
SELECT * FROM test8;
超过范围的值
INSERT test8 VALUES(2156);
-- [Err] 1264 - Out of range value for column 'birth' at row 1
字符串也可以插入；
00-69转换为2000-2069,70-99转换为1970-1999；
插入数字0转换为0000，插入字符串0转换为2000；
INSERT test8 VALUES('1988');
INSERT test8 VALUES(12);
INSERT test8 VALUES('79');
INSERT test8 VALUES(0);
INSERT test8 VALUES('0');
SELECT * FROM test8;
测试TIME
CREATE TABLE IF NOT EXISTS test9(
test TIME
);
INSERT test9 VALUES('1 12:12:12');
INSERT test9 VALUES('11:11');
INSERT test9 VALUES(1234);
INSERT test9 VALUES(12);
INSERT test9 VALUES('0');
INSERT test9 VALUES(0);
SELECT * FROM test9;
超过范围
INSERT test9 VALUES(66);
-- [Err] 1292 - Incorrect time value: '66' for column 'test' at row 1
测试DATE
CREATE TABLE IF NOT EXISTS test10(
test DATE
);
INSERT test10 VALUES('12-6-7');
INSERT test10 VALUES('12/6/7');
INSERT test10 VALUES('120607');
INSERT test10 VALUES('12@6/7');
SELECT * FROM test10;
714
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
