- 连接和断开数据库服务器
- 连接数据库服务：shell> mysql -h host -u user -p
- 断开数据库服务：mysql> QUIT
- 输入查询（验证数据库安装正确）
- 查询数据库版本：SELECT VERSION();
- 查询数据库创建时间：SELECT CURRENT_DATE;
- 进行简单数学计算：SELECT (4+1)*5;
- 创建和使用数据库
- 创建数据库：mysql> CREATE DATABASE menagerie;
- 选择menagerie数据库：mysql> USE menagerie
- 列表数据库中数据表：mysql> SHOW TABLES;
- 创建数据表：mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
- 查看数据表详细结构：mysql> DESCRIBE pet;
- 插入数据：INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
- 查询数据：
- 查询所有数据：SELECT * FROM pet;
- 清空数据：DELETE FROM pet;
- 修改/更新数据：UPDATE pet SET birth = '1989-08-31' WHWER name = 'name';
- 查询特定行列值相等：SELECT * FROM pet WHERE name = 'Bowser';
- 查询列值在某个范围：SELECT * FROM pet WHERE birth >= '1998-1-1';
- 逻辑与查询and：SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
- 逻辑或查询or：SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
- 将查询结果去重：SELECT DISTINCT owner FROM pet;
- 根据条件升序表数据排序：SELECT name,birth FROM pet WHERE name='' ORDER BY birth;
- 根据条件降序表数据排序：SELECT name,birth FROM pet WHERE name='' ORDER BY birth DESC;
- 多条件表数据排序：SELECT name,species,birth FROM pet ORDER BY species,birth DESC;
- 日期计算使用特定函数：SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;
- 将计算后结果排序：SELECT name, birth, CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY name;
- 取得日期中的月份（ YEAR(), MONTH(), DAYOFMONTH(). MONTH()）：SELECT name, birth, CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY age;
- 取得日期中月份是特定的值：SELECT name, birth, death,TIMESTAMPDIFF(YEAR,birth,death) AS age FROM pet WHERE death IS NOT NULL ORDER BY age;
- 查看常用的日期时间函数：SELECT name, birth, MONTH(birth) FROM pet;
- 使用NULL值（数据库的查询中一定不要使用NULL，因为NULL会引起全表扫描。导致性能下降很多。）：SELECT 1 IS NULL, 1 IS NOT NULL;SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
- 模式匹配（模糊查询）---》高级匹配方式：正则表达式：查找以b开头的名称：SELECT * FROM pet WHERE name LIKE 'b%';
- 模式匹配（模糊查询）---》高级匹配方式：正则表达式：查找以结尾的名称fy：SELECT * FROM pet WHERE name LIKE '%fy';
- 模式匹配（模糊查询）---》高级匹配方式：正则表达式：查找包含名称wSELECT * FROM pet WHERE name LIKE '%w%';
- 模式匹配（模糊查询）---》高级匹配方式：正则表达式：查找包含五个字符的名称，请使用_模式字符的五个实例：SELECT * FROM pet WHERE name LIKE '_____';
- 计数总记录数：SELECT COUNT(*) FROM pet;
- 条件查询记录数：SELECT COUNT(*) FROM pet WHERE name=?;
- 分组计数记录数计数：SELECT species, COUNT(*) FROM pet GROUP BY species;
- 多条件分组记录计数：SELECT species, sex, COUNT(*) FROM pet WHERE species = 'dog' OR species = 'cat' GROUP BY species, sex;
- 多表查询-子查询：
- 多表查询-联合查询：1.SELECT pet.name, TIMESTAMPDIFF(YEAR,birth,date) AS age, remarkFROM pet INNER JOIN event ON pet.name = event.name WHERE event.type = 'litter'; 2.SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species FROM pet AS p1 INNER JOIN pet AS p2ON p1.species = p2.species AND p1.sex = 'f' AND p1.death IS NULL AND p2.sex = 'm' AND p2.death IS NULL;
- 获取数据库和表的信息
- 查找当前使用的数据库：SELECT DATABASE();
- 列表数据库中的数据表：SHOW TABLES;
- 查找数据表的结构：DESCRIBE pet;（简写：DESC pet）
- 常见查询示例
- 查询表中某列最大值：SELECT MAX(article) AS article FROM shop;
- 查询表中某列最大值，并取得当前行所有数据：子查询方式-SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop);联合查询方式-SELECT s1.article，s1.dealer，s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.price <s2.price WHERE s2.article IS NULL;
- 查询分组后，每组数据的最大值： SELECT article，MAX（price）AS price FROM shop GROUP BY article ORDER BY article;
- 使用用户定义的变量：SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
- 使用AUTO_INCREMENT（自动增长列）：CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id));
