书接上回，重新进入mysql，继续学习新内容。
1.进入mysql并新建数据库
sudo mysql -u root -p
create database test_database;
use test_database;
select database();
可以看到
mysql> select database();
+---------------+
| database() |
+---------------+
| test_database |
+---------------+
1 row in set (0.00 sec)
2.表格
新建
mysql> create table users(
-> id int auto_increment primary key,
-> name varchar(100) not null,
-> email varchar(100),
-> age int);
Query OK, 0 rows affected (0.03 sec)
查看
mysql> select * from users;
Empty set (0.00 sec)
select查询关键字
*，表示选择表中的所有列
from指定来源关键字，后面跟表的名字
给表users增加数据并查看
mysql> insert into users(name,email,age) values
-> ('张三','[email protected]',20),
-> ('Zhangsan','[email protected]',25);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from users;
+----+----------+----------------------+------+
| id | name | email | age |
+----+----------+----------------------+------+
| 1 | 张三 | [email protected] | 20 |
| 2 | Zhangsan | [email protected] | 25 |
+----+----------+----------------------+------+
2 rows in set (0.00 sec)
可以看到两条数据添加完成。
继续插入数据
mysql> insert into users(name,email,age) values
-> ('李四','[email protected]',66);
Query OK, 1 row affected (0.00 sec)
mysql> select * from users;
+----+----------+----------------------+------+
| id | name | email | age |
+----+----------+----------------------+------+
| 1 | 张三 | [email protected] | 20 |
| 2 | Zhangsan | [email protected] | 25 |
| 3 | 李四 | [email protected] | 66 |
+----+----------+----------------------+------+
3 rows in set (0.00 sec)
更新表中的数据
使用update+更新的表名+set+更改的元素名字=新数据+ where+需要更新的数据条件
update users set age = 88 where name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from users;
+----+----------+----------------------+------+
| id | name | email | age |
+----+----------+----------------------+------+
| 1 | 张三 | [email protected] | 88 |
| 2 | Zhangsan | [email protected] | 25 |
| 3 | 李四 | [email protected] | 66 |
+----+----------+----------------------+------+
3 rows in set (0.00 sec)
注意：此处如果出现多条数据都符合需要更新的数据条件，那么所有符合条件的数据都会被更新。
删除一条数据
mysql> delete from users where name = '张三';
Query OK, 1 row affected (0.01 sec)
mysql> select * from users;
+----+----------+------------------+------+
| id | name | email | age |
+----+----------+------------------+------+
| 2 | Zhangsan | [email protected] | 25 |
| 3 | 李四 | [email protected] | 66 |
+----+----------+------------------+------+
2 rows in set (0.00 sec)
delete操作也是按照where的条件进行数据删除，也会把所有符合条件的数据都删掉。
复制表格(方法一）
先新建一个相同结构的空表
mysql> create table new_users like users;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from new_users;
Empty set (0.00 sec)
再把旧表内容插入新表
mysql> insert into new_users select * from users;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from new_users;
+----+----------+------------------+------+
| id | name | email | age |
+----+----------+------------------+------+
| 2 | Zhangsan | [email protected] | 25 |
| 3 | 李四 | [email protected] | 66 |
+----+----------+------------------+------+
2 rows in set (0.00 sec)
复制表格(方法二）
使用`create table ...
mysql> create table n_users as select * from users;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from n_users;
+----+----------+------------------+------+
| id | name | email | age |
+----+----------+------------------+------+
| 2 | Zhangsan | [email protected] | 25 |
| 3 | 李四 | [email protected] | 66 |
+----+----------+------------------+------+
2 rows in set (0.00 sec)
方法比较
方法二复制的表格没有保留原表格中id列的自增属性。
所以在增加数据后，id列不会自动编号。
mysql> insert into n_users(name,email,age) values('张三','[email protected]',22);
Query OK, 1 row affected (0.01 sec)
mysql> select * from n_users;
+----+----------+----------------------+------+
| id | name | email | age |
+----+----------+----------------------+------+
| 2 | Zhangsan | [email protected] | 25 |
| 3 | 李四 | [email protected] | 66 |
| 0 | 张三 | [email protected] | 22 |
+----+----------+----------------------+------+
3 rows in set (0.00 sec)
mysql> insert into n_users(name,email,age) values('王五','[email protected]',23);
Query OK, 1 row affected (0.00 sec)
mysql> select * from n_users;
+----+----------+----------------------+------+
| id | name | email | age |
+----+----------+----------------------+------+
| 2 | Zhangsan | [email protected] | 25 |
| 3 | 李四 | [email protected] | 66 |
| 0 | 张三 | [email protected] | 22 |
| 0 | 王五 | [email protected] | 23 |
+----+----------+----------------------+------+
4 rows in set (0.00 sec)
方法一是可以自增的
mysql> insert into new_users(name,email,age) values('王五','[email protected]',23);
Query OK, 1 row affected (0.00 sec)
mysql> select * from new_users;
+----+----------+------------------+------+
| id | name | email | age |
+----+----------+------------------+------+
| 2 | Zhangsan | [email protected] | 25 |
| 3 | 李四 | [email protected] | 66 |
| 4 | 王五 | [email protected] | 23 |
+----+----------+------------------+------+
3 rows in set (0.00 sec)
查看数据库中的表
mysql> show tables;
+-------------------------+
| Tables_in_test_database |
+-------------------------+
| n_users |
| new_users |
| users |
+-------------------------+
3 rows in set (0.00 sec)
删除表
使用drop table删除表
mysql> drop table n_users;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+-------------------------+
| Tables_in_test_database |
+-------------------------+
| new_users |
| users |
+-------------------------+
2 rows in set (0.00 sec)