常用的mysql语句

1.关于python和mysql对接异常

信息： raise ImproperlyConfigured("Error loading MySQLdb module: %s" % e)
django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module: dlopen(/Library/Python/2.7/site-packages/_mysql.so, 2): Library not loaded: /usr/local/opt/mysql/lib/libmysqlclient.20.dylib

解决方式：
sudo ln -s /Users/liushuqing/Documents/mysql-5.7.13-osx10.11-x86_64/lib/libmysqlclient.20.dylib /usr/local/opt/mysql/lib/libmysqlclient.20.dylib

2.启动与杀死mysql

ps -ef | grep mysqld#查看是否在运行

sudo start mysql #手动的话这样启动

sudo stop mysql  #杀死mysql

3.查看建表语句：

show create table web_platform;  
或者 
desc（describe） web_platform;  

4.创建新用户:

//只允许本地连接
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password’;  
//可以在任何地方连接
 CREATE USER username@'%' IDENTIFIED BY 'password';

5.为新用户授权:

//给某个用户授予在本地连接某个数据库的权限
GRANT ALL ON dbName.* TO 'username'@'localhost’;  
//给某个用户授予在任何地方连接某个数据库的权限
GRANT ALL ON dbName.* TO ‘username’@'%';  

6.修改用户密码:

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');  
//密码修改修改生效：
flush privileges;  

7.登录

//本地
mysql -u root -p密码  
//远程
mysql -h IPAddress -P 3306 -u username -ppassword  

8.导入sql语句

mysql -h localhost -u root -p dbName < filePath/fileName.sql  

9.关于乱码

#别去改配置文件了,一定会出错,应该在创建数据库的时候，指明编码格式
//创建一个utf-8的数据库 

CREATE DATABASE dbName DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;  

10.几条建库建表语句

 CREATE DATABASE test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

 CREATE TABLE if not exists user(

  id int(11) PRIMARY KEY AUTO_INCREMENT,

  password varchar(50) NOT NULL,

  name varchar(100) DEFAULT NULL,

  created_at TIMESTAMP NOT NULL,

  updated_at TIMESTAMP NOT NULL,

  leval int(11) NOT NULL,

  description varchar(300) DEFAULT NULL

); 

CREATE TABLE if not exists web_searchspider_results (

   id int(11) PRIMARY KEY AUTO_INCREMENT,

  platform varchar(20) references web_platform(name), 

  keyword varchar(50) NOT NULL,

  resultUrl varchar(500) DEFAULT NULL,

  targetUrl varchar(500) NOT NULL,

  targetTitle varchar(500) DEFAULT NULL,

  username varchar(100) DEFAULT NULL,

  fetchCode varchar(10) DEFAULT NULL,

  createDate date NOT NULL,

  searchTask_id int(11) references web_searchtask(id),

  status int(11) NOT NULL,

  processDate datetime DEFAULT NULL,

  project_id int(11) references web_project(id),

  checkStatus int(11) DEFAULT NULL

);