MySQL 基本操作
1.连接数据库
查看可用数据库使用show命令:
SHOW DATABASES;
连接数据库使用use命令:
USE test;
获取数据库内的所有表:
SHOW TABLES;
查看表结构:
SHOW COLUMNS FROM test;
2.检索数据
检索某一列或某几列:
SELECT name FROM products;
检索所有列:
SELECT * FROM products;
如果只想显示一列中的不同数值,可以用distinct关键字:
SELECT DISTINCT name FROM products;
限定结果的数量,用LIMIT关键字:
SELECT DISTINCT name FROM products LIMIT 5; SELECT DISTINCT name FROM products LIMIT 0, 5; 从第1行开始的5个(从0开始计数)
3.排序
排序使用order by子句:
SELECT name, id, price FROM products ORDER BY name, id; SELECT name, id, price FROM products ORDER BY name, id DESC; 默认是升序排列,加上DESC设置为对id降序排列
4.过滤
过滤数据使用where子句:
SELECT id FROM products WHERE id = 2;
也可以过滤一个范围:
SELECT name, price FROM products WHERE price BETWEEN 5 AND 10;
寻找值存在的记录:
SELECT id FROM products WHERE name IS NULL;
也可以使用多重where子句,中间用and或者or相连。
如果要查询的记录有一个明确的范围,则可以用in关键字或者not in关键字。
SELECT name FROM products WHERE id IN (1, 2, 3); SELECT name FROM products WHERE id NOT IN (1, 2, 3);
5.通配符和正则表达式
使用LIKE关键字来进行通配符匹配。%表示多个字符,_表示单个字符。
SELECT id, name FROM products WHERE name LIKE 's%e'; 查找以s开头、e结尾的name记录 SELECT id, name FROM products WHERE name LIKE '_s'; 查找两个单词且后一个字母是s的记录
使用REGEXP关键字来进行正则匹配。
SELECT id, name FROM products WHERE name REGEXP '.00'; 输出所有包含00的记录
'1|2'表示匹配1或2,[123]表示匹配1、2、3之一,[1-9]表示匹配1-9之一,.表示匹配一个字符,*表示0个或多个匹配,+表示1个或多个匹配,?表示0个或1个匹配。
{n}表示匹配n个字符,{n,}表示匹配n个以上字符,{n,m}表示匹配n-m个字符。
\\表示转义符。
^表示文本的开始,$表示文本的结尾。而[^1-3]表示匹配1-3以外的字符。
通配符匹配整个字段,而正则表达式匹配字段中的一部分。
6.拼接
拼接将两个字段的内容组合起来,用concat方法:
SELECT Concat(name, '(', country, ')') AS title FROM product;
也可以对两个字段的值进行计算:
SELECT price, number, price*number AS expense FROM products;
7.函数
常用函数包括Date()、Time()、Length()、LTrim()、RTrim()、Upper()、Lower()、AVG()、SUM()、COUNT()、MIN()、MAX()等。
SELECT COUNT(*) AS num, MIN(price) AS min_price, MAX(price) AS max_price, AVG(price) AS avg_price FROM products;
8.分组
分组用group by方法实现。group by应位于where之后,order by之前。
SELECT id, COUNT(*) AS num FROM products GROUP BY id;
对于数据行,用where进行过滤,而对于分组则用having进行过滤,即where在数据分组前进行过滤,而having在数据分组后进行过滤:
SELECT id, COUNT(*) AS num FROM products GROUP BY id HAVING COUNT(*) > 10;
SELECT id, SUM(num*price) AS expense FROM products WHERE id > 10 GROUP BY id HAVING SUM(num*price) > 50 ORDER BY expense;
9.子查询
将先后进行的两个查询写在一起:
SELECT id FROM customers WHERE num IN (SELECT num FROM products WHERE price = 10);
或者将子查询作为一个新字段:
SELECT name, id, (SELECT COUNT(*) FROM products WHERE products.id = customers.id) AS product FROM customers;
10.联结表
即将两张表进行联结查询。联结查询的两张表一定要具有联结关系,否则输出的结果为两张表的笛卡尔积:
SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name;
也可以写作:
SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;
上述联结方式称为内部联结。此外,还有自联结和外部联结。
自联结即在select语句中引用自身不止一次。如下所示:
SELECT prod_id, prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 5);
上面为子查询,找出所有prod_id为5的商品的供应商id,再查找这一供应商的商品id和name。写成联结查询如下:
SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 5;
11.组合查询
使用union将两个或以上select查询语句联合起来。两条select语句查询的列必须相同:
SELECT id, name, price FROM products WHERE price >= 5 UNION SELECT id, name, price FROM products WHERE id > 5;
12.数据插入
数据插入使用insert into方法。如果插入的数据按照列的次序排列,也可以省略(id, name, price),但最好写出:
INSERT INTO products (id, name, price) VALUES (9, 'new', 300);
如需插入多条,直接在(9, 'new', 300)后面加上新的条目,用逗号隔开。
也可以将select查询出的数据插入。
INSERT INTO products (id, name, price) SELECT id, name, price FROM newproducts;
13.更新和删除
更新用update方法:
UPDATE products SET price = 100 WHERE id = 8;
也可以用update删除某一条目的部分数据:
UPDATE products SET name = NULL WHERE id = 6;
删除某条数据用delete方法:
DELETE FROM products WHERE id = 7;
想删除整张表,可以用trucate方法:
TRUNCATE TABLE test;
14.创建表
CREATE TABLE products( id int NOT NULL AUTO_INCREMENT, name char(50) NOT NULL, price float NOT NULL, PRIMARY KEY (id) )ENGINE = InnoDB;
表中主键的值必须唯一,但是主键可以由多列共同确定。
可以设置默认值:DEFAULT 0之类。
引擎主要有InnoDB、MyISAM、MEMORY三种。InnoDB支持事务,不支持全文本搜索;MyISAM支持全文本搜索,不支持事务;MEMORY与MyISAM相同,但是数据存储于内存中,用于临时表。
15.更新和删除表
更新表结构用alter方法:
ALTER TABLE products ADD vend_id int; ALTER TABLE products DROP COLUMN vend_id;
也可以用alter方法更新外键:
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (prod_id) REFERENCES vendors (prod_id);
删除表用drop方法:
DROP TABLE products;
重命名用rename方法:
RENAME TABLE products TO newproducts;