子插入:
INSERT INTO user_area(user_id,province_id,province_name)
SELECT 3,15000000,NAME FROM AREA a WHERE a.id = 15000000;
带order by的limit:SELECT id,user_name FROM (SELECT * FROM
mylog ORDER BY TIME) m LIMIT 0,10;
ps -aux | grep mysqld
linux下重启mysql[root@avatar /usr/local/etc/rc.d]# ./mysql-server
restart
解决死锁的方法:在my.ini或者my.cnf中设置innodb_lock_wait_timeout属性,默认是50秒,可以设置成10秒
Mysql函数
length(字段名称):返回字段长度
连接数据库
1连接数据库:返回数据库连接
2选择某个库:使用数据库连接
3指定输出编码方式
4确认sql语句
5执行sql语句:使用数据库连接,返回数据库结果
6分析结果集:使用数据库结果
7关闭数据库:使用数据库连接
一操作数据库
1显示所有数据库
show databases;
2创建数据库
Create database
dbname;
dbname是数据库名称
3删除数据库
Drop database
dbname;
dbname是数据库名称
4选择数据库
Use dbname;
dbname是数据库名称
5备份与恢复
备份
1备份一个数据库
today=`date
+"%Y-%m-%d_%H%M%S"`
Mysqldump –u root –p mydb –h
localhost
database>mydb_$today.sql
U是用户名,p是密码,mydb是数据库名称,-h是主机地址,database是数据库,mydb.sql是为备份产生的存放sql语句的文件名,$today是当前日期。
备份压缩文件
按提示输入密码,这就把yejihao_agent数据库所有的表结构和数据备份到yejihao_agent.sql了,因为要总进行备份工作,如果数据量大会占用很大空间,这时可以利用gzip压缩数据,命令如下:
mysqldump -u root -p yejihao_agent | gzip > yejihao_agent.sql.gz
2备份多个数据库
Mysqldump –u root –p mydb
-database>mydb.sql
U是用户名,p是密码,mydb是数据库名称,mydb.sql是为备份产生的存放sql语句的文件名。
3备份所有数据库
Mysqldump –u root –p -all
-database>mydb.sql
U是用户名,p是密码,mydb是数据库名称,mydb.sql是为备份产生的存放sql语句的文件名。
4备份数据库中的某个表
Mysqldump
–u用户名
–p密码
–h主机地址
数据库名称 表名称>xx.sql
6操作数据库变量
显示数据库变量
显示innodb类型数据库锁等待时间:show variables like 'table_lock%';
修改数据库变量
修改innodb类型数据库锁等待时间:set global table_lock_wait_timeout=600;
二操作数据表
1显示所有表
Show tables;
2创建表
Create table tablename
col_name type [NOT NULL|NULL][DEFAULT
default_value][AUTO_INCREMENT]|[PRIMARY KEY][COMMENT
‘string’]
Tablename是表名称,col_name和type是表中的列名称和列类型,NOT NULL|NULL表示该列是否为空,DEFAULT default_value用于设置该列的默认值,AUTO_INCREMENT用于设置该列是否自增,PRIMARY KEY设置该列是否为主键,COMMENT表示此列的说明。
例子:
Create table user(
Id int(6) NOT NULL
auto_increment PRIMARY KEY comment ‘序号’,
Name varchar(20) NOT NULL
comment ‘姓名’,
Sex char(2) NOT NULL
comment ‘性别’,
Birthday char(10) NULL
comment ‘出生日期’,
Address varchar(100) NULL
comment ‘地址’,
Postcode char(6) NULL
comment ‘邮政编码’,
Email varchar(100) NULL
comment ‘电子邮件’
)COMMENT=’用户信息表’;
3显示某个表
Show create table
tablename;
Tablename
是表名称。
4修改表结构
增加列
Alter table tablename ADD
[COLUMN] column_name type [FIRST|AFTEER
col_name] [COMMENT ‘string’]
修改列
Alter table tablename MODIFY
[COLUMN] column_name type [FIRST|AFTEER
col_name] [COMMENT ‘string’]
删除列
Alter table
tablename
DROP [COLUMN] column_name
删除主键
Alter table
tablename
DROP PRIMARY KEY;
Tablename
是表名称,COLUMN表示要操作的列,type表示列的类型,FIRST|AFTEER col_name表示在某列之前或者某列之后添加新的列,COMMENT表示此列的说明。
修改数据库引擎
TABLE
`test`.`train_price_tmp` ENGINE = INNODB;
Test是数据库名称,train_price_tmp是数据库表名称,INNODB表示数据库中引擎的类型。
5删除表
Drop table
tablename;
Tablename
是表名称
三数据类型
1数值类型
严格数值数据类型
INTEGER
SMALLINT
DECIMAL
NUMERIC
近似数据数值类型
FLOAT
REAL
DOUBLE PRECISION
Sql扩展的整数类型
TINYINT
MEDIUMINT
BIGINT
2日期和时间类型
DATE:日期
DATETIME:日期和时间
TIMESTAMP:时间戳
TIME:时间
TEAR:两位或者四位表示的年
3字符串类型
CHAR:字符串
VARCHAR可变类型字符串
BINARY:二进制字符串
VARBINARY:可变二进制字符串
TINYBLOB:微大对象类型
BLOB:大对象类型
MEDIUMBLOB:中大对象类型
LONGBLOG:长大对象类型
TINYTEXT:微文本类型
TEXT:文本类型
MEDIUMTEXT:中文本类型
LONGTEXT:长文本类型
ENUM:枚举类型
SET:集合类型
备注翻译medium:中间,媒介,环境
四操作视图
1创建视图
CREATE [or REPLACE] VIEW
viewname [column_list] AS
select_statement;
or
REPLACE表示替换已经存在的视图,viewname为视图名称,column_list为视图制定的列名称,select_statement为数据库查询语句
例子:
Create view
view_user(id,name,sex) as select id,name,sex from user;
2显示视图
Show create view
viewname;
viewname为视图名称
例子:
Show create view
view_user;
3修改视图
ALTER VIEW viewname [column_list] AS
select_statement;
viewname为视图名称,column_list为视图制定的列名称,select_statement为数据库查询语句
例子:
ALTER VIEW view_user
(id,name,sex,email) as select id,name,sex,email
from
user;
4删除视图
DROP VIEW
viewname;
viewname为视图名称
例子:
Drop view
view_user;
五操作数据
1查询语句
SELECT
[ALL|DISTINCT|DISTINCTROW] select_expr
column_name as alias_name
[INTO OUTFILE ‘file_name ’
export_options|INTO DUMPFILE ‘file_name’] FROM tablename
WHERE wher_definition GROUP
BY {col_name|expr|position} [ASC|DESC][WITH ROLLUP]
[HAVINGwhere_definition][ORDER BY
{col_name|expr|position}] [ASC|DESC] [LIMIT {[offset]
row_count|row_count OFFSET offset}]
[ALL|DISTINCT|DISTINCTROW]制定是否显示重复行,all表示显示,其他两个表示不显示;select_expr表示表达式,如果没有字段名称只有表达式的话则可以进行一些表达式运算;
As语句为表达式或者字段制定一个别名;INTO OUTFILE ‘file_name ’ export_options|INTO
DUMPFILE ‘file_name’表示将select语句的结果写入到某个外部文件中去;WHERE wher_definition指明查询条件;GROUP BY
{col_name|expr|position}用于分组;[HAVINGwhere_definition]指明分组条件;ORDER BY {col_name|expr|position}]
[ASC|DESC]用于排序;[LIMIT {[offset]} row_count|row_count OFFSET
offset]用于限制返回结果的行数。Offset表示返回结果的起始行,row_count表示
返回结果的总行数
例子:
1)select只跟表达式
select 10*10 as
mu,10*3;
2)写入文件(只会写到数据库的/tmp目录下)
将select语句的结果写入到服务器端某个外部文件中去
Select * into outfile
‘rs.txt’ from user;
(下边的可以在本地使用)
SELECT * INTO OUTFILE
'e:\\admin.txt' FROM train_line_stop WHERE train_code LIKE '�3%'
ORDER BY train_code;
将select语句的结果写入到客户端的某个外部文件中去
C:\>mysql
–uroot –p –e “select 1+1 from
user”;>a.txt
Enter
password:****
备注翻译dump:倾倒,脏的
3)select后边跟非当前表字段
Select
dbname.tablename.columnname from dbname.tablename;
Dbname是库名称,tablename是表名称,columnname是字段名称
通过当前表字段查询非当前表字段
SELECT lot_num AS batchId,lot_name AS
batchName,lot_desc AS batchDesc,user_id AS
userId,
(SELECT NAME FROM b_userinfo WHERE user_id = d.user_id) AS
userName FROM d_lot_list d
4)select
后边跟当前表字段
Select columnname from
tablename;
tablename是表名称,columnname是字段名称
5)分页查询
SELECT columnname FROM
tablename LIMIT 5,10;
tablename是表名称,columnname是字段名称,该语句表示查询从第5条以后10条数据
2插入数据
INSERT[INTO]
tablename[(columnlist)] values(valuelist) [ON DUPLICATE KEY UPDATE
columnname = expr]
Tablename是表名称,columnlist是列集合,valuelist列中的数据集合,ON DUPLICATE KEY UPDATE columnname =
expr表示插入时因主键等原因而造成的该值的唯一值无法插入时将对现有记录进行修改。
例子:
1)
插入部分数据
insert into user
(id,name,sex) values('100010','张三','男');
2)插入全部数据
insert into
user(id,name,sex,birthday,address,post,email,country,province,city)
values('100011','李四','男','1985-05-10','北京','123456','xxx@163.com','中国','北京','北京');
3)同时插入多条数据
insert into
user(id,name,sex,birthday,address,post,email,country,province,city)
values
('100013','赵六','男','1985-05-12','北京','123456','xxx@yahu.com','中国','北京','北京'),
('100014','冯七','男','1986-03-01','北京','123456','xxx@126.com','中国','北京','北京');
3更新语句
UPDATE tablename set
columnname=columnvalue [WHERE where_definition][ORDER BY][LIMIT
row_count OFFSET offset ]
无条件更新语句
update user set
salary=salary+500;
有条件更新语句
update user set
salary=salary+500 where salary < 1000;
4删除语句
DELETE FROM tablename [WHERE
where_definition][ORDER BY][LIMIT row_count OFFSET
offset]
delete from user where
salary > 1000;
5清空语句
TRUNCATE TABLE
tablename
TRUNCATE TABLE
train_line_stop;
六操作数据索引
1新建普通索引
ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX
例子ALTER TABLE example0 ADD INDEX index13_name ( name(20) ) ;
注意:字符串需要指定长度,其他类型不需要
2删除普通索引
DROP INDEX
source ON comment_count;
ALTER TABLE comment_count DROP INDEX province_id;
其他数据库命令
连接数据库命令
本地:mysql -h主机地址
-u用户名
-p用户密码
远程主机:mysql -h主机地址
-u用户名
-p用户密码
退出MYSQL命令
exit
(回车)
在远程主机打开MySQL远程访问权限命令
mysql>GRANT ALL PRIVILEGES ON *.*
TO '用户名'@%'IDENTIFIED BY '密码' WITH GRANT OPTION;
FLUSH PRIVILEGES;
赋予任何主机访问数据的权限命令
mysql>FLUSH PRIVILEGES
修改密码命令
mysqladmin -u用户名
-p旧密码
password 新密码
SHOW ENGINES
显示所有引擎
SHOW CREATE TABLE
person
显示创建的表结构
Delimiter
默认情况下,delimiter是分号“;”。也可以使用delimiter,把delimiter后面换成其它符号,如//或$$。此时,delimiter作用就是对整个小段语句做一个简单的封装。
mysql -h服务器地址 -u用户名
-p密码 -e "set names gb2312;select * from joke":外部执行sql语句