数据科学工程师面试宝典系列之四---MySQL基础

1.MySQL 基本命令详解

数据库管理系统-dbms-mysql；

mysql数据库：（1）MySQL设计部分，（2）MySQL操作部分；

数据库就是永久存储数据的地方；

mysql的帮助手段：？...

MySQL数据库在架构中的位置：

数据表三部分组成：

1）表结构（.frm）  // 列信息，列也叫字段

2）表数据 （.MYD） // 行信息，行也叫记录

3）表索引（.MYI）  // 把列中的行加到索引中（一般情况下一个表一定要把id这一列的所有数据都加到主键索引中）

表记录，表字段，表结构；表字段决定表结构；索引是为了检索时加速；主键索引，普通索引；

net start mysql	//开启数据库
net stop mysql	//关闭数据库
mysql -uroot -p123 --tee=c:\mysql.log	//cmd登录
show databases;	//查看数据库
use test	//进入数据库test
show tables	//查看表
create tables user(id int unsigned auto_increment primary key,name varchar(30), passwd varchar(30) ); //创建表
desc  user;	//查看表user列结构（字段）
select * from user;  	//查看表user有多少数据（记录），*代表所有字段
exit;		//退出mysql客户端
insert into user(id,name,pass)  values(1,"allen","123456");	//插入数据
select * from user where id=2;	//查询
delete from user where id=2;	//删除
update user  set name='allwn' where id =1;	//修改
alter table user add sex varchar(5) not null default “”male“”;  //增加一列

数据库操作：

1.创建数据库

//SQL命令不区分大小写，每一个数据库会多一个文件夹，在Windows下数据库名不区分大小写，在Linux下相反

create database t62;

2.查看数据库

show databases;

3.删除数据库

drop database t62;

4.切换数据库

use test;

表操作：

1.查看表

show tables；

2.创建表

//每创建一个表，在磁盘上会产生三个文件：1）表结构 2）表数据 3）表索引

create table user(id int,name varchar(30),passwd varchar(30));

//create table if not exists t1();

3.修改表名

rename table user to user1;

4.删除表

drop table user；

//drop table if exists user;

5.查看表字段

desc user;

6.查看表数据

select * from user;

mysql数据库设计篇

1.数据表概念

2.数据值和列类型

3.数据字段属性

4.数据表对象管理

5.数据表的类型及存储位置

6.数据表的默认字符集

7.创建索引

表字段类型：

1.数值

//显示和大小，这个显示是没有意义，如果你一定左侧补的0，本列得有zerofill属性，而且这一列变成无符号

int 类型  

// 最大是10位，有符号的类型为int(11)，无符号的类型为int(10) unsigned zerofill

//int(3)与长度无关，不够3位时前面补0，默认看不见

float 类型

2.字符串

//显示和个数

//char类型比varchar类型要快，varchar类型比char类型要节约空间

名字：char varchar

标题：varchar(100)

内容：text类型

char(n)  //占用n个字节，最多255字节

varchar(n)  //存储l+1，最多65535字节

char(30)    255字节

varchar(30)    255字节

text     //65535字节，存储l+2

longtext    //42亿字节，存储l+4

varchar(30);    可变长的最大30个字节

char(30);     不可变长的占用30个字节

3.日期和时间（数值）

data //年月日

time //时分秒

datatime //年月日时分秒

year  //年

timestamp

//用整型时间戳来替代日期类型，在PHP中把time()时间戳存到mysql中的int列

time()

数据库库操作：MySQL表字段属性和数据库字符集

数据字段属性：

1.unsigned  

//无符号，正数

2.zerofill   

//0填充，int（3），不够3位补0

3.auto_increment  

//自增

4.null 

//这一列值允许为null

5.not null  

//这一列值不允许为null

6.default   

//默认值

数据库表操作

数据表的字符集：服务器端，客户端；

\s    //查看服务器的基本信息

四种字符集：

Server characterset:     utf8

//服务器字符集

Db   characterset:          utf8

//数据字符集

Client  characterset:      utf8

//客户端字符集

Conn.   characterset      utf8

//客户端连接字符集

查看数据库字符集：show create database test;

查看表字符集：show create table user;

服务器、数据库和表校验字符集：collation-server = utf8_general_ci

客户端：客户端字符集，连接字符集；

php客户端和字符集设置方法：$sql = "set names utf8";

MySQL索引技术和字段修改：

数据表字段索引设置：主键索引，普通索引；

desc select id,name from t1 where id=3\G      //检测SQL语句，加\G把表颠倒

                                                                                //rows：1   代表找一个id=3的人检索一行就找到了

desc select * from t1 where name="c"\G         //不属于任何索引

create table t2(id int unsigned auto_increment,name varchar(30),primary key(id),index in_named(name));

show index from t2;   //查看表中的所有索引

alter table t2 drop index in_name;   //删除普通索引

alter table t2 add index in_name(name);  //添加普通索引

后期维护数据表字段：

alter table t1 add age int;    //添加字段

alter table t1 modify age int not null default 20;  //修改字段值

alter table t1 drop age; //删除字段

alter table t1 change name username varchar(30);  //修改字段名name为username，类名必须跟上

数据库记录操作（增删改查）

mysql结构化查询语言包含4个部分：

1.DDL  //数据定义语言，create，drop，alter

2.DML  //数据操作语言，insert，update，delete

3.DQL  //数据查询语言，select

4.DCL  //数据控制语言，grant（授权，用户安全），commit，rollback

增-insert：

insert into t1(username) values('allen');

删-delete

delete from t1 where id=6;  //没有==与=的区别

delete from t1 where id>=3 and id<=5;

delete from t1 where id between 3 and 5;

delete from t1 where id in (1,3,5);

delete from t1 where id=1 or id=3 or id=5;

改-updata

updata t1 set username='g' ,age='12',where id=6; //一次更改多个值

查-select

1.选择特定的字段

select id,name from t1 where id=3;

//select * from t1;

2.给字段取别名-as

select id,name from t1;

select id maoxian,name from t1;

select id as maoxian,name from t1 where id=3;

3.distinct关键字的使用

//取出唯一值

select distinct age from t1;

4.使用where条件进行查询

select * from t1 where id>=3 and id <=5;

5.查询空值null

select * from t1 where age is null;

select * from t1 where age is not null;

6.between and 的使用方法

select * from t1 where id between 3 and 5;

7.in的使用方法

select * from t1 where id=1 or id=2 or id=10;

select * from user where id in(1,3,5);   //建议使用

8.like的使用方法

//模糊查询，text类型不能加索引

%匹配所有

_匹配一个字符

select * from t1 where name like '%mysql%' or name like '%php%';   //%在前，name这一列的索引会失效

select * from t1 where name regexp '(.*user.*) | (.*php.*)';   //正则表达式，速度慢

9.使用order by对查询结果排序

//排序，asc和desc，一个是升序，一个是降序

select * from user order by id asc; //默认就是升序，asc数字从小到大

select * from user order by id desc;  //默认就是升序，desc数字从大到小 

10.使用limit限定输出个数（分页实现）

select * from user order by id desc limit 5; //取前面5条

select * from user order by id desc limit 0,5; //从0开始取5条，即前5条

mysql常用函数--select

concat()  //连接函数

select concat(id,age) from user;

select concat("aaa","bbb","ccc");

select id,name,pass,concat(id,'-',name) idname from user;

rand()   //随机数

select * from t1 order by rand() limit 3;

count() 

select  count(id),sum(age),avg(age),max(id),min(id) from t1;    //统计个数，求和，平均数，最大值/最小值

select count(id) tot from t1; //别名tot

select count(id) from t1 where name='user4';

select count(*) from t1;  //统计表总行数

分组聚合

select name,count(id) tot from mess group by name order by tot desc;  //group by 必须写在order by 之前

select  name,count(id) tot from mess group by name having tot>=5;   //group by 必须写在having之前，having 是对分组的结果进行筛选，这不能用where

多表查询

1.普通多表查询--多表（优先）

2.子查询、嵌套查询--多表

3.左链接--多表

需求：论坛用户和帖子统计

select * from t1,t2 where t1.id=t2.uid;   //多表查询

select t1.name,t2.title,t2.content from t1,t2 where t1.id=t2.uid;  

select t1.name,count(user,id)  from t1,t2 where t1.id=t2.uid group by t2.uid ;