构建Mysql三部曲之二 概念语法

一、基本概念

SQL(Structured Query Language) 结构化查询语言

SQL 语言包含4个部分：

1.数据定义语言(DDL)，例如：CREATE、DROP、ALTER 等语句

2.数据操作语言(DML)，例如：INSERT（插入）、UPDATE（修改）、DELETE（删除）语句

3.数据查询语言(DQL)，例如：SELECT 语句

4.数据控制语言(DCL)，例如：GRANT、REVOKE、COMMIT、ROLLBACK 等语句

MySQL 支持标准的 SQL 语句，也有自己私有的SQL语句。

SQL 数据基本操作分为为创建（create）、更新（update）、读取（read）和删除（delete），即 CURD ，以及在此基础上的更为复杂的一些数据操作。

create：分为 create 和 insert into ，用于创建数据库（表）以及向表里添加数据记录

update：更新数据库（表）信息以及更新表内的具体数据信息记录

read：读取数据库（表）信息以及表内的具体数据信息记录

delete：删除数据库（表）信息以及表内的具体数据信息记录

Mysql只是一种管理数据库的小型管理系统（DBMS），并不是数据库数据本身。

数据库是一个保存有组织的数据的容器，使用DBMS访问数据库。

表是某中特定类型数据的结构化清单。

模式是关于数据库和表的布局及特性信息。

列是表中的一个字段，所有的表都是由一个或多个列组成。

数据类型是所允许存储数据的类型，每个表列都有相应的数据类型，它限制该列中存储的数据。

行是表中的一个记录。

主键是一列或一组列，其值能够唯一区分表中的每个行。

注意事项

1. 在处理SQL语句时，其中所有的空格都会被忽略。

2. mysql语句之后用”;”或”\g”结束，enter不执行命令。

3. 数据库名不能是纯数字。

MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：

1、数据库名与表名是严格区分大小写的；

2、表的别名是严格区分大小写的；

3、列名与列的别名在所有的情况下均是忽略大小写的；

4、变量名也是严格区分大小写的；

二、Mysql数据(字段)类型

在创建表的时候，要明确定义字段对应的数据类型。MySQL 主要的数据类型分为数值类型、字符串（文本）类型、时间日期类型和其他类型几类。

数值类型

数值类型说明：

补充说明

1.在 int（integer）系列中，只能存储整型值，且可以在后面用括号指定显示的尺寸（M），如果不指定则会默认分配。如果实际值的显示宽度大于设定值，将会显示实际值而不会截断以适应显示尺寸。如 smallint(3)中的 3 即为显示尺寸，即显示三位的数值（不包括 - 号）

2.int 类型可以指定 UNSIGNED 属性，即无符号（非负），所以存储范围有两种

3.在 float、double 及 decimal 类型中，不能指定UNSIGNED 属性，其显示尺寸包含了小数点精度（D），即 float(3,1) 保存范围为 -99.9 至 99.9

4.decimal 必须指定显示尺寸（M）和小数点精度（D），float 和 double 都是可选的

5.在可能涵盖取值范围的基础上，尽可能选择较小的类型以提高效率和节约存储空间，如年龄，就选择 tinyint(3) 。该原则对于字符类型同样适用。

字符串（文本）类型

字符串（文本）类型说明：

补充说明

char 和 varcha 需要指定长度，不同的是，char存储时总是按照指定的长度储存，而 varchar 则根据实际字符串长度再加上一个字节分配空间。

时间日期类型

时间日期类型说明：

补充说明

在 PHP 中，一般情况下对于时间都是按照 UNIX 时间戳以 int 类型存储于表中，再根据实际需要用 PHP 的时间函数进行处理，但不完全都是这样

三、基本语法

系统管理

连接MySQL

格式: mysql -h 主机地址 -u用户名 -p用户密码

mysql默认没有密码，直接mysql默认以root(和系统的root没关系)身份链接本地的mysql服务

例 1:连接到本机上的 MySQL。

[root@jacken ~]# mysql -uroot -pmima

例 2:连接到远程主机上的 MYSQL。

[root@lamp ~]# mysql -uroot -h192.168.1.2-phello

修改新密码

在终端输入：mysql -u用户名 -p密码，回车进入Mysql。

> use mysql;

> update user setpassword=PASSWORD(‘新密码‘) where user=‘用户名‘;

> flush privileges;#更新权限

> quit; #退出

增加新用户

格式:grant select on 数据库.* to 用户名@登录主机 identified by ‘密码‘

例 :增加一个用户jacken密码为 123,让他可以在任何主机上登录,并对所有数据库有

查询、插入、修改、删除的权限。

首先用以 root 用户连入 MySQL,然后键入以下命令:

mysql> grantselect,insert,update,delete  on *.* [email protected] identified by‘123‘;

mysql> grant allprivileges on *.* to root@localhost identified by ‘mysql‘;(有所有权限)

然后刷新权限设置。

mysql>flush privileges;

删除用户

delete from user where user=‘用户名‘ andhost=’主机’;

例子：删除本地主机上的jacken用户(远程主机的jacken用户可登录)

mysql> delete from user whereuser=‘jacken‘ and host=’localhost’;

数据库操作

显示所有的数据库

mysql> show databases;(注意:最后有个 s)

创建数据库

mysql> create database test;

连接数据库

mysql> use test01;

查看当前使用的数据库

mysql> select database();

当前数据库包含的表信息

mysql> show tables; (注意:最后有个 s)

删除数据库

mysql> drop database test01;

表操作

备注:操作之前使用“use <数据库名>”应连接某个数据库。

建表

命令:create table <表名> (<字段名 1> <类型 1> [,..<字段名 n> <类型 n>]);

例子：

mysql> create table MyClass(

> id int(4) not null primary keyauto_increment,

> name char(20) not null,

> sex int(4) not null default ‘0‘,

> degree double(16,2));

获取表结构

命令: desc 表名，或者show columns from 表名

例子：

mysql> describe MyClass

mysql> desc MyClass;

mysql> show columns from MyClass;

删除表

命令:drop table <表名>

例如:删除表名为 MyClass 的表

mysql> drop table MyClass;

插入数据

命令:insert into <表名> [( <字段名 1>[,..<字段名 n > ])] values ( 值 1 )[, ( 值 n )]

例子：

mysql> insert into myclass values(‘‘,‘Tom‘,‘‘,96.45),(‘‘,‘Joan‘,‘‘,82.99),(‘‘,‘Wang‘,‘‘,96.59);

查询表中的数据

查询所有行

mysql> select * from MyClass;

查询前几行数据

例如:查看表 MyClass 中前 2 行数据

mysql> select * from MyClass order by idlimit 0,2;

或者

mysql> select * from MyClass limit 0,2;

select distinct 字段 from 表名；去除重复的字段值

select * from 表名 where 字段=‘值‘ and  字段2<值

select * from 表名 order by 字段；   按照指定字段升序排列

select * from 表名 order by 字段【desc】；按照指定字段降序排列

select * from 表名 order by 字段【limit n】；显示前n 行

select * from 表名 order by 字段【limit n,m】；从n+1行开始，显示出m行

select FUN_NAME(字段) frombiao1; 

FUN_NAME包括sum(求和)、count(*)记录数、max(最大值)、min(最小值)

having和where的区别在于，having是对聚合后的结果进行条件的过滤，而where是在聚合前就对记录进行过滤，如果逻辑允许，尽可能用where先过滤记录，这样因为结果集减少，将对聚合的效率大大提高，最后再根据逻辑看是否用having进行再过滤。

删除表中数据

命令:delete from 表名 where 表达式

例如:删除表 MyClass 中编号为 1 的记录

mysql> delete from MyClass where id=1;

修改表中数据

命令:update 表名 set 字段=新值,... where 条件

mysql> update MyClass set name=‘Mary‘where id=1;

在表中增加字段

命令:alter table 表名 add 字段类型其他;

alter table 表名 modify 字段varchar(20) 【first|after】；

alter table 表名 add  字段 int(3) 【first|after】；

alter table 表名 drop 字段；

alter table 表名 change 字段字段2 int(4) 【first|after】；

alter table 表名 rename 字段2；

modify修改字段类型  change修改字段名字(新名字加类型)rename修改表名

例如:在表 MyClass 中添加了一个字段 passtest,类型为 int(4),默认值为 0

mysql> alter table MyClass add passtestint(4) default ‘0‘

更改表名

命令:rename table 原表名 to 新表名;

例如:在表 MyClass 名字更改为 YouClass

mysql> rename table MyClass to YouClass;

更新字段内容

命令:update 表名 set 字段名[where 字段]=‘‘= 新内容

update 表名 set 字段名 =replace(字段名, ‘旧内容‘, ‘新内容‘);

例如:文章前面加入 4 个空格

update article set content=concat(‘    ‘, content);

数据库导入导出

从数据库导出数据库文件

使用“mysqldump”命令

1)导出所有数据库

格式:mysqldump -u [数据库用户名] -p -A>[备份文件的保存路径]

2)导出数据和数据结构

格式:mysqldump -u [数据库用户名] -p [要备份的数据库名称]>[备份文件的保存路径]

举例:

例 1:将数据库 mydb 导出到 /data/mydb.sql 文件中。

 mysqldump -h localhost -u root -p mydb >/data/mydb.sql

然后输入密码,等待一会导出就成功了,可以到目标文件中检查是否成功。

例 2:将数据库 mydb 中的 mytable 导出到/data/mytable.sql 文件中。

mysqldump -h localhost -u root -p mydbmytable>/data/mytable.sql

例 3:将数据库 mydb 的结构导出到 /data/mydb_stru.sql 文件中。

 mysqldump -h localhost -u root -p mydb--add-drop-table > /data/mydb_stru.sql

备注:-h localhost 可以省略,其一般在虚拟主机上用。

3)只导出数据不导出数据结构

格式:

mysqldump -u [数据库用户名] -p -t[要备份的数据库名称]>[备份文件的保存路径]

4)导出数据库中的Events

格式:mysqldump -u [数据库用户名] -p -E [数据库用户名]>[备份文件的保存路径]

5)导出数据库中的存储过程和函数

格式:mysqldump -u [数据库用户名] -p -R [数据库用户名]>[备份文件的保存路径]

从外部文件导入数据库中

1)使用“source”命令

首先创建数据库,然后使用该数据库。最后执行下面操作。

mysql>source [备份文件的保存路径]

2)使用“<”符号

首先创建数据库

mysql -u root –p < [备份文件的保存路径]

Other

show processlist

显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程（也就是，与您正在使用的MySQL账户相关的线程）。

通配符(wildcard)

用来匹配值的一部分的特殊字符。

搜索模式(search pattern) 又字面值、通配符或两者组合构成的搜索条件。

% 任何字符出现任意次(>=0)

select prod_name,prod_id from productswhere prod_name LIKE ‘hi%‘;

_ 匹配单个字符

select prod_name,prod_id from productswhere prod_name LIKE ‘_hi‘;

正则表达式

.  代表任意字符。

|  OR匹配，两个串之一，如 a|b 匹配a或b。

[]  匹配几个字符之一，[abc]1匹配a1或b1或c1。。

-        匹配范围 [2-5]a 匹配2a或3a或4a或5a。

\\  转义,使通配符代表其本身意思。

^       有两种用法，在集合中用来否定该集合，否则指定串的开始处。

$       结束符

空白元字符

\\f             换页

\\n                      换行

\\r                       回车

\\t                       制表

\\v                       纵向制表

重复元字符

*                          0个或多个匹配

+                          一个或多个匹配(等于{1,})

?                          0个或1个匹配(等于{0,1})

{n}                       指定数目的匹配

{n,}            不少于指定数目的匹配

{n,m}              匹配数目的范围(m不超过255)

本文出自 “一步步踏入Linux世界” 博客，转载请与作者联系！