《mysql学习笔记》
1.打开与关闭mysql
mysql -uXXX -pXXX #其中XXX为用户名和密码
#成功进入后提示符变为: mysql>
#可输入第一个例子 version(),current_date();
【注意一定要在结尾有分号;】
【多行输入时在最后一行要有分号作为输入结束标志】
【SQL关键字大小写不敏感】
mysql>exit #关闭
mysql>quit #关闭
>help #帮助
配置:
which mysqld 查看路径
配置文件 : /etc/my.cnf
2.使用某数据库
use 库名 #mysql安装完成后默认建立mysql库
use mysql; #可不加分号,use语句必须在一个单行上给出
输出:
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| ... |
| help_category |
| help_keyword |
3.查看库中数据库、表、DB信息
show databases;
show tables;
select database(); # 找出当前选了哪个数据库
select version(); # 产看mysql版本
#查看mysql的连接权限
select host,user from mysql.user order by user;
#查看某主机用户有哪些权限
show grants for bbpk@10.xx.xx.xx;
# 查看mysql 当前连接
> show processlist;
4.查看某个表结构
DESC tablename;
show columns from tablename;
describe tablename;
show create table d_fun.t_char_data; #查看建表语句
##语法顺序
select|distinct > from > where > group by > having > union > order by
5 创建并使用数据库
创建数据库
mysql> CREATE DATABASE menagerie; 【Unix下,数据库名称是区分大小写的】
使用数据库
mysql> use menagerie
登陆时直接选择数据库
shell> mysql -h host -u user -p menage 【menage为库名】
#远程访问mysql需要有权限
Enter password: ********
【注:也可在-p后直接写输入密码,不过这样不安全】
授权:GRANT ALL PRIVILEGES ON `db%`.* TO 'conf'@'localhost' IDENTIFIED BY 'xxxxxxx';
flush PRIVILEGES ;
6 创建表
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
显示表结构
mysql> DESCRIBE pet;
DESC pet;
7 填充表
7.1 多行填充
创建一个文本文件“pet.txt”,每行包含一个记录,用定位符(tab)把值分开,每行数据顺序必须与表的列对应。
空值可用NULL或 “\N”表示。
将文本文件“pet.txt”装载到pet表中,使用这个命令:
mysql> LOAD DATA LOCAL INFILE '/path/xxx.txt' INTO TABLE pet;
请注意如果用Windows中的编辑器(使用\r\n做为行的结束符)创建文件,应使用:
mysql> LOAD DATA LOCAL INFILE '/path/text.txt' INTO TABLE pet
-> LINES TERMINATED BY '\r\n';
7.2 单行填充
mysql> insert into pet
-> values('pp','diane','hamster','f','2006-03-30',NULL);
8 查询
8.1 基本查询
select * from table where id>5 and id<10;
select name from table where id=3 or id =9;
select DISTINCT name from pet; #去重
select name,birth from pet ORDER BY birth; #分类,默认升序
select name,birth from pet ORDER BY birth DESC; #降序排列
select * from basss limit 1\G # \G 单列显示数据
8.2 查询时间
select name,CURDATE(),YEAR(CURDATE()),MONTH(CURDATE()),RIGHT(CURDATE(),5)
from pet ORDER BY name;
# CURDATE() 当前日期 XXXX-XX-XX
# RIGHT(str,num) 取str右边num个字符
# MONTH(time) 取出月份
# UNIX_TIMESTAMP('xxxx-xx-xx') 获取UNIX时间
# CURTIME() 当前时间
# NOW() 当前日期加时间 xxxx-xx-xx xx:xx:xx
# DATE_FORMAT(date,format) 将日期转为特定格式
# FROM_UNIXTIME(unix_timestamp) 将unix时间转为xxxx-xx-xx xx:xx:xx
select from_unixtime(unix_timestamp(),"%Y-%m-%d %H:%i:%s");
# %Y 年 xxxx
%y 年 xx
%m 月 数字形式 01..12
%M 月 名称形式 January,Decembe
%d 日 数字形式
%H 时 00..23
%h 时 01..12 结合 %p AM/PM
%i 分 00..60
%s 秒
8.3 模式匹配
select * from pat where name like 'b%'; # 用百分号%做匹配 表示任意个字符
select * from pat where name like '___' # 一个下划线’_‘ 表示一个字符
select * from pat where name REGEXP '^b' # 正则匹配 ,以b开头
select * from pat where name REGEXP BINARY '^b' #只匹配小写
select * from pet where name REGEXP '^.{5}$' # 匹配5个字符
select iUin,max(dtEventTime) time from RoleLogin group by iUin having time <'2013-07-28';
# having 写在group by之后
8.4 计数行
select count(*) from pet; # 取出表记录个数
select owner,count(*) from pet GROUP BY owner; #查看每个owner有多少个记录
select owner,sex,count(*) from pet where sex IS NOT NULL GROUP BY owner,sex; #按owner和sex分组统计
8.5 从多个表查询
select pet.name,(year(date)-year(birth)-(right(date,5)<right(birth,5))) AS age,remark
from pet,event # AS 别名 如果忽略AS,该别名仍然成立
where pet.name=event.name
8.6 嵌套查询
8.6.1 子查询返回单个条件
select name,score
from student
where score=(select max(score) from student);
8.6.2 子查询返回多个条件
select name,score
from student
where score in (select score from stu_info where age < 12);
###【注意】 in 中不支持limit,即上面括号的语句中不能有 limit
#其他方法
select name,score from student
order by score DESC #降序
LIMIT 1; #只取第一行
8.7 使用ANY, IN,SOME,ALL进行子查询
8.7.1 ANY
# operand comparison_operator ANY (subquery) #返回子查询中任一满足比较操作的值
select s1 from t1 where s1 > ANY (select s1 from t2);
8.7.2 IN
# IN 其实是 = ANY 的别名
# IN 中不能用LIMIT
# operand IN (subquery)
# 下面两句功能相同
select s1 from t1 where s1 = ANY (select s1 from t2);
select s1 from t1 where s1 IN (select s1 from t2);
# NOT IN 是 <> ALL的别名
8.7.3 SOME
# operand comparison_operator SOME (subquery)
# SOME 是ANY 的别名
# 下面两句等同
select s1 from t1 where s1 <> ANY (select s1 from t2);
select s1 from t1 where s1 <> SOME (select s1 from t2);
8.7.4 ALL
# operand comparison_operator ALL (subquery)
# ALL必须接在一个比较操作符的后面,对于子查询返回的所有值,如果比较结果为真,则返回TURE
select s1 from t1 where s1 > ALL (select s1 from t2);
8.8 HAVING子句
在SQL中 , where 关键字无法与某些函数一起使用, 因此用 HAVING语句对函数结果进行判断
select Customer,SUM(OrderPrice) from Orders
where Customer='Bush' OR Customer='Adams'
having SUM(OrderPrice)>1500
9 删除
9.1删除库:
drop database if exists drop_database;
9.2条件删除表数据:
删除取到的第一个
delete from Db.table
where user='123'
order by time
limit 1;
9.3 清除表内容
truncate table FightTower;
9.4 清除表分区文件(todo)
alter table tbPveAttack drop partition p_20140321;alter table tbPveAttack add partition (partition p_20140321 VALUES IN (735678));
9.5 删除表
drop table if exists table_name;
9.6 生成删除库中所有表的语句
SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'YourDatabaseName';
9.7 删除表时取消外键约束
USE mpboy_web;
SET FOREIGN_KEY_CHECKS = 0;
DROP table if exists auth_group;
DROP table if exists south_migrationhistory;
SET FOREIGN_KEY_CHECKS = 1;
10 修改
update t_char_data set created_world_id=1 where aid=16268 or aid=136168;
11 在批处理模式下使用MYSQL
$ mysql < batch-file # 基本结构 batch-file为mysql脚本
$ mysql -h host -u user -pxxx <batch-file > mysql.out # 加入参数,将结果输出到文件中
$ mysql -u root -p xxx -N -e "select name from db.table"; # -e 或 --execute
for i in $(ls);do mysql -ufun -pxxx d_fun -h gamedb.1.Qyouji.db -P10001 --default-character-set=utf8<$i; done # -N skip-colum-names
mysql -uxxx -pxx d_fun -h gamedb.1.Qyouji.db -P10001 --default-character-set=utf8 <reset.sql
#注:批处理模式下的默认输出不包含列名
12 操作符
:= #冒号前变量为空或不存在,将等号后内容赋给它
||,OR,XOR
&&,AND
NOT
BETWEEN,CASE,WHEN,THEN,ELSE
=,<=>,>=,<=,<>,!=,IS,LIKE,REGEXP,IN # <=> NULL-safe-equal 空值进行比较时给出正确结果
select NULL<=>NULL; 结果为1
# <> 不等于
select '0.01'<>'.01'; 结果为1
|
&
<<,>>
-,+
&,/,DIV,%,MOD
^
-,~
!
BINARY,COLLATE
13 mysqldump学习
mysqldump -uxx -pxx --default-character-set=*** --single-transaction --skip-opt --create-option -R -q -B db_QQyxsq_logtool_1 >db_QQyxsq_logtool_1.sql
mysqldump -uxx -pxx --default-character-set=*** --single-transaction --skip-opt --create-option -R -q -d -B db_QQyxsq_logdb_1 >db_QQyxsq_logtdb_1.schema
mysqldump -uxx -pxxx -h www.chengxu.kr.db -P10000 db_kr_ptdl --skip-lock-tables
格式:
mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases
帮助:
mysqldump --help
选项:
--default-character-set=charset_name 选择字符集
--add-drop-database Add a DROP DATABASE statement before each CREATE DATABASE statement.
--add-drop-table Add a DROP TABLE statement before each CREATE TABLE statement.
--all-databases, -A Dump all tables in all databases.
--skip-opt 跳过 opt的选项
--databases, -B 导出某个库
--ignore-table=db_name.tbl_name 忽略某个表
--single-transaction 在执行导出前向服务器发送一个开始事务声明(START TRANSACTION SQL statement),仅对Innodb引擎的DB有效
--opt 等于同时选 --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset
--quick, -q 快速模式,用于导出数据量很大的表。
It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set
and buffering it in memory before writing it out.
--replace Write REPLACE statements rather than INSERT statements. This option was added in MySQL 5.1.3.
--routines, -R Dump stored routines (functions and procedures) from the dumped databases.
--create-options Include all MySQL-specific table options in the CREATE TABLE statements.
--no-data -d
14 mysqlbinlog
日志与数据恢复(略)
15 修改密码
安装好的Mysql首次登录后,
mysql> update mysql.user
-> set password = password('密码') where User = 'root';
-> flush privileges;
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。