MySQL 入门了
MySQL的相关概念介绍
MySQL 是关系型数据库(Relational Database Management System),
这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成。
, 如图所示的一个表格:
- 表头(header): 每一列的名称;
- 列(row): 具有相同数据类型的数据的集合;
- 行(col): 每一行用来描述某个人/物的具体信息;
- 值(value): 行的具体信息, 每个值必须与该列的数据类型相同;
- 键(key): 表中用来识别某个特定的人\物的方法, 键的值在当前列中具有唯一性。
思维导图
-- 画这个图的时候只有我和上帝知道它是什么意思。
通俗的概述
数据库系统:
数据库:就相当于文件夹
数据表:就是文件夹里的文件
表记录:一条数据 (是数据库中最小的一个载体,也是一个对象)
显示数据的形式:像excel表格一样
数据库软件:
mysql : 开源
oracle
sqlserver
mysql :
服务端: mysqld
客户端: cmd php python
数据库操作
show databases; 查看数据库软件中都有那些数据库(后面的分号必须有,这是语法规范)
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set
create database tony; 创建一个数据库 ,(create 创建,创建什么,database 数据库,数据的名字叫什么 自己写,以分号结尾)
Query OK, 1 row affected
show databases; 查看数据库软件中都有那些数据库(后面的分号必须有,这是语法规范)
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| tony | ----------------------------->>> 可以看到刚刚创建的 tony 数据库
+--------------------+
5 rows in set
show create database tony; 查看创建数据库的创建信息 (分号结尾)
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| tony | CREATE DATABASE `tony` /*!40100 DEFAULT CHARACTER SET utf8 */ | -- CHARACTER SET utf8 */ 字符集,用的 utf8 可以修改。
+----------+---------------------------------------------------------------+
1 row in set
show create database tony character set utf8 ; 创建的时候设置好字符集 utf8 不是utf-8
后面如果 insert 中文时 ,不让插入的时候需要注意一下这里的字符集
use tony; 切换数据库;
Database changed
select database(); 全局命令,查看所在数据库
+------------+
| database() |
+------------+
| tony |
+------------+
1 row in set
举个栗子
/* 多行注释, MySql 操作数据库命令 */ mysql> show databases; -------------------------------- 显示所有数据库名称 show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | tony | | tony_1 | +--------------------+ rows in set mysql> create database tony_3; ------------------------ 创建名字tony_3的数据库 create database db_name; Query OK, 1 row affected mysql> show databases; -------------------------------- 显示所有数据库名称 show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | tony | | tony_1 | | tony_3 | -------------------------------- 创建成功的tony_3数据库 +--------------------+ rows in set mysql> show create database tony_3; ------------------- 查看创建信息 show create database db_name; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | tony_3 | CREATE DATABASE `tony_3` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+-----------------------------------------------------------------+ row in set mysql> use tony_3; ------------------------------------ 切换数据库 想linux cd 命令一样。 use db_name; Database changed mysql> select database(); ----------------------------- 查看当前所在的数据库,全局的命令。select database(); +------------+ | database() | +------------+ | tony_3 | +------------+ row in set mysql> alter database tony_3 character set gbk; ---------- 修改数据库的字符集,有时遇到 insert 中文时,不允许的情况时,看看字符集。alter database db_name character set xxxx; Query OK, 1 row affected ---------- 开始创建时字符集是 utf8 刚刚修改过 mysql> show create database tony_3; -------------------- 查看数据库的创建信息,刚刚修改的字符集生效了。 show create database db_name; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | tony_3 | CREATE DATABASE `tony_3` /*!40100 DEFAULT CHARACTER SET gbk */ | +----------+----------------------------------------------------------------+ row in set mysql> show databases; ------------------------------------ 显示所有数据库名称 show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | tony | | tony_1 | | tony_3 | +--------------------+ rows in set mysql> drop database tony_3; -------------------------------- 删除数据库 drop database db_name; Query OK, 0 rows affected mysql> show databases; ------------------------------------- 删除后查看一下 show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | tony | | tony_1 | +--------------------+ rows in set mysql> MySQL 操作
数据库操作总结:
show databases; 显示所有数据库,(规范是都用大写);
create database database_name [ character set utf8 ]; 创建一个数据库,中括号里的是可选的,不写就是默认的;
show create database database_name; 显示创建数据库的创建信息;
use database_name; 切换数据库;
alter database db_name [ character set xxx ] 只是修个字符集,修改别的不好使。
drop database db_name; 删除数据库; 慎用,连同数据一起删除。
select database(); 看一看所在数据库;
-- 1.创建数据库(在磁盘上创建一个对应的文件夹) create database [if not exists] db_name [character set xxx] -- 2.查看数据库 show databases;查看所有数据库 show create database db_name; 查看数据库的创建方式 -- 3.修改数据库 alter database db_name [character set xxx] -- 4.删除数据库 drop database [if exists] db_name; -- 5.使用数据库 切换数据库 use db_name; -- 注意:进入到某个数据库后没办法再退回之前状态,但可以通过use进行切换 查看当前使用的数据库 select database();
数据表的操作
创建数据表:
use db_name; -- 进入数据库
select database(); -- 或者查看当前所在的数据库
create table table_name (
name varchar(20),
age int,
gender varchar(10));
----------- create table ta_name(); 括号里写字段,此时可以看做操作execl 往里写东西了。
每个字段都要有各自数据类型(可以重复),字段还要完整性约束(意思当前的字段是否唯一,是否可以为空,等) 字段之间用逗号分开,
创建表: create table table_name ( filed 字段类型 [完整性约束], filed 字段类型 [完整性约束], filed 字段类型 [完整性约束], );
主键约束:
primary key : 非空(not null )且 唯一 (unique), 在索引取值的时候,避免普通字段有重复的值,所以用一个主键来约束。有且只有一个。
not null unique 约束的字段不一定是主键,也可约束多个字段,但是主键一定是 非空且唯一的。primary key
表结构中没有primary key 约束,会以第一个 not null and unique 为主键。
在 select 时 用 primay key ,比普通字段快。primary key 特性 非空且唯一
外键约束(关联字段)
举个栗子
CREATE TABLE employee( id int primary key auto_increment , name varchar(20), gender bit default 1, birthday date, department varchar(20), salary double(8,2) unsigned, resume text );
查看数据表:
desc tab_name; --- 查看表结构,查看刚刚设置的一些字段的信息。
mysql> desc c1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra | field 字段 , type 数据类型,Null 是否为空,就是可以不写吗?,没有指定约束,默认可以为空。
+-------+-------------+------+-----+---------+-------+ key 键 ,主键约束,外键 约束 ,Default 默认值,没有一个字段不需要添加的时候给个默认值。
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | | int(11) 默认 整数的长度。11 为的数字。
+-------+-------------+------+-----+---------+-------+
show create table c1; --- 查看 表的创建信息
show create table teach;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| teach | CREATE TABLE `teach` (
`name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`gender` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | ENGINE 是引擎 默认lnnoDB
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
show tables; ----- 查看数据库中所有的表名
show tables;
+----------------+
| Tables_in_tony | --- 数据库所有的表
+----------------+
| teach | --- 第一个
| tony_ble | --- 第二个
+----------------+
2 rows in set
2 查看表 desc tab_name -- 查看表结构 show columns from tab_name -- 查看表结构 show tables -- 查看当前数据库中的所有的表 show create table tab_name -- 查看当前数据库表建表语句
举个栗子
mysql> select database(); ------- 查看当前所属的数据库 select database(); 类似于Linux pwd +------------+ | database() | +------------+ | tony | +------------+ row in set mysql> use tony; --------------- 切换到tony 数据库 use db_name; 类似于Linux cd Database changed mysql> show tables; --------------- 显示该数据库中所有的数据表 show tables; 类似于Linux ls file +----------------+ | Tables_in_tony | +----------------+ | teach | +----------------+ row in set mysql> create table tony_ble( --------------------- 创建表 create table db_name(fiedler type [ 完整性约束 ]); 类似于Linux touch file name varchar(20), age int, gender varchar(20)); Query OK, 0 rows affected mysql> show create table ---------------------- 查看创建表信息 show create table; 类似于Linux ll -a tony_ble; +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tony_ble | CREATE TABLE `tony_ble` ( `name` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL, `gender` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ row in set mysql> desc tony_ble; ------------------------- 查看表结构 desc table_name; 类似于Linux vim file +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | | gender | varchar(20) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ rows in set mysql> 创建表和查看表结构
修改表结构
alter table table_name add field type [ 完整性约束 ]
-- 创建完数据表后,增加字段。
alter table table_name modify field type [ 完整性约束 ]
-- 创建完数据表后,修改字段的约束。
alter table table_name change field type [ 完整性约束 ]
-- 创建完数据表后,修改名字。
alter table table_name drop field type [ 完整性约束 ]
-- 删除一个字段。
alter table table_name rename new_tab_name;
-- 重命名数据表名字
举个栗子
mysql> desc teach; -- 先查看一下表结构信息 +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | age | int(11) | NO | | NULL | | | gender | varchar(20) | NO | | NULL | | | salary | int(11) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ rows in set mysql> alter table teach add TEL int; -- 增加一个字段没有约束 Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc teach; -- 一眼 TEL 字段增加上了 +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | age | int(11) | NO | | NULL | | | gender | varchar(20) | NO | | NULL | | | salary | int(11) | YES | | NULL | | | TEL | int(11) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ rows in set mysql> alter table teach drop TEL; -- 删除字段 Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc teach; -- 一眼 TEL 字段被删除了 +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | age | int(11) | NO | | NULL | | | gender | varchar(20) | NO | | NULL | | | salary | int(11) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ rows in set mysql> alter table teach add TEL int not null; -- 增加字段的同时加上约束性 Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc teach; -- 一眼 新增加字段,完整性约束 +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | age | int(11) | NO | | NULL | | | gender | varchar(20) | NO | | NULL | | | salary | int(11) | YES | | NULL | | | TEL | int(11) | NO | | NULL | | +--------+-------------+------+-----+---------+----------------+ rows in set mysql> alter table teach modify TEL int; -- 修改字段约束性,默认 null 可以为空。 Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc teach; -- 一眼 字段完整性约束被修改了。 +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | age | int(11) | NO | | NULL | | | gender | varchar(20) | NO | | NULL | | | salary | int(11) | YES | | NULL | | | TEL | int(11) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ rows in set mysql> select * from teach; -- 看一眼每一条数据 记住字段 gender +----+-----------+-----+--------+--------+-----+ | id | name | age | gender | salary | TEL | +----+-----------+-----+--------+--------+-----+ | 1 | Liushubao | 29 | male | NULL | 0 | | 2 | Tony | 29 | male | NULL | 0 | | 3 | TONY | 27 | MALE | NULL | 0 | | 4 | Tony | 26 | male | NULL | 0 | | 5 | Liushubao | 29 | male | NULL | 0 | | 6 | Tony | 29 | male | NULL | 0 | | 7 | TONY | 27 | MALE | NULL | 0 | | 8 | Tony | 26 | male | NULL | 0 | | 9 | Liushubao | 29 | male | NULL | 0 | | 10 | Tony | 29 | male | NULL | 0 | | 11 | TONY | 27 | MALE | NULL | 0 | | 12 | Tony | 26 | male | NULL | 0 | | 13 | Liushubao | 29 | male | NULL | 0 | | 14 | Tony | 29 | male | NULL | 0 | | 15 | TONY | 27 | MALE | NULL | 0 | | 16 | Tony | 26 | male | NULL | 0 | | 17 | Liushubao | 29 | male | NULL | 0 | | 18 | Tony | 29 | male | NULL | 0 | | 19 | TONY | 27 | MALE | NULL | 0 | | 20 | Tony | 26 | male | NULL | 0 | | 21 | Liushubao | 29 | male | NULL | 0 | | 22 | Tony | 29 | male | NULL | 0 | | 23 | TONY | 27 | MALE | NULL | 0 | | 24 | Tony | 26 | male | NULL | 0 | | 25 | Liushubao | 29 | male | NULL | 0 | | 26 | Tony | 29 | male | NULL | 0 | | 27 | TONY | 27 | MALE | NULL | 0 | | 28 | Tony | 26 | male | NULL | 0 | +----+-----------+-----+--------+--------+-----+ rows in set mysql> alter table teach change gender xingbie varchar(20) not null; -- 修该字段的名字,继承以前的完整性约束。 Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc teach; -- 一眼 gender 字段 修个成 xingbie +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | age | int(11) | NO | | NULL | | | xingbie | varchar(20) | NO | | NULL | | | salary | int(11) | YES | | NULL | | | TEL | int(11) | YES | | NULL | | +---------+-------------+------+-----+---------+----------------+ rows in set mysql> select * from teach; -- 字段名字修改对应的数据不影响 +----+-----------+-----+---------+--------+-----+ | id | name | age | xingbie | salary | TEL | +----+-----------+-----+---------+--------+-----+ | 1 | Liushubao | 29 | male | NULL | 0 | | 2 | Tony | 29 | male | NULL | 0 | | 3 | TONY | 27 | MALE | NULL | 0 | | 4 | Tony | 26 | male | NULL | 0 | | 5 | Liushubao | 29 | male | NULL | 0 | | 6 | Tony | 29 | male | NULL | 0 | | 7 | TONY | 27 | MALE | NULL | 0 | | 8 | Tony | 26 | male | NULL | 0 | | 9 | Liushubao | 29 | male | NULL | 0 | | 10 | Tony | 29 | male | NULL | 0 | | 11 | TONY | 27 | MALE | NULL | 0 | | 12 | Tony | 26 | male | NULL | 0 | | 13 | Liushubao | 29 | male | NULL | 0 | | 14 | Tony | 29 | male | NULL | 0 | | 15 | TONY | 27 | MALE | NULL | 0 | | 16 | Tony | 26 | male | NULL | 0 | | 17 | Liushubao | 29 | male | NULL | 0 | | 18 | Tony | 29 | male | NULL | 0 | | 19 | TONY | 27 | MALE | NULL | 0 | | 20 | Tony | 26 | male | NULL | 0 | | 21 | Liushubao | 29 | male | NULL | 0 | | 22 | Tony | 29 | male | NULL | 0 | | 23 | TONY | 27 | MALE | NULL | 0 | | 24 | Tony | 26 | male | NULL | 0 | | 25 | Liushubao | 29 | male | NULL | 0 | | 26 | Tony | 29 | male | NULL | 0 | | 27 | TONY | 27 | MALE | NULL | 0 | | 28 | Tony | 26 | male | NULL | 0 | +----+-----------+-----+---------+--------+-----+ rows in set mysql> desc teach; -- 查看数据结构 +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | age | int(11) | NO | | NULL | | | xingbie | varchar(20) | NO | | NULL | | | salary | int(11) | YES | | NULL | | | TEL | int(11) | YES | | NULL | | +---------+-------------+------+-----+---------+----------------+ rows in set mysql> alter table teach modify TEL int not null; -- 修改字段名字和完整性约束 Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc teach; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | age | int(11) | NO | | NULL | | | xingbie | varchar(20) | NO | | NULL | | | salary | int(11) | YES | | NULL | | | TEL | int(11) | NO | | NULL | | +---------+-------------+------+-----+---------+----------------+ rows in set mysql> alter table teach modify TEL int -- 修改字段的完整性约束 ; Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc teach; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | age | int(11) | NO | | NULL | | | xingbie | varchar(20) | NO | | NULL | | | salary | int(11) | YES | | NULL | | | TEL | int(11) | YES | | NULL | | +---------+-------------+------+-----+---------+----------------+ rows in set mysql> alter table teach change TEL tel int not null; -- 修改字段名字和完整性约束 Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc teach; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | age | int(11) | NO | | NULL | | | xingbie | varchar(20) | NO | | NULL | | | salary | int(11) | YES | | NULL | | | tel | int(11) | NO | | NULL | | +---------+-------------+------+-----+---------+----------------+ rows in set mysql> alter table teach change TEL tel int -- 修改字段名字和完整性约束,明明TEL 没有,只有tel 但是也不报错,且完整性约束修改了。不区分大小写。 ; Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc teach; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | age | int(11) | NO | | NULL | | | xingbie | varchar(20) | NO | | NULL | | | salary | int(11) | YES | | NULL | | | tel | int(11) | YES | | NULL | | +---------+-------------+------+-----+---------+----------------+ rows in set mysql> alter table teach change TEL tel int not null; -- -- 再次验证 修改字段名字和完整性约束,明明TEL 没有,只有tel 但是也不报错,且完整性约束修改了。不区分大小写。 Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc teach; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | age | int(11) | NO | | NULL | | | xingbie | varchar(20) | NO | | NULL | | | salary | int(11) | YES | | NULL | | | tel | int(11) | NO | | NULL | | +---------+-------------+------+-----+---------+----------------+ rows in set mysql> alter table teach drop tel; -- 删除一个字段 Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc teach; -- tel 字段以被删除 +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | age | int(11) | NO | | NULL | | | xingbie | varchar(20) | NO | | NULL | | | salary | int(11) | YES | | NULL | | +---------+-------------+------+-----+---------+----------------+ rows in set mysql> alter table teach change SALARY xinshui int not null; -- 修改字段的名字 大小写不分,完整性约束需要继承以前的。 - Invalid use of NULL value mysql> alter table teach change SALARY xinshui int --- -- 修改字段的名字 大小写不分,完整性约束需要继承以前的。 ; Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc teach; -- 完整性约束不变呢 +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | age | int(11) | NO | | NULL | | | xingbie | varchar(20) | NO | | NULL | | | xinshui | int(11) | YES | | NULL | | +---------+-------------+------+-----+---------+----------------+ ------------------------------------------------------------------------------- mysql> desc teach; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | age | int(11) | NO | | NULL | | | gender | varchar(20) | NO | | NULL | | | salary | int(11) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ rows in set mysql> alter table teach modify name varchar(10) not null; ------------- 修改完整性约束 Query OK, 28 rows affected Records: 28 Duplicates: 0 Warnings: 0 mysql> desc teach; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | age | int(11) | NO | | NULL | | | gender | varchar(20) | NO | | NULL | | | salary | int(11) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ rows in set 修该表结构 -- ------------------------------------- -- 重命名数据表 mysql> show tables; +--------------+ | Tables_in_c1 | +--------------+ | emp | +--------------+ 1 row in set mysql> alter table emp rename my_emp; -- 重名重数据表 Query OK, 0 rows affected mysql> show tables; -- 查看 +--------------+ | Tables_in_c1 | +--------------+ | my_emp | +--------------+ 1 row in set mysql> alter table my_emp rename emp; -- 再改过来 Query OK, 0 rows affected mysql> show tables; -- 没毛病 +--------------+ | Tables_in_c1 | +--------------+ | emp | +--------------+ 1 row in set
表记录操作
增加记录
此时就相当于给execl 里 写入一条条数据。先看一下整个表的结构,有那些字段,每个字段的完整性约束。对应好去创建记录。
insert [into] tab_name (field1,field2....) values (values1,values2....) , (values1,values2....), ... ; insert tab_name set field=value, field=value,..... -- 需要注意的是 insert 后面的 into 可以不加,效果一样 ---------------------------------------------------------------- 增加记录: insert [into] tab_name (field1,field2....) values (values1,values2....) , (values1,values2....), ... ; -- insert 插入数据 into 可选,往哪里插入数据 直接跟 tab_name 后面跟 (字段1,字段2,字段3)values (字段1的值,字段2的值) ,(),() -- 多行添加 值 逗号分隔开,直接写值。最后的分号不能少; -- ---------- 第二种 field = value 添加方式 insert [into] set field= value,fiel2=value2,field3=value3; -- 这种一一对应的方式 来实现 添加记录
举个栗子
1 增加记录: insert [into] tab_name (field1,field2....) values (values1,values2....) , (values1,values2....), ... ; -- insert 插入数据 into 可选,往哪里插入数据 直接跟 tab_name 后面跟 (字段1,字段2,字段3)values (字段1的值,字段2的值) ,(),() -- 多行添加 值 逗号分隔开,直接写值。最后的分号不能少; -- ---------- 第二种 field = value 添加方式 insert [into] set field= value,fiel2=value2,field3=value3; -- 这种一一对应的方式 来实现 添加记录 -- 例子 --------------------------------------------------------------------- select * from emp; +----+------+-----+--------+--------+------------+-----+ | id | name | age | gender | salary | bir | tel | +----+------+-----+--------+--------+------------+-----+ | 1 | tony | 29 | 1 | 30000 | 1989-02-06 | 110 | +----+------+-----+--------+--------+------------+-----+ 1 row in set mysql> insert into emp(name,age,gender,salary,bir,tel)values('黄渤',39,1,30000,'1969-12-26',111),('刘德华',50,1,50000,'1962-2-12',123), ('林志玲',40,0,10000,'1976-4-5',12344); Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0 select * from emp; +----+--------+-----+--------+--------+------------+-------+ | id | name | age | gender | salary | bir | tel | +----+--------+-----+--------+--------+------------+-------+ | 1 | tony | 29 | 1 | 30000 | 1989-02-06 | 110 | | 2 | 黄渤 | 39 | 1 | 30000 | 1969-12-26 | 111 | | 3 | 刘德华 | 50 | 1 | 50000 | 1962-02-12 | 123 | | 4 | 林志玲 | 40 | 0 | 10000 | 1976-04-05 | 12344 | +----+--------+-----+--------+--------+------------+-------+ ------------------------------------------------------------ mysql> insert emp set name='任贤齐',age=40,gender=1,salary=50000,bir='1966-2-19',tel=4773; Query OK, 1 row affected mysql> select * from emp; +----+--------+-----+--------+--------+------------+-------+ | id | name | age | gender | salary | bir | tel | +----+--------+-----+--------+--------+------------+-------+ | 1 | tony | 29 | 1 | 30000 | 1989-02-06 | 110 | | 2 | 黄渤 | 39 | 1 | 30000 | 1969-12-26 | 111 | | 3 | 刘德华 | 50 | 1 | 50000 | 1962-02-12 | 123 | | 4 | 林志玲 | 40 | 0 | 10000 | 1976-04-05 | 12344 | | 9 | 任贤齐 | 40 | 1 | 50000 | 1966-02-19 | 4773 | ---- id ? +----+--------+-----+--------+--------+------------+-------+ 5 rows in set -- 这里的 ID 是9 跟下面的删除数据情况数据 的方法有关系
修改记录
针对已有的表记录,对其更新。
update tab_name set field=value where 子句 -- 如果不加 where 子句 条件筛选的话,所更改的可就是整个表记录了 ,so 该不该加 过滤条件,看情况定。
举个栗子
select * from emp; +----+--------+-----+--------+--------+------------+-------+ | id | name | age | gender | salary | bir | tel | +----+--------+-----+--------+--------+------------+-------+ | 1 | tony | 29 | 1 | 30000 | 1989-02-06 | 110 | | 2 | 黄渤 | 39 | 1 | 30000 | 1969-12-26 | 111 | | 3 | 刘德华 | 50 | 1 | 50000 | 1962-02-12 | 123 | | 4 | 林志玲 | 40 | 0 | 10000 | 1976-04-05 | 12344 | | 9 | 任贤齐 | 40 | 1 | 50000 | 1966-02-19 | 4773 | +----+--------+-----+--------+--------+------------+-------+ 5 rows in set update emp set salary=20000 where id=4; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from emp; +----+--------+-----+--------+--------+------------+-------+ | id | name | age | gender | salary | bir | tel | +----+--------+-----+--------+--------+------------+-------+ | 1 | tony | 29 | 1 | 30000 | 1989-02-06 | 110 | | 2 | 黄渤 | 39 | 1 | 30000 | 1969-12-26 | 111 | | 3 | 刘德华 | 50 | 1 | 50000 | 1962-02-12 | 123 | | 4 | 林志玲 | 40 | 0 | 20000 | 1976-04-05 | 12344 | | 9 | 任贤齐 | 40 | 1 | 50000 | 1966-02-19 | 4773 | +----+--------+-----+--------+--------+------------+-------+ 5 rows in set ------------- 来一个没有 where 字句 的 尝尝 update emp set tel=123456; Query OK, 5 rows affected Rows matched: 5 Changed: 5 Warnings: 0 mysql> select * from emp; +----+--------+-----+--------+--------+------------+--------+ | id | name | age | gender | salary | bir | tel | +----+--------+-----+--------+--------+------------+--------+ | 1 | tony | 29 | 1 | 30000 | 1989-02-06 | 123456 | | 2 | 黄渤 | 39 | 1 | 30000 | 1969-12-26 | 123456 | | 3 | 刘德华 | 50 | 1 | 50000 | 1962-02-12 | 123456 | | 4 | 林志玲 | 40 | 0 | 20000 | 1976-04-05 | 123456 | | 9 | 任贤齐 | 40 | 1 | 50000 | 1966-02-19 | 123456 | +----+--------+-----+--------+--------+------------+--------+ 5 rows in set
删除和清楚记录
删除一条表记录或者全部记录。注意: 有字段的完整性约束设置的 auto_increment 的话。delete 删除一条或者整个记录时 自动增加的 会保留,下次插入时 继续递增。
truncate 则就是清空整个记录表。
delete from ta_name where 字句。
删除表记录: delete from tab_name where 子句 -- 条件过滤 清空表记录: 1 delete from tab_name -- 字句条件过滤,全部删除整个表记录,就等于剩个空表,auto_increment 不受影响,下次插入数据时 ,基于上次继续递增。 2 truncate tab_name (数据量大时推荐使用) -- 清空就是清空。all clear ,auto_increment 也会重置。
举个栗子
mysql> desc emp; --- 查看表结构,id 的完整性约束 auto_increment +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | int(11) | NO | | NULL | | | gender | tinyint(4) | YES | | NULL | | | salary | float(7,2) | YES | | NULL | | | bir | date | YES | | NULL | | | tel | int(11) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ mysql> select * from emp; +----+--------+-----+--------+--------+------------+--------+ | id | name | age | gender | salary | bir | tel | +----+--------+-----+--------+--------+------------+--------+ | 1 | tony | 29 | 1 | 30000 | 1989-02-06 | 123 | | 2 | 黄渤 | 39 | 1 | 30000 | 1969-12-26 | 345 | | 3 | 刘德华 | 50 | 1 | 50000 | 1962-02-12 | 1345 | | 9 | 任贤齐 | 40 | 1 | 50000 | 1966-02-19 | 123456 | | 10 | 林志玲 | 40 | 0 | 20000 | 1976-05-04 | 78787 | +----+--------+-----+--------+--------+------------+--------+ 5 rows in set mysql> delete from emp where id>4; Query OK, 2 rows affected mysql> select * from emp; +----+--------+-----+--------+--------+------------+------+ | id | name | age | gender | salary | bir | tel | +----+--------+-----+--------+--------+------------+------+ | 1 | tony | 29 | 1 | 30000 | 1989-02-06 | 123 | | 2 | 黄渤 | 39 | 1 | 30000 | 1969-12-26 | 345 | | 3 | 刘德华 | 50 | 1 | 50000 | 1962-02-12 | 1345 | +----+--------+-----+--------+--------+------------+------+ 3 rows in set --------------------- 插入新数据后 看 ID insert emp(name,age,gender,salary,bir,tel)values('任贤齐',40,1,50000,'1967-03-23',88372); Query OK, 1 row affected mysql> insert emp(name,age,gender,salary,bir,tel)values('任齐',50,1,50000,'1967-03-23',68372),('林志玲',40,0,25000,'1976-03-23',87234),('孙红雷',39,1,50000,'1977-03-30',98838); Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from emp; +----+--------+-----+--------+--------+------------+-------+ | id | name | age | gender | salary | bir | tel | +----+--------+-----+--------+--------+------------+-------+ | 1 | tony | 29 | 1 | 30000 | 1989-02-06 | 123 | | 2 | 黄渤 | 39 | 1 | 30000 | 1969-12-26 | 345 | | 3 | 刘德华 | 50 | 1 | 50000 | 1962-02-12 | 1345 | | 11 | 任贤齐 | 40 | 1 | 50000 | 1967-03-23 | 88372 | | 12 | 任齐 | 50 | 1 | 50000 | 1967-03-23 | 68372 | | 13 | 林志玲 | 40 | 0 | 25000 | 1976-03-23 | 87234 | | 14 | 孙红雷 | 39 | 1 | 50000 | 1977-03-30 | 98838 | +----+--------+-----+--------+--------+------------+-------+ 7 rows in set ------------ delete from tab_name; 没有where 字句,删除所有记录,等于清空记录表,重新插入数据 auto_indrement 还是基于以前递增。 delete from emp; -- 不指定where 字句,删除所有记录。 Query OK, 7 rows affected mysql> select * from emp; Empty set -- 剩下一个空表 -- 重新插入新数据后,看ID insert emp(name,age,gender,salary,bir,tel)values('任贤齐',50,1,50000,'1967-03-23',68372),('林志玲',40,0,25000,'1976-03-23',87234),('孙红雷',39,1,50000,'1977-03-30',98838),('tony',29,1,40000,'1989-0-06',185117),('黄渤',39,1,50000,'1968-12-23',156); Query OK, 5 rows affected Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from emp; -- 就这个样。 +----+--------+-----+--------+--------+------------+--------+ | id | name | age | gender | salary | bir | tel | +----+--------+-----+--------+--------+------------+--------+ | 15 | 任贤齐 | 50 | 1 | 50000 | 1967-03-23 | 68372 | | 16 | 林志玲 | 40 | 0 | 25000 | 1976-03-23 | 87234 | | 17 | 孙红雷 | 39 | 1 | 50000 | 1977-03-30 | 98838 | | 18 | tony | 29 | 1 | 40000 | 1989-00-06 | 185117 | | 19 | 黄渤 | 39 | 1 | 50000 | 1968-12-23 | 156 | +----+--------+-----+--------+--------+------------+--------+ 5 rows in set --- truncate tab_name 就是清空,all clear truncate emp; -- all clear Query OK, 0 rows affected mysql> select * from emp; Empty set mysql> desc emp; --- 查看表结构,id 的完整性约束 auto_increment +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | int(11) | NO | | NULL | | | gender | tinyint(4) | YES | | NULL | | | salary | float(7,2) | YES | | NULL | | | bir | date | YES | | NULL | | | tel | int(11) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ -- 重新插入新数据后,看ID insert emp(name,age,gender,salary,bir,tel)values('任贤齐',50,1,50000,'1967-03-23',68372),('林志玲',40,0,25000,'1976-03-23',87234),('孙红雷',39,1,50000,'1977-03-30',98838),('tony',29,1,40000,'1989-0-06',185117),('黄渤',39,1,50000,'1968-12-23',156); mysql> select * from emp; -- truncate tab_name; 就是清空。 +----+--------+-----+--------+--------+------------+--------+ | id | name | age | gender | salary | bir | tel | +----+--------+-----+--------+--------+------------+--------+ | 1 | 任贤齐 | 50 | 1 | 50000 | 1967-03-23 | 68372 | | 2 | 林志玲 | 40 | 0 | 25000 | 1976-03-23 | 87234 | | 3 | 孙红雷 | 39 | 1 | 50000 | 1977-03-30 | 98838 | | 4 | tony | 29 | 1 | 40000 | 1989-00-06 | 185117 | | 5 | 黄渤 | 39 | 1 | 50000 | 1968-12-23 | 156 | +----+--------+-----+--------+--------+------------+--------+ 5 rows in set
查询表记录(******)
select * from tab_name : 显示所有的记录的所有字段信息
select [distinct] filed,filed2,..... from tab_name
where 子句
group by 分组 key : 分组条件
having 子句 : 过滤
order by
limit
(1)查询表中全部数据
select * from tab_name; -- * 代表所有的字段 从 数据表中查询
举个栗子
- select * from tab_name; -- * 代表所有的字段 从 数据表中查询 mysql> select * from emp; -- * 代表所有的字段 从 数据表中查询 +----+--------+-----+--------+--------+------------+--------+ | id | name | age | gender | salary | bir | tel | +----+--------+-----+--------+--------+------------+--------+ | 1 | 任贤齐 | 50 | 1 | 50000 | 1967-03-23 | 68372 | | 2 | 林志玲 | 40 | 0 | 25000 | 1976-03-23 | 87234 | | 3 | 孙红雷 | 39 | 1 | 50000 | 1977-03-30 | 98838 | | 4 | tony | 29 | 1 | 40000 | 1989-00-06 | 185117 | | 5 | 黄渤 | 39 | 1 | 50000 | 1968-12-23 | 156 | +----+--------+-----+--------+--------+------------+--------+ 5 rows in set
(2)查询表中特定列的数据
select field,field2,field from tab_name; -- 指定特定的字段的列 从 数据表中查询
举个栗子
-- select field,field2,field from tab_name; 指定特定的字段 从 数据表中查询 mysql> select id,name from emp; +----+--------+ | id | name | +----+--------+ | 1 | 任贤齐 | | 2 | 林志玲 | | 3 | 孙红雷 | | 4 | tony | | 5 | 黄渤 | +----+--------+ 5 rows in set
where 字句
-- where字句中可以使用: -- 比较运算符: > < >= <= <> != between 80 and 100 值在10到20之间 in(80,90,100) 值是10或20或30 like 'tony%' /* pattern可以是%或者_, 如果是%则表示任意多字符,此例如唐僧,唐国强 如果是_则表示一个字符唐_,只有唐僧符合。两个_则表示两个字符:__ */ -- 逻辑运算符 在多个条件直接可以使用逻辑运算符 and or not
按特定条件查询表中的数据
select
* from tab_name where field=
'xxx '
;
-- where 字句 加 特定条件
举个栗子
select * from tab_name where field='xxx '; -- where 字句 加 特定条件 --------------------------------------- mysql> select * from emp where name='tony'; -- name ='tony' 的一行 +----+------+-----+--------+--------+------------+--------+ | id | name | age | gender | salary | bir | tel | +----+------+-----+--------+--------+------------+--------+ | 4 | tony | 29 | 1 | 40000 | 1989-00-06 | 185117 | +----+------+-----+--------+--------+------------+--------+ ----- 基于上面的 mysql> select id,name,age from emp where name='tony'; -- 过滤后按指定的列显示 +----+------+-----+ | id | name | age | +----+------+-----+ | 4 | tony | 29 | +----+------+-----+ 1 row in set
特定条件其实就类似于"where 列名称='值'"这样的格式
进一步举个栗子
mysql> select * from emp; +----+--------+-----+--------+--------+------------+--------+ | id | name | age | gender | salary | bir | tel | +----+--------+-----+--------+--------+------------+--------+ | 1 | 任贤齐 | 50 | 1 | 50000 | 1967-03-23 | 68372 | | 2 | 林志玲 | 40 | 0 | 25000 | 1976-03-23 | 87234 | | 3 | 孙红雷 | 39 | 1 | 50000 | 1977-03-30 | 98838 | | 4 | tony | 29 | 1 | 40000 | 1989-00-06 | 185117 | | 5 | 黄渤 | 39 | 1 | 50000 | 1968-12-23 | 156 | | 6 | tom | 19 | 1 | 10000 | 1990-09-10 | 3243 | | 7 | timer | 22 | 0 | 20000 | 1991-02-12 | 135 | +----+--------+-----+--------+--------+------------+--------+ rows in set mysql> select name,age ,salary from emp where salary > 10000 and name like 't%'; -- % 模糊匹配 +-------+-----+--------+ | name | age | salary | +-------+-----+--------+ | tony | 29 | 40000 | | timer | 22 | 20000 | +-------+-----+--------+ rows in set mysql> select name,age ,salary from emp where salary > 10000 and name like 't___'; -- _ 一个下划线是一个占位符 +------+-----+--------+ | name | age | salary | +------+-----+--------+ | tony | 29 | 40000 | +------+-----+--------+ row in set mysql> select name,age ,salary from emp where salary > 10000 and name like 't____'; -- _ 一个下划线是一个占位符 +-------+-----+--------+ | name | age | salary | +-------+-----+--------+ | timer | 22 | 20000 | +-------+-----+--------+ row in set mysql> select name,age ,salary from emp where salary > 10000 and name like 't____'; -- _ 一个下划线是一个占位符 -- select 后面跟的其实是显示给用户所看的列,并不是收索依据,查找依据 where 字句 来过滤 。 特定列特定条件特殊符号 -- -- betwenn and 在什么什么之间 ,取区间值。 -- mysql> select name,age,salary from emp where age between 20 and 50; +--------+-----+--------+ | name | age | salary | +--------+-----+--------+ | 任贤齐 | 50 | 50000 | | 林志玲 | 40 | 25000 | | 孙红雷 | 39 | 50000 | | tony | 29 | 40000 | | 黄渤 | 39 | 50000 | | timer | 22 | 20000 | +--------+-----+--------+ 6 rows in set
group by 和 聚合函数
select [distinct] filed,filed2,..... from tab_name
where 子句
group by 分组 key : 分组条件
having 子句 : 过滤
order by
limit
GROUP BY 语句根据某个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG等函数进行相关查询。
-- gruop by 和 聚合函数 ,按组分查看(此处按gender),记住最小单位为组。
-- count : 统计各个分组的成员个数 (以组为单位,即使给了列的信息显示出也无意义)
-- max : 统计各个分组内最大的值(以组为单位,即使给了列的信息也只能显示组内第一个)
-- min : 统计各个分组内最小的值 (以组为单位,即使给了列的信息也只能显示组内第一个)
-- avg : 统计各个分组内的平均值 (以组为单位,即使给了列的信息也只能显示组内第一个)
-- count max min avg 是全局函数。跟 select count(*) from tab_name; 可以统计整个表的数量。
mysql> select gender,count(*) from emp group by gender; -- gruop by 和 聚合函数 ,按组分查看(此处按gender),记住最小单位为组。 +--------+----------+ -- count : 统计各个分组的成员个数 (以组为单位,即使给了列的信息显示出也无意义) | gender | count(*) | -- max : 统计各个分组内最大的值(以组为单位,即使给了列的信息也只能显示组内第一个) +--------+----------+ -- min : 统计各个分组内最小的值 (以组为单位,即使给了列的信息也只能显示组内第一个) | 0 | 2 | -- avg : 统计各个分组内的平均值 (以组为单位,即使给了列的信息也只能显示组内第一个) | 1 | 5 | +--------+----------+ mysql> select name,count(*) from emp group by gender; -- 此时的name 是无意义的,只能说明按gender分组后每个组的成员个数,最小单位组 +--------+----------+ | name | count(*) | +--------+----------+ | 林志玲 | 2 | | 任贤齐 | 5 | +--------+----------+ rows in set mysql> select max(age) from emp group by gender; -- 按gender 分组后,查看组内最大的值 +----------+ | max(age) | +----------+ | 40 | | 50 | +----------+ rows in set 2 rows in set mysql> select name,max(age) from emp group by gender; -- 按gender 分组后,查看组内最大的值 ,此时name 也是无意义的。 +--------+----------+ | name | max(age) | +--------+----------+ | 林志玲 | 40 | | 任贤齐 | 50 | +--------+----------+ rows in set mysql> select min(age) from emp group by gender; -- 按gender 分组后,查看组内最小的值 +----------+ | min(age) | +----------+ | 22 | | 19 | +----------+ rows in set mysql> select avg(salary) from emp group by gender; -- 按gender 分组后,查看组没平均的值 +-------------+ | avg(salary) | +-------------+ | 22500 | | 40000 | +-------------+ 2 rows in set mysql> select name,age from emp where age in (select max(age) from emp group by gender); -- 分组后再 加 where 字句 ,这样就可以定位每一个组内某一个成员的信息 +--------+-----+ | name | age | +--------+-----+ | 任贤齐 | 50 | | 林志玲 | 40 | +--------+-----+ mysql> select name,age from emp where age in (select min(age) from emp group by gender); -- 分组后再 加 where 字句 ,这样就可以定位每一个组内某一个成员的信息 +-------+-----+ | name | age | +-------+-----+ | tom | 19 | | timer | 22 | +-------+-----+ 2 rows in set --------------------------------------------------------------------- -- group by 分组 key : 分组条件
having
是对 group by 分组后的某一个组的条件进行过滤
order by
按指定的列进行,排序的列即可是表中的列名,也可以是select语句后指定的别名。
-- 语法: select *|field1,field2... from tab_name order by field [Asc|Desc] -- Asc 升序、Desc 降序,其中asc为默认值 ORDER BY 子句应位于SELECT语句的结尾
-- 查询男女员工各有多少人 SELECT gender 性别,count(*) 人数 FROM emp5 GROUP BY gender; -- 查询各个部门的人数 SELECT dep 部门,count(*) 人数 FROM emp5 GROUP BY dep; -- 查询每个部门最大的年龄 SELECT dep 部门,max(age) 最大年纪 FROM emp5 GROUP BY dep; -- 查询每个部门年龄最大的员工姓名 SELECT * FROM emp5 WHERE age in (SELECT max(age) FROM emp5 GROUP BY dep); -- 查询每个部门的平均工资 SELECT dep 部门,avg(salary) 最大年纪 FROM emp GROUP BY dep; -- 查询教学部的员工最高工资: SELECT dep,max(salary) FROM emp11 GROUP BY dep HAVING dep="教学部"; -- 查询平均薪水超过8000的部门 SELECT dep,AVG(salary) FROM emp GROUP BY dep HAVING avg(salary)>8000; -- 查询每个组的员工姓名 SELECT dep,group_concat(name) FROM emp GROUP BY dep; -- 查询公司一共有多少员工(可以将所有记录看成一个组) SELECT COUNT(*) 员工总人数 FROM emp; -- KEY: 查询条件中的每个后的词就是分组的字段
limit记录条数限制
SELECT * from ExamResult limit 1; SELECT * from ExamResult limit 2,5; -- 跳过前两条显示接下来的五条纪录 5 是 count SELECT * from ExamResult limit 2,2;
-- 查询男女员工各有多少人
SELECT
gender 性别,
count
(*) 人数
FROM
emp5
GROUP
BY
gender;
-- 查询各个部门的人数
SELECT
dep 部门,
count
(*) 人数
FROM
emp5
GROUP
BY
dep;
-- 查询每个部门最大的年龄
SELECT
dep 部门,
max
(age) 最大年纪
FROM
emp5
GROUP
BY
dep;
-- 查询每个部门年龄最大的员工姓名
SELECT
*
FROM
emp5
WHERE
age
in
(
SELECT
max
(age)
FROM
emp5
GROUP
BY
dep);
-- 查询每个部门的平均工资
SELECT
dep 部门,
avg
(salary) 最大年纪
FROM
emp
GROUP
BY
dep;
-- 查询教学部的员工最高工资:
SELECT
dep,
max
(salary)
FROM
emp11
GROUP
BY
dep
HAVING
dep=
"教学部"
;
-- 查询平均薪水超过8000的部门
SELECT
dep,
AVG
(salary)
FROM
emp
GROUP
BY
dep
HAVING
avg
(salary)>8000;
-- 查询每个组的员工姓名
SELECT
dep,group_concat(
name
)
FROM
emp
GROUP
BY
dep;
-- 查询公司一共有多少员工(可以将所有记录看成一个组)
SELECT
COUNT
(*) 员工总人数
FROM
emp;
-- KEY: 查询条件中的每个后的词就是分组的字段