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