數(shù)據(jù)庫(kù)操作
數(shù)據(jù)庫(kù)備份與恢復(fù)
備份mydb數(shù)據(jù)庫(kù)，使用mydb.sql文件存儲(chǔ)
[root@localhost bin]# mysqldump -u root -p mydb > mydb.sql
備份mydb數(shù)據(jù)庫(kù)中的stu表，使用mydb.sql文件存儲(chǔ)
[root@localhost bin]# mysqldump -u root -p mydb stu > mydb_stu.sql
恢復(fù)mydb數(shù)據(jù)信息（條件是mydb庫(kù)存在）
[root@localhost bin]# mysql -u root -p mydb < mydb.sql
查看當(dāng)前MySQL下的所有數(shù)據(jù)庫(kù)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
創(chuàng)建一個(gè)mydb2的數(shù)據(jù)庫(kù)
mysql> create database mydb2;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb2 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
刪除數(shù)據(jù)庫(kù)mydb2
mysql> drop database mydb2;
Query OK, 0 rows affected (0.04 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
創(chuàng)建一個(gè)mydb數(shù)據(jù)庫(kù)
mysql> create database mydb;
Query OK, 1 row affected (0.00 sec)
再次常見(jiàn)mydb數(shù)據(jù)庫(kù)會(huì)報(bào)錯(cuò)
mysql> create database mydb;
ERROR 1007 (HY000): Can''t create database 'mydb'; database exists
嘗試創(chuàng)建mydb數(shù)據(jù)庫(kù)（若已存在則會(huì)報(bào)一個(gè)警告，不會(huì)報(bào)Error錯(cuò)誤）
mysql> create database if not exists mydb;
Query OK, 1 row affected, 1 warning (0.00 sec)
查看mydb的建庫(kù)語(yǔ)句
mysql> show create database mydb;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| mydb | CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create database mydb\G
*************************** 1. row ***************************
Database: mydb
Create Database: CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)
查看當(dāng)前所在數(shù)據(jù)庫(kù)位置：NULL表示沒(méi)有在任何數(shù)據(jù)庫(kù)中
mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
選擇進(jìn)入mydb數(shù)據(jù)庫(kù)
mysql> use mydb;
Database changed
查看當(dāng)前所在數(shù)據(jù)庫(kù)的位置
mysql> select database();
+------------+
| database() |
+------------+
| mydb |
+------------+
1 row in set (0.00 sec)
查看當(dāng)前數(shù)據(jù)庫(kù)中的所有表
mysql> show tables;
Empty set (0.00 sec)
創(chuàng)建一個(gè)uu表，內(nèi)有三個(gè)字段id，name和age
mysql> create table uu(id int,name varchar(16),age int);
Query OK, 0 rows affected (0.09 sec)
查看uu表的建表語(yǔ)句
mysql> show create table uu\G
*************************** 1. row ***************************
Table: uu
Create Table: CREATE TABLE `uu` (
`id` int(11) DEFAULT NULL,
`name` varchar(16) DEFAULT NULL,
`age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
刪除uu表
mysql> drop table uu;
Query OK, 0 rows affected (0.02 sec)
添加一個(gè)數(shù)據(jù)，給定所有字段，所有的值
mysql> insert into uu(id,name,age) values(1,'zhangsan',20);
Query OK, 1 row affected (0.01 sec)
mysql> insert into uu(id,name,age) values(2,'lisi',22);
Query OK, 1 row affected (0.02 sec)
不指定字段，添加值，值按默認(rèn)順序?qū)?/h5>
mysql> insert into uu values(3,'wangwu',25);
Query OK, 1 row affected (0.01 sec)
批量添加值
mysql> insert into uu values(4,'zhaoliu',21),
-> (5,'xiaoli',22),
-> (6,'xiaozhang',19);
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
將id值為4的信息age改為30（修改）
mysql> update uu set age=30 where id=4;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from uu;
+------+-----------+------+
| id | name | age |
+------+-----------+------+
| 1 | zhangsan | 20 |
| 2 | lisi | 22 |
| 3 | wangwu | 25 |
| 4 | zhaoliu | 30 |
| 5 | xiaoli | 22 |
| 6 | xiaozhang | 19 |
+------+-----------+------+
6 rows in set (0.00 sec)
刪除id為5的所有信息
mysql> delete from uu where id=5;
Query OK, 1 row affected (0.01 sec)
修改表結(jié)構(gòu)實(shí)例
mysql> show create table tt\G
*************************** 1. row ***************************
Table: tt
Create Table: CREATE TABLE `tt` (
`id` int(11) DEFAULT NULL,
`name` varchar(16) DEFAULT NULL,
`age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> desc tt;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(16) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
1. 在tt表末尾添加一個(gè)phone字段，類(lèi)型varchar(11)，無(wú)其他約束
mysql> alter table tt add phone varchar(11);
2. 在tt表中age字段后添加一個(gè)address字段，類(lèi)型varchar(100) ，無(wú)其他約束
mysql> alter table tt add address varchar(100) after age;
3. 在tt表首位插入一個(gè)mm字段，類(lèi)型int
mysql> alter table tt add mm int first;
mysql> desc tt;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| mm | int(11) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | varchar(16) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
| phone | varchar(11) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
4. 刪除tt表的mm字段
mysql> alter table tt drop mm;
5. 修改字段：tt表age字段類(lèi)型改為tinyint類(lèi)型，unsigned not null default 20
mysql> alter table tt modify age tinyint unsigned not null default 20;
6. 修改name字段名為username
mysql> alter table tt change name username varchar(16);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tt;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| username | varchar(16) | YES | | NULL | |
| age | tinyint(3) unsigned | NO | | 20 | |
| address | varchar(100) | YES | | NULL | |
| phone | varchar(11) | YES | | NULL | |
+----------+---------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
數(shù)據(jù)的增、刪、改，查
mysql> insert into uu values(3,'wangwu',25);
Query OK, 1 row affected (0.01 sec)
mysql> insert into uu values(4,'zhaoliu',21),
-> (5,'xiaoli',22),
-> (6,'xiaozhang',19);
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> update uu set age=30 where id=4;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from uu;
+------+-----------+------+
| id | name | age |
+------+-----------+------+
| 1 | zhangsan | 20 |
| 2 | lisi | 22 |
| 3 | wangwu | 25 |
| 4 | zhaoliu | 30 |
| 5 | xiaoli | 22 |
| 6 | xiaozhang | 19 |
+------+-----------+------+
6 rows in set (0.00 sec)
mysql> delete from uu where id=5;
Query OK, 1 row affected (0.01 sec)
mysql> show create table tt\G
*************************** 1. row ***************************
Table: tt
Create Table: CREATE TABLE `tt` (
`id` int(11) DEFAULT NULL,
`name` varchar(16) DEFAULT NULL,
`age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> desc tt;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(16) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table tt add phone varchar(11);
mysql> alter table tt add address varchar(100) after age;
mysql> alter table tt add mm int first;
mysql> desc tt;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| mm | int(11) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | varchar(16) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
| phone | varchar(11) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> alter table tt drop mm;
mysql> alter table tt modify age tinyint unsigned not null default 20;
mysql> alter table tt change name username varchar(16);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tt;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| username | varchar(16) | YES | | NULL | |
| age | tinyint(3) unsigned | NO | | 20 | |
| address | varchar(100) | YES | | NULL | |
| phone | varchar(11) | YES | | NULL | |
+----------+---------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
=============
mysql> desc stu;
+---------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(8) | NO | UNI | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| sex | enum('w','m') | NO | | m | |
| classid | char(8) | YES | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
5 rows in set (0.03 sec)
1. 添加：
格式：insert into 表名[(字段列表)] values(值列表)[,(值列表)...]
標(biāo)準(zhǔn)添加sql，指定所有字段，給定所有的值
mysql> insert into stu(id,name,age,sex,classid) values(1,'zhangsan',20,'m','python03');
指定部分字段，給定部分值添加
mysql> insert into stu(name,age,classid) values('lisi',22,'python02');
Query OK, 1 row affected (0.02 sec)
不指定字段添加值（注意順序和個(gè)數(shù)）
mysql> insert into stu values(null,'wangwu',25,'w','python03');
Query OK, 1 row affected (0.01 sec)
mysql> select * from stu;
+----+----------+------+-----+----------+
| id | name | age | sex | classid |
+----+----------+------+-----+----------+
| 1 | zhangsan | 20 | m | python03 |
| 2 | lisi | 22 | m | python02 |
| 3 | wangwu | 25 | w | python03 |
+----+----------+------+-----+----------+
3 rows in set (0.00 sec)
批量添加值
mysql> insert into stu values
-> (null,'xiaoli',28,'m','python02'),
-> (null,'xiaozhang',21,'w','python01'),
-> (null,'xiaozhao',27,'m','python03'),
-> (null,'uu01',18,'m','python03');
Query OK, 4 rows affected, 1 warning (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 1
mysql> select * from stu;
+----+----------+------+-----+----------+
| id | name | age | sex | classid |
+----+----------+------+-----+----------+
| 1 | zhangsan | 20 | m | python03 |
| 2 | lisi | 22 | m | python02 |
| 3 | wangwu | 25 | w | python03 |
| 4 | xiaoli | 28 | m | python02 |
| 5 | xiaozhan | 21 | w | python01 |
| 6 | xiaozhao | 27 | m | python03 |
| 7 | uu01 | 18 | m | python03 |
| 8 | uu02 | NULL | m | NULL |
| 9 | uu03 | NULL | m | NULL |
| 10 | uu04 | NULL | m | NULL |
| 11 | uu06 | NULL | m | NULL |
| 12 | uu08 | NULL | m | NULL |
+----+----------+------+-----+----------+
12 rows in set (0.00 sec)
2. 刪除數(shù)據(jù)
格式： delete from 表名 [where 條件 [分組、排序、limit]]
刪除id為20的信息
mysql> delete from stu where id=20;
Query OK, 0 rows affected (0.00 sec)
刪除id大于100的所有信息
mysql> delete from stu where id>100;
Query OK, 0 rows affected (0.00 sec)
刪除id是100~200的所有信息
mysql> delete from stu where id between 100 and 200;
等價(jià)于
mysql> delete from stu where id>=100 and id<=200;
Query OK, 0 rows affected (0.00 sec)
刪除性別為w，年齡大于25的所有信息
mysql> delete from stu where sex='w' and age>25;
Query OK, 0 rows affected (0.00 sec)
3. 修改數(shù)據(jù)
格式：update 表名 set 字段名1=值1[,字段名2=值2,...] [where 條件...]
將id為9和11 的年齡age改為23，班級(jí)classid改為python03
mysql> update stu set age=23,classid='python03' where id in(9,11);
Query OK, 2 rows affected (0.08 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from stu;
+----+----------+------+-----+----------+
| id | name | age | sex | classid |
+----+----------+------+-----+----------+
| 1 | zhangsan | 20 | m | python03 |
| 2 | lisi | 22 | m | python02 |
| 3 | wangwu | 25 | w | python03 |
| 4 | xiaoli | 28 | m | python02 |
| 5 | xiaozhan | 21 | w | python01 |
| 6 | xiaozhao | 27 | m | python03 |
| 7 | uu01 | 18 | m | python03 |
| 8 | uu02 | NULL | m | NULL |
| 9 | uu03 | 23 | m | python03 |
| 10 | uu04 | NULL | m | NULL |
| 11 | uu06 | 23 | m | python03 |
| 12 | uu08 | NULL | m | NULL |
+----+----------+------+-----+----------+
12 rows in set (0.00 sec)
將id為8,10和12 的年齡age改為26，班級(jí)classid改為python02
mysql> update stu set age=26,classid='python02' where id in(8,10,12);
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from stu;
+----+----------+------+-----+----------+
| id | name | age | sex | classid |
+----+----------+------+-----+----------+
| 1 | zhangsan | 20 | m | python03 |
| 2 | lisi | 22 | m | python02 |
| 3 | wangwu | 25 | w | python03 |
| 4 | xiaoli | 28 | m | python02 |
| 5 | xiaozhan | 21 | w | python01 |
| 6 | xiaozhao | 27 | m | python03 |
| 7 | uu01 | 18 | m | python03 |
| 8 | uu02 | 26 | m | python02 |
| 9 | uu03 | 23 | m | python03 |
| 10 | uu04 | 26 | m | python02 |
| 11 | uu06 | 23 | m | python03 |
| 12 | uu08 | 26 | m | python02 |
+----+----------+------+-----+----------+
12 rows in set (0.00 sec)
mysql> update stu set sex='w' where id in(8,11,12);
Query OK, 3 rows affected (0.05 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from stu;
+----+----------+------+-----+----------+
| id | name | age | sex | classid |
+----+----------+------+-----+----------+
| 1 | zhangsan | 20 | m | python03 |
| 2 | lisi | 22 | m | python02 |
| 3 | wangwu | 25 | w | python03 |
| 4 | xiaoli | 28 | m | python02 |
| 5 | xiaozhan | 21 | w | python01 |
| 6 | xiaozhao | 27 | m | python03 |
| 7 | uu01 | 18 | m | python03 |
| 8 | uu02 | 26 | w | python02 |
| 9 | uu03 | 23 | m | python03 |
| 10 | uu04 | 26 | m | python02 |
| 11 | uu06 | 23 | w | python03 |
| 12 | uu08 | 26 | w | python02 |
+----+----------+------+-----+----------+
12 rows in set (0.00 sec)
mysql>
mysql> update stu set age=age-2 where id=15;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
4. 查詢
mysql> select * from stu;
+----+----------+------+-----+----------+
| id | name | age | sex | classid |
+----+----------+------+-----+----------+
| 1 | zhangsan | 20 | m | python03 |
| 2 | lisi | 22 | m | python02 |
| 3 | wangwu | 25 | w | python03 |
| 4 | xiaoli | 28 | m | python02 |
| 5 | xiaozhan | 21 | w | python01 |
| 6 | xiaozhao | 27 | m | python03 |
| 7 | uu01 | 18 | m | python03 |
| 8 | uu02 | 26 | w | python02 |
| 9 | uu03 | 23 | m | python03 |
| 10 | uu04 | 26 | m | python02 |
| 11 | uu06 | 23 | w | python03 |
| 12 | uu08 | 26 | w | python02 |
| 13 | qq06 | NULL | m | NULL |
| 14 | qq01 | 31 | m | python01 |
| 15 | qq03 | 33 | m | python01 |
+----+----------+------+-----+----------+
15 rows in set (0.00 sec)
mysql> select id,name,age from stu;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 20 |
| 2 | lisi | 22 |
| 3 | wangwu | 25 |
| 4 | xiaoli | 28 |
| 5 | xiaozhan | 21 |
| 6 | xiaozhao | 27 |
| 7 | uu01 | 18 |
| 8 | uu02 | 26 |
| 9 | uu03 | 23 |
| 10 | uu04 | 26 |
| 11 | uu06 | 23 |
| 12 | uu08 | 26 |
| 13 | qq06 | NULL |
| 14 | qq01 | 31 |
| 15 | qq03 | 33 |
+----+----------+------+
15 rows in set (0.00 sec)
mysql> select id,name as username,age from stu;
+----+----------+------+
| id | username | age |
+----+----------+------+
| 1 | zhangsan | 20 |
| 2 | lisi | 22 |
| 3 | wangwu | 25 |
| 4 | xiaoli | 28 |
| 5 | xiaozhan | 21 |
| 6 | xiaozhao | 27 |
| 7 | uu01 | 18 |
| 8 | uu02 | 26 |
| 9 | uu03 | 23 |
| 10 | uu04 | 26 |
| 11 | uu06 | 23 |
| 12 | uu08 | 26 |
| 13 | qq06 | NULL |
| 14 | qq01 | 31 |
| 15 | qq03 | 33 |
+----+----------+------+
15 rows in set (0.01 sec)
mysql> select id,name username,age from stu;
+----+----------+------+
| id | username | age |
+----+----------+------+
| 1 | zhangsan | 20 |
| 2 | lisi | 22 |
| 3 | wangwu | 25 |
| 4 | xiaoli | 28 |
| 5 | xiaozhan | 21 |
| 6 | xiaozhao | 27 |
| 7 | uu01 | 18 |
| 8 | uu02 | 26 |
| 9 | uu03 | 23 |
| 10 | uu04 | 26 |
| 11 | uu06 | 23 |
| 12 | uu08 | 26 |
| 13 | qq06 | NULL |
| 14 | qq01 | 31 |
| 15 | qq03 | 33 |
+----+----------+------+
15 rows in set (0.00 sec)
mysql> select *,age+5 age5 from stu;
+----+----------+------+-----+----------+------+
| id | name | age | sex | classid | age5 |
+----+----------+------+-----+----------+------+
| 1 | zhangsan | 20 | m | python03 | 25 |
| 2 | lisi | 22 | m | python02 | 27 |
| 3 | wangwu | 25 | w | python03 | 30 |
| 4 | xiaoli | 28 | m | python02 | 33 |
| 5 | xiaozhan | 21 | w | python01 | 26 |
| 6 | xiaozhao | 27 | m | python03 | 32 |
| 7 | uu01 | 18 | m | python03 | 23 |
| 8 | uu02 | 26 | w | python02 | 31 |
| 9 | uu03 | 23 | m | python03 | 28 |
| 10 | uu04 | 26 | m | python02 | 31 |
| 11 | uu06 | 23 | w | python03 | 28 |
| 12 | uu08 | 26 | w | python02 | 31 |
| 13 | qq06 | NULL | m | NULL | NULL |
| 14 | qq01 | 31 | m | python01 | 36 |
| 15 | qq03 | 33 | m | python01 | 38 |
+----+----------+------+-----+----------+------+
15 rows in set (0.01 sec)
mysql> select *,'beijing' as city from stu;
+----+----------+------+-----+----------+---------+
| id | name | age | sex | classid | city |
+----+----------+------+-----+----------+---------+
| 1 | zhangsan | 20 | m | python03 | beijing |
| 2 | lisi | 22 | m | python02 | beijing |
| 3 | wangwu | 25 | w | python03 | beijing |
| 4 | xiaoli | 28 | m | python02 | beijing |
| 5 | xiaozhan | 21 | w | python01 | beijing |
| 6 | xiaozhao | 27 | m | python03 | beijing |
| 7 | uu01 | 18 | m | python03 | beijing |
| 8 | uu02 | 26 | w | python02 | beijing |
| 9 | uu03 | 23 | m | python03 | beijing |
| 10 | uu04 | 26 | m | python02 | beijing |
| 11 | uu06 | 23 | w | python03 | beijing |
| 12 | uu08 | 26 | w | python02 | beijing |
| 13 | qq06 | NULL | m | NULL | beijing |
| 14 | qq01 | 31 | m | python01 | beijing |
| 15 | qq03 | 33 | m | python01 | beijing |
+----+----------+------+-----+----------+---------+
15 rows in set (0.00 sec)
mysql> select *,'beijing' city from stu;
+----+----------+------+-----+----------+---------+
| id | name | age | sex | classid | city |
+----+----------+------+-----+----------+---------+
| 1 | zhangsan | 20 | m | python03 | beijing |
| 2 | lisi | 22 | m | python02 | beijing |
| 3 | wangwu | 25 | w | python03 | beijing |
| 4 | xiaoli | 28 | m | python02 | beijing |
| 5 | xiaozhan | 21 | w | python01 | beijing |
| 6 | xiaozhao | 27 | m | python03 | beijing |
| 7 | uu01 | 18 | m | python03 | beijing |
| 8 | uu02 | 26 | w | python02 | beijing |
| 9 | uu03 | 23 | m | python03 | beijing |
| 10 | uu04 | 26 | m | python02 | beijing |
| 11 | uu06 | 23 | w | python03 | beijing |
| 12 | uu08 | 26 | w | python02 | beijing |
| 13 | qq06 | NULL | m | NULL | beijing |
| 14 | qq01 | 31 | m | python01 | beijing |
| 15 | qq03 | 33 | m | python01 | beijing |
+----+----------+------+-----+----------+---------+
15 rows in set (0.01 sec)
mysql> select concat(classid,":",name) from stu;
+--------------------------+
| concat(classid,":",name) |
+--------------------------+
| python03:zhangsan |
| python02:lisi |
| python03:wangwu |
| python02:xiaoli |
| python01:xiaozhan |
| python03:xiaozhao |
| python03:uu01 |
| python02:uu02 |
| python03:uu03 |
| python02:uu04 |
| python03:uu06 |
| python02:uu08 |
| NULL |
| python01:qq01 |
| python01:qq03 |
+--------------------------+
15 rows in set (0.00 sec)
where條件查詢
1. 查詢班級(jí)為python03期的所有學(xué)生信息
mysql> select * from stu where classid='python03';
2. 查詢班級(jí)為python03期，并且性別為m的所有學(xué)生信息
mysql> select * from stu where classid='python03' and sex="m";
3. 查詢年齡大于20，性別為w的所有信息
mysql> select * from stu where age>20 and sex='w';
4. 查詢年齡是20~25的所有信息
mysql> select * from stu where age>=20 and age<=25;
mysql> select * from stu where age between 20 and 25;
5. 查詢年齡不在20~25的學(xué)生信息
mysql> select * from stu where age<20 or age>25;
mysql> select * from stu where age not between 20 and 25;
6. 查詢id號(hào)為1,3,5,7,9的學(xué)生信息
mysql> select * from stu where id in(1,3,5,7,9);
7. 查詢classid不為null所有信息
mysql> select * from stu where classid is not null;
8. 查詢班級(jí)為python01和python02期所有男生（sex='m'）信息
mysql> select * from stu where (classid='python01' or classid='python02') and sex='m';
mysql> select * from stu where classid in('python01','python02') and sex='m';
9. 查詢姓名中含有an子串的所有信息
like 模糊查詢，支持倆個(gè)特殊符號(hào)：'%'和'_' %表示任意數(shù)量的任意字符， _表示任意一位字符
mysql> select * from stu where name like '%an%';
mysql> select * from stu where name regexp 'an';
10. 查詢姓名是有4位任意小寫(xiě)字符或數(shù)字構(gòu)成的信息
mysql> select * from stu where name like '____';
mysql> select * from stu where name regexp '^[a-z0-9]{4}$';
統(tǒng)計(jì)函數(shù)（聚合函數(shù)）max() min() sum() avg() count()
獲取最大年齡，最小年齡，年齡總和，平均年齡，總計(jì)條數(shù)
mysql> select max(age),min(age),sum(age),avg(age),count(id) from stu;
+----------+----------+----------+----------+-----------+
| max(age) | min(age) | sum(age) | avg(age) | count(id) |
+----------+----------+----------+----------+-----------+
| 33 | 18 | 371 | 24.7333 | 15 |
+----------+----------+----------+----------+-----------+
1 row in set (0.01 sec)
group by 字段名 分組
按性別sex分組，并統(tǒng)計(jì)人數(shù)
mysql> select sex,count(*) from stu group by sex;
+-----+----------+
| sex | count(*) |
+-----+----------+
| w | 5 |
| m | 10 |
+-----+----------+
2 rows in set (0.00 sec)
按班級(jí)分組統(tǒng)計(jì)每個(gè)班級(jí)的人數(shù)（排除班級(jí)信息為null的數(shù)據(jù)）
mysql> select classid,count(*) from stu where classid is not null group by classid;
+----------+----------+
| classid | count(*) |
+----------+----------+
| python01 | 3 |
| python02 | 5 |
| python03 | 6 |
+----------+----------+
3 rows in set (0.00 sec)
按班級(jí)分組，并統(tǒng)計(jì)每個(gè)班級(jí)的男生和女生人數(shù)（排除班級(jí)信息為null的數(shù)據(jù)）
mysql> select classid,sex,count(*) from stu where classid is not null group by classid,sex;
+----------+-----+----------+
| classid | sex | count(*) |
+----------+-----+----------+
| python01 | w | 1 |
| python01 | m | 2 |
| python02 | w | 2 |
| python02 | m | 3 |
| python03 | w | 2 |
| python03 | m | 4 |
+----------+-----+----------+
6 rows in set (0.00 sec)
在上面的查詢中，加入過(guò)濾條件(人數(shù)大于等于3的信息）
mysql> select classid,sex,count(*) num from stu where classid is not null group by classid,sex having num>=3;
+----------+-----+-----+
| classid | sex | num |
+----------+-----+-----+
| python02 | m | 3 |
| python03 | m | 4 |
+----------+-----+-----+
2 rows in set (0.00 sec)
排序：order by 字段名 asc(默認(rèn)升序)|desc（降序）
-------------------------------------------------------------
mysql> select * from stu order by age;
+----+----------+------+-----+----------+
| id | name | age | sex | classid |
+----+----------+------+-----+----------+
| 7 | uu01 | 18 | m | python03 |
| 1 | zhangsan | 20 | m | python03 |
| 5 | xiaozhan | 21 | w | python01 |
| 2 | lisi | 22 | m | python02 |
| 13 | qq06 | 22 | m | NULL |
| 11 | uu06 | 23 | w | python03 |
| 9 | uu03 | 23 | m | python03 |
| 3 | wangwu | 25 | w | python03 |
| 12 | uu08 | 26 | w | python02 |
| 10 | uu04 | 26 | m | python02 |
| 8 | uu02 | 26 | w | python02 |
| 6 | xiaozhao | 27 | m | python03 |
| 4 | xiaoli | 28 | m | python02 |
| 14 | qq01 | 31 | m | python01 |
| 15 | qq03 | 33 | m | python01 |
+----+----------+------+-----+----------+
15 rows in set (0.00 sec)
mysql> select * from stu order by age asc;
+----+----------+------+-----+----------+
| id | name | age | sex | classid |
+----+----------+------+-----+----------+
| 7 | uu01 | 18 | m | python03 |
| 1 | zhangsan | 20 | m | python03 |
| 5 | xiaozhan | 21 | w | python01 |
| 2 | lisi | 22 | m | python02 |
| 13 | qq06 | 22 | m | NULL |
| 11 | uu06 | 23 | w | python03 |
| 9 | uu03 | 23 | m | python03 |
| 3 | wangwu | 25 | w | python03 |
| 12 | uu08 | 26 | w | python02 |
| 10 | uu04 | 26 | m | python02 |
| 8 | uu02 | 26 | w | python02 |
| 6 | xiaozhao | 27 | m | python03 |
| 4 | xiaoli | 28 | m | python02 |
| 14 | qq01 | 31 | m | python01 |
| 15 | qq03 | 33 | m | python01 |
+----+----------+------+-----+----------+
15 rows in set (0.00 sec)
mysql> select * from stu order by age desc;
+----+----------+------+-----+----------+
| id | name | age | sex | classid |
+----+----------+------+-----+----------+
| 15 | qq03 | 33 | m | python01 |
| 14 | qq01 | 31 | m | python01 |
| 4 | xiaoli | 28 | m | python02 |
| 6 | xiaozhao | 27 | m | python03 |
| 12 | uu08 | 26 | w | python02 |
| 10 | uu04 | 26 | m | python02 |
| 8 | uu02 | 26 | w | python02 |
| 3 | wangwu | 25 | w | python03 |
| 9 | uu03 | 23 | m | python03 |
| 11 | uu06 | 23 | w | python03 |
| 13 | qq06 | 22 | m | NULL |
| 2 | lisi | 22 | m | python02 |
| 5 | xiaozhan | 21 | w | python01 |
| 1 | zhangsan | 20 | m | python03 |
| 7 | uu01 | 18 | m | python03 |
+----+----------+------+-----+----------+
15 rows in set (0.00 sec)
mysql> select * from stu order by classid desc;
+----+----------+------+-----+----------+
| id | name | age | sex | classid |
+----+----------+------+-----+----------+
| 1 | zhangsan | 20 | m | python03 |
| 3 | wangwu | 25 | w | python03 |
| 11 | uu06 | 23 | w | python03 |
| 6 | xiaozhao | 27 | m | python03 |
| 7 | uu01 | 18 | m | python03 |
| 9 | uu03 | 23 | m | python03 |
| 12 | uu08 | 26 | w | python02 |
| 10 | uu04 | 26 | m | python02 |
| 8 | uu02 | 26 | w | python02 |
| 4 | xiaoli | 28 | m | python02 |
| 2 | lisi | 22 | m | python02 |
| 5 | xiaozhan | 21 | w | python01 |
| 14 | qq01 | 31 | m | python01 |
| 15 | qq03 | 33 | m | python01 |
| 13 | qq06 | 22 | m | NULL |
+----+----------+------+-----+----------+
15 rows in set (0.00 sec)
mysql> select * from stu order by classid asc;
+----+----------+------+-----+----------+
| id | name | age | sex | classid |
+----+----------+------+-----+----------+
| 13 | qq06 | 22 | m | NULL |
| 15 | qq03 | 33 | m | python01 |
| 14 | qq01 | 31 | m | python01 |
| 5 | xiaozhan | 21 | w | python01 |
| 12 | uu08 | 26 | w | python02 |
| 10 | uu04 | 26 | m | python02 |
| 8 | uu02 | 26 | w | python02 |
| 4 | xiaoli | 28 | m | python02 |
| 2 | lisi | 22 | m | python02 |
| 7 | uu01 | 18 | m | python03 |
| 9 | uu03 | 23 | m | python03 |
| 6 | xiaozhao | 27 | m | python03 |
| 11 | uu06 | 23 | w | python03 |
| 3 | wangwu | 25 | w | python03 |
| 1 | zhangsan | 20 | m | python03 |
+----+----------+------+-----+----------+
15 rows in set (0.01 sec)
mysql> select * from stu order by classid asc,age desc;
+----+----------+------+-----+----------+
| id | name | age | sex | classid |
+----+----------+------+-----+----------+
| 13 | qq06 | 22 | m | NULL |
| 15 | qq03 | 33 | m | python01 |
| 14 | qq01 | 31 | m | python01 |
| 5 | xiaozhan | 21 | w | python01 |
| 4 | xiaoli | 28 | m | python02 |
| 12 | uu08 | 26 | w | python02 |
| 10 | uu04 | 26 | m | python02 |
| 8 | uu02 | 26 | w | python02 |
| 2 | lisi | 22 | m | python02 |
| 6 | xiaozhao | 27 | m | python03 |
| 3 | wangwu | 25 | w | python03 |
| 9 | uu03 | 23 | m | python03 |
| 11 | uu06 | 23 | w | python03 |
| 1 | zhangsan | 20 | m | python03 |
| 7 | uu01 | 18 | m | python03 |
+----+----------+------+-----+----------+
15 rows in set (0.00 sec)
mysql>
獲取部分?jǐn)?shù)據(jù)：limit
-- 分頁(yè)公式： limit (頁(yè)號(hào)-1)*頁(yè)大小,頁(yè)大小
--------------------------------------------------
mysql> select * from stu limit 0,3;
+----+----------+------+-----+----------+
| id | name | age | sex | classid |
+----+----------+------+-----+----------+
| 1 | zhangsan | 20 | m | python03 |
| 2 | lisi | 22 | m | python02 |
| 3 | wangwu | 25 | w | python03 |
+----+----------+------+-----+----------+
3 rows in set (0.00 sec)
mysql> select * from stu limit 3,3;
+----+----------+------+-----+----------+
| id | name | age | sex | classid |
+----+----------+------+-----+----------+
| 4 | xiaoli | 28 | m | python02 |
| 5 | xiaozhan | 21 | w | python01 |
| 6 | xiaozhao | 27 | m | python03 |
+----+----------+------+-----+----------+
3 rows in set (0.01 sec)
mysql> select * from stu limit 6,3;
+----+------+------+-----+----------+
| id | name | age | sex | classid |
+----+------+------+-----+----------+
| 7 | uu01 | 18 | m | python03 |
| 8 | uu02 | 26 | w | python02 |
| 9 | uu03 | 23 | m | python03 |
+----+------+------+-----+----------+
3 rows in set (0.00 sec)
mysql> select * from stu limit 9,3;
+----+------+------+-----+----------+
| id | name | age | sex | classid |
+----+------+------+-----+----------+
| 10 | uu04 | 26 | m | python02 |
| 11 | uu06 | 23 | w | python03 |
| 12 | uu08 | 26 | w | python02 |
+----+------+------+-----+----------+
3 rows in set (0.00 sec)
綜合查詢練習(xí)
1. 查詢python03期所有學(xué)員，按年齡降序排序
mysql> select * from stu where classid='python03' order by age desc;
2. 查詢python03期，年齡最大的3位學(xué)員信息
mysql> select * from stu where classid='python03' order by age desc limit 3;
3. 統(tǒng)計(jì)每個(gè)班級(jí)人數(shù)，并按人數(shù)降序排序（排除班級(jí)信息為null的數(shù)據(jù)）
mysql> select classid,count(*) m from stu where classid is not null group by classid order by m desc;
4. 統(tǒng)計(jì)每個(gè)班級(jí)年齡在20~30的學(xué)員人數(shù)信息，并按人數(shù)降序排序（排除班級(jí)信息為null的數(shù)據(jù)）
mysql> select classid,count(*) m from stu where classid is not null and age between 20 and 30
-> group by classid order by m desc
5. 統(tǒng)計(jì)每個(gè)班級(jí)男女生人數(shù)最多3條記錄信息。（排除班級(jí)信息為null的數(shù)據(jù)）
mysql> select classid,sex,count(*) m from stu where classid is not null
-> group by classid,sex order by m desc limit 3;
多表查詢：
1. 嵌套查詢 （一個(gè)查詢的結(jié)果是另外查詢的條件）
2. where關(guān)聯(lián)查詢
3. join連接查詢：內(nèi)聯(lián)inner join，左聯(lián) left join，右聯(lián)right join
1. 嵌套查詢：
-- 查詢年齡最大的學(xué)生信息
mysql> select * from stu where age=(select max(age) from stu);
+----+------+------+-----+----------+
| id | name | age | sex | classid |
+----+------+------+-----+----------+
| 15 | qq03 | 33 | m | python01 |
+----+------+------+-----+----------+
1 row in set (0.00 sec)
查詢帖子標(biāo)題為'工作十年'的發(fā)帖人信息
mysql> select * from users where id in(select uid from topic where title='工作十年');
+----+---------------+----------------------------------+--------+-------------+
| id | email | password | name | phone |
+----+---------------+----------------------------------+--------+-------------+
| 2 | lisi@sohu.com | e10adc3949ba59abbe56e057f20f883e | 李四 | 13466587533 |
+----+---------------+----------------------------------+--------+-------------+
1 row in set (0.00 sec)
2. where關(guān)聯(lián)查詢
查詢帖子信息，并關(guān)聯(lián)發(fā)帖人姓名信息
mysql> select t.id,t.title,t.addtime,u.name from topic t,users u where t.uid=u.id;
+----+-----------------------------------------------+---------------------+-----------+
| id | title | addtime | name |
+----+-----------------------------------------------+---------------------+-----------+
| 1 | 工作十年 | 2017-09-10 12:23:45 | 李四 |
| 2 | 畢業(yè)一年，大家過(guò)著怎樣的生活 | 2017-09-11 18:23:45 | 張三豐 |
| 3 | 英語(yǔ)四級(jí)真的很重要嗎？ | 2017-09-12 08:03:45 | 王月 |
| 4 | 為什么工資一直漲不了？速度進(jìn)來(lái) | 2017-09-13 15:43:23 | 王五 |
| 5 | 現(xiàn)在創(chuàng)業(yè)好難啊 | 2017-09-14 16:23:45 | 李四 |
| 6 | 現(xiàn)在學(xué)什么技術(shù)好一些？ | 2017-09-14 18:23:25 | 王五 |
+----+-----------------------------------------------+---------------------+-----------+
6 rows in set (0.00 sec)
使用where管理，統(tǒng)計(jì)每個(gè)用戶的發(fā)帖量信息
mysql> select u.id,u.name,count(*) from users u,topic t where u.id=t.uid group by u.id;
+----+-----------+----------+
| id | name | count(*) |
+----+-----------+----------+
| 1 | 張三豐 | 1 |
| 2 | 李四 | 2 |
| 3 | 王五 | 2 |
| 5 | 王月 | 1 |
+----+-----------+----------+
4 rows in set (0.00 sec)
mysql> select u.id,u.name,count(*) num from users u,topic t where u.id=t.uid group by u.id order by num desc limit 10;
+----+-----------+-----+
| id | name | num |
+----+-----------+-----+
| 2 | 李四 | 2 |
| 3 | 王五 | 2 |
| 5 | 王月 | 1 |
| 1 | 張三豐 | 1 |
+----+-----------+-----+
4 rows in set (0.01 sec)
mysql>
join 連接查詢
以左聯(lián)為主，user信息為主，右側(cè)topic表信息沒(méi)有的補(bǔ)null
mysql> select u.id,u.name,t.id,t.title from users u left join topic t on u.id=t.uid;
+----+-----------+------+-----------------------------------------------+
| id | name | id | title |
+----+-----------+------+-----------------------------------------------+
| 1 | 張三豐 | 2 | 畢業(yè)一年，大家過(guò)著怎樣的生活 |
| 2 | 李四 | 1 | 工作十年 |
| 2 | 李四 | 5 | 現(xiàn)在創(chuàng)業(yè)好難啊 |
| 3 | 王五 | 4 | 為什么工資一直漲不了？速度進(jìn)來(lái) |
| 3 | 王五 | 6 | 現(xiàn)在學(xué)什么技術(shù)好一些？ |
| 4 | 趙六 | NULL | NULL |
| 5 | 王月 | 3 | 英語(yǔ)四級(jí)真的很重要嗎？ |
| 6 | 陸玲 | NULL | NULL |
+----+-----------+------+-----------------------------------------------+
8 rows in set (0.00 sec)
右聯(lián)查詢，以topic為主，左側(cè)users沒(méi)有對(duì)應(yīng)信息的補(bǔ)null
mysql> select u.id,u.name,t.id,t.title from users u right join topic t on u.id=t.uid;
+------+-----------+----+-----------------------------------------------+
| id | name | id | title |
+------+-----------+----+-----------------------------------------------+
| 2 | 李四 | 1 | 工作十年 |
| 1 | 張三豐 | 2 | 畢業(yè)一年，大家過(guò)著怎樣的生活 |
| 5 | 王月 | 3 | 英語(yǔ)四級(jí)真的很重要嗎？ |
| 3 | 王五 | 4 | 為什么工資一直漲不了？速度進(jìn)來(lái) |
| 2 | 李四 | 5 | 現(xiàn)在創(chuàng)業(yè)好難啊 |
| 3 | 王五 | 6 | 現(xiàn)在學(xué)什么技術(shù)好一些？ |
| NULL | NULL | 8 | aaaaaaaa |
+------+-----------+----+-----------------------------------------------+
7 rows in set (0.00 sec)
內(nèi)聯(lián)（等價(jià)于where管理），求的是兩側(cè)交集的數(shù)據(jù)
mysql> select u.id,u.name,t.id,t.title from users u inner join topic t on u.id=t.uid;
+----+-----------+----+-----------------------------------------------+
| id | name | id | title |
+----+-----------+----+-----------------------------------------------+
| 1 | 張三豐 | 2 | 畢業(yè)一年，大家過(guò)著怎樣的生活 |
| 2 | 李四 | 1 | 工作十年 |
| 2 | 李四 | 5 | 現(xiàn)在創(chuàng)業(yè)好難啊 |
| 3 | 王五 | 4 | 為什么工資一直漲不了？速度進(jìn)來(lái) |
| 3 | 王五 | 6 | 現(xiàn)在學(xué)什么技術(shù)好一些？ |
| 5 | 王月 | 3 | 英語(yǔ)四級(jí)真的很重要嗎？ |
+----+-----------+----+-----------------------------------------------+
6 rows in set (0.00 sec)
統(tǒng)計(jì)每個(gè)用戶的發(fā)帖量信息，包含沒(méi)有發(fā)帖的用戶統(tǒng)計(jì)
mysql> select u.id,u.name,count(t.id) num from users u left join topic t on u.id=t.uid group by u.id order by num desc limit 10;
+----+-----------+-----+
| id | name | num |
+----+-----------+-----+
| 3 | 王五 | 2 |
| 2 | 李四 | 2 |
| 5 | 王月 | 1 |
| 1 | 張三豐 | 1 |
| 6 | 陸玲 | 0 |
| 4 | 趙六 | 0 |
+----+-----------+-----+
6 rows in set (0.01 sec)
查詢實(shí)戰(zhàn)
1. 查詢帖子id號(hào)為1的所有評(píng)論信息，要求管理評(píng)論人信
顯示字段：評(píng)論id，帖子id，評(píng)論人id，評(píng)論人姓名，評(píng)論內(nèi)容
要求使用：where關(guān)聯(lián)，inner jion和left join各執(zhí)行一次
mysql> select r.id,r.tid,r.uid,u.name,r.content from reply r,users u where r.uid = u.id and r.tid=1;
mysql> select r.id,r.tid,r.uid,u.name,r.content from reply r inner join users u on r.uid = u.id where r.tid=1;
mysql> select r.id,r.tid,r.uid,u.name,r.content from reply r left join users u on r.uid = u.id where r.tid=1;
2. 查詢帖子信息，并關(guān)聯(lián)輸出發(fā)帖人信息
顯示字段：帖子id，帖子標(biāo)題，發(fā)帖時(shí)間，發(fā)帖人id，發(fā)帖人姓名
要求使用：where關(guān)聯(lián)，inner jion和left join各執(zhí)行一次
mysql> select t.id,t.title,t.addtime,t.uid,u.name from topic t,users u where t.uid=u.id;
mysql> select t.id,t.title,t.addtime,t.uid,u.name from topic t inner join users u on t.uid=u.id;
mysql> select t.id,t.title,t.addtime,t.uid,u.name from topic t left join users u on t.uid=u.id;
3. 查詢id號(hào)為3的單條評(píng)論信息，關(guān)聯(lián)帖子和用戶信息
顯示字段：評(píng)論id，帖子id，帖子標(biāo)題，評(píng)論人id，評(píng)論人姓名，評(píng)論內(nèi)容，評(píng)論時(shí)間
mysql> select r.id,r.tid,t.title,r.uid,u.name,r.content,r.addtime from reply r,topic t,users u where r.tid=t.id and r.uid=u.id and r.id=3;
--左聯(lián)查
mysql> select r.id,r.tid,t.title,r.uid,u.name,r.content,r.addtime
-> from reply r left join topic t on r.tid=t.id
-> left join users u on r.uid=u.id where r.id=3\G
