数据库的作用,数据库是存放数据的仓库
数据库：数据库中存放的是表，一个数据库中可以存放多个表
表：表是用来存放数据的。
启动/关闭MySQL服务
方法一：在服务面板中启动或关闭
控制面板项——管理工具——服务，选择相应服务，右键执行操作。
通过命令行启动\关闭
net start 服务名： 启动MySQL服务
net stop 服务器： 关闭MySQL服务
Structured Query Language（结构化查询语言），是用来操作关系型数据库的一门语言。这是一个关系型数据库的通用操作语言，也成为标准SQL，也叫SQL-92。
常见的关系型数据库
| 关系型数据库 | 开发公司 | 使用语言 |
| ------------ | --------------------------------- | -------- |
| SQL Server | 微软公司 | T-SQL |
| Oracle | 甲骨文公司 | PL/SQL |
| MySQL | MySQL AB 公司开发——甲骨文公司收购 | MySQL |
通过命令行面板连接
host：主机 -h
username：用户名 -u
password：密码 -p
port：端口 -P
关闭连接
方法一：exit
方法二：quit
方法三：\q
显示数据库
show databases
安装MySQL后，MySQL自带了4个数据库
1. information_schema：存储了MySQL服务器管理数据库的信息。
2. performance_schema：MySQL5.5新增的表，用来保存数据库服务器性能的参数
3. mysql：MySQL系统数据库，保存的登录用户名，密码，以及每个用户的权限等等
4. test：给用户学习和测试的数据库。
创建数据库
语法：create database [if not exists] `数据名` [字符编码]
创建数据库：
如果创建的数据库已存在，就会报错
解决：创建数据库的时候判断一下数据库是否存在，如果不存在再创建
如果数据库名是关键字和特殊字符要报错
解决：在特殊字符、关键字行加上反引号
创建数据库的时候可以指定字符编码
gbk 简体中文
gb2312： 简体中文
utf8： 通用字符编码
删除数据库
语法：drop database [if exists] 数据库名
删除数据库
如果删除的数据库不存在，会报错
解决：删除之前判断一下，如果存在就删除
显示创建数据库的SQL语句
语法：show create database 数据库名
修改数据库
修改数据库的字符编码
alter database 数据库名 charset=字符编码
选择数据库
use 数据库名
表的操作
显示所有表
show tables
创建表
create table [if not exists] 表名(
字段名 数据类型 [null|not null] [auto_increment] [primary key] [comment],
字段名 数据类型 [default]…
)engine=存储引擎
null | not null 空|非空
default 默认值
auto_increment 自动增长
primary key 主键
comment 备注
engine 引擎 innodb myisam memory 引擎是决定数据存储的方式
create table 数据库名.表名，用于给指定的数据库创建表
显示创建表的语句
show create table 表名
显示创建teacher表的语句
mysql> show create table teacher;
查看表结构
desc[ribe] 表名
删除表
drop table [if exists] 表1，表2,…
如果删除一个不存在的表就会报错，删除的时候可以判断一下，存在就删除。
可以一次删除多个表
修改表
语法：alter table 表名
添加字段：alter table 表名add [column] 字段名 数据类型 [位置]
删除字段：alter table 表 drop [column] 字段名
修改字段(改名改类型)：alter table 表 change [column] 原字段名 新字段名 数据类型 …
修改字段（不改名）:alter table 表 modify 字段名 字段属性…
修改引擎：alter table 表名 engine=引擎名
修改表名：alter table 表名 rename to 新表名
复制表
语法一：create table 新表 select 字段 from 旧表
特点：不能复制父表的主键，能够复制父表的数据
语法二：create table 新表 like 旧表
特点：只能复制表结构，不能复制表数据
插入数据
语法：insert into 表名 (字段名, 字段名,…) values (值1, 值1,…)
插入的字段可以和表的字段顺序不一致。值的顺序必须和插入字段的顺序一致。
可以插入部分字段，但是，非空字段必须插入
自动增长字段不用插入，数据库会自动插入增长的数字
自动增长列的值插入null即可
插入值的顺序和个数与表字段的顺序和个数一致，插入的字段可以省略
通过default关键字插入默认值
插入字段的顺序与值的顺序必须一致
插入多条数据
更新数据
update 表名 set 字段=值 [where 条件]
删除数据
delete from 表名 [where 条件]
清空表
truncate table 表名
delete from 表和truncate table 表区别？
delete from 表：遍历表记录，一条一条的删除
truncate table：将原表销毁，再创建一个同结构的新表。就清空表而言，这种方法效率高。
查询表
select 列名 from 表
SQL分类
DDL（data definition language）数据库定义语言CREATE、ALTER、DROP、SHOW
DML（data manipulation language）数据操纵语言SELECT、UPDATE、INSERT、DELETE
DCL（Data Control Language）数据库控制语言,是用来设置或更改数据库用户或角色权限的语句
数据表的文件介绍
一个数据库对应一个文件夹
一个表对应一个或多个文件
引擎是myisam，一个表对应三个文件
引擎是innodb,一个表对应一个表结构文件
所有的innodb引擎的数据统一的存放在data\ibdata1文件中。如果数据量很大，MySQL会自动的创建ibdata2，ibdata3，…，目的就是为了便于管理。
引擎是memory，数据存储在内存中，重启服务数据丢失，但是读取速度非常快。
字符集
字符集：字符在保存和传输时对应的二进制编码集合。
总结：客户端编码、character_set_client、character_set_results三个编码的值一致即可操作中文。
我们只要设置“set names 字符编码”，就可以更改character_set_client、character_set_results的值。
数据类型——值类型
整型
| 类型 | 字节 | 范围 |
| :-------: | :--: | :--------------: |
| tinyint | 1 | -128~127 |
| smallint | 2 | -32768~32767 |
| mediumint | 3 | -8388608~8388607 |
| int | 4 | -2^31^~2^31^-1 |
| bigint | 8 | -2^63^~2^63^-1 |
1、无符号整数（unsigned）：无符号数没有负数，正数部分是有符号的两倍。
2、整型支持显示宽度（最小的显示位数） 比如int(5)，如果数值的位数小于5位，前面加上前导0。比如输入12，显示00012；大于5位就不添加前导0。必须结合zerofill才起作用
浮点型（保存近似值小数）
| 浮点型 | 占用字节 | 范围 |
| :--------------: | :------: | :----------------: |
| float（单精度） | 4 | -3.4E+38~3.4E+38 |
| double（双精度） | 8 | -1.8E+308~1.8E+308 |
1、浮点数声明: float(M,D) double(M,D)
M：总位数
D：小数位数
#如果精度超出了允许的范围，会四舍五入
2、浮点的精度可能会丢失【精度指的是小数】
定点数
语法：decimal(M,D)
多学一招：
1、定点数是变长的，大致每9个数字用4个字节来存储。定点数之所以能保存精确的小数，因为整数和小数是分开存储的。占用的资源比浮点数要多。
2、定点数和浮点数都支持显示宽度和无符号数。
数据类型——字符型
| 数据类型 | 描述 | 长度 |
| :-----------: | :------: | :-----------: |
| char(长度) | 定长 | 最大255 |
| varchar(长度) | 变长 | 最大65535 |
| tinytext | 大段文本 | 2^8^-1=255 |
| text | 大段文本 | 2^16^-1=65535 |
| mediumtext | 大段文本 | 2^24^-1 |
| longtext | 大段文本 | 2^32^-1 |
数据类型——枚举（enum）
1、从集合中选择一个数据（单选）
2、MySQL的枚举类型是通过整数来管理的，第一个值是1，第二个值是2，以此类推。
3、既然枚举在数据库内部存储的是整数，那么可以直接插入数字
枚举的优点：
1、 运行速度快（数字比字符串运算速度快）
2、 限制数据，保证数据完整性
3、 节省空间
数据类型——集合（set）
从集合中选择一些数据（多选）
每个集合的元素都分配一个固定的数字，分配的方式从左往右按2的0、1、2、…次方
数据类型——日期类型
| 数据类型 | 描述 |
| :-------: | :-------------------: |
| datetime | 日期时间，占用8个字节 |
| date | 日期 占用3个字节 |
| time | 时间 占用3个字节 |
| timestamp | 时间戳，占用4个字节 |
| year | 年份 占用1个字节 |
datetime 格式：年-月-日 小时:分钟:秒
+---------------------+
| field |
+---------------------+
| 2025-10-12 10:12:36 |
| 0100-10-12 10:12:36 |
+---------------------+
2、date 日期格式
+------------+
| field |
+------------+
| 2025-10-12 |
+------------+
3、timestamp：时间戳
timestamp类型和 datetime类型在表现上是一样的。他们的区别：
datetime是从1到9999，而timestamp从1970年~2038年，2038年01月19日11:14:07秒以后就超出timestamp范围了。
+---------------------+
| field |
+---------------------+
| 1975-05-05 12:12:12 |
| 2038-01-19 11:14:07 |
+---------------------+
4、year
因为只占用1个字节，最多只能表示255个年份，范围是1901-2155之间的年份
5、time 表示时间或时间间隔，范围是-838:59:59~838:59:59
多学一招：time支持以天的方式插入
mysql> insert into t14 values ('10 10:10:10');
Query OK, 1 row affected (0.02 sec)
mysql> select * from t14;
+-----------+
| field |
+-----------+
| 12:12:12 |
| 212:12:12 |
| 838:59:59 |
| 250:10:10 |
+-----------+
数据类型——boolean
MySQL不支持boolean类型，true和false在数据库中对应1和0。
列属性——是否为空(null | not null)
null：可以为空
not null：不可以为空
列属性——默认值（default）
1、如果一个字段没有插入值，可以默认插入一个指定的值。
2、default关键字用来插入默认值
列属性——自动增长（auto_increment）
1、字段的值从1开始，每次递增1，特点就在字段中的数据不可能重复，适合为记录生成唯一的id
2、自动增长都是无符号整数。
3、在MySQL中，auto_increment必须是主键。但是主键不一定是自动增长的。
4、如果要给自动增长列插入数据，使用null关键字。
5、自动增长列上的数据被删除，默认情况下此记录的编号不再使用。
列属性——主键（primary key）
主键：唯一标识表中记录的一个或一组列
主键的特点：不能重复，不能为空
一个表只能有一个主键，主键可以有多个字段组成。
主键的作用：
1、 保证数据完整性
2、 加快查询速度
添加主键
方法一：创建表的时候添加主键
mysql> create table t17(
-> id varchar(5) primary key, # 创建主键
-> name varchar(10) not null
-> );
# 如果插入主键相同数据会报错
# 主键不能插入null值
方法二：创建表的时候添加主键
mysql> create table t18(
-> id int,
-> name varchar(10),
-> primary key(id)
-> );
方法三：更改表的时候添加主键
mysql> create table t20(
-> id int,
-> name varchar(10)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> alter table t20 add primary key (id); # 更改表添加主键
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
选择主键的原则
1、 最少性：尽量选择一个字段做主键
2、 稳定性：尽量选择更新少的列做主键
3、 尽量选择数字型的列做主键
列属性——唯一键
特点：
1、不能重复，可以为空
2、一个表可以有多个唯一键
作用：
1、 保证数据不能重复。保证数据完整性
2、 加快数据访问
添加唯一键
方法一：创建表的时候添加唯一键
mysql> create table t22(
-> id int primary key,
-> name varchar(20) unique, #通过unique添加唯一键
-> addr varchar(100) unique
-> );
mysql> create table t26(
-> id int,
-> name varchar(20),
-> addr varchar(20),
-> primary key(id),
-> unique (name), # 添加唯一键
-> unique (addr)
-> );
方法二：修改表的时候添加唯一键
mysql> create table t23(
-> id int primary key,
-> name varchar(20)
-> );
mysql> alter table t23 add unique (name); # 添加一个唯一键
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
一次添加多个唯一键
mysql> create table t24(
-> id int primary key,
-> name varchar(20),
-> addr varchar(20)
-> );
mysql> alter table t24 add unique(name),add unique(addr);
添加组合唯一键
mysql> create table t25(
-> id int primary key,
-> name varchar(20),
-> addr varchar(20)
-> );
mysql> alter table t25 add unique(name,addr);
查看唯一键
mysql> show create table t26\G
*************************** 1. row ***************************
Table: t26
Create Table: CREATE TABLE `t26` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(20) DEFAULT NULL,
`addr` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`), # 唯一键
UNIQUE KEY `addr` (`addr`) # 唯一键
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
添加唯一键，给唯一键取名
mysql> create table t27(
-> name varchar(20)
-> );
mysql> alter table t27 add unique UQ_name(name);
mysql> show create table t27\G
*************************** 1. row ***************************
Table: t27
Create Table: CREATE TABLE `t27` (
`name` varchar(20) DEFAULT NULL,
UNIQUE KEY `UQ_name` (`name`) # 唯一键的名字是UQ_name
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
```
删除唯一键
通过唯一键的名字来删除唯一键
语法：alter table 表名 drop index 唯一键名称
问题：主键和唯一键的区别？
1、主键不能重复，不能为空，唯一键不能重复，可以为空
2、主键只有一个，唯一键可以有多个。
列属性——备注（comment）
为了程序员之间的相互交流
SQL注释
单行注释：--或#
多行注释：/* */
保证实体完整性
1、 主键约束
2、 唯一约束
3、 自动增长列
保证域完整性
1、 数据类型约束
2、 非空约束
3、 默认值约束
保证引用完整性
1、外键约束：从表中的公共字段是主表的外键
引用完整性
主表和从表
两个表建立关系（两个表只要有公共字段就有关系），一个表称为主表，一个表称为从表。
外键约束可以实现：
1、 主表中没有的从表中不允许插入
2、 从表中有的主表中不允许删除
3、 不能更改主表中的值而导致从表中的记录孤立存在。
4、 先删除从表，再删除主表
外键（foreign key）
1、 外键：从表中的公共字段，公共字段的名字可以不一样，但是数据类型必须一样。
2、 外键约束用来保证引用完整性
添加外键
方法一：创建表的时候添加外键
```mysql
create table stuinfo(
stuno char(4) primary key,
name varchar(10) not null
);
create table stumarks(
stuid char(4) primary key,
score tinyint unsigned,
foreign key (stuid) references stuinfo(stuno)
);
```
方法二：修改表的时候添加外键
```mysql
mysql> create table stuinfo(
-> stuno char(4) primary key,
-> name varchar(10) not null
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> create table stumarks(
-> stuid char(4) primary key,
-> score tinyint unsigned
-> );
Query OK, 0 rows affected (0.06 sec)
语法： alter table 从表 add foreign key (从表的公共字段) references 主表(公共字段)
mysql> alter table stumarks add foreign key (stuid) references stuinfo(stuno);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
```
脚下留心：要创建外键必须是innodb引擎，myisam不支持外键约束
删除外键
通过外键的名字删除外键
语法：alter table 表名 drop foreign key 外键名
外键操作
1、 严格操作（前面讲的是严格操作）
2、 置空操作（set null）：如果主表记录删除或更新，从表置空
3、 级联操作（cascade）：如果主表记录删除或更新，从表级联
一般来说：主表删除的时候，从表置空操作，主表更新的时候，从表级联操作。
语法：foreign key(外键) references 主表(关键字段)[主表删除是的动作][主表更新时候的动作]
数据库基本概念
1、关系：两个表的公共字段
2、行：也称记录，也称实体
3、列：也称字段，也称属性
就表结构而言，表分为行和列；
就表数据而言，分为记录和字段；
就面向对象而言，一个记录就是一个实体，一个字段就是一个属性。
4、数据冗余：相同的数据存储在不同的地方
1、冗余只能减少，不能杜绝。
2、减少冗余的方法是分表
3、为减少数据查找的麻烦，允许数据有一定的冗余
5、数据完整性：正确性+准确性=数据完整性
实体和实体之间的关系
1、一对一
2、一对多 （多对一）
3、多对多
一对多 1：N
1、主表中的一条记录对应从表中的多条记录。
2、一对多和多对一是一样的
一对一（1:1）
1、主表中的一条记录对应从表中的一条记录
思考：一对一两个表完全可以用一个表实现，为什么还要分成两个表？
答：在字段数量很多情况下，数据量也就很大，每次查询都需要检索大量数据，这样效率低下。我们可以将所有字段分成两个部分，“常用字段”和“不常用字段”，这样对大部分查询者来说效率提高了。【表的垂直分割】
多对多（N：M）
主表中的一条记录对应从表中的多条记录，从表中的一条记录对应主表中的多条记录
数据库设计具体步骤
1、 收集信息：与该系统有关人员进行交流、坐谈，充分理解数据库需要完成的任务
2、 标识对象（实体－Entity）标识数据库要管理的关键对象或实体
3、 标识每个实体的属性（Attribute）
4、 标识对象之间的关系（Relationship）
5、 将模型转换成数据库
6、 规范化
绘制E-R图
E-R（Entity－Relationship）实体关系图
将E-R图转成表
1、 实体转成表，属性转成字段
2、 如果没有合适的字段做主键，给表添加一个自动增长列做主键。
第一范式：确保每列原子性
第一范式确保每个字段不可再分
地址包含省、市、县、地区是否需要拆分？
答：如果仅仅起地址的作用，不需要统计，可以不拆分；如果有按地区统计的功能需要拆分。
在实际项目中，建议拆分。
第二范式：非键字段必须依赖于键字段
一个表只能描述一件事
第三范式：消除传递依赖
在所有的非键字段中，不能有传递依赖
查询语句
语法：select [选项] 列名 [from 表名] [where 条件] [group by 分组] [order by 排序][having 条件] [limit 限制]
from子句
from：来自，from后面跟的是数据源。数据源可以有多个。返回笛卡尔积。
dual表
dual表是一个伪表。在有些特定情况下，没有具体的表的参与，但是为了保证select语句的完整又必须要一个表名，这时候就使用伪表。
where子句
where后面跟的是条件，在数据源中进行筛选。返回条件为真记录
MySQL支持的运算符
1. `>` 大于
2. `<`小于
3. `>=`
4. `<=`
5. `=`
6. `!=`
7. and 与
8. or 或
9. not 非
in | not in
between…and|not between…and
is null | is not null
脚下留心：查询一个为空的字段不能用等于，必须用is null
聚合函数
1. sum() 求和
2. avg() 求平均值
3. max() 求最大值
4. min() 求最小值
5. count() 求记录数
通配符
1. _ [下划线] 表示任意一个字符
2. % 表示任意字符
模糊查询（like）
order by排序
asc：升序【默认】
desc：降序
group by 【分组查询】
将查询的结果分组，分组查询目的在于统计数据。
1、如果是分组查询，查询字段必须是分组字段和聚合函数。
2、查询字段是普通字段，只取第一个值
通过group_concat()函数将同一组的值连接起来显示
1、分组后的结果默认会按升序排列显示
2、也是可以使用desc实现分组后的降序
多列分组
having条件
having和where的区别：
where是对原始数据进行筛选，having是对记录集进行筛选。
limit
语法：limit 起始位置，显示长度
起始位置可以省略，默认是从0开始
limit在update和delete语句中也是可以使用的。
查询语句中的选项
查询语句中的选项有两个：
1、 all：显示所有数据 【默认】
2、 distinct：去除结果集中重复的数据
union（联合）
union的使用
作用：将多个select语句结果集纵向联合起来
union的选项
union的选项有两个
1、 all：显示所有数据
2、 distinct：去除重复的数据【默认】
默认是去重复的
union的注意事项
1、 union两边的select语句的字段个数必须一致
2、 union两边的select语句的字段名可以不一致，最终按第一个select语句的字段名。
3、 union两边的select语句中的数据类型可以不一致。
多表查询分类
将多个表的数据横向的联合起来。
1、 内连接
2、 外连接
a) 左外连接
b) 右外连接
3、 交叉连接
4、 自然连接
内连接【inner join】
语法一：select 列名 from 表1 inner join 表2 on 表1.公共字段=表2.公共字段
语法二：select 列名 from 表1,表2 where 表1.公共字段=表2.公共字段
思考：
select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段 和
select * from 表2 inner join 表1 on 表1.公共字段=表2.公共字段 结果是否一样？
答：一样的，因为内连接获取的是两个表的公共部分
三个表的内连接如何实现？
select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段
inner join 表3 on 表2.公共字段=表3.公共字段
左外连接【left join】
以左边的表为标准，如果右边的表没有对应的记录，用NULL填充。
语法：select 列名 from 表1 left join 表2 on 表1.公共字段=表2.公共字段
select * from 表1 left join 表2 on 表1.公共字段=表2.公共字段
和
select * from 表2 left join 表1 on 表1.公共字段=表2.公共字段 是否一样？
答：不一样，左连接一左边的表为准。
```
右外连接【right join】
以右边的表为标准，如果左边的表没有对应的记录，用NULL填充。
语法：select 列名 from 表1 right join 表2 on 表1.公共字段=表2.公共字段
select * from 表1 left join 表2 on 表1.公共字段=表2.公共字段
和
select * from 表2 right join 表1 on 表1.公共字段=表2.公共字段 是否一样？
答：一样的
交叉连接【cross join】
1、如果没有连接表达式返回的是笛卡尔积
2、如果有连接表达式等价于内连接
自然连接【natural】
自动的判断连接条件，它是过同名字段来判断的
自然连接又分为：
1. 自然内连接 natural join
2. 自然左外连接 natural left join
3. 自然右外连接 natural right join
自然连接结论：
1. 表连接通过同名的字段来连接的
2. 如果没有同名的字段返回笛卡尔积
3. 会对结果进行整理，整理的规则如下
a) 连接字段保留一个
b) 连接字段放在最前面
c) 左外连接左边在前，右外连接右表在前
using()
1. 用来指定连接字段。
2. using()也会对连接字段进行整理，整理方式和自然连接是一样的。
子查询
语法
语法：select 语句 where 条件 (select … from 表)
1. 外面的查询称为父查询，括号中的查询称为子查询
2. 子查询为父查询提供查询条件
in|not in子查询
用于子查询的返回结果多个值。
1、查找笔试成绩及格的同学
exists和not exists
子查询分类
1、标量子查询：子查询返回的结果就一个
2、列子查询：子查询返回的结果是一个列表
3、行子查询：子查询返回的结果是一行
例题：查询成绩最高的男生和女生
mysql> select stuname,stusex,ch from stu where (stusex,ch) in (select stusex,max(ch) from stu group by stusex);
+----------+--------+------+
| stuname | stusex | ch |
+----------+--------+------+
| 争青小子 | 男 | 86 |
| Tabm | 女 | 88 |
+----------+--------+------+
4、表子查询：子查询返回的结果当成一个表
例题：查询成绩最高的男生和女生
mysql> select stuname,stusex,ch from (select * from stu order by ch desc) as t group by stusex;
+----------+--------+------+
| stuname | stusex | ch |
+----------+--------+------+
| Tabm | 女 | 88 |
| 争青小子 | 男 | 86 |
+----------+--------+------+
脚下留心：from后面是一个表，如果子查询的结果当成表来看，必须将子查询的结果取别名。
视图【view】
1、 视图是一张虚拟表，它表示一张表的部分或多张表的综合的结构。
2、 视图仅仅是表结构，没有表数据。视图的结构和数据建立在表的基础上。
创建视图
语法
create [or replace] view 视图的名称
as
select语句
多学一招：因为视图是一个表结构，所以创建视图后，会在数据库文件夹中多一个与视图名同名的.frm文件
使用视图
视图是一张虚拟表，视图的用法和表的用法一样
mysql> select * from vw_stu;
查看视图的结构
desc 视图名
查看创建视图的语法
show create view 视图名
显示所有视图
方法一：
mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| stu |
| stuinfo |
| stumarks |
| t1 |
| t2 |
| vw_stu |
方法二
mysql> select table_name from information_schema.views;
+------------+
| table_name |
+------------+
| vw_stu |
+------------+
1 row in set (0.05 sec)
+------------------+
方法三
mysql> show table status where comment='view' \G
*************************** 1. row ***************************
Name: vw_stu
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
1 row in set (0.00 sec)
更改视图
alter view 视图名
as
select 语句
删除视图
drop view [if exists] 视图1,视图2,…
mysql> drop view vw_stu;
视图的作用
1. 筛选数据，防止未经许可访问敏感数据
2. 隐藏表结构
3. 降低SQL语句的复杂度
视图的算法
1. merge：合并算法，将视图的语句和外层的语句合并后在执行。
2. temptable：临时表算法，将视图生成一个临时表，再执行外层语句
3. undefined：未定义，MySQL到底用merge还是用temptable由MySQL决定，这是一个默认的算法，一般视图都会选择merge算法，因为merge效率高。
解决：在创建视图的时候指定视图的算法
create algorithm=temptable view 视图名
as
select 语句
事务【transaction】
1. 事务是一个不可分割的执行单元
2. 事务作为一个整体要么一起执行，要么一起回滚
事务操作
开启事务：start transaction或begin [work]
提交事务：commit
回滚事务：rollback
事务什么时候产生？什么时候结束？
答：开启的时候产生，提交事务或回滚事务都结束
只有innodb和BDB才支持事务，myisam不支持事务。
设置事务的回滚点
设置回滚点： savepoint 回滚点名
回滚到回滚点： rollback to 回滚点
事务的特性（ACID）
1. 原子性（Atomicity）：事务是一个整体，不可以再分，要么一起执行，要么一起不执行。
2. 一致性（Consistency）：事务完成时，数据必须处于一致的状态。
3. 隔离性（Isolation）：每个事务都是相互隔离的
4. 永久性（Durability）：事务完成后，对数据的修改是永久性的。
索引【index】
索引的优点：查询速度快
索引的缺点：
1. 增、删、改（数据操作语句）效率低了
2. 索引占用空间
索引的类型
1. 普通索引
2. 唯一索引（唯一键）
3. 主键索引：只要主键就自动创建主键索引，不需要手动创建。
4. 全文索引，搜索引擎使用，MySQL不支持中文的全文索引，我们通过sphinx去解决中文的全文索引。
创建普通索引【create index】
create index [索引名] on 表名 （字段名）
alter table 表名 add index [索引的名称] （列名）
# 创建索引方法一
mysql> create index ix_stuname on stuinfo(stuname);
# 创建索引方法二
mysql> alter table stuinfo add index ix_address (stuaddress);
# 创建表的时候就添加索引
mysql> create table emp(
-> id int,
-> name varchar(10),
-> index ix_name (name) # 创建索引
-> );
创建唯一索引
语法一：create unique index 索引名 on 表名 （字段名）
语法二：alter table 表名 add unqiue [index] [索引的名称] （列名）
语法三：创建表的时候添加唯一索引，和创建唯一键是一样的。
# 方法一：
mysql> create unique index UQ_stuname on stu(stuname);
# 方法二：
mysql> alter table stu add unique UQ_address (stuaddress);
# 方法三
mysql> create table stu2(
-> id int,
-> name varchar(20),
-> unique UQ_name(name)
-> );
删除索引
drop index 索引名 on 表名
mysql> drop index ix_stuname on stuinfo;
创建索引的指导原则
1. 该列用于频繁搜索
2. 改列用于排序
3. 公共字段要创建索引
4. 如果表中的数据很少，不需要创建索引。MySQL搜索索引的时间比逐条搜索数据的时间要长。
5. 如果一个字段上的数据只有几个不同的值，改字段不适合做索引，比如性别。
函数
数字类
mysql> select rand(); # 生成随机数
+---------------------+
| rand() |
+---------------------+
| 0.18474003969201822 |
+---------------------+
mysql> select * from stuinfo order by rand(); # 随机排序
mysql> select * from stuinfo order by rand() limit 2; # 随机抽两个学生
+--------+----------+--------+--------+---------+------------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress |
+--------+----------+--------+--------+---------+------------+
| s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 |
+--------+----------+--------+--------+---------+------------+
mysql> select round(3.5); #四舍五入
+------------+
| round(3.5) |
+------------+
| 4 |
+------------+
mysql> select ceil(3.1); # 向上取整
+-----------+
| ceil(3.1) |
+-----------+
| 4 |
+-----------+
mysql> select floor(3.9); # 向下取整
+------------+
| floor(3.9) |
+------------+
| 3 |
+------------+
mysql> select truncate(3.1415926,3); # 截取数字
+-----------------------+
| truncate(3.1415926,3) |
+-----------------------+
| 3.141 |
+-----------------------+
字符串类
mysql> select ucase('i am a boy!'); # 转成大写
+----------------------+
| ucase('i am a boy!') |
+----------------------+
| I AM A BOY! |
+----------------------+
mysql> select lcase('I Am A Boy!'); #转成小写
+----------------------+
| lcase('I Am A Boy!') |
+----------------------+
| i am a boy! |
+----------------------+
mysql> select left('abcde',3); # 从左边开始截取，截取3个
+-----------------+
| left('abcde',3) |
+-----------------+
| abc |
+-----------------+
mysql> select right('abcde',3); # 从右边开始截取，截取3个
+------------------+
| right('abcde',3) |
+------------------+
| cde |
+------------------+
mysql> select substring('abcde',2,3); #从第2个位置开始截取，截取3个【位置从1开始】
+------------------------+
| substring('abcde',2,3) |
+------------------------+
| bcd |
+------------------------+
mysql> select concat('中国','上海'); # 字符串相连
+-----------------------+
| concat('中国','上海') |
+-----------------------+
| 中国上海 |
+-----------------------+
mysql> select concat(stuname,'-',stusex) from stuinfo; # 将表中的姓名和性别连接起来
+----------------------------+
| concat(stuname,'-',stusex) |
+----------------------------+
| 张秋丽-男 |
| 李文才-男 |
| 李斯文-女 |
| 欧阳俊雄-男 |
| 诸葛丽丽-女 |
| 争青小子-男 |
| 梅超风-女 |
+----------------------------+
# coalesce(字段1，字段2) 如果字段1不为空就显示字段1，否则，显示字段2
mysql> select stuname,coalesce(writtenexam,'缺考'),coalesce(labexam,'缺考') from stuinfo natural left join stumarks; # 将考试成绩为空的显示为缺考
+----------+------------------------------+--------------------------+
| stuname | coalesce(writtenexam,'缺考') | coalesce(labexam,'缺考') |
+----------+------------------------------+--------------------------+
| 张秋丽 | 77 | 82 |
| 李文才 | 50 | 90 |
| 李斯文 | 88 | 58 |
| 欧阳俊雄 | 65 | 50 |
| 诸葛丽丽 | 缺考 | 缺考 |
| 争青小子 | 56 | 48 |
| 梅超风 | 缺考 | 缺考 |
+----------+------------------------------+--------------------------+
mysql> select length('锄禾日当午'); # 字节长度
+----------------------+
| length('锄禾日当午') |
+----------------------+
| 10 |
+----------------------+
mysql> select char_length('锄禾日当午'); # 字符个数
+---------------------------+
| char_length('锄禾日当午') |
+---------------------------+
| 5 |
+---------------------------+
时间类
mysql> select unix_timestamp(); #获取时间戳
+------------------+
| unix_timestamp() |
+------------------+
| 1537084508 |
+------------------+
mysql> select from_unixtime(unix_timestamp()); # 将时间戳转成年-月-日 小时:分钟:秒的格式
+---------------------------------+
| from_unixtime(unix_timestamp()) |
+---------------------------------+
| 2018-09-16 15:55:56 |
+---------------------------------+
mysql> select now(); # 获取当前日期时间
+---------------------+
| now() |
+---------------------+
| 2018-09-16 15:57:04 |
+---------------------+
mysql> select year(now()) 年,month(now()) 月, day(now()) 日,hour(now()) 小,minute(now()) 分钟,second(now()) 秒;
+------+------+------+------+------+------+
| 年 | 月 | 日 | 小时 | 分钟 | 秒 |
+------+------+------+------+------+------+
| 2018 | 9 | 16 | 15 | 59 | 14 |
+------+------+------+------+------+------+
mysql> select dayname(now()) 星期,monthname(now()),dayofyear(now()) 本年的第几天;
+--------+------------------+--------------+
| 星期 | monthname(now()) | 本年的第几天 |
+--------+------------------+--------------+
| Sunday | September | 259 |
+--------+------------------+--------------+
mysql> select datediff(now(),'2008-8-8'); # 日期相减
+----------------------------+
| datediff(now(),'2008-8-8') |
+----------------------------+
| 3691 |
+----------------------------+
mysql> select convert(now(),date),convert(now(),time); # 将now()转成日期和时间
+---------------------+---------------------+
| convert(now(),date) | convert(now(),time) |
+---------------------+---------------------+
| 2018-09-16 | 16:07:24 |
+---------------------+---------------------+
mysql> select cast(now() as date),cast(now() as time); # 将now()转成日期和时间
+---------------------+---------------------+
| cast(now() as date) | cast(now() as time) |
+---------------------+---------------------+
| 2018-09-16 | 16:08:03 |
+---------------------+---------------------+
加密函数
+----------------------------------+------------------------------------------+
| md5('root') | sha('root') |
+----------------------------------+------------------------------------------+
| 63a9f0ea7bb98050796b649e85481845 | dc76e9f0c0006e8f919e0c515c66dbba3982f785 |
+----------------------------------+------------------------------------------+
判断函数
if(表达式,值1,值2)
预处理
预编译一次，可以多次执行。用来解决一条SQL语句频繁执行的问题。
预处理语句：prepare 预处理名字 from ‘sql语句’
执行预处理：execute 预处理名字 [using 变量]
例题一：
mysql> prepare stmt from 'select * from stuinfo'; # 创建预处理
mysql> execute stmt; # 执行预处理
+--------+----------+--------+--------+---------+------------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress |
+--------+----------+--------+--------+---------+------------+
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 |
| s25302 | 李文才 | 男 | 31 | 3 | 上海 |
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 |
| s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 |
| s25319 | 梅超风 | 女 | 23 | 5 | 河北 |
+--------+----------+--------+--------+---------+------------+
例题二：传递参数
mysql> delimiter //
mysql> prepare stmt from 'select * from stuinfo where stuno=?' // -- ?是位置占位符
mysql> set @id='s25301'; -- 变量以@开头，通过set给变量赋值
-> execute stmt using @id // -- 执行预处理，传递参数
+--------+---------+--------+--------+---------+------------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress |
+--------+---------+--------+--------+---------+------------+
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 |
+--------+---------+--------+--------+---------+------------+
1、?是位置占位符
2、变量以@开头
3、通过set给变量赋值
例题三：传递多个参数
mysql> prepare stmt from 'select * from stuinfo where stusex=? and stuaddress=?' //
mysql> set @sex='男';
-> set @addr='北京';
-> execute stmt using @sex,@addr //
+--------+---------+--------+--------+---------+------------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress |
+--------+---------+--------+--------+---------+------------+
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 |
+--------+---------+--------+--------+---------+------------+
存储过程【procedure】
存储过程的优点
1. 存储过程可以减少网络流量
2. 允许模块化设计
3. 支持事务
创建存储过程
create procedure 存储过程名(参数)
begin
//sql语句
end;
脚下留心：由于过程中有很多SQL语句，每个语句的结束都要用（；）结束。默认情况下，分号既表示语句结束，又表示向服务器发送SQL语句。我们希望分号仅表示语句的结束，不要将SQL语句发送到服务器执行，通过delimiter来更改结束符。
mysql> delimiter //
mysql> create procedure proc() -- 创建存储过程
-> begin
-> select * from stuinfo;
-> end //
调用存储过程
call 存储过程名()
删除存储过程
drop procedure [if exists] 存储过程名
mysql> drop procedure proc // -- 删除存储过程
查看存储过程的信息
显示所有的存储过程
mysql> show procedure status \G
存储过程的参数
存储过程的参数分为：输入参数（in）【默认】，输出参数（out），输入输出参数（inout）
存储过程不能使用return返回值，要返回值只能通过“输出参数”来向外传递值。
例题一：传递学号，获取对应的信息
```mysql
mysql> create procedure proc(in param varchar(10)) -- 输入参数
-> select * from stuinfo where stuno=param //
Query OK, 0 rows affected (0.00 sec)
mysql> call proc('s25301') //
+--------+---------+--------+--------+---------+------------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress |
+--------+---------+--------+--------+---------+------------+
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 |
+--------+---------+--------+--------+---------+------------+
例题二：查找同桌
```mysql
mysql> create procedure proc(name varchar(10))
-> begin
-> declare seat tinyint; -- 声明局部变量
-> select stuseat into seat from stuinfo where stuname=name; -- 将座位号保存到变量中
-> select * from stuinfo where stuseat=seat+1 or stuseat=seat-1; -- 查找同桌
-> end //
mysql> call proc('李文才') //
+--------+----------+--------+--------+---------+------------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress |
+--------+----------+--------+--------+---------+------------+
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 |
+--------+----------+--------+--------+---------+------------+
强调
```
1、通过declare关键字声明局部变量；全局变量@开头就可以了
2、给变量赋值有两种方法
方法一：set 变量名=值
方法二：select 字段 into 变量 from 表 where 条件
3、声明的变量不能与列名同名
```
例题三：输出参数
mysql> create procedure proc(num int, out result int) //out 表示输出参数
-> begin
-> set result=num*num;
-> end //
mysql> call proc(10,@result) //
mysql> select @result //
+---------+
| @result |
+---------+
| 100 |
+---------+
例题四：输入输出参数
mysql> create procedure proc(inout num int) # inout 表示是输入输出参数
-> begin
-> set num=num*num;
-> end //
mysql> set @num=10;
-> call proc(@num);
-> select @num //
GO连接MySQL
1. 因为Go语言没有提供任何官方数据库驱动，所以需要安装第三方函数库。
2. 由于在github上安装，所以需要安装git软件，安装过程一直点击下一步即可。安装完成后需要配置环境变量
340
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
