python学习笔记12-mysql、orm
一、mysql基本操作
安装、改密码、授权等略
1、创建数据库
语法：CREATE DATABASE [DATABASENAME] CHARSET='utf8';
mysql> CREATE DATABASE testdb charset='utf8'; Query OK, 1 row affected (0.00 sec)
2、创建表
语法：CREATE TABLE [TABLENAME] (column1 TYPE(LENGTH) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT] [PRIMARY KEY],column2 TYPE(LENGTH)...);
字段数据类型：TYPE
NOT NULL:设置该字段是否可以为空
DEFAULT value：设置默认值
AUTO_INCREMENT：设置自增
PRIMARY KEY：设置是否为主键
mysql> create table test1 ( id int not null auto_increment primary key, name char(32) not null, status char(32) not null default "yes"); Query OK, 0 rows affected (0.02 sec)
3、数据增删改查
增：
语法：INSERT INTO [TABLENAME](column1,column2,...,columnn) VALUES(value1,value2,...,valuen);
mysql> insert into test1(name,status) values("张三","NO"); Query OK, 1 row affected (0.01 sec)
删：
语法：DELETE FROM [TABLENAME] [WHERE CONDITION]
mysql> DELETE FROM test1 where name="张三"; Query OK, 1 row affected (0.01 sec)
查：
语法：SELECT * FROM TABLENAME [WHERE CONDITION];
SELECT column1,column2,...,column FROM TABLENAME [WHERE CONDITION];
mysql> select * from test1; +----+--------+--------+ | id | name | status | +----+--------+--------+ | 2 | 李四 | NO | | 3 | 王五 | NO | +----+--------+--------+ 2 rows in set (0.00 sec) mysql> select * from test1 where name="李四"; +----+--------+--------+ | id | name | status | +----+--------+--------+ | 2 | 李四 | NO | +----+--------+--------+ 1 row in set (0.01 sec)
改：
语法：UPDATE TABLENAME SET column="value" [WHERE CONDITION];
mysql> update test1 set status="YES" where name="李四"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from test1; +----+--------+--------+ | id | name | status | +----+--------+--------+ | 2 | 李四 | YES | | 3 | 王五 | NO | +----+--------+--------+ 2 rows in set (0.00 sec)
4、数据库、表的增删改查
查数据库：
语法：SHOW DATABASES;
查数据库中的表：
语法：SHOW TABLES;
查表结构：
语法：DESC TABLENAME;
查建表语句：
语法：SHOW CREATE TABLE TABLENAME;
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | | testdb | +--------------------+ 4 rows in set (0.00 sec) mysql> show tables; +------------------+ | Tables_in_testdb | +------------------+ | test1 | +------------------+ 1 row in set (0.00 sec) mysql> use testdb Database changed mysql> show create table test1\G *************************** 1. row *************************** Table: test1 Create Table: CREATE TABLE `test1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(32) NOT NULL, `status` char(32) NOT NULL DEFAULT 'yes', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
改表字段类型：
语法：ALTER TABLE TABLENAME MODIFY column TYPE(LENGTH);
改表字段名称：
语法：ALTER TABLE TABLENAME CHANGE oldcolumn newcolumn TYPE(LENGTH);
改表名：
语法：ALTER TABLE TABLENAME RENAME TO NEWNAME;
mysql> ALTER TABLE test1 MODIFY name char(64); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> desc test1; +--------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(64) | YES | | NULL | | | status | char(32) | NO | | yes | | +--------+----------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) mysql> ALTER TABLE test1 CHANGE status STATUS char(32); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> DESC test1; +--------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(64) | YES | | NULL | | | STATUS | char(32) | YES | | NULL | | +--------+----------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> ALTER TABLE test1 RENAME TO TEST2; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +------------------+ | Tables_in_testdb | +------------------+ | TEST2 | +------------------+ 1 row in set (0.00 sec)
增加一个字段：
语法：ALTER TABLE TABLENAME ADD newcolumn TYPE(LENGTH),[NOT NULL] [DEFAULT]...
mysql> ALTER TABLE TEST2 ADD addr char(64) NOT NULL; Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> DESC TEST2; +--------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(64) | YES | | NULL | | | STATUS | char(32) | YES | | NULL | | | addr | char(64) | NO | | NULL | | +--------+----------+------+-----+---------+----------------+ 4 rows in set (0.01 sec)
删除一个字段：
语法：ALTER TABLE TABLENAME DROP column;
删除一张表：
语法：DROP TABLE TABLENAME;
删除一个数据库：
语法：DROP DATABASE DATABASENAME;
mysql> DESC TEST2; +--------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(32) | NO | | NULL | | | status | char(32) | NO | | yes | | | addr | char(64) | NO | | NULL | | +--------+----------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> ALTER TABLE TEST2 DROP addr; Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> DESC TEST2; +--------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(32) | NO | | NULL | | | status | char(32) | NO | | yes | | +--------+----------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) mysql> DROP TABLE TEST2; Query OK, 0 rows affected (0.00 sec) mysql> SHOW TABLES; Empty set (0.01 sec) mysql> DROP DATABASE testdb; Query OK, 0 rows affected (0.01 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+ 3 rows in set (0.00 sec)
5、排序和分组
排序语法：SELECT * FROM TABLENAME ORDER BY column [ASC|DESC];
ASC:降序排列
DESC:升序排列
mysql> select * from test1; +----+--------+------+--------+ | id | name | addr | status | +----+--------+------+--------+ | 1 | 张三 | bj | YES | | 2 | 李四 | sh | YES | | 3 | 李四 | bj | NO | +----+--------+------+--------+ 3 rows in set (0.00 sec) mysql> select * from test1 order by name; +----+--------+------+--------+ | id | name | addr | status | +----+--------+------+--------+ | 1 | 张三 | bj | YES | | 2 | 李四 | sh | YES | | 3 | 李四 | bj | NO | +----+--------+------+--------+ 3 rows in set (0.00 sec) mysql> select * from test1 order by name asc; +----+--------+------+--------+ | id | name | addr | status | +----+--------+------+--------+ | 1 | 张三 | bj | YES | | 2 | 李四 | sh | YES | | 3 | 李四 | bj | NO | +----+--------+------+--------+ 3 rows in set (0.00 sec) mysql> select * from test1 order by name desc; +----+--------+------+--------+ | id | name | addr | status | +----+--------+------+--------+ | 2 | 李四 | sh | YES | | 3 | 李四 | bj | NO | | 1 | 张三 | bj | YES | +----+--------+------+--------+ 3 rows in set (0.00 sec)
分组统计：
语法：SELECT column,func() FROM TABLENAME GROUP BY column;
SELECT column,func() as RESULTNAME FROM TABLENAME GROUP BY column WITH ROLLUP;
SELECT coalesce(column,RESULTNAME),func() FROM TABLENAME GROUP BY column WITH ROLLUP;
mysql> select * from test1; +----+--------+------+--------+ | id | name | addr | status | +----+--------+------+--------+ | 1 | 张三 | bj | YES | | 2 | 李四 | sh | YES | | 3 | 李四 | bj | NO | +----+--------+------+--------+ 3 rows in set (0.00 sec) mysql> select name,count(*) from test1 group by name; +--------+----------+ | name | count(*) | +--------+----------+ | 张三 | 1 | | 李四 | 2 | +--------+----------+ 2 rows in set (0.00 sec) mysql> select name,count(*) as "数量" from test1 group by name with rollup; +--------+--------+ | name | 数量 | +--------+--------+ | 张三 | 1 | | 李四 | 2 | | NULL | 3 | +--------+--------+ 3 rows in set (0.00 sec) mysql> select coalesce(name,"总数"),count(*) as "数量" from test1 group by name with rollup; +-------------------------+--------+ | coalesce(name,"总数") | 数量 | +-------------------------+--------+ | 张三 | 1 | | 李四 | 2 | | 总数 | 3 | +-------------------------+--------+ 3 rows in set (0.00 sec)
未完待续
