一、基本概念

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

SQL 语言包含4个部分:

1.数据定义语言(DDL),例如:CREATEDROPALTER 等语句

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

3.数据查询语言(DQL),例如:SELECT 语句

4.数据控制语言(DCL),例如:GRANTREVOKECOMMITROLLBACK 等语句

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. 数据库名不能是纯数字。

MySQLLinux下数据库名、表名、列名、别名大小写规则是这样的:

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

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

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

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

 

二、Mysql数据(字段)类型

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

数值类型

数值类型说明:

wKiom1UazWmDRcNPAANEuU7Hr50414.jpg

补充说明

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

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

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

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

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

字符串(文本)类型

字符串(文本)类型说明:

wKioL1UazsHSQaiLAAMUIQN4PPo214.jpg

补充说明

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

时间日期类型

时间日期类型说明:

wKioL1Uaztzh0hqWAAGFqN4EoU0995.jpg

补充说明

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 set password=PASSWORD('新密码')  where user='用户名';

> flush privileges;#更新权限

> quit; #退出

授权用户

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

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

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

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

mysql> grantselect,insert,update,delete  on *.* to 'jacken'@'192.168.1.3'  identified by'123';

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

然后刷新权限设置。

mysql>flush privileges;

取消授权

revoke all on *.* from 'username'@'host';

删除用户

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(最小值)

havingwhere的区别在于,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 匹配ab

[]  匹配几个字符之一,[abc]1匹配a1b1c1。。

-        匹配范围 [2-5]a 匹配2a3a4a5a

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

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

$       结束符

空白元字符

\\f             换页

\\n                      换行

\\r                       回车

\\t                       制表

\\v                       纵向制表

重复元字符

*                          0个或多个匹配

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

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

{n}                       指定数目的匹配

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

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