MySQL数据语言 mysql语言有哪几种

MySQL简介

数据库简介：

• 用途：用于存储生活的几乎一切数据，如：身份、住房、车票、网站、…
• 概念：数据库服务器、数据库、数据表、一行数据(一条)、一列数据(字段)
• 分类：

• 关系型数据库：mysql、oracle、SQL server、…
• 非关系型数据库：Redis、MongoDB、…

• SQL：Structured Query Language，结构化查询语言
• SQL分类：

• 数据定义语言：DDL
• 数据操作语言：DML
• 数据查询语言：DQL
• 数据控制语言：DCL
• 数据事务语言：DTL

MySQL(ubuntu)安装：

• 安装：sudo apt-get install mysql-server

• 若安装出现系统服务问题，很可能是自己添加系统服务的问题，移除即可

• 安全配置：sudo mysql_secure_installation

• 按照提示，一路进行下去
• 设置支持远程连接，为后面做准备

• 连接测试：mysql -h host -u user -p

• -h：指定主机，localhost/127.0.0.1表示本机，本机可以省略
• -u：指定用户，默认只有root用户
• -p：指定密码，输入密码时要与登录分开，以防不测
• 示例：mysql [-hlocalhost] -uroot -p，然后根据提示输入密码

• 端口：3306
• 退出：\h 或 help;
• 说明：所有的命令都是以’;'结尾

数据定义语言(DDL)

• 查看库：show databases;，会显示数据库服务器上所有的数据库
• 创建库：create database test;，创建数据库test
• 删除库：drop database test;，删除数据库test
• 选择库：use test;，选择数据库test

• 查看当前选中数据库：

• show tables；
• select database();

• 查看表：show tables;，查看当前选中数据库中的数据表
• 创建表：create table user(username varchar(20), password char(32));
• 查看表结构：desc user;
• 删除表：drop table user;
• 查看创建方式：

• 库：show create database test;
• 表：show create table user;

• 修改字符集：

• 创建时指定：在创建表的语句后指定字符集。
• 修改配置文件：sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf，在[mysqld]后面添加

• character-set-server=utf8
• 重启服务：sudo service mysql restart
• 说明：以后再创建的库中的表默认字符集就是utf8

• 修改字段：alter table user modify username char(30);，不能修改名字
• 添加字段：

• 默认(最后)添加：alter table user add age int(3);
• 在指定字段后添加：alter table user add email varchar(60) after password;
• 在最开始出添加：alter table user add id int(11) first;

• 删除字段：alter table user drop age;
• 修改字段名称：alter table user change email em char(32);
• 修改位置及名称：add/change/modify

• alter table user modify em char(32) first;

• 修改表名：alter table user rename new_user;

数据类型

• 整型：tinyint(1个字节)、smallint(2个字节)、mediumint(3个字节)、int(4个字节)、bigint(8个字节)
• 浮点类型：

• float(m, d)，单精度浮点数，4个字节，m表示总位数，d表示小数位数
• double(m, d)，双精度浮点数，8个字节，m表示总位数，d表示小数位数
• decimal(m, d)，以字符串的形式存储浮点数，用于金融领域等要求严格的场景

• 字符类型：

• char：定长字符串，0~255个字节
• varchar：变长字符串，0~65535个字节

• 时间日期：

• date：日期，格式：2018-04-16
• time：时间，格式：15:36:30
• datetime：日期时间，格式：2018-04-16 15:37:38
• timestamp：时间戳
• year：年，只占1个字节，年份范围：1901~2155

• 符合类型：

• set：集合类型，格式：set(s1, s2, …, s63)，最多63种可能
• enum：枚举类型，格式：enum(e1,e2, … ,e65535)，最多65535种可能

• 字段修饰：

• unsigned：无符号数
• zerofill：高位0填充，防止出现负数
• auto_increment：自动增加(1)，用于整数，经常与主键结合使用
• default：设置默认值
• not null：不能为空

• 字符集及存储引擎

• 查看支持的字符集：show character set;，我们通常只使用utf8，不能书写成utf-8
• 查看支持的存储引擎：show engines;

• 常用存储引擎：MyISAM和InnoDB

• 索引：

• 说明：简单理解，就是一本书最前面的目录，虽然可以提高读取效率，单并非越多越好。
• 分类：

• 普通索引(index)：最基本的索引
• 唯一索引(unique)：修饰的字段不能重复
• 主键索引(primary key)：是一种特殊的唯一索引，一张表中只能有一个字段设置
• 全文索引(fulltext)：多全局数据添加索引。

• 示例：
  alter table user add index(em); # 给em字段添加普通索引
  alter table user add unique(username); # 给username字段添加唯一索引
  alter table user add primary key(id); # 将id设置为主键索引

alter table user drop index em;				# 删除em字段的普通索引

alter table 表名add fulltext(字段);

• 很多选项都可在创建表时指定，如：

•  create table user(
 id int auto_increment,
 name varchar(20),
 primary key(id),
 unique(name)
 )engine=innodb default charset=utf8;
 数据操作语言(DML)

• 说明：在最大多数的使用中，都是对数据的增、删、改、查的操作(CURD)
• 准备：创建一个star表

•  create table star(
 id int auto_increment,
 name varchar(20) not null,
 money float not null,
 province varchar(20) default null,
 age tinyint unsigned not null,
 sex tinyint not null,
 primary key(id)
 )engine=innodb default charset=utf8;

• 插入数据：

• 方式1：不指定字段，按照数据表的数据添加一条数据的全部字段
  insert into star values(1,‘黄晓明’,200000,‘山东’,28,0);
  可以同时插入多条数据，每条一个小括号。
• 方式2：指定字段，只需要传递指定字段的值
  insert into star(name,money,age,sex,province) values(‘小岳岳’,4000000, 33, 0, ‘河南’)；
  可以同时插入多条数据，每条一个小括号
• 注意：什么情况下可以不传字段值

• 自增的字段
• 有默认值的
• 可以为空的

• 说明：插入数据通常使用方式2，可以根基需要进行数据的插入。

• 查询数据：

• select * from star;

• 删除数据：

• delete from star where id=1;
• 警告：删除操作一定不要忘了指定条件，否则后果自负。

• 修改数据：

• update star set age=22,money=8000000 where id=6;
• 警告：修改操作一定不要忘了指定条件，否则后果自负。

数据查询语言(DQL)

• 基础查询：select * from star;
• 指定字段查询：select id,name,money from star;
• 排除重复记录：select distinct name from star;

• 使用distinct修饰的字段组合不能重复

• 指定条件查询：

• 条件：
  符号 说明

大于

= 大于等于
< 小于
<= 小于等于
= 等于
!= 或 <> 不等于
and 并且
or 或者
[not] between m and n [不]在指定[m,n]的闭区间
[not] in [不]在指定的集合中
[not] like 模糊匹配，%表示任意字符

• 示例：
  select id,name,money from star where id > 4;
  select id,name from star where id between 2 and 5;
  select id,name from star where id in(2,4,6);
  select id,name from star where id not in(2,4,6);
  select id,name from star where name like ‘小%’;

• 结果集排序：

• 示例：select id,name,money from star order by money desc;
• order by ：指定排序字段

• asc：升序，默认的
• desc：降序

• 也可以多字段排序，先按照前面的字段排序，再按照后面字段排序

• 示例：select id,name,money,age from star order by money desc,age asc;

• 限制结果集：

• 示例：
  select id,name,money from star limit 3; # 取3条数据
  select id,name,money from star limit 2,3; # 偏移2条，然后取3条数据
  select id,name,money from star limit 3 offset 2; # 偏移2条，然后取3条数据
• 用途：分页显示，假设一页显示10条数据
  第一页：limit 0, 10
  第二页：limit 10, 10
  第三页：limit 20, 10
  page：表示页数，pageSize：表示一页的大小
  查询条件：limit (page-1)*pageSize, pageSize

• 常用聚合函数
  函数 功能
  count 统计个数
  sum 求和
  max 最大值
  min 最小值
  avg 平均值

1. 使用count时指定任何字段都行
2. 使用其他函数时必须要指定字段
   示例：
   select count(*) from star;
   select max(money) as m from star;
   as可以给字段其别名

• 分组操作

• 示例：
  select * from star group by sex; # 分组
  select count(*), sex from star group by sex; # 分组统计

• 结果集过滤：

• 示例：
  select count(*) as c,province from star group by province having c>1;
  搜索所有记录，然后按照省份分组，统计成员大于1的省份

数据控制语言(DCL)

• 创建用户：

• 格式：create user ‘用户名’@‘主机’ identified by ‘密码’
• 示例：create user ‘liusan’@’%’ identified by ‘123456’

• 授权用户：

• 格式：grant 权限 privileges on 库.表 to ‘用户’@‘主机’ identified by ‘密码’
• 示例：grant all privileges on test.* to ‘liusan’@’%’ identified by ‘123456’

• 查看授权：

• 格式：show grants for ‘用户’@‘主机’
• 示例：show grants from ‘liusan’@’%’

• 刷新权限：flush privileges;
• 取消授权：

• 格式：revoke 权限 privileges on 库.表 from ‘用户’@‘主机’;
• 示例：revoke all privileges on test.* from ‘liusan’@’%’;

• 删除用户：

• 格式：drop user ‘用户’@‘主机’
• 示例：drop user ‘liusan@’%’

• root用户远程登录：

• sudo mysql_secure_installation，根据提示配置，允许root用户远程登录
• 修改配置文件：sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

• 将bind-address=127.0.0.1，改为bind-address=0.0.0.0

• 给root添加授权主机，%表示所有主机

• 如：grant all privileges on . to ‘root’@’%’ identified by ‘123456’
• 给root用户添加任意主机以123456作为密码登录，对所有的库下的所有表用于所有权限

多表联合查询

• 隐式内连接：没有出现join关键的连接

• 示例：select username,name from user,goods where user.gid=goods.gid;
• 说明：查看用户买的商品名

• 显式内连接：会出现join关键字，后面的条件使用on

• 示例：select username,name from user [inner/cross] join goods on user.gid=goods.gid;
• 说明：功能同上
• join前可以加inner，也可以使用cross，也可以省略

• 外左连接：以左表为主

• 示例：select username,name from user left [outer] join goods on user.gid=goods.gid;
• 说明：以左表为主，显示左边所有内容，右表不匹配的选项显示NULL

DTL，数据事务语言

事务的定义：
就是指一组相关的SQL操作，我们所有的操作都是事务中的。

注意：在数据库中，执行业务的基本单位是【事务】，不是以某一条SQL。
数据库在默认情况下，事务是都打开的，也就是说它一种处在事务当中的，一个事务的结束，代表着下一个事务的开启。
执行commit或者rollback指令时，会结束当前事务

作用：用来保证数据的平稳性和可预测性
例如：银行转账业务
SQL1：A账号向B账号转账10000
update tbl_account set balance=balance-10000 where accountNo=A账号；
SQL2:
update tbl_account set balance=balance+10000 where accountNo=B账号；
SQL1和SQL2必须处在同一个事务中，从而保证同时成功或者同时失败。

事务的四大特性（ACID）：
atomic,原子性，事务是不可分割的，要么同时成功，要么同时失败；
consistency,一致性，事务一旦结束，内存中的数据和数据库中的数据是保持一致;
isolation,隔离性，事务之间互不干扰，一个事务的结束意味着下一个事务的开启；
duration，持久性，事务一旦提交，则数据持久化到数据库中，永久保存

在oracle中，操作事务的命令：
1.commit，提交事务
把事务中所有的数据持久化到磁盘中

2.rollback to 回滚点,回滚事务
把事务中所做的操作全部取消，回到初始化状态

3.savepoint 回滚点，设置回滚点
事务回滚时，回滚到起点

总结：
1.目前主流的数据库都是支持事务的，而且其中Oracle支持的最好
2.一个事务不能读取到另一个事务还没有提交的数据
3.DDL语句都会自动提交事务
4.DML语句不会自动提交事务，需要手动提交commit
多事务的并发处理机制：
原因：多个事务同时操作一个表中的同一行数据，如果这些操作同时修改的话，就会产生并发问题，如果不处理，则会造成数据的不一致的情况

数据库可能产生的并发问题包括：
1.脏读
是指一个事务正在访问数据，并且对这个数据进行修改，而这种修改还没有提交到数据库中，而另一个事务也访问了这个数据，并且使用了这个数据。
解决方法：一个事务在修改数据时，该数据不能被其他事务访问

2.不可重复读：
是指一个事务多次读取同一条记录，如果此时另一个事务也访问并且修改了该数据，则就会出现多次读取出现数据不一致的情况，原来的数据变成了不可重复读取的数据。
解决方法：只有在修改事务完全提交过后才可以读取数据

3.幻读
是指一个事务修改表中的多行记录，但是此时另一个事务对该表格进行了插入数据的操作，则第一个事务会发现表格中会出现没有被修改的行，就像发生了幻象一样；
解决方法：在一个事务提交数据之前，其他事务不能添加数据

Oracle中采用‘锁’来做并发处理
1.表级排它锁(X) exclusive mode
2.表级共享锁(S) share mode
3.表中行级排它锁 share row exclusive
注：这三种锁是通过专门的命令来申请的

语法：
lock table tbl_name in mode;

例如：
–以共享锁锁表
lock table tbl_emp in share mode;

–以排它锁锁表
lock table tbl_emp in exlusive mdoe;

4.行级共享锁(RS) row share
5.行级排它锁(RX) row exclusive
注：这两种锁无需通过专门的命令申请，而是通过DQL和DML来自动申请的

注意：
1.所有的DQL语句默认情况下都会自动申请RS锁
2.所有的DML语句默认情况下都会自动申请RX锁，每一行记录都有唯一的RX锁
3.在项目中，为了满足业务要求，一般select语句需要申请RX锁

select语句通过for update来申请RX锁：
select * from s_emp for update;
select * from s_emp for update wait 5;等待5秒
select * from s_emp for update nowait;