mysql study 7
来源：互联网 发布：notepad mac版 编辑：程序博客网 时间：2024/05/23 12:47
mysql 查询数据：1.查询语句的基本语法。SELECT 属性列表 FROM 表名和视图列表 [WHERE 条件表达式][GROUP BY 属性名1[HAVING 条件表达式2]][ORDER BY 属性名2[ASC|DESC]]];mysql> CREATE TABLE employee( -> num INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -> id INT NOT NULL, -> name VARCHAR(20), -> age INT, -> sex VARCHAR(4), -> homeaddr VARCHAR(50) -> );Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO employee VALUES(NULL,1001,'zhangsan',26,'man','beijing');Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO employee VALUES(NULL,1001,'lisi',24,'woman','hubei');Query OK, 1 row affected, 1 warning (0.00 sec)mysql> INSERT INTO employee VALUES(NULL,1002,'wangwu',25,'woman','hunan');Query OK, 1 row affected, 1 warning (0.00 sec)mysql> INSERT INTO employee VALUES(NULL,1004,'sky',15,'man','sichuan');Query OK, 1 row affected (0.00 sec)mysql> SELECT * FROM employee;+-----+------+----------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+----------+------+------+----------+| 1 | 1001 | zhangsan | 26 | man | beijing || 2 | 1001 | lisi | 24 | woma | hubei || 3 | 1002 | wangwu | 25 | woma | hunan || 4 | 1004 | sky | 15 | man | sichuan |+-----+------+----------+------+------+----------+mysql> SELECT num,id,name,age,sex,homeaddr -> FROM employee -> WHERE age<26 -> ORDER BY id DESC;+-----+------+--------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+--------+------+------+----------+| 4 | 1004 | sky | 15 | man | sichuan || 3 | 1002 | wangwu | 25 | woma | hunan || 2 | 1001 | lisi | 24 | woma | hubei |+-----+------+--------+------+------+----------+3 rows in set (0.00 sec)2.在单表上查询数据。(表示从一张表中查询数据)<1>.查询所有字段mysql> SELECT num,id,name,age,sex,homeaddr -> FROM employee;+-----+------+----------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+----------+------+------+----------+| 1 | 1001 | zhangsan | 26 | man | beijing || 2 | 1001 | lisi | 24 | woma | hubei || 3 | 1002 | wangwu | 25 | woma | hunan || 4 | 1004 | sky | 15 | man | sichuan |+-----+------+----------+------+------+----------+4 rows in set (0.00 sec)mysql> SELECT * FROM employee;+-----+------+----------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+----------+------+------+----------+| 1 | 1001 | zhangsan | 26 | man | beijing || 2 | 1001 | lisi | 24 | woma | hubei || 3 | 1002 | wangwu | 25 | woma | hunan || 4 | 1004 | sky | 15 | man | sichuan |+-----+------+----------+------+------+----------+4 rows in set (0.00 sec)<2>.查询指定字段mysql> SELECT num, id FROM employee;+-----+------+| num | id |+-----+------+| 1 | 1001 || 2 | 1001 || 3 | 1002 || 4 | 1004 |+-----+------+4 rows in set (0.00 sec)mysql> SELECT * FROM employee WHERE id=1001;+-----+------+----------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+----------+------+------+----------+| 1 | 1001 | zhangsan | 26 | man | beijing || 2 | 1001 | lisi | 24 | woma | hubei |+-----+------+----------+------+------+----------+2 rows in set (0.00 sec)<3>带IN(NOT IN)关键字的查询：mysql> SELECT * FROM employee WHERE id IN(1001,1004);+-----+------+----------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+----------+------+------+----------+| 1 | 1001 | zhangsan | 26 | man | beijing || 2 | 1001 | lisi | 24 | woma | hubei || 4 | 1004 | sky | 15 | man | sichuan |+-----+------+----------+------+------+----------+3 rows in set (0.00 sec)mysql> SELECT * FROM employee WHERE name NOT IN('zhangsan','lisi');+-----+------+--------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+--------+------+------+----------+| 3 | 1002 | wangwu | 25 | woma | hunan || 4 | 1004 | sky | 15 | man | sichuan |+-----+------+--------+------+------+----------+2 rows in set (0.00 sec)<4>BETWEEN AND (NOT BETWEEN AND):mysql> SELECT * FROM employee WHERE age BETWEEN 15 AND 25;+-----+------+--------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+--------+------+------+----------+| 2 | 1001 | lisi | 24 | woma | hubei || 3 | 1002 | wangwu | 25 | woma | hunan || 4 | 1004 | sky | 15 | man | sichuan |+-----+------+--------+------+------+----------+3 rows in set (0.00 sec)mysql> SELECT * FROM employee WHERE age NOT BETWEEN 15 AND 25;+-----+------+----------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+----------+------+------+----------+| 1 | 1001 | zhangsan | 26 | man | beijing |+-----+------+----------+------+------+----------+1 row in set (0.00 sec)<5>LIKE(NOT LIKE)'字符串'mysql> SELECT * FROM employee WHERE name LIKE 's%';#匹配名字字段中开头为s的+-----+------+------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+------+------+------+----------+| 4 | 1004 | sky | 15 | man | sichuan |+-----+------+------+------+------+----------+1 row in set (0.00 sec)mysql> SELECT * FROM employee WHERE name LIKE '%s%';#匹配名字中包含有s的+-----+------+----------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+----------+------+------+----------+| 1 | 1001 | zhangsan | 26 | man | beijing || 2 | 1001 | lisi | 24 | woma | hubei || 4 | 1004 | sky | 15 | man | sichuan |+-----+------+----------+------+------+----------+3 rows in set (0.00 sec)mysql> SELECT * FROM employee WHERE name NOT LIKE 's%';+-----+------+----------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+----------+------+------+----------+| 1 | 1001 | zhangsan | 26 | man | beijing || 2 | 1001 | lisi | 24 | woma | hubei || 3 | 1002 | wangwu | 25 | woma | hunan |+-----+------+----------+------+------+----------+3 rows in set (0.00 sec)mysql> SELECT * FROM employee WHERE name NOT LIKE '%s%';+-----+------+--------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+--------+------+------+----------+| 3 | 1002 | wangwu | 25 | woma | hunan |+-----+------+--------+------+------+----------+1 row in set (0.00 sec)mysql> SELECT * FROM employee WHERE name LIKE 's_y';#_表示的是任意的字符+-----+------+------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+------+------+------+----------+| 4 | 1004 | sky | 15 | man | sichuan |+-----+------+------+------+------+----------+1 row in set (0.00 sec)<6>IS (NOT)NULLmysql> SELECT * FROM work WHERE info is NULL;+------+-----------+------+------+| id | name | sex | info |+------+-----------+------+------+| 1001 | zhangsan | NULL | NULL || 1002 | zhangsan1 | NULL | NULL |+------+-----------+------+------+2 rows in set (0.00 sec)mysql> SELECT * FROM work WHERE info is NOT NULL;+------+-----------+------+---------+| id | name | sex | info |+------+-----------+------+---------+| 1003 | zhangsan2 | NULL | student |+------+-----------+------+---------+1 row in set (0.00 sec)<7>AND 多条件查询(同时满足所有条件)：mysql> SELECT * FROM employee WHERE id=1001 AND age=26;+-----+------+----------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+----------+------+------+----------+| 1 | 1001 | zhangsan | 26 | man | beijing |+-----+------+----------+------+------+----------+1 row in set (0.00 sec)mysql> SELECT * FROM employee WHERE id=1001 AND age=26;+-----+------+----------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+----------+------+------+----------+| 1 | 1001 | zhangsan | 26 | man | beijing |+-----+------+----------+------+------+----------+1 row in set (0.00 sec)mysql> SELECT * FROM employee WHERE id=1001 AND age=26 AND sex='man';+-----+------+----------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+----------+------+------+----------+| 1 | 1001 | zhangsan | 26 | man | beijing |+-----+------+----------+------+------+----------+1 row in set (0.00 sec)<8>OR 查询(满足其中一个条件即可)mysql> SELECT * FROM employee WHERE id=1001 OR age<26 OR sex='man';+-----+------+----------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+----------+------+------+----------+| 1 | 1001 | zhangsan | 26 | man | beijing || 2 | 1001 | lisi | 24 | woma | hubei || 3 | 1002 | wangwu | 25 | woma | hunan || 4 | 1004 | sky | 15 | man | sichuan |+-----+------+----------+------+------+----------+4 rows in set (0.00 sec)<9>DISTINCT(消除重复的值)mysql> SELECT DISTINCT id FROM employee;+------+| id |+------+| 1001 || 1002 || 1004 |+------+3 rows in set (0.00 sec)<10>ORDER BY 属性名[ASC(升序)|DESC]mysql> SELECT * FROM employee ORDER BY age;+-----+------+----------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+----------+------+------+----------+| 4 | 1004 | sky | 15 | man | sichuan || 2 | 1001 | lisi | 24 | woma | hubei || 3 | 1002 | wangwu | 25 | woma | hunan || 1 | 1001 | zhangsan | 26 | man | beijing |+-----+------+----------+------+------+----------+4 rows in set (0.00 sec)mysql> SELECT * FROM employee ORDER BY age DESC;+-----+------+----------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+----------+------+------+----------+| 1 | 1001 | zhangsan | 26 | man | beijing || 3 | 1002 | wangwu | 25 | woma | hunan || 2 | 1001 | lisi | 24 | woma | hubei || 4 | 1004 | sky | 15 | man | sichuan |+-----+------+----------+------+------+----------+4 rows in set (0.00 sec)mysql> SELECT * FROM employee ORDER BY age ASC;+-----+------+----------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+----------+------+------+----------+| 4 | 1004 | sky | 15 | man | sichuan || 2 | 1001 | lisi | 24 | woma | hubei || 3 | 1002 | wangwu | 25 | woma | hunan || 1 | 1001 | zhangsan | 26 | man | beijing |+-----+------+----------+------+------+----------+4 rows in set (0.00 sec)<11>GROUP BY mysql> SELECT * FROM employee GROUP BY sex;+-----+------+----------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+----------+------+------+----------+| 1 | 1001 | zhangsan | 26 | man | beijing || 2 | 1001 | lisi | 24 | woma | hubei |+-----+------+----------+------+------+----------+2 rows in set (0.00 sec)mysql> SELECT sex ,GROUP_CONCAT(name) FROM employee GROUP BY sex;+------+--------------------+| sex | GROUP_CONCAT(name) |+------+--------------------+| man | zhangsan,sky || woma | lisi,wangwu |+------+--------------------+2 rows in set (0.00 sec)mysql> SELECT sex ,COUNT(sex) FROM employee GROUP BY sex;+------+------------+| sex | COUNT(sex) |+------+------------+| man | 2 || woma | 2 |+------+------------+2 rows in set (0.00 sec)mysql> SELECT sex ,COUNT(sex) FROM employee GROUP BY sex HAVING COUNT(sex)>=3;Empty set (0.00 sec)mysql> SELECT sex ,COUNT(sex) FROM employee GROUP BY sex HAVING COUNT(sex)>=2;+------+------------+| sex | COUNT(sex) |+------+------------+| man | 2 || woma | 2 |+------+------------+2 rows in set (0.00 sec)mysql> SELECT * FROM employee GROUP BY sex,id;+-----+------+----------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+----------+------+------+----------+| 1 | 1001 | zhangsan | 26 | man | beijing || 4 | 1004 | sky | 15 | man | sichuan || 2 | 1001 | lisi | 24 | woma | hubei || 3 | 1002 | wangwu | 25 | woma | hunan |+-----+------+----------+------+------+----------+4 rows in set (0.00 sec)mysql> SELECT * FROM employee GROUP BY id,sex;+-----+------+----------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+----------+------+------+----------+| 1 | 1001 | zhangsan | 26 | man | beijing || 2 | 1001 | lisi | 24 | woma | hubei || 3 | 1002 | wangwu | 25 | woma | hunan || 4 | 1004 | sky | 15 | man | sichuan |+-----+------+----------+------+------+----------+4 rows in set (0.00 sec)mysql> SELECT sex ,COUNT(sex) FROM employee GROUP BY sex WITH ROLLUP;+------+------------+| sex | COUNT(sex) |+------+------------+| man | 2 || woma | 2 || NULL | 4 |+------+------------+3 rows in set (0.00 sec)mysql> SELECT * FROM employee LIMIT 6;+-----+------+----------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+----------+------+------+----------+| 1 | 1001 | zhangsan | 26 | man | beijing || 2 | 1001 | lisi | 24 | woma | hubei || 3 | 1002 | wangwu | 25 | woma | hunan || 4 | 1004 | sky | 15 | man | sichuan |+-----+------+----------+------+------+----------+4 rows in set (0.00 sec)mysql> SELECT * FROM employee LIMIT 0,2;+-----+------+----------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+----------+------+------+----------+| 1 | 1001 | zhangsan | 26 | man | beijing || 2 | 1001 | lisi | 24 | woma | hubei |+-----+------+----------+------+------+----------+2 rows in set (0.00 sec)mysql> SELECT * FROM employee LIMIT 1,2;+-----+------+--------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+--------+------+------+----------+| 2 | 1001 | lisi | 24 | woma | hubei || 3 | 1002 | wangwu | 25 | woma | hunan |+-----+------+--------+------+------+----------+2 rows in set (0.00 sec)<12>集合函数:(1)COUNT:mysql> SELECT id, COUNT(*) FROM employee GROUP BY id;#以id分组然后统计数量+------+----------+| id | COUNT(*) |+------+----------+| 1001 | 2 || 1002 | 1 || 1004 | 1 |+------+----------+3 rows in set (0.00 sec)mysql> SELECT COUNT(*) FROM employee ;+----------+| COUNT(*) |+----------+| 4 |+----------+1 row in set (0.00 sec)(2)SUM:求和函数mysql> CREATE TABLE grade( -> num INT(10) NOT NULL, -> course VARCHAR(10) NOT NULL, -> score FLOAT -> );Query OK, 0 rows affected (0.00 sec)mysql> DESC grade;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| num | int(10) | NO | | NULL | || course | varchar(10) | NO | | NULL | || score | float | YES | | NULL | |+--------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> INSERT INTO grade VALUES(1001,'shuxue',80);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO grade VALUES(1001,'yuwen',90);Query OK, 1 row affected (0.01 sec)mysql> INSERT INTO grade VALUES(1001,'English',60);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO grade VALUES(1001,'computer',79);Query OK, 1 row affected (0.01 sec)mysql> INSERT INTO grade VALUES(1002,'shuxue',99);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO grade VALUES(1002,'yuwen',91);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO grade VALUES(1002,'english',77);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO grade VALUES(1002,'computer',78);Query OK, 1 row affected (0.01 sec)mysql> SELECT -> * FROM grade;+------+----------+-------+| num | course | score |+------+----------+-------+| 1001 | shuxue | 80 || 1001 | yuwen | 90 || 1001 | English | 60 || 1001 | computer | 79 || 1002 | shuxue | 99 || 1002 | yuwen | 91 || 1002 | english | 77 || 1002 | computer | 78 |+------+----------+-------+8 rows in set (0.00 sec)mysql> SELECT num, SUM(score) FROM grade WHERE num=1001;+------+------------+| num | SUM(score) |+------+------------+| 1001 | 309 |+------+------------+1 row in set (0.00 sec)mysql> SELECT num, SUM(score) FROM grade GROUP BY num;+------+------------+| num | SUM(score) |+------+------------+| 1001 | 309 || 1002 | 345 |+------+------------+2 rows in set (0.00 sec)mysql> SELECT course, SUM(score) FROM grade GROUP BY course;+----------+------------+| course | SUM(score) |+----------+------------+| computer | 157 || English | 137 || shuxue | 179 || yuwen | 181 |+----------+------------+4 rows in set (0.00 sec)(3)AVG:平均值mysql> SELECT num, AVG(score) FROM grade GROUP BY num;+------+------------+| num | AVG(score) |+------+------------+| 1001 | 77.25 || 1002 | 86.25 |+------+------------+2 rows in set (0.00 sec)(4)MAX:最大值：mysql> SELECT MAX(score) FROM grade;+------------+| MAX(score) |+------------+| 99 |+------------+1 row in set (0.00 sec)mysql> SELECT course, MAX(score) FROM grade GROUP BY course;+----------+------------+| course | MAX(score) |+----------+------------+| computer | 79 || English | 77 || shuxue | 99 || yuwen | 91 |+----------+------------+4 rows in set (0.00 sec)mysql> SELECT MAX(course) FROM grade ;#比较的是ASCLL码。+-------------+| MAX(course) |+-------------+| yuwen |+-------------+1 row in set (0.00 sec)(4)MIN:最小值与最大值相反。内连接查询：mysql> SELECT * FROM department;+------+------------+--------------------+------------------+| id | d_name | d_function | d_dis |+------+------------+--------------------+------------------+| 1001 | kaifabu | kaifachanpgongneng | asdfljkasfkljasf || 1002 | shichangbu | renliziyuan | asdfljkasfkljasf || 1002 | shichangbu | renliziyuan | asdfljkasfkljasf || 1004 | touzibu | faangongguan | asdfljkasfkljasf |+------+------------+--------------------+------------------+4 rows in set (0.00 sec)mysql> SELECT * FROM employee;+-----+------+----------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+----------+------+------+----------+| 1 | 1001 | zhangsan | 26 | man | beijing || 2 | 1001 | lisi | 24 | woma | hubei || 3 | 1002 | wangwu | 25 | woma | hunan || 4 | 1004 | sky | 15 | man | sichuan |+-----+------+----------+------+------+----------+4 rows in set (0.00 sec)mysql> SELECT department.id, d_name,d_function,d_dis,name,age,sex,homeaddr FRO department,employee WHERE department.id=employee.id;+------+------------+--------------------+------------------+----------+------+-----+----------+| id | d_name | d_function | d_dis | name | age |sex | homeaddr |+------+------------+--------------------+------------------+----------+------+-----+----------+| 1001 | kaifabu | kaifachanpgongneng | asdfljkasfkljasf | zhangsan | 26 |man | beijing || 1001 | kaifabu | kaifachanpgongneng | asdfljkasfkljasf | lisi | 24 |woma | hubei || 1002 | shichangbu | renliziyuan | asdfljkasfkljasf | wangwu | 25 |woma | hunan || 1002 | shichangbu | renliziyuan | asdfljkasfkljasf | wangwu | 25 |woma | hunan || 1004 | touzibu | faangongguan | asdfljkasfkljasf | sky | 15 |man | sichuan |+------+------------+--------------------+------------------+----------+------+-----+----------+5 rows in set (0.00 sec)mysql> SELECT department.id, d_name,name,age,sex FROM department,employee WHER department.id=employee.id;+------+------------+----------+------+------+| id | d_name | name | age | sex |+------+------------+----------+------+------+| 1001 | kaifabu | zhangsan | 26 | man || 1001 | kaifabu | lisi | 24 | woma || 1002 | shichangbu | wangwu | 25 | woma || 1002 | shichangbu | wangwu | 25 | woma || 1004 | touzibu | sky | 15 | man |+------+------------+----------+------+------+5 rows in set (0.00 sec)外连接查询：SELECT 属性名 FROM 表名1 LEFT|RIGHT JOIN 表名2 ON 表名1.属性名1=表名2.属性名2;mysql> SELECT department.id, d_name,name,age,sex FROM department LEFT JOIN employee ON department.id=employee.id;+------+------------+--------+------+------+| id | d_name | name | age | sex |+------+------------+--------+------+------+| 1001 | kaifabu | NULL | NULL | NULL || 1002 | shichangbu | wangwu | 25 | woma || 1002 | shichangbu | wangwu | 25 | woma || 1004 | touzibu | sky | 15 | man |+------+------------+--------+------+------+mysql> SELECT department.id, d_name,name,age,sex FROM department LEFT JOIN employee ON department.id=employee.id AND age>15;+------+------------+--------+------+------+| id | d_name | name | age | sex |+------+------------+--------+------+------+| 1001 | kaifabu | NULL | NULL | NULL || 1002 | shichangbu | wangwu | 25 | woma || 1002 | shichangbu | wangwu | 25 | woma || 1004 | touzibu | NULL | NULL | NULL |+------+------------+--------+------+------+4 rows in set (0.00 sec)mysql> SELECT num,name,age,sex,department.id,d_name,d_function,d_dis -> FROM employee RIGHT JOIN department ON employee.id=department.id;+------+--------+------+------+------+------------+--------------------+------------------+| num | name | age | sex | id | d_name | d_function | d_dis |+------+--------+------+------+------+------------+--------------------+------------------+| NULL | NULL | NULL | NULL | 1001 | kaifabu | kaifachanpgongneng | asdfljkasfkljasf || 3 | wangwu | 25 | woma | 1002 | shichangbu | renliziyuan | asdfljkasfkljasf || 3 | wangwu | 25 | woma | 1002 | shichangbu | renliziyuan | asdfljkasfkljasf || 4 | sky | 15 | man | 1004 | touzibu | faangongguan | asdfljkasfkljasf |+------+--------+------+------+------+------------+--------------------+------------------+4 rows in set (0.00 sec)3.使用聚合函数查询数据。4.多表上联合查询。5.子查询。mysql> SELECT * FROM department;+------+------------+--------------------+------------------+| id | d_name | d_function | d_dis |+------+------------+--------------------+------------------+| 1001 | kaifabu | kaifachanpgongneng | asdfljkasfkljasf || 1002 | shichangbu | renliziyuan | asdfljkasfkljasf || 1002 | shichangbu | renliziyuan | asdfljkasfkljasf || 1004 | touzibu | faangongguan | asdfljkasfkljasf |+------+------------+--------------------+------------------+4 rows in set (0.00 sec)mysql> SELECT * FROM employee;+-----+------+--------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+--------+------+------+----------+| 3 | 1002 | wangwu | 25 | woma | hunan || 4 | 1004 | sky | 15 | man | sichuan |+-----+------+--------+------+------+----------+2 rows in set (0.00 sec)mysql> SELECT * FROM employee WHERE id IN(SELECT id FROM department);+-----+------+--------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+--------+------+------+----------+| 3 | 1002 | wangwu | 25 | woma | hunan || 4 | 1004 | sky | 15 | man | sichuan |+-----+------+--------+------+------+----------+2 rows in set (0.00 sec)mysql> SELECT * FROM employee WHERE id NOT IN(SELECT id FROM department);Empty set (0.00 sec)<1>带比较运算符的子查询：mysql> CREATE TABLE computer_stu( -> id INT PRIMARY KEY, -> name VARCHAR(20), -> score FLOAT -> );Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO computer_stu VALUES(1001,'lily',85);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO computer_stu VALUES(1002,'Tom',91);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO computer_stu VALUES(1003,'Jim',87);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO computer_stu VALUES(1004,'Aric',77);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO computer_stu VALUES(1005,'Lucy',65);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO computer_stu VALUES(1006,'Andy',99);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO computer_stu VALUES(1007,'Jack',70);Query OK, 1 row affected (0.00 sec)mysql> SELECT -> * FROM computer_stu;+------+------+-------+| id | name | score |+------+------+-------+| 1001 | lily | 85 || 1002 | Tom | 91 || 1003 | Jim | 87 || 1004 | Aric | 77 || 1005 | Lucy | 65 || 1006 | Andy | 99 || 1007 | Jack | 70 |+------+------+-------+7 rows in set (0.00 sec)mysql> CREATE TABLE scoship( -> level INT PRIMARY KEY, -> score INT -> );Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO scoship VALUES(1,90);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO scoship VALUES(2,80);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO scoship VALUES(3,70);Query OK, 1 row affected (0.00 sec)mysql> SELECT * FROM scoship;+-------+-------+| level | score |+-------+-------+| 1 | 90 || 2 | 80 || 3 | 70 |+-------+-------+3 rows in set (0.00 sec)mysql> SELECT id,name,score FROM computer_stu WHERE score >= (SELECT score FROMscoship WHERE level=1);+------+------+-------+| id | name | score |+------+------+-------+| 1002 | Tom | 91 || 1006 | Andy | 99 |+------+------+-------+2 rows in set (0.00 sec)<2>EXISTS 关键字的子查询：mysql> SELECT * FROM computer_stu WHERE EXISTS (SELECT Level FROM scoship WHEREscore=90);+------+------+-------+| id | name | score |+------+------+-------+| 1001 | lily | 85 || 1002 | Tom | 91 || 1003 | Jim | 87 || 1004 | Aric | 77 || 1005 | Lucy | 65 || 1006 | Andy | 99 || 1007 | Jack | 70 |+------+------+-------+7 rows in set (0.00 sec)<3>ANY (满足其中的任一条件)mysql> SELECT * FROM computer_stu WHERE score >= ANY (SELECT score FROM scoship);+------+------+-------+| id | name | score |+------+------+-------+| 1001 | lily | 85 || 1002 | Tom | 91 || 1003 | Jim | 87 || 1004 | Aric | 77 || 1006 | Andy | 99 || 1007 | Jack | 70 |+------+------+-------+6 rows in set (0.00 sec)<4>ALLmysql> SELECT * FROM computer_stu WHERE score >= ALL (SELECT score FROM scoship);+------+------+-------+| id | name | score |+------+------+-------+| 1002 | Tom | 91 || 1006 | Andy | 99 |+------+------+-------+2 rows in set (0.00 sec)6.合并查询结果。mysql> SELECT * FROM department;+------+------------+--------------------+------------------+| id | d_name | d_function | d_dis |+------+------------+--------------------+------------------+| 1001 | kaifabu | kaifachanpgongneng | asdfljkasfkljasf || 1002 | shichangbu | renliziyuan | asdfljkasfkljasf || 1002 | shichangbu | renliziyuan | asdfljkasfkljasf || 1004 | touzibu | faangongguan | asdfljkasfkljasf |+------+------------+--------------------+------------------+4 rows in set (0.00 sec)mysql> SELECT * FROM employee;+-----+------+----------+------+------+----------+| num | id | name | age | sex | homeaddr |+-----+------+----------+------+------+----------+| 1 | 1001 | zhangsan | 26 | man | beijing || 2 | 1001 | lisi | 24 | woma | hubei || 3 | 1002 | wangwu | 25 | woma | hunan || 4 | 1004 | sky | 15 | man | sichuan |+-----+------+----------+------+------+----------+4 rows in set (0.00 sec)<5>UNION (UNION ALL)mysql> SELECT id FROM department UNION SELECT id FROM employee;+------+| id |+------+| 1001 || 1002 || 1004 |+------+3 rows in set (0.00 sec)mysql> SELECT id FROM department UNION ALL SELECT id FROM employee;+------+| id |+------+| 1001 || 1002 || 1002 || 1004 || 1002 || 1004 |+------+6 rows in set (0.00 sec)7.为表和字段取别名。mysql> SELECT * FROM department d WHERE d.id=1002;+------+------------+-------------+------------------+| id | d_name | d_function | d_dis |+------+------------+-------------+------------------+| 1002 | shichangbu | renliziyuan | asdfljkasfkljasf || 1002 | shichangbu | renliziyuan | asdfljkasfkljasf |+------+------------+-------------+------------------+2 rows in set (0.00 sec)mysql> SELECT d_name AS department_name, id AS department_id FROM department;+-----------------+---------------+| department_name | department_id |+-----------------+---------------+| kaifabu | 1001 || shichangbu | 1002 || shichangbu | 1002 || touzibu | 1004 |+-----------------+---------------+4 rows in set (0.00 sec)8.使用正则表达式查询(REGEXP)。<1> ^匹配字符串开始的部分;<2> $匹配字符串结束的部分;<3> .代表字符串的任意一个部分，包括回车和换行<4> [字符集合]匹配'字符集合'中的任何一个字符<5> [^字符集合]匹配除了'字符集合'中的任何一个字符<6> $1|$2|$3匹配$1,$2,$3中的任意一个字符串<7> * 代表多个该符号之前的字符，包括0和1个<8> + 代表多个该符号之前的字符，包括1个<9> 字符串[N]字符串出现N次<10> 字符串[M,N] 字符串出现最少M次最多N次mysql> SELECT -> * FROM computer_stu;+------+------+-------+| id | name | score |+------+------+-------+| 1001 | lily | 85 || 1002 | Tom | 91 || 1003 | Jim | 87 || 1004 | Aric | 77 || 1005 | Lucy | 65 || 1006 | Andy | 99 || 1007 | Jack | 70 |+------+------+-------+7 rows in set (0.00 sec)mysql> SELECT * FROM computer_stu WHERE name REGEXP '^l';+------+------+-------+| id | name | score |+------+------+-------+| 1001 | lily | 85 || 1005 | Lucy | 65 |+------+------+-------+2 rows in set (0.00 sec)mysql> SELECT * FROM computer_stu WHERE name REGEXP 'y$';+------+------+-------+| id | name | score |+------+------+-------+| 1001 | lily | 85 || 1005 | Lucy | 65 || 1006 | Andy | 99 |+------+------+-------+3 rows in set (0.00 sec)mysql> SELECT * FROM computer_stu WHERE name REGEXP '^l..y$';+------+------+-------+| id | name | score |+------+------+-------+| 1001 | lily | 85 || 1005 | Lucy | 65 |+------+------+-------+2 rows in set (0.00 sec)mysql> SELECT * FROM computer_stu WHERE name REGEXP '[k]';+------+------+-------+| id | name | score |+------+------+-------+| 1007 | Jack | 70 |+------+------+-------+1 row in set (0.00 sec)mysql> SELECT * FROM computer_stu WHERE name REGEXP '[y]';+------+------+-------+| id | name | score |+------+------+-------+| 1001 | lily | 85 || 1005 | Lucy | 65 || 1006 | Andy | 99 |+------+------+-------+3 rows in set (0.00 sec)mysql> SELECT * FROM computer_stu WHERE name REGEXP '[^y]';+------+------+-------+| id | name | score |+------+------+-------+| 1001 | lily | 85 || 1002 | Tom | 91 || 1003 | Jim | 87 || 1004 | Aric | 77 || 1005 | Lucy | 65 || 1006 | Andy | 99 || 1007 | Jack | 70 |+------+------+-------+7 rows in set (0.00 sec)mysql> SELECT * FROM computer_stu WHERE name REGEXP 'lily|kibi';+------+------+-------+| id | name | score |+------+------+-------+| 1001 | lily | 85 |+------+------+-------+1 row in set (0.00 sec)mysql> SELECT * FROM computer_stu WHERE name REGEXP '[0-9a-c]';+------+------+-------+| id | name | score |+------+------+-------+| 1004 | Aric | 77 || 1005 | Lucy | 65 || 1006 | Andy | 99 || 1007 | Jack | 70 |+------+------+-------+4 rows in set (0.00 sec)mysql> SELECT * FROM computer_stu WHERE name REGEXP 'a*c';#a最少出现0次以上+------+------+-------+| id | name | score |+------+------+-------+| 1004 | Aric | 77 || 1005 | Lucy | 65 || 1007 | Jack | 70 |+------+------+-------+3 rows in set (0.00 sec)mysql> SELECT * FROM computer_stu WHERE name REGEXP 'a+c';#a最少出现0次以上+------+------+-------+| id | name | score |+------+------+-------+| 1007 | Jack | 70 |+------+------+-------+1 row in set (0.00 sec)mysql> SELECT * FROM computer_stu WHERE name REGEXP 'l{2}';#L字符最少出现2次Empty set (0.00 sec)mysql> SELECT * FROM computer_stu WHERE name REGEXP 'l{1}';#L字符最少出现1次+------+------+-------+| id | name | score |+------+------+-------+| 1001 | lily | 85 || 1005 | Lucy | 65 |+------+------+-------+2 rows in set (0.00 sec)mysql> SELECT * FROM computer_stu WHERE name REGEXP 'l{1,2}';#L字符最少出现1次，最多出现2次+------+------+-------+| id | name | score |+------+------+-------+| 1001 | lily | 85 || 1005 | Lucy | 65 |+------+------+-------+2 rows in set (0.00 sec) 0 0
- mysql study 7
- MySql study notes [1]
- MySql study note 1
- Mysql study note 3
- mysql study<六>
- mysql study 1
- MySQL Study之--MySQL约束
- MySQL Study之--MySQL 备份
- MySQL 5.0 Certification Study Guide
- MySql study note 2 ---mysqladmin
- MySql Study之--MySql日志管理
- MySql Study之--MySql日志管理
- MySQL Study之--MySQL体系结构深入解析
- MySQL Study之--MySQL innodb存储架构
- MySQL Study之--MySQL工具mysqlshow
- MySQL Study之--MySQL schema_information数据库
- MySQL Study之--MySQL日期函数
- MySQL Study之--MySQL存储过程循环
- LinearLayout布局
- FrameLayout
- 进程_查看堆大小，句柄，进程数限制
- Sqlserver表值函数
- Tomcat6w.exe 运行 提示 指定的服务未安装 unable to open the service 'tomcat6'解决方案
- mysql study 7
- XZ压缩最新压缩率之王
- uCOSII在BC45的仿真
- 传智学习日志篇：四
- AbsoluteLayout
- A*算法简介
- intent
- 如何在eclipse jee中创建Maven project并且转换为Dynamic web projec
- intent1
