Mysql Structured Query Language

sql简介

SQL (Structured Query Language) 是具有数据操
纵和数据定义等多种功能的数据库语言，这种语言具有交互性特点，能为用户提供极大的便利，数据库管理系统应充分利用SQL语言提高计算机应用系统的工作质量与效率。SQL语言不仅能独立应用于终端，还可以作为子语言为其他程序设计提供有效助力，该程序应用中，SQL可与其他程序语言一起优化程序功能，进而为用户提供更多更全面的信息。

数据库操作

1.数据定义：DDL语言，定义数据库的逻辑结构，包括定义数据库、基本表、视图和索引4部分。

数据定义语言（data definition language，DDL）属于DBMS语言的一种，用于明确定义概念纲要(conceptual schemas)及内部纲要(internal schemas)，DBMS内的DDL编译器(complier)能够处理DDL，识别纲要建构物(schema constructs)的描述(description)并加以存入DBMS目录中的纲要描述(schema description)。 为SQL语言集中负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成，最早是由Codasyl（Conference on Data Systems Languages）数据模型开始，现在被纳入SQL指令中作为其中一个子集。

# 创建数据库 CREATE DATABASE <数据库名> 〔其它参数〕
> create database if not exists `db_name` character set utf8mb4 collate utf8mb4_0900_ai_ci;

# 修改数据库 ALTER DATABASE <数据库名> 〔其它参数〕
> alter database `db_name` character set=utf8 COLLATE=utf8_general_ci;  

# 删除数据库 DROP DATABASE <数据库名>
> drop database if exists `db_name`;


# 创建表 CREATE
> create table t_user(id int not null default 0 primary key, name varchar(20) default "", age int, country char(20), website varchar(64));

# 修改表
> alter table t_user convert to character set utf8 collate utf8_general_ci;(修改表字符集)

> alter table t_user default character set utf8 collate utf8_general_ci;(只修改默认字符集)

> alter table t_user modify name varchar(20);(修改字段类型)

> alter table t_user change name username varchar(20);(修改字段名称)

> alter table t_user change column_name column_name varchar(255) character set utf8mb4 collate utf8mb4_unicode_ci; (修改表字段字符集)

> alter table t_user add age int(11);(表添加字段)

> alter talbe t_user alter column column_name (char) (修改表字段类型)

> alter table t_user add primary key(id);(添加主键)

> alter table t_user drop primary key;(删除主键)

> alter table t_user add constraint fk_name foreign key(name) fererences t_user(name);(添加外键)

> alter table t_user drop foreign key name;(删除外键)

> alter table t_user rename to user; (修改表名)

# 删除表
> drop table t_user;


# 创建索引 CREATE INDEX <索引名称> on <表名称(key)>
> create index index_name on t_user(name)

# 删除索引 DROP INDEX  <索引名称> on <表名称>
> drop index index_name on t_user;

2.数据操纵：DML，包括插入、删除和更新三种操作。

数据操纵语言（Data Manipulation Language, DML）是用于数据库操作，对数据库其中的对象和数据运行访问工作的编程语句，通常是数据库专用编程语言之中的一个子集，例如在信息软件产业通行标准的SQL语言中，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入(意指新增或创建)、更新(修改)与删除(销毁)。在使用数据库的系统开发过程中，其中应用程序必然会使用的指令；而加上 SQL的SELECT语句，欧美地区的开发人员把这四种指令，以“CRUD”(分别为 Create, Read, Update, Delete英文四前缀字母缩略的术语)来称呼；而亚洲地区使用汉语的开发人员，或可能以四个汉字：增 查 改 删 来略称。

# 插入数据
> insert into t_user (`column1`, `column2`) values (`value1`, `value2`);

# 修改数据
> update t_user set `column1`=`v1`, `column2`=`v2` where id = 1;
 
# 删除数据
> delete from t_user where id = 1;

3.数据查询：DQL，包括数据查询操作。

# SELECT 语句
> select * from t_user where id = 1;

# SELECT DISTINCT 语句（DISTINCT 用于返回唯一的值。）
> select distinct id, name from t_user;

# WHERE 子句
> select * fORM t_user where id < 3;

# AND & OR 运算符
> select * from t_user where country = "CN" and age < 30;

# ORDER BY 关键字
> select * from t_user order by id, age desc;

# SELECT LIMIT 子句
> select * from t_user order by id desc limit 1;

# LIKE 操作符(% 代表0或多个，_ 代表一个)
> select * from t_user where name like "小_";

# IN 操作符
> select * from t_user where id in (select id from t_user where id < 3);

# BETWEEN  NOT BETWEEN操作符
> select * from t_user where age between 18 and 30;

# 连接(JOIN)
> select t_user.id, t_user.name, t_user.age, t_user.country, t_score.course_name, t_score.score from t_user  join t_score on t_score.user_id = t_user.id;

# INNER JOIN 关键字(INNER JOIN 与 JOIN 是相同的。)
> select t_user.*,t_score.* from t_user inner join t_score on t_user.id = t_score.user_id;

# LEFT JOIN 关键字
> select t_user.*, t_score.* from t_user left join t_score on t_user.id = t_score.user_id;

# RIGHT JOIN 关键字
> select t_user.*, t_score.* from t_user right join t_score on t_user.id = t_score.user_id;

# UNION 操作符
> select country from t_user where id < 3 union select country from t_user where id > 2;
> select country from t_user where id < 3 union all select country from t_user where id > 2;

# SELECT INTO 语句
> select * into t_user_bak from t_user;

# INSERT INTO SELECT 语句
> insert into t_user_bak select * from t_user;

# CREATE VIEW 语句
> create view v_user as select * from t_user;

4.数据控制：DCL，对用户访问数据的控制有基本表和视图的授权及回收。

数据控制语言 (Data Control Language) 在SQL语言中，是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、存储程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。

# 创建用户 create user "用户名称"@"主机名称" indentified by "用户密码"
> create user "jack"@"localhost" identified by "jack";

# 授予权限  grant 权限 on 数据库.表 to "用户名"@"登录主机"  [indentified by "用户密码"];
> grant replication slave on *.* to "jack"@"localhost";

# 刷新
# $ flush privileges; 

# 修改root用户可以远程连接
> update mysql.user set host="%" where user="root";

# 查看mysql所用用户
> select user,host from mysql.user;

# docker 修改mysql的最大连接数
apt-get update
apt-get install vim
vim /etc/mysql/mysql.conf.d/mysqld.cnf
max_connections=1000

# 解决远程连接密码错误
> alter user "root"@"%" identified with mysql_native_passWord by "pass";

5.事务控制：TCL，包括事务的提交与回滚。

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：

BEGIN 或 START TRANSACTION 显式地开启一个事务；

COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO identifier 把事务回滚到标记点；

SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

1.用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

2.直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

数据库函数

日期函数

聚合函数

字符串函数

控制流函数

数学函数

比较函数

其他函数

MySQL 常用操作

启动关闭 MySQL systemctl startstop mysqld

查看mysql状态 systemctl status mysqld

登陆mysql mysql -uroot -proot -h1127.0.0.1 -P3306

查看所有数据库 show databases;

使用某个数据库 use db01;

查看数据库下所有表 show tables;

查看表字段详情 describe t_user;

导出mysql数据 mysqldump -uroot -ppass db_name 表名> mysql_bak.sql