20世纪70年代，IBM开发出SQL，用于DB21981年，IBM推出SQL/DS数据库业内标准微软和Sybase的T-SQL，Oracle的PL/SQL SQL作为关系型数据库所使用的标准语言，最初是基于IBM的实现在1986年被批准的。1987年，国际标准化组织(ISO) 把ANSI(美国国家标准化组织) SQL作为国际标准SQL：ANSI SQL，SQL-1986, SQL-1989, SQL-1992, SQL-1999, SQL-2003，SQL-2008, SQL-2011
在数据库系统中，SQL语句不区分大小写，建议用大写 SQL语句可单行或多行书写，以“;”结尾 关键词不能跨多行或简写 用空格和缩进来提高语句的可读性 子句通常位于独立行，便于编辑，提高可读性 注释： SQL标准： -- 注释内容 单行注释，注意有空格 /*注释内容*/ 多行注释 MySQL注释： # 注释内容
数据库的组件(对象)： 数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等 命名规则： 必须以字母开头，可包括数字和三个特殊字符（# _ $） 不要使用MySQL的保留字
DDL: Data Defination Language 数据定义语言 CREATE，DROP，ALTER DML: Data Manipulation Language 数据操纵语言 INSERT，DELETE，UPDATE DQL：Data Query Language 数据查询语言 SELECT DCL：Data Control Language 数据控制语言 GRANT，REVOKE，COMMIT，ROLLBACK 软件开发：CRUD
关健字Keyword组成子句clause，多条clause组成语句
示例：
SELECT * #SELECT子句 FROM products #FROM子句 WHERE price>666 #WHERE子句 说明：一组SQL语句，由三个子句构成，SELECT,FROM和WHERE是关键字 数据库操作
官方帮助：https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html mysql> HELP KEYWORD
CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME' CHARACTER SET 'character set name' COLLATE 'collate name';
mysql> create database db1; Query OK, 1 row affected (0.01 sec)
mysql> show databases; +--------------------+ | Database | +--------------------+ | db1 | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) ###这里可以看到db1数据库已经创建成功
2.1-1-2当你创建一个数据库的同时，会在你数据库目录下来会创建一个相应的目录
#这里结果显示可以看到db1这个目录##这里结果显示可以看到db1这个目录#
[root@centos7 ~]# yum install tree -y [root@centos7 ~]# tree /data/mysql/data_3306/ -d /data/mysql/data_3306/ ├── db1 ├── #innodb_temp ├── mysql ├── performance_schema └── sys #这里结果显示可以看到db1这个目录# #这里结果显示可以看到db1这个目录#
2.1-1-3查看db1数据库的字符集和排序规则
##这里可以看到db1这个数据库的字符集是utf8mb4
mysql> show create database db1; +----------+-------------------------------------------------------------------------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------------------------------------------------------------------------+ | db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ | +----------+-------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ##这里可以看到db1这个数据库的字符集是utf8mb4 ##这里可以看到db1这个数据库的排序规则是utf8mb4_0900_ai_ci
查看mysql5.7默认的字符集 mysql> show variables like 'character%'; +--------------------------+----------------------------------------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-5.7.29-linux-glibc2.12-x86_64/share/charsets/ | +--------------------------+----------------------------------------------------------------+ 8 rows in set (0.00 sec)
mysql> create database db2；
mysql> show create database db1; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | db1 | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+----------------------------------------------------------------+ 1 row in set (0.00 sec) ##这里可以看到db1的字符集是latin1
mysql> alter database db2 character set utf8mb4 collate utf8mb4_bin;
2.1-2-4查看db2的默认字符集
这里可以看到db2默认的latin1已经修改为utf8mb4
mysql> show create database db2; +----------+-------------------------------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------------------------------+ | db1 | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ | +----------+-------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ##这里可以看到db1默认的latin1已经修改为utf8mb4 生产环境不要随意修改
mysql> create database if not exists db3 character set 'utf8mb4';
2.2-1-2查看db3的数据库的字符集和排序规则**
mysql> show create database db3; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | db3 | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql> create database db4 character set utf8 collate utf8_bin;
mysql> drop database db1; Query OK, 0 rows affected (0.01 sec) 查看 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec)
[root@centos7 ~]# tree /data/mysql/data_3306/ -d /data/mysql/data_3306/ ├── #innodb_temp ├── mysql ├── performance_schema └── sys
数据类型： 数据长什么样 数据需要多少空间来存放 数据类型 系统内置数据类型 用户定义数据类型 MySQL支持多种内置数据类型 数值类型 日期/时间类型 字符串(字符)类型 数据类型参考链接 https://dev.mysql.com/doc/refman/8.0/en/data-types.htm
选择正确的数据类型对于获得高性能至关重要，三大原则： 1. 更小的通常更好，尽量使用可正确存储数据的最小数据类型 2. 简单就好，简单数据类型的操作通常需要更少的CPU周期 3. 尽量避免NULL，包含为NULL的列，对MySQL更难优化
tinyint(m) 1个字节 范围(-128~127) smallint(m) 2个字节 范围(-32768~32767) mediumint(m) 3个字节 范围(-8388608~8388607) int(m) 4个字节 范围(-2147483648~2147483647) bigint(m) 8个字节 范围(+-9.22*10的18次方) 上述数据类型，如果加修饰符unsigned后，则最大值翻倍 如：tinyint unsigned的取值范围为(0~255) int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，Int(1)和Int(20)是相同的BOOL，BOOLEAN：布尔型，是TINYINT(1)的同义词。zero值被视为假，非zero值视为真
float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位 double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位 设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位
在数据库中存放的是精确值,存为十进制 decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位 MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。 例如: decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：其中，小数点前的9个数字用4个字节，小数点后的9个数字用4个字节，小数点本身占1个字节浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal，例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal
char(n) 固定长度，最多255个字符,注意不是字节 varchar(n) 可变长度，最多65535个字符 tinytext 可变长度，最多255个字符 text 可变长度，最多65535个字符 mediumtext 可变长度，最多2的24次方-1个字符 longtext 可变长度，最多2的32次方-1个字符 BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节 VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节 内建类型：ENUM枚举, SET集合
3.1-4char和varchar：
https://dev.mysql.com/doc/refman/8.0/en/char.html
| Value | CHAR(4) | Storage Required | VARCHAR(4) | Storage Required |
|---|---|---|---|---|
| '' | '' | 4 bytes | '' | 1 byte |
| 'ab' | 'ab' | 4 bytes | 'ab' | 3 byte |
| 'abcd' | 'abcd' | 4 bytes | 'abcd' | 5 byte |
| 'abcd' | 'abcd' | 4 bytes | 'abcd' |
1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，所以char类型存储的字符串末尾不能有空格，varchar不限于此 2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n< n>255)，所以varchar(4),存入3个字符将占用4个字节 3.char类型的字符串检索速度要比varchar类型的快varchar和text： 1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n< n>255)，text是实际字符数+2个字节。 2.text类型不能有默认值 3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text数据类型
BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob以二进制方式存储，不分大小写BLOB存储的数据只能整体读出TEXT可以指定字符集，BLOB不用指定字符集
date 日期 '2008-12-2' time 时间 '12:25:36' datetime 日期时间 '2008-12-2 22:06:44' timestamp 自动存储记录修改时间 YEAR(2), YEAR(4)：年份 timestamp字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字段可以存放这条记 录最后被修改的时间
适用所有类型的修饰符： NULL 数据列可包含NULL值，默认值 NOT NULL 数据列不允许包含NULL值，*为必填选项 DEFAULT 默认值 PRIMARY KEY 主键，所有记录中此字段的值不能重复，且不能为NULL UNIQUE KEY 唯一键，所有记录中此字段的值不能重复，但可以为NULL CHARACTER SET name 指定一个字符集
适用数值型的修饰符：
AUTO_INCREMENT 自动递增，适用于整数类型 UNSIGNED 无符号 范例：关于AUTO_INCREMENT
mysql> show variables like 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set (0.01 sec)
范例：
mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test Database changed mysql> create table t1(id int unsigned auto_increment primary key) auto_increment = 4294967294; Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values(null); Query OK, 1 row affected (0.01 sec) mysql> select * from t1; +------------+ | id | +------------+ | 4294967294 | +------------+ 1 row in set (0.00 sec) mysql> insert into t1 values(null); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +------------+ | id | +------------+ | 4294967294 | | 4294967295 | +------------+ 2 rows in set (0.00 sec) mysql> insert into t1 values(null); ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY' MariaDB [testdb]> insert t1 value(null); ERROR 167 (22003): Out of range value for column 'id' at row 1
表：二维关系 设计表：遵循规范 定义：字段，索引 字段：字段名，字段数据类型，修饰符 约束，索引：应该创建在经常用作查询条件的字段上
4.1-1创建表
crate table 表名
mysql> help create tables;
Nothing found
Please try to run 'help contents' for a list of all accessible topics
mysql> help create tables
Nothing found
Please try to run 'help contents' for a list of all accessible topics
mysql> help create table
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
[IGNORE | REPLACE]
[AS] query_expression
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
create_definition: {
col_name column_definition
| {INDEX | KEY} [index_name] [index_type] (key_part,...)
[index_option] ...
| {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
[index_name] [index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name,...)
reference_definition
| check_constraint_definition
}
column_definition: {
data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
[VISIBLE | INVISIBLE]
[AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[COLLATE collation_name]
[COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
[ENGINE_ATTRIBUTE [=] 'string']
[SECONDARY_ENGINE_ATTRIBUTE [=] 'string']
[STORAGE {DISK | MEMORY}]
[reference_definition]
[check_constraint_definition]
| data_type
[COLLATE collation_name]
[GENERATED ALWAYS] AS (expr)
[VIRTUAL | STORED] [NOT NULL | NULL]
[VISIBLE | INVISIBLE]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[reference_definition]
[check_constraint_definition]
}
data_type:
(see https://dev.mysql.com/doc/refman/8.0/en/data-types.html)
key_part: {col_name [(length)] | (expr)} [ASC | DESC]
index_type:
USING {BTREE | HASH}
index_option: {
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
| {VISIBLE | INVISIBLE}
|ENGINE_ATTRIBUTE [=] 'string'
|SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}
check_constraint_definition:
[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
reference_definition:
REFERENCES tbl_name (key_part,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
table_options:
table_option [[,] table_option] ...
table_option: {
AUTOEXTEND_SIZE [=] value
| AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
| CONNECTION [=] 'connect_string'
| {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| ENCRYPTION [=] {'Y' | 'N'}
| ENGINE [=] engine_name
| ENGINE_ATTRIBUTE [=] 'string'
| INSERT_METHOD [=] { NO | FIRST | LAST }
| KEY_BLOCK_SIZE [=] value
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] 'string'
| ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
| SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
| STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
| STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
| STATS_SAMPLE_PAGES [=] value
| TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]
| UNION [=] (tbl_name[,tbl_name]...)
}
partition_options:
PARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
| RANGE{(expr) | COLUMNS(column_list)}
| LIST{(expr) | COLUMNS(column_list)} }
[PARTITIONS num]
[SUBPARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) }
[SUBPARTITIONS num]
]
[(partition_definition [, partition_definition] ...)]
partition_definition:
PARTITION partition_name
[VALUES
{LESS THAN {(expr | value_list) | MAXVALUE}
|
IN (value_list)}]
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[(subpartition_definition [, subpartition_definition] ...)]
subpartition_definition:
SUBPARTITION logical_name
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
query_expression:
SELECT ... (Some valid select or union statement)
CREATE TABLE creates a table with the given name. You must have the
CREATE privilege for the table.
By default, tables are created in the default database, using the
InnoDB storage engine. An error occurs if the table exists, if there is
no default database, or if the database does not exist.
MySQL has no limit on the number of tables. The underlying file system
may have a limit on the number of files that represent tables.
Individual storage engines may impose engine-specific constraints.
InnoDB permits up to 4 billion tables.
For information about the physical representation of a table, see
https://dev.mysql.com/doc/refman/8.0/en/create-table-files.html.
URL: https://dev.mysql.com/doc/refman/8.0/en/create-table.html先来创建dba1数据库
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
进入到db1数据库里
mysql> use db1
查看db1数据库里没有表
mysql> show tables;
Empty set (0.00 sec)
这里看到当前db1数据库为空表
创建student表，并插入数据
CREATE TABLE student ( id int UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, age tinyint UNSIGNED, gender ENUM('M','F') default 'M' )ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
解释：
id
id(列)字段名称，为一标识符，一般为数字，递增
name
name(列)字段名称，一般为用户名，比如名字
age
age(列)字段，年龄
gender
gener(列)性别
id int
ID列字段的长度，4个字节 范围(-2147483648~2147483647)
name VARCHAR(20)
name（20）最长为20，列字符长度，可变长度，最多65535个字符
age tinyint
age列字段长度，占用1个字节 范围(-128~127)
gender ENUM('M','F')
gender列字段，M为男,F为女
UNSIGNED
都是整数，不能是负数
AUTO_INCREMENT
自动增长
PRIMARY KEY
设置为主键
NOT NULL
不允许为空
ENUM('M','F') default 'M' )
枚举类型，用户选择只能二选一，默认M男
ENGINE=InnoDB
指定默认存储引擎为InnoDB
AUTO_INCREMENT=10
自动增长，默认从10开始增长
EFAULT CHARSET=utf8;
指定默认字符集为utf8
查看当前表有没有创建成功
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| student |
+---------------+
1 row in set (0.00 sec)
查看db1表里面的结构
mysql> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
查看student表创建的过程
mysql> show create table student;
+---------+----------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------- ---------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------- ---------------------+
| student | CREATE TABLE `student` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` tinyint unsigned DEFAULT NULL,
`gender` enum('M','F') DEFAULT 'M',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb3 |
+---------+----------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------- ---------------------+
1 row in set (0.01 sec)
查看student表的更为详细的信息
mysql> show table status like 'student'\G
*************************** 1. row ***************************
Name: student
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 10
Create_time: 2022-11-14 19:51:23
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
查看db1数据库里面的所有的表，因为db1书库里只有一个表所有只列举出了一个表
mysql> show table status from db1\G
*************************** 1. row ***************************
Name: student
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 10
Create_time: 2022-11-14 19:51:23
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)修改表名字，把student的表名修改成s1
mysql> alter table student rename s1;
Query OK, 0 rows affected (0.01 sec)
再把s1的表名修改成student
mysql> alter table s1 rename student;
Query OK, 0 rows affected (0.01 sec)
查看student表结构
mysql> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
在student表结构里面插入一条数据，在name后面插入pgon字段设置为varchar(11)
mysql> alter table student add phone varchar(11) after name;
查看student表结构有没有发生改变，通过查看已经可以看到在name后面添加了phone的列
mysql> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| phone | varchar(11) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
修改student表里面pgone列的字段类型为int
mysql> ALTER TABLE sudent MODIFY phone int;
查看student表结构，可以看到phone原来的varcgar(11)，已经修改成int了
mysql> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| phone | int | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
修改pgone列命为mobile，并设置字符类型为varchar(12);
mysql> ALTER TABLE sudent CHANGE COLUMN phone mobile varchar(12);
查看sudent表结构
mysql> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| mobile | varchar(12) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
删除mobile这一列
mysql> ALTER TABLE student drop COLUMN mobile;
查看student表
mysql> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
修改字符集
ALTER TABLE student character set utf8;
ALTER TABLE student change name name varchar(20) character set utf8;
在student表里面添加一行test列字字符类型为ENUM('m','f')
mysql> ALTER TABLE students ADD test ENUM('m','f');
查看student表结构
mysql> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
| test | enum('m','f') | YES | | NULL | |
+--------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
修改字段和类型
ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;
删除字段
ALTER TABLE students DROP age;
查看表结构
DESC students;
#新建表无主键，添加和删除主键
CREATE TABLE t1 SELECT * FROM students;
ALTER TABLE t1 add primary key (stuid);
ALTER TABLE t1 drop primary key ;MDL: INSERT 插入 DELETE 删除 UPDATE 升级，修改
查看stduent表结构
mysql> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| phone | varchar(11) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
查看student表里面的数据，可以看到student表里面的数据为空
mysql> select * from student;
Empty set (0.00 sec)
在student里面插入数据，完整写法：id为1，name为mazong,pgone为1008，age为20,gender为M
mysql> INSERT student(id,name,phone,age,gender) value(1,'mazong',10086,20,'M');
查看student表里面的数据，看看有没有插入成功
mysql> select id,name,phone,age,gender from student;
+----+--------+-------+------+--------+
| id | name | phone | age | gender |
+----+--------+-------+------+--------+
| 1 | mazong | 10086 | 20 | M |
+----+--------+-------+------+--------+
1 row in set (0.00 sec)
再来插入一条信息，简略写法，id2,name为ruxia,phone为10000，age为18，gender为F
mysql> INSERT student value(2,'ruxia',10000,18,'F');
Query OK, 1 row affected (0.00 sec)
查看student表是否写入成功
mysql> select id,name,phone,age,gender from student;
+----+--------+-------+------+--------+
| id | name | phone | age | gender |
+----+--------+-------+------+--------+
| 1 | mazong | 10086 | 20 | M |
| 2 | ruxia | 10000 | 18 | F |
+----+--------+-------+------+--------+
2 rows in set (0.00 sec)
例子3，ID列默认是自动增长的可以不用填写，我们只填写name列且name为中文，和age列，其他为空
mysql> INSERT student (name,age) value('李涛',18);
Query OK, 1 row affected (0.01 sec)
查看student表里面的内容是否插入成功
mysql> select * from student;
+----+--------+-------+------+--------+
| id | name | phone | age | gender |
+----+--------+-------+------+--------+
| 1 | mazong | 10086 | 20 | M |
| 2 | ruxia | 10000 | 18 | F |
| 10 | 李涛 | NULL | 18 | M |
+----+--------+-------+------+--------+
3 rows in set (0.00 sec)
##这里可以看到phone为空，默认gender为M
##这里可以看到phone为空，默认gender为M
再来插入一条数据，先写age再写name
mysql> INSERT student (age,name) value(19,'紫强');
查看student表里面的内容是否插入成功
mysql> select * from student;
+----+--------+-------+------+--------+
| id | name | phone | age | gender |
+----+--------+-------+------+--------+
| 1 | mazong | 10086 | 20 | M |
| 2 | ruxia | 10000 | 18 | F |
| 10 | 李涛 | NULL | 18 | M |
| 11 | 紫强 | NULL | 19 | M |
+----+--------+-------+------+--------+
4 rows in set (0.00 sec)格式： update 表名字 set 要修改的内容 where 条件 先来看student表的内容 mysql> select * from student; +----+--------+-------+------+--------+ | id | name | phone | age | gender | +----+--------+-------+------+--------+ | 1 | mazong | 10086 | 20 | M | | 2 | ruxia | 10000 | 18 | F | | 10 | 李涛 | NULL | 18 | M | | 11 | 紫强 | NULL | 19 | M | +----+--------+-------+------+--------+ 4 rows in set (0.00 sec) 修改ID为11的用户的age为17 mysql> mysql> update student set age=17 where id=11; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 查看student表里面的内容是否修改成功 mysql> select * from student; +----+--------+-------+------+--------+ | id | name | phone | age | gender | +----+--------+-------+------+--------+ | 1 | mazong | 10086 | 20 | M | | 2 | ruxia | 10000 | 18 | F | | 10 | 李涛 | NULL | 18 | M | | 11 | 紫强 | NULL | 17 | M | +----+--------+-------+------+--------+ 4 rows in set (0.00 sec) 修改ID为11用户的age=18并加入phone为10010 mysql> update student set age=18,phone=10010 where id=11; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 查看student表里面的内容是否修改成功 mysql> select * from student; +----+--------+-------+------+--------+ | id | name | phone | age | gender | +----+--------+-------+------+--------+ | 1 | mazong | 10086 | 20 | M | | 2 | ruxia | 10000 | 18 | F | | 10 | 李涛 | NULL | 18 | M | | 11 | 紫强 | 10010 | 18 | M | +----+--------+-------+------+--------+ 4 rows in set (0.00 sec)
delete删除表(只会删除表里面的数据，不会删除表)
delete from 表名；
比如我添加错了一条信息，我想把他删掉
mysql> insert student(name,age) value('老五',30);
Query OK, 1 row affected (0.00 sec)
查看表里面的内容
mysql> select * from student;
+----+--------+-------+------+--------+
| id | name | phone | age | gender |
+----+--------+-------+------+--------+
| 1 | mazong | 10086 | 20 | M |
| 2 | ruxia | 10000 | 18 | F |
| 10 | 李涛 | NULL | 18 | M |
| 11 | 紫强 | 10010 | 18 | M |
| 12 | 老五 | NULL | 30 | M |
+----+--------+-------+------+--------+
5 rows in set (0.00 sec)
我想删除ID=12的用户
mysql> delete from student where id=12;
Query OK, 1 row affected (0.00 sec)
再来查看ID=12的用户有没有删除掉
mysql> select * from student;
+----+--------+-------+------+--------+
| id | name | phone | age | gender |
+----+--------+-------+------+--------+
| 1 | mazong | 10086 | 20 | M |
| 2 | ruxia | 10000 | 18 | F |
| 10 | 李涛 | NULL | 18 | M |
| 11 | 紫强 | 10010 | 18 | M |
+----+--------+-------+------+--------+
4 rows in set (0.00 sec)语法:
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[SQL_CACHE | SQL_NO_CACHE]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[FOR UPDATE | LOCK IN SHARE MODE]说明：
字段显示可以使用别名：
col1 AS alias1, col2 AS alias2, ...
WHERE子句：指明过滤条件以实现“选择”的功能：
过滤条件：布尔型表达式
算术操作符：+, -, *, /, %
比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <=
BETWEEN min_num AND max_num
IN (element1, element2, ...)
IS NULL
IS NOT NULL
DISTINCT 去除重复行，范例：SELECT DISTINCT gender FROM students;
LIKE:
% 任意长度的任意字符
_ 任意单个字符
RLIKE：正则表达式，索引失效，不建议使用
REGEXP：匹配字符串可用正则表达式书写模式，同上
逻辑操作符：NOT，AND，OR，XOR
GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算
常见聚合函数：avg(), max(), min(), count(), sum()
HAVING: 对分组聚合运算后的结果指定过滤条件
一旦分组group by ，select语句后只跟分组的字段，聚合函数
ORDER BY: 根据指定的字段对查询结果进行排序
升序：ASC
降序：DESC
LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制
OPTIMIZE TABLE tb_name
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[SQL_CACHE | SQL_NO_CACHE]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[FOR UPDATE | LOCK IN SHARE MODE]
对查询结果中的数据请求施加“锁”
FOR UPDATE: 写锁，独占或排它锁，只有一个读和写操作
LOCK IN SHARE MODE: 读锁，共享锁，同时多个读操作打印hello字符串，需要加单引号，不然mysql会认为这是一个字段的名称
mysql> select 'hello';
+-------+
| hello |
+-------+
| hello |
+-------+
1 row in set (0.00 sec)
数字运算2+3
mysql> select 2+3;
+-----+
| 2+3 |
+-----+
| 5 |
+-----+
1 row in set (0.00 sec)
查看student表里面的所有字段，* 表示所有列
mysql> select * from student;
+----+--------+-------+------+--------+
| id | name | phone | age | gender |
+----+--------+-------+------+--------+
| 1 | mazong | 10086 | 20 | M |
| 2 | ruxia | 10000 | 18 | F |
| 10 | 李涛 | NULL | 18 | M |
| 11 | 紫强 | 10010 | 18 | M |
+----+--------+-------+------+--------+
4 rows in set (0.00 sec)
解释：
* 表示所有列
查看student的字段内容
mysql> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| phone | varchar(11) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
解释：
这里可以看到student的列有id，name，phone，age，gender
查看student表的name列,age列,gender列的信息
mysql> select name,age,gender from student;
+--------+------+--------+
| name | age | gender |
+--------+------+--------+
| mazong | 20 | M |
| ruxia | 18 | F |
| 李涛 | 18 | M |
| 紫强 | 18 | M |
+--------+------+--------+
4 rows in set (0.00 sec)
查看student表里面ID=2的信息
mysql> select * from student where id=2;
+----+-------+-------+------+--------+
| id | name | phone | age | gender |
+----+-------+-------+------+--------+
| 2 | ruxia | 10000 | 18 | F |
+----+-------+-------+------+--------+
1 row in set (0.00 sec)
查看student表里面ID大于2的行
mysql> select * from student where ID > 2;
+----+--------+-------+------+--------+
| id | name | phone | age | gender |
+----+--------+-------+------+--------+
| 10 | 李涛 | NULL | 18 | M |
| 11 | 紫强 | 10010 | 18 | M |
+----+--------+-------+------+--------+
2 rows in set (0.00 sec)
推出数据库
mysql> exit
Bye
导入数据库，方便后面的查询语句
mysql -uroot -p123456 > /opt/hellodb_innodb.sql
进入数据库
mysql -uroot -p123456s
查看有那些数据库，这里可以看到
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
进入到hellodb数据库里
mysql> use hellodb
Database chang
查看hellodb里面有哪些表
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
查看students表有哪些数据
mysql> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
查看students有哪些列
mysql> desc students;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| StuID | int unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint unsigned | YES | | NULL | |
| TeacherID | int unsigned | YES | | NULL | |
+-----------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
我想查看age小于20的
mysql> select * from students where age <20;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
+-------+--------------+-----+--------+---------+-----------+
8 rows in set (0.00 sec)
解释：
where age <20;
查找调教age<20的内容
我想查看age小于20的,并且是女孩
mysql> select * from students where age <20 and gender ='F';
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
+-------+--------------+-----+--------+---------+-----------+
7 rows in set (0.00 sec)
解释：
and gender ='F';
搜索age <20并且gender=F的
查看students表里面年龄在18，20，22岁的
mysql> select * from students where age in (18,20,22);
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID为空的 | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
+-------+---------------+-----+--------+---------+-----------+
6 rows in set (0.00 sec)
解释：
in (18,20,22)
包含18，20，22岁的内容
查看students表里面ClassID为空的
mysql> select * from students where ClassID is null;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+-------------+-----+--------+---------+-----------+
2 rows in set (0.00 sec)
解释：
is null
判断是否为空，取为null的行
查看students表里面ClassID不为空的
mysql> select * from students where ClassID is not null;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
+-------+---------------+-----+--------+---------+-----------+
23 rows in set (0.00 sec)
解释：
is not
取反
模糊搜索，查看name列里面以d(大小写不区分)开头的用户
mysql> select * from students where name like 'd%';
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
+-------+-----------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
查看students表里面age的内容并且去除重复的内容
mysql> select distinct
age from students;
+-----+
| age |
+-----+
| 22 |
| 53 |
| 32 |
| 26 |
| 46 |
| 19 |
| 17 |
| 20 |
| 23 |
| 33 |
| 21 |
| 25 |
| 18 |
| 27 |
| 100 |
+-----+
15 rows in set (0.00 sec)
解释：
distinct
去重，移除掉重复的内容展示出来
查看所有StuID的id数
mysql> select count(stuid) from students;
+--------------+
| count(stuid) |
+--------------+
| 25 |
+--------------+
1 row in set (0.01 sec)
解释：
count()
函数，统计数量（）里面填写列的内容就可以统计出数量
只统计非空值
查看所有ClassID的id数
mysql> select count(ClassID) from students;
+----------------+
| count(ClassID) |
+----------------+
| 23 |
+----------------+
1 row in set (0.00 sec)
count()别名
mysql> select count(*) 记录数量 from students;
+--------------+
| 记录数量 |
+--------------+
| 25 |
+--------------+
1 row in set (0.00 sec)
查找别名，查找的内容以中文显示
mysql> select name 姓名,age 年龄,gender 性别 from students;
+---------------+--------+--------+
| 姓名 | 年龄 | 性别 |
+---------------+--------+--------+
| Shi Zhongyu | 22 | M |
| Shi Potian | 22 | M |
| Xie Yanke | 53 | M |
| Ding Dian | 32 | M |
| Yu Yutong | 26 | M |
| Shi Qing | 46 | M |
| Xi Ren | 19 | F |
| Lin Daiyu | 17 | F |
| Ren Yingying | 20 | F |
| Yue Lingshan | 19 | F |
| Yuan Chengzhi | 23 | M |
| Wen Qingqing | 19 | F |
| Tian Boguang | 33 | M |
| Lu Wushuang | 17 | F |
| Duan Yu | 19 | M |
| Xu Zhu | 21 | M |
| Lin Chong | 25 | M |
| Hua Rong | 23 | M |
| Xue Baochai | 18 | F |
| Diao Chan | 19 | F |
| Huang Yueying | 22 | F |
| Xiao Qiao | 20 | F |
| Ma Chao | 23 | M |
| Xu Xian | 27 | M |
| Sun Dasheng | 100 | M |
+---------------+--------+--------+
25 rows in set (0.00 sec)
查看每个班级的学生数量
mysql> select classid 班级, count(*) 学生数量 from students group by classid;
+--------+--------------+
| 班级 | 学生数量 |
+--------+--------------+
| 2 | 3 |
| 1 | 4 |
| 4 | 4 |
| 3 | 4 |
| 5 | 1 |
| 7 | 3 |
| 6 | 4 |
| NULL | 2 |
+--------+--------------+
8 rows in set (0.00 sec)
解释：
group
分组统计
聚合函数：
count
统计数量
sum
统计总和
avg
统计平均值
max
统计最大值
min
统计最小值
搜索班级和性别，并按班级和性别分组统计数量，
mysql> select ClassID,Gender,count(*) from students group by ClassID,Gender;
+---------+--------+----------+
| ClassID | Gender | count(*) |
+---------+--------+----------+
| 2 | M | 3 |
| 1 | M | 2 |
| 4 | M | 4 |
| 3 | M | 1 |
| 5 | M | 1 |
| 3 | F | 3 |
| 7 | F | 2 |
| 6 | F | 3 |
| 6 | M | 1 |
| 1 | F | 2 |
| 7 | M | 1 |
| NULL | M | 2 |
+---------+--------+----------+
12 rows in set (0.00 sec)
解释：
group by
分组
搜索班级和性别，并按班级和性别分组统计数量，搜索出来并按班级排序，正序排序
mysql> select ClassID,Gender,count(*) from students group by ClassID,Gender order by classid;
+---------+--------+----------+
| ClassID | Gender | count(*) |
+---------+--------+----------+
| NULL | M | 2 |
| 1 | F | 2 |
| 1 | M | 2 |
| 2 | M | 3 |
| 3 | F | 3 |
| 3 | M | 1 |
| 4 | M | 4 |
| 5 | M | 1 |
| 6 | F | 3 |
| 6 | M | 1 |
| 7 | F | 2 |
| 7 | M | 1 |
+---------+--------+----------+
12 rows in set (0.00 sec)
解释：
order by
排序，默认是正序排序
搜索班级和性别，并按班级和性别分组统计数量，搜索出来并按班级排序，倒叙排序
mysql> select ClassID,Gender,count(*) from students group by ClassID,Gender order by classid desc;
+---------+--------+----------+
| ClassID | Gender | count(*) |
+---------+--------+----------+
| 7 | F | 2 |
| 7 | M | 1 |
| 6 | F | 3 |
| 6 | M | 1 |
| 5 | M | 1 |
| 4 | M | 4 |
| 3 | F | 3 |
| 3 | M | 1 |
| 2 | M | 3 |
| 1 | F | 2 |
| 1 | M | 2 |
| NULL | M | 2 |
+---------+--------+----------+
12 rows in set (0.00 sec)
解释：
desc
倒叙排序
统计每个班级学生的数量，并且是大于等于3个以上的数量才显示
mysql> select ClassID,count(*) from students group by classid having count(*)>=3;
+---------+----------+
| ClassID | count(*) |
+---------+----------+
| 2 | 3 |
| 1 | 4 |
| 4 | 4 |
| 3 | 4 |
| 7 | 3 |
| 6 | 4 |
+---------+----------+
6 rows in set (0.00 sec)
解释：
having count(*)>=3;
在group分组里面过滤条件不能使用where，只能使用having
where用也只能在gruop前面使用
having count(*)>=3条件是大于等于3的
在group分组前面使用
mysql> select ClassID,count(*) from students where classid >=2 group by classid having count(*)>=3;
+---------+----------+
| ClassID | count(*) |
+---------+----------+
| 2 | 3 |
| 4 | 4 |
| 3 | 4 |
| 7 | 3 |
| 6 | 4 |
+---------+----------+
5 rows in set (0.00 sec)
挑出前5个stuid和age的学员正序排序
mysql> select stuid,age from students order by age limit 5;
+-------+-----+
| stuid | age |
+-------+-----+
| 14 | 17 |
| 8 | 17 |
| 19 | 18 |
| 12 | 19 |
| 7 | 19 |
+-------+-----+
5 rows in set (0.00 sec)
出前5个，后续的三个，只显示后面三个
mysql> select stuid,age from students order by age limit 5,3;
+-------+-----+
| stuid | age |
+-------+-----+
| 12 | 19 |
| 15 | 19 |
| 7 | 19 |
+-------+-----+
3 rows in set (0.00 sec)