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. 插入数据
4.1 插入单选数据
insert into usrinfo values (12123123,"hello","1990-02-15");
4.2 插入多行数据
insert into usrinfo values (12123123,"hello","1990-02-15"),
(12123123,"cc","1991-03-01");
4.3 插入某些列
insert into usrinfo(number,name) values (121234123,"eefef");
4.4 插入某些列多行
insert into usrinfo(number,name) values (121234123,"eefef"),
(121234123,"eefef");
5. 查询某一列不重复的值
select distinct number from usrinfo;
6. 增加新的列
6.1 增加新列
alter table usrinfo add id int(11);
6.2 并给修改新列的属性
alter table usrinfo change id id int not null auto_increment primary key;
6.3 更改新加列的排序:
把id这列,放到第1列
alter table usrinfo modify id int(11) first;
6.4 把birthday这列放在number后面
alter table usrinfo modify birthday date after number;
7. 重命名表 和 列名.
7.1 更改表名
alter table <old_name> rename <new_name>;
7.2 更改列名,后面必须有属性值,才能修改成功
alter table usrinfo change id new_id int(10) ;
7.3 更改列名+ 属性,由int 转换为varchar
alter table usrinfo change id new_id varchar(11);
8. 删除列.
8.1 提前新建一个列 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. 返回唯一不同的值
11.1 查询单行的唯一不同的值
select DISTINCT birthday from usrinfo;
11.2 查询多行唯一不同的值,组合起来是唯一不同的值
select DISTINCT number,birthday from usrinfo;
12. where的简单查询语句
12.1 In 和 like
select * from usrinfo where name in ("Hello");
select * from usrinfo where name like '%ello';
%表示多个字符,_ 下划线表示一个字母,like不区分大小写的
12.2 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
14.1 升序,默认方式也是这种
select * from usrinfo order by number asc;
14.2 降序
select * from usrinfo order by number desc;
15. update
15.1 update 记录
update usrinfo set number=999 where id=18;
update usrinfo set number=999 wehre id in(18,19);
16. limit
16.1 查询数据库中从2行到后n行的数据
select * from usrinfo limit 2,n;
16.2 查询起始后n行数据,如顶部的5行数据.
select * from usrinfo limit 5;
17. Min 和 Max
17.1 查询某列的最小值并赋值给参数
select min(id) as small_id from usrinfo;
select max(id) as max_id from usrinfo;
18. count计算搜索得到的行数, sum求合 avg求平均值
18.1 sum 和 avg 仅针对所有int数据
select count(id) from usrinfo;
select avc(number) from usrinfo;
19. alias 重命名
19.1 MySQL中语句
select id,CONCAT(name,' , ',address) as cc from usrinfo;
19.2 其它数据库写法
select id,name,' , ', address as cc from usrinfo;
20. inner join, left join, rigth join, full join
20.1 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;
20.2 全连接
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;
20.3 三个表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;
23. group by 把查询到的结果分组
23.1 以国家为组统计有多少个id
select count(c.CustomerID),c.Country
from orders as o join customers as c on o.customerID = c.CustomerID group by c.Country;
23.2 以国家为组统计有多少个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则是在分组前筛选.
24.1 以country分组,然后找个数超过1的.
select count(CustomerID) as number,Country from customers where CustomerID>1 group by Country having count(CustomerID) > 1;
25. select into, 复制表或表结构的时候, 只得到一个"外壳", clone一个躯体, 原表的主键,外键,约束,触发器,索引都不会被复制过来.
25.1 非mysql用法
select * into orders_back from orders;
25.2 mysql用法
create table orders_back (select * from orders);
25.3 复制部分列
create table order_b1(select orderId,orderDate from orders);
25.4 可以增加where语句
create table order_b2(select orderId,orderDate from orders where orderId > 123);
25.5 只复制表
create table orders_back3 (select * from orders where 1=2)
26. Insert into select, 从一个表拿数据插入另一个表中,
26.1 复制某些字段到另一个表中
insert into orders_back(orderId,customerID,orderDate) select orderId,customerID,orderDate from orders;
26.2 复制整个表到另一个表中,两张表的结构属性是一样的
insert into orders_back select * from orders;
26.3 也可以后面跟where语句进入, 筛选要复制的列
insert into orders_back select * from orders where orderId > 1;
27. 创建一个数据库, 删除一个数据
27.1 你必须有创建或者删除数据库的权限
create database mydata;
27.2 删除数据
drop database mydata;
28. 创建一个表时,列的属性的限定
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT
INDEX
28.1 check
Mysql 用法
create table person(
id int(11) primary key,
Age int(11),
CHECK (Age > = 18)
);
28.2 Oracle SQL server用法
create table person(
id int(11) primary key,
Age int CHECK (Age>=18)
);
28.3 多个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')
);
28.4 增加一个check条件
alter table person add CHECK (Age>=18);
28.5 增加多个check条件, mysql oracle都可用
alter table person add CONSTRAINT CHK_personAge CHECK (Age>=18 and city='Shanghai');
28.6 删除表中一个check条件
Mysql用法
alter table person drop CHECK CHK_personAge;
28.7 orace/SQL server用法
alter table person drop CONSTRAINT CHK_personAge;
29. Default 用法
29.1 创建表的时间
create table person(
id int(11) primary key,
Age int,
City varchar(255) DEFAULT 'Shanghai'
);
29.2 增加表的属性时
Mysql用法
alter table person alter City set DEFAULT 'Shanghai';
oracle用法
alter table person modify City DEFAULT 'Shanghai';
29.3 删除默认
Mysql 用法
alter table person alter City drop default;
Oracle SQL server 用法
alter table person alter COLUMN City drop default;
30. Index 索引, 聚集索引一个表最多有一个
30.1 创建一个索引和创建多个索引
create index ind_orderId on orders(orderId);
create index ind_orderid on orders(orderId, orderDate);
30.2 删除一个索引
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 自动增加值
31.1 Mysql
create table person(
id int(11) auto_increment primary key,
age int(3)
);
31.2 SQL server
create table person(
id int(11) IDENTITY(1,1) primary key,
Age int(11)
);
31.3 Oracle 未知待更新
32. View 视图是原表某些列的影分身, 更改视图也就是更改原表的数据.
create VIEW view_orders_list as select orderId,orderDate from orders;
update view_orders_list set orderId=222 where orderId=22;
32.1 更新一个视图, 可以更新列的,直接在下面语句中增加就可以了.
create or replace view view_list
as
select orderId,orderDate,customerID
from orders ;
32.2 删除一个视图
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连用
35.1 oracle 中用法
update orders set orderDate="1991-02-09" where orders.customerID in(
select customers.CustomerID from orders join customers on
orders.customerID = customers.CustomerID;
);
35.2 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;