本文的阅读前提:已经安装了MySQL数据库。安装过程,当做一个小习题,大家自行查阅资料解决。

SQL(Structured Query Language)是一种专门用来与数据库通信进而操作数据库的语言。与其他语言不一样,SQL由很少的词构成,其目的是提供一种从数据为中读写数据的简单有效的方法。

几乎所有重要的DBMS都支持SQL,一次学习此语言使你几乎能与所有关系型数据库打交道。国际标准化组织制定了SQL标准,统一了操作方式。

SQL分为四种:

a.DDL(Data Definition Language):数据定义语言,用来定义数据库对象:库、表、列等。主要由create、alter、drop和truncate四个关键字完成。

b.DML(Data Manipulation Language):数据操作语言,用来定义数据库记录(数据)。主要由insert、updata和delete三个关键字完成。

c.DCL(Data Control Language):数据控制语言,用来定义用户访问权限和安全级别。主要由grant、revoke两个关键字完成

d.DQL(Data Query Language):数据查询语言,select,用来查询记录(数据)。

数据库中的数据类型(列类型):

tinyint、smallint、mediumint、int、bigint。分别代表的数值范围是:1字节/2字节/3字节/4字节/8字节整数,又可分为有符号和无符号两种。

范围(有符号)

范围(无符号)

(-128,127)

(-32 768,32 767)

(0,65 535)

(-8 388 608,8 388 607)

(0,16 777 215)

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

(0,4 294 967 295)

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

(0,18 446 744 073 709 551 615)

MySQL 以一个可选的显示宽度指示器的形式对 SQL 标准进行扩展,这样当从数据库检索一个值时,可以把这个值加长到指定的长度。例如: INT(6)可以保证所包含数字少于 6位的值从数据库中检索出来时能够自动地用空格填充。如果使用ZEROFILL约束,则不使用空格填充,使用0进行填充(没必要使用)。默认宽度是11位。需要注意的是,使用一个宽度指示器不会影响字段的大小和它可以存储的值的范围。

2)浮点型

MySQL 支持的三个浮点类型是 FLOAT、DOUBLE 和 DECIMAL 类型。

非精度浮点数:

MySQL使用DECIMAL类型去存储对精度要求比较高的数值,比如金额,叫做定点数。decimal在mysql内存是以字符串存储的。声明语法是DECIMAL(M,D),占用字节 M+2 bytes。M是数字最大位数(精度precision),最大38位默认为18;D是小数点右侧数字个数(标度scale),范围不得超过M。

比如:定义DECIMAL(7,3):能存的数值范围是 -9999.999 ~ 9999.999,占用9个字节。

a.123.12 --> 123.120,因为小数点后未满3位,补0。

b.123.1245 --> 123.125,小数点只留3位,多余的自动四舍五入截断。

c.12345.12 --> 保存失败,因为小数点未满3位,补0变成12345.120,超过了7位。严格模式下报错,非严格模式存成9999.999。

精度符点数:

FLOAT 数值类型用于表示单精度浮点数值,而 DOUBLE 数值类型用于表示双精度浮点数值。单精度浮点数float占4字节,float标准语法允许通过FLOAT(M)的形式指定精度,但是这个精度值M只是决定存储大小:0-23与默认不指定效果相同,24-53就变成双精度的DOUBLE了。建议不加长度。

float还有MySQL自己实现的非标准语法FLOAT(M,D),代表最多存储M个数字长度,其中小数点后数字个数为D。效果与 DECIMAL(M,D)很相似。

double 和 float 的区别是double精度高,有效数字16位(float精度7位)。但double消耗内存是float的两倍,占8字节,double的运算速度比float慢得多。float默认能精度到6位有效数字。

3)日期和时间类型

在处理日期和时间类型的值时,MySQL 带有 5 个不同的数据类型可供选择。它们可以被分成简单的日期、时间类型,和混合日期、时间类型。

根据要求的精度,子类型在每个分类型中都可以使用,并且 MySQL 带有内置功能可以把多样化的输入格式变为一个标准格式。

大小(字节)

1000-01-01/9999-12-31

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

时间值或持续时间

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

YYYY-MM-DD HH:MM:SS

混合日期和时间值

1970-01-01 00:00:00/2037

YYYYMMDD HHMMSS

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

DATE、TIME 和 TEAR 类型:

MySQL用DATE和TIME类型存储简单的日期值,使用TIME类型存储时间值。这些类型可以描述为字符串或不带分隔符的整数序列。如果描述为字符串,DATE类型的值应该使用连字号作为分隔符分开,而TIME类型的值应该使用冒号作为分隔符分开。

需要注意的是,没有冒号分隔符的TIME类型值,将会被MySQL理解为持续的时间,而不是时间戳。

MySQL 还对日期的年份中的两个数字的值,或是SQL中为YEAR类型输入的两个数字进行最大限度的通译。因为所有YEAR类型的值必须用4个数字存储。MySQL试图将2个数字的年份转换为4个数字的值。把在00-69范围内的值转换到 2000-2069 范围内。把 70-99 范围内的值转换到 1970-1979 之内。

因此,如果MySQL自动转换后的值并不符合我们的需要,请输入4个数字表示的年份。

DATETIME 和 TIMESTAMP 类型:

除了日期和时间数据类型,MySQL 还支持 DATEYIME 和 TIMESTAMP 这两种混合类型。它们可以把日期和时间作为单个的值进行存储。

如果我们对TIMESTAMP类型的字段没有明确赋值,或是被赋与了null 值。MySQL会自动使用系统当前的日期和时间来填充它。

4)CHAR和VARCHAR

CHAR 类型用于定长字符串,并且必须在圆括号内用一个大小修饰符来定义。这个大小修饰符的范围从0-255。比指定长度大的值将被截短,而比指定长度小的值将会用空格作填补。

CHAR类型的一个变体是VARCHAR类型。它是一种可变长度的字符串类型,并且也必须带有一个范围在0-255之间的指示器。

CHAR和VARCHGAR不同之处在于MYSQL数据库处理这个指示器的方式:CHAR把这个大小视为值的大小,长度不足的情况下就用空格补足。而VARCHAR类型把它视为最大值并且只使用存储字符串实际需要的长度(增加一个额外字节来存储字符串本身的长度)来存储值。所以短于指示器长度的 VARCHAR 类型不会被空格填补,但长于指示器的值仍然会被截短。

因为 VARCHAR 类型可以根据实际内容动态改变存储值的长度,所以在不能确定字段需要多少字符时使用 VARCHAR 类型可以大大地节约磁盘空间、提高存储效率。

选择建议:

a)char。char存储定长数据很方便,char字段上的索引效率高,比如定义char(10),那么不论你存储的数据是否达到了10个字节,都要占去10个字节的空间。

b)varchar。存储变长数据,但存储效率没有char高。如果一个字段可能的值是不固定长度的,我们只知道它不可能超过10个字符,把它定义为varchar(10)是最合算的。varchar类型的实际长度是它的值的实际长度+1。为什么“+1”呢?这一个字节用于保存实际使用了多大的长度。

从空间上考虑,用varchar合适;从效率上考虑,用char合适,关键是根据实际情况找到权衡点。

binary,定长二进制字符串类型,它以二进制形式保存字符串

varbinary,可变长度的二进制字符串类型,它以二进制形式保存字符串

5)TEXT 和 BLOB 类型

对于字段长度要求超过255个的情况下,MySQL提供了TEXT和BLOB两种类型。根据存储数据的大小,它们都有不同的子类型。这些大型的数据用于存储文本块或图像、声音文件等二进制数据类型。

TEXT和BLOB类型在分类和比较上存在区别。BLOB类型区分大小写,而TEXT不区分大小写。大小修饰符不用于各种BLOB和TEXT子类型。

比指定类型支持的最大范围大的值将被自动截短。

但是,我们一般不会把文件存储在数据库中,数据库中只存储文件所在本地路径,根据路径在本地中使用文件。

DCL(Data Control Language)

1) 创建用户

CREATE USER 用户名@地址 IDENTIFIED BY ‘密码’

CREATE USER lili@localhost IDENTIFIED BY ‘lili’

a) username:你将创建的用户名

b) host:指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost,如果想让该用户可以从任意远程主机登陆,可以使用通配符%

c)       password:该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器

2)授予权限

GRANT 权限1,权限2,,,ON 数据库.* TO 用户名

GRANT CREATE,DROP,SELECT ON student.* TO lili@localhost;

3)撤消权限

REVOKE 权限1,权限2,,,ON 数据库.* FROM 用户名

REVOKE CREATE,DROP,SELECT ONT student.* FROM lili@localhost;

4)查看用户权限

SHOW GRANTS FOR 用户名

SHOW GRANTS FOR lili@localhost;

5)删除用户

DROP USER 用户名

DROP USER lili@localhost;

6)修改用户密码

UPDATE USER SET PASSWORD=PASSWORD(‘password’) WHERE User=’username’and Host=”ip”;

7)更新权限

FLUSH PRIVILEGES;

注:当mysqld启动时,所有的权限都会被加载到内存中。

如果使用GRANT/REVOKE/SET PASSWORD/RENAME USER命令来更改数据库中的权限表,mysqld服务器将会注意到这些变化并立即加载更新后的权限表至内存中,即权限生效;

如果使用INSERT/UPDATE/DELETE语句更新权限表,则内存中的权限表不会感知到数据库中权限的更新,必须重启服务器或者使用FLUSH PRIVILEGES命令使更新的权限表加载到内存中,即权限需在重启服务器或者FLUSH PRIVILEGES之后方可生效

DDL(Data Definition Language)

1)数据库的基本操作

a.查看所有数据库名称:SHOW DATABASES;

b.切换数据库: USE DATABASENAME;

2)操作数据库

a.创建数据库:

CREATE DATABASE [IF NOT EXISTS] 数据库名称;

IF NOT EXISTS,保证当创建数据库时,会查看是否此数据库存在,避免报错。

b.删除数据库:

DROP DATABASE [IF EXISTS] 数据库名称;

IF EXISTS,保证当数据库不存在时删除不会报错。

c. 修改数据库编码:

ALTER DATABASE 数据库名称 CHARACTER SET 编码格式

修改数据库的编码,注意MySQL中UTF8编码如此写,没有“—”

3)表操作

1.创建表

CREATE TABLE 表名(

列名  列类型,

列名  列类型

)default charset = utf8mb4;   //声明字符集为tuf8mb4

CREATE TABLE student(

stu_id int,

stu_name varchar(30),

stu_gender varchar(5)

)default charset = utf8mb4;

2.查看表结构

a.查看当前数据库中所有表名称:SHOW TABLES;

b.查看指定表的创建语句:SHOW CREATE TABLE stu,查看stu表的创建语句;

c.查看表结构:DESC stu,查看stu表结构;

3.修改表结构

a.增加列

ALTER TABLE student ADD (

classNum varchar(50)

b.修改列类型

ALTER TABLE student MODIFY name CHAR(20);

修改列定义每次只能对一个列进行修改,修改列时,列名在原表中必须存在。如果数据表中已有数据记录,则修改列定义非常容易失败,因为可能修改的列定义规则与原有的数据记录不符合。如果修改数据列的默认值,则只会对以后的插入操作有作用,对以前已经存在的数据不会有任何影响。

c. 修改列名

ALTER TABLE student CHANGE 原列名 新列名 列类型;

d.删除列

ALTER TABLE student DROP原列名;

从数据表中删除列定义通常总是可以成功,删除列定义时会同时删除每行中该列的数据,并释放该列在数据块中占用的空间,可能会占用较长时间。

e.修改表名

ALTER TABLE stu RENAME TO student1;

f.删除表

DROP TABLE stu;

删除表后,表结构、数据、索引、约束全部消失。

g.删除表全部数据

TRUNCATE TABLE stu;

只能一次性删除整个表的全部记录,但保留表结构。

DML(Data Manipulation Language)

1) 插入数据

INSERT INTO用于向指定数据表中插入记录,每次只能插入一条记录。

a.插入所有列

INSERT INTO student(id,name,age,gender) VALUES (‘1001,’zhangsan’,23,’男’);

b.插入部分列,没有插入的默认为NULL值

INSERT INTO student(id,name) VALUES (‘1001,’zhangsan’);

c.插入所有列但不指定列名,必须插入的顺序与数据库表中字段顺序一致

INSERT INTO student VALUES (1001,’zhangsan’,’男’);

INSERT INTO student VALUES (1001,null,24,’男’);

表名后括号中是列名,VALUES后是要插入的数据,如果省略了列名,则默认在所有列中插入值。不确定的列可以赋值为null。

在标准 SQL 中,字符串使用的是单引号。如果字符串本身也包括单引号,则使用两个单引号(注意,不是双引号,字符串中的双引号不需要另外转义)。

可以使用select * from student;查看所有表数据

2) 修改数据

UPDATE 表名 SET  colum=value,colnum1=value1 WHERE…

update用于修改数据表的记录,每次可修改多条记录,并可通过where添加修改条件。可一次性修改多列。where后添加条件,当满足where后条件的列才会进行列数据修改。

条件(条件可选的):

a. 条件必须是一个boolean类型的值或表达式:

UPDATE t_person SET gender='男', age=age+1 WHERE sid='1';

b.运算符:

=、!=、<>、>、<、>=、<=、BETWEEN...AND、IN(...)、IS NULL、NOT、OR、AND

3)删除数据

DELETE FROM table_name WHERE….

删除满足条件的行。如果不指定条件则是删除整个表中的数据。