MySQL学习第二天—操作数据库\数据表命令

一、登录数据库

mysql -h 127.0.0.1 -u 用户名 -p
mysql -D 所选择的数据库名 -h 主机名 -u 用户名 -p
mysql> exit # 退出 使用 “quit;” 或 “\q;” 一样的效果
mysql> status;  # 显示当前mysql的version的各种信息
mysql> select version(); # 显示当前mysql的version信息
mysql> show global variables like 'port'; # 查看MySQL端口号

二、基本数据库操作命令

1. 操作数据库

1.1 创建数据库

create database [if not exists] 数据库名

例子：

-- 创建一个名为 samp_db 的数据库，数据库字符编码指定为 gbk
create database samp_db character set gbk;

1.2 删除数据库

drop database [if exists] 数据库名

例子：

drop database samp_db; -- 删除库名为samp_db的库

1.3 查看数据库

show databases; -- 显示数据库列表。

1.4 使用数据库

use 数据库名

1.5 其他操作

show tables;     -- 显示samp_db下面所有的表名字
describe 表名;    -- 显示数据表的结构
delete from 表名; -- 清空表中记录

2.操作数据表

2.1 创建数据表

create table [if not exists] `表名`(
    '字段名1' 列类型 [属性][索引][注释],
    '字段名2' 列类型 [属性][索引][注释],
    #...
    '字段名n' 列类型 [属性][索引][注释]
)[表类型][表字符集][注释];

例子：

-- 创建user_accounts数据库表，COMMENT内容表示用户表信息
CREATE TABLE `user_accounts` (
  `id`             int(100) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户号',
  `password`       varchar(32)       NOT NULL DEFAULT '' COMMENT '用户密码',
  `reset_password` tinyint(32)       NOT NULL DEFAULT 0 COMMENT '用户类型：0－不需要重置密码；1-需要重置密码',
  `mobile`         varchar(20)       NOT NULL DEFAULT '' COMMENT '手机',
  `create_at`      timestamp(6)      NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `update_at`      timestamp(6)      NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
)ENGINE=INNODB DEFAULT CHARSET=utf8

2.2 数据类型

图片来自https://www.runoob.com/mysql/mysql-data-types.html

数值类型：

字符串类型

日期和时间型数值类型

2.3 数据类型的属性解释

• NULL：数据列可包含NULL值；
• NOT NULL：数据列不允许包含NULL值；
• DEFAULT：默认值；
• PRIMARY KEY：主键；
• AUTO_INCREMENT：自动递增；通常设置主键，适用于整数类型；可定义初始值和步长；

当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)

• UNSIGNED：是指数值类型只能为正数；
• CHARACTER SET name：指定一个字符集；
• COMMENT：对表或者字段说明；
• ZEROFILL：0填充的；不足位数的用0来填充 , 如int(3),5则为005；

例子：

-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段)
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
-- 创建表之前 , 一定要先选择数据库
 
CREATE TABLE IF NOT EXISTS `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` datetime DEFAULT NULL COMMENT '生日',
  `address` varchar(100) DEFAULT NULL COMMENT '地址',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 
-- 查看数据库的定义
SHOW CREATE DATABASE school;
-- 查看数据表的定义
SHOW CREATE TABLE student;
-- 显示表结构
DESC student;  -- 设置严格检查模式(不能容错了)SET sql_mode='STRICT_TRANS_TABLES';

2.4 数据表的类型

• 设置数据表的类型

CREATE TABLE 表名(
   -- 省略一些代码
   -- Mysql注释
   -- 1. # 单行注释
   -- 2. /*...*/ 多行注释
)ENGINE = MyISAM (or InnoDB)

-- 查看mysql所支持的引擎类型 (表类型)
SHOW ENGINES;

• MyISAM和InnoDB区别：
  MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为InnoDB。

1、 InnoDB 支持事务，MyISAM 不支持。
2、 InnoDB 支持外键约束，而 MyISAM 不支持。
3、 InnoDB不支持FULLTEXT类型的全文索引（但是innodb可以使用sphinx插件支持全文索引）MyISAM支持全文索引。
4、 InnoDB 支持数据行锁定，MyISAM 不支持。
5、 InnoDB 表空间较大，MyISAM 表空间较小。

• 适用场合 ：

适用 MyISAM : 节约空间及相应速度
适用 InnoDB : 安全性 , 事务处理及多用户操作数据表

• 数据表的存储位置 ：
  MySQL数据表以文件方式存放在磁盘中，Mysql安装目录\data\下存放数据表 . 目录名对应数据库名 , 该目录下文件名对应数据表 。
• MyISAM和InnoDB类型数据表：

InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的ibdata1文件
MyISAM类型数据表对应三个文件 : FRM、MYD、MYI文件

---