【PHP笔记】mysql数据库设计

数据库管理系统-dbms-mysql

mysql数据库：
1.mysql设计部分
2.mysql操作部分

mysql基本指令：
1.登录
mysql -uroot -p123
2.查看数据库
show databases;
3.切换数据库
use test
4.查看数据库所有表
show tables;
5.查看表结构或表字段
desc user
6.查看表记录
select * from user;
7.退出mysql终端
exit;

mysql基础篇：
一、数据库操作
1.查看数据库
show databases;
2.创建数据库
create database y1 default charset=utf8;
3.切换数据库
use y1;
4.删除数据库
drop database y1;

二、表操作
1.查看表user
show tables;
2.创建表
mysql> create table user(
-> id intshow databases;,
-> name varchar(30),
-> pass varchar(30),
-> )default charset=utf8;
Query OK, 0 rows affected (0.03 sec)
3.修改表名
rename table user to user1；
4.删除表
dorp table user1;
5.查看表字段
desc user
6.查看表数据
select * from user

mysql数据库设计篇：
1.数据表概念
2.数据字段类型
3.数据字段属性
4.数据表对象管理
5.数据表的类型及存储位置
6.数据表的默认字符集
7.创建索引

表字段类型：
1.数值
int
//int(3)与长度无关，不够3位时前面补0，默认看不见float

2.字符串
char(n) //不可变的最大n个字节 最多255
varchar(n) //可变的占用n个字节 最多255
text //字节最多65535
longtext //42亿字节

3.日期
date
time
datetime
year
timestamp
//用整型时间来替代日期类型，在php中把time()时间戳存到mysql中的int列

字段属性：
1.unsigned
//无符号，全是正数
2.zerofill
//零填充，int(3),不够3位补0
3.auto_increment
//自动增加
4.null
//这一列值允许为null
5.not_null
//这一列值不允许为null
6.default
//默认值

建表语句：
mysql> create table t2(
-> id int unsigned auto_increment primary key,
-> name varchar(30),
-> sex varchar(5) not null default “nv”
-> )default charset=utf8;

用\s查看四种字符集：
Server characterset: utf8
//服务器字符集
Db characterset: latin1
//数据字符集
Client characterset: utf8
//客户端字符集
Conn. characterset: utf8
//客户端连接字符集

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

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

[mysql]
default-character-set=utf8
//客户端和连接字符集

[mysqld]
character-set-server=utf8
//服务器、数据库和表字符集

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

php中设置客户端和连接字符集：
$sql=“set names utf8”;

表字段索引：
1.主键索引PRI
2.普通索引MUL

检测sql语句：
desc select * from t1 where id=3\G //加\G把表颠倒一下
//rows 1

//创建带索引的表
mysql> create table t2(
-> id int unsigned auto_increment,
-> name varchar(30),
-> primary key(id),
-> index in_named(name)
-> )default charset=utf8;

mysql帮助手段：
? show

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

后期维护普通索引：
1.添加普通索引
mysql> alter table t2 add index in_name(name);

2.删除普通索引
mysql> alter table t2 drop index in_named;

后期维护数据表字段：
1.添加字段
alter table t1 add age int;
2.修改字段
alter table t1 modify age int not null default 20;
3.删除字段
alter table t1 drop age;
4.修改字段名
alter table t1 change name username varchar(30);

SQL语言：
1.DDL 数据定义语言，create,drop,alter
2.DML 数据操作语言，insert,update,delete
3.DQL 数据查询语言，select
4.DCL 数据控制语言，grant，commit，rollback

增-insert：
insert into t1(username) values(‘f’);

改-update：
update t1 set username=‘g’ where id=6;

一次性更改多个值,中间用，隔开：
update t1 set id=77,username=‘hh’ where id=7;

删-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=1 or id=3 or id=5;
delete from t1 where id in(1,3,5);

查-select：
1.选择特定的字段
select pass，id from user where id=3;

2.给字段取别名-as
select pass as p,id from user where id=3;
select pass p,id from user where id=3;

3.取掉列中的重复值
select distinct name from user;

4.用where条件进行查询
select * from user where id>=3 and id<=5;

5.查询空空值null
select * from user where age is null;
select * from user where age is not null;

6.between and 的使用方法
select * from user where id between 3 and 5;

7.in的使用方法
select * from user where id=1 or id=2 or id=10;
select * from user where id in(1,2,10); //建议使用

8.搜索like关键字
select * from user where name like ‘%4%’;
select * from user where name regexp ‘.4.’;

select * from user where name like ‘%4%’ or name like ‘%5%’;
select * from user where name regexp ‘(.4.)|(.5.)’;

9.使用oeder by 对查询结果排序
升序排序
select * from user order by id;
select * from user order by id asc;
降序排序
select * from user order by id desc;

10.使用limit限制输出个数
select * from user order by id limit 0,2;
select * from user order by id limit 5;//limit 0,5 前五个

11.concat函数-字符串连接符
select concat(“a”,"-",“b”);

12.rand函数-随机排序
select * fron user order by rand() limit 3;

13.count统计
select count(*) tot from user;
select count(id) from user;
select count(id) from user where name=“user4”; //user4发的帖子数

14.sum求和
select sum(id) from user where name=“user4”; //符合要求的id之和

15.avg平均值
select avg(id) from user;

16.max最大值
select max(id) from user;

17.min最小值
select min(id) from user;

18.分组聚合
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

19.多表查询
1.普通查询-多表（优先选择）
2.嵌套查询-多表
3.左连接查询-多表

需求：论坛用户和帖子统计
user表
mysql> create table user(
-> id int unsigned auto_increment primary key,
-> name varchar(30),
-> age int)default charset=utf8;

post表
mysql> create table post(
-> id int unsigned auto_increment primary key,
-> uid int,
-> title varchar(200),
-> content text)default charset=utf8;

结果1-查看谁发了什么帖子
结果2-查看谁发了多少个帖子

左连接查询-多表
select user.name,post.title from user left join post on user.id=post.uid;

嵌套查询
//得到发帖子的人-普通查询
select distinct user.name from user,post where user.id=post.uid;

//得到发帖子的人-嵌套查询
select name from user where id in(select uid from post);

php操作数据库
1.通过PHP连接上mysql数据库
2.选择数据库
3.通过PHP进行insert操作
4.通过PHP进行delete操作
5.通过PHP进行update操作
6.通过PHP进行select操作

通过PHP连接上mysql数据库：
mysql_connect(“localhost”,“root”,“root”);

选择数据库：
mysql_select_db(“test”);

设置客户端和连接字符集：
mysql_query(“set names utf8”);