MySQL40-51:union/limit/表的增删查改

高开低走。

于 2021-03-11 21:40:00 发布

阅读量109

点赞数

分类专栏：锦到说MySQL

本文链接：https://blog.csdn.net/qq_43568982/article/details/114647497

版权

锦到说MySQL 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

开始时间：2021-03-10

union

找出工作岗位是salesman和manager的员工

mysql> select * from emp e where job='salesman' or job='manager';
//两组代码等价
mysql> select * from emp e where job in('salesman','manager');
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  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 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+

用union的方式求并集

mysql> select * from emp e where job='salesman' union select * from emp e where job='manager';
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7698 | BLAKE  | MANAGER  | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER  | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
+-------+--------+----------+------+------------+---------+---------+--------+

union的好处在于可以拼接两张不相关的表格

limit

limit是mysql特有的，其他数据库不通用
limit startindex,length

案例：取出工资排名前五的员工

mysql> select ename,sal from emp order by sal desc limit 0,5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

找出排名是4到9的员工

mysql> select ename ,sal from emp order by sal limit 3,6;//6指的是6个数据
+--------+---------+
| ename  | sal     |
+--------+---------+
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
+--------+---------+

分页

limit pageSize*(pageNo-1),pageSize
pageNo 第几页
pageSize 每页显示条数

创建表

建表语句的语法格式:
create tabie表名(
字段名1数据类型,
字段名2数据类型,
字段名3数据类型,
) ;

关于MysQL当中字段的数据类型?以下只说常见的

int	整数型(ava中的int)
bigint	长整型(java中的1ong)
f1oat	浮点型(ava中的f1oat double)char定长字符串(string)
varchar	可变长字符串(stringBuffer/stringBuilder)
date	日期类型(对应Java中的java.sq1.Date类型)
BLOB	二进制大对象（存储图片、视频等流媒体信息）Binary Large 0Bject（对应java中的object)
CLOB	字符大对象（存储较大文本，比如，可以存储4c的字符串。) character Large oBject(对应java中的object)

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

BLOB和CLOB类型的使用?
电影表:t movie

id(int)	name (varchar)	playtime (date/ char)	poster(BLOB)	story (CLOB)
1	蜘蛛侠
2
3

表名在数据库当中一般建议以:t_或者tb1_开始。

创建学生表

create table t_student (
no bigint,
name varchar(255),
sex char(1),
classno varchar (255),
birth char (10)
);

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| dept                      |
| emp                       |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| salgrade                  |
| servers                   |
| slow_log                  |
| t_student                 |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+

mysql> desc t_student;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| no      | bigint(20)   | YES  |     | NULL    |       |
| name    | varchar(255) | YES  |     | NULL    |       |
| sex     | char(1)      | YES  |     | NULL    |       |
| classno | varchar(255) | YES  |     | NULL    |       |
| birth   | char(10)     | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

插入数据

mysql> insert into t_student(no,name,sex,classno,birth)values(5,'lisi',1,'grade 3','2000-01-01');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_student;
+------+------+------+---------+------------+
| no   | name | sex  | classno | birth      |
+------+------+------+---------+------------+
|    5 | lisi | 1    | grade 3 | 2000-01-01 |
+------+------+------+---------+------------+

values(…),(…)可以插多行数据

删库

mysql> drop table if exists t_student;
Query OK, 0 rows affected (0.02 sec)

建表的时候可以提供默认值

create table t_student (
no bigint,
name varchar(255),
sex char(1) default 1,
classno varchar (255),
birth char (10)
);

mysql> desc t_student;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| no      | bigint(20)   | YES  |     | NULL    |       |
| name    | varchar(255) | YES  |     | NULL    |       |
| sex     | char(1)      | YES  |     | 1       |       |
| classno | varchar(255) | YES  |     | NULL    |       |
| birth   | char(10)     | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

表的复制

mysql> create table emp2 as select ename,job from emp;
Query OK, 14 rows affected (0.02 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> select * from emp2;
+--------+-----------+
| ename  | job       |
+--------+-----------+
| SMITH  | CLERK     |
| ALLEN  | SALESMAN  |
| WARD   | SALESMAN  |
| JONES  | MANAGER   |
| MARTIN | SALESMAN  |
| BLAKE  | MANAGER   |
| CLARK  | MANAGER   |
| SCOTT  | ANALYST   |
| KING   | PRESIDENT |
| TURNER | SALESMAN  |
| ADAMS  | CLERK     |
| JAMES  | CLERK     |
| FORD   | ANALYST   |
| MILLER | CLERK     |
+--------+-----------+

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+


mysql> create table mydept
    -> (deptno int,
    -> dname varchar(20),
    -> loc varchar(20)
    -> );

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

mysql> select * from mydept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

修改数据 update

update 表名 set 字段名1=值1,字段名2=值2… where 条件;
注意：字段之间用逗号隔开，不能用and
如果没有条件的话，整张表都会更新

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> update dept set loc='beijing' where dname='sales';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | beijing  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

删除数据

delete from 表名 where条件;
没有条件就全部删除

mysql> delete from dept where loc='beijing';
Query OK, 1 row affected (0.01 sec)

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     40 | OPERATIONS | BOSTON   |

删大表，发大招，删了找不回来那种，很快的。

truncate table XX;

mysql> truncate table emp2;
Query OK, 0 rows affected (0.01 sec)

修改表的结构

一般是通过可视化工具操作
不会写代码直接改结构
增删改查CRUD操作(create,retrieve查,update,delete)

约束constraint

创建表的时候，给表的字段添加相应的约束，目的是保证表中数据的合法性、有效性、完整性。

非空约束(not null)	约束的字段不能为NULL
唯一约束(unique)	约束的字段不能重复
主键约束(primary key)	约束的字段既不能为NULL，也不能重复（简称PK）
外键约束(foreign key)	…简称（FK）
检查约束(check)	注意oracle数据库有check约束，但是mysq1没有，目前mysql不支持该约束。

非空约束 not null

create

//创建新表
mysql> create table t_student 
(no bigint,name varchar(255),sex char(1) not null,birth char (10));
Query OK, 0 rows affected (0.01 sec)

//null字段变为了no，要求性别必须为非空
mysql> desc t_student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| no    | bigint(20)   | YES  |     | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
| sex   | char(1)      | NO   |     | NULL    |       |
| birth | char(10)     | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
//插入带sex的数据就ok了
mysql> insert into t_student(no,name,sex,birth)values(5,'lisi',1,'2000-01-01');

//插入不带sex 的数据会报错
mysql> insert into t_student(no,name,birth)values(5,'lisi','2000-01-01');
ERROR 1364 (HY000): Field 'sex' doesn't have a default value

Query OK, 1 row affected (0.01 sec)

mysql> select * from t_student;
+------+------+-----+------------+
| no   | name | sex | birth      |
+------+------+-----+------------+
|    5 | lisi | 1   | 2000-01-01 |
+------+------+-----+------------+
1 row in set (0.00 sec)

结束时间：2021-03-11

高开低走。

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL40-51:union/limit/表的增删查改

开始时间：2021-03-10union找出工作岗位是salesman和manager的员工mysql> select * from emp e where job='salesman' or job='manager';//两组代码等价mysql> select * from emp e where job in('salesman','manager');+-------+--------+----------+------+------------+---------+----
复制链接

扫一扫

专栏目录