MySQL Basics (ii) Common SQL statements

SQL statement type: &NBSP;&NBSP;&NBSP;&NBSP;DDL: Database Definition language CREATE,DROP,ALTER&NBSP;&NBSP;&NBSP;&NBSP;DML: Data manipulation Language Insert,delete, UPDATE,SELECT&NBSP;&NBSP;&NBSP;&NBSP;DCL: Data Control Language Grant,revoke common SQL statements:    create  database# Creating a database create table         #创建表CREATE  TABLE  table_name (field name, field data type, constraint) #创建表CREATE  INDEX              #创建索引      Data type: Plastic:int    tinyint#1byte     smallint#2byte    mediumint#3byte    int#4byte     bigint#8byte     character type:     char# fixed length character type (case insensitive)      varchar# variable-length character type (case-insensitive)     binary# fixed-length character type (casing-sensitive)     varbinary# Variable-length character (case-sensitive)      constraint:     not null# not allowed for null      default# Default Value &NBSP;&NBSP;&NBSP;&NBsp;        primary key# PRIMARY Key              unique key# Unique key     unsigned# unsigned (for int type)      auto_increment# is self-increasing and needs to be defined in one key (for type int) Use instance:show global variables;       #查看全局参数show  session variables;      #mysql的当前会话参数show   character set;       #查看支持的字符集show  collation;        #查看支持的排序规则show  engines;       #查看支持的存储引擎show  table  status like  ' user ' \g; #查看表状态show  global variables like  '%server% '; #数据库idshow  master logs; #查看数据库二进制日志show  master status; #查看主服务器状态show  grants for  ' DJ ' @ ' localhost '; #查看dj用户的授权信息show  index from mysql.user; #查看索引show  databases; #查看数据库show   Tables; #查看数据库的表select   Field name  from  table name  [where query condition] #查看表中的内容select  *  from user\G; #查看用户的详细信息selectdatabese ();          #查看默认数据库select  * from test where id >2 and id<4; #查询test表中id大于2小于4的数据where条件: >  <  >=  <=   ==  !=  and  or  not  like: Fuzzy query   Rlike: Fuzzy query based on regular expression drop database  database name; #删除数据库drop  table  table name; #删除指定表drop  user  ' user name ' @ ' Host '; #删除用户update   table name  set  changed value   where   condition Match # Modify data in table Update test set  name= ' Huyuan '  where id=2;deletefrom  table name  where  condition # Delete criteria matching data delete from  test where id=2;insert into  Table name (Field 1, Field 2)  values (Value of field 1, Value of field 1) insert into  test (name)  values (' Zhangtao ');insert into  table name   (Field 1, Field 2)  select Statement # Insert data from a SELECT query insert into user  (USER,HOST,PASSWD)  select User,Host,Password from mysql.user;grant  Permissions List  on  Database. Table  to  ' username ' @ ' authorized host '  identified by  ' password '           #授权用户revoke  drop on  database. Table  from  ' username ' @ ' authorized host '; #撤销授权altar  table  table name   add  Field name   Character # Add field alter table test ip varchar;altar table  table name  change   Source name   modified name   character # change field alter table test change ip sid int;altar  table  table name  drop  field name # Delete Field alter table test drop sid;set global  variable name = value; #设置全局参数set  session  variable name = value; #设置当前会话参数use   database name; #指定默认数据库create  index  index name  on  table name; # Create an index flush privileges; #重读授权表创建表的三种方式: 1, create            directly   create teble  Table name   (Field name 1  field type, field name 2  field type) 2, copy data from tables and tables      Create teble   table Name  select Statement Example: Create teble  test select user,host,password from  mysql.user;3, copy table structure     create teble tbl_name like  Template table Example: Create  teble test2 like test

This article is from the "Automated Operations" blog, please be sure to keep this source http://hongchen99.blog.51cto.com/12534281/1931918

MySQL Basics (ii) Common SQL statements