一、概念
二、数据类型
三、具体操作语句学习
四、约束
五、聚合函数
六、排序（order by）分组操作（group by 列名）
七、连接查询
八、视图
九、索引
十、数据库设计范式：
十一、数据库账户管理
一、首先了解概念：
数据库服务器: MySQL的服务器程序名称叫 mysqld, 在windows下叫 mysqld.exe
数据库客户端: MySQL的客户端程序名叫 mysql, 在windows下叫 mysql.exe
MySQL所有的可执行程序都放在MySQL家路径下的bin下边, 所以要将bin设置到PATH(windows下是Path)环境变量中。
数据库: DataBase, 类似于一个Excel文件, 服务器上可以创建多个数据库(就像一台电脑上可以有多个excel文件)。
数据表: table, 类似于一个Excel文件中的sheet,
列: column, 对应一个sheet中的一列
行: row, 对应一个sheet中的一行数据， 一行数据也叫一条数据
二、mysql数据类型
三、具体操作
1.登录操作：mysql -uroot -p*******(输入你自己的密码）
2.数据库操作
展示有几个数据库：show databases;
创建数据库: create database sx; // 相当于在windows下右键新建一个excel文件
create database if not exists dbname; //不存在就创建库
create database if not exists dbname default character set = ‘utf8’;
create database if not exists dbname default character set = ‘utf8’ default collate = ‘utf8_general_ci’;
名词解释:
字符集(characte）: utf8是一种编码，但在此用utf8就意味着用的unicode字符集,
字符序(collate用来对字符比较大小的规则): 分 _ci, _cs, _bin, 分别是不区分大小写，区分大小写， 以编码值比较
打开数据库:use student; //相当于在windows下双击打开excel文件 ,但不会有表存在，而excel文件打开有个默认的sheet1
删库
drop database dbname;
drop database if exists dbname;
修改库
alter database dbname character set ‘ut8’ collate ‘utf8_unicode_ci’;
3、table相关语句
建表
例子1、CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
create table if not exists user
(
username varchar(30),
gender enum('M','F')
) engine=innodb default character set 'utf8' comment '这是一张用户表，用于登录';
例子2、create table user_audit(
id bigint(20) primary key auto_increment comment '主键自增',
auditor_id bigint(20) comment '审核人id',
context_id bigint(20) comment '审核对象id(用户id)',
remarks varchar(512) comment '审核备注',
result int(11) comment '审核结果:1:待审核、2:驳回，3：通过，4暂不合作',
time datetime comment '审核时间'
) character set utf8 comment '用户审核表';
改表
alter table tbname default charset 'gb2312' comment 'aaa'; //修改了表的编码和注释
alter table tbname change oldcolumnname newcolumnname timestamp; //修改列的名称和类型
alter table tbname modify columnname datetime; //修改列的类型,不能改名
alter table tbname add state enum('START','STOP'); //添加列最后一列
alter table tbname drop colname; //删除一列
alter table user_audit modify remarks varchar(512) after time; //remark是表中其中一列，将其位置移动到time后面。
alter table tbname rename newname; //改表名
删表
drop table tbname;
重新表(将原来表删除，再建一个和原来表结构一模一样的表，但是空表，没有数据);
truncate tbname; //一定是先删了表，再重建的空表,不是删数据的行为
复制表（建一个和原来表一模一样的表(没有数据):
create table tbname like otherdbname;
4、对表中数据操作
建立一个表create table stu( id int, name varchar(‘20’), age smallint, gender enum(‘M’,‘F’) );
插入语句:（stu是表名）
insert stu values(1,‘jet’,35,‘M’);
insert stu values(2,‘lisa’,30,‘F’),(3, ‘tom’,28,‘M’);
insert stu(id,name) values(4,‘jerry’),(5,‘riche’); //可指定列添加数据，是因为所有列都有默认值(null) ,如果没有默认值则不可以这样
查看插入结果:
select * from stu;
修改数据/更新数据:
update stu set gender=‘M’ where name=‘lisa’; //将姓名为lisa的数据的性别改为M
update stu set age=age+1 where name=‘lisa’; //将lisa长一岁
update stu set age=18,gender=‘F’ where name=‘jet’; //将jet的age,gender同时修改
删除数据:
delete from stu where gender=‘M’; //删除所有性别为男的数据行
5、导入数据库操作
导入world数据库:
在D:建个路径: D:\resources
把下载的world.sql放到D:\resources中,
在mysql客户端中输入:
mysql> source d:\resources\word.sql
或者进入数据库的文件目录下，打开cmd
mysql -u root -proot < employees.sql 导入数据库
6、select语句（查询语句）
limit 10;想要限制输出行数时，将limit语句添加到语句最后；
select * from 表名;//输出表里的一切数据
select * from表明 where 列名=‘”’；//筛选数据
select 列1名称，列2名称from 表名 where列名=‘”’；
例如： select code, name, case when gnp/population>0.01 then ‘发达国家’ else ‘发展中国家’ end from country where continent=‘Asia’;
select code ‘国家编码’ , name ‘国家’, case when gnp/population>0.001 then ‘发达国家’ else ‘发展中国家’ end ‘发达程度’ from country where continent=‘Asia’;
四、约束
1、唯一约束: 该列的值必须是唯一的, 居民身份证号码就必须是唯一的,
添加唯一约束的方式:
1, 建表时直接添加,
a, 列级添加（unique）
b, 表级添加
c 改表时添加
alter table t3 add uniqe（cardid)
alter table t3 modify cardid char（18）uniqe
2、非空约束:（not null）
约束一个列的值不能为空, 用not null修饰列, 如果不加not nul 是指可空, 非空列必有值，在插入时要注意。 
改表时指定非空:
alter table t5 modify cardid char（18） not null；
3、主键约束: primary key；
指定一个列的值或多个列的组合必须唯一，必须非空
一个列为主键: 单列主键
多个列为主键: 联合主键
主键自增: auto_increment 
4、默认值约束：默认值约束:
5、外键约束
外键约束:
是指有两张表A,B， A表中的一列必须来源于B表的主键列,
例如: world库中, city表,country表, city表中的countrycode列是引用的country表中的主键列(code); 
建表时添加外键:
外键练习:
建两张表:
表A: 学生表: tb_stu, 有字段: id(主键), name, gender,age
成绩表: tb_score, 有字段, id, stu_id, chinese,math,english
思考: 外键怎么配置: 成绩表中的stu_id要去引用学生表中的主键,
create table tb_stu( id int primary key comment ‘主键’, name varchar(32) not null, gender enum(‘M’,‘F’) default ‘M’, age int );
mysql> create table tb_score( id int primary key comment ‘主键’, stu_id int, chinese float,math float, english float );
alter table tb_score add foreign key (stu_id) references tb_stu (id);
五、聚合函数
sum, max, min, avg, count
select sum(population) total from country;//和
select max(population) from country;//最大
select min(population) from country;//最小
select avg(population) from country;//平均值
select count(code) from country;//计算数量
select count(*) from country;
六、排序（order by）分组操作（group by 列名）；无法说明，直接上例子
排序:
需求: 输出全球人口前十名的国家,以降序排
select name from country order by population desc limit 10;
练习: 亚洲人口前十的国家名字
select name from country where continent='Asia' order by population desc limit 10;
需求: 以人口数量对各大洲排序输出洲名和洲人口数量
select continent, sum(population) total_population from country group by continent order by total_population desc;
此时需要注意如果一长串查询语句时应该注意各个要素的顺序(from, where, group by order by limit )
七、连接查询
连接查询:
一次查询投影的结果来自来多张表,
create database if not exists sx;
use sx;
drop table if exists tb_student;
create table tb_student( id int primary key auto_increment comment '主键', name varchar(33) not null );
drop table if exists tb_teacher;
create table tb_teacher(id int primary key auto_increment comment '主键', name varchar(32) not null );
drop table if exists tb_football_player;
create table tb_football_player( id int primary key auto_increment comment '主键', stu_id int comment '学号', tea_id int comment '教师编号', player_no int ,role varchar(20) );
insert tb_student(name) values( 'jet'),('lisa'),('tom'),('jerry'),('riche');
insert tb_teacher(name) values('trump'),('penpeiao');
insert tb_football_player(stu_id,tea_id,player_no,role) values( 1,null,101,'前锋'),(2,null,102,'中锋'),(3,null,103,'后卫'), (null, 1,104,'守门员');
需求: 输出所有学生球员的姓名，角色，球员编号 ? 用内连接
姓名 角色 球员编号
jet 前锋 101
lisa 中锋 102
tom 后卫 103
select name, role, player_no from tb_student left join tb_football_player on tb_student.id=tb_football_player.stu_id;
左外连接的结果:
右外连接的结果:
select name, role, player_no from tb_student right join tb_football_player on tb_student.id=tb_football_player.stu_id;
全外连接(oracle中有,但mysql没有, 但mysql可以用union达到同样的效果) :
select name,role,player_no from tb_student left join tb_football_player on tb_student.id=tb_football_player.stu_id union select name,role,player_no from tb_student right join tb_football_player on tb_student.id=tb_football_player.stu_id;
内连接:
select name, role, player_no from tb_student inner join tb_football_player on tb_student.id=tb_football_player.stu_id;
交叉连接(笛卡尔积): 注意不要加连接条件(on … ) ,加了on就相当于内连接
select name, role, player_no from tb_student cross join tb_football_player; 
交叉连接也可以直接用逗号:
select name, role, player_no from tb_student cross , tb_football_player;
having子句: 也是一种条件筛选,但是它是对分组后的数据进行条件筛选,和where的区别:
where是对全表所有数据进行筛选，
having是对分组后的数据进行筛选,
例子: 输出总人口数大于1亿的地区region降序排名
Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld
| LocalName | GovernmentForm | HeadOfState | Capital | Code2
select region from country group by region having sum(population)>100000000 order by sum(population) desc;
学会用别名: 将后边的整个括号取个别名叫 newtb
select region from (select region ,sum(population) total from country group by region having total>100000000 order by total desc ) newtb ;
注意select语句中的各语法要素的编写顺序,但不是执行顺序
select 字段 from 表引用 join 表引用 on 连接条件 where 表级筛选条件 group by 字段 having 组级筛选条件 order by 分组字段 desc limit
注意: 如果select语句中有group by,则前边的投影字段中使用聚合函数时是针对分组之后的每一组进行运行, 例如，以地区分组之后，对人口进行求和时，即sum(population)计算的是一组内所有数据的和
八、视图
概念: 它是相对于表(表的数据是存储在数据库文件中的)来说，结构相同，但数据来源不同，视图的数据来源于表，而表的数据来源于文件
视图的好处: 对于复杂的查询，可能多次用到，不如把复杂查询的结构建成一个视图，以后可以用简单的sql语句来直接查视图可以了。
注意: 表中的数据变化会实时的反映到视图上,
创建视图的语法:
create view 视图名称 as select 列名1，列名2，from 表明；
需求:
1, 建个视图，两列，国家编码，国家名称
create view country_view as select code,name from country;
2, 建个视图，三列，国家编码，国家名称，官方语言 ,假设每个国家只有一种语言为官方语言
create view cty_view as select code,name, language from country cty left join (select * from countrylanguage where isOfficial='t') tb_lg on cty.code=tb_lg.countrycode;
九、索引
index, 其功能是快速定位数据，拿到数据，在数据库的表中，数据量是巨大的，且是无序的,
select * from tb where xxx=?
思考: 在大量数据里查找数据:
1, 线性查找，线性挨个地比对, 效率极其低下
2, 二分查找, 拆半比对，效率高于线性查找，但是对非线性的数据集无计于事.
3, 哈希映射:
4, 二叉查找树,
5, 多路平衡查找树, B树, B+, B*
对于一个表中巨大的数据量，必须设计好查找方式,
功能: 查找快
缺点: 索引也占用存储空间
分类:
1, 主键索引, 为列指定primary key, 则该列会生成索引，
2, 唯一索引, unique, 要求列值唯一,
3, 普通索引, create table中添加，也可以是alter table中添加
4, 联合索引,
5, 全文索引,只能对char,varchar,text这三种类型的列施加, 如果一个列的数据特别大，例如是一篇文章,
全文索引的添加方式: alter table tb_name add fulltext (column_name);
查找方式: select * from tb_name where match(column_name) against (‘关键字’);
1、使用ALTER TABLE语句创建索性
应用于表创建完毕之后再添加。
ALTER TABLE 表名 ADD 索引类型 （unique,primary key,fulltext,index）[索引名]（字段名）
alter table table_name add index index_name (column_list) ;//普通索引
alter table table_name add unique (column_list) ;//唯一索引
alter table table_name add primary key (column_list) ;//主键索引
MySQL通过对表中的列添加索引来实现快速查找,
2、使用CREATE INDEX语句对表增加索引
CREATE INDEX可用于对表增加普通索引或UNIQUE索引，可用于建表时创建索引。
CREATE INDEX index_name ON table_name(username(length));
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
//create只能添加这两种索引;
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
(3)删除索引
删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理，其格式如下：
drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;
其中，在前面的两条语句中，都删除了table_name中的索引index_name。而在最后一条语句中，只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。
如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。
创建联合索引:
CREATE TABLE emp3 ( emp_no int(11) NOT NULL, birth_date date NOT NULL, first_name varchar(14) NOT NULL, last_name varchar(16) NOT NULL , gender enum(‘M’,‘F’) NOT NULL, hire_date date NOT NULL, PRIMARY KEY (emp_no) , index name_index (first_name, last_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
十、数据库设计范式：
设计表时应该遵循的一些原则,
三范式:
1, 列必须是原子不可分, 就是针客观需求设计一个列
2,在满足第一范式的基础上, 必须要有主键, 且其它非主键列必须完全依赖于主键，不能部分依赖
3, 在满足第二范式的基础上, 非主键列必须直接依赖于主键，而不能传递依赖。
第一范式举例:
价格:
1.5¥
2.6$
应该如此:
价格 ,价格单位
1.5 ¥
2.6 $
第二范式举列:
需求: 用户购物车, 里边有多个商品 ，一台iphone7, 一副耳机
有两个用户: 1,2
用户表: user: id(主键), name
商品表: product: id(主键), set_price
购物车表: user_id(用户id), product_id(商品id),
假设 102是耳机, 101是iphone7
联合主键(user_id,product_id)
user_id product_id price
1 101 1000
2 101 1500
1 102 50
2 102 60
思考: 上边的单价即依赖于用户id,也依赖于商品id, 称非主键列必须完全依赖主键，不能部份依赖
第三范式:
拿employees库中的表举例:
员工表: employees( emp_no, first_name,last_name, birth_date, hire_date,gender) ;
如何设计工资表: (id为主键)
id, emp_no, first_name,last_name, salary , from_date, to_date
1 1001 Jet Li 1000 2018-02 2019-2
2 1001 Jet Li 1200 2019-02 9999-01
分析: 上表中的first_name,last_name都已经出现在employees表中,一个emp_no(1001)就决定first_name和last_name必须是Jet,Li, 我们说 first_name,last_name直接依赖于非主键emp_no, 而不是直接依赖于主键, 而emp_no依赖于主键id,所以出现:first_name传递依赖id,不是直接依赖id. 正确的设计:
id , emp_no, salary, from_date,to_date
十一、数据库账户管理(权限于root用户,当然其它用户经root授权之后也可以管理):
root@localhost,
帐户创建: create user ‘jet’@‘localhost’ identified by ‘123456’;
帐户修改: 密码修改,
修改当前登录帐号(自己)的密码: set password=password(‘123456’);
修改其它用户的密码: alter user ‘jet’@‘localhost’ identified by ‘666666’;
帐户授权:
grant all on . to ‘jet’@‘localhost’;//授予全部权限
`` grant all on 数据库名.表名 to ‘jet’@‘localhost’; 只将一个表的权限授予
帐户撤销权限:
mysql> revoke all on *.* from 'jet'@'localhost';
mysql> revoke select on world.country from 'jet'@'localhost';
删除帐户:
drop user 'jet'@'localhost';
- List item
1001
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
