当前位置:   article > 正文

【MySQL】SQL的概念_创建一个和学生表结构相同的表“软件工程系学生”,将学生表中软件工程系同学的记

创建一个和学生表结构相同的表“软件工程系学生”,将学生表中软件工程系同学的记

一、基本概念

1.1 什么是 SQL

Structured Query Language 结构化查询语言

1.2 SQL作用

(1) 是一种所有关系型数据库的查询规范,不同的数据库都支持。

(2) 通用的数据库操作语言,可以用在不同的数据库中。

(3)不同的数据库 SQL 语句有一些区别。

1.3 SQL 语句分类

(1)Data Definition Language (DDL 数据定义语言) 如:建库,建表

(2)Data Manipulation Language(DML 数据操纵语言), 如:对表中的记录操作增删改

(3)Data Query Language(DQL 数据查询语言),如:对表中的查询操作

(4)Data Control Language(DCL 数据控制语言),如:对用户权限的设置

也就是SQL语句主要是围绕上述四种语句。

1.4 MySQL 的语法

(1)每条语句以分号结尾,如果在 SQLyog 中不是必须加的。

(2)SQL 中不区分大小写,关键字中认为大写和小写是一样的

(3)3 种注释

注释的语法说明
--空格单行注释
/* */多行注释
#这是 mysql 特有的注释方式

二 、DDL 操作数据库

2.1 创建数据库

2.1.1 创建数据库的几种方式

  • 创建数据库
CREATE DATABASE 数据库名;
  • 判断数据库是否已经存在,不存在则创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名;
  • 创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;

2.1.2 具体操作

  1. -- 直接创建数据库 db1
  2. create database db1;
  3. -- 判断是否存在,如果不存在则创建数据库 db2
  4. create database if not exists db2;
  5. -- 创建数据库并指定字符集为 gbk
  6. create database db3 default character set gbk;

2.2 查看数据库

按 tab 键可以自动补全关键字

  1. -- 查看所有的数据库
  2. show databases;
  3. -- 查看某个数据库的定义信息
  4. show create database db3;
  5. show create database db1;

2.3 修改数据库

2.3.1 修改数据库默认的字符集

  1. ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;
  2. alter database 数据库名 default character set 字符集;

2.3.2 具体操作

将 db3 数据库的字符集改成 utf8

alter database db3 character set utf8;

2.4 删除数据库

2.4.1 删除数据库的语法

  1. DROP DATABASE 数据库名;
  2. drop database 数据库名;

2.4.2 具体操作

每行 SQL 语句需要选中再执行,可以按 F9

删除 db2 数据库

drop database db2;

2.5 使用数据库

2.5.1 查看正在使用的数据库

SELECT DATABASE(); 使用的一个 mysql 中的全局函数

2.5.2 使用/切换数据库

USE 数据库名;

2.5.3 具体操作

  1. -- 查看正在使用的数据库
  2. select database();
  3. -- 改变要使用的数据库
  4. use db4;

2.5.4 面试题

在 MySQL 数据库软件中,有如下三个数据库:test1、test2、test3

登录数据库之后,输入语句: select database test2; 运行结果是什么?


这是一条错误的语句,如果要选中一个数据库,应用使用: use test2;
 

三、DDL操作表结构

前提先使用某个数据库

3.1 创建表

3.1.1 创建表的格式

  1. CREATE TABLE 表名 (
  2. 字段名 1 字段类型 1,
  3. 字段名 2 字段类型 2
  4. );

3.1.2 关键字说明

创建表的关键字说明
CREATE创建
TABLE

3.2 MySQL 数据类型

3.2.1 常使用的数据类型如下

3.2.2 详细的数据类型如下

整数:

类型名称类型说明
tinyInt微整型:很小的整数(占 8 位二进制)
smallint小整型:小的整数(占 16 位二进制)
mediumint中整型:中等长度的整数(占 24 位二进制)
int(integer)整型:整数类型(占 32 位二进制)

小数

float单精度浮点数,占 4 个字节
double双精度浮点数,占 8 个字节

日期

time表示时间类型
datetime同时可以表示日期和时间类型
date表示日期类型

字符串

char(m)固定长度的字符串, 无论使用几个字符都占满全部, M 为 0~255 之间的整
varchar(m)可变长度的字符串, 使用几个字符就占用几个, M 为 0~65535 之间的整数

大二进制

tinyblob
Big Large Object
允许长度 0~255 字节
blob允许长度 0~65535 字节
mediumblob允许长度 0~167772150 字节
longblob允许长度 0~4294967295 字节

大文本

tinytext允许长度 0~255 字节
text允许长度 0~65535 字节
mediumtext允许长度 0~167772150 字节
longtext允许长度 0~4294967295 字节

3.2.3 具体操作

创建 student 表包含 id,name,birthday 字段

  1. create table student (
  2. id int, -- 整数
  3. name varchar(20), -- 字符串
  4. birthday date -- 生日,最后没有逗号
  5. );

3.3 查看表

3.3.1 查看某个数据库中的所有表

SHOW TABLES;

3.3.2 查看表结构

DESC 表名;

3.3.3 查看创建表的 SQL 语句

SHOW CREATE TABLE 表名;

3.3.4 具体操作:

查看 day21 数据库中的所有表

  1. use day21;
  2. show tables;

查看 student 表的结构

desc student;

 查看 student 的创建表 SQL 语句

show create table student;

执行结果

存在的目的是为了避免关键字的冲突

  1. CREATE TABLE `student` (
  2. `id` int(11) DEFAULT NULL,
  3. `name` varchar(20) DEFAULT NULL,
  4. `birthday` date DEFAULT NULL
  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8

3.4 快速创建一个表结构相同的表

3.4.1 语法

CREATE TABLE 新表名 LIKE 旧表名;

3.4.2 具体操作

创建 s1 表, s1 表结构和 student 表结构相同

  1. -- 创建一个 s1 的表与 student 结构相同
  2. create table s1 like student;
  3. desc s1;

3.5 删除表

3.5.1 直接删除表

DROP TABLE 表名;

3.5.2 判断表是否存在,如果存在则删除表

DROP TABLE IF EXISTS 表名;

3.5.3 具体操作

  1. -- 直接删除表 s1 表
  2. drop table s1;
  3. -- 判断表是否存在并删除 s1 表
  4. drop table if exists `create`;

与直接删除的区别

如果表不存在,不删除,存在则删除

3.6 修改表结构

3.6.1 添加表列 ADD

ALTER TABLE 表名 ADD 列名 类型;

3.6.2 修改列类型 MODIFY

ALTER TABLE 表名 MODIFY 列名 新的类型;

3.6.3 修改列名 CHANGE

ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;

3.6.4 删除列 DROP

ALTER TABLE 表名 DROP 列名;

3.6.5 修改表名

RENAME TABLE 表名 TO 新表名;

将学生表 student 改名成 student2

rename table student to student2;

3.6.6 修改字符集 character set

ALTER TABLE 表名 character set 字符集;

将 student2 表的编码修改成 gbk

alter table student2 character set gbk;

四、DML 操作表中的数据

用于对表中的记录进行增删改操作

4.1 插入记录

  1. INSERT [INTO] 表名 [字段名] VALUES (字段值)
  2. INSERT INTO 表名:表示往哪张表中添加数据
  3. (字段名 1, 字段名 2, …): 要给哪些字段设置值
  4. VALUES (值 1, 值 2, …): 设置具体的值

4.1.1 插入全部字段

所有的字段名都写出来

INSERT INTO 表名 (字段名 1, 字段名 2, 字段名 3…) VALUES (值 1, 值 2, 值 3);

不写字段名

INSERT INTO 表名 VALUES (值 1, 值 2, 值 3…);

4.1.2 插入部分数据

INSERT INTO 表名 (字段名 1, 字段名 2, ...) VALUES (值 1, 值 2, ...);

注:没有添加数据的字段会使用 NULL

4.1.3 具体操作:

插入所有的列,向学生表中

  1. insert into student (id,name,age,sex) values (1, '孙悟空', 20, '男');
  2. insert into student (id,name,age,sex) values (2, '孙悟天', 16, '男');

向表中插入所有字段

  1. -- 插入所有列
  2. insert into student values (3, '孙悟饭', 18, '男', '龟仙人洞中');
  3. -- 如果只插入部分列,必须写列名
  4. insert into student values (3, '孙悟饭', 18, '男');

4.2 DOS 命令窗口操作数据乱码问题的解决

当我们使用 DOS 命令行进行 SQL 语句操作如有有中文会出现乱码

 4.2.1 insert 的注意事项

1) 插入的数据应与字段的数据类型相同

2) 数据的大小应在列的规定范围内,例如:不能将一个长度为 80 的字符串加入到长度为 40 的列中。

3) 在 values 中列出的数据位置必须与被加入的列的排列位置相对应。在 mysql 中可以使用

value, 但不建议使用, 功能与 values 相同。

4) 字符和日期型数据应包含在单引号中。 MySQL 中也可以使用双引号做为分隔符。

5) 不指定列或使用 null,表示插入空值。

4.2.2 乱码产生的原因

 4.2.3 查看 MySQL 内部设置的编码

查看包含 character 开头的全局变量

show variables like 'character%';

4.2.4 解决方案

修改 client、 connection、 results 的编码为 GBK,保证和 DOS 命令行编码保持一致

单独设置说明
set character_set_client=gbk;修改客户端的字符集为 GBK
set character_set_connection=gbk;修改连接的字符集为 GBK
set character_set_results=gbk;修改查询的结果字符集为 GBK

同时设置三项

set names gbk;

注意:退出 DOS 命令行就失效了,需要每次都配置

4.3 蠕虫复制

4.3.1 什么是蠕虫复制

将一张已经存在的表中的数据复制到另一张表中。

4.3.2 语法格式

将表名 2 中的所有的列复制到表名 1 中

INSERT INTO 表名 1 SELECT * FROM 表名 2;

只复制部分列

INSERT INTO 表名 1(列 1, 列 2) SELECT1, 列 2 FROM student;

4.3.3 具体操作

  1. -- 创建 student2 表, student2 结构和 student 表结构一样
  2. drop table student2;
  3. create table student2 like student;
  4. -- 将 student 表中的数据添加到 student2 表中
  5. insert into student2 select * from student;
  6. -- 如果只想复制 student 表中 name,age 字段数据到 student2 表中,两张表都写出相应的列名
  7. insert into student2 (name,age) select name,age from student;

4.4 更新表记录

  1. UPDATE 表名 SET 列名=值 [WHERE 条件表达式]
  2. UPDATE: 需要更新的表名
  3. SET: 修改的列值
  4. WHERE: 符合条件的记录才更新
  5. 你可以同时更新一个或多个字段。
  6. 你可以在 WHERE 子句中指定任何条件

4.4.1 不带条件修改数据

UPDATE 表名 SET 字段名=值; -- 修改所有的行

4.4.2 带条件修改数据

UPDATE 表名 SET 字段名=WHERE 字段名=值;

4.4.3 具体操作

  1. -- 不带条件修改数据,将所有的性别改成女
  2. update student set sex = '女';
  3. -- 带条件修改数据,将 id 号为 2 的学生性别改成男
  4. update student set sex='男' where id=2;
  5. -- 一次修改多个列,把 id 为 3 的学生,年龄改成 26 岁, address 改成北京
  6. update student set age=26, address='北京' where id=3;

4.5 删除表记录

  1. DELETE FROM 表名 [WHERE 条件表达式]
  2. 如果没有指定 WHERE 子句, MySQL 表中的所有记录将被删除。
  3. 你可以在 WHERE 子句中指定任何条件

4.5.1 不带条件删除数据

DELETE FROM 表名;

4.5.2 带条件删除数据

DELETE FROM 表名 WHERE 字段名=值;

4.5.3 使用 truncate 删除表中所有记录

TRUNCATE TABLE 表名;

4.5.4 truncate 和 delete 的区别

truncate 相当于删除表的结构,再创建一张表

truncate:会清空表中所有的数据,速度快,不可回滚;实质是删除整张表包括数据再重新创建表;

delete:逐行删除数据,每步删除都是有日志记录的,可以回滚数据;实质是逐行删除表中的数据;

4.5.5 具体操作

  1. -- 带条件删除数据,删除 id 为 1 的记录
  2. delete from student where id=1;
  3. -- 不带条件删除数据,删除表中的所有数据
  4. delete from student;

五、DQL 查询表中的数据

查询不会对数据库中的数据进行修改.只是一种显示数据的方式

  1. SELECT 列名 FROM 表名 [WHERE 条件表达式]
  2. 1) SELECT 命令可以读取一行或者多行记录。
  3. 2) 你可以使用星号( *)来代替其他字段, SELECT 语句会返回表的所有字段数据
  4. 3) 你可以使用 WHERE 语句来包含任何条件。

5.1 简单查询

5.1.1 查询表所有行和列的数据

使用*表示所有列

SELECT * FROM 表名;

查询所有的学生

select * from student;

5.1.2 查询指定列

查询指定列的数据,多个列之间以逗号分隔

SELECT 字段名 1, 字段名 2, 字段名 3, ... FROM 表名;

查询 student 表中的 name 和 age 列

select name,age from student;

5.2 指定列的别名进行查询

5.2.1 使用关键字

使用别名的好处: 显示的时候使用新的名字,并不修改表的结构

5.2.2 语法

对列指定别名

SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名;

对列和表同时指定别名

SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名 AS 表别名;

5.2.3 具体操作

  1. -- 使用别名
  2. select name as 姓名,age as 年龄 from student;
  3. -- 表使用别名
  4. select st.name as 姓名,age as 年龄 from student as st;

表使用别名的原因:用于多表查询操作

5.3 清除重复值

5.3.1 查询指定列并且结果不出现重复数据

SELECT DISTINCT 字段名 FROM 表名;

5.3.2 具体操作

查询学生来至于哪些地方

  1. -- 查询学生来至于哪些地方
  2. select address from student;
  3. -- 去掉重复的记录
  4. select distinct address from student;

5.4 查询结果参与运算

5.4.1 某列数据和固定值运算

SELECT 列名 1 + 固定值 FROM 表名;

5.4.2 某列数据和其他列数据参与运算

SELECT 列名 1 + 列名 2 FROM 表名;

注意: 参与运算的必须是数值类型

5.4.3 需求

准备数据: 添加数学,英语成绩列,给每条记录添加对应的数学和英语成绩,查询的时候将数学和英语的成绩相加

5.4.4 实现

  1. select * from student;
  2. -- 给所有的数学加 5 分
  3. select math+5 from student;
  4. -- 查询 math + english 的和
  5. select * from student;
  6. select *,(math+english) as 总成绩 from student;
  7. -- as 可以省略
  8. select *,(math+english) 总成绩 from student;

5.5 条件查询

5.5.1 为什么要条件查询

如果没有查询条件,则每次查询所有的行。实际应用中,一般要指定查询的条件。对记录进行过滤。

5.5.2 条件查询的语法

  1. SELECT 字段名 FROM 表名 WHERE 条件;
  2. 流程:取出表中的每条数据,满足条件的记录就返回,不满足条件的记录不返回

准备数据

创建一个学生表,包含如下列

  1. CREATE TABLE student3 (
  2. id int, -- 编号
  3. name varchar(20), -- 姓名
  4. age int, -- 年龄
  5. sex varchar(5), -- 性别
  6. address varchar(100), -- 地址
  7. math int, -- 数学
  8. english int -- 英语
  9. );
  10. INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','
  11. 杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩
  12. ',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港
  13. ',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);

运算符

比较运算符说明
>、 <、 <=、 >=、 =、 <><>在 SQL 中表示不等于,在 mysql 中也可以使用!=
没有==
BETWEEN...AND在一个范围之内,如: between 100 and 200
相当于条件在 100 到 200 之间,包头又包尾
IN(集合)集合表示多个值,使用逗号分隔
LIKE '张%'模糊查询
IS NULL查询某一列为 NULL 的值,注:不能写=NULL

具体操作

  1. -- 查询 math 分数大于 80 分的学生
  2. select * from student3 where math>80;
  3. -- 查询 english 分数小于或等于 80 分的学生
  4. select * from student3 where english <=80;
  5. -- 查询 age 等于 20 岁的学生
  6. select * from student3 where age = 20;
  7. -- 查询 age 不等于 20 岁的学生,注:不等于有两种写法
  8. select * from student3 where age <> 20;
  9. select * from student3 where age != 20;

逻辑运算符

逻辑运算符说明
and 或 &&与, SQL 中建议使用前者,后者并不通用。
or 或 ||
not 或 !

具体操作

  1. -- 查询 age 大于 35 且性别为男的学生(两个条件同时满足)
  2. select * from student3 where age>35 and sex='男';
  3. -- 查询 age 大于 35 或性别为男的学生(两个条件其中一个满足)
  4. select * from student3 where age>35 or sex='男';
  5. -- 查询 id 是 1 或 3 或 5 的学生
  6. select * from student3 where id=1 or id=3 or id=5;

in 关键字

  1. SELECT 字段名 FROM 表名 WHERE 字段 in (数据 1, 数据 2...);
  2. in 里面的每个数据都会作为一次条件,只要满足条件的就会显示

具体操作

  1. -- 查询 id 是 1 或 3 或 5 的学生
  2. select * from student3 where id in(1,3,5);
  3. -- 查询 id 不是 1 或 3 或 5 的学生
  4. select * from student3 where id not in(1,3,5);

范围查询

  1. BETWEEN1 AND2
  2. 表示从值 1 到值 2 范围,包头又包尾
  3. 比如: age BETWEEN 80 AND 100 相当于: age>=80 && age<=100

查询 english 成绩大于等于 75,且小于等于 90 的学生

select * from student3 where english between 75 and 90;

like 关键字

  1. LIKE 表示模糊查询
  2. SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';

MySQL 通配符

通配符说明
%匹配任意多个字符串
_匹配一个字符
  1. -- 查询姓马的学生
  2. select * from student3 where name like '马%';
  3. select * from student3 where name like '马';
  4. -- 查询姓名中包含'德'字的学生
  5. select * from student3 where name like '%德%';
  6. -- 查询姓马,且姓名有两个字的学生
  7. select * from student3 where name like '马_';
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/繁依Fanyi0/article/detail/489651
推荐阅读
相关标签
  

闽ICP备14008679号