MySQL数据库语句完全讲解

mysql -u 用户名 -p

use 数据库名;

show databases;

show tables;

show columns from 表名; 
describe 表名;  # 另一种快捷方式

show status;

show create database 数据库名;

show create table 表名;

desc 表名;

select 列名1, 列名2, ... from 表名;  

select * from 表名;

show grants;

show erroes;
show warnings;

help show;

show create database 数据库名;

select database();

show insex from 表名;

create database 数据库名;

# 举例：创建并使用数据库
create database phone;  # 创建数据库 phone
show databases;  # 查看是否成功创建 phone
use phone;  # 使用数据库

/* 在使用 use 选择数据库的状态下，也能够操作其他数据库中的表。（将数据库名和表名用 . 连接起来）*/
select * from db2.table1;

create database 数据库名 character set utf8;
# 另一种缩写方式
create database 数据库名 charset utf8;

create table 表名(列名1 字段类型, 列名2 字段类型, 列名3 字段类型, …); 

create index 索引名 on 表名 (列名1, 列名2, ...);

create table 新表名 as select * from 旧表名;

create table 新表名 like 原表名;

# 设置单个主键列
create table 表名 (主键列名 数据类型 primary key, 普通列名 数据类型);

# 设置复合主键
create table 表名 (主键列名1 数据类型, 主键列名2 数据类型, 普通列名 数据类型, primary key (主键列名1, 主键列名2));

create table 表名 (唯一键列名 数据类型 unique, 普通列名 数据类型);

create table 表名 (列名 数据类型 default 默认值 ...);

# 修改已有列并为其添加默认值
alter table 表名 modify column 列名 数据类型 default 默认值;
# 添加默认值到新列
alter table 表名 add column 列名 数据类型 default 默认值;
# 添加默认值，不更改数据类型
alter table 表名 alter column 列名 set default 默认值;

update customers   # 更新 customers 表
set cust_name = 'the fudds', cust_email='elmer@fudd.com'  # 将 cust_name 列的值更新为 the fudds,cust_email 列的值更新为 elmer@fudd.com
where cust_id = 10005;  # 仅更新 cust_id 等于 '10005' 的行

update ignore 表名
set 列1 = 值1, 列2 = 值2, ...
where 条件;  # 在执行 update 操作时，如果出现某些错误（如重复键错误），则忽略这些错误并继续更新其余记录

rename table 旧表名1 to 新表名1,旧表名2 to 新表名2... ;

drop database 数据库名;

drop table 表名;
# 如果目标表不存在的情况下执行drop命令会报错，可以加个 if exists
drop table if exists 表名;

alter table 表名 drop 列名; 

delete from 表名;
# 删除符合条件的那一行数据
delete from 表名 where 列名=1006;

# turncate table（速度比delect快）
# 如果表被其他表通过外键约束引用，通常无法执行 truncate 操作。你需要先删除外键约束，或使用 delete 操作。
# 通常，truncate 不会触发与表相关的触发器（triggers），而 delete 可能会触发 before delete 或 after delete 触发器。
# truncate 不支持条件where,它会删除表中所有的记录，没有条件限制。
truncate table 表名;  # 删除整个表的数据，表结构不变

alter database 数据库名 character set utf8;

alter table 表名 modify 列名 数据类型;

alter table 表名 change 原列名 新列名 数据类型;

# 把某一列放在最前面
alter table 表名 modify 列名 数据类型 first;

alter table 表名 add 列名 数据类型;
# 把列添加到最前面
alter table 表名 add 列名 数据类型 first;
# 把列插到某一列后面
alter table 表名 add 列名 数据类型 after 某一列列名;

alter table 表名 modify column 列名 数据类型 default null;

drop index 索引名 on 表名;

create index my_ind on test1 (name);  # 在 表test1 的 列name 上创建名为 my_ind 的索引
show index from test1;  # 显示创建的索引
show index from test1 \g  # 纵向显示列值方便查看
drop index my_ind on test1;  # 删除索引

alter table 表名  # 指定要修改的表
add constraint 外键名称  # 定义外键名称
foreign key (外键列名)  # 当前表中哪个列将作为外键
references 参照表名(主键列名);  # 引用目标表及其主键

# 假设我们有两个表：orders 和 customers。我们想要在 orders 表中添加一个外键，以引用 customers 表中的 customer_id 列。
create table customers (customer_id int primary key, name varchar(100));
create table orders (order_id int primary key, order_date date, customer_id int);
alter table orders add constraint fk_customer foreign key (customer_id) references customers(customer_id);

insert into 表名 values(数据1， 数据2， ...);  # 句高度依赖于表中列的定义次序

insert into 表名 (列名1, 列名2, ...) values(数据1， 数据2， ...);  
# 也可以一次性插入多条数据： 
insert into 表名 (列名1, 列名2, ...) values (数据1， 数据2， ...),(数据1， 数据2， ...),(数据1， 数据2， ...)...;

# 举例
insert into phone (pid, name, color) values(456, 'marry', 'blue');
insert into phone (pid, name, color) values(456, 'aaa', 'rrrr'),(789, 'bbb', 'ooo'),(012, 'ccc', 'yyy');

insert low_priority into 表名 (列名1, 列名2, ...) values (值1, 值2, ...);

insert into 新表名(列1, 列2, ...) select 列1, 列2, ... from 原表名 where 条件;

# 从表中检索一列
select 列名 from 表名;
# 从表中检索多列
select 列名, 列名, 列名 from 表名;
# 检索所有列
select * from 表名;

# 返回每列的唯一值 (distinct 会作用于查询中的所有列的组合，而不是单独某一列。)
select distinct 列名, 列名 from 表名;
# 如果只去除某一列的重复，应该只选择该列，而不是整个组合。
select distinct 列名 from 表名;

# 显示前m行
select * from 表名 limit m;
# 如果要得出m行后的n行，可指定要检索的开始行和行数
select * from 表名 limit m,n;  # m：跳过前m行，即从第m+1行开始；  n：从第m+1行开始，返回后n行数据

select 表名.列名 from 数据库名.表名;

# 按顺序检索某一列
select 列名1 from 表名 order by 列名2;  # 按列名2的顺序给列名1排序（order by后面的排序列和select的选择列可以不一样）
# 按多个列排序
select 列名1, 列名2, 列名3...  from 表名 order by 列名1, 列名2...;  # 先按 order by 中列1的顺序排序，如果列1有相同值，相同部分按列2排序
# 降序排序
select 列名1, 列名2, 列名3... from 表名 order by 列名1 desc; 
# 也可以指定某一列为降序
select 列名1, 列名2, 列名3... from 表名 order by 列名1 desc, 列名2... ;  # 列1降序，列2升序，如果每列都想降序；必须对每列都指定desc关键字

# 结合order by + limit
select 列名 from 表名 order by 列名 desc limit 1;  # 找到最高值

select 列名1, 列名2 from 表名 where 列名1 = 'a';  # 找到列名1中值等于a的 列1列2数据
select 列名1, 列名2 from 表名 where 列名1 between a and b;  # 找到列名1中值在`a<=值<=b`范围内的 列1列2数据
# where 子句可以组合使用
select 列名1, 列名2, 列名3 from 表名 where 列名1=100 and 列名2 <=10;  # 找到 列名1中值=100 并且 列2数据<=10 的 列1列2列3数据
select 列名1, 列名2, 列名3 from 表名 where 列名1=100 or 列名2 =200;  # 找到 列名1中值=100 或者 列2数据=200 的 列1列2列3数据

# where子句的计算次序（默认 and 的优先级高于 or）
select 列名1, 列名2, 列名3 from 表名 where 列名1=100 or 列名2 =200 and 列名3>=10;  # 等价于where (列名1 = 100) or (列名2 = 200 and 列名3 >= 10);找到 列名1中值=100 或者 （列2数据=200并且列名3>=10） 的 列1列2列3数据
select 列名1, 列名2, 列名3 from 表名 where (列名1=100 or 列名2 =200) and 列名3>=10;  # 找到 （列名1中值=100或者列2数据=200）并且 列名3>=10 的 列1列2列3数据

# where 子句的 in 操作符：in 操作符等价于多个 or 条件组合。它的作用是检查 列名1 的值是否匹配括号中的任意一个值
select 列名1, 列名2, 列名3 from 表名 where 列名1 in (值1， 值2， 值3) order by 列名2;

# where 子句的 not 操作符：not 操作符否定它之后所跟的任何条件
select 列名1, 列名2, 列名3 from 表名 where 列名1 not in (值1， 值2， 值3) order by 列名2;

select 列名 from 表名 where 列名 is null;

# %通配符：%表示任何字符出现任意次数。（%会区分大小写；%可以匹配0个字符）
select 列名 from 表名 where 列名 like 'aaa' order by 列名;  # 检索字符串 完全等于 'aaa' 的字符串
select 列名1 from 表名 where 列名1 like 'aaa%';  # 检索任意以aaa开头的字符串（字符串：aaa也会被检索）
select 列名1 from 表名 where 列名1 like '%aaa%';  # 检索任何包含aaa的字符串
select 列名1 from 表名 where 列名1 like 'a%b';  # 检索任何以a开头b结尾的字符串

# %通配符不能匹配null
select 列名1 from 表名 where 列名1 like '%';  # 这样也检索不到 null 值

# _通配符：和%通配符用途一样，但只匹配单个字符
select 列名1 from 表名 where 列名1 like 'aaa_';  # 检索任意以aaa开头且后面只跟一个字符的字符串 （字符串：aaa不会被检索；aaall也不会被检索，只能检索到aaal）

# 基本字符匹配(正则表达式不会区分大小写，区分大小写的话需要在 regexp 后面添加 binary 关键字，)
select 列名 from 表名 where 列名 regexp 'aaa' order by 列名;  # 检索字符串所有包含aaa的字符串
select 列名 from 表名 where 列名 regexp '.aaa' order by 列名;  # 检索字符串所有包含aaa结且前面跟有一个字符 的 字符串（.表示匹配任意一个字符，例如：22aaa,2aaa,2aaab）

# or 匹配 （|）
select 列名 from 表名 where 列名 regexp 'aaa|bbb|ccc' order by 列名;  # 匹配包含 aaa、bbb 或 ccc 任意一个的字符串

# 匹配几个字符之一 （[]）
select 列名 from 表名 where 列名 regexp '[123]aaa' order by 列名;  # 它要求该字符串必须以 1、2 或 3 之一开始，并紧接着是 aaa。换句话说，它会匹配 1aaa、2aaa 或 3aaa 开头的任何字符串。

# 匹配范围
select 列名 from 表名 where 列名 regexp '[1-5]aaa' order by 列名;  # 匹配 列名 中以数字 1 到 5 之间的任何一个数字开头，并紧接着是 aaa 的字符串

# 匹配特殊字符（例如：.[]|_等）:用\\为前导
select 列名 from 表名 where 列名 regexp '\\.' order by 列名;  # 匹配包含 .（点号）的字符串

# 举例
select 列名 from 表名 where 列名 regexp '[[:digit:]]{4}'  # 匹配任何包含 4个连续数字 的字符串
select 列名 from 表名 where 列名 regexp '[0-9][0-9][0-9][0-9]'  # 和上面一致：匹配任何包含 4个连续数字 的字符串
select 列名 from 表名 where 列名 regexp '\\([0-9]aas?\\)'  # 匹配任何包含 以(开头，后跟一个数字，后跟aas或者aa(aaa? 只匹配 aas 或 aa) 最后跟一个) 的字符串

# 举例
select 列名 from 表名 where 列名 regexp '^[0-9\\.]'  # 匹配任何 以一个数字（0-9）或一个点（.）开始 的字符串
select 列名 from 表名 where 列名 regexp '[0-9\\.]'  # 匹配任何 包含至少一个数字（0-9）或一个点号（.）的字符串。

select concat(列名1, '(', 列名2, ')') from 表名 order by 列名1;  # 返回结果：列名1(列名2)

select concat(rtrim(列名1), '(', 列名2, ')') from 表名 order by 列名1;  # rtrim()：去掉右边所有空格;  ltrim()：去掉左边所有空格;  trim()：去掉两边所有空格

select concat(rtrim(列名1), '(', 列名2, ')') as 新列名 from 表名 order by 列名1;

select 列名3+列名4 as 新列名 from 表名;

select left(列名1, 3) as 新列名 from 表名;  # 返回列名1的前3个字符
select locate('pro', product_name, 3) from 表名;  # 从 列product_name 第3个字符开始 找到 pro 首次出现的位置（找不到返回 0 ）
select right(列名1, 3) as 新列名 from 表名;  # 返回列名1的后3个字符
select soundex(列名) from 表名;  # 返回每个数据发音的编码值
select 列名 from 表名 where soundex(列名) = soundex('smith');  # 想找出所有发音与 "smith" 相似的数据
select substring(列名, 1, 3) from 表名;  # 提取前 3 个字符
select substring(列名, 4) from 表名;  # 从第 4 个字符开始提取，提取到字符串的末尾

select abs(列名) from 表名;  # 返回绝对值

select avg(数值列名) from 表名;
select count(*) from 表名;
select avg(distinct prod_price) as acg_price from prducts where vend_id = 1003;
select count(*) as num_items, min(prod_price) as price_min, max(prod_price) as price_max, avg(prod_price) as price_avg from products; 

select vend_id, count(*) as num_prods from products group by vend_id;  # 对每个vend_id而不是整个表计算num_prods

select vend_id, count(*) as num_prods from products group by vend_id with rollup;  # with rollup 是一个用于生成汇总数据的特殊选项。它在原本分组的基础上，额外生成一个“总计”行，该行显示所有vend_id的总产品数量。

select cust_id，count(*) as orders from orders group by cust_id having count(*) >= 2;  # 过滤掉 聚合后cust_id数量 小于2 的 cust_id

select vend_id，count(*) as num_prods from products where prod_price >=10 group by vend_id having count(*) >= 2；  # 过滤掉 聚合后vend_id数量小于2 且 prod_price大于或等于10的 vend_id

select order_num, sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price) >= 50 order by ordertotal;  # 查询返回了所有ordertotal大于或等于 50 的order_num，并且按照ordertotal从小到大排序。

select cust_nume,cust_contact from customers where cust_id in (select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'tnt2'));  # 先在表orderitems中查询prod_id='tnt2'的order_num，再在orders表中找 order_num=子句中查询结果 的cust_id，最后在表customers中找到 cust_id=子句中查询结果 的cust_nume和cust_contact

# 修改格式
select cust_nume,cust_contact 
from customers 
where cust_id in (select cust_id 
                  from orders 
                  where order_num in (select order_num 
                                      from orderitems 
                                      where prod_id = 'tnt2'));
                                      
# 等同于
select cust_name，cust_contact
from customers,orders,orderitems
where customers.cust_id = orders.cust_id
and orderitems.order_num = orders.order_num
and prod_id = 'tnt2';

select cust_name,
       cust_state,
       (select count(*)
        from orders
       	where orders.cust_id = customers.cust_id) as orders
from customers
order by cust_name;

select concat(rtrim(vend_name),'(',rtrim(vend_country),')') as vend_title
from vendors
order by vend_name;

select cust_name，cust_contact
from customers as c，orders as o，orderitems as oi
where c.cust_id = o.cust_id
and oi.order_num = o.order_num
and prod_id = 'tnt2'；

# 联结（很少用，基本都用下面的内联结方式）
select vend_name,prod_name,prod_price
from vendors，products
where vendors.vend_id = products.vend_id
order by vend_name, prod_name;

# 笛卡尔积
select vend_name,prod_name，prod_price
from vendors，products
order by vend_name，prod_name;

select prod_name, vend_name, prod_price, quantity
from orderitems,products,vendors
where products.vend_id = vendors.vend_id
and orderitems.prod_id = products.prod_id
and order_num = 20005;

# 用内联结实现上面等值联结的查询
select vend_name, prod_name, prod_price
from vendors inner join products
on vendors.vend_id = products.vend_id;  # inner join 表示将 vendors 表与 products 表按照 vend_id 进行连接，连接条件是 vendors.vend_id 与 products.vend_id 相等。这样能获取所有与供应商相关的产品信息。

select prod_id, prod_name
from products
where vend_id = (select vend_id
                 from products
                 where prod_id = 'dtntr');
                 
# 自联结实现上述查询
select pl.prod_id, pl.prod_name
from products as pl, products as p2
where pl.vend_id = p2.vend_id
  and p2.prod_id = 'dtntr';

select c.*, o.order_num, o.order_date,
oi.prod_id, oi.quantity, oi.item_price
from customers as c, orders as o, orderitems as oi
where c.cust_id = o.cust_id
and oi.order_num = o.order_num
and oi.prod_id = 'fb';

select customers.cust_id, orders.order_num
from customers left outer join orders
on customers.cust_id = orders.cust_id;  # 返回 左表（第一个表） 中的所有记录,对于没有匹配的右表记录，查询结果中的相关列会显示为 null

select customers.cust_name, customers.cust_id,
	   count(orders.order_num) as num_ord
from customers inner join orders
on customers.cust_id = orders.cust_id
group by customers.cust_id;

select customers.cust_name，
customers.cust_id,
count(orders.order_num) as num_ord
from customers left outer join orders
on customers.cust_id= orders.cust_id
group by customers.cust_id;

select vend_id, prod_id, prod_price
from products
where prod_price <= 5;

select vend_id, prod_id, prod_price
from products
where vend_id in (1001,1002);

# 组合上面两条语句
select vend_id, prod_id, prod_price
from products
where prod_price <= 5
union
select vend_id, prod_id, prod_price
from products
where vend_id in (1001,1002);  # 会自动去除重复的行

# 等同于
select vend_id, prod_id, prod_price
from products
where prod_price <= 5
or vend_id in (1001,1002);

# 包含重复的行
select vend_id, prod_id, prod_price
from products
where prod_price <= 5
union all
select vend_id, prod_id, prod_price
from products
where vend_id in (1001,1002);  

# 对组合查询结果排序
select vend_id, prod_id, prod_price
from products
where prod_price <= 5
union
select vend_id, prod_id, prod_price
from products
where vend_id in (1001,1002)
order by vend_id, prod_price;   # 对返回最终结果进行排序，不是只对第二条select语句进行排序 

create table productnotes
(
note_id int not null auto_increment,
prod_id char(10) not null,
note_date datetime not null,
note_text text null,
primary key(note_id),
fulltext(note_text)  # 为 note_text 字段创建全文索引
)engine=myisam;

select note_text
from productnotes
where match(note_text) against('rabbit');

select note_text,
	match(note_text) against('rabbit') as rank  # 计算每条记录与搜索词的相关性
from productnotes;  # 查询结果返回note_text,rank两列

select note_text
from productnotes
where match(note_text) against('anvils' with query expansion);  # 在原始查询基础上，自动找到与 'anvils' 相关的其他词汇（这些词是基于 fulltext 索引的相关文档生成的）。

select note_text
from productnotes
where match(note_text) against('heavy -rope*'in boolean mode);  # 匹配包含heavy但不包含任意以rope开始的词的行

select note_text
from productnotes
where match(note_text) against('+rabbit +bait' in boolean mode);  # 匹配包含词rabbit和bait的行

select note_text
from productnotes
where match(note_text) against('rabbit bait' in boolean mode);  # 匹配包含rabbit和bait中的至少一个词的行

select note_text
from productnotes
where match(note_text) against('"rabbit bait"' in boolean mode);  # 匹配短语rabbit bait而不是匹配两个词rabbit和 bait

select note_text
from productnotes
where match(note_text) against('>rabbit <carrot' in boolean mode);  # 匹配rabbit和carrot，增加前者的等级，降低后者的等级

select note_text
from productnotes
where match(note_text) against('+safe +(<combination)' in boolean mode);  # 匹配词safe和combination，降低后者的等级。