MySQL必备知识手册

       数据库对于我们大家来说，不算是陌生的东西，因为我们的周边使用了太多数据库！你的QQ号是存在数据库中的，你的电子邮件是存在数据库中的，你的银行卡号和钱的数量也是存在数据库中的，因此数据库与我们是密不可分的，我们每天几乎都在和数据库打交道。但是我们并不是直接去写数据库命令去操作数据库，而是通过网站，软件等第三方来有权限的操作极小部分数据！接下来我会在这篇文章中详细列出初级数据库人员应该必备的数据库知识！

       我在论述数据库知识的过程中会用到一些表，这些表都在下面的附件中，请大家在阅读我的这篇文章之前，下载这些附件表！通过对这些表的操作，你会真正学到有用的数据库知识的。

       让我们一起来开启数据库之旅吧！

什么是数据库？

       database,保存有组织的数据的容器；你可以把它理解为一个文件柜！

数据库和数据库管理系统

       数据库（DB）数据库管理系统（DBMS），这两者实际是不同的，但是我们常常把他们混淆，你可以简单的认为：数据库是一个文件柜，而数据库管理系统是用来管理文件柜的工具。

一个数据库的组成

      数据库是由表（table）组成的，表具有表名，及表的结构，所谓表的结构，就是指表中的列（column），及每列所具有的属性，这些属性包括：列名，数据类型，约束条件，是否可以为NULL，默认值是什么，附加信息（如主键的自增auto_increment）。

      表中一本有零行或多行。行（row）也叫记录，每一行都包含所有列的信息（为NULL也算）。

使用MySQL

      了解了上述简单的知识后，我们开始学习MySQL中的基本语法，具体怎么安装MySQL,这里就不再赘述了，网上有详细的讲解，找有图有真相的，你以看就懂了。

• 连接数据库

为了连接数据库，我们需要以下信息：

1. 主机名-如果是本地服务器，为localhost或者127.0.0.1;
2. 端口，默认是3306；
3. 用户名，例如：root;
4. 用户口令，例如：root;

在命令提示符下（前提是你配置好path变量）连接数据库的指令如下：

 

mysql -uroot -proot [-hlocalhost]

 

• 显示当前数据库信息

status;

 

• 显示当前服务器所有数据库文件

show databases;

 

• 当要使用某个数据库时，必须切换到某个数据库

use kaishengit_db;#kaishengit_db是数据库的名称

 

• 显示kaishengit_db数据库中的所有表

show tables; 

 

• 显示customers表的表结构，即所有列及其属性

show column from customers;

 

desc customers;

 上述两种方式在MySQL中是都支持的。

显示的结果包含有以下项目：

表结构 写道

Filed 列名
Type 数据类型
Null 是否允许空值
Default 默认值是多少
Key 是否有约束（外键，唯一，主键等）
Extra 附加信息，例如主键的自增（auto_increment）

 

 

• 其他显示语句：

show create database kaishengit_db;#显示创建特定数据库的MySQL语句

  

show create table customers;#显示创建表customers的MySQL语句

 

show grants;#用来显示用户的安全权限（所有用户或指定用户）

 

show errors;#显示错误信息
show warnings;#显示警告信息

 

/*想了解更多关于SHOW的用法使用下面的语句*/
help show;

 其他语句不是我们常用的语句，可以有选择的记忆！

•  我们知道了怎么查询数据库，连接数据库，我们还不知道怎么创建数据库呢，下面我们来创建数据库。

创建数据库我们使用CREATE DATABASE语句；

create database mysql;#创建了msql数据库

•  我们来在mysql数据库中建表

create table t_test(
id int,
name,varchar(20),
age int,
address varchar(50)
);

 我们创建了包含有四列的一个test表；

• 在test表中插入一行数据；

insert into t_test(
id,name,age,address)
values(
'1','jack','21','USA');

 这样就插入了一行，我们在插入的时候，第一个括号中的字段代表列名，第二个括号中的是对应列名中要填的值，必须保证他们的个数一一对应，如果存在默认值，可以使用default代替值传入，但是不能不传，值列表中除了刚才说的default不要用单引号引起来，最好全部用单引号引起来，字符型是必须引起来的。

• 在test表中插入多行，values后面多写几个括号就可以了

insert into t_test
#如果插入所有列时，此处可以不写括号，直接values
values
('2','tom','22','UN'),
('3','rose','21','Japan');

 

• 重命名表

rename table
t_test
to
t_text;

•  删除表

drop table t_text;

•  删除数据库

drop database mysql;

•  怎么给数据库改名呢？有一个不是直接的方法帮助我们来实现；

/*先把数据库文件导*/
#1,使用quit或exit退出数据库连接
#2，使用mysqldump命令导出数据库文件
mysqldump -hlocalhost -uroot -proot mysql>c:/mydb.sql

#3,导出都了c盘mydb.sql文件，我们再导入新的数据库中即可
#创建新的数据库（改名吧）
create database newdb;
use newdb;
source c:/mydb.sql;

•  主键设计

 

 主键的添加一般有两种方式，一是创建表时即创建主键，二是后来添加主键约束；

create table t_test(
id int primary key,
name varchar(20)
);

create table t_test(
id int,
name varchar(20),
primary key(id)
);

alter table t_test add constraint pk_t_test_id primary key(id);
alter table t_test add constraint pk_t_test_id primary key t_test(id);

•  创建表示我们可以直接添加相应的约束条件，就像主键约束类似，我们常用的约束，有如下几个

#主键约束

#外键约束
alter table t_test 
add constraint fk_t_test_classid
foreign key(classid)
references
t_class(id);
#非NULL约束
alter table t_test modify column name varchar(20) not null;
#唯一约束
alter table t_test add constraint uk_t_test_name unique(name);
alter table t_test modify column name varchar(20) not null unique;
#删除外键
alter table t_test drop foreign key fk_t_test_classid;

•  添加一列，删除一列，添加默认值

alter table t_test add class_id int;

 

alter table t_test drop column class_id;

 

alter table t_test alter address set default 'USA';

•  修改记录值

update t_test set age=22;
update t_test 
set age=21
where id=1;

•  WHERE子句常用的类型

WHERE 写道

WHERE name='tom'
WHERE name='tom' or name='rose'
WHERE id>1 and id<3
WHERE id!=23
WHERE id<>23
WHEREid=12 or id=23
WHERE id IN(12,23)
WHERE id BETWEEN 12 AND 23
WHERE password IS NULL
WHERE password IS NOT NULL

•  删除表中的记录

delete from t_test;#表中数据清空
truncate table t_test;#清空数据，比delete更快
delete from t_test where id=1;

 

 

• 查看单个列

 这个查询的顺序是没有意义的，不同的计算机可能显示的结果不一样，但总行数是相同的。

select prod_name from products;#从产品表中查产品名称

 

•  查看多个列

select prod_id,prod_name,prod_price from products;#显示三列数据

 显示多个列，同样使用select语句，唯一的区别是select后跟的是要显示的哪些列！

• 检索所有列

select * from products;#显示所有列的信息

 不推荐使用通配符进行所有列查询，这样的效率是很慢的，性能不好！推荐列出所有列名的查询方式。

 

•  去重复行的查询方式

取出重复的关键字是DISTINCT，写在SELECT后，表示不显示重复的行

select distinct vend_id from products;

 显示的供应商ID是都不相同的

不能部分使用DISTINCT，DISTINCT是应用于它后面的所有列的，除非每列的值都相同，则不显示，否则会都显示出来！

select distinct vend_id,prod_price from products;

 只有供应商的ID和商品的价格都相同时，则只显示一行，否则，多行都显示！

 

• 查询分页（也叫限制结果），就是指：指定显示哪些查询结果

例如网页搜索结果，每页显示二十条，就是用的分页查询

select prod_name from products limit 5;#表示显示前五条查询结果

 LIMIT 5等同于LIMIT 0,5；表示从行零（查询结果的第一行）开始的五行，包括行零。即1，2，3，4，5行！

select prod_name from products limit 5,5;

 上个语句的意思是从行五开始的五行，第6行是行五，即6，7，8，9，10行！

 

• 使用完全限定名进行查询列

所谓完全限定名，即：数据库名.表名【表示表】           表名.列名【表示列】

select products.prod_name from kaishengit_db.products;

 查询kaishengit_db数据库中的产品表中的产品名列！

 

• 排序检索数据

使用ORDER BY子句

select prod_name 
from products 
order by prod_name;

 检索产品表的产品名，并按默认的升序排列（升序的关键字是ASC，默认是升序）

 

可以按多个列进行排序，先按第一列排序，如果条件相同的话再按第二个列排序。。。。如果第一列就不同，则不进行第二个列的排序

select prod_id,prod_price,prod_name
from products
order by prod_price,prod_name;

 先按价格的升序排，如果价格相同，再按产品名称的Unicode码进行排序！

 

指定排序方式：排序方式有两个关键字：ASC：升序，默认的，可以不用写；DESC：降序，这个需要写上

select prod_id,prod_price
from products
order by prod_price desc;

 表示按价格的降序排列显示出来。

排序方式关键字只适用于它前面的一列，对于其他列不在他的管辖范围

select prod_id,prod_price,prod_name
from products
order by prod_price desc,prod_name;

 表示按价格的降序排，然后按名称的升序排（默认的情况）。

MySQL中是不区分大小写的，也就是说对于大小写，会被认为是一样的。如果想按大小写排序，简单的ORDER BY办不到，必须请求数据库管理员（DBA）的帮助。

 

可以使用ORDER BY子句和LIMIT子句结合的方式找出一个列中的最高或最低的值；

select prod_price
from products
order by prod_price desc
limit 1;

 这样就找到了产品价格最高的值；

 

• 过滤数据：过滤数据，也就是有条件的查询，选出符合条件的数据。使用WHERE子句来实现

select prod_name,prod_price
from products
where prod_price=2.5;

 找到价格是2.5的产品名和价格；

 

WHERE子句的操作符：

操作符

=
<>等价于!=
<
<=
>
>=
BETWEEN....AND....等价于[3 5]

对于上面操作符的使用，很简单，相信你会熟练使用的。

对于空值的查询，不是用“=”了，而是用关键字IS

select prod_name
from products
where prod_price is null;

 

select prod_name
from products
where prod_price is not null;

  

• 数据过滤：就是指WHERE子句中使用一些更复杂的操作符，实现查询的条件更多：NOT   IN   AND   OR 配合WHERE的使用

AND操作符

select prod_id,prod_price,prod_name
from products
where vend_id=1003 and prod_price<=10;

 查询供应商ID为1003提供的价格不高于10的商品信息；

 

OR操作符

select prod_name,prod_price
from products
where vend_id=1002 or vend_id=1003;

 查询供应商ID为1002供应的商品和ID为1003供应的商品

 

WHERE子句中可以包含任意多个AND  OR     注意一点：AND操作符的优先级高于OR，如果没有括号限制，会先处理AND

 

IN操作符

select prod_name,prod_price
from products
where vend_id in(1002,1003)
order by prod_price;

 IN()表示条件范围，上列表示：选出供应商1002和1003供应的产品信息，并按价格升序显示出来；

记住：ORDER BY子句是放到WHERE语句之后的，否则会出错的。

 

 

 

 

 

 

 

 

IN子句有很多优点：

哈哈 写道

在使用长的合法选项清单时，IN操作符更清楚和直观
在使用IN时，计算次序更容易管理
IN要比OR执行的快
IN 的最大优点是可以包含其他SELECT语句

 

NOT操作符

select prod_name,prod_price
from products
where vend_id not in(1002,1003)
order by prod_price;

 NOT的作用就是否定它后面的任意条件；上列的意思是：显示不是供应商ID为1002，1003供应的商品，并按价格升序排序

 

• 用通配符（wildcard）进行过滤：使用通配符就要用到WHERE...LIKE...复合语句，通配符有两个：1是百分号（%）：表示零个或多个字符，2是下划线（_）表示一个任意的字符；

select prod_id,prod_name
from products
where prod_name like 'jet%';

 表示搜索产品名以jet开头的所有产品信息；

通配符可以使用多个：%hello%  是允许的，表示只要包含hello的都符合条件；s%e  表示以s开头和以e结尾的字符串；数据库存储的信息如果有空格，匹配的时候也是算字符的，如果字符是：【空格】jetom  则上述程序就匹配不到它；NULL值是不被通配符匹配的。

下划线（_）表示单个字符：

select prod_id,prod_name
from products
where prod_name like '_ ton anvi1';

 下划线地方存在一个字符，并且后面全匹配的（包括空格），猜会被选中。

 

通配符搜索是需要更多时间的，一般不要使用通配符搜索，除非迫不得已，即使迫不得已，也要把通配符搜索放到条件的最后！

 

•  文本处理函数

#截取左边两个字符显示
select prod_name,LEFT(prod_name,2) from products;
#右边两个
select prod_name,RIGHT(prod_name,2) from products;
#返回字符串的长度
select prod_name,LENGTH(prod_name) from products;
#小写
select prod_name,LOWER(prod_name) from products;
#大写
select prod_name,UPPER(prod_name) from products;
#去掉左边的空格
select prod_name,LTRIM(prod_name) from products;
#去掉右边的空格
select prod_name,RTRIM(prod_name) from products;
#去除两边的空格
select prod_name,TRIM(prod_name) from products;
#把字符连接到列值上
select CONCAT('I love ',prod_name) from products;

•  日期时间函数

 

 

select curDate();
select curTime();
select now();

select date(now());
select time(now());

select year(curDate());
select month(curDate());
select day(curDate());
select dayofweek(curDate());

select hour(curTime());
select minute(curTime());
select second(curTime());

select datediff(curDate(),'1970-01-01');
select addDate(curDate(),'20000');

 

 

•  数值处理函数

 

数值处理函数 写道

Abs()
返回一个数的绝对值
Cos()
返回一个角度的余弦
Exp()
返回一个数的指数值
Mod()
返回除操作的余数
Pi()
返回圆周率
Rand()
返回一个随机数
Sin()
返回一个数的正弦
Sqrt()
返回一个数的平方根
Tan()
返回一个数的正切

 

•  汇总数据
•  聚集函数

聚集函数 写道

AVG()
返回某列的平均值
COUNT()
返回某列的行数
MAX()
返回某列的最大值
MIN()
返回某列的最小值
SUM()
返回某列之和

select avg(prod_price)
from products;
//返回产品的平均价格

•  分组数据

select vend_id
from products
group by vend_id;
//对vend_id进行分组，即重复项目只显示一个

•  过滤分组

select cust_id,count(*)
from orders
group by cust_id
having count(*)>=2;

•  select子句顺序

select子句顺序 写道

select

from

where

group by

having

order by

limit

•  使用子查询

 

select order_num
from orderitems
where prod_id='TNT2';

select cust_id
from orders
where order_num
in(20005,20007);


select cust_id
from orders
where order_num
in(select order_num
    from orderitems
    where prod_id='TNT2');

 

 

//查询顾客的订单数

select cust_name,(select count(*)
                  from orders
                  where orders.cust_id=customers.cust_id)
from customers
order by cust_name;

 

•  联结表

 

//等值联结
select vend_name,prod_name,prod_price
from vendors,products
where vendors.vend_id=products.vend_id
order by vend_name,prod_name;

 如果没有WHERE将会出现笛卡尔积式的联结

//内部联结
select vend_name,prod_name,prod_price
from vendors
inner join products
on vendors.vend_id=products.vend_id;

 

//联结多个表
//显示编号为20005的订单中的物品
select prod_name,vend_name,prod_price,quantity
from orderitems,products,vendors
where products.vend_id=vendors.vend_id
and orderitems.prod_id=products.prod_id
and order_num=20005;

 

外联结 写道

select ...
from 表一
left join 表二
on ....


select ...
from 表一
right join 表二
on ...

 

组合查询 写道

select ...
from 表一
union
select ...
from 表二

//选择的列数必须相同，类型相符或能转换，完全相同的合并
//显示全部用：union all