SQL 基本语法 增删改查

创建数据库

create databases test1 chatset=utf8;

使用数据库

use test1;

创建student表

create table students{
	id int unsigned primary key auto_increment not null,
	name varchar(20) default '',
	age tinyint unsigned default 0,
	height decinal(5,2),
	gender enum('男','女') default '男',
	cls_id int unsigned default 0,
	is_delete bit defalut 0
}

字段	说明
id	主键id
name	学生姓名
age	学生年龄
height	学生身高
genfer	学生性别
cks_id	所属班级id（外键）
is_delete	逻辑删除(默认0，1表示删除)

创建class表

create table classes{
	id int unsigned auto_increment primary key not null,
	name varchar(30) not null
}

插入数据

insert into class values (6,"java"),(7,"python");

insert into students values
(0, '明1', 10, 100.00, 2, 1 ,0),
(0, '明2', 11, 150.00, 2, 1 ,0),
(0, '明3', 15, 130.00, 2, 1 ,0),
(0, '明4', 10, 110.00, 2, 1 ,0),
(0, '明5', 13, 130.00, 2, 1 ,0),
(0, '明6', 12, 140.00, 2, 1 ,0);

查询表中所有

select * from students;

查询指定字段

select name,age from students;

as给字段起别名

select id as 序号, name as 名字 from student;

as 表起别名

select id, name, genfer from students;
select students.id, students.name, students.genfer from students;
select s.id, s.name, s.genfer from students as s;

消除重复行

select distinct gender from students;

比较条件查询

select * from students where id = 1;
select * from students where id > 3;
select * from students where id <= 6;

逻辑条件查询

select * from students where id > 3 and id < 9;
select * from students where id = 3 or id = 9;

模糊查询

select * from students where name like '明%'

% 任意多个字符
_任意单个字符

范围查询

select * from students where id in(1,3,6);
select * from students where id between 3 and 6;
select * from students where (id between 3 and 6) and gender = 1;

空判断

select * from students where height is null;
select * from students where height is not null;

null 与 ’‘ 不同

排序

select * from students where gender = 1 order by id desc;
select * from students where gender = 1 order by name;
select * from students where gender = 1 order by age desc, height desc;

desc 降序 从大到小
asc 升序 从小到大 （默认）

聚合函数

// 计算总行数
select count(*) from students;
// 最大值
select max(id) from students;
// 最小值
select min(id) from students;
// 求和
select sum(age) from students;
// 求和 平均
select sum(age)/count(*) from students;
// 平均
select avg(age) from students;

分组查询

select * from students;
select gender from students group by gender;

// group by + group_concat()
select gender,group_concat(id) from students group by gender;

// group by + 聚合函数
select gender,avg(age) from students group by gender;

// group by + having having 和where作用一样 但是having只能用于group by
select gender,count(*) from students group by gender having count(*) > 2;

// group by + with rollup 在最后新增一行，显示记录总和
select gender,count(*) from students group by gender whith rollup;

分页

select * from students where gender = 1 limit 0,3;

// m条数据,当前显示n页
select * from students where gender = 1 limit (n-1)*n,m;

子查询

select * from students where age > (select ave(age) from students);

连接查询

// 内连接
select s.name, c.name from students as s inner join classes as c on s.cls_id = c.id;

// 右连接
select* from students as s right join classes as c on s.cls_id = c.id;

// 左连接
select* from students as s left join classes as c on s.cls_id = c.id;

---

备份 Sql Server

// 创建 备份数据的 device
USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'

// 开始 备份数据
BACKUP DATABASE pubs TO testBack

根据现有表创建新表

create table tab_new like tab_old

create table table_new as select c01,c02 from tab_old definition only

根据现有表改变表结构