一、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) 数据库对象。
最基本的数据库对象是数据表,数据表是存储数据的逻辑单元。但数据库里绝不仅包括数据表,数据库里可包含如下表所示的几种常见的数据库对象。
对象名称 | 对应关键字 | 描述 |
---|---|---|
表 | Table | 表是存储数据的逻辑单元,以行和列的形式存在;列就是字段,行就是记录 |
数据字典 | 就是系统表,存放数据库相关信息的表。系统表里的数据通常由数据库系统维护,程序员通常不应该手动修改系统表及系统表数据,只可查看系统表数据 | |
约束 | Constraint | 执行数据校验的规则,用于保证数据完整性的规则 |
视图 | View | 一个或者多个数据表里数据的逻辑显示。视图并不存储数据 |
索引 | Index | 用于提高查询性能,相当于书的目录 |
函数 | Function | 用于完成一次特定的计算,具有一个返回值 |
存储过程 | Procedure | 用于完成一次完整的业务处理,没有返回值,但可通过传出参数将多个值传给调用环境 |
触发器 | Trigger | 相当于一个事件监听器,当数据库发生特定事件后,触发器被触发,完成相应的处理 |
因为存在以上几种数据库对象,所有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类型的快。
参照表:
数据类型 | 字节长度 | 范围或用法 |
---|---|---|
bit | 1 | 无符号[0,255],有符号[-128,127],备注:BIT和BOOL布尔型都占用1字节 |
TinyInt | 1 | 整数[0,255] |
SmallInt | 2 | 无符号[0,65535],有符号[-32768,32767] |
MediumInt | 3 | 无符号[0,2^24-1],有符号[-2^23,2^23-1]] |
Int | 4 | 无符号[0,2^32-1],有符号[-2^31,2^31-1] |
BigInt | 8 | 无符号[0,2^64-1],有符号[-2^63 ,2^63 -1] |
Float(M,D) | 4 | 单精度浮点数。这里的D是精度,如果D<=24则为默认的FLOAT,如果D>24则会自动被转换为DOUBLE型。 |
Double(M,D) | 8 | 双精度浮点。 |
Decimal(M,D) | M+1或M+2 | 未打包的浮点数,用法类似于FLOAT和DOUBLE,如果在ASP中使用到Decimal数据类型,直接从数据库读出来的Decimal可能需要先转换成Float或Double类型后再进行运算。 |
Date | 3 | 用来存储没有时间的日期。Mysql获取和显示这个类型的格式为“YYYY-MM-DD”。支持的时间范围为“1000-00-00”到“9999-12-31”,如:2020-01-22 |
DateTime | 8 | 以YYYY-MM-DD HH:MM:SS的格式显示,比如:2020-01-22日09:39:22 |
TimeStamp | 4 | 也是存储既有日期又有时间的数据。存储和显示的格式跟Datetime一样。支持的时间范围是“1970-01-01 00:00:01”到“2038-01-19 03:14:07” |
Time | 3 | 以HH:MM:SS的格式显示。比如:09:49:08 |
Year | 1 | 以YYYY的格式显示。比如:2020 |
Char(M) | M | 定长字符串。 |
VarChar(M) | M | 变长字符串,要求M<=255 |
Binary(M) | M | 类似Char的二进制存储,特点是插入定长不足补0 |
VarBinary(M) | M | 类似VarChar的变长二进制存储,特点是定长不补0 |
TinyText | Max:255 | 大小写不敏感 |
Text | Max:64K | 大小写不敏感 |
MediumText | Max:16M | 大小写不敏感 |
Long Text | Max:4G | 大小写不敏感 |
TinyBlob | Max:255 | 大小写敏感 |
Blob | Max:64K | 大小写敏感 |
MediumBlob | Max:16M | 大小写敏感 |
LongBlob | Max:4G | 大小写敏感 |
Enum | 1或2 | 最大可达65535个不同的枚举值 |
Set | 可达8 | 最大可达64个不同的值 |
使用建议
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之间的差别:
值 | char(4) | 存储需求 | varchar(4) | 存储需求 |
---|---|---|---|---|
'' | ' ' | 4个字节 | '' | 1个字节 |
'ab' | 'ab ' | 4个字节 | 'ab' | 3个字节 |
'abcd' | 'abcd' | 4个字节 | 'abcd' | 5个字节 |
'abcdefgh' | 'abcd' | 4个字节 | 'abcd' | 5个字节 |
请注意上表中最后一行的值只适用不使用严格模式时;如果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约束不会有任何作用。)