mysql is rdbms（relational database management system） 创建连接mysql的用户 % mysql -p -u root mysql create user 'sampadm'@'localhost' identified by 'secret'; mysql grant all on sampdb.* to 'sampadm'@'localhost' 连接mysql mysql -h host_nam
mysql is rdbms（relational database management system）
创建连接mysql的用户
% mysql -p -u root
mysql> create user 'sampadm'@'localhost' identified by 'secret';
mysql> grant all on sampdb.* to 'sampadm'@'localhost'
连接mysql
mysql -h host_name -p -u user_name
退出mysql
mysql> quit
执行mysql
mysql> select now(),user(),version();
mysql> select now(),user(),version()\g // ; \g 终止语句
mysql> select now(),user(),version()\g //竖排显示，每行一个值
脚本执行
% mysql % mysql sampdb 创建数据表
mysql> create table member
(
member_id int unsigned not null auto_increment,
primary key (member_id),
first name varchar(20) not null,
last name varchar(20) not null,
suffix varchar(5) not null,
expiration date null
);
查看表结构
mysql> describe member;
显示特定列
mysql> show columns from member like '%name';
列出表
mysql> show tables;
% mysqlshow sampdb
列出数据库
mysql> show database; 
% mysql show
插入数据行
mysql> create table student
(
name varchar(20) not null,
sex enum('f','m') not null,
student_id int unsigned not null auto_increment,
primary key (student_id)
)engine = innodb
mysql> insert into student values('alex','m',null);
mysql> insert into student values('alex','m',null),('bob','f',null); //括号内包含所有列
mysql> insert into student (name,sex) values('alex','m');
加载数据文件
mysql> load data local infile 'member.txt' into table member;
% mysqlimport --local samdb member.txt
检索信息
书定顺序,select--from--where--group by--having--order by
执行顺序,from--where--group by--having--select--order by
null值表示'无数据'，不能与'有数据'的值比较
limit number，限制显示行
日期函数，year(),month(),dayofmonth()
变量，@variable
mysql> select @birth:=birth from student where name='bob';
mysql> select name from student where birthmysql> set @today=curdate();
mysql> set @one_week_ago:=date_sub(@today,interval 7 day);
统计信息
distinct,清除重复信息
count(*),计数所有行
count(数据列)，非null行
mysql> select state,count(*) from president group by status order by count desc limit 4;
多表联结
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行
exists 与 in 最大的区别在于 in引导的子句只能返回一个字段，比如:
select name from student where sex = 'm' and mark in (select 1,2,3 from grade where ...),
in子句返回了三个字段，这是不正确的，exists子句是允许的，但in只允许有一个字段返回，在1，2，3中随便去了两个字段即可
删除数据行
delete from tbl_name where which rows to delete;
更新数据行
update tbl_name set which columns to change where which rows to update;
简化链接过程
1. ~/.my.cnf
[client]
host=server_host
user=your_name
password=your_pass
2. 命令历史
% !my //最近使用过的命令
3. alias sampdb 'mysql -h host_name -p -u sampadm sampdb'
聚合函数,min(),max(),sun(),avg()
创建数据库
create database [if not exists] db_name [character set charset][collate collation];
删除数据库
drop database db_name;
变更数据库
alter database [db_name][character set charset][collate collation];