SHOW DATABASES; CREATE DATABASE IF NOT EXISTS test; USE test; SQL > select * from table where name = '; '; End CREATE TABLE user_test(id int, password varchar(6), name varchar(20), phone varchar(11), email varchar(20)); Alter table user_test modify email varchar(50); Insert into user_test values(1001,'123456',' 13468857714','[email protected]'); SELECT * from user_test; Desc user_test; SELECT name FROM user_test where name=' user_test '; CREATE TABLE dept_test(dept_id int(2), dept_name CHAR(20), dept_location CHAR(20)); Desc dept_test; INSERT INTO dept_test VALUES(10,'developer',' Beijing '); INSERT INTO dept_test VALUES(20,'account','shanghai'); INSERT INTO dept_test VALUES(30,'sales','guangzhou'); INSERT INTO dept_test VALUES(40,'operations','tianjin'); SELECT * FROM dept_test; CREATE TABLE emp_test(emp_id INT(4), name VARCHAR(20), job VARCHAR(20), salary FLOAT(7,2), bonus FLOAT(7,2), hire_date DATE, manager INT(4), dept_test_id INT(2) ); DESC emp_test; SELECT * FROM EMP_TEST; DATE_FORMAT(date,format) ¶ Oracle uses TO_DATE(ch, FMT) INSERT INTO emp_test VALUES (1001, 'zhang mowgli', 'Manager', '10000', '2000', DATE_FORMAT (' 2010-01-12 ', '% y - % m - % d'), 1005, 10); INSERT INTO emp_test VALUES(1002,' Analyst', 'Analyst', 8000, 1000,DATE_FORMAT('2011-01-12','%y-%m-%d'),1001,10); INSERT INTO emp_test VALUES(1003, 'Analyst', 'Analyst',9000, 1000,DATE_FORMAT('2010-02-11','%y-%m-%d'),1001,10); INSERT INTO emp_test VALUES(1004,' Programmer', NULL,DATE_FORMAT('2010-02-11','%y-%m-%d'),1001,10); INSERT INTO EMP_test VALUES(1005,' President', 10000,NULL, DATE_FORMAT('2008-02-15','%y-%m-%d'),NULL,20); INSERT INTO EMP_test VALUES(1006,' Manager',5000,400,DATE_FORMAT('2009-02-12','%y-%m-%d'), 1005, 20); Insert into EMP_test values(1007,' k',' CLERK ', 3000, 500, DATE_FORMAT('2009-02-01','%y-%m-%d'), 1006, 20); Insert into EMP_test values(1008,' Manager',5000,500, DATE_FORMAT('2009-11-01','%y-%m-%d'), 1005, 30); Insert into EMP_test values(1009,' salesman',4000, null, DATE_FORMAT('2009-05-20','%y-%m-%d'), 1008, 30); Insert into EMP_test values(1010,' salesman',4500, 500, DATE_FORMAT('2009-10-10','%y-%m-%d'), 1008, 30); SELECT * FROM emp_test; SELECT * FROM dept_test; SELECT name,salary,salary*12 year_salary FROM emp_test; IFNULL(expr1,expr2); SELECT name,salary,bonus,salary+ IFNULL(bonus,0) month_salary FROM emp_test; INSERT INTO emp_test (emp_id,name) VALUES(1011,' emp_id '); SELECT name,IFNULL(job,'no positon') job FROM emp_test; SQL > select * from emp_test where emp_test date = 10 October 2011 SELECT name,IFNULL(hire_date,'2016-12-12') hire_date FROM emp_test; # concatenate string using CONCAT(str1,str2...) And Oracle have distinction, Oracle | | SELECT emp_id, CONCAT (name, 'the job is, the job) the detail FROM emp_test; CREATE TABLE emp_test2 AS SELECT * FROM emp_test; SELECT * FROM emp_test2; Note: DISTINCT must (and only) be followed by SELECT. DISTINCT means a unique combination of all columns. SELECT DISTINCT job FROM emp_test; What departments are the employees in? SELECT DISTINCT dept_test_id FROM emp_test; SELECT DISTINCT job,dept_test_id FROM emp_test; # Data on employees who earn more than $10,000? SELECT * FROM emp_test WHERE salary>10000; # Position is the employee data of Analyst? SELECT * FROM emp_test WHERE LOWER(job)='analyst'; SELECT * FROM emp_test WHERE LOWER(job)='analyst'; # how many employees are being paid more than 5000 and less than 10000? > = < =; between and SELECT * FROM emp_test WHERE salary>=5000 AND salary<=10000; SELECT * FROM emp_test WHERE salary BETWEEN 5000 AND 10000; # Employees who started in 2011? SELECT * FROM emp_test WHERE hire_date=DATE_FORMAT('2011','%y'); SELECT * FROM emp_test WHERE hire_date BETWEEN DATE_FORMAT('2011-01-01','%y-%m-%d') AND DATE_FORMAT('2011-12-31','%y-%m-%d') ; SELECT * FROM emp_test WHERE job IN('Manager','Analyst'); SELECT * FROM emp_test WHERE job IN('Manager','Analyst'); # select * from 'sales'; SELECT * FROM emp_test WHERE LOWER(job) LIKE '%sales%' SELECT * FROM emp_test WHERE LOWER(job) LIKE '_a%' #SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME LIKE '%EMP%'; insert into emp_test values(1012 , 'text_test' , 'salesman' ,4500 , 500 , DATE_FORMAT('2009-10-10','%y-%m-%d') , 1008 , 30); SELECT * FROM emp_test; Mysql > select * from mysql_query where special characters (such as _ or %) exist; SELECT name FROM emp_test WHERE name LIKE '%\_%'; SELECT name FROM emp_test WHERE name LIKE '%\_%'; DELETE FROM emp_test WHERE emp_id=1012 SELECT * FROM emp_test WHERE bonus IS NULL; # Employees who don't earn between 5000 and 8000? SELECT * FROM emp_test WHERE salary NOT BETWEEN 5000 AND 8000; # Not an employee of department 20 and 30? SELECT * FROM emp_test WHERE dept_test_id NOT IN(20,30);Copy the code