sql语句增删改查的基本命令 数据库的增删改查sql语句

约束种类:

• not null: 非空 ; eg: username varchar(40) not null username这个列不能有null值
• unique:唯一约束, 后面的数据不能和前面重复; eg: cardNo char(18) unique; cardNo 列里面不可以有重复数据

• primary key；主键约束(非空+唯一); 一般用在表的id列上面. 一张表基本上都有id列的, id列作为唯一标识的 auto_increment: ==自动增长,必须是设置了primary key之后,才可以使用auto_increment==
• id int primary key auto_increment; id不需要我们自己维护了, 插入数据的时候直接插入null, 自动的增长进行填充进去, 避免重复了.

注意:

先设置了primary key 再能设置auto_increment

只有当设置了auto_increment 才可以插入null 自己维护 否则插入null会报错

id列:

1. 给id设置为int类型, 添加主键约束, 自动增长
2. 或者给id设置为字符串类型,添加主键约束, 不能设置自动增长

3.4练习

• 创建一张学生表(含有id字段,姓名字段,性别字段. id为主键自动增长)

CREATE TABLE student(
 id INT PRIMARY KEY AUTO_INCREMENT,
 `name` VARCHAR(40) NOT NULL,
 sex INT
);

4.小结

1. 语法

create table 表名(
    列 类型 【约束】,
    列 类型 【约束】
);

1. 类型数值：int bigint double 偶尔使用tinyint字符：char(n) 固定长度 varchar(n) 可变长度时间：Date DateTime TimeStamp
2. 约束not null 非空unique 唯一primary key 主键(非空+唯一)auto_increment 自动增长
3. iid可以设置成int类型, 设置成primary key, 添加auto_increment 作为记录唯一标识
4. 注意：

列名：见名识义 多个单词用_拼接
列类型：tinyint int bigint double char varchar datetime timestamp
列约束：
 not null:不能为空
 primary key：主键，限制表中的每一行都是唯一的，主键所在的列不能为空也不可以重复
 unique key：唯一约束 限制当前列可以为null(只能出现一个)，但是不能重复
 primary key == unique key + not null
列属性：
 auto_increment：自增 用在主键列上，并且该列为整型
 zerofill：零填充 用在整型类型列上
 unsigned：无符号，最高位不表示正负
 
    1.auto_increment需要和primary key 一起使用
    2.unsigned、zerofill不能和primary key一起使用

知识点-查看表【了解】

1.目标

我们把表创建好了, 下面就来介绍查看表

2.步骤

• 查看当前数据库所有的表
• 查看表的定义结构

3.讲解

3.1查看所有的表

show tables;

3.2查看表的定义结构

• 语法desc 表名；
• 练习: 查看student表的定义结构

desc student;

知识点-修改表【掌握】

1.目标

我们表创建好了, 如果要增加一列,要删除一列呢# 那下面就来讲解修改表

2.步骤

• 增加列
• 修改列的类型约束
• 修改列的名称，类型，约束
• 删除列
• 修改表名

3.讲解

3.1语法

• 增加一列；alter table 表 add 字段 类型 约束；
• 修改列的类型约束； alter table 表 modify 字段 类型 约束 ；
• 修改列的名称，类型，约束；alter table 表 change 旧列 新列 类型 约束;
• 删除一列； alter table 表名 drop 列名；
• 修改表名 ; rename table 旧表名 to 新表名；

3.2练习

• 给学生表增加一个grade字段

alter table student add grade varchar(20) not null;

• 给学生表的sex字段改成字符串类型

alter table student modify sex varchar(10);

• 给学生表的grade字段修改成class字段

alter table student change grade class varchar(20);

• 把class字段删除

alter table student drop class;

• 把学生表修改成老师表(了解)

rename table student to teacher

4.小结

1. 修改表都是以alter table tb_name打头
2. 增加列 add
3. 修改列的类型 约束 modify
4. 修改列的名字 类型 约束 change
5. 删除列 drop
6. 修改表名 rename

知识点-删除表【掌握】

1.目标

表创建好了, 我们还可以删除。 掌握表的删除

2.步骤

• 删除表

3.讲解

• 语法drop table 表名；
• 把teacher表删除

drop table teacher;

4.小结

1. 删除表语法

drop table 表名;

1. 创建表create table tb_name(
   列名 类型 约束 ,…);
2. 修改表add 、modify 、change 、 drop 、rename
3. 查看表show tables;
   desc tb_name;

第六章-DML操作表记录-增删改【重点】

• 准备工作: 创建一张商品表(商品id,商品名称,商品价格,商品数量.)

create table product(
    pid int primary key auto_increment,  //只有设置了auto_increment id列才可以赋值为null
    pname varchar(40),
    price double,
    num int
);

知识点-插入记录

1.目标

• 掌握插入记录

2.步骤

• 两种方式插入数据
• 使用命令行操作时候乱码的解决

3.讲解

• 方式一: 插入指定列, ==如果没有把这个列进行列出来, 以null进行自动赋值了==.eg: 只想插入pname, price , insert into t_product(pname, price) values(‘mac’,18000);

insert into 表(列，列..) values(值，值..)；

注意: 如果没有插入了列设置了非空约束, 会报错的

• 方式二: 插入所有的列

insert into 表 values(值，值....);           

eg:
insert into product values(null,'苹果电脑',18000.0,10);
insert into product values(null,'华为5G手机',30000,20);
insert into product values(null,'小米手机',1800,30);
insert into product values(null,'iPhonex',8000,10);
insert into product values(null,'苹果电脑',8000,100);
insert into product values(null,'iPhone7',6000,200);
insert into product values(null,'iPhone6s',4000,1000);
insert into product values(null,'iPhone6',3500,100);
insert into product values(null,'iPhone5s',3000,100);

insert into product values(null,'方便面',4.5,1000);
insert into product values(null,'咖啡',11,200); 
insert into product values(null,'矿泉水',3,500);

命令行插入中文数据报错:

• 关闭服务, net stop MySql
• 在数据库软件的安装目录下面, 修改配置文件 my.ini中客户端的编码为gbk

• 重新打开命令行,开启服务, net start MySql

4.小结

1. 语法

• 插入特定的列

insert into 表名(字段列表) values(值列表)

插入所有的列

insert into 表名 values(值,值,值....) 

多行插入

insert into 表名 values(值列表),(值列表),(值列表)...

注意：

• 插入特定的列没有赋值的列,系统自动赋为null(前提是当前列没有设置not null 约束)
• 在插入指定列数据时，可以省略不插入的是一些（可以为空的列、设置默认值的列、设置AUTO_INCREMENT的列）。
• 列名与列值的类型、个数、顺序要一一对应。
• 值不要超出列定义的长度。eg：password varchar(12) abcd123456789
• 插入的日期和字符串，使用单引号括起来。

知识点-更新记录

1.目标

我们数据插入成功了， 还可以对已有的数据进行更新。

2.步骤

• 更新数据

3.讲解

3.1语法

update 表 set 列 =值, 列 =值 [where 条件]

3.2练习

• 将所有商品的价格修改为5000元

update product set price = 5000;

• 将商品名是Mac的价格修改为18000元

UPDATE product set price = 18000 WHERE name = 'Mac';

• 将商品名是Mac的价格修改为17000,数量修改为5

UPDATE product set price = 17000,num = 5 WHERE name = 'Mac';

• 将商品名是方便面的商品的价格在原有基础上增加2元

UPDATE product set price = price+2 WHERE name = '方便面';

4.小结

1. 语法

update tb_name set 列1=值1[,列2=值2...] where条件

1. 注意实际工作中，where 条件必不可少，不可省略

知识点-删除记录

1.目标

• 掌握记录的删除

2.步骤

• 使用delete删除
• 使用truncate删除

3.讲解

3.1delete

• 语法

delete from 表 [where 条件]    注意: 删除数据用delete,不用truncate

• 类型

删除表中名称为’Mac’的记录

delete from product where pname = 'Mac';

删除价格小于5001的商品记录

delete from product where price < 5001;

删除表中的所有记录

delete from product;

3.2truncate

truncate table 表;

4.小结

1. 删除记录

delete from 表 【where 条件】 注意：实际开发中，where条件一定不能少

1. delete 和truncate区别【面试题】 DELETE 删除表中的数据，表结构还在; 删除后的数据可以找回,一条一条的删除.

• TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。删除的数据不能找回。执行速度比DELETE快。

1. 工作里面的删除 物理删除: 真正的删除了, 数据不在, 使用delete就属于物理删除逻辑删除: 没有真正的删除, 数据还在. 搞一个标记, 其实逻辑删除是更新 eg: del_flag 0存在 1删除工作里面一般使用逻辑删除用得多 ALTER TABLE product ADD del_flag TINYINT(4) DEFAULT 0 NOT NULL;
   # 逻辑删除UPDATE product SET del_flag=1 WHERE pid=3;

第七章-DQL操作表记录-查询【重点】

知识点-单表查询
1.目标

我们上面讲解了对数据的增删改, 下面就来重点讲解数据的简单查询.

2.路径

• 基本查询语法
• 查询所有的列
• 查询某张表特定列
• 去重查询
• 别名查询
• 运算查询(+,-,*,/等)
• 基本条件查询

3.讲解

3.1基本查询语法

select  [*] [列名 ,列名] [列名 as 别名 ...] [distinct 字段] from 表名 [where 条件] 

3.2简单查询

3.2.1 查询所有的列的记录

• 语法

select * form 表

• 查询商品表里面的所有的列

select * from product;

3.2.2查询某张表特定列的记录

• 语法

select 列名,列名,列名... from 表

• 查询商品名字和价格

select pname, price from product;

3.2.3 去重查询

• 语法

SELECT DISTINCT 字段名 FROM 表名;   //要数据一模一样才能去重

• 去重查询商品的价格

select distinct price from product;

注意点: 去重针对某列, distinct前面不能先出现列名

3.2.4 别名查询

• 语法

select 列名 as 别名 ,列名  from 表   //列别名  as可以不写
select 别名.* from 表 as 别名      //表别名(多表查询, 看主页另外一篇Mysql高级)

• 查询商品名称和商品价格，商品价格通过别名‘价格’来显示

select pname , price as 价格 from product;

3.2.5运算查询(+,-,*,/等)

• 把商品名，和商品价格+10查询出来

select pname ,price+10 from product;

注意

• 运算查询字段,字段之间是可以的
• 字符串等类型可以做运算查询，但结果没有意义

3.3条件查询

3.3.1语法

select ... from 表 where 条件 
//取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回

1. between…and… 区间查询

eg: where price between  1000 and 3000  相当于 1000<=price<=3000 

1. in(值,值..)

-- 查询id为1,3,5,7的
select * from t_product where id = 1
select * from t_product where id = 3
select * from t_product where id = 5
select * from t_product where id = 7

select * from t_product where id in(1,3,5,7)

1. like 模糊查询 一般和_或者%一起使用_ 占一位% 占0或者n位

name like '张%'  --查询姓张的用户, 名字的字数没有限制
name like '张_'  --查询姓张的用户 并且名字是两个的字的

1. and 多条件同时满足

where 条件1 and 条件2 and 条件3

1. or 任意条件满足

where 条件1 or 条件2 or 条件3

3.3.2练习

• 查询商品价格>3000的商品

select * from product where price > 3000;

• 查询id=1的商品

select * from product where pid = 1;

• 查询id<>1的商品

select * from product where pid <> 1;

• 查询价格在3000到6000之间的商品

select * from product where price between 3000 and 6000;

• 查询id在1，5，7，15范围内的商品

select * from product where id = 1;
select * from product where id = 5;
select * from product where id = 7;
select * from product where id = 15;

select * from product where id in (1,5,7,15);

• 查询商品名以iPho开头的商品(iPhone系列)

select * from product where pname like 'iPho%';

• 查询商品价格大于3000并且数量大于20的商品 (条件 and 条件 and…)

select * from product where price > 3000 and num > 20;

• 查询id=1或者价格小于3000的商品

select * from product where pid = 1 or price < 3000;

4.小结

• 语法

select [*]|[字段列表] from 表名 [where条件]
select * from tb_name;

• 条件where 条件
• between … and …
• in
• like _|%

知识点-排序查询

1.目标

• 能够使用SQL语句进行排序

2.分析

有时候我们需要对查询出来的结果排序显示，那么就可以通过ORDER BY子句将查询出的结果进行排序。排序可以根据一个字段排，也可以根据多个字段排序，排序只是对查询的结果集排序，并不会影响表中数据的顺序。

3.讲解

3.0 环境的准备

# 创建学生表(有sid,学生姓名,学生性别,学生年龄,分数列,其中sid为主键自动增长)
CREATE TABLE student(
    sid INT PRIMARY KEY auto_increment,
    sname VARCHAR(40),
    sex VARCHAR(10),
    age INT,
  score DOUBLE
);

INSERT INTO student VALUES(null,'zs','男',18,98.5);
INSERT INTO student VALUES(null,'ls','女',18,96.5);
INSERT INTO student VALUES(null,'ww','男',15,50.5);
INSERT INTO student VALUES(null,'zl','女',20,98.5);
INSERT INTO student VALUES(null,'tq','男',18,60.5);
INSERT INTO student VALUES(null,'wb','男',38,98.5);
INSERT INTO student VALUES(null,'小丽','男',18,100);
INSERT INTO student VALUES(null,'小红','女',28,28);
INSERT INTO student VALUES(null,'小强','男',21,95);

3.1单列排序

1. 语法: 只按某一个字段进行排序，单列排序

SELECT 字段名 FROM 表名 [WHERE 条件] ORDER BY 字段名 [ASC|DESC];  //ASC: 升序，默认值; DESC: 降序

1. 练习: 以分数降序查询所有的学生

SELECT * FROM student ORDER BY score DESC

3.2组合排序

1. 语法: 同时对多个字段进行排序，如果第1个字段相等，则按第2个字段排序，依次类推

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];

1. 练习: 以分数降序查询所有的学生, 如果分数一致,再以age降序

SELECT * FROM student ORDER BY score DESC, age DESC

4.小结

1. 排序的语法

select * from tb_name where条件  order by 排序要使用的列[ASC|DESC [,第二列...]]
order by 列 asc/desc, 列 asc/desc;
asc: 升序【默认值】
desc: 降序

1. 应用场景 商城里面 根据价格, 销量, 上架时间, 评论数… 社交里面 根据距离排序排序不改变表中数据的顺序，只是改变查询结果集的显示顺序

知识点-聚合函数

1.目标

• 能够使用聚合函数

2.分析

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是==纵向查询==，它是对一列的值进行计算，然后返回==一个结果值==。聚合函数会忽略空值NULL

聚合函数用于数学计算统计！

3.讲解

SELECT 聚合函数(列名) FROM 表名 [where 条件];

-- 求出学生表里面的最高分数
SELECT MAX(score) FROM student
-- 求出学生表里面的最低分数
SELECT MIN(score) FROM student
-- 求出学生表里面的分数的总和(忽略null值)
SELECT SUM(score) FROM student
-- 求出学生表里面的平均分
SELECT AVG(score) FROM student
-- 统计学生的总人数 (忽略null) 
SELECT COUNT(sid) FROM student
SELECT COUNT(*) FROM student

select 聚合函数(列) from 表名;

SELECT 字段1,字段2... FROM 表名  [where 条件] GROUP BY 列 [HAVING 条件];

-- 根据性别分组, 统计每一组学生的总人数
SELECT sex, count(*) FROM student GROUP BY sex

SELECT sex, count(*) FROM student GROUP BY sex HAVING count(*) > 5

select 分组列,分组统计结果 from 表 [where条件] group by 列 [having条件]

select ... from .... limit a ,b.

eg: 分页查询学生, 每一页查询4条  
b=4;   a=(当前页码-1)*b;

第一页: a=0, b=4;
第二页: a=4, b=4;
第三页: a=8, b=4;

limit a,b; 
a:从第几条数据开始查询, 从0开始计数 【a=(当前页码-1)*b】
b: 一页查询的数量【固定的,自定义的】

select...from...where...group by...order by...limit

select...from...where...
select...from...where...order by...
select...from...where...limit...
select...from...where...order by...limit