Mysql 创建数据表

　　创建一个学生表

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 ("alex li",22,"2016-03-4")    -> ;Query OK, 1 row affected (0.00 sec) mysql> select * from student;+--------+---------+-----+---------------+| stu_id | name    | age | register_date |+--------+---------+-----+---------------+|      1 | alex li |  22 | 2016-03-04    |+--------+---------+-----+---------------+1 row in set (0.00 sec)

 

Mysql 查询数据

　　语法

SELECT column_name,column_nameFROM table_name[WHERE Clause][OFFSET M ][LIMIT N]　　

• 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
• SELECT 命令可以读取一条或者多条记录。
• 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
• 你可以使用 WHERE 语句来包含任何条件。
• 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
• 你可以使用 LIMIT 属性来设定返回的记录数。

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条信息。

 

　　Mysql where 语句

　　语法

SELECT field1, field2,...fieldN FROM table_name1, table_name2...[WHERE condition1 [AND [OR]] condition2.....

　　以下为操作符列表，可用于 WHERE 子句中。

　　下表中实例假定 A为10 B为20

　　

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

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

 

 　　Mysql update 查询

　　语法

UPDATE table_name SET field1=new-value1, field2=new-value2[WHERE Clause]update student set age=22 ,name="Alex Li" where stu_id>3;

　　Mysql delete 语句

　　语法

DELETE FROM table_name [WHERE Clause]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 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_nameWHERE column_name operator valueGROUP 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 ROLLUPmysql> 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;

 

Mysql alter 命令

　　我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。

　　删除，添加或修改表字段

alter table student drop register_date;              #从student表删除register_date   字段 alter table student add phone int(11) not null;      ＃添加phone字段

 　　修改字段类型及名称

　　如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。

　　例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:

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

　　使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT; mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

　　ALTER TABLE 对 Null 值和默认值的影响

　　当你修改字段时，你可以指定是否包含只或者是否设置默认值。

　　以下实例，指定字段 j 为 NOT NULL 且默认值为100 。

mysql> ALTER TABLE testalter_tbl    -> MODIFY j BIGINT NOT NULL DEFAULT 100;

　　修改表名

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

 

外键

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

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表也插入不了，这就叫外键约束＃如果有student表中跟这个class表有关联的数据，你是不能删除class表中与其关联的纪录的

 

mysql -u root -h localhost -p        #mysql登录用户              show databases;                      #查看数据库create database mm;                  #创建库use mm;                                                  #选中库show tables;                         #查看表 create table message(                #创建表      id int primary key not null auto_increment,          username varchar(125),          content text,          publish_date datetime         );desc message                             #查看表结构show creat table message\G;                #方法同上，显示的方式不一样drop table message;                      #删除表drop datatbale mm;                                         #删除数据库

select * from table名               #查看表insert into message(id,content,publish_date) values(1,'python','lalala','2017-04-01')            #表中插入信息select * from message where publish_date > '2017-04-16 '      #根据条件匹配查询select * from message whete publish_date > '2017-04-15' and id >=4;select * from message where publish_date > '2017-04-15' and id >=4 and username = 'kk';select * from message where content like '%kk%'     #两边空格填充select * from message where content is 'kk'  #精确匹配update message set content = 'bbbbbb' where id >= 8    # 修改content的内容                                  update message set content = 'chinese' where content = 'ops';update message set title='我是测试'，content='我是一个测试'，username='test' where titil is null;    #更新多个点的内容  del from message where publish_date <= '2017-04-16 14:22:22'       #根据条件做匹配，然后删除 select * from message order by publish_date desc;   #按publish_date时间排序select count(*) from message where username='kk2';    #计数select ip,count(*) from accesslog group by ip    #统计IP数量select ip,url,status,count(*) from accesslog group by ip ,url , status;select ip , count(*) from accesslog where log_date >= '2017-01-01' adn log_date < '2019-01-01' group by ipselect max(log_date) from accesslog;select min(log_date) from accesslog;select avg(log_date) from accesslog;select date_format(log_date ,'%y-%m-%d') as log_date , ip ,count(*) from accesslog group by log_date,ip;

 

Mysql 链接Join

　　我们已经学会了如果在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。

　　本章节我们将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。

　　你可以在SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

　　JOIN 按照功能大致分为如下三类：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

假设我们有如下的表：

A    B      ----  表名    a     b      -----列名　　　　　　　　　　　　　　　1    32    43    54    6

　　Inner join　　

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 | 34 | 4

　　Left join

select * from a LEFT JOIN b on a.a = b.b; a |  b--+-----1 | null2 | null3 |    34 |    4

　　Right join

select * from a RIGHT JOIN b on a.a = b.b; a    |  b-----+----3    |  34    |  4null |  5null |  6

　　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;+------+------+| a    | b    |+------+------+|    3 |    3 ||    4 |    4 ||    1 | NULL ||    2 | NULL || NULL |    5 || NULL |    6 |+------+------+6 rows in set (0.00 sec)

 

事务

　　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; 回滚 ， 这样数据是不会写入的#当然如果上面的数据没问题，就输入commit提交命令就行；

 

索引

　　MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

　　打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

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

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

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

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

　　显示索引信息

mysql> SHOW INDEX FROM table_name\G

　　创建索引

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

　　创建表的时候直接指定

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   INDEX [indexName] (username(length))   );

　　删除索引

DROP INDEX [indexName] ON mytable;