Markdown database notebook
1.1. Mysql知识/基础
1.1.1. Msyql的基本知识
- 定义:Mysql是一种关系型数据库管理系统使用SQL语句进行管理;
- 历史
- 1979年,由瑞典MySQL AB 公司开发;
- 1996年,MySQL 1.0发布;
- MySQL关系型数据库于1998年1月发行第一个版本;
- 2008年1月16号,MySQL被Sun公司收购;
- 2009年4月,Sun公司被Oracle整体收购;
- 特点:
- 多线程服务,可以支持多CPU的体系结构
- 普适
- 使用C和C++编写,可移植性强;
- 不同平台均可运行;
- 多操作系统运行;
- 多语言嵌入,既可以完全嵌入,又可以调用;
- 多文字语言支持;
- 性能
- 优化的SQL保证速度;
- 管理能力强;
- 多种管理工具;
- 多种连接方式;
- 多版本差异
- 社区版本;
- 企业版本;
- 集群版本(多个server集合);
- 高级集群版本;
- Mysql workbench是数据库建模工具;
1.2. Mysql知识/深入
1.2.1. Mysql的储存引擎
不同的储存引擎提供不同的存储机制,索引技巧,锁定水平等功能;
SHOW ENGINES;语句查看系统支持的引擎类型
-
InnDB引擎
事务性数据库的首选引擎,支持事务ACID,支持行锁定和外键,MYSQL5.5.5之后的默认储存引擎;
不创建目录,创建一个ibdata1的自动拓展数据文件,以及ib_logfile的两个日志文件; -
MyISAM引擎
有较高的插入、查询速度,不支持事务;
MyISAM产生3个文件,拓展名为frm的文件存储表定义,拓展名是MYD的是数据文件,拓展名是MYI的是索引文件; -
Memory引擎
表中的数据存储到内存,可以提供快速访问。
1.2.2. MySQL的系统自带库
-
information_schema
提供了访问数据库元数据的方式,保存着关于MySQL所维护的所有其他数据库的信息.元数据包括:数据库名或表名,列的数据类型,或访问权限等- TABLES:提供了关于数据库中的表的信息(包括视图);
- COLUMNS:提供了表中的列信息;
- STATISTICS:提供了关于表索引的信息;
- USER_PRIVILEGES(用户权限):给出了关于全程权限的信息,该信息源自mysql.user授权表;
- SCHEMA_PRIVILEGES(方案权限):给出了关于方案(数据库)权限的信息,该信息来自mysql.db授权表;
- TABLE_PRIVILEGES(表权限):给出了关于表权限的信息,该信息源自mysql.tables_priv授权表;
- COLUMN_PRIVILEGES(列权限):给出了关于列权限的信息,该信息源自mysql.columns_priv授权表;
- CHARACTER_SETS(字符集):提供了mysql实例可用字符集的信息;
- TABLE_CONSTRAINTS:描述了存在约束的表,以及表的约束类型;
- KEY_COLUMN_USAGE:描述了具有约束的键列
- ROUTINES:提供了关于存储子程序(存储程序和函数)的信息;
- VIEWS:给出了关于数据库中的视图的信息;
- TRIGGERS:提供了关于触发程序的信息;
-
mysql
核心数据库,类似于sqlserver中的master表,主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息。例如在mysql.user表中修改root用户的密码 -
performance_schema
用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE_SCHEMA,而用户不能创建存储引擎为PERFORMANCE_SCHEMA的表
MySQL5.5默认是关闭的,需要手动开启,在配置文件里添加:[mysqld] performance_schema=ON
从MySQL5.6开始,默认打开
-
sys/test(5.6)
Sys库所有的数据源来自:performance_schema。目标是把performance_schema的复杂度降低,让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况。- host :以IP分组相关的统计信息
- innodb: innodbbuffer 相关信息
- io: 数据内不同维度展的IO相关的信息
- memory : 以IP,连接,用户,分配的类型分组及总的占用显示内存的使用
- metrics : DB的内部的统计值
1.3. Mysql实践/基础
1.3.1. MySQL数据库的安装和运行(实践)
- 下载安装
- 配置运行
- 数据库试运行
1.3.2. 数据库安装运行(实践)
- 常用工具
- 图形化客户端:Mysql Administractor、Navicat等等;
- 基于web的管理工具;
1.3.3. 创建和删除数据库
- 查看MySQL已有的数据库
mysql>show database;
-
创建数据库
msyql>create database test_db;
-
修改数据库
mysql>alter database xscj default character set GB2312 default collate GB2312_CHINESE_CI;
-
删除数据库
mysql>drop database test_db;
1.3.4. 语言简介与编程
-
语言简介
SQL语言一般由如下几部分组成-
(1)数据定义语言(DDL)
用于执行数据库的任务,对数据库及数据库中的各种对
象进行创建、删除、修改等操作。如前所述,数据库对
象主要包括:表、默认约束、规则、视图、触发器、存
储过程等。DDL包括的主要语句及功能如表所示。
-
(2)数据操纵语言(DML)
用于操纵数据库中各种对象,检索和修改数据。DML
包括的主要语句及功能如表所示。
-
(3)数据控制语言(DCL)
用于安全管理,确定哪些用户可以查看或修改数据库中的数据,DCL包
括的主要语句及功能如表6.3所示。
-
(4)MySQL增加的语言元素
这部分不是SQL标准所包含的内容,而是为
了用户编程的方便增加的语言元素。这些语
言元素包括常量、变量、运算符、函数、流
程控制语句和注解等。 -
(5)有关MySQL数据库语言的几点说明如下:
- MySQL语句以分号结束,并且SQL处理器忽略空格、制表符和回车符。
- 箭头(->)代表MySQL语句没有输入完。
- 取消MySQL语句使用(\C)。
- Windows下MySQL语句关键字和函数名不区分大小写,但Linux区分。
- 使用函数时,函数名与其后的括号之间不能有空格
-
-
MySQL中常量、变量、运算符、函数
- 数据类型
- 数值数据类型
为了保存数值数据,MySQL 提供以下数值数据类型- 整数类型:保存整个数字
TINYINT、SMALLINT、MEDIUMINT、INT 或INTEGER、BIGINT; - 浮点类型:保存近似数值数据
FLOAT 与DOUBLE。它们都被用于表示近似数值数据,有整数部分、
小数部分。缺省值是NULL,如果列不能为NULL,其缺省值是0; - 定点类型:保存精确数值
DECIMAL(P,S)
例如:cost DECIMAL(10,2)
在MySQL 中,NUMERIC 数据类型与DECIMAL 是相同的类型位; - BIT类型:保存位字段数值
BIT 列指定了每个值所需要的位数,从1 到64 位。
例如:bit_col1 BIT(4) 四位二进制, b’1111’ 等于15;
- 整数类型:保存整个数字
- 字符串数据类型
- CHAR 固定长度字符串CHAR(N), N 定义字符型数据的长度,N在1-255之间
- VARCHAR 可变长度字符串
- VARCHAR(N), N 可以指定为0-65535之间的值,但这里N表示的是字符串可达到的最大长度。
- 二进制数据类型
- BINARY VARBINARY二进制数据类型
- BLOB 一个可变长度的非结构化的二进制数据集合。通常地,BLOB 是图像、语音或其它多媒体对象。BLOB 术语是二进制大对象(Binary Large Object)的字首缩写。TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
- 日期和时间数据类型
- TIME类型 用于保存时间部分的信息
- YEAR类型 用两位数字或四位数字格式表示年,缺省的是四位数字格式
- DATE类型 用于保存完整的日历日期
- DATETIME类型 保存日期与时间信息
- TIMESTAMP类型 与DATETIME 一样保存日期与时间
- 数值数据类型
- 常量
-
字符串常量
hello’ ‘How are you!’ N‘hello’ N‘How are you!’ > Unicode 数据中的每个字符用两个字节存储,而每个ASCII字 符用一个字节存储。
-
- 数据类型
- 数值常量
数值常量可以分为整数常量和浮点数常量。
- 整数常量即不带小数点的十进制数,例如:1894,2,+145345234,–2147483648。
- 浮点数常量是使用小数点的数值常量,例如:5.26,−1.39,101.5E5,0.5E−2。
- 十六进制常量
- 十六进制数值不区分大小写,其前缀“X”或“x”可以被“0x”取代而且不用引号。即X’41’可以替换为0x41,注意:“0x”中x一定要小写。
- 十六进制值的默认类型是字符串。如果想要确保该值作为数字处理,可以使用CAST(…AS UNSIGNED)。
- 如果要将一个字符串或数字转换为十六进制格式的字符串,可以用HEX()函数。
- 日期时间常量
- 日期时间常量:用单引号将表示日期时间的字符串括起来构成。日期型常量包括年、月、日,数据类型为DATE,表示为“2018-06-17”这样的值。时间型常量包括小时数、分钟数、秒数及微秒数,数据类型为TIME,表示为“12:30:43.00013”这样的值。MySQL 还支持日期/时间的组合,数据类型为DATETIME或TIMESTAMP,如“2018-06-17 12:30:43”。
- 需要特别注意的是,MySQL 是按年-月-日的顺序表示日期的。中间的间隔符“-”也可以使用如“\”、“@”或“%”等特殊符号。
- 位字段值
- 布尔值
- 布尔值只包含两个可能的值:TRUE和FALSE。FALSE的数字值为“0”,TRUE的数字值为“1”。
- NULL值
- NULL 的含义
NULL通常用来表示“没有值”、“无数据”等意义,并且不同于数字类型的“0”或字符串类型的空字符串。
- 何时使用NULL
在数据库设计的开始阶段,如果某列中有些数据是不可用的,这时需要详查并决定是否允许NULL值。同时,对已有的表,如果发现问题是由于列中出现了NULL值,可以修改表的定义以允许NULL值。
- 变量
- 用户变量
```sql
//表达式可以是多样的。
SET @变量名1=表达式1 [, 变量名2=表达式2 , … ]
select @t2:=(@t2:=2)+5 as t2;
set @user1=1, @user2=2, @user3=3;
set @user4=@user3+1;
select @name;
use teach
set @student=(select 姓名from xswhere 学号='081101');
```
定义和初始化一个变量可以使用SET语句,其中,变量名可以由当前字符集的文字数字字符、“.”、“_”和“$”组成。当变量名中需要包含了一些特殊符号(如空格、#等)时,可以使用双引号或单引号将整个变量括起来。表达式为要给变量赋的值,可以是常量、变量或它们通过运算符组成的式子。
注: 在SELECT语句中,表达式发送到客户端后才进行计算。这说明在HAVING、GROUP BY或ORDER BY子句中,不能使用包含SELECT列表中所设的变量的表达式。
- 系统变量
- 初始化
```sql
select @@version ;
select CURRENT_TIME;
```
大多数的系统变量应用于其他SQL语句中时,必须在名称前加两个@符号,而为了与其他SQL产品保持一致,某些特定的系统变量是要省略这两个@符号的。如CURRENT_DATE(系统日期)、CURRENT_TIME(系统时间)、CURRENT_TIMESTAMP(系统日期和时间)和CURRENT_USER(SQL用户的名字)。
```sql
SET 系统变量名= 表达式
| [GLOBAL | SESSION] 系统变量名=表达式
| @@ [global.| session.] 系统变量名=表达式
```
在MySQL中,有些系统变量的值是不可以改变的,例如VERSION和系统日期。而有些系统变量是可以通过SET语句来修改的,例如SQL_WARNINGS。
- 全局系统变量
```sql
set @@global.sort_buffer_size=25000;
```
当MySQL启动的时候,全局系统变量就初始化了,并且应用于每个启动的会话。如果使用GLOBAL(要求SUPER权限)来设置系统变量,则该值被记住,并被用于新的连接,直到服务器重新启动为止。
- 会话系统变量
```sql
set @@SQL_WARNINGS =ON;
set @@SESSION.SQL_SELECT_LIMIT=10;
select @@LOCAL.SQL_SELECT_LIMIT;
```
会话系统变量只适用于当前的会话。大多数会话系统变量的名字和全局系统变量的名字相同。当启动会话的时候,每个会话系统变量都和同名的全局系统变量的值相同。一个会话系统变量的值是可以改变的,但是这个新的值仅适用于正在运行的会话,不适用于
所有其他会话。
说明:在这个例子中,关键字SESSION放在系统变量的名字前面(SESSION和LOCAL可以通用)。这明确地表示会话系统变量SQL_SELECT_LIMIT和SET语句指定的值保持一致。
- 其它
- 默认值
```sql
set @@LOCAL.SQL_SELECT_LIMIT=DEFAULT;
```
- 系统变量清单
```sql
SHOW GLOBAL VARIABLES
SHOW SESSION VARIABLES
//如果不加关键字就默认为SHOW SESSION VARIABLES。
show variables like 'max_join_size';
show global variables like 'max_join_size';
show variables like 'character%';
```
- 运算符
- 算术运算符
- 普通算术
+(加)、−(减)、*(乘)、/(除)和%(求模)
- 不同类型的运算
其中,+(加)和-(减)运算符还可用于对日期时间值(如DATETIME)进行算术运算。例如:
```sql
select '2014-01-20'+ INTERVAL 22 DAY;
```
在运算过程中,用字符串表示的数字可以自动地转换为数值。当执行转换时,如果字符串的第一位是数字,那么它被转换为这个数字的值,否则,它被转换为零。例如:
```sql
select '80AA'+'1', 'AA80'+1, '10x' * 2 * 'qwe';
```
- 比较运算符
关系运算符(>,<,=,!= <>),运算结果为逻辑值,可以为三种之一:1(真)、0(假)
及NULL(不能确定)。
注: 关系运算符中=是等于。
```sql
select 3.14=3.142,5.12=5.120, 'a'='A','A'='B','apple'='banana';
// 0 1 1 0 0
// 因为在默认情况下MySQL以不区分大小写的方式比较字符串,所以表达式'a'='A'的结果为真。如果想执行区分大小写的比较,可以添加BINARY关键字,这意味着对字符串以二进制方式处理。当在字符串上执行比较运算时,MySQL将区分字符串的大小写。
select 'Apple'='apple' , BINARY 'Apple'='apple';
// 大小写拓展
// 1、linux下mysql安装完后是默认:区分表名的大小写,不区分列名的大小写;
// 2、用root帐号登录后,在/etc/my.cnf 中的[mysqld]后添加添加lower_case_table_names=1,重启MYSQL服务,这时已设置成功:不区分表名的大小写;
// lower_case_table_names参数详解:
// lower_case_table_names = 0
// 其中 0:区分大小写,1:不区分大小写
// MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的:
// 1、数据库名与表名是严格区分大小写的;
// 2、表的别名是严格区分大小写的;
// 3、列名与列的别名在所有的情况下均是忽略大小写的;
// 4、变量名也是严格区分大小写的;
// MySQL在Windows下都不区分大小写。
// 3、如果想在查询时区分字段值的大小写,则:字段值需要设置BINARY属性,设置的方法有多种:
// A、创建时设置:
// CREATE TABLE T(
// A VARCHAR(10) BINARY
// );
// B、使用alter修改:
// ALTER TABLE `tablename` MODIFY COLUMN `cloname` VARCHAR(45) //BARY;
// C、mysql table editor中直接勾选BINARY项。
// 与“=”运算符相对立的是“<>”运算符,它用来检测表达式的两边是否不相等,如果不相等则返回真值,相等则返回假值。
select 5<>5,5<>6,'a'<>'a','5a'<>'5b';
// 0 1 0 1
select NULL<>NULL, 0<>NULL, 0<>0;
// Null Null 0
```
- 逻辑运算符
对某个条件进行测试,运算结果为TRUE(1)或FALSE(0)
NOT(!) 逻辑非OR(||)逻辑或AND(&&)逻辑与XOR 逻辑异或
- 位运算符
两个表达式之间执行二进制位操作,类型可为整型或与整型兼容的数据类型(如字符型,但不能为image类型)
如:& (位AND) | (位OR) ^ (位XOR) ~ (位取反) >> (位右移)<< (位左移)
![运算符优先级](database2.5.PNG)
- 常用函数
- 数学函数
MySQL支持很多的数学函数。若发生错误,所有的数学函数都会返回NULL。
- 1.GREATEST()和LEAST()函数
获得一组数中的最大值和最小值。
- 2.FLOOR()和CEILING()函数
FLOOR()用于获得小于一个数的最大整数值,CEILING()函数用于获得大于一个数的最小整数值
- 3.ROUND()和TRUNCATE()函数
ROUND()函数用于获得一个数的四舍五入的整数值
TRUNCATE()函数用于把一个数字截取为一个指定小数个
数的数字,逗号后面的数字表示指定小数的个数
例如:TRUNCATE(1.54578, 2) = 1.54
- 4.其它
```sql
1.ABS()获得一个数的绝对值
2.SIGN()返回数字的符号,结果是正数(1)、负数(−1)或者零(0)
3.
SQRT()返回一个数的平方根
4.
POW() 一个数作为另外一个数的指数,并返回结果
5.
SIN() COS() TAN() 返回一个角度(弧度)的正弦、余弦和正切值
6.
ASIN() ACOS() ATAN()
返回一个角度(弧度)的反正弦、反余弦和反正切值
7.
BIN() OTC() HEX()
以字符串形式返回一个数的二进制、八进制
和十六进制值
```
- 字符串函数
```sql
1.ASCII(‘A’) 65返回字符表达式最左端字符的ASCII值
2.CHAR (x1,x2,x3,…) x1、x2……的ASCII码转换为字符,结果组合成一个字符串。参数x1,x2,x3……为介于0~255之间的整数
3.LEFT( str ,x ) RIGHT( str ,x )
分别返回从字符串str左边和右边开始指定x个字符
4.TRIM | LTRIM | RTRIM(str)
使用LTRIM和RTRIM分别删除字符串中前面的空格和尾部的空格,返回值为字符串, TRIM删除字符串首部和尾部的所有空格。
5.REPLACE (str1 , str2 , str3 )
用于用字符串str3替换str1中所有出现的字符串str2。最后返回替换后的字符串。
6.CONCAT(s1,s2,…sn) 用于连接指定的几个字符串
例如:SELECT CONCAT('中国', '人民银行');
返回:中国人民银行
7.SUBSTRING (expression , Start, Length )
返回expression中指定的部分数据。参数expression可为字符串、二进制串、text、image字段或表达式。Start、Length均为整型,前者指定子串的开始位置,后者指定子串的长度(要返回字节数)
8.STRCMP(s1,s2)
用于比较两个字符串,相等返回0,s1大于s2返回1,s1小于s2返回−1
```
- 日期时间函数
```sql
1.NOW()
NOW()函数可以获得当前的日期和时间,它以YYYY-MM-DD HH∶MM∶SS 的格式返回当前的日期和时间
2.YEAR ( ) MONTH() DAYOFYEAR() WEEK() HOUR() MINUT()SECOND()
年月日周时分秒
3.DATE_ADD()和DATE_SUB()
可以对日期和时间进行算术操作,它们分别用来增加和减少日期值
//例程
select DATE_SUB('2014-08-20 10:25:35', INTERVAL 20 MINUTE);
```
![日期照片](databases2.6.PNG)
- 格式化函数
```sql
1. FORMAT()函数
FORMAT(x, y)
select FORMAT(11111111111.23654,2), FORMAT(-5468,4);
2. DATE_FORMAT()和TIME_FORMAT()函数
DATE_FORMAT/ TIME_FORMAT(date | time, fmt)
select DATE_FORMAT(NOW(), '%W,%d,%M, %Y %r');
![日期格式化](database2.7.png)
3. INET_NTOA()和INET_ATON()函数
select INET_ATON('192.168.1.1');
```
- FORMAT()函数
FORMAT()函数把数值格式化为以逗号间隔的数字序列。FORMAT()的第一个参数x是被格式化的数据,第二个参数y是结果的小数位数。
- DATE_FORMAT()和TIME_FORMAT()函数
DATE_FORMAT()和TIME_FORMAT()函数可以用来格式化日期和时间值。其中,date和time是需要格式化的日期和时间值,fmt是日期和时间值格式化的形式,下表列出了MySQL中的日期/时间格式化代码。
- INET_NTOA()和INET_ATON()函数
MySQL中的INET_NTOA()和INET_ATON()函数可以分别把IP地址转换为数字或者进行相反的操作。
- 加密函数
```sql
AES_ENCRYPT | AES_DECRYPT(str,key)
ENCODE | DECODE(str,key)
select PASSWORD('MySQL');
```
- (1)AES_ENCRYPT和AES_DECRYPT函数
AES_ENCRYPT函数返回的是密钥key对字符串str利用高级加密标准(AES)算法加密后的结果,结果是一个二进制的字符串,以BLOB类型存储。而AES_DECRYPT函数用于对用高级加密方法加密的数据进行解密。若检测到无效数据或不正确的填充,函数会返回NULL。AES_ENCRYPT和AES_DECRYPT函数可以被看作MySQL中普遍使用的最安全的加密函数。
- (2)ENCODE和DECODE
ENCODE函数用来对一个字符串str进行加密,返回的结果是一个二进制字符串,以BLOB类型存储。DECODE函数使用正确的密钥对加密后的值进行解密。
- (3)ENCRYPT
使用UNIX crypt()系统加密字符串,ENCRYPT(str,salt)函数接收要加密的字符串和用于加密过程的salt(一个可以确定唯一口令的字符串)。在Windows上不可用。
- (4)PASSWORD
返回字符串str加密后的密码字符串,适合于插入到MySQL的安全系统。该加密过程不可逆,和UNIX密码加密过程使用不同的算法。主要用于MySQL的认证系统。
- 系统信息函数
```sql
select DATABASE(),USER(), VERSION();
select BENCHMARK(10000000, ENCODE('hello','goodbye'));
select * from xs;
select FOUND_ROWS();
select SQL_CALC_FOUND_ROWS * from xswhere 性别=1 limit 5;
select FOUND_ROWS();
//这个貌似是一个有利有弊的语句,使用与否要看具体情景。
```
- (1)DATABASE()、USER()和VERSION()
函数可以分别返回当前所选数据库、当前用户和MySQL版本信息:
- (2)BENCHMARK()
函数用于重复执行n次表达式expr。它可以被用于计算MySQL处理表达式的速度,结果值通常为零。另一种用处来自MySQL客户端内部,能够报告问询执行的次数,根据经过的时间值可以推断服务器的性能。
- (3)FOUND_ROWS()
函数用于返回最后一个SELECT语句返回的记录行的数目。
SELECT语句可能包括一个LIMIT子句,用来限制服务器返回客户端的行数。在有些情况下,需要不用再次运行该语句而得知在没有LIMIT 时到底该语句返回了多少行。为了知道这个行数,包括在SELECT语句中选择SQL_CALC_FOUND_ROWS,随后调用FOUND_ROWS()
- 类型转换函数
- 基本类型
MySQL提供CAST()函数进行数据类型转换,它可以把一个值转换为指定的数据类型。
expr是CAST函数要转换的值,type是转换后的数据类型。
在CAST函数中MySQL支持这几种数据类型:BINARY、CHAR、DATE、TIME、DATETIME、SIGNED和UNSIGNED。
通常情况下,当使用数值操作时,字符串会自动地转换为数字,因此下面例子中两种操作得到相同的结果:
```sql
CAST(expr, AS type)
select 1+'99', 1+CAST('99' AS SIGNED);
```
- BINARY类型
字符串可以指定为BINARY类型,这样它们的比较操作就成为大小写敏感的。使用CAST()函数指定一个字符串为BINARY和字符串前面使用BINARY关键词具有相同的作用。
```sql
select 'a'=BINARY 'A', 'a'=CAST('A' AS BINARY);
// 0 0
select CAST(CURDATE() AS SIGNED);
// 20190514
```
-
SQL语句创建数据库、表、视图和索引
- 创建数据库(回见1.3.3)
- 创建表
- 创建视图
- 创建表
- 展示记录
-
SHOW
- SHOW tables或SHOW tables from database_name:显示当前数据库中所有表的名称。
- SHOW databases:显示MySQL中所有数据库的名称。
- SHOW columns from table_namefrom database_name或SHOW columns from
- SHOW status:显示一些系统特定资源的信息,例如,正在运行的线程数量。
- SHOW index from table_name:显示表的索引。
- database_name.table_name:显示表中列的名称。
- SHOW variables:显示系统变量的名称和值。
- SHOW processlist:显示系统中正在运行的所有进程,也就是当前正在执行的查询。大多数用户可以查看他们自己的进程,但是如果他们拥有process权限,就可以查看所有人的进程,包括密码。
- SHOW table status:显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间。
- SHOW privileges:显示服务器所支持的不同权限。
- SHOW create database database_name:显示创建某一个数据库的CREATE DATABASE语句。
- SHOW create table table_name:显示创建一个表的CREATE TABLE语句。
- SHOW warnings:显示最后一个执行的语句所产生的错误、警告和通知。
- SHOW [storage] engines:显示安装后的可用存储引擎和默认引擎。
- SHOW procedure status:显示数据库中所有存储过程基本信息,包括所属数据库、存储过程名称、创建时间等。
- SHOW create procedure sp_name:显示某一个存储过程的详细信息。
- SHOW events:显示所有事件的列表。
- SHOW innodbstatus:显示innoDB存储引擎的状态。
- SHOW logs:显示BDB存储引擎的日志。
- SHOW errors:只显示最后一个执行语句所产生的错误。
-
DESCRIBE
{DESCRIBE | DESC} 表名[列名| wild ] SHOW columns from // 例子 use teach; describe xs; desc xs学号;
DESC是DESCRIBE的简写,二者用法相同。
列名:除了列名,也可包含‘%’和‘_’通配符的字符串,用于获得对于带有与字符串相匹配的名称的各列的输出。
-
-
SQL语句对数据库表进行插入、修改和删除数据操作
-
插入记录
-
插入新记录
use teach; insert into xs values('081101', '王林', '计算机', 1, '1994-02-10',50, null, null); insert into xs(学号, 姓名, 性别, 出生日期, 总学分)values('081101', '王林', 1, '1994-02-10', 50); insert into xs values('081101', '王林', default, 1, '1994-02-10', 50, null, null); insert into xs set 学号='081101', 姓名='王林', 专业=default, 性别=1, 出生日期='1994-02-10', 总学分=50; insert into xs values('081211', '刘华', '通信工程', 1, '1995-03-08', 48, null, null);
-
插入图片
081102,程明,计算机,1,1995-02-01,50,picture.jpg,NULL insert into xs values('081102', '程明', '计算机', 1, '1995-02-01', 50, ' D:\image\picture.jpg', null); select * from student; insert into xs values('081102', '程明', '计算机', 1, '1995-02-01', 50, load_file(' D:\image\picture.jpg'), null);
-
用已有表记录插入当前表记录
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] 表名[( 列名,...)] SELECT ... [ ON DUPLICATE KEY UPDATE 列名=表达式, ... ] //说明:SELECT语句中返回的是一个查询到的结果集,INSERT语句将这个结果集插入到指定表中,但结果集中每行数据的字段数、字段的数据类型要与被操作的表完全一致。 use teach; drop table if exists xs1; create table xs1 like xs; insert into xs1 select * from xs; select * from xs1; use teach; drop table if exists xs1; create table xs1 as (select * from xs); select * from xs1;
-
替换旧记录
replace into xs values('081211', '刘华', '通信工程', 1, '1995-03-08', 48, null, null);
REPLACE语句与INSERT语句基本相同。如果存在相同的记录,REPLACE语句可以在插入数据之前将与新记录冲突的旧记录删除,从而使新记录能够替换旧记录,正常插入。
-
-
修改记录
-
修改单个表
UPDATE [LOW_PRIORITY] [IGNORE] 表名 SET 列名1=表达式1 [, 列名2=表达式2 ...] [WHERE 条件] [ORDER BY ...] [LIMIT 行数] //例程 update xs set 总学分= 总学分+ 1; update xs set 学号= '081250' , 备注= '辅修计算机专业' where 姓名= '刘华'; select 学号, 姓名, 总学分, 备注from xs;
- SET子句:
根据WHERE子句中指定的条件对符合条件的数据行进行修改。若语句中不设定WHERE子句,则更新所有行。 - LIMIT子句:
指定被修改的行的最大值。 - ORDER BY子句:
指定修改记录行的顺序,此子句只在与LIMIT联用时才起作用。
- SET子句:
-
修改多个表
UPDATE [LOW_PRIORITY] [IGNORE] 表名1 [, 表名2 ...] SET列名1=表达式1 [, 列名2=表达式2 ...] [WHERE 条件] update xs,xs1 set xs.总学分= xs.总学分+4, xs1.总学分= xs1.总学分+4 where xs.学号= xs1.学号; select * from xs; select * from xs1;
-
-
删除记录
-
删除满足条件的行
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM 表名 [WHERE 条件] [ORDER BY ...] [LIMIT 行数] //例子 delete from xs1 where 姓名= '刘华';
- QUICK修饰符:可以加快部分种类的删除操作的速度。
- FROM子句:要删除数据的表名。
- WHERE子句:指定的删除条件。如果省略WHERE子句则删除该表的所有行。
- ORDER BY子句:指定删除的顺序,此子句只在与LIMIT联用时才起作用。
- LIMIT子句:指定被删除的行的最大值。
-
从多个表中删除行
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] 表名[.*] [, 表名[.*] ...] FROM 表名1 [, 表名2 ...] [WHERE 条件] DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM 表名[.*] [, 表名[.*] ...] USING 表名1 [, 表名2 ...] [WHERE 条件] //例子 use teach; DELETE xs, xs1 FROM xs,xs1 WHERE xs.学号=xs1.学号; select * from xs; DELETE FROM xs, xs1 USING xs, xs1 WHERE xs.学号=xs1.学号; select * from xs;
说明:对于第一种语法,只删除列于FROM子句之前的表中对应的行;对于第二种语法,只删除列于FROM子句之中(在USING子句之前)的表中对应的行。作用是,可以同时删除多个表中的行,并使用其他的表进行搜索。
-
清除表数据
TRUNCATE TABLE 表名
TRUNCATE TABLE在功能上与不带WHERE子句的DELETE语句(如DELETE FROM XS)相同,二者均删除表中的全部行。但TRUNCATE TABLE比DELETE速度快,且使用的系统和事务日志资源少。
DELETE 语句每次删除一行,并在事务日志中为所删除的每行记录一项。而TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。使用TRUNCATE TABLE,AUTO_INCREMENT计数器被重新设置为该列的初始值。
-
-