Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL related statements (CRUD) (SQLyog software)     - PostgreSQL query result area is removed and precision (Database)

- ogg start being given libnnz11.so: can not open shared object file (Database)

- Lua non-blocking write log (Programming)

- Linux System Getting Started Learning: DeVeDe installed on Linux to create a video DVD (Linux)

- Install Debian Linux with R on the Android system (Linux)

- Linux operating system security tools of the Logs (Linux)

- How to use Aptik to backup and restore Apps/PPAs under ubuntu (Linux)

- Linux commands to access the cheat sheet (Linux)

- Linux formatted partition error Could not stat / dev / sda No such file or directory Solution (Linux)

- MongoDB relations, references, index query coverage (Database)

- To compile and install MySQL 5.7.7 RC under CentOS 7.1 (Database)

- Monitor log file (listener.log) (Database)

- Setting up Linux machine through a proxy firewall (Linux)

- Getting Started with Linux system to learn: how to install autossh (Linux)

- Use the DBMS_SCHEDULER package to manage scheduled tasks (Database)

- How to allow users to access only a specific database (MSSQL) (Database)

- Implement firewall function on a closed Linux machine (Linux)

- To disable the function by pressing Ctrl-Alt-Del to restart the system in RHEL / CentOS 5/6 (Linux)

- Linux Crontab Timing task command Detailed (Linux)

- ASM learning overview (Database)

 
         
  MySQL related statements (CRUD) (SQLyog software)
     
  Add Date : 2018-11-21      
         
         
         
  MySQL related statements (CRUD) (SQLyog software)

- Create the Employees table: emp
CREATE TABLE emp (
    empno INT, - number of employees
    ename VARCHAR (50), - name of the employee
    job VARCHAR (50), - employee
    mgr INT, - leading No.
    hiredate DATE, - the date of entry
    sal DECIMAL (7,2), - monthly
    comm DECIMAL (7,2), - bonus
    deptno INT - part number
);

- Add Data
INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600,300,30);
INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250,500,30);
INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250,1400,30);
INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20);
INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500,0,30);
INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20);
INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);


- Creating Student Information Sheet: stu
CREATE TABLE stu (
    sid CHAR (6), - students to learn numbers
    sname VARCHAR (50), - Name of student
    age INT, - age of the students
    gender VARCHAR (50) - Student Sex
);

- 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', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES ( 'S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES ( 'S_1005', 'wangWu', 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  parts table: dept
CREATE TABLE dept (
    deptno INT, - partially encoded
    dname VARCHAR (14), - part of the name
    loc VARCHAR (13) - Part location
);

- 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');


- Discover all the columns
SELECT * FROM stu;

- Query the specified column
SELECT sname, age FROM stu;

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

- Search school No. S_1001, or the name of a recording liSi
SELECT * FROM stu WHERE sid = 's_1001' OR sname = 'lisi';

- Search school No. S_1001, S_1002, S_1003 record
SELECT * FROM stu WHERE sid IN ( 's_1001', 's_1002', 's_1003');

- Discover Student ID is not S_1001, S_1002, S_1003 record
SELECT * FROM stu WHERE sid NOT IN ( 's_1001', 's_1002', 's_1003');

- Query age is null record
SELECT * FROM stu WHERE age IS NULL;

- Query students aged between 20-40 record
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 the name of student records is not null
SELECT * FROM stu WHERE sname IS NOT NULL; - or
SELECT * FROM stu WHERE NOT sname IS NULL;

- Discover the five-letter name of student records
SELECT * FROM stu WHERE sname LIKE '_____';

- Discover the five-letter name, and the first five letters "i" in student records
SELECT * FROM stu WHERE sname LIKE '____i';

- Student Records query name to "z" at the beginning of
SELECT * FROM stu WHERE sname LIKE 'z%';

Students record query name in the first two letters of "i" -
SELECT * FROM stu WHERE sname LIKE '_i%';

- Query name contains student records "a" letter
SELECT * FROM stu WHERE sname LIKE '% a%';

- Remove duplicate records
SELECT DISTINCT * FROM stu;


- Discover all the columns
SELECT * FROM emp;

- View employee salary and commission sum
SELECT *, sal + comm FROM emp; - or
SELECT *, sal + IFNULL (comm, 0) FROM emp;

- To add a column name alias
SELECT *, sal + IFNULL (comm, 0) AS total FROM emp; - or
SELECT *, sal + IFNULL (comm, 0) total FROM emp;

- Discover all student records, in ascending order by age
SELECT * FROM stu ORDER BY age;
SELECT * FROM stu ORDER BY age ASC;

- Discover all student records, sorted in descending order by age
SELECT * FROM stu ORDER BY age DESC;

- Discover all employees, sorted by salary in descending order, if the salary is the same, in ascending order by number
SELECT * FROM emp ORDER BY sal DESC, empno ASC;

- The number of records in the query emp table
SELECT COUNT (*) AS cnt FROM emp;

- Query the emp table has a number of commissions
SELECT COUNT (comm) AS cnt FROM emp;

- A monthly salary of more than 2,500 the number of queries the emp table
SELECT COUNT (*) AS cnt FROM emp WHERE sal> 2500;

- Statistics of the monthly salary and commission greater than $ 2,500 and the number of
SELECT COUNT (*) AS cnt FROM emp WHERE sal + IFNULL (comm, 0)> 2500;

- The number of inquiries with Commission, as well as the number of leadership
SELECT COUNT (comm), COUNT (mgr) FROM emp;

- Discover all employee salary and
SELECT SUM (sal) FROM emp;

- Discover all employees and salary, as well as all employees and commissions
SELECT SUM (sal), SUM (comm) FROM emp;

- Discover all employees and salary + commission
SELECT SUM (sal + IFNULL (comm, 0)) FROM emp;

- Statistics the average wage for all employees
SELECT AVG (sal) FROM emp;

- Query the highest wages and the minimum wage
SELECT MAX (sal), MIN (sal) FROM emp;

- Query salary in each department and each department number and department
SELECT deptno, SUM (sal) FROM emp GROUP BY deptno;

- The number of queries for each department and each department's department number
SELECT deptno, COUNT (*) AS cnt FROM emp GROUP BY deptno;

- Query each department number and the number of sectors in each sector wages higher than 1500
SELECT deptno, COUNT (*) FROM emp WHERE sal> 1500 GROUP BY deptno;

- Discover more than 9000 total wages and salaries and the number of sectors
SELECT deptno, SUM (sal) FROM emp GROUP BY deptno HAVING SUM (sal)> 9000;

-

- Discover all the columns
SELECT * FROM dept;

- Create a student information sheet student
CREATE TABLE student (
    sid INT, - Student ID
    sname VARCHAR (20), - name
    age INT, - Age
    scores INT, - results
    gender VARCHAR (6), - sex
    birthday DATE - Date of birth
);

- Add Data
INSERT INTO student (sid, sname, age, scores, gender) VALUES (0001, 'Zhang', 23,49 'female');
INSERT INTO student (sid, sname, age, scores, gender, birthday) VALUES (0007, 'Sun Meng', 20,59 'female', '1997-02-24');
INSERT INTO student (sid, sname, age, scores, gender) VALUES (0002, 'John Doe', 28,69 'M');
INSERT INTO student (sid, sname, age, scores, gender, birthday) VALUES (0009, 'Wu Xiaoqing', 22.68 'F', '1994-11-02');
INSERT INTO student (sid, sname, age, scores, gender) VALUES (0003, 'king of flowers', 26, 29, 'F');
INSERT INTO student (sid, sname, age, scores, gender, birthday) VALUES (0004, 'Zhao six', 22,85 'M', '1990-12-17');
INSERT INTO student (sid, sname, age, scores, gender, birthday) VALUES (0005, 'money rain', 21,87 'female', '1992-03-30');
INSERT INTO student (sid, sname, age, scores, gender, birthday) VALUES (0006, 'John Doe', 25,83 'M', '1996-05-13');
INSERT INTO student (sid, sname, age, scores, gender, birthday) VALUES (0008, 'Jay', 27,99 'M', '1995-07-23');
INSERT INTO student (sid, sname, age, scores, gender, birthday) VALUES (00010, 'Zheng Yuan', 27,79 'M', '1994-08-23');

- Discover all the columns
SELECT * FROM student;

- Delete table student
DROP TABLE student;

- Remove duplicate records
SELECT DISTINCT * FROM student;

- All tables under the mydb
USE mydb; SHOW TABLES;
     
         
         
         
  More:      
 
- Ubuntu and Derivative Edition users install LMMS 0.4.15 (Linux)
- Oracle table compression Technology Introduction (Database)
- How to monitor Nginx (Database)
- STL source code analysis - iterator each container classification (Programming)
- Math objects easily overlooked but very convenient method --JavaScript (Programming)
- How to Check notebook CPU temperature in Ubuntu (Linux)
- Linux memory management -free learning experience (Linux)
- Binary tree to the next node (Programming)
- Linux compiler of GCC (Linux)
- Use Pylint regulate your Python code (Programming)
- Ceph distributed storage system is installed on a CentOS 7.1 (Server)
- Oracle conditional select statements and looping statements (Database)
- Linux partition command (Linux)
- Basic Operation Linux files and directories (Linux)
- Analysis JavaBean (Programming)
- HomeKit User Interface Guidelines (Linux)
- XP virtual machine under VirtualBox solve occupy 100% CPU problem (Linux)
- Configuring automatic mail GAMIT under CentOS system (Linux)
- WinSCP to transfer text files will automatically convert the format (Linux)
- Windows 7 hard disk to install Ubuntu 15.04 (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.