2021年7月27日---------mysql-阿南达文事网

2021年7月27日---------mysql

mysql

mysql简介
- SQl语言的介绍
dos命令的操作
- 操作数据库
- 操作表
- 操作表字段
- 操作表数据
- 约束
- - select语句
- 聚集函数
索引
备份数据库
恢复数据库
外键约束
多表查询
- 内连接
- 外连接
- 全外连接
- ==注意==

mysql简介

一、mysql数据库概述

数据库的概念
数据库: 存储数据的仓库
数据库类型：层次式数据库, 网络式数据库, 关系型数据库
关系型数据库
o 使用关系模型来存储的数据的数据库叫做关系型数据库。
常见数据库
 商业数据库

Oracle
SQLServer
DB2
Sybase

 开源数据库
MySQL
SQLLite
 MySQL数据库的安装和配置
安装的路径不要有中文和空格
默认的端口3306不要去改, 保持默认即可
使用命令行窗口连接MYSQL数据库：mysql –u用户名 –p密码
登陆或退出MySql客户端命令
登录：mysql -u root -p123 -h localhost；
-u：后面的root是用户名，这里使用的是超级管理员root；
-p：后面的123是密码，这是在安装MySQL时就已经指定的密码；
-h：后面给出的localhost是服务器主机名，它是可以省略的，例如：mysql -u root -p
退出：quit或exit；

SQl语言的介绍

○Structured Query Language, 结构化查询语言
○ 非过程性语言
○ 美国国家标准局(ANSI）与国际标准化组织（ISO）已经制定了SQL标准○
如Oracle的PL/SQL 过程性处理能力
 SQL Server、Sybase的T-SQL
 为加强SQL的语言能力，各厂商增强了过程性语言的特征○ SQL是用来存取关系数据库的语言，具有查询、操纵、定义和控制关系型数据库的四方面功能。

dos命令的操作

mysql -uroot -p3306 -p

操作数据库

gbk_bin是二进制存储.区分大小写的
gbk_chinese_ci不区分大小写

查看数据库数据的位置

mysql> select @@datadir;
+---------------------------------------------+
| @@datadir                                   |
+---------------------------------------------+
| C:\ProgramData\MySQL\MySQL Server 5.7\Data\ |
+---------------------------------------------+
1 row in set (0.00 sec)

查看数据库的编码格式

mysql> show variables like "%character%";
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8                                                    |
| character_set_connection | utf8                                                    |
| character_set_database   | latin1

collate :校对规则

# 创建数据库
mysql> create database db;
Query OK, 1 row affected (0.00 sec)
# 创建一个使用utf8字符集的mydb2数据库
mysql> create database mydb character set gbk;
Query OK, 1 row affected (0.03 sec)
# 创建一个使用utf8字符集，并带校对规则的mydb3数据库
mysql> create database mydb3 character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.00 sec)
# 	显示数据库语句
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db                 |
| mydb               |# 显示数据库创建语句
mysql> show create database db;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| db       | CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
# 数据库删除语句
mysql> drop database db;
Query OK, 0 rows affected (0.00 sec)
# 修改数据库
mysql> alter database mydb character set gbk collate gbk_bin;
Query OK, 1 row affected (0.00 sec)
# 使用数据库并且切换当前数据库
mysql> use mydb;
Database changed
# 查看当前使用的数据库
mysql> select mydb;
ERROR 1054 (42S22): Unknown column 'mydb' in 'field list'
mysql> select database();
+------------+
| database() |
+------------+
| mydb       |
+------------+
1 row in set (0.00 sec)

操作表

mysql> # 创建表
mysql> create table employ(-> id int(10) primary key auto_increment,# 设置主键和自增-> name varchar(20),-> birthday date-> );
Query OK, 0 rows affected (0.27 sec)# 查看表的信息
mysql> select database();
+------------+
| database() |
+------------+
| mydb       |
+------------+
1 row in set (0.00 sec)
# 查看当前表的结构
mysql> desc employ;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(10)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20) | YES  |     | NULL    |                |
| birthday | date        | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)查看当前数据库中所有的表
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| employ         |
+----------------+
1 row in set (0.00 sec)mysql> # 查看当前表的创建的语句
mysql> show create table employ;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employ | CREATE TABLE `employ` (`id` int(10) NOT NULL AUTO_INCREMENT,`name` varchar(20) COLLATE gbk_bin DEFAULT NULL,`birthday` date DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COLLATE=gbk_bin |
1 row in set (0.00 sec)

操作表字段

mysql> # 增加一列
mysql> alter table employ add age int;
Query OK, 0 rows affected (0.42 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> # 修改一列属性
mysql> alter table employ modify name int(10);
Query OK, 0 rows affected (0.61 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> # 修改列名
mysql> alter table employ change name aname int;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> # 修改表名
mysql> rename table employ to em;
Query OK, 0 rows affected (0.13 sec)mysql> # 修改表所用的字符集
mysql> alter table em character set utf8;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> drop table em;
Query OK, 0 rows affected (0.11 sec)

操作表数据

# 添加一条信息
# 第一种
mysql> insert into e(id,name) values(null,'a');
Query OK, 1 row affected (0.03 sec)mysql> insert into e(id,name) values(null,'b');
Query OK, 1 row affected (0.05 sec)mysql> insert into e(id,name) values(null,'c');
Query OK, 1 row affected (0.03 sec)
# 第二种
mysql> insert into e values('c');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into e values(null,'c');
Query OK, 1 row affected (0.05 sec)mysql> # 修改一条记录
mysql> update e set name='a';
Query OK, 3 rows affected (0.03 sec)
Rows matched: 4  Changed: 3  Warnings: 0mysql> update e set id=4 where name='a';
mysql> # 修改一条记录
mysql> update e set name='a';
Query OK, 3 rows affected (0.03 sec)
Rows matched: 4  Changed: 3  Warnings: 0mysql> update e set id=4 where name='a';
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
mysql> delete from e where name='a';
Query OK, 4 rows affected (0.06 sec)mysql> # 使用truncate删除表中记录
mysql> truncate table e;
Query OK, 0 rows affected (0.27 sec)

约束

				primary keyuniquenot nullauto_increment 主键字段必须是数字类型。外键约束

truncate删除的用处
truncate是将整张表的结构进行摧毁再重建，数据也都清空了。delete from删除一条
一条数据进行删除。truncate删除效率较高。在以后如果出现多表的场景，truncate这种删除方式有可能会破坏表
与表之间的关系，以后删除还是使用delete from来删除数据。
当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，
DELETE操作不会减少表或索引所占用的空间。
drop语句将表所占用的空间全释放掉。

3.1!!!插入数据基本语句insert into table_name values(value...)insert into table_name(column_name1,column_name2) values(value1,value2);向employee表里插入数据第一种插入方式:insert into employee values(null,'cy','m','1999-9-9','2000-1-1','dancer',10000.0,'like dance');insert into employee values(null,'hx','f','1999-09-19','2020-1-1','rapper',10000.0,'like rap');insert into employee values(null,'xpq','m','1998-9-9','2019-1-1','teacher',20000.0,'like study');第二种插入方式:insert into employee(id,name) values(null,'tom');	总结插入数据需要注意的问题：1.字符串类型和日期类型需要通过单引号括起来2.如果字段自动增长可以不给这个字段给值3.如果是在表名后面指定字段，就需要在values里按照之前指定字段的顺序来给值4.如果在表名后面没有指定字段，就需要在values里按照表里定义的字段顺序来依次给值5.插入数据的类型必须要和字段类型一致6.插入数据不能超过字段长度限制3.2!!更新数据基本语句update table_name set column_name=value;//有条件的更新操作，只有满足这个条件才能进行更新数据update table_name set column_name=value where column_name=v;在上面创建的employee表中修改表中的纪录。要求:将所有员工薪水修改为5000元。update employee set salary=5000;将姓名为'pq'的员工薪水修改为3000元。update employee set salary=3000 where name='pq';将姓名为'lg'的员工薪水修改为4000元,job改为ccc。update employee set salary=4000,job='ccc' where name='lg';将'hx'的薪水在原有基础上增加1000元。update employee set salary=salary+1000 where name='hx';3.3删除数据基本语句delete from table_name;//有条件的删除操作，只有满足这个条件才能进行删除数据	delete from table_name where column_name=v;删除表中名称为’cy’的记录。delete from employee where name='cy';删除表中所有记录。delete from employee;使用truncate删除表中记录。truncate employee;

select语句

过滤表中重复数据。
select distinct english from exam;（2）select 也可以使用表达式，并且可以使用 as 别名
在所有学生分数上加10分特长分显示。
select name,english+10,chinese+10,math+10 from exam;
统计每个学生的总分。
select name,english+chinese+math from exam;
使用别名表示学生总分。
select name,english+chinese+math as 总成绩 from exam;
select name,english+chinese+math 总成绩 from exam;
select name english from exam;
*where字句中可以使用：*比较运算符：	> < >= <= <>  between 10 and 20 值在10到20之间  in(10,20,3)值是10或20或30like '张pattern' pattern可以是%或者_，如果是%则表示任意多字符，此例中张三丰 张飞 张abcd ，如果是_则表示一个字符张_，张飞符合。张Is null*逻辑运算符在多个条件直接可以使用逻辑运算符 and or not

Order by

指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的别名。
Asc 升序、Desc 降序，其中asc为默认值
ORDER BY 子句应位于SELECT语句的结尾。

	*练习：对数学成绩排序后输出。select * from exam order by math;对总分排序按从高到低的顺序输出select name ,(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) 总成绩 from exam order by 总成绩 desc;对姓张的学生成绩排序输出select name ,(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) 总成绩 from exam where name like '张%' order by 总成绩 desc;

聚集函数

1.count：返回某一列，行的总数,如果为本字段有的数据为null，则不计数

mysql> select count(*) from employee;
功能相同：select count(4) from em;
在大量数据下，他们效率相同。

2.sum
对于满足条件的行，将指定字段全部数据进行求和,只能求数值之和

select sum(salary) from em;

3.avg
AVG函数返回满足where条件的一列的平均值

mysql> select avg(salary) from em;

4.Max/min函数
返回满足where条件的一列的最大/最小值

mysql> select min(salary) from em;

子查询操作
在一个查询语句中添加where自居，where子句中条件是使用另一个查询语句来实现的

select name,chinese from exam where chinese in(select max(chinese) from exam);

5.分组group by通过having
指定字段名进行分组，字段相同的分在同一组，同一组只能显示本组的第一条数据
分组之后只会展示同一组的第一条数据，但是同一组的数据没有被覆盖。

查询商品和价格总价按照商品名称来分组

select product,sum(price) from orders group by product;

大于100的商品价格

 select product,sum(price) from orders group by product having sum(price)>100;

having是在分组之后执行的，能用where的就能用Having，但是能用having的不一定能用where.

索引

本身：数据结构

备份数据库

C:\Users\Administrator>mysqldump -uroot -p mydb>d:1.sql

恢复数据库

source 文件名.sql

外键约束

o 用来通知数据库表与表字段之间的对应关系, 并让数据库帮我们维护这样关系的键就叫做外键
o 外键作用: 维护数据的完整性一致性
o 定义外键约束

多表查询

多表查询叫做笛卡尔积

内连接

左边表有且右边表也有的记录。

SELECT * FROM dept d INNER JOIN emp e ON e.dept_id=d.id;
SELECT * FROM dept,emp WHERE dept.id=emp.dept_id;

外连接

左边表有右边表没有的数据
左外连接
在内连接查询的基础上，加上左边表有而右边表没有的记录

SELECT * FROM dept d LEFT JOIN emp e ON d.id=e.dept_id;

右外连接
在内连接查询的基础上，加上右边表有而左边表没有的记录。

SELECT * FROM dept d RIGHT JOIN emp e ON d.id=e.dept_id;

全外连接

注意:

mysql不支持全外连接

通过union关键字来模拟全外连接查询

在内连接查询的基础上，加上左边表有而右边表没有的记录和右边表有而左边表没有的记录。

SELECT * FROM dept d LEFT JOIN emp e ON d.id=e.dept_id
UNION
SELECT * FROM dept d RIGHT JOIN emp e ON d.id=e.dept_id
;

六个关键字的顺序
from–where–group by–having–select–order by。

注意

1.虽然select在having后执行，但是mysql中仍然可以在having中使用select语句定义的别名。

原因分析：mysql在查询的时候会产生一个临时表，所有的字段名称（别名）在临时表中已经产生，产生了临时表之后才会进行having操作。也就是说mysql内部有一定的解析顺序，解析顺序select优先于having。

2.insert into 和replace into和merge into

a.insert into是mysql的基本插入语句。
b.replace into 是mysql中的代替插入语句，可以理解为insert into的升级版。replace into在执行的时候，首先会根据指定的主键或者唯一索引判断当前表中是否存在指定的主键或索引，如果主键或唯一索引已经存在，则先将对应的索引的数据删除，然后在索引位置插入replace into中包含的数据。如果主键和唯一索引没有存在，则直接在索引位置插入replace into中包含的数据。
c.merge into 是oracle数据库中的代替插入语句。实现方式和replace into类似。
d.执行效率：
i.如果指定索引位置没有数据，insert into和replace into执行效率相差无几，二者效率相同。
ii.如果指定位置索引已经存在，insert into语句不能正常执行，replace into语句可以正常执行。
e.注意：虽然replace into比较好使用，但是也存在一定风险：
i.replace每次要重新分配自增id;
ii.replace中执行delete时, 在有外键的情况下会很麻烦;
iii.如果delete时定义的有触发器, 则会被执行;
iv.副作用也会被传播到replica slave.
f.推荐使用INSERT INTO … ON DUPLICATE KEY UPDATE
ON DUPLICATE KEY UPDATE是mysql特有的一个sql关键字，只能在mysql中使用。
在MySQL数据库中，如果在insert语句后面带上ON DUPLICATE KEY UPDATE 子句，而要插入的行与表中现有记录的惟一索引或主键中产生重复值，那么就会发生旧行的更新；如果插入的行数据与现有表中记录的唯一索引或者主键不重复，则执行新纪录插入操作。

3.外键的使用情景：

在不要求吞吐速度而对数据的正确性和安全性要求较高时，推荐使用外键。
如果面对高吞吐量，要求优先保证读取效率时，则不推荐使用外键。

2021年7月27日---------mysql