Mysql数据库学习(三):表的crud操作、完整性约束、select各种查询

一、表的crud操作

指增加(Create)、查询(Retrieve)(重新得到数据)、更新(Update)和删除(Delete)

// select 查询后面再讲

create table tb_test2 select * from db_test.tb_test; 
// insert into .. select      
create table t_emp(empno int, ename varchar(20), esex char(2));

alter table t_emp modify ename varchar(30);

alter table t_emp drop esex;

alter table t_emp add esex char(2);

insert into t_emp(empno, ename, esex) values(1000, 'tom', 'm');

insert into t_emp values(1000, 'maggie', 'f');  /* 还没设置主键,故empno可以相同 */

insert into t_emp(empno, ename) values(1002, 'john');

insert into t_emp(empno, ename, esex) values(1003, null, 'm');

insert into t_emp values(1004, '张三', '男');

show variables like 'character_set%';   /* 查看字符集设置 */
set character_set_database=utf8;    /* 也可设置配置文件 */

set names gbk;  /* 支持插入中文 */

update t_emp set empno=1001 where ename='maggie';

delete from t_emp where esex is null;

delete from t_emp; /* 表结构还在 */  truncate table t_emp;//
 比较快

drop table t_emp; /* 整表删除 */

二、完整性约束

表完整性约束

主键 (constraint) 外键 (constraint) 用户自定义完整性约束 (check)

create table t_emp(empno int not null primary key, ename varchar(20), esex char(2)); /* 创建时设置主键*/

create table t_emp(empno int, ename varchar(20), esex char(2), primary key (empno));

create table t_emp(empno int, ename varchar(20), esex char(2), constraint PK_EMPNO primary key(empno)); /* 设置主键*/

create table t_emp(empno int, ename varchar(20), esex char(2));

alter table t_emp add constraint PK_EMPNO primary key(empno);   /* 这种方式也可以设置主键 */

insert into t_emp values(1000, 'john', 'm');

insert into t_emp values(1000, 'lily', 'f');    /* error,empno不能相等 */

insert into t_emp values(null, 'lily', 'f');    /* error,主键不能为空 */



create table t_emp(empno int, deptno int, ename varchar(20), esex char(2));

alter table t_emp add constraint PK_EMPNO primary key(empno);

create table t_dept(deptno int, dname varchar(20));

alter table t_dept add constraint PK_DEPTNO primary key(deptno);

alter table t_emp add constraint FK_DEPTNO foreign key(deptno) references t_dept(deptno); /*设置t_emp 的外键为t_dept 的主键 */

set names gbk;
insert into t_dept values(2001, '人事部');
insert into t_dept values(2002, '技术部');

insert into t_emp values(1001, 2001, 'john', 'm');

insert into t_emp values(1003, 2003, 'john', 'm');

create table t_test1(id int auto_increment primary key, name varchar(30), age int default 20);

insert into t_test1 values(null, 'aaa');

insert into t_test1 values(null, 'aaa', null);

insert into t_test1 (name) values( 'bbb');

create table t_test2(id int, name varchar(30), age int);
alter table t_test2 add constraint CC_AGE check (age >=18 and age<=60); /* 实际上现在mysql不支持check限制 */

alter table t_test2 add constraint CC_AGE check (length(name)>2);

增加/删除unique key

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
ALTER TABLE Persons
DROP INDEX uc_PersonID

此时insert ignore 如果插入的数据中有重复的primary key or unique 索引,则忽略不插入

mysql 中常用的四种插入数据的语句: 

insert into 表示插入数据,数据库会检查主键,如果出现重复会报错; 

replace into 表示插入替换数据,需求表中有Primary Key,或者unique索引,如果数据库已经存在数据,则用新数据替换,如果没有数据效果则和

insert into 一样; 

insert ignore 表示,如果中已经存在完全相同的记录,或者primary key/ unique 索引冲突, 则忽略当前新数据,但不会出现错误

insert into ... ON DUPLICATE KEY UPDATE 如果插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE

MySQL MyIsAm 存储引擎在创建索引的时候,索引键长度是有一个较为严格的长度限制的,索引键最大长度总和不能超过1000(注意:utf8 为 3bytes),innodb 引擎则不受限制。查询系统是否支持 innodb,可以 执行如下命令: SHOW variables like "have_%" 显示结果中会有如下3种可能的结果: have_innodb YES have_innodb NO have_innodb DISABLED 这 3 种结果分别对应: 已经开启 InnoDB 引擎 未安装 InnoDB 引擎 未启用 InnoDB 引擎 针对第二种未安装,只需要安装即可;针对第三种未启用,则打开mysql 配置文件,找到 skip-innodb 项,将其改成 #skip-innodb,之后重启 mysql 服务即可。

三、select 查询

练习前先导入数据:

create database scott;

use  scott;

source  C:\scott.sql   scott.sql 点这下载

或者 mysql -uxxx -pxxx  scott < scott.sql

1.select 单表查询:

select empno,ename,job from emp;
select * from emp;

SELECT empno as '工号',ename '姓名' FROM emp; /* as 后面是别名 */
SELECT empno, '暨南大学' FROM emp; /* 常量列查询 */
SELECT empno, concat(ename,'#') FROM emp; /* concat连接 */
SELECT empno, ename||'#' FROM emp; /* oracle可以用||作为连接符 */

SELECT empno, ename, job FROM emp WHERE ename = 'SMITH'
SELECT empno, ename, job FROM emp WHERE ename <> 'SMITH' /* 也可以使用!= */
SELECT empno, ename, sal FROM emp WHERE sal>= 1500
SELECT * FROM emp WHERE deptno=30 and sal>1500; /*  and */
SELECT * FROM emp WHERE job='MANAGER' or job='SALESMAN' /* or */
SELECT * FROM emp where sal BETWEEN 800 and 1500;
SELECT * FROM emp where sal >= 800 and sal <= 1500;
SELECT empno, ename, sal, comm FROM emp WHERE comm is null 
SELECT empno, ename, sal, comm FROM emp WHERE comm is not null /* not */

SELECT * FROM emp where sal not BETWEEN 800 and 1500; /* between */
SELECT * FROM emp where ename in ('SMITH', 'KING'); /* in */
SELECT * FROM emp where ename like 'S%'; /* 模糊查询 通配符: ‘%’(0个多个字符); 通配符: ‘_’ (单个字符) */
SELECT * FROM emp where ename like 'S_ITH';

SELECT * FROM emp ORDER BY ename desc;  /* order by 默认是升序 asc */
SELECT empno, ename, job FROM emp ORDER BY 2 desc;
SELECT * FROM emp ORDER BY job asc, sal desc;

如果我就真的要查%或者_,怎么办呢?使用escape,转义字符后面的%或_就不作为通配符了,注意前面没有转义字符的%和_仍然起通配符作用 select username from gg_user where username like '%xiao/_%' escape '/'; select username from gg_user where username like '%xiao/%%' escape '/';

在like 里面为了查找   “\”,必须指定它为   “\\\\” 。

select * from emp ORDER BY sal limit 5; /*limit可用于分页查询*/
select * from emp ORDER BY sal limit 0,5; /* 0表示offet, 5表示从0开始的5条记录*/
select * from emp ORDER BY sal limit 5,5;
select * from emp ORDER BY sal limit 10,5;

select job,deptno from emp;
select all job,deptno from emp; /* 默认是all */
select distinct job,deptno from emp; /* 去除重复记录 */


select * from dept where deptno in (SELECT DISTINCT deptno from emp); /* 查询有员工的部门信息 */

/* UNION (无重复并集):当执行UNION 时,自动去掉结果集中的重复行,并以第一列的结果进行升序排序。*/
select empno,ename,job from emp where job='SALESMAN'
union /* union即联合查询 */
select empno,ename,job from emp where job='MANAGER';

select empno,ename,job from emp where job='SALESMAN' or job='MANAGER' /* 比较结果 */

/* UNION ALL (有重复并集):不去掉重复行,并且不对结果集进行排序。*/
select job, sal from emp where empno=7902
union all
select job, sal from emp where empno=7788;

select job, sal from emp where empno=7902
union
select job, sal from emp where empno=7788;

2.select 多表查询:

多表查询

交叉连接 内连接 自身连接 外连接

左外连接 右外连接 全连接

自然连接

交叉连接是不带WHERE子句的多表查询,它返回被连接的两个表所有数据行的笛卡尔积。返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。

内连接(等值连接):在连接条件中使用等于号(=)运算符比较被连接列的列值,其查询结果中列出被连接表中的所有列,包括其中的重复列。

内连接(不等连接):在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>

内连接(自身连接)

外连接(左连接):返回包括左表中的所有记录和右表中联结字段相等的记录;即左外连接就是在等值连接的基础上加上主表中的未匹配数据(被连接

表字段为 NULL)。

外连接(右连接):返回包括右表中的所有记录和左表中联结字段相等的记录;即右外连接是在等值连接的基础上加上被连接表的不匹配数据(连接表字段为 NULL)。

外连接(全连接):全外连接是在等值连接的基础上将左表和右表的未匹配数据都加上。mysql 不支持 full outer join。

自然连接:在连接条件中使用等于(=)运算符比较被连接列的列值,但它使用选择列表指出查询结果集合中所包括的列,并删除连接表中的重复列。

select * from emp,dept /*交叉连接 */

SELECT * FROM emp INNER JOIN dept ON emp.deptno = dept.deptno; /*内连接(等值连接) */
select * from emp,dept where emp.deptno=dept.deptno;

select * from emp INNER JOIN dept on emp.deptno > dept.deptno; /* 内连接(不等连接)*/
select * from emp,dept where emp.deptno > dept.deptno;

select A.ename 员工, B.ename 领导  from emp A, emp B where A.mgr = B.empno; /*内连接(自身连接) */

SELECT * FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;
select * from emp left join dept on emp.deptno=dept.deptno /*外连接(左连接) */


/* scott.sql并未设置emp表的外键为deptno,故这里可以插入在dept表中不存在的deptno值*/
/* 主要是为了演示左连接和右连接的区别 */
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (9999, 'XXXX', 'CLERK', 7782, '1982-01-23', 1300, null, 90);

select * from emp right outer join dept on emp.deptno=dept.deptno /* 外连接(右连接) */

select * from emp left join dept on emp.deptno=dept.deptno

/* 自然连接会合并deptno一项,而外连接不会 */
SELECT * FROM emp NATURAL JOIN dept;

SELECT * FROM emp NATURAL LEFT JOIN dept;

SELECT * FROM emp NATURAL RIGHT JOIN dept;

3.子查询/any/all./exists

子查询即一个查询语句嵌到另一个查询语句的子句中;可以出现在另一个查询的列中,where子句中,from子句中等。

<any,小于子查询中的某个值。等价于<max

>any,大于子查询中的某个值。等价于>min

>all,大于子查询中的所有值。等价于>max

<all,小于子查询中的所有值。等价于<min

exists 存在性条件判断: 若内层查询非空,则外层的where子句返回真值,否则返回假。not exists相反。

/* 查询员工及其领导名称 */
select A.ename 员工, B.ename 领导  from emp A, emp B where A.mgr = B.empno;

/* 子查询,同上 */
select ename 员工, (select ename from emp where empno = e.mgr) 领导
from emp e;

/* 列出所有“CLERK”(办事员)的姓名及其部门名称 */
select ename, dname from emp,dept where job='CLERK' and emp.deptno = dept.deptno;

/* 子查询,同上 */
select ename, (select dname from dept where deptno=e.deptno) dname
from emp e where job='CLERK' and deptno in (select deptno from dept);

/* 子查询,同上 */
select ename, dname
from
(select ename, (select dname from dept where deptno=e.deptno) dname
from emp e where job='CLERK') a
where dname is not null;

/* 子查询,多出deptno=90的行 */
select ename, (select dname from dept where deptno=e.deptno) dname
from emp e where job='CLERK';
/* 同上 */
select ename, dname from emp  LEFT JOIN dept on emp.deptno = dept.deptno where job='CLERK';



/* 列出薪金比'SMITH'高的员工*/
select * from emp where sal > (select sal from emp where ename='SMITH');
/* 列出受雇日期早于其直接上级的所有员工*/
select * from emp e where hiredate < (select hiredate from emp where empno=e.mgr);


/* 查询薪金小于销售员某个员工的员工信息*/
select * from emp WHERE
sal < any (select sal from emp where job='SALESMAN');

select * from emp WHERE
sal < (select max(sal) from emp where job='SALESMAN');

/* 查询薪金大于所有销售员的员工信息 */
select * from emp WHERE
sal > all (select sal from emp where job='SALESMAN');

/* 列出与“SCOTT”从事相同工作的所有员工 */
select * from emp e where EXISTS
(
select * from emp where ename='SCOTT' and e.job = job
);

select * from emp where job =(select job from emp where ename='SCOTT');

4.聚合函数/group by/having/group by与子查询

聚合函数一般用于统计,常用如下:

count(field)  //记录数 avg(field)     //平均值 min(field)     //最小值 max(field)    //最大值 sum(field)    //总和

group by/having:分组查询通常用于统计,一般和聚合函数配合使用

select 分组字段或聚合函数 from 表  group by 分组字段 having 条件  order by 字段

select count(comm) as 记录数 from emp; /* 非 NULL 则计数 */
select count(*) as 记录数 from emp;
select count(0) as 记录数 from emp;
select count(empno) as 记录数 from emp;

select avg(sal) as 平均薪金,
max(sal) as 最高薪金,
min(sal) as 最低薪金,
sum(sal) as 薪金总和
from emp;

/* 列出各部门各有多少人 */
select deptno, count(*) from emp group by deptno;
/* 列出各部门人数大于3的 并按人数降序排列 */
select deptno, count(*) cn from emp group by deptno HAVING cn > 3 ORDER BY cn desc;

/* 列出在dept表中出现过的部门各有多少人 */
select *, (select count(*) from emp group by deptno HAVING deptno = dept.deptno) total 
from dept;

/* 如果人数为NULL 赋值为 0 */
select *, ifnull((select count(*) from emp group by deptno HAVING deptno = dept.deptno), 0) total 
from dept;

/* 查询出薪金成本最高的部门的部门号和部门名称 */
select dept.deptno, dept.dname
from dept, emp
where dept.deptno=emp.deptno
group by dept.deptno, dept.dname
HAVING sum(sal) >= all (select sum(sal) from emp group by deptno);


select dept.deptno, dept.dname
from dept, emp
where dept.deptno=emp.deptno
group by dept.deptno, dept.dname
HAVING sum(sal) >= (
select max(t.total)
from
(select sum(sal) total from emp group by deptno) t
);

select url, get_keys, post_keys, count(distinct url, get_keys, post_keys) from sec_tmp_after_task1_step10 group by url, get_keys, post_keys

select url, get_keys, post_keys, wm_concat(distinct url_source, ",", "asc") as url_source_all from sec_tmp_luascan_task_step1 group by url, get_keys, post_keys

5.MySQL函数

控制流程函数 字符串函数 数值函数 日期和时间函数

now date_add/adddate datediff date_format

聚合函数

CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END

CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END 

SELECT CASE 1 WHEN 1 THEN 'one' 
WHEN 2 THEN 'two' ELSE 'more' END;

SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;

select  a.cgi from ( select case when port=443 then concat('https://', url) else concat('http://', url) as cgi from t_url) a;

/* 查询员工的薪金等级 */
select ename 员工, sal 薪金, case grade when 1 then '一级'
when 2 then '二级'
when 3 then '三级'
when 4 then '四级'
when 5 then '五级'
end 等级
from emp, salgrade
where sal between losal and hisal;

SELECT ASCII('2a');
SELECT ASCII('a2');
SELECT BIN(12);
SELECT BIT_LENGTH('text');

SELECT CHAR(77,121,83,81,'76');
SELECT CHAR(77,121,83,81,76);

SELECT 3+5;
SELECT 3/5;

SELECT ABS(-32);

select now();

SELECT DATE_ADD('1998-02-02', INTERVAL 31 DAY);
SELECT DATE_ADD('1998-02-02', INTERVAL 28 DAY);

SELECT adddate('1998-02-02', INTERVAL 28 DAY);

SELECT adddate('1998-02-02', 28);

select DATEDIFF(now(),'2014-02-01');
select DATEDIFF('2014-02-01','2014-03-01');


select DATE_FORMAT(now(), '%H:%i:%s');

select DATE_FORMAT(now(), '%Y%M%D');

select DATE_FORMAT(now(), '%Y%m%d');

工作中常用的时间函数还有 time_to_sec, date_format, str_to_date, addtime,  timestampdiff 等,注意 NULL 数据做什么运算结果都是NULL,不为真,为此可以用 ifnull(exp1, exp2) 指定默认值。

参考: 《数据库系统概论》 mysql 5.1 参考手册

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

@@identity , SCOPE_IDENTITY,IDENT_CURRENT 三者的异同

--===================================================== --@@identity , SCOPE_ID...

422
来自专栏AhDung

【SQL】找出行数与自增标识值不相等的表(即有缺行)

原理:遍历所有含自增列的用户表,用sp_spaceused过程分别获取每张表的行数并写入临时表,然后使用IDENT_CURRENT函数获取表的最大标识值,比较二...

942
来自专栏开发技术

cassandra高级操作之索引、排序以及分页

  本次就给大家讲讲cassandra的高级操作:索引、排序和分页;处于性能的考虑,cassandra对这些支持都比较简单,所以我们不能希望cassandra完...

962
来自专栏钟绍威的专栏

当子查询碰上NULLUNIONJOIN总结

情景: 现在有如图两个表,boy和girl,对应着Boy和Girl两个bean,有共同字段id、name,另外boy还有个外键grilfriend(指向girl...

1707
来自专栏个人随笔

MySQL基本语句

创建表 如果是关键字  那么使用反引号  `` esc下面的键 create  table  [if  not  exists] 表名(    字段1  数据类...

3567
来自专栏编程心路

写给新手的Mysql入门指南(一)

关键字最好大写,这样便于阅读。可以用windows的cmd运行工具对数据库操作,前提是mysql的安装目录的子目录bin的路径添加导论系统变量PATH中,mys...

823
来自专栏Hadoop数据仓库

Oracle 中的树查询和 connect by

Oracle 中的树查询和 connect by 使用 connect by 和 start with 来建立类似于树的报表并不难,只要遵循以下基本原则即可:...

1727
来自专栏C# 编程

1 - SQL Server 2008 之 使用SQL语句创建具有约束条件的表

约束条件分为以下几种: 1)非空约束,使用NOT NULL关键字; 2)默认值约束,使用DEFAULT关键字; 3)检查约束,使用CHECK关键字; 4)唯一约...

1860
来自专栏乐沙弥的世界

dbms_xplan之display函数的使用

DBMS_XPLAN包包括一系列函数,主要是用于显示SQL语句的执行计划,且不同的情形下使用不同的函数来显示,如预估的执行计划则使用 displ...

892
来自专栏向治洪

百度地图之收索视野内的建筑物

根据用户移动地图的位置,显示在视野范围内的建筑物,简单的思路是,添加地图监听,当地图移动结束之后,计算出当前屏幕四个角的GeoPoint,根据这4个点,通过my...

1649

扫码关注云+社区