摘要:SQL是目前使用最为广泛的数据库语言之一。这里,我总结了在数据库上,用SQL语言对数据排序、过滤和分组,以及表、视图、联结、子查询、游标、存储过程和触发器等内容。
SQL是目前使用最为广泛的数据库语言之一。这里,我总结了在数据库上,用SQL语言对数据排序、过滤和分组,以及表、视图、联结、子查询、游标、存储过程和触发器等内容。
1、数据库相关
查所有数据库 show databases; 创建数据库 create database 数据库名; 查看数据库 show create database 数据库名; //显示当初创建这个库的时候使用什么样的sql语句 创建数据库指定字符集 create database 数据库名 character set utf8/gbk 删除数据库 drop database 数据库名; 使用数据库 use 数据库名;
2、表相关
创建表 create table 表名(id int,name varchar(10)); //表名区分大小写 查看所有表 show tables; 查看单个表属性 show create table 表名; //使用的什么创建语句,可以在后面加\G使描述更清晰 查看表字段 desc 表名; 创建表指定引擎和字符集 create table 表名(id int,name varchar(10)) engine=myisam/innodb charset=utf8/gbk; 删除表 drop table [if exists] 表名;删除表(可选择添加是否存在则删除)
DROP TABLE IF EXISTS `abc`; CREATE TABLE `abc` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT comment'商品名称', `name` char(80) NOT NULL DEFAULT '' comment'商品名称', `title` char(20) NOT NULL DEFAULT '' comment'商品名称', `type` tinyint(1) NOT NULL DEFAULT '1' comment'商品名称', `condition` char(100) NOT NULL DEFAULT '' comment'商品名称', `show` bit DEFAULT 1 comment '是否可见', `price` decimal(5,2) not null comment '价格', `status` enum('0', '1', '2') NOT NULL DEFAULT '0' comment '状态', PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=INNODB DEFAULT CHARSET=utf8;
创建数据库
CREATE DATABASE IF NOT EXISTS my_db default charset utf8 COLLATE utf8_general_ci;
3、约束
not null 非空 default 默认约束语句,用于约束对应列中的值的默认值,除非默认值为空值,否则不可插入空值 unique 唯一约束语句,用于约束对应列中的值不能重复,可以有空值,但只能出现一个空值 primary 主键 = 唯一 + 非空 auto_increment 自动增长,用于系统自动生成字段的主键值 foreign key(从表id) reference 主表名(id); 表与表之间建立联系
4、修改表
修改表名 rename table 旧表名 to 新表名; 修改表名 alter table 旧表名 rename 新表名 修改字段数据类型 alter table 表名 modify 字段名 数据类型 修改表属性 alter table 表名 engine=myisam/innodb charset=utf8/gbk; 添加表字段 alter table 表名 add 新字段名 新数据类型 [约束] [first/after 已存在字段名]; 删除表字段 alter table 表名 drop 字段名; 修改表字段名和类型 alter table 表名 change 旧字段名 新字段名 类型; 修改表的类型和位置 alter table 表名 modify 字段名 类型 first/after 已存在字段名; 删除表 drop table 表名; 更改表的存储引擎 alter table 表名 engine = 新的存储引擎; 删除表的外键约束 alter table 表名 drop foreign key 外键名; //删除所有的外键之后,才能删除对应的主键所在的表
5、插入数据
insert into 表名 values(5,‘xiaoming',null); insert into 表名 (字段名1,字段名2…) values (2,‘aa'…); insert into 表名 values(5,‘xiaoming',null),(5,‘xiaoming',null),(5,‘xiaoming',null); insert into 表名 (字段名1,字段名2) values (2,‘aa'),(2,‘aa'),(2,‘aa');
6、查询数据
select * from 表名; select name from 表名; select * from 表名 where id=10;
7、修改数据
update 表名 set 要修改的字段名=100 where 根据字段名=10;
8、删除数据
delete from 表名 where 字段名=10;
9、数据检索
SELECT prod_nameFROM Products; #检索单列 SELECT prod_id, prod_name, prod_priceFROMProducts; #检索多列 SELECT * FROM Products; #检索所有列 SELECT DISTINCTvend_id FROMProducts; #检索不同的值 SELECTprod_name FROM Products LIMIT 5; #返回不超过5行数据 SELECTprod_name FROM Products LIMIT 5 OFFSET 5; #返回从第5行起的5行数据。LIMIT指定返回的行数,LIMIT带的OFFSET指定从哪儿开始。 /* SELECT prod_name, vend_id FROMProducts; */ SELECTprod_name FROMProducts; #多行注释
10、排序检索数据
SELECTprod_name FROMProducts ORDER BYprod_name; #排序数据 SELECT prod_id, prod_price, prod_name FROMProducts ORDER BY prod_price, prod_name; #按多个列排序 SELECT prod_id, prod_price, prod_name FROMProducts ORDER BY 2, 3; #按列位置排序,第三行表示先按prod_price, 再按prod_name进行排序 SELECT prod_id, prod_price, prod_name FROMProducts ORDER BY prod_priceDESC, prod_name; #prod_price列以降序排序,而prod_name列(在每个价格内)仍然按标准的升序排序
11、数据过滤
SELECT prod_name, prod_price FROMProducts WHERE prod_price< 10; #检查单个值 SELECT prod_name, prod_price FROMProducts WHERE vend_id <> ‘DLL01'; #不匹配检查 SELECT prod_name, prod_price FROMProducts WHERE prod_priceBETWEEN 5 AND 10; #范围值检查 SELECT cust_name FROMCUSTOMERS WHERE cust_emailIS NULL; #空值检查
12、高级数据过滤
SELECTprod_id, prod_price, prod_name FROMProducts WHERE vend_id = ‘DLL01'ANDprod_price <= 4; #AND操作符 SELECTprod_name, prod_price FROMProducts WHEREvend_id='DLL01' OR vend_id='BRS01'; #OR操作符 SELECTprod_name, prod_price FROMProducts WHERE (vend_id = 'DLL01'ORvend_id='BRS01') ANDprod_price >= 10; #求值顺序 AND的优先级高于OR SELECTprod_name, prod_price FROMProducts WHERE vend_idIN (‘DLL01','BRS01') ORDER BY prod_name; #IN操作符 SELECT prod_name FROMProducts WHERE NOTvend_id = ‘DLL01' ORDER BY prod_name; #NOT 操作符 SELECT prod_name FROMProducts WHEREvend_id <> ‘DLL01' ORDER BY prod_name; #NOT 操作符
13、通配符过滤数据
SELECT prod_id, prod_name FROMProducts WHERE prod_nameLIKE ‘Fish%'; #%表示任何字符出现任意次数,找出所有以词Fish起头的产品 SELECT prod_id, prod_name FROMProducts WHERE prod_nameLIKE ‘%bean bag%'; #‘%bean bag%'表示匹配任何位置上包含文本bean bag的值,不论它在之前或之后出现什么字符 SELECT prod_name FROMProducts WHERE prod_nameLIKE ‘F%y'; #找出以F起头,以y结尾的所有产品