数据库入门(mySQL):创建数据库
- 基于JetBrains DataGrip创建数据库、SQL语句创建数据库
- MySQL数据库存储引擎和数据类型
- 创建数据库表及基本操作
- 导出数据库、删除数据库、导入数据库
一、基于JetBrains DataGrip创建数据库、SQL语句创建数据库
1.1 首先确定mySql服务处于开启状态(win10):右击“我的电脑”-“属性”-“系统和安全”-“管理工具”-“服务”;
如果没有显式正在运行:双击-“启动类型:手动”-“服务状态:启动”-“确定”。(IOS系统每次开机都是关闭状态的,具体操作百度的到)
1.2 启动JetBrains DataGrip-database-New(+▼)-Data Source-MySQL:
1.3 必要的输入:Name-Host-Port-User-Password-URL(一般填入host和port会自动添加),然后测试连接通过后-Apply-ok.
测试通过:
创建成功后是这样的:这一步还只是连接到MySQL服务器。
MySQL服务器内会有一些系统数据库,这里只有information_schema可见,可以通过“show database”命令查看所有数据库,一般还会有performance_schema、mysql数据库,新版本中还会有sakila、sys、world服务系统数据库。
开启DataGrip源代码编辑器:?? (source editor:快捷键Ctrl+b),编辑命令查看数据库:
#查看所有数据库
show databases;
执行SQL源代码:?(Execute:快捷键Ctrl+Enter):
通过show databases命令可以查看到所有服务器系统,这些数据库是用来管理服务的:
information_schema:用于存储系统中的一些数据库对象信息,如用户信息、列信息、权限信息、字符集信息和分区信息等。
performance_schema:主要存储数据库服务器性能参数。
mysql:主要存储系统的用户权限信息。
sakila:样例数据库,可以通过下载官方提供的数据库样例学习分析mySQL数据的使用,了解更多细节可以参考这篇博客:Mysql 的示例数据库 Sakila 介绍。官方地址:https://dev.mysql.com/doc/sakila/en/。
sys:这个数据库的数据都是来源performance_schema库,为了简化performance_schema,让DBA更快的了解DB的运行情况。了解详细可以参考这篇博客:MYSQL的SYS数据库。
world:这是一个世界地区信息数据库,其中包含了三个表:国家(country)、城市( city)、国家语言(countrylanguage),官方文档:Setting Up the world Database,可以在控制台查询国家的基本信息:
有些老版本的mySQL数据库会有一个test数据库,这是一个测试数据库,有了sakila和world替代test在新版本中也就没有这个数据库了。
注意:如果是使用免安装包安装的mysql数据库只有四个系统数据库:information_schema、mysql、performance_schema、sys。其实也就是缺少两个测试数据库。
1.4 创建数据库(school)
create database school;
创建数据库名命名规则:
- 不能重名。
- 由字母、数字、下划线、@、#、$、符号组成,字母可以使用大小写。
- 首字符不能是数字、$符号。
- 标识符不允许是MySQL的保留字。
- 不允许空格和特殊字符。
- 长度小于128位。
使用DataGrip示图工具创建数据库:
二、MySQL数据库存储引擎和数据类型
MySQL数据库软件提供了存储引擎的概念,由于存储引擎是以插件的形式没MySQL数据库软件引入,所以可以根据实际应用、实际领域来选择相应的存储引擎。虽然通过存储引擎决定数据库对象表的类型,但是如果像创建表,还需要了解数据类型,因为其决定了表中可以存储数据的类型。
2.1 MySQL软件支持的存储引擎
//查看当前MySQL数据库管理系统支持的存储引擎 show engines
MySQL5.7.28支持的存储引擎:
#字段:解释:
# --Engine:存储引擎名称 # --Support:MySQL支持的存储引擎(yes支持-no不支持-default默认支持) # --Comment:存储引擎的评论 # --Transactions:引擎是否支持事物 # --XA:引擎支持的分布式是否符合XA规范 # --Savepoints:引擎是否支持事物处理中的保存点
配置默认存储引擎:还记得在my.ini配置文件中的default-storage-engine配置项吗?
//通过my.ini配置默认存储引擎 default-storage-engine=INNODB
2.2 MySQL软件默认支持的数据类型
整数类型:
//类型 字节 最小值(有/无符号) 最大值(有/无符号)
int/integer 4 -21474483648/0 2147483647/4294967295
smallint 2 -32768/0 32767/65535
tinyint 1 -128/0 127/255
mediumint 3 -8388608/0 8388607/1677215
bigint 8 -9223372036854775807/0 9223372036854775807/1844674407370955161
浮点数类型:
//类型 字节 最小值 最大值 float 4 ±1.75494351E-38 ±3.402823466E+38
double 8 -2.2250738585072014E-308 -1.7976931348623157E+30
定点数类型:
//类型 字节 最小值 最大值 dec(m,d)/decimal(m,d) M+2 与double相同 与double相同
位类型:
//类型 字节 最小值 最大值 bit 1~8 bit(1) bit(64)
日期和时间类型:
//类型 字节 最小值 最大值 date 4 1000-01-01 9999-12-31
datetime 8 1000-01-01 00:00:00 9999-12-31 23:59:59
timestamp 4 19700101080001 2038年某个时刻
time 3 -835:59:59 838:59:59
year 1 1901 2155
字符串类型:
//类型 字节 描述 char(m) m m为0~255之间的整数
varchar(m) m m为0~65535之间的整
text系列字符串类型:
//类型 字节 描述 tinytext 0~255 值的长度为+2个字节
text 0~65535 值的长度为+2个字节
mediumtext 0~167772150 值的长度为+3个字节
longtext 0~4294967295 值的长度为+4个字节
binary系列字符串类型:
//类型 字节 描述 binary(m) m 允许长度为0~m
varbinary(m) m 允许长度为0~m
blob系列字符串类型:
//类型 字节 描述 tinyblob 0~255 blob 0~216 mediumblob 0~224 longblob 0~232
我们可以通过help指令来查看数据类的帮助信息,其中会包含该数据类型的描述,并且会打印出官方详细帮助文档的连接:
三、创建数据库表及基本操作
3.1 通过DataGrip图形化界面创建数据库表
然后进入表的具体创建操作:
创建完成以后可以通过show指令来查看创建表的完整SQL语句:
show create table users;
比如,测试时我给users表总共添加了id、name、age、phone、password五个字段,下面时查询的完整创建语句:
CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT \'users id\', `name` varchar(32) NOT NULL COMMENT \'user name\', `age` int(3) NOT NULL COMMENT \'user age\', `phone` char(11) NOT NULL COMMENT \'user phone\', `password` char(32) NOT NULL COMMENT \'user password\', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
还可以通过describe来查看表的结构,可以通过这个操作查看到每个字段的信息(describe可以简介desc):
describe users;
这些信息就是刚刚创建表时添加的每个字段的内容:
3.2 通过SQL语句创建表
#创建数据表最基本的语法 create table table_name( 字段名 数据类型, 字段名 数据类型 )
但是你会发现前面通过DataGrip图形化界面创建的users表,查询出来的创建语句每个字段后面还有很多内容,其中主要的就包括约束和一个字段描述。然后整个创建语句最后面也还有一些内容,这部分内容是用来描述表的,比如存储引擎、表的字符集等。
//完整性约束关键字 含义 not null 约束字段的值不能为空 default 设置字段的默认值 unique key(UK) 约束字段的唯一值 primary key(PK) 约束字段为主键,可以作为该表记录的唯一标识 auto_increment 约束字段的值为自动增加 foreign key(FK) 约束字段为表的外键
字段约束的示例:
//非空 create table table_name( 字段名 数据类型 not null, ... ) //设置字段默认值 create table table_name( 字段名 数据类型 default, ... ) //设置唯一约束 create table table_name( 字段名 数据类型 unique L, ... ) //设置主键约束 create table table_name( 字段名 数据类型 primary key, ... ) //设置字段值自动增加 create table table_name( 字段名 数据类型 auto_increment, ... ) //设置外键约束--属性名1表示子表的字段,也就是当前表的字段--属性名2是父表的字段 create table table_name( constraint 外键约束名 foreign key (属性名1) references 表名 (属性名2) )
表的相关描述配置:
CREATE TABLE `users` (
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
engine=innoDB用来设置表的存储引擎,一般常用的的存储引擎有两个:myisam、innoDB。myisam适用于读较多的场景,因为myisam是表级锁。innoDB适用于读写都较多的场景,因为innoDB是行级锁。
default用来设置默认配置,charset=utf8mb4表示默认字符集为utf8mb4。
例如通过SQL语句实现一个商品购物车表:
1 create table shopping_cart( 2 `id` int(11) not null auto_increment comment \'shopping_cart id\',#订单id:非空 自增 3 `commodity_id` int(11) not null ,#商品编号 4 `count` int(11) not null default 1,#订购数量 5 `add_time` datetime not null,#添加到购物车的时间 6 `settle_time` datetime,#结算的时间 7 `money` float not null default 0,#订单金额 8 `address_id` int(2) default 0,#订单收货地址编号 9 PRIMARY KEY (`id`), #设置主键 10 constraint fk_shoppingCart_address foreign key (address_id) 11 references address(id),#设置订单的收获地址信息外键 12 constraint fk_shoppingCart_commodity foreign key (commodity_id) 13 references commodity(id)#设置订单对应的商品信息外键 14 )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
需要注意的是,带有外键的表在执行添加表格的SQL之前,必须将父表都先添加好才能添加带有外键的数据表。
3.3通过SQL语句添加字段、删除字段、修改字段、修改表名
添加字段:
1 //在表的最后一个位置增加字段 2 alter table table_name add 字段名 字段类型 ...约束; 3 //在表的第一个位置增加字段 4 alter table table_name add 字段名 字段类型 ...约束 flrst; 5 //在表的指定位置增加字段(通过after关键字指定在某个字段后面添加) 6 alter table table_name add 字段名 字段类型 ...约束 after 字段名; 7 8 //示例:在表commodity的最后一个位置添加字段type,类型为int(11),非空约束 9 alter table commodity add type int(11) not null;
删除字段:
alter table table_name drop 属性名;
修改字段:
//修改字段的数据类型 alter table table_name modify 字段名 数据类型 //修改字段的名字 alter table table_name change 旧字段名 新字段名 旧数据类型 //修改字段的名字和属性(属性以数据类型为例) alter table table_name change 旧字段名 新字段名 新数据类型
修改字段的顺序:
//比如将字段1调整到字段名2后面 alter table table_name modify 字段名1 数据类型 first | after 字段名2
修改表名:
//将数据表old_table_name的表名修改成new_table_name,TO关键字可选 alter table old_table_name rename [TO] new_table_name
四、导出数据库、删除数据库、导入数据库
3.1 导出数据库
通过控制台命令导出:
//导出整个数据库 mysqldump -u 用户名 -p 数据库名 > 导出的文件名 //导出一个表 mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名 //导出数据库结构 mysqldump -u 用户名 -p -d --add-drop-table 数据库名 >导出文件名
示例:
//将MySQL数据库nodemysql1导出到桌面,导出文件名为nodemysql1.sql ...我的桌面路径>MySQLdump -u root -p nodemysql1 >nodemysql1.sql //将MySQL数据库nodemysql1的users表导出到桌面,导出文件名为nodemysql1users.sql ...我的桌面路径>MySQLdump -u root -p nodemysql users>nodemysql1users.sql //将MySQL数据库nodemysql1的数据库结构导出到桌面,导出文件名为nodemysql_d.sql ...我的桌面路径>MySQLdump -u root -p -d --add-drop-table nodemysql1>nodemysql_d.sql
3.2 删除数据库
drop database database_name;
3.3 导入数据库
//如果没有进入到sql文件exported_file.sql目录中需要在前面加上文件目录 mysql -u root -p 数据库名称 <exported_file.sql C:\Users\...\Desktop>mysql -u root -p nodemysql1 <C:\Users\...\Desktop\nodemysql_d.sql