慢谈MYSQL常用SQL语句
浏览数：36 /
时间：2015年06月12日
目录
1、SQL语句分类
2、DDL语句
3、DML语句
4、DCL语句
5、事务相关语句
6、mysql查询
6.1、单表简单查询
6.2、多表组合查询
6.2.1、联结查询（交叉联结，内联结，外联结（左外联结、右外联结））
6.2.2、联合查询（UNION）
6.2.3、自联结
6.3、子查询
6.3.1、WHERE子查询
6.3.2、FROM子查询
1、SQL语句分类
DDL：数据定义语言，用来定义数据库对象，包括数据库、表、索引、存储过程、存储函数、约束、触发器、事件调度器等
DML：数据操作语言，包括CRUD（Insert，Select，Replace，Update，Delete）
DCL：数据控制语言，包括grant，revoke
与事务相关的语言：start transaction，commit，rollback，save point
2、DDL语句
2.1、数据库操作
a)、创建数据库：
语法：mysql> create {database | schema} [if not exists] 数据库名; #“if not exists”是用来作条件判断
举例：
mysql> create database if not exists mydb; #创建一个mydb数据库 Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> create schema if not exists mydb; #尝试再次建立mydb数据库 Query OK, 1 row affected, 1 warning (0.00 sec) mysql> show warnings; #查看警告信息 +-------+------+-----------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------+ | Note | 1007 | Can‘t create database ‘mydb‘; database exists | +-------+------+-----------------------------------------------+ 1 row in set (0.00 sec)
b)、删除数据库：
语法：
mysql> drop {database | schema} [if exists] 数据库名;
举例：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> drop schema if exists mydb; Query OK, 0 rows affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec)
c)、修改数据库
语法：
mysql> alter {database | schema} 数据库名 [DEFAULT] CHARACTER SET [=] charset_name ｜[DEFAULT] COLLATE [=] collation_name
mysql> alter {database | schema} 数据库名 upgrade data directory name; #此语句用在数据进行升级后用来升级数据字典
2.2、数据表操作
a)、创建字段自定义表
语法：
mysql> create table [if not exists] 表名 (字段1 字段定义,字段2 字段定义, ...) 众多选项
mysql> help create table; #获取表创建的帮助。
举例：
mysql> use test; mysql> create table if not exists tb1 (id int unsigned auto_increment primary key,name char(30) not null,age tinyint unsigned,class char(20) not null ) character set = utf8 engine = innodb;
以下三个sql语句都可查数据库中的表状态：
mysql> show table status from test\G mysql> show table status in test\G mysql> show table status like ‘tb1‘;
b)、以select语句查询到的数据为结果来创建一张表
mysql> use mydb1; mysql> create table if not exists tb2 select user,host,password from mysql.user; Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from tb2; +------+------------+-------------------------------------------+ | user | host | password | +------+------------+-------------------------------------------+ | root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | root | db\_server | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +------+------------+-------------------------------------------+ 3 rows in set (0.00 sec) 注：这种方式创建出来的表，表的表结构与源表的表结构是不同的，也就是说字段中的定义和修饰符不会全部在新表中都有
c)、模仿一张表的表结构来创建一张只有表结构的表
mysql> create table if not exists tb3 like tb2; Query OK, 0 rows affected (0.03 sec) mysql> desc tb3; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | user | char(16) | NO | | | | | host | char(60) | NO | | | | | password | char(41) | NO | | | | +----------+----------+------+-----+---------+-------+ 3 rows in set (0.03 sec) mysql> desc tb2; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | user | char(16) | NO | | | | | host | char(60) | NO | | | | | password | char(41) | NO | | | | +----------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> select * from tb3; #tb3中是没有数据的 Empty set (0.02 sec)
开动脑筋：
怎样从一个表中挑选一些数据生成一张新表，且新表的表结构(字段定义、修饰符等)与源表是完成相同的。
实现方式分两部来完成，先创建和源表一样表结构的表，再以select的方式插入数据到此表。
实现：
mysql> DESC tb1; +-------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | | age | tinyint(3) unsigned | NO | | NULL | | +-------+---------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM tb1; +----+-------+-----+ | id | name | age | +----+-------+-----+ | 1 | jack | 29 | | 2 | jason | 39 | | 3 | tom | 37 | | 4 | luck | 22 | | 7 | root | 0 | | 8 | root | 0 | | 9 | root | 0 | +----+-------+-----+ 7 rows in set (0.01 sec) mysql> CREATE TABLE IF NOT EXISTS tb2 LIKE tb1; #创建tb2以tb1的表结构为源 Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> DESC tb2; #tb2的表结构和tb1的表结构是完成相同的 +-------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | | age | tinyint(3) unsigned | NO | | NULL | | +-------+---------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> INSERT INTO tb2 SELECT * FROM tb1 WHERE age>30; #只把tb1中年龄大于30的数据插入到新表中 Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM tb2; #验证数据 +----+-------+-----+ | id | name | age | +----+-------+-----+ | 2 | jason | 39 | | 3 | tom | 37 | +----+-------+-----+ 2 rows in set (0.00 sec) 这样就可实现保持源表的表结构提取所需数据。
d)、删除表
mysql> drop table [if exists] 表名;
e)、修改表名称、字段、定义、修饰符等
用help alter table来获取帮助
新增字段，删除字段
modify 修改指定字段定义：
change 修改字段名
rename 新表名
举例：
mysql> SELECT * FROM tb2; +------+------+ | id | name | +------+------+ | 1 | Zcj | | 2 | Tom | +------+------+ 2 rows in set (0.00 sec) mysql> DESC tb2; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | char(30) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ mysql> ALTER TABLE tb2 ADD Age TINYINT UNSIGNED AFTER id; #增加“Age”字段 Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM tb2; +------+------+------+ | id | Age | name | +------+------+------+ | 1 | NULL | Zcj | | 2 | NULL | Tom | +------+------+------+ mysql> ALTER TABLE tb2 DROP Age; #删除字段“Age” Query OK, 2 rows affected (0.06 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM tb2; +------+------+ | id | name | +------+------+ | 1 | Zcj | | 2 | Tom | +------+------+ mysql> ALTER TABLE tb2 MODIFY id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY; #修改id字段的定义 Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> DESC tb2; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | char(30) | YES | | NULL | | +-------+------------------+------+-----+---------+----------------+ mysql> ALTER TABLE tb2 CHANGE name fullname CHAR(50) NOT NULL; #修改字段name的名称及定义 Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> DESC tb2; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | fullname | char(50) | NO | | NULL | | +----------+------------------+------+-----+---------+----------------+ mysql> ALTER TABLE tb2 RENAME newtb2; #修改表名 Query OK, 0 rows affected (0.00 sec) mysql> DESC newtb2; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | fullname | char(50) | NO | | NULL | | +----------+------------------+------+-----+---------+----------------+
3、DML语句
3.1、insert：用来插入数据
语法：
mysql> insert into 表名 [(字段名，...)] {values | value} (值,...)
mysql> insert into 表名 set 字段名=值，...
举例：
mysql> CREATE TABLE IF NOT EXISTS tb1 (id int unsigned auto_increment primary key,name char(20) not null,age tinyint unsigned not null); #先创建一张表
a)、插入多行数据
mysql> INSERT INTO tb1 (name,age) VALUES (‘jack‘,29),(‘jason‘,39),(‘tom‘,37);
b)、插入一行数据
mysql> INSERT INTO tb1 SET name=‘luck‘,age=22; mysql> SELECT * FROM tb1; #查看表数据 +----+-------+-----+ | id | name | age | +----+-------+-----+ | 1 | jack | 29 | | 2 | jason | 39 | | 3 | tom | 37 | | 4 | luck | 22 | +----+-------+-----+ 4 rows in set (0.00 sec)
c)、以select结果插入数据
mysql> INSERT INTO tb1 (name) SELECT user FROM mysql.user; #把user表中的用户信息检索出来填充到tb1表中的name字段 Query OK, 3 rows affected, 1 warning (0.08 sec) Records: 3 Duplicates: 0 Warnings: 1 mysql> SHOW WARNINGS; #有个警告信息，因为创建表时age字段是not null的,只填充了name字段，没有age字段的相应数据 +---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 1364 | Field ‘age‘ doesn‘t have a default value | +---------+------+------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM tb1; #自动把age字段填充为0 +----+-------+-----+ | id | name | age | +----+-------+-----+ | 1 | jack | 29 | | 2 | jason | 39 | | 3 | tom | 37 | | 4 | luck | 22 | | 7 | root | 0 | | 8 | root | 0 | | 9 | root | 0 | +----+-------+-----+ 7 rows in set (0.08 sec)
3.2、replace：用来替换表的数据，如果源表中没有此数据则增加数据
mysql> DESC tb2; #name字段是唯一键索引 +-------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | char(20) | NO | UNI | NULL | | | age | tinyint(3) unsigned | NO | | NULL | | +-------+---------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM tb2; +----+-------+-----+ | id | name | age | +----+-------+-----+ | 1 | jack | 29 | | 2 | jason | 39 | | 3 | tom | 37 | | 4 | luck | 22 | +----+-------+-----+ 4 rows in set (0.00 sec) mysql> REPLACE INTO tb2 SET name=‘jack‘,age=33; #因原表中有jack这个用户，replace这个命令会先删除原来的那一行，再新增加一行，所以会有两行受到影响 Query OK, 2 rows affected (0.04 sec) mysql> REPLACE INTO tb2 SET name=‘zhaochj‘,age=29; #因原表中没有zhaochj这个用户，所以直接在最后增加 Query OK, 1 row affected (0.03 sec) mysql> SELECT * FROM tb2; +----+---------+-----+ | id | name | age | +----+---------+-----+ | 2 | jason | 39 | | 3 | tom | 37 | | 4 | luck | 22 | | 5 | jack | 33 | | 6 | zhaochj | 29 | +----+---------+-----+ 5 rows in set (0.02 sec)
3.3、update：修改表中的数据
mysql> SELECT * FROM tb2; +----+---------+-----+ | id | name | age | +----+---------+-----+ | 2 | jason | 39 | | 3 | tom | 37 | | 4 | luck | 22 | | 5 | jack | 33 | | 6 | zhaochj | 29 | +----+---------+-----+ 5 rows in set (0.02 sec) mysql> UPDATE tb2 SET age=33 WHERE name=‘zhaochj‘; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> UPDATE tb2 SET age=11 LIMIT 2; #修改前两行的年龄为11 Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> SELECT * FROM tb2; +----+---------+-----+ | id | name | age | +----+---------+-----+ | 2 | jason | 11 | | 3 | tom | 11 | | 4 | luck | 22 | | 5 | jack | 33 | | 6 | zhaochj | 33 | +----+---------+-----+ 5 rows in set (0.02 sec)
3.4、delete 删除表中数据
mysql> SELECT * FROM tb2; +----+---------+-----+ | id | name | age | +----+---------+-----+ | 2 | jason | 11 | | 3 | tom | 11 | | 4 | luck | 22 | | 5 | jack | 33 | | 6 | zhaochj | 33 | +----+---------+-----+ 5 rows in set (0.02 sec) mysql> mysql> DELETE FROM tb2 WHERE name=‘zhaochj‘; #加上where子句限定删除的范围 Query OK, 1 row affected (0.38 sec) mysql> SELECT * FROM tb2; +----+-------+-----+ | id | name | age | +----+-------+-----+ | 2 | jason | 11 | | 3 | tom | 11 | | 4 | luck | 22 | | 5 | jack | 33 | +----+-------+-----+ 4 rows in set (0.00 sec) mysql> DELETE FROM tb2; #这样直接把表中的数据全部删除 Query OK, 4 rows affected (0.01 sec) mysql> SELECT * FROM tb2; Empty set (0.00 sec) mysql> INSERT INTO tb2 (name,age) VALUES (‘zcj‘,29); #试着插入一行数据 Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM tb2; #查看数据发现id号不是从"1"开始的，这是因为字段“id”被定义成了“auto_increment”了 +----+------+-----+ | id | name | age | +----+------+-----+ | 7 | zcj | 29 | +----+------+-----+ 1 row in set (0.03 sec) mysql> TRUNCATE TABLE tb2; #清空一张表，应该用这个命令，表示重置表，会清空“auto_increment”的计数器 Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO tb2 (name,age) VALUES (‘zcj‘,29); Query OK, 1 row affected (0.03 sec) mysql> SELECT * FROM tb2; #id号又从“1”开始 +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | zcj | 29 | +----+------+-----+ 1 row in set (0.00 sec)
4、DCL语句
mysql> GRANT select ON mydb2.tb1 TO ‘mydb2user1‘@‘%‘ IDENTIFIED BY ‘111111‘; #对mydb2数据库中的tb1表授予mydb2user1用户可在任意主机以6个1为密码登陆，但权限只有select mysql> SHOW GRANTS FOR mydb2user1; #显示mydb2user1用户的权限信息 +-----------------------------------------------------------------------------------------------------------+ | Grants for mydb2user1@% | +-----------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO ‘mydb2user1‘@‘%‘ IDENTIFIED BY PASSWORD ‘*FD571203974BA9AFE270FE62151AE967ECA5E0AA‘ | | GRANT SELECT ON `mydb2`.`tb1` TO ‘mydb2user1‘@‘%‘ | +-----------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) 验证权限是否生效： [root@mariadb ~]# mysql -umydb2user1 -p111111 #以mydb2user1登陆 mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mydb2 | | test | +--------------------+ mysql> USE mydb2; Database changed mysql> SHOW TABLES; +-----------------+ | Tables_in_mydb2 | +-----------------+ | tb1 | +-----------------+ mysql> SELECT * FROM tb1; +------+------+ | id | name | +------+------+ | 1 | Zcj | | 2 | Tom | | 3 | Jack | +------+------+ mysql> INSERT INTO tb1 (id,name) VALUES (4,‘Cora‘); #没有权限插入数据 ERROR 1142 (42000): INSERT command denied to user ‘mydb2user1‘@‘localhost‘ for table ‘tb1‘ mysql> GRANT insert ON mydb2.tb1 TO ‘mydb2user1‘@‘%‘; #再赋予insert权限 Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR mydb2user1; +-----------------------------------------------------------------------------------------------------------+ | Grants for mydb2user1@% | +-----------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO ‘mydb2user1‘@‘%‘ IDENTIFIED BY PASSWORD ‘*FD571203974BA9AFE270FE62151AE967ECA5E0AA‘ | | GRANT SELECT, INSERT ON `mydb2`.`tb1` TO ‘mydb2user1‘@‘%‘ | +-----------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) [root@mariadb ~]# mysql -umydb2user1 -p111111 #再以mydb2user1登陆 mysql> INSERT INTO tb1 (id,name) VALUES (4,‘Cora‘); Query OK, 1 row affected (0.07 sec) mysql> SELECT * FROM tb1; #数据已插入 +------+------+ | id | name | +------+------+ | 1 | Zcj | | 2 | Tom | | 3 | Jack | | 4 | Cora | +------+------+ 4 rows in set (0.00 sec)4.2、回收权限
[root@mariadb ~]# mysql -uroot -p123456 #以root登陆 mysql> REVOKE insert ON mydb2.tb1 FROM mydb2user1; Query OK, 0 rows affected (0.00 sec) [root@mariadb ~]# mysql -umydb2user1 -p111111 #以mydb2user1登陆 mysql> INSERT INTO mydb2.tb1 (id,name) VALUES (5,‘Lucky‘); #无法插入数据，insert权限已被回收 ERROR 1142 (42000): INSERT command denied to user ‘mydb2user1‘@‘localhost‘ for table ‘tb1‘
5、事务相关语句
mysql> START TRANSACTION; #开始事务
mysql> SAVEPOINT point_name; #保存一个事务的位置,以方便撤销事务
mysql> ROLLBACK TO point_name; #回滚到一个点
mysql> ROLLBACK; #回滚到开始事务时的状态
mysql> COMMIT; #提交事务
举例：
mysql> START TRANSACTION; #开始事务 Query OK, 0 rows affected (0.02 sec) mysql> SELECT * FROM tb1; +------+------+ | id | name | +------+------+ | 1 | Zcj | | 2 | Tom | | 3 | Jack | | 4 | Cora | +------+------+ 4 rows in set (0.00 sec) mysql> INSERT INTO tb1 (id,name) VALUES (5,‘Lucky‘); #插入一行数据 Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM tb1; +------+-------+ | id | name | +------+-------+ | 1 | Zcj | | 2 | Tom | | 3 | Jack | | 4 | Cora | | 5 | Lucky | +------+-------+ 5 rows in set (0.00 sec) mysql> SAVEPOINT p1; #在有id为5这行数据时创建一个保存点 Query OK, 0 rows affected (0.01 sec) mysql> DELETE FROM tb1 WHERE id = 2; #删除一行数据 Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM tb1; +------+-------+ | id | name | +------+-------+ | 1 | Zcj | | 3 | Jack | | 4 | Cora | | 5 | Lucky | +------+-------+ 4 rows in set (0.00 sec) mysql> ROLLBACK TO p1; #回滚到保存点p1的状态 Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM tb1; +------+-------+ | id | name | +------+-------+ | 1 | Zcj | | 2 | Tom | | 3 | Jack | | 4 | Cora | | 5 | Lucky | +------+-------+ 5 rows in set (0.00 sec) mysql> ROLLBACK; #回滚到事务开始时的状态 Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM tb1; +------+------+ | id | name | +------+------+ | 1 | Zcj | | 2 | Tom | | 3 | Jack | | 4 | Cora | +------+------+ 4 rows in set (0.00 sec) mysql> COMMIT; #提交事务 Query OK, 0 rows affected (0.00 sec)
6、mysql查询
为演示各种SELECT语句，建立以下几个表：
一)、表名为“students_tb”，字段为：StudentID，Name，Age，Gender，ClassID，分别存放学生的名字、年龄、性别、有班级的ID号，具体的班级存放在另一张表中
二)、表名为“classes_tb”，字段为：ClassID，Class，TeacherID，分别存放班级的ID号，具体的班级名，这个班级是哪个老师负责的ID号，具体的负责的老师存放在另一张表中
三)、表名为“teacher_tb”，字段为：TeacheID，Name，Age，Gender，分别存放老师的ID号，老师的名字，年龄，性别
mysql> USE mydb1; mysql> CREATE TABLE IF NOT EXISTS students_tb (StudentID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Name CHAR(30) NOT NULL,Age TINYINT UNSIGNED,Gender ENUM(‘M‘,‘F‘),ClassID TINYINT UNSIGNED NOT NULL); mysql> INSERT INTO students_tb (Name,Age,Gender,ClassID) VALUES (‘Tom‘,17,‘M‘,1),(‘Jack‘,18,‘M‘,3),(‘Lucy‘,21,‘F‘,6),(‘Jimima‘,15,‘F‘,4),(‘Jimmy‘,30,‘M‘,9),(‘Jim‘,26,‘M‘,7); mysql> SELECT * FROM students_tb; +-----------+--------+------+--------+---------+ | StudentID | Name | Age | Gender | ClassID | +-----------+--------+------+--------+---------+ | 1 | Tom | 17 | M | 1 | | 2 | Jack | 18 | M | 3 | | 3 | Lucy | 21 | F | 6 | | 4 | Jimima | 15 | F | 4 | | 5 | Jimmy | 30 | M | 9 | | 6 | Jim | 26 | M | 7 | +-----------+--------+------+--------+---------+ mysql> CREATE TABLE IF NOT EXISTS classes_tb (ClassID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Class CHAR(20) NOT NULL,TeacherID TINYINT UNSIGNED NOT NULL); mysql> ALTER TABLE classes_tb ADD UNIQUE KEY (Class); #这个表中的班别应该是唯一的，所以用此sql语句来修改 Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC classes_tb; +-----------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------------+------+-----+---------+----------------+ | ClassID | tinyint(3) unsigned | NO | PRI | NULL | auto_increment | | Class | char(20) | NO | UNI | NULL | | | TeacherID | tinyint(3) unsigned | NO | | NULL | | +-----------+---------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> INSERT INTO classes_tb (Class,TeacherID) VALUES (‘class1‘,5),(‘class2‘,2),(‘class3‘,5),(‘class4‘,2),(‘class5‘,4),(‘class6‘,1); mysql> SELECT * FROM classes_tb; +---------+--------+-----------+ | ClassID | Class | TeacherID | +---------+--------+-----------+ | 1 | class1 | 5 | | 2 | class2 | 2 | | 3 | class3 | 5 | | 4 | class4 | 2 | | 5 | class5 | 4 | | 6 | class6 | 1 | +---------+--------+-----------+ mysql> CREATE TABLE IF NOT EXISTS teacher_tb (TeacherID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Name CHAR(30) NOT NULL,Age TINYINT UNSIGNED,Gender ENUM(‘M‘,‘F‘)); mysql> INSERT INTO teacher_tb (Name,Age,Gender) VALUES (‘Wang baoqiang‘,30,‘M‘),(‘Zhang shanfeng‘,32,‘F‘),(‘Zhao qiang‘,40,‘M‘),(‘Ying hui‘,50,‘M‘),(‘Feng bing‘,46,‘M‘),(‘Qian qiang‘,37,‘M‘),(‘Shun bin‘,68,‘M‘); mysql> SELECT * FROM teacher_tb; +-----------+----------------+------+--------+ | TeacherID | Name | Age | Gender | +-----------+----------------+------+--------+ | 1 | Wang baoqiang | 30 | M | | 2 | Zhang shanfeng | 32 | F | | 3 | Zhao qiang | 40 | M | | 4 | Ying hui | 50 | M | | 5 | Feng bing | 46 | M | | 6 | Qian qiang | 37 | M | | 7 | Shun bin | 68 | M | +-----------+----------------+------+--------+ 三个表创建及数据插入完成。
6.1、单表简单查询
语法：
SELECT 字段，字段，... FROM 表名 WHERE 子句;
举例：
查询students_tb表中前5个学生的姓名和年龄
mysql> SELECT Name,Age FROM students_tb LIMIT 5; +--------+------+ | Name | Age | +--------+------+ | Tom | 17 | | Jack | 18 | | Lucy | 21 | | Jimima | 15 | | Jimmy | 30 | +--------+------+
a)、比较运算：>,<,>=,<=,!=,<>,<=>
在students_tb表中查询年龄大于20的学生有哪些：
mysql> SELECT Name,Age FROM students_tb WHERE Age>20; +-------+------+ | Name | Age | +-------+------+ | Lucy | 21 | | Jimmy | 30 | | Jim | 26 | +-------+------+
在students_tb表中查询年龄不等于30的学生有哪些：
mysql> SELECT Name,Age FROM students_tb WHERE Age!=30; +--------+------+ | Name | Age | +--------+------+ | Tom | 17 | | Jack | 18 | | Lucy | 21 | | Jimima | 15 | | Jim | 26 | +--------+------+在students_tb表中查询年龄不等于30和不等于15的学生有哪些：
mysql> SELECT Name,Age FROM students_tb WHERE Age!=30 and Age!=15; +------+------+ | Name | Age | +------+------+ | Tom | 17 | | Jack | 18 | | Lucy | 21 | | Jim | 26 | +------+------+
b)、BETWEEN...AND..表示在一个范围内的数据
在students_tb表中查询年龄在20到30之间的学生：
mysql> SELECT Name,Age FROM students_tb WHERE Age BETWEEN 20 AND 30; +-------+------+ | Name | Age | +-------+------+ | Lucy | 21 | | Jimmy | 30 | | Jim | 26 | +-------+------+
本文出自 “知识需要总结与记录” 博客，请务必保留此出处http://zhaochj.blog.51cto.com/368705/1629759
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
