MySQL的使用

常用MySQL服务命令

连接mysql格式：
mysql -h主机地址-u用户名－p用户密码

• 修改密码格式：mysqladmin -u用户名 -p旧密码password 新密码
• 数据库备份格式：mysqldump –u 用户名 –p 密码 数据库名 > backup.sql
• 数据库还原格式：mysql –u 用户名 –p 密码 数据库名 < backup.sql
• 创建数据库: create database 数据库名称 default character set utf8;
  create database test default character set utf8;
  创建成功后，在对应的安装路径下出现一个data/test文件夹
• 选定默认数据库：use dbname;
• 查看当前的数据库：select database();
• 显示所有数据库：show databases;
• 显示默认数据库中所有表：show tables;
• 创建新表： create table 表名称(列名称 数据类型 约束规则,…) engine=数据库引擎

default charset utf8;
mysql> create table t_student(
 -> id bigint primary key,
 -> name varchar(20) not null,
 -> sex boolean default 1
 -> ) engine=innodb default charset utf8;

• 创建表结束后则会发现文件夹data/test/下出现一个文件t_student.ibd
• 查看表中的列定义
  简单方法： desc t_student;
  查看具体的列定义
  show create table t_student;
• 放弃正在输入的命令：\c
• 显示命令清单：\h
• 退出mysql程序：\q 或 exit、quit
• 查看MySQL服务器状态信息：\s

Mysql已存数据库

• information_schema – 系统数据库
  schemata – 可以通过此表获取已经有数据库
  tables – 可通过此表获知某数据库的所有表
• mysql-系统数据库
• test – 非系统数据库,测试使用
• SELECT * FROM TABLES WHERE TABLE_SCHEMA=‘mysql‘是查询mysql数据库中的所有表

常见的SQL命令

• SQL语言都基于SQL92标准，但各数据库之间又存在差异。目前最新的标准为SQL99，它同样兼容SQL92。
• SQL-Structured QueryLanguage结构化查询语言。
• DDL – 数据定义语言Data Definition Language
  建库、建表、设置约束等：create\drop\altertruncate
• DCL – 数据控制语言 Data Controll Language
  数据授权，收回授权。grant\revoke
• DQL - 数据查询语言select
• DML－数据操纵语言Data Manipulation Language
  主要指数据的增删改。delete\update\insert\call

mysql –uroot –p1234 //登录
mysql>show databases; //显示所有已经存在的数据库
mysql>use test; //使用某个数据库
mysql>show tables; //显示当前数据库中的所有表
mysql>help; //显示
mysql>desc tableName; //显示表结构
导出数据库中的所有表：在命令行模式下：
shell/> mysqldump –uroot –p1234 –d dbName > d:\a\a.sql
导入：导入时，必须要先手工建立一个新的数据库：
mysql>use dbName;
Mysql>source d:\a\a.sql或mysql –uroot –p1234 –D dbname < d:\a\a.sql (注意参数-Ｄ是大写)

SQL语言的动词

SQL 对大小写不敏感；
分号是在数据库系统中分隔每条SQL 语句的标准方法

SQL的数据定义语句

数据库涉及字符规范（不是语法硬性规则）

• 采用26个英文字母和0-9这十个自然数,加上下划线’_'组成,共63个字符.不能出现其他字符(注释除外)
• 以上命名都不得超过30个字符的系统限制.变量名的长度限制为29(不包括标识字符@)
• 数据对象、变量的命名都采用英文字符,禁止使用中文命名.绝对不要在对象名的字符之间留空格create table t 1(id int);
• 小心保留词,要保证你的字段名没有和保留词、数据库系统或者常用访问方法冲突
• 保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性.假如数据类型在一个表里是整数,那在另一个表里可就别变成字符型

MySQL 的数据类型

数值列类型

mysql 提供了五种整型： tinyint、smallint、mediumint、int 和 bigint。int 为 integer 的缩写。这些类型在可表示的取值范围上是不同的。
整数列可定义为 unsigned 从而禁用负值；这使列的取值范围为 0 以上。各种类型的存储量需求也是不同的。取值范围较大的类型所需的存储量较大。
mysql 提供三种浮点类型： float、double 和 decimal。

4 种用户定义约束

• null 允许为空，默认/not null 不允许为空 id int not null 表示 id 列不允许为空，如果插入 null 值则报错
• default 设置默认值，如果试图插入 null，则加入 null，默认值无效；如果不针对当前列进行操作，
  则默认值生效

create table t1(id int default 1,name varchar(20));
Insert into t1 values(null,'ss'); 

此时 id 插入 null值。
如果 insert into t1(name) values(‘ys’s’)则默认值生效

• check 检查型约束，可以定义，语法 create table t1(id int check(id>5)); 要求插入的数据必须满足条件 id>5，但是在 mysql 中无效
• unique 唯一性约束，表示列中的数据不允许重复，否则报错，拒绝操作
  create table t1(id int unique); 允许为 null，null 表示不确定的值

字符串列类型

在 mysql 中使用单引号表示字符串类型，事实上不区分单双引号，但是一般建议使用单引号，允许使用\转义字符

• create table t1(id char(2)); 表示 id 为字符串类型，允许最大存储 2 个字符，不区分中英文
• char(2)表示存储的字符个数为 2，如果实际长度大于 2 则报错；如果实际存放的字符数不足，则自动末尾补充空格。

varchar(2)表示存储的字符个数最大为 2，存放字符数不足时按照实际长度存放如果存放字符串末尾是空格时，会自动裁剪到满足长度限制为止，注意不能裁剪开头的和中间的空格等值判定时空格不参与
char 与 varchar 的区别

1、char 是定长数据，如果 char(10)但里面只保存了 1 个字符，则它的实际长度仍然是 10。Mysql 对查询的结果进行了处理 ，所以使用 length 无法判断它所占用的长度；varchar(10)时，如果里面只保存了 1 个字符，则它的长度为 1，所以 varchar 被称了可变长字符
2、char 的取值范围为 0-255；varchar 的取值范围为 0-65535。如果需要存储的字符串的长度跟所有值的平均长度相差不大，适合用 char，如 MD5。 对于经常改变的值，char 优于 varchar，原因是固定长度的行不容易产生碎片。
3、对于很短的列，char 优于 varchar，原因是 varchar 需要额外一个或两个字节存储字符串的长度
4、char 和 varchar 后面如果有空格，char 会自动去掉空格后存储，varchar 虽然不会去掉空格，但在进行字符串比较时，会去掉空格进行比较。

大对象类型

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB和 LONGBLOB。它们只是可容纳值的最大长度不同。
有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。这些对应 4 种 BLOB 类型，有相同的最大
长度和存储需求TINYTEXT 可变长度最多 255 个字符；TEXT 可变长度最多 65535 个字符；MEDIUMTEXT 可变长度最多 16M个字符；LONGTEXT 可变长度，最多 4G 个字符

set 和枚举

enum 枚举类型实际上是当作字符串进行处理，设置当前列中所允许的取值，取值只能是枚举值中的任意一个值

create table t1(id enum(‘a’,’b’,’c’));

set 集合类型实际上是当作字符串进行处理，设置当前列中所允许的取值，取值可以是集合中的任意多个值

create table t1(id set(‘a’,’b’,’c’));

日期和时间列类型

• date 类型只能存放日期，datetime 可以存放日期和时间
• datetime 一般供用户使用，而 timestamp 可以存放 1970-2038 年的日期和时间，一般供系统使用

总结

• 定义数据类型就是定义列，数据类型决定数据的特性
• 数据类型主要分为字符串类型，浮点书类型和定点数类型，日期类型以及二进制类型
• 数据类型在不同的存储引擎上表现不同
• 根据所采用的数据类型，需求和数据特性选择数据类型

删除操作 drop

• 删除数据库 DROP DATABASE <数据库名>;

drop database test;

• 删除数据表 DROP TABLE <表名>;

drop table t_users;

基本表删除数据、表上的索引都删除，表上的视图往往仍然保留，但无法引用
删除基本表时，系统会从数据字典中删去有关该基本表及其索引的描述（标准中没有，认为表建立后就永久存在）

修改基本表 alter

修改表 ALTER TABLE <表名>
[ ADD <新列名> <数据类型> [ 完整性约束] ]
[ DROP <完整性约束名> ]
[ MODIFY <列名> <数据类型> ]；
说明：

• <表名>：要修改的基本表
• ADD 子句：增加新列和新的完整性约束条件
• DROP 子句：删除指定的完整性约束条件
• MODIFY 子句：用于修改列名和数据类型