mysql学习笔记2

书接上回，重新进入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 
    -> ('张三','zhangsan@example.com',20),
    -> ('Zhangsan','ZS@example.com',25);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from users;
+----+----------+----------------------+------+
| id | name     | email                | age  |
+----+----------+----------------------+------+
|  1 | 张三     | zhangsan@example.com |   20 |
|  2 | Zhangsan | ZS@example.com       |   25 |
+----+----------+----------------------+------+
2 rows in set (0.00 sec)

可以看到两条数据添加完成。

继续插入数据

mysql> insert into users(name,email,age) values 
    -> ('李四','lisi@example.com',66);
Query OK, 1 row affected (0.00 sec)
mysql> select * from users;
+----+----------+----------------------+------+
| id | name     | email                | age  |
+----+----------+----------------------+------+
|  1 | 张三     | zhangsan@example.com |   20 |
|  2 | Zhangsan | ZS@example.com       |   25 |
|  3 | 李四     | lisi@example.com     |   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 | 张三     | zhangsan@example.com |   88 |
|  2 | Zhangsan | ZS@example.com       |   25 |
|  3 | 李四     | lisi@example.com     |   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 | ZS@example.com   |   25 |
|  3 | 李四     | lisi@example.com |   66 |
+----+----------+------------------+------+
2 rows in set (0.00 sec)

delete操作也是按照where的条件进行数据删除，也会把所有符合条件的数据都删掉。

复制表格(方法一）

先新建一个相同结构的空表

使用create table+新表+ like+旧表

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)

再把旧表内容插入新表

insert into+表名+select * from 源

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 | ZS@example.com   |   25 |
|  3 | 李四     | lisi@example.com |   66 |
+----+----------+------------------+------+
2 rows in set (0.00 sec)

复制表格(方法二）

使用create table+新表+as+select * from 源

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 | ZS@example.com   |   25 |
|  3 | 李四     | lisi@example.com |   66 |
+----+----------+------------------+------+
2 rows in set (0.00 sec)

方法比较

方法二复制的表格没有保留原表格中id列的自增属性。
所以在增加数据后，id列不会自动编号。

mysql> insert into n_users(name,email,age) values('张三','zhangsan@example.com',22);
Query OK, 1 row affected (0.01 sec)

mysql> select * from n_users;
+----+----------+----------------------+------+
| id | name     | email                | age  |
+----+----------+----------------------+------+
|  2 | Zhangsan | ZS@example.com       |   25 |
|  3 | 李四     | lisi@example.com     |   66 |
|  0 | 张三     | zhangsan@example.com |   22 |
+----+----------+----------------------+------+
3 rows in set (0.00 sec)

mysql> insert into n_users(name,email,age) values('王五','ww@example.com',23);
Query OK, 1 row affected (0.00 sec)

mysql> select * from n_users;
+----+----------+----------------------+------+
| id | name     | email                | age  |
+----+----------+----------------------+------+
|  2 | Zhangsan | ZS@example.com       |   25 |
|  3 | 李四     | lisi@example.com     |   66 |
|  0 | 张三     | zhangsan@example.com |   22 |
|  0 | 王五     | ww@example.com       |   23 |
+----+----------+----------------------+------+
4 rows in set (0.00 sec)

方法一是可以自增的

mysql> insert into new_users(name,email,age) values('王五','ww@example.com',23);

Query OK, 1 row affected (0.00 sec)

mysql> select * from new_users;
+----+----------+------------------+------+
| id | name     | email            | age  |
+----+----------+------------------+------+
|  2 | Zhangsan | ZS@example.com   |   25 |
|  3 | 李四     | lisi@example.com |   66 |
|  4 | 王五     | ww@example.com   |   23 |
+----+----------+------------------+------+
3 rows in set (0.00 sec)

#######解决方法二的自增问题
#######查看n_users表的结构

mysql> describe n_users;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int          | NO   |     | 0       |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| email | varchar(100) | YES  |     | NULL    |       |
| age   | int          | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

使用alter table+表+modify id int auto_increment primary key更改字段属性
MODIFY修改现有的列
id修改的列的名称
int列的数据类型
auto_increment自动递增属性
primary key表的主键，唯一且不能为null，每个表只有1个主键。

mysql> alter table n_users modify id int auto_increment primary key;
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

#######再次查看表结构

mysql> describe n_users;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| email | varchar(100) | YES  |     | NULL    |                |
| age   | int          | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

可以看到多了 PRI和auto_increment属性

查看数据库中的表

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)