windows 环境变量配置: paht:C:\Program Files\MySQL\MySQL Server 5.1\bin
create database xxx charset gbk;
初始化语句或建表sql文件
mysql下执行sql文件:
source 路径/sql文件
1、mysql -uxxx -pxxx;
2、show databases;
3、use database;
4、show tables;
5、show engines\G; 显示存储引擎
6、mysql -uroot -p12345 dbName < 1.sql 执行sql文件
7、create user comuser identified by 'comuser4321';
select user,host,password from mysql.user; 查看用户
delete from mysql.user where user = 'comuser'and host ='%';
8、在Mysql 众多表中查找一个表名或者字段名的 SQL 语句:
SELECT table_name, column_name from information_schema.columns WHERE column_name LIKE 'Name';
下面两种方法也可以查到:
SELECT column_name from information_schema.columns WHERE column_name LIKE ’%searchTerm%’ AND table_schema = ‘yourDB’
SELECT column_name from information_schema.columns WHERE column_name LIKE ’%searchTerm%’ AND table_schema = ‘yourDB’ AND table_name = ‘yourDBTable’
10、导出sql
mysqldump -u用戶名 -p密码 -d 數據库名 表名 脚本名;
1、导出数据库dbname的表结构(其中用戶名為root,密码為dbpasswd,生成的脚本名為db.sql)
mysqldump -uroot -pdbpasswd -d dbname >db.sql;
2、导出数据库dbname某张表(test)结构
mysqldump -uroot -pdbpasswd -d dbname test>db.sql;
3、导出数据库dbname所有表结构及表數據(不加-d)
mysqldump -uroot -pdbpasswd dbname >db.sql;
4、导出数据库dbname某张表(test)结构及表數據(不加-d)
mysqldump -uroot -pdbpasswd dbname test>db.sql;
11、显示表结构或者列
SHOW CREATE TABLE tablename
SHOW FULL COLUMNS FROM tablename
show create procedure getseq; 显示存过过程代码
12、mysql建库时设置默认编码
GBK: create database xxx DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
UTF8: CREATE DATABASE xxx DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
若是已经建好的数据库可使用:
alter database opensource default character set 'utf8';
alter table `opensource`.`t_books` character SET 'utf8';
添加带注释的列:
alter table directory add index_url varchar(256) default null comment '章节书目链接' after dir_url;
set names 'utf8'
编码查看:
show variables like "%colla%";
13、linux下mysql大小写不敏感配置
/etc/my.cnf
# The MySQL server
[mysqld]
default-character-set=gbk
lower_case_table_names=1
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
14、noAccessToProcedureBodies
用户没有获得所需的元数据的权限来确定存储过程的参数类型,应用程序中调用存储过程可能会出错,
可设置noAccessToProcedureBodies=true;
jdbc:mysql://x.x.x.x:3306/database?noAccessToProcedureBodies =true&useUnicode =true&characterEncoding=utf-8