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中是都支持的。

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

• 其他显示语句：

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

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

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

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

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

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

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

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

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

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

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

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

• 在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子句常用的类型

•  删除表中的记录

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;

• 排序检索数据

使用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;

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;

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;

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子句有很多优点：

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');

•  数值处理函数

•  汇总数据
•  聚集函数

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 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;