MySQL必知必会_学习笔记

1.了解SQL
数据库：保存有组织的数据的容器（通常是一个文件或一组文件）
模式：关于数据库和表的布局及特性信息。
主键：一列（或一组列），其值能够唯一区分表中每个行。
表：某种特定类型数据的结构化清单。
列：表中的一个字段，所有表都是由一个或多个列组成的。
行：表中的一条记录。

2.MySQL简介
SQL:结构化查询语言,用来鱼数据库通信的语言
DBMS:数据库管理系统,对数据库进行检索,管理,处理.包括Oracle,MySQL,Microsoft SQL Server, Access ,PostgreSQL
MySQL:开源,可二次开发,执行速度快,可信赖

查看MySQL是否安装:sudo ps -ef|grep mysqld
查看MySQL版本:mysql -V
运行MySQL:sudo mysql -uroot -p
检索版本:SELECT VERSION();
检索时间:SELECT NOW();
退出MySQL:EXIT;或QUIT;

3.使用MySQL
显示数据库:SHOW databases;
使用数据库:USE database_name;
显示表:SHOW tables;
显示表例:SHOW column FROM table_name;或DESCRIBE table_name;
显示广泛的服务器状态信息:SHOW status;
显示创建数据库的语句:SHOW CREATE database database_name;
显示创建表的语句:SHOW CREATE table table_name;
显示用户的权限:SHOW GRANTS;
显示服务器错误:SHOW ERRORS;
显示服务器警告:SHOW WARNINGS;

4.检索数据
检索单个列:SELECT column_name FROM table_name;
检索多个列:SELECT column_name1, column_name2, column_name3 FROM table_name;
检索所有列:SELECT * FROM table_name;
检索表中某列不同值(去重):SELECT DISTINCT column_name FROM table_name;
检索表中某列前5个值:SELECT column_name FROM table_name LIMIT 5;
从表中某列5行起向后检索4个值:SELECT column_name FROM table_name LIMIT 5, 4;
完全限定列名表名检索:SELECT table_name.column_name FROM database_name.table_name;

5.排序检索数据
检索某列并按列名排序:SELECT column_name FROM table_name ORDER BY table_name;
检索某列并按列名降序排序:SELECT column_name FROM table_name ORDER BY table_name DESC;
检索列1,列2,列3并按列名1,列名2排序:SELECT column_name FROM table_name1, table_name2 table_name3 ORDER BY table_name1, table_name2;

6.过滤数据
按条件检索:SELECT column_name FROM table_name WHERE column_name = XXX;(>,<,<>,!=,<=,>=,BETWEEN,=)
检索非空:SELECT column_name FROM table_name WHERE column_name IS NULL;

7.数据过滤
AND:SELECT column_name1, column_name2, FROM table_name WHERE column_name1 = xx and column_name2 = XXX;
OR:SELECT column_name1, column_name2, FROM table_name WHERE column_name1 = xx or column_name2 = XXX;
IN:SELECT column_name1, column_name2, FROM table_name WHERE column_name1 IN (XXX,XXX);
NOT IN:SELECT column_name1, column_name2, FROM table_name WHERE column_name1 NOT IN (XXX,XXX);

8.用通配符进行过滤
LIKE操作符
不区分大小写:SELECT column_name FROM table_name WHERE column_name LIKE ‘xxx’;
%表示任何字符出现任意次数:SELECT column_name FROM table_name WHERE column_name LIKE ‘%x%x%’;
_只匹配单个字符而不是多个字符:SELECT column_name FROM table_name WHERE column_name LIKE ‘x’;

9.用正则表达式进行搜索
检索包含xxx的文本:SELECT column_name FROM table_name WHERE column_name REGEXP ‘xxx’;
‘.’检索任意字符:SELECT column_name FROM table_name WHERE column_name REGEXP ‘.00’;
区分大小写:SELECT column_name FROM table_name WHERE column_name REGEXP BINARY ‘X’;
‘|’或检索:SELECT column_name FROM table_name WHERE column_name REGEXP ‘XXX|XXX’;
检索几个字符:SELECT column_name FROM table_name WHERE column_name REGEXP ‘[123]’;
检索除xy字符:SELECT column_name FROM table_name WHERE column_name REGEXP ‘[^xy]’;
检索X字符:SELECT column_name FROM table_name WHERE column_name REGEXP ‘\x’;
检索字符类:SELECT column_name FROM table_name WHERE column_name REGEXP ‘[[:digit]]’ ORDER BY table_name ;
从头匹配字符X,文本尾匹配任意X个数字:SELECT column_name FROM table_name WHERE column_name REGEXP ‘^\x[[:digit:]]{x}$’ ORDER BY table_name;

LIKE要求整个数据都要匹配，而REGEXP只需要部分匹配即可.
LIKE是全匹配，如果被匹配的文本只是列值的一部分，LIKE将不会找到它，相应的行也不会被返回。
而REGEXP在列值内进行匹配，如果匹配的文本在列值中出现，REGEXP将会找到它，相应的行降被返回。

10.创建计算字段
SELECT Concat (RTrim(column_name1), ‘(’, LTrim(column_name2), ’)’) AS column_new_name FROM table_name ORDER BY column_name;
匹配column_name1,column_name2在column_new_name新字段中展示,并去掉column_name1右边的空格以及column_name2左边的空格((RTrim去掉串右边的空格)(LTrim去掉串左边的空格))
SELECT column_name1, column_name2, column_name3, column_name1*column_name2 AS column_new_name FROM table_name;(加减乘除)(+, -, *, / )
SELECT column_name1, Upper(column_name2) AS column_new_name FROM table_name;将列_2中的文本转换为大写

创建
创建数据库:CREATE DATABASE database_name;
创建表:CREATE TABLE column_name;

删除
删除数据库:drop database database_name;

https://blog.csdn.net/weixin_40047053/article/details/84251219