目录
简介
一、数据库、表、数据
（1）创建数据库 create database dbname charset=utf8;
（3）删除数据库 drop database dbname;
（2）创建表 create table tablename(...);
（3）修改表 alter table tablename 命令；
（6）表更新名字 rename table oldname to newname
（7）查看表的创建语句 show create table tablename
（1）单条全列插入 insert into table_name values(id,name,birthday);
（2）缺省插入 insert into table_name(字段3,字段4) values('values3','values4');
（3）批量插入 insert into table_name values(按照顺序写),(),();
（4）修改 update table_name set 字段2="新值" where 查询的条件
（5）查询所有数据 select * from table_name
（6）物理删除 delete from table_name where 删除条件
二、查询
1、基本查询 select * from table_name where 查询条件
（1）条件运算: = 、 >、 >=、 <、 <=、 != <>
（2）逻辑运算 : and、 or 、not、 和其他运算组合写的 in
2、模糊查询 select * from table_name where 字段 like "模糊值"
3、范围查询 select * from table_name where id in(1,3,5,7);
4、聚合查询 select count(*) from table_name where 查询条件
（1）统计个数：select count(*) from table_name where 查询条件
（2）列的最大值：select max(*) from table_name where 查询条件
（3）列的最小值：select min(*) from table_name where 查询条件
（4）列求和：select sumx(*) from table_name where 查询条件
（5）列求平均值：select avg(*) from table_name where 查询条件
5、分组查询 select gender as "性别",count(*) from stu group by gender;
（1）创建索引： creat index IndexName on TableName(key(length))
（2）查看索引：show index from TableName
（3）删除索引：drop index IndexName on TableName
7、判断空 select * from tablename where key is null;
10、去重 select distinct key from table_name;
（2）方法二：先建表，在约束
2、多表链接查询 （实例：对学生表、学科表、成绩表 三表的关联查询 ）
3、自关联查询（实例：将省、城市、区县 放在一个表里面实现查询）
1、创建视图 create view 视图的名字 as sql的查询语句
1、备份 mysqldump -u root -p db_name table_name > 备份文件绝对路径
2、恢复 mysql -u root -p db_name < 备份文件的绝对路径
简介
1、MySQL
MySQL 为关系型数据库(Relational Database Management System), 这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成, 如图所示的一个表格:
MySQL 为关系型数据库(Relational Database Management System), 这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成, 如图所示的一个表格:
- 表头(header): 每一列的名称;
- 列(col): 具有相同数据类型的数据的集合;
- 行(row): 每一行用来描述某条记录的具体信息;
- 值(value): 行的具体信息, 每个值必须与该列的数据类型相同;
- 键(key): 键的值在当前列中具有唯一性。
连接：mysql -u root -p; 退出：exit
C:\WINDOWS\system32>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 8.0.17 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit;
Bye
2、E-R模型
实体-联系模型（简称E-R模型）Entity-relationship model
表表关系 1对1 1 对多 多对多
3、三范式
① 列不可拆分
② 唯一标志
③ 引用主键
3、数据的完整性
key 数据类型 数据约束
mysql> create table dog(
-> id int auto_increment primary key,
-> name varchar(10) not null
-> );
Query OK, 0 rows affected (0.04 sec)
（1）数据类型
① 数字：int decimal
② 字符串： char varchar(可变长度) text
③ 日期 ： datetime
④ 布尔 ： bit
（2）数据约束
① 主键 primary key
② 非空 not null
③ 唯一 unique
④ 默认 default
⑤ 外键 foreign
一、数据库、表、数据
1、数据库的操作:
（1）创建数据库 create database dbname charset=utf8;
（2）查看所有数据库 show databases;
（3）删除数据库 drop database dbname;
（4）切换数据库 use dbname;
（5）查看当前数据库 select database();
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.17 |
+-----------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql> create database animal charset=utf8;
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> create database onel charset=utf8;
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| animal |
| information_schema |
| mysql |
| onel |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> use animal
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| animal |
+------------+
1 row in set (0.00 sec)
mysql> drop database onel;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| animal |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
2、表的操作
（1）查看所有的表 show tables;
（2）创建表 create table tablename(...);
create table tablename(
字段 类型 是否为空 是否是主键 默认值,
字段2,
字段3
);
（3）修改表 alter table tablename 命令；
增加字段：alter table tablename add 字段4 类型 是否为空 是否有默认值
删除字段：alter table tablename drop 字段1
修改字段：alter table tablename change 字段1
（4）删除表 drop table tablename;
（5）查看表结构 desc tablename
（6）表更新名字 rename table oldname to newname
（7）查看表的创建语句 show create table tablename
mysql> select database();
+------------+
| database() |
+------------+
| animal |
+------------+
1 row in set (0.00 sec)
mysql> use animal
Database changed
mysql> show tables;
Empty set (0.01 sec)
mysql> create table dog(
-> id int auto_increment primary key,
-> name varchar(10) not null
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> show tables;
+------------------+
| Tables_in_animal |
+------------------+
| dog |
+------------------+
1 row in set (0.00 sec)
mysql> alter table dog add birthday datetime;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table dog add age int;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc dog;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| birthday | datetime | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> alter table dog drop age
-> ;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc dog;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| birthday | datetime | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> rename table dog to cat;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+------------------+
| Tables_in_animal |
+------------------+
| cat |
+------------------+
1 row in set (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_animal |
+------------------+
| cat |
+------------------+
1 row in set (0.00 sec)
mysql> show create table cat;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| cat | CREATE TABLE `cat` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`birthday` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> drop table cat;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| animal |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
3、数据的操作
（1）单条全列插入 insert into table_name values(id,name,birthday);
按照表的顺序写,不可以乱 ； 主键primary 自增的操作; 全列插入的时候 写个0 站位
（2）缺省插入 insert into table_name(字段3,字段4) values('values3','values4');
（3）批量插入 insert into table_name values(按照顺序写),(),();
insert into table_name('字段3','字段4') values(按照顺序写),(),();
（4）修改 update table_name set 字段2="新值" where 查询的条件
（5）查询所有数据 select * from table_name
（6）物理删除 delete from table_name where 删除条件
（7）逻辑删除
假的删除 1.在表中增加一个 标志是否删除的字段 isdelete bit 0,1
2.如果是删除 , update修改 isdelete=1
mysql> show tables;
Empty set (0.00 sec)
mysql> create table dog(
-> id int auto_increment primary key,
-> name varchar(20) not null,
-> gender bit default 0
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+------------------+
| Tables_in_animal |
+------------------+
| dog |
+------------------+
1 row in set (0.00 sec)
1）单条全列插入
mysql> insert into dog values(1,"拉布拉多",0);
Query OK, 1 row affected (0.00 sec)
mysql> insert into dog values(2,"黄金毛",1);
Query OK, 1 row affected (0.00 sec)
2）查询所有数据 select * from table_name
mysql> select * from dog;
+----+----------+--------+
| id | name | gender |
+----+----------+--------+
| 1 | 拉布拉多 | |
| 2 | 黄金毛 | |
+----+----------+--------+
2 rows in set (0.00 sec)
3）缺省插入
mysql> insert into dog(name) values('小猎犬');
Query OK, 1 row affected (0.00 sec)
mysql> insert into dog(name,gender) values('德国牧羊犬',1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from dog;
+----+------------+--------+
| id | name | gender |
+----+------------+--------+
| 1 | 拉布拉多 | |
| 2 | 黄金毛 | |
| 3 | 小猎犬 | |
| 4 | 德国牧羊犬 | |
+----+------------+--------+
4 rows in set (0.00 sec)
4）批量插入
mysql> insert into dog values(5,'腊肠',0),(6,'约克夏',1);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into dog(name) values('泰式犬'),('贵宾犬'),('狮子狗');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from dog;
+----+------------+--------+
| id | name | gender |
+----+------------+--------+
| 1 | 拉布拉多 | |
| 2 | 黄金毛 | |
| 3 | 小猎犬 | |
| 4 | 德国牧羊犬 | |
| 5 | 腊肠 | |
| 6 | 约克夏 | |
| 7 | 泰式犬 | |
| 8 | 贵宾犬 | |
| 9 | 狮子狗 | |
+----+------------+--------+
9 rows in set (0.00 sec)
5）修改
mysql> update dog set name='泰迪' where id=8;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update dog set name='秋田犬' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dog;
+----+------------+--------+
| id | name | gender |
+----+------------+--------+
| 1 | 秋田犬 | |
| 2 | 黄金毛 | |
| 3 | 小猎犬 | |
| 4 | 德国牧羊犬 | |
| 5 | 腊肠 | |
| 6 | 约克夏 | |
| 7 | 泰式犬 | |
| 8 | 泰迪 | |
| 9 | 狮子狗 | |
+----+------------+--------+
9 rows in set (0.00 sec)
6）删除
mysql> delete from dog where id=4;
Query OK, 1 row affected (0.00 sec)
mysql> delete from dog where id=8;
Query OK, 1 row affected (0.00 sec)
mysql> select * from dog;
+----+--------+--------+
| id | name | gender |
+----+--------+--------+
| 1 | 秋田犬 | |
| 2 | 黄金毛 | |
| 3 | 小猎犬 | |
| 5 | 腊肠 | |
| 6 | 约克夏 | |
| 7 | 泰式犬 | |
| 9 | 狮子狗 | |
+----+--------+--------+
7 rows in set (0.00 sec)
7）逻辑删除
mysql> alter table dog add isdelete bit default 0;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from dog;
+----+--------+--------+----------+
| id | name | gender | isdelete |
+----+--------+--------+----------+
| 1 | 秋田犬 | | |
| 2 | 黄金毛 | | |
| 3 | 小猎犬 | | |
| 5 | 腊肠 | | |
| 6 | 约克夏 | | |
| 7 | 泰式犬 | | |
| 9 | 狮子狗 | | |
+----+--------+--------+----------+
7 rows in set (0.00 sec)
mysql> update dog set isdelete=1 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dog;
+----+--------+--------+----------+
| id | name | gender | isdelete |
+----+--------+--------+----------+
| 1 | 秋田犬 | | |
| 2 | 黄金毛 | | |
| 3 | 小猎犬 | | |
| 5 | 腊肠 | | |
| 6 | 约克夏 | | |
| 7 | 泰式犬 | | |
| 9 | 狮子狗 | | |
+----+--------+--------+----------+
7 rows in set (0.00 sec)二、查询
1、基本查询 select * from table_name where 查询条件
（1）条件运算: = 、 >、 >=、 <、 <=、 != <>
（2）逻辑运算 : and、 or 、not、 和其他运算组合写的 in
mysql> select * from stu;
+----+------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+------+---------------------+--------+----------+---------+-------+
| 1 | 小明 | 2008-01-01 00:00:00 | | | 北京 | 90 |
| 2 | 小红 | 2007-01-01 00:00:00 | | | 上海 | 80 |
| 3 | 小兰 | 2006-01-01 00:00:00 | | | 广州 | 100 |
| 4 | 小王 | 2005-01-01 00:00:00 | | | 深圳 | 20 |
| 5 | 老王 | 2009-01-01 00:00:00 | | | NULL | 30 |
| 6 | 老刘 | 2004-01-01 00:00:00 | | | NULL | 40 |
| 7 | 小丽 | 2003-01-01 00:00:00 | | | 东莞 | 50 |
| 8 | 小芳 | 2002-01-01 00:00:00 | | | 福建 | 60 |
| 9 | 小粒 | 2001-01-01 00:00:00 | | | 福州 | 70 |
+----+------+---------------------+--------+----------+---------+-------+
9 rows in set (0.00 sec)
mysql> select id from stu where id=7;
+----+
| id |
+----+
| 7 |
+----+
1 row in set (0.00 sec)
mysql> select * from stu where id=7;
+----+------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+------+---------------------+--------+----------+---------+-------+
| 7 | 小丽 | 2003-01-01 00:00:00 | | | 东莞 | 50 |
+----+------+---------------------+--------+----------+---------+-------+
1 row in set (0.00 sec)
mysql> select * from stu where id>7;
+----+------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+------+---------------------+--------+----------+---------+-------+
| 8 | 小芳 | 2002-01-01 00:00:00 | | | 福建 | 60 |
| 9 | 小粒 | 2001-01-01 00:00:00 | | | 福州 | 70 |
+----+------+---------------------+--------+----------+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from stu where id!=7;
+----+------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+------+---------------------+--------+----------+---------+-------+
| 1 | 小明 | 2008-01-01 00:00:00 | | | 北京 | 90 |
| 2 | 小红 | 2007-01-01 00:00:00 | | | 上海 | 80 |
| 3 | 小兰 | 2006-01-01 00:00:00 | | | 广州 | 100 |
| 4 | 小王 | 2005-01-01 00:00:00 | | | 深圳 | 20 |
| 5 | 老王 | 2009-01-01 00:00:00 | | | NULL | 30 |
| 6 | 老刘 | 2004-01-01 00:00:00 | | | NULL | 40 |
| 8 | 小芳 | 2002-01-01 00:00:00 | | | 福建 | 60 |
| 9 | 小粒 | 2001-01-01 00:00:00 | | | 福州 | 70 |
+----+------+---------------------+--------+----------+---------+-------+
8 rows in set (0.00 sec)
mysql> select name,address from stu where id!=7;
+------+---------+
| name | address |
+------+---------+
| 小明 | 北京 |
| 小红 | 上海 |
| 小兰 | 广州 |
| 小王 | 深圳 |
| 老王 | NULL |
| 老刘 | NULL |
| 小芳 | 福建 |
| 小粒 | 福州 |
+------+---------+
8 rows in set (0.00 sec)
mysql> select name,address from stu where id<>7;
+------+---------+
| name | address |
+------+---------+
| 小明 | 北京 |
| 小红 | 上海 |
| 小兰 | 广州 |
| 小王 | 深圳 |
| 老王 | NULL |
| 老刘 | NULL |
| 小芳 | 福建 |
| 小粒 | 福州 |
+------+---------+
8 rows in set (0.00 sec)
mysql> select name from stu where name<>'老王';
+------+
| name |
+------+
| 小明 |
| 小红 |
| 小兰 |
| 小王 |
| 老刘 |
| 小丽 |
| 小芳 |
| 小粒 |
+------+
8 rows in set (0.00 sec)
mysql> select name from stu where gender=1;
+------+
| name |
+------+
| 小红 |
| 小兰 |
| 小丽 |
| 小芳 |
+------+
4 rows in set (0.00 sec)
mysql> select * from stu where gender=1 and id>4;
+----+------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+------+---------------------+--------+----------+---------+-------+
| 7 | 小丽 | 2003-01-01 00:00:00 | | | 东莞 | 50 |
| 8 | 小芳 | 2002-01-01 00:00:00 | | | 福建 | 60 |
+----+------+---------------------+--------+----------+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from stu where gender=0 or id<4;
+----+------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+------+---------------------+--------+----------+---------+-------+
| 1 | 小明 | 2008-01-01 00:00:00 | | | 北京 | 90 |
| 2 | 小红 | 2007-01-01 00:00:00 | | | 上海 | 80 |
| 3 | 小兰 | 2006-01-01 00:00:00 | | | 广州 | 100 |
| 4 | 小王 | 2005-01-01 00:00:00 | | | 深圳 | 20 |
| 5 | 老王 | 2009-01-01 00:00:00 | | | NULL | 30 |
| 6 | 老刘 | 2004-01-01 00:00:00 | | | NULL | 40 |
| 9 | 小粒 | 2001-01-01 00:00:00 | | | 福州 | 70 |
+----+------+---------------------+--------+----------+---------+-------+
7 rows in set (0.00 sec)2、模糊查询 select * from table_name where 字段 like "模糊值"
类似于正则表达式查询，%表示 任意多个字符；_表示任意一个字符
mysql> select * from stu where name like '小%';
+----+------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+------+---------------------+--------+----------+---------+-------+
| 1 | 小明 | 2008-01-01 00:00:00 | | | 北京 | 90 |
| 2 | 小红 | 2007-01-01 00:00:00 | | | 上海 | 80 |
| 3 | 小兰 | 2006-01-01 00:00:00 | | | 广州 | 100 |
| 4 | 小王 | 2005-01-01 00:00:00 | | | 深圳 | 20 |
| 7 | 小丽 | 2003-01-01 00:00:00 | | | 东莞 | 50 |
| 8 | 小芳 | 2002-01-01 00:00:00 | | | 福建 | 60 |
| 9 | 小粒 | 2001-01-01 00:00:00 | | | 福州 | 70 |
+----+------+---------------------+--------+----------+---------+-------+
7 rows in set (0.00 sec)
mysql> insert into stu values(0,'张三丰','1000-01-01',0,0,'武当山',100);
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu where name like '%丰';
+----+--------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+--------+---------------------+--------+----------+---------+-------+
| 10 | 张三丰 | 1000-01-01 00:00:00 | | | 武当山 | 100 |
+----+--------+---------------------+--------+----------+---------+-------+
1 row in set (0.00 sec)
mysql> select * from stu where name like '%三%';
+----+--------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+--------+---------------------+--------+----------+---------+-------+
| 10 | 张三丰 | 1000-01-01 00:00:00 | | | 武当山 | 100 |
+----+--------+---------------------+--------+----------+---------+-------+
1 row in set (0.00 sec)
mysql> select * from stu where name like '张%' or name like '%王';
+----+--------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+--------+---------------------+--------+----------+---------+-------+
| 4 | 小王 | 2005-01-01 00:00:00 | | | 深圳 | 20 |
| 5 | 老王 | 2009-01-01 00:00:00 | | | NULL | 30 |
| 10 | 张三丰 | 1000-01-01 00:00:00 | | | 武当山 | 100 |
+----+--------+---------------------+--------+----------+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from stu where name like '张__';
+----+--------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+--------+---------------------+--------+----------+---------+-------+
| 10 | 张三丰 | 1000-01-01 00:00:00 | | | 武当山 | 100 |
+----+--------+---------------------+--------+----------+---------+-------+
1 row in set (0.00 sec)
mysql> select * from stu where name like '张_';
Empty set (0.00 sec)
3、范围查询 select * from table_name where id in(1,3,5,7);
in 、not in、between
mysql> select * from stu where id in(1,3,5,7);
+----+------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+------+---------------------+--------+----------+---------+-------+
| 1 | 小明 | 2008-01-01 00:00:00 | | | 北京 | 90 |
| 3 | 小兰 | 2006-01-01 00:00:00 | | | 广州 | 100 |
| 5 | 老王 | 2009-01-01 00:00:00 | | | NULL | 30 |
| 7 | 小丽 | 2003-01-01 00:00:00 | | | 东莞 | 50 |
+----+------+---------------------+--------+----------+---------+-------+
4 rows in set (0.04 sec)
mysql> select * from stu where id not in(1,3,5,7);
+----+--------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+--------+---------------------+--------+----------+---------+-------+
| 2 | 小红 | 2007-01-01 00:00:00 | | | 上海 | 80 |
| 4 | 小王 | 2005-01-01 00:00:00 | | | 深圳 | 20 |
| 6 | 老刘 | 2004-01-01 00:00:00 | | | NULL | 40 |
| 8 | 小芳 | 2002-01-01 00:00:00 | | | 福建 | 60 |
| 9 | 小粒 | 2001-01-01 00:00:00 | | | 福州 | 70 |
| 10 | 张三丰 | 1000-01-01 00:00:00 | | | 武当山 | 100 |
+----+--------+---------------------+--------+----------+---------+-------+
6 rows in set (0.00 sec)
mysql> select * from stu where id between 1 and 5;
+----+------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+------+---------------------+--------+----------+---------+-------+
| 1 | 小明 | 2008-01-01 00:00:00 | | | 北京 | 90 |
| 2 | 小红 | 2007-01-01 00:00:00 | | | 上海 | 80 |
| 3 | 小兰 | 2006-01-01 00:00:00 | | | 广州 | 100 |
| 4 | 小王 | 2005-01-01 00:00:00 | | | 深圳 | 20 |
| 5 | 老王 | 2009-01-01 00:00:00 | | | NULL | 30 |
+----+------+---------------------+--------+----------+---------+-------+
5 rows in set (0.00 sec)
mysql> select * from stu where id between 1 and 5 and gender=1;
+----+------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+------+---------------------+--------+----------+---------+-------+
| 2 | 小红 | 2007-01-01 00:00:00 | | | 上海 | 80 |
| 3 | 小兰 | 2006-01-01 00:00:00 | | | 广州 | 100 |
+----+------+---------------------+--------+----------+---------+-------+
2 rows in set (0.00 sec)
4、聚合查询 select count(*) from table_name where 查询条件
（1）统计个数：select count(*) from table_name where 查询条件
（2）列的最大值：select max(*) from table_name where 查询条件
（3）列的最小值：select min(*) from table_name where 查询条件
（4）列求和：select sumx(*) from table_name where 查询条件
（5）列求平均值：select avg(*) from table_name where 查询条件
mysql> select count(*) from stu;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
mysql> select count(id) from stu;
+-----------+
| count(id) |
+-----------+
| 10 |
+-----------+
1 row in set (0.01 sec)
mysql> select count(*) from stu where gender=0;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
mysql> select max(score) from stu;
+------------+
| max(score) |
+------------+
| 100 |
+------------+
1 row in set (0.00 sec)
mysql> select min(score) from stu;
+------------+
| min(score) |
+------------+
| 20 |
+------------+
1 row in set (0.00 sec)
mysql> select sum(score) from stu where gender=1;
+------------+
| sum(score) |
+------------+
| 290 |
+------------+
1 row in set (0.00 sec)
mysql> select avg(score) from stu where gender=0;
+------------+
| avg(score) |
+------------+
| 58.3333 |
+------------+
1 row in set (0.00 sec)
5、分组查询 select gender as "性别",count(*) from stu group by gender;
男生女生的总数：select gender as "性别",count(*) from stu group by gender;
男生的总人数：
① select count(*) from stu where gender=0;
② select gender as "性别",count(*) from stu group by gender having gender=0;
“having ”：分组之后 继续筛选，分组结束之后 group by 结果进行筛选
“where”：原始数据筛选
mysql> select gender,count(*) from stu group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| | 6 |
| | 4 |
+--------+----------+
2 rows in set (0.00 sec)
mysql> select gender as 性别,count(*) from stu group by gender;
+------+----------+
| 性别 | count(*) |
+------+----------+
| | 6 |
| | 4 |
+------+----------+
2 rows in set (0.00 sec)
mysql> select score as 分数,count(*) from stu group by score;
+------+----------+
| 分数 | count(*) |
+------+----------+
| 90 | 1 |
| 80 | 1 |
| 100 | 2 |
| 20 | 1 |
| 30 | 1 |
| 40 | 1 |
| 50 | 1 |
| 60 | 1 |
| 70 | 1 |
+------+----------+
9 rows in set (0.00 sec)
mysql> select count(*) from stu where gender=0;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
mysql> select gender as 性别,count(*) from stu group by gender;
+------+----------+
| 性别 | count(*) |
+------+----------+
| | 6 |
| | 4 |
+------+----------+
2 rows in set (0.00 sec)
mysql> select gender as 性别,count(*) from stu group by gender
-> ;
+------+----------+
| 性别 | count(*) |
+------+----------+
| | 6 |
| | 4 |
+------+----------+
2 rows in set (0.00 sec)
mysql> select gender as 性别,count(*)
-> from stu
-> group by gender
-> having gender=0;
+------+----------+
| 性别 | count(*) |
+------+----------+
| | 6 |
+------+----------+
1 row in set (0.00 sec)6、索引查询
索引查询，查询效率最高
1、提高效率的方法：
（1）数据类型越小越好，如int
（2）能用整型最好
（3）不适用NULL，用0，“ ”,特殊符号代替
2、验证执行时间流程：
（1）set profiling=1
（2）查询内容
（3）show profiles
3、索引查询
（1）创建索引： creat index IndexName on TableName(key(length))
（2）查看索引：show index from TableName
（3）删除索引：drop index IndexName on TableName
mysql> select * from areas where title="贵州省";
+--------+--------+------+
| id | title | pid |
+--------+--------+------+
| 520000 | 贵州省 | NULL |
+--------+--------+------+
1 row in set (0.00 sec)
mysql> show profiles;
+----------+------------+-----------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------+
| 1 | 0.00196375 | select * from areas where title="?????" |
+----------+------------+-----------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> create index titleIndex on areas(title(50));
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from areas where title="贵州省";
+--------+--------+------+
| id | title | pid |
+--------+--------+------+
| 520000 | 贵州省 | NULL |
+--------+--------+------+
1 row in set (0.00 sec)
mysql> show profiles;
+----------+------------+---------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------+
| 1 | 0.00196375 | select * from areas where title="?????" |
| 2 | 0.07919425 | create index titleIndex on areas(title(50)) |
| 3 | 0.00048900 | select * from areas where title="?????" |
+----------+------------+---------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
mysql> show index from areas;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| areas | 0 | PRIMARY | 1 | id | A | 3258 | NULL | NULL | | BTREE | | | YES | NULL |
| areas | 1 | pid | 1 | pid | A | 372 | NULL | NULL | YES | BTREE | | | YES | NULL |
| areas | 1 | titleIndex | 1 | title | A | 3205 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.02 sec)
mysql> drop index titleIndex on areas;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 07、判断空 select * from tablename where key is null;
null 、 not null
查询符号的优先级：
（）> not > 条件运算 > and > or
mysql> select * from stu where address is null;
+----+------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+------+---------------------+--------+----------+---------+-------+
| 5 | 老王 | 2009-01-01 00:00:00 | | | NULL | 30 |
| 6 | 老刘 | 2004-01-01 00:00:00 | | | NULL | 40 |
+----+------+---------------------+--------+----------+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from stu where address is not null;
+----+--------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+--------+---------------------+--------+----------+---------+-------+
| 1 | 小明 | 2008-01-01 00:00:00 | | | 北京 | 90 |
| 2 | 小红 | 2007-01-01 00:00:00 | | | 上海 | 80 |
| 3 | 小兰 | 2006-01-01 00:00:00 | | | 广州 | 100 |
| 4 | 小王 | 2005-01-01 00:00:00 | | | 深圳 | 20 |
| 7 | 小丽 | 2003-01-01 00:00:00 | | | 东莞 | 50 |
| 8 | 小芳 | 2002-01-01 00:00:00 | | | 福建 | 60 |
| 9 | 小粒 | 2001-01-01 00:00:00 | | | 福州 | 70 |
| 10 | 张三丰 | 1000-01-01 00:00:00 | | | 武当山 | 100 |
+----+--------+---------------------+--------+----------+---------+-------+
8 rows in set (0.00 sec)
mysql> select * from stu where address is not null and gender=1;
+----+------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+------+---------------------+--------+----------+---------+-------+
| 2 | 小红 | 2007-01-01 00:00:00 | | | 上海 | 80 |
| 3 | 小兰 | 2006-01-01 00:00:00 | | | 广州 | 100 |
| 7 | 小丽 | 2003-01-01 00:00:00 | | | 东莞 | 50 |
| 8 | 小芳 | 2002-01-01 00:00:00 | | | 福建 | 60 |
+----+------+---------------------+--------+----------+---------+-------+
4 rows in set (0.00 sec)8、排序 order by 字段 desc;
order by 字段 asc : 升序 （默认）
order by 字段 desc：降序
mysql> select * from stu
-> where isdelete=0
-> order by score desc;
+----+--------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+--------+---------------------+--------+----------+---------+-------+
| 3 | 小兰 | 2006-01-01 00:00:00 | | | 广州 | 100 |
| 10 | 张三丰 | 1000-01-01 00:00:00 | | | 武当山 | 100 |
| 1 | 小明 | 2008-01-01 00:00:00 | | | 北京 | 90 |
| 2 | 小红 | 2007-01-01 00:00:00 | | | 上海 | 80 |
| 9 | 小粒 | 2001-01-01 00:00:00 | | | 福州 | 70 |
| 8 | 小芳 | 2002-01-01 00:00:00 | | | 福建 | 60 |
| 7 | 小丽 | 2003-01-01 00:00:00 | | | 东莞 | 50 |
| 6 | 老刘 | 2004-01-01 00:00:00 | | | NULL | 40 |
| 5 | 老王 | 2009-01-01 00:00:00 | | | NULL | 30 |
| 4 | 小王 | 2005-01-01 00:00:00 | | | 深圳 | 20 |
+----+--------+---------------------+--------+----------+---------+-------+
10 rows in set (0.00 sec)
mysql> select * from stu
-> where gender=0
-> order by id desc;
+----+--------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+--------+---------------------+--------+----------+---------+-------+
| 10 | 张三丰 | 1000-01-01 00:00:00 | | | 武当山 | 100 |
| 9 | 小粒 | 2001-01-01 00:00:00 | | | 福州 | 70 |
| 6 | 老刘 | 2004-01-01 00:00:00 | | | NULL | 40 |
| 5 | 老王 | 2009-01-01 00:00:00 | | | NULL | 30 |
| 4 | 小王 | 2005-01-01 00:00:00 | | | 深圳 | 20 |
| 1 | 小明 | 2008-01-01 00:00:00 | | | 北京 | 90 |
+----+--------+---------------------+--------+----------+---------+-------+
6 rows in set (0.00 sec)
mysql> select * from stu
-> where gender=0;
+----+--------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+--------+---------------------+--------+----------+---------+-------+
| 1 | 小明 | 2008-01-01 00:00:00 | | | 北京 | 90 |
| 4 | 小王 | 2005-01-01 00:00:00 | | | 深圳 | 20 |
| 5 | 老王 | 2009-01-01 00:00:00 | | | NULL | 30 |
| 6 | 老刘 | 2004-01-01 00:00:00 | | | NULL | 40 |
| 9 | 小粒 | 2001-01-01 00:00:00 | | | 福州 | 70 |
| 10 | 张三丰 | 1000-01-01 00:00:00 | | | 武当山 | 100 |
+----+--------+---------------------+--------+----------+---------+-------+
6 rows in set (0.00 sec)
9、分页 limit start,count
在实际开发中 涉及到的是 数学计算问题
mysql> select * from stu
-> limit 0,3;
+----+------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+------+---------------------+--------+----------+---------+-------+
| 1 | 小明 | 2008-01-01 00:00:00 | | | 北京 | 90 |
| 2 | 小红 | 2007-01-01 00:00:00 | | | 上海 | 80 |
| 3 | 小兰 | 2006-01-01 00:00:00 | | | 广州 | 100 |
+----+------+---------------------+--------+----------+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from stu
-> limit 3,2;
+----+------+---------------------+--------+----------+---------+-------+
| id | name | birthday | gender | isdelete | address | score |
+----+------+---------------------+--------+----------+---------+-------+
| 4 | 小王 | 2005-01-01 00:00:00 | | | 深圳 | 20 |
| 5 | 老王 | 2009-01-01 00:00:00 | | | NULL | 30 |
+----+------+---------------------+--------+----------+---------+-------+
2 rows in set (0.00 sec)
10、去重 select distinct key from table_name;
去重 列 中的内容
mysql> select address from stu;
+---------+
| address |
+---------+
| 北京 |
| 上海 |
| 广州 |
| 深圳 |
| NULL |
| NULL |
| 东莞 |
| 福建 |
| 福州 |
| 武当山 |
+---------+
10 rows in set (0.00 sec)
mysql> select distinct address from stu;
+---------+
| address |
+---------+
| 北京 |
| 上海 |
| 广州 |
| 深圳 |
| NULL |
| 东莞 |
| 福建 |
| 福州 |
| 武当山 |
+---------+
9 rows in set (0.00 sec)
三、关联
1、E-R模型 （实例：学生表、学科表、成绩表的关系）
思路：成绩表scores的stuid subid ，分别通过外键foreign，链接学生表stu、学科表subjects的id
（0）E-R模型 简介
① 实体-联系模型（简称E-R模型）Entity-relationship model
② 表表关系 1对1 1 对多 多对多 （不建议表和表之间建立 循环的关系闭合）
③ 多表约束 产生关系，如果有删除修改操作, 对应的关联表数据 就会报错，通过逻辑删除可以解决这个问题
（1）方法一：建表时，增加两个外键
foreign key(stuid) references stu(id),
foreign key(subid) references subjects(id),
mysql> show tables;
+------------------+
| Tables_in_animal |
+------------------+
| stu |
| subjects |
+------------------+
2 rows in set (0.01 sec)
mysql> create table scores(
-> id int primary key auto_increment,
-> stuid int,
-> subid int,
-> score decimal(5,2),
-> foreign key(stuid) references stu(id),
-> foreign key(subid) references subjects(id)
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> insert into scores values(0,1,1,80),(0,2,2,60),(0,2,3,70),(0,3,1,90),(0,4,4,60),(0,5,2,75);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from scores;
+----+-------+-------+-------+
| id | stuid | subid | score |
+----+-------+-------+-------+
| 1 | 1 | 1 | 80.00 |
| 2 | 2 | 2 | 60.00 |
| 3 | 2 | 3 | 70.00 |
| 4 | 3 | 1 | 90.00 |
| 5 | 4 | 4 | 60.00 |
| 6 | 5 | 2 | 75.00 |
+----+-------+-------+-------+
6 rows in set (0.00 sec)
② 方法二：先建表，在约束
alter table scores add contraint stu foreign key (stuid) references stu(id) on delete cascade
cascade 级联关系，一删除 有关联的表 全部删除
restrict 限制关系
set null 外键制空
no action
2、多表链接查询 （实例：对学生表、学科表、成绩表 三表的关联查询 ）
（1）多表查询 inner join
查询学生的名字 学科对应的成绩
（2）A左链接B表 left join
查询结果 以A为基准操作
（3）A 右链接B表 right join
查询结果 以B为基准
mysql> select stu.sname,subjects.stitle,scores.score
-> from scores
-> inner join left stu on scores.stuid=stu.id
-> inner join subjects on scores.subid=subjects.id;
+------+-------+-------+
| name | title | score |
+------+-------+-------+
| 小明 | 语文 | 80.00 |
| 小兰 | 语文 | 90.00 |
| 小红 | 数学 | 60.00 |
| 老王 | 数学 | 75.00 |
| 小红 | 英语 | 70.00 |
| 小王 | 科学 | 60.00 |
+------+-------+-------+
6 rows in set (0.00 sec)
mysql> select stu.name,subjects.title,scores.score
-> from scores
-> left join stu on scores.stuid=stu.id
-> left join subjects on scores.subid=subjects.id;
+------+-------+-------+
| name | title | score |
+------+-------+-------+
| 小明 | 语文 | 80.00 |
| 小红 | 数学 | 60.00 |
| 小红 | 英语 | 70.00 |
| 小兰 | 语文 | 90.00 |
| 小王 | 科学 | 60.00 |
| 老王 | 数学 | 75.00 |
+------+-------+-------+
6 rows in set (0.00 sec)
mysql> select stu.name,avg(scores.score)
-> from scores
-> inner join stu on scores.stuid=stu.id
-> group by stu.name;
+------+-------------------+
| name | avg(scores.score) |
+------+-------------------+
| 小明 | 80.000000 |
| 小红 | 65.000000 |
| 小兰 | 90.000000 |
| 小王 | 60.000000 |
| 老王 | 75.000000 |
+------+-------------------+
5 rows in set (0.01 sec)
mysql> select stu.name,sum(scores.score)
-> from scores
-> inner join stu on scores.stuid=stu.id
-> where stu.gender=0
-> group by stu.name;
+------+-------------------+
| name | sum(scores.score) |
+------+-------------------+
| 小明 | 80.00 |
| 小王 | 60.00 |
| 老王 | 75.00 |
+------+-------------------+
3 rows in set (0.00 sec)
mysql> select stu.name,max(scores.score)
-> from scores
-> inner join stu on scores.stuid=stu.id
-> where stu.id<5
-> group by stu.name;
+------+-------------------+
| name | max(scores.score) |
+------+-------------------+
| 小明 | 80.00 |
| 小红 | 70.00 |
| 小兰 | 90.00 |
| 小王 | 60.00 |
+------+-------------------+
4 rows in set (0.00 sec)
mysql> select * from
-> scores
-> left join stu on scores.stuid=stu.id;
+----+-------+-------+-------+------+------+---------------------+--------+----------+---------+-------+
| id | stuid | subid | score | id | name | birthday | gender | isdelete | address | score |
+----+-------+-------+-------+------+------+---------------------+--------+----------+---------+-------+
| 1 | 1 | 1 | 80.00 | 1 | 小明 | 2008-01-01 00:00:00 | | | 北京 | 90 |
| 2 | 2 | 2 | 60.00 | 2 | 小红 | 2007-01-01 00:00:00 | | | 上海 | 80 |
| 3 | 2 | 3 | 70.00 | 2 | 小红 | 2007-01-01 00:00:00 | | | 上海 | 80 |
| 4 | 3 | 1 | 90.00 | 3 | 小兰 | 2006-01-01 00:00:00 | | | 广州 | 100 |
| 5 | 4 | 4 | 60.00 | 4 | 小王 | 2005-01-01 00:00:00 | | | 深圳 | 20 |
| 6 | 5 | 2 | 75.00 | 5 | 老王 | 2009-01-01 00:00:00 | | | NULL | 30 |
+----+-------+-------+-------+------+------+---------------------+--------+----------+---------+-------+
6 rows in set (0.03 sec)
mysql> select * from
-> scores
-> right join stu on scores.stuid=stu.id;
+------+-------+-------+-------+----+--------+---------------------+--------+----------+---------+-------+
| id | stuid | subid | score | id | name | birthday | gender | isdelete | address | score |
+------+-------+-------+-------+----+--------+---------------------+--------+----------+---------+-------+
| 1 | 1 | 1 | 80.00 | 1 | 小明 | 2008-01-01 00:00:00 | | | 北京 | 90 |
| 2 | 2 | 2 | 60.00 | 2 | 小红 | 2007-01-01 00:00:00 | | | 上海 | 80 |
| 3 | 2 | 3 | 70.00 | 2 | 小红 | 2007-01-01 00:00:00 | | | 上海 | 80 |
| 4 | 3 | 1 | 90.00 | 3 | 小兰 | 2006-01-01 00:00:00 | | | 广州 | 100 |
| 5 | 4 | 4 | 60.00 | 4 | 小王 | 2005-01-01 00:00:00 | | | 深圳 | 20 |
| 6 | 5 | 2 | 75.00 | 5 | 老王 | 2009-01-01 00:00:00 | | | NULL | 30 |
| NULL | NULL | NULL | NULL | 6 | 老刘 | 2004-01-01 00:00:00 | | | NULL | 40 |
| NULL | NULL | NULL | NULL | 7 | 小丽 | 2003-01-01 00:00:00 | | | 东莞 | 50 |
| NULL | NULL | NULL | NULL | 8 | 小芳 | 2002-01-01 00:00:00 | | | 福建 | 60 |
| NULL | NULL | NULL | NULL | 9 | 小粒 | 2001-01-01 00:00:00 | | | 福州 | 70 |
| NULL | NULL | NULL | NULL | 10 | 张三丰 | 1000-01-01 00:00:00 | | | 武当山 | 100 |
+------+-------+-------+-------+----+--------+---------------------+--------+----------+---------+-------+
11 rows in set (0.00 sec)
3、自关联查询（实例：将省、城市、区县 放在一个表里面实现查询）
当数据量不大时，采用自关联
案例：将省、城市、区县 放在一个表里面实现数据存储和查询
（1）查询省份名 pid is null
（2）查询某一省份的城市 城市 pid = id
（3）查询某一城市的区县 区县 pid =id
mysql> desc areas
-> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| title | varchar(50) | YES | | NULL | |
| pid | int(11) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from areas limit 0,10;
+--------+----------+--------+
| id | title | pid |
+--------+----------+--------+
| 110000 | 北京市 | NULL |
| 110100 | 北京市 | 110000 |
| 110101 | 东城区 | 110100 |
| 110102 | 西城区 | 110100 |
| 110103 | 朝阳区 | 110100 |
| 110104 | 丰台区 | 110100 |
| 110105 | 石景山区 | 110100 |
| 110106 | 海淀区 | 110100 |
| 110107 | 门头沟区 | 110100 |
| 110108 | 房山区 | 110100 |
+--------+----------+--------+
10 rows in set (0.00 sec)
mysql> select * from areas where pid is null;
+--------+------------------+------+
| id | title | pid |
+--------+------------------+------+
| 110000 | 北京市 | NULL |
| 120000 | 天津市 | NULL |
| 130000 | 河北省 | NULL |
| 140000 | 山西省 | NULL |
| 150000 | 内蒙古自治区 | NULL |
| 210000 | 辽宁省 | NULL |
| 220000 | 吉林省 | NULL |
| 230000 | 黑龙江省 | NULL |
| 310000 | 上海市 | NULL |
| 320000 | 江苏省 | NULL |
| 330000 | 浙江省 | NULL |
| 340000 | 安徽省 | NULL |
| 341402 | 居巢区 | NULL |
| 350000 | 福建省 | NULL |
| 360000 | 江西省 | NULL |
| 370000 | 山东省 | NULL |
| 410000 | 河南省 | NULL |
| 420000 | 湖北省 | NULL |
| 430000 | 湖南省 | NULL |
| 440000 | 广东省 | NULL |
| 450000 | 广西壮族自治区 | NULL |
| 460000 | 海南省 | NULL |
| 500000 | 重庆市 | NULL |
| 510000 | 四川省 | NULL |
| 520000 | 贵州省 | NULL |
| 530000 | 云南省 | NULL |
| 540000 | 西藏自治区 | NULL |
| 610000 | 陕西省 | NULL |
| 620000 | 甘肃省 | NULL |
| 630000 | 青海省 | NULL |
| 640000 | 宁夏回族自治区 | NULL |
| 650000 | 新疆维吾尔自治区 | NULL |
| 990000 | 新疆建设兵团 | NULL |
+--------+------------------+------+
33 rows in set (0.00 sec)
mysql> select city.* from areas as city
-> inner join areas as province on city.pid=province.id
-> where province.title="陕西省";
+--------+------------+--------+
| id | title | pid |
+--------+------------+--------+
| 610100 | 西安市 | 610000 |
| 610200 | 铜川市 | 610000 |
| 610300 | 宝鸡市 | 610000 |
| 610400 | 咸阳市 | 610000 |
| 610500 | 渭南市 | 610000 |
| 610600 | 延安市 | 610000 |
| 610700 | 汉中市 | 610000 |
| 610800 | 榆林市 | 610000 |
| 610900 | 安康市 | 610000 |
| 611000 | 商洛市 | 610000 |
| 611100 | 杨凌示范区 | 610000 |
+--------+------------+--------+
11 rows in set (0.00 sec)
mysql> select district.* from areas as district
-> inner join areas as city on city.id=district.pid
-> where city.title="西安市";
+--------+--------+--------+
| id | title | pid |
+--------+--------+--------+
| 610101 | 长安区 | 610100 |
| 610102 | 新城区 | 610100 |
| 610103 | 碑林区 | 610100 |
| 610104 | 莲湖区 | 610100 |
| 610111 | 灞桥区 | 610100 |
| 610112 | 未央区 | 610100 |
| 610113 | 雁塔区 | 610100 |
| 610114 | 阎良区 | 610100 |
| 610115 | 临潼区 | 610100 |
| 610122 | 蓝田县 | 610100 |
| 610124 | 周至县 | 610100 |
| 610125 | 户县 | 610100 |
| 610126 | 高陵县 | 610100 |
+--------+--------+--------+
13 rows in set (0.00 sec)
四、视图
对于复杂查询进行封装，便于调用
1、创建视图 create view 视图的名字 as sql的查询语句
2、查看视图 结果 select * from 视图名字
3、删除视图 drop view 视图名字
mysql> create view studentsscore as
-> select stu.name,scores.score from scores
-> inner join stu on scores.stuid=stu.id;
Query OK, 0 rows affected (0.01 sec)
mysql> create view zuidazhi as
-> select stu.name,max(scores.score)
-> from scores
-> inner join stu on scores.stuid=stu.id
-> where stu.id<5
-> group by stu.name;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables
-> ;
+------------------+
| Tables_in_animal |
+------------------+
| scores |
| stu |
| studentsscore |
| subjects |
| zuidazhi |
+------------------+
5 rows in set (0.00 sec)
mysql> select * from studentsscore;
+------+-------+
| name | score |
+------+-------+
| 小明 | 80.00 |
| 小红 | 60.00 |
| 小红 | 70.00 |
| 小兰 | 90.00 |
| 小王 | 60.00 |
| 老王 | 75.00 |
+------+-------+
6 rows in set (0.00 sec)
mysql> select * from zuidazhi;
+------+-------------------+
| name | max(scores.score) |
+------+-------------------+
| 小明 | 80.00 |
| 小红 | 70.00 |
| 小兰 | 90.00 |
| 小王 | 60.00 |
+------+-------------------+
4 rows in set (0.00 sec)
mysql> drop table zuidazhi;
ERROR 1051 (42S02): Unknown table 'animal.zuidazhi'
mysql> drop view zuidazhi;;
Query OK, 0 rows affected (0.01 sec)五、事务
1、事务四大特性 ACID
（1）原子性A
（2）一致性C
（3）隔离性I
（4）持久性D
2、mysql支持事务 要求的类型：innodb,ddb
3、事务流程
（1）开启事务 begin
（2）提交事务 commit
（3）事务回滚 rollback
sell1: 开启事务，相当于先写入缓存区，缓存区可以看见插入的数据
mysql> select * from subjects;
+----+-------+
| id | title |
+----+-------+
| 1 | 语文 |
| 2 | 数学 |
| 3 | 英语 |
| 4 | 科学 |
| 5 | AI |
+----+-------+
5 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into subjects values(0,'人工智能');
Query OK, 1 row affected (0.00 sec)
mysql> select * from subjects;
+----+----------+
| id | title |
+----+----------+
| 1 | 语文 |
| 2 | 数学 |
| 3 | 英语 |
| 4 | 科学 |
| 5 | AI |
| 6 | 人工智能 |
+----+----------+
6 rows in set (0.00 sec)c)sell1中事务插入了“人工智能”，sell2真实数据库中还没有存在，需要提交
sell2：
mysql> select * from subjects;
+----+-------+
| id | title |
+----+-------+
| 1 | 语文 |
| 2 | 数学 |
| 3 | 英语 |
| 4 | 科学 |
| 5 | AI |
+----+-------+
5 rows in set (0.00 sec)sell1：提交事务后，写入数据库
mysql> commit;
Query OK, 0 rows affected (0.00 sec)sell2:
mysql> select * from subjects;
+----+----------+
| id | title |
+----+----------+
| 1 | 语文 |
| 2 | 数学 |
| 3 | 英语 |
| 4 | 科学 |
| 5 | AI |
| 6 | 人工智能 |
+----+----------+
6 rows in set (0.00 sec)sell1：回滚事务，相当于撤销缓存区中上一步的操作
mysql> insert into subjects values(0,'电路');
Query OK, 1 row affected (0.00 sec)
mysql> select * from subjects;
+----+----------+
| id | title |
+----+----------+
| 1 | 语文 |
| 2 | 数学 |
| 3 | 英语 |
| 4 | 科学 |
| 5 | AI |
| 6 | 人工智能 |
| 7 | 电路 |
+----+----------+
7 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from subjects;
+----+----------+
| id | title |
+----+----------+
| 1 | 语文 |
| 2 | 数学 |
| 3 | 英语 |
| 4 | 科学 |
| 5 | AI |
| 6 | 人工智能 |
+----+----------+
6 rows in set (0.00 sec)六、与Python交互
1、connection对象
（1）建立和数据库的链接
host="localhost",
port=3306,
db='animal',
user='root',
passwd="123456",
charset='utf8'
（2）提交事务 conn.commit()
（3） 关闭游标 cur.close()
（4）关闭链接 conn.close(）
2.Cursor对象
（1）execute()：执行MySQL的语句
（2）fetchone()：一行数据一个元祖
（3）fetchall()：一个大元组包含多个 小元祖
# 安装 pip install pymysql
import pymysql
try:
'''一.链接 数据库 链接对象 connection() '''
conn = pymysql.Connect(
host="localhost",
port=3306,
db='animal',
user='root',
passwd="123456",
charset='utf8'
)
'''二. 创建 游标对象 cursor() '''
cur = conn.cursor()
'''1、增加一条数据 科目表--GO语言'''
# insert_sub = 'insert into subjects values(0,"GO语言")'
# result = cur.execute(insert_sub)
# print(result)
'''2、修改'''
update_sub = 'update subjects set title="区块链" where id=7'
result = cur.execute(update_sub)
'''3、删除'''
# delete_sub = 'delete from stu where id=8'
# result = cur.execute(delete_sub)
# select_sub = 'select * from subjects where id=1'
# cur.execute(select_sub)
# result = cur.fetchall()
# result = cur.fetchone()
# print(result)
# for res in result:
#
# print(result)
'''三、提交事务'''
conn.commit()
'''四、关闭游标'''
cur.close()
'''五、关闭链接'''
conn.close()
except Exception as e:
print(e)
六、mysql的备份和恢复
1、备份 mysqldump -u root -p db_name table_name > 备份文件绝对路径
mysqldump -u root -p 123456 animal stu > D:\beifen
2、恢复 mysql -u root -p db_name < 备份文件的绝对路径
扫一扫
25万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
