mysql(一)

一些概念

数据库是许多表的集合,而数据库服务器可以容纳许多这样的数据库
数据库和表称为数据库对象。任何操作(如创建，修改或删除数据库对象)的都称为数据定义语言(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到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;

posted @ 2020-10-08 22:07 kgwei 阅读(117) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

大道至简，知易行难

卑微的活在这个世界上，只为那个为伟大的梦想

mysql(一)

一些概念

加载示例数据

查询数据

正则表达式

对结果排序

对结果进行分组