yum list installed mysql*

rpm -qa | grep mysql*

yum list mysql*

yum install mysql

yum install mysql-server

yum install mysql-devel

service mysqld start

chkconfig -add mysqld

chkconfig --list | grep mysql*

service mysqld stop

mysqladmin -u root password 666666

service mysqld stop

mysqld_safe --user=root --skip-grant-tables

mysql -u root

use mysql

update user set password=password("666666") where user="root";

flush privileges;

use mysql;

UPDATE user SET `Host` = '%' WHERE `User` = 'root' LIMIT 1;

root@host# mysql -u root -p

Enter password:*******

mysql> use mysql;

Database changed

mysql> INSERT INTO user

          (host, user, password,

           select_priv, insert_priv, update_priv)

           VALUES ('localhost', 'guest',

           PASSWORD('guest123'), 'Y', 'Y', 'Y');

Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 1 row affected (0.01 sec)

mysql> SELECT host, user, password FROM user WHERE user = 'guest';

+-----------+---------+------------------+

| host      | user    | password         |

+-----------+---------+------------------+

| localhost | guest | 6f8c114b58f2ce9e |

+-----------+---------+------------------+

1 row in set (0.00 sec)

root@host# mysql -u root -p password;

Enter password:*******

mysql> use mysql;

Database changed

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

    -> ON TUTORIALS.*

    -> TO 'zara'@'localhost'

    -> IDENTIFIED BY 'zara123';　

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

[mysql.server]

user=mysql

basedir=/var/lib

[safe_mysqld]

err-log=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

CREATE TABLE table_name (column_name column_type);

create table student(

   stu_id INT NOT NULL AUTO_INCREMENT,

   name CHAR(32) NOT NULL,

   age  INT NOT NULL,

   register_date DATE,

   PRIMARY KEY ( stu_id )

);

INSERT INTO table_name ( field1, field2,...fieldN )

                       VALUES

                       ( value1, value2,...valueN );

mysql> insert into student (name,age,register_date) values ("cheng",25,"1992-10-23")

    -> ;

Query OK, 1 row affected (0.00 sec)

mysql> select * from student;

+--------+---------+-----+---------------+

| stu_id | name    | age | register_date |

+--------+---------+-----+---------------+

|      1 | cheng   |  25 | 1992-10-23    |

+--------+---------+-----+---------------+

1 row in set (0.00 sec)

SELECT column_name,column_name

FROM table_name

[WHERE Clause]

[OFFSET M ][LIMIT N]

mysql> select * from student limit 3 offset 2;

+--------+---------+-----+---------------+

| stu_id | name    | age | register_date |

+--------+---------+-----+---------------+

|      3 | alex li |  24 | 2016-03-04    |

|      4 | alex li |  24 | 2016-03-01    |

|      5 | alex li |  24 | 2016-03-02    |

+--------+---------+-----+---------------+

3 rows in set (0.00 sec)

比如这个SQL ，limit后面跟的是3条数据，offset后面是从第3条开始读取

mysql> select * from student limit 3 ,1;

+--------+---------+-----+---------------+

| stu_id | name    | age | register_date |

+--------+---------+-----+---------------+

|      4 | alex li |  24 | 2016-03-01    |

+--------+---------+-----+---------------+

1 row in set (0.00 sec)

而这个SQL，limit后面是从第3条开始读，读取1条信息。

SELECT field1, field2,...fieldN FROM table_name1, table_name2...

[WHERE condition1 [AND [OR]] condition2.....

select * from student where register_date > '2016-03-04';

UPDATE table_name SET field1=new-value1, field2=new-value2

[WHERE Clause]

1

update student set age=22 ,name="Alex Li" where stu_id>3;

DELETE FROM table_name [WHERE Clause] delete from student where stu_id=5;

SELECT field1, field2,...fieldN table_name1, table_name2...

WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

select *from student where name binary like "%Li";

select *from student where name binary like  binary "%Li"; #只匹配大写

SELECT field1, field2,...fieldN table_name1, table_name2...

ORDER BY field1, [field2...] [ASC [DESC]]

使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。

select *from student where name like binary "%Li" order by stu_id desc;

SELECT column_name, function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name;

mysql> SELECT * FROM employee_tbl;

+----+--------+---------------------+--------+

| id | name   | date                | singin |

+----+--------+---------------------+--------+

|  1 | 小明 | 2016-04-22 15:25:33 |      1 |

|  2 | 小王 | 2016-04-20 15:25:47 |      3 |

|  3 | 小丽 | 2016-04-19 15:26:02 |      2 |

|  4 | 小王 | 2016-04-07 15:26:14 |      4 |

|  5 | 小明 | 2016-04-11 15:26:40 |      4 |

|  6 | 小明 | 2016-04-04 15:26:54 |      2 |

+----+--------+---------------------+--------+

接下来我们使用 GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录：

mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;

+--------+----------+

| name   | COUNT(*) |

+--------+----------+

| 小丽 |        1 |

| 小明 |        3 |

| 小王 |        2 |

+--------+----------+

3 rows in set (0.01 sec)

使用 WITH ROLLUP

mysql> SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;

+--------+--------------+

| name   | singin_count |

+--------+--------------+

| 小丽 |            2 |

| 小明 |            7 |

| 小王 |            7 |

| NULL   |           16 |

+--------+--------------+

4 rows in set (0.00 sec)

其中记录 NULL 表示所有人的登录次数。

我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：

mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;

alter table student drop register_date; #从student表删除register_date字段

alter table student add phone int(11) not null; ＃添加phone字段

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

mysql> ALTER TABLE testalter_tbl

    -> MODIFY j BIGINT NOT NULL DEFAULT 100;

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

mysql> create table class(

    -> id  int not null primary key,

    -> name char(16));

Query OK, 0 rows affected (0.02 sec)

CREATE TABLE `student2` (

  `id` int(11) NOT NULL,

  `name` char(16) NOT NULL,

  `class_id` int(11) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `fk_class_key` (`class_id`),

  CONSTRAINT `fk_class_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)

)

此时如果class 表中不存在id 1,student表也插入不了，这就叫外键约束

mysql> insert into student2(id,name,class_id) values(1,'alex', 1);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdb`.`student2`, CONSTRAINT `fk_class_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))

mysql> insert into class(id,name) values(1,"linux");

Query OK, 1 row affected (0.01 sec)

mysql> insert into student2(id,name,class_id) values(1,'alex', 1);

Query OK, 1 row affected (0.00 sec)

＃如果有student表中跟这个class表有关联的数据，你是不能删除class表中与其关联的纪录的

mysql> delete from class where id =1;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`testdb`.`student2`, CONSTRAINT `fk_class_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))

A    B

-    -

1    3

2    4

3    5

4    6

select * from a INNER JOIN b on a.a = b.b;

select a.*,b.*  from a,b where a.a = b.b;

a | b

--+--

3 | 3

4 | 4

select * from a LEFT JOIN b on a.a = b.b;

a |  b

--+-----

1 | null

2 | null

3 |    3

4 |    4

select * from a RIGHT JOIN b on a.a = b.b;

a    |  b

-----+----

3    |  3

4    |  4

null |  5

null |  6

select * from a FULL JOIN b on a.a = b.b;

 a   |  b

-----+-----

   1 | null

   2 | null

   3 |    3

   4 |    4

null |    6

null |    5

select * from a left join b on a.a = b.b UNION select * from a right join b on a.a = b.b;

+------+------+

| a    | b    |

+------+------+

|    3 |    3 |

|    4 |    4 |

|    1 | NULL |

|    2 | NULL |

| NULL |    5 |

| NULL |    6 |

+------+------+

6 rows in set (0.00 sec)

mysql> begin； ＃开始一个事务

mysql> insert into a (a) values(555);

mysql>rollback; 回滚 ， 这样数据是不会写入的

CREATE INDEX indexName ON mytable(username(length));

ALTER mytable ADD INDEX [indexName] ON (username(length))

CREATE TABLE mytable( 

ID INT NOT NULL,  

username VARCHAR(16) NOT NULL, 

INDEX [indexName] (username(length)) 

);

DROP INDEX [indexName] ON mytable;

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

创建表的时候直接指定

CREATE TABLE mytable( 

ID INT NOT NULL,  

username VARCHAR(16) NOT NULL, 

UNIQUE [indexName] (username(length)) 

);

有四种方式来添加数据表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

以下实例为在表中添加索引。

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:

mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;

mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

删除指定时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

mysql> SHOW INDEX FROM table_name\G

pip3 install pymysql

#!/usr/bin/env python

# -*- coding:utf-8 -*-

import pymysql

# 创建连接

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')

# 创建游标

cursor = conn.cursor()

# 执行SQL，并返回收影响行数

effect_row = cursor.execute("update hosts set host = '1.1.1.2'")

# 执行SQL，并返回受影响行数

#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))

# 执行SQL，并返回受影响行数

#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])

# 提交，不然无法保存新建或者修改的数据

conn.commit()

# 关闭游标

cursor.close()

# 关闭连接

conn.close()

#!/usr/bin/env python

# -*- coding:utf-8 -*-

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')

cursor = conn.cursor()

cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])

conn.commit()

cursor.close()

conn.close()

# 获取最新自增ID

new_id = cursor.lastrowid

#!/usr/bin/env python

# -*- coding:utf-8 -*-

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')

cursor = conn.cursor()

cursor.execute("select * from hosts")

# 获取第一行数据

row_1 = cursor.fetchone()

# 获取前n行数据

# row_2 = cursor.fetchmany(3)

# 获取所有数据

# row_3 = cursor.fetchall()

conn.commit()

cursor.close()

conn.close()

#!/usr/bin/env python

# -*- coding:utf-8 -*-

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')

# 游标设置为字典类型

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

r = cursor.execute("call p1()")

result = cursor.fetchone()

conn.commit()

cursor.close()

conn.close()

MySQL-Python

    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>

pymysql

    mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

MySQL-Connector

    mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>

cx_Oracle

    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]

更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html

pip install SQLAlchemy

pip install pymysql  #由于mysqldb依然不支持py3，所以这里我们用pymysql与sqlalchemy交互

CREATE TABLE user (

    id INTEGER NOT NULL AUTO_INCREMENT,

    name VARCHAR(32),

    password VARCHAR(64),

    PRIMARY KEY (id)

)

import sqlalchemy

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String

engine = create_engine("mysql+pymysql://root:alex3714@localhost/testdb",

                                    encoding='utf-8', echo=True)

Base = declarative_base() #生成orm基类

class User(Base):

    __tablename__ = 'user' #表名

    id = Column(Integer, primary_key=True)

    name = Column(String(32))

    password = Column(String(64))

Base.metadata.create_all(engine) #创建表结构

from sqlalchemy import Table, MetaData, Column, Integer, String, ForeignKey

from sqlalchemy.orm import mapper

metadata = MetaData()

user = Table('user', metadata,

            Column('id', Integer, primary_key=True),

            Column('name', String(50)),

            Column('fullname', String(50)),

            Column('password', String(12))

        )

class User(object):

    def __init__(self, name, fullname, password):

        self.name = name

        self.fullname = fullname

        self.password = password

mapper(User, user) #the table metadata is created separately with the Table construct, then associated with the User class via the mapper() function

Session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例

Session = Session_class() #生成session实例

user_obj = User(name="alex",password="alex3714") #生成你要创建的数据对象

print(user_obj.name,user_obj.id)  #此时还没创建对象呢，不信你打印一下id发现还是None

Session.add(user_obj) #把要创建的数据对象添加到这个session里， 一会统一创建

print(user_obj.name,user_obj.id) #此时也依然还没创建

Session.commit() #现此才统一提交，创建数据

my_user = Session.query(User).filter_by(name="alex").first()

print(my_user)

<__main__.User object at 0x105b4ba90>

print(my_user.id,my_user.name,my_user.password)

输出

1 alex alex3714

def __repr__(self):

    return "<User(name='%s',  password='%s')>" % (

        self.name, self.password)

my_user = Session.query(User).filter_by(name="alex").first()

my_user.name = "Alex Li"

Session.commit()

my_user = Session.query(User).filter_by(id=1).first()

my_user.name = "Jack"

fake_user = User(name='Rain', password='12345')

Session.add(fake_user)

print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all() )  #这时看session里有你刚添加和修改的数据

Session.rollback() #此时你rollback一下

print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all() ) #再查就发现刚才添加的数据没有了。

# Session

# Session.commit()

print(Session.query(User.name,User.id).all() )

objs = Session.query(User).filter(User.id>0).filter(User.id<7).all()

Session.query(User).filter(User.name.like("Ra%")).count()

from sqlalchemy import func

print(Session.query(func.count(User.name),User.name).group_by(User.name).all() )

SELECT count(user.name) AS count_1, user.name AS user_name

FROM user GROUP BY user.name

from sqlalchemy import ForeignKey

from sqlalchemy.orm import relationship

class Address(Base):

    __tablename__ = 'addresses'

    id = Column(Integer, primary_key=True)

    email_address = Column(String(32), nullable=False)

    user_id = Column(Integer, ForeignKey('user.id'))

    user = relationship("User", backref="addresses") #这个nb，允许你在user表里通过backref字段反向查出所有它在addresses表里的关联项

    def __repr__(self):

        return "<Address(email_address='%s')>" % self.email_address

obj = Session.query(User).first()

for i in obj.addresses: #通过user对象反查关联的addresses记录

    print(i)

addr_obj = Session.query(Address).first()

print(addr_obj.user.name)  #在addr_obj里直接查关联的user表

obj = Session.query(User).filter(User.name=='rain').all()[0]

print(obj.addresses)

obj.addresses = [Address(email_address="r1@126.com"), #添加关联对象

                 Address(email_address="r2@126.com")]

Session.commit()

from sqlalchemy import Integer, ForeignKey, String, Column

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import relationship

Base = declarative_base()

class Customer(Base):

    __tablename__ = 'customer'

    id = Column(Integer, primary_key=True)

    name = Column(String)

    billing_address_id = Column(Integer, ForeignKey("address.id"))

    shipping_address_id = Column(Integer, ForeignKey("address.id"))

    billing_address = relationship("Address")

    shipping_address = relationship("Address")

class Address(Base):

    __tablename__ = 'address'

    id = Column(Integer, primary_key=True)

    street = Column(String)

    city = Column(String)

    state = Column(String)

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join

condition between parent/child tables on relationship

Customer.billing_address - there are multiple foreign key

paths linking the tables.  Specify the 'foreign_keys' argument,

providing a list of those columns which should be

counted as containing a foreign key reference to the parent table.

class Customer(Base):

    __tablename__ = 'customer'

    id = Column(Integer, primary_key=True)

    name = Column(String)

    billing_address_id = Column(Integer, ForeignKey("address.id"))

    shipping_address_id = Column(Integer, ForeignKey("address.id"))

    billing_address = relationship("Address", foreign_keys=[billing_address_id])

    shipping_address = relationship("Address", foreign_keys=[shipping_address_id])

#一本书可以有多个作者，一个作者又可以出版多本书

from sqlalchemy import Table, Column, Integer,String,DATE, ForeignKey

from sqlalchemy.orm import relationship

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import create_engine

from sqlalchemy.orm import sessionmaker

Base = declarative_base()

book_m2m_author = Table('book_m2m_author', Base.metadata,

                        Column('book_id',Integer,ForeignKey('books.id')),

                        Column('author_id',Integer,ForeignKey('authors.id')),

                        )

class Book(Base):

    __tablename__ = 'books'

    id = Column(Integer,primary_key=True)

    name = Column(String(64))

    pub_date = Column(DATE)

    authors = relationship('Author',secondary=book_m2m_author,backref='books')

    def __repr__(self):

        return self.name

class Author(Base):

    __tablename__ = 'authors'

    id = Column(Integer, primary_key=True)

    name = Column(String(32))

    def __repr__(self):

        return self.name

Session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例

s = Session_class() #生成session实例

b1 = Book(name="跟Alex学Python")

b2 = Book(name="跟Alex学把妹")

b3 = Book(name="跟Alex学装逼")

b4 = Book(name="跟Alex学开车")

a1 = Author(name="Alex")

a2 = Author(name="Jack")

a3 = Author(name="Rain")

b1.authors = [a1,a2]

b2.authors = [a1,a2,a3]

s.add_all([b1,b2,b3,b4,a1,a2,a3])

s.commit()

mysql> select * from books;

+----+------------------+----------+

| id | name             | pub_date |

+----+------------------+----------+

|  1 | 跟Alex学Python   | NULL     |

|  2 | 跟Alex学把妹     | NULL     |

|  3 | 跟Alex学装逼     | NULL     |

|  4 | 跟Alex学开车     | NULL     |

+----+------------------+----------+

4 rows in set (0.00 sec)

mysql> select * from authors;

+----+------+

| id | name |

+----+------+

| 10 | Alex |

| 11 | Jack |

| 12 | Rain |

+----+------+

3 rows in set (0.00 sec)

mysql> select * from book_m2m_author;

+---------+-----------+

| book_id | author_id |

+---------+-----------+

|       2 |        10 |

|       2 |        11 |

|       2 |        12 |

|       1 |        10 |

|       1 |        11 |

+---------+-----------+

5 rows in set (0.00 sec)

print('--------通过书表查关联的作者---------')

book_obj = s.query(Book).filter_by(name="跟Alex学Python").first()

print(book_obj.name, book_obj.authors)

print('--------通过作者表查关联的书---------')

author_obj =s.query(Author).filter_by(name="Alex").first()

print(author_obj.name , author_obj.books)

s.commit()

--------通过书表查关联的作者---------

跟Alex学Python [Alex, Jack]

--------通过作者表查关联的书---------

Alex [跟Alex学把妹, 跟Alex学Python]

author_obj =s.query(Author).filter_by(name="Jack").first()

book_obj = s.query(Book).filter_by(name="跟Alex学把妹").first()

book_obj.authors.remove(author_obj) #从一本书里删除一个作者

s.commit()

author_obj =s.query(Author).filter_by(name="Alex").first()

# print(author_obj.name , author_obj.books)

s.delete(author_obj)

s.commit()