mysql 简单教程(二) ----创建和使用数据库

1.创建和使用数据库

1.1创建数据库

创建数据库：
不指定字符集，使用默认（配置文件决定）

mysql> create database breath;
Query OK, 1 row affected (0.00 sec)
mysql> show create database breath;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| breath   | CREATE DATABASE `breath` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

指定字符集及排序规则：

mysql> create database breath_t default character set gbk collate gbk_chinese_ci;
Query OK, 1 row affected (0.00 sec)

mysql> show create database breath_t;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| breath_t | CREATE DATABASE `breath_t` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

1.2使用数据库

本地直接访问breath数据库:

[root@localhost ~]# mysql -u root -p breath

登录后切换数据库：

mysql> use breath                    -- 切换到breath数据库
Database changed
mysql> select database();   ---查看当前连接数据库
+------------+
| database() |
+------------+
| breath     |
+------------+
1 row in set (0.00 sec)

1.3 建表及加载数据

建表：

mysql> show tables;
Empty set (0.00 sec)

mysql> create table test (id int(10),name varchar(20));
Query OK, 0 rows affected (0.10 sec)

mysql> show tables;
+------------------+
| Tables_in_breath |
+------------------+
| test             |
+------------------+
1 row in set (0.00 sec)

mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(10)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

给表加载数据：
LOAD 加载文本文件
（前提是启用了本地文件加载功能）

mysql> load data local infile '/root/test.txt' into table test;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from test;
+------+---------+
| id   | name    |
+------+---------+
|    1 | breath1 |
|    2 | breath2 |
|    3 | breath3 |
+------+---------+
3 rows in set (0.00 sec)

INSERT 语句插入

mysql> insert into test values(4,'breath4');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(5,NULL);
Query OK, 1 row affected (0.03 sec)
mysql> select * from test;
+------+---------+
| id   | name    |
+------+---------+
|    1 | breath1 |
|    2 | breath2 |
|    3 | breath3 |
|    4 | breath4 |
|    5 | NULL    |
+------+---------+
5 rows in set (0.00 sec)

1.4删除数据库

mysql> drop database breath_t;
或者
[root@localhost ~]# mysqladmin -u root -p drop breath_t
Enter password: 
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'breath_t' database [y/N] y
Database "breath_t" dropped

2.简单sql语句(对于表)

2.1 Delete

删除特定数据

mysql> delete from test where id=1;

删除所有数据

mysql> delete from test;

2.2 ALTER TABLE

删除,添加字段

mysql> alter table test drop id;    ---删除字段id
mysql> alter table test add num int;        ---添加字段num

修改字段类型及名称

mysql> alter table test modify num char(10);      -----修改字段num 类型为char(10)
mysql> alter table test change num id int;      -----修改字段num，改名为id 类型 int
mysql> alter table test modify id bigint not null default 100;   --修改字段id为not null且默认值是100
mysql> alter table test alter id drop default;   ---删除字段id 的默认值
mysql> alter table test modify id bigint null;    ---删除字段 id  的 not null约束
mysql> alter table test rename to test1;    ---修改表名
mysql> alter table test1 engine= MYISAM;    ---修改表的类型

2.3 DROP TABLE

mysql> drop table test1;

2.4 CREATE TABLE

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

加载数据
mysql> load data local infile '/root/test.txt' into table pet;

2.5 UPDATE

mysql> update pet set birth='1989-08-31' WHERE name = 'Bowser';   ----修改指定数据
mysql> update pet set death=NULL;    --修改所有

2.6 SELECT

查询所有数据行:

mysql> select * from pet;

查询特定数据行

mysql> select * from pet where name='Bowser';
mysql> select * from pet where birth >='1988-1-1';
mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f');

查询指定的列

mysql> SELECT name, species, birth FROM pet WHERE species = 'dog' OR species = 'cat';
mysql> SELECT DISTINCT owner FROM pet;    --用DISTINCT对列去重

排序查询

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;

日期计算

mysql> SELECT name, birth, CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;      --- TIMESTAMPDIFF求日期与当前时间差距的年份
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;   --- month转换成的月份
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));     ---DATE_ADD 增加时间  1 MONTH
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1; ---与上一句效果等同

NULL值

mysql> select * from pet where sex is null;
mysql> select * from pet where sex is not null;     ---- null值用于其他比较符号没有意义(如<,>,<>等)

模糊匹配

mysql> SELECT * FROM pet WHERE name LIKE 'b%';      ---以b开头的(不区分大小写)
mysql> SELECT * FROM pet WHERE name LIKE '%fy';      ---以fy结尾的(不区分大小写)
mysql> SELECT * FROM pet WHERE name LIKE '%w%';   ---包含w的(不区分大小写)
SELECT * FROM pet WHERE name LIKE '_____';   --- 以 '_' 下划线模糊匹配字符，左边正好五个连起来，代表正好五个字符串的
mysql> SELECT * FROM pet WHERE name REGEXP '^b';    ---以b开头（不区分大小写）
mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';  ---以b开头（区分大小写）
mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';    ---以fy结尾的(不区分大小写)
mysql> SELECT * FROM pet WHERE name REGEXP 'w';      ---包含w的(不区分大小写)
mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';  --- ^代表开头，$代表结束，中间 '.' 代表字符,五个'.'代表个字符
mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$'    --同上条sql

统计行数

mysql> SELECT COUNT(*) FROM pet;    --统计所有
mysql> SELECT species, sex, COUNT(*) FROM pet WHERE sex IS NOT NULL GROUP BY species, sex;      --分组过滤统计

使用多表

mysql> SELECT pet.name,TIMESTAMPDIFF(YEAR,birth,date) AS age,remark FROM pet INNER JOIN event ON pet.name = event.name WHERE event.type = 'litter';