我的MySQL使用手记
近期在windows下使用MySQL,简单的记录下开发中遇到的一些问题
1. char,varchar和text的区别
char(M) | varchar(M) | text |
---|---|---|
定长 | 变长 | 变长 |
M个字节,0 <=M<= 255 | L+1个字节,其中L<=M且0 <=M<= 65535 | L+2个字节,其中L< 216 |
对英文(ASCII)字符占用1个字节,对一个汉字占用2个字节 | 对每个英文(ASCII)字符都占用2个字节,对一个汉字也只占用两个字节 | 储存不区分大小写的字符数据,每个占2字节 |
2.MySQL数据库备份
>mysqldump -u user_name -p database_name > D:\backup.sql
然后按照提示输入密码即可。
注:愉快地使用mysqldump有两种方法
1.进入mysql安装路径下的bin目录,找到mysqldump可执行程序,cmd命令行窗口执行
2.[推荐]将mysql的bin目录加到系统变量path中,任何位置下都能使用该命令
3.数据库备份的操作步骤
- 如果table存在,Drop
- 创建该表,Create
- 对该表进行加锁写保护,Lock
- 插入原始数据,Insert
- Done
4.MySQL执行sql脚本
- 未登录状态下
>mysql -h localhost -u user_name -p database_name < D:\backup.sql
- 登录状态下(两种)
mysql>source D:\backup.sql
mysql>\. D:\backup.sql
5. 文本中有单引号导致数据插入失败
mysql用两个连着的单引号”来标识单引号’,或者使用转移字符反斜杠\
所以插入数据之前应该先对数据中的特殊字符做转义处理
6.MySQL输入密码后闪退,无法启动MySQL服务
查看MySQL数据目录Data下的Computer_name.err日志文件,显然这是因为端口3306被占用。错误信:息如下
[ERROR] Do you already have another mysqld server running on port: 3306 ?
[ERROR] Aborting找到占用端口的进程Pid,kill掉,再次启动mysql服务
>netstat -aon|findstr "3306"
>taskkill /f /pid 3306
7.MySQL中Delete,Truncate和Drop的区别
- Delete
- Delete操作后相应的数据被删除,但当执行roolback操作后可以恢复数据
- 这是对数据的一种快速修改、因为在删除时并不合并索引
- Limit,where和Order By可以和delete一起使用
- Truncate
- Truncate先删除表结构然后再新建,这比一行一行的删除数据要快很多。
- Truncate执行后不可回滚操作
- Truncate会删除表中的所有数据
- Truncate并不会返回删除数据的行数
- Drop
- Drop与Delete的区别就在于,Delete删除了表中的数据但是表的结构还在,而Drop会将表的数据和结构一起删除
8.数据库基本操作语法
#创建数据库
create database database_name;
#使用指定数据库
use database_name;
#显示所有表
show tables;
#创建表
create table table_name(id int unsigend not null primary key ,name ……);
#选择
select * from table_name where id = '1'
#插入
insert into table_name(id,……)values('2',……)
#设置自增字段的初始值
alter table table_name auto_increment =1;
#在列id之后插入新列
alter table table_name add column column_name int after id;
#更新一个字段的多行数据
update tablename
set field= case other_field
when 0 then 'value'
when 1 then 'value'
when 2 then 'value'
end
where other_field in(0,1,2)
#修改字段的属性
alter table table_name modify id int auto_increment;
#删除表
drop table table_name;
9.excel数据导入到mysql数据库
- 按照数据库中字段的排列顺序,排列excel中数据的列
- 将excel中的数据copy到txt文件中,命名为data.txt,位置D盘
- mysql命令行中执行如下命令:
>>>load data local infile 'D:\data.txt' into table table_name fields terminated by '\t';
Done
9.MySQL数据库删除重复数据行
有时候发现数据库中的数据有重复,这个时候要删除其中的一行.比如id为主键,col_name列有重复数据
>>>delete from table_name where id not in (select max(id) from table_name group by col_name);
上面的语句在SQL Server和Oracle数据库执行是没有问题的,但是MySQL对这一操作做了限制,会报错:
You can’t specify target table table_name for update in FROM clause
也就是说在一个语句中不能先select某些值然后再对表执行更新操作。
这样解决:
>>>delete from table_name where id not in(select * from(select max(id) from table_name group by col_name)as tmp);
当然可以在删除操作之前查询要删除的数据有哪些
>>>select id from table_name where id not in(select * from(select max(id) from table_name group by col_name)as tmp);
10.MySQL数据库删除所有表中的数据
要删除所有表中的数据但是保留表的结构,有点像对数据库执行Truncate操作。步骤如下:
1.使用mysqldump输出数据库备份脚本
2.使用grep过滤出DROP开头的一行,内容应该是:DROP TABLE IF EXISTS `Table_name`;
3.将获得的行用sed替换为Truncate table Table_name;
4.使用sed将对视图的操作过滤掉
5.将得到的语句输出到mysql -u root -ppassword db_name中执行
>>>mysqldump -u root -ppassword db_name | grep ^DROP |sed "s/DROP TABLE IF EXISTS /Truncate table /g" | sed "s/\`/ /g"| sed "s/.*_v/ /g" |mysql -u root -ppassword db_name