一、基本操作
创建数据库 |
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 city, replace(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、避免大规模排序 |