一、数据库概述
数据库(Database)是按照数据结构来组织、存储和管理数据的仓库，它产生于距今六十多年前，随着信息技术和市场的发展，特别是二十世纪九十年代以后，数据管理不再仅仅是存储和管理数据，而转变成用户所需要的各种数据管理的方式。数据库有很多种类型，从最简单的存储有各种数据的表格到能够进行海量数据存储的大型数据库系统都在各个方面得到了广泛的应用。
在信息化社会，充分有效地管理和利用各类信息资源，是进行科学研究和决策管理的前提条件。数据库技术是管理信息系统、办公自动化系统、决策支持系统等各类信息系统的核心部分，是进行科学研究和决策管理的重要技术手段。
二、数据库基本操作
1、数据库操作
show databases; #显示所有数据库名称
create database database_name [character set utf8]；# 创建一个数据库
show create database database_name; #查看数据库的创建信息
use database_name； #切换数据库
alter database db_name [character set xxx] #修改数据库的字符集 character set 设定字符集
drop database [if exists] db_name; #删除数据库
select database(); 查看在那个数据库下面
2、数据表操作
（1） 创建表： create table table_name (
filed 字段类型 [完整性约束]，
filed 字段类型 [完整性约束]，
filed 字段类型 [完整性约束]，
)；
例：create table c2 (
id int Primary key auto_increment,
name varchar (20),
sex varchar (5),
c2_id int
);
#创建id、name、sex和c2_id自动。其中primary key 设定主键 ， auto_increment 设置自增长。
（2） 查看表
desc table_name; 查看表结构
show tables； 是该数据库下所有的表名
show create table table_name ； 查看数据库的表创建信息
（3） 修改表：
alter table table_name add field type 完整性约束 #增加字段
例： alter table c2 add ( te_id int); #增加字段te_id
alter table table_name modify field type 完整性约束 #修改字段信息
例：alter table c2 modify te_id varchar (2) ; #修改te_id 字段为varchar类型。
alter table table_name change field type 完整性约束 #修改字段名
例： alter table c2 change te_id te_name INTEGER; #修改te_id 成te_name;
alter table table_name drop field type 完整性约束 #删除表字段
例：alter table c2 drop te_name; #删除te_name 字段
（4） 删除表
drop table tab_name;
3、表记录操作
（1） 增加记录：
insert tab_name set field=value, field=value,..... ；
insert [into] tab_name (field1,field2....) values (values1,values2....) ,
(values1,values2....),
(values1,values2....),
... ；
例：
insert c2 (id,name,sex,c2_id) values (1,'ss','男',22),
(2,'ff','女',11)
(3,'ff','女',33); #增加字段 ，前面为哪些字段增加信息，后面为增加的内容。
（2） 修改记录
update tab_name set field=value where 子句
例：
update c2 set name='feng' where name='ss'; #修改ss为feng
（3） 删除表记录：
delete from tab_name where 子句
例：
delete from c2 where id=4; #删除id等于4的数据。
（4） 清空表记录：
1 delete from tab_name
2 truncate tab_name (数据量大时推荐使用)
（5）查询表记录（******）
select * from tab_name : 显示所有的记录的所有字段信息
例：
mysql> select * from c2;
+----+------+------+-------+
| id | name | sex | c2_id |
+----+------+------+-------+
| 1 | feng | 男 | 22 |
| 2 | ff | 女 | 11 |
| 3 | ss | 男 | 22 |
+----+------+------+-------+
3 rows in set (0.00 sec)
三、mysql查询
1、查询的使用
select [distinct] filed，filed2,..... from tab_name
where 子句
group by 分组 key : 分组条件
having 子句 : 过滤
order by #-- Asc 升序、Desc 降序，其中asc为默认值
ORDER BY 子句应位于SELECT语句的结尾。
limit
group_concat(name) 对那些进行分组的人显示名字
where子句: 过滤查询
-- where字句中可以使用：
-- 比较运算符：
> < >= <= <> !=
between 80 and 100 值在10到20之间
in(80,90,100) 值是10或20或30
like 'yuan%'
/*
pattern可以是%或者_，
如果是%则表示任意多字符，此例如唐僧,唐国强
如果是_则表示一个字符唐_，只有唐僧符合。两个_则表示两个字符：__
*/
-- 逻辑运算符
在多个条件直接可以使用逻辑运算符 and or not
示例：
-- 查询年纪大于24的员工 SELECT * FROM emp WHERE age>24; -- 查询教学部的男老师信息 SELECT * FROM emp WHERE dep="教学部" AND gender="male";
order：排序
按指定的列进行，排序的列即可是表中的列名，也可以是select语句后指定的别名。
-- 语法：
select *|field1,field2... from tab_name order by field [Asc|Desc]
-- Asc 升序、Desc 降序，其中asc为默认值 ORDER BY 子句应位于SELECT语句的结尾。
示例：
-- 按年龄从高到低进行排序 SELECT * FROM emp ORDER BY age DESC ; -- 按工资从低到高进行排序 SELECT * FROM emp ORDER BY salary;
group by：分组查询（＊＊＊＊＊）
GROUP BY 语句根据某个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG等函数进行相关查询。
-- 语法: SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;
示例：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
-- 查询男女员工各有多少人SELECT gender 性别,count(*) 人数 FROM emp5 GROUP BY gender;-- 查询各个部门的人数SELECT dep 部门,count(*) 人数 FROM emp5 GROUP BY dep;-- 查询每个部门最大的年龄SELECT dep 部门,max(age) 最大年纪 FROM emp5 GROUP BY dep;-- 查询每个部门年龄最大的员工姓名SELECT * FROM emp5 WHERE age in (SELECT max(age) FROM emp5 GROUP BY dep);-- 查询每个部门的平均工资SELECT dep 部门,avg(salary) 最大年纪 FROM emp GROUP BY dep;-- 查询教学部的员工最高工资:SELECT dep,max(salary) FROM emp11 GROUP BY dep HAVING dep="教学部";-- 查询平均薪水超过8000的部门SELECT dep,AVG(salary) FROM emp GROUP BY dep HAVING avg(salary)>8000;-- 查询每个组的员工姓名SELECT dep,group_concat(name) FROM emp GROUP BY dep;-- 查询公司一共有多少员工(可以将所有记录看成一个组)SELECT COUNT(*) 员工总人数 FROM emp; -- KEY: 查询条件中的每个后的词就是分组的字段 |
limit记录条数限制
SELECT * from ExamResult limit 1; SELECT * from ExamResult limit 2,5; -- 跳过前两条显示接下来的五条纪录 SELECT * from ExamResult limit 2,2;
正则表达式
SELECT * FROM employee WHERE emp_name REGEXP '^yu';
SELECT * FROM employee WHERE emp_name REGEXP 'yun$';
SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';
多表查询
创建表
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
salary DOUBLE(7,2),
dep_id INT
);
INSERT INTO emp (name,salary,dep_id) VALUES ("张三",8000,2),
("李四",12000,1),
("王五",5000,2),
("赵六",8000,3),
("猪七",9000,1),
("周八",7000,4),
("蔡九",7000,2);
CREATE TABLE dep(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
INSERT INTO dep (name) VALUES ("教学部"),
("销售部"),
("人事部");
mysql> select * from emp; +----+--------+----------+--------+ | id | name | salary | dep_id | +----+--------+----------+--------+ | 1 | 张三 | 8000.00 | 2 | | 2 | 李四 | 12000.00 | 1 | | 3 | 王五 | 5000.00 | 2 | | 4 | 赵六 | 8000.00 | 3 | | 5 | 猪七 | 9000.00 | 1 | | 6 | 周八 | 7000.00 | 4 | | 7 | 蔡九 | 7000.00 | 2 | +----+--------+----------+--------+ 7 rows in set (0.00 sec)
mysql> select * from dep;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 教学部 |
| 2 | 销售部 |
| 3 | 人事部 |
+----+-----------+
3 rows in set (0.00 sec)
1.笛卡尔积查询
|
1
|
select * from emp,dep; |
mysql> select * from emp,dep; +----+--------+----------+--------+----+-----------+ | id | name | salary | dep_id | id | name | +----+--------+----------+--------+----+-----------+ | 1 | 张三 | 8000.00 | 2 | 1 | 教学部 | | 1 | 张三 | 8000.00 | 2 | 2 | 销售部 | | 1 | 张三 | 8000.00 | 2 | 3 | 人事部 | | 2 | 李四 | 12000.00 | 1 | 1 | 教学部 | | 2 | 李四 | 12000.00 | 1 | 2 | 销售部 | | 2 | 李四 | 12000.00 | 1 | 3 | 人事部 | | 3 | 王五 | 5000.00 | 2 | 1 | 教学部 | | 3 | 王五 | 5000.00 | 2 | 2 | 销售部 | | 3 | 王五 | 5000.00 | 2 | 3 | 人事部 | | 4 | 赵六 | 8000.00 | 3 | 1 | 教学部 | | 4 | 赵六 | 8000.00 | 3 | 2 | 销售部 | | 4 | 赵六 | 8000.00 | 3 | 3 | 人事部 | | 5 | 猪七 | 9000.00 | 1 | 1 | 教学部 | | 5 | 猪七 | 9000.00 | 1 | 2 | 销售部 | | 5 | 猪七 | 9000.00 | 1 | 3 | 人事部 | | 6 | 周八 | 7000.00 | 4 | 1 | 教学部 | | 6 | 周八 | 7000.00 | 4 | 2 | 销售部 | | 6 | 周八 | 7000.00 | 4 | 3 | 人事部 | | 7 | 蔡九 | 7000.00 | 2 | 1 | 教学部 | | 7 | 蔡九 | 7000.00 | 2 | 2 | 销售部 | | 7 | 蔡九 | 7000.00 | 2 | 3 | 人事部 | +----+--------+----------+--------+----+-----------+ 21 rows in set (0.00 sec)
2、内连接
查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。
|
1
|
SELECT * FROM emp,dep WHERE emp.dep_id=dep.id; |
OR
|
1
|
SELECT * FROM emp INNER JOIN dep ON emp.dep_id=dep.id; |
查询结果：
+----+--------+----------+--------+----+-----------+ | id | name | salary | dep_id | id | name | +----+--------+----------+--------+----+-----------+ | 1 | 张三 | 8000.00 | 2 | 2 | 销售部 | | 2 | 李四 | 12000.00 | 1 | 1 | 教学部 | | 3 | 王五 | 5000.00 | 2 | 2 | 销售部 | | 4 | 赵六 | 8000.00 | 3 | 3 | 人事部 | | 5 | 猪七 | 9000.00 | 1 | 1 | 教学部 | | 7 | 蔡九 | 7000.00 | 2 | 2 | 销售部 | +----+--------+----------+--------+----+-----------+ 6 rows in set (0.00 sec)
这时，我们就可以利用两张表中所有的字段进行查询了
示例：
-- 查询李四所在的部门名称 SELECT emp.name,dep.name FROM emp INNER JOIN dep ON emp.dep_id=dep.id WHERE emp.name="李四"; -- 查询销售部所有员工姓名以及部门名称 -- SELECT name FROM emp WHERE dep_id in (SELECT id FROM dep WHERE name="销售部"); SELECT emp.name,dep.name FROM emp INNER JOIN dep ON emp.dep_id=dep.id WHERE dep.name="销售部";
3、外连接
|
1
2
3
|
--（1）左外连接：在内连接的基础上增加左边有右边没有的结果 SELECT * FROM emp LEFT JOIN dep ON dep.id=emp.dep_id; |
+----+--------+----------+--------+------+-----------+ | id | name | salary | dep_id | id | name | +----+--------+----------+--------+------+-----------+ | 2 | 李四 | 12000.00 | 1 | 1 | 教学部 | | 5 | 猪七 | 9000.00 | 1 | 1 | 教学部 | | 1 | 张三 | 8000.00 | 2 | 2 | 销售部 | | 3 | 王五 | 5000.00 | 2 | 2 | 销售部 | | 7 | 蔡九 | 7000.00 | 2 | 2 | 销售部 | | 4 | 赵六 | 8000.00 | 3 | 3 | 人事部 | | 6 | 周八 | 7000.00 | 4 | NULL | NULL | +----+--------+----------+--------+------+-----------+ 7 rows in set (0.00 sec)
|
1
2
3
|
--（1）外右连接：在内连接的基础上增加右边有左边没有的结果 SELECT * FROM emp RIGHT JOIN dep ON dep.id=emp.dep_id; |
mysql> SELECT * FROM emp RIGHT JOIN dep ON dep.id=emp.dep_id; +------+--------+----------+--------+----+-----------+ | id | name | salary | dep_id | id | name | +------+--------+----------+--------+----+-----------+ | 1 | 张三 | 8000.00 | 2 | 2 | 销售部 | | 2 | 李四 | 12000.00 | 1 | 1 | 教学部 | | 3 | 王五 | 5000.00 | 2 | 2 | 销售部 | | 4 | 赵六 | 8000.00 | 3 | 3 | 人事部 | | 5 | 猪七 | 9000.00 | 1 | 1 | 教学部 | | 7 | 蔡九 | 7000.00 | 2 | 2 | 销售部 | +------+--------+----------+--------+----+-----------+ 6 rows in set (0.00 sec)
