MySQL启动和常用命令（windows）

管理员权限打开CMD

打开：net start mysql
关闭：net stop mysql
退出：quit

登录：

mysql -h 主机名 -P 端口号 -u 用户名 -p

参数说明：
-h : 指定客户端所要登录的 MySQL 主机名, 登录本机(localhost 或 127.0.0.1)该参数可以省略;
-P ：指定端口号，默认3306
-u : 登录的用户名;
-p : 告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项。

所以登录本机：
mysql -u root -p

常用命令

一、查询

1. 导入数据库
   mysql> source c:\temp\mysqlsampledatabase.sql
2. 查看数据库
   mysql> show databases;
3. 查询数据
   SELECT select_list FROM table_name;

二、 排序

4. 排序
   SELECT select_list FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
SELECT orderNumber, orderlinenumber, quantityOrdered * priceEach FROM orderdetails ORDER BY quantityOrdered * priceEach DESC;

三、过滤数据 Filtering data

4.WHERE使用
SELECT select_list FROM table_name WHERE search_condition;

The search_condition is a combination of one or more predicates using the logical operator AND, ORand NOT.
In MySQL, a predicate is a Boolean expression that evaluates to TRUE, FALSE, or UNKNOWN.

image.png

• Using MySQL WHERE clause with AND operator
• Using MySQL WHERE clause with OR operator
• Using MySQL WHERE with BETWEEN operator example
  expression BETWEEN low AND high
• Using MySQL WHERE with the LIKE operator example

The % wildcard matches any string of zero or more characters while the _ wildcard matches any single character.

SELECT firstName, lastName FROM employees WHERE lastName LIKE '%son' ORDER BY firstName;

• Using MySQL WHERE clause with the IN operator example
  value IN (value1, value2,...)
SELECT firstName, lastName, officeCode FROM employees WHERE officeCode IN (1 , 2, 3) ORDER BY officeCode;
• Using MySQL WHERE clause with the IS NULL operator

To check if a value is NULL or not, you use the IS NULL operator, not the equal operator (=). The IS NULLoperator returns TRUE if a value is NULL.

value IS NULL
SELECT lastName, firstName, reportsTo FROM employees WHERE reportsTo IS NULL;

• Using MySQL WHERE clause with comparison operators
  SELECT lastname, firstname, jobtitle FROM employees WHERE jobtitle <> 'Sales Rep';

5. DISTINCT

When querying data from a table, you may get duplicate rows. In order to remove these duplicate rows, you use the DISTINCT clause in the SELECT`statement.

SELECT DISTINCT select_list FROM table_name;

If a column has NULL values and you use the DISTINCT clause for that column, MySQL keeps only one NULL value because DISTINCT treats all NULL values as the same value.

SELECT DISTINCT state, city FROM customers WHERE state IS NOT NULL ORDER BY state, city;

DISTINCT clause vs. GROUP BY clause

If you use the [GROUP BY](https://www.mysqltutorial.org/mysql-group-by.aspx) clause in the SELECT statement without using aggregate functions, the GROUP BY clause behaves like the DISTINCT clause.
SELECT state FROM customers GROUP BY state;

6. AND

boolean_expression_1 AND boolean_expression_2

SELECT 1 = 0 AND 1 / 0 ; 答案是0

说明：MySQL only evaluates the first part 1 = 0 of the expression 1 = 0 AND 1 / 0. Since the expression 1 = 0 returns false, MySQL can conclude the result of the whole expression, which is false. MySQL does not evaluate the remaining part of the expression, which is 1/0; If it did, it would issue an error because of the division by zero error

SELECT customername, country, state FROM customers WHERE country = 'USA' AND state = 'CA';

7. OR
   boolean_expression_1 OR boolean_expression_2

SELECT customername, country, creditLimit FROM customers WHERE(country = 'USA' OR country = 'France') AND creditlimit > 100000;
说明:一定要用括号，否则会先执行AND命令

8. IN
   SELECT column1,column2,... FROM table_name WHERE (expr|column_1) IN ('value1','value2',...);

SELECT officeCode, city, phone, country FROM offices WHERE country IN ('USA' , 'France');
Using MySQL IN with a subquery
SELECT orderNumber, customerNumber, status, shippedDate FROM orders WHERE orderNumber IN ( SELECT orderNumber FROM orderDetails GROUP BY orderNumber HAVING SUM(quantityOrdered * priceEach) > 60000

9. BETWEEN
   expr [NOT] BETWEEN begin_expr AND end_expr;

• Using MySQL BETWEEN with number examples

SELECT productCode, productName, buyPrice FROM products WHERE buyPrice NOT BETWEEN 20 AND 100;

• Using MySQL BETWEEN with dates example

SELECT orderNumber, requiredDate, status FROM orders WHERE requireddate BETWEEN CAST('2003-01-01' AS DATE) AND CAST('2003-01-31' AS DATE);

10. LIKE
    expression LIKE pattern ESCAPE escape_character

• The percentage ( % ) wildcard matches any string of zero or more characters.
• The underscore ( _ ) wildcard matches any single character.

• MySQL LIKE operator with ESCAPE clause

Sometimes the pattern, which you want to match, contains wildcard character e.g., 10%, _20, etc. In this case, you can use the ESCAPE clause to specify the escape character so that MySQL will interpret the wildcard character as a literal character. If you don’t specify the escape character explicitly, the backslash character \ is the default escape character.

For example, if you want to find products whose product codes contain the string _20 , you can use the pattern %_20% as shown in the following query:

11. LIMIT

The LIMIT clause is used in the SELECT statement to constrain the number of rows to return.

SELECT select_list FROM table_name LIMIT [offset,] row_count;

In this syntax:
The offset specifies the offset of the first row to return. The offset of the first row

The row_count specifies the maximum number of rows to return.

image.png image.png

SELECT customerNumber, customerName, creditLimit FROM customers ORDER BY creditLimit, customerNumber LIMIT 5;
12 IS NULL
SELECT customerName, country, salesrepemployeenumber FROM customers WHERE salesrepemployeenumber IS NULL ORDER BY customerName;

第四部分见下一个笔记

遇到的坑

1. navicat中执行source命令会一直报错，没有找到解决办法，用cmd进行操作。