MySQL ----随笔

MySQL ----随笔

新建一个表单：create table test_ggh(ID int,name varchar(20),tele int);
删除一个表单：drop table test_ggh;

插入一列：alter table test_ggh drop column tele int not null;
添加在指定的一列：alter table 表名 add column 列名 varchar(20) not null after user1;

添加到第一列：alter table 表名 add column 列名 varchar(20) not null first;

删除一列：alter table test_ggh drop column tele;
插入一行：insert into test_ggh(ID,name,tele) values(001,'zhangsan',123);

删除 ：delete from test_ggh where ID='002';

更新数据： update test_ggh set ID='002' where NAME='zhangsan';

替换部分字符串：update test_ggh set NAME=REPLACE(NAME,'综合财务科','综合管理科');

Mysql配置-允许别人链接我的Mysql

select * FROM mysql.user where user = 'root';
update mysql.user set host = '%' where user = 'root' and host = 'localhost';

mysql函数循环生成值：

CREATE DEFINER = CURRENT_USER PROCEDURE `point`()
BEGIN
 DECLARE nums INT DEFAULT 1;
 WHILE nums <=818 DO
	INSERT INTO point (id,point_id,create_time,update_time)
	VALUES (nums,nums,NOW(),NOW());
 SET nums=nums+1;
 END WHILE;
END

合并两列为一列

SELECT CONCAT(parent_machine,"_",point_id) AS new_name FROM point;
UPDATE `point` SET node_id =CONCAT(parent_machine,"_",point_id)

navicate MySQL server has gone away

show VARIABLES like '%max_allowed_packet%';
set global max_allowed_packet = 1024*1024*64;

my.ini文件中修改
#The maximum size of one packet or any generated or intermediate string, or any parameter sent by the
#mysql_stmt_send_long_data() C API function.
max_allowed_packet=128M

最大连接数：
#The maximum amount of concurrent sessions the MySQL server will allow. One of these connections will be reserved for a user with SUPER privileges to allow the administrator to login even if the connection limit has been reached.
max_connections=151