数据库学习笔记(mysql+sql server)数据库操作查看所有数据库 show databases；使用（进入

sql语法

单行注释 --

多行注释/* */

打印消息 print

数据类型

时间类型

字符串类型

数字类型

函数

聚合函数

AVG(表达式) 返回表达式中所有的平均值。仅用于数字列并自动忽略NULL值。
COUNT(表达式) 返回表达式中非NULL值的数量。可用于数字和字符列。
COUNT(*) 返回表中的行数(包括有NULL值的列)。
MAX(表达式) 返回表达式中的最大值,忽略NULL值。可用于数字、字符和日期时间列。
MIN(表达式) 返回表达式中的最小值,忽略NULL值。可用于数字、字符和日期时间列。
SUM(表达式) 返回表达式中所有的总和,忽略NULL值。仅用于数字列。

转化函数

CONVERT(data_type[(length)], expression [, style]) 例: Select convert(varchar(10) ,stuno) as stuno,stuname from student
CAST( expression AS data_type )例: Select cast(stuno as varchar(10)) as stuno,stuname from student
Cast和Convert的区别：Cast 和Convert都是用来将一种数据类型的表达式转换为另一种数据类型的表达式。CAST 和 CONVERT 提供相似的功能，只是语法不同。在时间转化中一般用到convert,因为它比cast多加了一个style,可以转化成不同时间的格式。

日期函数

GETDATE() 当前的系统日期。
DATEADD(日期部分,number,date) 返回带有指定数字(number)的日期(date),该数字添加到指定的日期部分(datepart)
DATEDIFF(日期部分,date1,date2) 返回两个日期中指定的日期部分之间的差值。
DATENAME(日期部分,date) 返回日期中日期部分的字符串形式。注：DATENAME 和 DATEPART 的区别，返回的值类型不同，一个是VARCHAR一个是INT，另外就是星期会用本地语言来表示
DATEPART(日期部分,date) 返回日期中指定的日期部分的整数形式。
YEAR(date) 返回指定日期的年份数值
MONTH(date)返回指定日期的月份数值
DAY(date)返回指定日期的天数值

数字函数

ABS(num_expr) 返回数值表达式的绝对值。
ACOS(float_expr) 返回角(以弧度表示),它的余弦值近似于指定的浮点表达式。
ASIN(float_expr) 返回角(以弧度表示),它的正弦值近似于指定的浮点表达式。
ATAN(float_expr) 返回角(以弧度表示),它的正切值近似于指定的浮点表达式。
ATN2(float_expr1, float_expr2) 返回角(以弧度表示),它的正切值在两个近似的浮点表达式之间。
CEILING(num_expr) 返回大于或等于数值表达式的最小整数。
COS(float_expr) 返回以浮点表达式表示的近似于指定角度(以弧度表示)的余弦三角函数的值。--- COT(float_expr) 返回以浮点表达式表示的近似于指定角度(以弧度表示)的余切三角函数的值。- - DEGREES(num_expr)返回数值表达式表示的弧度值对应的度值。
EXP(float_expr) 根据指定的近似浮点表达式,返回指数值。
FLOOR(num_expr) 返回小于或等于数值表达式的最大整数
LOG(float_expr) 根据指定的近似浮点表达式,返回自然对数值。
LOG10(float_expr) 根据指定的近似浮点表达式,返回以为底的对数。
PI() 返回常量值.141592653589793POWER(num_expr,y) 返回幂为y的数值表达式的值。
RADIANS(num_expr) 返回数值表达式表示的度值对应的弧度值。
RAND([seed]) 随机返回的到之间的近似浮点值,可以对seed指定为整数表达式(可选)。
ROUND(num_expr,length) 对数值表达式截取指定的整数长度,返回四舍五入后的值。
SIGN(num_expr) 对正数执行+1操作,对负数和零执行-1操作。
SIN(float_expr) 返回以浮点表达式表示的近似于指定角度(以弧度表示)的正弦三角函数的值。- - SQUARE(float_expr) 返回浮点表达式的平均值。
SQRT(float_expr) 返回指定的近似浮点表达式的平方根。
TAN(float_expr) 返回以浮点表达式表示的近似于指定角度(以弧度表示)的正切三角函数的值。

字符串函数

Expr1+expr2 返回两个表达式的组合形式的字符串。
ASCII(char_expr) 返回表达式最左边字符的ASCⅡ代码值。
CHAR(int_expr) 返回到之间的整数表达式的ASCⅡ字符值。如果输入的值不在有效范围内,则返回NULL。
CHARINDEX('pattern',char_expr) 返回字符表达式中指定模式的起始位置。
DIFFERENCE(char_expr1,char_expr2) 根据比较两个字符表达式的相似度,返回到之间的值。表示匹配度最佳。
LEN(char_expr) 返回字符表达式的长度。
LOWER(char_expr) 将字符表达式全部转换为小写。
LTRIM(char_expr) 返回删除掉前面空格的字符表达式。
PATINDEX('%pattern%',expr) 返回表达式中模式第一次出现的起始位置。返回表示不存在模式形式。
REPLICATE(char_expr,int_expr) 返回重复指定次数的字符表达式产生的字符串。
REVERSE(char_expr) 反转字符表达式。
RIGHT(char_expr,int_expr) 返回从字符表达式最右端起根据指定的字符个数得到的字符。
RTRIM(char_expr) 返回删除掉其后空格的字符表达式。
SOUNDEX(char_expr) 评估两个字符串的相似度后得到的位代码。
SPACE(int_expr) 返回包含指定空格数的字符串。
STR(float_expr[,length[,decimal]]) 返回浮点表达式的字符串表示法。
STUFF(char_expr1,start,length,char_expr2) 使用字符表达式替换字符表达式的一部分字符,从指定的位置开始替换指定的长度。
SUBSTRING(char_expr,start,length) 返回从字符表达式的指定位置开始,截取指定长度得到的字符集。
UPPER(char_expr) 将字符表达式全部转换为大写。

系统函数

COALESCE(expr1,expr2, xprN) 返回第一个非NULL表达式。
COL_LENGTH('table_name','column_name') 返回列的长度。
COL_NAME(table_id,column_id) 返回指定的表中的列名。
DATALENGTH('expr') 返回任何数据类型的实际长度。
DB_ID([‘database_name']) 返回数据库的标识号。
DB_NAME([database_id]) 返回数据库的名称。
GETANSINULL([‘database_name']) 返回数据库的默认空性(Nullability)。
HOST_ID() 返回工作站的标识号。
HOST_NAME() 返回工作站的名称。
IDENT_INCR('table_or_view') 有新的记录添加入到表中时计数加。
IDENT_SEED('table_or_view') 返回标识列的起始编号。
INDEX_COL('table_name',index_id,key_id) 返回索引的列名。
ISNULL(expr,value) 使用指定的值替换的NULL表达式。
NULLIF(expr1,expr2) Expr1与Expr2相等时,返回Null。
OBJECT_ID('obj_name') 返回数据库对象标识号。
OBJECT_NAME('object_id') 返回数据库对象名。
STATS_DATE(table_id,index_id) 返回上次更新指定索引的统计的日期。
SUSER_SID([‘login_name']) 返回用户的登录标识号。
SUSER_ID([‘login_name']) 返回用户的登录标识号。这个函数类似于SUSER_SID()函数,并且保留了向后的兼容性。
SUSER_SNAME([server_user_id]) 返回用户的登录标识号。
SUSER_NAME([server_user_id]) 返回用户的登录标识号。这个函数类似于SUSER_SNAME()函数,并且保留了向后的兼容性。
USER_ID('user_name') 返回用户的数据库标识号。
USER_NAME(['user_id']) 返回用户的数据库名称。

文本和图像操作函数

TEXTPTR(col_name) 返回varbinary格式的文本指针值。对文本指针进行检查以确保它指向第一个文本页。
TEXTVALID('table_name.col_name',text_ptr)检查给定的文本指针是否有效。返回表示有效,返回表示指针无效。

数据库操作

查看所有数据库 show databases；

使用（进入）数据库 use 数据库名；

查看当前所使用数据库 select database();

表操作

查看所有表 show tables；

查看表结构 desc 表名;

查看创建表的命令（可以查看约束）show create 表名；

创建表 create table table_name(字段名数据类型可选的约束条件）;

修改表名 alter table 表名 rename ro 新表名 删除表 drop table [if exists] 表名 删除并重新创建表 truncate table 表名

mysql:

修改表结构重命名：alter table 表名 change column 列名新列名列属性；

修改表结构不重命名：alter table 表名 modify 列名列属性；

删除列 alter table 表名 drop 列名

sql server:

修改表结构重命名：exec sp_rename '表名.列名','新列名',‘column’;

修改表结构不重命名：alter table 表名 modify 列名列属性;

视图操作

创建视图 create view 视图名 as 字段名相关查询;

修改视图定义 alter view 视图名 as 字段名相关查询;

删除视图 drop view 视图名;

数据操作

增：

insert into 表名 values()

inset into 表(列名) values()

改：

update 表 set 字段=值 where

删：

delete from 表 where

分页查询 limit 索引，长度;

查： select

\G转换显示格式

联合查询 union (all) 合并查询结果

完整性约束操作

完整性约束：实体完整性参照完整性用户自定义完整性

创建约束

建表时创建primaty key|foreign key(字段名) references 表名（字段名） |check
独立创建 alter table 表名 add constraint 约束名 foreign key(字段名) references 表名（字段名）
创建默认值约束 alter table 表名 add constraint 约束名 default ‘默认值’ for 列名；
创建唯一性约束 alter table 表名 add constraint 约束名 unique(列名)；

删除完整性约束(sql server) alter table 表名 drop constraint 约束名;

删除完整性约束(mysql) alter table 表名 drop [foreign key|index|约束名称] | [primary key];

触发器

新增触发器 create trigger 触发器名 on 表名 for 类型[insert|delete|update] as 执行语句

删除触发器drop trigger 触发器名

事务

方式一：

set @@autocommit=0;
--do some thing
commit;
rollback;

方式二：

start transaction;或begin;
--do some thing
commit;
rollback;

四大特性：AIDC

事务隔离级别读未提交读已提交可重复读串行化

其他

标识种子 identity(201801,1)

格式化年份 year()

获取时间 getdate()

设置计算列 as

允许将显式值插入表的标识列中 on-允许 off-不允许 set identity_insert 表名 on

todo：存储过程

索引

定义：索引(index)是帮助MyQL高效获取数据的数据结构(有序。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

分类一：主键索引、唯一索引、常规索引、全文索引

分类二：聚集索引、二级索引

索引性能分析

1.查询执行频次：show global|session status 'com_______'

3.show profile