MySql一些常用的操作2

其他特殊用法：
SELECT LAST_INSERT_ID(); 获得当时插入表的最后插入的ID值，有点象oracle中的sequence.curVal

CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);
CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
);

--注：ENUM为枚举类型，例如这里的style字段插入的值只能是t-shirt,polo或dress

ENUM为枚举类型，例如这里的style字段插入的值只能是t-shirt,polo或dress
INSERT INTO person VALUES (NULL, 'Antonio Paz');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
--注：插入数据时，可以不指明字段，但自动增长的字段要用NULL值填充, 在SQLServer中可以省写标识列的值
--SQLServer中的写法：INSERT INTO shirt VALUES ('polo', 'blue', 1),
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);

--zerofill: 位数不够用零填充
CREATE TABLE hire_date (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
             day INT(2) UNSIGNED ZEROFILL);
INSERT INTO hire_date  VALUES(2000,2,1),(2000,11,20),(2000,1,3);
mysql> select * from hire_date;
+------+-------+------+
| year | month | day  |
+------+-------+------+
| 2000 |    02 |   01 |
| 2000 |    11 |   20 |
| 2000 |    01 |   03 |
+------+-------+------+
3 rows in set (0.00 sec)
--注：如果month设置为INT(4)，而插入的月份为2，则结果变成0002

3.3.7 删除数据库表
mysql> drop table student;
Query OK, 0 rows affected (0.03 sec)
删除之后可以通过show tables指令看到删除之后的效果
mysql> show tables;
Empty set (0.00 sec)

3.3.8 插入数据到数据库表中
mysql> insert into student values(1,'jimmy','m','1982-11-12');
Query OK, 1 row affected (0.09 sec)
insert into student values(2,'sunny','m','1985-01-28');
insert into student values(3,'merry','f','1988-08-02');
insert into student values(4,'jack', 'm','1979-02-18');
insert into student values(5,'mary', 'f','1987-06-21');
insert into student values(6,'lily', 'f','1989-09-12');

mysql> select * from student;
+-----------+-------+------+------------+
| studentId | name  | sex  | birth      |
+-----------+-------+------+------------+
|         1 | jimmy | m    | 1982-11-12 |
+-----------+-------+------+------------+
1 row in set (0.00 sec)

也可以一次插入多数据
mysql> insert into course values (1,'java'),(2,'c#'),(3,'sql');
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from course;
+----------+------+
| courseId | name |
+----------+------+
|        1 | java |
|        2 | c#   |
|        3 | sql  |
+----------+------+
3 rows in set (0.00 sec)

3.3.8 更新数据
mysql> update student set birth='1985-11-22' where name='jimmy';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0
3.3.8 删除数据
mysql> delete from student where studentId>1;
Query OK, 4 rows affected (0.08 sec)
3.3.8 查询数据
过滤掉重复记录
mysql> select distinct sex from student;
+------+
| sex  |
+------+
| m    |
| f    |
+------+
2 rows in set (0.00 sec)
排序
mysql> select * from student order by birth desc;
+-----------+-------+------+------------+
| studentId | name  | sex  | birth      |
+-----------+-------+------+------------+
|         6 | lily  | f    | 1989-09-12 |
|         3 | merry | f    | 1988-08-02 |
|         5 | mary  | f    | 1987-06-21 |
|         1 | jimmy | m    | 1985-11-22 |
|         2 | sunny | m    | 1985-01-28 |
|         4 | jack  | m    | 1979-02-18 |
+-----------+-------+------+------------+
6 rows in set (0.00 sec)

日期函数
curdate() 获得当前日期
year  获得年份
month  获得月份
mysql> select name,birth, curdate(), (year(curdate())-year(birth)) as age  from student order by age;
+-------+------------+------------+------+
| name  | birth      | curdate()  | age  |
+-------+------------+------------+------+
| lily  | 1989-09-12 | 2008-12-20 |   19 |
| merry | 1988-08-02 | 2008-12-20 |   20 |
| mary  | 1987-06-21 | 2008-12-20 |   21 |
| jimmy | 1985-11-22 | 2008-12-20 |   23 |
| sunny | 1985-01-28 | 2008-12-20 |   23 |
| jack  | 1979-02-18 | 2008-12-20 |   29 |
+-------+------------+------------+------+
6 rows in set (0.00 sec)

模糊查询
like语法
% 通配符 代表任意多个字符
_ 通配符 一个下划线代表任意一个字符，例如下面4个_代表四个长度的字符
mysql> select * from student where name like '%m%';
+-----------+-------+------+------------+
| studentId | name  | sex  | birth      |
+-----------+-------+------+------------+
|         1 | jimmy | m    | 1985-11-22 |
|         3 | merry | f    | 1988-08-02 |
|         5 | mary  | f    | 1987-06-21 |
+-----------+-------+------+------------+
3 rows in set (0.00 sec)

mysql> select * from student where name like '____'; (四个_下划线表示name长度为4)
+-----------+------+------+------------+
| studentId | name | sex  | birth      |
+-----------+------+------+------------+
|         4 | jack | m    | 1979-02-18 |
|         5 | mary | f    | 1987-06-21 |
|         6 | lily | f    | 1989-09-12 |
+-----------+------+------+------------+
3 rows in set (0.00 sec)

使用正则表达式进行查询

例如，查询名称以m开头的所有学生
mysql> select * from student where name regexp '^m';
+-----------+-------+------+------------+
| studentId | name  | sex  | birth      |
+-----------+-------+------+------------+
|         3 | merry | f    | 1988-08-02 |
|         5 | mary  | f    | 1987-06-21 |
+-----------+-------+------+------------+
2 rows in set (0.05 sec)

还可以使用binary关键字来区分大小写，也就是使用二进制来进行比较
mysql> select * from student where name regexp binary '^M';
Empty set (0.00 sec)
mysql> select * from student where name regexp binary '^m';
+-----------+-------+------+------------+
| studentId | name  | sex  | birth      |
+-----------+-------+------+------------+
|         3 | merry | f    | 1988-08-02 |
|         5 | mary  | f    | 1987-06-21 |
+-----------+-------+------+------------+
2 rows in set (0.00 sec)

查询名称中含有m的所有学生（效果和like '%m%'一样）
mysql> select * from student where name regexp 'm';
+-----------+-------+------+------------+
| studentId | name  | sex  | birth      |
+-----------+-------+------+------------+
|         1 | jimmy | m    | 1985-11-22 |
|         3 | merry | f    | 1988-08-02 |
|         5 | mary  | f    | 1987-06-21 |
+-----------+-------+------+------------+
3 rows in set (0.00 sec)
mysql> select * from student where name like '%m%';
+-----------+-------+------+------------+
| studentId | name  | sex  | birth      |
+-----------+-------+------+------------+
|         1 | jimmy | m    | 1985-11-22 |
|         3 | merry | f    | 1988-08-02 |
|         5 | mary  | f    | 1987-06-21 |
+-----------+-------+------+------------+
3 rows in set (0.00 sec)

查询名称只有4个字符的所有学生（效果和like '____'; [四个_下划线表示name长度为4]一样）
mysql> select * from student where name regexp '^....$';
+-----------+------+------+------------+
| studentId | name | sex  | birth      |
+-----------+------+------+------------+
|         4 | jack | m    | 1979-02-18 |
|         5 | mary | f    | 1987-06-21 |
|         6 | lily | f    | 1989-09-12 |
+-----------+------+------+------------+
3 rows in set (0.00 sec)
注： ^ 表示正则表达式开始
     $ 表示正则表达式结束
     . 表示一个字符
这个正则表达式也可以写成
mysql> select * from student where name regexp '^.{4}$';
+-----------+------+------+------------+
| studentId | name | sex  | birth      |
+-----------+------+------+------------+
|         4 | jack | m    | 1979-02-18 |
|         5 | mary | f    | 1987-06-21 |
|         6 | lily | f    | 1989-09-12 |
+-----------+------+------+------------+
3 rows in set (0.00 sec)
注： ^ 表示正则表达式开始
     $ 表示正则表达式结束
     . 表示一个字符
     {4} 表示重复4次,也就是....

mysql> select * from student where name like '____';
+-----------+------+------+------------+
| studentId | name | sex  | birth      |
+-----------+------+------+------------+
|         4 | jack | m    | 1979-02-18 |
|         5 | mary | f    | 1987-06-21 |
|         6 | lily | f    | 1989-09-12 |
+-----------+------+------+------------+
3 rows in set (0.00 sec)

查询年龄最小的学生信息（也就是出生日期最大或最晚的那个）
max 函数

mysql> select * from student where birth = (select max(birth) from student);
+-----------+------+------+------------+
| studentId | name | sex  | birth      |
+-----------+------+------+------------+
|         6 | lily | f    | 1989-09-12 |
+-----------+------+------+------------+
1 row in set (0.03 sec)

或者通过order by和limit关键字来实现同样的效果(limit和SQLServer中的top功能类似)
mysql> select * from student order by birth desc limit 1;
+-----------+------+------+------------+
| studentId | name | sex  | birth      |
+-----------+------+------+------------+
|         6 | lily | f    | 1989-09-12 |
+-----------+------+------+------------+
1 row in set (0.00 sec)

获得通过变量的方式来实现
例如通过变量来获得出生日期最早和最晚的学生信息
mysql> select @min_birth:=min(birth),@max_birth:=max(birth) from student;
+------------------------+------------------------+
| @min_birth:=min(birth) | @max_birth:=max(birth) |
+------------------------+------------------------+
| 1979-02-18             | 1989-09-12             |
+------------------------+------------------------+
1 row in set (0.05 sec)
mysql> select * from student where [email=[ft=#000000,,]birth=@min_birth ]birth=@min_birth[/email] or [email=[ft=#000000,,]birth=@max_birth ]birth=@max_birth[/email] ;
+-----------+------+------+------------+
| studentId | name | sex  | birth      |
+-----------+------+------+------------+
|         4 | jack | m    | 1979-02-18 |
|         6 | lily | f    | 1989-09-12 |
+-----------+------+------+------------+
2 rows in set (0.00 sec)

Java连接MySQL数据
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/mytest"
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/mytest"; //mytest是数据库名称
DriverManager.getConnection(url, "root", "root123");
<Resource name="jdbc/zf"
auth="Container"
type="javax.sql.DataSource"
maxActive="100"
maxIdle="30"
maxWait="10000"
username="root"
password="root123"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/zf"/>