mysql数据库学习

最新推荐文章于 2023-08-31 09:24:16 发布

weixin_33811539

最新推荐文章于 2023-08-31 09:24:16 发布

阅读量362

点赞数

原文链接：http://blog.51cto.com/13606177/2373221

版权

数据库简介与类型

什么是数据库？
数据库是存放数据的仓库，按照一定的数据结果（数据结构是指数据的组织形式或数据之间联系）来组织，存储的。可以通过数据库提供的方法来管理数据库的数据。

数据库种类
早期比较流行的数据库模式有三种：层次式数据库，网络式数据库和关系型数据库。现在比较常用的数据库模型主要有两种：关系型数据库和非关系型数据库。

关系型数据库
关系型数据库模型是把复杂的数据结构归结为简单的二元关系（二维表格形式）；在关系型数据库中，对数据的操作几乎全部建立在一个或多个关系表格上，通过对这些关联的表格分类，合并，连接或选取等运算来实现数据库的管理；
关系型数据库的形式为表格；常用代表是oracle和mysql数据库；
通过sql结构化查询语句存储数据；
保持数据一致性方面很强；

非关系型数据库
非关系型数据库也被成为nosql数据库，nosql是指not only sql，指的是非关系型数据库，不是指no sql 。nosql的产生并不是要彻底否定非关系型数据库，而是作为传统关系型数据库的一个有效补充。

随着互联网Web2.0网站的兴起，传统的关系型数据库在应付web2,0网站，特别是对于规模日益扩大的海量数据，超大规模和高并发的微博、微信、SNS类型的web2.0纯动态网站已经显得力不从心，暴露了很多难以克服的问题。传统的关系型数据库IO瓶颈、性能瓶颈都难以有效突破，于是出现了大批针对特定场景，以高性能和使用便利为目的功能特异化的数据库产品。NOSQL（非关系型）类的数据就是在这样的情景下诞生并得到了非常迅速的发展；

非关系型数据库的种类
键值存储数据库

键值数据库就类似传统语言中使用的哈希表，可以通过key来添加，查询或者删除数据库，因为使用key主键访问，所以会获得很高的性能和拓展性；
键值数据库主要使用一个哈希表，这个表有个特定的键和一个指针指向特定数据，key与value模型对于简单，易部署，高并发。
典型产品：memcached,redis,memcacheDB

列存储
列存储数据库将数据存储在列族中，一个列族存储经常被一起查询的相关数据，比如人类，我们经常会查询某个人的姓名和年龄，而不是薪资。这种情况下姓名和年龄会被放到一个列族中，薪资会被放到另一个列族中。
这种数据库通常用来应对分布式存储海量数据。

面向文档数据库
文档型数据库的灵感是来自于Lotus Notes办公软件，而且它同第一种键值数据库类似。该类型的数据模型是版本化的文档，半结构化的文档以特定的格式存储，比如JSON。文档型数据库可以看作是键值数据库的升级版，允许之间嵌套键值。而且文档型数据库比键值数据库的查询效率更高。
面向文档数据库会将数据以文档形式存储。每个文档都是自包含的数据单元，是一系列数据项的集合。每个数据项都有一个名词与对应值，值既可以是简单的数据类型，如字符串、数字和日期等；也可以是复杂的类型，如有序列表和关联对象。数据存储的最小单位是文档，同一个表中存储的文档属性可以是不同的，数据可以使用XML、JSON或JSONB等多种形式存储。

图形数据库
图形数据库允许我们将数据以图的方式存储。实体会被作为顶点，而实体之间的关系则会被作为边。比如我们有三个实体，Steve Jobs、Apple和Next，则会有两个“Founded by”的边将Apple和Next连接到Steve Jobs。

mysql数据库特性

使用c和c++编写，并使用多种编译器进行测试，保证源代码的可移植性；
支持多只能够操作系统；
为多种编程语言提供API
支持多线程，充分利用cpu资源，支持多用户；
优化的sql查询算法，有效提高查询速度；
既能够作为单独的应用程序在客户端服务器网络环境运行，也能够作为一个程序库嵌入到其他软件中。
提供多语言支持；
提供tcp/ip，odbc和jdbc等多种数据库连接途径；
用于管理，检查，优化数据库操作的管理工具；
可以处理上千万条记录的大型数据库。
innoDB支持事务，myisam支持全文检索

数据库的编码格式(字符集)设置

character_set_client：你发送的数据必须与client指定的编码一致！！！服务器会使用该编码来解读客户端发送过来的数据；
character_set_connection：通常该编码与client一致！该编码不会导致乱码！当执行的是查询语句时，客户端发送过来的数据会先转换成connection指定的编码。但只要客户端发送过来的数据与client指定的编码一致，那么转换就不会出现问题；
character_set_database：数据库默认编码，在创建数据库时，如果没有指定编码，那么默认使用database编码；
character_set_server：MySQL服务器默认编码；
character_set_result：响应的编码，即查询结果返回给客户端的编码。这说明客户端必须使用result指定的编码来解码

1.配置文件修改数据库字符集
1.查询数据库拜编码格式（字符集）情况；

MariaDB [(none)]> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

2.修改配置文件

[root@amriadb ~]# vi /etc/my.cnf.d/server.cnf
[mysqld]
default-storage-engine=INNODB   #设置数据库的引擎为INNODB
character-set-server=utf8               #设置数据库的字符集格式为utf8
collation-server=utf8_general_ci    #数据库校对规则。ci是case insensitive的缩写，意思是大小写不敏感；

[root@mariadb ~]# vi /etc/my.cnf.d/client.cnf
[client]
default-character-set=utf8

3.重启数据库mariadb

[root@mariadb ~]# systemctl restart mariadb

4.进入数据库查询字符集

MariaDB [(none)]> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

2.创建数据库时设置
如果不修改配置文件，创建数据库时设置数据库的字符集和校验规则；

MariaDB [(none)]> create database A default character set utf8 collate utf8_general_ci;
#default character set utf8  设置字符集为utf8
#collate utf8_general_ci  #数据库校对规则,大小写不敏感；

数据库字符集校验规则

数据库的索引

index是数据库的物理结构，它只是辅助查询，它创建时会在另外的表空间（mysql中的innodb表空间），以一个类似目录结构存储。索引分类的话，分为前缀索引和全文本索引等；

MariaDB [test]> use mysql;
MariaDB [mysql]> desc db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(16)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+

我们在desc表名时会看见key值得行，表示该列表是否含有索引；一般key值一栏可能存在四种值：
该key值得有限顺序为PRI-->UNI-->MUL
如果key值为PRI，那么该列是主键的组成部分；
如果key值为空，那么该列值是可以进行重复，表示该列没有索引，或者是一个非唯一的复合索引的非前导列；
如果key值为NUI，该列是一个唯一值索引的第一列，且不能含有空值；
如果key值为MUL，那么该列的值可以重复，给列是一个非唯一索引的前导列或者是一个唯一性索引的组成部分但是可以含有空值；

数据库的主键和外键

关系型数据库中的一条记录中有若干个属性，若其中某一个属性组能唯一标识一条记录，该属性组可以成为一个主键；
比如说a表主键a,b表主键b,c表存在a和b属性，但是c表不能以单独的a和b属性为主键；在c表里a属性不是c表的主键但是属性a是a表的唯一主键，则称c表的a属性是a表的外键，同理可以称c表的b属性是b表的外键；

主键和外键的作用
定义主键和外键主要是为了维护关系型数据库的完整性；
主键是确定一条记录的唯一标识，而外键用于与另外一张表进行关联，确定另外一张表记录的字段；

主键，外键和索引的区别
主键
定义：唯一标识一条记录，不能重复，不允许为空
作用：保证数据完整性
个数：主键只可以有一个

外键
定义:标的外键是其他表的唯一主键，外键可以重复，可以为空值
作用：用来和其他表建立联系的
个数：一个表可以有多个外键

索引
定义：该字段没有重复，但可以有一个空值
作用：是提高查询排序的速度
个数：一个表可以有多个唯一索引

主键选取原则
数据库常见的主键选取方式：
自动增长型字段
自动增长型字段允许我们在向数据库添加数据时，不考虑主键的取值，记录插入后，数据库系统会自动为其分配一个值，确保绝对不会出现重复。

手动增长型字段

Uniqueidentifier

comb类型

创建数据库

1.创建数据库时设置字符集以及校验规则；

MariaDB [(none)]> create database A default character set utf8 collate utf8_general_ci;

2.更改数据库的编码格式

MariaDB [(none)]> alter database A character set utf8;

3.使用mysqladmin命令创建数据库

[root@mariadb ~]# mysqladmin -uroot -p create B
#数据库查询创建的数据库B

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| A                  |
| B                  |
| database           |
| mysql              |
| performance_schema |
| score              |
| test               |
| www                |
+--------------------+

删除数据库

1.使用drop命令删除

MariaDB [(none)]> drop database score;
Query OK, 0 rows affected (0.00 sec)

2.使用mysqlsdmin命令删除

[root@mariadb ~]# mysqladmin -uroot -p drop B
Enter password: 
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'B' database [y/N] y  #确认删除数据库B
Database "B" dropped

选择数据库

MariaDB [mysql]> use A;
Database changed

数值类型

在创建数据表时需要定义数据字段的类型，这方面对于数据的优化比较重要；MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
数值类型

日期和时间类型
表示时间值的日期和时间类型为datetime,date.timestamp,time和year
每个时间类型有一个有效值范围和一个零值，当指定不合法的mysql不能表示的值使用零值；

字符串类型
字符串类型是指char,varchar,binary,varbinary,blob,text,enum和set;
CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

创建数据表

在创建数据库表时，需要使用到表名和表字段和定义每个表字段；
使用create table来创建数据库表；

在数据库中创建表的通用语法：
create table table_name (column_name column_type)

MariaDB [test]> create table if not exists student(id int not null,username varchar(20) not null);

MariaDB [test]> create table if not exists students(id int not null,username varchar(20) not null,time DATE);

#设置定义列为主键；
MariaDB [test]> create table if not exists studentss(id int not null,username varchar(20) not null,time DATE,primary key (id));
#定义多个列为主键；
MariaDB [test]> create table if not exists studentsss(id int not null,username varchar(20) not null,time DATE,primary key (id,username))engine=InnoDB default charset=utf8;

#定义该表的引擎为InnoDB，默认编码格式为utf8;
MariaDB [test]> create table if not exists studentsss(id int not null,username varchar(20) not null,time DATE,primary key (id))engine=InnoDB default charset=utf8;

如果不想字段为空，可以字段属性设置为not null，在操作数据库时输入该字段的数据为空时就会报错；

auto_increment定义列为自增属性，一般用于主键，数值会自动增加1；

primary key关键字用于定义列为主键，你可以使用多列来定制主键。列间以逗号分隔。
该键值有两个作用，一个是约束作用，用来规范一个存储主键和唯一性，但同时也在key上建立一个主键索引；

engine设置存储引擎，charset设置编码格式；

备注：auto_increment用于主键自动增长的，从1开始增长，当你把第一条记录删除时，在插入第二条数据时，主键的值为2而不是1。

该参数是数据列的一种属性，只适合用于整数类型数据列。

该参数的整数类型数据列应该是一个正数序列，该数据列声明为unsigned(无符号)，这样序列的编号可以增加一倍。必须具备不为空的属性；

当进行全表删除时，该参数会从1重新开始编号。

删除数据表

使用drop命令去删除数据库表；


MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| student        |
| students       |
| studentss      |
| studentsss     |
+----------------+
4 rows in set (0.00 sec)

MariaDB [test]> drop table student;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| students       |
| studentss      |
| studentsss     |
+----------------+
3 rows in set (0.00 sec)

插入数据

mysql使用insert into sql语句来插入数据。

MariaDB [test]> insert into studentsss(id,username,time) values (4,'xiaoming','2019-02-28');

MariaDB [test]> select * from studentsss;
+----+----------+------------+
| id | username | time       |
+----+----------+------------+
|  4 | xiaoming | 2019-02-28 |
+----+----------+------------+

MariaDB [test]> insert into studentsss(id,username) values (5,'xiao');

MariaDB [test]> select * from studentsss;
+----+----------+------------+
| id | username | time       |
+----+----------+------------+
|  4 | xiaoming | 2019-02-28 |
|  5 | xiao     | NULL       |
+----+----------+------------+

MariaDB [test]> insert into studentsss(username,time) values ('ming',2019-02-27);

MariaDB [test]> select * from studentsss;
+----+----------+------------+
| id | username | time       |
+----+----------+------------+
|  0 | ming     | 0000-00-00 |
|  4 | xiaoming | 2019-02-28 |
|  5 | xiao     | NULL       |
+----+----------+------------+

MariaDB [test]> insert into studentsss(id,username,time) values (1,'ming','2019-02-27');

MariaDB [test]> select * from studentsss;
+----+----------+------------+
| id | username | time       |
+----+----------+------------+
|  0 | ming     | 0000-00-00 |
|  1 | ming     | 2019-02-27 |
|  4 | xiaoming | 2019-02-28 |
|  5 | xiao     | NULL       |
+----+----------+------------+

数据库查询数据

mysql数据库使用select 命令查询数据；

limit限制查询数量

selct column_nme,column_name1 from table_name where clause limit N offset M

可以查询一个或者多个表，表之间使用逗号隔开，使用where语句来设定查询条件；
也可以查询一条或者多条命令，*会返回数据表的所有字段；
limit属性设定返回的记录数(参数是限制数据数量)，offset指定select语句开始查询的数据偏移量。默认情况下偏移量为0。（参数为索引值,从M+1行开始查询数据）

查询所有的数据字段

select * from 表名；

查询指定字段

select  column（指定字段） from 表名

查询指定字段的数据

select * from 表名  where  column=value(指定字段满足某个条件)

条件查询
mysql可以有条件查询选取数据，使用where子句添加到select语句中；

select * from table1,table2 where 条件1 [and | or] 条件2

模糊查询
like模糊查询，通常与%一起使用；

select * from 表名 where username like '%om%';

查询空值与非空值
空值不是指数据没有或者为0，而是指该数据段没有被添加或者插入默认为null；

select * from 表名 where column is null; #查询指定字段为空值
select * from 表名 where column is not null; #查询指定字段为非空值

带in关键字查询
返回满足指定条件的数据

select * from 表名 where column in (条件1,条件2,....);

between and
表示在...到...范围内满足条件；

select * from 表名 where column in (范围开始,范围截止);

带and的多条件查询
相当于逻辑与，必须同时满足条件才返回值

select * from 表名 where 条件1 and 条件2 。。。。

带or的多条件查询
相当于逻辑或,只要满足其中一个条件即可

select * from 表名 where 条件1 or 条件2 。。。。

查询数据的排序
在数据库中使用order来设置排序列，ASC默认按升序排序，DESC按降序进行排序；

MariaDB [test]> select * from studentsss order by id ASC;
+----+----------+------------+
| id | username | time       |
+----+----------+------------+
|  0 | ming     | 0000-00-00 |
|  1 | ming     | 2019-02-27 |
|  4 | xiaoming | 2019-02-28 |
|  5 | xiao     | NULL       |
+----+----------+------------+
MariaDB [test]> select * from studentsss order by id DESC;
+----+----------+------------+
| id | username | time       |
+----+----------+------------+
|  5 | xiao     | NULL       |
|  4 | xiaoming | 2019-02-28 |
|  1 | ming     | 2019-02-27 |
|  0 | ming     | 0000-00-00 |
+----+----------+------------+

分组查询
group by命令，根据一个或多个类对结果集进行分组；分组是默认将查询结果去重操作，

select  a,b from 表名 group by a;

分组之后可以使用条件过滤，使用关键字having,该关键字是在分组之后进行过滤的；

select a,b from A group by a having a>1;

with rollup可以实现在分组统计的基础上再进行相同的统计（sum,avg,count）

select name,sum(singin) as singin_count from table group by name with rollup;(将该表以name进行分组，再次统计每个人登陆次数)

union操作符
union操作符用于连接两个以上的select语句的结果组合到一个结果集合中。多个select 语句会删除重复的数据。

（用于将不同表中相同列中数据查询出来）
select a from A union select a from B order by a:
 a,b是要检索的列，A，B要检索对的数据表，where 条件；
 order by a表示将返回的结果有小到大进行排序；
 union默认在排序的情况下已经将重复的数据删除。

（用于将不同表中不相同列中数据查询出来）
select a from A union all select a from B order by a:
union all 返回的数据结果不进行重复删除；

防止表中出现重复数据

数据表设定字段为primary key主键或者unique唯一索引保证数据的唯一性；

在创建数据表时，无索引和主键可允许该表出现多条重复记录。

#无索引和主键的建表语句
create table person(
firstname char(20),
lastname char(20),
sex char(10)
);

在建表时，可以设置双主键来保证数据的唯一性；主键不能为空；

create table person(
firstname char(20)，
lastname char(20),
sex char(10)
primary key (firstname,lastname)
);

insert ignore into 会忽略数据库存在的数据进行插入数据；

数据库的别名

在sql语言中，有两种方法去实现表的别名

使用关键字as指定

select a.id,a.name,b.id,b.sex from tbla as a,tblb as b where 条件；

在表名后直接添加表的别名
```
select a.id,a.name,b.id,b.sex from tbla  a,tblb  b where 条件；
```
注意：
1.别名通常是缩短了表名，如果连接中的多个表含有相同名称列存在，必须使用表名或标的别名限定列名；
2.定义了标的别名就不能在使用表名；

数据库的多表查询

多表连接的分类：
内连接，外链接，交叉连接
多表连接的每个分类有哪些连接方法：
内连接：inner join,join;获取两个表中字段匹配关系的记录；
外链接：left (outer) join：获取左表所有记录，及时右边没有对应的匹配记录；,right (outer) join：用于获取右表所有的记录，及时左表没有对应的匹配的记录；union
交叉连接：cross join，

内连接
也被称作等值连接；

内连接,得到满足某一条件的a,b内部的数据；内部共有数据所以称为内连接；on限定条件
MariaDB [test]> select a.*,b.* from tbla a inner join tblb b on a.id=b.id;
+----+-----+----+------+
| id | age | id | name |
+----+-----+----+------+
|  2 |  11 |  2 | 4    |
|  3 |  12 |  3 | 3    |
|  4 |  13 |  4 | 4    |
+----+-----+----+------+
MariaDB [test]> select a.*,b.* from tbla a join tblb b on a.id=b.id;
+----+-----+----+------+
| id | age | id | name |
+----+-----+----+------+
|  2 |  11 |  2 | 4    |
|  3 |  12 |  3 | 3    |
|  4 |  13 |  4 | 4    |
+----+-----+----+------+

外链接之左连接
left join 和 left outer join等同；
匹配左边，如果右边不存在匹配值也将显示左边，右边是有null值替换；
在mysql数据库中，左连接和有连接的使用方法相同；

ariaDB [test]> select a.*,b.* from tbla a left join tblb b on a.id=b.id;
+----+-----+------+------+
| id | age | id   | name |
+----+-----+------+------+
|  2 |  11 |    2 | 4    |
|  3 |  12 |    3 | 3    |
|  4 |  13 |    4 | 4    |
|  1 |  10 | NULL | NULL |
+----+-----+------+------+
MariaDB [test]> select a.*,b.* from tbla as a left outer join tblb as b on a.id=b.id;
+----+-----+------+------+
| id | age | id   | name |
+----+-----+------+------+
|  2 |  11 |    2 | 4    |
|  3 |  12 |    3 | 3    |
|  4 |  13 |    4 | 4    |
|  1 |  10 | NULL | NULL |
+----+-----+------+------+

left join 或者 left outer join + where b.column is （not）null;

MariaDB [test]> select a.*,b.* from tbla as a left join tblb as b on a.id=b.id where b.id is null;
+----+-----+------+------+
| id | age | id   | name |
+----+-----+------+------+
|  1 |  10 | NULL | NULL |
+----+-----+------+------+
1 row in set (0.00 sec)

MariaDB [test]> select a.*,b.* from tbla as a left join tblb as b on a.id=b.id where b.id is not null;
+----+-----+----+------+
| id | age | id | name |
+----+-----+----+------+
|  2 |  11 |  2 | 4    |
|  3 |  12 |  3 | 3    |
|  4 |  13 |  4 | 4    |
+----+-----+----+------+

full join 在mysql中不支持，left join + union + right join结合使用

交叉连接
笛卡尔积，返回的结果为被连接的两个数据表的乘积

MariaDB [test]> select a.id,a.age,b.id,b.name from tbla as a cross join tblb as b;
+----+-----+----+------+
| id | age | id | name |
+----+-----+----+------+
|  1 |  10 |  2 | 4    |
|  2 |  11 |  2 | 4    |
|  3 |  12 |  2 | 4    |
|  4 |  13 |  2 | 4    |
|  1 |  10 |  3 | 3    |
|  2 |  11 |  3 | 3    |
|  3 |  12 |  3 | 3    |
|  4 |  13 |  3 | 3    |
|  1 |  10 |  4 | 4    |
|  2 |  11 |  4 | 4    |
|  3 |  12 |  4 | 4    |
|  4 |  13 |  4 | 4    |
|  1 |  10 |  5 | 5    |
|  2 |  11 |  5 | 5    |
|  3 |  12 |  5 | 5    |
|  4 |  13 |  5 | 5    |
+----+-----+----+------+
16 rows in set (0.00 sec)

MariaDB [test]> select a.id,a.age,b.id,b.name from tbla as a cross join tblb as b where a.id=b.id
    -> ;
+----+-----+----+------+
| id | age | id | name |
+----+-----+----+------+
|  2 |  11 |  2 | 4    |
|  3 |  12 |  3 | 3    |
|  4 |  13 |  4 | 4    |
+----+-----+----+------+

获取数据库的结构

desc命令获取数据表结构

MariaDB [test]> desc tbla;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
| age   | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

另外一种获取数据结构方法

show columns from 表名

数据库的update

通过使用updata命令修改数据库表的数据；
UPDATE tablename SET columnName=NewValue [ WHERE condition ]；

MariaDB [test]> select * from studentsss;
+----+----------+------------+
| id | username | time       |
+----+----------+------------+
|  0 | ming     | 0000-00-00 |
|  1 | ming     | 2019-02-27 |
|  2 | ??     | 2019-02-26 |
|  4 | xiaoming | 2019-02-28 |
|  5 | xiao     | NULL       |
+----+----------+------------+

MariaDB [test]> update studentsss set username='hello' where id=2;

MariaDB [test]> select * from studentsss;
+----+----------+------------+
| id | username | time       |
+----+----------+------------+
|  0 | ming     | 0000-00-00 |
|  1 | ming     | 2019-02-27 |
|  2 | hello    | 2019-02-26 |
|  4 | xiaoming | 2019-02-28 |
|  5 | xiao     | NULL       |
+----+----------+------------+

数据库 DELETE命令

在mysql中使用delete form 删除数据库表的数据
#delete from 表名使用where指定条件，如果没有指定where条件，将会删除所有记录。

MariaDB [test]> delete from studentsss where id=2;

MariaDB [test]> select * from studentsss;
+----+----------+------------+
| id | username | time       |
+----+----------+------------+
|  0 | ming     | 0000-00-00 |
|  1 | ming     | 2019-02-27 |
|  4 | xiaoming | 2019-02-28 |
|  5 | xiao     | NULL       |
+----+----------+------------+

数据库的索引

索引可以提高对于mysql的检索速度，但是会降低更新表的速度。因为在更新时保存数据时也会在保存索引文件；

索引分类

单列索引即一个索引只包含单个列，一个表可以有多个单列索引，但不是组合索引
组合索引，即一个索引包含多个列;

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表记录。
建立索引也会占用磁盘空间的索引文件

普通索引

创建普通索引

create index indexname on mytable(username(length));

修改表结构，添加索引

alter table tablename add index indexname();

创建表的时候指定索引

alter table tablename add index indexname();

删除索引
```
drop index indexname on tblname;
```
唯一索引
唯一索引的索引列的值必须是唯一，单允许有空值。如果是组合索引，则列值的组合必须唯一。

创建唯一索引

create unique index indexname on tblname;

修改表索引
```
alter table tblname add uniq
```

alter命令添加和删除索引

alter table tblname add primary key(column_list);
alter table tblname add unique indexname(column_list);
aletr table tblname index indexname(column_list);
alter table tblname add fulltext indexname(column_list);

查看表格的索引信息

show index from tblname;\G  #\G表示格式化输出；

数据库复制完整表

数据库表完整复制表，包括结构，索引，默认值等；

获取需要复制的数据库表的建表语句，里面包含原数据表的结构，索引等。
```
show create table 表名\G；
```
将获取的建表语句复制修改表名在数据库执行；
使用插入语句来复制内容；
```
inset  sql语句插入数据
```

数据库的临时表

数据库的表保存一些临时数据，断开连接或者客户端连接时，数据库会销毁该临时表；

创建临时表，在查询数据库时会看不见该临时数据库表
```
create temporary table 表名（列名）；
```
删除数据表
```
drop tabel 临时表名;
```
数据库的事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
在mysql中只有使用InnoDB数据库引擎的数据库才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的sql语句要么全部执行，要么全部不执行；
事务用来管理insert,update,delete语句等；

事务控制语句

BEGIN或START TRANSACTION；显式地开启一个事务；

COMMIT：COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的；

ROLLBACK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT identifier：SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；

RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO identifier；把事务回滚到标记点；

SET TRANSACTION：用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

mysql事务处理的方法
1.使用begin,rollback,commit来实现

begin开始一个事务；
rollback事务回滚；
commit事务提交，事务确认；

2.使用set来改变mysqlz的自动提交模式；

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

数据库alter命令

alter命令修改数据库表名或者修改数据库表字段；

删除，添加或者修改表字段
使用alter命令和drop命令删除字段；

MariaDB [test]> select * from studentsss;
+----+----------+------------+
| id | username | time       |
+----+----------+------------+
|  0 | ming     | 0000-00-00 |
|  1 | ming     | 2019-02-27 |
|  4 | xiaoming | 2019-02-28 |
|  5 | xiao     | NULL       |
+----+----------+------------+
4 rows in set (0.00 sec)
MariaDB [test]> alter table studentsss drop time;
Query OK, 4 rows affected (0.05 sec)               
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from studentsss;
+----+----------+
| id | username |
+----+----------+
|  0 | ming     |
|  1 | ming     |
|  4 | xiaoming |
|  5 | xiao     |
+----+----------+
4 rows in set (0.00 sec)

使用alter和add命令插入字段

MariaDB [test]> alter table studentsss add time DATE;
Query OK, 4 rows affected (0.01 sec)               
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from studentsss;
+----+----------+------+
| id | username | time |
+----+----------+------+
|  0 | ming     | NULL |
|  1 | ming     | NULL |
|  4 | xiaoming | NULL |
|  5 | xiao     | NULL |
+----+----------+------+
4 rows in set (0.00 sec)
指定新增字段的位置，可以使用关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）
MariaDB [test]> alter table studentsss add sex int after username;
Query OK, 4 rows affected (0.01 sec)               
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from studentsss;
+----+----------+------+------+
| id | username | sex  | time |
+----+----------+------+------+
|  0 | ming     | NULL | NULL |
|  1 | ming     | NULL | NULL |
|  4 | xiaoming | NULL | NULL |
|  5 | xiao     | NULL | NULL |
+----+----------+------+------+
4 rows in set (0.00 sec)

MariaDB [test]> alter table studentsss add classname varchar(20) first;
Query OK, 4 rows affected (0.02 sec)               
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from studentsss;
+-----------+----+----------+------+------+
| classname | id | username | sex  | time |
+-----------+----+----------+------+------+
| NULL      |  0 | ming     | NULL | NULL |
| NULL      |  1 | ming     | NULL | NULL |
| NULL      |  4 | xiaoming | NULL | NULL |
| NULL      |  5 | xiao     | NULL | NULL |
+-----------+----+----------+------+------+
4 rows in set (0.00 sec)

修改字段类型以及名称
如果需要修改字段类型以及名称，在alter命令中使用modify或者change子句；

#将字段classname的varchar（20）改为varchar(10);
MariaDB [test]> alter table studentsss modify classname varchar(10);
Query OK, 4 rows affected (0.01 sec)               
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test]> desc studentsss;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| classname | varchar(10) | YES  |     | NULL    |       |
| id        | int(11)     | NO   | PRI | NULL    |       |
| username  | varchar(20) | NO   |     | NULL    |       |
| sex       | int(11)     | YES  |     | NULL    |       |
| time      | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型;

MariaDB [test]> alter table studentsss change username studentname varchar(10);
Query OK, 4 rows affected (0.01 sec)               
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test]> desc studentsss;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| classname   | varchar(10) | YES  |     | NULL    |       |
| id          | int(11)     | NO   | PRI | NULL    |       |
| studentname | varchar(10) | YES  |     | NULL    |       |
| sex         | int(11)     | YES  |     | NULL    |       |
| time        | date        | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

alter对于null值和默认值的影响

修改字段时指定是否包含值或者是否设置默认值，如果不设置默认值，MySQL会自动设置该字段默认为 NULL。

修改字段默认值

#修改字段默认值
MariaDB [test]> alter table studentsss alter time set default 20190304;

MariaDB [test]> desc studentsss;
+-------------+-------------+------+-----+------------+-------+
| Field       | Type        | Null | Key | Default    | Extra |
+-------------+-------------+------+-----+------------+-------+
| classname   | varchar(10) | YES  |     | NULL       |       |
| id          | int(11)     | NO   | PRI | NULL       |       |
| studentname | varchar(10) | YES  |     | NULL       |       |
| sex         | int(11)     | YES  |     | NULL       |       |
| time        | date        | YES  |     | 2019-03-04 |       |
+-------------+-------------+------+-----+------------+-------+
5 rows in set (0.00 sec)

#删除字段默认值
MariaDB [test]> alter table studentsss alter time drop default;

MariaDB [test]> desc studentsss;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| classname   | varchar(10) | YES  |     | NULL    |       |
| id          | int(11)     | NO   | PRI | NULL    |       |
| studentname | varchar(10) | YES  |     | NULL    |       |
| sex         | int(11)     | YES  |     | NULL    |       |
| time        | date        | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

修改表名

MariaDB [test]> alter table studentsss rename to studentA;

MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| studentA       |
| students       |
| studentss      |
+----------------+

查询mysql数据库占用空间大小

information_schema中的表主要有
schemata表：这个表里面主要是存储在mysql中的所有的数据库的信息
tables表：这个表里存储了所有数据库中的表的信息，包括每个表有多少个列等信息。
columns表：这个表存储了所有表中的表字段信息。
statistics表：存储了表中索引的信息。
user_privileges表：存储了用户的权限信息。
schema_privileges表：存储了数据库权限。
table_privileges表：存储了表的权限。
column_privileges表：存储了列的权限信息。
character_sets表：存储了mysql可以用的字符集的信息。
collations表：提供各个字符集的对照信息。
collation_character_set_applicability表：相当于collations表和character_sets表的前两个字段的一个对比，记录了字符集之间的对照信息。
table_constraints表：这个表主要是用于记录表的描述存在约束的表和约束类型。
key_column_usage表：记录具有约束的列。
routines表：记录了存储过程和函数的信息，不包含自定义的过程或函数信息。
views表：记录了视图信息，需要有show view权限。
triggers表：存储了触发器的信息，需要有super权限。

1.进入information_schema 数据库

use information_schema;

2.查看所有数据库的大小

select concat(round(sum(data_length/1024/1024),2),'MB') as allsize from tables;
+---------+
| allsize |
+---------+
| 2.38MB  |
+---------+
1 row in set (0.06 sec)

data_length:计算表中列的数据类型存储的长度
concat(,'MB'):用于将两个字符串连接起来
round(x,d):x指要处理的数，d是指保留几位小数

3.查看指定数据库的大小

查询mysql数据库大小

select concat(round(sum(data_length/1024/1024),2),'MB') as allsize from tables where table_schema='mysql';
+---------+
| allsize |
+---------+
| 2.212MB |
+---------+
1 row in set (0.01 sec)

4.查看指定数据库的某个表的大小

查询数据库mysql的user表

select concat(round(sum(data_length),2),'K') as allsize from tables where table_schema='mysql' and table_name='user';
+---------+
| allsize |
+---------+
| 0.00MB  |
+---------+
1 row in set (0.00 sec)

select concat(round(sum(data_length),2),'K') as allsize from tables where table_schema='mysql' and table_name='user';
+---------+
| allsize |
+---------+
| 500.00K |
+---------+
1 row in set (0.00 sec)

performance_schema数据库作用

用于收集数据库服务器性能参数,MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表
performance_schema提供以下功能：
1.提供进程等待的详细信息，包括锁、互斥变量、文件信息；
2.保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；
3.对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）
通过以上得到的信息，DBA能够较明细得了解性能降低可能是由于哪些瓶颈？
l performance_schema功能开启和部分表功能
Performance的开启很简单，在my.cnf中[mysqld]加入performanc_schema，检查性能数据库是否启动的命令:
SHOW VARIABLES LIKE ‘performance_schema’;
若是返回的值为ON，则说明性能数据库正常开启状态。

数据库导出导入数据

导出数据
mysqldump是mysql 用于转存储数据库的实用程序。该命令是mysql数据库中备份工具，用于将MySQL服务器中的数据库以标准的sql语言的方式导出，并保存到文件中。备份的文件/ 到 /都是注释,其余都是sql语句，故备份恢复就相当于把所有sql语句执行一遍。

选项
--add-locks：备份数据库表时锁定数据库表导出后解锁数据库表；
--all-databases：备份MySQL服务器上的所有数据库；
--databases：指定要备份的数据库，参数后面所有名字参量都被看作数据库名；
--default-character-set：指定默认字符集；
--force：当出现错误时仍然继续备份操作；
--host：指定要备份数据库的服务器；
--lock-tables：备份前，锁定所有数据库表；
--no-create-db：只导出数据，禁止生成创建数据库语句；
--no-create-info：只导出数据，禁止生成创建数据库库表语句；
--password：连接MySQL服务器的密码；
--port：MySQL服务器的端口号；
--user：连接MySQL服务器的用户名；
--all-tablespaces ：导出全部表空间；--no-tablespaces：不导出任何表空间信息；
--ignore-table：不导出指定表。指定忽略多个表时，需要重复多次，每次一个表；
--lock-all-tables：提交请求锁定所有数据库中的所有表，以保证数据的一致性；
--no-data：不导出任何数据，只导出数据库表结构；
--tab ：选项来指定导出文件指定的目录，该目标必须是可写的；
{mysqldump -uroot -predhat --tab=/tmp/ 数据库表名}

select ... into outfile命令导出数据

select * from 表名 into outfile '/tmp/user.txt';

导入数据

使用<符号导入数据；
数据库使用source+文件导入数据；
load data 命令导入数据
load data local infile '文件' into table 表名;

mysql8.0安装
1.下载rpm包的压缩包，校验md5码；
2.解压文件，删除test的rpm包
3.安装数据库
4.启动数据库
5.过滤初始化密码
6.进入数据库修改root用户密码，修改之前先刷新权限；
ALTER USER 'root'@'localhost' IDENTIFIED by '新的密码';
7.创建一个可以远程的用户
create user '用户'@'%' identified with mysql_native_password by '新的密码';
8.开始授权
9.权限刷新

转载于:https://blog.51cto.com/13606177/2373221

weixin_33811539

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql数据库学习

数据库简介与类型什么是数据库？数据库是存放数据的仓库，按照一定的数据结果（数据结构是指数据的组织形式或数据之间联系）来组织，存储的。可以通过数据库提供的方法来管理数据库的数据。数据库种类早期比较流行的数据库模式有三种：层次式数据库，网络式数据库和关系型数据库。现在比较常用的数据库模型主要有两种：关系型数据库和非关系型数据库。关系型数据库关系型数据库模型是把复杂的数据结构归结为简单的二元关系（...
复制链接

扫一扫