MySQL(1)

数据库概念

1、什么是数据库？

　　数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和复制所保存的数据。我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

　　所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
RDBMS即关系数据库管理系统(Relational Database Management System)的特点：
1.数据以表格的形式出现
2.每行为各种记录名称
3.每列为记录名称所对应的数据域
4.许多的行和列组成一张表单
5.若干的表单组成database

也可以简单把数据库理解为一个文件夹，而里边存储的 excle 表格就是存储的 tables .

2、什么是MySQL、Oracle、SQLite、Access、MS SQL Server等？

他们均是一个软件，都有两个主要的功能：

a. 将数据保存到文件或内存
b. 接收特定的命令，然后对文件进行相应的操作

PS：如果有了以上软件，无须自己再去创建文件和文件夹，而是直接传递命令给上述软件，让其来进行文件操作，他们统称为数据库管理系统（DBMS，Database Management System）

3、什么是SQL ？
　　上述提到MySQL等软件可以接受命令，并做出相应的操作，由于命令中可以包含删除文件、获取文件内容等众多操作，对于编写的命令就是是SQL语句。SQL􏰉􏵮􏵯􏰟，是􏵱􏰚􏵲􏵳􏵴􏰇􏰈􏵱􏰚􏵲􏵳􏵴􏰇􏰈结构化语言（Structured Query Language􏰕􏰐􏵵􏰯）的缩写，SQL􏰜􏰖􏰩􏰽􏵶􏱥􏲲􏰄􏰫􏰬􏰭􏵁􏵷􏰐􏰇􏰈是一种专门用来与数据库通信的语言。

4、RDBMS：

数据库: 数据库是一些关联表的集合。
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
冗余：存储两倍数据，冗余可以使系统速度更快。(表的规范化程度越高，表与表之间的关系就越多；查询时可能经常需要在多个表之间进行连接查询；而进行连接操作会降低查询速度。例如，学生的信息存储在student表中，院系信息存储在department表中。通过student表中的dept_id字段与department表建立关联关系。如果要查询一个学生所在系的名称，必须从student表中查找学生所在院系的编号（dept_id），然后根据这个编号去department查找系的名称。如果经常需要进行这个操作时，连接查询会浪费很多的时间。因此可以在student表中增加一个冗余字段dept_name，该字段用来存储学生所在院系的名称。这样就不用每次都进行连接操作了。)
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
外键：外键用于关联两个表。
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

安装MySQL:

　　Windows、Linux、Mac

MySQL基本操作:

链接数据库：

mysql -u root -p #终端输入之后会提示输入密码
Enter password: ********* #输入密码

>>>>
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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>

显示所有数据库：

show databases;

for example：
>>>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |  
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
默认数据库：
　　mysql - 用户权限相关数据
　　test - 用于用户测试数据
　　information_schema - MySQL本身架构相关数据

创建数据库：

create database db_1

>>>
mysql> create database db_1;  #创建名字为db_1的数据库
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| db_1   -创建的数据库  |
+--------------------+
5 rows in set (0.00 sec)

使用数据库：

use db_1;

>>>
mysql> use db_1;
Database changed

显示数据库中的所有表格：

show tables;

>>>
mysql> show tables;
Empty set (0.00 sec)

# 因为是新创建的数据库 所以里边是空的，接下来我们创建一个表格。

创建表格：

create table 表名(
    列名  类型  是否可以为空 默认值，
    列名  类型  是否可以为空 默认值
)ENGINE=InnoDB DEFAULT CHARSET=utf8

>>>
create table tb_1(nid int not null auto_increment,       # 创建一个列nid值的类型为int型、不许为空、自增
                  name char(10) not null,  　　　　    # 创建一个列为name（类型为char ，每个value最多占用10个字节）
　　　　　　　　　　 primary key(nid)　　　　　　　　　　　 # 设置主键为 nid
)ENGINE=InnoDB DEFAULT CHARSET=utf8                # 设置引擎为innoDB  、默认编码格式为utf-8

- 是否为空：
　　not null 不为空
　　null 　　 空
- 默认值：
　　创建时可指定默认值，当插入数据时，没有指定值的时候，以默认值填充。

- 自增：auto_increment
　　如果创建某列时，设置为自增队列，那么在插入value时，不需对此列设置，默认将自增（表中只能有一个自增列）

   注意：1、对于自增列，必须是索引（含主键）。
        2、对于自增可以设置步长和起始值
         show session variables like 'auto_inc%';
         set session auto_increment_increment=2;
         set session auto_increment_offset=10;
　　　　　 show global  variables like 'auto_inc%';
　　      set  global auto_increment_increment=2;
   　　   set  global auto_increment_offset=10;

- 主键：
　　一种唯一的值，不许为空，如果以单一的列为主键，则不允许主键重复，如以两个列为主键，则组合保持唯一性。
- 外键
　　一种特殊的索引，只能指定内容。

删除表格：

drop table 表名字

>>>
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| tb_1          |
+---------------+
1 row in set (0.00 sec)

mysql> drop table tb_1;   #删除表格tb_1
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

清空表格：

清空表名有两种操作
1、delete from 表名;
这种清空方法如果在表内有自增主键，在清空后重新插入新的值后，主键的值会根据清空前的值继续自增。
>>>
+-----+-------+
| nid | name  |
+-----+-------+
|   1 | tom   |
|   2 | jerry |
+-----+-------+
2 rows in set (0.00 sec)

mysql> delete from tb_1;
Query OK, 2 rows affected (0.00 sec)
insert into tb_1(name)values('Shylock'),('Aileen');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select *from tb_1;
+-----+---------+
| nid | name    |
+-----+---------+
|   3 | Shylock |
|   4 | Aileen  |
+-----+---------+
2 rows in set (0.00 sec)

2、truncate table 表名;
　　truncate 会完全清空数据，并将初始化自增值。
>>>
truncate table tb_1;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb_1(name)values('Shylock'),('Aileen');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select *from tb_1;
+-----+---------+
| nid | name    |
+-----+---------+
|   1 | Shylock |
|   2 | Aileen  |
+-----+---------+
2 rows in set (0.00 sec)

修改表格：

1、添加列：

alter table 表名 add 列名 类型
>>>
+-----+---------+
| nid | name    |
+-----+---------+
|   1 | Shylock |
|   2 | Aileen  |
+-----+---------+
2 rows in set (0.00 sec)

mysql> alter table tb_1 add age int;  # 添加列：age int
Query OK, 0 rows affected (0.04 sec)
mysql> select *from tb_1;
+-----+---------+------+
| nid | name    | age  |
+-----+---------+------+
|   1 | Shylock | NULL |
|   2 | Aileen  | NULL |
+-----+---------+------+
2 rows in set (0.00 sec)

alter table 表名 add 列名类型

2、删除列：

+-----+---------+------+
| nid | name    | age  |
+-----+---------+------+
|   1 | Shylock | NULL |
|   2 | Aileen  | NULL |
+-----+---------+------+
2 rows in set (0.00 sec)

mysql> alter table tb_1 drop column age;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select *from tb_1;
+-----+---------+
| nid | name    |
+-----+---------+
|   1 | Shylock |
|   2 | Aileen  |
+-----+---------+
2 rows in set (0.00 sec)

alter table 表名 drop column 列名

3、修改列

　　alter table 表名 modify column 列名类型; -- 修改类型

+-----+---------+------+
| nid | name    | age  |
+-----+---------+------+
|   1 | Shylock | NULL |
|   2 | Aileen  | NULL |
+-----+---------+------+
2 rows in set (0.00 sec)

mysql> alter table tb_1 change age hobby char(10);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select *from tb_1;
+-----+---------+-------+
| nid | name    | hobby |
+-----+---------+-------+
|   1 | Shylock | NULL  |
|   2 | Aileen  | NULL  |
+-----+---------+-------+
2 rows in set (0.00 sec)

alter table 表名 change 原列名新列明类型

4、添加主键：

　　alter table 表名 add primary key(列名);

5、删除主键：

　　alter table 表名 drop primary key;

　　alter table 表名 modify 列名 int, drop primary key;

6、添加外键：

　　alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);

7、删除外键：

　　alter table 表名 drop foreign key 外键名称

8、修改默认值：

　　ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

9、删除默认值：

　　ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

MySQL数据类型：

　　MySQL支持所有标准SQL数值数据类型。

　　这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

　　关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

　　BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值(替代布尔值0/1）
SMALLINT	2 字节	(-32 768，32 767)	(0，65535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4294 967 295)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18446744073709551615)	极大整数值
FLOAT	4 字节	(-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.175494351E-38，3.402823466E+38)	单精度浮点数值
DOUBLE	8 字节	(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.2250738585072014E-308，1.7976931348623157E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期和时间类型:

　　表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

　　每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

　　TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	大小 (字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2037 年某时	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。

其他：

a、条件
    select * from 表 where id > 1 and name != 'alex' and num = 12;
 
    select * from 表 where id between 5 and 16;       - id 到16之间的值
 
    select * from 表 where id in (11,22,33)    　　　　 - id = 11、22、33
    select * from 表 where id not in (11,22,33)    　　 - id != 11、22、33
    select * from 表 where id in (select nid from 表)   
 
b、通配符
    select * from 表 where name like 'ale%'  - ale开头的所有（多个字符串）
    select * from 表 where name like 'ale_'  - ale开头的所有（一个字符）
 
c、限制
    select * from 表 limit 5;            - 前5行
    select * from 表 limit 4,5;          - 从第4行开始的5行
    select * from 表 limit 5 offset 4    - 从第4行开始的5行
 
d、排序
    select * from 表 order by 列 asc              - 根据 “列” 从小到大排列
    select * from 表 order by 列 desc             - 根据 “列” 从大到小排列
    select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序
e、分页：
　　select *from 表 limit 5，3;　　　　　　　　- 获取 第5行往下（不包括第5行）3行的数据
　　select *from 表 limit 3 offset 5；　　　  - 同上

f、分组 -更多
    select num from 表 group by num　　　　　　　　　　-根据 num相同的值 分组
    select num,nid from 表 group by num,nid
    select num,nid from 表  where nid > 10 group by num,nid order nid desc
    select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
 
    select num from 表 group by num having max(id) > 10
 
    特别的：group by 必须在where之后，order by之前
 
g、连表
    无对应关系则不显示
    select A.num, A.name, B.name
    from A,B
    Where A.nid = B.nid
 
    无对应关系则不显示
    select A.num, A.name, B.name
    from A inner join B
    on A.nid = B.nid
 
    以A为主，显示A表所有，如果B中无对应关系，则值为null
    select A.num, A.name, B.name
    from A left join B
    on A.nid = B.nid
 
    B表所有显示，如果B中无对应关系，则值为null （与left join相反）
    select A.num, A.name, B.name
    from A right join B
    on A.nid = B.nid
 
h、组合
    组合，自动处理重合
    select nickname
    from A
    union
    select name
    from B
 
    组合，不处理重合
    select nickname
    from A
    union all
    select name
    from B

posted @ 2017-07-25 11:36 LeeeetMe 阅读(168) 评论(0) 编辑收藏举报

刷新页面返回顶部