mysql学习笔记002

来源：互联网发布：android高级编程视频编辑：程序博客网时间：2024/05/22 15:28

1.登陆数据库

$>mysql -u liliang -p

enter密码：

切换数据：

$>use test001

2.创建销售代表的数据表

mysql> create table sales_rep(employee_number int,
-> surname varchar(40),
-> first_name varchar(30),
-> commission tinyint
-> );

常看创建的表：

mysql>show tables （列出当前数据库所有的表）

mysql>show databases(列出当前用户下所有数据)

检查表结构：

mysql> describe sales_rep;

3.插入数据

mysql> insert into sales_rep(employee_number,surname,first_name,commission) values(1,'Rive','So1',10);

mysql> insert into sales_rep(employee_number,surname,first_name,commission) values(2,'Goruimer','Charlens',15);

mysql> insert into sales_rep(employee_number,surname,first_name,commission) values(3,'Serote','Mike',10);

便捷方式为：mysql> insert into sales_rep values(1,'Rive','So1',10);（必须以数据库定义字段的顺序来输入）

更便捷的方式为（每条记录用逗号隔开）：mysql> insert into sales_rep(employee_number,surname,first_name,commission) values(1,'Rive','So1',10)，(2,'Goruimer','Charlens',15)，(3,'Serote','Mike',10);

从文件导入输入数据库

mysql> load data local infile '/Users/mhc/Desktop/data.txt' into table sales_rep;

查看数据：select * from sales_rep;

data.txt内容（单词以制表符隔开（tab,\t））：

4 li liliang 10
5 zhang san 5
6 zhao wei 22

参考内容：

http://www.jb51.net/article/33365.htm

二：

从表中检索数据

mysql> select commission from sales_rep where surname='li';
+------------+
| commission |
+------------+
| 10 |
+------------+

mysql> select commission,employee_number from sales_rep where surname='li';

+------------+-----------------+
| commission | employee_number |
+------------+-----------------+
| 10 | 4 |
+------------+-----------------+

mysql> select * from sales_rep where surname='li';
+-----------------+---------+------------+------------+
| employee_number | surname | first_name | commission |
+-----------------+---------+------------+------------+
| 4 | li | liliang | 10 |
+-----------------+---------+------------+------------+

ysql> select * from sales_rep where commission>10 or surname='li' and first_name='liang';
+-----------------+----------+------------+------------+
| employee_number | surname | first_name | commission |
+-----------------+----------+------------+------------+
| 2 | Goruimer | Charlens | 15 |
| 6 | zhao | wei | 22 |
+-----------------+----------+------------+------------+

(2)模式匹配：like和%(%:0个或多个字符)

mysql> select * from sales_rep where surname like 'L%' ;

+-----------------+---------+------------+------------+
| employee_number | surname | first_name | commission |
+-----------------+---------+------------+------------+
| 4 | li | liliang | 10 |
+-----------------+---------+------------+------------+

mysql> select * from sales_rep where surname like '%h%' ;
+-----------------+---------+------------+------------+
| employee_number | surname | first_name | commission |
+-----------------+---------+------------+------------+
| 5 | zhang | san | 5 |
| 6 | zhao | wei | 22 |
+-----------------+---------+------------+------------+

mysql> select * from sales_rep where surname like '%h%o' ;(任何位置有一个h字符并以o结束)
+-----------------+---------+------------+------------+
| employee_number | surname | first_name | commission |
+-----------------+---------+------------+------------+
| 6 | zhao | wei | 22 |
+-----------------+---------+------------+------------+

(3)分类

mysql> select * from sales_rep order by surname;

mysql> select * from sales_rep order by surname,first_name;

acs默认排序顺序为升序，desc：降序（递减的顺序）

mysql> select * from sales_rep order by commission desc;

mysql> select * from sales_rep order by commission desc,surname asc,first_name asc;

(4)限制结果数量

mysql> select first_name,surname,commission from sales_rep order by commission desc limit 1;

limit 0 大数据库上测试查询而不实际运行查询时，它是一个有用的方法。

limit m,n(m:表示偏移量，n:表示限制行数,)从第m行开始，显示n行

mysql> select first_name,surname,commission from sales_rep order by commission desc;
+------------+----------+------------+
| first_name | surname | commission |
+------------+----------+------------+
| wei | zhao | 22 |
| Charlens | Goruimer | 15 |
| Mike | smith | 12 |
| So1 | Rive | 10 |
| Mike | Serote | 10 |
| liliang | li | 10 |
| Managene | Rive | 10 |
| san | zhang | 5 |
+------------+----------+------------+

mysql> select first_name,surname,commission from sales_rep order by commission desc limit 2,2;
+------------+---------+------------+
| first_name | surname | commission |
+------------+---------+------------+
| Mike | smith | 12 |
| So1 | Rive | 10 |
+------------+---------+------------+

limit 1 与limit 0，1是一样的，偏移量默认值0

（4）max返回最大值

mysql> select max(commission) from sales_rep;

(5)返回唯一的记录（distinct）

(6)计数

mysql> select count(surname) from sales_rep;与mysql> select count(*) from sales_rep;相同

去除重复的条目

mysql> select count(distinct surname) from sales_rep;

（7）avg(),min(),sum()

mysql> select avg(commission) from sales_rep;

mysql> select min(commission) from sales_rep;

mysql> select sum(commission) from sales_rep;

（8）在查询中进行计算（SQL允许在查询中执行计算）

mysql> select 1+1;
+-----+
| 1+1 |
+-----+
| 2 |
+-----+

mysql> select first_name,surname,commission+100 from sales_rep;

(9)删除记录

mysql> delete from sales_rep where employee_number = 5;

mysql> select from sales_rep;(删除表中所有记录)

（10）在表中更改记录

mysql> update sales_rep set commission=12 where employee_number=1;

(11)删除表和数据库

创建表：mysql> create table commission (id int);

删除表：mysql> drop table commission;

创建数据库：mysql> create database shortlived;

删除数据库：mysql> drop database shortlived;

（12）添加列

最后一个ddl语句是alter,允许修改表的结构。添加列，改变列的定义，重新命名表和删除表。

添加列：

mysql> alter table sales_rep add date_joined date;

mysql> describe sales_rep;（查看表结构）

(13)更改列定义

change oldcolumnname newcolumnname type :oldcolumnname:旧列名,newcolumnname(新列名)，type:新列类型

mysql> alter table sales_rep change year_born birthday date;

(14)为表重新命名

mysql> alter table sales_rep rename cash_flow_specialist;

（15）更改列定义

改变列类型，不改变列名mysql> alter table sales_rep rename cash_flow_specialist;

(16)删除列

mysql> alter table sales_rep drop date_joined;

0 0