一.sql的基本语法
1.对数据库
(1)系统数据库
show databases;
查看数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
information_schema:虚拟库,不占用磁盘空间,存储的是数据库启动后的一些参数,如用户表信息,权限信息等
performance_schema:MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象
mysql:授权库,主要存储系统用户的授权信息
test： MySQL数据库系统自动创建的测试数据库
(2)数据库相关操作
求救语法:
help +mysql语法;
查看当前语法的使用规则
创建数据库:
create database + 数据库名;
数据库命名规则:
可以由字母、数字、下划线、＠、＃、＄
区分大小写
唯一性
不能使用关键字如 create select
不能单独使用数字
最长128位
查看数据库详细信息:
show create database + 数据库名;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
查看所在的库:
select database();
+------------+
| database() |
+------------+
| db1 |
+------------+
使用数据库:
use + 数据库名;
删除数据库:
drop database + 数据库名;
修改数据库的编码:
alter database db1 charset + 编码方式;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db2 | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
sql语言分类:
SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型：
DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE
2.对表
增加表
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
删除表
drop table + 表名;
修改表
alter table t1 modify name char(6);
修改表的数据类型信息
alter table t1 change name NAMA char(7);
修改表中列的名称
查看表
show create table + 表名;
查看表的创建信息
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(10) DEFAULT NULL
`name` char(7) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------+
show tables;
查看该数据库中的所有表
desc + 表名;
查看表的结构详细信息
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | char(7) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
复制表
复制表的结构和数据
create table a1 select * from db2.t1;
只复制表的结构
create table a2 select * from db2.t1 where 1>2;
create table a3 like db2.t1;
3.对数据
增
insert + 表名(列的名称,列的名称) values(数据,数据),(数据,数据);
insert into + 表名(列的名称,列的名称) values(数据,数据),(数据,数据);
查
select id from t1;
查询某一列
select id,name from db1.t1;
查询多列
select name from t1 where id=1;
精确查询某一个
改
update t1 set name='huoyu';
更改某一列的所有制
update t1 set name='小乔' where id=2;
精确更改某一个值
删
delete from t1 where id=2;
精确删除某一个值
delete from t1;
删除某一个表
二.存储引擎
存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。
mysql 5.5版本之后默认为innodb存储引擎
mysql支持的存储引擎
1.InnoDB存储引擎
支持事务
2.MyISAN存储引擎
不支持事务
3.Memory存储引擎
正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失。
在重启mysql或者重启机器后，表内数据清空
4.BLACKHOLE存储引擎
往表内插入任何数据，都相当于丢入黑洞，表内永远不存记录
#.frm是存储数据表的框架结构
# .ibd是mysql数据文件
#.MYD是MyISAM表的数据文件的扩展名
#.MYI是MyISAM表的索引的扩展名
三.数据类型
1.数字
整形:
tinyint:
有符号:-128-127
无符号:0-255
int:
有符号: -2147483648 ～ 2147483647
无符号: 0 ～ 4294967295
bigint:
有符号:-9223372036854775808 ～ 9223372036854775807
无符号:0 ～ 18446744073709551615
sql中没有boolean类型 使用tinyint(1)来表示boolean值 1 表示true 0 表示false
默认有符号,无符号unsigned
zerofill,用0填充不足的位数
int类型后面的数字是显示宽度,而不是存储宽度(显示宽度是指最少显示宽度)
默认的显示宽度，都是在最大值的基础上加1
总结一句话，定义int类型不需要加宽度，使用默认就可以
浮点型:
float:
有符号: -3.402823466E+38 to -1.175494351E-38,1.175494351E-38 to 3.402823466E+38
无符号:1.175494351E-38 to 3.402823466E+38
随着小数的增多，精度变得不准确
double:
有符号:-1.7976931348623157E+308 to -2.2250738585072014E-308, 2.2250738585072014E-308 to 1.7976931348623157E+308
无符号:2.2250738585072014E-308 to 1.7976931348623157E+308
随着小数的增多，精度比float要高，但也会变得不准确
decimal:
随着小数的增多，精度始终准确
decaimal能够存储精确值的原因在于其内部按照字符串存储
范围同double
2.字符串
char:
定长，简单粗暴，浪费空间，存取速度快
存储char类型的值时，会往右填充空格来满足长度
varchar:
变长，精准，节省空间，存取速度慢
尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容
3.时间
year
YYYY（1901/2155）
data
YYYY-MM-DD（1000-01-01/9999-12-31）
time
HH:MM:SS（'-838:59:59'/'838:59:59'）
datatime
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59）
注:new()是mysql的内置函数,自动获取当前的年月日,时分秒,根据数据类型生成对应的时间格式
4.枚举和集合
enum:单选,只能在给定范围内选择一个值
set:多选,在给定范围内选择一个或多个
create table t5(x enum('a','b'),y set('a','b','c'));//创建一个新的表格,x只能单选,y能多选
insert into t5(x,y) values('a','b,c');
insert into t5(x,y) values('a','b');
+------+------+
| x | y |
+------+------+
| a | b |
| a | b,c |
+------+------+
四.约束
约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性
1.not null与default:不能为空
create table t7(x char(10) not null,y char(10) null,z char(10) not null default 'aaa');创建一个表,x不能为空,y可以为空,z为空显示aaa
insert into t7 values(null,null,null);//Column 'x' cannot be null
insert into t7(x,y) values('a',null);
select * from t7;
+------------+------------+------------+
| x | y | z |
+------------+------------+------------+
| | | |
| a | NULL | aaa |
+------------+------------+------------+
2.unique:
单列唯一:
create table t8(name char(10) unique);
insert into t8 values('市场部');
insert into t8 values('市场部');//error
多列唯一:
create table t9(a char(10) unique,b char(10) unique);
insert into t9 values('a','b');
insert into t9 values('a','c');//error
insert into t9 values('c','b');//error
insert into t9 values('d','d');//OK
组合唯一(联合唯一):只要有一列不同,就可以插入数据
create table t10(a char(10),b char(10),unique(a,b));
insert into t10 values('a','a');
insert into t10 values('b','a');//ok
insert into t10 values('a','b');//OK
insert into t10 values('a','a');//error
3.primary key
一张表中只能有一个主键
单列主键:
create table t11(id int primary key,name char(10) unique);
多列做主键(复合主键):
create table t12(a int,b int,primary key(a,b));
insert into t11 values(1,1);
insert into t11 values(1,2);//error
insert into t11 values(2,1);//error
insert into t11 values(2,2);//OK
primary key和not null + unique能实现的功能是一样的,但是primary key能索引优化,处理大量数据时,效果更好
4.auto_increment:约束的字段为自动增长，约束的字段必须同时被key约束
注:只能有一个自增列，且必须与主键同时用。
id也可以不用自增的,自己制定
对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
create table t13(id int primary key auto_increment,name char(10));
insert into t13(name) values('hua');
insert into t13(name) values('huxa');
+----+------------+
| id | name |
+----+------------+
| 1 | hua |
| 2 | huxa |
+----+------------+
5.foreign key
建立两张表之间的关系
先创建被关联的表(主表)def
create table dep(
id int primary key auto_increment,
name char(20) not null,
des char(30) not null
);
再创建关联表(从表)emp
create table emp(
id int primary key auto_increment,
name char(20) not null,
age int not null,
def_id int not null,
constraint fk_dep foreign key(def_id) references dep(id)
);
先给被关联的表插入数据
insert into dep(name,des) values
('IT','IT技术有限部门'),
('销售部','销售部门'),
('财务部','花钱太多部门');
再给关联表(从表)插入数据
insert into emp(name,age,def_id) values
('张三',18,1),
('李四',19,1),
('二宫',20,2),
('袁浩',40,3),
('alex',18,2)
;
此时想先删除部门表中的数据,再删除员工表中的数据会发生错误
delete from dep where id=3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
(`db5`.`emp`, CONSTRAINT `fk_dep` FOREIGN KEY (`def_id`) REFERENCES `dep` (`id`))
先删除员工表中的数据,再删除部门表中的数据是没有问题的
delete from emp where id=4;
delete from dep where id=3;
+----+-----------+----------------------+
| id | name | des |
+----+-----------+----------------------+
| 1 | IT | IT技术有限部门 |
| 2 | 销售部 | 销售部门 |
+----+-----------+----------------------+
同步更新,同步删除
on delete cascade 同步删除
on update cascade 同步更新
先创建被关联的表(主表)dep
create table dep(
id int primary key auto_increment,
name char(20) not null,
des char(30) not null
);
再创建关联表(从表)emp
create table emp(
id int primary key auto_increment,
name char(20) not null,
age int not null,
def_id int not null,
constraint fk_dep foreign key(def_id) references dep(id)
on delete cascade
on update cascade
);
添加数据
insert into dep(name,des) values
('IT','IT技术有限部门'),
('销售部','销售部门'),
('财务部','花钱太多部门');
insert into emp(name,age,def_id) values
('张三',18,1),
('李四',19,1),
('二宫',20,2),
('袁浩',40,3),
('alex',18,2)
;
同步删除
delete from dep where id=3;
select * from emp;
+----+--------+-----+--------+
| id | name | age | def_id |
+----+--------+-----+--------+
| 1 | 张三 | 18 | 1 |
| 2 | 李四 | 19 | 1 |
| 3 | 二宫 | 20 | 2 |
| 5 | alex | 18 | 2 |
+----+--------+-----+--------+
同步更新
update dep set id=111 where id=2;
select * from emp;
+----+--------+-----+--------+
| id | name | age | def_id |
+----+--------+-----+--------+
| 1 | 张三 | 18 | 1 |
| 2 | 李四 | 19 | 1 |
| 3 | 二宫 | 20 | 111 |
| 5 | alex | 18 | 111 |
+----+--------+-----+--------+
外键的变种
1.先站在左表的角度去找,是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）
2.再站在右表的角度去找,是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）
多对一,一对多
1或2成立
如,书和出版社的关系
多对多
1和2同时成立
如作者和书的关系
关联方式：foreign key+一张新的表
一对一
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
如人和身份证号的关系
关联方式：foreign key+unique
例:一对一
分别创建两张表
create table card(
id int primary key auto_increment,
cardid int not null unique);
create table people(
id int primary key auto_increment,
name char(20),
card_id int unique,
constraint fk_card foreign key(card_id) references card(id)
on delete cascade
on update cascade);
分别插入数据
insert into card(cardid) values(11111),(22222),(333333);
insert into people(name,card_id) values('aaa',1),('bbb',2),('ccc',3);
select * from people;
+----+------+---------+
| id | name | card_id |
+----+------+---------+
| 1 | aaa | 1 |
| 2 | bbb | 2 |
| 3 | ccc | 3 |
+----+------+---------+
例:多对多(作者和书实例)
create table autor(
id int primary key auto_increment,
name char(20)
);
create table book(
id int primary key auto_increment,
name char(20)
);
create table autor_book(
id int primary key auto_increment,
book_id int not null,
autor_id int not null,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
constraint fk_autor foreign key(autor_id) references autor(id)
on delete cascade
on update cascade
);
insert into autor(name) values ('egon'),('alex'),('wusir'),('yuanhao');
insert into book(name) values ('独孤九剑'),('葵花宝典'),('慕容秘籍'),('想不出来');
insert into autor_book(book_id,autor_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(2,1),
(3,4),
(4,1)
;
select * from autor_book;
+----+---------+----------+
| id | book_id | autor_id |
+----+---------+----------+
| 13 | 1 | 1 |
| 25 | 1 | 2 |
| 26 | 1 | 3 |
| 49 | 1 | 4 |
| 51 | 1 | 4 |
| 52 | 2 | 1 |
| 53 | 3 | 4 |
| 54 | 4 | 1 |
+----+---------+----------+
五.单表查询
1.单表查询的语法
select 字段1.字段2...form 表名
where 条件
group by field
having 筛选
order by field
limit 限制条数
2.关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit
1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.去重
7.将结果按条件排序：order by
8.限制结果的显示条数
(1)where约束
比较运算符：>、<、>=、<=、<>、!=
select id, name,age from employee where id>5;
+----+------------+-----+
| id | name | age |
+----+------------+-----+
| 6 | jingliyang | 18 |
| 7 | jinxin | 18 |
| 8 | xiaomage | 48 |
| 9 | 歪歪 | 48 |
| 10 | 丫丫 | 38 |
| 11 | 丁丁 | 18 |
| 12 | 星星 | 18 |
| 13 | 格格 | 28 |
| 14 | 张野 | 28 |
| 15 | 程咬金 | 18 |
| 16 | 程咬银 | 18 |
| 17 | 程咬铜 | 18 |
| 18 | 程咬铁 | 18 |
+----+------------+-----+
关键字between and
select id, name,age from employee where salary between 10000 and 50000;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 7 | jinxin | 18 |
| 8 | xiaomage | 48 |
| 14 | 张野 | 28 |
| 15 | 程咬金 | 18 |
| 16 | 程咬银 | 18 |
| 17 | 程咬铜 | 18 |
| 18 | 程咬铁 | 18 |
+----+-----------+-----+
关键字in集合查询
select id, name,age from employee where age in(18,28);
+----+------------+-----+
| id | name | age |
+----+------------+-----+
| 1 | egon | 18 |
| 5 | liwenzhou | 28 |
| 6 | jingliyang | 18 |
| 7 | jinxin | 18 |
| 11 | 丁丁 | 18 |
| 12 | 星星 | 18 |
| 13 | 格格 | 28 |
| 14 | 张野 | 28 |
| 15 | 程咬金 | 18 |
| 16 | 程咬银 | 18 |
| 17 | 程咬铜 | 18 |
| 18 | 程咬铁 | 18 |
+----+------------+-----+
关键字like模糊查询
通配符%表示人任意多字符
通配符_表示一个字符
select id, name,age from employee where name like 'j%';
+----+------------+-----+
| id | name | age |
+----+------------+-----+
| 6 | jingliyang | 18 |
| 7 | jinxin | 18 |
+----+------------+-----+
逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
select id, name,age from employee where name like 'j%' and sex='female';
+----+------------+-----+
| id | name | age |
+----+------------+-----+
| 6 | jingliyang | 18 |
+----+------------+-----+
(2)group by分组查询
为什么要分组?
取每个部门的最高工资
取每个部门的员工数
取男人数和女人数
小窍门：‘每’这个字后面的字段，就是我们分组的依据
select * from employee group by post;
这句代码得到的是每组中的第一条数据,没有任何意义,因为我们现在想查的是每组的多条数据
由于没有设置ONLY_FULL_GROUP_BY,默认都是组内的第一条记录
如果想分组，则必须要设置全局的sql的模式为ONLY_FULL_GROUP_BY
set global sql_mode='ONLY_FULL_GROUP_BY';
或者将ONLY_FULL_GROUP_BY添加到my,ini中
设置成功后，一定要退出，然后重新登录方可生效
如果想查看组内信息，需要借助于聚合函数
max()求最大值
min()求最小值
avg()求平均值
sum() 求和
count() 求总个数
select post,count(id),max(salary) from employee group by post;
+-----------------------------------------+-----------+-------------+
| post | count(id) | max(salary) |
+-----------------------------------------+-----------+-------------+
| operation | 5 | 20000.00 |
| sale | 5 | 4000.33 |
| teacher | 7 | 1000000.31 |
| 老男孩驻沙河办事处外交大使 | 1 | 7300.33 |
+-----------------------------------------+-----------+-------------+
(3)having过滤
Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
group_concat()函数
必须使用group by才能使用此函数
练习:
查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
mysql> select post,group_concat(name),count(1) from employee group by post having count(1)<2;
+-----------------------------------------+--------------------+----------+
| post | group_concat(name) | count(1) |
+-----------------------------------------+--------------------+----------+
| 老男孩驻沙河办事处外交大使 | egon | 1 |
+-----------------------------------------+--------------------+----------+
查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary)>10000;
+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| operation | 16800.026000 |
| teacher | 151842.901429 |
+-----------+---------------+
查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary)>10000 and avg(salary)<20000;
+-----------+--------------+
| post | avg(salary) |
+-----------+--------------+
| operation | 16800.026000 |
+-----------+--------------+
(4)order by查询排序
asc 升序
desc 降序
例:先按照age升序排序，如果年纪相同，则按照id降序
select * from employee order by age asc,id desc;
查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
select post,avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary) desc;
+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| teacher | 151842.901429 |
| operation | 16800.026000 |
+-----------+---------------+
(5)限制查询的记录数
第一个参数起始位置，第二个参数显示的个数
select * from employee limit 3;如果只有一个参数,默认第一个参数是0
select * from employee limit 3,4;
六.多表查询
1.多表链接查询
(0)交叉链接:不适用任何匹配条件。生成笛卡尔积
select * from employee,department;
(1)外链接
内连接:只连接匹配的行
语法:select 表1 inner join 表2 on 表1.字段=表2.字段;
select * from employee inner join department on employee.dep_id=department.id;
+----+---------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+---------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 5 | nvshen | male | 18 | 200 | 200 | 技术 |
+----+---------+--------+------+--------+------+--------------+
外链接之左连接：优先显示左表全部记录
select * from employee left join department on employee.dep_id=department.id;
+----+----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+----------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 5 | nvshen | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 6 | xiaomage | female | 18 | 204 | NULL | NULL |
+----+----------+--------+------+--------+------+--------------+
外链接之右连接：优先显示右表全部记录
select * from employee right join department on employee.dep_id=department.id;
+------+---------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+---------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 5 | nvshen | male | 18 | 200 | 200 | 技术 |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+---------+--------+------+--------+------+--------------+
全外链接:显示左右两个表全部记录
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id;
+------+----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+----------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 5 | nvshen | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 6 | xiaomage | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+----------+--------+------+--------+------+--------------+
七.符合条件查询
即:将链接之后的表作为一张大表,继续做单表查询
例:以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25，并且以age字段的升序方式显示。
select * from employee inner join department on employee.dep_id=department.id where employee.age>25 order by age asc;
+----+---------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+---------+--------+------+--------+------+--------------+
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
+----+---------+--------+------+--------+------+--------------+
八.子查询
子查询是将一个查询语句嵌套在另一个查询语句中。
内层查询语句的查询结果，可以为外层查询语句提供查询条件。
1.带in关键字的子查询
例:查看技术部员工姓名
select name from employee where dep_id in( select id from department where name='技术');
+--------+
| name |
+--------+
| egon |
| nvshen |
+--------+
2.带比较运算的子查询
例:查看大于部门内平均年龄的员工名,年龄
select name,age from employee inner join (select dep_id,avg(age) as a from employee group by dep_id) as t1 on employee.dep_id=t1.dep_id where age>a;
+------+------+
| name | age |
+------+------+
| alex | 48 |
+------+------+
select name,age from employee where age > (select avg(age) from employee);
+---------+------+
| name | age |
+---------+------+
| alex | 48 |
| wupeiqi | 38 |
+---------+------+
3.带exist关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
select * from employee where exists (select id from department where id=200);
+----+----------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+----------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | nvshen | male | 18 | 200 |
| 6 | xiaomage | female | 18 | 204 |
+----+----------+--------+------+--------+
九.pymysql模块的使用
1.pymysql模块的下载
pip3 install pymysql
2.pymysql的使用
import pymysql
username=input('请输入用户名:')
pwd=input('请输入密码:')
# 1.连接
conn=pymysql.connect(
host='localhost',
user='root',
password='',
database='db16',
port=3306
)
# 2.创建游标
cur=conn.cursor()
sql='select * from userinfo where username="%s" and pwd="%s"'% (username,pwd)
print(sql)
# 3.执行sql语句
res=cur.execute(sql)
print(res)
# 4.关闭连接
cur.close()
conn.close()
if res:
print('登录成功')
else:
print('登录失败')
3.execute()之sql注入
当用户名输入成这样时:sss " or 1=1 --
不用输入密码都能登录
解决方案
sql='select * from userinfo where username=%s and pwd=%s' #去掉%s两边的双引号
res=cur.execute(sql,(username,pwd)) # 用元组或列表都可以
4.增删该:conn.commit() 提交
在对数据进行增删改时,必须要提交,否则更改的数据不生效
import pymysql
conn=pymysql.connect(
host='localhost',
user='root',
password='',
database='db16',
port=3306
)
cur=conn.cursor()
sql='insert into userinfo values(2,"aaa","456")'
res=cur.execute(sql)
print(res)
conn.commit()
cur.close()
conn.close()
5.查：fetchone、fetchmany、fetchall
(1)fetchone
import pymysql
conn=pymysql.connect(
host='localhost',
user='root',
password='',
database='db16',
port=3306
)
cur=conn.cursor()
sql='select * from userinfo'
res=cur.execute(sql)
row=cur.fetchone()
print(row) #(1, 'mjj', '123')
cur.close()
conn.close()
(2)fetchall()
import pymysql
conn=pymysql.connect(
host='localhost',
user='root',
password='',
database='db16',
port=3306
)
cur=conn.cursor()
sql='select * from userinfo'
res=cur.execute(sql)
row=cur.fetchall()
print(row)
#((1, 'mjj', '123'), (2, 'aaa', '456'), (3, 'bbb', '456'), (4, 'ccc', '456'))
cur.close()
conn.close()
(3)fetchmany(a) 括号中的数字是获取几条数据
import pymysql
conn=pymysql.connect(
host='localhost',
user='root',
password='',
database='db16',
port=3306
)
cur = conn.cursor(cursor=pymysql.cursors.DictCursor)#让下面的fetchall返回的数据是一个字典
sql='select * from userinfo'
res=cur.execute(sql)
row=cur.fetchmany(2)
print(row)
# [{'id': 1, 'username': 'mjj', 'pwd': '123'}, {'id': 2, 'username': 'aaa', 'pwd': '456'}]
cur.close()
conn.close()
注:cursor.scroll(1,mode='relative') 相对当前位置移动
cursor.scroll(1,mode='absolute') 相对绝对位置移动
import pymysql
conn=pymysql.connect(
host='localhost',
user='root',
password='',
database='db16',
port=3306
)
cur = conn.cursor(cursor=pymysql.cursors.DictCursor)#让下面的fetchall返回的数据是一个字典
sql='select * from userinfo'
res=cur.execute(sql)
cur.scroll(1,mode='absolute')
row=cur.fetchmany(2)
print(row)
# [{'id': 2, 'username': 'aaa', 'pwd': '456'}, {'id': 3, 'username': 'bbb', 'pwd': '456'}]
cur.close()
conn.close()
十.索引
概念:数据库中专门用于帮助用户快速查找数据的一种数据结构。
类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置吗，然后直接获取。
索引的作用:约束和加速查找
常见的几种索引:
1.普通索引
作用:仅有一个加速查找
创建索引:
创建表时直接设定
语法:index 索引名(要添加索引的列)
create table userinfo(
id int primary key auto_increment,
name varchar(32) not null,
email varchar(64) not null,
index ix_name(name)
)
后追加的索引
语法:create index 索引的名字 on 表名(列名)
删除索引:
语法:drop index 索引的名字 on 表名
查看所以:
show index from 表名
2.唯一索引
功能：加速查找和唯一约束（可含null）
创建索引
创建表时直接设定
create table userinfo(
id int primary key auto_increment,
name varchar(32) not null,
email varchar(64) not null,
unique index ix_name(name)
)
后追加的索引
语法:create unique index 索引的名字 on 表名(列名)
删除索引:
语法:drop index 索引的名字 on 表名
3.主键索引
功能： 加速查找和唯一约束（不含null）
创建索引
创建表时直接设定
设定主键就相当于创建了主键索引
后追加的索引
alter table 表名 add primary key(列名);
也是创建主键就相当于创建了主键索引
删除主键索引
alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key;
即删除主键
4.组合索引:组合索引是将n个列组合成一个索引
创建索引:
create index 索引名 on 表名(列名1,列名2);
注:正确使用索引的情况
（1）创建索引
（2）命中索引
（3）正确使用索引