mysql 实现yyyyww_mysql设计-基本操作

mysql

设计

1)服务器管理

2)数据库管理

3)表管理

4)字段管理

5)索引管理

操作

1)sql语句

2)单表操作

3)多表操作

索引 记录 字段

mysam innodb ibdata1 三种引擎

innodb表引擎

1.user.frm 表字段

2.user.idb 表索引加部分数据

mysql基本命令

1关闭 开启

net start/stop mysql

2查看表结构

desc user

3修改表名

rename table user to user2

mysql数据设计

1.数据表引擎

2.数据值和列类型

3.数据字段属性

4.mysql服务器默认字符集

5.索引管理

表字段类型

1.数值

tinyint (比如存储年龄)

有符号 -128~127

无符号 0~255

smallint mediaini

int(日期一般用数值)

2.字符串类型

最大长度或最大数值

char 0~255(一个汉字三个字符) 一般用在

varchar 0~65535

text 0~65535

varchar text blog是变长类型

3.日期和时间

日期不是时间戳不方便加减乘除

字段属性

1.unsigned 无符号

2.zerofill 0填充

3.auto_increment

mysql> create table t9(

-> id int unsigned auto_increment primary key,

-> username varchar(50)

-> );

4.null

age int null

5.not null

age int not null

6.default

age int not null default 18;

mysql服务器默认字符集

mysql

my.ini

建议客服端字符集utf8

default-character-set = utf8

mysql服务端字符集为utf8

character-set-server = utf8

mysql服务器校验字符集

collation-server = utf8_general_ci (防止排序混乱)

1.utf8

字段管理

1.添加字段

alter table user add age tinyint unsigned not null;

alter table user add pass varchar(30) not null after user;(添加到某个字段之后)

2.删除字段

alter table user drop password;

3.修改字段

alter table user modify username varchar(30) not null;

alter table user change username user varchar(30) not null;

4.查看字段

desc user;

索引

外键带强制性 最好不要

1.主键

添加

mysql> create table t9(

-> id int unsigned auto_increment,

-> username varchar(50)

-> primary key(id)

-> );

删除 (先删除自增再删除主键)

alter table user2 modify id int unsigned not null;

alter table user2 drop primary key;

查看

2.唯一

添加(唯一索引需要名称，前提要有主键，不常见)

alter table user2 add unique u_username(username);

alter table user2 drop index u_username;

3.普通

alter table user2 add index i_username(username);

alter table user2 drop index i_username;

数据库操作

数据定义语言 create drop alter

数据操作语言 insert update delete

数据查询语言 select

数据控制语言 grant commit rollback

desc delete from user where username='user5'\G

判断删除操作需要遍历的row(如果有索引会更快)

条件查询

1.where id>3

2.where id>=3

3.where id>=3 and id<=7

4.where id between 3 and 7

5.where id=3 or id=5 or id=6

6.where id in(1,4,5,7)

update user set username='user33',password='123' where id=2;

数据库查询select

1.select username from user

select * from user

查询一般优化：

1.查单列的速度要优于多列

2.查主键索引的列中的所有值比其他列速度快

选择特定的字段：

select id,username from user;

给字段名起别名

select username as user,password as pass from user;

select username user,password pass from user;

distinct关键字的使用

select distinct password from t1;

like 标题 价格 标签用来查询(会让索引失效)搜素引擎技术

select * from user where username like '%u%';

数据量大的时候会很慢 上千万一亿条数据需要5～10分钟查出来

数据库设计(标签查询)：

课程表：

id name

1 linux服务器运营

2 shell脚本编程

标签：(name加索引)

id name c_id

1 linux 1

2 shell 1

3 unix 1

4 linux 2

5 shell 2

shell脚本编程：

标签：linux,shell

网站搜索技术

1.like

2.标签

3.搜索引擎技术：如sphinx客户端

order by 排序 asc(升序) desc(降序)

limit限定输出个数(分页实现)

select * from user order by id limit 0,2;

select * from user order by id limit 5; //limit 0,5 前五个

delete与truncate区别

delete可以清除表数据 不会清除计数器(自增)

delete from user;

truncate可以清除表数据清除计数器(自增)

truncate user;

mysql常用函数

1.连接函数concat (字符串连接)

select concat(user,pass) from t1;

2.随机数rand函数 随机取出数据

select * from user order by rand() limit 1;

3.统计个数count函数

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

4.求和sum()

slect sum(age) from user;

5.avg() max() min()

6.group by分组聚合 按条件进行分组 然后在分组的基础有条件的聚合

select * group by class;

select concat(class,'班级') as 班级,count(*) as 人数

from t1 group by class;

mysql多表查询

1.普通多表查询

关联字段 表名+id

查询最好用索引 索引占空间 不要随意加索引

需求2：查询每一个学员的姓名，年龄，班级名称和班级创建时间 并把时间戳转换成正常日期显示出来

select user.username,user.age,class.name,from_unixtime(class.ctime) ctime from user,class where user.class_id=class.id;

2.嵌套查询或子查询

缺点：两个表的关系不能体现 一般是取其一

需求3：查询user表中所在的所有班级的信息

select * from class where id in (select distinct class_id from user);

需求4：查询班级表中的每个班的所有学员信息

select * from user where class_id in(select id from class);

3.链接查询

普通多表查询

1)左链接

需求5：在左边的数据全部出来 以左边的表为主导

统计每个班级的总人数

select c.name,count(u.id) count from class c left join user u on c.id=u.class_id group by c.id;

2)右链接

左右链接效果类似

3)内链接 等同于普通多表查询

需求6：查询每个用户对应的用户名,班级名称

select u.username,c.name from user u inner join class c where u.class_id=c.id;