MySql基础总结

1、创建一个表

CREATE TABLE customers
(
cust_id INT NOT NULL AUTO_INCREMENT,
cust_name CHAR(50) NOT NULL ,
cust_address CHAR(50) NULL ,
cust_city CHAR(50) NULL ,
cust_state CHAR(5) NULL ,
cust_zip CHAR(10) NULL ,
cust_country CHAR(50) NULL ,
cust_contact CHAR(50) NULL ,
cust_email CHAR(255) NULL ,
PRIMARY KEY (cust_id)
) ENGINE=INNODB;

其中,AUTO_INCREMENT表示该列是自动增长的,char存放的是固定长度的字符串,CHAR(50)表示存放的字符长度为50,PRIMARY KEY (cust_id)表示将cust_id设置成为主键。ENGINE=InnoDB,表示将存储引擎设为InnoDB。

关于InnoDB:

InnoDB 是 MySQL 上第一个提供外键约束的数据存储引擎,除了提供事务处理外,InnoDB 还支持行锁,提供和 Oracle 一样的一致性的不加锁读取,能增加并发读的用户数量并提高性能,不会增加锁的数量。InnoDB 的设计目标是处理大容量数据时最大化性能,它的 CPU 利用率是其他所有基于磁盘的关系数据库引擎中最有效率的。

InnoDB 是一套放在 MySQL 后台的完整数据库系统,InnoDB 有它自己的缓冲池,能缓冲数据和索引,InnoDB 还把数据和索引存放在表空间里面,可能包含好几个文件,这和 MyISAM 表完全不同,在 MyISAM 中,表被存放在单独的文件中,InnoDB 表的大小只受限于操作系统文件的大小,一般为 2GB。

同理可创建orderitems表(存放每个订单中的实际物品)、orders表(存放客户订单)、products表(存放产品目录,每行表示一个产品)、vendors表(存放生产产品的供应商)

CREATE TABLE orderitems
(
order_num INT NOT NULL ,
order_item INT NOT NULL ,
prod_id CHAR(10) NOT NULL ,
quantity INT NOT NULL ,
item_price DECIMAL(8,2) NOT NULL ,
PRIMARY KEY (order_num, order_item)
) ENGINE=INNODB; CREATE TABLE orders
(
order_num INT NOT NULL AUTO_INCREMENT,
order_date DATETIME NOT NULL ,
cust_id INT NOT NULL ,
PRIMARY KEY (order_num)
) ENGINE=INNODB; CREATE TABLE products
(
prod_id CHAR(10) NOT NULL,
vend_id INT NOT NULL ,
prod_name CHAR(255) NOT NULL ,
prod_price DECIMAL(8,2) NOT NULL ,
prod_desc TEXT NULL ,
PRIMARY KEY(prod_id)
) ENGINE=INNODB; CREATE TABLE vendors
(
vend_id INT NOT NULL AUTO_INCREMENT,
vend_name CHAR(50) NOT NULL ,
vend_address CHAR(50) NULL ,
vend_city CHAR(50) NULL ,
vend_state CHAR(5) NULL ,
vend_zip CHAR(10) NULL ,
vend_country CHAR(50) NULL ,
PRIMARY KEY (vend_id)
) ENGINE=INNODB;

2、添加数据到customers表中,这里采用列=值的方式,这样可以保证当表中列的顺序改变时,插入数据也不会受到影响。

INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');

同理,可以给orderitems表、orders表、products表、vendors表插入数据。

3、显示所有数据库

SHOW DATABASES;

4、检索

(1)检索单列

如从products表中检索出一个名为prod_name的列。

SELECT prod_name FROM products;

输出:

(2)检索多列

SELECT prod_id,prod_name,prod_price FROM products;

输出

(3)检索所有列

SELECT * FROM products;

(4)排序检索

SELECT prod_name FROM products ORDER BY prod_name;

注意事项:

a.     在使用ORDER BY字句时,应保证它是SELECT语句的最后一条字句

b.     可以通过非选择的列来进行排序,如

SELECT prod_id FROM products ORDERBY prod_name;

Prod_id是选择的列,prod_name是非选择的列。

c.      可以按多个列进行排序,如

SELECTprod_id,prod_price,prod_name FROM products ORDER BY prod_price,prod_name;

d.     可以按照列的相对位置进行排序

SELECTprod_id,prod_price,prod_name FROM products ORDER BY 2,3;

此时,排序只能按照选择的列进行排序,而且增加了错用列名进行排序的可能性。

e.     默认排序为升序排序,降序排序需要指定DESC关键字,放在所选择的列后面。

SELECT prod_name FROM productsORDER BY prod_name DESC;

如果需要在多个列上进行降序排序,必须对每个列指定DESC。

(5) 过滤检索

a. 单条件过滤

WHERE通过特定的搜索条件对数据进行过滤,筛选出特定的行。

SELECT prod_name,prod_price FROM products WHERE prod_price < 3.49;

WHERE字句支持=, <>, !=, <, <=, >, >=,BETWEEN,IS NULL操作

注意,在WHERE字句中使用字符串进行过滤时,要用单引号限定字符串。如:

SELECT vend_id,prod_name FROM products WHERE prod_id != 'FB';
SELECT vend_id,prod_name,prod_id FROM products WHERE prod_id != 'FB';

b. 多条件过滤(组合过滤)

检索出满足所有条件的行,各条件用AND连接,等同于&&

SELECT prod_id,prod_price,prod_name,vend_id FROM products WHERE vend_id = 1001 AND prod_price < 10;

输出:

检索满足任一条件的行,各条件之间用OR来连接,等同于||

SELECT prod_id,prod_price,prod_name,vend_id FROM products WHERE vend_id = 1001 OR prod_price < 10;

注意:当同时使用AND和OR语句时,应当使用括号来规定计算次序,因为默认AND的优先级比OR高,SQL会先处理AND语句。如检索出价格在10美元以下的,由厂商1001或1002生产的所有产品。

SELECT prod_id,prod_price,prod_name,vend_id FROM products 
WHERE vend_id = 1001 OR vend_id = 1002 AND prod_price < 10;

输出:

在该结果中,出现了价格在10美元以上的产品,这是因为在没加括号的情况下,SQL先处理了AND导致的。更改为

SELECT prod_id,prod_price,prod_name,vend_id FROM products 
WHERE (vend_id = 1001 OR vend_id = 1002) AND prod_price < 10;

即可得到正确的结果

另一种多条件过滤-操作符IN(功能相当于OR),查询出符合IN中任一条件的数据。

SELECT prod_id,prod_price,prod_name,vend_id FROM products WHERE vend_id IN(1001,1002);

优点:

IN操作符一般比OR执行更快。

IN可以包含其他SELECT语句,使得能够更动态地创建WHERE字句。

否定查询-NOT

NOT可放在列名的前面,也可以放在列名的后面。如下:

SELECT prod_id,prod_price,prod_name,vend_id FROM products WHERE vend_id NOT IN(1001,1002);
SELECT prod_id,prod_price,prod_name,vend_id FROM products WHERE NOT vend_id IN(1001,1002);

使用LIKE进行模糊查询

“%”代表任意字符,”_”代表单个字符。

SELECT prod_id,prod_price,prod_name,vend_id FROM products WHERE prod_id LIKE 'F%';

SELECT prod_id,prod_price,prod_name,vend_id FROM products WHERE prod_id LIKE 'F_';

通配符搜索处理时间较长,所以能用其他操作符达到相同的目的,则不建议使用通配符。

去重复查询DISTINCT

SELECT DISTINCT order_num FROM orderitems;

(7) 计算字段查询

1、拼接:将多个值联结到一起构成单个值

如将vend_name和vend_country拼接。

SELECT CONCAT(vend_name,' (',vend_country,')') FROM vendors ORDER BY vend_name;

使用别名AS

SELECT CONCAT(vend_name,' (',vend_country,')') AS vend_title FROM vendors ORDER BY vend_name;

使用别名的好处:1)给计算字段赋予别名,可以让客户机方便使用

2)使得列的含义更容易理解

(8) 使用函数进行查询

文本处理函数:

Left(str,len);

str为一个字符串,len表示从左边开始截取,截取字符串的长度。

Right(str,len)

同left(str,len),只是从右边开始截取。

SUBSTRING(str,pos);

等同于SUBSTRING(str FROM pos),str为一个字符串,pos为从哪个位置开始截取,直到末尾,如果pos为负数,则表示从倒数开始截取。

SUBSTRING(str,pos,len);

等同于SUBSTRING(str FROM pos FOR len)表示从pos开始截取,截取长度为len。

SUBSTRING_INDEX(str,delim,count);

delim为标识字符,若count为正数,表示截取第count个标识符之前的字符串,若count为负数,表示截取倒数第count个字符之后的字符串。

Length();计算字符串长度

Lower();大写转小写

SOUNDEX();返回串的发音比较像的串。

如输入:

SELECT vend_name,UPPER(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

聚集函数:

AVG(),COUNT(),MAX(),MIN(),SUM()

如计算所有产品的价格的平均值:

SELECT AVG(prod_price) AS avg_price FROM products;

(9)分组查询GROUP BY

查询按某个字段分组后,各组的数量,如

SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id;

查询出了产品中,每个厂商生产的产品数量。

输出:

与WITH ROLLUP一起使用

SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP;-- 最后加入一个总和行

与HAVING一起使用,过滤分组

WHERE过滤行,HAVING过滤分组,如查询订单数大于等于2的顾客。

SELECT cust_id,COUNT(*) AS '订单数' FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;

注意:1) Group by字句可以包含任意数目的列

2) 如果在Group by中使用了嵌套分组,则数据将在最后规定的分组上进行汇总

3) Group by字句中列出的列必须是select中的列或者是有效表达式,但不能是聚集函数。

4) Select语句中的每个列都必须在group by字句中给出

5) 如果分组列中具有null值,则null将作为一个分组返回,如果列中有多行null值,它们将被分为一组。

6) Group by字句必须出现在where字句之后,order by字句之前

上一篇:html取地址栏参数的方法


下一篇:Linux命令之chown