1. MySQL简介
- MySQL是一种开放源代码的关系型数据库管理系统(RDBMS),使用最常用的数据库管理语言
结构化查询语言(SQL)进行数据库管理。 - MySQL是开放源代码的,因此任何人都可以在General Public License的许可下下载并根据个性化
的需要对其进行修改。 - MySQL因为其速度、可靠性和适应性而备受关注。MySQL是管理内容最好的选择。
2. SQL
1.SQL语句分类
- DDL数据定义语言—定义表、库、字段等 create table、database
- DML数据操作语言–数据库的增删改表记录 update … set、insert、delete
- DCL数据控制语言–定义访问权限和安全级别 create user
- DQL数据查询语言–查询记录 select
2.DCL数据控制语言
1.创建用户
- 用户只能在指定ip地址上登录mysql: create user 用户名@IP地址 identified by ‘密码’
- 用户可以在任意ip地址上登录: create user 用户名@‘%’ identified by ‘密码’
2.给用户授权
- grant 权限1,…,权限n on 数据库.* to 用户名@IP地址; 其中权限1、2、n可以直接用all关键字
代替。权限例如:create,alter,drop,insert,update,delete,select
3.撤销授权
- : revoke 权限1,…,权限n on 数据库.* from 用户名@ ip地址; 撤销指定用户在指定数据库上的
指定权限。撤销例如: revoke create,delete on mydb1.* form user@localhost; 表示的意思是撤
消user用户在数据库mydb1伤的create、alter权限。
4.查看权限
- show grants for 用户名@ip地址
5.删除用户
- drop user 用户名@ip地址
3.DDL数据定义语言
1.数据类型
略
4.DML数据操作语言
1.插入数据
insert into 表名 (字段1,字段2,字段3....) values (字段值1,字段值2,字段值3....)
--省略字段顺序则必须按照创建时字段的顺序插入字段值
insert into 表名 values (字段值1,字段值2,字段值3....)
2.修改数据
update 表名 set 列名1=列值1
--可以一次修改多个字段
update 表名 set 列名1=列值1,列名2=列值2,....
3.删除数据(记录)
delete from 表名 (where 条件)
3.建表约束
1.约束类型
约束名称 | 描述 |
---|---|
NOT NULL | 非空约束,不允许该字段为空 |
UNIQUE | 唯一约束,取值不允许重复 |
PRIMARY KEY | 主键约束(主关键字),自带非空、唯一、索引 |
FOREIGN KEY | 外键约束(外关键字) |
DEFAULT | 默认值(缺省值) |
2.NOT NULL
- 非空约束,不允许该字段为空
3.UNIQUE
- 唯一约束,取值不允许重复
4.PRIMARY KEY
- 主键约束(主关键字),自带非空、唯一、索引
- 可以存在多个主键,多个主键时只要不是所有主键都相同就算唯一
5.AUTO_INCREMENT
- 自增约束(需配合主键使用)
- 当不指定主键新增记录时,新纪录的主键会自动增加1
create table testone(
id_1 int not null,
id_2 int unique,
id_3 int primary key auto_increment,
id_4 int default 10
);
create table testtwo(
id_1 int not null,
id_2 int,
id_3 int,
id_4 int default 10 (id_4),
-- not null(id_1), //不行
unique(id_2),
primary key auto_increment(id_3)
-- default 10 (id_4) //不行
);
6. FOREIGN KEY
- 外键约束
- 删除表时,如果不删除引用外建的表,被引用的表不能直接删除
- 外键的值必须来源于引用的表的主键字段
create table test(
id int,
FOREIGN KEY (字段) REFERENCES 外表名(外表字段)
)
4.查询语言
select [ all|distinct ] <选择序列>
from <表引用>, ..., <表引用>
[ where <查询条件> ]
[ group by <分组列>, ..., <分组列> [ having <分组选择条件> ] ]
[ order by <排序列> [ ASC|DESC ], ..., order by <排序列> [ ASC|DESC ] ]
-- select 指定要显示的属性列
-- from 指定查询对象(基本表或视图)
-- where 指定查询条件
-- group by 对查询结果按指定列的值分组,该属性列值相等的元组为一个组。通常会在每组中作用聚集函数
-- having 用在 group by 后面,对分组后的数据进行过滤
-- order by 对查询结果表按指定列值的升序或降序排序
- 准备.创建数据库以及创建表单
insert into student (id,name,age,gander) values (1,'zhangsan',13,'男');
insert into student (id,name,age) values (2,'lisi',13);
insert into student values (3,'wangwu',13,'男');
update student set age = 25;
UPDATE student set age = 13 where id = 2;
UPDATE student set age = 15,gander = '女' where id > 1;
UPDATE student set age = 90 where id < 3 and gander='女';
DELETE from student where age > 15;
DELETE from student where id = 3;
DELETE FROM student;
drop TABLE if EXISTS student;
CREATE TABLE student (
id INT(10) PRIMARY key,
name VARCHAR (10),
age INT (10) NOT NULL,
gander varchar(2)
);
drop TABLE if EXISTS course;
CREATE TABLE course (
id INT (10) PRIMARY key,
name VARCHAR (10) ,
t_id INT (10)
) ;
drop TABLE if EXISTS teacher;
CREATE TABLE teacher(
id INT (10) PRIMARY key,
name VARCHAR (10)
);
drop TABLE if EXISTS scores;
CREATE TABLE scores(
s_id INT ,
score INT (10),
c_id INT (10) ,
PRIMARY key(s_id,c_id)
);
- 准备.表单填充数据
insert into student (id,name,age,gander)VALUES(1,'白杰',19,'男');
insert into student (id,name,age,gander)VALUES(2,'连宇栋',19,'男');
insert into student (id,name,age,gander)VALUES(3,'邸志伟',24,'男');
insert into student (id,name,age,gander)VALUES(4,'李兴',11,'男');
insert into student (id,name,age,gander)VALUES(5,'张琪',18,'男');
insert into student (id,name,age,gander)VALUES(6,'武三水',18,'女');
insert into student (id,name,age,gander)VALUES(7,'张志伟',16,'男');
insert into student (id,name,age,gander)VALUES(8,'康永亮',23,'男');
insert into student (id,name,age,gander)VALUES(9,'杨涛瑞',22,'女');
insert into student (id,name,age,gander)VALUES(10,'王杰',21,'男');
insert into course (id,name,t_id)VALUES(1,'数学',1);
insert into course (id,name,t_id)VALUES(2,'语文',2);
insert into course (id,name,t_id)VALUES(3,'c++',3);
insert into course (id,name,t_id)VALUES(4,'java',4);
insert into course (id,name)VALUES(5,'php');
insert into teacher (id,name)VALUES(1,'张楠');
insert into teacher (id,name)VALUES(2,'老孙');
insert into teacher (id,name)VALUES(3,'薇薇姐');
insert into teacher (id,name)VALUES(4,'磊磊哥');
insert into teacher (id,name)VALUES(5,'大微姐');
insert into scores (s_id,score,c_id)VALUES(1,80,1);
insert into scores (s_id,score,c_id)VALUES(1,56,2);
insert into scores (s_id,score,c_id)VALUES(1,95,3);
insert into scores (s_id,score,c_id)VALUES(1,30,4);
insert into scores (s_id,score,c_id)VALUES(1,76,5);
insert into scores (s_id,score,c_id)VALUES(2,35,1);
insert into scores (s_id,score,c_id)VALUES(2,86,2);
insert into scores (s_id,score,c_id)VALUES(2,45,3);
insert into scores (s_id,score,c_id)VALUES(2,94,4);
insert into scores (s_id,score,c_id)VALUES(2,79,5);
insert into scores (s_id,score,c_id)VALUES(3,65,2);
insert into scores (s_id,score,c_id)VALUES(3,85,3);
insert into scores (s_id,score,c_id)VALUES(3,37,4);
insert into scores (s_id,score,c_id)VALUES(3,79,5);
insert into scores (s_id,score,c_id)VALUES(4,66,1);
insert into scores (s_id,score,c_id)VALUES(4,39,2);
insert into scores (s_id,score,c_id)VALUES(4,85,3);
insert into scores (s_id,score,c_id)VALUES(5,66,2);
insert into scores (s_id,score,c_id)VALUES(5,89,3);
insert into scores (s_id,score,c_id)VALUES(5,74,4);
insert into scores (s_id,score,c_id)VALUES(6,80,1);
insert into scores (s_id,score,c_id)VALUES(6,56,2);
insert into scores (s_id,score,c_id)VALUES(6,95,3);
insert into scores (s_id,score,c_id)VALUES(6,30,4);
insert into scores (s_id,score,c_id)VALUES(6,76,5);
insert into scores (s_id,score,c_id)VALUES(7,35,1);
insert into scores (s_id,score,c_id)VALUES(7,86,2);
insert into scores (s_id,score,c_id)VALUES(7,45,3);
insert into scores (s_id,score,c_id)VALUES(7,94,4);
insert into scores (s_id,score,c_id)VALUES(7,79,5);
insert into scores (s_id,score,c_id)VALUES(8,65,2);
insert into scores (s_id,score,c_id)VALUES(8,85,3);
insert into scores (s_id,score,c_id)VALUES(8,37,4);
insert into scores (s_id,score,c_id)VALUES(8,79,5);
insert into scores (s_id,score,c_id)VALUES(9,66,1);
insert into scores (s_id,score,c_id)VALUES(9,39,2);
insert into scores (s_id,score,c_id)VALUES(9,85,3);
insert into scores (s_id,score,c_id)VALUES(9,79,5);
insert into scores (s_id,score,c_id)VALUES(10,66,2);
insert into scores (s_id,score,c_id)VALUES(10,89,3);
insert into scores (s_id,score,c_id)VALUES(10,74,4);
insert into scores (s_id,score,c_id)VALUES(10,79,5);
1.单表查询
-
查询所有列: select * from 表名; 其中*表示查询所有列,而不是所有行的意思。
-
查询指定列: select 列1,列2,列n from 表名;
-
完全重复的记录只显示一次:在查询的列之前添加 distinct
-
列运算
a.数量类型的列可以做加、减、乘、除:`SELECT sal*5 from 表名;`说明: 1.遇到null加任何值都等于null的情况,需要用到 ifnull() 函数。 2.将字符串做加减乘除运算,会把字符串当作0。 b.字符串累类型可以做连续运算(需要用到concat()函数):`select concat(列名1,列名2) from 表名;`其中列名的类型要为字符串。 c. 给列名起别名:`select 列名1 (as) 别名1,列名2 (as) 别名2 from 表名;`
-
条件控制
条件查询。在后面添加where指定条件:`select * from 表名 where 列名=指定值;` like ‘张_’ (_代表匹配任意一个字符,%代表匹配0~n个任意字符)。 between...and... ,not between...and... >,<,<>,!=,>=,<=,!>,!<,=,not + 上述运算符 not like is null,not null in,not in(...) or,and
-
order by
可以按一个或多个属性列排序
升序:ASC;降序:DESC;缺省值为 ASC
-
聚合函数
-
统计元组个数 count(*)
-
统计一列中值的个数 count([distinct | all] <列名>)
-
计算一列值的总和(此列必须为数值型)sum([distinct | all] <列名>)
-
计算一列值的平均值(此列必须为数值型)avg([distinct | all] <列名>)
-
求一列中的最大值和最小值 max([distinct | all] <列名>),min([distinct | all] <列名>)
-
-
group by
- group by子句中给出的一个或多个属性用于构造分组,group by子句中的所有属性上取值相同的元组将被分在同一组
-
having短语与 where子句的区别
-
作用对象不同
-
where 子句作用于基表或视图,从中选择满足条件的元组
-
having 短语作用于组,从中选择满足条件的组
-
-
limit
-
限制查询语句的起始行,总行数
select * from 表名 limit 2 -- 查询结果的前两行 select * from 表名 limit 2,3 -- 查询结果中第2行(第2行不算)开始的3条数据,即3,4,5三行
-
2.多表查询
1.等值连接与自然连接
- 等值连接:关系R、S,取两者笛卡尔积中属性值相等的元组,例如 R.A=S.B
- 自然连接:特殊的等值连接。运算作用于两个关系并产生一个关系作为结果,在相同属性上进行相等比较,并投影去掉重复属性
2.自身连接
- 自身连接:一个表与其自己进行连接
- 由于所有属性名都是同名属性,因此属性前必须给表起别名以示区别
3.内连接
- 内连接查询操作只列出与连接条件匹配的数据行,使用INNER JOIN或者直接使用JOIN 进行连接
select * from table_1 (inner) join table_2 on table1.id=table2.id
4.外连接
1.左(外)连接
- 根据条件,用右表(table_2)匹配左表(table_1),能匹配,正确保留,不能匹配其他表的字段都置空 Null
select * from table_1 left join table_2 on table1.id=table2.id
select * from table_1 left (outer) join table_2 on table1.id=table2.id
2.右(外)连接
- 根据条件,用左表(table_1)匹配右表(table_2),能匹配,正确保留,不能匹配其他表的字段都置空 Null
select * from table_1 right join table_2 on table1.id=table2.id
select * from table_1 right (outer) join table_2 on table1.id=table2.id
5.全连接
- MySQL不支持,但可以用 左连接 union 右链接 代替
select * from table_1 left join table_2 on table1.id=table2.id
union
select * from table_1 right join table_2 on table1.id=table2.id
6.连接总结
3.嵌套查询(子查询)
1.where 子查询
- 一个 select-from-where语句称为一个查询块。将一个查询块嵌套在另一个查询块的 where子句或 having短语的条件中的查询称为嵌套查询
select * -- 外层查询/父查询
from test
where id in (
select id -- 内层查询/子查询
from test
where id= '2'
);
2.from子查询
select * -- 外层查询/父查询
from (
select id -- 内层查询/子查询
from test
where id= '2'
)
4.集合查询
- intersect, union, except分别对应交、并、差运算,均可以自动去除重复,若想保留重复只需在后面加上 “all”
-- 交运算
select * from table_1 left join table_2 on table1.id=table2.id
intersect
select * from table_1 right join table_2 on table1.id=table2.id
-- 并运算
select * from table_1 left join table_2 on table1.id=table2.id
union
select * from table_1 right join table_2 on table1.id=table2.id
-- 差运算
select * from table_1 left join table_2 on table1.id=table2.id
except
select * from table_1 right join table_2 on table1.id=table2.id
select * from table_1 left join table_2 on table1.id=table2.id
intersect
select * from table_1 right join table_2 on table1.id=table2.id
-- 并运算
select * from table_1 left join table_2 on table1.id=table2.id
union
select * from table_1 right join table_2 on table1.id=table2.id
-- 差运算
select * from table_1 left join table_2 on table1.id=table2.id
except
select * from table_1 right join table_2 on table1.id=table2.id
5.数据库三范式
- 第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据线;也就是说:每列的值具有原子性,不可再分割
- 第二范式(2NF)是在第一范式(1NF)的基础上建立起来得,满足第二范式(2NF)必须先满足第一范式(1NF)。如果表是单主键,那么主键以外的列必须完全依赖于主键;如果表是复合主键,那么主键以外的列必须完全依赖于主键,不能仅依赖主键的一部分
- 第三范式(3NF)是在第二范式的基础上建立起来的,即满足第三范式必须要先满足第二范式。第三范式(3NF)要求:表中的非主键列必须和主键直接相关而不能间接相关;也就是说:非主键列之间不能相关依赖