SQL语句分类:
a.DDL 数据定义语言 create drop alter
b.DML 数据操作语言 select insert update delete
b.DCL 数据控制语言 grant revoke
c.TCL 事务控制语言 transaction(begin) commit rollback
创建一个库:
mysql> create database dba;
进入数据:
mysql> use dba;
创建一张表:
create table user(id int not null auto_increment,
name varchar(8),
birthday datetime,
constraint pk__person primary key(id));
查看表结构:
mysql> desc user;
+----------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(8) | YES | | NULL | |
| birthday | datetime | YES | | NULL | |
+----------+------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
插入语句:
mysql> insert into user(id,name,birthday) values(1,'lisi','1991-04-26');
Query OK, 1 row affected (0.01 sec)
查询表内容:
mysql> select * from user;
+----+------+---------------------+
| id | name | birthday |
+----+------+---------------------+
| 1 | lisi | 1991-04-26 00:00:00 |
+----+------+---------------------+
1 row in set (0.00 sec)
AUTO_INCREMENT说明:
(1)如果把一个NULL插入到一个AUTO_INCREMENT数据列里去,MySQL将自动生成下一个序列编号。编号从1开始,并1为基数递增。
(2)把0插入AUTO_INCREMENT数据列的效果与插入NULL值一样。但不建议这样做,还是以插入NULL值为好。
(3)当插入记录时,没有为AUTO_INCREMENT明确指定值,则等同插入NULL值。
(4)当插入记录时,如果为AUTO_INCREMENT数据列明确指定了一个数值,则会出现两种情况,情况一,如果插入的值与已有的编号重复,则会出现出错信息,因为AUTO_INCREMENT数据列的值必须是唯一的;情况二,如果插入的值大于已编号的值,则会把该数据插入到数据列中,并使在下一个编号将从这个新值开始递增。也就是说,可以跳过一些编号。
(5)如果用UPDATE命令更新自增列,如果列值与已有的值重复,则会出错。如果大于已有值,则下一个编号从该值开始递增。
创建表的语句(id int not null auto_increment)。
mysql> insert into user(id,name,birthday) values(null,'zhangsan','1992-04-26');
Query OK, 1 row affected (0.02 sec)
mysql> select * from user;
+----+----------+---------------------+
| id | name | birthday |
+----+----------+---------------------+
| 1 | lisi | 1991-04-26 00:00:00 |
| 2 | zhangsan | 1992-04-26 00:00:00 |
+----+----------+---------------------+
2 rows in set (0.00 sec)
如果省去id这一列,insert数据发现也是成功的.
mysql> insert into user(name,birthday) values('wangwu','1993-07-26');
Query OK, 1 row affected (0.02 sec)
mysql> select * from user;
+----+----------+---------------------+
| id | name | birthday |
+----+----------+---------------------+
| 1 | lisi | 1991-04-26 00:00:00 |
| 2 | zhangsan | 1992-04-26 00:00:00 |
| 3 | wangwu | 1993-07-26 00:00:00 |
+----+----------+---------------------+
3 rows in set (0.00 sec)
mysql> desc user;
+----------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(8) | YES | | NULL | |
| birthday | datetime | YES | | NULL | |
+----------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
注意:插入数据时,数字可以不加单引号(''),但字符串必须加单引号('').
_________________________________________________________________________________________________________________
mysql update用法:
update语法:
update 表名 set 列1=新值,列2=新值 where expr.
mysql> select * from user;
+----+----------+---------------------+
| id | name | birthday |
+----+----------+---------------------+
| 1 | lisi | 1991-04-26 00:00:00 |
| 2 | zhangsan | 1992-04-26 00:00:00 |
| 3 | wangwu | 1993-07-26 00:00:00 |
+----+----------+---------------------+
3 rows in set (0.00 sec)
mysql> update user set birthday = '2018-1-15' where name='zhangsan';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user;
+----+----------+---------------------+
| id | name | birthday |
+----+----------+---------------------+
| 1 | lisi | 1991-04-26 00:00:00 |
| 2 | zhangsan | 2018-01-15 00:00:00 |
| 3 | wangwu | 1993-07-26 00:00:00 |
+----+----------+---------------------+
3 rows in set (0.00 sec)
mysql> update user set name='lucy' where birthday='1993-07-26';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user;
+----+----------+---------------------+
| id | name | birthday |
+----+----------+---------------------+
| 1 | lisi | 1991-04-26 00:00:00 |
| 2 | zhangsan | 2018-01-15 00:00:00 |
| 3 | lucy | 1993-07-26 00:00:00 |
+----+----------+---------------------+
3 rows in set (0.00 sec)
update操作多个列的数据时,中间使用逗号','分割开.
mysql> update user set name='saluya',birthday='2010-01-11' where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user;
+----+--------+---------------------+
| id | name | birthday |
+----+--------+---------------------+
| 1 | lisi | 1991-04-26 00:00:00 |
| 2 | saluya | 2010-01-11 00:00:00 |
| 3 | lucy | 1993-07-26 00:00:00 |
+----+--------+---------------------+
3 rows in set (0.00 sec)
当update操作并没有匹配到where条件时,update操作不会报错.
mysql> update user set birthday='1991-06-25' where name='wangtian';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> select * from user;
+----+----------+---------------------+
| id | name | birthday |
+----+----------+---------------------+
| 1 | lisi | 1991-04-26 00:00:00 |
| 2 | zhangsan | 2018-01-15 00:00:00 |
| 3 | wangwu | 1993-07-26 00:00:00 |
+----+----------+---------------------+
3 rows in set (0.00 sec)