mysql 基础语句
mysql> show databases;
mysql> show databases \G #以行的方式显示
-e 常用与shell脚本。
[root@server01 ~]# mysql -e 'show databases' -usystem -p123456
mysql> create database HA;
mysql> create database `HA-test`;
[root@server01 ~]# ls /usr/local/mysql/data/ #查看数据库存放目录
选择数据库
mysql> use HA-test;
查看自己所处的位置及默认所在的位置
mysql> select database();
在命令行选择默认的数据库
mysql -uroot -p123456 HA
mysql> select now(),user(),database();
删除数据库：
mysql> drop database `HA-test`;
删除没有任何提示，要慎重操作
使用IF EXISTS 子句以避免删除不存在的数据库时出现的MySQL错误信息
mysql> drop database if exists `HA-test`;
mysql> create database if not exists HA;
创建表：
mysql> create table student(id int(20),name char(40),age int);
查看表的结构：
mysql> desc student;
mysql> explain mysql.user;
mysql> show columns from mysql.user;
mysql> show fields from mysql.user;
mysql> show columns from mysql.user like '%user';
查看创建表执行了哪些命令：
mysql> show create table student \G
mysql> create table student2(id int(20),name char(40),age int)ENGINE=MyISAM DEFAULT CHARSET=utf8;
可以指定表的默认存储引擎和字符集
删除表：
mysql> drop table student2;
修改表名称alter：
mysql> alter table student rename students; #studen表名修改为students
mysql> alter table students modify id int(10); 改类型
mysql> alter table students change name stname char(20); 改类型和名字
mysql> alter table students add sex enum('M','W'); 添加字段
mysql> alter table students add uid int(10) first;
mysql> alter table students add address char(40) after age;
mysql> alter table students drop address; 删除表中字段
插入记录
mysql> insert into student values(1,'zhangs',21);
mysql> insert into student values(2,'lis',24),(3,'wange',26);
mysql> insert into student (id,name)values(4,'hangl');
删除
mysql> delete from students where id=3;
mysql> delete from students where age is null;
更新记录
mysql> update students set sex='M' where id=2;
update students set stname='zhangsan',age=21 where uid=1;
查看某个数据库下指定的表内容，数据库名.表名
mysql> select *from HA.student;
mysql> select id,name,age from students where id>3 and age>25;
select * from students where stname='zhangsan' and (age=21 or age=24);
select * from students where stname like '%l%1%2%3%';
select * from mysql.user 查看所有用户
内连接外连接
测试：创建一个school数据库，创建两个表插入测试数据
mysql> create database school;
mysql> create table student(sid int(4) primary key auto_increment, name varchar(50));
mysql> insert into student values(1,'张三'),(2,'李四'),(3,'王二麻子'),(4,'HA'),(5,'Tom');
mysql> create table grade(id int(4) primary key auto_increment, score varchar(20), sid int(4));
mysql> insert into grade (score,sid) values ('1567',2),('1245',3),('1231',4),('1234',5),('1243',6);
mysql> select student.*,grade.* from student,grade where student.sid=grade.sid;
mysql> select student.*,grade.* from student inner join grade on student.sid=grade.sid;
mysql> select student.*,grade.* from student join grade on student.sid=grade.sid;
mysql> select * from student as s right join grade as g on s.sid=g.sid;
mysql> select * from student as s left join grade as g on s.sid=g.sid;
Mysql查询默认是不区分大小写的
mysql> select * from students where binary name='jk'; JK数据不会显示出来
MySQL查询排序：默认为升序 asc
mysql> select distinct id from students order by id;
mysql> select distinct id from students order by id desc;
比如select * from table limit m,n语句
m是指记录开始的index，从0开始，表示第一条记录
n是指从第m+1条开始，取n条。
--查看系统信息
show variables;
show global variables;
show global variables like '%version%';
show variables like '%storage_engine%'; 默认的存储引擎
show engines;查看支持哪些存储引擎
--查看系统运行状态信息
show status;
导入数据库
导入数据库前必须创建一个空数据库
方法1
create database book;
mysql -uroot -p123456 book < book.sql
mysql> use book;
mysql> show tables;
方法2：
create database book;
mysql> use book;
mysql> source /root/book.sql #sql脚本的路径
mysql> show tables;
导出数据库
mysqldump -u system -p123456 book>book2.sql
如何把一个select的结果导出到文本
select * into outfile '/tmp/123.txt' from books; 此处有个文件访问权限问题，mysql用户是可以访问/tmp路径的,所以这里放到tmp下
Sum()求和
avg()平均值：
max() 最大值：
min()最小值：
count()统计记录数:
字符串
substr(string ,start,len) 截取：从start开始，截取len长.start 从1开始算起。
mysql> select substr(bTypeName,1,6)from category where bTypeId=10;
+-----------------------+
| substr(bTypeName,1,6) |
+-----------------------+
| AutoCA | 本来是AutoCAD技术
concat(str1,str2,str3.....) 拼接。把多个字段拼成一个字段输出
mysql> select concat(bName,publishing) from books;
mysql> select concat(bName,"-----",publishing) from books;
upper()大写
lower()小写
日期
curdate():
curtime();
now();
Create table ckdata10 (riqi date)
mysql> insert into ckdata10 values('2015-10-23'),(20180925);
mysql> create table ckdata11(showtime time);
mysql> insert into ckdata11 values('17:23:45'),('18:00'),(162634);
mysql> create table ckdata12(showyear year);
mysql> insert into ckdata12 values(2015),(01),(9),(69),(70),(2070);
mysql> create table kdata14 ( f_datatime datetime,f_timestamp timestamp);
mysql> insert into kdata14 values ('1999-11-12 23:23:45',19991112232345)
如果要查询时间戳SELECT unix_timestamp('1999-11-12 23:23:45');
显示时间戳：
[root@server01 ~]# date +%s
1444124814
显示指定时间的时间戳
[root@server01 ~]# date -d "2015-10-06 17:48:23" +%s
1444124903
[root@server01 ~]# date -d "1970-01-01 08:00:00" +%s
0
[root@server01 ~]# date -d"@1455455465"
Sun Feb 14 21:11:05 CST 2016
[root@server01 ~]# date -d"@1455455465" "+%Y/%m/%d %H:%M:%S"
2016/02/14 21:11:05
[root@server01 ~]# date -d"@1455455465" "+%Y-%m-%d %H:%M:%S"
2016-02-14 21:11:05
[root@yu ~]# date +%F
2016-10-15
