SQL入门

MySQL数据库简介

市面上数据库有如下几种：

• SQL Server、Oracle
• MySQL、DB2
• SyBase

MySQL数据库的安装和配置

关于MySQL数据库的安装和配置，我在这里就不多数了，网上的教程一大堆。
MySQL数据库安装和配置好之后，可以使用命令行窗口连接MySQL数据库，命令如下：

mysql –u 用户名 –p 密码

怎么知道MySQL数据库是否安装和配置成功呢？可参考下图：

数据库服务器、数据库和表的关系

所谓安装数据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。
为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。
数据库服务器、数据库和表的关系如图所示：

数据在数据库中的存储方式

数据在数据库中的存储方式可用下图来粗略说明：

• Java是使用对象封装数据的，例如程序产生了如上所示２个user对象，这些对象的数据要想保存到数据库中，需要在数据库中创建与之对应的表，一个user对象中封装的数据，要保存到数据库中，一般就要在数据库中创建一个与之对应的表。
• 对象的属性定义为表头，对象的数据对应于表中的一条记录。
• 每个对象对应于表中的一条记录。

明白数据库使用表保存数据后，如何在数据库中创建表呢？不急，我们慢慢介绍。

创建数据库

创建数据库语法为：

CREATE  DATABASE  [IF NOT EXISTS] db_name        [create_specification [, create_specification] ...]

create_specification: [DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name

• CHARACTER SET：指定数据库采用的字符集
• COLLATE：指定数据库字符集的比较方式

练习

1. 创建一个名称为mydb1的数据库。

   create database mydb1;

2. 创建一个使用utf-8字符集的mydb2数据库。

   create database mydb2 character set utf8;

3. 创建一个使用utf-8字符集，并带校对规则的mydb3数据库。

   create database mydb3 character set utf8 collate utf8_general_ci;

   通过查看MySQL5.1 参考手册，我们知道utf8 (UTF-8 Unicode)校对规则有：
   

查看、删除数据库

显示数据库语句：

SHOW DATABASES

显示数据库创建语句：

SHOW CREATE DATABASE db_name

数据库删除语句：

DROP DATABASE  [IF EXISTS]  db_name 

练习

1. 查看当前数据库服务器中的所有数据库

   show databases;

2. 查看前面创建的mydb2数据库的定义信息

   show create database mydb2;

3. 删除前面创建的mydb1数据库

   drop database mydb1;

修改、备份、恢复数据库

修改数据库语法：

ALTER  DATABASE  [IF NOT EXISTS] db_name        [alter_specification [, alter_specification] ...]

alter_specification： [DEFAULT] CHARACTER SET charset_name
| [DEFAULT] COLLATE collation_name

备份数据库表中的数据命令：

mysqldump -u 用户名 -p 数据库名 > 文件名.sql   (windows命令)

恢复数据库语法：

Source 文件名.sql  (sql语句)

练习

1. 查看服务器中的数据库，并把其中某一个库的字符集修改为gbk

   alter database mydb2 character set gbk;show create database mydb2;

2. 演示恢复和备份

   • 演示备份

     create database tt;use tt;create table a (    name varchar(20));insert into a(name) values('liayun');select * from a;----看到a表有数据

     对tt库作备份操作，启动一个window命令行窗口，执行如下命令：

     mysqldump -u root -p tt>c:\tt.sql

   • 演示恢复

     1. 先删除库

        drop database tt;

     2. 再恢复tt库(第一种方式)（恢复只能恢复库里面的数据，无法恢复库及库里面的数据）
        为恢复库，要先创建库：

        create database tt;

        再恢复tt库：

        use tt;source c:\tt.sql---(source:可以执行一个sql脚本)select * from a;---看到a表有数据

     3. 再恢复tt库(第二种方式)（恢复只能恢复库里面的数据，无法恢复库及库里面的数据）
        为恢复库，要先创建库：

        create database tt;

        恢复tt库：

        mysql -u root -p yezi tt<c:\tt.sql----(windows命令)

创建表(基本语句)

创建表的语法为：

CREATE TABLE table_name(    field1  datatype,    field2  datatype,    field3  datatype,) character set 字符集 collate 校对规则

• field：指定列名
• datatype：指定列类型

注意：

• 创建表前，要先使用use db语句使用库。
• 创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。

创建表练习

创建一个员工表，如下所示：

创建表的语句为：

create table employee(    id int,    name varchar(40),    sex varchar(4),    birthday date,    entry_date date,    job varchar(40),    salary decimal(8,2),    resume text);

查看库的所有表：

show tables;

查看表的创建细节：

show create table employee;

查看表的结构：

desc employee;

MySQL常用数据类型

这里可参考我的笔记MySQL常见数据类型详解 。

修改表

使用ALTER TABLE语句追加，修改或删除列的语法：

ALTER TABLE tableADD        (column datatype [DEFAULT expr]           [, column datatype]...);

ALTER TABLE tableMODIFY     (column datatype [DEFAULT expr]           [, column datatype]...);

ALTER TABLE tableDROP          (column);

修改表的名称：

Rename table 表名 to 新表名;

修改表的字符集：

alter table 表名 character set 字符集;

练习

1. 在上面员工表的基本上增加一个image列。

   alter table employee add image blob;

2. 修改job列，使其长度为60。

   alter table employee modify job varchar(60);

3. 删除sex列。

   alter table employee drop sex;

4. 表名改为user。

   rename table employee to user;

5. 修改表的字符集为utf-8。

   alter table user character set utf8;

6. 列名name修改为username。

   alter table user change column name username varchar(40);

删除表

删除表的语法为：

drop table 表名;

数据库CRUD语句

• Insert语句(增加数据)
• Update语句(更新数据)
• Delete语句(删除数据)
• Select语句(查找数据)

Insert语句

使用INSERT语句向表中插入数据。

INSERT INTO table[(column [, column...])] VALUES(value [, value...]);

注意：

• 插入的数据应与字段的数据类型相同。
• 数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。
• 在values中列出的数据位置必须与被加入的列的排列位置相对应。
• 字符和日期型数据应包含在单引号中。
• 插入空值，不指定或insert into table value(null)

练习

使用insert语句向表中插入三个员工的信息。

注意：字符和日期要包含在单引号中。
插入数据的第一种方式：

insert into employee(id,username,birthday,entry_date,job,salary,resume) values(1,'liayun','1992-10-06','2015-09-12','Java程序员',7500,'我是一名合格的程序员');

插入数据的第二种方式：

insert into employee values(2,'yezi','1993-10-06','2015-09-12','Java程序员',7500,'我是一名合格的程序员');

插入数据的第三种方式：

insert into employee values('3','liyunling','1990-10-06','2015-09-12','Java程序员','7500','我是一名合格的程序员');

插入数据时，一定要注意乱码问题，乱码问题至今为止我已没搞清楚，只是知道怎么解决而已，关于这点我也做了笔记，可以参考我的MySQL中文乱码问题。以后回过头来一定要彻彻底底地搞明白，刨它的祖坟！

Update语句

使用update语句修改表中数据。

UPDATE  tbl_name        SET col_name1=expr1 [, col_name2=expr2 ...]        [WHERE where_definition]  

• UPDATE语法可以用新值更新原有表行中的各列。
• SET子句指示要修改哪些列和要给予哪些值。
• WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。

练习

在上面创建的employee表中修改表中的纪录。

1. 将所有员工薪水修改为5000元。

   update employee set salary=5000;

2. 将姓名为‘张三’的员工薪水修改为3000元。

   update employee set salary=3000 where username='张三';

3. 将姓名为‘liayun’的员工薪水修改为4000元，job改为前端工程师。

   update employee set salary=4000,job='前端工程师' where username='liayun';

4. 将‘yezi’的薪水在原有基础上增加1000元。

   update employee set salary=salary+1000 where username='yezi';

Delete语句

使用delete语句删除表中数据。

delete from tbl_name           [WHERE where_definition]

• 如果不使用where子句，将删除表中所有数据。
• Delete语句不能删除某一列的值（可使用update）。
• 使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。
• 同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。
• 删除表中数据也可使用TRUNCATE TABLE语句，它和delete有所不同——delete语句删除整个表时，是一行行删；truncate table语句删除整个表时摧毁整个表，再重建表结构。

练习

1. 删除employee表中id为’5’的记录。

   delete from employee where id=5;

2. 删除employee表中所有记录。

   delete from employee;

3. 使用truncate删除表中记录。

   truncate table employee;

Select语句(一)

基本select语句：

SELECT [DISTINCT] *|{column1, column2. column3..} FROM table;

• Select 指定查询哪些列的数据。
• column指定列名。
• *号代表查询所有列。
• From指定查询哪张表。
• DISTINCT可选，指显示结果时，是否剔除重复数据。

练习

使用如下SQL语句建一张学生表(student)：

create table student(    id int,    name char(20),    chinese float,    english float,    math float);

并插入一些数据，使student表显示为：

1. 查询表中所有学生的信息。

   select * from student;

2. 查询表中所有学生的姓名和对应的英语成绩。

   select name,english from student;

3. 过滤表中重复的英语成绩数据。

   select distinct english from student;

Select语句(二)

在select语句中可使用表达式对查询的列进行运算：

SELECT *|{column1｜expression, column2｜expression，..} FROM table;

在select语句中可使用as语句：

SELECT column as 别名 from 表名;

练习

对于以上student表，有如下练习：

1. 在所有学生总分上加10分特长分。

   select name,(chinese+english+math+10) from student;

2. 统计每个学生的总分。

   select name,(chinese+english+math) from student;

3. 使用别名表示学生分数。

   select name as 姓名,(chinese+english+math) as 总分 from student;

   或

   select name 姓名,(chinese+english+math) 总分 from student;

Select语句(三)

使用where子句，进行过滤查询，在where子句中经常使用的运算符有：

注意：Like语句中，%代表零个或多个任意字符，_代表一个字符，例first_name like ‘_a%’;

练习

对于以上student表，有如下练习：

1. 查询姓名为王五的学生成绩。

   select * from student where name='王五';

2. 查询英语成绩大于90分的同学。

   select * from student where english>'90';

3. 查询总分大于200分的所有同学。

   select name from student where (chinese+english+math)>200;

4. 查询英语分数在80－90之间的同学。

   select name from student where english>80 and english<90;

   select name from student where english between 80 and 90;   ===   select name from student where english>=80 and english<=90; 

5. 查询数学分数为89、90、91的同学。

   select * from student where math in(89,90,91);

6. 查询所有姓李的学生成绩。

   select * from student where name like '李%';

7. 查询数学分>80，语文分>80的同学。

   select * from student where math>80 and chinese>80;

Select语句(四)

使用order by子句排序查询结果。

SELECT column1, column2. column3..        FROM table;        order by column asc|desc

• Order by指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的列名。
• Asc升序、Desc降序。
• ORDER BY子句应位于SELECT语句的结尾。

练习

对于以上student表，有如下练习：

1. 对数学成绩排序后输出。

   select name,math from student order by math;

2. 对总分排序后输出，然后再按从高到低的顺序输出。

   select name 姓名,(chinese+english+math) 总分 from student order by (chinese+english+math) desc;

   或

   select name 姓名,(chinese+english+math) 总分 from student order by 总分 desc;

3. 对姓李的学生成绩排序输出。

   select * from student where name like '李%' order by (chinese+english+math) desc;

Select语句(五)

使用group by子句对列进行分组。

SELECT column1, column2. column3.. FROM table;        group by column

使用having子句过滤。

SELECT column1, column2. column3..        FROM table;        group by column having ...

练习

先使用如下SQL语句新建一张订单表(orders)：

create table orders (    id int,    product varchar(20),    price float);

再向订单表中插入一些数据，如下：

1. 对订单表中商品归类后，显示每一类商品的总价。

   select product,sum(price) from orders group by product; 

2. 查询购买了几类商品，并且每类总价大于100的商品。

   select product from orders group by product where sum(price)>100;

   注意：以上查询语句是错误的，因为where子句后面不能有合计函数。正确的查询语句应为：

   select product from orders group by product having sum(price)>100;

   记住：Having和where均可实现过滤，但在having可以使用合计函数，having通常跟在group by后，它作用于组。

合计函数

count

Count(列名)返回某一列，行的总数。

Select count(*)|count(列名) from tablename        [WHERE where_definition] 

关于count函数的细节：count只统计有这一列有值的行。

练习

对于以上student表，有如下练习：

1. 统计一个班级共有多少学生？

   select count(name) from student;

   或

   select count(*) from student;

2. 统计数学成绩大于80的学生有多少个？

   select count(*) from student where math>80;

3. 统计总分大于250的人数有多少？

   select count(*) from student where (chinese+english+math)>250;

sum

Sum函数返回满足where条件的行的和。

Select sum(列名)｛,sum(列名)…｝ from tablename        [WHERE where_definition] 

注意：

• sum仅对数值起作用，否则会报错。
• 对多列求和，“，”号不能少。

练习

对于以上student表，有如下练习：

1. 统计一个班级数学总成绩。

   select sum(math) from student;

2. 统计一个班级语文、英语、数学各科的总成绩。

   select sum(chinese),sum(english),sum(math) from student;

3. 统计一个班级语文、英语、数学的成绩总和。

   select sum(chinese+english+math) from student;

4. 统计一个班级语文成绩平均分。

   select sum(chinese)/count(*) from student;

avg

AVG函数返回满足where条件的一列的平均值。

Select sum(列名)｛,sum(列名)…｝ from tablename        [WHERE where_definition]

练习

对于以上student表，有如下练习：

1. 求一个班级数学平均分。

   select avg(math) from student;

2. 求一个班级总分平均分。

   select avg(chinese+english+math) from student;

max和min

Max/min函数返回满足where条件的一列的最大/最小值。

Select max(列名)　from tablename        [WHERE where_definition] 

练习

对于以上student表，有如下练习：

1. 求班级最高分和最低分（数值范围在统计中特别有用）

   select max(chinese+english+math),min(chinese+english+math) from student;

时间日期相关函数

示例：

• ADDTIME (date2 ,time_interval )——将time_interval加到date2上
  
  注意：字符串、时间日期的引号问题。
• CURRENT_TIMESTAMP ( )——得到某一刻的时间值，如2016-08-12 08:59:11
  
• NOW ( )——得到当前这一刻的时间值，如2016-08-12 09:01:41
  

字符串相关函数

数学相关函数

定义表的约束

定义主键约束

定义主键约束的关键字：primary key——不允许为空，不允许重复。
主键列的数据是不能重复的，并且还要是唯一的。
例，使用如下SQL语句创建一个表。

create table student (    id int primary key,     name varchar(40));

可以使用如下insert语句向student表中插入数据：

insert into student(id,name) values(1,'aaa');

但不能使用如下insert语句向student表中插入数据：

insert into student(name) values('aaa');

记住：以后在设计表的时候，每个表必须要有一列是主键列，也即每个表必须要有一列来唯一的标识这个表里面的每一条记录，一般来说把表的ID这一列设置为主键列。并且一个表只能有一个主键列。
还可使用关键字auto_increment定义主键自动增长，让数据库自己去维护这一列的值。例如：

create table student (    id int primary key auto_increment,     name varchar(40));

可以使用如下insert语句向student表中插入数据：

insert into student(name) values('aaa');insert into student(name) values('bbb');

此时删除掉id为2的一条记录，然后再次插入一条新的记录，如下：

delete from student where id=2;insert into student(name) values('ccc');

现在查询student表，姓名为ccc的id到底是2还是3呢？答案显然是3。

定义唯一约束

定义唯一约束的关键字：unique。
例，使用如下SQL语句创建一个表。

create table student (    id int primary key auto_increment,     name varchar(40) unique);

可使用如下insert语句向student表中插入数据：

insert into student(name) values('aaa');

注意：约束加的越严格越好，宁可错杀一千，不可放过一个。

定义非空约束

定义非空约束的关键字：not null。
例，使用如下SQL语句创建一个表。

create table student (    id int primary key auto_increment,     name varchar(40) unique not null);

定义外键约束

假设现在要设计一个夫妻关系管理系统，丈夫表里面肯定会有一个id字段，除此之外还有name字段；妻子表里面除了有id、name等字段外，应该还要有一个husband_id字段，该字段用于表示这个妻子的丈夫是谁，这个时候就需要用到外键约束。

• husband表

  create table husband (    id int primary key,    name varchar(40));

• wife表

  create table wife (    id int primary key,    name varchar(40),    husband_id int,    constraint husband_id_FK foreign key(husband_id) references husband(id));

  现在对wife表中定义外键约束的语句进行详细解释：

  constraint(定义一个约束) husband_id_FK(约束名称) foreign key(husband_id)(定义一个外键约束，这个外键约束作用在husband_id这列上) references(参考) husband(id)