MySQL-related statements (add, delete, modify, and query) (SQLyog software implementation)

MySQL-related statements (add, delete, modify, and query) (SQLyog software implementation)

MySQL-related statements (add, delete, modify, and query) (SQLyog software implementation)

-- Create an employee table: emp
Create table emp (
Empno INT, -- employee ID
Ename VARCHAR (50), -- employee name
Job VARCHAR (50), -- employee work
Mgr INT, -- lead number
Hiredate DATE, -- entry DATE
Sal DECIMAL (), -- monthly salary
Comm DECIMAL (), -- bonus
Deptno INT -- part number
);

-- Add data
Insert into emp VALUES (7369, 'Smith ', 'cler', 7902, '2017-12-17', 1980, NULL, 20 );
Insert into emp VALUES (7499, 'allen ', 'salesman', 7698, '2017-02-20', 1981, 30 );
Insert into emp VALUES (7521, 'ward ', 'salesman', 7698, '2017-02-22', 1981, 30 );
Insert into emp VALUES (7566, 'Jones ', 'manager', 7839, '2017-04-02', 1981, NULL, 20 );
Insert into emp VALUES (7654, 'martin ', 'salesman', 7698, '2017-09-28', 1250,1400, 30 );
Insert into emp VALUES (7698, 'bucke', 'manager', 7839, '2017-05-01 ', 1981, NULL, 30 );
Insert into emp VALUES (7782, 'clark', 'manager', 7839, '2017-06-09 ', 1981, NULL, 10 );
Insert into emp VALUES (7788, 'Scott ', 'analyst', 7566, '2017-04-19', 1987, NULL, 20 );
Insert into emp VALUES (7839, 'King', 'President ', NULL, '2017-11-17', 1981, NULL, 10 );
Insert into emp VALUES (7844, 'turner ', 'salesman', 7698, '2017-09-08', 1981, 30 );
Insert into emp VALUES (7876, 'adams', 'cler', 7788, '2017-05-23 ', 1987, NULL, 20 );
Insert into emp VALUES (7900, 'James ', 'cler', 7698, '2017-12-03', 1981, NULL, 30 );
Insert into emp VALUES (7902, 'Ford ', 'analyst', 7566, '2017-12-03', 1981, NULL, 20 );
Insert into emp VALUES (7934, 'miller ', 'cler', 7782, '2017-01-23', 1982, NULL, 10 );

-- Create a student information table: stu
Create table stu (
Sid CHAR (6), -- Student ID
Sname VARCHAR (50), -- Student name
Age INT, -- student age
Gender VARCHAR (50) -- Student gender
);

-- Add data
Insert into stu VALUES ('s _ 1001 ', 'liuyi', 35, 'male ');
Insert into stu VALUES ('s _ 1002 ', 'chener', 15, 'female ');
Insert into stu VALUES ('s _ 1003 ', 'hangsan', 95, 'male ');
Insert into stu VALUES ('s _ 1004 ', 'lisi', 65, 'female ');
Insert into stu VALUES ('s _ 1005 ', 'wangw', 55, 'male ');
Insert into stu VALUES ('s _ 1006 ', 'zhaoliu', 75, 'female ');
Insert into stu VALUES ('s _ 1007 ', 'sunqi', 25, 'male ');
Insert into stu VALUES ('s _ 1008 ', 'zhouba', 45, 'female ');
Insert into stu VALUES ('s _ 1009 ', 'wujiu', 85, 'male ');
Insert into stu VALUES ('s _ 1010 ', 'zhengshi', 50, 'female ');
Insert into stu VALUES ('s _ 1011 ', 'xxx', NULL, NULL );

-- Create some tables: dept
Create table dept (
Deptno INT, -- partially encoded
Dname VARCHAR (14), -- partial name
Loc VARCHAR (13) -- some locations
);

-- Add data
Insert into dept VALUES (10, 'accounting', 'New YORK ');
Insert into dept VALUES (20, 'Research ', 'Dallas ');
Insert into dept VALUES (30, 'sales', 'Chicago ');
Insert into dept VALUES (40, 'operations', 'boston ');

-- Query all columns
SELECT * FROM stu;

-- Query a specified Column
SELECT sname, age FROM stu;

-- Query records whose gender is female and whose age is 50
SELECT * FROM stu WHERE gender = 'female' AND age = 50;

-- Query records whose student ID is S_1001 or whose name is liSi
SELECT * FROM stu WHERE sid ='s _ 1001 'OR sname = 'lisi ';

-- Query records with the student ID S_1001, S_1002, S_1003
SELECT * FROM stu WHERE sid IN ('s _ 1001 ','s _ 1002','s _ 1003 ');

-- Query records whose student IDs are not S_1001, S_1002, and S_1003
SELECT * FROM stu WHERE sid not in ('s _ 1001 ','s _ 1002','s _ 1003 ');

-- Query records whose age is null
SELECT * FROM stu WHERE age is null;

-- Query student records between 20 and 40
SELECT * FROM stu WHERE age BETWEEN 20 AND 40; -- or
SELECT * FROM stu WHERE age> = 20 AND age <= 40;

-- Query gender non-male student records
SELECT * FROM stu WHERE gender! = 'Male'; -- or
SELECT * FROM stu WHERE gender <> 'male ';

-- Query student records whose names are not null
SELECT * FROM stu WHERE sname is not null; -- or
SELECT * FROM stu where not sname is null;

-- Query the student records whose names consist of five letters
SELECT * FROM stu WHERE sname LIKE '_____';

-- Query the student records whose names consist of five letters and whose 5th letters are "I"
SELECT * FROM stu WHERE sname LIKE '____ I ';

-- Query student records whose names start with "z"
SELECT * FROM stu WHERE sname LIKE 'z % ';

-- Query the student records whose 2nd letters are "I" in the name
SELECT * FROM stu WHERE sname LIKE '_ I % ';

-- Query the student records whose names contain the letter ""
SELECT * FROM stu WHERE sname LIKE '% a % ';

-- Remove duplicate records
Select distinct * FROM stu;

-- Query all columns
SELECT * FROM emp;

-- View the sum of the employee's monthly salary and Commission
SELECT *, sal + comm FROM emp; -- or
SELECT *, sal + IFNULL (comm, 0) FROM emp;

-- Add an alias to the column name
SELECT *, sal + IFNULL (comm, 0) AS total FROM emp; -- or
SELECT *, sal + IFNULL (comm, 0) total FROM emp;

-- Query all student records in ascending order of age
SELECT * FROM stu order by age;
SELECT * FROM stu order by age ASC;

-- Query all student records in descending order of age
SELECT * FROM stu order by age DESC;

-- Query all employees in descending order of monthly salary. If the monthly salary is the same, it is sorted by serial number in ascending order.
SELECT * FROM emp order by sal DESC, empno ASC;

-- Query the number of records in the emp table
Select count (*) AS cnt FROM emp;

-- Query the number of people with commissions in the emp table
Select count (comm) AS cnt FROM emp;

-- Query the number of people whose monthly salary is greater than 2500 in the emp table
Select count (x) AS cnt FROM emp WHERE sal> 2500;

-- Count the total number of people with a monthly salary and commission of more than 2500 yuan
Select count (*) AS cnt FROM emp WHERE sal + IFNULL (comm, 0)> 2500;

-- Query the number of people with commissions and leaders
Select count (comm), COUNT (mgr) FROM emp;

-- Query monthly salary and
Select sum (sal) FROM emp;

-- Query the monthly salary and commission of all employees and
Select sum (sal), SUM (comm) FROM emp;

-- Query monthly salary + Commission and
Select sum (sal + IFNULL (comm, 0) FROM emp;

-- Calculate the average salary of all employees
Select avg (sal) FROM emp;

-- Query the highest wage and minimum wage
Select max (sal), MIN (sal) FROM emp;

-- Query the Department numbers of each department and the salaries and
SELECT deptno, SUM (sal) FROM emp group by deptno;

-- Query the Department numbers of each department and the number of people in each department
SELECT deptno, COUNT (*) AS cnt FROM emp group by deptno;

-- Query the Department numbers of each department and the number of employees whose salaries are greater than 1500.
SELECT deptno, COUNT (*) FROM emp WHERE sal> 1500 group by deptno;

-- Query the number of the department whose total salary is greater than 9000 and the sum of salary and
SELECT deptno, SUM (sal) FROM emp group by deptno having sum (sal)> 9000;

--

-- Query all columns
SELECT * FROM dept;

-- Create student info table student
Create table student (
Sid INT, -- Student ID
Sname VARCHAR (20), -- name
Age INT, -- age
Scores INT, -- score
Gender VARCHAR (6), -- gender
Birthday DATE -- DATE of birth
);

-- Add data
Insert into student (sid, sname, age, scores, gender) VALUES (0001, 'zhang hua', 'female ');
Insert into student (sid, sname, age, scores, gender, birthday) VALUES (0007, 'Sun Meng ', 1997, 'female', '2017-02-24 ');
Insert into student (sid, sname, age, scores, gender) VALUES (0002, 'Lee 4', 'mal ');
Insert into student (sid, sname, age, scores, gender, birthday) VALUES (0009, 'wu Xiaoqing ', 1994, 'female', '2017-11-02 ');
Insert into student (sid, sname, age, scores, gender) VALUES (0003, 'wang Xiaohua ', 'female ');
Insert into student (sid, sname, age, scores, gender, birthday) VALUES (0004, 'zhao liu', 1990, 'male', '2017-12-17 ');
Insert into student (sid, sname, age, scores, gender, birthday) VALUES (0005, 'Qian yu', 1992, 'female, '2017-03-30 ');
Insert into student (sid, sname, age, scores, gender, birthday) VALUES (0006, 'Lee 4', 1996, 'mal', '2017-05-13 ');
Insert into student (sid, sname, age, scores, gender, birthday) VALUES (0008, 'Jay Chou ', 1995, 'male', '2017-07-23 ');
Insert into student (sid, sname, age, scores, gender, birthday) VALUES (00010, 'zheng yuan ', 1994, 'male', '2017-08-23 ');

-- Query all columns
SELECT * FROM student;

-- Delete the table student
Drop table student;

-- Remove duplicate records
Select distinct * FROM student;

-- Display all tables in mydb
USE mydb; show tables;

This article permanently updates the link address: