SQL语言基础

SQL全称： Structured Query Language，结构化查询语言
- 用于访问和处理数据库的标准的计算机语言
- 简单而言，SQL语言就是操作数据库的专用工具
- 基于功能可分为4类：
  - 数据定义：DDL（Data Definition Language）
    - 库的创建删除、表的创建删除等
  - 数据操纵：DML（Data Manipulation Language）
    - 新增数据、删除数据、修改数据
  - 数据控制：DCL（Data Control Language）
    - 新增用户、删除用户、密码修改、权限管理等
  - 数据查询：DQL（Data Query Language）
    - 基于需求查询和计算数据
SQL语法特征
- 大小写不敏感
- 可以单行或多行书写，最后 以;号 结束
- 支持注释：
  - 单行注释： -- 注释内容（--后面一定要有一个空格）
  - 单行注释：# 注释内容（# 后面可以不加空格，推荐加上）
  - 多行注释：/* 注释内容 */

（二）验证mysql数据库是否已经安转完成

1、启动mysql客户端，连接服务器

（1）语法

mysql -u root -p

mysql -uroot -p

（2）说明

-u：指定用户名
-p：回车后输入密码

2、能进入到此页面说明安装成功，并且以后想要在cmd命令行使用mysql函数也需要使用此命令进入数据库来操作

3、实际上的命令全称是：

（1）语法

mysql -h localhost -P 3306 -u root -p

（2）说明：

-h：表示要连接哪台主机（自己的主机就是localhost）
-P：表示连接数据库的端口号（默认3306）
如果不指定-h的话，默认连接就是本机，所以可以省略
如果不指定端口的话，默认连接3306

4、输入exit / quit 就可以退出mysql客户端程序

二、操作数据库

（一）查看数据库

1、语法

（1）展示服务器中已创建好的所有数据库

show databases;

其中：informatio_schema、mysql、performance_schema、test都是数据库自带的，不可随意删除修改

（2）查看数据库的结构

show create database mydb;

（二）创建数据库

1、语法

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

IF NOT EXISTS：在创建前判断是否存在，如果不存在则创建，存在则不创建也不报错
create_specification：CHARACTER SET charset_name # 数据库默认编码集；
COLLATE collation_name # 数据库默认校对规则
“[ ]”内的内容表示是可选的

2、案例

（1）创建一个名称为mydb的数据库

create database mydb;

（2）创建一个使用gbk字符集的mydb1数据库

create database mydb1 character set gbk;

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

 create database mydb3 character set utf8 collate utf8_bin;

需要注意的是创建数据库的编码集是utf-8，但是mysql语句中不能写入“-”
因为在mysql语句中“-”表示有特殊含义，mysql会理解成有一个列，且列名是utf然后将列中的值减去8，不能表示我们的utf-8编码。所以使用utf8。

（三）修改数据库

1、语法

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

IF NOT EXISTS：在修改前判断是否存在，如果存在则修改，如果不存在则不修改也不报错
alter_specification：CHARACTER SET charset_name #修改数据库字符集编码集；
COLLATE collation_name #修改数据库校对规则

2、案例

（1）把mydb1库的字符集修改为utf8

alter database mydb1 character set utf8;

（四）删除数据库

1、语法

DROP DATABASE [IF EXISTS] db_name

IF EXISTS：在删除前判断是否存在，如果存在则删除，如果不存在则不删除也不报错

2、案例

（1）删除前面创建的mydb数据库

drop database mydb;

（五）使用数据库

1、语法

use database;

（1）说明：跳转到指定的数据库下，接下来的所有操作将在该数据库下完成

三、mysql数据类型

（一）字符类型

1、varchar

eg：username varchar(20)

变长的字符串，需要在声明字段时指定能存储的最大字符数，真实占用的空间取决于数据本身的字符数，存入的越多占用空间越多。Varchar占用的空间更少。最大65535字节。

适合保存内容长度不定的字符类型数据。

2、char

eg：gender char(5)

定长字符串，需要在声明字段时指定固定字符数。即使存入的字符数少于该长度，该字段也会占用该固定长度。char类型的操作效率高于varchar最大255字节。

适合存储长度不变的字符类型数据。

（二）大数类型

1、Blob

大二进制类型，可以存入二进制类型的数据，通过这个字段，可以将图片、音频、视频等数据以二进制的形式存入数据库。最大为4GB。一般不直接存入音频文件等，因为会造成数据库的加载缓慢。而是在数据库中存入音频文件的磁盘地址，从磁盘中读取。

2、Text

大文本，被声明为这种类型的字段，可以保存大量的字符数据，最大为4GB。

注意：text属于mysql中特有的数据类型，在其他数据库中为clob类型

（三）数值类型

1、TINYINT

占用1个字节，相当于java中的byte

2、SMALLINT

占用2个字节，相当于java中的short

3、INT

占用4个字节，相当于java中的int

4、BIGINT

占用8个字节，相当于java中的long

5、FLOAT

占用4个字节，浮点型，相当于java中的float

6.DOUBLE

占用8个字节，浮点型，相当于java中的double

（三）逻辑型

1、BIT

位类型，只能保存0或1，对应到java中可以是boolean型。

（四）日期型

1、DATE

日期

2020-09-10

2、TIME

时间

20:30:22

3、DATETIME

日期时间

2020-09-10 20:30:22

4、TIMESTAMP

时间戳

2020-09-10 20:30:22

和DATATIME不同，TIMESTAMP类型底层存储的是毫秒值数据。TIMESTAME类型的数据有自动更新机制，即当数据发生变更时，时间戳会自动更新为最新的时间。

四、约束

（一）主键约束

通常每张表都会有一个字段或多个字段联合起来唯一标识表记录，这样的字段称为这张表的主键字段。

可以为这样的字段增加相应的主键相关的约束（额外的要求）称之为主键约束。

对于数值类型的主键还可以增加自动增长机制，数据库会给该字段维护一个以0开始的计数器，每当需要新的主键值，则在计数器原有的基础上+1来作为新的主键值。

主键约束要求字段必须非空且唯一。

primary key [auto_increment]

（二）唯一约束

如果需要指定某个字段的值不能重复，可以为该字段指定唯一约束。

unique

（三）非空约束

如果需要指定某个字段的值不能为空，可以为该字段指定非空约束。

not null

五、操作数据库表

注意：需要先选择数据库

（一）创建表

1、语法

CREATE TABLE table_name

(

field1 datatype [cons],

field2 datatype [cons],

field3 datatype [cons]

) [character set 字符集] [collate 校对规则];

field：指定列名
datatype：指定列类型
cons：约束条件
如果不指定表的字符集编码与校对规则，那么就会默认为表所在数据库的字符集编码与校对规则

2、案例

（1）创建employee表，无任何约束

字段	属性	约束
id	整型
name	字符型
gender	字符型
birthday	日期型
entry_date	日期型
job	字符型
salary	小数型
resume	大文本型

use mydb1;

create table employee(

id int,

name varchar(20),

gender char(1),

birthday date,

entry_date date,

job varchar(20),

salary double,

resume text

);

（2）创建Employee2表，加入指定约束

字段	属性	约束
id	整型	主键约束，自动增长（保证表中id字段永远不会重复）
name	字符型	唯一约束
gender	字符型	非空约束
birthday	日期型
entry_date	日期型
job	字符型
salary	小数型
resume	大文本型

use mydb1;

create table employee2(

id int primary key auto_increment,

name varchar(20) unique,

gender char(1) not null,

birthday date,

entry_date date,

job varchar(20),

salary double,

resume text

);

（二）查看表

1、列出数据库中所有表

（1）语法：

SHOW TABLES

（2）案例：

列出mydb1数据库下的所有表

use mydb1;

show tables;

2、查看指定的表结构

（1）语法：

DESC tab_name

（2）案例：

查看刚刚创建的表employee2

desc employee2;

3.、查看指定表的建表语句

（1）语法：

SHOW CREATE TABLE tab_name

（2）案例：

查看表employee2的建表语句

show create table employee2;

（三）修改表

1、增加列

（1）语法：

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

（2）案例：

在上面employee表的基础上增加一个image列，类型为大二进制型

alter table employee add (image blob);

2、修改列

（1）语法：

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

（2）案例：

修改job列，使其长度为70

alter table employee modify job varchar(70);

3、删除列

（1）语法：

ALTER TABLE tab_name DROP (column);

（2）案例：

删除gender列

alter table employee drop gender;

4、修改表名

（1）语法：

ALTER TABLE old_tab_name RENAME TO new_tab_name;

或

RENAME TABLE old_tab_name TO new_tab_name;

（2）案例：

表名改为user

alter table employee rename to user;

或

rename table employee to user;

5、修改列名

（1）语法：

ALTER TABLE tab_name CHANGE [column] old_col_name new_col_name datatype;

（2）案例：

列名name修改为username

alter table user change name username varchar(20);

6、修改列的顺序

（1）语法：

ALTER TABLE tab_name MODIFY col_name1 datatype AFTER col_name2;

或

ALTER TABLE tab_name MODIFY col_name1 datatype FIRST;

（2）案例：

① 将image插入到username列的后面

alter table user modify image blob after username;

② 将image列插入到表中的首行

alter table user modify image blob first;

7、修改表的字符集

（1）语法：

ALTER TABLE tab_name CHARACTER SET character_name;

（2）案例：

修改表的字符集为utf8

alter table user character set utf8;

（四）删除表

1、语法

DROP TABLE tab_name;

2、案例

删除user表

drop table user;

六、操作表记录

（一）新增表记录

1、语法

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

插入的数据应与字段的数据类型相同;
数据的大小应在列的规定范围内;
在values后声明的值必须和values前声明的列相匹配;
字符串和日期格式的数据要用单引号包围起来;
可以省略列的声明，值接按照表的列顺序指给定；

2、新建employee表做准备

use mydb1;

create table employee(

id int primary key auto_increment,

name varchar(20) unique,

gender char(1) not null,

birthday date,

entry_date date,

job varchar(20),

salary double,

resume text

);

3、案例

（1）向员工表中插入三条数据

insert into employee (id,name,gender,birthday,entry_date,job,salary,resume) values (null,'张三','male','2022-02-15','2022-03-01','boss',9999.9,'这是老板~');

insert into employee (id,name,gender,birthday,entry_date,job,salary,resume) values (null,'李四','female','2022-01-01','2022-02-03','ceo','1000.99','这是老板娘');

insert into employee (id,name,gender,birthday,entry_date,job,salary,resume) values (null,'王五','male','2022-02-02','2022-06-06','driver','11.11','这是一个单身狗');

（二）修改表记录

1、语法

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

（1）说明：

UPDATE语法可以实现对表记录的修改;
SET子句指定要修改哪些列，要给予哪些值;
WHERE子句指定修改符合什么条件的表记录中;
如没有WHERE子句，则修改所有的行。

2.案例

（1）将所有员工薪水修改为5000元

update employee set salary=5000;

（2）将姓名为‘张三’的员工薪水修改为3000.33元

update employee set salary=3000.33 where name = '张三';

（3）将姓名为‘李四’的员工薪水修改为4000元，job改为‘保安’

update employee set salary=4000,job='保安' where name='李四';

（4）将’王五’的薪水在原有基础上增加1000元

update employee set salary=salary+1000 where name ='王五';

（三）删除表记录

1、语法

DELETE FROM tab_name [WHERE where_definition]

where用来指定要删除符合那些条件的表记录;
如果不使用where子句，将删除表中所有数据;
delete语句不能删除某一列的值（可使用update）;
delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。

TRUNCATE TABLE tab_name

（1）说明：

delete是一条条删除记录，truncate是摧毁整表再重建相同结构的表，truncate效率更高。

2、案例

（1）删除表中名称为’张飞’的记录

delete from employee where name='张三';

（2）删除表中所有记录

delete from employee;

（3）使用truncate删除表中记录

truncate table employee;

（四）查询表记录

1、新建数据库表并且插入数据，为后续操作做准备

create table exam(

id int primary key auto_increment,

name varchar(20) not null,

chinese double,

math double,

english double

);

insert into exam values(null,'张三',100,90,70);

insert into exam values(null,'李四',70,75,70);

insert into exam values(null,'王五',95,66,95);

insert into exam values(null,'赵六',82,80,null);

2、基本查询

（1）语法：

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

select 指定查询哪些列的数据;
column指定列名;
*号代表查询所有列;
from指定查询哪张表;

从（FROM）表中，选择（SELECT）某些列进行展示

DISTINCT可选，指显示结果时，是否剔除重复数据

（2）案例：

① 查询表中所有学生的信息

select * from exam;

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

select name,english from exam;

③ 查询所有英语成绩并过滤重复数据

select distinct english from exam;

④ 在所有学生分数上加10分后展示

select id,name,chinese+10,math+10,english+10 from exam;

⑤ 统计每个学生的总分

select name,ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0) from exam;

⑥ 使用别名表示学生总分

select name,ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0) as sum_grade from exam;

或

select name,ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0) sum_grade from exam;

3、条件查询

（1）语法：

SELECT [DISTINCT] *|列名 FROM tab_name [WHERE where_definition]

（2）说明：

其中where中可以使用如下运算符声明过滤条件：

Like语句中，% 代表零个或多个任意字符，_ 代表一个字符

（3）案例：

① 查询姓名为张三的学生成绩

select * from exam where name='张三';

② 查询英语成绩大于90分的同学

select * from exam where english>90;

③ 查询总分大于230分的所有同学

select name,ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0) as sum_grade from exam where ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0) > 230;

④ 查询语文分数在 80－100之间的同学

select name,chinese from exam where chinese between 80 and 100;

⑤ 查询数学分数为75,76,77的同学，再查询分数不在这个范围内的同学

select name,math from exam where math in(75,76,77);

 select name,math from exam where math not in (75,76,77);

⑥ 查询所有姓张的学生成绩

select * from exam where name like '张%';

⑦ 查询数学分>70，语文分>80 且英语成绩为null的同学

select * from exam where math > 70 and chinese > 80 and english is null;

4、排序查询

（1）语法：

SELECT [DISTINCT] *|列名 FROM tab_name ORDER BY column_name ASC|DESC;

ORDER BY 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的别名;
ASC 升序（默认）、DESC 降序；
ORDER BY 子句应位于SELECT语句的结尾；
ORDER BY就是对结果集指定一个具体的列，然后赋予其一个排序规则，就会对整个结果集进行顺序重排

（2）案例：

① 对英语成绩排序后输出

select * from exam order by english;

② 对总分排序按从高到低的顺序输出

select name,ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0) as sum_grade from exam order by sum_grade desc;

③ 对姓张的学生成绩排序输出

select name,ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0) as sum_grade from exam where name like '张%' order by sum_grade;

④ 结果的分页查询

可以使用LIMIT关键字，对查询结果进行数量限制或分页显示
m是可以省略的
limit n：表示对结果集的限制，表示结果只出现n条
limt n , m：表示结果集中，跳过前n条，从第n+1条开始显示，向后显示m条
关键字的顺序必须符合

⑤ 关键字的执行顺序

FROM（锁定表）--> WHERE（过滤）--> GROUP BY和聚合函数（结果集分组）--> SELECT（展示列）--> ORDER BY --> LIMIT

5、聚合函数

（1）语法：

COUNT(列 \| *)	返回选取结果集中行的数目
SUM(列)	返回选取结果集中所有值的总和
AVG(列)	返回选取结果集中所有值的平均值
MAX(列)	返回选取结果集中所有值的最大值
MIN(列)	返回选取结果集中所有值的最小值

（2）案例：

① 统计一个班级共有多少学生

select count(*) from exam;

② 统计数学成绩>75的学生有多少个

select count(id) from exam where math > 75;

③ 统计总分大于230的人数有多少

select count(id) from exam where ifnull(chinese,0) + ifnull(math,0) + ifnull(english,0) > 230;

④ 统计一个班级数学总成绩

select sum(math) from exam;

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

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

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

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

⑦ 统计一个班级语文成绩平均分

select avg(chinese) from exam;

⑧ 求一个班级总分平均分

select avg(ifnull(chinese,0) + ifnull(math,0) + ifnull(english,0)) from exam;

⑨ 求一个班级的最高分和最低分

select max(ifnull(chinese,0) + ifnull(math,0) + ifnull(english,0)) from exam;

6、分组查询

（1）语法：

SELECT [DISTINCT] *|列名 FROM tab_name GROUP BY column[,comumn..] [HAVING ...]

分组操作可以按照指定的一个或多个列的值将数据进行分组操作；
同一个组内的多条数据会"摞"在一起,只显示一条，其他的数据没有消失只是"压"在这条数据之下看不到；
如果没有分组操作，聚合函数作用于整个查询结果；
如果有分组操作，聚合函数作用于每个组队内部；
where和having都可以用来过滤数据；
where是分组之前过滤，不能出现聚合函数；
having是分组之后过滤，可以使用聚合函数；
注意：GROUP BY 后写了哪个列（对哪个列分组），哪个列在能出现在SELECT中；聚合函数不受此限制，可以在select中任意出现

（2）新建数据库表并插入数据做准备：

create table orders(

id int,

product varchar(20),

price float

);

insert into orders(id,product,price) values(1,'电视',900);

insert into orders(id,product,price) values(2,'洗衣机',100);

insert into orders(id,product,price) values(3,'洗衣粉',90);

insert into orders(id,product,price) values(4,'桔子',9);

insert into orders(id,product,price) values(5,'洗衣粉',90);

（3）案例：

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

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

② 查询总价大于100的商品的名称和总价

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

③ 查询单价小于100而总价大于100的商品的名称

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

7、外键约束

（1）增加外键

① 建表时指定外键关系

a.语法：

[CONSTRAINT <外键名>] FOREIGN KEY (字段名 [，字段名2，…]) REFERENCES <主表名> (主键列1 [，主键列2，…])[on delete restrict] [on update restrict];

RESTRICT : 只要本表格里面有指向主表的数据，在主表里面就无法删除相关记录。
CASCADE : 如果在foreign key 所指向的那个表里面删除一条记录，那么在此表里面的跟那个key一样的所有记录都会一同删掉。

b.案例：

新建表dept并插入数据：

create table dept(

id varchar(10) primary key,

name varchar(20)

);

insert into dept values ('001','人事部');

insert into dept values ('002','财务部');

insert into dept values ('003','销售部');

insert into dept values ('004','科技部');

新建表emp并插入数据，为其中did列添加外键约束：

（emp表的did列参考dept表的id列）

create table emp(

id varchar(10) primary key,

name varchar(20),

did varchar(10),

foreign key(did) references dept(id) --外键约束

);

insert into emp values ('999','孙悟空','001');

insert into emp values ('888','哈利波特','001');

insert into emp values ('777','萨达姆','002');

insert into emp values ('666','特朗普','003');

insert into emp values ('555','我我我','004');

无法向emp表中插入dept表id不存在的数据：

insert into emp values ('444','赵四','005');

无法将存在于emp表中的数据更新为dept表中id不存在的数据：

update emp set did='005' where name='哈利波特';

无法删除dept表中已存在数据：

delete from dept where id = '001';

② 通过修改表增加外键关系

a.语法：

ALTER TABLE tab_name ADD [CONSTRAINT fk_name] FOREIGN KEY(字段名) REFERENCES 表名(字段名) [on delete restrict] [on update restrict];

RESTRICT : 只要本表格里面有指向主表的数据，在主表里面就无法删除相关记录。
CASCADE : 如果在foreign key 所指向的那个表里面删除一条记录，那么在此表里面的跟那个key一样的所有记录都会一同删掉。

b.案例：

创建表dept2并插入数据：

create table dept2(

id varchar(10) primary key,

name varchar(20)

);

insert into dept2 values ('001','人事部');

insert into dept2 values ('002','财务部');

insert into dept2 values ('003','销售部');

insert into dept2 values ('004','科技部');

创建表emp2并插入数据：

先创建好表并插入完成数据后，再对其中的did列添加外键约束，让其参考dept表的Id列

create table emp2(

id varchar(10) primary key,

name varchar(20),

did varchar(10)

);

insert into emp2 values ('999','孙悟空','001');

insert into emp2 values ('888','哈利波特','001');

insert into emp2 values ('777','萨达姆','002');

insert into emp2 values ('666','特朗普','003');

insert into emp2 values ('555','我我我','004');

alter table emp2 add foreign key (did) references dept2(id); --外键约束

（2）删除外键

① 语法：

ALTER TABLE tab_name DROP FOREIGN KEY fk_name

② 案例

show create table emp;--查询外键名

alter table emp drop foreign key emp_ibfk_1;--删除外键

8、多表查询

查询操作可能会跨着多张表进行

（1）数据准备

create table dept3(

id int primary key auto_increment,

name varchar(20)

);

insert into dept3 values(null,'财务部');

insert into dept3 values(null,'人事部');

insert into dept3 values(null,'科技部');

insert into dept3 values(null,'销售部');

create table emp3(

id int primary key auto_increment,

name varchar(20),

did int

);

insert into emp3 values(null,'刘备',1);

insert into emp3 values(null,'关羽',2);

insert into emp3 values(null,'张飞',3);

insert into emp3 values(null,'赵云',5);

（2）多表查询

① 笛卡尔积查询

尝试直接查询多张表

select * from dept3,emp3;

查询的结果是两张表相乘的结果，称之为笛卡尔积查询；
如果左边表有m条数据，右边表有n条数据，则得到m*n条数据；
笛卡尔积查询没有考虑两张表的对应关系，所以结果中包含大量错误的数据，无法直接使用。
虽然通常不会直接使用，但笛卡尔积查询是其他多表查询的基础，需要了解。

② 内连接查询

可以在笛卡尔积查询的基础上，基于外键字段筛选出正确的数据。这样的查询称之为内连接查询：

select * from dept3,emp3 where emp3.did = dept3.id;

内连接查询也可以通过专用的语法实现：

select * from dept3 inner join emp3 on emp3.did = dept3.id;

内连接查询只能查询到两张表中都有对应数据的记录。

对于左边表有而右边表没有数据和右边表有而左边表没有的数据都不会被查询到。

③ 外连接查询

a. 左外连接查询

在内连接的基础上增加左边表有而右边表没有的记录：

select * from dept3 left join emp3 on emp3.did = dept3.id;

b.右外连接查询

在内连接的基础上增加了右边表有而左边表没有的记录：

select * from dept3 right join emp3 on emp3.did = dept3.id;

c. 全外连接查询

在内连接的基础上增加左边表有右边表没有的数据和右边表有左边表没有的数据：

select * from dept3 full join emp3 on emp3.did = dept3.id;

mysql不支持全外连接查询，在mysql中需要使用union操作间接完成全外连接查询。

select * from emp3 left join dept3 on emp3.did = dept3.id

union

select * from emp3 right join dept3 on emp3.did = dept3.id;

Star_KeyW

关注

0
点赞
踩
9

收藏

觉得还不错? 一键收藏
打赏
0
评论
SQL语言基础

介绍并举例数据库基础sql语句
复制链接

扫一扫

专栏目录