PHP面试准备：Mysql语句

[DDL:数据定义语句 --- Data Definition Languange]
*** 数据库操作
1：创建数据库
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification [, create_specification] ...]
eg:
create database NAME; 
create database if not exists NAME;
create database if not exists NAME default character set utf8;
create database if not exists NAME charset UTF8;


2：删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
eg:
drop database NAME;
drop database if exists NAME;


3：修改数据库
ALTER {DATABASE | SCHEMA} [db_name] alter_specification [, alter_specification] ...
eg:
alter database learn default character set gbk;


*** 表操作
1：创建表
1.1：CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement]
1.1.1：column_definition:
   col_name type [NOT NULL | NULL] [DEFAULT default_value]
       [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
       [COMMENT 'string'] [reference_definition]
eg:
create table if not exists user_info(
id int not null auto_increment primary key comment "用户表主键",
username char(18) not null comment "用户名",
password char(32) not null comment "密码"
);       


1.1.2：table_option:
   {ENGINE|TYPE} = engine_name
 | AUTO_INCREMENT = value
 | AVG_ROW_LENGTH = value
 | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
 | CHECKSUM = {0 | 1}
 | COMMENT = 'string'
 | CONNECTION = 'connect_string'
 | MAX_ROWS = value
 | MIN_ROWS = value
 | PACK_KEYS = {0 | 1 | DEFAULT}
 | PASSWORD = 'string'
 | DELAY_KEY_WRITE = {0 | 1}
 | ROW_FORMAT = {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
 | UNION = (tbl_name[,tbl_name]...)
 | INSERT_METHOD = { NO | FIRST | LAST }
 | DATA DIRECTORY = 'absolute path to directory'
 | INDEX DIRECTORY = 'absolute path to directory'
  eg:
  create table if not exists user_info(
  id int not null auto_increment primary key comment "用户表主键",
  username char(18) not null unique key comment "用户名",
  password char(32) not null comment "密码"
  )engine=innodb default character set utf8 comment "用户表";


  create table if not exists user_info(
  id int not null auto_increment primary key comment "用户表主键",
  username char(18) not null unique key comment "用户名",
  password char(32) not null comment "密码"
  )engine=innodb default charset utf8 comment "用户表";


***1.2：CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(] LIKE old_tbl_name [)];
eg:
create table if not exists user_ninfo like user_info;


2：删除表
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE]
eg:
drop table if exists user_ninfo, user_nninfo;


3：修改表
ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] ...
3.1：增加列
   ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
 | ADD [COLUMN] (column_definition,...)
 | ADD INDEX [index_name] [index_type] (index_col_name,...)
 | ADD [CONSTRAINT [symbol]]
       PRIMARY KEY [index_type] (index_col_name,...)
 | ADD [CONSTRAINT [symbol]]
       UNIQUE [index_name] [index_type] (index_col_name,...)
 | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)
 | ADD [CONSTRAINT [symbol]]
       FOREIGN KEY [index_name] (index_col_name,...)
       [reference_definition]
eg:
alter ignore table user_info add status tinyint(1) not null default 0 after password;
alter table user_info add create_time int(10) not null after password;
alter table user_info add to_delete int(1) not null after status;


3.2：修改列
ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
 | CHANGE [COLUMN] old_col_name column_definition
       [FIRST|AFTER col_name]
 | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
   eg:
    alter table user_info alter status set default 1;
    alter table user_info change create_time create_time timestamp;
    alter table user_info modify status tinyint(1) not null default 0 after create_time;
    alter table user_info modify create_time int(10) not null after password;


3.3：删除列
DROP [COLUMN] col_name
 | DROP PRIMARY KEY
 | DROP INDEX index_name
 | DROP FOREIGN KEY fk_symbol
eg:
alter table user_info drop to_delete;  


3.4：修改表设置
table_options
eg:
alter table user_info engine=myisam default charset gbk;


4：重命名表
RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...
eg:
rename table user_info to user;


*** 索引操作
1：创建索引
1.1: CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name (index_col_name,...)


index_col_name:
col_name [(length)] [ASC | DESC]
eg:
create index book_name_index on book_info (book_name);
create index book_author_index on book_info (author(3) desc);


1.2: ALTER [IGNORE] TABLE tbl_name ADD INDEX [index_name] [index_type] (index_col_name,...)
index_col_name:
col_name [(length)] [ASC | DESC]
eg:
alter table book_info add index publish_time_index (publish_time desc);




2：删除索引
2.1: DROP INDEX index_name ON tbl_name
eg:
drop index book_name_index on book_name;
2.2: ALTER [IGNORE] TABLE tbl_name DROP INDEX index_name
eg:
alter table book_info drop index publish_time_index;


[DML:数据操作语句 --- Data Manipulation Languange]
*** 1：数据插入
1.1：INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)]
    VALUES ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
   eg:
    insert into user_info values (null, 'guojunzhou', 'sanyue', 1410000000, 0);
    insert into user_info (username, password, create_time) values ('admin', 'admin', 1410000000);


    1.2：INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name
   SET col_name={expr | DEFAULT}, ...
   [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
   eg:
    insert into user_info values (null, 'marchsoft', md5(username), 141000000, 2%2);


1.3: INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)]
   SELECT ...
   [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
    eg:
    insert into user_info (username, password, create_time) select concat(username,'_copy'), password, create_time from user_info;
  
*** 2：数据查询
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr, ...
[INTO OUTFILE 'file_name' export_options
| INTO DUMPFILE 'file_name']
[FROM table_references
[WHERE where_definition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_definition]
[ORDER BY {col_name | expr | position}
[ASC | DESC] , ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[FOR UPDATE | LOCK IN SHARE MODE]]


    2.1：select_expr, ...
    eg:
    select 1+1 as sum;
    select concat('hello','world','!') as greet;


    2.2：into outfile 'file_name'
    eg:
    select concat('Hello',' ','World','!') as greet into outfile 'D:/sql_result.txt';


    2.3：table references
    eg:
    select * from user_info;
    select id,password from user_info;
    select id,md5(password) from user_info;


    2.4：swhere_condition
    eg:
    select * from user_info where id = 1;
    select id,md5(password) as passwd from login_info where md5(password) = '3b980300341e7314993f8ed5a992b388';


    2.4：group by ...
    eg:
    insert into user_info values (null,floor(10000*rand()),substring(concat('',rand()),1,12),floor(100*rand()));
    select * from student_info where name != '' group by class_id asc/desc;


    2.5：having where_condition
    eg:
    select * from student_info where class_id = 1 having name like '%a%';


    2.6: order by 
    eg:
    select * from student_info order by name desc;


    2.7: limit
    eg:
    select * from student_info order by name asc limit 3;
    select * from student_info order by name asc limit 1,2;


*** 3：数据删除
3.1：单表删除
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
   [WHERE where_definition]
   [ORDER BY ...]
   [LIMIT row_count]
   eg:
    delete from user_info where username like '%_copy_1';


3.2：多表删除
3.2.1：DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
   tbl_name[.*] [, tbl_name[.*] ...]
   FROM table_references
   [WHERE where_definition]
    eg:
    delete login_info.*, user_info.* from login_info left join user_info on login_info.id = user_info.login_id where login_info.id = 15;
   3.2.2：DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[.*] [, tbl_name[.*] ...]
   USING table_references
   [WHERE where_definition]
   eg:
    delete from l.*, u.* using login_info as l left join user_info as u on l.id = u.login_id where u.login_id = 14;
    
*** 4：数据修改
4.1：单表修改
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
   SET col_name1=expr1 [, col_name2=expr2 ...]
   [WHERE where_definition]
   [ORDER BY ...]
   [LIMIT row_count]
   eg:
    update login_info set password=md5('123456') where status = 0;


4.2：多表修改
UPDATE [LOW_PRIORITY] [IGNORE] table_references
   SET col_name1=expr1 [, col_name2=expr2 ...]
   [WHERE where_definition]
    eg:
    update login_info as l left join user_info as u on l.id = u.login_id set password=md5('sanyue') where l.username='guojunzhou';


*** 5：清空表
TRUNCATE [TABLE] tbl_name
eg:
truncate table student_info;    


*** 6：replace
6.1：REPLACE [LOW_PRIORITY | DELAYED]
   [INTO] tbl_name [(col_name,...)]
   VALUES ({expr | DEFAULT},...),(...),...
eg:
replace into login_info values(null,'hehe','haah',1410000000,0);


6.2：REPLACE [LOW_PRIORITY | DELAYED]
   [INTO] tbl_name
   SET col_name={expr | DEFAULT}, ...
eg:
replace into login_info set username='123456',password='123456',create_time=1421111111;


6.3: REPLACE [LOW_PRIORITY | DELAYED]
   [INTO] tbl_name [(col_name,...)]
   SELECT ...
eg:
replace into login_info (username,password,create_time) select concat(username,'_0') as username, password, create_time from login_info;