MySQL数据库操作——增删查改

最新推荐文章于 2023-12-15 12:28:04 发布

WAREY~

最新推荐文章于 2023-12-15 12:28:04 发布

阅读量725

点赞数 1

文章标签： mysql

本文链接：https://blog.csdn.net/qq_43744773/article/details/107158788

版权

MySQL操作

1. 数据库基本操作

启动

net start mysql57    
//对于不同版本的MySQL启动会不同，如MySQL8.0.0：net start mysql80,

查看已存在的数据库

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| community          |
| community_test     |
| data               |
| mydata             |
| mysql              |
| performance_schema |
| test               |
+--------------------+
8 rows in set (0.04 sec)

创建数据库

mysql>  CREATE DATABASE example;
Query OK, 1 row affected (0.00 sec)

删除数据库

mysql>  DROP DATABASE example;
Query OK, 0 rows affected (0.07 sec)

查询数据库存储引擎

存储引擎就是指表的类型，数据库存储引擎决定了表在计算机的存储方式。
MySQL中查询存储引擎的类型命令：SHOW ENGINES;

创建表

//创建表注意:需要在已有的数据库中，即进入MySQL后使用：use 数据库名称;
CREATE TABLE 表名 (
    属性名 数据类型 [完整约束条件],
    属性名 数据类型 [完整约束条件],
    ...
    ...
    属性名 数据类型 [完整约束条件]
);

创建表涉及到的完整性约束条件完整性约束条件表：

约束条件	说明
PRIMARY KEY	标识该属性为该表的主键，可以唯一的标识对应的元组
FOREIGN KEY	标识该属性为该表的外键，是与之联系某表的主键
NOT NULL	标识该属性不能为空
UNIQUE	标识该属性的值是唯一的
AUTO_INCREMENT	标识该属性的值是自动增加，这是MySQL的SQL语句的特色
DEFAULT	为该属性设置默认值

使用示例

# 1）多字段主键格式：PRIMARY KEY(属性名1,属性名2....属性名n)
mysql> CREATE TABLE student2 (
    -> id int,
    -> stu_id int,
    -> name varchar(20),
    -> PRIMARY KEY(id,stu_id)
    -> );
# 2）设置表的外键
#格式：CONSTRAINT 外键别名 FOREIGN KEY(属性1,属性2,....属性n) REFERENCES 表名(属性1',属性2',...属性n')
    mysql> CREATE TABLE teacher (
    -> id int PRIMARY KEY,
    -> stu_id int,
    -> name varchar(20),
    -> CONSTRAINT STUID FOREIGN KEY(stu_id) REFERENCES student1(id)
    -> );
#  4）设置表的非空约束
　　简单的说就是不让这个属性的值为空，不填的话就会报错
　　格式：属性名 数据类型 NOT NULL
 
#  5）设置表的唯一性约束
　　就是这个属性的值是不能重复的
　　格式：属性名 数据类型 UNIQUE
 
#　6）设置表的属性值自动增加
　　AUTO_INCREMENT约束的字段可以是任何整数类型（TINYINT、SMALLINT、INT和BIGINT），在默认的情况下，该字段的值是从1开始自增
　　格式：属性名 数据类型 AUTO_INCREMENT
 
#  7）设置表的属性的默认值
　　格式：属性名 数据类型 DEFAULT 默认值
/*-------------------------------------------------------------------------------------------------*/
　　mysql> CREATE TABLE student3 (
    -> id int PRIMARY KEY AUTO_INCREMENT,
    -> teacher_id int UNIQUE,
    -> name varchar(20) NOT NULL,
    -> sex varchar(10) DEFAULT 'male'
    -> );

查看表结构

#查看表基本结构语句DESCRIBE
    格式：DESCRIBE 表名;
    通过查看表的结构，就很明确的对表进行解读，而且可以查看一下自己创建的表有没错误。
    示例： mysql> desc student3;
          +------------+-------------+------+-----+---------+----------------+
          | Field      | Type        | Null | Key | Default | Extra          |
          +------------+-------------+------+-----+---------+----------------+
          | id         | int(11)     | NO   | PRI | NULL    | auto_increment |
          | teacher_id | int(11)     | YES  | UNI | NULL    |                |
          | name       | varchar(20) | NO   |     | NULL    |                |
          | sex        | varchar(10) | YES  |     | male    |                |
          +------------+-------------+------+-----+---------+----------------+
          4 rows in set (0.01 sec)
#查看表详细结构语句  SHOW CREATE TABLE  表名
　　通过这个SQL语句可以查看表的详细定义，除了字段名、字段的数据类型、约束条件外，还可以查看表的默认存储引擎和     字符编码
　　格式：SHOW CREATE TABLE 表名;
　　示例：mysql> SHOW CREATE TABLE student3;
          +----------+----------------------------------+
          | Table    | Create Table |
          +----------+----------------------------------+
          | student3 | CREATE TABLE `student3` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `teacher_id` int(11) DEFAULT NULL,
            `name` varchar(20) NOT NULL,
            `sex` varchar(10) DEFAULT 'male',
            PRIMARY KEY (`id`),
            UNIQUE KEY `teacher_id` (`teacher_id`)
          ) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
          +----------+----------------------------------+
          1 row in set (0.00 sec)

修改表

1）修改表名

表名可以在一个数据库中唯一的确定一张表。
格式：ALTER TABLE 旧表名 RENAME 新表名;

mysql> ALTER TABLE student RENAME student4;
Query OK, 0 rows affected (0.11 sec)

mysql> DESCRIBE student;
ERROR 1146 (42S02): Table 'example.student' doesn't exist
# 修改表名后原来的表不存在

2）修改字段的数据类型

格式：ALTER TABLE 表名 MODIFY 属性名数据类型;

mysql> DESCRIBE student1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.08 sec)

mysql> ALTER TABLE student1 MODIFY name varchar(30);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE student1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

3）修改字段名：

格式：ALTER TABLE 表名 CHANGE 旧属性名新属性名新数据类型;

mysql> DESCRIBE student1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE student1 CHANGE name stu_name varchar(40);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE student1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| stu_name | varchar(40) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

4）增加字段

格式：ALTER TABLE 表名 ADD 属性名1 数据类型 [完整性约束条件] [FIRST | AFTER 属性名2];
其中，“属性名1”参数指需要增加的字段的名称；“FIRST”参数是可选参数，其作用是将新增字段设置为表的第一个字段；“AFTER”参数也是可选的参数，其作用是将新增字段添加到“属性名2”后面；“属性名2”当然就是指表中已经有的字段

mysql> DESCRIBE student1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| stu_name | varchar(40) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE student1 ADD teacher_name varchar(20) NOT NULL AFTER id;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE student1;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int(11)     | NO   | PRI | NULL    |       |
| teacher_name | varchar(20) | NO   |     | NULL    |       |
| stu_name     | varchar(40) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

5）删除字段

	格式：ALTER TABLE 表名 DROP 属性名;

mysql> DESCRIBE student1;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int(11)     | NO   | PRI | NULL    |       |
| teacher_name | varchar(20) | NO   |     | NULL    |       |
| stu_name     | varchar(40) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> ALTER TABLE student1 DROP teacher_name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE student1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| stu_name | varchar(40) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

6) 更改表的存储引擎

格式：ALTER TABLE 表名 ENGINE = 存储引擎名;

mysql> SHOW CREATE TABLE student2;
+----------+----------------------------------------
| Table    | Create Table
+----------+----------------------------------------
| student2 | CREATE TABLE `student2` (
  `id` int(11) NOT NULL DEFAULT '0',
  `stu_id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`,`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+----------+----------------------------------------
1 row in set (0.05 sec)

mysql> ALTER TABLE student2 ENGINE = MYISAM;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE student2;
+----------+----------------------------------------
| Table    | Create Table
+----------+----------------------------------------
| student2 | CREATE TABLE `student2` (
  `id` int(11) NOT NULL DEFAULT '0',
  `stu_id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`,`stu_id`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312 |
+----------+----------------------------------------
1 row in set (0.00 sec)

7) 删除表的外键约束

格式：ALTER TABLE 表名 DROP FOREIGN KEY 外键别名;

mysql> SHOW CREATE TABLE teacher;
+---------+------------------------------------------------
| Table   | Create Table      |
+---------+-----------------------------------------------+
| teacher | CREATE TABLE `teacher` (
  `id` int(11) NOT NULL,
  `stu_id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `STUID` (`stu_id`),
  CONSTRAINT `STUID` FOREIGN KEY (`stu_id`) REFERENCES `stu
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+---------+-----------------------------------------------+
1 row in set (0.08 sec)

mysql> ALTER TABLE teacher DROP FOREIGN KEY STUID;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE teacher;
+---------+-----------------------------------------------+
| Table   | Create Table  |
+---------+-----------------------------------------------+
| teacher | CREATE TABLE `teacher` (
  `id` int(11) NOT NULL,
  `stu_id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `STUID` (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+---------+-----------------------------------------------+
1 row in set (0.00 sec)

插入数据

语法

以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法：

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );
//如果数据是字符型，必须使用单引号或者双引号，如："value"。

通过命令提示窗口插入数据

以下实例中我们将向 runoob_tbl 表插入三条数据:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> INSERT INTO runoob_tbl 
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("学习 PHP", "菜鸟教程", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO runoob_tbl
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("学习 MySQL", "菜鸟教程", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO runoob_tbl
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("JAVA 教程", "RUNOOB.COM", '2016-05-06');
Query OK, 1 rows affected (0.00 sec)
mysql>
/*
注意： 使用箭头标记 -> 不是 SQL 语句的一部分，它仅仅表示一个新行，如果一条SQL语句太长，我们可以通过回车键来创建一个新行来编写 SQL 语句，SQL 语句的命令结束符为分号 ;。 
在以上实例中，我们并没有提供 runoob_id 的数据，因为该字段我们在创建表的时候已经设置它为 AUTO_INCREMENT(自动增加) 属性。 所以，该字段会自动递增而不需要我们去设置。实例中 NOW() 是一个 MySQL 函数，该函数返回日期和时间。 
接下来我们可以通过以下语句查看数据表数据：
*/

数据库的查询

1、条件

from / where / group by / having distinct / order by / limit

使用这些条件可以按照需求任意选择，但是顺序必须按照上面的顺序来
特殊点：distinct是放在最前面的，但是执行逻辑是上面的顺序

2、去重

#  distinct 
- select  distinct  *  from  表;
- 对于你查询出的所有记录，所有字段必须完全一模一样才会去重
- * 可以改为  任意字段

3、常用函数

- concat()/concat_ws()
  - 拼接
  - concat(字段,['拼接符号'],字段)
    - 将字段按照你写的格式拼接
  - concat_ws('拼接符号',字段n)
    - 所有字段按照第一拼接符号拼接
- upper()/lower()
  - upper()将字符串都变成大写
  - lower()将字符串都变成小写
- ceil()/floor()/round()
  - ceil(浮点型字段)
    - 向上取整
  - floor(浮点型字段)
    - 向下取整
  - round(浮点型字段)
    - 小于0.5的舍去
    - 大于0.5的进一位
- 整型可以使用运算符直接运算
# 使用案例

拼接：concat() | concat_ws()
大小写：upper() | lower()
浮点型操作：ceil() | floor() | round()
整型：可以直接运算

mysql>: select name,area,port from emp;
mysql>: select name as 姓名, concat(area,'-',port) 地址 from emp;  # 上海-浦东
mysql>: select name as 姓名, concat_ws('-',area,port,dep) 信息 from emp;  # 上海-浦东-教职部

mysql>: select upper(name) 姓名大写,lower(name) 姓名小写 from emp;

mysql>: select id,salary,ceil(salary)上薪资,floor(salary)下薪资,round(salary)入薪资 from emp;

mysql>: select name 姓名, age 旧年龄, age+1 新年龄 from emp;

4、条件： where

- where  判断规则;
  - 筛选出符合规则的记录
- 判断规则
  - 比较符合
    - > < >=  <=  =  !=
  - 区间符合
    - between  开始  and 结束
      - 在满足开始和结束中间都符合
    - in   (自定义容器)
      - 只要在里面的都符合
  - 逻辑符合
    - and  or   not
  - 相似符合
    - like   _或者%
    - _表示代替一个任意字符
    - %表示代替任意个任意字符
  - 正则符合
    - regexp   正则语法
    - 支持部分正则语法
# 使用案例
# 多条件协调操作导入：where 奇数 [group by 部门 having 平均薪资] order by [平均]薪资 limit 1

mysql>: select * from emp where id<5 limit 1;  # 正常
mysql>: select * from emp limit 1 where id<5;  # 异常，条件乱序

# 判断规则

比较符合：>  |  <  |  >=  |  <=  |  =  |  !=
区间符合：between 开始 and 结束 |  in(自定义容器)
逻辑符合：and  |  or  |  not
相似符合：like _|%
正则符合：regexp 正则语法

mysql>: select * from emp where salary>5;
mysql>: select * from emp where id%2=0;

mysql>: select * from emp where salary between 6 and 9;

mysql>: select * from emp where id in(1, 3, 7, 20);

# _o 某o | __o 某某o | _o% 某o* (*是0~n个任意字符) | %o% *o*
mysql>: select * from emp where name like '%o%';
mysql>: select * from emp where name like '_o%';  
mysql>: select * from emp where name like '___o%';

# sql只支持部分正则语法
mysql>: select * from emp where name regexp '.*\d';  # 不支持\d代表数字，认为\d就是普通字符串
mysql>: select * from emp where name regexp '.*[0-9]';  # 支持[]语法

5、分组与筛选： group by | having

where与having

首先要知道having也是用来筛选的
在不分组的情况下，使用where 和 having是一样的
并且，where和having的使用方式完全一致
- PS： having可以对聚合结果进行筛选，还可以对分组的数据进行筛选
- PS： where可以分组前使用

# 表象：在没有分组的情况下，where与having结果相同
# 重点：having可以对 聚合结果 进行筛选
mysql>: select * from emp where salary > 5;
mysql>: select * from emp having salary > 5;

mysql>: select * from emp where id in (5, 10, 15, 20);
mysql>: select * from emp having id in (5, 10, 15, 20);

聚合函数

将多条数据统一处理的方式叫做聚合
max()：求最大值
min()：求最大值
avg()：求平均数
sum()；求和
count()：求个数
group_concat()：组内字段拼接，用来查看组内其他字段
- 无法设置拼接方式默认以，连接

max()：最大值
min()：最小值
avg()：平均值
sum()：和
count()：记数
group_concat()：组内字段拼接，用来查看组内其他字段

分组查询 group by

# 修改my.ini配置重启mysql服务
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# 在sql_mode没有 ONLY_FULL_GROUP_BY 限制下，可以执行，但结果没有意义
# 有 ONLY_FULL_GROUP_BY 限制，报错
mysql>: select * from emp group by dep;

# 分组后，表中数据考虑范围就不是 单条记录，因为每个分组都包含了多条记录，参照分组字段，对每个分组中的 多条记录 统一处理
# eg: 按部门分组，每个部门都有哪些人、最高的薪资、最低的薪资、平均薪资、组里一共有多少人

# 将多条数据统一处理，这种方式就叫 聚合
# 每个部门都有哪些人、最高的薪资、最低的薪资、平均薪资 都称之为 聚合结果 - 聚合函数操作的结果
# 注：参与分组的字段，也归于 聚合结果

mysql>: 
select 
	dep 部门,
	group_concat(name) 成员,
	max(salary) 最高薪资,
	min(salary) 最低薪资,
	avg(salary) 平均薪资,
	sum(salary) 总薪资,
	count(gender) 人数
from emp group by dep;

mysql>: select 
	dep 部门,
	max(age) 最高年龄
from emp group by dep;

# 总结：分组后，查询条件只能为 分组字段 和 聚合函数操作的聚合结果
- 修改my.ini配置重启mysql服务
-sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
- 在sql_mode没有 ONLY_FULL_GROUP_BY 限制下，可以执行，但结果没有意义
- 有 ONLY_FULL_GROUP_BY 限制，报错
- 分组后的查询就不是单条记录了，而是一组一组的记录，对每组记录做同一处理
- 用聚合函数来处理这些数据

分组后的having

having可以对聚合结果再进行筛选，where不可以

mysql>: 
select 
	dep 部门,
	group_concat(name) 成员,
	max(salary) 最高薪资,
	min(salary) 最低薪资,
	avg(salary) 平均薪资,
	sum(salary) 总薪资,
	count(gender) 人数
from emp group by dep;

# 最低薪资小于2
mysql>:
select 
	dep 部门,
	group_concat(name) 成员,
	max(salary) 最高薪资,
	min(salary) 最低薪资,
	avg(salary) 平均薪资,
	sum(salary) 总薪资,
	count(gender) 人数
from emp group by dep having min(salary)<2;

# having可以对 聚合结果 再进行筛选，where不可以

联合分组

group by 字段1,…,字段n
将所有字段的结果看作一个整体，进而来进行分组

排序

排序规则
```
# order by 主排序字段 [asc|desc], 次排序字段1 [asc|desc], ...次排序字段n [asc|desc]
```
order by 主排序字段 [asc / desc] [ 次排序字段 [asc/desc] …]
- asc 升序排序，可以不写，默认升序
- desc 降序排序

未分组状态

mysql>: select * from emp;

# 按年龄升序
mysql>: select * from emp order by age asc;
# 按薪资降序
mysql>: select * from emp order by salary desc;

# 按薪资降序，如果相同，再按年龄降序
mysql>: select * from emp order by salary desc, age desc;
# 按龄降序，如果相同，再按薪资降序
mysql>: select * from emp order by age desc, salary desc;

分组状态

mysql>:
select 
	dep 部门,
	group_concat(name) 成员,
	max(salary) 最高薪资,
	min(salary) 最低薪资,
	avg(salary) 平均薪资,
	sum(salary) 总薪资,
	count(gender) 人数
from emp group by dep;

# 最高薪资降序
mysql:
select 
	dep 部门,
	group_concat(name) 成员,
	max(salary) 最高薪资,
	min(salary) 最低薪资,
	avg(salary) 平均薪资,
	sum(salary) 总薪资,
	count(gender) 人数
from emp group by dep
order by 最高薪资 desc;

限制 limit

limit 条数/偏移量,条数

# 语法：limit 条数  |  limit 偏移量,条数
mysql>: select name, salary from emp where salary<8 order by salary desc limit 1;

mysql>: select * from emp limit 5,3;  # 先偏移5条满足条件的记录，再查询3条

单表查询

增：
  insert [into] 
    [数据库名.]表名[(字段1[, ..., 字段n])] 
  values 
    (数据1[, ..., 数据n])[, ..., (数据1[, ..., 数据n])];
删：
	delete from [数据库名.]表名 [条件];
改：
	updata [数据库名.]表名 set 字段1=值1[, ..., 字段n=值n] [条件];
查：
	select [distinct] 字段1 [[as] 别名1],...,字段n [[as] 别名n] from [数据库名.]表名 [条件];
# 条件：from、where、group by、having、distinct、order by、limit => 层层筛选后的结果
# 注：一条查询语句，可以拥有多种筛选条件，条件的顺序必须按照上方顺序进行逐步筛选，distinct稍有特殊(书写位置)，条件的种类可以不全
# 可以缺失，但不能乱序

连表查询

连接

# 连接：将有联系的多张表通过关联(有联系就行，不一定是外键)字段，进行连接，形参一张大表
# 连表查询：在大表的基础上进行查询，就称之为连表查询
# 将表与表建立连接的方式有四种：内连接、左连接、右连接、全连接

建立表与表间的联系，可以是两张及以上
不要求外键，有联系就行
连接后是一大张包所有表字段的大表—实际上是多张表
连表查询
- 在张大表的基础上进行查询叫做连表查询
建立表与表连接的方式有四种
- 内连接
- 左连接
- 右连接
- 全连接

笛卡尔积

# 笛卡尔积: 集合 X{a, b} * Y{o, p, q} => Z{{a, o}, {a, p}, {a, q}, {b, o}, {b, p}, {b, q}}
mysql>: select * from emp, dep;
# 总结：是两张表 记录的所有排列组合，数据没有利用价值

select * from 表1 , 表2;
将两张表的记录进行全排列

一对多

内连接

# 关键字：inner join on
# 语法：from A表 inner join B表 on A表.关联字段=B表.关联字段

mysql>: 
select 
	emp.id,emp.name,salary,dep.name,work 
from emp inner join dep on emp.dep_id = dep.id 
order by emp.id;

# 总结：只保留两个表有关联的数据 from A表 [inner] join B表 on A表.关联字段=B表关联字段

左连接

# 关键字：left join on
# 语法：from 左表 left join 右表 on 左表.关联字段=右表.关联字段

mysql>: 
select 
	emp.id,emp.name,salary,dep.name,work 
from emp left join dep on emp.dep_id = dep.id 
order by emp.id;

# 总结：保留左表的全部数据，右表有对应数据直接连表显示，没有对应关系空填充
# from A表 left join B表 on A表.关联字段=B表关联字段

右连接

# 关键字：right join on
# 语法：from A表 right join B表 on A表.关联字段=B表关联字段

mysql>: 
select 
	emp.id,emp.name,salary,dep.name,work 
from emp right join dep on emp.dep_id = dep.id 
order by emp.id;

# 总结：保留右表的全部数据，左表有对应数据直接连表显示，没有对应关系空填充
# from A表 right join B表 on A表.关联字段=B表关联字段

左右连接转化

mysql>: 
select 
	emp.id,emp.name,salary,dep.name,work 
from emp right join dep on emp.dep_id = dep.id 
order by emp.id;

mysql>: 
select 
	emp.id,emp.name,salary,dep.name,work 
from dep left join emp on emp.dep_id = dep.id 
order by emp.id;

# 总结：更换一下左右表的位置，相对应更换左右连接关键字，结果相同
 # 交AB表位置和left/right关键字
 # 形成的新连接是一模一样的

全连接

mysql>: 
select 
	emp.id,emp.name,salary,dep.name,work 
from emp left join dep on emp.dep_id = dep.id 

union

select 
	emp.id,emp.name,salary,dep.name,work 
from emp right join dep on emp.dep_id = dep.id 

order by id;

# 总结：左表右表数据都被保留，彼此有对应关系正常显示，彼此没有对应关系均空填充对方

select 表的字段名

from A表 left join B表 on A表.关联字段=B表关联字段

union

select 表的字段

from A表 right join B表 on A表.关联字段=B表关联字段
- 因为mysql了不支持全连接，所以两张表的左右连接和union实现全连接
- 注意：两个左右连接必都先select形成一张新表，再用union连接成一张信新表
- 之后的操作只能对新表来完成
  - PS：两个select中的字段们必须完全一致，连顺序也要一样

一对一与一对多情况一致

# 创建一对一 作者与作者详情 表
create table author(
	id int,
    name varchar(64),
    detail_id int
);
create table author_detail(
	id int,
    phone varchar(11)
);
# 填充数据
insert into author values(1, 'Bob', 1), (2, 'Tom', 2), (3, 'ruakei', 0);
insert into author_detail values(1, '13344556677'), (2, '14466779988'), (3, '12344332255');

# 内连
select author.id,name,phone from author join author_detail on author.detail_id = author_detail.id order by author.id;

# 全连
select author.id,name,phone from author left join author_detail on author.detail_id = author_detail.id
union
select author.id,name,phone from author right join author_detail on author.detail_id = author_detail.id
order by id;

多对多：两表两表建立连接

# 在一对一基础上，建立 作者与书 的多对多关系关系

# 利用之前的作者表
create table author(
	id int,
    name varchar(64),
    detail_id int
);
insert into author values(1, 'Bob', 1), (2, 'Tom', 2), (3, 'ruakei', 0);

# 创建新的书表
create table book(
	id int,
    name varchar(64),
    price decimal(5,2)
);
insert into book values(1, 'python', 3.66), (2, 'Linux', 2.66), (3, 'Go', 4.66);

# 创建 作者与书 的关系表
create table author_book(
	id int,
    author_id int,
    book_id int
);
# 数据：author-book：1-1,2  2-2,3  3-1,3
insert into author_book values(1,1,1),(2,1,2),(3,2,2),(4,2,3),(5,3,1),(6,3,3);

# 将有关联的表一一建立连接，查询所以自己所需字段
select book.name, book.price, author.name, author_detail.phone from book 
join author_book on book.id = author_book.book_id
join author on author_book.author_id = author.id
left join author_detail on author.detail_id = author_detail.id;

前提两张被关系表个关系表都创建完成了【表里有数据了】
将有关联的表一一建立连接，查询所以自己所需字段
select 表的字段 from A表
join 关系表 on A表.关联字段=关系表.关联字段
join B表 on 关系表.关联字段=B表.关联呢字段；
join可以写多个，但内链连接会导致数据的丢失

Other

启动错误

ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)
//若无其他程序占用端口的情况下尝试依次运行以下命令
1. 安装mysqld服务器，输入命令：       mysqld --install
2. 接下来就是启动服务器了，输入命令：  net start mysql
3. 输入命令：                        mysqld --initialize-insecure
4. 输入命令：                        net start mysql

修改密码：（进入数据库的前提下）

update mysql.user set authentication_string=password('****') where user='root' ; //*位置为修改后密码

刷新数据库：

flush privileges;

数据库基本数据类型

# Character 数据类型
  Character 数据类型用来存储字母数字型数据。当你在oracle 中定义一个character 数据时，通常需要制定字段的长               度，它是该字段的最大长度。ORACLE提供以下几种character 数据类型：
# CHAR() 
  CHAR 数据类型是一种有固定长度和最大长度的字符串。存储在数据类型为CHAR字段中的数据将以空格的形式补到最大长          度。长度定义在1——2000字节之间。
     * 当你创建一个CHAR型字段，数据库将保证在这个字段中的所有数据是定义长度，如果某个数据比定义长度短，那么将        用空格在数据的右边补到定义长度。如果长度大于定义长度将会触发错误信息。
# VARCHAR() 
  varchar   型数据是varchar2型数据的快照。
# VARCHAR2() 
  varchar2  数据类型是一种可变长度的、有最大长度的字母数字型数据。Varchar2类型的字段长度可以达到4000字节，               Varchar2类型的变量长度可以达到32676字节。
          * 一个空的varchar2(2000)字段和一个空的varchar2(2)字段所占用的空间是一样的。
          
NCHAR() 和 NVARCHAR2() NCHAR() 和 NVARCHAR2()数据类型分别与CHAR() 和 VARCHAR2()类型是相同的，只不过它们用来存储NLS（National Language Support）数据。

# LONG 
  LONG    数据类型是一个遗留下来的而且在将来不会被支持的数据类型。它将被LOB(Large Object)数据类型所代替。
  
比较规则 Varchar2和char数据类型根据尾部的空格有不同的比较规则。对Char型数据，尾部的空格将被忽略掉，对于Varchar2型数据尾部带空格的数据排序比没有空格的要大些。比如：

# Char 型数据： ‘YO’=‘YO ’

# Varchar2型数据： ‘YO’<’YO ’

# Numberic 数据类型
  Numberic 数据类型用来存储负的和正的整数、分数和浮点型数据，范围在-1*10-103 和9.999…99*10125之间，有38位的            精确度。标识一个数据超出这个范围时就会出错。

# Number()
  Number   数据类型存储一个有p位精确度的s位等级的数据。

# DATE     数据类型
  DATE     数据类型用来存储日期和时间格式的数据。这种格式可以转换为其他格式的数据去浏览，而且它有专门的函数和            属性用来控制和计算。以下的几种信息都包含在DATE数据类型中：
           Century:   Year Month Day Hour Minute Second
# LOB      数据类型
  LOB(Large Object) 数据类型存储非结构化数据，比如二进制文件，图形文件，或其他外部文件。LOB 可以存储到4G字节            大小。数据可以存储到数据库中也可以存储到外部数据文件中。LOB数据的控制通过DBMS_LOB 包实现。BLOB,              NCLOB, 和CLOB 数据可以存储到不同的表空间中，BFILE存储在服务器上的外部文件中。LOB数据类型有以下几            种：
           BLOB: 二进制数据     CLOB: 字符型数据    BFILE: 二进制文件
# 其他数据类型
  ROWID ROWID 数据类型是ORACLE数据表中的一个伪列，它是数据表中每行数据内在的唯一的标识。

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

使用PHP脚本插入数据

你可以使用PHP 的 mysqli_query() 函数来执行 SQL INSERT INTO命令来插入数据。
该函数有两个参数，在执行成功时返回 TRUE，否则返回 FALSE。

语法

mysqli_query(connection,query,resultmode);

参数	描述
connection	必需。规定要使用的 MySQL 连接。
query	必需，规定查询字符串。
resultmode	可选。一个常量。可以是下列值中的任意一个： MYSQLI_USE_RESULT（如果需要检索大量数据，请使用这个） MYSQLI_STORE_RESULT（默认）

实例

以下实例中程序接收用户输入的三个字段数据，并插入数据表中：

添加数据

<?php
$dbhost = 'localhost:3306';  // mysql服务器主机地址
$dbuser = 'root';            // mysql用户名
$dbpass = '123456';          // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('连接失败: ' . mysqli_error($conn));
}
echo '连接成功<br />';
// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
 
$runoob_title = '学习 Python';
$runoob_author = 'RUNOOB.COM';
$submission_date = '2016-03-06';
 
$sql = "INSERT INTO runoob_tbl ".
        "(runoob_title,runoob_author, submission_date) ".
        "VALUES ".
        "('$runoob_title','$runoob_author','$submission_date')";
 
mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
  die('无法插入数据: ' . mysqli_error($conn));
}
echo "数据插入成功\n";
mysqli_close($conn);
?>
//对于含有中文的数据插入，需要添加  **mysqli_query($conn , "set names utf8");** 语句。

推荐文章： https://www.cnblogs.com/marklijian/p/11761430.html

CREATE DATABASE UDBMS DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
USE udbms;
CREATE SCHEMA UnAc;
/*创建学校表  学校（学校代码，学校名称，学校地址，学校等级，校长，校办公室电话）*/
CREATE TABLE `University`(                                       
   `university_code`          INTEGER PRIMARY KEY UNIQUE NOT NULL, /*学校编号 PRIMARY KEY*/
   `university_name`          VARCHAR(20) NOT NULL,                /*学校名称*/
   `university_address`       VARCHAR(40) NOT NULL,                /*学校地址*/
	 `university_grade`         SMALLINT NOT NULL,                   /*学校等级*/
	 `principal`                CHAR(20) NOT NULL,                   /*校长*/
	 `university_office_number` INT(15) NOT NULL,                    /*学校办公室电话*/
   `submission_date`          TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP    /*提交修改日期*/
)
/*创建学院表 学院（学院代码，学校代码，学院名称，院办公室电话，院长，院长电话）*/
CREATE TABLE `Academy`(
   `academy_code`            INTEGER PRIMARY KEY UNIQUE NOT NULL,     /*学院代码 PRIMARY KEY*/
	 `university_code`         INTEGER REFERENCES University(university_code),   /*学院所属学校代码*/
	 `academy_name`            VARCHAR(20) NOT NULL,                /*学院名称*/
	 `academy_office_phone`    INT(15) NOT NULL,                    /*学院办公室电话*/
	 `academy_dean`            CHAR(20) NOT NULL,                   /*院长*/
	 `dean_phone`              INT(15) NOT NULL,                    /*院长电话*/
	 `submission_date`         TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP    /*提交修改日期*/
)
/*创建教职工表 教职工（职工号，学院代码，姓名，性别，电话，年龄，教龄，职称，工资）*/
CREATE TABLE `Staff`(
   `staff_code`             INTEGER PRIMARY KEY UNIQUE NOT NULL,         /*教职工编号*/
	 `staff_academy_code`     INTEGER REFERENCES Academy(academy_code),    /*教职工所属学院代码*/
	 `staff_name`             CHAR(20) NOT NULL,                           /*姓名*/
	 `staff_sex`              CHAR(2) NOT NULL CHECK(staff_sex IN("男","女")),/*性别*/
	 `staff_phone`            INT(15) NOT NULL,                            /*电话*/
	 `staff_age`              SMALLINT NOT NULL,                           /*年龄*/
	 `staff_seniority`        SMALLINT NOT NULL,                           /*教龄*/
	 `staff_title`            VARCHAR(10),                                 /*职称*/
	 `submission_date`        TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP    /*提交修改日期*/
)
/*创建学生表 学生（学号，姓名，性别，电话，家庭住址，身份证号，所属院系，专业，宿舍号）*/
CREATE TABLE `Student`(
   `student_id`             INTEGER PRIMARY KEY UNIQUE NOT NULL,        /*学号*/
	 `student_academy`        INTEGER REFERENCES Academy(academy_code),   /*所属学院*/
	 `student_name`           CHAR(10) NOT NULL,                          /*姓名*/
	 `student_sex`            CHAR(2) NOT NULL CHECK(student_sex IN("男","女")), /*性别*/
	 `student_phone`          INT(15) NOT NULL,                           /*电话*/
	 `student_address`        VARCHAR(50),                                /*家庭住址*/
	 `student_ID_No`          INT UNSIGNED NOT NULL UNIQUE,               /*身份证号*/
	 `student_major`          INTEGER REFERENCES Major(major_code),       /*专业*/
	 `student_dormitory`      INT(5) NOT NULL,                            /*宿舍号*/
	 `submission_date`        TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP    /*提交修改日期*/
)
/*创建课程表 课程（课程代码，课程名称，任课教师，课程学时，课程学分，开课学院，开课时间，上课地点，班级编号，班级人数）*/
CREATE TABLE `Course`(
   `course_code`            INTEGER PRIMARY KEY UNIQUE NOT NULL,        /*课程代码*/
	 `course_name`            VARCHAR(8) NOT NULL,                        /*课程名称*/
	 `course_staff`           INTEGER REFERENCES  Staff(staff_code),      /*任课教师*/
	 `course_period`          INT(4) NOT NULL,                            /*课程学时*/
   `course_credit`          SMALLINT NOT NULL,                          /*课程学分*/
	 `course_academy`         INTEGER REFERENCES Academy(academy_code),   /*开课学院*/
	 `course_start_time`      TIMESTAMP NOT NULL,                         /*开课时间*/
	 `course_place`           VARCHAR(10) NOT NULL,                       /*上课地点*/
	 `course_class_id`        INT UNSIGNED NOT NULL,                      /*班级编号*/
	 `course_student_number`  INT UNSIGNED NOT NULL,                      /*班级人数*/
	 `submission_date`        TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP    /*提交修改日期*/
)
/*创建学生-课程选课关系表 选修（课程号，学生学号）*/
CREATE TABLE `course_elective`(
   `course_code`            INTEGER REFERENCES Course(course_code),          /*选修课程号*/
	 `student_id`             INTEGER REFERENCES Student(student_id),          /*选课学生学号*/
	 `submission_date`        TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP   /*提交修改日期*/
)
/*创建学生课程成绩表 成绩（课程号，学生学号，平时成绩，考试成绩，最终成绩）*/
CREATE TABLE `course_grade`(
   `course_code`            INTEGER REFERENCES course_elective(course_code),          /*选修课程号*/
	 `student_id`             INTEGER REFERENCES course_elective(student_id),           /*选课学生学号*/
	 `usual_performance`      INT(4) NOT NULL CHECK(usual_performance>=0 AND usual_performance<=100), /*平时成绩*/
	 `test_performance`       INT(4) NOT NULL CHECK(test_performance>=0 AND test_performance<=100),   /*考试成绩*/
	 `course_grade`           INT(4) NOT NULL CHECK(course_grade>=0 AND course_grade<=100),           /*总成绩*/
	 `submission_date`        TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP                          /*提交修改日期*/
)
/*创建 专业（专业编号，学院代码，专业名称，专业人数，最近更新日期）*/
CREATE TABLE `Major`(
   `major_code`             INTEGER PRIMARY KEY UNIQUE NOT NULL,                      /*专业编号*/
	 `academy_code`           INTEGER REFERENCES Academy(academy_code),                 /*学院编号*/
	 `major_name`             VARCHAR(10) NOT NULL,                                     /*专业名称*/
	 `submission_date`        TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP            /*提交修改日期*/
)
/*创建宿舍表 宿舍（宿舍编号，容纳人数）*/
CREATE TABLE `Dormitory`(
   `dormitory_code`         INT UNSIGNED NOT NULL UNIQUE,                       /*宿舍号*/
   `dormitory_capacity`	    SMALLINT NOT NULL,                                  /*宿舍容量*/
	 `submission_date`        TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP      /*提交修改日期*/
)
/*创建教师工资表 教师工资（工资流水号、教职工号、岗位工资、薪级工资、绩效工资、津补贴、最终发放）*/
CREATE TABLE `Salary`( 
   `serial_number`         VARCHAR(20) NOT NULL UNIQUE,                            /*流水号*/
	 `staff_code`            INTEGER REFERENCES Staff(staff_code),                   /*教职工号*/
	 `post_wage`             FLOAT(5) NOT NULL,                                      /*岗位工资*/
	 `salary_welfare`        FLOAT(5) NOT NULL,                                      /*薪级工资*/  
	 `merit_pay`             FLOAT(5) NOT NULL,                                      /*绩效工资*/
	 `subsidies`             FLOAT(5) NOT NULL,                                      /*津补*/
	 `wage`                  FLOAT(5) NOT NULL,                                      /*最终发放*/
	 `submission_date`       TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP          /*提交修改日期*/
)
/*创建学生学费表 学费（学费流水号、学生学号、学分学费、住宿费、教材代收费、重修费、总费用）*/
CREATE TABLE `tuition_fees`(
   `serial_number`         VARCHAR(20) NOT NULL UNIQUE,                          /*流水号*/
	 `student_code`          INTEGER REFERENCES Student(student_code),             /*学生学号*/
	 `credits_tuition`       FLOAT(5) NOT NULL,                                    /*学分学费*/
	 `quarterage`            FLOAT(5) NOT NULL,                                    /*住宿费*/
	 `book_fees`             FLOAT(5) NOT NULL,                                    /*教材费*/
	 `repair_fee`            FLOAT(5) NOT NULL,                                    /*重修费*/
	 `all_fees`              FLOAT(5) NOT NULL,                                    /*学分学费*/
	 `submission_date`       TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP        /*提交修改日期*/
)

WAREY~

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库操作——增删查改

MySQL操作1. 数据库基本操作启动net start mysql57 //对于不同版本的MySQL启动会不同，如MySQL8.0.0：net start mysql80,查看已存在的数据库mysql> SHOW DATABASES;+--------------------+| Database |+--------------------+| information_schema || community || communi
复制链接

扫一扫