Mysql——表操作、插入数据、查询数据、删除和更新数据[笔记整理]

库和表操作

一、创建和删除数据库

1. 创建一个指定的数据库： CREATE DATABASE 数据库名

例：建立test数据库。
CREATE DATABASE IF NOT EXISTS test;         #如果不存在test，就创建该数据库

2. 删除一个指定的数据库：DROP DATABASE 数据库名

例：删除test数据库。
DROP DATABASE IF EXISTS test;                # 如果存在test，则删除该数据库
SHOW DATABASES ; #查看数据库
USE test; #选择数据库

二、创建表 

1. 创建基本表语法：

CREATE TABLE [IF [NOT] EXIST ]
表名（
字段1 数据类型 [<列级完整性约束1> <列级完整性约束2> …],
字段2 数据类型 [<列级完整性约束>],
...
[，表列级完整性约束条件]）;

➢ 完整性约束，在创建表时表中添加约束，也可以使用ALTER语句添加

2. 常见的完整性约束 

约束	说明
primary key	主键约束，标识某字段的值非空且唯一
foreign key	外键约束，标识该字段为外键关联其他表
unique	唯一性约束
null / not null	空值/ 非空值约束
auto_increment	该字段的值自动增长（整数类型，且为主键）
default default_value	默认值约束
unsigned/signed	无符号，即正数（反之为有符号，即有正负的数据）
zerofill	使用0填充，仅对数值类型生效
check	约束表中某个或某些列的数据值或者数据格式

3. 创建表的注意事项

➢ 完整性约束条件：从作用上分为 列级的完整性约束和 表级的完整性约束
1. 表级约束：可以约束表中任意一个或多个字段。与列定义的相互独立； 与列定义用逗号分隔；必须指出约
束的列的名称。
2. 列级约束：包含在列定义中，直接跟在该列的其他定义之后， 多个列级约束用空格分隔，不必指定列名。
➢若完整性约束条件涉及该表的多个属性列，则必须定义在表级上，外键必须定义在表级

实例演练：

 要求：建立一个表student：
第一列，学号，整数类型，主键, 自增长,备注学号
第二列，姓名，可变字符类型，长度25，不允许为空，备注姓名
第三列，年龄，非负整数类型，允许为空，备注年龄
第四列，性别，定长字符，长度1，不允许为空，默认值为‘男’，备注性别
表级约束，学号和姓名唯一，设置学号为索引

CREATE TABLE IF NOT EXISTS
student(
 id        INT(6) ZEROFILL PRIMARY KEY AUTO_INCREMENT COMMENT '学号'，
 sname     VARCHAR(25) NOT NULL COMMENT '姓名',
 age       INT UNSIGNED NULL COMMENT '年龄',
 sex       CHAR(1) NOT NULL DEFAULT '男' COMMENT '性别',  #id到sex后面的均为列级约束
 UNIQUE(id,sname),INDEX(id)            # 表级约束
);

drop table if EXISTS student;         # 创建新表前，先删除同名的表
create table if not EXISTS student(           #如果不存在，则创建表
`id` int PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname varchar(25) not null COMMENT '姓名',
age int UNSIGNED null COMMENT '年龄',
sex set('男','女') not null COMMENT '性别' ,
UNIQUE(id, sname),index(id));

注：

1.关键字，不能拿来作为列名或自定义函数名，内置函数也是如此。

2.建表时若想将关键字作为列名使用，需要将列名使用号 单引号`符号包裹。例如：`name`

3.通常Navicat查询界面中的蓝色字体为关键字

三、数据的完整性

定义：数据完整性是指数据的精确性和可靠性。

数据完整性分为三类：

• 实体完整性（Entity Integrity）
• 域完整性 （Domain Integrity）
• 参照完整性（Referential Intefrity）

注：实体完整性、域完整性和参照完整性分别在行、列、表上实施

1. 实体完整性

➢  是指保证表中所有的行唯一

➢  实体完整性要求每一个表中的关键字段都不能为空或重复值【即行唯一性】

➢  强制实体完整性的方法：

• 主键约束
• 唯一约束
• 自动增长列（Auto_increment）
• 索引（index）等唯一索引或主键索引

2. 域完整性

➢  是针对一个列的输入有效性的约束条件，它保证表中某些列不能输入无效的值。

➢  强制域完整性的方法：

• 限制类型（通过设定列的数据类型）
• 格式（通过CHECK约束）
• 可能值的范围（通过FOREIGN KEY、CHECK、DEFAULT、NOT NULL等约束）

CHECK约束：

MySQL所有的存储引擎均不支持CHECK约束，MySQL会对CHECK子句进行分析，但是在插入数据时会忽略，因此CHECK并不起作用。

因此，解决CHECK无效的两种方式：

① 若要设置CHECK约束的字段范围小，并且比较容易列举全部的值，就可以考虑将该字段的类型设置为枚举类型 ENUM()或集合类型SET()

 ② 若需要设置CHECK约束的字段范围大，且列举全部值比较困难，例如整数或者某一区间，可以使用触发器
来代替约束实现数据的有效性

3.参照完整性（引用完整性）

➢  是指保证主关键字（被引用表）和外部关键字（引用表）之间的参照关系。涉及两个或两个以上表数据的一致性维护。

➢  外键的主要作用是保证数据引用的一致性、完整性

•       被引用表（主表、父表）：主键所在的表。
•       引用表（从表、子表）：外键所在的表。

四、主键与外键的关系

主键：主关键字是表中的一个或多个字段，它的值用于唯一的标识表中的某一条记录

——主键是紧凑的，因此整数类型比较适合

——1个表中只可以有一个主键

外键：外键将子表中包含此外键的记录和父表中主键与外键相匹配的记录关联起来

——外键列类型尽可能与主键列类型保持一致，可以是一列或多列

——外键是表中的一个字段，可以不是本表的主键，但对应另外一个表的主键

——1个表可以有一个或多个外键

五、表的操作

1.查看表

a）查看表结构

• DESCRIBE table_name;
• DESC table_name;

b）查看建表语句

• SHOW CREATE TABLE table_name;

c）查看表的数据

• SELECT  *  FROM table_name;

2.修改表

语法： ALTER TABLE 表名 action；

action为以下行为语句：

1）添加列： add 列名 列类型  [FIRST] | [AFTER 列名]

例：

alter table student add tall float      #修改student表添加类型为float的tall身高列，添加到列尾

alter table student add kk varchar(50)  First;       #添加到首列

2）添加主键：add primary key(列名);

alter table std add primary key(out_time);     #添加主键

3）添加外键：add foreign key(列名) references 父表名(列名)

alter table scores add FOREIGN KEY (cid) REFERENCES courses(cid);     #添加外键

4） 修改列的数据类型：modify  列名  属性

alter table std modify kk int;   

# 修改数据类型（修改kk列的数据类型为int，modify意为修改）

5）修改列名：change 旧列名  新列名  数据类型 

alter table std change kk sid timestamp;   # 修改列名和数据类型，将列kk重命名为sid

6）删除列：drop 列名

7）删除主键：drop primary key ，自增长主键不可删除，需先修改其类型不为自增长

8）更改表名：rename as 新表名 、  rename to 新表名

插入数据

一、INSERT操作

语法：INSERT INTO < 表名> [ 列名] VALUES < 值列表>

注：若表名后面没写字段名，则默认所有字段添加值。 

示例：
INSERT INTO student(id, sname, age, sex, address)
VALUES (1, '乔峰', 36, '男', '丐帮');

注2: 具有缺省值的列， 可以使用DEFAULT （缺省）关键字来代替插
入的数值

INSERT INTO student (stu_id, stu_name, age, address)
VALUES (9, '张三', 50, DEFAULT);

多行插入

方法一：直接插入数据

insert into studen(id,sname,age,sex,address)
values
(1, '乔峰', 36, '男', '丐帮'),
(2, '段誉', 36, '男', '大理'),
(3, '令狐冲', 36, '男', '华山'),
(4, '张三丰', 87, '男', '武当'),
(5, '黄蓉', 42, '女', '桃花岛'),
(6, '慕容复', 66, '男', NULL);

方法二： 复制表批量插入数据

 #复制student表结构，新复制的表名为copy
CREATE TABLE scopy LIKE student; 
 #通过SELECT语句批量插入数据
INSERT INTO scopy SELECT * FROM student; 

复制表

 1.直接复制表和数据

CREATE TABLE student_like SELECT * FROM student;

2.复制表结构，但不复制数据

CREATE TABLE student_like SELECT * FROM student limit 0;     

 #LIMIT 0 表示查询表中的第0条数据，即不复制表数据

CREATE TABLE student_like LIKE student;

3.通过查看建表语句，复制其建表语句去执行创建表

show create  table sudent_like;    #展示创建的表，即查看建表语句

查询数据

一、基本语法

#示例1：查询全体学生的学号与姓名。
SELECT id,sname FROM student;
#示例2：查询全体学生的学号与姓名，去掉重复的记录。
SELECT DISTINCT id,sname FROM student;
#示例3：查询全体学生的学号与姓名，用中文显示列名。
SELECT id '学号', sname '姓名' FROM student;
#示例4：查询年龄大于30的学生学号与姓名。
SELECT id, sname FROM student WHERE age>30;
#示例5：查询成绩表所有的学生学号。(去重)
SELECT DISTINCT id FROM scores;

注：去重操作常用distinct方法

二、集函数

➢ COUNT函数表示计数。有几种常用的用法：

• COUNT（*）：统计所有列的总行数
• COUNT（1）：1表示恒真表达式，统计总行数
• COUNT（[DISTINCT] 字段）：统计字段总行数，不包括NULL值，DISTINCT是除去NULL值和重复数据

#查询所有学生住址数量
SELECT COUNT(address), COUNT(*), COUNT(1) FROM student;
#查询每个学生的成绩数量
SELECT id, COUNT(score), COUNT(*), COUNT(1)
FROM scores GROUP BY id;

 ➢ COUNT函数的三种方法在执行效率上：
• 若表只有一个字段且字段为主键， COUNT(*) > COUNT(字段) > COUNT(1)。
• 若表只有一个字段且字段不为主键， COUNT(*) > COUNT(1) > COUNT(字段)。
• 若表多个字段并且没有主键，COUNT(1)> COUNT(*)。
• 若表有多列且有主键，COUNT(主键) > COUNT(1) > COUNT(*)。

➢ 从执行结果来说：
• COUNT(1)和COUNT(*)之间没有区别，因为都不会过滤NULL值。
• COUNT(字段)会过滤NULL值

三、IF语句

语法：IF（条件，值1，值2）

示例：将查询结果的男女替换为0、1

SELECT id,IF(sex='男',0,1)  AS sex FROM student;     

 #当sex为男时返回0，否则返回1（即为sex为女）

IF函数通常与SUM、COUNT等函数组合，用来计算不同条件数据的结果

示例：查询不同性别的人数

SELECT SUM(IF(sex='男',0,1))'男',

                SUM(IF(sex='女',0,1))'女' FROM student;

四、IFNULL语句

➢ IFNULL函数用于将查询结果NULL转换为特定的值

语法：IFNULL（值1，值2）

示例：查询每个学生的分数，将NULL替换成0

SELECT IFNULL(score,0) FROM scores;

示例：查询每个学生的总分，将NULL替换成0

SELECT id,SUM(score), IFNULL(SUM(score) , 0)   FROM scores group by id;

五、CASE WHEN语句

➢ 若要将分数转换为成绩等级：分数0-59为E级，60-70为D级，71-80为C级，81-90为B级，90以上为A级。此类型需求可以用case when语句表达实现

语法：case when + 条件语句 then 结果语句

或 case  字段或表达式  when  + 值或条件语句 then  结果语句（输出值）

简单实例：

# 若sex='男'，则返回值0，反之为1，其他情况为99
SELECT id,sex,CASE sex WHEN '男' then 0
                    WHEN '女' then 1
                    ELSE 99 END AS sex1  
                    FROM  student;

删除和更新数据

一、DELECT操作

语法： DELECT FROM 表名 [WHERE<删除条件>]

• TRUNCATE TABLE 可以清空表中所有数据，表和表结构不被删除，释放空间
• TRUNCATE 和 DELETE 均不会使表结构及其列、约束、索引等发生改变

示例：删除student表中的所有年龄小于20岁的记录

delete from student where age<20;

二、UPDATE操作

语法：UPDATE 表名 SET （列名=更新值） WHERE 更新条件

示例：将student表中的学生名称为“乔峰”的改为“小风”

update student set sname='小风' where sname='乔峰';

如何根据不同条件更新不同的行？

# 示例：根据成绩修改为成绩等级
ALTER TABLE scores ADD rank CHAR(1);   #修改score表，添加rank列，数据类型为char
UPDATE scores SET rank =
CASE WHEN score<60 THEN 'E'
WHEN score<=70 THEN 'D'
WHEN score<=80 THEN 'C’
WHEN score<=90 THEN 'B'
ELSE 'A' END ;