学者斋2016年数据库设计技巧大全
- ACCP
- 关注：1.68W次
下文所讲解的数据库技巧是许多人在大量的数据库分析与设计实践中，逐步总结出来的。对于这些经验的运用，读者不能生帮硬套，死记硬背，而要消化理解，实事求是，灵活掌握。并逐步做到：在应用中发展，在发展中应用。和yjbys一起来学习吧!
常用数据操作语言DML笔记(select insert update delete)
select 语句
高级的查询功能,见下面的详细内容
| 12345 | select列名1,列名2 ...from表名1,表名2...[where条件]like[groupby...][having...][orderby...] 以特定的顺序显示 例:orderbynameasc;以名字显示,为降序排列 |
insert 语句
MySQL 当记录不存在时插入 insert if not exists.在 MySQL 中,插入(insert)一条记录很简单,但是一些特殊应用,在插入记录前,需要检查这条记录是否已经存在,只有当记录不存在时才执行插入操作,本文介绍的就是这个问题的解决方案.
| 1 | insertinto表名(列名1,列名2,... )values(值1,值2,...) |
update 语句
| 1 | update表名set列名1=值1,列名2=值2 ... [where条件] |
delete 语句
| 1 | deletefrom表名 [where条件] 注:条件时会删除整个表名 |
truncate 语句
| 12 | truncatetables 表名# 注:相当删除整个表名,在重建.非常快 |
从一个文件加载数据到 MYSQL
| 1 | loaddata infile'文件'intotables 表名 [fields terminatedby'字符'] |
导出 MYSQL 数据成一个文件
| 1 | select*from表名intooutfile'文件' |
DDL数据定义语言(vreate tabe,drop table,altre table)
MYSQL 的字符类型要注意 CHAR VARCHAR TEXT 不分大小写,只能存字符 ,BLOB可以存二进制的内容,如果声音,文件.
CHAR主要是定长,VARCHAR是变长,所以VARCHAR更加节约空间.
查看表结构字段
| 1 | desctablesname; |
添加新的表字段
| 1234 | altertabletestaddnamevarchar(20); #添加一个name的字段altertabletestaddf_namevarchar(20)afterid; #在id之后添加一个name的'字段altertabletestaddsidintfirst; #在最前面加入一个字段altertabletestaddprimarykey(id); #添加一个primary主键 |
删除表中的字段
| 12 | altertabletestdropname; #删除altertabletestaddprimarykey; #删除主键 |
设置表中字段的默认值
| 1 | altertabletestalternamesetdefault'不知道'; |
修改字段名
| 1 | altertabletest changenamel_anmevarchar(10); #修改字段名,数据类型要写上 |
数据库插入
| 123 | insert#表名valuesinsertintotmpvalues(default,'kate',0,20);#可以使用default.insertintotmp(name,sex,age)values('katess',0,20); |
表的导入导出
| 1 | inserttmp2selectname,sexfromtmpwhereid<4; #可以给select中得到的内容放到tmp2的新表中 |
更新 MYSQL 表中的字段
| 12 | UPDATE<表名>SET<列名 = 更新值> [WHERE<更新条件>]updatetmpsetaddress='北京'whereidbetween4and5 |
删除表中特定的内容
| 12 | DELETEFROMtmpWHEREid >5 #删除id大于5的内容DELETEFROMss1,ss2 using ss1,ss2where=andss1.name='alex'#二张表ss1和ss2相同的id中内容有alex的内容删除 |
高级查询
常用查询函数
concat(字符连接)
distinct(去掉重复数据)
as(别名)
limit 3,2 (控制显示数,如前现示从第三个起,拿二个数据)
集函数
count(统计)
sun
age
查找(条件内容查找)
| 12 | select'abc'like'abc%';# %任意字符 _单个字符最少要出现一次 |
MYSQL 中使用正则来查找数据
| 1 | select'abc'regexp'^a.*c$'; |
排序查询结果
使用排序功能,和使用别名功能
| 123 | SELECT*FROMstudentORDERBYsageDESC; #降排序orderby要放在最后<SELECT*FROMstudentORDERBYsdeptin('信息系','美术系') #同时二个条件SELECTsnoAS'学号', snameAS'姓名'FROMstudent; |
分组
| 12 | selectcid,avg(grad)fromscgroupbycid;# 在分组中groupby要放在最后,要是要加条件的话,集函数要使用having来换where.放在groupby后 |
多表查询
| 1234 | selecte,e form scasg, studentasswheree='扶%'and=;selecte,efromscasginnerjoinstudentasson=;# 内连接,同连接显示相同的内容,要加inner给whereis 改成on# 如果有的数据没有,可以换left,和right以第一个表为基础来排数据,innerjoin是二个表都有的内容. |
子查询
| 12 | selectsname,sagefromstudentwheresnonotin(selectsidfromsc) ;selectsnamefromstudentwheresnoin(select) |
在 MYSQL 中查询,要先转义
#name=mysql_escape_string($name); mysql_escape_string
FAQ:
1. 查看和修改设置 MYSQL 默认编码
| 123 | SHOW CHARACTER SET;查看支持的所有字show variables like'character_set_%';SHOW VARIABLES LIKE'collation_%'; |
让 MYSQL 重起也可以使用utf8
| 1234 | [mysqld]default-character-set=utf[mysql]default-character-set=utf8 |
2. 怎么样进行 MYSQL 备份
MYSQL 的备份可用命令mysqldump ,使用方法很简单,
| 1 | $ mysqldump -u 用户名 -p (密码) -h 主机名 数据库名 >路径/备份名 |
同时也可以是用mysqldump备份数据结构()和数据()
| 1 | $ mysqldump -u 用户名 -p (密码) -h 主机名 数据库名 tablename1 tablename2 > |
mysqldump -u 用户名 -p (密码) -h 主机名 数据库名 –tab 路径 –opt 数据库名.
EXA:
| 1 | $ mysqldump -u xxxx -p xxxxt databases > ./ |
3. 改变mysql管理员的密码
方法1: 在/usr/local/mysql/bin/下：
| 1 | $ mysqladmin -u root password'new_password' |
一般安装时用此方法设置.
方法2:
在mysql状态下：
| 12 | 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来修复,这种修复方法是最好的.
具体命令如下：
| 123 | $ myisamchk -r database/*$ myisamchk -o database/*$ myisamchk -f database/* |
参数 -r, -o , -f 是递进关系,一般首先用-r修复,然后-o , -f
5. mysql的导出导入
| 12 | $ mysqldump -uroot -p mysql >$ mysql -uroot -p pcti > |
使用source命令,后面参数为脚本文件(如这里用到的)
| 1 | mysql>source d:backup_ |
6. 设置进入时的默认编码
| 1 | 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 nt_id = lier_id);
示例一：插入单条记录
Code:
INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, ‘IBM’, ‘advertising’
FROM dual
WHERE not exists (select * from clients
where nt_id = 10345);
使用 dual 做表名可以让你在 select 语句后面直接跟上要插入字段的值,即使这些值还不存在当前表中.
MySQL 连接的状态信息
我们常常需要看一些连接的信息,如下可以显示相关的信息:
| 010203040506070809101112 | mysql> show statuslike'%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 当前打开的连接的数量.
设置的连接数可以通过下面这个查看
| 1 | show variableslike'max_connections'; |
如果想修改的话,修改 /etc/ 找到max_connections一行，修改为(如果没有，则自己添加)
| 1 | max_connections = 1000 |
临时修改此参数的值, 注意大小写
| 1 | set GLOBAL max_connections=1000; |
查询表的格式
| 1 | SHOWTABLESTATUSWHEREROW_FORMATLIKE'Compact' |
查询 binlog 转换成可读
mysqlbinlog mysql-bin.000002 -vvvv –base64-output=DECODE-ROWS
- 文章版权属于文章作者所有，转载请注明 https://xuezhezhai.com/itrz/accp/rvodo3.html
