mysql

来源：互联网发布：荣威i6 知乎编辑：程序博客网时间：2024/06/07 06:11

1 introduction

mysql是一个关系型数据库管理系统，由瑞典mysql ab 公司开发，目前属于 oracle 旗下产品。关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。在业界，由于mysql的开源特性，与操作系统linux，服务器apache或者nginx，脚本解释器php/perl/python，并称为“lamp”或者“lnmp”组合。

mysql使用sql语言访问数据库。结构化查询语言(structured querylanguage)简称sql，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。结构化查询语言包含6个部分：一：数据查询语言（dql:data querylanguage）：其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字select是dql（也是所有sql）用得最多的动词，其他dql常用的保留字有where，order by，group by和having。这些dql保留字常与其他类型的sql语句一起使用。二：数据操作语言（dml：data manipulation language）：其语句包括动词insert，update和delete。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。三：事务处理语言（tpl）：它的语句能确保被dml语句影响的表的所有行及时得以更新。tpl语句包括begin transaction，commit和rollback。四：数据控制语言（dcl）：它的语句通过grant或revoke获得许可，确定单个用户和用户组对数据库对象的访问。某些rdbms可用grant或revoke控制对表单个列的访问。五：数据定义语言（ddl）：其语句包括动词create和drop。在数据库中创建新表或删除表（creat table 或 drop table）；为表加入索引等。ddl包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。六：指针控制语言（ccl）：它的语句，像declare cursor，fetch into和update where current用于对一个或多个表单独行的操作。（来源自百度百科）

2 数据库操作

1) 创建数据库

createdatabase shujuku;

createdatabase zifuji character set utf8;

showdatabases;

showcreate database zifuji;

2) 删除数据库

dropdatabase shujuku;

3) 切换数据库

usedatabase zifuji;

4) 修改数据库编码集

alterdatabase shujuku character set utf8;

5) 查看正在使用的数据库

selectdatabase();

3 数据库数据类型

1) 字符型

varchar char

都用来存储字符串长度小于255的字符。

char(20)===只能存长度为20的数据，哪怕不存任何的数据 20的空间还是被占用。varchar(200)===只能存取200的数据，但是，如果只存20,剩下的空间会自动释放可变字符型

2) 文本型

text

使用文本型数据，你可以存放超过二十亿个字符的字符串。当你需要存储大串的字符时，应该使用文本型数据。一个文本型字段中的数据通常要么为空，要么很大。文本型字段既大且慢，滥用文本型字段会使服务器速度变慢。文本型字段还会吃掉大量的磁盘空间。

3) 数值型

整数 tinyint smallint int bigint

浮点数float double

数型numeric：numeric型数据使你能表示非常大的数——比int型数据要大得多， numeric型数据还能表示有小数部分的数。

钱型money smallmoney

money型数据可以存储从-922,337,203,685,477.5808到922,337,203,685,477.5807的钱数。

4) 逻辑型

bit：bit型字段只能取两个值：0或1。

5) 日期型

datetime smalldatetime

一个 datetime型的字段可以存储的日期范围是从1753年1月1日第一毫秒到9999年12月31日最后一毫秒。

4 表操作

1) 创建表

createtable newtable(

id int(20) primary key auto_increment,

name varchar(20) unique,

address varchar(50) not null

);

2) 修改表结构

增加列

altertable newtable age int(2) not null;

修改列名

altertable newtable change name username varchar(20) unique;

修改列的属性等

altertable newtable modify address varchar(20) unique;

删除列

altertable newtable drop sex;

修改表名

renametable newtable to oldtable;

显示当前库中所有的表

showtables;

修改表的字符集

altertable newtable character set utf8;

删除表

droptable newtable;

显示表的字符集编码

showcreate table newtable;

5 数据库注释

多行注释

- - 单行注释，可用于sqlinject

6 数据库字段操作

crud

create 增 read读 update 改 delete 删

1) 向数据表插入数据

insertinto newtable (id,name,address) values (1,’aa’,’bbb’);

或者

insertinto newtable values (null,’aa’,’bbb’),(null,’aa2’,’bb2’);

2) 更新数据

updatenewtable set name=’aa3’,address=’cc3’ where id=1;

3) 删除数据

deletefrom newtable where name=’aa3’;

deletefrom 表名;和truncate table 表名;区别？

delete:直接把表中的数据按照行删除。 truncate：它是先把表删除，然后再创建一张新的表。

truncate删除表比较快，但删除的数据不能恢复，不能rollback回滚，只能删除表中的所有数据，而delete删除比truncate慢，删除的数据可以恢复，可以rollback回滚，后面可以跟where条件删除某些指定记录。

drop tablestudent和deletefrom sdudent 的区别？

drop：数据被删除同时表也被删除。

delete：只是删除表中的数据，表还是存在。

4) 查询数据

①　查询数据库中所有的数据

select * from newtable;

②　查询表中列的记录

select id,name from newtable;

③　按条件查询

select name,address from newtablewhere id>3;

判断条件：

a.相等= 不等< >

b.区间between...and...

c.in(23,25,26) 相当于where age=23 or age=25 or age=26

d.模糊查询 like。where name like... %:表示任意字符 _:单个的任意字符

example：name like ‘张%’; namelike ‘%张%’; name like ‘张_’; name like ‘_张_’

e.is null 判断该列的记录是否为空

where name is null; where name is not null;

f. 逻辑运算符

and or not

④　对查询的结果进行排序

a.降序

select * from newtable order by ageasc;

b.升序

select * from newtable order by agedesc;

c.可变参数排序

select * from newtable order by agedesc,score asc;

排序可以应用于排行榜，查询最小值，最大值的操作。

⑤　给查询出来的列明取别名

select name as username from newtablewhere id=1;

//省略关键字 as

select name username from newtablewhere id=1;

question:下面两条sql的区别

select age score from student;

//这从student表中查询出age一列，并且将age起别名为score

select age,score from student;

//这是直接查询的两列数据

5) sql函数(聚合函数)

①　count() 统计记录的个数

select count(*) from newtable; select count(行数) from newtable;

②　sum() 求和

select sum(列名) from newtable;

select sum(score) from newtable; select sum(age)+sum(score) from newtable; select sum(ifnull(age,0) from newtable;

③　avg() 求平均数

select avg(age) from newtable;

④　max() min()

select max(age) from newtable; select min(age) from newtable;

⑤　group by 分组函数

create table orders(

idint primary key auto_increment,

productvarchar(20),

pricenumeric(18,2)

);

insert into orders values(null,'电视',5000);

insert into orders values(null,'洗衣机',300);

insert into orders values(null,'冰箱',1000);

insert into orders values(null,'洗衣粉',5);

insert into orders values(null,'桔子',2);

insert into orders values(null,'电视',5000);

insert into orders values(null,'洗衣粉',10);

select produnt,sum(price) from odersgroup by product;

同时出现需要group by 分组函数， order by 关键字，where或having 条件语句，或者出现其中两个时，它们的先后顺序是

group by>order by>where或者having

如果有聚合函数的话，先分组，再进行计算；select 后面跟着的是被分组的列，或者是函数；如果使用分组的话，再需要过滤（条件），这时一般不使用where而是使用having关键字；where 条件后面不可以跟聚合函数；having 后面是可以跟着聚合函数的。

⑥　过滤重复的数据 distinct

select distincename,age from newtable;

6) 表设计

外键约束

①　创建表时添加外键约束

foreign key (id) reference oldtable(id),

②　修改原表中的列为外键约束

alter table newtable add foreign key(id) references oldtable(id);

7) 多表查询

①　直接多张表一起查询，得到的是一组笛卡尔积

select * from newtable,oldtable;

②　内连接查询。在多表笛卡尔积的基础上，按照条件查询。

语法一：select a,b from newtable,oldtable wherenewtable.id=oldtable.id;

语法二：select a,b, from newtable inner join oldtableon newtable.id=oldtable.id;

③　外连接查询。

a. 左外连接

用左表的数据去连接右表数据去查询，不管在右表中是否得到记录，左表中的数据全部显示。如果没有记录，则用null来填充

select * from newtable left outer joinoldtable on newtable.name=oldtable.name;

outer可以省略。

select * from newtable outer joinoldtable on newtable.name=oldtable.name;

b. 右外连接

用右表的数据去连接左表数据去查询，不管在左表中是否得到记录，右表中的数据全部显示。如果没有记录，则用null来填充

select * from newtable right outerjoin oldtable on newtable.name=oldtable.name;

outer可以省略。

select * from newtable outer joinoldtable on newtable.name=oldtable.name;

c. 全外连接

select * from 表1 full outer join 表2 on 条件但是mysql不支持这个语法。在mysql中使用union将左外和右外连接到一起实现全连接，但是如果使用union 自动去除重复数据

select * from newtable left outer joinoldtable on newtable.name=oldtable.name union select * from newtable rightouter join oldtable on newtable.name=oldtable.name;

④　关联子查询

把一个sql的查询结果做为另一个sql的查询条件（参数）。

select * from newtable where id in(

select id from oldtable where age=(

select max(age) from oldtable

)

);

8) 其他

①　in

age in(23,24) age=2 or age=3;

select * from newtable where agein(select id from newtable where age>10);

②　exists

表示存在，当子查询的结果存在，就会显示主查询中的所有数据。

select* from student s where exists (select * from studentcourse sc wheres.`id`=sc.student_id and score<60);

③　any some

any和some是一样；any 或者some的用法 >any(1,2,3) 等价于>1 or >2 or>32 数据库操作联系

④　all

>all(1,2,3)等价于 >1 and >2 and >3

select* from student where id in(

selectstudent_id from studentcourse where score >=all(

selectscore from studentcourse

)

);

7 数据库操作练习

准备工作：

-- 部门表

create table dept(

deptno int primary key auto_increment, --部门编号

dname varchar(14) , --部门名字

loc varchar(13) -- 地址

) ;

-- 员工表

create table emp(

empno int primary key auto_increment,-- 员工编号

ename varchar(10), -- 员工姓名 -

job varchar(9), -- 岗位

mgr int, -- 直接领导编号

hiredate date, -- 雇佣日期，入职日期

sal int, -- 薪水

comm int, -- 提成

deptno int not null, -- 部门编号

foreign key (deptno) referencesdept(deptno)

);

insert into deptvalues(10,'财务部','北京');

insert into deptvalues(20,'研发部','上海');

insert into deptvalues(30,'销售部','广州');

insert into deptvalues(40,'行政部','深圳');

insert into empvalues(7369,'刘一','职员',7902,'1980-12-17',800,null,20);

insert into empvalues(7499,'陈二','推销员',7698,'1981-02-20',1600,300,30);

insert into empvalues(7521,'张三','推销员',7698,'1981-02-22',1250,500,30);

insert into empvalues(7566,'李四','经理',7839,'1981-04-02',2975,null,20);

insert into empvalues(7654,'王五','推销员',7698,'1981-09-28',1250,1400,30);

insert into empvalues(7698,'赵六','经理',7839,'1981-05-01',2850,null,30);

insert into empvalues(7782,'孙七','经理',7839,'1981-06-09',2450,null,10);

insert into empvalues(7788,'周八','分析师',7566,'1987-06-13',3000,null,20);

insert into empvalues(7839,'吴九','总裁',null,'1981-11-17',5000,null,10);

insert into empvalues(7844,'郑十','推销员',7698,'1981-09-08',1500,0,30);

insert into empvalues(7876,'郭靖','职员',7788,'1987-06-13',1100,null,20);

insert into empvalues(7900,'令狐冲','职员',7698,'1981-12-03',950,null,30);

insert into empvalues(7902,'张无忌','分析师',7566,'1981-12-03',3000,null,20);

insert into empvalues(7934,'杨过','职员',7782,'1983-01-23',1300,null,10);

-----------------------------------------------------------------------------------------

根据以上员工表练习

-- 1．列出至少有一个员工的所有部门。

selectdeptno,count(*) from emp group by deptno having count(*) >= 1;

select *from dept where deptno in(

select distinct deptno from empwhere empno is not null

);

selectd.deptno,d.dname,t.renshu from dept d inner join

(selectdeptno,count(deptno) renshu from emp group by deptno havingcount(deptno)>=1) t

ond.deptno=t.deptno;

-- 2．列出薪金比"刘一"多的所有员工。

select *from emp where sal>(

select sal from emp where ename='刘一'

);

-- 3．***** 列出所有员工的姓名及其直接上级的姓名。

selectt1.ename 员工姓名, t2.ename直接上级 from empt1,emp t2 where t1.mgr = t2.empno;

selectempt.ename 员工,mgrt.ename上级 from(select ename,mgr from emp) empt inner join

(select ename,empno from emp) mgrt onempt.mgr=mgrt.empno

;

selectename 员工,tt.jingli经理 from empee inner join

(selectt.mgr jinglihao,ename jingli from emp e inner join

(selectmgr from emp group by mgr) t

one.empno=t.mgr) tt

onee.mgr=tt.jinglihao

;

-- 4．列出受雇日期早于其直接上级的所有员工。

selectt1.*,t2.hiredate from emp t1,emp t2 where t1.mgr = t2.empno and t1.hiredate< t2.hiredate;

selectempt.`ename` 员工 from empempt

inner join

emp mgrt

on empt.`mgr`=mgrt.`empno` andempt.`hiredate`<mgrt.`hiredate`;

-- 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

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

-- 6．列出所有job为“职员”的姓名及其部门名称。

select enamefrom emp empt inner join dept on empt.`deptno`=dept.`deptno` and empt.`job`='职员';

selectd.dname 部门,t.ename 职员 from dept d inner join

(selectename,deptno from emp where job='职员') t

ond.deptno=t.deptno

;

-- 7．列出最低薪金大于1500的各种工作。

selectmin(sal),job from emp group by job having min(sal)>1500;

-- 8．列出在部门 "销售部" 工作的员工的姓名，假定不知道销售部的部门编号。

selectename from emp where deptno=(

select deptno from dept wheredname='销售部'

);

selectemp.ename from emp,dept where emp.deptno = dept.deptno and dept.dname = '销售部';

-- 9．列出薪金高于公司平均薪金的所有员工。

selectename from emp where sal>(

select avg(sal) from emp

);

-- 10．列出与"周八"从事相同工作的所有员工。

selectename from emp where ename<>'周八' and job=(

select job from emp where ename='周八'

);

-- 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

selectename sal from emp where sal in (

select sal from emp wheredeptno=30

);

-- 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

selectename sal from emp where sal>all(

select sal from emp wheredeptno=30

);

select *from emp where sal > (

select max(sal) from emp where deptno = 30

);

-- 13．列出在每个部门工作的员工数量、平均工资。

selectcount(*) 员工数量,avg(sal) 平均工资,deptno from emp group by deptno;

select t.dname 部门,count(empno),avg(sal) from emp e inner join

(selectdname,deptno from dept) t

one.deptno=t.deptno

group bye.deptno

;

-- 14．列出所有员工的姓名、部门名称和工资。

selectename,sal,deptt.`dname` from emp empt inner join dept deptt whereempt.`deptno`=deptt.`deptno` order by empt.`sal` desc;

selectemp.ename,dept.dname,emp.sal from emp,dept where emp.deptno = dept.deptno;

-- 15．列出所有部门的详细信息和部门人数。

selectdeptt.*,empt.renshu from dept deptt left join (

select count(*) renshu,deptno fromemp group by deptno

) empt ondeptt.`deptno`=empt.`deptno`;

selectd.*,t.renshu 部门人数 from deptd inner join

(selectcount(sal) renshu,deptno from emp group by deptno) t

ond.deptno=t.deptno;

-- 16．列出各种工作的最低工资。

selectmin(sal),job from emp group by job;

-- 17．列出各个部门的经理的最低薪金。

selectmin(sal),deptno from(

select * from emp where job='经理'

) empt group by deptno;

selectd.dname,min(sal) 经理最低薪金from emp e inner join

(selectdname,deptno from dept) d

ond.deptno=e.deptno

wheree.job='经理'

group bye.deptno;

-- 18．列出所有员工的年工资,按年薪从低到高排序。

selectename,12*sal+ifnull(comm,0) sals from emp order by sals asc;

-- 19.查出emp表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。

selectempno,ename,sal from emp where sal>=3000;

-- 20.查询出所有薪水在'陈二'之上的所有人员信息。

select *from emp where sal>(

select sal from emp where ename='陈二'

);

-- 21.查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号员工名字薪水

selectempno 员工编号,ename 员工姓名,sal 薪水 from emp where deptno=20 and sal>2000;

-- 22.查询出emp表中所有的工作种类（无重复）

selectdistinct job from emp;

-- 23.查询出所有奖金（comm）字段不为空的人员的所有信息。

select *from emp where comm is not null;

-- 24.查询出薪水在800到2500之间（闭区间）所有员工的信息。（注：使用两种方式实现and以及between and）

select *from emp where sal>=800 and sal<=2500;

select *from emp where sal between 800 and 2500;

-- 25.查询出员工号为7521，7900，7782的所有员工的信息。（注：使用两种方式实现，or以及in）

select *from emp where empno=7521 or empno=7900 or empno=7782;

select *from emp where empno in(7521,7900,7782);

-- 26.查询出名字中有“张”字符，并且薪水在1000以上（不包括1000）的所有员工信息。

select *from emp where ename like '%张%'and sal>1000;

-- 27.查询出名字第三个汉字是“忌”的所有员工信息。

select *from emp where ename like '__忌';

-- 28.将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序。

select *from emp order by sal asc,hiredate desc;

-- 29.将所有员工按照名字首字母升序排序，首字母相同的按照薪水降序排序。 order byconvert(name using gbk) asc;

select *from emp order by convert(ename using gbk)asc,sal desc;

-- 30.查询出最早工作的那个人的名字、入职时间和薪水。

selectename,hiredate,sal from emp where hiredate = (select min(hiredate) from emp);
select ename,hiredate,sal from emp where hiredate <= all(select hiredatefrom emp);

-- 31.显示所有员工的名字、薪水、奖金，如果没有奖金，暂时显示100.

selectename,sal,ifnull(comm,100) from emp;

-- 32.显示出薪水最高人的职位。

select jobfrom emp where sal>=all(

select sal from emp

);

select jobfrom emp where sal = (

selectmax(sal) from emp

);

-- 33.查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。

selectmin(sal),max(sal) from emp where deptno !=10 group by deptno;

selectmin(sal),max(sal) from emp where deptno <>10 group by deptno;

selectt.dname 部门,max(sal) 最高薪水,min(sal) 最低薪水 from emp e inner join

(select dname,deptnofrom dept) t

one.deptno=t.deptno

wheree.deptno<>10

group bye.deptno

;

-- 34.删除10号部门薪水最高的员工。

deletefrom emp where empno=(

select empno from emp wheresal>=all(

select sal from emp wheredeptno=10

) and deptno=10

);

上面是1093错误代码。

deletefrom emp where empno=(

select c.e from (

select empno e from emp emptwhere sal>=all(

select sal from empwhere deptno=10

)

) c

);

//mysql不支持，在一条语句对同一个表，先查询再更新的操作。

//可以利用临时表套嵌实际的表，取表别名，解决这个问题。oracle没有这个问题。

insertinto emp values(7840,'吴九','总裁',null,'1981-11-17',5000,null,10);

或者

deletefrom emp where empno=(select c.d from

(selectempno d from emp where sal in

(selectb.a from(select max(sal) a from emp where deptno=10)

anddeptno=10)

c);

deletefrom emp where empno=(select empno from emp where sal

in(selectmax(sal) b from emp where deptno=10)

anddeptno=10);--这是错误的代码。

或者

deletefrom emp where deptno=10 and sal >= all(

selectt.sal from (

select salfrom emp where deptno=10

)

t );

-- 35.将薪水最高的员工的薪水降30%。

update empset sal=sal*0.7 where sal=(

select c.e from (

select max(sal) e from emp

) c

);

与上题同样，如果不做套嵌查询表，会出现1093报错。

-- 36.查询员工姓名，工资和工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)

selectename,sal, case when sal>=3000 then '3级' when sal>2000 then '2级' else '1级' end 级别 from emp;

阅读全文

0 0