本文详细介绍了MySQL数据库的常用操作，包括启动数据库服务、创建和连接数据库、查看数据库、创建和删除表、插入和查询数据、修改表结构以及使用索引。还展示了如何处理重复数据、更新和删除记录，以及使用视图和导出数据。内容涵盖了一系列数据库管理的基础知识，是MySQL初学者的实用教程。
sudo netstat -tap|grep mysql 检查MYSQL启动情况
sudo service mysql start 启动数据库服务
mysql -u root 连接数据库
show databases;
查看有哪些数据库
使用例子
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
mysql> use information_schema
选择需要操作的数据库
使用例子
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;查看库里面的表内容
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| INNODB_BUFFER_PAGE |
| INNODB_TRX |
| INNODB_BUFFER_POOL_STATS |
| INNODB_LOCK_WAITS |
| INNODB_CMPMEM |
| INNODB_CMP |
| INNODB_LOCKS |
| INNODB_CMPMEM_RESET |
| INNODB_CMP_RESET |
| INNODB_BUFFER_PAGE_LRU |
+---------------------------------------+
40 rows in set (0.00 sec)
quit
exit
同样功效退出
sudo gedit /etc/mysql/my.cnf 编辑 配置文件
创建数据库 mysql_shiyan
mysql> create database mysql_shiyan;
Query OK, 1 row affected (0.00 sec)
创建表
CREATE TABLE 表的名字
(
列名a 数据类型(数据长度),
列名b 数据类型(数据长度)，
列名c 数据类型(数据长度)
);
创建表 employee
mysql> create table employee(id int(10),name char(20),phone int(12));
Query OK, 0 rows affected (0.01 sec)
创建表 department
mysql> create table department
-> (
-> dpt_name CHAR(20),
-> dpt_phone INT(12)
-> );
Query OK, 0 rows affected (0.02 sec)
查看表内全部内容
mysql> select * from employee;
Empty set (0.00 sec)
插入数据
INSERT INTO 表的名字(列名a,列名b,列名c) VALUES(值1,值2,值3);
确定要插入的位置 然后输入数据
mysql> insert into employee(id,name,phone) values(01,'Tom',110110110);
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee values(02,'jack',119119119);
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee(id,name) values(03,'ROSE');
Query OK, 1 row affected (0.00 sec)
重复问题怎么处理？？？？？？
mysql> select * from employee;
+------+------+-----------+
| id | name | phone |
+------+------+-----------+
| 1 | Tom | 110110110 |
| 2 | jack | 119119119 |
| 3 | ROSE | NULL |
| 4 | Jack | 119119119 |
| 3 | Rose | NULL |
+------+------+-----------+
5 rows in set (0.00 sec)
mysql>
数据库属性类型
数据类型大小(字节)用途格式
INT4整数
FLOAT4单精度浮点数
DOUBLE8双精度浮点数
ENUM单选,比如性别ENUM('a','b','c')
SET多选SET('1','2','3')
DATE3日期YYYY-MM-DD
TIME3时间点或持续时间HH:MM:SS
YEAR1年份值YYYY
CHAR0~255定长字符串
VARCHAR0~255变长字符串
TEXT0~65535长文本数据
在MySQL中，通常有这几种约束：
约束类型：主键默认值唯一外键非空
关键字：PRIMARY KEYDEFAULTUNIQUEFOREIGN KEY
删除数据库
mysql> DROP DATABASE mysql_shiyan;
Query OK, 2 rows affected (0.01 sec)
从源文件导入数据库
mysql> source /home/shiyanlou/Desktop/SQL3/MySQL-03-01.sql;
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.06 sec)
Query OK, 0 rows affected (0.01 sec)
CREATE DATABASE mysql_shiyan;
use mysql_shiyan;
CREATE TABLE department
(
dpt_name CHAR(20) NOT NULL,
people_num INT(10) DEFAULT '10',
CONSTRAINT dpt_pk PRIMARY KEY (dpt_name)
);
CREATE TABLE employee
(
id INT(10) PRIMARY KEY,
name CHAR(20),
age INT(10),
salary INT(10) NOT NULL,
phone INT(12) NOT NULL,
in_dpt CHAR(20) NOT NULL,
UNIQUE (phone),
CONSTRAINT emp_fk FOREIGN KEY (in_dpt) REFERENCES department(dpt_name) 主键的定义
);
CREATE TABLE project
(
proj_num INT(10) NOT NULL,
proj_name CHAR(20) NOT NULL,
start_date DATE NOT NULL,
end_date DATE DEFAULT '2015-04-01',
of_dpt CHAR(20) REFERENCES department(dpt_name),
CONSTRAINT proj_pk PRIMARY KEY (proj_num,proj_name)
);
# 正常插入数据
INSERT INTO department(dpt_name,people_num) VALUES('dpt1',11);
#插入新的数据，people_num 为空，使用默认值
INSERT INTO department(dpt_name) VALUES('dpt2');
mysql> insert into employee values(01,'Tom',25,3000,110110,'dpt1');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into employee values(01,'Tom',25,3000,110110,'dpt1');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into employee values(02,'jack',30,3500,110110,'dpt2');
ERROR 1062 (23000): Duplicate entry '110110' for key 'phone'
mysql> insert into employee values(02,'jack',30,3500,110110,'dpt2');
ERROR 1062 (23000): Duplicate entry '110110' for key 'phone'
mysql> insert into employee values(02,'jack',30,3500,110110,'dpt2');
ERROR 1062 (23000): Duplicate entry '110110' for key 'phone'
mysql>
#INSERT 成功 age 为空，因为没有非空约束，表中显示 NULL
INSERT INTO employee(id,name,salary,phone,in_dpt) VALUES(03,'Jim',3400,119119,'dpt2');
#报错 salary 被非空约束，插入数据失败
INSERT INTO employee(id,name,age,phone,in_dpt) VALUES(04,'Bob',23,123456,'dpt1');
SELECT 要查询的列名 FROM 表名字 WHERE 限制条件;
SELECT name,age FROM employee;
mysql> select name,age FROM employee;
+------+------+
| name | age |
+------+------+
| Tom | 26 |
| Jack | 24 |
| Rose | 22 |
| Jim | 35 |
| Mary | 21 |
| Alex | 26 |
| Ken | 27 |
| Rick | 24 |
| Joe | 31 |
| Mike | 23 |
| Jobs | NULL |
| Tony | NULL |
+------+------+
12 rows in set (0.01 sec)
mysql>
SELECT name,age FROM employee WHERE age>25; 查询 年龄 超过25岁的
SELECT name,age,phone FROM employee WHERE name='Mary'; 查询 名字是MARY的
SELECT name,age FROM employee WHERE age<25 OR age>30; 筛选出 age 小于 25，或 age 大于 30
mysql> select name,age from employee where age between 25 and 30;
+------+------+
| name | age |
+------+------+
| Tom | 26 |
| Alex | 26 |
| Ken | 27 |
+------+------+
3 rows in set (0.01 sec)
SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';
mysql> select name,age,phone from employee where phone like '1101__';
通配符 ——代替一个字符 %代替不确定的字符
+------+------+--------+
| name | age | phone |
+------+------+--------+
| Joe | 31 | 110129 |
| Mike | 23 | 110110 |
+------+------+--------+
2 rows in set (0.01 sec)
比如只记名字的首字母，又不知道名字长度，则用 % 通配符代替不定个字符：
SELECT name,age,phone FROM employee WHERE name LIKE 'J%';
mysql> select max(salary) as max_salary,min(salary) from employee;
+------------+-------------+
| max_salary | min(salary) |
+------------+-------------+
| 3600 | 2500 |
+------------+-------------+
1 row in set (0.01 sec)
SELECT of_dpt,COUNT(proj_name) AS count_project FROM project GROUP BY of_dpt
HAVING of_dpt IN
(SELECT in_dpt FROM employee WHERE name='Tom');
mysql> SELECT of_dpt,COUNT(proj_name) AS count_project FROM project GROUP BY of_dpt
-> HAVING of_dpt IN
-> (SELECT in_dpt FROM employee WHERE name='Tom');
+--------+---------------+
| of_dpt | count_project |
+--------+---------------+
| dpt4 | 2 |
+--------+---------------+
1 row in set (0.01 sec)
mysql>
SELECT id,name,people_num
FROM employee,department
WHERE employee.in_dpt = department.dpt_name
ORDER BY id;
连表查询
SELECT id,name,people_num
FROM employee JOIN department
ON employee.in_dpt = department.dpt_name
ORDER BY id;
drop database xxxx; 删除东西
TABLE 原名 TO 新名字;
ALTER TABLE 原名 RENAME 新名;
ALTER TABLE 原名 RENAME TO 新名;
mysql> rename table table_1 to table_2;
Query OK, 0 rows affected (0.06 sec)
更改 table_1 为table_2
mysql> drop table table_2
-> ;
Query OK, 0 rows affected (0.01 sec)
删除表
mysql> show tables;
+------------------------+
| Tables_in_mysql_shiyan |
+------------------------+
| department |
| employee |
| project |
| table_2 |
+------------------------+
4 rows in set (0.00 sec)
mysql> drop table table_2
-> ;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+------------------------+
| Tables_in_mysql_shiyan |
+------------------------+
| department |
| employee |
| project |
+------------------------+
3 rows in set (0.00 sec)
mysql>
ALTER TABLE 表名字 ADD COLUMN 列名字 数据类型 约束;
或： ALTER TABLE 表名字 ADD 列名字 数据类型 约束;
在现有格式上面变更表的名字
例如
mysql> select * from employee;
+----+------+------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt |
+----+------+------+--------+--------+--------+
| 1 | Tom | 26 | 2500 | 119119 | dpt4 |
| 2 | Jack | 24 | 2500 | 120120 | dpt2 |
| 3 | Rose | 22 | 2800 | 114114 | dpt3 |
| 4 | Jim | 35 | 3000 | 100861 | dpt1 |
| 5 | Mary | 21 | 3000 | 100101 | dpt2 |
| 6 | Alex | 26 | 3000 | 123456 | dpt1 |
+----+------+------+--------+--------+--------+
6 rows in set (0.00 sec)
mysql> alter table employee add height int(4) default 170;
Query OK, 6 rows affected (0.05 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from employee;
+----+------+------+--------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt | height |
+----+------+------+--------+--------+--------+--------+
| 1 | Tom | 26 | 2500 | 119119 | dpt4 | 170 |
| 2 | Jack | 24 | 2500 | 120120 | dpt2 | 170 |
| 3 | Rose | 22 | 2800 | 114114 | dpt3 | 170 |
| 4 | Jim | 35 | 3000 | 100861 | dpt1 | 170 |
| 5 | Mary | 21 | 3000 | 100101 | dpt2 | 170 |
| 6 | Alex | 26 | 3000 | 123456 | dpt1 | 170 |
+----+------+------+--------+--------+--------+--------+
6 rows in set (0.01 sec)
mysql>
插入到
mysql> alter table employee add weight int(4) default 120 after age;
Query OK, 6 rows affected (0.08 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from employee;
+----+------+------+--------+--------+--------+--------+--------+
| id | name | age | weight | salary | phone | in_dpt | height |
+----+------+------+--------+--------+--------+--------+--------+
| 1 | Tom | 26 | 120 | 2500 | 119119 | dpt4 | 170 |
| 2 | Jack | 24 | 120 | 2500 | 120120 | dpt2 | 170 |
| 3 | Rose | 22 | 120 | 2800 | 114114 | dpt3 | 170 |
| 4 | Jim | 35 | 120 | 3000 | 100861 | dpt1 | 170 |
| 5 | Mary | 21 | 120 | 3000 | 100101 | dpt2 | 170 |
| 6 | Alex | 26 | 120 | 3000 | 123456 | dpt1 | 170 |
+----+------+------+--------+--------+--------+--------+--------+
6 rows in set (0.00 sec)
mysql> alter table employee add test int(10) default 11 first;
Query OK, 6 rows affected (0.09 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from employee;
+------+----+------+------+--------+--------+--------+--------+--------+
| test | id | name | age | weight | salary | phone | in_dpt | height |
+------+----+------+------+--------+--------+--------+--------+--------+
| 11 | 1 | Tom | 26 | 120 | 2500 | 119119 | dpt4 | 170 |
| 11 | 2 | Jack | 24 | 120 | 2500 | 120120 | dpt2 | 170 |
| 11 | 3 | Rose | 22 | 120 | 2800 | 114114 | dpt3 | 170 |
| 11 | 4 | Jim | 35 | 120 | 3000 | 100861 | dpt1 | 170 |
| 11 | 5 | Mary | 21 | 120 | 3000 | 100101 | dpt2 | 170 |
| 11 | 6 | Alex | 26 | 120 | 3000 | 123456 | dpt1 | 170 |
+------+----+------+------+--------+--------+--------+--------+--------+
6 rows in set (0.01 sec)
插入到第一列
mysql> alter table employee add test int(10) default 11 first;
Query OK, 6 rows affected (0.09 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from employee;
+------+----+------+------+--------+--------+--------+--------+--------+
| test | id | name | age | weight | salary | phone | in_dpt | height |
+------+----+------+------+--------+--------+--------+--------+--------+
| 11 | 1 | Tom | 26 | 120 | 2500 | 119119 | dpt4 | 170 |
| 11 | 2 | Jack | 24 | 120 | 2500 | 120120 | dpt2 | 170 |
| 11 | 3 | Rose | 22 | 120 | 2800 | 114114 | dpt3 | 170 |
| 11 | 4 | Jim | 35 | 120 | 3000 | 100861 | dpt1 | 170 |
| 11 | 5 | Mary | 21 | 120 | 3000 | 100101 | dpt2 | 170 |
| 11 | 6 | Alex | 26 | 120 | 3000 | 123456 | dpt1 | 170 |
+------+----+------+------+--------+--------+--------+--------+--------+
6 rows in set (0.01 sec)
ALTER TABLE 表名字 DROP COLUMN 列名字;
或： ALTER TABLE 表名字 DROP 列名字;
mysql> alter table employee drop column test;
Query OK, 6 rows affected (0.08 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from employee;
+----+------+------+--------+--------+--------+--------+--------+
| id | name | age | weight | salary | phone | in_dpt | height |
+----+------+------+--------+--------+--------+--------+--------+
| 1 | Tom | 26 | 120 | 2500 | 119119 | dpt4 | 170 |
| 2 | Jack | 24 | 120 | 2500 | 120120 | dpt2 | 170 |
| 3 | Rose | 22 | 120 | 2800 | 114114 | dpt3 | 170 |
| 4 | Jim | 35 | 120 | 3000 | 100861 | dpt1 | 170 |
| 5 | Mary | 21 | 120 | 3000 | 100101 | dpt2 | 170 |
| 6 | Alex | 26 | 120 | 3000 | 123456 | dpt1 | 170 |
+----+------+------+--------+--------+--------+--------+--------+
6 rows in set (0.00 sec)
ALTER TABLE 表名字 CHANGE 原列名 新列名 数据类型 约束;
Database changed
mysql> alter table employee change height shengao int(4) default 170;
Query OK, 6 rows affected (0.09 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from employee
-> ;
+----+------+------+--------+--------+--------+--------+---------+
| id | name | age | weight | salary | phone | in_dpt | shengao |
+----+------+------+--------+--------+--------+--------+---------+
| 1 | Tom | 26 | 120 | 2500 | 119119 | dpt4 | 170 |
| 2 | Jack | 24 | 120 | 2500 | 120120 | dpt2 | 170 |
| 3 | Rose | 22 | 120 | 2800 | 114114 | dpt3 | 170 |
| 4 | Jim | 35 | 120 | 3000 | 100861 | dpt1 | 170 |
| 5 | Mary | 21 | 120 | 3000 | 100101 | dpt2 | 170 |
| 6 | Alex | 26 | 120 | 3000 | 123456 | dpt1 | 170 |
+----+------+------+--------+--------+--------+--------+---------+
6 rows in set (0.00 sec)
ALTER TABLE 表名字 MODIFY 列名字 新数据类型;
UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;
mysql> update employee set age = 21,salary=3000 where name = 'Tom';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from employee where name='Tom';
+----+------+------+--------+--------+--------+--------+---------+
| id | name | age | weight | salary | phone | in_dpt | shengao |
+----+------+------+--------+--------+--------+--------+---------+
| 1 | Tom | 21 | 120 | 3000 | 119119 | dpt4 | 170 |
+----+------+------+--------+--------+--------+--------+---------+
1 row in set (0.00 sec)
DELETE FROM 表名字 WHERE 条件;
mysql> delete from employee where name='Tom'
-> ;
Query OK, 1 row affected (0.01 sec)
mysql> select * from employee where name='Tom';
Empty set (0.01 sec)
索引是一种与表有关的结构，它的作用相当于书的目录，可以根据目录中的页码快速找到所需的内容。
当表中有大量记录时，若要对表进行查询，没有索引的情况是全表搜索：将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录。这样做会消耗大量数据库系统时间，并造成大量磁盘 I/O 操作。
而如果在表中已建立索引，在索引中找到符合查询条件的索引值，通过索引值就可以快速找到表中的数据，可以大大加快查询速度。
对一张表中的某个列建立索引，有以下两种语句格式
ALTER TABLE 表名字 ADD INDEX 索引名 (列名);
CREATE INDEX 索引名 ON 表名字 (列名);
ALTER TABLE employee ADD INDEX idx_id (id); #在employee表的id列上建立名为idx_id的索引
CREATE INDEX idx_name ON employee (name); #在employee表的name列上建立名为idx_name的索引
ALTER TABLE employee ADD INDEX idx_id (id); #在employee表的id列上建立名为idx_id的索引
CREATE INDEX idx_name ON employee (name); #在employee表的name列上建立名为idx_name的索引
CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;
Database changed
mysql> create view v_emp(v_name,v_age,v_phone) as select name,age,phone from employee;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from v_emp
-> ;
+--------+-------+---------+
| v_name | v_age | v_phone |
+--------+-------+---------+
| Tom | 26 | 119119 |
| Jack | 24 | 120120 |
| Jobs | NULL | 19283 |
| Tony | NULL | 102938 |
| Rose | 22 | 114114 |
+--------+-------+---------+
5 rows in set (0.01 sec)
mysql>
LOAD DATA INFILE '文件路径和文件名' INTO TABLE 表名字;
mysql> use mysql_shiyan
Database changed
mysql> load data infile '/var/lib/mysql-files/SQL6/in.txt' INTO table employee;
Query OK, 7 rows affected (0.01 sec)
Records: 7 Deleted: 0 Skipped: 0 Warnings: 0
6Alex263000123456dpt1
7Ken273500654321dpt1
8Rick243500987654dpt3
9Joe313600100129dpt2
10Mike233400110110dpt1
11Jim353000100861dpt4
12Mary213000100101dpt2
SELECT 列1，列2 INTO OUTFILE '文件路径和文件名' FROM 表名字;
SELECT * INTO OUTFILE '/var/lib/mysql-files/out.txt' FROM employee;
mysqldump -u root 数据库名>备份文件名; #备份整个数据库
mysqldump -u root 数据库名 表名字>备份文件名; #备份整个表
cd /home/shiyanlou/
mysqldump -u root mysql_shiyan > bak.sql;
在Unix下，数据库名称是区分大小写的(不像SQL关键字)，因此你必须总是以test访问数据库，而不能用Test、TEST或其它一些变量。对表名也是这样的。(在Windows下，该限制不适用，尽管你必须在一个给定的查询中使用同样的大小写来引用数据库和表。但是，由于多种原因，作为最好的惯例，一定要使用与数据库创建时的同样的大小写。)
mysql> create table pet(name varchar(20), owener varchar(20),
-> species varchar(20),sex char(1), birth date, death date);
Query OK, 0 rows affected (0.02 sec)
create table user(name varchar(16), password varchar(16), );
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet |
+----------------+
1 row in set (0.00 sec)
mysql> describe pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owener | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> LOAD DATA INFILE '文件路径' INTO TABLE pet LINES
-> TERMINATED BY '\r\n';
mysql> LOAD DATA INFILE '/var/lib/mysql-files/pet.txt' INTO TABLE pet;
mysql> insert into pet
-> values('Puffball','diane','hamster','f','1999-03-30',null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name | owener | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | harold | cat | f | 1993-02-04 | 0000-00-00 |
| claws | cwen | cat | m | 1994-03-17 | NULL |
| buffy | harold | dog | f | 1989-05-13 | 0000-00-00 |
| fang | benny | dog | m | 1990-08-27 | 0000-00-00 |
| bowser | diane | dog | m | 1979-08-31 | 1995-07-29 |
| chirpy | gwen | bird | f | 1998-09-11 | 0000-00-00 |
| whistler | gwen | bird | f | 0000-00-00 | NULL |
| slimm | benny | snake | m | 1996-04-28 | 0000-00-00 |
| | NULL | NULL | NULL | NULL | NULL |
| Puffball | diane | hamster | f | 1999-03-30 | NULL |
+----------+--------+---------+------+------------+------------+
10 rows in set (0.00 sec)
mysql> DELETE FROM pet;
mysql> LOAD DATA INFILE '/var/lib/mysql-files/pet.txt' INTO TABLE pet;
mysql> update pet set birth = '1989-08-31' where name = 'Bowser';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from pet where name = "bowser";
+--------+--------+---------+------+------------+------------+
| name | owener | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| bowser | diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
1 row in set (0.00 sec)
mysql> select * from pet where birth > '1998-1-1';
+----------+--------+---------+------+------------+------------+
| name | owener | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| chirpy | gwen | bird | f | 1998-09-11 | 0000-00-00 |
| Puffball | diane | hamster | f | 1999-03-30 | NULL |
+----------+--------+---------+------+------------+------------+
2 rows in set (0.01 sec)
mysql> select * from pet where species = 'dog' and sex = 'f';
+-------+--------+---------+------+------------+------------+
| name | owener | species | sex | birth | death |
+-------+--------+---------+------+------------+------------+
| buffy | harold | dog | f | 1989-05-13 | 0000-00-00 |
+-------+--------+---------+------+------------+------------+
1 row in set (0.00 sec)
mysql> select * from pet where species = 'snake' or species = 'bird';
+----------+--------+---------+------+------------+------------+
| name | owener | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| chirpy | gwen | bird | f | 1998-09-11 | 0000-00-00 |
| whistler | gwen | bird | f | 0000-00-00 | NULL |
| slimm | benny | snake | m | 1996-04-28 | 0000-00-00 |
+----------+--------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> select * from pet where(species='cat' and sex = 'm')
-> or(species = 'dog' and sex = 'f');
+-------+--------+---------+------+------------+------------+
| name | owener | species | sex | birth | death |
+-------+--------+---------+------+------------+------------+
| claws | cwen | cat | m | 1994-03-17 | NULL |
| buffy | harold | dog | f | 1989-05-13 | 0000-00-00 |
+-------+--------+---------+------+------------+------------+
2 rows in set (0.00 sec)
Database changed
mysql> select name,birth from pet;
+----------+------------+
| name | birth |
+----------+------------+
| Fluffy | 1993-02-04 |
| claws | 1994-03-17 |
| buffy | 1989-05-13 |
| fang | 1990-08-27 |
| bowser | 1989-08-31 |
| chirpy | 1998-09-11 |
| whistler | 0000-00-00 |
| slimm | 1996-04-28 |
| | NULL |
| Puffball | 1999-03-30 |
+----------+------------+
10 rows in set (0.00 sec)
Database changed
mysql> select name,birth from pet;
+----------+------------+
| name | birth |
+----------+------------+
| Fluffy | 1993-02-04 |
| claws | 1994-03-17 |
| buffy | 1989-05-13 |
| fang | 1990-08-27 |
| bowser | 1989-08-31 |
| chirpy | 1998-09-11 |
| whistler | 0000-00-00 |
| slimm | 1996-04-28 |
| | NULL |
| Puffball | 1999-03-30 |
+----------+------------+
10 rows in set (0.00 sec)
mysql> select owner from pet;
ERROR 1054 (42S22): Unknown column 'owner' in 'field list'
异常
NULLPOINT
mysql> SELECT name, species, birth FROM pet
-> WHERE species = 'dog' OR species = 'cat';
+--------+---------+------------+
| name | species | birth |
+--------+---------+------------+
| Fluffy | cat | 1993-02-04 |
| claws | cat | 1994-03-17 |
| buffy | dog | 1989-05-13 |
| fang | dog | 1990-08-27 |
| bowser | dog | 1989-08-31 |
+--------+---------+------------+
5 rows in set (0.00 sec)
Database changed
mysql> select name,birth from pet order by birth;
+----------+------------+
| name | birth |
+----------+------------+
| | NULL |
| whistler | 0000-00-00 |
| buffy | 1989-05-13 |
| bowser | 1989-08-31 |
| fang | 1990-08-27 |
| Fluffy | 1993-02-04 |
| claws | 1994-03-17 |
| slimm | 1996-04-28 |
| chirpy | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+
10 rows in set (0.00 sec)
mysql>
mysql> select name,birth from pet order by birth desc;
+----------+------------+
| name | birth |
+----------+------------+
| Puffball | 1999-03-30 |
| chirpy | 1998-09-11 |
| slimm | 1996-04-28 |
| claws | 1994-03-17 |
| Fluffy | 1993-02-04 |
| fang | 1990-08-27 |
| bowser | 1989-08-31 |
| buffy | 1989-05-13 |
| whistler | 0000-00-00 |
| | NULL |
+----------+------------+
10 rows in set (0.00 sec)
可以对多个列进行排序，并且可以按不同的方向对不同的列进行排序。例如，按升序对动物的种类进行排序，然后按降序根据生日对各动物种类进行排序(最年轻的动物在最前面)，使用下列查询：
mysql> SELECT name, species, birth FROM pet
-> ORDER BY species, birth DESC;
mysql> select name, birth, curdate(), timestampdiff(year,birth,curdate()) as age
-> from pet;
+----------+------------+------------+------+
| name | birth | curdate() | age |
+----------+------------+------------+------+
| Fluffy | 1993-02-04 | 2018-07-30 | 25 |
| claws | 1994-03-17 | 2018-07-30 | 24 |
| buffy | 1989-05-13 | 2018-07-30 | 29 |
| fang | 1990-08-27 | 2018-07-30 | 27 |
| bowser | 1989-08-31 | 2018-07-30 | 28 |
| chirpy | 1998-09-11 | 2018-07-30 | 19 |
| whistler | 0000-00-00 | 2018-07-30 | NULL |
| slimm | 1996-04-28 | 2018-07-30 | 22 |
| | NULL | 2018-07-30 | NULL |
| Puffball | 1999-03-30 | 2018-07-30 | 19 |
+----------+------------+------------+------+
10 rows in set (0.00 sec)
mysql> select name, birth, curdate(), (year(curdate())-year(birth)) -(right(curdate(),5)
+----------+------------+------------+------+
| name | birth | curdate() | age |
+----------+------------+------------+------+
| Fluffy | 1993-02-04 | 2018-07-30 | 25 |
| claws | 1994-03-17 | 2018-07-30 | 24 |
| buffy | 1989-05-13 | 2018-07-30 | 29 |
| fang | 1990-08-27 | 2018-07-30 | 27 |
| bowser | 1989-08-31 | 2018-07-30 | 28 |
| chirpy | 1998-09-11 | 2018-07-30 | 19 |
| whistler | 0000-00-00 | 2018-07-30 | 2018 |
| slimm | 1996-04-28 | 2018-07-30 | 22 |
| | NULL | 2018-07-30 | NULL |
| Puffball | 1999-03-30 | 2018-07-30 | 19 |
+----------+------------+------------+------+
10 rows in set (0.00 sec)
mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)
-> FROM pet;
mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)
-> AS age
-> FROM pet ORDER BY name;
mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)
-> AS age
-> FROM pet ORDER BY age;
mysql> select name, birth,death,
-> (year(death)-year(birth)) - (right(death,5)
-> as age
-> from pet where death is not null order by age;
+--------+------------+------------+-------+
| name | birth | death | age |
+--------+------------+------------+-------+
| chirpy | 1998-09-11 | 0000-00-00 | -1999 |
| slimm | 1996-04-28 | 0000-00-00 | -1997 |
| Fluffy | 1993-02-04 | 0000-00-00 | -1994 |
| fang | 1990-08-27 | 0000-00-00 | -1991 |
| buffy | 1989-05-13 | 0000-00-00 | -1990 |
| bowser | 1989-08-31 | 1995-07-29 | 5 |
+--------+------------+------------+-------+
6 rows in set (0.00 sec)
可以使用一个类似的查询来确定已经死亡动物的死亡年龄。你通过检查death值是否为NULL来确定是哪些动物已经死亡，然后对于那些非NULL值的动物，需要计算出death和birth值之间的差来知道他们在这个世界上所存在的时间：
mysql> SELECT name, birth, death,
-> (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)
-> AS age
-> FROM pet WHERE death IS NOT NULL ORDER BY age;
查询使用death IS NOT NULL而非death != NULL，因为NULL是特殊的值，不能使用普通比较符来比较
mysql> SELECT name, birth, MONTH(birth) FROM pet;
mysql> SELECT name, birth, MONTH(birth) FROM pet;
你甚至可以编写查询，不管当前月份是什么它都能执行。因此不必在查询中使用一个特定的月份，DATE_ADD()允许在一个给定的日期上加上时间间隔。如果在NOW()值上加上一个月，然后用MONTH()提取月份，产生生日所在月份：
mysql> SELECT name, birth FROM pet
-> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
完成该任务的另一个方法是加1得出当前月份的下一个月(在使用取模函数MOD()后，如果月份当前值是12，则“返回”到值0)：
mysql> SELECT name, birth FROM pet
-> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
模式匹配
mysql> select * from pet where name like 'b%'
-> ;
+--------+--------+---------+------+------------+------------+
| name | owener | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| buffy | harold | dog | f | 1989-05-13 | 0000-00-00 |
| bowser | diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.00 sec)
mysql> select * from pet where name like '%w%'
-> ;
+----------+--------+---------+------+------------+------------+
| name | owener | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| claws | cwen | cat | m | 1994-03-17 | NULL |
| bowser | diane | dog | m | 1989-08-31 | 1995-07-29 |
| whistler | gwen | bird | f | 0000-00-00 | NULL |
+----------+--------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> select * from pet where name like '_____';
+-------+--------+---------+------+------------+------------+
| name | owener | species | sex | birth | death |
+-------+--------+---------+------+------------+------------+
| claws | cwen | cat | m | 1994-03-17 | NULL |
| buffy | harold | dog | f | 1989-05-13 | 0000-00-00 |
| slimm | benny | snake | m | 1996-04-28 | 0000-00-00 |
+-------+--------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> select * from pet where name REGEXP '^b';
+--------+--------+---------+------+------------+------------+
| name | owener | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| buffy | harold | dog | f | 1989-05-13 | 0000-00-00 |
| bowser | diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.01 sec)
mysql> select * from pet where name REGEXP 'fy$';
+--------+--------+---------+------+------------+------------+
| name | owener | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Fluffy | harold | cat | f | 1993-02-04 | 0000-00-00 |
| buffy | harold | dog | f | 1989-05-13 | 0000-00-00 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.00 sec)
ysql> select * from pet where name REGEXP 'w';
+----------+--------+---------+------+------------+------------+
| name | owener | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| claws | cwen | cat | m | 1994-03-17 | NULL |
| bowser | diane | dog | m | 1989-08-31 | 1995-07-29 |
| whistler | gwen | bird | f | 0000-00-00 | NULL |
+----------+--------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> select * from pet where name REGEXP '^......$'
-> ;
+--------+--------+---------+------+------------+------------+
| name | owener | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Fluffy | harold | cat | f | 1993-02-04 | 0000-00-00 |
| bowser | diane | dog | m | 1989-08-31 | 1995-07-29 |
| chirpy | gwen | bird | f | 1998-09-11 | 0000-00-00 |
+--------+--------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';
+-------+--------+---------+------+------------+------------+
| name | owener | species | sex | birth | death |
+-------+--------+---------+------+------------+------------+
| claws | cwen | cat | m | 1994-03-17 | NULL |
| buffy | harold | dog | f | 1989-05-13 | 0000-00-00 |
| slimm | benny | snake | m | 1996-04-28 | 0000-00-00 |
+-------+--------+---------+------+------------+------------+
3 rows in set (0.00 sec)
计算你拥有动物的总数目与“在pet表中有多少行?”是同样的问题，因为每个宠物都对应一条记录。COUNT(*)函数计算行数，所以计算动物数目的查询应为：
mysql> SELECT COUNT(*) FROM pet;
在前面的章节中，你检索了拥有宠物的人的名字。如果你想要知道每个主人有多少宠物，你也可以使用COUNT(*)函数：
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
注意，使用GROUP BY对每个owner的所有记录分组，没有它，你会得到错误消息：
mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)
with no GROUP columns is illegal if there is no GROUP BY clause
COUNT(*)和GROUP BY以各种形式分类你的数据。下列例子显示出以不同方式进行动物普查操作。
查看每种动物的数量：
mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
查看每种性别的动物数量：
mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
按种类和性别组合分类的动物数量：
mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
若使用COUNT(*)，你不必检索整个表。例如, 当只对狗和猫进行查询时，应为：
mysql> SELECT species, sex, COUNT(*) FROM pet
-> WHERE species = 'dog' OR species = 'cat'
-> GROUP BY species, sex;
或，如果你仅需要知道已知性别的按性别分组的动物数目：
mysql> SELECT species, sex, COUNT(*) FROM pet
-> WHERE sex IS NOT NULL
-> GROUP BY species, sex;
pet表追踪你拥有的宠物。如果你想要记录其它相关信息，例如在他们看兽医的情况或后代出生的情况，那么你需要另外的表。这张表应该拥有些什么呢？它需要：
需要包含宠物名字以便你知道每个发生的事件属于哪个动物。
需要一个日期以便你知道事件是什么时候发生的。
需要一个描述事件的字段。
如果你想要对事件进行分类，则需要一个事件类型字段。
综上所述，event表的CREATE TABLE语句应为：
mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
-> type VARCHAR(15), remark VARCHAR(255));
类似于pet表，最简单的方法是创建一个用定位符分隔的文本文件来加载载初始记录：
此处输入图片的描述
采用如下方式加载记录：
mysql> LOAD DATA INFILE '/home/shiyanlou/Desktop/event.txt' INTO TABLE event;
此处输入图片的描述
由于你已经在pet表上的查询中学到了一定的知识，你应该能执行对event表中记录的检索；原理是一样的。但是有没有event表本身不能回答你可能问的问题呢?
当宠物们生了了一窝小动物时，假定你想要找出这时候每只宠物的年龄。我们前面看到了如何通过两个日期计算年龄。event表中有母亲的生产日期，但是为了计算母亲的年龄，你需要她的出生日期，存储在pet表中。说明查询需要两个表：
mysql> SELECT pet.name,
-> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)
-> remark
-> FROM pet, event
-> WHERE pet.name = event.name AND event.type = 'litter';
此处输入图片的描述
关于该查询要注意以下几件事：
FROM子句连接两个表，因为查询需要从两个表中提取信息。
当从多个表组合(联结)信息时，你需要指定其中一个表中的列明以期匹配其它表的列名。这很简单，因为它们都有一个name列，查询可以通过使用WHERE子句基于name值来匹配两个表中的记录。
因为name列都存在两个表中，因此当引用该列时，一定要指定是哪个表，把表名附在列名前即可以实现。 如果你想要将一个表的记录与该表的其它记录进行比较，可以将该表联结到自身。例如，为了在你的宠物之中选择繁殖中的配偶，你可以用pet表联结自身来进行相同种类的雄雌配对：
mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
-> FROM pet AS p1, pet AS p2
-> WHERE p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';
在这个查询中，我们为表名指定别名p1和p2以便能引用它们的列并且使得每一个列的引用更直观。
455
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
