DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `user_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `user_password` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, PRIMARY KEY (`user_id`) USING BTREE, ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; insert into `user`(`user_id`,`user_name`,`user_password`) values (1,'zxc123','zxc'),(2,'yui','123');有错吗

这段 SQL 语句是创建一个名为 `user` 的表，并向其中插入两行数据。根据语法来看，这段代码是没有问题的。但是如果在实际执行时，发现该表已经存在，就会执行 DROP TABLE 语句删除表，然后再创建一个新表，并插入两行数据。

需要注意的是，如果该表已经存在并且里面有数据，执行 DROP TABLE 语句将会删除整个表和表中的数据，所以需要谨慎操作。

相关问题

USE `sys`; DROP TABLE IF EXISTS `admin`; CREATE TABLE `admin` ( `admin_id` int(11) NOT NULL AUTO_INCREMENT, `admin_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `admin_password` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, PRIMARY KEY (`admin_id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; insert into `admin`(`admin_id`,`admin_name`,`admin_password`) values (1,'zxc','abc'),(2,'qwe','acc'); DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `user_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `user_password` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, PRIMARY KEY (`user_id`) USING BTREE, ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;这段语句的错误

这段语句有一个错误，就是在创建`user`表的语句中，最后有一个逗号，导致语法错误。正确的语句应该是：

USE `sys`; 
DROP TABLE IF EXISTS `admin`; 
CREATE TABLE `admin` (
    `admin_id` int(11) NOT NULL AUTO_INCREMENT,
    `admin_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `admin_password` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (`admin_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; 

INSERT INTO `admin`(`admin_id`, `admin_name`, `admin_password`) 
VALUES (1, 'zxc', 'abc'), (2, 'qwe', 'acc'); 

DROP TABLE IF EXISTS `user`; 
CREATE TABLE `user` (
    `user_id` int(11) NOT NULL AUTO_INCREMENT,
    `user_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `user_password` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

create procedure p10(in sex int) begin declare user_no varchar(100); declare user_pwd varchar(100); declare user_cursor cursor for select user_no,user_pwd from t_user where sex = sex; declare exit handler for not found close user_cursor; drop table if exists t_user_test; create table t_user_test( id int primary key auto_increment, user_no varchar(100) not null, user_pwd varchar(100) not null ); open user_cursor; while true do fetch user_cursor into user_no,user_pwd; insert into t_user_test(user_no,user_pwd) values(user_no,user_pwd); end while; end; 哪个地方有误

在该存储过程中，有一处错误：

declare user_cursor cursor for select user_no,user_pwd from t_user where sex = sex;

这里的 `sex = sex` 应该改为 `sex = in_sex`，以引用存储过程的输入参数。修改后的存储过程如下：

create procedure p10(in sex int) 
begin  	
    declare user_no varchar(100); 	
    declare user_pwd varchar(100); 	
    declare user_cursor cursor for select user_no,user_pwd from t_user where sex = in_sex; 	
    declare exit handler for not found close user_cursor; 	 	
    drop table if exists t_user_test; 	
    create table t_user_test(       
        id int primary key auto_increment,         
        user_no varchar(100) not null,         
        user_pwd varchar(100) not null     
    );          
    open user_cursor; 	
    while true do 		
        fetch user_cursor into user_no,user_pwd; 		
        insert into t_user_test(user_no,user_pwd) values(user_no,user_pwd); 	
    end while; 	 
end;