若username='chenhaha'的记录不存在,INSERT语句将插入新记录,否则,当前username='chenhaha'的记录将被更新,更新的字段由UPDATE指定。 对了,ON DUPLICATE KEY UPDATE为MySQL特有语法,比如在MySQL迁移Oracle或其他DB时,类似的语句要改为MERGE INTO语法,兼容性让人想骂街。但没办法,就像用WPS写的xlsx用Office无法打开一...
mysql> INSERT INTO tb_courses -> (course_name,course_info,course_id,course_grade) -> VALUES('Database','MySQL',2,3); Query OK, 1 rows affected (0.08 sec) mysql> SELECT * FROM tb_courses; +---+---+---+---+ | course_id | course_name | course_grade | course_info | +--...
在student数据库中创建一个学生表,包含学生编号 id、学生姓名 name、学生年龄age输入的 SQL 语句和执行结果如下所示。 mysql>createtable student(idintprimarykeyauto_increment,namevarchar(8),ageintnotnull); Query OK,0 rows affected (0.16 sec) mysql>insertinto student (id,name,age)value(null,"张三",...
INSERT INTO `UKPostCodes` VALUES ('AB10', 'Aberdeen', 57.1350, -2.1170); but its always setting the value that should be 57.1350 to 9.9999 but the other value goes in fine. What's going on? I've been trying to get this working for a while now, I don't fancy having to do it ...
mysql>INSERTINTOtb_courses->(course_id,course_name,course_grade,course_info)->VALUES(1,'Network',3,'Computer Network'); Query OK,1rows affected (0.08sec) mysql>SELECT*FROMtb_courses;+---+---+---+---+|course_id|course_name|course_grade|course_info|+---+---+---+---+|1|Netwo...
insert ignore into test_data(xid,name) values(1,'aaa');Query OK, 0 rows affected, 1 warning (0.01 sec)mysql--root@localhost:test 18:58:13>>show warnings;+---+---+---+| Level | Code | Message |+---+---+---+| Warning | 1062 | Duplicate entry '1' for key 'xid' |+--...
3 rows in set (0.00 sec) 下面我们使用REPLACE语句向table1中插入一条记录。 REPLACE INTO table1(a, b, c) VALUES(1,2,3); mysql> REPLACE INTO table1(a, b, c) VALUES(1,2,3); Query OK, 4 rows affected (0.04 sec) 此时查询table1中的记录如下,只剩一条数据了~ ...
5 rows in set (0.00 sec) T2 时刻 session2 遇到唯一键冲突,对REPLACE的记录和其下一条记录加 X 型 NEXT-KEY 锁,即锁范围是 (20,30],(30,40],对 (20,30],(30,40) 加锁成功,但是等待 session1 a=40 的记录锁。 mysql> SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LO...
2 rows in set (0.00 sec) mysql> insert into staff_3 (name,slary) values ('xiaoli',3200); ERROR 1062 (23000): Duplicate entry 'xiaoli' for key 'PRIMARY' mysql> insert ignore into staff_3 (name,slary) values ('xiaoli',3200); ...
二: MySQL实现分页查询 方法一:limit 就 两条语句: select count(*) from table: 查询得到记录总条数,便于决定分页。 select * from table limit pageNo,rowsCount: 查询从第pageNo条开始的rowsCount条数据。 这是最常见MYSQL最基本的分页方式,查询前10条数据,倒序就加上desc: ...