数据库基本知识和基础sql语句

2023年6月21日发(作者：)

. .

数据库的开展历程











没有数据库，使用磁盘文件存储数据；

层次构造模型数据库；

网状构造模型数据库；

关系构造模型数据库：使用二维表格来存储数据；

关系-对象模型数据库；

理解数据库

 RDBMS = 管理员〔manager〕+仓库〔database〕

 database = N个table

 table：

 表构造：定义表的列名和列类型！

 表记录：一行一行的记录！

Mysql安装目录:

 bin目录中都是可执行文件；

 文件是MySQL的配置文件；

相关命令:

 启动：net start mysql；

 关闭：net stop mysql；

 mysql -u root -p 123 -h localhost；

➢ -u：后面的root是用户名，这里使用的是超级管理员root；

➢ -p：后面的123是密码，这是在安装MySQL时就已经指定的密码；

 退出：quit或exit；

sql语句

语法要求







分类



SQL语句可以单行或多行书写，以分号结尾；

可以用空格和缩进来来增强语句的可读性；

关键字不区别大小写，建议使用大写；

DDL〔Data Definition Language〕：数据定义语言，用来定义数据库对象：库、表、列等；

 DML〔Data Manipulation Language〕：数据操作语言，用来定义数据库记录〔数据〕；

根本操作











查看所有数据库名称：SHOW DATABASES；

切换数据库：USE mydb1，切换到mydb1数据库；

创立数据库：CREATE DATABASE [IF NOT EXISTS] mydb1；

修改数据库编码：ALTER DATABASE mydb1 CHARACTER SET utf8

创立表：

CREATE TABLE 表名(

. v . . .











1.

2.

3.

4.

5.

列名列类型,

列名列类型,

......

);

查看当前数据库中所有表名称：SHOW TABLES；

查看指定表的创立语句：SHOW CREATE TABLE emp，查看emp表的创立语句；

查看表构造：DESC emp，查看emp表构造；

删除表：DROP TABLE emp，删除emp表；

修改表：

修改之添加列：给stu表添加classname列：

ALTER TABLE stu ADD (classname varchar(100));

修改之修改列类型：修改stu表的gender列类型为CHAR(2)：

ALTER TABLE stu MODIFY gender CHAR(2);

修改之修改列名：修改stu表的gender列名为sex：

ALTER TABLE stu change gender sex CHAR(2);

修改之删除列：删除stu表的classname列：

ALTER TABLE stu DROP classname;

修改之修改表名称：修改stu表名称为student：

ALTER TABLE stu RENAME TO student;

其他常用命令:

mysql根本操作命令

一、数据库操作

1.新增数据库

create database 数据库名字 [数据库选项];

数据库选项：规定数据库部该用什么进展规

字符集：charset 具体字符集(utf8)

校对集：collate 具体校对集〔依赖字符集〕

2.查看数据库

2.1查看所有的数据库

show databases;

匹配查询：

show databases like 'pattern'; *pattern可以使用通配符

_：下划线匹配，表示匹配单个任意字符，如：_s，表示任意字符开场，但是以s结尾的数据库

%：百分号匹配，表示匹配任意个数的任意字符，如：student%，表示以student开场的所有数据库

2.2查看数据库的创立语句

. v . . .

show create database 数据库名字;

3.修改数据库

数据库名字在mysql高版本中不允许修改，所以只能修改数据库的库选项〔字符集和校对集〕

alter database 数据库名字 [数据库选项];

eg:alter database stu charset utf8;

4.删除数据库

对于数据库的删除要慎重考虑，是不可逆的。

drop database 数据库名字;

4.选择数据库

use 数据库名字;

二、数据表操作〔字段〕

1.新增数据表

create table 表名(

字段名1 数据类型 ment '备注...',

字段名2 数据类型 ment '备注...',

.... *最后一行不需要逗号

)[表选项];

表选项：

1〕字符集：charset/character set〔可以不写，默认采用数据库的〕

2〕校对集：collate

3〕存储引擎：engine = innodb〔默认的〕：存储文件的格式〔数据如何存储〕

注意：创立数据表的时候，需要指定要在哪个数据库下创立。创立方式有隐式创立和显式创立

1)显式创立：create table 数据库名字.数据表名字

2)隐式创立：use 数据库名字;

2.查看数据表

2.1查看所有的数据表

. v . . .

show tables;

2.2查看表使用匹配查询

Show tables like ‘pattern’;*与数据库的pattern一样：_和%两个通配符

2.3查看数据表的创立语句

show create table 数据表名字;

2.4查看数据表的构造

desc 数据表名字;

3.修改数据表

3.1修改表名字

rename table 旧表名 to 新表名;

3.2修改表选项〔存储引擎，字符集和校对集〕

alter table 表名 [表选项];

3.3修改字段〔新增字段，修改字段名字,修改西段类型,删除字段〕

新增字段：alter table 表名 add [column] 字段名字数据库类型 [位置first/after];

位置选项：first 在第一个字段

after 在某个字段之后，默认就是在最后一个字段后面

修改字段名称：alter table 表名 change 旧字段名字新字段名字字段数据类型 [位置];

eg:alter table student name fullname varchar(30) after id;

修改字段的数据类型：alter table

删除字段：alter table

4.删除数据表

. v .

表名 modify 字段名字数据类型 [位置];

表名 drop 字段名字; . .

drop table 表名;

三、数据操作

1. 新增数据

inser into table 表名 [(字段列表)] values 〔值列表);

2.查看数据

select */字段列表 from 表名 [where条件];

3.修改数据

update 表名 set 字段名 = 值 where 条件;

注意：使用update操作最好配合limit 1使用,防止操作大批量数据更新错误.

4.删除数据

delete from 表名 where 条件;

注意：没有where 条件就是默认删除全部数据.

四、列属性〔字段〕

1.删除主键：alter table

2.增加主键：alter table

表名 drop primary key;

表名 add primary key(字段列表);*可以是复合主键

3.删除自增长：只能通过修改字段属性的方法操作.

4.删除唯一键：alter table

本身

5.增加唯一键：alter table

五、外键约束

1.创立表的时候增加外键

constraint 外键名字 foreign key(外键字段) references 父表〔主键字段〕;

eg:

-- 创立父表〔班级表〕

表名 drop index 索引名字;*默认的唯一键名字就是字段的表名 add unique key (字段列表);*可以是复合唯一索引

. v . . .

create table class(

id int primary key auto_increment,

name varchar(10) not null ment '班级名字',

room varchar(10) not null ment '教室号'

)charset utf8;

-- 创立子表〔外键表〕create table student(

id int primary key auto_increment,

number char(10) not null unique ment '学号：itcast + 四位数',

name varchar(10) not null ment 'XX',

c_id int ment '班级ID',

-- 增加外键foreign key(c_id) references class(id))charset utf8;

2.创立表之后增加外键

alter table 表名 add constraint 外键名字 foreign key(外键字段) references 父表(主键字段);

eg:

-- 增加外键alter table student add constraint student_class_fk foreign key(c_id)

references class(id);

3.删除外键

alter table 表名 drop foreign key 外键名字; *查看外键名字需要通过表创立语句来查询.

eg:

-- 删除外键

alter table student drop foreign key student_ibfk_1;

数据查询语法〔DQL〕

DQL就是数据查询语言，数据库执行DQL语句不会对数据进展改变，而是让数据库发送结果集给客户端。

SELECT selection_list /*要查询的列名称*/

FROM table_list /*要查询的表名称*/

WHERE condition /*行条件*/

GROUP BY grouping_columns /*对结果分组*/

HAVING condition /*分组后的行条件*/

ORDER BY sorting_columns /*对结果分组*/

LIMIT offset_start, row_count /*结果限定*/

. v . . .

根底查询

1.1 查询所有列

SELECT * FROM stu;

1.2 查询指定列

SELECT sid, sname, age FROM stu;

2 条件查询

2.1 条件查询介绍

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：

 =、!=、<>、<、<=、>、>=；

 BETWEEN…AND；

 IN(set)；

 IS NULL；

 AND；

 OR；

 NOT；

2.2 查询性别为女，并且年龄50的记录

SELECT * FROM stu

WHERE gender='female' AND ge<50;

2.3 查询学号为S_1001，或者XX为liSi的记录

SELECT * FROM stu

WHERE sid ='S_1001' OR sname='liSi';

2.4 查询学号为S_1001，S_1002，S_1003的记录

SELECT * FROM stu

WHERE sid IN ('S_1001','S_1002','S_1003');

2.5 查询学号不是S_1001，S_1002，S_1003的记录

SELECT * FROM tab_student

WHERE s_number NOT IN ('S_1001','S_1002','S_1003');

2.6 查询年龄为null的记录

SELECT * FROM stu

WHERE age IS NULL;

. v . . .

2.7 查询年龄在20到40之间的学生记录

SELECT *

FROM stu

WHERE age>=20 AND age<=40;

或者

SELECT *

FROM stu

WHERE age BETWEEN 20 AND 40;

2.8 查询性别非男的学生记录

SELECT *

FROM stu

WHERE gender!='male';

或者

SELECT *

FROM stu

WHERE gender<>'male';

或者

SELECT *

FROM stu

WHERE NOT gender='male';

2.9 查询XX不为null的学生记录

SELECT *

FROM stu

WHERE NOT sname IS NULL;

或者

SELECT *

FROM stu

WHERE sname IS NOT NULL;

3 模糊查询

当想查询XX中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。

3.1 查询XX由5个字母构成的学生记录

SELECT *

FROM stu

WHERE sname LIKE '_____';

模糊查询必须使用LIKE关键字。其中“_〞匹配任意一个字母，5个“_〞表示5个任意字母。

. v . . .

3.2 查询XX由5个字母构成，并且第5个字母为“i〞的学生记录

SELECT *

FROM stu

WHERE sname LIKE '____i';

3.3 查询XX以“z〞开头的学生记录

SELECT *

FROM stu

WHERE sname LIKE 'z%';

其中“%〞匹配0~n个任何字母。

3.4 查询XX中第2个字母为“i〞的学生记录

SELECT *

FROM stu

WHERE sname LIKE '_i%';

3.5 查询XX中包含“a〞字母的学生记录

SELECT *

FROM stu

WHERE sname LIKE '%a%';

4 字段控制查询

4.1 去除重复记录

去除重复记录〔两行或两行以上记录中系列的上的数据都一样〕，例如emp表中sal字段就存在一样的记录。当只查询emp表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用DISTINCT：

SELECT DISTINCT sal FROM emp;

4.2 查看雇员的月薪与佣金之和

因为sal和m两列的类型都是数值类型，所以可以做加运算。如果sal或m中有一个字段不是数值类型，那么会出错。

SELECT *,sal+m FROM emp;

m列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL：

SELECT *,sal+IFNULL(m,0) FROM emp;

4.3 给列名添加别名

在上面查询中出现列名为sal+IFNULL(m,0)，这很不美观，现在我们给这一列给出一个别名，为total：

SELECT *, sal+IFNULL(m,0) AS total FROM emp;

. v . . .

给列起别名时，是可以省略AS关键字的：

SELECT *,sal+IFNULL(m,0) total FROM emp;

5 排序

5.1 查询所有学生记录，按年龄升序排序

SELECT *

FROM stu

ORDER BY sage ASC;

或者

SELECT *

FROM stu

ORDER BY sage;

5.2 查询所有学生记录，按年龄降序排序

SELECT *

FROM stu

ORDER BY age DESC;

5.3 查询所有雇员，按月薪降序排序，如果月薪一样时，按编号升序排序

SELECT * FROM emp

ORDER BY sal DESC,empno ASC;

6 聚合函数

聚合函数是用来做纵向运算的函数：

 COUNT()：统计指定列不为NULL的记录行数；

 MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

 MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

 SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

 AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

6.1COUNT

当需要纵向统计时可以使用COUNT()。

 查询emp表中记录数：

SELECT COUNT(*) AS t FROM emp;

 查询emp表中有佣金的人数：

SELECT COUNT(m) t FROM emp;

注意，因为count()函数中给出的是m列，那么只统计m列非NULL的行数。

 查询emp表中月薪大于2500的人数：

. v . . .

SELECT COUNT(*) FROM emp

WHERE sal > 2500;

 统计月薪与佣金之和大于2500元的人数：

SELECT COUNT(*) AS t FROM emp WHERE sal+IFNULL(m,0) > 2500;

 查询有佣金的人数，以及有领导的人数：

SELECT COUNT(m), COUNT(mgr) FROM emp;

6.2SUM和AVG

当需要纵向求和时使用sum()函数。

 查询所有雇员月薪和：

SELECT SUM(sal) FROM emp;

 查询所有雇员月薪和，以及所有雇员佣金和：

SELECT SUM(sal), SUM(m) FROM emp;

 查询所有雇员月薪+佣金和：

SELECT SUM(sal+IFNULL(m,0)) FROM emp;

 统计所有员工平均工资：

SELECT SUM(sal), COUNT(sal) FROM emp;

或者

SELECT AVG(sal) FROM emp;

6.3MAX和MIN

 查询最高工资和最低工资：

SELECT MAX(sal), MIN(sal) FROM emp;

分组查询

当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用局部来分组。

7.1 分组查询

 查询每个部门的部门编号和每个部门的工资和：

SELECT deptno, SUM(sal)

FROM emp

GROUP BY deptno;

 查询每个部门的部门编号以及每个部门的人数：

SELECT deptno,COUNT(*)

FROM emp

GROUP BY deptno;

 查询每个部门的部门编号以及每个部门工资大于1500的人数：

SELECT deptno,COUNT(*)

FROM emp

WHERE sal>1500

GROUP BY deptno;

. v . . .

HAVING子句

 查询工资总和大于9000的部门编号以及工资和：

SELECT deptno, SUM(sal)

FROM emp

GROUP BY deptno

HAVING SUM(sal) > 9000;

注意，WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

8LIMIT

LIMIT用来限定查询结果的起始行，以及总行数。

8.1 查询5行记录，起始行从0开场

SELECT * FROM emp LIMIT 0, 5;

注意，起始行从0开场，即第一行开场！

8.2 查询10行记录，起始行从3开场

SELECT * FROM emp LIMIT 3, 10;

8.3 分页查询

如果一页记录为10条，希望查看第3页记录应该怎么查呢.

 第一页记录起始行为0，一共查询10行；

 第二页记录起始行为10，一共查询10行；

 第三页记录起始行为20，一共查询10行；

多表连接查询

 连接查询

➢ 连接

➢ 外连接

 左外连接

 右外连接

 全外连接〔MySQL不支持〕

➢ 自然连接

 子查询

连接查询

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。

连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，那么两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

. v . . .

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

2.1 连接

上面的连接语句就是连接，但它不是SQL标准中的查询方式，可以理解为方言！SQL标准的连接为：

SELECT *

FROM emp e

INNER JOIN dept d

ON =;

连接的特点：查询结果必须满足条件。例如我们向emp表中插入一条记录：

其中deptno为50，而在dept表中只有10、20、30、40部门，那么上面的查询结果中就不会出现“三〞这条记录，因为它不能满足=这个条件。

2.2 外连接〔左连接、右连接〕

外连接的特点：查询出的结果存在不满足条件的可能。

左连接：

SELECT * FROM emp e

LEFT OUTER JOIN dept d

ON =;

左连接是先查询出左表〔即以左表为主〕，然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

子查询

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

 子查询出现的位置：

➢ where后，作为条件的一局部；

➢ from后，作为被查询的一条表；

 当子查询出现在where后作为条件时，还可以使用如下关键字：

➢ any

➢ all

 子查询结果集的形式：

➢ 单行单列〔用于条件〕

➢ 单行多列〔用于条件〕

➢ 多行单列〔用于条件〕

➢ 多行多列〔用于表〕

练习：

1. 工资高于smith的员工。

分析：

查询条件：工资>smith工资，其中smith工资需要一条子查询。

第一步：查询smith的工资

SELECT sal FROM emp WHERE ename='smith'

. v . . .

第二步：查询高于smith工资的员工

SELECT * FROM emp WHERE sal > (${第一步})

结果：

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='smith')

 子查询作为条件

 子查询形式为单行单列

2. 工资高于30部门所有人的员工信息

分析：

查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键字。

第一步：查询30部门所有人工资

SELECT sal FROM emp WHERE deptno=30;

第二步：查询高于30部门所有人工资的员工信息

SELECT * FROM emp WHERE sal > ALL (${第一步})

结果：

SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)

 子查询作为条件

 子查询形式为多行单列〔当子查询结果集形式为多行单列时可以使用ALL或ANY关键字〕

3. 查询工作和工资与smith完全一样的员工信息

分析：

查询条件：工作和工资与smith完全一样，这是子查询

第一步：查询出smith的工作和工资

SELECT job,sal FROM emp WHERE ename='smith'

第二步：查询出与smith工作和工资一样的人

SELECT * FROM emp WHERE (job,sal) IN (${第一步})

结果：

SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE

ename='smith')

 子查询作为条件

 子查询形式为单行多列

4. 查询员工编号为1006的员工名称、员工工资、部门名称、部门地址

分析：

查询列：员工名称、员工工资、部门名称、部门地址

查询表：emp和dept，分析得出，不需要外连接〔外连接的特性：某一行〔或某些行〕记录上会出现一半有值，一半为NULL值〕

条件：员工编号为1006

第一步：去除多表，只查一表，这里去除部门表，只查员工表

SELECT ename, sal FROM emp e WHERE empno=1006

第二步：让第一步与dept做连接查询，添加主外键条件去除无用笛卡尔积

SELECT , , ,

FROM emp e, dept d

WHERE = AND empno=1006

第二步中的dept表表示所有行所有列的一完整的表，这里可以把dept替换成所有. v . . .

行，但只有dname和loc列的表，这需要子查询。

第三步：查询dept表中dname和loc两列，因为deptno会被作为条件，用来去除无用笛卡尔积，所以需要查询它。

SELECT dname,loc,deptno FROM dept;

第四步：替换第二步中的dept

SELECT , , ,

FROM emp e, (SELECT dname,loc,deptno FROM dept) d

WHERE = AND =1006

 子查询作为表

 子查询形式为多行多列

. v .

2023年6月21日发(作者：)

. .

数据库的开展历程











没有数据库，使用磁盘文件存储数据；

层次构造模型数据库；

网状构造模型数据库；

关系构造模型数据库：使用二维表格来存储数据；

关系-对象模型数据库；

理解数据库

 RDBMS = 管理员〔manager〕+仓库〔database〕

 database = N个table

 table：

 表构造：定义表的列名和列类型！

 表记录：一行一行的记录！

Mysql安装目录:

 bin目录中都是可执行文件；

 文件是MySQL的配置文件；

相关命令:

 启动：net start mysql；

 关闭：net stop mysql；

 mysql -u root -p 123 -h localhost；

➢ -u：后面的root是用户名，这里使用的是超级管理员root；

➢ -p：后面的123是密码，这是在安装MySQL时就已经指定的密码；

 退出：quit或exit；

sql语句

语法要求







分类



SQL语句可以单行或多行书写，以分号结尾；

可以用空格和缩进来来增强语句的可读性；

关键字不区别大小写，建议使用大写；

DDL〔Data Definition Language〕：数据定义语言，用来定义数据库对象：库、表、列等；

 DML〔Data Manipulation Language〕：数据操作语言，用来定义数据库记录〔数据〕；

根本操作











查看所有数据库名称：SHOW DATABASES；

切换数据库：USE mydb1，切换到mydb1数据库；

创立数据库：CREATE DATABASE [IF NOT EXISTS] mydb1；

修改数据库编码：ALTER DATABASE mydb1 CHARACTER SET utf8

创立表：

CREATE TABLE 表名(

. v . . .











1.

2.

3.

4.

5.

列名列类型,

列名列类型,

......

);

查看当前数据库中所有表名称：SHOW TABLES；

查看指定表的创立语句：SHOW CREATE TABLE emp，查看emp表的创立语句；

查看表构造：DESC emp，查看emp表构造；

删除表：DROP TABLE emp，删除emp表；

修改表：

修改之添加列：给stu表添加classname列：

ALTER TABLE stu ADD (classname varchar(100));

修改之修改列类型：修改stu表的gender列类型为CHAR(2)：

ALTER TABLE stu MODIFY gender CHAR(2);

修改之修改列名：修改stu表的gender列名为sex：

ALTER TABLE stu change gender sex CHAR(2);

修改之删除列：删除stu表的classname列：

ALTER TABLE stu DROP classname;

修改之修改表名称：修改stu表名称为student：

ALTER TABLE stu RENAME TO student;

其他常用命令:

mysql根本操作命令

一、数据库操作

1.新增数据库

create database 数据库名字 [数据库选项];

数据库选项：规定数据库部该用什么进展规

字符集：charset 具体字符集(utf8)

校对集：collate 具体校对集〔依赖字符集〕

2.查看数据库

2.1查看所有的数据库

show databases;

匹配查询：

show databases like 'pattern'; *pattern可以使用通配符

_：下划线匹配，表示匹配单个任意字符，如：_s，表示任意字符开场，但是以s结尾的数据库

%：百分号匹配，表示匹配任意个数的任意字符，如：student%，表示以student开场的所有数据库

2.2查看数据库的创立语句

. v . . .

show create database 数据库名字;

3.修改数据库

数据库名字在mysql高版本中不允许修改，所以只能修改数据库的库选项〔字符集和校对集〕

alter database 数据库名字 [数据库选项];

eg:alter database stu charset utf8;

4.删除数据库

对于数据库的删除要慎重考虑，是不可逆的。

drop database 数据库名字;

4.选择数据库

use 数据库名字;

二、数据表操作〔字段〕

1.新增数据表

create table 表名(

字段名1 数据类型 ment '备注...',

字段名2 数据类型 ment '备注...',

.... *最后一行不需要逗号

)[表选项];

表选项：

1〕字符集：charset/character set〔可以不写，默认采用数据库的〕

2〕校对集：collate

3〕存储引擎：engine = innodb〔默认的〕：存储文件的格式〔数据如何存储〕

注意：创立数据表的时候，需要指定要在哪个数据库下创立。创立方式有隐式创立和显式创立

1)显式创立：create table 数据库名字.数据表名字

2)隐式创立：use 数据库名字;

2.查看数据表

2.1查看所有的数据表

. v . . .

show tables;

2.2查看表使用匹配查询

Show tables like ‘pattern’;*与数据库的pattern一样：_和%两个通配符

2.3查看数据表的创立语句

show create table 数据表名字;

2.4查看数据表的构造

desc 数据表名字;

3.修改数据表

3.1修改表名字

rename table 旧表名 to 新表名;

3.2修改表选项〔存储引擎，字符集和校对集〕

alter table 表名 [表选项];

3.3修改字段〔新增字段，修改字段名字,修改西段类型,删除字段〕

新增字段：alter table 表名 add [column] 字段名字数据库类型 [位置first/after];

位置选项：first 在第一个字段

after 在某个字段之后，默认就是在最后一个字段后面

修改字段名称：alter table 表名 change 旧字段名字新字段名字字段数据类型 [位置];

eg:alter table student name fullname varchar(30) after id;

修改字段的数据类型：alter table

删除字段：alter table

4.删除数据表

. v .

表名 modify 字段名字数据类型 [位置];

表名 drop 字段名字; . .

drop table 表名;

三、数据操作

1. 新增数据

inser into table 表名 [(字段列表)] values 〔值列表);

2.查看数据

select */字段列表 from 表名 [where条件];

3.修改数据

update 表名 set 字段名 = 值 where 条件;

注意：使用update操作最好配合limit 1使用,防止操作大批量数据更新错误.

4.删除数据

delete from 表名 where 条件;

注意：没有where 条件就是默认删除全部数据.

四、列属性〔字段〕

1.删除主键：alter table

2.增加主键：alter table

表名 drop primary key;

表名 add primary key(字段列表);*可以是复合主键

3.删除自增长：只能通过修改字段属性的方法操作.

4.删除唯一键：alter table

本身

5.增加唯一键：alter table

五、外键约束

1.创立表的时候增加外键

constraint 外键名字 foreign key(外键字段) references 父表〔主键字段〕;

eg:

-- 创立父表〔班级表〕

表名 drop index 索引名字;*默认的唯一键名字就是字段的表名 add unique key (字段列表);*可以是复合唯一索引

. v . . .

create table class(

id int primary key auto_increment,

name varchar(10) not null ment '班级名字',

room varchar(10) not null ment '教室号'

)charset utf8;

-- 创立子表〔外键表〕create table student(

id int primary key auto_increment,

number char(10) not null unique ment '学号：itcast + 四位数',

name varchar(10) not null ment 'XX',

c_id int ment '班级ID',

-- 增加外键foreign key(c_id) references class(id))charset utf8;

2.创立表之后增加外键

alter table 表名 add constraint 外键名字 foreign key(外键字段) references 父表(主键字段);

eg:

-- 增加外键alter table student add constraint student_class_fk foreign key(c_id)

references class(id);

3.删除外键

alter table 表名 drop foreign key 外键名字; *查看外键名字需要通过表创立语句来查询.

eg:

-- 删除外键

alter table student drop foreign key student_ibfk_1;

数据查询语法〔DQL〕

DQL就是数据查询语言，数据库执行DQL语句不会对数据进展改变，而是让数据库发送结果集给客户端。

SELECT selection_list /*要查询的列名称*/

FROM table_list /*要查询的表名称*/

WHERE condition /*行条件*/

GROUP BY grouping_columns /*对结果分组*/

HAVING condition /*分组后的行条件*/

ORDER BY sorting_columns /*对结果分组*/

LIMIT offset_start, row_count /*结果限定*/

. v . . .

根底查询

1.1 查询所有列

SELECT * FROM stu;

1.2 查询指定列

SELECT sid, sname, age FROM stu;

2 条件查询

2.1 条件查询介绍

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：

 =、!=、<>、<、<=、>、>=；

 BETWEEN…AND；

 IN(set)；

 IS NULL；

 AND；

 OR；

 NOT；

2.2 查询性别为女，并且年龄50的记录

SELECT * FROM stu

WHERE gender='female' AND ge<50;

2.3 查询学号为S_1001，或者XX为liSi的记录

SELECT * FROM stu

WHERE sid ='S_1001' OR sname='liSi';

2.4 查询学号为S_1001，S_1002，S_1003的记录

SELECT * FROM stu

WHERE sid IN ('S_1001','S_1002','S_1003');

2.5 查询学号不是S_1001，S_1002，S_1003的记录

SELECT * FROM tab_student

WHERE s_number NOT IN ('S_1001','S_1002','S_1003');

2.6 查询年龄为null的记录

SELECT * FROM stu

WHERE age IS NULL;

. v . . .

2.7 查询年龄在20到40之间的学生记录

SELECT *

FROM stu

WHERE age>=20 AND age<=40;

或者

SELECT *

FROM stu

WHERE age BETWEEN 20 AND 40;

2.8 查询性别非男的学生记录

SELECT *

FROM stu

WHERE gender!='male';

或者

SELECT *

FROM stu

WHERE gender<>'male';

或者

SELECT *

FROM stu

WHERE NOT gender='male';

2.9 查询XX不为null的学生记录

SELECT *

FROM stu

WHERE NOT sname IS NULL;

或者

SELECT *

FROM stu

WHERE sname IS NOT NULL;

3 模糊查询

当想查询XX中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。

3.1 查询XX由5个字母构成的学生记录

SELECT *

FROM stu

WHERE sname LIKE '_____';

模糊查询必须使用LIKE关键字。其中“_〞匹配任意一个字母，5个“_〞表示5个任意字母。

. v . . .

3.2 查询XX由5个字母构成，并且第5个字母为“i〞的学生记录

SELECT *

FROM stu

WHERE sname LIKE '____i';

3.3 查询XX以“z〞开头的学生记录

SELECT *

FROM stu

WHERE sname LIKE 'z%';

其中“%〞匹配0~n个任何字母。

3.4 查询XX中第2个字母为“i〞的学生记录

SELECT *

FROM stu

WHERE sname LIKE '_i%';

3.5 查询XX中包含“a〞字母的学生记录

SELECT *

FROM stu

WHERE sname LIKE '%a%';

4 字段控制查询

4.1 去除重复记录

去除重复记录〔两行或两行以上记录中系列的上的数据都一样〕，例如emp表中sal字段就存在一样的记录。当只查询emp表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用DISTINCT：

SELECT DISTINCT sal FROM emp;

4.2 查看雇员的月薪与佣金之和

因为sal和m两列的类型都是数值类型，所以可以做加运算。如果sal或m中有一个字段不是数值类型，那么会出错。

SELECT *,sal+m FROM emp;

m列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL：

SELECT *,sal+IFNULL(m,0) FROM emp;

4.3 给列名添加别名

在上面查询中出现列名为sal+IFNULL(m,0)，这很不美观，现在我们给这一列给出一个别名，为total：

SELECT *, sal+IFNULL(m,0) AS total FROM emp;

. v . . .

给列起别名时，是可以省略AS关键字的：

SELECT *,sal+IFNULL(m,0) total FROM emp;

5 排序

5.1 查询所有学生记录，按年龄升序排序

SELECT *

FROM stu

ORDER BY sage ASC;

或者

SELECT *

FROM stu

ORDER BY sage;

5.2 查询所有学生记录，按年龄降序排序

SELECT *

FROM stu

ORDER BY age DESC;

5.3 查询所有雇员，按月薪降序排序，如果月薪一样时，按编号升序排序

SELECT * FROM emp

ORDER BY sal DESC,empno ASC;

6 聚合函数

聚合函数是用来做纵向运算的函数：

 COUNT()：统计指定列不为NULL的记录行数；

 MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

 MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

 SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

 AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

6.1COUNT

当需要纵向统计时可以使用COUNT()。

 查询emp表中记录数：

SELECT COUNT(*) AS t FROM emp;

 查询emp表中有佣金的人数：

SELECT COUNT(m) t FROM emp;

注意，因为count()函数中给出的是m列，那么只统计m列非NULL的行数。

 查询emp表中月薪大于2500的人数：

. v . . .

SELECT COUNT(*) FROM emp

WHERE sal > 2500;

 统计月薪与佣金之和大于2500元的人数：

SELECT COUNT(*) AS t FROM emp WHERE sal+IFNULL(m,0) > 2500;

 查询有佣金的人数，以及有领导的人数：

SELECT COUNT(m), COUNT(mgr) FROM emp;

6.2SUM和AVG

当需要纵向求和时使用sum()函数。

 查询所有雇员月薪和：

SELECT SUM(sal) FROM emp;

 查询所有雇员月薪和，以及所有雇员佣金和：

SELECT SUM(sal), SUM(m) FROM emp;

 查询所有雇员月薪+佣金和：

SELECT SUM(sal+IFNULL(m,0)) FROM emp;

 统计所有员工平均工资：

SELECT SUM(sal), COUNT(sal) FROM emp;

或者

SELECT AVG(sal) FROM emp;

6.3MAX和MIN

 查询最高工资和最低工资：

SELECT MAX(sal), MIN(sal) FROM emp;

分组查询

当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用局部来分组。

7.1 分组查询

 查询每个部门的部门编号和每个部门的工资和：

SELECT deptno, SUM(sal)

FROM emp

GROUP BY deptno;

 查询每个部门的部门编号以及每个部门的人数：

SELECT deptno,COUNT(*)

FROM emp

GROUP BY deptno;

 查询每个部门的部门编号以及每个部门工资大于1500的人数：

SELECT deptno,COUNT(*)

FROM emp

WHERE sal>1500

GROUP BY deptno;

. v . . .

HAVING子句

 查询工资总和大于9000的部门编号以及工资和：

SELECT deptno, SUM(sal)

FROM emp

GROUP BY deptno

HAVING SUM(sal) > 9000;

注意，WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

8LIMIT

LIMIT用来限定查询结果的起始行，以及总行数。

8.1 查询5行记录，起始行从0开场

SELECT * FROM emp LIMIT 0, 5;

注意，起始行从0开场，即第一行开场！

8.2 查询10行记录，起始行从3开场

SELECT * FROM emp LIMIT 3, 10;

8.3 分页查询

如果一页记录为10条，希望查看第3页记录应该怎么查呢.

 第一页记录起始行为0，一共查询10行；

 第二页记录起始行为10，一共查询10行；

 第三页记录起始行为20，一共查询10行；

多表连接查询

 连接查询

➢ 连接

➢ 外连接

 左外连接

 右外连接

 全外连接〔MySQL不支持〕

➢ 自然连接

 子查询

连接查询

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。

连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，那么两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

. v . . .

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

2.1 连接

上面的连接语句就是连接，但它不是SQL标准中的查询方式，可以理解为方言！SQL标准的连接为：

SELECT *

FROM emp e

INNER JOIN dept d

ON =;

连接的特点：查询结果必须满足条件。例如我们向emp表中插入一条记录：

其中deptno为50，而在dept表中只有10、20、30、40部门，那么上面的查询结果中就不会出现“三〞这条记录，因为它不能满足=这个条件。

2.2 外连接〔左连接、右连接〕

外连接的特点：查询出的结果存在不满足条件的可能。

左连接：

SELECT * FROM emp e

LEFT OUTER JOIN dept d

ON =;

左连接是先查询出左表〔即以左表为主〕，然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

子查询

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

 子查询出现的位置：

➢ where后，作为条件的一局部；

➢ from后，作为被查询的一条表；

 当子查询出现在where后作为条件时，还可以使用如下关键字：

➢ any

➢ all

 子查询结果集的形式：

➢ 单行单列〔用于条件〕

➢ 单行多列〔用于条件〕

➢ 多行单列〔用于条件〕

➢ 多行多列〔用于表〕

练习：

1. 工资高于smith的员工。

分析：

查询条件：工资>smith工资，其中smith工资需要一条子查询。

第一步：查询smith的工资

SELECT sal FROM emp WHERE ename='smith'

. v . . .

第二步：查询高于smith工资的员工

SELECT * FROM emp WHERE sal > (${第一步})

结果：

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='smith')

 子查询作为条件

 子查询形式为单行单列

2. 工资高于30部门所有人的员工信息

分析：

查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键字。

第一步：查询30部门所有人工资

SELECT sal FROM emp WHERE deptno=30;

第二步：查询高于30部门所有人工资的员工信息

SELECT * FROM emp WHERE sal > ALL (${第一步})

结果：

SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)

 子查询作为条件

 子查询形式为多行单列〔当子查询结果集形式为多行单列时可以使用ALL或ANY关键字〕

3. 查询工作和工资与smith完全一样的员工信息

分析：

查询条件：工作和工资与smith完全一样，这是子查询

第一步：查询出smith的工作和工资

SELECT job,sal FROM emp WHERE ename='smith'

第二步：查询出与smith工作和工资一样的人

SELECT * FROM emp WHERE (job,sal) IN (${第一步})

结果：

SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE

ename='smith')

 子查询作为条件

 子查询形式为单行多列

4. 查询员工编号为1006的员工名称、员工工资、部门名称、部门地址

分析：

查询列：员工名称、员工工资、部门名称、部门地址

查询表：emp和dept，分析得出，不需要外连接〔外连接的特性：某一行〔或某些行〕记录上会出现一半有值，一半为NULL值〕

条件：员工编号为1006

第一步：去除多表，只查一表，这里去除部门表，只查员工表

SELECT ename, sal FROM emp e WHERE empno=1006

第二步：让第一步与dept做连接查询，添加主外键条件去除无用笛卡尔积

SELECT , , ,

FROM emp e, dept d

WHERE = AND empno=1006

第二步中的dept表表示所有行所有列的一完整的表，这里可以把dept替换成所有. v . . .

行，但只有dname和loc列的表，这需要子查询。

第三步：查询dept表中dname和loc两列，因为deptno会被作为条件，用来去除无用笛卡尔积，所以需要查询它。

SELECT dname,loc,deptno FROM dept;

第四步：替换第二步中的dept

SELECT , , ,

FROM emp e, (SELECT dname,loc,deptno FROM dept) d

WHERE = AND =1006

 子查询作为表

 子查询形式为多行多列

. v .