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 开启自动提交

数据库函数

日期函数

名称	含义	案例
now()	返回当前的日期和时间	select now();
sysdate()	返回系统时间	select sysdate();
current_timestamp()	返回当前时间	select current_timestamp();
curdate()	返回当前的日期	select curdate();
curtime()	返回当前的时间	select curtime();
to_days(p1)	返回从0年到p1年的天数	select to_days(now());
from_days(p1)	给出一个天数p1，返回一个日期	select from_days(1000013);
str_to_date(p1, p2)	字符串转换为日期	select str_to_date('08/09/2008', '%m/%d/%y');
time_to_sec(p1)	将时间转换为秒值	select time_to_sec('01:00:05');
makedate(p1, p2)	拼凑日期、时间函数	select makedate(2001,31);
data_add(p1, p2)	p1日期加上指定的时间间隔	select date_add(now(),interval 1 day);
date_sub(p1, p2)	p1日期减去指定的时间间隔	select date(date_sub(now(), interval -1 day));
date_format(p1, p2)	格式化时间转换为字符串	select date_format(now(), '%y%m%d%h%i%s');
period_diff(p1,p2)	返回周期p1和p2之间的月数	select period_diff(9802,199703);
quarter(p1)	返回p1时间的季度	select quarter(now());
year(p1)	返回p1所在的年份	select year(now());
month(p1)	返回p1所在年的月份	select month(now());
day(p1)	返回p1所在月份的天数	select day(now());
week(p1)	返回p1所在的年的第几周	select week(now());
datadiff(p1, p2)	返回两个时间相差的天数	select datediff("2020-04-18",now());
dayname(p1)	获取指定日期的工作日的名称	select dayname(sysdate());
dayofweek(p1)	返回日期的工作日索引	select dayofweek(current_timestamp());
extract(p1)	提取日期的一部分	select extract(day from curdate());
timediff(p1, p2)	计算两个time或datetime值之间的差值	select timediff("2020-04-19 17:00:00", now());
timestampdiff(p1, p2)	计算两个date或datetime值之间的差值	select timestampdiff(second,curdate(), now());
weekday(p1)	返回一个日期表示为工作日/星期几的索引	select weekday(now());

聚合函数

名称	含义	案例
avg()	计算一组值或表达式的平均值	select id,avg(salary) from t_user group by id;
count()	计算表中的行数	select count(*) from t_user;
instr()	返回子字符串在字符串中第一次出现的位置	select instr("hello","h");
sum()	计算一组值或表达式的总和	select id,sum(salary) from t_user group by id;
min()	在一组值中找到最小值	selec id,mix(salary) from t_user group by id;
max()	在一组值中找到最大值	select id,max(salary) from t_suer group by id;
group_concat()	将字符串从分组中连接成具有各种选项	select group_concat(country) from t_user;

字符串函数

名称	含义	案例
concat(p1,p1,...)	将两个或多个字符串组合成一个字符串	select concat("aa","bb","cc");
length(p1)	返回字节长度	select length("你");
char_lenght(p1)	返回字符长度	select char_length("你");
left(p1,p2)	获取指定长度的字符串的左边部分	select left("hello",2);
right(p1,p2)	获取指定长度的字符串的右边部分	select right("hello",2);
replace(p1,p2,p3)	搜索并替换字符串中的子字符串	select replace("hello","h","o");
substring(p1,p2,p3)	从具有特定长度的位置开始提取一个子字符串	select substring("hello",1,2);
trim(p1)	从字符串中删除不需要的字符	select trim(" hello ");
find_in_set(p1,p2)	在逗号分隔的字符串列表中找到一个字符串	select find_in_set("a","a,b,c");
format(p1,p2,p3)	格式化具有特定区域设置的数字，舍入到小数位数。	select format(14500.2018, 2);

控制流函数

名称	含义	案例
case()	如果满足when分支中的条件，则返回then分支中的相应结果，否则返回else分支中的结果。
if	根据给定的条件返回一个值。
ifnull()	如果第一个参数不为null，则返回第一个参数，否则返回第二个参数。
nullif()	如果第一个参数等于第二个参数，则返回null，否则返回第一个参数。

数学函数

名称	含义	案例
abs(x)	返回x的绝对值	select abs(-1);
floor(x)	返回不大于x的最大整数	select floor(100.1);
ceil(x)、ceiling(x)	返回不小于x的最小整数	select ceil(20.1),ceiling(32.4);
truncate(x,d)	返回数值x保留到小数点后d位的值，截断时不进行四舍五入	select truncate(12.22043,2);
round(x)	返回离x最近的整数，截断时要进行四舍五入	select round(23.12);
round(x,d)	保留x小数点后d位的值，截断时要进行四舍五入	select round(18.1202,2);
rand()	返回0~1的随机数	select rand();
sign(x)	返回x的符号(负数，零或正)对应-1，0或1	select sign(0);
pi()	返回圆周率的值。默认的显示小数位数是7位	select pi();
pow(x,y)、power(x,y)	返回x的y次乘方的值	select pow(2, 3), power(3,3);
sqrt(x)	返回非负数的x的二次方根	select sqrt(5);
exp(x)	返回e的x乘方后的值	select exp(3);
mod(n,m)	返回n除以m以后的余数	select mod(3,4);
log(x)	返回x的自然对数，x相对于基数2的对数	select log(6);
log10(x)	返回x的基数为10的对数	select log10(90);
radians(x)	返回x由角度转化为弧度的值	select radians(12);
degrees(x)	返回x由弧度转化为角度的值	select degrees(23);
sin(x)、asin(x)	前者返回x的正弦，其中x为给定的弧度值；后者返回x的反正弦值，x为正弦	select sin(23), asin(120);
cos(x)、acos(x)	前者返回x的余弦，其中x为给定的弧度值；后者返回x的反余弦值，x为余弦	select cos(12), acos(12);
tan(x)、atan(x)	前者返回x的正切，其中x为给定的弧度值；后者返回x的反正切值，x为正切	select ,atan(12) tan(2);
cot(x)	返回给定弧度值x的余切	select cot(14);

比较函数

名称	含义	案例
coalesce(p1,p2,...)	返回第一个非空的参数如果都为空则返回null	select coalesce(null,2,3);
greatest(p1,p2,...)	返回最大的数	select greatest(1,4,0);
least(p1,p2,...)	返回最小值	select least(1,4,0);
isnull(p1)	判断p1是否为null是返回1，否则0	select isnull(1);

其他函数

名称	含义	案例
cast(p1)	将任何类型的值转换为具有指定类型的值。	select cast('2013-01-01' as datetime);
last_insert_id()	获取最后插入的记录的最后生成的序列号。	select last_insert_id();
sleet(p1)	睡眠	select sleep(2)
version()	查看数据库版本	select version();
database()、schema()	查看当前使用的数据库	select database();
user()、system_user()、session_user()	查看当前用户	select user();
connection_id()	返回连接的客户端中唯一的id	select connection_id();
charset()	返回字符串str的字符集	select charset("hello");
collation()	返回字符串str的字符排列方式	select collation("hello");

mysql 常用操作

1.启动\关闭 mysql systemctl start\stop mysqld

2.查看mysql状态 systemctl status mysqld

3.登陆mysql mysql -uroot -proot -h1127.0.0.1 -p3306

4.查看所有数据库 show databases;

5.使用某个数据库 use db01;

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

7.查看表字段详情 describe t_user;

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