1.创建表

create table if not exists usrinfo( id INT(11), number INT(11), name VARCHAR(255), birthday DATE );

2.查询表结构

show full columns from usrinfo;

desc usrinfo;

describe usrinfo;

3.查询创建表的语句

show create table usrinfo;

4.插入数据

插入单选数据

insert into usrinfo values (12123123,“hello”,“1990-02-15”);

插入多行数据

insert into usrinfo values (12123123,“hello”,“1990-02-15”), (12123123,“cc”,“1991-03-01”);

插入某些列

insert into usrinfo(number,name) values (121234123,“eefef”);

插入某些列多行

insert into usrinfo(number,name) values (121234123,“eefef”), (121234123,“eefef”);

5.查询某一列不重复的值

select distinct number from usrinfo;

6.增加新的列

增加新列

alter table usrinfo add id int(11);

并给修改新列的属性

alter table usrinfo change id id int not null auto_increment primary key;

更改新加列的排序:

把id这列,放到第1列 alter table usrinfo modify id int(11) first;

把birthday这列放在number后面

alter table usrinfo modify birthday date after number;

7.重命名表 和 列名.

更改表名

alter table <old_name> rename <new_name>;

更改列名,后面必须有属性值,才能修改成功

alter table usrinfo change id new_id int(10) ;

更改列名+ 属性,由int 转换为varchar

alter table usrinfo change id new_id varchar(11);

8.删除列.

提前新建一个列 alter table usrinfo add dd varchar(11);
mysql适用于下面的语句, 别的数据库未知.

alter table usrinfo drop dd;

9.删除某个列中某行为null的数据,注意是is null 不是=null

delete from usrinfo where birthday is null;

10.删除一个表

为了删除我先创建一个表create table del(id int(11) not null primary key auto_increment,name varchar(255),phone int(11));

drop table del;

11.返回唯一不同的值

查询单行的唯一不同的值

select DISTINCT birthday from usrinfo;

查询多行唯一不同的值,组合起来是唯一不同的值

select DISTINCT number,birthday from usrinfo;

12.where的简单查询语句

In 和 like

select * from usrinfo where name in (“Hello”);

select * from usrinfo where name like ‘%ello’; %表示多个字符,_ 下划线表示一个字母,like不区分大小写的

binary 区分大小写

select * from usrinfo where name like binary ‘Hello’;

13.and和or

select * from usrinfo where name=‘hello’ and number=‘1233’; select * from usrinfo where name=‘hello’ or name=‘Hello’; select * from usrinfo where name=‘hello’ or number=1233;

14.order by

升序,默认方式也是这种

select * from usrinfo order by number asc;

降序

select * from usrinfo order by number desc;

15.update

update 记录

update usrinfo set number=999 where id=18; update usrinfo set number=999 wehre id in(18,19); update更新必须带where,否则会更新所有的数据

16.limit

查询数据库中从2行到后n行的数据

select * from usrinfo limit 2,n;

查询起始后n行数据,如顶部的5行数据.

等价于select * from usrinfo limit 5;

17.Min 和 Max

查询某列的最小值并赋值给参数

select min(id) as small_id from usrinfo; select max(id) as max_id from usrinfo;

18.count计算搜索得到的行数, sum求合 avg求平均值

sum 和 avg 仅针对所有int数据

select count(id) from usrinfo;

select avc(number) from usrinfo;

19.alias 重命名

MySQL中语句

select id,CONCAT(name,' , ‘,address) as cc from usrinfo;

其它数据库写法

select id,name,’ , ‘, address as cc from usrinfo;

20.inner join, left join, rigth join, full join

Mysql not have full,jush left join union right join.

默认为join为inner join两个表的交集, left join 取左边表的集合包括交集但不包含右表没有交集的部分,
右连接与左连接相反,全连接是左右表的并集.

select o.id,o.name,c.id,c.number from order as o (inner) join customers as c on o.id = c.id; select o.id,o.name,c.id,c.number from order as o left join customers as c on o.id = c.id; select o.id,o.name,c.id,c.number from order as o right join customers as c on o.id = c.id;

全连接

select o.id,o.name,c.id,c.number from order as o right join customers as c on o.id = c.id union select o.id,o.name,c.id,c.number from order as o left join customers as c on o.id = c.id;

三个表inner join

select o.orderId,o.customerID,cu.costomerID,cu.costomerName,cp.CustomerID,cp.phone1 from (orders as o join customers as cu on where o.CustomerID = cu.CustomerID) join cphone as cp on o.CustomerID = cp.CustomerID;

21. self join

select A.CustomerName as CustomerName1,B.CustomerName as CustomerName2,A.Country from customers A, customers B where A.CustomerID <> B.CustomerID and A.Country= B.Country order by A.Country;

22. union

select CustomerID from orders union select CustomerID from customers;

重复值用ALL

select CustomerID from orders union select CustomerID from customers;

23. group by 把查询到的结果分组

以国家为组统计有多少个id

select count(c.CustomerID),c.Country from orders as o join customers as c on o.customerID = c.CustomerID group by c.Country;

以国家为组统计有多少个id,并按照id个数排序,从小到大

select count(c.CustomerID),c.Country from orders as o join customers as c on o.customerID = c.CustomerID group by c.Country order by count(c.CustomerID);

24. having, having是分组(group by)后的筛选条件,分组后的数据组内再筛选,where则是在分组前筛选.

以country分组,然后找个数超过1的.

select count(CustomerID) as number,Country from customers where CustomerID>1 group by Country having count(CustomerID) > 1;

25. select into, 复制表或表结构的时候, 只得到一个"外壳", clone一个躯体, 原表的主键,外键,约束,触发器,索引都不会被复制过来.

非mysql用法

select * into orders_back from orders;

mysql用法

create table orders_back (select * from orders);

复制部分列

create table order_b1(select orderId,orderDate from orders);

可以增加where语句

create table order_b2(select orderId,orderDate from orders where orderId > 123);

只复制表

create table orders_back3 (select * from orders where 1=2)

26. Insert into select, 从一个表拿数据插入另一个表中,

复制某些字段到另一个表中

insert into orders_back(orderId,customerID,orderDate) select orderId,customerID,orderDate from orders;

复制整个表到另一个表中,两张表的结构属性是一样的

insert into orders_back select * from orders;

也可以后面跟where语句进入, 筛选要复制的列

insert into orders_back select * from orders where orderId > 1;

27. 创建一个数据库, 删除一个数据

你必须有创建或者删除数据库的权限

create database mydata;

删除数据

drop database mydata;

28. 创建一个表时,列的属性的限定

NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK DEFAULT INDEX

check

Mysql 用法
create table person(
    id int(11) primary key,
    Age int(11),
    CHECK (Age > = 18)
);
Oracle SQL server用法
create table person(
    id int(11) primary key,
    Age int CHECK (Age>=18)
        );

多个check条件, 能用mysql, oracle都可用

create tables person(
    id int primary key,
    Age int(11),
    City varchar(255),
    CONSTRAINT CHK_person check (Age>=18 and City='Shanghai')
);
增加一个check条件
alter table person add CHECK (Age>=18);
增加多个check条件, mysql oracle都可用
alter table person add CONSTRAINT CHK_personAge CHECK (Age>=18 and city='Shanghai');
删除表中一个check条件
Mysql用法
alter table person drop CHECK CHK_personAge;
orace/SQL server用法
alter table person drop CONSTRAINT CHK_personAge;

29. Default 用法

创建表的时间
create table person(
    id int(11) primary key,
    Age int,
    City varchar(255) DEFAULT 'Shanghai'
);

增加表的属性时

Mysql用法

alter table person alter City set DEFAULT ‘Shanghai’;

oracle用法

alter table person modify City DEFAULT ‘Shanghai’;

删除默认

Mysql 用法

alter table person alter City drop default;

Oracle SQL server 用法

alter table person alter COLUMN City drop default;

30. Index 索引, 聚集索引一个表最多有一个

创建一个索引和创建多个索引

create index ind_orderId on orders(orderId); create index ind_orderid on orders(orderId, orderDate);

删除一个索引
mysql,格式是alter table table_name drop INDEX index_name;

alter table orders drop INDEX ind_orderId;

Oracle DB2

drop index index_name;

SQL server

drop index table_name.index_name;

31. Auto_INCREMENT 自动增加值

Mysql
create table person(
    id int(11) auto_increment primary key,
    age int(3)
);
SQL server
create table person(
    id int(11) IDENTITY(1,1) primary key,
    Age int(11)
);
Oracle 未知待更新

32. View 视图是原表某些列的影分身, 更改视图也就是更改原表的数据.

create VIEW view_orders_list as select orderId,orderDate from orders;

update view_orders_list set orderId=222 where orderId=22;

更新一个视图, 可以更新列的,直接在下面语句中增加就可以了.

create or replace view view_list as select orderId,orderDate,customerID from orders ;

删除一个视图

drop view view_list;

33. Null Functions

select productName, unitPrice*(unitInstock+IFNULL(unitOrder,0)) from products; select productName, unitPrice*(unitInstock+COALESCE(unitOrder,0)) from products;

34. Cross join 是两个表笛卡尔集

select * from orders cross join usrinfo;

35. update 与 inner join连用

oracle 中用法
update orders set orderDate="1991-02-09" where orders.customerID in(
    select customers.CustomerID from orders join customers on
    orders.customerID = customers.CustomerID;
    );
Mysql用法
update orders  as o join
(select c.customerID from orders as o join customers as c on o.customerID = c.CustomerID)
c on o.customerID = c.customerID
set o.orderDate="1990-09-08";
36 date日期查询特定year month day的人
select * from student where year(birthday)=1990 and month(birthday)=02 and day(birthday)=13;