mysql基本使用

1. Windows下，使用MySQL的时候需要先启动服务。

2.打开命令行窗口

3. 输入密码进入(1234)

4，show databases，共有4个数据库

5. -------use mysql; 进入名字为mysql的数据库

--------show tables; 显示当前数据库中的table

6. 查看user表的结构：----desc user; describe的缩写。

规定了每一列的类型和字符串的长度，某个字段是否可以为空，primary key主键，一般只有一个主键，出现2个，表示它们是复合键(联合主键)。主键默认是不能为空的。

7. 查看表里的数据---select * from userG

8. 查看当前所有用户：select user()

9. grant select,insert,update,delete,create,drop

grant all on test.* to 'alex'@'%' identified by 'alex3714'(test下所有的表)

10. 创建用户：

11.总结：基本使用

select version()  # 查看版本
mysqladmin -uroot -p password  # 设密码(shell环境下用此句)
show grants for root@'localhost'  # 查看用户权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '123456' WITH GRANT OPTION;  # 授权
flush privileges;
mysql -u root -p  # 连接
show databases;  # 查看所有库
use mysql       # 进入mysql库
show tables;     # 查看库里的所有表
desc user;       # 查看表结构；同SHOW COLUMNS FROM user;
select * from user;  # 查看所有数据
select * from userG # 格式化显示

show create database test;  # 查看创建
create database testdb charset "utf8";  # 创建支持中文testdb库
SHOW INDEX FROM 数据表 # 显示表的详细索引信息，包括PRIMARY KEY（主键）
show columns form user; 查看列
create database oldboydb; 新建数据库
create database oldboydb charset utf8; 新建数据库，可以写中文的。
drop database oldboydb; 删除数据库
create table table_name(column_name column_type);创建table

二，MySQL数据类型

1、数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
tinyint	1 字节	(-128，127)	(0，255)	小整数值
smallint	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
mediumint	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
int或integer	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
bigint	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
float	4 字节	(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
double	8 字节	(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
decimal	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

2、日期和时间类型

类型	大小(字节)	范围	格式	用途
date	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
time	3	‘-838:59:59’/’838:59:59’	HH:MM:SS	时间值或持续时间
year	1	1901/2155	YYYY	年份值
datetime	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
timestamp	4	1970-01-01 00:00:00/2037 年某时	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

3、字符串类型

类型	大小	用途
char	0-255字节	定长字符串
varchar	0-65535 字节	变长字符串
tinyblob	0-255字节	不超过 255 个字符的二进制字符串
tinytext	0-255字节	短文本字符串
blob	0-65 535字节	二进制形式的长文本数据
text	0-65 535字节	长文本数据
mediumblob	0-16 777 215字节	二进制形式的中等长度文本数据
mediumtext	0-16 777 215字节	中等长度文本数据
longblob	0-4 294 967 295字节	二进制形式的极大文本数据
longtext	0-4 294 967 295字节	极大文本数据

三，mysql 常用命令

MySQL 创建数据表

语法

create table table_name (column_name column_type);

创建一个student表

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 )
);

建表说明：

如果你不想字段为 null 可以设置字段的属性为 not null，在操作数据库时如果输入该字段的数据为null ，就会报错。
auto_increment 定义列为自增的属性，一般用于主键，数值会自动加1。
primary key 关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔。

mysql 插入数据

insert into table_name ( field1, field2,...fieldN )
                       values
                       ( value1, value2,...valueN );

插入数据

mysql> insert into student (name,age,register_date) values ("ZhangYang",3,"2017-09-8");

MySQL 查询数据

select语句的语法格式如下：

select selection_list                  // 要查询的内容，选择哪些列
from table_list                        // 从什么表中查询，从何处选择行
where primary_constraint               // 查询时需要满足的条件，行必须满足的条件
group by grouping_columns              // 如何对结果进行分组
order by sorting_columns               // 如何对结果进行排序
having secondary_constraint            // 查询时满足的第二条件
limit count                            // 限定输出的查询结果
[offset M ]                            // 指定select语句开始查询的数据偏移量，默认情况下偏移量为0。
[limit N]                              // 使用limit属性来设定返回的记录数。

详解如下：

MySQL offset/limit 子句

select column_name from table_name [offset M ][limit N]

MySQL where 子句

select field1, field2,...fieldN from table_name1, table_name2...
[where condition1 [and [or]] condition2.....
# %表示匹配任意

以下为操作符列表，可用于 where 子句中。
下表中实例假定 A为10 B为20

操作符	描述	实例
=	等号，检测两个值是否相等，如果相等返回true	(A = B) 返回false。
<>, !=	不等于，检测两个值是否相等，如果不相等返回true	(A != B) 返回 true。
>	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true	(A > B) 返回false。
<	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true	(A < B) 返回 true。
>=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true	(A >= B) 返回false。
<=	小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true	(A <= B) 返回 true。

使用主键来作为 where 子句的条件查询是非常快速的。

MySQL update 更新

语法

update table_name set field1=new-value1, field2=new-value2
[where where_definition]
[order by ...]
[limit row_count]

　update语法可以用新值更新原有表行中的各列。

　　set子句指示要修改哪些列和要给予哪些值。

　　where子句指定应更新哪些行。如果没有where子句，则更新所有的行。

　　如果指定了order by子句，则按照被指定的顺序对行进行更新。

　　limit子句用于给定一个限值，限制可以被更新的行的数目。

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

MySQL delete 语句

delete from table_name [where where_condition]

delete from student where stu_id=5;

MySQL like 子句

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"; #只匹配大写

MySQL 排序

select field1, field2,...fieldN from 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;

MySQL group by 语句

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

MySQL sum 语句 rollup是归纳的意思

select name,sum(daily_typing_pages) from employee_tb group by name;
select name,sum(daily_typing_pages) as new_name from employee_tb group by name;
select name,sum(daily_typing_pages) as new_name from employee_tb group by name with rollup;

with rollup用法：

MySQL alter命令:修改表本身

删除，添加或修改表字段

alter table student drop register_date; #从student表删除register_date字段
alter table student add phone int(11) not null; #添加phone字段
枚举类型enum, 要慎用这个字段

修改字段类型及名称

alter table testalter_tbl modify c char(10); #只是修改了数据类型
alter table testalter_tbl change i j bigint;  #把字段名和数据类型都可以修改
alter table testalter_tb1 modify j bigint not null default 100; #设置默认值

修改表名

mysql> alter table testalter_tbl rename to alter_tbl;

关于外键

外键，一个特殊的索引，用于关联2个表，只能是指定内容.

不建议用自动编号字段作为外键关系约束的字段。

alter table 表名 add constraint FK_ID foreign key(你的外键字段名) references  外表表名(对应的表的主键字段名);

//FK_ID是外键的名称.一个对象的名字而已。外键也是一种数据库对象，所以它也有名字，虽然这个名字看上去用处不大。 但当删除一个外键的时候就需要这个名字了。

出错时方便找到出错原因.不写的话，引擎会自动给这个外键赋值。

//外键名称在建立外键时可以自定义名称，如果不自定义，会按照mysql自动生成一个名称.

MySQL null 值处理（空数据）

我们已经知道MySQL使用 SQL select 命令及 where 子句来读取数据表中的数据,但是当提供的查询条件字段为 null 时，该命令可能就无法正常工作。 
 为了处理这种情况，MySQL提供了三大运算符:
•is null: 如果当列的值是null,则此运算符返回true。
•is not null: 如果当列的值不为null, 则运算符返回true。
•<=>: 比较操作符（不同于=运算符），当比较的的两个值为null时返回true。

关于 null 的条件比较运算是比较特殊的。你不能使用 = null 或 != null 在列中查找 null 值 。 
 在MySQL中，null值与任何其它值的比较（即使是null）永远返回false，即 null = null 返回false 。 
MySQL中处理NULL使用is null和is not null运算符。

多表连接查询

join 按照功能大致分为如下三类：
•inner join（内连接,或等值连接）：获取两个表中字段匹配关系的记录。其实就是2个表的交集。
•left join（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
•right join（右连接）： 与 left join 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
•full join（全连接）： 求并集。select * from a FULL JOIN b on a.a=b.b;mySql 并不直接支持full join.

select * from A left join B on A.a=B.b union select * from A right join B on A.a=B.b;

五、事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
事务用来管理insert,update,delete语句

一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

1、事务的原子性：一组事务，要么成功；要么撤回。
2、稳定性 ：有非法数据（外键约束之类），事务撤回。
3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项决定什么时候吧事务保存到日志里。

在Mysql控制台使用事务来操作

mysql> begin； ＃开始一个事务

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

mysql>rollback; 回滚 ， 这样数据是不会写入的.
mysql>commit; 确定。确定完以后，再写rollback也没法撤销了。

当然如果上面的数据没问题，就输入commit提交命令就行；

六、索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。 打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

索引是新建在一张表里的，里面按照哈希算法进行排序的。如果修改数据的话，势必得重新排序，浪费时间。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 where 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

主键也是一种索引。

普通索引 创建索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

create index indexName ON mytable(username(length));

如果是char，varchar类型，length可以小于字段实际长度；如果是和text类型，必须指定 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;

唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：
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);

使用 alter 命令添加和删除主键

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（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_nameG

七、python 操作mysql

python-mysqldb : pymysql : pymsql是Python中操作MySQL的模块，其使用方法和MySQLdb几乎相同。

7.1安装

7.2

mysql 的游标定义，干什么用的。

游标简单来说就是查询出来的数据索引，通过对游标的操作（第一个位置、最后一个位置、上一个位置、下一个位置）可以遍历出数据。

游标允许应用程序对查询语句select  返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力；
而且，正是游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够进行沟通。

实例

import pymysql

# 创建连接，指明了：客户端（主机，端口，用户名，密码）和服务器端（要连接的数据库）。
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='oldboydb')
# 创建游标
cursor = conn.cursor()
print(cursor.fetchone()) #打印取到的第1行数据

# 执行SQL，并返回受影响行数
effect_row = cursor.execute("select * from student")

# 执行SQL，并返回受影响行数
#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))

# 执行SQL，并返回受影响行数
data=[("N1","2015-05,22",'M'),("N2","2015-5-21",'F'),]
#effect_row = cursor.executemany("insert into student(name,register_date,gender)values(%s,%s,%s)", data)


# 提交，不然无法保存新建或者修改的数据
conn.commit()

# 关闭游标
cursor.close()
# 关闭连接
conn.close()

2、获取查询数据

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='1234', db='oldboydb')
cursor = conn.cursor()
cursor.execute("select * from student")

# 获取第一行数据
row_1 = cursor.fetchone()

# 获取前n行数据
# row_2 = cursor.fetchmany(3)
# 获取所有数据
# row_3 = cursor.fetchall()

conn.commit()
cursor.close()
conn.close()

3.cursor就像读取数据库的光标一样。是依次往下的。

程序：

import pymysql
conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='1234',db='oldboydb')
cursor=conn.cursor()
cursor.execute("select * from student")

row_1=cursor.fetchone()
row_2=cursor.fetchone()
print(row_1)
print(row_2)
conn.commit()
cursor.close()
conn.close()

运行结果：

C:abccdxdddOldboypython-3.5.2-embed-amd64python.exe C:/abccdxddd/Oldboy/Py_Exercise/Day12/ex1.py
(1, 'Alex', datetime.date(2014, 5, 21))
(2, 'Jack', datetime.date(2014, 3, 21))

Process finished with exit code 0

参考源：http://blog.csdn.net/fgf00/article/details/52976250