Configuring the Database

Local login database

mysql -u root -p
Copy the code

Changing the Root Password

alter user user() identified by "abc123456"
Copy the code

Handy CURD operation

  1. Insert data into
  • Batch insert data. All data fails to be written because of a record problem
  • Insert if the implementation does not exist, and update the SQL statement if it does
INSERT INTO table_name(id, props) VALUES
  (4, xxx),
  (5, xxx)
ON DUPLITCATE KEY UPDATE ip=VALUES(props)
Copy the code

DUPLITCATE KEY UPDATE ON DUPLITCATE KEY UPDATE

  • The Mysql database has caching turned off by default, so each subquery is a related subquery
  • A related subquery is a subquery that is iterated multiple times

The following SQL statement executes a subquery for each record queried

 SELECT id, props
 FORM table_name
 WHERE props > (
   SELECT props 
   FORM table1_name
   WHERE props = value
 )
Copy the code
  • Use a FORM subquery instead of a WHERE subquery. The FORM subquery is executed only once and therefore is not a related subquery
SELECTT.id, t.ps FORM table_name tJOIN (SELECT props FORM table2_name) ON props = value
Copy the code
  • In an inner join, the query condition is in the ON or WHERE clause, which has the same effect
SELECT e.ename, d.dname FROM t_emp e
JOIN t_dept d ON e.deptno = d.deptno AND d.deptno = 10;

SELECT e.ename, d.dname FROM t_emp e
JOIN t_dept d ON e.deptno = d.deptno
WHERE d.deptno = 10;
Copy the code
  • In the outer join, the query condition is written in the ON or WHERE clause, which has different effects
SELECT e.ename, d.dname FROM t_emp e
LEFT JOIN t_dept d ON e.deptno = d.deptno AND d.deptno = 10;

SELECT e.ename, d.dname FROM t_emp e
LEFT JOIN t_dept d ON e.deptno = d.deptno
WHERE d.deptno = 10;
Copy the code

Select * from t_DEPT where table emp is NULL; select * from T_DEPT where table emp is NULL; select * from T_DEPT where table EMP is NULL

  • How can A WHERE subquery in an UPDATE statement be changed to a table join

Before optimization, correlation sub-query

UPDATE t_emp SET sal = 10000
WHERE deptno = 
(SELECT deptno FROM t_dept WHERE dname = 'SALES')
Copy the code

After optimization, table joins can be modified not only t_EMp but also T_DEPT

UPDATE t_emp e JOIN t_dept d ON e.deptno = d.deptno
AND d.dname='SALES'
SET e.sal = 10000, d.dname="Sales Department"Copy the code
  • Table joins can also be used in DELETE statements
DELETE e, d FROM t_emp e JOIN t_dept d
ON e.deptno =d.deptno AND d.dname ="Sales Department"Copy the code