|
字段，类型为varchar
MariaDB [admin]> alter table student add column email varchar(30);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [admin]> show columns from student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(30) | YES | | NULL | |
| sex | char(5) | NO | | NULL | |
| phone | varchar(20) | NO | | NULL | |
| email | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
例6：更改student表的name列为st_name;
MariaDB [admin]> alter table student change column name st_name char(10);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [admin]> show columns from student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| st_name | char(10) | YES | | NULL | |
| sex | char(5) | NO | | NULL | |
| phone | varchar(20) | NO | | NULL | |
| email | varchar(30) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
7、查询数据
语法：select 列名 from 表名 where 查询条件 limit N
limit 设定返回的记录数。
例7：查询mysql数据库里user表的user和password列，只显示root用户。
MariaDB [mysql]> select user,password from user where user = 'root' ;
+------+----------+
| user | password |
+------+----------+
| root | |
| root | |
| root | |
| root | |
+------+----------+
4 rows in set (0.00 sec)
8、插入数据
语法：insert into 表名 （列1，列2，···，列3）values （值1，值2，···，值3）;
添加的值如果是字符型必须用单引号或双引号引起来。
例8：添加数据到表student；
MariaDB [admin]> insert into student (id,st_name,sex,phone,email) values (1,'whr','man',1766262166,'admin@163.com');
Query OK, 1 row affected (0.00 sec)
MariaDB [admin]> select * from student;
+----+---------+-----+------------+---------------+
| id | st_name | sex | phone | email |
+----+---------+-----+------------+---------------+
| 1 | whr | man | 1766262166 | admin@163.com |
+----+---------+-----+------------+---------------+
1 row in set (0.01 sec)
9、更新数据
语法：update 表名 set 列1=新值1，列2=新值2 where 条件；
例9：修改id 1的名字为admin
MariaDB [admin]> update student set st_name = 'admin' where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [admin]> select * from student;
+----+---------+-----+------------+---------------+
| id | st_name | sex | phone | email |
+----+---------+-----+------------+---------------+
| 1 | admin | man | 1766262166 | admin@163.com |
+----+---------+-----+------------+---------------+
1 row in set (0.00 sec)
10、删除数据
语法：delete from 表名 where 条件；
例10：删除id为1的所有数据。
MariaDB [admin]> delete from student where id = 1;
Query OK, 1 row affected (0.00 sec)
MariaDB [admin]> select * from student;
Empty set (0.00 sec)
11、where子句的运用
where子句是用来筛选数据。
语法：以select为例，select 列1，列2，···，列n from 表1，表2，···，表n where 条件1 and|or 条件2;
where子句的操作符有以下几种：
= 是否相等
<>,!= 不等于
> 大于
< 小于
>= 大于等于
<= 小于等于
例11：查询mysql库user表中的host=localhost的所有数据。
MariaDB [mysql]> select host,user from us |