MySQL使用insert into语句来插入数据
insert into table_name (fiel1, field2,.....fieldN) values(value1, value2, value3,...valueN);
如果数据是字符型,就必须使用单引号或者双引号,如’value’
下面举个例子吧
先创建一个user表
create table user(id int primary key auto_increment, name varchar(100), age int, phone_number varchar(20));
然后插入数据
insert into user (name, age, phone_number) values ('xiaoli',21,15236547896), ('qiansan',18,15212345678), ('zhangsan',30,18210721111);
在MySQL数据库中使用select语句来查询数据
在数据库中通用的select语句语法如下:
SELECT column_name, column_name
FROM table_name
[WHERE Clause]
[LIMIT N] [ OFFSET M]
查看user表的数据
select * from user;
查询年龄大于20的用户:
select name, age from user where age>20
我们使用update来更新数据表中的数据。
语法如下:
update table_name set field1=new-value1, field2=new-value2;
比如将用户id为1的年龄更新为22:
update user set age=22 where id=1;
可以使用sql的delete from命令来删除mysql数据表中的记录
语法如下
delete from table_name [where clause]
删除年龄在25岁以上的用户
delete from user where age >25;
如果数据库中存在相同主键的数据,replace的作用相当于修改操作;如果数据库中不存在相同主键的数据,replace相当于插入操作。
replace的语法如下
replace [info] table_name [column_name,...] {values|value} ({expr|default}, ...), (...),...
举个实际的例子,在这个例子中,id是数据表的主键
mysql> select * from user
-> ;
+----+---------+------+--------------+
| id | name | age | phone_number |
+----+---------+------+--------------+
| 1 | xiaoli | 22 | 15236547896 |
| 2 | xiaoli | 21 | 15236547896 |
| 3 | qiansan | 18 | 15212345678 |
| 4 | xiaoli | 21 | 15236547896 |
| 5 | qiansan | 18 | 15212345678 |
+----+---------+------+--------------+
5 rows in set (0.00 sec)
mysql> replace into user (id, name, age, phone_number) values(1, 'xiaoli', 21, 15236547896), (3, 'qiansan', 18, 15212345678), (6, 'zhangsan', 30, 18210721111);
Query OK, 4 rows affected (0.01 sec)
Records: 3 Duplicates: 1 Warnings: 0
mysql> selece * from user;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'selece * from user' at line 1
mysql> select * from user;
+----+----------+------+--------------+
| id | name | age | phone_number |
+----+----------+------+--------------+
| 1 | xiaoli | 21 | 15236547896 |
| 2 | xiaoli | 21 | 15236547896 |
| 3 | qiansan | 18 | 15212345678 |
| 4 | xiaoli | 21 | 15236547896 |
| 5 | qiansan | 18 | 15212345678 |
| 6 | zhangsan | 30 | 18210721111 |
+----+----------+------+--------------+
6 rows in set (0.00 sec)
在例子里面,执行replace语句,然后再次执行数据查询语句,可以看到id为1的用户存在,所以replace语句变为修改操作,将用户1的年龄修改为21。用户3的信息与replace语句的内容一样,不修改。用户6不存在,replace语句相当于插入操作。