MySQL数据库2
- 数据库创建
- 表操作,增、删、改、查、分组、排序等
- SQL语句实例
- 多表操作
- 视图及自定义函数
1 多实例操作
- 实现多实例需要手动创建的文件
- 用户以及组
- 数据库的文件目录
- 配置文件
- 数据库的启动脚本
- 数据库存放系统内容的数据库
- 相同版本数据库实现多实例, 这里以yum安装方式为例
同一个版本的数据库程序,来实现多实例,其二进制程序可以不改动,账号可以相同,相关配置需独立建设,比如数据库的文件夹,配置文件,服务的启动脚本定义各自的端口号- 计划创建3个同版本实例,使用的端口号为3306,3307,3308;安装软件,由于是基于同一个版本,所以安装一次软件即可,yum安装会自动生成账号
[root@hai7-8 ~]$yum install mariadb-server
- 规划目录存放路径,创建目录,结构如下所示
将建成的目录及文件,所属组及所有者都改为mysql[root@hai7-8 /data]$mkdir /data/mysql/{3306,3307,3308}/{etc,data,socket,log,bin,pid} -p [root@hai7-8 /data]$tree . └── mysql ├── 3306 │ ├── bin │ ├── data │ ├── etc │ ├── log │ ├── pid │ └── socket ...... <==3307/3308结构相同,这里省略
[root@hai7-8 /data]$chown -R mysql.mysql mysql
- 生成数据库必要文件,安装包内自带的脚本mysql_install_db,支持help,可以查看修改帮助,这里需要修改的路径为数据库路径,用户名
[root@hai7-8 /data]$/usr/bin/mysql_install_db --help '执行三次,分别生成3306,3307,3308的数据库文件' [root@hai7-8 /data]$/usr/bin/mysql_install_db --datadir=/data/mysql/3306/data --user=mysql [root@hai7-8 /data]$/usr/bin/mysql_install_db --datadir=/data/mysql/3307/data --user=mysql [root@hai7-8 /data]$/usr/bin/mysql_install_db --datadir=/data/mysql/3308/data --user=mysql
- 准备配置文件,拷贝系统自带的my.cnf作为模板来修改
'1. 拷贝模板文件' [root@hai7-8 /data]$cp /etc/my.cnf /data/mysql/3306/etc/ '2. 修改模板为我们规划的路径' [root@hai7-8 /data]$vim mysql/3306/etc/my.cnf [mysqld] port=3306 <==默认为3306端口,使用此端口可以不用指定,非默认端口需要指定 datadir=/data/mysql/3306/data <==存放数据路径 socket=/data/mysql/3306/socket/mysql.sock <==socket文件路径 symbolic-links=0 <==默认,不需要修改 [mysqld_safe] log-error=/data/mysql/3306/log/mariadb.log <==日志文件路径 pid-file=/data/mysql/3306/pid/mariadb.pid <==进程文件路径 #!includedir /etc/my.cnf.d <==注释掉此项 '3. 拷贝修改后的文件,分别创建3307,3308的配置文件,将文件内容修改为3307和3308' [root@hai7-8 /data]$cp mysql/3306/etc/my.cnf mysql/3307/etc/my.cnf [root@hai7-8 /data]$cp mysql/3306/etc/my.cnf mysql/3308/etc/my.cnf
- 准备服务启动脚本,脚本内容如下命名为mysqld,拷贝到各实例的bin目录下
脚本内容[root@hai7-8 /data]$cp mysqld /data/mysql/3306/bin/ [root@hai7-8 /data]$cp /data/mysql/3306/bin/mysqld /data/mysql/3307/bin/ [root@hai7-8 /data]$cp /data/mysql/3306/bin/mysqld /data/mysql/3308/bin/ '将配置文件端口已经根目录修改后,加上执行权限' [root@hai7-8 /data]$chmod +x /data/mysql/3306/bin/mysqld [root@hai7-8 /data]$chmod +x /data/mysql/3307/bin/mysqld [root@hai7-8 /data]$chmod +x /data/mysql/3308/bin/mysqld
#!/bin/bash port=3306 <==端口变量 mysql_user="root" <==启动mysql的用户身份 mysql_pwd="" <==指定root口令 cmd_path="/usr/bin" <==二进制程序路径,如果是二进制安装这里是/usr/local/bin,编译安装为编译时指定的路径 mysql_basedir="/mysqldb" <==以哪个目录为根,这里要修改为上面规划的/data/mysql mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock" function_start_mysql() <==负责启动mysql的函数,关键部分为通过mysqld_safe程序,读取--defaults-file=的配置文件 { if [ ! -e "$mysql_sock" ];then printf "Starting MySQL...\n" ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf &> /dev/null & else printf "MySQL is running...\n" exit fi } function_stop_mysql() <==负责停止mysql的函数,关键部分为通过mysqladmin shutdown关闭指定端口的mysql { if [ ! -e "$mysql_sock" ];then printf "MySQL is stopped...\n" exit else printf "Stoping MySQL...\n" ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown fi } function_restart_mysql() <==负责重启mysql的函数 { printf "Restarting MySQL...\n" function_stop_mysql sleep 2 function_start_mysql } case $1 in start) function_start_mysql ;; stop) function_stop_mysql ;; restart) function_restart_mysql ;; *) printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n" esac
- 启动各实例的mysql服务,查看端口号
'1. 启动服务' [root@hai7-8 /data]$/data/mysql/3306/bin/mysqld start [root@hai7-8 /data]$/data/mysql/3307/bin/mysqld start [root@hai7-8 /data]$/data/mysql/3308/bin/mysqld start '2. 查看端口号' [root@hai7-8 /data]$ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 50 *:3306 *:* LISTEN 0 50 *:3307 *:* LISTEN 0 50 *:3308 *:*
- 启动数据库指定端口号,或者指定sock文件
'指定socket文件连接,使用-S(大S)选项,加上sock文件路径' [root@hai7-8 /data]$mysql -S /data//mysql/3306/socket/mysql.sock '指定端口号为-P选项' [root@hai7-8 /data]$mysql -P 3306 -h127.0.0.1
- 修改数据库口令
[root@hai7-8 /data]$mysqladmin -uroot -S socket/mysql.sork password "123"
- 将各实例设置为开机启动,
'1. 拷贝服务启动脚本到init.d下,自定义名称' [root@hai7-8 /data]$cp /data/mysql/3306/bin/mysqld /etc/init.d/mysql3306 '2. 增加开机启动需要的内容' [root@hai7-8 /data]$vim /etc/init.d/mysql3306 #!/bin/bash #chkconfig: 345 20 50 <==增加此行,345级别可以启动,启动序号为20,关闭序号50 #description: mysql 3306 <==增加此行,描述信息 '3. 确定有执行权限后,将其加入启动列表' [root@hai7-8 /data]$chkconfig --add mysql3306 '或者放到/etc/rc.local中'
- 计划创建3个同版本实例,使用的端口号为3306,3307,3308;安装软件,由于是基于同一个版本,所以安装一次软件即可,yum安装会自动生成账号
2 创建数据库
-
获取信息
- 获取命令使用帮助:
HELP KEYWORD;
- 查看数据库列表:
SHOW DATABASES;
- 查看支持所有字符集:
SHOW CHARACTER SET;
- 查看支持所有排序规则:
SHOW COLLATION;
- 查看当前mysql版本信息
status
- 获取命令使用帮助:
-
创建数据库
- 创建数据库命令:create database
首先查看帮助说明
创建命令语法MariaDB [(none)]>help create database
在mysql中database和schema是等效的,其他数据库有区别
create_specification子句CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ... `[IF NOT EXISTS]`用来判断数据库是否存在的参数 `[create_specification]`用来定义数据库的子句
'1. 定义字符集:尽可能选择UTF8的字符集,可以通过`show character set`查看默认支持的字符集,utf8mb4可以支持特殊符号,比如表情包' [DEFAULT] CHARACTER SET [=] charset_name '2. 定义排序规则:字符集有默认的排序规则,一般不需要指定,查看排序规则`SHOW COLLATION`' [DEFAULT] COLLATE [=] collation_name
- 创建一个数据库,查看其默认字符集
'1. 创建名为db1的数据库' MariaDB [(none)]> create database db1; '2. 查看创建时系统默认完整命令' MariaDB [(none)]> show create database db1; <==查看命令 | db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ | '系统会加上默认参数,默认的字符集为utf8,10版本以前默认的为latin1'
- 用户指定字符集输入示例,创建数据库db2,子句指定字符集为utf8mb4
MariaDB [(none)]> CREATE DATABASE db2 CHARACTER SET=utf8mb4;
- 创建的数据库,会在程序家目录下生成相应的库目录,没有数据前只有一个文件.opt
[root@hai7-8 data]$cat mysql/db2/db.opt default-character-set=utf8mb4 <==默认字符集 default-collation=utf8mb4_general_ci <==默认排序方法
- 既然数据库是以目录方式存在的,也可以在家目下创建一个目录当做数据库
[root@hai7-8 mysql]$cp db1 db3 -ar [root@hai7-8 mysql]$ll drwx------ 2 mysql mysql 20 Oct 6 09:31 db1 drwx------ 3 mysql mysql 31 Oct 6 10:07 db3 <==权限所属都没有改变,连其下的db.opt一起复制 '在数据库中查看数据库列表' MariaDB [mysql]> show databases; +--------------------+ | Database | +--------------------+ | db1 | | db2 | | db3 | <==可以生成
- 查看数据库列表: 命令为
SHOW DATABASES
MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | db1 | | db2 | | information_schema | | mysql | | performance_schema | | test | +--------------------+
- 切换数据库,使用命令
use db_name;
MariaDB [(none)]> use db2 Database changed MariaDB [db2]> <==中括号中设置的为当前数据库,可以看到切换到db2中
- 创建数据库命令:create database
-
删除数据库
- 命令语法
DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';
- 示例
MariaDB [mysql]> drop database IF EXISTS test <==如果为空就删除,避免误删除
- 命令语法
3 创建表
-
表结构
二维关系(横行纵列)- 表名:尽可能和存放的实体密切相关,表名大小写敏感(例如修改或搜索时)
- 字段:
- 字段名:例如姓名、性别,大小写不敏感
- 字段数据类型:需要声明此字段数据类型
- 修饰符:用于定义字段属性
- 约束,索引:应该创建在经常用作查询条件的字段上
-
获取信息
- 帮助
HELP CREATE TABLE;
- 查看所有支持的引擎
SHOW ENGINES
- 查看表列表
SHOW TABLES [FROM db_name]
- 查看表结构
DESC [db_name.]tb_name
- 查看表创建命令,显示创建此表的各种参数
SHOW CREATE TABLE tbl_name
- 查看表状态
SHOW TABLE STATUS LIKE 'tbl_name'
横向显示 MariaDB [db2]> SHOW TABLE STATUS LIKE 'newcoc2'; 纵向显示 MariaDB [db2]> SHOW TABLE STATUS LIKE 'newcoc2'\G
- 查看库中所有表状态
SHOW TABLE STATUS FROM db_name
横向显示 MariaDB [db2]> SHOW TABLE STATUS FROM hellodb;<== ;可以替换为\g 纵向显示 MariaDB [db2]> SHOW TABLE STATUS FROM hellodb\G
- 帮助
-
创建表方法
-
直接创建
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]select_statement
-
通过复制现存的表的表结构创建,但不复制数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }
-
-
参数说明
- 建表描述(create_definition,…)
格式为:(col1 type1 修饰符, col2 type2 修饰符, …)- col :字段的名称
- type:字段类型
- 修饰符:来修饰此字段的特性
- 表选项[table_options]
-
在表创建时指明其使用的存储引擎,同一库中不同表可以使用不同的存储引擎,建议使用同一种,默认为InnoDB
ENGINE [=] engine_name
-
指定行的格式,一般情况默认,避免带来数据库压力
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
-
- 建表描述(create_definition,…)
-
修饰符
- 所有类型
类型 描述 INDEX 索引 NULL 数据列可包含NULL值。允许为空 NOT NULL 数据列不允许包含NULL值,不允许为空 DEFAULT 定义默认值,防止出现空值 PRIMARY KEY 主键 UNIQUE KEY 唯一键 CHARACTER SET name 表可以自定义使用的字符集,不建议单独指定 - 数值型
类型 描述 AUTO_INCREMENT 自动递增,适用于整数类型,例如职工编号 UNSIGNED 无符号,表示全为正数 -
删除表
DROP TABLE [IF EXISTS] tb_name
示例,删除表newcocMariaDB [db2]> DROP TABLE IF EXISTS newcoc;
3.1 数据类型
官方文档 https://dev.mysql.com/doc/refman/5.5/en/data-types.html
-
数据类型解决数据以下问题
- 数据长什么样?
- 数据需要多少空间来存放?
-
MySql支持多种内置类型:
- 数值类型
- 日期/时间类型
- 字符串(字符)类型
-
选择正确的数据类型对于获得高性能至关重要,三大原则:
- 更小的通常更好,尽量使用可正确存储数据的最小数据类型
- 简单就好,简单数据类型的操作通常需要更少的CPU周期
- 尽量避免NULL,包含为NULL的列,对MySQL更难优化
-
数据类型-数值型
-
整数型
数值型-整数 数据大小 取值范围 tinyint(m) 1个字节 范围(-128~127) smallint(m) 2个字节 范围(-32768~32767) mediumint(m) 3个字节 范围(-8388608~8388607) int(m) 4个字节 范围(-2147483648~2147483647) igint(m) 8个字节 范围(±9.22*10的18次方) - 加上unsigned修饰符,表示没有负数,则最大值翻倍
如:tinyintunsigned的取值范围为(0~255) - int(m)里的m是表示SELECT查询结果集中的显示宽度
并不影响实际的取值范围,规定了MySQL的一些交互工具(例如MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说,Int(1)和Int(20)是相同的 - BOOL,BOOLEAN:布尔型,是TINYINT(1)的同义词。zero值被视为假,非zero值视为真
- 加上unsigned修饰符,表示没有负数,则最大值翻倍
-
浮点型(float和double),近似值
数值型-浮点 数据大小 描述 float(m,d) 单精度浮点型8位精度(4字节) m总个数,d小数位 double(m,d) 双精度浮点型16位精度(8字节) m总个数,d小数位 示例1
设一个字段定义为float(6,3),如果插入一个数123.45678,实际数据库里存的是123.457,但总个数还以实际为准,即6位
示例2
在实际工作中,如果遇到财务数据,通常将元、角、分单独列段表示,避免小数 -
定点数
数值型-定点 描述 mal(m,d) 参数m<65 是总个数,d<30且d<m 是小数位 - 在数据库中存放的是精确值,存为十进制
- 浮点类型在存储同样范围的值时,通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节
- 因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用decimal——例如存储财务数据。但在数据量比较大的时候,可以考虑使用bigint代替decimal
- MySQL5.0和更高版本将数字打包保存到一个二进制字符串中(每4个字节存9个数字)。例如,decimal(18,9)小数点两边将各存储9个数字,一共使用9个字节:小数点前的数字用4个字节,小数点后的数字用4个字节,小数点本身占1个字节
-
-
数据类型-字符型
类型 是否可变 最大长度 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字节 -
char和varchar的区别
- char(n) 若存入字符数小于n,则以空格补于其后,查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格,varchar不限于此。
- char(n) 固定长度,char(4)不管是存入几个字符,都将占用4个字节,varchar是存入的实际字符数+1个字节(n< n>255),所以varchar(4),存入3个字符将占用4个字节。
- char类型的字符串检索速度要比varchar类型的快
-
varchar和text区别
- varchar可指定n,text不能指定,内部存储varchar是存入的实际字符数+1个字节(n< n>255),text是实际字符数+2个字节。
- text类型不能有默认值
- varchar可直接创建索引,text创建索引要指定前多少个字符
- varchar查询速度快于text
-
内建类型:
- ENUM枚举:列出范围,从中选择一个
- SET集合:可以选择任何好多个的组合
-
-
数据类型-二进制数据(BLOB)
- 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):年份以2|4个字符表示
3.2 创建示例
- 创建新表 students,自定义属性
MariaDB [(none)]> CREATE TABLE students ( <==创建命令 -> id int unsigned AUTO_INCREMENT primary key , <==()内为表描述,定义类型属性等 'ID,数据类型为int,自动递增AUTO_INCREMENT,设为主键(pk),主键不能为空' -> name varchar (30) not null, ' 姓名,数据类型为varchar,占30个字符(30),不允许为空' -> sex enum('m','f'), '性别,规定枚为m或f,只能在此区间选择一个' -> age tinyint unsigned default 20, ' 年龄,数据类型为tinyint,全为正数unsigned,默认值default为20' -> PRIMARY KEY(id,name) <==没有此行,如果要定义复合主键,前面的内容就不能定义,主键只能有一个 ' 如果使用复合主键,表示方法如上所示,在最后逗号隔开为独立项' -> );
- 查看表的创建内容,以上例的students为例
方法一 MariaDB [db2]> SHOW CREATE TABLE students 方法二 MariaDB [db2]> DESC students ;
- 查询表内容,以hellodb数据库中的coc表为例,命令为
SELECT * FROM coc
'1. 列出现有数据库' MariaDB [db2]> show databases; '2. 进入选定数据库' MariaDB [db2]> use hellodb '3. 列出数据库内所有表列表 ' MariaDB [hellodb]> show tables '4. 查看coc表格' MariaDB [hellodb]> SELECT * FROM coc;<==select表示查询,[ * ]表示所有字段,from表示从那张表查 +----+---------+----------+ | ID | ClassID | CourseID | +----+---------+----------+ | 1 | 1 | 2 | | 2 | 1 | 5 |
- 通过查询现存表创建;新表会被直接插入查询而来的数据,新表为newcoc,旧表为coc
跨数据库,以hellodb数据库表格为模板,在db2数据库中建表newcoc'创建表格' MariaDB [hellodb]> CREATE TABLE newcoc SELECT * FROM coc '比较新旧两张表的内容,结构与内容基本相同' MariaDB [hellodb]> SELECT * FROM newcoc; +----+---------+----------+ | ID | ClassID | CourseID | +----+---------+----------+ | 1 | 1 | 2 | | 2 | 1 | 5 |
'1. 切换至db2数据库' MariaDB [hellodb]> use db2 '2. 语法与上例相同,查询地址加上数据库名称以点与表隔开,表示为`hellodb.coc`' MariaDB [db2]> CREATE TABLE newcoc SELECT * FROM hellodb.coc
- 通过复制现存的表的表结构创建,但不复制数据
方法2:给定查询命令一个不能匹配的条件,返回空置,只有表结构,创建空值的查询结果,就是表结构'1. 以hellodb.coc为模板,在db2数据库中创建架构类似的表格newcoc2' MariaDB [db2]> CREATE TABLE newcoc2 LIKE hellodb.coc <==命令格式 '2. 查看结构' MariaDB [db2]> desc newcoc2; <==结构相同 +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | ID | int(10) unsigned | NO | PRI | NULL | auto_increment | | ClassID | tinyint(3) unsigned | NO | | NULL | | | CourseID | smallint(5) unsigned | YES | | NULL | | +----------+----------------------+------+-----+---------+----------------+ '3. 查看表内容' MariaDB [db2]> select * from newcoc2; Empty set (0.00 sec) <==表内容为空
MariaDB [db2]> CREATE TABLE newcoc3 SELECT * FROM hellodb.coc where 1=0;
3.3 表文件存放说明
在mysql中创建的数据库以及表格,都会在mysql的家目录下生成对应目录和文件,10.2版本在数据库下表格文件是一对,分别存放表结构和表数据,如下所示
[root@hai7-8 db2]$ll /mysql/db2/
total 304
-rw-rw---- 1 mysql mysql 67 Oct 6 09:44 db.opt
-rw-rw---- 1 mysql mysql 976 Oct 6 14:38 newcoc2.frm <==表结构
-rw-rw---- 1 mysql mysql 98304 Oct 6 14:38 newcoc2.ibd <==表数据
-rw-rw---- 1 mysql mysql 487 Oct 6 14:33 newcoc.frm
-rw-rw---- 1 mysql mysql 98304 Oct 6 14:33 newcoc.ibd
-rw-rw---- 1 mysql mysql 1115 Oct 6 11:25 students.frm
-rw-rw---- 1 mysql mysql 98304 Oct 6 11:25 students.ibd
老版本比如镜像文件中自带的5.5版,数据库下只存放各表的结构文件.frm,所有数据统一放在mysql目录下的ibdata1文件,不好区分,文件超,需要通过修改配置文件,将数据独立生成文件,在mysqld块下增加如下参数
[root@localhost support-files]$vim /etc//my.cnf
[mysqld]
innodb_file_per_table= on <==数据库的每一个表都生成独立的文件10.2后版本,默认开启
4 表操作
Help ALTER TABLE 查看帮助
4.1 字段
-
添加字段:add
- 语法格式
ADD col1 data_type[FIRST|AFTER col_name]
- 参数说明
参数 描述 col1 加入字段的字段名 data_type 数据类型 FIRST 在表的最前面加入 AFTER col_name 在指定字段后加入 - 示例
显示表students的创建信息,以下各例将在此表基础上进行
:在students表name后新增一行tel,数据类型为char占用11各字节MariaDB [db2]> desc students +-------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | NULL | | | sex | enum('m','f') | YES | | NULL | | +-------+---------------------+------+-----+---------+----------------+
再次查看students的创建信息MariaDB [db2]> ALTER TABLE students ADD tel char(11) AFTER name ;
MariaDB [db2]> desc students | name | varchar(30) | NO | | NULL | | | tel | char(11) | YES | | NULL | | <==新增字段信息
- 语法格式
-
删除字段
- 参数为
drop
- 示例,删除上例中增加的字段tel
MariaDB [db2]> ALTER TABLE students DROP tel ; | 表操作命令 | 表名 | 参数|删除字段|
- 参数为
-
修改表名
- 参数为
RENAME
- 示例:将表students改名为S1
MariaDB [db2]> ALTER TABLE students RENAME S1 ;
- 参数为
-
修改字段属性
- 参数为
MODIFY
- 示例:修改S1表的id段属性,原属性为int,修改为tinyint
MariaDB [db2]> ALTER TABLE S1 MODIFY id tinyint;
- 参数为
-
修改字段名
CHANGE COLUMN
:- 参数为
CHANGE COLUMN
- 示例:将表S1中tel字段修改为num,数据类型为char(15)
MariaDB [db2]> ALTER TABLE S1 CHANGE COLUMN tel num char(15) ;
- 参数为
-
索引:
-
查看表上的索引
- 语法格式:
SHOW INDEXES FROM [db_name.]tbl_name;
- 示例:查看数据库db2中S1表的索引
MariaDB [db2]> SHOW INDEXES FROM db2.S1 \G
- 语法格式:
-
添加索引:
- 参数:
add index
- 示例:为S1表的name字段增加索引
MariaDB [db2]> ALTER TABLE S1 ADD INDEX(name)\G
- 参数:
-
删除索引:
- 参数:
drop index
- 示例:删除上例中name字段的索引
MariaDB [db2]> ALTER TABLE S1 DROP index name; MariaDB [db2]> DROP INDEX name on S1;
- 参数:
-
-
增加唯一键
- 语法格式
ALTER TABLE students ADD UNIQUE KEY(name); - 示例:后面讲唯一键时详见
- 语法格式
4.2 DML语句(INSERT, DELETE, UPDATE)
-
INSERT:一次插入一行或多行数据
-
语法
INSERT <==插入命令 [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] <==指定优先级 [IGNORE] <==忽略 [INTO] tbl_name[(col_name,...)] <==INTO可加可不加,跟表名,()中指定需要赋值的字段名 ,如果不指定默认全部赋值,按表顺序依次赋值 {VALUES | VALUE} ({expr | DEFAULT},...),(...),... <==为字段赋值,值得顺序与字段名一一对应 [ ON DUPLICATE KEY UPDATE <==如果重复更新之,用的不多
另一种赋值写法
col_name=expr
[, col_name=expr] ... ]
常用简化写法
INSERT tbl_name[(col1,...)] VALUES (val1,...), (val21,...)
-
示 例
为表S1添加两行内容,指定id,name,sex,分别为(1,‘石昊’,‘m’),(2,‘sanfeng’,‘f’)
注:utf8支持中文,如果使用其他字符集可能会乱码,赋值时除了数值型外,其他的都要加引号MariaDB [db2]> INSERT INTO S1 (id,name,sex) VALUES (1,'石昊','m'),(2,'sanfeng','f'); | 增加命令 |表名|增加内容的字段|参数VALUES|添加内容
赋值的另一种写法,为表S1增加内容 name=‘黄忠’,sex=‘m’,id=4
MariaDB [db3]> INSERT INTO S1 SET name='黄忠',sex='m',id=4; | 命令 |表名|参数|添加项及内容 相当于键=值 |
将其他表内的数据导入当前表中,将db2数据库中的S1表内容复制到当前数据库S1表中
注:跨数据库引用,字段名字可以不同,但字段数据类型必须相同,字段数也要相同MariaDB [db3]> INSERT INTO S1 (id,name,sex) SELECT id,name,sex FROM db2.S1; 将查询结果添加到当前表中|命令|表名|预添加内容的字段|查找参数| 查找项 |参数|来自哪里|
-
-
UPDATE:修改表内容
注意:一定要有限制条件,否则将修改所有行的指定字段- 语法格式
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] <==限制条件语句 [ORDER BY ...] <==排序 [LIMIT row_count] <==与排序配合使用,去指定行
- 限制条件
WHERE:限制范围
LIMIT:与ORDER BY配合使用,取指定行 - 防止误操作,没有加限制条件执行命令,造成数据破坏
在登入mysql时,加-U|--safe-updates| --i-am-a-dummy
命令行登入也可能忘记加-U选项,可以直接加到配置文件中 ,5.5版本加入到客户端配置文件中/etc/my.cnf.d/mysql-clients.cnf[root@hai7-8 ~]$mysql -U
[root@hai7-8 mysql]$vim /etc/my.cnf [mysql] safe-updates
- 示例
修改表S1中id为2的行,将name改为貂蝉,sex修改为’f’MariaDB [db3]> UPDATE S1 SET name='貂蝉',sex='f'where id=2
- 语法格式
-
DELETE:删除表内容
注意:一定要有限制条件,否则将清空表中的所有数据,同UPDATE,登入时加入-U或者修改配置文件-
语法格式
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_condition] <==限制条件 [ORDER BY ...] [LIMIT row_count]
-
限制条件
WHERE:限制范围
LIMIT:删除指定数量的行 -
示例
删除单条记录,删除表S1,id号为2的行MariaDB [db2]> DELETE FROM S1 where id=2;
删除表的前多少列,参数
LIMIT
;删除students表的前4行,如果进入mysql设置了-U选项,此操作不能进行MariaDB [hellodb]> DELETE FROM students LIMIT 4;
根据需要排序后删除,参数
ORDER BY
;删除表students中年级最小的行MariaDB [hellodb]> DELETE FROM students ORDER BY Age LIMIT 1;
清空表内容
TRUNCATE TABLE tbl_name <==快速清除所有内容 DELETE FROM S1 <==效率不高,同样可以删除
-
5 DQL语句(Data Query Language): 数据查询语言
SELECT分类:单表查询,多表查询
5.1 单表查询
-
指定条件,过滤想要的行
WHERE子句:指明过滤条件以实现“选择”的功能- 限定范围
BETWEEN min_num AND max_num - 限定值
IN (element1, element2, …) - 过滤条件:布尔型表达式
- 为空
IS NULL - 非空
IS NOT NULL
- 为空
- 操作符
- 算术操作符
+, -, *, /, % - 比较操作符
=,<=>(相等或都为空), <>(不等), !=(非标准SQL), >, >=, <, <= - 逻辑操作符:
NOT,AND,OR,XOR
- 算术操作符
- 示例
示例1:挑选需要的字段来查看,例如值查看teachers表的 name ,age ,tid字段
示例2:用别名显示字段信息,格式为MariaDB [hellodb]>SELECT name ,age ,tid FROM teachers; |查寻命令| 查询字段 | 参数| 查询表名| +---------------+-----+-----+ | name | age | tid |
col1 AS alias1, col2 AS alias2, ...
,顺序可以不按原表
将teachers表的字段名显示为中文(起别名)
示例3:查看students表中,字段Age大于30的学员MariaDB [hellodb]> SELECT age AS 年龄,name AS 姓名,tid AS 编号 from teachers; | 命令 |命名age别名 | 命名name别名 | 命名tid别名| 参数| 查询表名| +---------------+--------+--------+ | 姓名 | 年龄 | 编号 | +---------------+--------+--------+ | Song Jiang | 45 | 1 | 起别名AS可以省略不写 MariaDB [hellodb]> SELECT name 姓名,age 年龄,tid 编号 from teachers;
示例:4:限制条件为给定值MariaDB [hellodb]> SELECT * FROM students WHERE Age>30;
IN (element1, element2, ...)
,查看students表中属于1班和2班的学员
示例5:查看值为null(空)的行,语法为MariaDB [hellodb]> SELECT * FROM students WHERE ClassID in (1,2);
is null
,特殊语法不用=号,使用is
列出表students中, teacherid列为空的行
示例6:列出表students中 teacherid列非空的行,语法为MariaDB [hellodb]> SELECT * FROM students WHERE teacherid is null;
is not null
示例7:列出表students,年龄大于30且小于50的学员MariaDB [hellodb]> SELECT * FROM students WHERE teacherid is not null;
另一种写法MariaDB [hellodb]> SELECT * FROM students WHERE Age>30 and Age<50;
between
,这种写法相当于 Age>=30 and Age<=50MariaDB [hellodb]> SELECT * FROM students WHERE age between 33 and 50;
- 限定范围
-
DISTINCT 去除重复列
-
语法格式
SELECT DISTINCT gender FROM students;
-
通配符用法
参数:LIKE- %: 任意长度的任意字符
- _:任意单个字符
-
正则表达式
- RLIKE:正则表达式,索引失效,不建议使用
- REGEXP:匹配字符串可用正则表达式书写模式,同上
-
示例
示例1:去重列示例,参数为DISTINCT
,查看表中种类
学员表students,想要查看所报班级classid共种类MariaDB [hellodb]> SELECT DISTINCT classid FROM students ;
示例2:通配符使用示例,搜索students表中,name以S开头的行
MariaDB [hellodb]> SELECT * FROM students where name like "s%"
通配符使用示例,关键字为
LIKE
,搜索students表中,name以o结尾的行MariaDB [hellodb]> SELECT * FROM students where name like "%o" `%o`左写法和包含`%o%`不建议使用,会带来性能影响
正则表达式使用示例,关键字为
RLIKE|REGEXP
,搜索students表中,name以x开始的行,在mysql中不推荐使用,性能不好MariaDB [hellodb]> SELECT * FROM students where name RLIKE "^X"
-
-
ORDER BY 排序
根据指定的字段对查询结果进行排序,排序结果是由指定的排序规则决定的,通常是指定的字符集排序规则,查看字符集排序规则show character set
- 参数
- 升序:ASC
- 降序:DESC
- 示例
给字段Age排序,显示students表,如果排序字段数据为字符串,以字母顺序排列默认值(ASC) MariaDB [hellodb]> SELECT * FROM students ORDER BY Age; 倒叙排列 MariaDB [hellodb]> SELECT * FROM students ORDER BY Age DESC ;
- 参数
-
LIMIT
配合ORDER BY使用,对查询的结果进行输出行数数量限制- 语法
[[offset,]row_count] - 示例
示例1:查找年龄最大的3个同学
示例2,偏移量[offset]示例,承上,跳过年龄最大的,只显示年龄第2和第3大的同学MariaDB [hellodb]> SELECT * FROM students ORDER BY Age DESC LIMIT 3; +-------+--------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+--------------+-----+--------+---------+-----------+ | 25 | Sun Dasheng | 100 | M | NULL | NULL | | 6 | Shi Qing | 46 | M | 5 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | +-------+--------------+-----+--------+---------+-----------+
MariaDB [hellodb]> SELECT * FROM students ORDER BY Age DESC LIMIT 1,2; <==1为偏移量,即offset +-------+--------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+--------------+-----+--------+---------+-----------+ | 6 | Shi Qing | 46 | M | 5 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | +-------+--------------+-----+--------+---------+-----------+
- 语法
-
GROPU 分组
根据指定的条件把查询结果进行“分组”以用于做“聚合”运算-
聚合函数
avg():返回expr 的平均值
max():返回最大字符串值
min():返回最小字符串值
count():返回SELECT语句检索到的行中非NULL值得数目,若找不到匹配的行,则COUNT(表达式|字段名)返回0
sum():不同值的总和 -
条件限定
HAVING: 对分组聚合运算后的结果指定过滤条件,作用同未分组查找的where -
示例
以下示例需要用到的表 students+-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | 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 | | 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 | +-------+---------------+-----+--------+---------+-----------+
示例1:聚合函数COUNT(),统计表students,包含多少行
MariaDB [hellodb]> SELECT COUNT(*) FROM students;
示例2:如果统计的字段有空值,则返回结果为非空数目,统计表students,字段TeacherID
MariaDB [hellodb]> SELECT COUNT(TeacherID) FROM students; | COUNT(TeacherID) | +------------------+ | 1 |
示例3:分组
GROUP BY
,统计上表中各班级有多少人MariaDB [hellodb]> SELECT ClassID,COUNT(*) FROM students GROUP BY ClassID; '分组后,SELECT后面跟的搜索内容,只能是2种,1是分组字段名 ,2是聚合函数' +---------+----------+ | ClassID | COUNT(*) | +---------+----------+ | NULL | 2 | | 1 | 3 | | 2 | 1 | | 3 | 3 | | 4 | 3 | | 5 | 1 | | 6 | 4 | | 7 | 2 | +---------+----------+
示例4:统计上表各班中年龄最小的同学,
函数MIN()
MariaDB [hellodb]> SELECT ClassID,MIN(Age) FROM students GROUP BY ClassID; +---------+----------+ | ClassID | MIN(Age) | +---------+----------+ | NULL | 27 | | 1 | 19 | | 2 | 33 | | 3 | 19 | | 4 | 19 | | 5 | 46 | | 6 | 18 | | 7 | 19 | +---------+----------+
示例5:统计上表各班学员的平均年龄,
函数AVG()
MariaDB [hellodb]> SELECT ClassID,AVG(Age) FROM students GROUP BY ClassID;
示例6:分组后条件过滤
HAVING
,统计1班和2班学员的平均年龄MariaDB [hellodb]> SELECT ClassID,AVG(Age) FROM students GROUP BY ClassID HAVING ClassID IN (1,2);
示例7:分组前WHERE与分组后HAVING组合使用,统计1班和2班男学员的平均年龄
MariaDB [hellodb]> SELECT ClassID,AVG(Age) FROM students WHERE Gender = "m" GROUP BY ClassID HAVING ClassID IN (1,2); 在分组前使用`WHERE`过滤出所有男同学,再根据过滤条件分组,分组后过滤出1班和2班
示例8:一次可以对多个字段分组,统计各班男女分别有多少人
MariaDB [hellodb]> SELECT ClassID,Gender,COUNT(*) FROM students GROUP BY ClassID,Gender; +---------+--------+----------+ | ClassID | Gender | COUNT(*) | +---------+--------+----------+ | NULL | M | 2 | | 1 | F | 2 | | 1 | M | 1 | | 2 | M | 1 | | 3 | F | 2 | | 3 | M | 1 | | 4 | M | 3 | | 5 | M | 1 | | 6 | F | 3 | | 6 | M | 1 | | 7 | F | 1 | | 7 | M | 1 | +---------+--------+----------+
-
-
练习题
- 在students表中,查询年龄大于25岁,且为男性的同学的名字和年龄
MariaDB [hellodb]> SELECT * FROM students WHERE age > 20 and GENDER = "M" ;
- 以ClassID为分组依据,显示每组的平均年龄
MariaDB [hellodb]> SELECT Classid,AVG(Age) FROM students GROUP BY Classid;
- 显示第2题中平均年龄大于30的分组及平均年龄
MariaDB [hellodb]> SELECT Classid,AVG(Age) FROM students GROUP BY Classid HAVING avg(age) > 30;
- 显示以L开头的名字的同学的信息
MariaDB [hellodb]> SELECT * FROM students WHERE name LIKE "L%";
- 显示TeacherID非空的同学的相关信息
MariaDB [hellodb]> SELECT * FROM students WHERE TeacherID IS not null;
- 以年龄排序后,显示年龄最大的前10位同学的信息
MariaDB [hellodb]> SELECT * FROM students ORDER BY Age DESC LIMIT 10;
- 查询年龄大于等于20岁,小于等于25岁的同学的信息
MariaDB [hellodb]> SELECT * FROM students WHERE Age >=20 and Age <=25; MariaDB [hellodb]> SELECT * FROM students WHERE Age between 20 and 25;
- 数据类型为日期和时间的格式
datetime
,查询格式为MariaDB [db2]> select * from S1 WHERE birth between "1988-01-01 00:00:00" and "1991-01-01 10:10:00"
- 在students表中,查询年龄大于25岁,且为男性的同学的名字和年龄
6. 多表查询
-
以下说明需要用到的表
- 表students
MariaDB [hellodb]> select * from students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | 1 | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | 2 | | 21 | Huang Yueying | 22 | F | 6 | 3 | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | 2 | +-------+---------------+-----+--------+---------+-----------+
- 表teachers
MariaDB [hellodb]> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | +-----+---------------+-----+--------+
- 表students
-
多表查询
- 语法格式(与内连接的效果相同)
SELECT <SELECT_LIST> FROM db_name1,db_name2… [where A.KEY=B.KEY ] - 参数说明
DISTINCT:表示需要显示的字段,多个用逗号分隔,可以使用别名区分不同表中同名字段
db_name:表名 - 限定条件:[where A.KEY=B.KEY ]
多表查询的限定条件为两表之间有联系的字段,比如students表中的teacherid字段,对应的是teachers表中的tid - 示例:找出当前students表中有辅导老师的学员,并显示老师名字
MariaDB [hellodb]> SELECT s.name, t.name,t.tid FROM students s,teachers t WHERE s.teacherid=t.tid; +---------------+---------------+-----+ | name | name | tid | +---------------+---------------+-----+ | Hua Rong | Song Jiang | 1 | | Diao Chan | Zhang Sanfeng | 2 | | Ma Chao | Zhang Sanfeng | 2 | | Huang Yueying | Miejue Shitai | 3 | +---------------+---------------+-----+
- 语法格式(与内连接的效果相同)
-
子查询:基于某语句的查询结果再次进行的查询,性能较差
-
用在WHERE子句中的子查询:
用于比较表达式中的子查询;子查询仅能返回单个值
例如,下例括号中的子查询,只返回平均年龄MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Age > (SELECT avg(age) FROM students);
-
用于IN中的子查询:子查询应该单键查询并返回一个或多个值从构成列表
IN只执行一次,将括号内的子表与主查询表做笛卡尔乘积,再按主查询条件筛选结果
例如:下例中查询所有老师的年龄,返回多值,来匹配学生的年龄MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
-
用于EXISTS,实现条件判断,EXISTS后的子查询实际上并不返回任何数据,而是返回值True或False
根据表students的每一条记录,依次去判断WHERE EXISTS后面的条件是否成立,成立则返回true,保留此行,不成立则返回false,删除此行。MariaDB [hellodb]> SELECT a.stuid, a.name FROM students a WHERE EXISTS (SELECT b.stuid FROM scores b WHERE a.stuid=b.stuid);
-
多表查询,用于FROM子句中的子查询
- 使用格式:
SELECT tb_alias.col1,… FROM (SELECT clause) AS tb_alias WHERE Clause; - 示例:
找出平均年龄大于30的分组MariaDB [hellodb]> SELECT s.aage,s.ClassID FROM <==以子查询结果各组的平均年龄为查询对象 (SELECT avg(Age) AS aage,ClassID <==以 ClassID分组,显示每组的平均年龄 FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s WHERE s.aage>30;
- 使用格式:
-
示例:查询students表大于平级年龄的同学
MariaDB [hellodb]> select * from students where age >(select avg(age) from students) ; +-------+--------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+--------------+-----+--------+---------+-----------+ | 6 | Shi Qing | 46 | M | 5 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | +-------+--------------+-----+--------+---------+-----------+
-
-
交叉连接:笛卡尔乘积
表示为X × Y,第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员- 语法格式
SELECT DISTINCT FROM db_name1 [cross join] db_name2...
- 参数说明
cross join:交叉连接关键字,表示A与B的连接方式,可以不加此参数,效果相同 - 示例:
示例1:以交叉连接方式,将表students和表teachers连接起来
如上例中显示连接效果,两表都有name字段,访客不好区分数据,可以使用别名加以区分;MariaDB [hellodb]> select * from students <==表1 -> cross join <==交叉合并参数 -> teachers; <==表2 +---------------+---------------+-----+ | name | name | tid | +---------------+---------------+-----+ | Lin Chong | Song Jiang | 1 | <==表students中每一项都与teachers表中的项匹配一次,全部列出 | Lin Chong | Zhang Sanfeng | 2 | | Lin Chong | Miejue Shitai | 3 | | Lin Chong | Lin Chaoying | 4 | | Hua Rong | Song Jiang | 1 | | Hua Rong | Zhang Sanfeng | 2 | | Hua Rong | Miejue Shitai | 3 | | Hua Rong | Lin Chaoying | 4 | ......省略
定义了别名后,就不可以使用原名了,不然会报错
示例2::为显示的字段取别名(students.name表示students表的name字段)
示例3:先为表取别名,再去定义字段别名MariaDB [hellodb]> select students.name student_name, <==定义students表的name字段,别名为student_name,下同 students.age student_age, teachers.name teacher_name, <==定义teachers表的name字段,下同 teachers.age teacher_age from students cross join teachers;
交叉连接得到的是一个庞大的集合训,对于数据查询意义不大,我们通常需要的是表与表之间的连接内容,比如students表中学生对应的负责教师名字;利用内连接方式组合表格,可以得到我们想要的数据MariaDB [hellodb]> select s.name student_name, <==s.name代表的就是students表的name字段 s.age student_age, t.name teacher_name, t.age teacher_age from students as s cross join teachers as t; <==将students表定义别名为s, 将teachers表定义别名为 t
- 语法格式
-
内连接
- 等值连接
让表之间的字段以“等值”建立连接关系;连接表不分前后,得到结果相同 - 语法格式
SELECT <SELECT_LIST> FROM tableA INNER JOIN tableB ON A.KEY=B.KEY - 示例
使用内连接,将学生信息与负责教师信息放在一起MariaDB [hellodb]> SELECT <== 命令 s.name stu_name, <== students表name字段的别名 t.name t_name , <==teacherss表name字段的别名 t.tid FROM <== 语法关键字 students s <== students表的别名为s INNER JOIN <==表连接参数 teachers t <==teachers表name字段的别名 ON <==连接参数 s.teacherid=t.tid; <==有联系的字段,A.KEY=B.KEY +---------------+---------------+-----+ | stu_name | t_name | tid | +---------------+---------------+-----+ | Hua Rong | Song Jiang | 1 | | Diao Chan | Zhang Sanfeng | 2 | | Ma Chao | Zhang Sanfeng | 2 | | Huang Yueying | Miejue Shitai | 3 | +---------------+---------------+-----+
- 等值连接
-
左外连接
- 语法格式
SELECT <SELECT_LIST> FROM tbA LEFT [OUTER] JOIN tbB ON A.KEY=B.KEY
- 等值连接
让表之间的字段以“等值”建立连接关系;连接表注意分前后,得到结果不同 - 显示效果
显示左边表A的所有信息,右边表B只显示匹配到的内容,没有匹配的用NULL(空)补上 - 连接参数
LEFT [OUTER] JOIN
- 示例
用左外连接结合表,将学生与教师信息放在一起MariaDB [hellodb]> SELECT s.name stu_name, t.name t_name ,t.tid from students s LEFT JOIN teachers t ON s.teacherid=t.tid; +---------------+---------------+------+ | stu_name | t_name | tid | +---------------+---------------+------+ | Lin Chong | NULL | NULL | <==B表也就是teachers表没有匹配到的,用NULL补 | Hua Rong | Song Jiang | 1 | | Xue Baochai | NULL | NULL | | Diao Chan | Zhang Sanfeng | 2 | | Huang Yueying | Miejue Shitai | 3 | | Xiao Qiao | NULL | NULL | | Ma Chao | Zhang Sanfeng | 2 | +---------------+---------------+------+
- 语法格式
-
左外连接延伸版
- 语法格式
SELECT <SELECT_LIST> FROM tbA LEFT [OUTER] JOIN tbB ON A.key=B.key WHERE B.key IS NULL
- 显示效果
仅保留左边表A,没有匹配项的行 - 示例
用左外连接延伸多表查询,将学生与教师信息放在一起MariaDB [hellodb]> SELECT s.name stu_name, t.name t_name ,t.tid from students s LEFT JOIN teachers t ON s.teacherid=t.tid WHERE t.tid IS NULL; +-------------+--------+------+ | stu_name | t_name | tid | +-------------+--------+------+ | Lin Chong | NULL | NULL | | Xue Baochai | NULL | NULL | | Xiao Qiao | NULL | NULL | +-------------+--------+------+
- 语法格式
-
右外连接
- 语法格式
FROM tbA RIGHT JOIN tbB ON A.KEY=B.KEY
- 显示效果
显示右边表B的所有信息,左边表A只显示匹配到的内容,没有匹配的用NULL(空)补上 - 连接参数
RIGHT JOIN - 示例
用右外连接结合表,将学生与教师信息放在一起MariaDB [hellodb]> SELECT s.name stu_name, t.name t_name ,t.tid from students s RIGHT JOIN teachers t ON s.teacherid=t.tid; +---------------+---------------+-----+ | stu_name | t_name | tid | +---------------+---------------+-----+ | Hua Rong | Song Jiang | 1 | | Diao Chan | Zhang Sanfeng | 2 | | Huang Yueying | Miejue Shitai | 3 | | Ma Chao | Zhang Sanfeng | 2 | | NULL | Lin Chaoying | 4 | <==只有4号老师是空闲的 +---------------+---------------+-----+
- 语法格式
-
右外连接延伸版
- 语法格式
SELECT <SELECT_LIST> FROM tbA RIGHT JOIN tbB ON A.key=B.key WHERE A.key IS NULL
- 显示效果
仅保留右边表B,没有匹配项的行 - 示例
用右外连接延伸多表查询,将学生与教师信息放在一起MariaDB [hellodb]> SELECT s.name stu_name, t.name t_name ,t.tid from students s RIGHT JOIN teachers t ON s.teacherid=t.tid WHERE s.teacherid IS NULL; +----------+--------------+-----+ | stu_name | t_name | tid | +----------+--------------+-----+ | NULL | Lin Chaoying | 4 | +----------+--------------+-----+
- 语法格式
-
完全外连接
musql中不支持这种语法,想要实现需要通过子查询,用左外连接union右外连接实现
- 正规语法格式
SELECT <SELECT_LIST> FROM tbA FULL OUTER JOIN tbB ON A.key=B.key
- 变相实现语法
SELECT <SELECT_LIST> FROM tbA LEFT JOIN tbB ON A.key=B.key <==左连接 UNION <==上下连接关键字 SELECT <SELECT_LIST> FROM tbA RIGHT JOIN tbB on ON A.key=B.key <==右连接
- 显示效果
显示表A与表B,所有匹配结果,包括没有匹配项的行 - 示例
完全外连接多表查询,将学员和老师信息放在一起MariaDB [hellodb]> SELECT s.name stu_name, t.name t_name ,t.tid from students s RIGHT JOIN teachers t ON s.teacherid=t.tid UNION SELECT s.name stu_name, t.name t_name ,t.tid from students s LEFT JOIN teachers t ON s.teacherid=t.tid WHERE t.tid IS NULL; +---------------+---------------+------+ | stu_name | t_name | tid | +---------------+---------------+------+ | Hua Rong | Song Jiang | 1 | | Diao Chan | Zhang Sanfeng | 2 | | Huang Yueying | Miejue Shitai | 3 | | Ma Chao | Zhang Sanfeng | 2 | | NULL | Lin Chaoying | 4 | | Lin Chong | NULL | NULL | | Xue Baochai | NULL | NULL | | Xiao Qiao | NULL | NULL | +---------------+---------------+------+
- 正规语法格式
-
完全外连延伸
- 正规语法格式
SELECT <SELECT_LIST> <==命令以及查询项 FROM tbA FULL OUTER JOIN tbB <==多表组合方式 ON A.key=B.key <==关联字段 WHERE A.key IS NULL OR B.key IS NULL <==限制条件
- 变相实现语法
SELECT <SELECT_LIST> FROM ( SELECT <SELECT_LIST> FROM tbA LEFT JOIN tbB ON A.key=B.key <==左连接 UNION <==去重连接关键字 SELECT <SELECT_LIST> FROM tbA RIGHT JOIN tbB on ON A.key=B.key <==右连接 ) AS % <==为括号中的结果起别名 WHERE %.key(A1) IS NULL OR %.key(B1) IS NULL `限定条件为原A表没有匹配结果的空字段或者B表没有匹配结果的空字段`
- 显示效果
显示表A与表B,所有没有匹配项的行 - 示例
完全外连接延伸多表查询,将学员和老师信息放在一起MariaDB [hellodb]> SELECT * FROM ( SELECT s.stuid,s.name stu_name, t.tid,t.name t_name from students s RIGHT JOIN teachers t ON s.teacherid=t.tid UNION SELECT s.stuid,s.name stu_name, t.tid,t.name t_name from students s LEFT JOIN teachers t ON s.teacherid=t.tid ) AS x WHERE x.stu_name IS NULL OR x.tid IS NULL; +-------+-------------+------+--------------+ | stuid | stu_name | tid | t_name | +-------+-------------+------+--------------+ | NULL | NULL | 4 | Lin Chaoying | | 17 | Lin Chong | NULL | NULL | | 19 | Xue Baochai | NULL | NULL | | 22 | Xiao Qiao | NULL | NULL | +-------+-------------+------+--------------+
- 正规语法格式
-
自然连接(纵向合并):自动去掉重复列的等值连接
将内容相仿的字段合并在一起,字段数量<SELECT_LIST> 必须匹配,字段内容不同是可以合并,不过用处不大- 语法格式
SELECT <SELECT_LIST> FROM tbA UNION SELECT <SELECT_LIST> FROM tbB
- 显示效果
以A表字段为名,依次从上到下显示A表和B表的查询结果 - 示例
将表studens和表teachers纵向合并
UNION合并可以自动去掉重复行,比如用students表,合并students表,会自动过滤掉重复行,依然显示为原值MariaDB [hellodb]> SELECT tid,name from teachers union SELECT stuid,name from students; +-----+---------------+ | tid | name | +-----+---------------+ | 1 | Song Jiang | | 2 | Zhang Sanfeng | | 3 | Miejue Shitai | | 4 | Lin Chaoying | | 17 | Lin Chong | | 18 | Hua Rong | | 19 | Xue Baochai | | 20 | Diao Chan | | 21 | Huang Yueying | | 22 | Xiao Qiao | | 23 | Ma Chao | +-----+---------------+
MariaDB [hellodb]> SELECT stuid,name from students UNION SELECT stuid,name from students; +-------+---------------+ | stuid | name | +-------+---------------+ | 17 | Lin Chong | | 18 | Hua Rong | | 19 | Xue Baochai | | 20 | Diao Chan | | 21 | Huang Yueying | | 22 | Xiao Qiao | | 23 | Ma Chao | +-------+---------------+ 7 rows in set (0.00 sec)
- 语法格式
-
自连接,特殊的外连接,在一张表内找对应关系
- 制作一个表,内容为职员id,name,领导leaderid
MariaDB [db2]> select * from emp; +------+----------+----------+ | id | name | leaderid | +------+----------+----------+ | 1 | shanfeng | NULL | | 2 | wuji | 1 | | 3 | yingwang | 2 | | 4 | wuxing | 3 | | 5 | jia | 3 | +------+----------+----------+
- 将员工的领导信息在同一行中列出,可以理解为将表emp拆分为个表,一张存员工,一张存领导,然后多表查询
MariaDB [db2]> SELECT s.id,s.name as ename,l.name <==查询项 FROM emp AS s <==将表emp定义别名为s LEFT OUTER JOIN <==外连接查询 emp AS l <==仍然是表emp定义别名为l ON s.leaderid=l.id; <==关联字段 +------+----------+----------+ | id | ename | name | +------+----------+----------+ | 2 | wuji | shanfeng | | 3 | yingwang | wuji | | 4 | wuxing | yingwang | | 5 | jia | yingwang | | 1 | shanfeng | NULL | +------+----------+----------+
- 制作一个表,内容为职员id,name,领导leaderid
-
三张表查询
- 准备三张表格,结构如下所示
- 表students结构
MariaDB [hellodb]> select * from students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+
- 表scores结构
MariaDB [hellodb]> select * from scores; +----+-------+----------+-------+ | ID | StuID | CourseID | Score | +----+-------+----------+-------+
- 表courses结构
MariaDB [hellodb]> select * from courses; +----------+----------------+ | CourseID | Course | +----------+----------------+
- 表students结构
- 目标显示结果为|students.name | courses.course|scores.score|
- 利用内连接查询,得到结果
MariaDB [hellodb]> select s.name ,c.course,sc.score <==显示字段名 from students s <==连接表students与scores,分别定义别名为s和sc inner join <==内连接关键词 scores sc on s.stuid=sc.stuid <==students与scores的关联字段 inner join <==内连接关键词,再次连接表courses ,定义别名为c courses c on sc.courseid=c.CourseID; <=第二次关联字段 +-------------+----------------+-------+ | name | course | score | <=输出结果
- 准备三张表格,结构如下所示
-
SELECT语句执行流程
7. 视图
-
什么是视图
视图是指计算机数据库中的视图,是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成 -
创建注意事项
- 表和视图共享数据库中相同的名称空间,因此,数据库不能包含具有相同名称的表和视图
- 视图属于数据库,在默认情况下,将在当前数据库创建新视图。要想在给定数据库中明确创建视图,创建时,应将名称指定为db_name.view_name
'在没有进入数据库时,创建属于hellodb数据库的视图' MariaDB [(none)]>CREATE VIEW hellodb.cocview AS SELECT * FROM hellodb.coc;
- 多表结构创建的视图,不可以进行修改操作
-
创建视图
- 语法格式
CREATE [ or replace ] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
- 参数说明
- or replace
如果有同名视图,则替换它'1. 创建一各表coc,结构如下所示' MariaDB [hellodb]> select * from coc; +----+---------+----------+ | ID | ClassID | CourseID | '2. 进入hellodb数据库' MariaDB [hellodb]> use hellodb Database changed '3. 把coc当基表,创建一个视图viewa,如果存在就替换掉' MariaDB [hellodb]> CREATE or replace VIEW viewa AS SELECT * FROM coc;
- (column_list)
需要显示的字段名,与查询内容数量相同,重新定义的字段名用逗号分隔,放在括号内方法1:'在view创建段中定义视图字段名' MariaDB [hellodb]> CREATE or replace VIEW viewa (vID,vCID,vcourse) AS SELECT * FROM coc; '查看更改后效果' MariaDB [hellodb]> select * from viewa; +-----+------+---------+ | vID | vCID | vcourse | +-----+------+---------+ 方法2:'在子查询语句中定义' MariaDB [hellodb]> CREATE or replace VIEW viewa AS SELECT id vid,ClassID vcid FROM coc;
- [WITH [CASCADED | LOCAL] CHECK OPTION]
mysql允许基于视图来创建另一个视图,而WITH CHECK OPTION
就是用来检测依赖视图中的规则是否保持一致性默认为CASCADED,5.7.6以后版本[CASCADED和 LOCAL区别不大。
- or replace
- 示例
在视图中进行修改操作,先创建一个有条件限制的视图,只需要ID小于20的行
增加一条符合条件的行17MariaDB [hellodb]> CREATE OR REPLACE VIEW viewa AS select * from coc WHERE ID<20;
试着增加不符合要求的记录,ID为21MariaDB [hellodb]> INSERT INTO viewa VALUES(17,6,10); 查看修改结果 MariaDB [hellodb]> select * from viewa; | 17 | 6 | 10 | +----+---------+----------+ 前面说过,视图是没有实际数据的,那么修改内容应该是在基表中的,查看基表 MariaDB [hellodb]> select * from coc; | 17 | 6 | 10 | +----+---------+----------+
基于上例中的viewa创建一个带有WITH CHECK OPTION的子句的视图viewbMariaDB [hellodb]> INSERT INTO viewa VALUES(21,6,10); Query OK, 1 row affected (0.01 sec) <==创建成功 查看修改结果 MariaDB [hellodb]> select * from viewa; | 17 | 6 | 10 | <==最后一条ID仍然为17,在视图中并没有增加ID为21的行 +----+---------+----------+ 查看基表 MariaDB [hellodb]> select * from coc; | 21 | 6 | 10 | <==成功添加ID为21的行 +----+---------+----------+ 以上示例说明,视图是数据是来源于基表的,创建的视图遵守子查询语句的限定条件
试着添加ID为22的行,viewa查询子句中定义了限定条件为>20,所以22是不符合要求的MariaDB [hellodb]> CREATE OR REPLACE VIEW viewb AS SELECT * FROM viewa WITH CHECK OPTION;
MariaDB [hellodb]> INSERT INTO viewb VALUES(22,6,10); ERROR 1369 (44000): CHECK OPTION failed `hellodb`.`viewb` <==报错,不能增加
- 语法格式
-
查看已创建视图内容
查看创建的视图内容,与查询表方法相同,如下所示MariaDB [hellodb]> SELECT * FROM viewa; +----+---------+----------+ | ID | ClassID | CourseID |
-
查看视图定义
SHOW CREATE VIEW view_name -
查看是否为视图,命令show table status from db_name,找到描述行
MariaDB [hellodb]> show table status from hellodb\G *************************** 9. row *************************** Name: view_score Comment: VIEW <==描述这里,标明是视图 9 rows in set (0.00 sec)
-
删除视图:
- 格式
DROP VIEW [IF EXISTS] view_name[, view_name]... [RESTRICT | CASCADE]
- 示例
MariaDB [hellodb]>DROP VIEW viewa;
- 格式
8. 函数
-
函数分类
- 系统函数
系统函数:https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html - 自定义函数(user-defined function UDF)
保存在mysql.proc表中
- 系统函数
-
创建自定义函数
- 创建UDF语法
CREATE FUNCTION function_name(parameter_nametype [...]) RETURNS {STRING|INTEGER|REAL} routine_body
- 参数说明
- function_name(函数名)
用在SQL声明中以备调用的函数名字,避免自定义函数与SQL函数名字一样,如果相同需要与随后的括号之间插入一个空格 - (parameter_nametype)
括号内的参数列,必须总是存在,即使没有参数,也要使用一个空参数列() - RETURNS
针对FUNCTION指定,用来指定函数的返回类型,而且函数体必须包含一个RETURN value语句 - routine_body
符合规定的SQL过程语句,可以使用复合语法(例如BEGIN…END),复合语句可以包含声明,循环和其他控制结构语句
- function_name(函数名)
- 说明:
- 参数可以有多个,也可以没有参数
- 必须有且只有一个返回值
- 函数在哪个库定义,就在哪个库使用,不能跨库使用
- 创建UDF语法
-
查看函数列表
SHOW FUNCTION STATUS; -
查看函数定义
SHOW CREATE FUNCTION function_name -
删除UDF:
DROP FUNCTION function_name -
调用自定义函数语法
函数是不能独立使用,必须通过别的命令调用
SELECT function_name(parameter_value,…) -
示例
- 示例:无参UDF
MariaDB [hellodb]> CREATE FUNCTION simpleFun() <==函数名及参数 RETURNS VARCHAR(20) <==定义返回值得类型 RETURN "Hello World!"; <==返回值
- 示例:修改输入定界符,命令
DELIMITER
MariaDB [(none)]> DELIMITER // <==定界符从 ;变为//,中间有空格 MariaDB [(none)]> SHOW DATABASES; <==输入命令后,用;结尾,命令没有结束 -> // <==输入//,命令执行
- 示例:有参数UDF,定义函数体时会用到[;],所以要先将定界符暂时更改为其他符号
MariaDB [hellodb]> DELIMITER // <==声明定界符为// MariaDB [hellodb]> CREATE FUNCTION <==自定义函数 -> deleteById(uid SMALLINT UNSIGNED) <==函数名及参数,uid为参数名,后续为类型 -> RETURNS VARCHAR(20) <==定义返回结果类型 -> BEGIN <==符合语法与END成对 -> DELETE FROM students WHERE stuid= uid; <==SQL过程语句 '函数体,删除传递参数uid对应的学员' -> RETURN (SELECT COUNT(stuid) FROM students); <==RETURN返回值 '返回值,删除后整个表还有多少条记录' -> END// MariaDB [hellodb]> DELIMITER ; <==将定界符还原
- 示例:无参UDF
-
自定义函数中定义局部变量
-
语法格式
DECLARE 变量1[,变量2,… ]变量类型 [DEFAULT 默认值] -
为变量赋值语法
- SET parameter_name= value[,parameter_name= value…]
- SELECT INTO parameter_name
-
说明
局部变量的作用范围是在BEGIN…END程序中,而且定义局部变量语句必须在BEGIN…END的第一行定义 -
示例
示例1:赋值语法1MariaDB [hellodb]> DELIMITER // MariaDB [hellodb]> CREATE FUNCTION add(x SMALLINT UNSIGNED, Y SMALLINT UNSIGNED) -> RETURNS SMALLINT -> BEGIN -> DECLARE a, b SMALLINT UNSIGNED; <==定义局部变量a,b为正整数 -> SET a = x, b = y; <==变量赋值 -> RETURN a+b; -> END// MariaDB [hellodb]> DELIMITER ; <==将定界符还原
示例2:赋值语法2
DECLARE x int; SELECT COUNT(id) FROM tdb_name INTO x; <==赋值语句,查询表tdb_name统计数,赋值给x RETURN x; END//
-
-
错误提示解决办法
- 错误提示
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary
- 错误原因
mysql的设置默认是不允许创建函数 - 解决办法
- mysql中执行命令,重启失效
SET GLOBAL log_bin_trust_function_creators = 1;
- 修改配置文件my.cnf,需要重启服务
增加log-bin-trust-function-creators=1
,
- mysql中执行命令,重启失效
- 错误提示