一些概念
- 数据库是许多表的集合,而数据库服务器可以容纳许多这样的数据库
- 数据库和表称为数据库对象。任何操作(如创建，修改或删除数据库对象)的都称为数据定义语言(DDL)操作
- 数据按照某种蓝图组织构建数据库(分为数据库和表)，这种数据的组织形式被称为schema
-
连接到Mysql 服务器
- mysql -u root -p 或者 mysql -u root -p company (直接连接到你想要连接的数据库)
-
基本常用操作
- 创建一个数据库 =====> create database company;
- create database ` my.contacts `； // 如果名字中有特殊符号就要用反标记字符 /``
- 要查找已经连接到了那个数据库用 ====> select database();
- 切换数据库======> use dbname;
- 查找你有权访问的所有数据库 =====> show databases;
- 获取数据库存放的目录 ======> show variables like 'datadir';
- 显示表结构======> describe 表名; 或者 DESC 表名;
- 删除指定的表======> drop table users;
- 删除指定的数据库======> drop database users;
-
创建表
- 数据类型
- 1.数字 TINYINT、 SMALLINT、 MEDIUMINT、 INT、 BIGINT、 BIT
- 2.浮点数 DECIMAL、 FLOAT、 DOUBLE
- 3.字符串 CHAR、 VARCHAR、 BINARY、 VARBINARY、 BLOB、 TEXT、 ENUM、 SET
- 4.Spatial
- 5.JSON
- 数据类型
// 创建表在 company 数据库中创建了customers表;
CREATE TABLE IF NOT EXISTS `company`.`customers`（
`id` int unsigned AUTO_INCREMENT PRIMARY_KEY,
`firstname` varchar(20),
`lastname` varchar(20),
`country` varchar(20)
) ENGINE=InnoDB;
-
查看表结构
- SHOW CREATE TABLE 表名; 或者 DESC 表名;
-
克隆表结构
- 你可以将一个表的结构克隆到新表中 不克隆记录：
- 命令: CREATE TABLE new_customers LIKE customers;
-
插入
- INSERT语句用于在表中创建新记录：
INSERT IGNORE INTO `company`．`customers`（first_name,last_name,country) VALUES ('Mike','Christensen','USA'), ('Andy','Hollands','Australia'), ('Ravi','Vedantam','India'), ('Ra]iv','Perera','sri Lanka') IGNORE ：如果该行已经存在，并给出了IGNORE子句，则新数据将被忽略，INSERT语句仍然会执行成功，同时生成一个警告和重复数据的数目。反之，如果未给出IGNORE子句，则INSERT语句会生成一条错误信息。行的唯一性由主键标识： -
更新
- UPDATE 语句用于修改表中的现有记录：
- UPDATE customers SET firstname＝'Rajiv', country＝'UK' WHERE id=4;
WHERE ：这是用于过滤的子句。在WHERE子句后指定的任何条件都会用于过滤，被筛选出来的行都会被更新 WHERE子句是强制性的。如果没有给出它，UPDATE会更新整个表。I建议在事务中修改数据，以便在发现任何错误时轻松地回滚这些更改 -
删除
DELETE FROM customers WHERE id=4 AND first_name＝'Rajiv'; WHERE子句是强制性的。如果没有给出它，DELETE将删除表中的所有行。建议在事务中修改数据，以便在发现任何错误时轻松地回滚更改 -
REPLACE 重复项处理
- REPLACE INTO customers VALUES (1，'Mike'，'Christensen'，'America');
-
INSERT ON DUPLICATE KEY UPDATE
将更新该行并将此付款金额添加到以 前的金额中(如每次支付后的金额和之前的金额进行累加) INSERT INTO new_kewei VALUES (7,120007,'Rav',1,"value值3",4) ON DUPLICATE KEY UPDATE sort_order=sort_order+50; -
删除整个表记录 保留表结构
- TRUNCATE TABLE 表名;
加载示例数据
- MySQL提供了一个示例employee数据库和大量数据供我们学习使用
- 下载 demo 数据库 https://codeload.github.com/datacharmer/test_db/zip/master
- 登录后导入数据库=====> source改绝对目录
1.下载压缩文件
wget 'https://codeload.github.com/datacharmer/test_db/zip/master' -O master.zip
2. 解压缩文件
3. 加载数据
cd test_db-master
修改employees.sql 内的 source 路径,改成绝对路径;
导入数据库 或者 表:
1. 登录成功后
2. 打开要运行的 *.sql 文件 如果里面没有创建数据库的命令，则我们要选择一个数据库再执行导入
3. 文件路径使用绝对路径,使用右斜杠;
source D:/Z_Kit/test_db-master/employees.sql
查询数据
-
所有列======> select * from dept_manager;
-
选择列======> select emp_no,column1,column2 from dept_manager;
-
计数 =======> SELECT COUNT(*) FROM employees; // 统计 employees 中有多少条数据;
-
条件过滤 ======>
// 从nideshop_attribute_category表中回去 name为灯具的那条数据的id; SELECT id FROM nideshop_attribute_category WHERE name='灯具'; 所有的过滤条件都是通过WHERE子句给出的，除整型数和浮点数之外，其他所有内容都应放在引号内 -
操作符
-
equality 表示等号;
-
IN 检查一个值是存在一组值中
SELECT COUNT(*) FROM employees WHERE last_name IN ('Christ','Lambat','Baba'); -
BETWEEN ... AND 检查一个值是否在一个范围内。
SELECT COUNT(*) FROM employees WHERE bhire_date BETWEEN '1986-12-0-1' AND '1986-12-31' -
NOT 你可以简单地用NOT运算符来否定结果
// 例如，找出不是在1986年12月人职的员工的人数： SELECT COUNT（*）FROM employees WHERE hire_date NOT BETWEEN '1986-12-0-1' AND '1986-12-31' -
-
简单模式匹配
- 可以使用LIKE运算符来实现简单模式匹配。使用下画线（_）来精准匹配一个字符，使用（%）来匹配任意数量的字符
// 找出名字以Christ开头的所有员工的人数 SELECT COUNT(*) FROM employees WHERE first_name LIKE 'christ%'; // 找出名字以Christ开头并以ed结尾的所有员工的人数： SELECT COUNT (*) FROM employees WHERE first_name LIKE 'christ%ed'; // 找出名字中包含sri工的所有员工的人数： SELECT COUNT(*) FROM employees WHERE first_name LIKE '%sri%'; // 找到名字以er结尾的所有员工的人数: SELECT COUNT(*) FROM employees WHERE first_name LIKE '%er'; // 找出名字以任意两个字符开头、后面跟随ka、再后面跟随任意数量字符的所有员工的人数 SELECT COUNT(*) FROM employees WHERE first_name LIKE '__ka%';
正则表达式
| 表达式 | 描述 | column2 |
|---|---|---|
| * | 零次或多次重复 | |
| + | 一个或多个重复 | |
| ？ | 可选字符 | |
| . | 任何字符 | |
| . | 区间 | |
| ^ | 以....开始 | |
| $ | 以....结束 | |
| [abc] | 只有a、b或c | |
| [^abc] | 非a, 非b,非c | |
| [a-z] | 字符a到z | |
| [0-9] | 数字0到9 | |
| ^...$ | 开始和结束 | |
| d | 任何数字 | |
| D | 任何非数字字符 | |
| s | 任何空格 | |
| S | 任何非空白字符 | |
| w | 任何字母数字字符 | |
| W | 任何非字母数字字符 | |
| {m} | m次重复 | |
| {m,n} | m到n次重复 |
// 找出名字以Christ开头的所有员工的人数
SELECT COUNT(*) FROM employees WHERE first_name RLIKE '^christ'
// 找出姓氏以ba结尾的所有员工的人数
SELECT COUNT(*) FROM employees WHERE last_name REGEXP 'ba$';
// 查找姓氏不包含元音（a、e、 1 、o和u）的所有员工的人数
SELECT COUNT(*) FROM employees WHERE last_name NOT REGEXP '[aeiou]';
-
限定结果
// 查询 hire_date 在1986年之前的任何10名员工的姓名。可以在查询语句末尾使用LIMIT子句来实现此查询 SELECT first_name,last name FROM employees WHERE hire_date < '1986-0l-01' LIMIT 10; -
使用表别名
默认情况下，SELECT子句中给出的任何列都将显示在结果中。在前面的示例中，我们已经得出了统计数值，但它显示为COUNT()。你可以使用AS别名来更改COUNT();
SELECT COUNT(*) AS count FROM employees WHERE hire_date BETWEEN '1986-12-01' AND '1986-12-31';
对结果排序
可以根据列或别名列对结果进行排序，也可以用DESC指定按降序或用ASC指定按升序来排序。默认情况下，排序将按照升序进行。你可以将LIMIT子句与ORDERBY结合使用以限定结果集
1. 查找薪水最高的前5名员工的员工编号 和 薪水
SELECT emp_no,salary FROM salaries ORDER BY salary DESC LIMIT 5;
2. 你可以在SELECT语句中提及列的位置，而不是指定列名称。例如，你想、对位于第二列的工资进行排序，那么可以指定ORDERBY 2 :
SELECT emp_no,salary FROM salaries ORDER BY 2 DESC LIMIT 5;
对结果进行分组
你可以在列上使用GROUP BY子句对结果进行分组，然后使用AGGREGATE（聚合）函数，例如COUNT、MAX、MIN和AVERAGE。还可以在group by子句中的列上使用函数。请参阅下面的SUM示例，其中用到了YEAR（）函数
COUNT 数量
1. 分别找出男性和女性员工的人数：
SELECT gender , COUNT(*) AS count FROM employees GROUP BY gender;
2. 如果你希望查找员工名字中最常见的10个名字，可以使用GROUPBY first_name 对所有名字分组，然后使用COUNT(first_name）在各组内计数，最后使用ORDER BY计数对结果进行排序并将返回结果行数限制为前10行：
SELECT first_name, COUNT(first_name) AS count FROM employees GROUP BY first_name ORDER BY count DESC LIMIT 10;
SUM 求和
查找每年给予员工的薪水总额并按薪水高低对结果进行排序。YEAR（）函数将返回给定日期所在的年份
SELECT YEAR(from_date), SUM(salary) AS sum FROM salaries GROUP BY YEAR(from_date) ORDER BY sum DESC;
AVERAGE 平均
查找平均工资最高的10名员工
SELECT emp_no, AVG(salary) AS avg FROM salaries GROUP BY emp_no ORDER BY avg DESC LIMIT 10;
DISTINCT （distinct）
可以使用DISTINCT子句过滤出表中的不同条目：表示只显示 title 列中不同的条目;
SELECT DISTINCT title FROM titles;
使用HAVING过滤
可以通过添加HAVING子句来过滤GROUPBY子句的结果
找到平均工资超过140,000美元的员工
SELECT emp_no, AVG(salary) AS avg FROM salaries GROUP BY emp_no HAVING avg > 140000 ORDER BY avg DESC;