MySql: Basic Query

Grammar:
What SELECT wants to query
 [FROM table name];

Similar to Java: System. out. println (something to print);
Characteristic:
(1) The result of the query through select is a virtual table, not a real one.
(2) What you want to query can be a constant value, an expression, a field, or a function.

Create an employees table

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `employee_id` int(6) NOT NULL AUTO_INCREMENT COMMENT 'Employee Number',
  `first_name` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'name',
  `last_name` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'surname',
  `email` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'mailbox',
  `phone_number` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Phone number',
  `job_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Type Number',
  `salary` double(10, 2) NULL DEFAULT NULL COMMENT 'A monthly salary',
  `commission_pet` double(4, 2) NULL DEFAULT NULL COMMENT 'Bonus rate',
  `manager_id` int(6) NULL DEFAULT NULL COMMENT 'Manager Number',
  `department_id` int(4) NULL DEFAULT NULL COMMENT 'Department Number',
  `hiredate` datetime(0) NULL DEFAULT NULL COMMENT 'Date of entry',
  PRIMARY KEY (`employee_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

1 Single field (last name) in the query table

select last_name FROM employees;

2 Multiple fields in the query table (surname, salary, mailbox)

SELECT last_name,salary,email FROM employees;

3. All fields in the query table

SELECT * FROM employees;

4. Query Constants

SELECT 'Chen'
SELECT 'yellow'

5. Query expression

SELECT 100*98

6. Query function

SELECT VERSION()

7. Aliases

/*
a. Easy to understand
b. If the field to be queried has a duplicate name, the use of aliases can be distinguished.
*/

Mode 1: Use AS

SELECT 98*189 AS 'Operational results';
SELECT first_name AS 'surname',last_name AS 'name' FROM employees;

Mode 2: Use spaces

select first_name 'surname',last_name 'name' FROM employees;

8. Weight removal

Case study: Query all department numbers involved in the employee table

SELECT DISTINCT department_id FROM employees;

The role of 9. +

/*
The + number in java:
a. Both operands are numeric
b. Connector: As long as one operand is a string

The + sign in mysql has only one function: the operator
SELECT 10 + 20; if both operands are numeric, add

SELECT'123'+90: One of them is character type, trying to convert character type numeric value into numeric value, and calculating two operands.

SELECT'jack'+ 100: Convert character values to 0 if conversion fails

SELECT null + 0: As long as one of the results is null, the result must be null.

*/

10CONCAT function

SELECT CONCAT('huang','chen','name') AS huangchen;

Case: Query the employee name and name to connect into a field and display the name

SELECT CONCAT(first_name,last_name) AS 'Full name' FROM employees;