数据库简单命令

mysql -u root -p //登陆
quit /q //退出
select version(),current_date; //获得当前版本号 日期

select (4+1)*5; //作为计算器

/c   //清除刚才输入  等待新的输入

show databases; //查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| biweb              |
| mysql              |
| phpmyadmin         |
| phpnews            |
| test               |
+--------------------+
use test;               //使用数据库
create database menagerie;   //创建数据库
show tables;      //检查数据库中 表
create table pet(name varchar(20),owner varchar(20),species varchar(20),sex char(1),birth DATE,death DATE);         //创建表 并且为表添加字段

desc pet;  //检查表创建样式
insert into pet values('Puffball','Diane','hamster','f','1999-03-30',NULL);  //向表中插入数据

select * from pet;  // 选择表中的所有数据
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
| Pll      | De    | h       | f    | 2999-03-30 | NULL  |
| dd       | nn    | h       | f    | 2999-03-30 | NULL  |
| aa       | De    | h       | f    | 2999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+

update pet set owner='nn'where name='dd'; //修改数据

select * from pet where name='Chirpy' and sex='f';  //条件查找 and or and比or的优先级别高  可以联合使用
select distinct name from pet;           //distinct 过滤重复信息
select birth from pet order by birth;    //按birth 排序 可以和desc  混合使用
select * from pet where name like 'p%';  //匹配查找
select * from pet where name like'___';   //使用'_'模式匹配字符
+------+-------+---------+------+------------+-------+
| name | owner | species | sex  | birth      | death |
+------+-------+---------+------+------------+-------+
| Pll  | De    | h       | f    | 2999-03-30 | NULL  |
+------+-------+---------+------+------------+-------+

select * from pet where name regexp'^p';   //关键字的作用是查询 p开头regexp
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
| Pll      | De    | h       | f    | 2999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+

select * from pet where name regexp'a$'  ; //关键字的作用是查询a结尾的regex
+------+-------+---------+------+------------+-------+
| name | owner | species | sex  | birth      | death |
+------+-------+---------+------+------------+-------+
| aa   | De    | h       | f    | 1985-05-23 | NULL  |
+------+-------+---------+------+------------+-------+


select database();  //查看当前使用哪个数据库


create table shop(article int (4),dealer char (20),prioce double(16,2),primary key(article,dealer));  //创建表
insert into shop values (1,'A',3.45),(1,'B',3.99),(3,'B',1.45),(3,'C',1.49),(4,'D',19.93); //添加数据

select p1.name from pet as p1; //可以使用熟悉的变量
+----------+
| name     |
+----------+
| Puffball |
| Pll      |
| dd       |
| aa       |
+----------+
4 rows in set (0.00 sec)