MySQL 基础 - 表操作与表联结

2.1 MySQL 基础 (二)- 表操作

1. MySQL表数据类型

参考:MySQL数据类型,详细内容参照该篇
MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型

数值类型
MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值,并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展,MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。

日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性,将在后面描述。

字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

2. 用SQL语句创建表

语句解释; 设定列类型 、大小、约束; 设定主键
创建MySQL数据表需要以下信息:

  • 表名
  • 表字段名
  • 定义每个表字段

语法
以下为创建MySQL数据表的SQL通用语法:

CREATE TABLE table_name (column_name column_type);

以下例子中我们将在 RUNOOB 数据库中创建数据表runoob_tbl:

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

实例解析:

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL, 在操作数据库时如果输入该字段的数据为NULL ,就会报错。
AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键,列间以逗号分隔。
ENGINE 设置存储引擎,CHARSET 设置编码。

3. 用SQL语句向表中添加数据

语句解释; 多种添加方式(指定列名;不指定列名)

MySQL 表中使用 INSERT INTO SQL语句来插入数据。

以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法:

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

如果数据是字符型,必须使用单引号或者双引号,如:“value”。

注:添加数据的时候可以规定列进行添加。如果所有的列都要添加数据可以不规定列进行添加数据:

4. 用SQL语句删除表

语句解释; DELETE;DROP;TRUNCATE;不同方式的区别

MySQL中删除数据表非常容易操作, 但在进行删除表操作时要非常小心,因为执行删除命令后所有数据都会消失。

语法:以下为删除MySQL数据表的通用语法:

DROP TABLE table_name ;

三种删除方式区别:
1、删除表内数据,用 delete。格式为:

delete from 表名 where 删除条件;

实例:删除学生表内姓名为张三的记录。

delete from  student where  T_name = "张三";

2、清除表内数据,保存表结构,用 truncate。格式为:

truncate table 表名;

实例:清除学生表内的所有数据。

truncate  table  student;

3、删除表用 drop,就是啥都没了。格式为:

drop  table  表名;

实例:删除学生表。

drop table student;

对比:
1、当你不再需要该表时, 用 drop;

2、当你仍要保留该表,但要删除所有记录时, 用 truncate;

3、当你要删除部分记录时, 用 delete。

5. 用SQL语句修改表

修改列名:
语法: ALTER TABLE 表名字 CHANGE 列名称 新列名称

修改表中数据:
Update 语句用于修改表中的数据。

语法: UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

在这里插入图片描述
将王老五的PersonId字段修改为 ’ 3 ’

UPDATE Person SET PersonId = '3' WHERE  PersonId = '5';

在这里插入图片描述

删除行:
DELETE 语句用于删除表中的数据。
DELETE 语句的基本格式如下所示:

DELETE  FROM tbl_name
WHERE  which rows to delete;

其中,WHERE子句是可选的,它用于指定需要删除掉哪些行。如果没有WHERE 子句,那么将删除表里面的所有行。这意味着,越简单的DELETE语句越危险,例如下面的语句:

DELETE FROM tbl_name

它会彻底删除表里的内容,因此务必要小心使用。

删除列:

   alter table 表名 drop column 列名;

新建列:

   alter table 表名 add column 列名 varchar(30);

新建行:
insert可以插入多条记录,记录用圆括号括起来,用逗号分隔开

insert into table_name(col1,col2...) values (col1,col2,...),(col2,col2,...)

项目三:超过5名学生的课(难度:简单)

创建如下所示的courses 表 ,有: student (学生) 和 class (课程)。
例如,表:
±--------±-----------+
| student | class |
±--------±-----------+‘’
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
| A | Math |
±--------±-----------+

编写一个 SQL 查询,列出所有超过或等于5名学生的课。
应该输出:
±--------+
| class |
±--------+
| Math |
±--------+
Note:
学生在每个课中不应被重复计算。

创建表:

   CREATE TABLE courses (
   student VARCHAR(20) NOT NULL PRIMARY KEY,
   class VARCHAR(25) NOT NULL
    );

插入:

INSERT INTO courses VALUES ('A', 'Math'),
 ('B', 'English'),
 ('C', 'Math'),
 ('D', 'Biology'),
 ('E', 'Math'),
 ('F', 'Computer'),
 ('G', 'Math'),
 ('H', 'Math'),
 ('I', 'Math'),
 ('A', 'Math');

在这里插入图片描述
列出所有超过或等于5名学生的课:

 SELECT class from courses group by class having count(DISTINCT student) >= 5;

在这里插入图片描述

项目四:交换工资(难度:简单)

创建一个 salary表,如下所示,有m=男性 和 f=女性的值 。
例如:

idnamesexsalary
1Am2500
2Bf1500
3Cm5500
4Df500

交换所有的 f 和 m 值(例如,将所有 f 值更改为 m,反之亦然)。要求使用一个更新查询,并且没有中间临时表。
运行你所编写的查询语句之后,将会得到以下表:

idnamesexsalary
1Af2500
2Bm1500
3Cf5500
4Dm500

建表:

CREATE TABLE  IF NOT EXISTS `salary` (
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
sex ENUM('m','f','u') DEFAULT 'u',
salary INT DEFAULT 0
);

插入数据后表内容:
在这里插入图片描述
交换:

UPDATE salary 
SET sex=IF(sex='f','m','f');

在这里插入图片描述

2.2 MySQL 基础 (三)- 表联结

1MySQL别名
INNER JOIN
LEFT JOIN
CROSS JOIN
自连接
UNION
以上几种方式的区别和联系

项目五:组合两张表 (难度:简单)

在数据库中创建表1和表2,并各插入三行数据(自己造)
表1: Person
±------------±--------+
| 列名 | 类型 |
±------------±--------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
±------------±--------+
PersonId 是上表主键

表2: Address
±------------±--------+
| 列名 | 类型 |
±------------±--------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
±------------±--------+
AddressId 是上表主键

编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:FirstName, LastName, City, State


建Person表:

CREATE TABLE IF NOT EXISTS Person
(     PersonId INT NOT NULL,
      FirstName VARCHAR(30) NOT NULL,
      LastName VARCHAR(30) NOT NULL);

建Address表:

CREATE TABLE IF NOT EXISTS Address(
AddressId INT ,
 PersonId INT NOT NULL, 
 City VARCHAR(30) NOT NULL, 
 State VARCHAR(30) NOT NULL);

插入数据:
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

项目六:删除重复的邮箱(难度:简单)

编写一个 SQL 查询,来删除 email 表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。
±—±--------+
| Id | Email |
±—±--------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
±—±--------+
Id 是这个表的主键。
例如,在运行你的查询语句之后,上面的 Person表应返回以下几行:
±—±-----------------+
| Id | Email |
±—±-----------------+
| 1 | a@b.com |
| 2 | c@d.com |
±—±-----------------+

在这里插入图片描述

  • 1
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值