sql基础知识汇总

--创建数据库
create database mysql_name charset utf-8;
--创建表
create table school(
name varchar(15) not null,
history int(8) not null
);
--创建一个学生表
create table student(
id int(20) not null,
name varchar(20) not null,
age int(20) not null,
register_date date not null,
sex char(32) not null,
primary key(id)
);
create table classes(
    id int unsigned auto_increment primary key not null,
    name varchar(10)
);
--创建学生表&
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男','女','人妖','保密'),
    cls_id int unsigned default 0
);
--unsigned 是int 无符号,就是都是正数 not null表示该字段不允许空值
--enum 枚举  设置gender 只能在枚举内
--default 默认值
--修改表-添加字段
--alter table 表名 add 列名 类型;
alter table students add birthday datetime;
--比较运算符 = > <  >=  <=  !=  <>
--逻辑运算符   and  or not 
select * from student where id >3;
select * from goods where id = 1 and name="双肩包";
select * from goods where id = 1 or id = 2;

--范围查询  in  between 
select * from goods where between 1 and 5;
select * from goods where id in(1,2,3,4);
--空判断  非空判断为 is not null
select * from student where height is null;
--排序
select * from goods order by price asc;  -- 从小到大
select * from goods order by price desc; -- 从大到小
--优先级
由高到低的顺序为：小括号，not 比较运算符，逻辑运算符
--排序 group by  asc(从小到大)  desc(从大到小)
select age,count(*) from student group by age;
select register_date,count(*) as stu_num from student group by register_date;
select name,sum(age) from student group by name;
--聚合函数 
--count(*) 查询总数
--max(列) 最大值
--min(列)  最小值
--sum(列)  求和
--avg(列)  求平均值
select count(*) from goods ;  --查询总数
select max(price) from students where gender=2;
select coalesce(name,"Total Age"),sum(age) from student group by name with rollup;
--给school表中插入数据
insert into school(name,history) values("name_cn",20);
select * from school;
--更新数据
update student set sex = "F" where id>=1;
--删除
delete from students where id=5;
--修改字段 change可以修改字段名字与约束条件   modify 不能修改字段名字只修改字段约束条件
alter table school change name_old name_new varchar(32) not null default "Null";
--增加字段
alter table school add stu_id int;
alter table student modify sex enum("F","M") not null ;

--分组 group by
--group by + group_concat(字段)
select price,group_concat(name) from goods group by price order by price desc;
+------------+---------------------------------------+
| price      | group_concat(name)                    |
+------------+---------------------------------------+
| 999999.000 | 大春                                  |
|  28888.000 | mac pro专业级台式电脑                 |
|   9188.000 | imac me086ch/a 21.5英寸一体电脑       |
|   8499.000 | g150th 15.6英寸游戏本                 |
|   7999.000 | svp13226scb 触控超极本                |
|   6999.000 | hmz-t3w 头戴显示设备                  |
|   6888.000 | x3250 m4机架式服务器                  |
|   5388.000 | poweredge ii服务器                    |
|   4999.000 | y400n 14.0英寸笔记本电脑              |
|   4880.000 | x240 超极本                           |
|   4299.000 | u330p 13.3英寸超极本                  |
|   4288.000 | z220sff f4f06pa工作站                 |
|   3699.000 | at7-7414lp 台式电脑 linux ）          |
|   3499.000 | ideacentre c340 20英寸一体电脑        |
|   3399.000 | r510vc 15.6英寸笔记本                 |
|   3388.000 | ipad air 9.7英寸平板电脑              |
|   2899.000 | vostro 3800-r1206 台式电脑            |
|   2799.000 | x550cc 15.6英寸笔记本                 |
|   2788.000 | ipad mini 配备 retina 显示屏          |
|   1998.000 | ipad mini 7.9英寸平板电脑             |
|     99.000 | 商务双肩背包,0                        |
+------------+---------------------------------------+
--group by + 聚合函数
select name,avg(price) from goods group by name;
select price,count(*) from goods group by price;
--group by + having 添加条件  约束查询结果
--having 条件表达式：用来分组查询后指定一些条件来输出查询结果
--having作用和where一样，但having只能用于group by
select price,count(*) from goods group by price having count(*)>1;
--group by + with rollup  在最后新增一行，来记录当前列里所有记录的总和
select price,count(*) from goods group by price with rollup;

--分页  limit默认从零开始 limit start,count
select * from goods limit 0,5
select * from goods where price>99 limit (n-1)*m,m


page = data1["page"] if data1["page"] else 0    # page=3
limit = data1["limit"] if data1["limit"] else 20 # limit=3
c_num = (int(page) - 1) * int(limit)
# 第三页取三个数据   显示为6~9条数据
sql = "select * from goods limit 6,3 " 



--连接查询  
--select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列
select * from students as s inner join classes as c on s.cls_id = c.id;
select * from students as s left join classes as c on s.id = c.id
update students as s left join classes as c on s.id = c.id set s.cls_id =c.id;

--子查询
select * from students where age > (select avg(age) from students);
--列表子查询
select name from classes where id in (select cls_id from students);
--行级子查询
select * from students where (height,age) = (select max(height),max(age) from students);

子查询中特定关键字使用
in 范围
格式: 主查询 where 条件 in (列子查询)

将分组结果写入到goods_cates数据表
insert into goods_cates (name) select cate_name from goods group by cate_name;
插入多行数据
insert into students(cls_id) select id from classes;
--两张表必须有数据  同步表数据 通过classes表数据来更新goods表
update students as s left join classes as c on s.id = c.id set s.cls_id =c.id;

quit ctrl+d exit;
show databases;
use mysql_name;
show tables;
--查看表结构
desc school;
show create table school;
show create database mysql_name ;
--查看版本
select version();
--显示时间
select now();

insert into student values(0,"dachun",48,099.00,2,2,2019-2-22);
update student set name = "李",age = 22 where id = 1;
select name as 姓名, age as 年龄 from student;
select student.name, student.age from student as s;
select s.name as 姓名, s.age as 年龄 from student as s;
select age, name ,id from student ;
--round(条件，保留位数)   as 重命名
select round(avg(price),2) as avg_price from goods;

--外键约束:对数据的有效性进行验证
--关键字: foreign key,只有 innodb数据库引擎 支持外键约束
-- 给brand_id 添加外键约束成功
alter table goods add foreign key (brand_id) references goods_brands(id);
-- 给cate_id 添加外键失败
-- 会出现1452错误
-- 错误原因:已经添加了一个不存在的cate_id值12,因此需要先删除
alter table goods add foreign key (cate_id) references goods_cates(id);

--如何在创建数据表的时候就设置外键约束呢?
--注意: goods 中的 cate_id 的类型一定要和 goods_cates 表中的 id 类型一致
create table goods(
    id int primary key auto_increment not null,
    name varchar(40) default '',
    price decimal(5,2),
    cate_id int unsigned,
    brand_id int unsigned,
    is_show bit default 1,
    is_saleoff bit default 0,
    foreign key(cate_id) references goods_cates(id),
    foreign key(brand_id) references goods_brands(id)
);

--如何取消外键约束
-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
show create table goods;
-- 获取名称之后就可以根据名称来删除外键约束
alter table goods drop foreign key 外键名称;

--在实际开发中,很少会使用到外键约束,会极大的降低表更新的效率
--创建外键时,一定要注意类型要相同,否则失败

完整的select语句
select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit start,count

执行顺序为：
from 表名
where ....
group by ...
select distinct *
having ...
order by ...
limit start,count

视图就是一条SELECT语句执行后返回的结果集。视图是对若干张基本表的引用，一张虚表，查询语句执行的结果。对于复杂的查询，往往是有多个数据表进行关联查询而得到，如果数据库因为需求等原因发生了改变，为了保证查询出来的数据与之前相同，则需要在多个地方进行修改，维护起来非常麻烦

解决办法：定义视图

create view 视图名称 as select语句;

show tables;

select * from 试图名称；

视图的作用

1. 提高了重用性，就像一个函数

2. 对数据库重构，却不影响程序的运行

3. 提高了安全性能，可以对不同的用户

4. 让数据更加清晰

事务四大特性(简称ACID)

所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

• 原子性(Atomicity)

• 一致性(Consistency)

• 隔离性(Isolation)

• 持久性(Durability)

• begin;
  --或者
  start transaction;
  commit;
  rollback;

   

注意

1. 修改数据的命令会自动的触发事务，包括insert、update、delete

2. 而在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据

索引**的目的在于提高查询效率

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度

--查看索引
show index from 表名;
--创建索引
--如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
--字段类型如果不是字符串，可以不填写长度部分
create index 索引名称 on 表名(字段名称(长度))
--删除索引：
drop index 索引名称 on 表名;

要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。

建立索引会占用磁盘空间