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