sql基础

1. navicat 建立数据库的Sql 文件

参考： 资源下载内容

2. 注释

-- SELECT * FROM `users`;
select id,name,age from users;
-- 单行注释
-- 多行注释 ctrl + / 

3. create table

-- create
-- create table goods(
-- goodName varchar(20),
-- price decimal(4,2)
-- );
-- 删除存在重新创建
DROP TABLE
IF
	EXISTS goods;
CREATE TABLE goods(
-- unsigned 
-- primary key
-- auto_increment
id INT UNSIGNED PRIMARY KEY auto_increment,
goodsName VARCHAR ( 20 ),
price decimal ( 6, 2 ),
num INT,
company VARCHAR ( 20 ),
remark VARCHAR ( 30 ) 
);

-- struction
-- select * from goods;
-- one data
insert into goods values (0, '战神笔记本', 6000.00, 100, '某东', '战神在手, 天下我有!');
-- more datas 
insert into goods values (0, '小新笔记本', 5000.00, 100, '某东', '小新小新, 蜡笔小新!'),
(0, '外星人笔记本', 9999.00, 100, '某宝', '外星人上位, 战神渣渣!');
-- the name 
insert into goods(goodsName) values ('123');
select * from goods;

update goods set price=4500.00,num=50,company='PDD' where id=4;
select * from goods;
update goods set price =100,num=10,company="jd" where id =1;

select * from goods;

4. 单表sql

-- query
select * from goods;
-- insert 
insert into  goods values(5,'xiaoxin',999.00,100,'PDD','hello python');
insert into goods values(6,'lenven',1000.00,200,'difh','difh');
insert into goods(goodsName) values ('hello');
-- update  one record 
update goods set price=100.9,num=99,company='24',remark='234' where id=7;
-- delete one field 
delete from goods where id =6;
delete from goods where goodsName='123';
-- query
select * from goods;

select goodsName,price from goods;
-- alias 
select goodsName as '商品名称',price ="价格" from goods;
select goodsName as '商品名称',price as "价格" from goods;
-- data deduplication
select distinct(company) from goods;
-- condition query
select * from good where price=100;
select * from goods where price=999 and company='jb';

-- fuzzy query 
select * from goods;
-- keyword like %  any numbers of character
select * from goods where goodsName like '%本';
-- keyword like _ any signle character
select * from goods where remark like '_34';
-- query scope
-- between  and  == continuous range
select * from goods where price between 10 and 999;
-- in + container
select *from goods where company in('jd','PDD');

-- determines if the data is empty
insert into goods (price) values(100.0);
-- any operation must set criteria
update goods set goodsName="python",num=897,company='jd' where price =100.0;
select * from goods where goodsName like "%n";
select * from goods where remark is null;
-- determines if the data is not empty
select * from goods where remark is not null;

-- data sorting
-- ascending order
select * from goods order by price asc;
-- descending order 
select * from goods order by price desc;

-- delete = clear the data and save table structure
-- primary key counts are not reset

delete from goods;
select * from goods;
-- truncate = clear the data and save table structure
-- reset primary key
truncate table goods;
-- drop = clear the date and clear table structure
insert into goods(price) values(100.03);
drop table goods;
-- delete speed : drop >truncate > delete


-- descending sorting 
select * from goods order by price desc;
-- ascending sorting 
select * from goods order by price asc;


-- aggregate function 
-- count(*) calculate the column(not null)
select count(*) as 总数目 from goods;
select count(*) 总数目 from goods;
select count(price) from goods;
-- max()
select max(price) from goods; 
-- min()
select min(price) from goods;
-- avg
select avg(remark) from goods;
-- sum()
select * from goods where remark like '%战%';
select sum(price) from goods where remark like '%战%';

-- divide into groups + aggregate function 
insert into goods (company) values('jd');
insert into goods (company) values('pdd');
select company 公司名称,count(company) 公司数量 from goods group by company;

-- delete the empty record
delete  from goods where goodsName is null or remark is null ;

-- keyword having --
-- 查询jd和taobao最贵商品的价格
select company 公司名称,max(price) 最高价格 from goods group by company having company !='pdd';

-- paging query 
-- limit start,count (start -= 1)
select * from goods;
select * from goods limit 0,1;
select * from goods limit 4,3;
select * from goods limit 4,2;

-- (n-1)*m,m 
-- each page displays m pieces of data , and displays  
-- n page
select * from goods limit 2*3,3;

5. 连接

-- create new table categroy
-- drop table categroy;
drop table if exists category;
create table category (
id int unsigned primary key  auto_increment,
className varchar(30),
typeid int );
-- insert values 
insert into category values(1,'book',1);
insert into category values(2,'drink',2);
insert into category values(3,'food',3);

select * from category;
-- add new field into table goods 
alter table goods add typeid int;
update goods set typeid = 1 ;
update goods set typeid = 2 where id =2 or id =3;
update goods set typeid = 3 where id =11 or id=12;
-- select * from users;
select * from goods;


-- inner join 
select * from goods inner join category on goods.typeid=category.typeid;
-- display restrictions
select goods.goodsName ,category.className from goods inner join category on goods.typeid = category.typeid;
-- alias table 1.reduce table name 2.create separate spaces for tables 
select go.goodsName ,ca.className from goods go inner join category ca on go.typeid = ca.typeid;

update  goods set typeid=null where id =5;
-- left join 
-- display all the information of left table ,if the data is null ,fill the keyword null

select * from goods go left join category ca on go.typeid = ca.typeid;

-- query all class infomation 
select * from category ca left join goods go on ca.typeid = go.typeid;

-- right join 
-- display all the information of right table , if the data is null, fill the keyword null
select * from category ca right join goods go on ca.typeid = go.typeid;


-- test 
select * from goods go inner join category ca on go.typeid = ca.typeid where go.price <= 120;

select * from goods go left join category ca on go.typeid =ca.typeid where go.price >120;

select * from category ca right join goods go on go.typeid = ca.typeid where go.price >=120;


drop table if exists areas;
create table areas(
aid int unsigned primary key ,
name varchar(30),
pid int
);

insert into areas values(100,'安徽省',null);
insert into areas values(101,'合肥市',100);
insert into areas values(102,'芜湖市',100);
insert into areas values(103,'池州市',100);
insert into areas values(104,'铜陵市',100);
insert into areas values(105,'2区',101);
insert into areas values(106,'2区',101);
insert into areas values(107,'1区',102);
insert into areas values(108,'2区',103);

select * from areas;


-- self-associative table 
-- self-associative tale 
select * from areas a1 inner join areas a2 on a1.aid = a2.pid where a1.name ="安徽省";

-- self-associative table 
select a1.aid,a1.name 省份,a2.name 市,a3.name 区 from areas a1 inner join areas a2 on a1.aid =a2.pid
left join areas a3 on a2.aid = a3.pid
where a1.name ="安徽省";

6. 子查询

-- subquery 
-- price 
select * from goods;
-- average price
select avg(price) from goods;
-- 
select * from goods where price >(select 
avg(price) from goods);


-- subquery as the resource of the data
select * from category ca 
inner join
(select * from goods where company ="pdd")a on 
a.typeid = ca.typeid;

-- scalar quantum query
-- column  query
-- row query
--  table-level subquery

select * from goods where price between 100 and 999;

-- in 
select * from goods where price in (
select price from goods where price between 100 and 999);

-- some/any  ：任意一个
select * from goods where price =some(
select price from goods where price between 100 and 999);
-- 
select * from goods where price =any(
select price from goods where price between 100 and 999);
-- all : 全部(= all : 等于所有; != 或 <> all : 不等于所有)
select * from goods where price=all(
select price from goods where price between 100 and 999);
--
select * from goods where price !=all(
select price from goods where price between 100 and 999);

--
select * from goods where price<>all(
select price from goods where price between 100 and 999);

7. 外键

-- class table 
drop table if exists class;
create table class(
id int unsigned primary key auto_increment,
name varchar(10)
);

-- stu table 
drop table if exists stu;
create table stu(
name varchar(10) default null,
class_id int(10) unsigned default null,
foreign key(class_id) references class(id)
);

-- 
select * from class;
select * from stu;
-- show 
show create table class;
show create table stu;

drop table if exists ca;
create table ca(
tu_id int(10) unsigned
);
select * from ca;
-- add foreign key 
-- alter table ca add tu_id int unsigned;
alter table ca add  foreign key (tu_id) references class(id);
show create table ca;
-- drop foreign key 
alter table ca add id int unsigned primary key auto_increment;

alter table ca drop foreign key tu_id;

8. index 索引

-- open the time monitoring
set profiling  =1;
-- query 
select * from test_index where num=868;

-- running time 
show profiles;

-- set index 
create index num_index on test_index(num);
-- index query 
select *from test_index where num =868;
-- running time 
show profiles;


-- show index
show index from test_index;


-- add index when you create table 
drop table if exists index_1;
create table index_1(
id int unsigned primary key auto_increment,
name varchar(10) unique,
age int,
key(age) -- age --> index
);
select * from index_1;
show index from index_1;
--

-- drop index
drop index age on index_1;

9.存储结构

-- table structure
-- desc index_1;
-- datatest
DROP TABLE
IF
	EXISTS datatest;
CREATE TABLE datatest ( id INT  unsigned primary key auto_increment , num INT );

delimiter //
DROP PROCEDURE
IF
	EXISTS test;
CREATE PROCEDURE test ( ) BEGIN
-- var
DECLARE
		i INT;
	
	SET i = 0;
	WHILE
			i < 100 DO
			INSERT INTO datatest
		VALUES
			( null, i );
		
		SET i = i + 1;
		
	END WHILE;
	SELECT
		* 
	FROM
		datatest;
	
END //
-- 空格
delimiter ;

-- call the store  procedure 
call test();

10. 视图

-- view 
-- 	encapsulating sql statements

create view v_goods as select goodsName 商品名称,
price 价格,num 库存数量 from goods;

select * from v_goods;

-- encapusulating complex sql statments 
create view v_goods_cate as 
select go.* ,ca.id 序号,ca.typeid 类型,ca.className 类型名称 from goods go inner join category ca on go.typeid =ca.typeid;

select * from v_goods_cate;

drop view if exists v_goods_cate;

select * from v_goods_cate;

11. 日志

-- log function 
show variables like "general%";

set global general_log =1 ;
show variables like "general%";

set global general_log =0;
show variables like "general%";