MySQL基础操作汇总

时间:2021-10-15 06:59:41


一、基本操作

创建数据库

create database learnsql

登陆本地MySQL

Mysql -h localhost -u username -ppassword

使用数据库

use learsql;

显示该数据库下所有的表

show tables;

列出表的全部字段和属性

describe employee_tbl;

二、表和字段的操作

创建表格

create table table_name

(ID datatype null | not null,

...

primary key(ID) || foreign key(ID) references another_table_name (emp_id));

删除表格

drop table table_name;

更改表名

alter table table_name rename to new_table_name;

添加列

alter table table_name add column colunmname datatype;

添加主键约束

alter table table_name add primary key (pid)

去除主键约束

alter table table_name drop primary key

修改列定义

alter table table_name modify columnname datatype

修改列名

alter table table_name  change columnname  new_columnname 

type NULL

三、操作数据

插入

insert into table_name(column1,column2...) values(value1,value2...)

更新

update table_name set column_name1='value1', column_name2='value2'  [where condition]

删除

delete from table_name [where condition]  //只有删除行用delete剩余用drop

查询

select [*|distinct column1,column2...]

from table1 [,table2...]

where [condition1 and|or condition2]

order by column1 asc|desc            //asc为升序,desc为降序

四、操作符(前面都可以加上NOT进行求反)

IS NULL

update table_name set id=0 where id IS NULL

BETWEEN

select column1,column2 from table_name where column2  BETWEEN 100 AND 200

IN

select column1  from table_name where column1 IN ('100','300','400')

LIKE

select  column1   from table_name  where column1  LIKE'%s_'  

 //'%'代表一个或多个字符,'_'代表一个数字或字符

五、函数

5.1 汇总函数

默认情况下NULL值不计算,除非使用count(*); 都可以使用distinct关键字,只计算不同条目的汇总

计数

count(distinct column)

求和

sum()

最大值

max()

最小值

min()

均值

avg()

例子

select sum(column) from table_name

5.2 字符函数

替换

select cityreplace(city,'i','z') from table_name

全部替换为

小写/大写

lower(city)  upper(city)

取子串

substr(column ,1,3) //从第一个字符开始取三个字符

子串第一次

出现的位置

instr(column,'str') 

条件替换

decode(column ,'indianapolis','indy','greenwood','green','other') 

//indianapolis显示为indy,greenwood显示为green,否则为other‘

左右填充

lpad(column ,20,'.')  //用'.'将column中不满20字符的填充上直到20字符

Rpad(column ,20,'.') 

5.3 算数函数

绝对值

abs()

舍入

round()

开方

sqrt()

取符号

sign()

求幂

power()

六、子/组合查询(例子)

就是利用select语句返回在一表中的值用户外层where的比较

select * from products where cost >(select avg(cost) from products);        //返回比平均价格高的数据

组合多个查询

select column1 from table_name1

union/union all                                //前者不显示相同的值,后者显示相同值

select column2 from table_name2

七、索引

创建单字段索引

create index index_name on table_name (column)

唯一索引

create unique index index_name on table_name  (column )

组合索引:对于经常在查询的where语句里共同使用的字段

create index index_name on table_name (column1,column2)

删除索引

drop index index_name  on table_name 

显示索引

show indexes from table_name 

适合使用索引的场合

1、首先是较大规模的表中

2、大多数用户表结合的字段

3、经常在order by 或where中使用的字段

4、具有大量唯一值的字段。

不适合使用索引的场合

1、小规模的表

2、会在where语句中返回大部分记录的字段

3、经常被操作的字段

4、包含大量NULL的字段

批量操作时可以先删除索引,等操作完毕后再创建索引,有助于改善性能并避免索引的碎片化。

八、创建视图

从表创建视图

create view view_name as select column1,column2 from table_name;

从视图创建表

create table table_name as select column1,column2 from view_name;

删除视图

drop view view_name

删除表

drop table table_name

九、备注

大小写的问题:

似乎只有replace进行替换匹配时才区分大小写LIKE关键字也不区分大小写

排序与分组:

select

from

where

group by

having

order by

LIMIT

提高性能

1、from子句中的表把小表放在前面,大表放在后面

2、让SQL优化器先读取where语句中的最严格的条件,这样以后的过滤范围就会大幅缩小

3、避免OR操作符(可用IN代替),避免HAVING操作符

4、避免大规模排序