MYSQL数据库设计的一些小技巧[转载]

常用数据操作语言DML笔记(select insert update delete)

select 语句 高级的查询功能,见下面的详细内容

select 列名1,列名2 ... from 表名1,表名2...
[where 条件] like
[group by...]
[having ...]
[order by...] 以特定的顺序显示 例:order by name asc;以名字显示,为降序排列

insert 语句 MySQL 当记录不存在时插入 insert if not exists.在 MySQL 中,插入(insert)一条记录很简单,但是一些特殊应用,在插入记录前,需要检查这条记录是否已经存在,只有当记录不 存在时才执行插入操作,本文介绍的就是这个问题的解决方案.

insert into 表名(列名1,列名2,... )values(值1,值2,...)

update 语句

update 表名 set 列名1=值1,列名2=值2 ... [where 条件]

delete 语句

delete from 表名 [where 条件] 注:条件时会删除整个表名

truncate 语句

truncate tables 表名 
# 注:相当删除整个表名,在重建.非常快

从一个文件加载数据到 MYSQL

load data infile '文件' into tables 表名 [fields terminated by '字符']

导出 MYSQL 数据成一个文件

select * from 表名 into outfile '文件'

DDL数据定义语言(vreate tabe,drop table,altre table)

MYSQL 的字符类型要注意 CHAR VARCHAR TEXT 不分大小写,只能存字符 ,BLOB可以存二进制的内容,如果声音,文件. CHAR主要是定长,VARCHAR是变长,所以VARCHAR更加节约空间.

查看表结构字段

desc tablesname;

添加新的表字段

alter table test add name varchar(20);  #添加一个name的字段
alter table test add f_name varchar(20) after id;  #在id之后添加一个name的字段
alter table test add sid int first;           #在最前面加入一个字段
alter table test add primary key(id);  #添加一个primary主键

删除表中的字段

alter table test drop name; #删除
alter table test add primary key; #删除主键

设置表中字段的默认值

alter table test alter name set default '不知道';

修改字段名

alter table test change name l_anme varchar(10);  #修改字段名,数据类型要写上

数据库插入

insert  #表名 values
insert into tmp values(default,'kate',0,20);#可以使用default.
insert into tmp(name,sex,age) values('katess',0,20);

表的导入导出

insert tmp2 select name,sex from tmp where id<4;  #可以给select 中得到的内容放到tmp2的新表中

更新 MYSQL 表中的字段

UPDATE <表名> SET <列名 = 更新值> [WHERE <更新条件>]
update tmp set address='北京' where id between 4 and 5

删除表中特定的内容

DELETE FROM tmp WHERE id >5  #删除id大于5的内容
DELETE FROM ss1,ss2 using ss1,ss2  where ss1.id=ss2.id and  ss1.name='alex' #二张表ss1和ss2相同的id中内容有alex的内容删除

高级查询

常用查询函数 concat(字符连接) distinct(去掉重复数据) as(别名) limit 3,2 (控制显示数,如前现示从第三个起,拿二个数据)

集函数 count(统计) sun age

查找(条件内容查找)

select 'abc' like 'abc%';     
# %任意字符  _单个字符最少要出现一次

MYSQL 中使用正则来查找数据

select 'abc' regexp '^a.*c$';

排序查询结果 使用排序功能,和使用别名功能

SELECT * FROM student ORDER BY sage DESC ; #降排序order by要放在最后<
SELECT * FROM student ORDER BY  sdept in('信息系','美术系')  #同时二个条件
SELECT sno AS '学号', sname AS '姓名' FROM student;

分组

select cid,avg(grad) from sc group by cid;
# 在分组中 group by要放在最后,要是要加条件的话,集函数要使用having来换where.放在group by后

多表查询

select s.sname,g.grade form sc as g, student as s where s.sname='扶%' and s.sno=g.sid;
select s.sname,g.grade from sc as g inner join student as s on s.sno=g.sid;
# 内连接,同连接显示相同的内容,要加inner给whereis 改成on
# 如果有的数据没有,可以换left,和right以第一个表为基础来排数据,inner join是二个表都有的内容.

子查询

select sname,sage from student where sno not in (select sid from sc) ;
select sname from student where sno in (select)

在 MYSQL 中查询,要先转义

#name=mysql_escape_string($name); mysql_escape_string

FAQ:

1. 查看和修改设置 MYSQL 默认编码

SHOW CHARACTER SET;查看支持的所有字
show variables like 'character_set_%';
SHOW VARIABLES LIKE 'collation_%';

让 MYSQL 重起也可以使用utf8

[mysqld]
default-character-set=utf
[mysql]
default-character-set=utf8

2. 怎么样进行 MYSQL 备份

MYSQL 的备份可用命令mysqldump ,使用方法很简单,

$ mysqldump -u 用户名 -p (密码) -h 主机名 数据库名 >路径/备份名.bak

同时也可以是用mysqldump备份数据结构(tablename.sql)和数据(tablename.txt)

$ mysqldump -u 用户名  -p (密码)  -h 主机名 数据库名 tablename1 tablename2 > back.sql

mysqldump -u 用户名 -p (密码) -h 主机名 数据库名 –tab 路径 –opt 数据库名. EXA:

$ mysqldump -u xxxx -p xxxxt databases  > ./news.sql

3. 改变mysql管理员的密码

方法1: 在/usr/local/mysql/bin/下:

$ mysqladmin -u root password 'new_password'

一般安装时用此方法设置.

方法2: 在mysql状态下:

mysql>UPDATE user SET password=PASSWORD('new_password') WHERE user='root';
mysql>FLUSH PRIVILEGES;

Method 3:

mysql>SET PASSWORD FOR root=PASSWORD('new_password');

4. 什么情况下会导致 MYSQL 数据库损坏和怎么修复 MYSQL

mysql 正在运行的时候,服务器突然断电或者直接按reset键重启,硬盘空间不够,导致数据写不进去,也很有可能导致数据表损坏,物理硬盘有损坏.主要是这几个原因,mysql修复方法如下

可以把mysql停掉,用mysql的命令myisamchk来修复,这种修复方法是最好的.

具体命令如下:

$ myisamchk -r database/*.MYI
$ myisamchk -o database/*.MYI
$ myisamchk -f database/*.MYI

参数 -r, -o , -f 是递进关系,一般首先用-r修复,然后-o , -f

5. mysql的导出导入

$ mysqldump -uroot -p mysql >pcti15.sql
$ mysql -uroot -p pcti >pcti15.sql

使用source命令,后面参数为脚本文件(如这里用到的.sql)

mysql>source d:\backup_db.sql

6. 设置进入时的默认编码

mysql -uroot -p --default-character-set=utf8

问题:我创建了一个表来存放客户信息,我知道可以用 insert 语句插入信息到表中,但是怎么样才能保证不会插入重复的记录呢? 答案:可以通过使用 EXISTS 条件句防止插入重复记录. 示例一:插入多条记录 假设有一个主键为 client_id 的 clients 表,可以使用下面的语句: Code:

INSERT INTO clients 

(client_id, client_name, client_type) 
SELECT supplier_id, supplier_name, ‘advertising’ 
FROM suppliers 
WHERE not exists (select * from clients 
where clients.client_id = suppliers.supplier_id); 

示例一:插入单条记录 Code:

INSERT INTO clients 
(client_id, client_name, client_type) 
SELECT 10345, ‘IBM’, ‘advertising’ 
FROM dual 
WHERE not exists (select * from clients 
where clients.client_id = 10345); 

使用 dual 做表名可以让你在 select 语句后面直接跟上要插入字段的值,即使这些值还不存在当前表中.

MySQL 连接的状态信息

我们常常需要看一些连接的信息,如下可以显示相关的信息:

mysql> show status like '%onnect%'; 
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         | 8960  |
| Connections              | 31530 |
| Max_used_connections     | 111   |
| Ssl_client_connects      | 0     |
| Ssl_connect_renegotiates | 0     |
| Ssl_finished_connects    | 0     |
| Threads_connected        | 73    |
+--------------------------+-------+

Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。 Connections 试图连接MySQL服务器的次数。 Threads_connected 当前打开的连接的数量. 设置的连接数可以通过下面这个查看

show variables like 'max_connections';

如果想修改的话,修改 /etc/my.cnf 找到max_connections一行,修改为(如果没有,则自己添加)

max_connections = 1000

临时修改此参数的值, 注意大小写

set GLOBAL max_connections=1000;

查询表的格式

SHOW TABLE STATUS WHERE ROW_FORMAT LIKE 'Compact'

查询 binlog 转换成可读 mysqlbinlog mysql-bin.000002 -vvvv –base64-output=DECODE-ROWS

原文发布于微信公众号 - php(phpdaily)

原文发表时间:2014-12-07

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏个人分享

hiveql笔记(一)

create table if not exists mydb.employees{

652
来自专栏乐沙弥的世界

替代变量与SQL*Plus环境设置

scott@ORCL> select * from emp where empno=7788;

562
来自专栏闻道于事

C语言课程设计(成绩管理系统)

C语言课程设计(成绩管理系统) 翻到了大学写的C语言课程设计,缅怀一下 内容: 增加学生成绩 查询学生成绩 删除 按照学生成绩进行排序 等 1 #inclu...

3725
来自专栏C/C++基础

MySQL错误:ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY

MySQL版本:Ver 14.14 Distrib 5.1.61, for redhat-linux-gnu (x86_64)

813
来自专栏乐沙弥的世界

SQL*Plus copy 命令处理大批量数据复制

    对于数据库表级上的数据复制,我们最常用的是CREATE TABLE AS(CTAS)..方式。其实在SQL*Plus下面copy命令可以完成同样的工作,...

681
来自专栏Hongten

python开发_sqlite3_绝对完整_博主推荐

=========================================

724
来自专栏.net core新时代

数据字典生成工具之旅(8):SQL查询表的约束默认值等信息

      上一篇代码生成工具里面已经用到了读取表结构的SQL,这篇将更加详细的介绍SQL SERVER常用的几张系统表和视图! 阅读目录 系统表视图介绍 实际...

1797
来自专栏云计算教程系列

想熟悉PostgreSQL?这篇就够了

PostgreSQL是自由的对象-关系型数据库服务器,在灵活的BSD风格许可证下发行。它在其他开放源代码数据库系统和专有系统之外,为用户又提供了一种选择。 我们...

1362
来自专栏云计算教程系列

如何管理SQL数据库

安装SQL数据库时,需要添加,修改,删除和查询数据所需的所有命令。这个备忘单样式指南提供了一些最常用的SQL命令的快速参考。

732
来自专栏文渊之博

SQL Server 2016 行级别权限控制

背景 假如我们有关键数据存储在一个表里面,比如人员表中包含员工、部门和薪水信息。只允许用户访问各自部门的信息,但是不能访问其他部门。一般我们都是在程序端实现这个...

18510

扫码关注云+社区