MySQL数据库学习(一)

下篇地址：MySQL数据库学习(二）
1、什么是数据库

数据库是保存有组织的数据的容器，通常是一个文件或一组文件（一个有组织的数据文件库）

---

2、数据库管理软件

数据库是放在文件中，而管理该数据库的软件称为数据库管理软件（DBMS），对数据库的管理是通过数据库管理软件完成的。数据库管理软件主要有，MySQL，SQLSERVER。

---

3、mysql中的表（主键以及外键）

• mysql数据库的表是一个二维表，由一个或者多个数据构成，一个表由列和行组成，列是一个表的字段，比如一个顾客表中，一个列存储着顾客的编号，另一个列存储着顾客的姓名，表中的一行便是一个数据，唯一标识表的列被成为主键（注意唯一性），这个主键列中的行值被成为主键值。
• 列A存于表1和表2中，如果列A在表1中是主键，那么列A称为表2的外键，外键表示了两个表之间的相连关系。

---

4、mysql的数据类型
数据类型指的是列的数据类型，有三种，数值类，字符串类，日期时间类。
4.1 数值类的列类型包括整形和浮点型。

整形：
TINYINT：1字节，非常小的整数，带符号：-128~127，不带符号：0~255
SMALLINT：2字节小整数，带符号：-32768~32767，不带符号：0~65535
MEDIUMINT：3字节中等大小整数，带符号：-8388608~8388607，不带符号：0~4294967295
IN：4字节，标准整数。
BIGINT：8字节，大整数。

---

浮点型：
FLOAT：4字节，单精度浮点数。
DOUBLE：8字节，双精度浮点数。
DECIMAL：M+2字节以字符串形式表示的浮点数。

4.2 字符串的列类型包括整形和浮点型。
字符串可以用来表示任何一种值，是最基本的类型之一。

CHAR[(M)] ：M字节，M字节
VARCHAR[(M)]：M字节，L+1ZHIJIE1
TINYBLOD，TINYTEXT：2^8-1字节 L+1字节
BLOB，TXET：2^16-1字节 L+2字节
MEDIUMBLOB，MEDIUMTEXT：2^24-1字节 L+3字节
LONGBLOB，LONGTEXT：2^32-1字节 L+4字节
ENUM（‘value1’，‘value2’,….）：65535成员 1或2字节
SET（‘value1’，‘value’,…）：64成员 1，2，3，4或者8字节

CHAR和VARCHAR是最常用的两种字符串类型，CHAR是固定长度的，每个值占用相同的字节，不够的位数MySQL会在它的右边用空格字符补足。VARCHAR是可变长度字符串，每个值占用其刚好的字节数再加上一个用来记录其长度的字节即L+1字节。

---

5、基本操作
这里用的是navicat 8进行操作。cmd登陆的语句为：mysql -u root -p

 >show database; //查看数据库
 >create database mydb; //创建一个测试数据库
 >show create database mydb; //查看创建的数据库
 >use mydb; //切换mydb
 >show table; //查看mydb中的表 （显然这里是空的）
 >desc 表名; //查看表的内容（desc=describe）
 >show status; //查看服务器的状态
 >show grants; //查看数据库用户的权限
 >show errors; //查看mysql服务器的错误日志
 >show warnings; //查看警告日志

 >use mydb; //切换mydb

 //mydb中，创建一个表，noll指的是允许插入时不给值，not noll指的是带值插入,auto_increment指的是本列每增加一行自动增加1。
 >create table student(
 >   id int not null auto_increment,
 >   name char(10) not null,
 >   primary key(id))
 >   engine = innodb;
 > drop table student;//删掉这个表
 > rename table student to xuesheng; //表改名

 >show create table student; //查看创建的表
 >insert into student(id,name) values(0,'wanger'); //插入一条数据
 >select * from student;   //查看表中的数据
 >alter table student add score int; //给表增加一个列
 >alter table student drop column score; //删掉表中的一个列
 >alter table student change score sex char(10);//列改名
 >update student set name='zhangsan',score = 100 where id=0;//行改值
 >delete from student where id=0; //删掉id=0这一行
 >drop database mydb //删掉这个库 

---

6、数据库查询（查询列之前需要注意切换到当前数据库）
查找用select，注意查找只能查找列，*表示全部查找，列明表示全部查找。

 >select id from student;  //查询单个列
 >select id,name from student;  //查询多个列
 >select * from student;  //查询所有的列
 >select distinct id from student;  //去掉这个列下面重复的行
 >select id from student limit n,m;  //查询限定在第n行到第m行（从零开始的）

---

7、查询结果排序（order by）

> select name from student oeder by id;   //选择name下面所有的行，以id来排序（排序是以字母A到Z来排序）
> select name from student order by id desc; //默认查询结果是升序，使用关键字desc可以实现降序
> //找到倒数第一个
> select name from student order by id desc limit 1;  //查找最后一个

---

8、查询结果过滤（where）
数据库中有大量的数据，具体查到某一行，或者某一列中选取特定的行就需要过滤。注意同时使用排序 order by 和 where 时，order by 要放到最后

>select name from student where id=0;//查询name这一列过滤的条件是id=0

where 子句操作符

操作符	说明
=	等于
<	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between n and m	在n和m之间
null	无值，与零，空字符是不同的

**组合操作字符，**and / or，多条件以及组合条件，and的优先级大于or，组合时要用括号括起来，以免混乱。

 > select name from student where id>1 and scor>=80;//id大于1且分数大于等于80分
 > select name from student where id>1 or scor>=80; //id大于1或者分数大于等于80分的返回。
 > select name from student where （id>1 or id < 100 ) and scor>=80; //先括号再括号外面。

in 的条件操作
in表示括号内的每个条件都进行匹配，用逗号隔开。

> select name from student where id in (0,1);//id取0和1.

not 操作符
not 是否定之后所跟的任何条件，使用not 找出不匹配的行。

>select name from student where id not in (0,1); //id 除了0，1其他的。

like 操作符
查找表中是否包含某行，like 后面接通配符，对相匹配的进行查找，这里是指找到所有，%表示匹配多个字符，_表示匹配一个字符。

> select name from student where scor like 100; //找到考100分的
> select name from student where sex like '%a%'; //找到性别中有a的
> select name from student where sex like '_a%'; //找到性别中有a的

8、创建新字段
存储在表中的数据不是应用所需的，需要创建新的字段来表示数据库中的数据进行转换后的数据。用concat（）进行字符串拼接，给查询出来的数据列指定新名字

> select name from student order by id; //以id升序，选择name
> select concat('hha',name) from student id>0;
>  +--------------------+
   | concat('hha',name) |
   +--------------------+
   | hhawanger          |
   | hhahahha
   +--------------------+  

执行算术计算 mysql支持对查询出的数据进行算术计算。as作为别名。（ + - * / ）

//计算总分数
> select name,math+english as score from student order by name;

concat()函数扩展

> select concat('11','22','33');
> select concat('11','22',null);  //有null就为null
> select concat_ws(',','11','22','33');
>   +-------------------------------+
   | concat_ws(',','11','22','33') |
   +-------------------------------+
   | 11,22,33 |
   +-------------------------------+
> 

---

9、mysql函数
mysql 支持利用函数处理数据，函数在数据上执行，给数据的转换和处理提供了方便。支持的函数类型有：

1. 用于处理字符串的文本处理函数；
2. 用于在数值数据上进行算术操作的数值函数；
3. 用于处理日期的和时间的函数；
4. 返回数据库信息的系统函数；

文本处理函数如下：

函数	说明
left()	返回左边的字符
length()	返回字符串的长度
locate()	找出字符串的一个子串
lower()	将字符串转成小写
ltrim()	去掉字符串左边的空格
right()	返回字符串右边的字符
soundex()	返回字符串的soundex值
substring()	返回子串的字符串
upper()	将字符串转换成大写

使用upper（）函数将小写字母转换成大写字母

>  select id, upper(name) as name_upcase from student;
>  +----+-------------+
   | id | name_upcase |
   +----+-------------+
   |  0 | XIAOYUN     |
   |  1 | WANGER      |
   |  2 | HAHHA       |
   +----+-------------+

---

10、日期和时间处理函数

函数	说明
adddate()	增加一个日期（天，周）
addtime()	增加一个时间（时，分）
curdate()	返回当前日期
curtime()	返回当前时间
date()	返回日期
datediff()	计算两个日期之差
date_add()	日期相加运算
date_format()	返回格式化的日期
day()	返回日期的天数部分
dauoffweek()	返回日期的星期
hour()	返回日期的小时
minute()	返回日期的分钟
month()	返回日期的月份
now()	返回当前日期的和时间
second()	返回时间的秒部分
time()	返回一个日期时间的时间部分
year()	返回一个日期的年份

查找指定时间范围内的数据

> select id name from student where data(order_date) between '2002-09-01' and '2009-02-01';

数值处理函数

函数	说明
abs()	返回一个数的绝对值
cos()	返回一个角度的余弦
exp()	返回一个数的指数值
mod()	返回除操作的余数
pi()	返回圆周率
rand()	返回一个随机数
sin()	返回一个角度的正弦
sqrt()	返回一个数的平方根
tan()	返回一个角度的正切

获取一个随机数

> select rand();   

11、数据汇总
在应用中经常需要汇总数据，myaql提供了专门的聚集操作函数。函数也可以组合使用

函数	说明
avg()	返回某列的平均值
count()	返回某列的行数
max()	返回某列的最大值
min()	返回某列的最小值
sum()	返回某列值之和

 > select avg(score) as avg_score from mydb.student ;//计算全班平均分
 > select name，max(score) as max_score from mydb.student ; //查看最高分是谁
 > select count(score) from student where score=100;//统计100分的人数 

---

11、分组数据 （group by）
将数据分成多个组，以便对数据进行聚集计算。group by创建分组，但是不保证分组内部数据的排序，使用 order by对内部数据排序。（group by 是按照列来分组，必须出现在where之后，order by 之前。查询字段中出现聚合函数和普通列，一起查询的时候，那么分组的条件就是普通列，使用普通列进行分组查询要聚合的函数）
使用形式：

> select [聚合函数] 字段名 from 表名
>      [where 分组前行过滤] 
>      [group by 字段名] 
>      [having 分组后行过滤]

选择一列进行分组聚合，选择多列进行分组聚合。

>select avg(score),subject from student group by subject; //对学科进行分组，计算平均分。（一列）
>select avg(score),max(score),min(score),subject，Sex from student
    -> where score>=80
    -> group by subject,Sex
    -> having subject in ('math','eng')
>