mysql基础总结

原创
2017/06/17 18:11
阅读数 19

1、SQL分类

DDL(Data Definition Languages)语句:数据定义语言,定义数据库、数据段、表、列、索引等数据库对象。关键字有:create、drop、alter

DML(Data Manipulation Languages)语句:数据操作语句,用于添加、删除、更新、查询数据库记录。关键字有:insert、delete、update、select等。

DCL(Data Control Languages)语句:数据控制语句,用于控制不同数据段直接的许可和访问级别的语句,这些定义了数据库、表、字段、用户的访问权限和安全级别。关键字有:grant、revoke等。

1.1 DDL语句

1.1.1 数据库操作

//创建数据库
create database dname;
//删除数据库
drop database dname;

     自带数据库:

  • infomation_schema : 存储系统中一些数据库对象信息,比如用户表信息、列信息、权限信息、字符集信息、分区信息。
  • performance_schema : 主要用于收集数据库服务器性能参数。
  • mysql : 存储系统的用户权限信息。
  • test : 系统自动创建的测试数据库,任何人都可使用。

1.1.2 表操作

    创建表:

create table tablename(
  column_name_1 column_type_1 constraints,
  column_name_2 column_type_2 constraints,
  column_name_3 column_type_3 constraints,
  ...
  column_name_n column_type_n constraints
)engine=InnoDB DEFAULT CHARTSET=UTF-8

    查看表信息:

desc tablename;

    查看表的创建语句:

show create table table_name;

    删除表:

drop table tablename;

    修改表:


//修改表类型
alter table tablename modify [column] column_definition [first|after col_name];
例子:
  alert table user modify user_name varchar(100);


//增加表字段
alter table table_name add [column] column_definition [first|after col_name];
例子:
  alter table user add age int(10);

//删除表字段
alter table table_name drop [column] col_name
例子:
  alter table user drop age;

//字段改名字
alter table table_name change [column] old_col_name column_definition [first|after col_name];
例子:
  alter table user change user_name real_name varchar(255);

//修改表名
alter table tablename rename newtablename;
例子:
  alter table user rename user2;

注意:
  change和modify都可以修改表,但是modify不可修改表的名称。
  first 或 after col_name可定义字段的顺序,新增的字段默认在最后。

1.2 DML语句

//插入记录
insert into table_name (col1,col2,col3...) values (val1,val2,val3...);
例子:
  insert into user (user_name,age,sex) values ('JONE',26,'MAN');

//更新记录
update table_name set field1=value1,field2=value2,field3=value3,...,fieldn=valuen [where...];
例子:
 update user set user_name = 'JAY' where user_name='JONE';

//删除记录
delete from table_name [where ...];
例子:
  delete from user where user_name = 'JAY';

//查询记录
where子句
group by [with rollup] [having]子句
order by子句
limit start,count子句
连接查询、子查询
记录联合

1.3 DCL子句

//授权
grant 权限 on 数据库对象 to 用户
例子:
  //授予用户common_user对数据库testdb中表数据的增删改查权限
  grant select on testdb.* to common_user@'%';
  grant insert on testdb.* to common_user@'%';
  grant update on testdb.* to common_user@'%';
  grant delete on testdb.* to common_user@'%';
  grant select,insert,update,delete on testdb.* to common_user@'%' identified by '123456';
  
  //授予用户developer对数据库testdb数据库表结构创建、修改、删除权限
  grant create on testdb.* to developer@'192.168.0.%';  
  grant alter  on testdb.* to developer@'192.168.0.%';  
  grant drop   on testdb.* to developer@'192.168.0.%'; 

//查看权限
  show grants;
  show grants for common_user@localhost

//撤销权限
revoke 权限 on 数据库对象 from 用户;

2、数据类型

2.1 数值类型

类型 字节 最小值 最大值
tinyint 1

有符号 -128

无符号 0

有符号 127

无符号 255

smallint     2

有符号 -32768

无符号 0

有符号 32767

无符号 65535

mediumint 3

有符号 -8388608

无符号 0

有符号 8388607

无符号 1677215

int、integer 4

有符号 -2147483648

无符号 0

有符号 2147483647

无符号 4294967295

bigint 8

有符号 -9223372036854775808

无符号 0

有符号 9223372036854775807

无符号 18446744073709551615

float 4 ±1.175494351E-38 ±1.175494351E+38
double 8 ±2.2250738585072014E-308 ±2.2250738585072014E+308

DEC(M,D)、

DECIMAL(M,D)

M+2 最大取值范围与double相同,给定DECIMAL取值范围由M和D决定
BIT(M) 1 ~ 8 BIT(1) BIT(64)

2.1.1 整数类型

  • 可指定宽度,如int(10)。同时配合zerofill使用;
  • 整型可以设置自动增长,auto_increment。每个表只可设置一个自增长字段,这个字段要么是primery要么是unique;

2.1.2 小数

  • 浮点数:float和double,最好不要用(M,D)

  • 定点数:decimal,默认整数位是10,小数位是0。用(M,D)设置精度(M--精度 表示整数位+小数位,D--标度 表示小数位)

2.2 日期时间类型

日期和时间类型 字节 最小值 最大值 零值表示
DATE 4 1000-01-01 9999-12-31 0000-00-00 00:00:00
DATETIME 8 1000-01-01 00:00:00 9999-12-31 23:59:59 0000-00-00
DATESTAMP 4 19700101080001 2038年的某个时刻 00000000000000
TIME 3 -838:59:59 838:59:59 00:00:00
YEAR 1 1901 2155 0000
  • 经常插入当前日期用timestamp,它返回后显示格式为YYYY-MM-DD HH:mm:ss,如果要让其显示为YYYYMMDDHHmmss则只需’+0‘即可。
  • timestamp如不设置值则默认为当前系统时间,若设置的值溢出则0000-00-00 00:00:00。
  • 表中只有第一列timestamp可默认为系统时间,其他的timestamp默认为0000-00-00 00:00:00。
  • timestamp表示的时间与时区相关,当我们改变时区(set time_zone='+8:00')时,timestamp类型的字段值也会随着变。
  • 只需表示年份则用year。
  • datetime是date和time的组合
  • 插入的数据YYYY-MM-DD HH:MM:SS 连接符可以是任意字符,如:2017+10+10 10/10/10。
  • 插入数据为连续数字时,必须是6位、8位、12位、14位的,其中6位指的是YYMMDD,8位指的是YYYYMMDD,12位指的是YYMMDDHHMMDD,14位YYYYMMDDHHMMSS。

2.3 字符串类型

3、常用函数

3.1 字符串函数

函数 描述
concat(S1,S2,S3,...,Sn) 连接列出的这些字符串
insert(S1,x,y,S2) 将S1中从位置x开始,y长度的子串,替换成S2,其中x从1开始。

3.2 数值函数

函数 描述
abs(x) 取绝对值
ceil(x) 返回大于x的最小整数值
floor(x) 返回小于x的最大整数值
mod(x,y) 返回x/y的模
rand() 返回0~1随机数
round(x,y) 对x四舍五入并取y位小数
truncate(x,y) 返回数字x截断为y位小数的结果

3.3 日期和时间函数

函数 描述
curdate() 返回当前日期
curtime() 返回当前时间
now() 返回当前日期和时间
unix_timestamp(date) 返回date的unix时间戳
FROM_UNIXTIME 返回unix时间戳的日期值
WEEK(date) 返回date在一年中第几周
YEAR(date) 返回date的年份
HOUR(date) 返回date的小时值
MINUTE(date) 返回date的分钟值
MONTHNAME(date) 返回date的月份名
DATE_FORMAT(date,fmt) 格式化日期
DATE_ADD(date,INTERVAL expr type) 在date基础上加一个时间间隔
DATEDIFF(expr,expr2) 返回expr和expr2之间间隔的天数

注意:

  • DATE_FORMAT格式化字符串fmt,一般取值有Y%-m%-d% H%-i%-s%
  • DATE_ADD中type有HOUR、DAY、MONTH、YEAR等取值。例:表示获取十天后的日期 select date_add(now(),interval 10 day);

3.4 流程函数

函数 描述
if(value,a,b) 如果value为真,则a,否则b
ifnull(value1,value2) 如果value1不为空则返回value1,否则value2
case when [value1] then [result1]... else [default] end  
case expr when value1 then result1 ... else [default] end  

4、SQL优化

 

 

展开阅读全文
打赏
0
2 收藏
分享
打赏
0 评论
2 收藏
0
分享
返回顶部
顶部