Mysqlデータベースの高度なクエリーマルチテーブルクエリー、結合テーブルクエリー
最近PHP+MYSQLのプロジェクトをやっていますが、正直、初めてですね
絶えず資料を調べて、いくつかの資料がコレクションする価値があると感じます
Mysqlデータベースの高度なクエリー
絶えず資料を調べて、いくつかの資料がコレクションする価値があると感じます
Mysqlデータベースの高度なクエリー
emp ，emp_name
mysql> select * from emp where emp_name=' ';
：
+--------+----------+---------+---------+------------+---------+
| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |
+--------+----------+---------+---------+------------+---------+
| 100005 | | 27 | 4000 | 1979-07-10 | male |
+--------+----------+---------+---------+------------+---------+
1 row in set (0.00 sec)
emp ，emp_sal， 5000
mysql> select * from emp where emp_sal>5000;
：
+--------+----------+---------+---------+------------+---------+
| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |
+--------+----------+---------+---------+------------+---------+
| 100001 | | 29 | 9000 | 1977-01-01 | male |
| 100002 | | 27 | 8000 | 1979-12-31 | fmale |
+--------+----------+---------+---------+------------+---------+
2 rows in set (0.00 sec)
emp 1978 1 1
mysql> select * from emp where emp_bir>'1978-01-01';
：
+--------+----------+---------+---------+------------+---------+
| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |
+--------+----------+---------+---------+------------+---------+
| 100005 | | 27 | 4000 | 1979-07-10 | male |
| 100002 | | 27 | 8000 | 1979-12-31 | fmale |
+--------+----------+---------+---------+------------+---------+
2 rows in set (0.00 sec)
emp 1979 12 1 ， 5000
mysql> select * from emp where emp_bir<'1979-12-01' and emp_sal>5000;
：
+--------+----------+---------+---------+------------+---------+
| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |
+--------+----------+---------+---------+------------+---------+
| 100001 | | 29 | 9000 | 1977-01-01 | male |
+--------+----------+---------+---------+------------+---------+
1 row in set (0.00 sec)
2.6.2
CEO
mysql> select emp_name,emp_sal from emp;
：
+----------+---------+
| emp_name | emp_sal |
+----------+---------+
| | 4000 |
| | 9000 |
| | 8000 |
+----------+---------+
3 rows in set (0.00 sec)
1978 、
mysql> select emp_name,emp_sal,emp_sex from emp where emp_bir>"1977-12-31";
：
+----------+---------+---------+
| emp_name | emp_sal | emp_sex |
+----------+---------+---------+
| | 4000 | male |
| | 8000 | fmale |
+----------+---------+---------+
2 rows in set (0.00 sec)
2.6.3
ORDER BY emp （ —— ）
mysql> select * from emp order by emp_sal;
：
+--------+----------+---------+---------+------------+---------+
| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |
+--------+----------+---------+---------+------------+---------+
| 100005 | | 27 | 4000 | 1979-07-10 | male |
| 100002 | | 27 | 8000 | 1979-12-31 | fmale |
| 100001 | | 29 | 9000 | 1977-01-01 | male |
+--------+----------+---------+---------+------------+---------+
3 rows in set (0.00 sec)
DESC ——
mysql> select * from emp order by emp_sal desc;
：
+--------+----------+---------+---------+------------+---------+
| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |
+--------+----------+---------+---------+------------+---------+
| 100001 | | 29 | 9000 | 1977-01-01 | male |
| 100002 | | 27 | 8000 | 1979-12-31 | fmale |
| 100005 | | 27 | 4000 | 1979-07-10 | male |
+--------+----------+---------+---------+------------+---------+
3 rows in set (0.00 sec)
2.6.4
LIMIT emp ：
mysql> select * from emp order by emp_sal desc limit 2;
：
+--------+----------+---------+---------+------------+---------+
| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |
+--------+----------+---------+---------+------------+---------+
| 100001 | | 29 | 9000 | 1977-01-01 | male |
| 100002 | | 27 | 8000 | 1979-12-31 | fmale |
+--------+----------+---------+---------+------------+---------+
2 rows in set (0.00 sec)
2 3 ：
mysql> select * from emp order by emp_sal desc limit 1,2;
：
+--------+----------+---------+---------+------------+---------+
| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |
+--------+----------+---------+---------+------------+---------+
| 100002 | | 27 | 8000 | 1979-12-31 | fmale |
| 100005 | | 27 | 4000 | 1979-07-10 | male |
+--------+----------+---------+---------+------------+---------+
2 rows in set (0.01 sec)
rand() ， 2 ，
mysql> select * from emp order by rand() limit 2;
：（ ）
+--------+----------+---------+---------+------------+---------+
| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |
+--------+----------+---------+---------+------------+---------+
| 100005 | | 27 | 4000 | 1979-07-10 | male |
| 100001 | | 29 | 9000 | 1977-01-01 | male |
+--------+----------+---------+---------+------------+---------+
2 rows in set (0.01 sec)
2.6.5
mysql> select concat(emp_id," ",emp_name) from emp;
：
+------------------------------+
| concat(emp_id," ",emp_name) |
+------------------------------+
| 100005 |
| 100001 |
| 100002 |
+------------------------------+
3 rows in set (0.00 sec)
AS
mysql> select concat(emp_id," ",emp_name) as info from emp;
：
+----------------+
| info |
+----------------+
| 100005 |
| 100001 |
| 100002 |
+----------------+
3 rows in set (0.00 sec)
2.6.6
YEAR()、MONTH()、DAYOFMONTH()
7 ：
mysql> select * from emp where month(emp_bir)=7;
：
+--------+----------+---------+---------+------------+---------+
| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |
+--------+----------+---------+---------+------------+---------+
| 100005 | | 27 | 4000 | 1979-07-10 | male |
+--------+----------+---------+---------+------------+---------+
1 row in set (0.00 sec)
：
mysql> select * from emp where monthname(emp_bir)="January";
：
+--------+----------+---------+---------+------------+---------+
| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |
+--------+----------+---------+---------+------------+---------+
| 100001 | | 29 | 9000 | 1977-01-01 | male |
+--------+----------+---------+---------+------------+---------+
1 row in set (0.00 sec)
TO_DAYS() ，
mysql> select to_days(current_date) - to_days(emp_bir) as livingdays from emp;
：
+------------+
| livingdays |
+------------+
| 9425 |
| 10345 |
| 9251 |
+------------+
3 rows in set (0.00 sec)
100
mysql> select date_add(now(),interval 100 day);
：
+----------------------------------+
| date_add(now(),interval 100 day) |
+----------------------------------+
| 2005-08-07 13:56:58 |
+----------------------------------+
1 row in set (0.00 sec)
100
mysql> select date_sub(now(),interval 100 day);
：
+----------------------------------+
| date_sub(now(),interval 100 day) |
+----------------------------------+
| 2005-01-19 14:00:20 |
+----------------------------------+
1 row in set (0.00 sec)
2.6.7
COUNT() （ emp ）
mysql> select count(*) from emp;
：
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.01 sec)
5000
mysql> select count(*) from emp where emp_sal>5000;
：
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
：（GROUP BY ）
mysql> select emp_sex,count(*) from emp group by emp_sex;
：
+---------+----------+
| emp_sex | count(*) |
+---------+----------+
| fmale | 1 |
| male | 2 |
+---------+----------+
2 rows in set (0.01 sec)
（MIN(),MAX(),SUM(),AVG()）
mysql> select
-> min(emp_sal) as min_salary,
-> max(emp_sal) as max_salary,
-> sum(emp_sal) as sum_salary,
-> avg(emp_sal) as avg_salary,
-> count(*) as employee_num
-> from emp;
：
+------------+------------+------------+------------+--------------+
| min_salary | max_salary | sum_salary | avg_salary | employee_num |
+------------+------------+------------+------------+--------------+
| 4000 | 9000 | 21000 | 7000.0000 | 3 |
+------------+------------+------------+------------+--------------+
1 row in set (0.00 sec)
2.6.8
， ：
1). asb dept， ：
dept_id --> varchar(6)
dept_name --> varchar(10)
2). emp ：
+--------+----------+---------+---------+------------+---------+
| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |
+--------+----------+---------+---------+------------+---------+
| 100003 | | 30 | 8000 | 1976-11-11 | fmale |
+--------+----------+---------+---------+------------+---------+
3). dept ，
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 100005 | MTD |
| 100001 | MTD |
| 100002 | MTD |
| 100003 | HR |
+---------+-----------+
emp dept ，
mysql> select emp.emp_name,dept.dept_name from emp,dept
-> where emp.emp_id=dept.dept_id;
：
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| | MTD |
| | MTD |
| | MTD |
| | HR |
+----------+-----------+
4 rows in set (0.00 sec)
：
1). FROM
2). （ emp emp_id emp.emp_id）
3). Where （ ，emp.emp_id dept.dept_id ）