sql基础操作练习

select * from msg where id <>61 and id<>64 order by id desc limit  1,3

select * from msg where id <>61 and id<>64 order by id desc limit  1,3

use runoob

set names utf8

select * from websites

大写不敏感,且在语句末端使用分号

select update delete 

insert into create database

alter database

create table

alter table

drop table

create index

drop index

select column_name,column_name from table_name;

select * from table_name;

select name,country from websites;

select distinct column_name,column_name from table_name;

select column_name,column_name from table_name where column_name operator value;

select * from websites where country=’CN’;

select * from websites where id = 1;

and 如果第一个条件和第二个条件都成立，则AND运算符显示一条记录

or 如果第一个条件和第二个条件只要有一个成立，则or运算符显示一条记录

（基于一个以上的条件对记录过滤）

select * from  websites where country=’CN’ and alexa > 50;

select * from websites where  country=’USA’ or country=’CN’;

select * from websites where  alexa>5 and (country=’CN’ or country=’USA’);

select column_name,column_name from table_name order by column_name,column_name asc|desc

insert into table_name values (1,2,3,4);

insert into table_name (column_name1,column_name2,column_name3) values (value1,value2,value3);

insert into websites (name,url,alexa,country) values(1,2,3,4);

insert into websites(name,url,alexa,country) values(1,2,3,4);

update table_name set column1=value1,column2=value2 where some_column=some_value;

update websites set alexa=’5000’,country=’USA’ where name = ‘菜鸟教程’;

delete from table_name where some_column=some_value;

delete from websites where name=’facebook’ and country=’USA’;

delete from table_name;delete * from table_name;

select column_name(s) from table_name limit number;

select * from persons limit 5;

select * from websites limit 2;

select column_name(s) from table_name where column_name like pattern;

select * from websites;

select * from websites where name like ‘G%’;%为通配符

%替代0个或多个字符

_替代一个字符

[charlist]字符列中的任何一个单一字符

[^charlist]不在字符列中的任何单一字符

select * from websites where url like ‘https%’;

select * from websites where url like ‘_oogle’;

select column_name(s) from table_name where column_name in (value1,value2);

select column_name(s) from table_name where column_name between value1 and value2;

select column_name(s) from table_name where column_name not between value1 and value2;

select * from websites where (alexa between 1 and 20) and country not in (‘usa’,’ind’);

select column_name(s) from table1 union select column_name(s) from table2;

select column_name(s) from table1 union all select column_name(s) from table2;

select * from websites;

select * from apps;

select country from websites union select country from apps order by country;

select country,name from websites where country=’CN’ union all select country,app_name from apps where country=’CN’ order by country;

drop table table_name;

drop database database_name;

truncate table table_name;

alter table table_name;

add column_name datatype;

modify column column_name datatype;

mysqli_connect(host,username,password,dbname,port,socket);

mysqli_close(mysqli &link);

select system_user();

select user();

select current_user();

select session_user();

select database();

use security;

select database();

select version();

select @@version;

select @@datadir;

select @@basedir;

select @@version_compile_os;

select * from users;

select count(*) from users;

select concat(1,2);

select concat(username,password) from users;

select concat_ws(“:”username,password) from users;

select username from users;

select group_concat(username) from users;

select ‘mysql’ into outfile ‘/tmp/mysql’;

select load_file(‘/tmp/mysql’);

select ascii(‘a’);

select ord(‘bc’);

select mid(‘mysql’,1,1);

select substr(‘mysql’,1,1);

select  length(‘mysql’);

select left(‘mysql’,1);

select floor(5.1);

select rand();

select sleep(2);

select IF(1<2,2,3);

select char(97);

select strcmp(‘a’,’b’);

select ifnull(1,2);

select ifnull(Null,2);

select exp(1);

and 1=2 union select 1,2,3–;

select user() regexp ‘^ro’;

ascii(substr((select user()),1,1))=114;

if(ascii((select user()),1,1))=114,0,sleep(4));

(ascii(substr((select table_name from information_schema.tables where table_schema=database() limit 0,1),1,1))=9);

updatexml(1,concat(0x7e,(select,@@version),0x7e),1);