MySQL_Base

来源：互联网发布：华尔街英语软件课程编辑：程序博客网时间：2024/06/05 15:25

MySQL Database学习

一、MyQSL介绍

数据库–MySql
- 数据仓库.就与我们之前学过的纯文本,properties这些技术一样.用来保存数据.并提供对数据进行增删改查的操作.我们以后做项目时,项目中的数据都是保存在数据库中的.
为什么要用数据库,数据库的特点
- 1>实现数据共享
- 2>减少数据的冗余度
- 3>数据实现集中控制
- 4>数据一致性,完整性和可维护性，以确保数据的安全性和可靠性
- 5>故障恢复
常用数据库
- 1>MySQL
  - MySQL是最受欢迎的开源SQL数据库管理系统，它由 MySQL AB开发、发布和支持。MySQL AB是一家基于MySQL开发人员的商业公司.MySQL是一个关系数据库管理系统。MySQL是开源的。已经被Oracle收购。
- 2>SQL Server
  - SQL Server是由微软开发的数据库管理系统,它只能在Windows上运行.
- 3.Oracle
  - 提起数据库，第一个想到的公司，一般都会是Oracle(甲骨文)。该公司成立于1977年，最初是一家专门开发数据库的公司。Oracle在数据库领域一直处于领先地位。 Oracle数据库成为世界上使用最广泛的关系数据系统之一。
- 4.Sybase（退隐）配套的数据库设计软件。 power designer 数据库设计软件。建模工具。
  - 1984年，Mark B. Hiffman和Robert Epstern创建了Sybase公司，并在1987年推出了Sybase数据库产品。
- 5.DB2 IBM开发的。也是收费数据库。
SQL
- DDL 数据库定义语言 Data Definition Language || create alter drop
- DCL 数据库控制语言 Data Control Language || grant rollback commit.
- DML 数据库操纵语言 Data Manipulation Language || insert update delete select|
- DQL 数据库查询语言 Data Query Language || select
SQL中的注释
- 单行 : –
- 多行 : /* */
sql不区分大小写吗?
语句不区分大小写.
数据区分大小写.
mysql中sql的结束使用”;”号表示.
退出的命令 => exit
连接数据库服务的命令
```
mysql -u root -p
```
- 根据提示输入密码 ,即可建立连接.

二、数据库操作介绍

2.1 数据库定义语言(库的操作) DDL

1.创建一个库
```
create database 库名称 [character set 码表名称 collate 字符校对集名称]create database day15;
```
- 1>看到Query OK, 1 row affected (0.00 sec) 表示执行成功.
- 2>sql语句语法: 结尾应使用”;”号.
- 3>字符校对集: 决定数据排序的。
2.显示mysql中都有哪些库了.
```
show databases;+--------------------+| Database           |+--------------------+| information_schema || day13             || mysql              || performance_schema || test               |+--------------------+
```
- 可以看到除了自己创建的库之外，还有一些其他库。其他库（除了test）不要乱动。因为是保存mysql的配置信息，账户信息等等。
  test库：自动创建用于测试的。

3.删除一个数据

drop database 数据库名称;drop database day15;

4.修改数据库码表和字符校对（不常用）

alter database 数据库名称 character set 数据库码表 [collate 校对集名称];alter database day15 character set utf8 collate utf8_bin;

5.当前要使用的库（重要）
```
use 库名use day15;
```
- 出现Database changed,说明切换完成.

6.查看当前选择的数据库

select database();+------------+| database() |+------------+| day15      |+------------+

7.显示创建库的语句.

show create database 数据库名称;show create database day15;| day15    | CREATE DATABASE `day15` /*!40100 DEFAULT CHARACTER SET utf8 COLLATEutf8_bin */ |

2.2 数据类型

数据库中的数据类型 (了解,知道在什么情况下应该使用什么类型的数据)

一。数字型

整型    TINYINT    1字节      byte    SMALLINT   2字节      short    MEDIUMINT  3字节      （常用）INT        4字节        int    BIGINT     8字节      long浮点型    FLOAT   单精度4字节      float    **DOUBLE  8字节           double     *DECIMAL 没有精度损失

DOUBLE 和 DECIMAL 区别？
- DOUBLE类型在运算时会有精度的缺失。
- DECIMAL 就是解决精度缺失问题的。（底层使用字符串来保存数字）
- 单纯想表示小数属性时，使用double。
- 需要频繁参与运算的小数，使用decimal。

二。字符串类型
- 注意: 字符串类型要使用单引号包裹.
```
短字符串类型    CHAR/VARCHAR  (最大长度255字节)               长字符串类型(流类型)TEXT/CLOB 保存文本(字符流) --> 当要保存的内容超过255字节时使用.  java中的writer 字符BLOB      保存字节(字节流) --> 开发中用不到                       java中的stream 字节Character Large Object binary Large Object
```
- 问题:char和varchar有什么区别?
  - char定长字符串.varchar表示变长字符串.同时指定长度为10。当存储 abc , char =》 ‘abc ’
    varchar => ‘abc’
  - 结论：开发中varchar用的最多。 char只在表示固定长度的枚举中使用。例如：性别（用01,02表示）
- 区别：
  - text：只能存储字符数据.
  - BLOB：可以存储字符和多媒体信息(图片声音图像)
三。日期和时间类型
```
    date   只记录日期    2015-01-14    time   只记录时间    11:36:25    year   只记录年      2015    datatime  又记录日期 又记录 时间    2015-01-14 11:36:25    timestamp 同上    2015-01-14 11:36:25
```
- 问题: datatime 和 timestamp 区别?
  - 这两种类型记录的数据是一模一样.
  - 区别在于插入的时候,如果插入datatime类型时,没有传值,那么该类型默认值就是null;如果插入timestamp类型时,没有传值,那么该类型默认值就是当前时间;

2.3 与创建表相关的语句(DDL) (记住,能够手写)

1.创建表

    CREATE TABLE table_name    (        field1  datatype  约束/主键约束 auto_increment,        field2  datatype  约束,        field3  datatype  约束    )[character set 字符集 collate 校对规则]

    create table t_user(        id int,        name varchar(20),        sal double(4,3),        birthday datetime,        hiredate timestamp    );

varchar最好指定长度
整型一般不指定.

2.查看当前库中有哪些表
```
show tables;
```

3.查看表的结构

desc 表名;     descriptiondesc t_user;

4.删除表
```
drop table 表名;drop table t_user;
```

5.添加一列

alter table 表名 add 列名 类型;alter table t_user add photo blob;

6.修改列的类型

alter table 表名 modify 列名 类型;alter table t_user modify photo varchar(20);

7.修改列的名称

alter table 表名 change  旧列名  新列名 数据类型；将 photo这一列 改名为 imagealter table t_user change photo image varchar(20);

8.删除某列

alter table 表名 drop 列名;alter table t_user drop image;

9.修改表的名称

rename table 旧表名 to 新名;rename table t_user to user;

10(用的极少)修改表的字符集. (如果创建表时不指定,默认使用数据库的字符集)

alter table 表名 character set 字符集 collate 校对集;alter table t_user character set utf8 collate utf8_bin;

2.4 列的约束 (掌握)

保证数据的完整性的.
- 1.非空约束(not null) 指定非空约束的列, 在插入记录时必须包含值.
- 2.唯一约束(unique) 该列的内容在表中. 值是唯一的.
- 3.主键约束(primary key) 当想要把某一列的值,作为该列的唯一标示符时,可以指定主键约束(包含非空约束和唯一约束). 一个表中只能指定一个主键约束列.
  - 主键约束 , 可以理解为非空+唯一.
  - 注意：并且一张表中只能有一个主键约束.
- 约束体现数据库的完整性.
  - 例如:创建带有约束的表
```
 
create table t_user2( 
id int primary key auto_increament,                 -- 员工编号 
name varchar(10) not null,      -- 员工姓名 
loginname varchar(10) not null unique,  -- 登陆名称 
password varchar(20) not null,  -- 密码 
age int(3) not null,                -- 年龄 
birthday datetime not null,     -- 生日 
hiredate timestamp not null     -- 入职日期 
); 
```
主键自动增长 (掌握)
- 注意：
  - 1.前提某个表的主键是数字. 我们可以将该主键设置为自增.
  - 2.使用主键自增可能会造成主键的断层。
  - 3.mysql,sqlserver,sqllite这三个数据库具有该功能.
  - 4.主键自增只能给主键约束的列加。
- 自增就是每次插入记录时不需要指定值. 该字段自己维护自己的值.
- 维护方式就是每次加1;
  - 语法:
```
 
create table t_user( 
        id  int primary key auto_increment, 
        password varchar(30) not null, 
        age     int not null, 
        birthday datetime not null, 
        hiredate timestamp not null, 
        number int unique 
    ); 
```

2.5 对表中数据的增删改

修改一条记录 (必须掌握)

update 表名 set 列名1 = 值 , 列名2 = 值 ....[where 条件1,条件2...]修改表中id为3 的记录, 将name修改为rose;    update t_user set name='rose' where id=3;    update t_user set name='rose';

删除记录语句 (必须掌握)
```
DELETE FROM   表名  [WHERE 条件];删除表中名称为’rose’的记录。 
DELETE FROM employee WHERE NAME=’rose’;
删除表中所有记录。 
DELETE FROM employee ;
3.使用truncate删除表中记录。    TRUNCATE TABLE employee;
```
- DELETE 删除和 TRUNCATE删除(了解) 两者有什么区别?
  - 首先,这两种都是删除表中的记录.
  - 不同的是:
    - 1. delete 是逐行标记删除. TRUNCATE 是将整张表包括表结构都移除,然后将表重新创建.
    - 1. delete DML语句。 TRUNCATE DDL语句。
    - 3。delete 删除的记录可以被恢复，TRUNCATE 不能回复。
    - 1. delete 不释放空间，TRUNCATE 释放空间.
    - 1. TRUNCATE 会提交事务. (还没学)

2.6 DQL语句(DML) 查询语句. (必须掌握)

<pre>    语法：      SELECT selection_list /*要查询的列名称*/      FROM table_list /*要查询的表名称*/      WHERE condition /*行条件*/      GROUP BY grouping_columns /*对结果分组*/      HAVING condition /*分组后的行条件*/      ORDER BY sorting_columns /*对结果排序*/      LIMIT offset_start, row_count /*结果限定*/;</pre>

1.1　查询所有行所有列
```
           select * from stu;
```
- - 号是通配符.通配所有列. 上面语句与下面是一模一样的
  - select sid,sname,age,gender from stu;谁的效率更高?
    - 下面的效率更高. *需要运算.
1.2　查询所有行指定列
```
    select sname from stu;
```
2.1　条件查询介绍
- 条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：
  - =、!=、<>、<、<=、>、>=；
  - BETWEEN…AND；
  - IN(SET)/NOT IN(SET)
  - IS NULL/IS NOT NULL
- 条件连接符
  - AND； &&
  - OR； ||
  - NOT； !

2.2　查询性别为女，并且年龄小于50的记录

    select * from stu where gender='female'  and age

2.7 where 字段 like ‘表达式’;

<pre>     % => 通配 通配任意个字符.    _ => 通配 通配单个字符.    说明: LIKE 条件后 根模糊查询表达式,  "_"==> 代表一个任意字符</pre>

3.1查询姓名由5个字母构成的学生记录
```
select * from stu where sname like '_____';
```
3.2查询姓名由5个字母构成，并且第5个字母为“i”的学生记录
```
select * from stu where sname like '____i';
```
3.3 查询姓名以“z”开头的学生记录
*说明: “%”该通配符匹配任意长度的字符.
```
select * from stu where sname like 'z%';
```
3.4查询姓名中第2个字母为“i”的学生记录
```
select * from stu where sname like '_i%';
```
3.5　查询姓名中包含“a”字母的学生记录
```
select * from stu where sname like '%a%';
```

4.1　去除重复记录

关键词: distinct => 去除重复查询结果记录.select gender from stu; ==> 出现大量重复的记录 select distinct gender from stu; =>去除重复的记录

4.2查看雇员的月薪与佣金之和

select sal*12+comm from emp; null与任何数字计算结果都是null.上面的写法是错误的.使用IFNULL(参数1,参数2) 函数解决. 判断参数1的值是否为null,如果为null返回参数2的值.select sal*12 + IFNULL(comm,0) from emp;这个函数在所有数据库通用吗？    不通用

4.3　给列名添加别名

select sal*12 + IFNULL(comm,0) as '年收入' from emp;select sal*12 + IFNULL(comm,0)  '年收入' from emp;select sal*12 + IFNULL(comm,0)  年收入 from emp;

5.1　查询所有学生记录，按年龄升序排序

asc: 升序
desc:降序

select * from stu order by age asc;默认就是升序select * from stu order by age;

5.2 查询所有学生记录，按年龄降序排序
```
select * from stu order by age desc;
```
5.3 查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序
```
select * from emp order by sal desc , empno asc;
```
聚合函数
- 聚合函数是用来做纵向运算的函数：
  - COUNT()：统计指定列不为NULL的记录行数；
  - MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
  - MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
  - SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
  - AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
6.1　COUNT
- 当需要纵向统计时可以使用COUNT()。
  - 1>查询emp表中记录数：
```
select count(*) from emp;
```
  - 2>查询emp表中有佣金的人数：
```
select count(*) from emp where comm is not null and comm >0;
```
  - 3>查询emp表中月薪大于2500的人数：
```
select count(*) from emp where sal > 2500;
```
  - 4>统计月薪与佣金之和大于2500元的人数：
```
select count(*) from emp where sal+IFNULL(comm,0) > 2500;
```
  - 5>查询有佣金的人数并且有领导的人数：
```
select count(*) from emp where comm > 0 and  mgr is not null;
```
6.2　SUM(计算总和)和AVG(计算平均值)
- 当需要纵向求和时使用sum()函数。
  - 1>查询所有雇员月薪和：
```
select sum(sal) from emp;
```
  - 2>查询所有雇员月薪和，以及所有雇员佣金和：
```
select sum(sal),sum(comm) from emp;
```
  - 3>查询所有雇员月薪+佣金和：
```
select sum(sal+IFNULL(comm,0)) from emp;
```
  - 4>统计所有员工平均工资：
```
   select avg(sal) from emp;
```
6.3　MAX和MIN
- 查询最高工资和最低工资：
```
 
select max(sal),min(sal) from emp; 
```

2.8 分组查询

当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部分来分组。
- 1>查询每个部门的部门编号和每个部门的工资和：
```
select deptno,sum(sal) from emp group by deptno;
```
- 2>查询每个部门的部门编号以及每个部门的人数：
```
select deptno,count(ename) from emp group by deptno;
```
- 3>查询每个部门的部门编号以及每个部门工资大于1500的人数：
```
select deptno,count(ename) from emp where sal>1500 group by deptno ;
```
HAVING子句
- 4>查询工资总和大于9000的部门编号以及工资和：
```
select deptno,sum(sal) from emp group by deptno having sum(sal)>9000;
```
- 使用having在分组之后加条件.
- where和having都可以加条件?
  - 1.where在分组之前加条件.
  - 2.having在分组之后加条件.
  - where的效率要远远高于having. 分组本身消耗资源非常大.

2.9 LIMIT（MySQL方言） (必须掌握)

LIMIT用来限定查询结果的起始行，以及总行数。
- 1>查询5行记录，起始行从0开始
```
select * from emp limit 0,5;
```
- 2>　查询10行记录，起始行从3开始
```
select * from emp limit 3,10;
```
- 3>如果一页记录为5条，希望查看第3页记录应该怎么查呢？
  - 第一页记录起始行为0，一共查询5行；
```
select * from emp limit 0,5;
```
  - 第二页记录起始行为5，一共查询5行；
```
select * from emp limit 5,5;
```
  - 第三页记录起始行为10，一共查询5行；
```
select * from emp limit 10,5;
```

三、总结

SQL语句
- DDL 定义 create drop alter show
- DML 操纵 insert delete update select ….
- DCL(没学)

增删改查库

1 创建

create database 库名 [character set utf8 collate 校对集]

2 删除
```
drop database 库名
```
3 查看库
```
show databases;
```

4 修改库的码表

alter databases 库名 character set utf8 collate 校对集

5 选择库
```
use 库名;
```
6 查看当前选择的库
```
select database();
```
7 查看建库语句
```
show create database 库名;
```

增删改查表
- 1 创建
```
create table 表名 (    列名 类型 约束  [主键自增],    列名 类型 约束,    .....)
```
- 2 删除表
```
drop table 表名
```
- 3 查表
```
show tables;
```
- 4 增加一列
```
alter table 表名 add 列名 类型;
```
- 5 修改一列的类型
```
alter table 表名 modify 列名 新类型;
```
- 6 修改列名
```
alter table 表名 change 旧列名 新列名 类型;
```
- 7 删除一列
```
 alter table 表名 drop 列名;
```
- 8 修改表名
```
rename table 旧表名 to 新表名
```
- 9 查看表详情
```
desc 表名
```
- 数据类型
  - 数字型
    - 整型
      - int
    - 浮点型
      - double
      - decimal
  - 字符串
    - 短字符串
      - char
      - varchar
    - 长字符串(不常用)
      - text/clob
      - blob
  - 日期型
    - datatime 年月日时分秒
    - timestamp
  - 约束
    - 非空 not null
    - 唯一 unique
    - 主键 primary key
  - 主键自增
    - 整数类型的主键.可以由数据库自动维护. 每次自动加1.

增删改查数据

1.增加

insert into 表名 [(列名,列名...)] values (值,值,值...)

2.删除
```
delete from 表名 [where 条件]
```

3.改

update 表名 set 列名=值,列名=值 .. [where 条件]

4.查

select 列名...from 表名...where 条件...group by 列名...having 条件...order by 列名 升/降序limit x,y

条件
- < <= > >= != <> =
- between .. and ..
- in (set) / not in (set)
- is null / is not null
- not and or
模糊查询
- 关键词:like
- 匹配符:_ %
null的特性
- 1.与null运算 ,结果还是null => ifnull函数
- 1. null不等于null => is null
聚合函数
- 总数: count
- 平均值: avg
- 总和 : sum
- 最大/最小: max / min

1 0