一、sql介绍
1.1 常用动作
sql语言简洁只有7个动词:SELECT , DROP, ALTER, CREATE,INSERT, UPDATE,DELETE;
获取表结构:Desc 表名;(show databases;tables;数据库和表)。
1 INSERT INTO 表名称 VALUES(值1, 值2,....), (值1, 值2,....), (值1, 值2,....);2 INSERT INTO table_name (列1, 列2,...) VALUES(值1, 值2,....);3
4 update 表名 set
5 列1=新值1 ,6 列2=新值27 Whereexpr;(不加影响所有行);8
9 delete from 表名 whereexpr;10
11 select 列1,列2,......列n from 表名 whereexpr;12 select * from tmp where name is not null;mysql> select * from tmp where name is null;
1.2 注意事项
当条件比较多时,应用括号来避免混乱。
and的优先级比or高。
内存里面的结果集筛选得使用having。
like 模糊匹配
% 通配任意字符
_ 通配单一字符
In:in (4,11); between: between 100 and 500;
1.3 NULL查询
1 select * from tmp where name is not null;2 select * from tmp where name is null;
二、建表语法
create table表名 (
列名1 列类型1 列1参数,
列名2 列类型2 列2参数,
....
...
列名n 列类型n 列n参数
)engine myisam/innodb/bdb charset utf8/gbk/latin1...
mysql> create tabletmp(-> id int,-> name varchar(20)-> )charset utf8 engine myisam;
2.1 列类型
数值型:整型( tinyint/smallint/int)、浮点型(Float/double/decimal)、定点型。Int(4byte,0-2^32-1)
属性:Unsigned,zerofill,M
字符串:char、varchar、text、bolb(字符串当二进制看)
日期时间类型:时间戳: timestamp
Zerofill属性默认决定列为unsigned;
2.2 列默认值
NULL查询不便
NULL索引效果不高;
所以实用中避免列值为NULL;
如何避免,声明列 NOT NULL default 默认值;
例:
mysql> create tablet9(-> id int not null default 0,-> name char(10) not null default ''
-> );
主键primary key 是能区分每一行的列。此列不重复
1 mysql> create tablet11 (2 -> id int primary key not null default 0,3 -> name char(2) not null default ''
4 ->);5
6 mysql> create tablet12 (7 -> id int,8 -> name char(2),9 -> primary key(id)10 ->);11
12 mysql> create tablet3(13 -> id tinyint primary key not null auto_increment);
三、表操作
3.1 列的删除增加与修改
1、Alter table 表名 add 列名称 列类型 列参数; [加的列在表的最后]
alter table m1 add birth date not null default '0000-00-00';2、Alter table 表名 add 列名称 列类型 列参数 after 某列; [把新列加在某列后]
alter table m1 add gender char(1) not null default ''after username;3、Alter table 表名 add 列名称 列类型 列参数 first; [把新列加在最前面]
alter table m1 add pid int not null default 0 first;
3.1.1 删除列
Alter table 表名 drop 列名;
3.1.2 修改列类型
Alter table表名 modify 列名 新类型 新参数; (不能修改列名);
例:alter table m1 modify gender char(4) not null default '';
3.1.3 修改列名及列类型
Alter table表名 change 旧列名 新列名 新类型 新参数;
例:alter table m1 change id uid int unsigned;
3.2 改表名
Rename table regist3 to reg3;
3.3 删除表
drop table 表名;
3.4 增加列
mysql> alter table t12 add high tinyint unsigned not null default 0;
3.5 表的增删改查
INSERT INTO 表名称 VALUES(值1, 值2,....), (值1, 值2,....), (值1, 值2,....);INSERT INTOt1(field1,field2) VALUE(v001,v002);update 表名 set 列1=新值1 ,列2=新值2 Whereexpr;(不加影响所有行)delete from 表名 whereexpr;select 列1,列2,......列n from 表名 where expr
四、视图
4.1 创建视图
视图创建语法:CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHEREcondition;Create [algorithm = UNDEFINED/MERGE/TEMPTABLE] view view_name as select statement;([]里面的可写可不写)
mysql> create view v1 as select goods_name,goods_id,shop_price from goods;
查看所有表:show tables;
查看表结构:desc 表名/视图名;
查看建表过程: show create table 表名;
查看建视图过程: show create view 视图名;
删除表: drop table 表名;
删除视图: drop view 视图名;
查看表详细信息: show table status;能看出是否是视图
查看某张表详细信息: show table status where name=’表名’;(/G竖着排列)
改表名: rename table oldname to newname;
清空表数据: truncate 表名;(相当于删除表,再重建两步操作)快
Delete from 表名;(DML操作,删除之后主键还是在自增长;)
查看带character变量:show variables(变量) like '%character%';
Set names utf8
五、索引(index)
5.1 索引类型
Key 普通索引;
Unique key 唯一索引;(主要起到约束的作用,比如注册邮箱;)
Primary key 主键索引;(一张表只能有一个;)
Fulltext 全文索引;(中文环境下无效,,要分词+索引,一般用第三方解决方案.如sphinx);
查看索引详情信息 explain select 语句;
mysql> create tablet1 (-> name char(10),-> email char(20),-> keyname(name),-> unique keyemail(email)-> );
5.2 索引操作
查看索引:show index from 表名; show create table表名;
删除索引:alter tbale 表名 drop index 索引名; 或drop index 索引名 on表名;
添加索引:alter table 表名 add [index/unique]索引名(列名);
添加主键索引:Alter table 表名 add primary key(列名称);
删除主键索引:Alter table 表名 drop primary key;
六、事务
引擎Myisam是不支持事务的,innodb支持
事物开启:Start transaction;
事物结束: commit;
撤销:rollbacks;
七、子查询
三种子查询 :where子查询、from子查询、exists子查询
7.1 where子查询
先执行where后的语句,得到内层结果。再执行外层
语法:select 字段 from 表 where 内层结果
select goods_name,goods_id from goods where goods_id =(select max(goods_id) from goods);
7.2 from子查询
先执行from后的语句,得到内层结果。再执行外层
语法:select 字段 from (from子查询)
select tmp.article_id,tmp.article_content,article_comments from ( select * from article order by articlecategory_id,article_comments desc ) as tmp group by tmp.articlecategory_id;
7.3 exists子查询
将主查询的数据,放到子查询中做条件验证,根据验证结果(TRUE 或 FALSE)来决定主查询的数据结果是否得以保留。
语法:SELECT … FROM table WHERE EXISTS (subquery)
SELECT * FROM article WHERE EXISTS (SELECT * FROM user WHERE article.uid = user.uid)
八、连接查询
通常在项目中对表的查询都是关联多张表,多表查询就涉及到sql的内连接、外连接和交叉连接查询等。
8.1 内连接查询
在表中存在至少一个匹配时,INNER JOIN 关键字返回行。
SELECTcolumn_name(s)FROMtable_name1INNER JOINtable_name2ON table_name1.column_name=table_name2.column_name
8.2 外连接查询
外连接(全部并集)mysql不支持,oricle,sql server都可以。
外连接:外连接分为两种,一种是左连接(Left JOIN)、右连接(Right JOIN)、完整外部联接(FULL JOIN)。
select goods_name,goods.cat_id,cat_name,shop_price from goods left join category on goods.cat_id =category.cat_id;
8.3 交叉联接查询
交叉联接返回左表中的所有行,左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。
FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定;但是,用左或右向外联接指定表或视图时,表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息,请参见使用外联接。
九、联合查询
9.1 union查询
语法:sql1 union all sql2;
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。
SELECT column_name(s) FROMtable_name1UNION
SELECT column_name(s) FROM table_name2
注释:默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。
9.2 UNION ALL查询
SELECT column_name(s) FROMtable_name1UNION ALL
SELECT column_name(s) FROM table_name2
另外,UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。