SQL 学习笔记

mysql 远程连接

1. 登陆mysql 后 修改登陆用户名密码
   mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root');
   其中：’root’@’localhost’ 分别为登陆的用户名和ip，后面root 为新密码
2. 设置本地 mysql 可以 远程登陆
   登入mysql后，更改 “mysql” 数据库里的 “user” 表里的 “host” 项，从”localhost”改称”%”

> mysql -u root -pmysql> use mysql;mysql>update user set host = '%' where user = 'root';mysql>select host, user from user;mysql>flush privileges;

数据库 读取与插入

# 连接指定数据库connect =pymysql.connect(host='localhost',user='root',passwd='passwd',db='dbname',charset='utf8')cursor = connect.cursor()cursor.execute('select wechat_id from wechat_member')wechat_id = cursor.fetchall() # 返回 tuple 的 tuple# print(wechat_id)with open('wechat_id.txt','w+') as f:  for tup in wechat_id:    f.write(tup[0]+'\n')cursor.close()connect.close()# ---------------------------------------------------------------------------  def __init__(self):    self.connect = pymysql.connect(host='localhost', user='root', passwd='passwd', db='dbname', charset='utf8')    self.cursor = self.connect.cursor()  def insert_data(self, lst):    sql = 'insert into weibo_user VALUES ("{}","{}","{}","{}")'.format(lst[0], lst[1], lst[2], lst[3])    # print(sql)    self.cursor.execute(sql)  def closeMysql(self):    self.cursor.close()    self.connect.close()

mysql for Excel

MySQL for Excel简介
Download MySQL for Excel
Visual Studio 2010 Tools for Office Runtime

---

使用SQL查询所有数据库名和表名

MySQL 查询语句

1. 查询数据库中所有表对应的记录数：

USE information_schema;  SELECT      table_name,      table_rows  FROM      TABLES  WHERE      table_schema = 'book'  ORDER BY      table_rows DESC;  

SQL Server 查询语句

1. 查询数据库中所有表名：select name from sysobjects where xtype='U' ORDER BY name
2. 查询数据库中所有表对应的记录数：select o.name,i.rows from sysobjects o,sysindexes i where o.id=i.id and o.Xtype='U' and i.indid<2 ORDER BY name

SQL Server 语法

1. 灵活运用 SQL SERVER FOR XML PATH
2. SQLServer中ISNULL、NULLIF和CONVERT函数