mysql desc 二个属性_mysql 基础语法

以下为自己学习mysql 的一些笔记，以方便查询

目录

一、 ALTER的 语法

二、 表的完整性约束

三、 索引的操作(mysql 数据库支持至少 16 个索引)

四、 视图的操作

五、 触发器的操作

六、 单表查询数据记录

七、 多表数据记录查询

八、 使用mysql 常用函数

九、 mysql 的存储过程和 函数的操作

十、 mysql 游标的 使用

十一、 mysql 的日志管理

十二、 mysql 的维护和 性能提高

十三、 mysql 的安全机制

一、 ALTER的 语法

1.修改表名

Alter  table  old_tab_name rename  [to]  new_tab_name

2.在表的最后一个位置增加字段

Alter  table  table_name  add 属性名 属性类型

3.在表的第一个位置增加字段

Alter  table  table_name  add  属性名 属性类型 first

4.在表的指定字段后面添加字段

Alter  table  table_name  add  属性名   属性类型  after 属性名

5.删除字段

Alter  table  table_name  drop  属性名

6.修改字段

Alter  table  table_name  modify  属性名  数据类型

7.修改字段的名字

Alter  table table_name  change  旧的属性名 新的属性名 旧数据类型

8.同时修改字段的名字 和 属性

Alter  table  table_name  change  旧属性名 新属性名  新数据类型

9.修改字段的 顺序

Alter  table table_name  modify  属性名1数据类型  first | after 属性名2

二、表的完整性约束

单列约束 ： 即每个约束只是约束一列数据

多列约束 ： 每个约束可以约束多列数据

完整性约束

Not  null   约束字段值不能为空

Default 设置字段的默认值

Unique  key(UK)约束的字段值是唯一的

Primary  key(PK)约束的字段为表的主键，是表的唯一标识

Auto_increment 约束字段的值自动添加

Foreign key(FK)外键

设置唯一约束

Create  table  table_name(

属性名  数据类型  unique  字段名 ,

);

多字段唯一约束

Create  table  t_dept(

deptno  int,

Dname char(21),

Constraint  uk_name  unqiue(dname)

);

3.多字段主键

Create  table   table_name(

属性名  数据类型，

【 constraint 约束名 】primary  key (属性名 ， 属性名 .... )

);

外键约束 【 设置了FK 约束必须依赖于数据库中以及存在的父表的 主键 】

Create  table table_name (

属性名  数据类型，

...

Constraint  外键约束名  foreign  key  (属性名1)

References  父表名(字段名1)

)

三、索引的操作(mysql 数据库支持至少 16 个索引)

INNODB  默认使用的存储引擎支持 BTREE类型索引

MerMory 存储引擎支持HASH类型索引

1.索引的分类(6种)

普通索引

唯一索引

全文索引

单列索引

多列索引

空间索引

2.何时使用索引

经常被查询的字段， 即在where 子句中出现的字段

在分组中的字段， 即在 group by下的字段

存在依赖关系的字段，如主键和外键

拥有许多重复值的字段

3.普通索引的创建

创 建表的时候 创建索引

Create table tab_name(

字段名  数据类型，

...

Index | key 【索引名】 (属性名1【长度】 【 asc | desc 】)

)

在建好的表上创建索引

Create index  索引名  on 表名(属性名 【长度】 【 asc | desc 】)

使用alter table 创建索引

Alter  table  tab_name  add  index | key  索引名(属性名 【长度】 【 asc | desc 】)

4.唯一索引的创建

创 建表的时候 创建索引

Create table tab_name(

字段名  数据类型，

...

Unqiue  Index | key 【索引名】 (属性名1【长度】 【 asc | desc 】)

)

在建好的表上创建索引

Create  unique   index  索引名  on 表名(属性名 【长度】 【 asc | desc 】)

使用alter table 创建索引

Alter  table  tab_name  add  unique  index | key  索引名(属性名 【长度】 【 asc | desc 】)

5.全文索引的 创建

创 建表的时候 创建索引

Create table tab_name(

字段名  数据类型，

...

Fulltext  Index | key 【索引名】 (属性名1【长度】 【 asc | desc 】)

)

在建好的表上创建索引

Create fulltext  index  索引名  on 表名(属性名 【长度】 【 asc | desc 】)

使用alter table 创建索引

Alter  table  tab_name  add  fulltext  index | key  索引名(属性名 【长度】 【 asc | desc 】)

6.多列索引的创建

创 建表的时候 创建索引

Create table tab_name(

字段名  数据类型，

...

Index | key 【索引名】 (属性名1【长度】 【 asc | desc 】,

....,

属性名n 【长度】 【asc | desc 】)

)

在建好的表上创建索引

Create index  索引名  on 表名(属性名 【长度】 【 asc | desc 】,

....

属性名n【长度】 【asc | desc 】

)

使用alter table 创建索引

Alter  table  tab_name  add  index | key  索引名(属性名 【长度】 【 asc | desc 】,

....

属性名n 【长度】 【asc | desc 】

)

7.删除索引

Drop index  index_name  on  table_name

8.查看索引

Explain  select  *  from  table_name  where .....

四、视图的操作

对于视图数据可以进行增删改 操作

视图来自多个表的 时候不允许增删数据

1.视图的 创建

Create  view  view_name  as  查询语句

2.删除视图

Drop  view  view_name ;

3.查询视图

Select  查询字段  from  view_name

4.查看视图的 相关信息

Show table status  like  ‘view’ ;

Show table status  like  ‘view_name(自定义的view )’ ;

5.Show create view view_name

6.通过系统表(information_schema) 查看视图信息

Use  information_schema ;

Select  *  from  views  where  table_name  =’view_name(自定义的视图名)’;

7.修改视图

替换或是创建视图的方法

Create  or  replace  view  view_name  as  查询语句

Alter 语句修改视图

Alter  view  view_name  as  查询语句

五、触发器的一操作

1.在触发器进行下面操作语句时， 会触发触发器

Delete   insert   update

2.创建单条语句的 触发器 trigger

Create  trigger  trigger_name

Before | after  触发事件(update | insert | delete)

On 表名 for  each  row

触发的语句

3.创建多条触发器

Creater  trigger  trigger_name

Befor | after  触发事件(update | insert | delete)

On  表名  for  each  row

Begin

触发的语句

End

4.查触发器

Show triggers ;

5.通过系统表来 查看触发器

Use information_schema

Select  *  from  triggers    //查看所有的触发器

Select  *  from  trigger_name(查询的触发器名)

6.删除触发器

Droptriggertrigger_name

六、单表查询数据记录

主要学习内容有：

简单数据记录查询

条件数据记录查询

排序数据记录查询

限制数据记录查询

统计函数 和 分组数据记录查询

1.避免 重复数据查询

Selectdistinct  field1 ,field2  from  table_name ;

2.设置格式显示数据查询

Select  CONCAT(filed1 , ‘文本解释内容’, value ) from table_name;

Select  concat(ename,’雇员年薪是：’,sal*12) from emp;

条件数据 记录查询

带有关系运算符和 逻辑运算符的 数据查询

带有between and 关键字的 条件查询

带有is null 关键字的 条件记录查询

带有 in 的条件记录查询

带有  like 的条件记录查询

A.between  and

Select filed1 , field2 ...  From table_name  where  field  between value1 and  value2

B. 不符合 范围的数据记录查询

Select filed ...  From table_name  where filed  not  between value1 and value2

C. 带 IS   NULL 的关键字查询

Select  field ....  table_name where  filed  is   null

D. 带 in  关键字 的 集合查询

Select filed ... From table_name  where  filed in (value1,value2,value3,....)

该情况类似于：

Select  filed form table_name  where  filed=value1 or  filed=value2 or ....

E.带like 的模糊查询

Select filed ....  From  table_name  where  filed  like value;

Select filed .,.. From  table_name   where   filed   not  like value

Select filed .,.. From  table_name   where   not   filed   like value

<

“_”    该字符表示， 通配符能匹配单个字符

“%”   该通配符表示可匹配任意字符的模糊查询

>

4.排序数据记录查询

Select   filed  ...  From  table_name  where   条件(contition  )  order  by  filed1_name   [asc | desc ] ,  file2_name [ asc | desc ]

5.限制 数据记录查询 数量

Select  filed ....  From  table_name  where   条件语句 limit  offser_start , row_counts;

6.统计记录查询 (avg count sum max min )

Select  function(filed)  from  table_name  where  条件语句 ;

*关于统计函数的 注意点

对于mysql 的统计函数 ，如果操作的表中没有任何数据记录 ， 则 count() 函数会返回数据0， 其他的 函数 返回值为  null

7.分组数据 查询

Select  function()  from   table_name  where  条件语句  group  by  filed;

8.实现统计功能 的 分类查询

Select   group_concat(filed)  from  table_name  where  条件语句  group  by  filed;

10.实现多个分组的 查询

Select  group_concat(filed), function(filed)  From  table_name  where  condition  group  by   Filed1 , filed2  ...;

11.分组的 数据查询 -- 实现 having 子句限定分组查询

Select  function(filed) from table_name  where  group  by  filed1 ,filed2

Having  contition

七、多表数据记录查询

1.内连接查询

Select filed1 ,filed2 ..... Filedn   from  join_table_name1  inner  join  join_table_name2  [ inner join  join_table_name_n ]  on   连接条件

例子：

Select  e.empno  , e.ename , e.sal , e.job , l.ename  from  t_employee  e  inner  join  t_employee  l  on   e.agr =  l.empno ;

select  e.empno  ,  e.ename  , e.sal  ,e.job , l.ename ,d.dname  ,d.location  from t_employee  , t_employee  l ,  t_dept  d  where   e.mgr =l.empno a and  l.deptno  = d.deptno  ;

2.外连接查询

select  filed1,  filed2  ... Filed_n   from   join_table_name1

Left | right | full  join  join_table_name2  on  连接条件

3.合并查询数据记录

select  filed1 , filed2 .....  Filedn  from  tablename1

Union  | union  all

Select  filed1,  filed2  ....  Filedn  from  tablename2

.....

4.子查询

带 in 的子查询

Select  *  from  t_employee  where  deptno  in  (select  deptno  from  t_dept)

带 any  的子查询

=any ： 它的功能与关键字一样

>any(>=any)： 比子查询中返回的数据记录最小的还要大于数据记录

Select  ename ,  sal  from  r_employee  where  sal  > any  ( select  sal  from t_employee  where  job=’manager’ )

返回结果为多列的列子查询

Select  count(*)  numberfrom  t_dept  d ,

( select  deptno dno , count(empno)  number, avg(sal)  average  from  t_employee  group  by  deptno )   employee ;

八、使用mysql 常用函数

字符串函数

数值函数

日期函数

系统信息函数

1.常用的字符函数

1)合并字符串的函数 concat()  和 concat_ws() 函数

Concat ( s1 ,s2c , ... S_n )

Concat_ws( sep ,  s1 , s2 ... S_n );  -- sep 为分隔符

例子： Concat_ws( ‘-’ , ‘098’ , ’75914’  );

2) 比较字符串大小函数 strcmp()

Strcmp( str1, str2);

3) 获取字符串长度length()  和 char_length()

Length( str)

Char_length( str)

4) 实现大小写转换函数 upper()  和 字符数函数  lower()

Upper()  函数 等同于 ucase()

Lower()  函数等同于  lcase()

5) 查找字符串位置函数

Find_in_set( str1, str2)            / /返回字符串函数位置

Filed( str ,str1,  str2, str_n.... )/ /返回字符串指

定位置的函数

Locate( str1, str2)

Position ( str1 in  str2)

Instr ( str, str1)                 // 查找字符串匹配的位置

6) 截取字符串函数

Left ( str,num )

Right( str ,  num )

Substring( str,num , len  )   //截取指定位置和长度的字符串

7)替换字符串  insert() 函数  replace () 函数

Insert( str, pos , length , 替换后的字符 ) ;

Replace ( str, 替换的内容 , 替换后的字符);

8)去除首尾空格函数

Ltrim( str)

Rtrim( str)

Trim( str)

2.使用数值函数

Abs (x)  返回数值 x 的绝对值

Ceil (x)返回大于 x 的最大整数值

Floor(x)返回小于 x 的最大整数值

Mod(x)           返回x 模 y 的值

Rand(x)返回 0~ 1之间的随机数

Round(x, y)返回数值 x 的四舍五入后有 y 位 小数的数值

Truncate(x ,y)返回数值 x 截取 位数为 y 位的数值

3、使用日期函数和时间函数

Curdate() 和 current_date()获取的前日期

Curtime() 和 current_time()获取当前时间

Now()获取当前的日期时间

Unix_timestamp(date)获取当前date 的 unix 时间戳

Form_unixtime()获取 unix 时间戳的日期值

Utc_date()国际协调时间

Utc_time()

Week(date)返回当前日期是一年中的那一天

Year(date)返回日期date的年份

Hour(time)返回时间的小时值

Minute(time)分钟值

Second(time)秒数值

Monthname(date)返回时间的月份值

例子：

Selectnow()  当前时间和日期,

YEAR( NOW()) 年,

-- Quarter(now()) 季度 ,

Month(now())月份,

Week(now())    星期,

Dayofmonth( now()) 天,

Hour(now()) 小时 ;

1)  获取指定值的函数 extract( )

SelectExtract( type  from  date )

例子：selectextract( year from  now() );

2) 关于星期的 函数

Dayname()星期几的英文名

Dayofweek()返回是一星期中的第几天

Weekday()返回星期几

3) 计算日期和 时间的函数

To_days(date)

From_days(date())

Datediff( date1 , date2)

例子：

Selectnow() , to_days(now()) 相隔天数,

From_days(to_days(now()) 一段时间后的日期和时间,

Datediff(now() , ‘2000-1-1’);

Selectdatediff(now() , ‘2000-1-1’);

4)  于指定日期时间的操作

Adddate(date , n)表示 n 天后的日期

Sundate(date ,n)表示 n 天前的日期

Addtime(time, n)表示 n 秒后的时间

Subtime(time, n)表示 n 秒前的时间

4.使用系统信息函数

Version()返回数据库版本号

Datebase()返回当前数据库名字

User()返回当前用户

Last_insert_id()返回最近生成的 auto_increment 值

例子：

Selectversion() , user() ,datebase();

5.实现特殊功能的函数

Password(str)对于字符串 str 进行加密

Format(x , n)实现将数字 x 进行格式化 ，保留n 位小数

Inet_aton(ip)把 ip 地址 转换成数字

Inrt_ntoa(x)实现将数字转换成 ip

Get_loct(name , time)新建一个持续时间 为 time的名为 name 的锁

Release_loct(name)解锁

Benchmark( counter  , expr)把表达式执行 count 次

Convert(s using cs)实现将字符串s 的字符集变成 cs

Convert(x ,type)实现将x 变成 type 类型

九、mysql 的存储过程和 函数的操作

1.创建存储过程

Create  procedure  procedure_namebegin---   end;

2.创建函数

Create  function  function_name

3.定义存储过程 和 函数 的参数

Parameter_name   type   ;

如：  name  int;

4.关于存储过程 和函数的表达式

1) 声明变量

Declare  var_name  type  【 default  value 】

2) 赋值变量

Set  var_name = expr

3) 使用 select .... Into 实现赋值

Select  filed_name  into  var_name   from   table_name  where  condition(条件);

4) 定义条件

Declare  condition_name   condition  for  condition_value  ;

5.删除存储过程 和 函数

Drop  procedure  procedure_name;

Drop  function   function_name;

修改 存储过程 和函数

Alter procedure procedure_name [ 修改 内容语句 ]

Alter function function_name [ 修改 语句 ]

7.使用流程语句

If  search_condition  then  statement_list

. . .

End if

Case  case_value

When  when_value  then   statement_list

...

Else  statement_list

End  case

8.循环控制语句

1) [ begin_label : ]  loop

Statement_list

End  loop  [ end_loop ]

Leave  begin_label  // 离开标签

2)   [ begin_label : ]  while  searcg_condition do

Statement_list

End while [ end lable ]

3)   [ begin_label : ]  repeat  search_condition  do

Statement_list

End  repeat  [ end_list ]

9.查看 存储过程 和 函数

1)通过 show status  语句查看函数和存储过程

Show procedure status  [ like  ‘pattern’ ]

Show function  stutus  [ like  ‘pattern’ ]

2)通过 系统表 information_schema.routines  查看函数和存储过程

Use  information_schema

Select  *  from  routines  //

3)通过 show  create  语句查看函数和存储过程]

十、mysql 游标的 使用

1.声明 光标

Decade  cursor_name  cursorfrom  select_statement[sleect查询语句];

2.打开光标

Open   cursor_name

3.使用 光标

Fetch  cursor_name into   var_name ....

4.关闭光标

Close  cursor_name;

十一、mysql 的日志管理

1.二进制日志： 该日志文件会以二进制的形式记录下数据库的各种操作，但是不会记录查询语句

1)修改my.ini：

[mysqld]

Log-bin[=save_bin_path\filename]   //保存二进制文件的目录

2)查看二进制文件

Mysqlbinlog  filename.number

3)停止或是 开启二进制文件

Set sql_log=0;   //关闭

Set sql_log=1;//开启

4)删除二进制文件

Reset mater  ;-- 删除索引的二进制文件

Purgemaster logs  to  filename.number; -- 删除所有编号小于二进制文件的日志文件

Purge  master  logs  before  ‘yyyy-mm-dd’ ; --删除指定时间的日志文件

5) 利用 mysql 二进制文件进行恢复数据库

-- 恢复在时间点前开始的数据备份

Mysqlbinlog  -- start  - datetime = ‘ 时间点’  -u root  -p

-- 恢复在时间点后的数据备份

Mysqlbinlog  --stop  -datetime =’时间点’  -u  root  -p

-- 恢复在 某一个位置 的 数据备份

Mysqlbinlog --start【stop】  -position=’该位置的id’  -u  root  -p

2.错误日志： 该日志文件 会记录mysql 的启动 、关闭 、运行时出错的信息

1)修改 my.ini 文件

[mysqld]

Error-bin[=path\filename]

2)删除错误日志文件

Mysqladmin  -u  root  -pflush  -logs

3.其他类型日志

查询日志  ：该类型 的日志包括  通用查询日志 和 慢查询日志

通用查询日志： 记录用户登录和记录查询语句

慢查询 日志：记录执行时间超过 指定时间的 各种操作

1)修改 my.ini

[mysqld]

Log [=path/filename]

2)配置慢查询文件

[mysqld]

Log-slow-queries [=path\filename]

Long_query_time=n -- 设定时间

3)删除慢查询日志文件

Mysqladmin -u root  -p  flush  -logs

十二、mysql 的维护和 性能提高

1.通过 mysqldump 是显示数据备份

1) 备份一个数据库

Mysqldump  -u username  -pdbname  table_name  > backup_name.sql

2)备份多个数据库

Mysqldump -u username  -p--datebases  dbname  , dbnamen .... > path\database_back.sql

3)备份所有数据库

Mysqldump  -u  username  -p--all-datebases  > backup_name.sql

2.使用 mysqldump 还原数据库

Mysqldump-uusername-p[ dbname ]

3.实现数据库中的表导出成文本文件

1)利用 select ...  Into  outfile 方式来实现导出操作

Select  [ filed_name ]  from  table_name  [ where contion ]  into  outfile  ‘filename’  [ option ]

例子：

Select  *  from  t_dept  into  outfile   ‘c:/file_name.txt ’  --输出的文件

Fileds  terminated by  ‘\,’   -- 段结束符

Optionally  enclosed  by  ‘\ “’ --字段结束符

Lines  starting  by  ‘\>’--行结束符

Terminated  by  ‘\r\n’ ;

2)利用mysqldump 命令实现 导出的操作

Mysqldump  -u  root  -p  -T  c:\ database_name.txt

3)利用mysql 命令来实现导出操作

Mysql  -uroot  -p  -e  “select  [ file_name ]  from  table_name”  dbname > file_name;

4.实现文本文件导入到数据库表

1)使用 “load  data  infile” 方式导入数据

Load  data [local ]  infile  file_name  into  table  table_name  [ option ]

2)使用 mysqlimport  命令来实现导入操作

Mysqlimport  -u root  -p  [ --local ]  dbname  file_name  [ option ]

十三、mysql 的安全机制

1.创建普通用户

1)使用 create user 创建用户

Create  user  username  [ identified  by  password ]

2)使用insert 语句创建用户

Insert into  user( host , user , password )  values( ‘’ , ’’ , ’’);

执行 一下语句使其生效

Flush  privileges ;

3) 执行 grant 语句来创建用户

Grant  priv_typeon  databasesname.table  to

username  [ identified  by  password  ]

...

2.修改用户密码

1)使用 mysqladmin 修改用户密码

Mysqladmin -u  username  -p  password  new_password

2) 用户登录后 可以修改 用户的密码

Set password = password( ‘new_password’);

3) 更新系统表 mysql.user 数据记录修改密码( 限于 root 用户 )

Update user  set  password=password(‘new_password’)

Where  user =”user_name” and  host = ‘localhost’ ;

4)使用grant 来修改密码

Grant  priv_typeon  databasesname.table  to

username  [ identified  by  “new_password”  ]

3.删除 普通用户账号

1) 使用 drop user 语句

Dropuser  user1,  user2....

2)使用 mysql.user 表来实现删除 普通用户账号

Deletefrom  user  where= ‘user_name’  and  host = ‘ localhost ’ ;

4.权限管理

1)对用户进行授权

Grant priv_type  [ (column_list) ]on  database.table  to  user  [ identified  by  password  ]  with  with_option

2) 授权的权限 有

Grant  option ：被授权用户可以授权给其他用户

Max_queries_per_hour count：每小时的最大查询数

Max_connections_per_hour count：每小时的最大连接数

Max_updates_per_hour count：每小时的最大更新数

Max_user_connection count：单个用户可以同时有count个连接

3) 查看用户权限

A)查看指定用户权限

Select  host  , user , password , select_priv , update_priv , grant_priv , drop_priv  from  mysql.user  where  user=’user_name’

B)查看所有用户的权限

Show  grants  from  ‘user_name’ ;

5.收回权限

1) 收回所有权限

Revoke  all  privileges , grant  option  from  user_name [ identified  by  password ]

2) 收回权限

Revoke  priv_type  [( column_list )]  on  database.table_name  from  user_name  [ identified  by  password  ]