sql 增删改合表操作

最新推荐文章于 2024-07-24 17:38:32 发布

ddl12131

最新推荐文章于 2024-07-24 17:38:32 发布

阅读量414

点赞数

文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/ddl12131/article/details/121706719

版权

第二种:
Select ename,job from emp where job in (‘Manager’,salesman’);

mysql> desc emp;
±---------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±---------±------------±-----±----±--------±------+
| EMPNO | int(4) | NO | PRI | NULL | |
| ENAME | varchar(10) | YES | | NULL | |
| JOB | varchar(9) | YES | | NULL | |
| MGR | int(4) | YES | | NULL | |
| HIREDATE | date | YES | | NULL | |
| SAL | double(7,2) | YES | | NULL | |
| COMM | double(7,2) | YES | | NULL | |
| DEPTNO | int(2) | YES | | NULL | |
±---------±------------±-----±----±--------±------+
8 rows in set (0.01 sec)
第三种:union
查询结果集相加

select ename,job from emp where job =‘manager’
-> union
-> select ename, job from emp where job = ‘salesman’;

10.mysql> select ename,sal from emp
-> union
-> select dname from dept;
ERROR 1222 (21000): The used SELECT statements have a different number of columns

11.041 limit以及通用分页SQL.flv
limit的使用
Limit(重点中的重点,以后的分页查询全靠它了)
5.1 limit 是mysql特有的,其他数据库中没有,不通用(oracle 中有个相同的机制叫做rownum)

5.2 limit 取结果集中的部分数据,这时它的作用
5.3 语法机制
Limit startIndex, length
StartIndex 表示起始位置
length表示取几个
案例:取出工资前5名的员工 (思路:降序取前5个)
#排序
Select ename,sal from emp order by sal desc;
mysql> select ename, sal from emp order by sal desc;
±-------±--------+
| ename | sal |
±-------±--------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
| WARD | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
±-------±--------+
14 rows in set (0.00 sec)

Select ename,sal from emp order by sal desc limit 0,5;
mysql> select ename,sal from emp order by sal desc limit 0,5;
±------±--------+
| ename | sal |
±------±--------+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
±------±--------+
5 rows in set (0.00 sec)
取前5个：
Select ename,sal from emp order by sal desc limit 5;
mysql> select ename,sal from emp order by sal desc limit 5;
±------±--------+
| ename | sal |
±------±--------+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
±------±--------+
5 rows in set (0.00 sec)

5.4 limit是sql语句最后执行的一个环节:
Select …5
From …1
Where .2
…group by…3
Having…4
. order by…6
Limit…;7

5.5 案例:找出工资排名在第四到第九名的员工
Select ename, sal from emp from order by sal desc limit 3, 6;
mysql> select ename,sal from emp order by sal desc limit 3,6;
±-------±--------+
| ename | sal |
±-------±--------+
| JONES | 2975.00 |第四名
| BLAKE | 2850.00 | 第5名
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |第九名
±-------±--------+
6 rows in set (0.00 sec)

5.6通用的标准分页sql?
每页显示3条记录:
第一页:0, 3 (1-0)*3
第2页:3,3
第3页：6，3 (3-1)*3
第4页: 9,3 (4-1)*3
第5页:12,3 (5-1)*3
每页显示pagesize条记录
第pageNo页:?? (pageNo-1)*pagesize, pagesize
pagesize是什么?是每页显示多少条记录
pageno是什么？显示第几页
Java 代码:
Int pageNo=2; //页码是2
Int pagesize=10; //每页显示10条

Limit 10, 10
}

5.7表的创建
创建表语句的语句格式:
Create table 表名:
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,
…
);
关于MySQL当中字段的数据类型？以下只说常见的
Int整数型(java中的int); bignit长整型(java中的long); float 浮点型(java中的float double); double ; char ; varchar可变长字符串 ;date日期类型（对应java中的java.sql,Date类型）; BLOB 二进制大对象(存储图片,视频等流媒体信息)
Binary Large Object
CLOB 字符大对象(存储较大文本,比如,可以存储4g的字符串)
char和varchar怎么选择?
Char 在实际开发中，当某个字段中的数据长度不发生
改变的时候，是定长,例如:性别,生日等都是采用char.
当一个字段的数据长度不确定,例如：简介,姓名都是采用
Varchar.
blob和clob的类型的使用
33
电影表:t_movie
Id name playtime haibao history

BloB 和 Clob类型的使用
电影表:t_movie
Id(int) name(varchar) playtime(date/char) haibao(Blob) history(Clob)
1.蜘蛛侠
2.
3.
表名在数据库当中一般建议以：t_或者tbl_开始
创建学生表：
学生信息包括:
学号,姓名,性别，班级编号,生日
学号:bigint
姓名:varchar
性别：char
班级编号:int
生日:char

Create table t_student(
no bright,
name varchar(255),
sex char(1),
Classno varchar(255),
birth char(10)
);
创建学生表：
学生信息包括：学号,姓名,性别, 班级编号, 生日
学号:bigint
mysql> create table t_student(
-> no bigint, #学号
-> name varchar(255), #姓名
-> sex char(1), #性别
-> classno varchar(255), # 班级编号
-> birth char(10)); #生日
Query OK, 0 rows affected (0.01 sec)

任务48：043 向表中插入数据
Insert 语句插入数据
语法格式:
Inset into 表名(字段名1,字段名2,字段名3…) value(值1,值2,值3…)
要求:字段的数量和值的数量相同,并且数据类型要对应相同.

Insert into t_student(no,name, sex, classno, birth) value(1, ‘zhangsan’, ‘1’, ‘高三1’);
mysql> insert into t_student(no, name, sex, classno,birth) values(1,‘zhangsan’,‘1’,‘gaosan1ban’);
ERROR 1136 (21S01): Column count doesn’t match value count at row 1

mysql> insert into t_student(name,sex,classno,birth,no) values(‘lisi’,‘1’,‘gaosan1ban’,‘1950-10-12’,2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_student;
±-----±---------±-----±-----------±-----------+
| no | name | sex | classno | birth |
±-----±---------±-----±-----------±-----------+
| 1 | zhangsan | 1 | gaosan1ban | 1950-10-12 |
| 2 | lisi | 1 | gaosan1ban | 1950-10-12 |
| 2 | lisi | 1 | gaosan1ban | 1950-10-12 |
±-----±---------±-----±-----------±-----------+

mysql> select * from t_student; //除了name字段之外,其他都是null
±-----±---------±-----±-----------±-----------+
| no | name | sex | classno | birth |
±-----±---------±-----±-----------±-----------+
| 1 | zhangsan | 1 | gaosan1ban | 1950-10-12 |
| 2 | lisi | 1 | gaosan1ban | 1950-10-12 |
| 2 | lisi | 1 | gaosan1ban | 1950-10-12 |
| NULL | wangwu | NULL | NULL | NULL |
±-----±---------±-----±-----------±-----------+
4 rows in set (0.00 sec)

mysql> insert into t_student(no) values(3);
Query OK, 1 row affected (0.00 sec)

插入全字段，颠倒字段顺序，部分字段 3种方法。
Task48. 当这个表存在的话删除
Drop table if exists t_student; //当这个表存在的话删除

Task49
mysql> drop table if exists t_student;
Query OK, 0 rows affected (0.01 sec)
创建表
mysql> create table t_student(
-> no bigint,
-> name varchar(255),
-> sex char(1),
-> classno varchar(255),
-> birth char(10));
Query OK, 0 rows affected (0.01 sec)

Task50
向表中插入数据
Insert into t_student values(1,’jack’,’0’,’gaosan2ban’,’1986-10-23’);
mysql> insert into t_student values(1,‘jack’,‘0’,‘gaosan2ban’);
ERROR 1136 (21S01): Column count doesn’t match value count at row 1

//字段可以省略不写但是h后面的value对数量和顺序都有要求
mysql> insert into t_student
-> (no,name,sex,classno,birth)
-> values
-> (3,‘rose’,‘1’,‘gaosi2ban’,‘1952-12-14’),(4,‘laotie’,‘1’,‘gaosi2ban’,‘1955-12-14’);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
//
mysql> select * from t_student;
±-----±-------±-----±-----------±-----------+
| no | name | sex | classno | birth |
±-----±-------±-----±-----------±-----------+
| 2 | lisi | 1 | gaosan1ban | 1950-10-12 |
| 2 | lisi | 1 | gaosan1ban | 1950-10-12 |
| NULL | NULL | NULL | NULL | NULL |
| 1 | jack | 0 | gaosan2ban | 1986-10-23 |
| 1 | jack | 0 | gaosan2ban | 1986-10-23 |
| 1 | jack | 0 | gaosan2ban | 1986-10-23 |
| 3 | rose | 1 | gaosi2ban | 1952-12-14 |
| 4 | laotie | 1 | gaosi2ban | 1955-12-14 |
±-----±-------±-----±-----------±-----------+
8 rows in set (0.00 sec)

Task51表的复制以及批量插入
mysql> create table emp1 as select * from emp;
Query OK, 14 rows affected (0.02 sec)
Records: 14 Duplicates: 0 Warnings: 0

Task51

11
mysql> select * from emp1;
±------±-------±----------±-----±-----------±--------±--------±-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
±------±-------±----------±-----±-----------±--------±--------±-------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |

创建第二个表
mysql> create table emp2 as select empno,ename from emp;
Query OK, 14 rows affected (0.02 sec)
Records: 14 Duplicates: 0 Warnings: 0

8.表的复制
语法:
Create table 表名 as select语句;
将查询结果当作表创建出来
9.将查询结果当作表创建出来
mysql> select * from dept1;
ERROR 1146 (42S02): Table ‘notaeasyday.dept1’ doesn’t exist
mysql> create table dept1 as select * from dept;
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> insert into dept1 select * from dept;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

Task51 修改表中的数据
语法格式
Update 表名 set 字段名1=值1,字段名2=值2… where 条件:
注意:没有条件整张表数据全部更新
案列:
将部门10的loc修改为SHANGHAI,将部门名称修改为RensHIBU
Update dept1 set loc = ‘SHANGHAI’,dname=’人事部’ where deptno =10;

047 删除表中的数据.flv
删除数据
语法格式:
Delete from 表名 where 条件
注意没有条件全部删除

删除10部门数据
Delete from dept1 where deptno =10;
删除所有记录:
Delete from dept1;
怎么删除大表:
Delete from dept1 where deptno =10;

mysql> delete from dept1;
Query OK, 6 rows affected (0.01 sec)
mysql> delete from dept1;
Query OK, 6 rows affected (0.01 sec)

怎么删除大表
Truncate table emp1; (重点)

048 关于表结构的修改
DQL(select) DML(insert delete update) DDL(create drop alter)
Navicat for mysql(工具表结构有问题 ----很少----)

对于表结构的修改,这里不讲了,大家使用工具完成即可,因为在实际开发种
表一旦设计好之后,对表结构的修改是很少的,修改表机构就是对之前设计的否定,即使需要修改表结构;修改表结构的语句不会出现在Java代码当中
增删改查y有一个术语：CRUD操作
Create(增) retrieve（检索) update(修改) delete(修改)

Insert delete

049任务54：049 约束作用及常见约束.
Constraint
什么是约束？常见的约束有哪些呢?
表格
Id username(唯一性约束) password(非空约束)

在创建表的时候, 可以给表的字段添加相应的约束, 添加约束的目的是为了
保证表中数据的合法性, 有效性，完整性

常见的约束有哪些呢？
非空约束（not null):约束字段不能为null
唯一约束（unique）：不能重复
主键约束(primary key): 约束的字段即不能为NULL,也不能重复（简称pk）

外键约束(foreign key)（简称PK）检查约束(check):注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束

050 非空约束
Drop table if exists t_user;
Create table t_user(
Id int,
Username varchar(255) not null,
Password varchar(255));

mysql> drop table if exists t_user;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t_user(id, password) values(1,‘123’);
ERROR 1364 (HY000): Field ‘username’ doesn’t have a default value

mysql> insert into t_user(id,username,password) values(1,‘lisi’,‘123’);
Query OK, 1 row affected (0.00 sec)

Insert into t_user(id, username, password) values(1,’lisi’,’123’);
Insert into t_user(id,name, password) values(1,’lisi’,’123’);
Insert into t_user(id, name, password) values(1,’lisi’,’123’);
任务56：051 回顾之前内容
关于查询去重
mysql> select distinct deptno,job from emp;
±-------±----------+
| deptno | job |
±-------±----------+
| 20 | CLERK |
| 30 | SALESMAN |
| 20 | MANAGER |
| 30 | MANAGER |
| 10 | MANAGER |
| 20 | ANALYST |
| 10 | PRESIDENT |
| 30 | CLERK |
| 10 | CLERK |
±-------±----------+
9 rows in set (0.01 sec)

Select count(distinct job) from emp;

mysql> Select count(distinct job) from emp;
±--------------------+
| count(distinct job) |
±--------------------+
| 5 |
±--------------------+
1 row in set (0.00 sec)

2.连接查询
子查询可以出现在哪里?
什么是子查询?子查询都可以出现在哪里
Select 语句当中嵌套select语句,被嵌套的select语句是子查询
Select …(select),
From …(select),
Where … (select).
3.Where 子句中使用子查询
Select * from emp where sal>(select avg(sal) from emp);

4.From 后面嵌套子查询
第一步：找出每个部门平均薪水（按照部门编号分组,求sal的平均值)
Select deptno, avg(sal) as avgsal from emp group by deptno;
第二步：
将以上的查询结果当作临时表t，让t表和salgrade s表连接，条件是:
t.avgsal between s.losal and s.hisal .
Salgrade s表连接,条件是 t.avgsal between s.local and s.hisal
Select t.*, s.grade
From (select deptno, avg(sal) as avgsal from emp group by deptno) t
Join salgrade s
On t.avgsal between s.losal and s.hisal;

找出每个员工平均的薪水等级
第一步找出每个员工的薪水等级
Select e.ename, e.sal,e.deptno, s.grade from emp e join salgrade s on
E.sal between s.losal and s.hisal;
±-------±--------±-------±------+
| ename | sal | deptno | grade |
±-------±--------±-------±------+
| SMITH | 800.00 | 20 | 1 |
| ALLEN | 1600.00 | 30 | 3 |
| WARD | 1250.00 | 30 | 2 |
| JONES | 2975.00 | 20 | 4 |
| MARTIN | 1250.00 | 30 | 2 |
| BLAKE | 2850.00 | 30 | 4 |
| CLARK | 2450.00 | 10 | 4 |
| SCOTT | 3000.00 | 20 | 4 |
| KING | 5000.00 | 10 | 5 |
| TURNER | 1500.00 | 30 | 3 |
| ADAMS | 1100.00 | 20 | 1 |
| JAMES | 950.00 | 30 | 1 |
| FORD | 3000.00 | 20 | 4 |
| MILLER | 1300.00 | 10 | 2 |
±-------±--------±-------±------+
14 rows in set (0.00 sec)
第二步
将以上的查询结果当作临时表t,让t表和salgrade s表连接,条件是:
t.avgsal between s.losal and s.hisal

Select t., s.grade
From (select deptno,avg(sal) as avgsal from emp group by deptno) t
Join
Salgrade s
On t.avgsal between s.losal and s.hisal;
mysql> Select t., s.grade
-> From (select deptno,avg(sal) as avgsal from emp group by deptno) t
-> Join
-> Salgrade s
-> On t.avgsal between s.losal and s.hisal;
±-------±------------±------+
| deptno | avgsal | grade |
±-------±------------±------+
| 10 | 2916.666667 | 4 |
| 20 | 2175.000000 | 4 |
| 30 | 1566.666667 | 3 |
±-------±------------±------+
3 rows in set (0.00 sec)

Limit(重点中的重点，以后分页查询全靠它了)
5.1
Limit 是mysql特有的,其他的数据库中没有，不通用(oracle相同机制叫 rownum)
Limit取结果集中的部分数据这时它的作用
语法机制
Limit startIndex,length
startIndex表示起始位置，从0开始，0表示第一条数据
length表示取几个
案例:
取出工资前5名的员工(思路,降序取前5个)
±-------±--------+
| ename | sal |
±-------±--------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
| WARD | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
±-------±--------+
14 rows in set (0.00 sec)取前5个:
mysql> select ename, sal from emp order by sal desc limit 0,5;
±------±--------+
| ename | sal |
±------±--------+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
±------±--------+
5 rows in set (0.00 sec)
Select ename, sal from emp order by sal desc limit 5;
mysql> select ename, sal from emp order by sal desc limit 5;
±------±--------+
| ename | sal |
±------±--------+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
±------±--------+
5 rows in set (0.00 sec)

5.4 limit 是sql 语句最后执行的一个环节
Select … from …;

5.5案例:找出工资排名在第4到第9名的员工?
mysql> select ename,sal from emp order by sal desc limit 3,6;
±-------±--------+
| ename | sal |
±-------±--------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
±-------±--------+
6 rows in set (0.00 sec)
通用的标准分页sql?
每页显示3条记录:
第一页:0,3
第二页:3,3;
第3页:6,3;
第4页:9,3
第5页：12，3
每页显示pagesize 条记录: 第pageNo 页: (pageNo -1) * pagesize, pagesize
Pagesize 是什么？是每页显示多少条记录
PageNo是什么？显示第几页

Java代码{
Int pageNo=2; //页码是2
Int pagesize =10； //每页显示10条
Limit （pageNo-1)* pagesize, pagesize
}
5.创建表
建表语句的语法格式:
Create table 表名(
Create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,
…
);
关于Mysql 当中字段的数据类型？以下只说常见的
Int 整数型(java中的int）
Bigint 长整型(java 中的long）
Float 浮点型(java 中 float double)
Char 定长字符串（string)
Varchar 可变长字符串(StringBuffer/StringBuilder)
Date 日期类型 (对应Java中的java,sql,date类型)
BLOB 二进制大对象( object 图
Clob 字符大对象（存储较大文本，4g字符串)

Char 和 varchar怎么选择？
实际开发中,当某个字段中的数据长度步发生改变的时候，定长的，例如:性别
生日等都是采用char
数据长度不确定-----简介，姓名等都是采用Varchar.

BLOB和 CLOB 类型的使用?
电影表 t_movie
Id(int) name(varchar) playtime(date/char) haibao(BLOB) history（CLOB)
1.蜘蛛侠
2.Create table t_student(
No bigint,
Name varchar(255),
Sex char(1),
Classno varchar(255),
Birth char(10)
};

6.Insert 语句插入数据
语句格式:
Insert into 表名（字段名1，字段名2，字段3， …) values(值1,值2,值3 …)
要求:字段的数据和值的数量相同,并且数据类型要对应相同
Insert into t_student(no, name, sex, classno, birth) values(1,’zhangsan’,’1’,’gaosan1ban’);
Insert into t_student(no, name, sex, classno, birth) values(1,’zhangsan’,’1’,’gaosan1ban’,’1950-10-12’);

删除数据?
语句格式:
Delete from 表名 where 条件;
注意:没有条件的全删除

删除所有记录?
Delete from dept1;
怎么删除大表中的数据?（重点)
Truncate table 表名; //表被截断，不可回滚，永久丢失
删除表
Drop table表名;// 这个通用
Drop table if exists 表名; //oracle 不支持这种写法

对于表结构的修改，大家使用工具完成即可
Ddl语句(create drop alter)

ddl12131

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
sql 增删改合表操作

Day4Task46 Union的用法Union(可以将查询结果集相加)案例:找出工作岗位是salesman和manager的员工第一种:Select ename,job from emp where job=’Manager’ or job =’salesman’;第二种:mysql> select ename,job from emp where job = ‘Manager’ or job = ‘Salesman’;±-------±---------+| ename | j
复制链接

扫一扫

sql 增删改 合表操作

sql 增删改合表操作