MYSQL数据库基础

MYSQL基本命令

查看当前实例下包含多少个数据库

show databases;

删除指定数据库

drop database 数据库名；

创建新数据库

create database [if not exists] 数据库名；

进入指定数据库

use 数据库名；

查询该数据库下包含多少数据表

show tables;

查看指定数据表的表结构

desc 表名；

启动MYSQL命令行客户端

mysql -p 密码 -u 用户 -h 主机名；（为了保证较好的安全性，省去-p后的密码，运行该命令时会提示输入密码）

转载http://blog.csdn.net/jiemushe1814/article/details/4716069

MYSQL常见数据类型

数值类型

类型

大小

范围（有符号）

范围（无符号）

用途

TINYINT

1 字节

(-128，127)

(0，255)

小整数值

SMALLINT

2 字节

(-32 768，32 767)

(0，65 535)

大整数值

MEDIUMINT

3 字节

(-8 388 608，8 388 607)

(0，16 777 215)

大整数值

INT或INTEGER

4 字节

(-2 147 483 648，2 147 483 647)

(0，4 294 967 295)

大整数值

BIGINT

8 字节

(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)

(0，18 446 744 073 709 551 615)

极大整数值

FLOAT

4 字节

(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)

0，(1.175 494 351 E-38，3.402 823 466 E+38)

单精度
浮点数值

DOUBLE

8 字节

(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)

0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)

双精度
浮点数值

DECIMAL（DEC）

对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2

依赖于M和D的值

依赖于M和D的值

小数值

 

字符串类型

类型

大小

用途

CHAR

0-255字节

定长字符串

VARCHAR

0-255字节

变长字符串

TINYBLOB

0-255字节

不超过 255 个字符的二进制字符串

TINYTEXT

0-255字节

短文本字符串

BLOB

0-65 535字节

二进制形式的长文本数据

TEXT

0-65 535字节

长文本数据

MEDIUMBLOB

0-16 777 215字节

二进制形式的中等长度文本数据

MEDIUMTEXT

0-16 777 215字节

中等长度文本数据

LOGNGBLOB

0-4 294 967 295字节

二进制形式的极大文本数据

LONGTEXT

0-4 294 967 295字节

极大文本数据

日期和时间类型

类型

大小
(字节)

范围

格式

用途

DATE

3

1000-01-01/9999-12-31

YYYY-MM-DD

日期值

TIME

3

'-838:59:59'/'838:59:59'

HH:MM:SS

时间值或持续时间

YEAR

1

1901/2155

YYYY

年份值

DATETIME

8

1000-01-01 00:00:00/9999-12-31 23:59:59

YYYY-MM-DD HH:MM:SS

混合日期和时间值

TIMESTAMP

8

1970-01-01 00:00:00/2037 年某时

YYYYMMDD HHMMSS

混合日期和时间值，时间戳

复合类型

enum('value1','value2',...)枚举类型，该列的值只能是enum后括号里多个值的其中之一set('value1','value2',...)集合类型，该列的值可以是set后括号里多个值的其中几个

使用建议

1、在指定数据类型的时候一般是采用从小原则，比如能用TINY INT的最好就不用INT，能用FLOAT类型的就不用DOUBLE类型，这样会对MYSQL在运行效率上提高很大，尤其是大数据量测试条件下。

2、不需要把数据表设计的太过复杂，功能模块上区分或许对于后期的维护更为方便，慎重出现大杂烩数据表

3、数据表和字段的起名字也是一门学问

4、设计数据表结构之前请先想象一下是你的房间，或许结果会更加合理、高效

5、数据库的最后设计结果一定是效率和可扩展性的折中，偏向任何一方都是欠妥的

SQL语法基础

常见数据库对象：

table表：列是字段，行是记录

数据字典：就是系统表，存放数据库相关信息的表，程序员一般不用手动改

constraint约束：数据检验规则

view视图：数据的逻辑显示，不储存数据

index索引：为了提高数据查询的性能

function函数：完成特定计算，具有返回值

procedure存储过程：完成特定的业务处理，没有返回值

trigger触发器：相当于一个事件监听器

大部分数据库支持以下五种完整性约束：

not null:非空约束，指定某列不能为空

unique:唯一约束，指定某列或者几列组合不能重复，但可以出现多个null值

primary key：主键约束，指定该列的值可以唯一地标识该条记录，相当于非空和唯一约束，不允许出现null值

foreign key:外键约束，指定改行的记录从属于主表中的一条记录，主要用于保证参照完整性

check:检查，指定一个布尔表达式，用于指定对应列的值必须满足该表达式，MYSQL的check语句无效

约束的作用只是用于保证数据表里数据的完整性，但约束也是数据库对象，并被存储在系统表中，拥有自己的名字。根据约束对数据列限制，可以分为如下两类：

单列约束

多列约束

为数据表指定约束有两个时机

在建表的同时为相应的数据列指定约束

建表后创建，以修改表的方式来增加约束

not null:非空约束

建表时指定非空约束例句

create table hehe 

(

//建立非空约束，意味着hehe_id不可以为null

hehe_id int not null,

//MYSQL的非空约束不能指定名字

hehe_name varchar(255) defalut 'xyz' not null,

//下面列可以为空，默认就是可以为空

hehe_gender varchar(2) null

）；

修改表时增加或删除非空约束例句

//增加非空约束

alter table hehe

modify hehe_gender varchar(2) not null;

//取消

alter table hehe

modify hehe_name varchar(2) not ;

//取消并指定默认值

alter table hehe

modify hehe_name varchar(255) defalut 'abc' null;

unique:唯一约束

创建表时列级唯一约束例句

create table unique_test

(

test_id int not null,

//unique 就是唯一约束，使用列级约束语法建唯一约束

test_name varchar(255) unique

);

创建表时表级唯一约束例句1

create table unique_test2

(

test_id int not null,

test_name varchar(255),

test_pass varchar(255),

//使用表级唯一约束

unique (test_name),

//使用表级唯一约束，并指定约束名

constraint test2_uk unique(test_pass)

);

创建表时表级唯一约束例句2

create table unique_test2

(

test_id int not null,

test_name varchar(255),

test_pass varchar(255),

//使用表级唯一约束，指定两列组合不能为空

constraint test3_uk unique(test_name,test_pass)

);

修改表时表级唯一约束例句1

alter table unique_test3

add unique(test_name,test_pass);

修改表时列级唯一约束例句2

alter table unique_test3

modify test_name varchar(255) unique;

删除唯一约束例句

alter table unique_test3

//MYSQL删除唯一约束方式，大部分数据库使用drop constraint 约束名来删除唯一约束

drop index test3_uk

primary key：主键约束

建表时列级主键约束例句

create table primary_test

(

//建立主键约束

test_id int primary key，

test_name varchar(255)

);

建表时表级主键约束例句1

create table primary_test

(

test_id int not null，

test_name varchar(255),

test_pass varchar(255),

//指定主键约束名为test_pk，对大部分数据库有效，对MYSQL无效

//MYSQL数据中该主键约束名依然是PRIMARY

constraint test2_pk primary key(test_id)

);

建表时表级主键约束例句2

create table primary_test

(

test_name varchar(255),

test_pass varchar(255),

//建立多列组合的主键约束

primary key(test_name, test_pass)

);

删除主键约束例句

alter table primary_test3

drop primary key;

修改表时列级主键约束例句

alter table primary_test3

modify test_name varchar(255) primary key;

修改表时表级主键约束例句

alter table primary_test3

add primary key(test_name, test_pass);

注意：不能连续执行上面两条SQL语句，因为同一个表里最多只能有一个主键约束

指定自增长功能通常用于设置逻辑主键列-该列的值没有任何物理意义，仅仅用于标识每行记录。一旦指定了某列具有自增长特性，则向该表插入记录时可不为该列指定值，该列的值由数据库系统自动生成。

create table primary_test4

(

//建立主键约束，使用MYSQL的自增长

test_id int auto_increment primary key,

test_name varchar(255),

test_pass varchar(255)

);

foreign key:外键约束

标准SQL语句通常分为如下几种类型：

查询语句：主要有select关键字完成。

DDL语句：主要由create，alter，drop，truncate完成。

DML语句：主要由insert，update，delete完成。

DCL语句：grant，revoke这部分主要由DBA（数据库管理员）来完成。

事务控制语句：commit，rollback，savepoint。

SQL语句的关键字不区分大小写。

其实具体编程时用的是select语句和DML语句，别的语句是在数据库设计时就完成的。

SQL命令中的标示符可以用来定义表名、列名、变量等，命名规则如下：

必须以字母开头

可包括字母、数字和三个特殊字符（#_$）

不能使用当前数据库系统的关键字、保留字，建议使用多个单词连缀而成，单词之间以_分隔

同一模式下的对象不应该同名，这里的模式指的是外模式

DDL语句：操作数据库对象的语句。

标准建表语句

create table [模式名.]表名

(

//可以有多个列定义，由列名、列类型、可选的默认值组成，使用default关键字指定默认值

columnName1 datatype [default expr],

...

);

建表例句

create table test

(

test_id int,

//普通长度文本，使用default指定默认值，SQL语句中字符串值使用单引号引起，而不是双引号

test_name varchar(255) default 'xxx'

test_img blod,

);

标准子查询语句

create table [模式名.]表名

[column(,column...)]

as subquery;

子查询例句

//创建haha数据表，该数据表和user_inf完全相同，数据也完全相同。

create table haha

as 

select * from user_inf;

提示：当数据库创建成功后，MYSQL使用information_schema数据库里的TABLES表来保存该数据库实例中所以数据表

标准修改表语句

alter table 表名

add

(

//可以有多个列定义，如果只是新增一列，圆括号可以省去

column_namel datatype[dafault expr],

...

);

修改表例句

//为hehe数据表增加一个hehe_id字段，该字段的类型是int

alter table hehe

add hehe_id int;

//为hehe数据表增加aaa、bbb字段，两个字段的类型是varchar(255)

alter table hehe

add

(

aaa varchar(255) default 'xxx',

bbb varchar(255)

);

增加字段时注意：如果数据表中已有数据记录，除非给增加的值指定了默认值，否则新增的数据列不可指定非空约束，因为那些已有的记录在新增列上肯定是空（实际上：修改表结构很容易失败，只要新增的约束与已有的数据冲突，修改就会失败）

标准修改列语句

alter table 表名

modify column_name datatype [default expr] [first|after col_name];

修改列例句

//将hehe表的hehe_id列修改成varchar(255)类型

alter table hehe

modify hehe_id varchar(255);

//将hehe表的bbb列修改成int类型

alter table hehe 

modify bbb int;

注意：其他数据库如Oracle可以一个modify命令修改多个列定义。

标准删除列语句

alter table 表名

drop column_name

删除列例句

//删除hehe表中的aaa字段

alter table hehe

drop aaa;

MSQL还提供了两种特殊的语法：

标准重命名数据表语句

alter table 表名

rename to 新表名；

重命名数据表例句

//将hehe数据表重命名为wawa

alter table hehe 

rename to wawa;

标准修改列名语句

alter table 表名

changge old_column_name new_column_name type [default|after col_name]；

修改列名例句

//将wawa数据表中的bbb字段重命名为ddd

alter table wawa

change bbb ddd int;

标准删除表语句

drop table 表名；

删除数据表的效果如下：

表结构被删除，表对象不存在

表里所有的数据也被删除

该表所有相关的索引、约束也被删除

truncate被称为“截断”某个表-它的作用是删除该表里的全部数据，但保留表结构。相对于DML里的delete而言，truncate的速度要快得多，truncate只能一次性删除整个表的全部记录。

truncate 表名；