mysql學(xué)習(xí)筆記2
書接上回，重新進(jìn)入mysql，繼續(xù)學(xué)習(xí)新內(nèi)容。
1.進(jìn)入mysql并新建數(shù)據(jù)庫
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查詢關(guān)鍵字
*，表示選擇表中的所有列
from指定來源關(guān)鍵字，后面跟表的名字
給表users增加數(shù)據(jù)并查看
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)
可以看到兩條數(shù)據(jù)添加完成。
繼續(xù)插入數(shù)據(jù)
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)
更新表中的數(shù)據(jù)
使用update+更新的表名+set+更改的元素名字=新數(shù)據(jù)+ where+需要更新的數(shù)據(jù)條件
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)
注意：此處如果出現(xiàn)多條數(shù)據(jù)都符合需要更新的數(shù)據(jù)條件，那么所有符合條件的數(shù)據(jù)都會被更新。
刪除一條數(shù)據(jù)
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的條件進(jìn)行數(shù)據(jù)刪除，也會把所有符合條件的數(shù)據(jù)都刪掉。
復(fù)制表格(方法一）
先新建一個(gè)相同結(jié)構(gòu)的空表
使用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)
再把舊表內(nèi)容插入新表
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)
復(fù)制表格(方法二）
使用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)
方法比較
方法二復(fù)制的表格沒有保留原表格中id列的自增屬性。
所以在增加數(shù)據(jù)后，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表的結(jié)構(gòu)
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修改現(xiàn)有的列
id修改的列的名稱
int列的數(shù)據(jù)類型
auto_increment自動遞增屬性
primary key表的主鍵，唯一且不能為null，每個(gè)表只有1個(gè)主鍵。
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
#######再次查看表結(jié)構(gòu)
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屬性
查看數(shù)據(jù)庫中的表
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)
浙公網(wǎng)安備 33010602011771號