数据库的增删改查
针对库的增删改查
-
切换数据库
语法: use 数据库名
-
增
语法: create database 数据库名; mysql> create database db1; Query OK, 1 row affected (0.02 sec)
-
删
语法: drop database 库名;
-
改
最好不要修改数据库名
-
查
查询所有的数据库; 语法: show databases; 查看当前所在库; 语法: select database();
针对表的增删改查
-
增
语法: create table 表1(字段1 类型), (字段2 类型); mysql> create table t1(id int, name varchar(16)); Query OK, 0 rows affected (0.09 sec)
-
删
语法: drop table 表1;
-
改
语法: 1.修改表名:alter table 表1 rename 表2; 2.增加字段 alter table 表1 add 字段1 数据类型; 3.修改字段类型 alter table 表1 modify 字段1 类型; 4. 删除字段 alter table 表1 drop 字段名; 5. 修改字段 alter table 表1 change 旧字段名 新字段名 旧数据类型 alter table 表1 change 旧字段名 新字段名 新数据类型 mysql> alter table t1 rename t2; Query OK, 0 rows affected (0.04 sec) mysql> show tables; +---------------+ | Tables_in_db1 | +---------------+ | t2 | +---------------+ 1 row in set (0.00 sec) mysql> alter table t2 add age int; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(16) | YES | | NULL | | | age | int | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table t2 modify name varchar(16); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(16) | YES | | NULL | | | age | int | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table t2 drop age; Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(16) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> alter table t2 change name name1 int; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t2; +-------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name1 | int | YES | | NULL | | +-------+------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql>
-
查
语法: desc 表1; show tables; show create tables 表名; mysql> desc t1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(16) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> show tables; +---------------+ | Tables_in_db1 | +---------------+ | t2 | +---------------+ 1 row in set (0.00 sec) mysql>
记录的增删改查
-
增
1.插入完整数据(顺序插入) insert into 表1(字段1, 字段2) values(值1,值2) insert into 表1 values(值1, 值2) 2.指定插入数据 insert into 表1(字段1) values (值1) 3.插入多条记录 insert into 表1 values (值1, 值2), (值1, 值2);
-
删
delete from 表1 where 条件(可以用and或者or连接)
-
改
update 表1 set 字段1=值1 where 条件;
-
查
select *from 表1; select 字段1,字段2 from 表1;
-
mysql> insert into t2(id, name1) values(1, 123); Query OK, 1 row affected (0.01 sec) mysql> show tables; +---------------+ | Tables_in_db1 | +---------------+ | t2 | +---------------+ 1 row in set (0.00 sec) mysql> select * from t2; +------+-------+ | id | name1 | +------+-------+ | 1 | 123 | +------+-------+ 1 row in set (0.00 sec) mysql> insert into t2 values(2, 23); Query OK, 1 row affected (0.01 sec) mysql> select * from t2; +------+-------+ | id | name1 | +------+-------+ | 1 | 123 | | 2 | 23 | +------+-------+ 2 rows in set (0.00 sec) mysql> insert into t2 values (3, 34), (4, 45); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t2; +------+-------+ | id | name1 | +------+-------+ | 1 | 123 | | 2 | 23 | | 3 | 34 | | 4 | 45 | +------+-------+ 4 rows in set (0.00 sec) mysql> delete from t2 where id=2; Query OK, 1 row affected (0.01 sec) mysql> select * from t2; +------+-------+ | id | name1 | +------+-------+ | 1 | 123 | | 3 | 34 | | 4 | 45 | +------+-------+ 3 rows in set (0.00 sec) mysql> update t2 set id =2 where id=3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t2; +------+-------+ | id | name1 | +------+-------+ | 1 | 123 | | 2 | 34 | | 4 | 45 | +------+-------+ 3 rows in set (0.00 sec)
-