基本Sql语句的使用

数据库：

1.ubuntu数据库安装 服务端，客户端

安装：sudo apt-get install mysql-server

查看状态，停止，启动服务：

sudo service mysql status

sudo service mysql stop

sudo service mysql start

navicat: tar zxvf …

2.数据库客户端使用，navicat图形化软件和命令行

./start_navicat 启动navicat

3.数据库基本操作

数据库和表和字段操作：

登录数据库
mysql -uroot -p
显示当前时间
select now();
退出数据库
exit/quit/ctrl+d
查看所有数据库
show databases;
创建数据库
create database 名字 charset=utf8;
使用数据库
use 名字
查看当前使用的数据库
select database();
删除数据库
drop database 名字;
查看当前数据库所有表
show tables;

创建表
create table 表名(
字段名称 数据类型 可约束条件
id int unsigned primary key auto_increment not null,
name varchar(10) not null,
age tinyint default 0,
gender enum("男","女") default "男"
);

查看表结构
desc 表名
使用表
use 表名

修改表-给表添加字段
alter table 表名 add 列名 类型 约束;
alter table students add birthday date not null;

修改表-修改字段类型和约束:modify
alter table students modify birthday datetime;

修改表-修改字段名和字段类型和约束
alter table 表名 change 原名  新名 类型 约束;
alter table students change birthday birth date not null;

修改表-删除字段
alter table students drop birth;

查看创建数据库的SQL语句
show create database 数据库名;

查看创建表的SQL语句
show create table 表名;

删除表
drop table 表名;

表数据操作：

create table students(
id int unsigned primary key auto_increment not null,
name varchar(10) not null,
age tinyint default 0,
gender enum("男","女") default "男"
);
查询所有列数据
select * from students;

查询指定列数据
select name,age from students;

添加数据:
说明：default 使用字段默认值，以下效果一样,主键id自增
全列（字段）插入
insert into students values(0,'张三',18,default);
insert into students values(default,'张三',19,default);
insert into students values(null,'张三',19,default);
部分列插入
insert into students(name,age) values('李四',90);
全列多行插入
insert into students values(0,'王五',10,'女'),(0,'王八',14,'男');
部分列多行插入
insert into students(name,age) values('你好',10),('不好',11);

修改数据:
update students set age=40 where id = 3;
update students set age=10,gender='男' where id = 4;

删除数据
delete from students where id = 8;
一般不删除数据，加入标识列逻辑删除：
alter table students add is_del tinyint default 0;

as，where，distinct关键字
as起别名：
select name as 姓名,age as 年龄 from students;
select name n,age a from students;as可以省略

distinct:用于去除重复的数据行
select gender from student;
select distinct gender from students;删除了重复的gender
select distinct age,gender from students;

where:可以指定条件 and,or,not,>,<,=,!,>=,<=
逻辑，比较查询：
select * from students where id=3;
select * from students where id>=3;
select * from students where id!=3;
select * from students where name!='李四';
select * from students where id>3 or name!='张三';
select * from students where id>3 and name!='张三';
select * from students where not (age>=10 and age <=15);

范围查询：between .... and,in,not in
查询一定范围内的编号
select * from students where id between 3 and 8;
select * from students where id not (between 3 and 8);
select * from students where id in(3,5,7);
select * from students where id not in(3,5,7);

模糊查询：like,    字符：%：任意多个字符   _:任意一个字符
查询姓张任意多个字符学生的学生
select * from students where name like '张%';
查询姓张而且名字只有一个字符的学生
select * from students where name like '张_';

select * from students where name like '张%' or name like '%三'

空判断查询：如果数据为空null可使用,=变为is
select * from stuednts where age is null;

SQL高级查询：

1.排序 order by 字段  		asc|desc  升降序.不写默认升序
select * from students where is_del=0 and gender ='男' order by id desc;
select * from students order by age desc;
select * from students order by age desc,height desc;先按年龄降序，相同按升高降序
2.分页查询：数据很多一页显示不完，可以一页一页的显示
select * from 表名 limit start,count    start可以省略，表示开始索引默认0,count 每页显示的条数
例如：select * from students where gender='男' limit 0,3;
简写：select * from students where gender='男' limit 3;
select * from students limit (n-1)*m,m;  第n页查询m条数据,索引为(n-1)*m
3.聚合函数:对表数据进行统计和计算，一般和分组(group by)来使用
常见聚合函数：不对空值进行统计,用法select count(col) from .....
count(col);求指定列的总行数
max|min(col);
sum(col);
avg(col);
例如：统计总行数,一般统计主键字段
select count(id) from students;
select count(*) from students;
查询女生编号最大值
select max(id) from students where gender='女';
select avg(ifnull(height,0)) from students where gender='男';若要统计空值，该sql空值按0统计
4.分组查询:按照指定字段进行分组，字段数据相等分为一组
group by：
查询性别种类:select gender from students group by gender;前后字段匹配

结合聚合函数：
例如：根据性别分组，并统计男女的总数
select gender,count(*) from students group by gender;

group_concat():统计每个分组的信息集合
例如：根据性别分组，并且统计男女所有名字集合
select gender,group_concat(name) from students group by gender;

having：相当于分组查询中加了条件进行过滤
select gender,count(*) from students group by gender having count(*)>3;

with rollup:对聚合函数进行汇总
select gender,count(*) from students group by gender with rollup; with rollup统计了男女count(*)总和
select gender,group_concat(age) from students group by gender with rollup;

连接查询：连接多表查询
create table classes(id int unsigned not null primary key auto_increment,name varchar(20) not null);
5.内连接查询:查询两个表中符合条件的共有记录   ...表1 inner join 表2 on 条件
例如：学生表和班级表共有的班级号
select * from students inner join classes on students.class_id=classes.id;
select s.name,c.name from students s inner join classes c on s.class_id=c.id;只显示学生名字和班级名字
6.左连接查询:以左表数据为查询条件查询右表数据，不存在使用null填充
...表1 left join 表2 on 条件
select * from students s left join classes c on s.class_id=c.id;
select s.name,c.name from students s left join classes c on s.class_id=c.id;只显示学生名字和班级名字
7.右连接查询:以右表数据为查询条件查询左表数据，不存在使用null填充
...表1 right join 表2 on 条件
select * from students s right join classes c on s.class_id=c.id;
select s.name,c.name from students s right join classes c on s.class_id=c.id;只显示学生名字和班级名字
8.自连接查询：左表和右表为同一个表
source 路径.sql    :执行sql文件，里面包含一系列sql语句
select * from areas c inner join areas p on c.pid=p.id limit 20;
select c.id,c.title,p.pid,p.title from areas c inner join areas p on c.pid=p.id limit 20;按需要选择显示的内容
9.子查询：select语句中嵌入了另外的selcte语句,外面select主查询，括号里面子查询
例如：查询大于平均年龄的学生
select * from students where age > (select avg(age) from students );
查询学生在班的所有班级名字
select * from classes where id in(select class_id from students where class_id is not null);

4.MYSQL高级操作

1.远程登录，更改配置文件/etc/mysql/mysqld/mysqld.cnf中的bind-address注释掉。执行以下操作：update user set host='%'where user='root';。然后要知道服务端ip地址，端口号即可远程登录。
2.数据库设计3大范式
第一范式：列的原子性，列不能够分成其他列
第二范式：满足第一范式，还有，表必须有一个主键，非主键字段必须完全依赖于主键，而不能只依赖于主键的一部分。例如订单表和产品表得分开，订单表中若有产品价格则不符合完全依赖于订单id，它应该依赖于产品id。
第三范式：满足第二范式，另外非主键列必须直接依赖于主键，不能出现传递依赖。例如订单id，订单编号，订单客户名，订单客户名依赖于订单编号，订单编号依赖于订单id，出现传递依赖，应该拆分。
4.E-R模型，实体-关系模型，描述数据库存储数据的结构模型，简单说就是表内字段，表之间关系图。可以使用建模工具，如power designer。方框：表，菱形：关系，椭圆：字段。关系可以有一对一，一对多，多对多。多对多可以使用中间表。
5.外键约束SQL语句的编写
比如班级只有1，2，插入数据不能为3，需要添加约束，避免产生垃圾数据。
alter table students add foreign key(class_id) references classes(id);
创建表的时候创建外键约束:括号中添加foreign key(class_id) references classes(id)
以上让学生表中的班级id只能约束于classes表中的id即班级id号。
删除外键约束：alter table students drop foreign key 外键名;外键名为查看创建表show create table students显示的外键名字
6.演练-分组和聚合函数的组合，一些演练
比如查询价格大于平均价格并且降序
select * from goods where price > (select avg(price) from goods) order by desc
查询商品种类和平均价格
select cate_name,avg(price) from goods group by cate_name;
7.将查询结果插入到其他表中
例如：利用goods表中的商品分类信息重新创建一个商品分类表
create table good_cates(
id int unsigned not null primary key auto_increment,
    name varchar(50) not null
);
insert into good_cates(name) select cate_name from goods group by cate_name;把good商品分类查询结果直接插入到good_cates表中
8.使用连接查询更新表中的某个字段
update goods g inner join goods_cates gs on g.cate_name=gs.name set g.cate_name=gs.id;
更新goods表的cate_name变为goods_cates表中name对应的编号。
9.创建表并给某个字段添加数据,一次性完成
create table goods_brands(
id int unsigned not null primary key atuo_increment,
name varchar(50) not null
)select brand_name as name from goods group by brand_name;
10.修改goods表结构
desc goods 查看表结构
alter table goods change cate_name cate_id int not null,change brand_name brand_id int not null;一次性修改两个字段名称和类型和约束

5.事务和索引

事务：
支持事务引擎：InnoDB  MYSQL默认该引擎
改引擎：alter table students engin = 'MylSAM';不支持事务
MYSQL默认把sql操作提交事务到数据库，可以 set autocommit=0; 之后就不会自动提交事务，只是修改了缓存的数据，需要commit才真正修改数据库，才能被其他用户看见。改为手动提交事务。
rollback：回滚到上一确定的数据库状态.
pymysql默认支持事务，自动开启事务。只需commit就行。


索引：也叫做键，提升数据库的查询速度，保存着数据表里所有记录的位置信息。数据库数据量很大时可以用索引，提升查询效率。
查看表中已有索引：主键id是索引
show index from 表名;
索引创建：
alter table 表名 add index 索引名字段名);
alter table classes add index my_name(name);默认索引名与字段名一样
索引删除：
show create table classes;查看索引名
alter table 表名 drop index 索引名
联合索引：一个索引覆盖表中两个或多个字段,减少磁盘空间开销
alter table 表名 add index (name,age);默认索引名为第一个
删除同索引
联合索引的最左原则：使用查询的时候需要最左边的字段，创建联合索引的时候常用的查询字段放到最左边。
alter table 表名 add index (name,age);
联合索引；
select * from students where name='xxx';
select * from students where name='xxx' and age=10;
非联合索引：
select * from students where age=10;