MySQL(一)

数据库分类与常见数据库

• 关系型数据库与非关系型数据库
  • 关系型：采用关系模型(二维表)组织结构的数据库
  • 非关系型：不采用关系模型组织数据结构的数据库
• 开源与非开源
  • 开源：MySQL、SQLite、MongoDB
  • 非开源：Oracle、DB2、SQL_Server

MySQL

MySQL是开源数据库，瑞典专门的MySQL开发公司，将MySQL发展壮大，之后被Sun收购，如今Sun又被Oracle收购。

MySQL优点

• 开源数据库，使用C和C++编写；
• 可以工作在众多不同的平台上；
• 提供了用于C、C++、Python、Java、Perl、PHP、Ruby众多语言的API;
• 存储结构优良，运行速度快；
• 功能全面丰富

MySQL安装

• Ubuntu安装MySQL服务
  • 使用快捷键crtl+alt+T打开终端，执行：sudo apt install mysql-server
  • 配置文件位于：/etc/mysql
  • 数据库存储目录：/var/lib/mysql
• Windows安装MySQL服务
  • 下载MySQL安装包MySQL安装包
  • 直接运行文件安装

启动和连接MySQL服务

• 服务端
  • 查看MySQL状态：sudo service mysql status
  • 启动/停止/重启：sudo service mysql start/stop/restart
• 连接数据库

mysql -h 主机地址 -u 用户名 -p 

【注】

• 回车之后输入密码
• 如果连接自己主机上的数据库则可以省略**-h**选项
• 关闭连接

ctrl-D
exit

• MySQL数据库结构
  数据结构 => 记录 => 数据表 => 数据库
• 基本概念解析
  • 数据表(table)：存放数据的表格
  • 字段：每一列的列名，用来表示该列数据的含义
  • 记录：每一行，表示一组完整的数据

SQL

SQL是结构化查询语言(Structured Query Language)，一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存放数据以及查询、更新和管理关系数据库系。

• SQL语言特点
  -SQL语言基本独立于数据库本身；
  • 各种不同的数据对SQL语言支持与标准存在这细微地不同；
  • 每条SQL命令以 ; 结尾；
  • SQL命令(除了数据库名和表名)关键字和字符串可以不区分字母大小写

使用SQL管理MySQL

数据库基本操作

• 查看已有库

show databases;

• 创建库

create database 库名 [character set utf8];

【注】

• 数字、字母、下划线，但不能使用纯数字；
• 库名区分字母大小写；
• 不要使用特殊字符和sql关键字
• 切换库

use 库名;

• 查看已有库

select database();

• 删除库

drop database 库名;

数据类型

• 基本思考过程
  • 确定存储内容；
  • 明确字段构成；
  • 确定字段数据类型
• sql基础数据类型
  • 数字类型
    • 整数类型：INT，SMALLINT，TINYINT，MEDIUMINT，BIGINT；
    • 浮点类型：FLOAT，DOUBLE，DECIMAL
    • 比特值类型：BIT
      【注意】
• 对于准确性要求比较高的东西，比如内存，用decimal类型减少存储误差。声明语法：DECIMAL(M,D)；M是数字的最大数字位数，D是小数点右侧数字的位数。例如：DECIMAL(6,2)表示最大存储6位数字，其中小数点后占两位，取值范围是-9999.99 ~ 9999.99；
• 比特值类型指0，1表达两种情况，如真、假
• 字符串类型
  • 普通字符串：CHAR、VARCHAR；
  • 存储文本：text;
  • 存储二进制数据：BLOB；
  • 存储选项型数据：ENUM、SET
    【注意】
• char：字长，即指定存储字节数后，无论实际存储了多少字节的数据，最终都只占指定的字节大小，默认·只能存储1个字节的数据，存储效率高；
• varchar：不定长，效率偏低，但是节省空间。实际占用空间根据实际存储的数据大小而定，必须指定存储大小，例如：varchar(50);
• enum：用来存储给定的多个值中的一个值，例如：enum(‘A’,‘B’,‘C’);
• set用来存储给定的多个值中的一个或者多个值，例如：set(‘A’,‘B’,‘C’)

数据表基本操作

• 创建表

create table 表名(字段名 数据类型 约束,字段名 数据类型 约束,...字段名 数据类型 约束);

• 字段约束
  • 如果需要设置数字为无符号则加上 unsigned；
  • 如果不想字段为NULL可以设置字段的属性为NOT NULL，在操作数据库时如果输入该字段的数据为NULL，就会报错；
  • DEFAULT表示设置一个字段的默认值；
  • AUTO_INCREMENT定义为自增的属性，一般用于主键，数值会自动加1；
  • PRIMARY KEY关键字用于定义列为主键，主键的值不能重复且不允许为空

e.g.  创建班级表
create table class_1 (id int primary key auto_increment,name varchar(32) not null,age tinyint unsigned not null,sex enum('w','m'),score float default 0.0);

e.g. 创建兴趣班表
create table interest (id int primary key auto_increment,name varchar(32) not null,hobby set('sing','dance','draw'),level char not null,price decimal(6,2),remark text);

• 查看数据表

show tables;

• 查看数据表结构

desc 表名；

• 查看数据表创建信息

show create table 表名；

• 删除表

drop table 表名;

数据基本操作

• 插入(insert)

insert into 表名 values(值1),(值2),...;
insert into 表名(字段1,...) values(值1),...;

e.g. 
insert into class_1 values (2,'Baron',10,'m',91),(3,'Jame',9,'m',90);

insert into class_1 (name,age,sex,score) values ('Lucy',17,'w',81);

• 查询(select)

select * from 表名 [where 条件];
select 字段1,字段2 from 表名 [where 条件];

e.g. 
select * from class_1;
select name,age from class_1;

• where字句
  where字句在sql语句中扮演了重要角色，主要通过一定的运算条件进行数据的筛选，在查询、删除、修改中都可以使用
  1）算数运算符
  

e.g.
select * from class_1 where age % 2 = 0;

2）比较运算符

e.g.
select * from class_1 where age > 8;
select * from class_1 where between 8 and 10;
select * from class_1 where age in (8,9);

3）逻辑运算符

e.g.
select * from class_1 where sex='m' and age>9;

• 更新表记录(update)

update 表名 set 字段1=值1,字段2=值2,... where 条件;

【注意】
	update语句后如果不加where条件,所有记录全部更新

e.g.
update class_1 set age=11 where name='Abby';

• 删除表记录

delete from 表名 where 条件;

注意:delete语句后如果不加where条件,所有记录全部清空

e.g.
delete from class_1 where name='Abby';

• 表字段的操作

语法 ：alter table 表名 执行动作;

* 添加字段(add)
    alter table 表名 add 字段名 数据类型;
    alter table 表名 add 字段名 数据类型 first;
    alter table 表名 add 字段名 数据类型 after 字段名;
* 删除字段(drop)
    alter table 表名 drop 字段名;
* 修改数据类型(modify)
    alter table 表名 modify 字段名 新数据类型;
* 修改字段名(change)
    alter table 表名 change 旧字段名 新字段名 新数据类型;
* 表重命名(rename)
    alter table 表名 rename 新表名;

e.g. 
alter table interest add tel char(11) after name;

时间类型数据

- 日期：DATE
- 日期时间：DATETIME，TIMESTAMP
- 时间：TIME
- 年份：YEAR

• 时间格式

date ："YYYY-MM-DD"
time ："HH:MM:SS"
datetime ："YYYY-MM-DD HH:MM:SS"
timestamp ："YYYY-MM-DD HH:MM:SS"

【注意】
- datetime：以系统时间存储；
- timestamp：以标准时间存储但是查看时自动转换为系统时区，因此表现形式与datetime相同

e.g.
create table marathon (id int primary key auto_increment,athlete varchar(32),birthday date,registration_time datetime,performance time);

• 日期时间函数
  • **now()**返回服务器当前日期时间，格式为对应的datatime类型；
  • **curdate()**返回当前日期，格式为date类型；
  • **curtime()**返回当前时间，格式为time类型
• 时间操作
  时间类型可以进行比较和排序等操作，在写时间字符串时尽量按照标准格式书写。

  select * from marathon where birthday>='2000-01-01';
  select * from marathon where birthday>="2000-07-01" and performance<="2:30:00";

高级查询语句

• 查询过程顺序
  SQL语句一般的执行过程，理清SQL语句执行的先后顺序复杂的SQL语句就可以看的很清晰

(5)SELECT DISTINCT <select_list>                     

(1)FROM <left_table> <join_type> JOIN <right_table> ON <on_predicate>

(2)WHERE <where_predicate>

(3)GROUP BY <group_by_specification>

(4)HAVING <having_predicate>

(6)ORDER BY <order_by_list>

(7)LIMIT <limit_number>

• 模糊查询和正则查询
  LIKE用于在while字句中进行模糊查询，SQL LIKE字句中使用百分号**%**表示任意0个或多个字符，下划线_表示任意一个字符。

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1

e.g. 
mysql> select * from class_1 where name like 'A%';

• 正则查询
  SQL中对正则表达式的支持有限，只支持部分正则元字符。

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 REGEXP condition1

e.g. 
select * from class_1 where name regexp '^B.+';

• as用法
  用于给字段或者表重命名

select name as 姓名,age as 年龄 from class_1;
select * from class_1 as c where c.age > 17;

• ORDER BY排序
  ORDER BY字句用于设置字段以某种方式进行排序，然后返回搜索结果

SELECT field1, field2,...fieldN from table_name1 where field1
ORDER BY field1 [ASC [DESC]]

默认ASC表示升序，DESC表示降序

select * from class_1 where sex='m' order by age desc;

复合排序：对多个字段进行排序，即当第一排序项相同时按照第二排序项进行排序

select * from class_1 order by score desc,age;

• LIMIT限制
  LIMIT字句用于SELECT语句返回的数据数量或者UPDATE，DELETE语句操作数量
  带有LIMIT字句的SELECT语句的基本语法如下：

SELECT column1, column2, columnN 
FROM table_name
WHERE field
LIMIT [num]

• UNION联合查询
  UNION操作符用于连接两个以上的SELECT语句的结果组合到一个结果集合中，多个SELECT语句会删除重复的数据。
  语法格式：

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

默认UNION后参数为DISTINCT表示删除结果集中重复的数据，如果使用ALL则返回所有结果集，包含重复数据。

select * from class_1 where sex='m' UNION ALL select * from class_1 where age > 9;

• 子查询
  当一个SELECT语句中包含另一个SELECT查询语句时，称为有子查询的语句；
  1）子查询放在关键字from之后

select name from (select * from class_1 where sex='m') as s where s.score > 90;

【注意】
建议将子查询结果集重命名，便于WHERE字句的引用操作

2）子查询放在WHERE子句中
此时，子查询返回的结果作为外层查询的条件值

select *  from class_1 where age = (select age from class_1 where name='Tom');

【注意】

• 字句结果作为单个值使用时，返回的结果需要是一个明确值，不能多行或者多列；
• 字句结果作为集合使用时，即WHERE字句中是IN操作，则结果可以是一个字段的多个记录

进一步的操作请见enginelong的博客
OK，先这样吧！！！