一篇文章搞懂SQL语句

  • 2019-05-01
  • 483
  • 0

SQL分类

DDL:数据定义语言,定义数据库,数据表和它们的结构,create,drop,alter

DML:数据操纵语言,主要用来操作数据,insert,update,delete

DCL:数据控制语言,定义访问权限,取消访问权限,安全设置,grant

DQL:数据查询语言:select from wherr

数据库的CRUD操作

创建数据库

--登陆
mysql -uroot -proot 没有分号
--设定字符集和校对规则
create database test character set utf8;
create database test character set utf8 collate utf8_bin;

查看数据库

--查看所有数据库
show databases;

--查看数据库定义的语句
show cteate database test;

修改数据库

--修改数据库的字符集
alter database test character set gbk;

删除数据库

drop database test;

其他数据命令

--使用数据库
use test;

--查看当前正在使用的数据库
select database();

表的CRUD操作

表的创建

CREATE TABLE IF NOT EXISTS `test_1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  `email` varchar(50) NOT NULL DEFAULT '',
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `create_time` int(11) unsigned NOT NULL DEFAULT '0',
  `update_time` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

查看表

--查看所有表
show tables;

--查看表的创建过程
show create table test_1;

--查看表结构
desc test_1;

修改表

--添加一列add
alter table test_1 add logo varchar(25) not null;

--修改列modify
alter table test_1 modify logo varchar(10);

--修改列名change
alter table test_1 change logo logos varchar(10);

--修改表名
rename table test_1 to test_2;

--修改表的字符集
alter table test_2 character set gbk;

删除表

drop table test_2;

表的CRUD操作

插入数据

insert into test_2(id, name,age) values(1,'zhangsan',22);
--简单写法,如果插入部分列,列名就不可以省略
insert into test_2  values(1,'zhangsan',22);

--查看表中的数据
select * from test_2;

--批量插入
insert into test_2 values
(2,"wangwu",23),
(3,"zhaoliu",25),
(4,"liuming",11);

CMD命令行下插入中文乱码问题

临时解决:set names gbk;

无忧方法:修改my.ini配置将57行的编码改成gbk

删除数据

delete from test_2 where id=1;
--如果不指定条件,会将表中的数据一条一条全部删除

delete删除数据和truncate删除数据有什么区别

delete:DML,一条一条删除表中的数据

truncate:DDL,先删除表,再重建表

如果数据比较少,delete高效,反之truncate高效。

更新表记录

update test_2 set name="zhangsansan" where id=3;
--如果参数是字符串,日期需要加上单引号

简单查询

--查询名字和价格字段
select name, price from test_2;

--表别名查询 as可省略
select p.name,p.price from product  as p;

--列别名查询 as 可省略
select name as 商品名称,price as 商品价格 from product;

--去除重复值
select distinct price from product;

--运算查询 as 可省略,仅仅在查询结果上运算
select *,price*1.5 as 折后价 from product;

--条件查询
<> 不等于,标准SQL语法
!= 不等于,非标准
select * from product where proce >10 and price <100;

beetween ...and
select * from product where price between 10 and 100;

逻辑运算 and,or,not,
select * from product where proce >900 or price <100;

--like模糊查询
_: 代表一个字符
%: 代表多个字符
select * from product where name like '_美%';

--在某个范围查询
selec * from product where id in(1,2,3);

--排序查询 order by
asc:  升序,默认
desc:降序
select * from product order by price desc;

select * from product where name like'%小% order by price asc;

--聚合函数 sum():求和
ave():求平均值
count():统计数量
max():最大值
min():最小值

select sum(price) from product;  获取的所有商品价格总和

注意,where条件后面不能接聚合函数!!!
查出所有商品价格大于某个值的全部平均值
--子查询
select * from product where price >(select ave(prive) from product);

--分组 group by
1.根据cno字段分组,分组后统计商品的个数
select cno,count(*) from product group by cno;

2.根据cno分组,分组统计每组商品的平均价格,并且商品平均价格 > 60
select cno,ave(price) from product group by cno having avg(price) > 60;

--having关键字可以接聚合函数,出现在分组后,都是做条件过滤
--where关键字,不可以接聚合函数,出现在分组前

--SQL编写顺序
S..F..W..G..H..O
select ..from..where..group by..having..order by

--SQL执行顺序
F..W..G..H..S..O

多表查询

多表之间的关系如何来维护

外键约束: foreign key

一个表中的外键指向另一个中的Primary key 主键约束用于预防破坏表之间连接的动作,也能防止非法数据插入外键列,用为它必须指向的那个表中的值之一。

  • 一对多 : 商品和分类

建表原则: 在多的一方添加一个外键,指向一的一方的主键

alter table product add foreign key(cno) references category(cid);
  • 多对多: 老师和学生, 学生和课程

建表原则: 建立一张中间表,将多对多的关系,拆分成一对多的关系,中间表至少要有两个外键,分别指向原来的那两张表

  • 一对一: 班级和班长, 公民和身份证, 国家和国旗

建表原则:

  • 将一对一的情况,当作是一对多情况处理,在任意一张表添加一个外键,并且这个外键要唯一,指向另外一张表。
  • 直接将两张表合并成一张表。
  • 将两张表的主键建立起连接,让两张表里面主键相等。

实际用途: 用的不是很多. (拆表操作),将个人的常用信息和不常用信息,减少表的臃肿。

主键约束: 默认就是不能为空, 唯一

  1. 外键都是指向另外一张表的主键
  2. 主键一张表只能有一个

唯一约束: 列面的内容, 必须是唯一, 不能出现重复情况, 为空

  1. 唯一约束不可以作为其它表的外键
  2. 可以有多个唯一约束

多表查询之内链接

1.内连接查询

--隐式内链接
select * from product p,category c where p.id=c.id;

--显式内链接
select * from product p inner join category c on p.id=c.id;

区别:

  • 隐式内链接:在查询出结果的基础上去做where条件过滤。
  • 显式内链接:带着条件区查询结果,执行效率高一些。

2.外连接查询

--左外连接
select * from product p left outer join categroy c on p.id=c.id;

--右外连接
select * from product p right outer join caategroy c on p.id=c.id;

区别:

  • 左外连接,会将左表中的所有数据都查询出来,如果右表中没有对应的数据,用NULL替代。
  • 右外连接,会将右表中的所有数据都查询出来,如果左表中没有对应的数据,用NULL替代。

分页查询

select * from product limit 0,3;
select * from product limit 3,3;    索引,条数

startIndex = (index -1)*条数

子查询

--查询分类名为手机数码的ID
select id from category where name="手机数码";

--得出ID为1的结果
select * from product where id = (select id from categroy where name="手机数码");

--查询出商品名称,商品分类名称的信息
--左连接
select p.name,c.name from product p left outer join category c on p.id=c.id;

--子查询
select name,(select name from category c where p.id=c.id) as 商品分类名称 from product p;

评论

还没有任何评论,你来说两句吧

粤ICP备17155863号

- 友情链接 - Theme by Qzhai