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%";