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