mysql 常用小操作

登入mysql:

msql -u用户名 -p;

 

修改表结构：

alter table productbandwidth_productbandwidth modify column productcode varchar(20);

并加注释

alter table member add column province varchar(50) COMMENT '户籍省';

  给表加注释

ALTER TABLE table_name COMMENT='这是表的注释';

 修改默认值时（defalut null 之前加上null ）

ALTER TABLE member MODIFY COLUMN dri_lic_first_time TIMESTAMP NULL DEFAULT NULL COMMENT '驾驶证初次领证日期';

 

查看数据库名，表名

show databases/tables;

 切换表空间

use  表名

 启动mysql

/etc/init.d/mysqld start

重启mysql

/etc/init.d/mysqld restart

导出数据：

mysqldump -u root -p 库.[表] > news.sql

遇到的问题：（磁盘满了）

mysqldump: Couldn't execute 'show fields from `channel_flux_monitoring`': Go

 

删除指定的记录

DELETE FROM users WHERE name = 'Mike' ORDER BY id DESC LIMIT 6;   删除name是mike降序的前6条记录

 快速删除记录（不产生碎片，无法恢复）

TRUNCATE TABLE table1

 查看表结构

desc 表名;
show columns from 表名;

 查询去重

SELECT DISTINCT billing_code FROM channel_billing_customer_product

 查看mysql版本

select version();
status;

 表的复制（查询的表结构要与要插入的一致）

INSERT INTO portal_product_billtype SELECT * FROM portal_product_billtype_temp

 解决php访问出现：mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication

SET old_passwords = 0;
UPDATE mysql.user SET Password = PASSWORD('testpass') WHERE User = 'testuser' limit 1;
SELECT LENGTH(Password) FROM mysql.user WHERE User = 'testuser';
FLUSH PRIVILEGES;

     user表中，host代表哪台机器可以访问，%所有;

 

mysql 提示SQL-ERROR summary different number of columns

这是因为使用union的两个SQL语句产生的记录的表结构不一致. 必须是结构完全一致的记录集合才可以使用UNION.

 

修改连接数

写道

重启后失效
show variables like "max_connections"; #
set global max_connections = 200; #
修改配置文件（不会失效）
/etc/my.conf
max_connections=200

mysql -uroot -p
SELECT @@MAX_CONNECTIONS AS 'Max Connections';
set GLOBAL max_connections=1000;

 模糊查询

1，%：表示任意0个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。
比如 SELECT * FROM [user] WHERE u_name LIKE '%三%'
2，_： 表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句：
比如 SELECT * FROM [user] WHERE u_name LIKE '_三_'
3，[ ]：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
比如 SELECT * FROM [user] WHERE u_name LIKE '[张李王]三' 将找出“张三”、“李三”、“王三”（而不是“张李王三”）；
4，[^ ] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。
比如 SELECT * FROM [user] WHERE u_name LIKE '[^张李王]三' 将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等；
5，查询内容包含通配符时
由于通配符的缘故，导致我们查询特殊字符“%”、“_”、“[”的语句无法正常实现，而把特殊字符用“[ ]”括起便可正常查询。据此我们写出以下函数：
function sqlencode(str) str=replace(str,"';","';';")
str=replace(str,"[","[[]") ';此句一定要在最先 str=replace(str,"_","[_]") str=replace(str,"%","[%]") sqlencode=str end function

 

MySQL 添加唯一约束

写道

创建表时直接设置:
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`stu_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`stu_id`),
UNIQUE KEY `UK_student_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;


创建唯一索引:
create unique index UK_student_name on student (name);

建表后添加约束:
alter table student add constraint uk_student_name unique (name);

 得到系统时间

NOW()函数以'YYYY-MM-DD HH:MM:SS'返回当前的日期时间，可以直接存到DATETIME字段中。

CURDATE()以'YYYY-MM-DD'的格式返回今天的日期，可以直接存到DATE字段中。

CURTIME()以'HH:MM:SS'的格式返回当前的时间，可以直接存到TIME字段中。

MYSQL获取自增ID的四种方法

写道

1. select max(id) from tablename

2.SELECT LAST_INSERT_ID() 函数

LAST_INSERT_ID 是与table无关的，如果向表a插入数据后，再向表b插入数据，LAST_INSERT_ID会改变。

在多用户交替插入数据的情况下max(id)显然不能用。这时就该使用LAST_INSERT_ID了，因为LAST_INSERT_ID是基于Connection的，只要每个线程都使用独立的 Connection对象，LAST_INSERT_ID函数将返回该Connection对AUTO_INCREMENT列最新的insert or update 操作生成的第一个record的ID。这个值不能被其它客户端（Connection）影响，保证了你能够找回自己的 ID 而不用担心其它客户端的活动，而且不需要加锁。使用单INSERT语句插入多条记录, LAST_INSERT_ID返回一个列表。

3. select @@IDENTITY;

@@identity 是表示的是最近一次向具有identity属性(即自增列)的表插入数据时对应的自增列的值，是系统定义的全局变量。一般系统定义的全局变量都是以@@开头，用户自定义变量以@开头。

比如有个表A，它的自增列是id，当向A表插入一行数据后，如果插入数据后自增列的值自动增加至101，则通过select @@identity得到的值就是101。使用@@identity的前提是在进行insert操作后，执行select @@identity的时候连接没有关闭，否则得到的将是NULL值。

4. SHOW TABLE STATUS;

得出的结果里边对应表名记录中有个Auto_increment字段，里边有下一个自增ID的数值就是当前该表的最大自增ID.

 修改自增字段

alter table users AUTO_INCREMENT=10000;