mysql 权威指南笔记

///==============================

多年前，就买了一个本mysql权威指南！
一页没看过 :(

想从mysql上打开一个深入的缺口从它上来比较不错 :)

我觉的首先了解mysql 基本的常用语法;
再深入了解一些mysql,优化SQL，设计与实现测试的工作!
mysql 也有function prodecuted[复杂的功能先不看，测试一些数据用的上..]

阿弥陀佛,开始:


我使用的myqsl权限是.
mysql> select version();
+---------------------+
| version() |
+---------------------+
| 5.0.37-community-nt |
+---------------------+
1 row in set (0.00 sec)


还行不老不新，先在window环境下把它搞定，再上linux, :)

我个人习惯root 访问，不合适;;


给sampdb库，设一个管理员帐户tom 密码tom
grant all on sampdb.* to 'tom'@'localhost' identified by 'tom';
权限问题，没搞清;;
现在只有root可以，做上面的操作;

grant all on jpetstone.* to 'tom'@'localhost' identified by 'tom';


C:\>mysql -u tom -p
Enter password: ***
mysql>

1:当前时间
2:当前用户
3:当前数据库系统版本
mysql> select now(),user(),version();
+---------------------+---------------+---------------------+
| now() | user() | version() |
+---------------------+---------------+---------------------+
| 2009-11-01 15:34:48 | tom@localhost | 5.0.37-community-nt |
+---------------------+---------------+---------------------+
1 row in set (0.00 sec)

在查询的参数后面加一个\G;
1：查询显示为竖形式，
这个功能可是非常有用，当我们要查询优化时这一点可是用的上;

mysql> select now(),user(),version() \G
*************************** 1. row ***************************
now(): 2009-11-01 15:36:00
user(): tom@localhost
version(): 5.0.37-community-nt
1 row in set (0.00 sec)


创建数据库;
create database sampdb;

创建脚本;
#总统
create table president(
last_name varchar(15) not null,
first_name varchar(15) not null,
suffix varchar(5) null,
city varchar(20) not null,
state varchar(2) not null,
birth date not null,
death date null
);
#会员
create table member(
member_id int unsigned not null auto_increment,
primary key(member_id),
last_name varchar(15) not null,
first_name varchar(15) not null,
suffix varchar(5) null,
expiration date null default '0000-00-00',
email varchar(100) null,
street varchar(50) null,
city varchar(20) null,
state varchar(2) null,
zip varchar(10) null,
phone varchar(20) null,
interests varchar(255) null
);

查询所有的表;
mysql> show tables;
+------------------+
| Tables_in_sampdb |
+------------------+
| member |
| president |
+------------------+
2 rows in set (0.00 sec)
查询所有的库;
mysql> show databases;

//======================================

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)
);

create table event(
date date not null,
type enum('T','Q')not null,
event_id int unsigned not null auto_increment,
primary key(event_id)
);

create table score(
student_id int unsigned not null,
event_id int unsigned not null,
primary key(event_id,student_id),
score int not null
);

create table absence(
student_id int unsigned not null,
date date not null,
primary key(student_id,date)
);


//==================================
查询当然是要了解一个特点;
学oracle 我知道有一个 dual 表;;
在mysql可不用;
mysql> select user(); 这一点还不错;

限制查询的行个数;
mysql> select * from user
-> order by name limit 5;
+----+---------+------+
| id | name | mid |
+----+---------+------+
| 1 | manager | NULL |
| 2 | tom0 | 1 |
| 3 | tom1 | 1 |
| 4 | tom2 | 1 |
| 5 | tom3 | 1 |
+----+---------+------+
5 rows in set (0.00 sec)

mysql> select * from user
-> order by name limit 3,5;
+----+------+------+
| id | name | mid |
+----+------+------+
| 4 | tom2 | 1 |
| 5 | tom3 | 1 |
| 6 | tom4 | 1 |
| 7 | tom5 | 1 |
| 8 | tom6 | 1 |
+----+------+------+
5 rows in set (0.00 sec)

随机选出一条记录，不错;

select * from user
order by rand() limit 1;


//========================
日期常用处理方式;
select * from uu where rdate = '2009-11-04';
select * from uu where month(rdate) = 11;
select * from uu where year(rdate) = 2009;
select * from uu where dayofmonth(rdate) = 04;


//当前日期与时间
//当前日期
//当前时间
select now();
select curdate();
select curtime();

+------------+
| curdate() |
+------------+
| 2009-11-04 |
+------------+
+-----------+
| curtime() |
+-----------+
| 15:03:01 |
+-----------+


select date_format(now(),'%Y-%m-%d %H:%i:%S') ;
+----------------------------------------+
| date_format(now(),'%Y-%m-%d %H:%i:%S') |
+----------------------------------------+
| 2009-11-04 15:11:05 |
+----------------------------------------+
mysql> select date_format(now(),'%Y年%m月%d日 %H:%i:%S') 当前时间;
+-------------------------+
| 当前时间 |
+-------------------------+
| 2009年11月04日 15:13:05 |
+-------------------------+
1 row in set (0.00 sec)

//===========================================
建国60周年
to_days()天数;;

mysql> select round((to_days(now()) - to_days('1949-10-1'))/365);
+----------------------------------------------------+
| round((to_days(now()) - to_days('1949-10-1'))/365) |
+----------------------------------------------------+
| 60 |
+----------------------------------------------------+
1 row in set (0.00 sec)


//============================
设置和使用sql变量;

我感觉mysql变量，就是一个查询的结果;


select @id := id from uu
where name = 'tom';

mysql> select * from uu
-> where id = @id;
+----+------+------------+
| id | name | rdate |
+----+------+------------+
| 1 | tom | 2009-11-04 |
+----+------+------------+
1 row in set (0.00 sec)

//=======================================

mysql> select * from student;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 10 |
| 2 | jerry | 20 |
| 3 | long | 20 |
+----+-------+------+

mysql> select * from score;
+----+---------+------+
| id | name | rs |
+----+---------+------+
| 1 | gaoshu | 0 |
| 2 | english | 1 |
+----+---------+------+
2 rows in set (0.00 sec)

mysql> select * from rel;
+----+------+------+
| id | sid | scid |
+----+------+------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
+----+------+------+
2 rows in set (0.00 sec)


1:
查询tom所选科目;
mysql> select s.id,s.name,sc.name
-> from student s,score sc,rel r
-> where s.id = r.sid and sc.id = r.scid;
+----+------+---------+
| id | name | name |
+----+------+---------+
| 1 | tom | gaoshu |
| 1 | tom | english |
+----+------+---------+
2 rows in set (0.00 sec)

2:
查询没有选课的人员名称;
mysql> select s.id,s.name,count(r.scid)
-> from student s left join rel r
-> on s.id = r.sid
-> group by s.id,s.name
-> ;
+----+-------+---------------+
| id | name | count(r.scid) |
+----+-------+---------------+
| 1 | tom | 2 |
| 2 | jerry | 0 |
| 3 | long | 0 |
+----+-------+---------------+
3 rows in set (0.04 sec)

select s.id,s.name,count(r.scid)
from student s left join rel r
on s.id = r.sid
group by s.id,s.name
having count(r.scid) = 0;

+----+-------+---------------+
| id | name | count(r.scid) |
+----+-------+---------------+
| 2 | jerry | 0 |
| 3 | long | 0 |
+----+-------+---------------+
2 rows in set (0.03 sec)


///==============================

今天看书，有一个小知识点:
1:auto_increment
mysql> create table mytb1(
-> seq int auto_increment not null,
-> primary key(seq)
-> )type=myisam auto_increment = 3000;
Query OK, 0 rows affected, 1 warning (0.12 sec)

mysql> insert into mytb1 values(null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into mytb1 values(null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into mytb1 values(null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from mytb1;
+------+
| seq |
+------+
| 3000 |
| 3001 |
| 3002 |
+------+
3 rows in set (0.00 sec)

create table counter(
name varchar(20),
value int(20),
primary key(value));


2:regex

我们在搜索字符串时，常用like
功能是比较弱的，今天发现mysql居然支持正则表达式;
mysql> select * from user where name like 't__0';
+----+------+------+
| id | name | mid |
+----+------+------+
| 2 | tom0 | 1 |
+----+------+------+
1 row in set (0.00 sec)

select * from user where name regexp '^[a-z0-9]{4}$';
+----+------+------+
| id | name | mid |
+----+------+------+
| 2 | tom0 | 1 |
| 3 | tom1 | 1 |
| 4 | tom2 | 1 |

//=================================
查看字符集:

mysql> show create database test;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table user;
+-------+-----------------------------------------------------------------------------
--------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------------
--------+
| user | CREATE TABLE `user` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) default NULL,
`mid` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `FK36EBCBA429B0BC` (`mid`),
CONSTRAINT `FK36EBCBA429B0BC` FOREIGN KEY (`mid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+-----------------------------------------------------------------------------
mysql> show character set;