mysql basic operation,mysql总结分分快三全天计划网站

作者:分分快三全天计划网站

9)查看存储过程简单信息
show create procedure p1;

CREATE TABLE tab2 LIKE tab1
使用和tab1表相同的结构来创建一个新表,列名、数据类型、空指和索引也将复制,但是表的内容不会被复制。外键和专用的权限也没有被复制。

/*返回当前使用的数据库用户 root@ip root@ip root@ip root@% root@% */
select USER(),SYSTEM_USER(),SESSION_USER(),CURRENT_USER(),CURRENT_USER;

ALTER TABLE person ADD INDEX birthday_index (`birthday`);

/*返回指定日期加1分钟的日期时间 2016-08-24 01:59:37*/
select DATE_ADD(LOCALTIME(),INTERVAL 1 minute);

删除某个索引

#修改用户密码
use mysql;
update mysql.user set password=password('新密码') where User='用户名' and Host='localhost';
flush privileges;

删除列[drop 列名]

①alter table 表名 drop 列名
例:alter table test drop pid;

=========================

#显示profiling
show profiles G;

添加普通索引:

#允许外网访问
create user 'mike'@'%' identified by '123456';

 

/*返回指定重复次数的字符串 mysql数据函数mysql数据函数mysql数据函数/*
SELECT REPEAT('mysql数据函数',3);

//删除列

/*返回指定格式的日期时间 2016-08-24 01:58:37*/
select DATE_FORMAT(LOCALTIMESTAMP(),'%Y-%m-%d %H:%i:%S');

查询列

①desc 表名【查询所有列】
例: desc test;
mysql> desc department;
------- ------------- ------ ----- --------- -------
| Field | Type | Null | Key | Default | Extra |
------- ------------- ------ ----- --------- -------
| dId | int(11) | NO | PRI | | |
| dName | varchar(32) | YES | | NULL | |
------- ------------- ------ ----- --------- -------

②show columns from 表名【效果和desc一样】
mysql> show columns from department;
------- ------------- ------ ----- --------- -------
| Field | Type | Null | Key | Default | Extra |
------- ------------- ------ ----- --------- -------
| dId | int(11) | NO | PRI | | |
| dName | varchar(32) | YES | | NULL | |
------- ------------- ------ ----- --------- -------

③show create table 表名【查看表的创建代码】
mysql> show create table department;
CREATE TABLE `department` (
`dId` int(11) NOT NULL,
`dName` varchar(32) DEFAULT NULL,
PRIMARY KEY (`dId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

总结一下对mysql表结构的修改操作,包括对字段及索引的修改操作: 添加字段: alter table `user_movement_log` Add column GatewayId int not null default 0 AFTER `Regionid` (在哪个字段后面添加) 删除字段: alter table `user_movement_log` drop column Gatewayid 调整字段顺序: ALTER TABLE `user_movement_log` CHANGE `GatewayId` `GatewayId` int not null default 0 AFTER RegionID //主键 alter table tabelname add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id); //增加一个新列 alter table t2 add newfield timestamp; (简单语法,只指定新添字段newfield 类型为timestamp) alter table infos add newfield tinyint not null default '0'; (同时指定字段null属性、默认值) //删除列 alter table t2 drop column c; (删除列c) //重命名列 alter table t1 change a b integer; (把列a改名为b,注意要指定列类型) //改变列的类型 alter table t1 change b b bigint not null; (把列a改名为b,指定列类型及null属性) alter table infos change list list tinyint not null default '0'; //重命名表 alter table t1 rename t2; 加索引 mysql> alter table tablename change depno depno int(5) not null; mysql> alter table tablename add index 索引名 (字段名1[,字段名2 …]); mysql> alter table tablename add index emp_name (name); 加主关键字的索引 mysql> alter table tablename add primary key(id); 加唯一限制条件的索引 mysql> alter table tablename add unique emp_name2(cardnumber); 删除某个索引 mysql>alter table tablename drop index emp_name; 修改表: 增加字段: mysql> ALTER TABLE table_name ADD field_name field_type; 修改原字段名称及类型: mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type; 删除字段: mysql> ALTER TABLE table_name DROP field_name;

 

ORDER BY _column1, _column2; /* _column1升序,_column2升序 */ 
ORDER BY _column1, _column2 DESC; /* _column1升序,_column2降序 */ 
ORDER BY _column1 DESC, _column2 ; /* _column1降序,_column2升序 */ 
ORDER BY _column1 DESC, _column2 DESC; /* _column1降序,_column2降序 */ 
用 DESC 表示按倒序排序(即:从大到小排序)
用 ACS 表示按正序排序(即:从小到大排序)

 

order应该是以汉字的 ASCII 码排序,下面是按照汉字拼音排序
select * from corp_data where Chengshi like "图木舒克" order by convert(name using gbk);

#select count(*) as count from corp_data where Chengshi like "图木舒克";
select * from corp_data where Chengshi like "图木舒克" order by convert(name using gbk);
#delete from corp_data where Chengshi like "图木舒克";
#desc corp_data;
#alter table corp_data modify column hangye varchar(100) ;
#alter table corp_data modify column jianjie varchar(10000) ;

 

 

 

 

 

mysql如何查询最新的5条数据
select * from tb_content where id=(select msx(id) from tb_content) limit 0,5;

jdbcTemplate.execute("CREATE
建数据库不合适,要手动drop database ,再create database.

查总数:
select count(*) as count from tb_content;
select count(id) as count from tb_content;
navicat->分析表->取得表的总数

select * from corp_data where Chengshi like "上海";
select * from corp_data where Shengzhixiashi like "河南";

select name ,count(distinct(url)) from corp_data group by  name;
select Chengshi ,count(distinct(url)) from corp_data group by  Chengshi;
select Chengshi ,count(*) from corp_data group by  Chengshi;
select count(*) from corp_data where Chengshi like "北京";
select * from corp_data where Chengshi like "北京";
select name ,url  from corp_data limit 1;
select *  from corp_data limit 1;

select *  from corp_data limit 10;

select count(*) as count from corp_data limit 10;
select count(distinct(name)) as count from corp_data;

 varchar(N),  这里的N是指字符数,并不是字节数.占用的字节数与编码有关
utf-8, 一个汉字3字节 英文字母1字节 

 

使用root账户进入mysql
create user 'test'@'localhost' indentified by '123456';
grant all privileges on *.* to 'test'@'localhost' identified by '123456';

grant all privileges on *.* to 'test'@'%' identified by '123456';   #在其它任意台电脑上访问

flush privileges;

mysql编码最好使用utf-8,导出导入不用考滤编码。
GB2312是GBK的子集,GBK是GB18030的子集,是国标。utf-8为国际标准,特殊中文时使用gb18030。

 

/*返回指定时间减10秒后的日期时间 01:58:27*/
select SUBTIME(CURRENT_TIME,10);

禁用非唯一索引

返回第1个参数字符串在第2个参数字符串中的位置 3
SELECT POSITION('s' IN 'mysql');

修改表的语法

返回第2个参数字符串在第1个参数字符串中的位置 3
SELECT INSTR('mysql','sq');

如果您在一个表达式中通过tbl_name访问一列,则UPDATE使用列中的当前值。例如,以下语句把年龄列设置为比当前值多一:
UPDATE persondata SET age=age 1;
UPDATE赋值被从左到右评估。例如,以下语句对年龄列加倍,然后再进行增加:
UPDATE persondata SET age=age*2, age=age 1;
如果您把一列设置为其当前含有的值,则MySQL会注意到这一点,但不会更新。
update表的某些字段为null
update person set number=null,name=null;
如果您把被已定义为NOT NULL的列更新为NULL,则该列被设置到与列类型对应的默认值,并且累加警告数。对于数字类型,默认值为0;对于字符串类型,默认值为空字符串('');对于日期和时间类型,默认值为“zero”值。

insert into person(name,sex,age,phone,address,statusinfo,describes)
values(1,'地**',1,32,'150****9871','某市某区某大厦某东某室','未婚,***万/月,技术工程师,无,购物,良好,有房贷无车贷,有***万存款,***万投资'),
(2,'地**',1,32,'150****9871','某市某区某大厦某东某室','未婚,***万/月,技术工程师,无,购物,良好,有房贷无车贷,有***万存款,***万投资');
commit;

3、显示数据表的结构:
describe 表名;

/*返回数字的2进制*/
select BIN(10);

 

#关闭profiling
set profiling=0

mysql>alter table tablename drop index emp_name;

2)常用数据库操作命令
1)查看数据库支持的存储引擎
show engines;

alter table `user_movement_log`
Add column GatewayId int not null default 0 AFTER `Regionid` (在哪个字段后面添加)

18)开启profiling的步骤
#查看profiling是否开启
show variables like 'profiling%';

12.查询表中的数据:select          */表字段1,表字段2,……表字段n     from 表名 【where 查询条件】;
mysql> select * from student;
mysql> select * from student where id=1;
mysql> select id,name from student;
mysql> select id,name from student where id in(1,3,4);//in表示集合。

7)显示表结构
describe 表名;

加主关键字的索引

SELECT INSERT('mysql数据函数',1,4,'非常实用的');

 

8)查看创建表的sql语句
show create table commentG;

删除列[drop 列名]

①alter table 表名 drop 列名
例:alter table test drop pid;

=========================

4)列出所有数据库
show databases;

 

返回第1个参数字符串在第2个参数字符串中的位置 3
SELECT LOCATE('sq','mysql');

 //看当前使用的是哪个数据库 ,如果你还没选择任何数据库,结果是NULL。
mysql>select database();
------------
| DATABASE() |
------------
| menagerie  |
------------

/*返回以第3个参数从左边开始填充后的字符串,长度为10 好mysql数据函数/*
SELECT LPAD('mysql数据函数',10,'好的');

删除默认值
ALTER TABLE employee ALTER COLUMN truename DEOP DEFAULT

#grant 权限 on 数据库.* to '用户名'@'登录主机' identified by '密码';
#原始密码为:123456 加密后的密码为:*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
grant all privileges on *.* to 'root'@'%' identified by password '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' with grant option;

CREATE TALBE tab2 ENGINE=MYISAM, CHARSET='UTF8' AS (SELECT * FROM tab1)
可以自己指定存储引擎和字符集,弥补方法一的不足

/*修改列名和列类型*/
alter table person change address personaddress varchar(128) default '无';
alter table person modify statusinfo varchar(256) default '各种状态简述;无';

//看数据库里有哪些表
mysql>show tables;
---------------------
| Tables in menagerie |
---------------------
| event               |
| pet                 |
---------------------
如果你想要知道一个表的结构,可以使用DESCRIBE命令;它显示表中每个列的信息:

/*数字保留2位小数*/
select FORMAT(1949001,2);

复制旧表的数据到新表(假设两个表结构一样)
INSERT INTO tab1 SELECT * FROM tab2

/*返回第1个字符的ascii码*/
select ASCII('myql');

ALTER TABLE person CONVERT TO CHARACTER SET utf8;
修改表某一列的编码

/*返回以第3个参数从左右边开始填充后的字符串,长度为10 mysql数据函数好/*
SELECT RPAD('mysql数据函数',10,'好的');

//重命名表

/*参数1为真,返回参数1的值,否则返回参数2的值*/
select if(2=1,'成立','不成立');

修改原字段名称及类型:

/*返回在0年1月1日之后n天的日期*/
select FROM_DAYS(1000);

ALTER TABLE `user_movement_log` CHANGE `GatewayId` `GatewayId` int not null default 0 AFTER RegionID

返回从第2个参数指定的值开始截取指定第3个参数长度的字符串 sq
SELECT MID('mysql数据函数',3,2);

alter table `user_movement_log` drop column Gatewayid

/*返回UTC日期 2016-08-23*/
SELECT UTC_DATE();

删除字段:

/*返回时间数字对应的时间 01:55:19 AM 01:55:19 AM*/
select TIME_FORMAT(UTC_TIME(),'%r');
select TIME_FORMAT(CURTIME(),'%r');
select TIME_FORMAT(CURRENT_TIME,'%r');

mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

/*返回相连接的2个字符串*/
SELECT CONCAT('mysql','数据函数');

如果表有索引,SHOW INDEX FROM tbl_name生成有关索引的信息。

返回从第2个参数指定的值开始截取指定第3个参数长度的字符串 ql数据函
SELECT SUBSTRING('mysql数据函数',4,5);

4、建库:
create database 库名;

/*对字符串进行dencode编码 解码密码为 encode*/
select DECODE('123456','encode');

加唯一限制条件的索引

#用户通过外网IP访问数据库
grant all privileges on `testdb`.* to 'mike'@'%' identified by '123456';

删除数据库.
mysql>drop database test;  

/*返回指定日期减1天后的日期时间 2016-08-23 01:58:37*/
select SUBDATE(SYSDATE(),INTERVAL 1 day);

更改表名
ALTER TABLE employee RENAME TO staff

/*返回UTC时间 17:55:19*/
select UTC_TIME();

ALTER TABLE person DROP COLUMN birthday;
添加列:

/*返回加密字符串后的编码 *3F81B1F810FF5EFEF95211C8034BF1F0C1EAB7A8*/
select PASSWORD(user());

复制旧表的数据到新表(假设两个表结构不一样)
INSERT INTO tab1(字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM tab2

/*返回指定日期加1天后的日期时间 2016-08-25 01:58:37*/
select ADDDATE(now(),1);

添加主键:

#查看SQL的执行情况,是否有死锁、执行状态等
show full processlist ;

alter table t2 add d timestamp;
alter table infos add ex tinyint not null default '0';

/*函数适用于:BINARY、CHAR、DATE、DATETIME、TIME、SIGNED INTEGER、UNSIGNED INTEGER*/

10.向表中删除行数据:
delect from 表名;(清空表)
delect from 表名 where [检索条件表达式]
delete from wifi_test where dev_id=1234567890ab;
delete from table_xx where catch_time LIKE '2016-07-14 20:22:%';  

5)切换数据库
use 数据库名;

 

17)其他操作命令
#mariadb启动和停止命令
systemctl stop mariadb.service
systemctl start mariadb.service

mysql修改表
表的结构如下:

/*返回字符串中字节的个数*/
SELECT LENGTH('mysql数据函数');

5,设置指定可访问主机权限
mysql>grant all privileges on *.* to 'liuhui'@'10.2.1.11';
说明:设置指定用户名为liuhui,可访问所有数据库*,只有10.2.1.11这台机器有权限访问
还可以设置指定访问某个数据库下的某个数据表,请继续关注MySQL基础知识系列。

2)查看数据库当前的存储引擎
show variables like '%storage_engine%';

删除字段:

#获取执行了SQL语句的id
show profile for query query_id

mysql> ALTER TABLE table_name ADD field_name field_type;

/*返回时间 17:57:26*/
select SEC_TO_TIME(TIME_TO_SEC(UTC_TIME()));
select SEC_TO_TIME(TIME_TO_SEC(CURTIME()));
select SEC_TO_TIME(TIME_TO_SEC(CURRENT_TIME));

alter table tabelname add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id);

/*以unix时间戳形式返回当前时间*/
select unix_timestamp();

ALTER TABLE person ADD COLUMN birthday datetime;
修改列,把number修改为bigint:

/*返回指定日期加1秒后的日期时间 2016-08-24 01:58:38*/
select ADDDATE(CURRENT_TIMESTAMP(),INTERVAL 1 second);

增加列[add 列名]

①alter table 表名 add 列名 列类型 列参数【加的列在表的最后面】
例:alter table test add username char(20) not null default '';
alter table test add birth date not null default '0000-00-00';

②alter table 表名 add 列名 列类型 列参数 after 某列【把新列加在某列后面】
例:alter table test add gender char(1) not null default '' after username;

③alter table 表名 add 列名 列类型 列参数 first【把新列加在最前面】
例:alter table test add pid int not null default 0 first;

=========================

/*以unix时间戳形式返回当前时间*/
select unix_timestamp(now());

alter table t1 change b b bigint not null;
alter table infos change list list tinyint not null default '0';

/*从右边开始返回2个长度的字符串*/
SELECT RIGHT('mysql数据函数',2);

9、表数据中的增删改查
(全部插入)insert into 表名 valus('','',……,'');
(部分插入)insert into 表名(列x,列y,……,列z) valus('','',……,'');
insert into wifi_test values("1234567890ab","201506161330","300");
insert into wifi_test values("1234567890ad","201506161332",0);
 insert into wifi_test(dev_id,timestamp) values("1234567890ag","201506181405");
 insert into wifi_test(timestamp) values("201506181505");
select * from wifi_test;

/*返回指定位置的字符串 数据/*
SELECT ELT(2,'mysql','数据','函数');

mysql> select * from wifi_data where dev_id like "0023-AABBCCCCBBAA" ;

#删除用户
delete from user where User='用户名' and Host='localhost';
flush privileges;

只查询列名和注释
select  column_name, column_comment from information_schema.columns where table_schema ='testdb'  and table_name = 'tb_content' ;

13)获取所有表名并删除所有表
select concat('drop table',table_name,';') from information_schema.`tables` where table_schema='数据库名';

ALTER TABLE person ADD UNIQUE name_unique_index (`name`);
为name这一列创建了唯一索引,索引的名字是name_unique_index.

/*将指定字段转换为date类型*/
select CAST(now() AS date);

在mysql中我们对数据表字段的修改命令只要使用alter就可以了,下面我来给大家详细介绍mysql中修改表字段名/字段长度/字段类型等等一些方法介绍,有需要了解的朋友可参考。

返回与第1个参数的字符串匹配的位置
SELECT FIELD('my','mql','ysql','my','mysql','my');

alter table t1 change a b integer;

查看编码信息
show variables like 'character_set%';

ALTER TABLE person DROP INDEX birthday_index;
ALTER TABLE person DROP INDEX name_unique_index;

/*返回当前使用的数据库名称 mysql*/
select DATABASE()、SCHEMA();

1、显示数据库列表。
show databases;
刚开始时才两个数据库:mysql和test。mysql库很重要它里面有MYSQL的系统信息,我们改密码和新增用户,实际上就是用这个库进行操作。

11)删除数据库
drop database 数据库名;

ALTER TABLE person MODIFY number BIGINT NOT NULL;
或者是把number修改为id,类型为bigint:

/*返回当前使用的数据库使用的字符集 排序方式 自增长ID值 utf8mb4 utf8mb4_general_ci 1*/
select CHARSET('数据库'),COLLATION('数据库'),LAST_INSERT_ID();

把表默认的字符集和所有字符列(CHAR, VARCHAR, TEXT)改为新的字符集:

/*返回时间 01:00:00*/
select SEC_TO_TIME(3600);

 

所有主机:%,指定主机:ip地址或域名,通配符:*,某一个网段:192.168.1.0/255.255.225.0

ALTER TABLE person DISABLE KEYS;
ALTER TABLE...DISABLE KEYS让MySQL停止更新MyISAM表中的非唯一索引。

/*返回日期与0年1月1日之间的天数*/
select TO_DAYS(now());

iptables -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
打开防火墙配置文件可查看当前防火墙情况:vi  /etc/sysconfig/iptables
iptables-save
sudo service iptables save
iptables-save, service iptables save 作用一样,只不过iptables-save需要手动指定路径及文件名,而service iptables save的文件为/etc/sysconfig/iptables
注意:增加的开放3306端口的语句一定要在icmp-host-prohibited之前

/*返回翻转字符串后的字符串 数函据数lqsym/*
SELECT REVERSE('mysql数据函数');

//主键
alter table tabelname add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id);
//增加一个新列
alter table t2 add d timestamp;
alter table infos add ex tinyint not null default ‘0′;
//删除列
alter table t2 drop column c;
//重命名列
alter table t1 change a b integer;
//改变列的类型
alter table t1 change b b bigint not null;
alter table infos change list list tinyint not null default ‘0′;
//重命名表
alter table t1 rename t2;
加索引
mysql> alter table tablename change depno depno int(5) not null;
mysql> alter table tablename add index 索引名 (字段名1[,字段名2 …]);
mysql> alter table tablename add index emp_name (name);
加主关键字的索引
mysql> alter table tablename add primary key(id);
加唯一限制条件的索引
mysql> alter table tablename add unique emp_name2(cardnumber);
删除某个索引
mysql>alter table tablename drop index emp_name;
增加字段:
mysql> ALTER TABLE table_name ADD field_name field_type;
修改原字段名称及类型:
mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;
删除字段:
mysql> ALTER TABLE table_name DROP field_name;
mysql修改字段长度
alter table 表名 modify column 字段名 类型;
例如
数据库中user表 name字段是varchar(30)
可以用
alter table user modify column name varchar(50) ;

/*返回字符串的md5码 dad661767e9d552e4b689cd98536b8f7*/
select MD5(user());

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

/*将 以unix时间戳形式返回当前时间 转换为 普通日期时间*/
select from_unixtime(unix_timestamp(now()));

mysql> ALTER TABLE table_name DROP field_name;

/*返回时间 838:59:59*/
select SEC_TO_TIME(UNIX_TIMESTAMP());
select SEC_TO_TIME(UNIX_TIMESTAMP(now()));

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

/*将指定字段转换为date类型*/
select CONVERT(now(),date);

ALTER TABLE person CHANGE name name varchar(255) CHARACTER SET utf8;
仅仅改变一个表的默认字符集

/*返回 指定运行now() 100次数后的时间*/
select BENCHMARK(100,now());

先来看看常用的方法
MySql的简单语法,常用,却不容易记住。当然,这些Sql语法在各数据库中基本通用。下面列出:
1.增加一个字段
alter table user add COLUMN new1 VARCHAR(20) DEFAULT NULL; //增加一个字段,默认为空
alter table user add COLUMN new2 VARCHAR(20) NOT NULL;    //增加一个字段,默认不能为空
2.删除一个字段
alter table user DROP COLUMN new2;                //删除一个字段
3.修改一个字段
alter table user MODIFY new1 VARCHAR(10);            //修改一个字段的类型
alter table user CHANGE COLUMN new1 new4 varchar(512) default "";              //修改一个字段的名称,此时一定要重新指定该字段的类型

#显示状态变量
mysqladmin extented-status -u username -p password G;

MySQL复制表结构及数据到新表
CREATE TABLE tab_new SELECT * FROM tab_old

16)用户及权限管理
#创建用户并授权
#允许本地访问
create user 'mike'@'localhost' identified by '123456';

查询表中列的注释信息
select * from information_schema.columns where table_schema = 'testdb';
select * from information_schema.columns where table_schema = 'testdb' and table_name = 'tb_content';

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
default-character-set=utf8
collation-server=utf8-unicode_ci
init-connect='SET NAMES utf8'
character-set-server=utf8

alter table t1 rename t2;

修改当前用户密码
set password=password('新密码');

查询列

①desc 表名【查询所有列】
例: desc test;
mysql> desc department;
------- ------------- ------ ----- --------- -------
| Field | Type | Null | Key | Default | Extra |
------- ------------- ------ ----- --------- -------
| dId | int(11) | NO | PRI | | |
| dName | varchar(32) | YES | | NULL | |
------- ------------- ------ ----- --------- -------

②show columns from 表名【效果和desc一样】
mysql> show columns from department;
------- ------------- ------ ----- --------- -------
| Field | Type | Null | Key | Default | Extra |
------- ------------- ------ ----- --------- -------
| dId | int(11) | NO | PRI | | |
| dName | varchar(32) | YES | | NULL | |
------- ------------- ------ ----- --------- -------

③show create table 表名【查看表的创建代码】
mysql> show create table department;
CREATE TABLE `department` (
`dId` int(11) NOT NULL,
`dName` varchar(32) DEFAULT NULL,
PRIMARY KEY (`dId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

总结一下对mysql表结构的修改操作,包括对字段及索引的修改操作: 添加字段: alter table `user_movement_log` Add column GatewayId int not null default 0 AFTER `Regionid` (在哪个字段后面添加) 删除字段: alter table `user_movement_log` drop column Gatewayid 调整字段顺序: ALTER TABLE `user_movement_log` CHANGE `GatewayId` `GatewayId` int not null default 0 AFTER RegionID //主键 alter table tabelname add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id); //增加一个新列 alter table t2 add newfield timestamp; (简单语法,只指定新添字段newfield 类型为timestamp) alter table infos add newfield tinyint not null default '0'; (同时指定字段null属性、默认值) //删除列 alter table t2 drop column c; (删除列c) //重命名列 alter table t1 change a b integer; (把列a改名为b,注意要指定列类型) //改变列的类型 alter table t1 change b b bigint not null; (把列a改名为b,指定列类型及null属性) alter table infos change list list tinyint not null default '0'; //重命名表 alter table t1 rename t2; 加索引 mysql> alter table tablename change depno depno int(5) not null; mysql> alter table tablename add index 索引名 (字段名1[,字段名2 …]); mysql> alter table tablename add index emp_name (name); 加主关键字的索引 mysql> alter table tablename add primary key(id); 加唯一限制条件的索引 mysql> alter table tablename add unique emp_name2(cardnumber); 删除某个索引 mysql>alter table tablename drop index emp_name; 修改表: 增加字段: mysql> ALTER TABLE table_name ADD field_name field_type; 修改原字段名称及类型: mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type; 删除字段: mysql> ALTER TABLE table_name DROP field_name;

ORDER BY _column1, _column2; /* _column1升序,_column2升序 */
ORDER BY _column1, _column2 DESC; /* _column1升序,_column2降序 */
ORDER BY _column1 DESC, _column2 ; /* _column1降序,_column2升序 */
ORDER BY _column1 DESC, _column2 DESC; /* _column1降序,_column2降序 */
用 DESC 表示按倒序排序(即:从大到小排序)
用 ACS 表示按正序排序(即:从小到大排序)

 

order应该是以汉字的 ASCII 码排序,下面是按照汉字拼音排序
select * from corp_data where Chengshi like "图木舒克" order by convert(name using gbk);

#select count(*) as count from corp_data where Chengshi like "图木舒克";
select * from corp_data where Chengshi like "图木舒克" order by convert(name using gbk);
#delete from corp_data where Chengshi like "图木舒克";
#desc corp_data;
#alter table corp_data modify column hangye varchar(100) ;
#alter table corp_data modify column jianjie varchar(10000) ;

 

 

 

 

 

mysql如何查询最新的5条数据
select * from tb_content where id=(select msx(id) from tb_content) limit 0,5;

jdbcTemplate.execute("CREATE
建数据库不合适,要手动drop database ,再create database.

查总数:
select count(*) as count from tb_content;
select count(id) as count from tb_content;
navicat->分析表->取得表的总数

select * from corp_data where Chengshi like "上海";
select * from corp_data where Shengzhixiashi like "河南";

select name ,count(distinct(url)) from corp_data group by  name;
select Chengshi ,count(distinct(url)) from corp_data group by  Chengshi;
select Chengshi ,count(*) from corp_data group by  Chengshi;
select count(*) from corp_data where Chengshi like "北京";
select * from corp_data where Chengshi like "北京";
select name ,url  from corp_data limit 1;
select *  from corp_data limit 1;

select *  from corp_data limit 10;

select count(*) as count from corp_data limit 10;
select count(distinct(name)) as count from corp_data;

 varchar(N),  这里的N是指字符数,并不是字节数.占用的字节数与编码有关
utf-8, 一个汉字3字节 英文字母1字节

CREATE TABLE IF NOT EXISTS corp_url_all_20150909 (id int(11) NOT NULL AUTO_INCREMENT,url varchar(500),PRIMARY KEY (id))
insert into corp_url_all_20150909 select * from corp_url_all;

 

备份:

mysql> create table corp_url_all_20150909 select * from corp_url_all;
Query OK, 3186326 rows affected (1 min 14.19 sec)
Records: 3186326  Duplicates: 0  Warnings: 0
mysql> select count(distinct(url)) as count from corp_url_all_20150909;
--------
| count  |
--------
| 357399 |
--------
1 row in set (11.21 sec)

去重:

create table corp_url_all_single select distinct(url) from corp_url_all_20150909;
select count(distinct(url)) as count from corp_url_all_single;

 

 

 

 

 

基本的SQL语句

二、数据定义语言(DDL)

数据定义语言:Data Definition Language。如CREATE, DROP,ALTER等语句。需要注意的是,数据库中的命令不区分大小写

建数据库,命名为mydb:

CREATE DATABASE mydb; 

注:如若要删除数据库,将“create”改为“drop”即可。

创建一个名为teacher的表:

分分快三全天计划网站 1

CREATE TABLE teacher(
id int primary key auto_increment,
name varchar(20),
gender char(1),
age int(2),
birth date,
description varchar(100)
);

分分快三全天计划网站 2

查看表结构:

desc teacher; 

需要注意的是:主键是用来唯一代表一条记录的字段(主键值必须是唯一)

修改表结构(ALTER TABLE语句):(假设表名为test)

  • alter table test add column job varchar(10); --添加表列
  • alter table test rename test1; --修改表名
  • alter table test drop column name; --删除表列
  • alter table test modify address char(10) --修改表列类型(改类型)
  • alter table test change address address1  char(40) --修改表列类型(改名字和类型,和下面的一行效果一样)
  • alter table test change column address address1 varchar(30)--修改表列名(改名字和类型)

自动增长

alter table tab_teacher change id id int auto_increment;

 

增加数据

insert into tab_teacher(name,gender,age,createDate) values('smyh','b',22,now()); 

now()函数可以调出当前系统的时间。

 

修改数据(UPDATE … SET语句):

UPDATE 表名 SET 字段1名=值,字段2名=值,字段3名=值 where 字段名=值; 

注:where后面的部分表示修改的条件。修改时,按照字段1、字段2、字段3的先后顺序修改。

 

删除数据

DELETE FROM 表名; 

 

删除ID为1的记录:

DELETE FROM 表名 where id=1; 

 

四、数据查询语言(DRL):

数据查询语言(Data Retrieval Language --DRL):SELECT语句。

在实际开发中,数据查询语言用的是最多的。注:实际开发中,要避免使用通配符"*"(通配符代表所有字段),因为系统要先解析出所有的字段名,将其还原为真实的名字,然后再进行操作。如果记录数据库内容特别多,会影响效率。

查询所有信息:

select id,name,gender,age,job,createDate from tab_teacher; 

 

在没有表被引用的情况下,允许指定DUAL作为一个假的表名:

DUAL是虚拟表。也就是说,表中没有这个数据,但是要执意使用sql语句,系统就会用这个虚拟表来满足你。举例效果如下:

分分快三全天计划网站 3

查询ID为2的老师信息:

select * from tab_teacher where id=2; 

 

查询职业为空的老师信息:

select * from tab_teacher where job is null;#字段为空用“job is null”来表示,而不是“job=null”。

 

查询ID为2的老师的姓名和性别:

select name,gender from tab_teacher where id=2; 

 

查询性别为女和指定日期的老师信息:

select $ from tab_teacher where gender='w' and ceateDate='2014-10-14'; #并且用“and”,或用“or”。

 

查询性别为男或者ID小于2的老师:

select * from tab_teacher where gender='m' or id<2; 

 

查询姓名的最后一个字符为“e”的老师:(关键字:like '% 指定字符')

select * from tab_teacher where name like '%e'; 

 

查询姓名以指定字符开头的老师:

select * from tab_teacher where name like 's%'; 

 

查询姓名中包含“m”的老师:

select * from tab_teacher where name like '%m%'; 

 

查询所有老师信息,并按日期降序或者升序排列:(ORDER BY 字段, 默认为升序:ASC/降序:DESC

  • 升序:

    select * from tab_teacher order by createDate; #最后一个单词ASC可写可不写,因为默认为升序

 

  • 降序:

    select * from tab_teacher order by createDate desc; 

 

多个排序条件:(当第一个条件相同时,按照第二个条件排序)

例如:上面图片的排序中,是按照日期降序排序的,但是id为2和id为3的日期相同,这两条记录按照默认顺序排序。

举例:现在排序的第一条件为:日期降序,第二条件为:年龄降序。多个排序条件用逗号隔开。命令为:

select * from tab_teacher order by createDate desc,age desc;

 

按性别分组查询男女老师的人数(GROUP BY ):

select gender,count(gender) from tab_teacher group by gender;

上面的命令表示,查找gender这个字段;并计算gender有多少个(count(gender));然后按照gender里的内容将个数进行分组(group by gender)。

问题:我觉得应该是先分组,再查询性别,再数性别的个数。

注:这里面用到了count()这个内置的函数。关于count()这个聚合函数,将在下一篇文章中讲到。

正确的效果和错误的效果如下:

分分快三全天计划网站 4

按性别分组,查询出女老师人数的总数:

select gender,count(gender) from tab_teacher group by gender having gender='m';

这里在上一个命令的基础上,增加了一个关键字:having。having关键字专门用来在分组之后添加的条件。意思是:先求总数,然后进行分组,然后再从分组里找到字段等于“m”的个数。注:不能用where作为关键字,因为where是加在分组之前的条件。

显示效果如下:

分分快三全天计划网站 5

注:这种写法操作繁琐,效率较低,having语句尽量少用,可以用其他语句代替,例如:

select gender,count(gender) from tab_teacher where gender='m';

效果是一样的。

查询表的总记录数:

  • 方式一:(不推荐)

    select count(*) from tab_teacher;

  • 方式二:(使用主键的方式查询)

    select count(id) from tab_teacher;

方式一将空记录也包含进来了。所以采用方式二,因为主键唯一,且不能为空,此时id总数则代表总记录数。

别名的用法:

  • 使用别名查询表的总记录数:

    select count(id) as counts from tab_teacher; 

不用别名和用别名的效果对比如下:(命令中的"as"可以省略)

分分快三全天计划网站 6

  • 使用别名来代替表的名字:

举例:将表的名字起为t。

select t.name,t.age from tab_teacher t;

分分快三全天计划网站 7

当表名比较长,或者同时有多个表存在时,用别名就显得非常方便了。

查询老师记录的前三条(从0位置开始找出3条):(非常有用)

select * from tab_teacher limit 0,3;

命令解释:limit后面有两个参数:第一个参数是起始位置,第二个参数是偏移量。声明:第一条记录的id不管为多少,索引位置都是0。

注:这种分页查询方式非常有用,例如使用百度搜索时,告诉你总记录数,但是会分页显示。

 

五、事务控制语言(TCL):

事务控制语言:Transaction Control Language--TCL。如COMMIT,ROLLBACK语句。

1、事务:

事务(Transaction)的概念:事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。在关系数据库中,一个事务可以是一条SQL语句,一组SQL语句或整个程序。

事务的属性:原子性(atomicity)、一致性(consistency)、隔离性(isolation)、持续性(durability)。这四个属性通常称为ACID特性。

事务的特点:事务就是对数据库的多步操作,要么一起成功,要么一起失败。

总结:事务就是可以把多步操作看成一个整体,这个整体要么一起成功,要么一起失败。

2、事务的提交和回滚命令:

设置默认事务提交方式:(默认为true)

  • 设置事务提交方式为“手动提交”:

    set autocommit = false;

  • 设置事务提交方式为“自动提交”:

    set autocommit = true;

手动提交事务:

commit;

回滚事务:

rollback;

 

注:只要还没手动commit提交,一旦回滚,之前的修改操作都将清零。

注:commit命令和rollback命令只有在提交方式为“手动提交”时,才可以用。

3、保存还原点的命令:

当数据未commit之前,增删改查的操作都是暂时保存在内存之中的,当我们修改操作进行到某一步时,可以给这一步设置一个还原点,方便以后回滚到此还原点(类似于PS当中的快照)。

保存还原点:

savepoint name_point;

回滚到指定还原点:

rollback to name_point;

 

 

 

 

 九、MySQL的一些函数:
  1、字符串链接——CONCAT()
    >SELECT CONCAT(name,'=>',score) FROM tb_name

  2、数学函数:
    AVG、SUM、MAX、MIN、COUNT;

  3、文本处理函数:
    TRIM、LOCATE、UPPER、LOWER、SUBSTRING

  4、运算符:
     、-、*、

  5、时间函数:
    DATE()、CURTIME()、DAY()、YEAR()、NOW().....

 十、分组查询:
   1、分组查询可以按照指定的列进行分组:
    >SELECT COUNT(*) FROM tb_name GROUP BY score HAVING COUNT(*)>1;
  2、条件使用Having;
  3、ORDER BY 排序:
    ORDER BY DESC|ASC    =>按数据的降序和升序排列

十一、UNION规则——可以执行两个语句(可以去除重复行)
 十二、全文检索——MATCH和AGAINST
  1、SELECT MATCH(note_text)AGAINST('PICASO') FROM tb_name;
  2、InnoDB引擎不支持全文检索,MyISAM可以;

 十三、视图

  1、创建视图

    >CREATE VIEW name AS SELECT * FROM tb_name WHERE ~~ ORDER BY ~~;

  2、视图的特殊作用:

      a、简化表之间的联结(把联结写在select中);

      b、重新格式化输出检索的数据(TRIM,CONCAT等函数);

      c、过滤不想要的数据(select部分)

      d、使用视图计算字段值,如汇总这样的值。

 十四、使用存储过程:

  个人理解,存储过程就是一个自定义函数,有局部变量参数,可传入参数,可以返回值,不过这语法够呆滞的~~~

  1、创建存储过程:

    >CREATE PROCEDURE pro(

    >IN num INT,OUT total INT)

    >BEGIN

    >SELECT SUM(score) INTO total FROM tb_name WHERE id=num;

    >END;

   ***这里的  IN (传递一个值给存储过程),OUT(从存储过程传出一个值),INOUT(对存储过程传入、传出),INTO(保存变量)

  2、调用存储过程:

    >CALL pro(13,@total)      //这里的存储过程两个变量,一个是IN一个是OUT,这里的OUT也是需要写上的,不写会出错

    >SELECT @total         //这里就可以看到结果了;

  3、存储过程的其他操作:

    >SHOW PROCEDURE STATUS;      //显示当期的存储过程

    >DROP PROCEDURE pro;         //删除指定存储过程

十五、使用游标:

  对这个理解不是很懂,朋友多多指点哦~~~

   1、游标的操作

    >CREATE PROCEDURE pro()

    >BEGIN

    >DECLARE ordername CURSOR FOR

    >SELECT order_num FROM orders;

    >END;

    

    >OPEN ordername;    //打开游标

 

    >CLOSE ordername;    //关闭游标

十六、触发器:

  触发器是指在进行某项指定操作时,触发触发器内指定的操作;

  1、支持触发器的语句有DELETE、INSERT、UPDATE,其他均不支持

  2、创建触发器:

    >CREATE TRIGGER trig AFTER INSERT ON ORDERS FOR EACH ROW SELECT NEW.orser_name;

    >INSERT语句,触发语句,返回一个值

  3、删除触发器

    >DROP TRIGGER trig;

十七、语法整理:

  1、ALTER TABLE(修改表)

    ALTER TABLE table_name

    (  ADD    column  datatype    [ NULL | NOT NULL ]  [ CONSTRAINTS ]

       CHANGE  column   datatype   COLUMNS  [ NULL | NOT NULL ]   [ CONSTRAINTS ]

       DROP    column,

       。。。。

    )

  2、COMMIT(处理事务)

    >COMMIT;

   3、CREATE INDEX(在一个或多个列上创建索引)

    CREATE INDEX index_name ON tb_name (column [ ASC | DESC ] , .......);

   4、CREATE PROCEDURE (创建存储过程)

    CREATE PROCEDURE pro([ parameters ])

    BEGIN

    ........

    END

   5、CREATE TABLE(创建表)

    CREATE TABLE tb_name(

    column_name  datetype  [ NULL | NOT NULL ]   [ condtraints]   ,

    column_name  datetype  [ NULL | NOT NULL ]   [ condtraints]   ,

    .......

    PRIMARY KEY( column_name )

    )ENGINE=[  InnoDB | MyiSAM ]DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

   6、CREATE USER(创建用户)

    CREATE USER user_name [ @hostname ] [ IDENTIFIED BY [ PASSWORD ] 'pass_word' ];

   7、CREATE VIEW (在一个或多个表上创建视图)

    CREATE [ OR REPLACE ] VIEW view_name AS SELECT。。。。。。

   8、DELETE (从表中删除一行或多行)

    DELETE FROM table_name [WHERE ......]

   9、DROP(永久删除数据库及对象,如视图、索引等)

    DROP DATEBASE | INDEX | PROCEDURE | TABLE | TRIGGER | USER | VIEW  name

   10、INSERT (给表添加行)

    INSERT INTO tb_name [ ( columns,...... ) ]  VALUES(value1,............);

    使用SELECT值插入:

    INSERT INTO tb_name [ ( columns,...... ) ]

    SELECT columns , .......   FROM tb_name [ WHERE ...... ] ;

   11、ROLLBACK(撤销一个事务处理块)

    ROLLBACK [  TO  savapointname  ];

   12、SAVEPOINT(为ROLLBACK设置保留点)

    SAVEPOINT sp1;

   13、SELECT (检索数据,显示信息)

    SELECT column_name,.....FROM tb_name  [ WHERE ]   [ UNION ]    [ RROUP BY ]   [ HAVING ]   [ ORDER BY ]

   14、START TRANSACTION (一个新的事务处理块的开始)

    START TRANSACTION

   15、UPDATE(更新一个表中的一行或多行)

    UPDATE tb_name SET column=value,......[ where ]

 

其它mysql基本操作:

几个最常用的api函数
mysql_affected_rows()     返回被最新的UPDATE, DELETE或INSERT查询影响的行数。
mysql_close()     关闭一个服务器连接。
mysql_errno()     返回最近被调用的MySQL函数的出错编号。
mysql_error()     返回最近被调用的MySQL函数的出错消息。
mysql_fetch_row()     从结果集合中取得下一行。
mysql_field_count()     返回最近查询的结果列的数量。
mysql_init()     获得或初始化一个MYSQL结构。
mysql_insert_id()     返回有前一个查询为一个AUTO_INCREMENT列生成的ID。
mysql_num_rows()     返回一个结果集合中的行的数量。
mysql_query()     执行指定为一个空结尾的字符串的SQL查询。
mysql_real_connect()     连接一个MySQL服务器。
mysql_real_query()     执行指定为带计数的字符串的SQL查询。

mysql常用字符串操作函数大全,以及实例

CREATE TABLE tab2 AS (SELECT * FROM tab1)
这种做法表的存储引擎也会采用服务器默认的存储引擎而不是源表的存储引擎,此种复制方法把表的内容也一起复制过来了。

CREATE TALBE tab2 ENGINE=MYISAM, CHARSET='UTF8' AS (SELECT * FROM tab1)
可以自己指定存储引擎和字符集,弥补方法一的不足

CREATE TABLE tab2 LIKE tab1
使用和tab1表相同的结构来创建一个新表,列名、数据类型、空指和索引也将复制,但是表的内容不会被复制。外键和专用的权限也没有被复制。

MySQL复制表结构及数据到新表
CREATE TABLE tab_new SELECT * FROM tab_old

复制旧表的数据到新表(假设两个表结构一样)
INSERT INTO tab1 SELECT * FROM tab2

复制旧表的数据到新表(假设两个表结构不一样)
INSERT INTO tab1(字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM tab2

更改表名
ALTER TABLE employee RENAME TO staff

更改列类型
ALTER TABLE employee MODIFY COLUMN truename VARCHAR(10) NOT NULL DEFAULT ''

更改列名
ALTER TABLE employee CHANGE COLUMN truename employeename VARCHAR(10) NOT NULL DEFAULT ''

添加默认值
ALTER TABLE employee ALTER COLUMN truename SET DEFAULT ''

删除默认值
ALTER TABLE employee ALTER COLUMN truename DEOP DEFAULT

mysql修改表
表的结构如下:

mysql> show create table person;
| person | CREATE TABLE `person` (
`number` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`birthday` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
删除列:

ALTER TABLE person DROP COLUMN birthday; 
添加列:

ALTER TABLE person ADD COLUMN birthday datetime;
修改列,把number修改为bigint:

ALTER TABLE person MODIFY number BIGINT NOT NULL;
或者是把number修改为id,类型为bigint:

ALTER TABLE person CHANGE number id BIGINT;

添加主键:

ALTER TABLE person ADD PRIMARY KEY (id);
删除主键:

ALTER TABLE person DROP PRIMARY KEY;
添加唯一索引:

ALTER TABLE person ADD UNIQUE name_unique_index (`name`);
为name这一列创建了唯一索引,索引的名字是name_unique_index.

 

添加普通索引:

ALTER TABLE person ADD INDEX birthday_index (`birthday`);

删除索引:

ALTER TABLE person DROP INDEX birthday_index;
ALTER TABLE person DROP INDEX name_unique_index;

禁用非唯一索引

ALTER TABLE person DISABLE KEYS;
ALTER TABLE...DISABLE KEYS让MySQL停止更新MyISAM表中的非唯一索引。

激活非唯一索引

ALTER TABLE person ENABLE KEYS;
ALTER TABLE ... ENABLE KEYS重新创建丢失的索引。

 

把表默认的字符集和所有字符列(CHAR, VARCHAR, TEXT)改为新的字符集:

ALTER TABLE person CONVERT TO CHARACTER SET utf8;
修改表某一列的编码

ALTER TABLE person CHANGE name name varchar(255) CHARACTER SET utf8;
仅仅改变一个表的默认字符集

ALTER TABLE person DEFAULT CHARACTER SET utf8;
修改表名

RENAME TABLE person TO person_other;
移动表到其他数据库

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

 

在mysql中我们对数据表字段的修改命令只要使用alter就可以了,下面我来给大家详细介绍mysql中修改表字段名/字段长度/字段类型等等一些方法介绍,有需要了解的朋友可参考。

先来看看常用的方法
MySql的简单语法,常用,却不容易记住。当然,这些Sql语法在各数据库中基本通用。下面列出:
1.增加一个字段
alter table user add COLUMN new1 VARCHAR(20) DEFAULT NULL; //增加一个字段,默认为空
alter table user add COLUMN new2 VARCHAR(20) NOT NULL;    //增加一个字段,默认不能为空
2.删除一个字段
alter table user DROP COLUMN new2;                //删除一个字段
3.修改一个字段
alter table user MODIFY new1 VARCHAR(10);            //修改一个字段的类型
alter table user CHANGE new1 new4 int;              //修改一个字段的名称,此时一定要重新

//主键
alter table tabelname add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id);
//增加一个新列
alter table t2 add d timestamp;
alter table infos add ex tinyint not null default ‘0′;
//删除列
alter table t2 drop column c;
//重命名列
alter table t1 change a b integer;
//改变列的类型
alter table t1 change b b bigint not null;
alter table infos change list list tinyint not null default ‘0′;
//重命名表
alter table t1 rename t2;
加索引
mysql> alter table tablename change depno depno int(5) not null;
mysql> alter table tablename add index 索引名 (字段名1[,字段名2 …]);
mysql> alter table tablename add index emp_name (name);
加主关键字的索引
mysql> alter table tablename add primary key(id);
加唯一限制条件的索引
mysql> alter table tablename add unique emp_name2(cardnumber);
删除某个索引
mysql>alter table tablename drop index emp_name;
增加字段:
mysql> ALTER TABLE table_name ADD field_name field_type;
修改原字段名称及类型:
mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;
删除字段:
mysql> ALTER TABLE table_name DROP field_name;
mysql修改字段长度
alter table 表名 modify column 字段名 类型;
例如
数据库中user表 name字段是varchar(30)
可以用
alter table user modify column name varchar(50) ;

 

 

 

 

mysql更改表结构:添加、删除、修改字段、调整字段顺序
mysqltablenulluserlist
添加字段:

alter table `user_movement_log`
Add column GatewayId int not null default 0 AFTER `Regionid` (在哪个字段后面添加)

删除字段:

alter table `user_movement_log` drop column Gatewayid

调整字段顺序:

ALTER TABLE `user_movement_log` CHANGE `GatewayId` `GatewayId` int not null default 0 AFTER RegionID

//主键

alter table tabelname add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id);

//增加一个新列

alter table t2 add d timestamp;
alter table infos add ex tinyint not null default '0';

//删除列

alter table t2 drop column c;

//重命名列

alter table t1 change a b integer;

//改变列的类型

alter table t1 change b b bigint not null;
alter table infos change list list tinyint not null default '0';

//重命名表

alter table t1 rename t2;

加索引

mysql> alter table tablename change depno depno int(5) not null;
mysql> alter table tablename add index 索引名 (字段名1[,字段名2 …]);
mysql> alter table tablename add index emp_name (name);

加主关键字的索引

mysql> alter table tablename add primary key(id);

加唯一限制条件的索引

mysql> alter table tablename add unique emp_name2(cardnumber);

删除某个索引

mysql>alter table tablename drop index emp_name;

修改表:

增加字段:

mysql> ALTER TABLE table_name ADD field_name field_type;

修改原字段名称及类型:

mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

删除字段:

mysql> ALTER TABLE table_name DROP field_name;

 

 

 

 

 

 

MySql表结构修改详解

6)列出所有表
show tables;

解决方法:
1、在不重启MySQL服务的情况下,只需要在登录的时候加上-h参数。
例如:(a). /usr/local/mysql/bin/mysql -uroot -p123456 -h::1
         (b)./usr/local/mysql/bin/mysql -uroot -p123456 -h127.0.0.1
2、需要重启MySQL。把skip-name-resolve参数去掉  ---- 还没验证。不过我记得,如果去掉了,日志里面会有大量的警告信息。我是因为那些警告信息,才添加的这个参数。

/*创建唯一索引*/
create unique index idx_phone_02 on person(phone);
alter table person add unique index idx_phone_03(phone);

CREATE TABLE tab2 AS (SELECT * FROM tab1)
这种做法表的存储引擎也会采用服务器默认的存储引擎而不是源表的存储引擎,此种复制方法把表的内容也一起复制过来了。

删除一列
alter table t_user drop column address;

 

1、常用SQL语句

修改表:

/*将数字转换为IP地址 127.0.0.1*/
select INET_NTOA(INET_ATON('127.0.0.1'));

更改列类型
ALTER TABLE employee MODIFY COLUMN truename VARCHAR(10) NOT NULL DEFAULT ''

15)修改表名和列名
修改表名
alter table user rename t_user;

2,设置访问全部数据库权限
mysql>grant all privileges on *.* to 'root'@'%';
说明:设置用户名为root,密码为空,可访问所有数据库*

14)显示数据库的编码
show character set;
要更改数据库的编码,character set、collation 2个字段的值都要修改

加索引

/*返回日期对应的时间数字 64519*/
select TIME_TO_SEC(UTC_TIME());
select TIME_TO_SEC(CURTIME());
select TIME_TO_SEC(CURRENT_TIME);

修改列[modife 列名]

①alter table 表名 modify 列名 新类型 新参数【修改列类型】
例:alter table test modify gender char(4) not null default '';
②alter table 表名 change 旧列名 新列名 新类型 新参数【修改列名和列类型】
例:alter table test change pid uid int unsigned not null default 0;

=========================

/*删除指定索引*/
drop index idx_phone_00 on person;

ERROR 1044 (42000): Access denied for user 'root'@'%' to database
我这里root有三种解析登录方式分别是:Host: bidevedw_db、Host: ::1、Host: %
一般情况还有Host:127.0.0.1、Host:localhost我的里面把哪两种删了。
请注意 !
 注意上面每条记录的的红色字体部分Grant_priv: Y
这个表示,以这种方式解析登录的root用户,是否有grant权限,Y则表示有授权限给其他用户的权限,N表示没有。
这里恰好,就是我们要找的原因,因为我的/my.cnf文件里面有skip-name-resolve参数,所以root都是解析到@'%'方式登录,于是就没有grant_priv权限。

/*返回字符串比较后的数字 -1 不等 0 相等/*
select STRCMP('mysql数据函数','mysql数据函数');

 

#删除用户及权限
drop user '用户名'@'localhost';
drop user '用户名'@'%';

查看当前使用的数据库:
mysql>select database();
mysql>status;

 

//主键

/*返回指定2个日期时间相差的天数 11*/
select DATEDIFF(current_date() 1,current_date()-10);

//查看有哪些数据库
 mysql> show databases;
--------------------
| Database           |
--------------------
| information_schema |
| mysql              |
| test               |
--------------------

#开启profiling
set profiling=1;

删除索引:

/*返回指定时间加5秒后的日期时间 01:58:42*/
select ADDTIME(CURTIME(),5);

2、显示库中的数据表:
use mysql; //打开库,学过FOXBASE的一定不会陌生吧
show tables;

/*从左边开始返回2个长度的字符串*/
SELECT LEFT('mysql数据函数',2);

mysql> show create table person;
| person | CREATE TABLE `person` (
`number` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`birthday` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
删除列:

10)查看存储过程详细信息
select * from mysql.proc where db='mysql';
show procedure status where db='mysql';

6、删库和删表:
drop database 库名;
drop table 表名;

返回指定间隔符出现的位置左边或右边的字符串,第3个参数为整数,则为左边,否则为右边
SELECT SUBSTRING_INDEX('my@sql@数@据函数@命令','@',4);

MySql表结构修改详解

修改列名
alter table t_user chanage column address addr varchar(200);

use testdb;
简单描述表结构,字段类型:
desc tb_content;

/*返回将数字p1,从p2进制转换为p3进制*/
select CONV(10,10,2);

//改变列的类型

#显示系统变量
mysqladmin variables -u username -p password G;

 

/*查询表所有中索引*/
show index from person;

在创建数据库时报错
mysql> create database haha;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'haha' mysql>  
解决方案:这是因为在mysql的usr表中,存在用户名为空的账户,虽然登陆时用的是root,但是匿名登陆。删除user.user中值为NULL的,或更新NULL为test 1)delete from user where user is NULL
2)update user set user='test' where user is NULL

/*查看当前数据库的字符集*/
select CHARSET(CONVERT('数据库' USING utf8mb4));

如何远程访问MySQL数据库设置权限方法总结,讨论访问单个数据库,全部数据库,指定用户访问,设置访问密码,指定访问主机。
1,设置访问单个数据库权限
mysql>grant all privileges on test.* to 'root'@'%';
说明:设置用户名为root,密码为空,可访问数据库test

/*创建普通索引*/
create index idx_phone_00 on person(phone);
alter table person add index idx_phone_01(phone);

UPDATE语法可以用新值更新原有表行中的各列。SET子句指示要修改哪些列和要给予哪些值。WHERE子句指定应更新哪些行。如果没有WHERE子句,则更新所有的行。如果指定了ORDER BY子句,则按照被指定的顺序对行进行更新。LIMIT子句用于给定一个限值,限制可以被更新的行的数目。

#刷新权限
flush privileges;

 

/*创建表并插入数据*/
create table person(
id smallint(5) unsigned auto_increment primary key not null comment '主键id',
name varchar(50) not null comment '用户名',
sex tinyint(2) not null comment '性别',/*1 男 2 女 3 未知*/
age tinyint(2) not null comment '年龄',
phone varchar(11) comment '手机号',
address varchar(128) comment '当前住址',
statusinfo varchar(128) comment '各种状态简述:婚姻 收入 职业 信仰 兴趣爱好 信用状况 资产状况等',
describes varchar(1024) comment '当前所处的状况描述'
)ENGINE=innodb default charset=utf8;

修改表的语法

返回以指定第3个参数替换第2个参数后的字符串 mysql数据语句
SELECT REPLACE('mysql数据函数','函数','语句');

ALTER TABLE person DROP PRIMARY KEY;
添加唯一索引:

/*创建主键索引*/
alter table person add primary key(id);

mysql更改表结构:添加、删除、修改字段、调整字段顺序
mysqltablenulluserlist
添加字段:

1)常用函数
/*
type可取值为:
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
*/

//增加一个新列

/*参数1不为null,返回参数1的值,否则返回参数2的值*/
select ifnull('非空','空');

mysql> alter table tablename change depno depno int(5) not null;
mysql> alter table tablename add index 索引名 (字段名1[,字段名2 …]);
mysql> alter table tablename add index emp_name (name);

/*返回100次空格/*
SELECT SPACE(100);

//选择数据库
mysql>use test;  //;号可有可无,必须单行使用.  

/*对字符串进行encode编码 编码密码为 encode*/
select ENCODE('123456','encode');

多个表的UPDATE操作
UPDATE items,month SET items.price=month.price WHERE items.id=month.id;
以上的例子显示出了使用逗号操作符的内部联合,但是multiple-table UPDATE语句可以使用在SELECT语句中允许的任何类型的联合,比如LEFT JOIN,但是您不能把ORDER BY或LIMIT与multiple-table UPDATE同时使用。

/*返回指定格式的日期时间字符串*/
select str_to_date('2013-09-15 09:00:00', '%Y-%m-%d %H:%i:%S');

此问题是没有开启mysqld服务.

/*返回数据的版本 版本号 5.5.38-log 24*/
select version(),CONNECTION_ID();

更改列名
ALTER TABLE employee CHANGE COLUMN truename employeename VARCHAR(10) NOT NULL DEFAULT ''

/*返回数字的16进制*/
select OCT(10);

添加默认值
ALTER TABLE employee ALTER COLUMN truename SET DEFAULT ''

#用户通过本地IP访问数据库
grant all privileges on `testdb`.* to 'mike'@'localhost' identified by '123456';

为了找出当前的数据库包含什么表(例如,当你不能确定一个表的名字),使用这个命令:

/*指定数据库的字符集*/
select CONVERT('数据库' USING utf8mb4);

11.修改,更改表中的数据:
update 表名 set 列名='表达式' [from 另一表名] where [检索条件表达式];
update wifi_test set interval='300' where dev_id=1234567890ag;
update wifi_test set interval=replace(interval,127,80);
 update wifi_test set timestamp=20150618143333 where dev_id;

/*返回数字的8进制*/
select HEX(10);

8、显示表中的记录:
select * from 表名;

修改列数据类型
alter table t_user modify address varchar(300);

 

alter database mysql character set utf8 collate utf8_general_ci;

windows添加防火墙端口:
.需要设置防火墙放开mysql 3306端口的访问,控制面板->windows防火墙->高级设置,打开另一个界面,
  在该界面中选择入站规则,然后在右边栏选择 新建规则,下一步选择端口,下一步选择TCP/特定本地端口并输入3306,下一步选择允许连接,下一步默认选择,下一步输入你想给的名称比如mysqlport,然后点击完成。
打开防火墙,选择例外选项卡,添加端口
名称:mysqlport
端口号:3306
选中TCP
  设置好后,务必重新启动计算机

12)删除表
drop table 表名;

 如先输入mysql,成功启动后输入use mysql,出现如下错误:Access denied for user ''@'localhost' to database 'mysql'
还有,输mysql可以,输mysql -u root就出错了:
Access denied for user 'root'@'localhost' (using password: NO).
The reason is:
是昨日更新ROOT密码时出错
update user set password = '123456' where user ="root" //这样写是错的,密码其实不是123456
应该为update user set password = password ('123456') where user = "root"
具体操作步骤:
关闭mysql:
# service mysqld stop
然后:
# mysqld_safe --skip-grant-tables(该命令完成后,可能终端不会提示输入下一命令,下面命令在新开的终端中实现)
启动mysql:
# service mysqld start
mysql -u root
mysql> use mysql
mysql> UPDATE user SET Password=PASSWORD('xxx') WHERE user='root';
mysql> flush privileges;
mysql>q

3)查看数据库是否支持分区
show plugins;

增加列[add 列名]

①alter table 表名 add 列名 列类型 列参数【加的列在表的最后面】
例:alter table test add username char(20) not null default '';
alter table test add birth date not null default '0000-00-00';

②alter table 表名 add 列名 列类型 列参数 after 某列【把新列加在某列后面】
例:alter table test add gender char(1) not null default '' after username;

③alter table 表名 add 列名 列类型 列参数 first【把新列加在最前面】
例:alter table test add pid int not null default 0 first;

=========================

/*返回以@符号连接2个字符串*/
SELECT CONCAT_WS('@','mysql','数据函数');

RENAME TABLE person TO person_other;
移动表到其他数据库

#刷新权限
flush privileges;

 

/*返回字符串中字符的个数*/
SELECT CHAR_LENGTH('mysql数据函数');

 

/*将IP地址转换为数字 2130706433*/
select INET_ATON('127.0.0.1');

//看表里有哪些列
mysql> describe pet;
--------- ------------- ------ ----- --------- -------
| Field   | Type        | Null | Key | Default | Extra |
--------- ------------- ------ ----- --------- -------
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| ***     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
--------- ------------- ------ ----- --------- -------
Field显示列名字,Type是列的数据类型,Null表示列是否能包含NULL值,Key显示列是否被索引而Default指定列的默认值。

修改指定用户密码
set password for 用户名=password('新密码');

3,设置指定用户名访问权限
mysql>grant all privileges on *.* to 'liuhui'@'%';
说明:设置指定用户名为liuhui,密码为空,可访问所有数据库*

添加一列
alter table t_user add address varchar(100);

ERROR 1044 (42000): Access denied for user ''@'localhost' to database
 解决 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) 问题
最近新装好的mysql在进入mysql工具时,总是有错误提示:
# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
或者
# mysql -u root -p password 'newpassword'
Enter password:
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'
现在终于找到解决方法了。本来准备重装的,现在不必了。
方法操作很简单,如下:
# /etc/init.d/mysql stop
# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
# mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root' and host='root' or host='localhost';//把空的用户密码都修改成非空的密码就行了。
mysql> FLUSH PRIVILEGES;
mysql> quit # /etc/init.d/mysqld restart
# mysql -uroot -p
Enter password: <输入新设的密码newpassword>

5、建表:
use 库名;
create table 表名 (字段设定列表);
如下:
CREATE TABLE `wifi_test` (
 `dev_id` varchar(30) NOT NULL default '' COMMENT '设备ID',
 `timestamp` varchar(50) NOT NULL default '' COMMENT '时间',
 `interval` tinyint(1) NOT NULL default '1' COMMENT '上传间隔',
 PRIMARY KEY  (`dev_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

ALTER TABLE person ENABLE KEYS;
ALTER TABLE ... ENABLE KEYS重新创建丢失的索引。

修改列[modife 列名]

①alter table 表名 modify 列名 新类型 新参数【修改列类型】
例:alter table test modify gender char(4) not null default '';
②alter table 表名 change 旧列名 新列名 新类型 新参数【修改列名和列类型】
例:alter table test change pid uid int unsigned not null default 0;

=========================

alter table t2 drop column c;

调整字段顺序:

查看所有用户密码:
SELECT User, Password, Host FROM user;

update语法
Single-table语法:
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]
Multiple-table语法:
UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]

  root 用户的 create_priv 权限是否被设置为N了?

mysql> alter table tablename add unique emp_name2(cardnumber);

 

ALTER TABLE person ADD PRIMARY KEY (id);
删除主键:

ALTER TABLE person CHANGE number id BIGINT;

激活非唯一索引

mysql> alter table tablename add primary key(id);

7、将表中记录清空:
delete from 表名;

 

增加字段:

//重命名列

 
修改root密码
mysql> use mysql
Database changed
mysql> update user set password=PASSWORD('123456') where user='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查看表生成的DDL,这个命令虽然显示起来不是太容易看, 这个不是问题可以用G来结尾,使得结果容易阅读;该命令把创建表的DDL显示出来,于是表结构、类型,外键,备注全部显示出来了。我比较喜欢这个命令:输入简单,显示结果全面。
show create table tb_content;

开启远程连接:
2, 修改 Mysql-Server 用户配置
mysql> USE mysql; -- 切换到 mysql DB
Database changed
mysql> SELECT User, Password, Host FROM user; -- 查看现有用户,密码及允许连接的主机
------ ---------- -----------
| User | Password | Host      |
------ ---------- -----------
| root |          | localhost |
------ ---------- -----------
1 row in set (0.00 sec)
mysql> -- 只有一个默认的 root 用户, 密码为空, 只允许 localhost 连接
12
mysql> -- 下面我们另外添加一个新的 root 用户, 密码为空, 只允许 192.168.1.100 连接
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.100' IDENTIFIED BY '' WITH GRANT OPTION;
mysql> -- @'192.168.1.100'可以替换为@‘%’就可任意ip访问,当然我们也可以直接用 UPDATE 更新 root 用户 Host, 但不推荐, SQL如下:
mysql> -- UPDATE user SET Host='192.168.1.100' WHERE User='root' AND Host='localhost' LIMIT 1;
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

ALTER TABLE person DEFAULT CHARACTER SET utf8;
修改表名

4,设置密码访问权限
mysql>grant all privileges on *.* to 'liuhui'@'%' IDENTIFIED BY 'liuhui';
说明:设置指定用户名为liuhui,密码为liuhui,可访问所有数据库*

 

本文由分分快三计划发布,转载请注明来源

关键词: 分分快三计划 mysql