SQL语句笔记

最新推荐文章于 2024-07-30 03:50:30 发布

冬～

最新推荐文章于 2024-07-30 03:50:30 发布

阅读量588

点赞数 2

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_43819984/article/details/110943559

版权

本文详细介绍了MySQL数据库的相关操作，包括数据库的创建、查看、修改和删除，以及数据表的创建、查询、更新和删除。讨论了SQL语句的分类和注意事项，强调了DDL操作如创建数据库和数据表，以及DML操作如插入、更新和删除数据。此外，还提到了数据类型的选择、视图、存储过程和触发器的概念，以及数据库设计的基本原则。

摘要由CSDN通过智能技术生成

SQL语句

一、mysql目录结构

目录	目录内容
bin	放置可执行文件
docs	文档
include	包含（头）文件
lib	依赖库
share	存放字符集，语言
Data	mysql管理的数据库文件所在的目录
mysql.ini	mysql配置文件

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6dW8QBEG-1607528520368)(D:\java_study\java-study\第二阶段\01模块一 MySQL数据库和SQL\01任务一MySQL基础和SQL入门\02_图片\MySql自带数据库的简单介绍.jpg)]

二、sql语法注意事项

1） SQL语句可以单行或者多行书写，以分号结尾 ; （Sqlyog中可以不用写分号）

2）可以使用空格和缩进来增加语句的可读性。

3） MySql中使用SQL不区分大小写，一般关键字大写，数据库名表名列名小写。

4）注释方式

--空格单行注释

/**/ 多行注释

# Mysql特有的单行注释

# show databases; 单行注释

– show databases; 单行注释

多行注释

show databases;

三、SQL的分类

分类	说明
数据定义语言	简称DDL(Data Defifinition Language)，用来定义数据库对象：数据库，表，列等。
数据操作语言	简称DML(Data Manipulation Language)，用来对数据库中表的记录进行更新。
数据查询语言	简称DQL(Data Query Language)，用来查询数据库中表的记录。
数据控制语言	简称DCL(Date Control Language)，用来定义数据库的访问权限和安全级别，及创建用户。(了解)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CH2RHlka-1607528520372)(D:\java_study\java-study\第二阶段\01模块一 MySQL数据库和SQL\01任务一MySQL基础和SQL入门\02_图片\SQL的分类.jpg)]

四、DDL操作数据库

1、创建数据库(create)

第一种：create database 数据库名；创建指定名称的数据库

CREATE DATABASE db1;

第二种：create database 数据库名 character set 字符集创建指定数据库，并且指定字符集（一般都指定utf8）

CREATE DATABASE db1_1 CHARACTER SET utf8;

2、查看/选择数据库(show/select)

use 数据库切换数据库，使用选定的数据库

USE db1;

select database(); 查看当前正在使用的数据

SELECT DATABASE();

show databases; 查看Mysql中都有哪些数据库

SHOW DATABASES;

show create database 数据库名；查看一个数据库的定义信息

SHOW CREATE DATABASE db1;

3、修改数据库（alter）

alter database 数据库名 character set 字符集修改数据库的字符集

ALTER DATABASE db1 CHARACTER SET utf8;
SHOW CREATE DATABASE db1;

4、删除数据库(drop)

drop database 数据库名从Mysql中永久删除某个数据库

DROP DATABASE db1;
DROP DATABASE db1_1;

五、DDL操作数据表

1、mysql常见的数据类型

类型	描述
int	整型
double	浮点型
varchar	字符串型
data	日期类型，yyyy-MM-dd ，只要年月日，没有时分秒

注意：

MySQL中的 char类型与 varchar类型，都对应了 Java中的字符串类型，区别在于：

char类型是固定长度的：根据定义的字符串长度分配足够的空间。

varchar类型是可变长度的：只使用字符串长度所需的空间

char类型适合存储固定长度的字符串，比如密码，性别一类

varchar类型适合存储在一定范围内，有长度变化的字符串

2、创建表

create table 表名（

字段名称1 字段类型（长度），

字段名称2 字段类型（长度）注意：最后一列不需要加逗号

）

需求：

表名：category

表中字段：

分类ID ：cid ,为整型

分类名称：cname，为字符串类型，指定长度20

CREATE TABLE category(
cid INT,
cname VARCHAR(20)
);

需求2：创建测试表

表名： test1

表中字段：

测试ID ： tid ,为整型

测试时间： tdate , 为年月日的日期类型

CREATE TABLE test1(
tid INT,
tdate DATE
);

复制表（like）：

语法格式：create table 新表名 like 旧表名；

CREATE TABLE test2 LIKE test1;

3、查看表(show)

show tables；查看数据库中所有表名

SHOW TABLES;

desc 表名；查看数据表的结构

DESC test1;

show create table 表名；查看创建表使用的SQL语句

SHOW CREATE TABLE category;

4、删除表（drop）

drop table 表名；

DROP TABLE test1;

drop table if exists 表名；判断表是否存在，存在就删除，不存在就不执行删除

DROP TABLE IF EXISTS test1;

DROP TABLE IF EXISTS test2;

5、修改表

1、修改表名（rename…to…）

rename table 旧表名 to 新表名

RENAME TABLE category TO category1;

2、修改字符集

alter table 表名 character set 字符集；

ALTER TABLE category CHARACTER SET gbk;

3、向表中添加列（add）

alter table 表名 add 字段名称字段类型

ALTER TABLE category ADD cdesc VARCHAR(20);

ALTER TABLE student ADD (sname VARCHAR(20),age INT,sex CHAR(1),address VARCHAR(40));

4、修改表中列的数据类型或长度（modify）

alter table 表名 modify 字段名称字段类型

ALTER TABLE category MODIFY cdesc VARCHAR(50);

5、修改列名称（change）

alter table 表名 change 旧列名新列名类型（长度）；

ALTER TABLE category CHANGE cdesc description VARCHAR(30);

6、删除列（drop）

alter table 表名 drop 列名；

ALTER TABLE category DROP description;

六、DML操作表中数据

1、插入数据(insert into…values…)

语法格式：

insert into 表名（字段名1，字段名2…） values(字段值1，字段值2…)

INSERT INTO student(sid,sname,age,sex,address) VALUES(1,‘孙悟空’,20,‘男’,‘花果山’);

INSERT INTO student VALUES(2,‘孙悟空’,10,‘男’,‘地球’);

INSERT INTO category(cname) VALUES(‘白骨精’);

注意：

值与字段必须要对应，个数相同&数据类型相同

2）值的数据大小，必须在字段指定的长度范围内

3）varchar char date类型的值必须使用单引号，或者双引号包裹。

4）如果要插入空值，可以忽略不写，或者插入null

如果插入指定字段的值，必须要上写列名

2、更改数据（updata）

语法格式1：不带条件的修改

update 表名 set 列名 = 值

UPDATE student SET sex = ‘女’;

语法格式2：带条件的修改

update 表名 set 列名 = 值【where 条件表达式：字段名 = 值】

UPDATE student SET sid = 6 WHERE age = 500;

UPDATE student SET sex = ‘男’ WHERE sid = 3;

3、删除数据(delete)

语法格式一：delete from 表名删除所有数据，不推荐. 有多少条记录就执行多少次删除操作. 效率低

语法格式二：truncate table 表名删除所有数据，推荐. 先删除整张表, 然后再重新创建一张一模一样的表. 效率高

TRUNCATE TABLE student;

语法格式三：delete from 表名 [where 字段名 = 值] 指定条件删除

DELETE FROM student WHERE sid = 1;

七、DQL查询表中数据(select)

1、简单查询

语法格式：select 列名 from 表名

需求1：查询emp中的所有数据

select * from emp; 使用*表示所有列

需求2：查询emp中的所有数据，仅显示id和name字段

SELECT eid,ename FROM emp;

需求3：将所有的员工信息查询出来，并将列名改为中文，使用关键字as

SELECT
eid AS ‘编号’,
ename AS ‘姓名’,
sex AS ‘性别’,
salary AS ‘薪资’,
hire_date AS ‘入职时间’, --AS可以省略不写
dept_name AS ‘部门职能’
FROM emp;

需求4：查询一共有几个部门，使用去重关键字 distinct

SELECT DISTINCT dept_name FROM emp;

需求5: 将所有员工的工资 +1000 元进行显示

SELECT ename,salary + 1000 FROM emp;

2、条件查询

语法格式：select 列名 from 表名 where 条件表达式

运算符	说明
> < <= >= = <> !=	大于、小于、大于(小于)等于、不等于
BETWEEN …AND…	显示在某一区间的值例如: 2000-10000之间： Between 2000 and 10000
IN(集合)	集合表示多个值,使用逗号分隔,例如: name in (悟空，八戒) in中的每个数据都会作为一次条件,只要满足条件就会显示
LIKE ‘%张%’	模糊查询
IS NULL	查询某一列为NULL的值, 注: 不能写 = NULL
And &&	多个条件同时成立
Or\|\|	多个条件任一成立
Not	不成立，取反。

需求1:

# 查询员工姓名为黄蓉的员工信息

SELECT * FROM emp WHERE ename = ‘黄蓉’;

# 查询薪水价格为5000的员工信息

SELECT * FROM emp WHERE salary = 5000;

# 查询薪水价格不是5000的所有员工信息

SELECT * FROM emp WHERE salary != 5000;

SELECT * FROM emp WHERE salary <> 5000

# 查询薪水价格大于6000元的所有员工信息

SELECT * FROM emp WHERE salary > 6000;

# 查询薪水价格在5000到10000之间所有员工信息

SELECT * FROM emp WHERE salary >= 5000 && salary <= 10000;

SELECT * FROM emp WHERE salary BETWEEN 5000 AND 10000;

# 查询薪水价格是3600或7200或者20000的所有员工信息

SELECT * FROM emp WHERE salary = 3600 OR salary = 7200 OR salary = 20000;

SELECT * FROM emp WHERE salary IN(3600,7200,20000);

需求2:

# 查询含有’精’字的所有员工信息

SELECT * FROM emp WHERE ename LIKE ‘%精%’;

# 查询以’孙’开头的所有员工信息

SELECT * FROM emp WHERE ename LIKE ‘孙%’;

# 查询第二个字为’兔’的所有员工信息

SELECT * FROM emp WHERE ename LIKE ‘_兔%’;

# 查询没有部门的员工信息

SELECT * FROM emp WHERE dept_name IS NULL;

# 查询有部门的员工信息

SELECT * FROM emp WHERE dept_name IS NOT NULL;

通配符	说明
%	表示匹配任意多个字符串,
_	表示匹配一个字符

3、去除重复（DISTINCT）

语法规则：

SELECT DISTINCT 列名称 FROM 表名称

SELECT DISTINCT Company FROM Orders;

MySQL单表&约束&事务

一、DQL操作单表

1、排序（ORDER BY）

语法结构：

select 字段名 from 表名 [where 字段 = 值] order by 字段名 [ASC/DESC]

ASC：升序排序

DESC：降序排序

a、单列排序，只按照一个字段排序

使用 salary 字段,对emp 表数据进行排序 (升序/降序)

SELECT * FROM emp ORDER BY salary ASC; --升序，默认排序，也可以不写ASC

SELECT * FROM emp ORDER BY salary DESC; --降序

b、组合排序，同时对多个字段排序，如果第一个字段相同，就按照第二个字段排序

在薪水排序的基础上,再使用id进行排序, 如果薪水相同就以id 做降序排序

SELECT * FROM emp ORDER BY salary,eid DESC;

2、聚合函数

语法结构：select 聚合函数（字段名） from 表名；

聚合函数	作用
count	统计指定列不为NULL的记录行数
sum	计算指定列的数值和
max	计算指定列的最大值
min	计算指定列的最小值
avg	计算指定列的平均值

需求1：

#1 查询员工的总数

SELECT COUNT(eid) FROM emp;

SELECT COUNT(*) FROM emp;

#2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值

SELECT SUM(salary) FROM emp; --总薪水

SELECT MAX(salary) FROM emp; --最高薪水

SELECT MIN(salary) FROM emp; --最小薪水

SELECT AVG(salary) FROM emp; --薪水的平均值

SELECT
SUM(salary) AS ‘总薪水’,
MAX(salary) AS ‘最高薪水’,
MIN(salary) AS ‘最小薪水’,
AVG(salary) AS ‘平均薪水’
FROM emp;

#3 查询薪水大于4000员工的个数

SELECT COUNT(eid) FROM emp WHERE salary > 4000;

#4 查询部门为’教学部’的所有员工的个数

SELECT COUNT(eid) FROM emp WHERE dept_name = ‘教学部’;

SELECT COUNT(*) FROM emp WHERE dept_name = ‘教学部’;

#5 查询部门为’市场部’所有员工的平均薪水

SELECT AVG(salary) FROM emp WHERE dept_name = ‘市场部’;

3、分组（group by）

分组查询指的是使用GROUP BY 语句,对查询的信息进行分组,相同数据作为一组

语法格式：

select 分组字段/聚合函数 from 表名 group by 分组字段 [having 条件]

对数据进行过滤,使用关键字 hiving

需求1：

按照性别进行分组

SELECT * FROM emp GROUP BY sex; --无意义

分组时可以查询要分组的字段, 或者使用聚合函数进行统计操作，单独分组无意义

需求: 通过性别字段进行分组,求各组的平均薪资

SELECT sex,AVG(salary) FROM emp GROUP BY sex;

需求2：

#1.查询所有部门信息

SELECT dept_name AS ‘部门信息’ FROM emp GROUP BY dept_name;

#2.查询每个部门的平均薪资

SELECT dept_name AS ‘部门’,AVG(salary) FROM emp GROUP BY dept_name;

#3.查询每个部门的平均薪资, 部门名称不能为null

SELECT dept_name AS ‘部门’,AVG(salary) FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name

需求3:

# 查询平均薪资大于6000的部门.

需要在分组后,对数据进行过滤,使用关键字 hiving

SELECT dept_name AS ‘部门’,AVG(salary) FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name HAVING AVG(salary) > 6000;

where 与 having的区别

过滤方式	特点
where	where 进行分组前的过滤，where 后面不能写聚合函数
having	having 是分组后的过滤，having 后面可以写聚合函数

4、limint关键字(指定查询多少行数据)

语法格式：

SELECT 字段1,字段2… FROM 表名 LIMIT offset , length;

limit offffset , length;关键字可以接受一个或者两个为0 或者正整数的参数
offffset 起始行数, 从0开始记数, 如果省略则默认为 0.
length 返回的行数

需求1:

# 查询emp表中的前 5条数据

SELECT * FROM emp LIMIT 5;

SELECT * FROM emp LIMIT 0,5;

# 查询emp表中从第4条开始,查询6条

SELECT * FROM emp LIMIT 3,6;

需求2: 分页操作每页显示3条数据

SELECT * FROM emp LIMIT 0,3;
SELECT * FROM emp LIMIT 3,3;
SELECT * FROM emp LIMIT 6,3;

– 分页公式起始索引 = (当前页 - 1) * 每页条数

二、约束

约束的作用:

对表中的数据进行进一步的限制，从而保证数据的正确性、有效性、完整性.违反约束的不正确数据,将无法插入到表中

常见的约束

约束名	约束关键字
主键	primary key
唯一	unique
非空	not null
外键	foreign key

1、主键约束(primary key)

特点：不可重复，唯一非空

作用：用来表示数据库中的每一条数据

语法格式：

字段名字段类型 primary key;

需求1: 创建一个带主键的表

方法一：

CREATE TABLE emp2 (
– 设置唯一主键
eid INT PRIMARY KEY,
ename VARCHAR(20),
sex CHAR(1)
);

方法二：

CREATE TABLE emp2(
eid INT,
ename VARCHAR(20),
sex CHAR(1),
PRIMARY KEY(eid)
)

方法三：

CREATE TABLE emp2(
eid INT,
ename VARCHAR(20),
sex CHAR(1)
)
ALTER TABLE emp2 ADD PRIMARY KEY(eid);

测试主键的唯一性非空性

# 正常插入一条数据

INSERT INTO emp2 VALUES(1,‘宋江’,‘男’);

# 插入一条数据,主键为空

– Column ‘eid’ cannot be null 主键不能为空

INSERT INTO emp2 VALUES(NULL,‘李逵’,‘男’);

# 插入一条数据,主键为 1

– Duplicate entry ‘1’ for key ‘PRIMARY’ 主键不能重复

INSERT INTO emp2 VALUES(1,‘孙二娘’,‘女’);

哪些字段可以作为主键 ?

通常针对业务去设计主键,每张表都设计一个主键id
主键是给数据库和程序使用的,跟最终的客户无关,所以主键没有意义没有关系,只要能够保证不重复就好,比如身份证就可以作为主键.

2、删除主键

语法格式：

alter table emp2 drop primary key;

3、主键的自增(AUTO_INCREMENT)

AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)

创建主键自增的表

CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
);

添加数据观察主键的自增

INSERT INTO emp2(ename,sex)VALUES(‘张三’,‘男’);

INSERT INTO emp2(ename,sex)VALUES(‘李四’,‘男’);

INSERT INTO emp2 VALUES(NULL,‘翠花’,‘女’);

INSERT INTO emp2 VALUES(NULL,‘艳秋’,‘女’);

4、修改主键自增的起始值

默认地AUTO_INCREMENT的开始值是 1

– 创建主键自增的表,自定义自增其实值

CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
)AUTO_INCREMENT=100; --从100开始自增

– 插入数据,观察主键的起始值

INSERT INTO emp2(ename,sex) VALUES(‘张百万’,‘男’);

INSERT INTO emp2(ename,sex) VALUES(‘艳秋’,‘女’);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VB8oOuRE-1607528520374)(C:\Users\admin\AppData\Roaming\Typora\typora-user-images\image-20201206003736638.png)]

5、delete和truncate对自增长的影响

清空数据的方式	特点
DELETE	只是删除表中所有数据,对自增没有影响
TRUNCATE	truncate 是将整个表删除掉,然后创建一个新的表自增的主键,重新从 1开始

测试1: delete 删除表中所有数据

– 目前最后的主键值是 101

SELECT * FROM emp2;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-u2MWDzs9-1607528520378)(C:\Users\admin\AppData\Roaming\Typora\typora-user-images\image-20201206003736638.png)]

– delete 删除表中数据,对自增没有影响

DELETE FROM emp2;

– 插入数据查看主键

INSERT INTO emp2(ename,sex) VALUES(‘张百万’,‘男’);

INSERT INTO emp2(ename,sex) VALUES(‘艳秋’,‘女’);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-U4s0z2mr-1607528520382)(C:\Users\admin\AppData\Roaming\Typora\typora-user-images\image-20201206004222255.png)]

测试2: truncate删除表中数据

– 使用 truncate 删除表中所有数据,

TRUNCATE TABLE emp2;

– 插入数据查看主键

INSERT INTO emp2(ename,sex) VALUES(‘张百万’,‘男’);

INSERT INTO emp2(ename,sex) VALUES(‘艳秋’,‘女’);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BkwxUSJM-1607528520383)(C:\Users\admin\AppData\Roaming\Typora\typora-user-images\image-20201206004348189.png)]

6、非空约束

非空约束的特点某一列不予许为空

语法格式：

字段名字段类型 not null

需求1: 为 ename 字段添加非空约束

CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20) NOT NULL,
sex CHAR(1)
);

–Column ‘ename’ cannot be null --ename不能为空

INSERT INTO emp2 VALUES(NULL,NULL,‘女’);

7、唯一约束（unique）

唯一约束的特点表中的某一列的值不能重复(对null不做唯一的判断 )

语法格式：

字段名字段值 unique

1、添加唯一约束

CREATE TABLE emp3(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20) UNIQUE,
sex CHAR(1)
);

2、测试唯一约束

– 添加数据

INSERT INTO emp3 VALUES(NULL,‘张三’,‘男’);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-niXJ8WVI-1607528520385)(C:\Users\admin\AppData\Roaming\Typora\typora-user-images\image-20201206005458777.png)]

–添加一条重复的数据

INSERT INTO emp3 VALUES(NULL,‘张三’,‘女’);

–Duplicate entry ‘张三’ for key ‘ename’ ename字段不能添加重复的数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NBQSSPTY-1607528520386)(C:\Users\admin\AppData\Roaming\Typora\typora-user-images\image-20201206005549447.png)]

主键约束与唯一约束的区别:
1. 主键约束唯一且不能够为空
2. 唯一约束,唯一但是可以为空
3. 一个表中只能有一个主键 , 但是可以有多个唯一约束