你的位置:首页 > 信息动态 > 新闻中心
信息动态
联系我们

2.mysql基础语法

2021/11/23 2:41:59

文章目录

  • 1. order by 排序检索
  • 2. where 过滤数据
  • 3. like 通配符
  • 4. 正则表达式
  • 5. 使用创建字段
  • 6. 数据处理函数
  • 7. 聚集函数
  • 8. 分组函数
  • 9. 子查询
  • 10. 联结表
  • 11.组合查询
  • 12. 全文本搜索
  • 13. 插入数据
  • 14. 更新和删除数据
  • 15. 创建和操纵表

1. order by 排序检索

1.按单个列排序

select prod_name from products order by prod_name;

2.按多个列排序,下例仅在多行具有相同的prod_price时才对产品按prod_name进行排序

select prod_id,prod_price,prod_name from products order by prod_price,prod_name;

3.指定方向排序,默认升序,降序 desc。若在多个列上进行desc排序,必须对每个列指定desc关键字

select prod_id,prod_pricemprod_name from products order by prod_price desc;

4.order by + limit;order by位于from之后,limit位于oder by之后

select prod_price from products order ny prod_price desc limit 1;

2. where 过滤数据

1.where指定搜索条件,位于from之后,order by 位于where之后

select prod_name,prod_price from products where prod_price = 2.50;

2.where字句操作符<,>,!=,is null,not null,between and
3.组合where字句:orand;sql处理and的优先级高于or,若先处理or则需要添加圆括号

select prod_name,prod_price from products where (vend_id = 1002 or vend_id = 1003) and prod_price >= 10;

4.in 指定条件范围
5.not 否定后跟条件关键字

select prod_name,prod_price from products where vend_id not in (1002,1003) order by prod_name;

3. like 通配符

1.like 操作符与通配符使用,% 表示任何字符出现任意次数,%通配符也不能匹配值null的数据

-- 找出所有以jet起头的产品
select prod_id,prod_name from products where prod_name like 'jet%';
-- 找出任意位置包含jet的产品
select prod_id,prod_name from products where prod_name like '%jet%';

2._下划线通配符,作用同%,但是只匹配单个字符而非多个

4. 正则表达式

1.使用正则表达式

-- 匹配1或2或3 Ton
select prod_name from products where prod_name regexp '1|2|3 Ton' order by prod_name;

2.匹配特殊字符必须用\为前导,匹配\需要使用\\

select vend_name from vendors where vend_name regexp '\\.' order by vend_name;

3.匹配多个实例 *0个或多个匹配、 +1个或多个匹配、 ?0个或1个匹配、{n}指定数目的匹配、{n,}不少于指定数目的匹配、{n,m}匹配数目范围n-m

select prod_name from products where prod_name regexp '\\([0-9] sticks?\\)' order by prod_name;

在这里插入图片描述
4.定位符 ^文本的开始、$文本的结尾、[[:<:]]词的开始、[[:>:]]词的结尾

-- 0-9数字开头或者.开头
select prod_name from products where prod_name regexp '^[0-9\\.]';

5. 使用创建字段

1.concat()函数拼接字段,需要一个或多个串,通过逗号分隔

select concat(vend_name,'(',vend_country,')') from vendors;

2.trim()函数去掉左右两边空格、rtrim()去掉串右边空格、ltrim()去掉串左边空格

select concat(rtrim(vend_name),'(',rtrim(vend_country),')') from vendors;

3.as使用别名

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

6. 数据处理函数

1.文本处理函数upper()转换大写、lower()转换小写、locate()找出串的子串、subString()返回子串的字符串
2.日期和时间处理函数、具体使用查找

--比较日期,仅将给出的部分与列中的日期比较
select cust_id,order_num from orders where date(order_date) = '2005-09-01';
--检索2005年9月的数据
select cust_id,order_num from orders where date(order_date) between '2005-09-01' and '2005-09-30';
select cust_id,order_num from orders where year(order_date) = 2005 and month(order_date) = 9;

3.数值处理函数

7. 聚集函数

1.avg() 求某列的平均值
2.count() 统计行数,统计总行或某列有值的行数
3.max() 最大值,忽略值为null的行
4.min() 最小值,忽略值为null的行
5.sum() 返回指定列的和

select avg(distinct prod_price) from products where vend_id = 1003;
select count(*) from products;
select max(prod_price) from products;
select min(prod_price) from products;
select sum(prod_price) from products where vend_id = 1003;

8. 分组函数

1.group by 分组

  • group by 子句中只能是列、不能是聚集函数
  • 除聚集计算语句外,select 语句中的每个列都必须在group by子句中给出
  • 若分组中具有null值,则null将作为一个分组返回,多个null值分为一组
  • group by位于where之后、order by 之前
select vend_id, count(*) as num_prods from products group by vend_id;

2.having 过滤分组,where过滤行

  • where在数据分组前进行过滤;having在数据分组后进行过滤
  • having通常对分组的select后的聚集函数做过滤
  • select顺序:select、from、where、group by、having、order by、limit
select vend_id, count(*) as num_prods from products where prod_price >= 10 group by vend_id having count(*) >= 2;

9. 子查询

1.将一条select语句的返回结果用于另一条select语句的where语句

  • 性能问题导致通常采用联结表
select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'TNT2');

10. 联结表

1.=等值联结实际上也就是内连接inner join on

select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id = products.vend_id;
select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id = products.vend_id;

2.表别名、自联结(单条select语句不能同一次引用相同的表)

select p1.prod_id,p1.prod_name from products as p1,products as p2 where p1.vend_id = p2.vend_id and p2.prod_id = 'DTNTR';

3.外联结,left outer join onright outer join on

  • 外联结包含了那些在相关表中没有关联的行
  • 左外联结,左表选择所有行,右外联结,右表选择所有行
select customers.cust_id,orders.order_num from customers left outer join orders on customers.cust_id = orders.cust_id;
select customers.cust_id,orders.order_num from customers right outer join orders on customers.cust_id = orders.cust_id;

4.带聚集函数的联结

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

11.组合查询

1.union 将多个查询的结果合并为单个返回

  • 必须由2或2条以上的select语句组成,语句之间使用union关联
  • union中的每个查询必须包含相同的列或函数表达式
  • union并集时会自动去除重复行
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);

2.union all 返回所有匹配行,包含重复行
3.对组合查询结果排序,order by必须出现在最后一条select语句之后,对结果集进行排序

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;

12. 全文本搜索

  • myisam支持全文本搜索,innodb不支持
  • create table时,通过指定fulltext(column)去进行全文本搜索
  • 不要在导入数据时使用fulltext,有助于更快的导入数据

1.match() 指定被搜索的列,against()指定要使用的搜索表达式

  • 传递给match()的值必须与fulltext()定义中相同,若指定多个列,则次序也需完全一致
  • 全文搜索会对结果排序,较高等级的行先返回
--此处指定fulltext(note_text)
select note_text from productnotes where match(note_text) against('rabbit');

2.查询扩展

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

13. 插入数据

1.插入完整行

insert into customers values ('jet','100 street','90046','USA',null);
insert into customers (cust_name,cust_address,cust_zip,cust_country,cust_email) values ('jet','100 street','90046','USA',null);

2.插入多行

insert into customers (cust_name,cust_address,cust_zip,cust_country,cust_email) values 
('jet','100 street','90046','USA',null),
('sss','100 street','90047','USA',null)

3.插入检索出的数据,mysql不关心select返回的列名,仅做对应列填充

insert into customers(name,age) select(name,age) from CustomersOld;

14. 更新和删除数据

1.更新

update customers set cust_name = 'bis',cust_email='ww@fudd.com' where cust_id = 10005;
update customers set cust_email = null where cust_id = 10005;

2.删除

delete from customers where cust_id = 10086;

15. 创建和操纵表

1.create table 建表

  • PRIMARY KEY 主键,可以指定多个列为主键,用逗号分隔
  • auto_increment 每个表只允许一个,且必须被索引,列数据自动增量生成
  • engine = innodb 指定数据处理内部引擎
CREATE TABLE `Student`(
`s_id` VARCHAR(20) not null auto_increment,
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
) engine = innodb;

2.alter table 更新表

--添加列
alter table vendors add vend_phone char(20);
--删除列
alter table vendors drop column vend_phone;
--删除表
drop table customers2;
--重命名表
rename table customers2 to customers;