Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL related statements (CRUD) (SQLyog software)     - Python regular expressions: how to use regular expressions (Programming)

- How to deploy Icinga server (Server)

- Boost notes --Thread - problems encountered in the initial use on Ubuntu (Programming)

- CentOS7 minimized installation can not find the 'ifconfig' command - Repair Tips (Linux)

- ARM runtime environment built from scratch using QEMU emulator (Linux)

- Python extension module Ganglia 3.1.x (Linux)

- ActiveMQ5.10.2 version configuration JMX (Linux)

- PXE + Kickstart automatically install CentOS 6.5 (Linux)

- IOwait Linux system monitoring diagnostic tools (Linux)

- SYN attack hacker attack and defense of the basic principles and prevention technology (Linux)

- Mounting Windows shared directory system under the Linux (Linux)

- grep command Detailed and relevant examples (Linux)

- Use Aptik Battery Monitor monitoring Ubuntu laptop battery usage (Linux)

- C ++ free store and heap (Programming)

- To create a full command line Android Build System (Linux)

- C ++ stderr / stdout redirected to a file (Programming)

- Use preseed script to install Ubuntu 14.04 (Linux)

- Installation CD audio file extraction tool Flacon (Linux)

- Configuring a Linux operating system security management services (Linux)

- Android system source code and compile the kernel source code (Programming)

 
         
  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:      
 
- Lazarus IDE Start Basics Tutorial (Linux)
- Install Apache streaming media services on CentOS 6.4 (Server)
- tcpdump Linux system security tools (Linux)
- Swift string common method (Programming)
- Install the system cleaning software under Linux: BleachBit 1.4 (Linux)
- Ubuntu 14.04 configure JDK1.8.0_25, switchable version (Linux)
- PostgreSQL-- run Supervisord on Docker in Ubuntu (Database)
- Using Maven to download Spring (Linux)
- Ubuntu 14.04.1 LTS compile and install the new kernel (Linux)
- CentOS7 yum install third-party source EPEL (Linux)
- Python: Finding meet the conditions specified in the file directory (Programming)
- How to set cache valid time in Apache (Server)
- Linux System Getting Started Learning: The Linux log (Linux)
- Ubuntu installation under Scrapy (Linux)
- Wireless LAN security solutions (Linux)
- OGG-03510 Problem (Database)
- HomeKit User Interface Guidelines (Linux)
- How to restart after a crash Cinnamon (Linux)
- Ubuntu Tutorial - Manually install Oracle Java JDK 8 (Linux)
- ssh port forwarding Comments (Server)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.