SQL简单基础语法

----创建表
	--create table table3(
	--id int ,
	--name nchar(10)
	--);


	 --select * from employee inner join department on employee.dep_id = department.id order by age limit 1,3;

	 --create table student(
		--	id int primary key,
		--	name varchar(20),
		--	class int
	 --);
	 --create table class(
		--	cla_id int,
		--	super_QQ varchar(20)
	 --)

	--insert into student select id,name,class from table1;
	--insert into class values(1,'13456'),(2,'5455'),(3,'222445'),(4,'123455'),(5,'123456');
	--select * from student;
	--select * from class;



----create database 创建一个数据库 
--create database test;
----use   跳转到一个数据库
--use test;

----create table 创建数据库中的一个表 
----	CREATE TABLE table_name
----(
----column_name1 data_type(size),
----column_name2 data_type(size),
----column_name3 data_type(size),
----....
----);
--create table table1 (
--		id int,
--		name char(10)
--);


------SQL约束-----
----NOT NULL - 非空--指示某列不能存储 NULL 值。
----UNIQUE - 唯一--保证某列的每行必须有唯一的值。
----PRIMARY KEY - 主键--NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
----FOREIGN KEY - 外键--保证一个表中的数据匹配另一个表中的值的参照完整性。
----CHECK - 限制其值--保证列中的值符合指定的条件。
----DEFAULT - 默认值--规定没有给列赋值时的默认值。
----IDENTITY(m,n) 自增数据，始值为m，公差为n
--create table table5(
--		id int identity(1,1) primary key ,
--		name varchar(20) not null,
--		sex varchar(10),
--		conncetion_way varchar(20) unique,
--		class_num int default 1 check(class_num<10),
--		check (id < 100),
--		check (sex ='male' or sex='female')
--	);
----添加主键 
--alter table table5 add constraint table5_pk foreign key(class_num) references table1(id); 
----删除主键
--alter table table5 drop constraint table5_pk;

--drop table table5;
--select * from class;




------表数据的增删改查------

----插入数据
--insert into table1 values(1,'李白');
--insert into table1 values(2,'毒妇');
--insert into table1 values(3,'赵括'),(4,'鸭梨');

----删除数据
--delete table1 where name='毒妇';
--truncate table 表名;----清空表数据
	--insert into table3 values(1,'dd'),(2,'dd');
	--select * from table3;
	--truncate table table3;
	--select * from table3;

--修改数据(更新数据)
--update table1 set name='鸭梨大' where name='鸭梨'; 
--update table1 set name='赵括大' where id=3;




--**查找数据**--
			----#select 字段 from 表 
			--#where 条件
			--关系运算符> = < >= <= !=
			--包含运算符 between(a,b) , in 
			--distinct 去重 查找时加上可以消去完全重复的数据;
			--#order by 排序
			--#group by 分组		
			--#having 过滤条件 
			--#top n 取前n条数据
			--like '%','_',[字符串],[^字符串]    %:匹配大量字符，_匹配一个字符 ,[字符串]匹配字符串中任意一个字符,[^字符串]匹配不为字符串中任一字符的字符
			--计数  count()计总数量，
			--as 别名   a as b :b是a的别名 可以调用b.id==a.id，b=a;
--select * from table1;
--select * from table1 where id !=1;
--select id as num ,name as guess from table1 where id<=3;
--select * from table1 where id between 3 and 5;
--select * from table1 where id in(1,3,5,4);
--select distinct class from table1  ;
--select id as num ,name as guess from table1 where id<=3 order by guess ;
--select id as num ,name as guess from table1 where id<=3 order by guess desc;
--select * from table1 where class>2; 
--select class,count(id) as num from table1 group by class;
--select class,count(id) as num from table1 group by class having count(id)>1;
--select top 3 * from table1 ;
--select * from table1 order by id;
--select * from table1 order by id desc;
--select top 3 * from table1 order by id;
--select top 3 * from table1 order by id desc;
----前方高能！   ：查找id=3-5的集合：				
		--select * from (select top 3 * from (select top 5 * from table1 order by id) as t1 order by t1.id desc) as t2 order by t2.id;
		--select top 3 * from (select top 6 * from table1 order by id desc) as t1 order by t1.id;
----虽然SQL没有MYSQL的limit m,n功能 但我们也可以用这种方式查找从第三个开始，查找三个元素;  只要思想不滑坡，办法总比困难多！
--select class as class_name,count(id) as person_num from table1 group by class;
--select class ,count(id) as num from table1 group by class having count(id) >1;
--select * from table1 where connection_way like '%41%';
--select * from table1 where connection_way like '74%';
--select * from table1 where connection_way like '_4%';
--select * from table1 where connection_way like '__4%';
--select * from table1 where connection_way not like '__4%';
--select * from table1 where connection_way like '[27456]%';
--select * from table1 where connection_way like '[^27456]%';


------SQL 连接(JOIN)
	---- a inner join b    取a与b交集
	---- a left join b     取a - a∩b
	---- a right join b    取b
	---- a full outer join b 取a∪b;
	---- union 联合
--select * from student,class;
--select * from student inner join class on student.class=class.cla_id;
--select * from student inner join class on student.class=class.cla_id where student.id>3;
--select * from  student as a inner join class as b on a.class=b.cla_id;
--select * from student as a full outer join class as b on a.class=b.cla_id;
--select * from student as a right join class as b on a.class=b.cla_id where a.class is null;
--select * from student as a full outer join class as b on a.class=b.cla_id where a.class is null or b.cla_id is null;
--select class from student union select cla_id from class;

----select into    从别的表查找新建一个新表
--select * into table4 from table1;
--select id,name into table4 from table1 where id<5;
--select * into table4 from student inner join class on student.class = class.cla_id;
--select * from table4;
--drop table table4;

----insert into : 从别的表取值增添为本表新值  insert into table_name select * from table_old_name
--insert into table4 select * from table1;
--delete from table4;
--insert into table4 select * from table1;
--select * from table1;
--insert into table4(id,name,connection_way,class) select id,name,connection_way,class from table1;
--select * from table4;
--insert into table4(id,name,connection_way,class) select id,name,connection_way,class from table1 where id <5;
--insert into table4(id,name,connection_way,class) select id,name,connection_way,class from table1 where id<5 or class<3;
--delete from table4;


------Index 索引
----create index 索引的创建
--create index in_id  on table4(id);
----drop index 删除索引  
--drop index in_id on table4;









------表结构的修改-------

----修改表名
--alter table table1 rename table_one;
--alter table table_one rename table1;
----修改一列的数据类型
--alter table table1 alter column name varchar(18) not null; 
----增加一列
--alter table table1 add connection_way nchar(20);
--alter table table2 add class int ;
----删除一列
--alter table table2 drop column class; 

--select * from table2;


------视图------
----create view 创建视图
--create view v1 as select id from table1;
--select * from v1;
--select * from v1 where id<5;
----drop view 撤销视图
--drop view v1;

----更新视图
--create or alter view v1 as select id,name from table1;
--select * from v1;

---------------------------------------------打草纸----------------------------------------------------

--insert into table4(id ,name,connection_way,class) values(1,'僵小鱼','54188','1'),(2,'鲸鲨王','14524','2'),(3,'夜凌云','7474741','3'),(4,'龙震天','123456','4'),(5,'涂山苏苏','2580','2'),(6,'洞房月初','10086','3'),(7,'疯狗侠路路通','11111','1'),(8,'百世','15468','1');

--alter table table1 add class char(10);
--alter table table1 alter column id int not null;
--alter table table1 add primary key(id) ;
---清空
--delete table1;
----写入大量数据(慎用，每次都算一次写入)
--insert into table1(id ,name,connection_way,class) values(1,'僵小鱼','54188','1'),(2,'鲸鲨王','14524','2'),(3,'夜凌云','7474741','3'),(4,'龙震天','123456','4'),(5,'涂山苏苏','2580','2'),(6,'洞房月初','10086','3'),(7,'疯狗侠路路通','11111','1'),(8,'百世','15468','1');

--select * from table1;
---查找前50%数据
--select top 50 percent * from table1;

--insert into table1 values();
--select * from table1;

--use test;
--select * from table1;
--insert into table1 values(1,'李白');
--update table1 set id=2 where name='李白';
--delete from table1 where id=2;

--select * from table1;

--create table table2(
-- id int primary key,
-- name nchar(10),
-- connection_way nchar(20)
-- );

--insert into table2 values(1,'剑豪','123'),(2,'孟姜女','1333'),(3,'如意郎','344'),(4,'郭恩','345'),(5,'少侠','5444');
--select * from table2;
--insert into table2 values(6,'少侠','55');

--select distinct id,name,connection_way from table2;