一、SQL操作

1.使用传统的cmd指令进入mysql

1) 本地连接       mysql -u user_name （-p）
2）远程连接       mysql -u user_name -h(ip_name) (-p)

2. 用户管理（root权限下mysql数据库的user表）(\G  格式化输出)

1）创建用户        create user user_name（@ip_address） identified by password
2）删除用户        drop user user_name(@ip_address)
3）修改用户        rename user old_user_name@ip_name to new_user_name@ip_name
4）修改密码        set password for user_name@ip_namedo = password('new_password')

3.建表

create table table_name(
    column_name1  datatype ( 大部分一样  / auto_increment )
    ...
)engine = InnoDB default charset = utf8

4.重命名表

alter table old_table_name rename new_table_name

5.root默认没有密码，重设root密码

mysqladmin -u root -p password new_password

二、SQL语句

1.SQL语句基础

SQL的全称是Structured Query Language,也就是结构化查询语言。SQL是操作和检索关系数据库的标准语言，标准的SQL语句可用于操作任何关系数据库。

使用SQL语句，程序员（主要1,2,3功能）和数据库管理员(DBA)（4,5功能）可以完成如下任务。

1)在数据库中检索信息。

2)对数据库的信息进行更新。

3)改变数据库的结构。

4)更改系统的安全设置。

5)增加或回收用户对数据库、表的许可权限。

标准的SQL语句通常可分为如下几种类型。

1)  查询语句: 主要由select关键字完成，查询语句是 SQL语句中最复杂、 功能最丰富的语句。

2)  DML (Data Manipulation Language,数据操作语言)语句:主要由insert、update 和delete三个关键字完成。

3)  DDL (Data Definition Language, 数据定义语言)语句:主要由create、alter、 drop 和tuncate四个关键字完成。

4)  DCL (Data Control Language,数据控制语言)语句:主要由grant和revoke两个关键字完成。

5)  事务控制语句:主要由commit、rollback 和savepoint三个关键字完成。

SQL语句的关键字不区分大小写，也就是说，create 和CREATE的作用完全一样。在上面5种SQL语句中，DCL语句用于为数据库用户授权，或者回收指定用户的权限，通常无须程序员操作。

在SQL命令中也可能需要使用标识符，标识符可用于定义表名、列名，也可用于定义变量等。这些标识符的命名规则如下：

1)标识符通常必须以字母开头。

2)标识符包括字母、数字和三个特殊字符(# _ $)。

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

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

2.DDL语句

DDL语句是操作数据库对象的语句，包括创建(create)、删除(drop)和修改(alter) 数据库对象。

最基本的数据库对象是数据表，数据表是存储数据的逻辑单元。但数据库里绝不仅包括数据表，数据库里可包含如下表所示的几种常见的数据库对象。

因为存在以上几种数据库对象，所有create后面可以跟上不同的关键字。例如建表使用create table，建立索引使用create index等等。

三、数据类型

1.数字类型

1）整数      tinyint,smallint, mediumint, int, bigint

2）浮点数    float, double, real (real就是double ，如果sql服务器模式包括real_as_float选项，real是float的同义词而不是double的同义词。)

3）定点数    decimal(m,d)  (m 表示十进制数字总的个数, d表示小数点后面数字的位数)

注意：取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~256)。

2.日期和时间

1) date        YYYY-MM-DD

2) time        HH:MM:SS

3) datetime    YYYY-MM-DD HH:MM:SS

4) timestamp   YYYY-MM-DD HH:MM:SS(时间戳:若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。)    

5) year             YYYY

3.字符串类型

1）字符串                            char, varchar

2）文本                              text, tinytext, mediumtext, longtext

3）二进制（可能用来存储、音乐等）      tinyblob, blob, mediumblob, longblob

注意

(char与varchar的区别)：

1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。

2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入3个字符将占用4个字节。

3.char类型的字符串检索速度要比varchar类型的快。

参照表：

使用建议

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

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

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

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

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

选择数据类型的基本原则

前提：使用适合存储引擎。

选择原则：根据选定的存储引擎，确定如何选择合适的数据类型。

下面的选择方法按存储引擎分类：

1.MyISAM 数据存储引擎和数据列：MyISAM数据表，最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。

2.MEMORY 存储引擎和数据列：MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系。两者都是作为CHAR类型处理的。

3.InnoDB 存储引擎和数据列：建议使用 VARCHAR类型。
对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列简单。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。

固定长度数据列与可变长度的数据列的联系

1.char与varchar

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

下面的表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果，说明了CHAR和VARCHAR之间的差别：

请注意上表中最后一行的值只适用不使用严格模式时；如果MySQL运行在严格模式，超过列长度不的值不保存，并且会出现错误。

从CHAR(4)和VARCHAR(4)列检索的值并不总是相同，因为检索时从CHAR列删除了尾部的空格。通过下面的例子说明该差别：

CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
INSERT INTO vc VALUES ('ab  ', 'ab  ');
SELECT CONCAT(v, '+'), CONCAT(c, '+') FROM vc;

2.text和blob

在使用text和blob字段类型时要注意以下几点，以便更好的发挥数据库的性能。

1.BLOB和TEXT值也会引起自己的一些问题，特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下很大的"空洞"，以后填入这些"空洞"的记录可能长度不同,为了提高性能,建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理.

2.使用合成的（synthetic）索引。合成的索引列在某些时候是有用的。一种办法是根据其它的列的内容建立一个散列值，并把这个值存储在单独的数据列中。接下来你就可以通过检索散列值找到数据行了。但是，我们要注意这种技术只能用于精确匹配的查询（散列值对于类似<或>=等范围搜索操作符 是没有用处的）。我们可以使用MD5()函数生成散列值，也可以使用SHA1()或CRC32()，或者使用自己的应用程序逻辑来计算散列值。请记住数值型散列值可以很高效率地存储。同样，如果散列算法生成的字符串带有尾部空格，就不要把它们存储在CHAR或VARCHAR列中，它们会受到尾部空格去除的影响。

合成的散列索引对于那些BLOB或TEXT数据列特别有用。用散列标识符值查找的速度比搜索BLOB列本身的速度快很多。

3.在不必要的时候避免检索大型的BLOB或TEXT值。例如，SELECT *查询就不是很好的想法，除非你能够确定作为约束条件的WHERE子句只会找到所需要的数据行。否则，你可能毫无目的地在网络上传输大量的值。这也是 BLOB或TEXT标识符信息存储在合成的索引列中对我们有所帮助的例子。你可以搜索索引列，决定那些需要的数据行，然后从合格的数据行中检索BLOB或 TEXT值。

4.把BLOB或TEXT列分离到单独的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，可以让你把原数据表中 的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会减少主表中的碎片，使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT *查询的时候不会通过网络传输大量的BLOB或TEXT值。

3.浮点数与定点数

举例：

CREATE TABLE test (c1 float(10,2),c2 decimal(10,2));
insert into test values(2020202.32,2020202.32);
select * from test;

从上面的例子中我们看到c1列的值由2020202.32变成了2020202.38，这就是浮点数的不精确性造成的。

在mysql中float、double（或real）是浮点数，decimal（或numberic）是定点数。

浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围；它的缺点是会引起精度问题。在今后关于浮点数和定点数的应用中，记住以下几点：

1.浮点数存在误差问题；

2.对货币等对精度敏感的数据，应该用定点数表示或存储；

3.编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；

4.要注意浮点数中一些特殊值的处理。

四、MySQL与Oracle的区别

1.物理方面的区别

1）Oracle是大型数据库而Mysql是中小型数据库，Oracle市场占有率达40%，Mysql只有20%左右，同时Mysql是开源的而Oracle价格非常高

2）Oracle支持高并发，大访问量，是OLTP最好的工具

3）安装所用的空间差别也是很大的，Mysql安装完后才152M而Oracle有3G左右，且使用的时候Oracle占用特别大的内存空间和其他机器性能

2. 操作上的区别

(1)主键

（1）Mysql一般使用自动增长类型，在创建表时只要指定表的主键为auto increment,插入记录时，不需要再指定该记录的主键值，Mysql将自动增长

（2）Oracle没有自动增长类型，主键一般使用的序列，插入记录时将序列号的下一个值付给该字段即可；只是ORM框架是只要是native主键生成策略即可

(2)单引号的处理

（1）MYSQL里可以用双引号包起字符串，ORACLE里只可以用单引号包起字符串。在插入和修改字符串前必须做单引号的替换：把所有出现的一个单引号替换成两个单引号

(3) 翻页的SQL语句的处理

（1）MYSQL处理翻页的SQL语句比较简单，用LIMIT 开始位置, 记录个数；

（2）ORACLE处理翻页的SQL语句就比较繁琐了。每个结果集只有一个ROWNUM字段标明它的位置, 并且只能用 ROWNUM<100, 不能用ROWNUM>80

(4)长字符串的处理

（1）长字符串的处理oracle也有它特殊的地方。INSERT和UPDATE时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型，方法借用 ORACLE里自带的DBMS_LOB程序包

（2）oracle插入修改记录前一定要做进行非空和长度判断，不能为空的字段值和超出长度字段值都应该提出警告,返回上次操作

(5)空字符的处理

（1）MYSQL的非空字段也有空的内容，ORACLE里定义了非空字段就不容许有空的内容

（2）按MYSQL的NOT NULL来定义ORACLE表结构,导数据的时候会产生错误。因此导数据时要对空字符进行判断，如果为NULL或空字符，需要把它改成一个空格的字符串

(6)字符串的模糊比较

（1）MYSQL里用 字段名 like ‘%字符串%’,ORACLE里也可以用 字段名 like ‘%字符串%’ 但这种方法不能使用索引, 速度不快

(7)Oracle实现了ANSII SQL中大部分功能，如，事务的隔离级别、传播特性等而Mysql在这方面还是比较的弱

五、数据库约束

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

1)not null：非空约束，指定某列不能为空。

2)unique：唯一约束，指定某列或者几列不能为空。

3)primary key：主键，指定该列的值可以唯一地标识该条记录(几列是联合主键，一张表只能有一个主键)

4)foreign key：外键，指定该行记录从属于主表中的一条记录，主要用于保证参照完整性

5)check：检查，指定一个布尔表达式，用于指定对应列的值必须满足该表达式(MySQL不支持CHECK约束，虽然MySQL的SQL语句也可以使用CHECK约束，但这个CHECK约束不会有任何作用。)