- 浏览: 197188 次
- 性别:
- 来自: 上海
文章分类
最新评论
-
qiankai86:
s
多个文件上传的功能 -
zhjxzhj:
经测试不能用
PDF破解软件 -
meadlai:
很不错...哈哈...
PDF破解软件 -
talin2010:
刚学了,复习一下。。
Mysql+tomcat连接池自己的例子 -
yshuaiwen:
上面的方法都不怎么好,太麻烦,而且都需要改tomcat的xml ...
Mysql+tomcat连接池的配置实例
MYSQL完全手册学习笔记
SQL基础
User 库名——使用某个库
Show 表名——显示表的各个字段
Select count (*) from callslog ——显示一共有多少条记录
mysql> create database toys;——建立个数据库
Query OK, 1 row affected (0.02 sec)
mysql> use toys;
Database changed
mysql> show tables;
Empty set (0.01 sec)
mysql> create table user(id int(11) not null auto_increment,primary key(id));
建立个表
mysql> create table status(id int(11) not null,video_id tinyint(11) not null);
Query OK, 0 rows affected (0.08 sec)
mysql> alter table status add (hyht varchar(255));
Query OK, 0 rows affected (0.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
增加字段
mysql> alter table status rename haha;
Query OK, 0 rows affected (0.03 sec)
Alter的用法
mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));
重命名表,从t1到t2:
mysql> ALTER TABLE t1 RENAME t2;
为了改变列a,从INTEGER改为TINYINT NOT NULL(名字一样),并且改变列b,从CHAR(10)改为CHAR(20),同时重命名它,从b改为c:
mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
增加一个新TIMESTAMP列,名为d:
mysql> ALTER TABLE t2 ADD d TIMESTAMP;
在列d上增加一个索引,并且使列a为主键:
mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
删除列c:
mysql> ALTER TABLE t2 DROP COLUMN c;
增加一个新的AUTO_INCREMENT整数列,命名为c:
mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD INDEX (c);
注意,我们索引了c,因为AUTO_INCREMENT柱必须被索引,并且另外我们声明c为NOT NULL,因为索引了的列不能是NULL。 当你增加一个AUTO_INCREMENT列时,自动地用顺序数字填入列值。
mysql> insert into t2 (a,c)values(1,'hghg');
Query OK, 1 row affected (0.03 sec)
添加记录
mysql> alter table t2 drop column c;
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除列
mysql> alter table t2 add (c varchar(255));
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
增加列
注意:一个表中必须有主键才能在mysql插件中直接编辑,否则edit那项为灰色
mysql> delete from haha where video_id=2;
Query OK, 1 row affected (0.03 sec)
有条件的删除数据
mysql> update haha set hyht='tt'where hyht='1';
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0
更新数据
mysql> select * from haha;
+----+----------+------+
| id | video_id | hyht |
+----+----------+------+
| 1 | 3 | tt |
| 2 | 3 | erer |
| 3 | 3 | tt |
| 4 | 1 | tt |
| 5 | 2 | tt |
+----+----------+------+
5 rows in set (0.01 sec)
mysql> select hyht from haha
+------+
| hyht |
+------+
| tt |
| erer |
| tt |
| tt |
| tt |
+------+
5 rows in set (0.00 sec)
mysql> select distinct hyht
+------+
| hyht |
+------+
| tt |
| erer |
+------+
2 rows in set (0.01 sec)
三个select 查询语句
mysql> select * from haha where video_id>2;
+----+----------+------+
| id | video_id | hyht |
+----+----------+------+
| 1 | 3 | tt |
| 2 | 3 | erer |
| 3 | 3 | tt |
+----+----------+------+
3 rows in set (0.00 sec)
mysql> select * from chengji;
+----+-------+------+---------+------------+
| id | name | math | physics | literature |
+----+-------+------+---------+------------+
| 1 | john | 60 | 37 | 45 |
| 2 | jim | 96 | 89 | 92 |
| 3 | bill | 65 | 12 | 57 |
| 4 | harry | 69 | 85 | 12 |
+----+-------+------+---------+------------+
4 rows in set (0.00 sec)
查询chengji表
mysql> select * from chengji where math>90;
+----+------+------+---------+------------+
| id | name | math | physics | literature |
+----+------+------+---------+------------+
| 2 | jim | 96 | 89 | 92 |
+----+------+------+---------+------------+
1 row in set (0.00 sec)
查询math〉90 分的人
mysql> select name from chengji where math>85 and physics>85 and literature >85;
+------+
| name |
+------+
| jim |
+------+
1 row in set (0.00 sec)
查询出来的优秀学生
mysql> select * from chengji where math<25 or physics <25 or literature <25;
+----+-------+------+---------+------------+
| id | name | math | physics | literature |
+----+-------+------+---------+------------+
| 3 | bill | 65 | 12 | 57 |
| 4 | harry | 69 | 85 | 12 |
+----+-------+------+---------+------------+
2 rows in set (0.00 sec)
其中有一门没有过25分的学生
mysql> select name,math+physics+literature from chengji;
+-------+-------------------------+
| name | math+physics+literature |
+-------+-------------------------+
| john | 142 |
| jim | 277 |
| bill | 134 |
| harry | 166 |
+-------+-------------------------+
得出三门成绩的总分数
mysql> select name,math+physics+literature as zf from chengji order by zf;
+-------+-----+
| name | zf |
+-------+-----+
| bill | 134 |
| john | 142 |
| harry | 166 |
| jim | 277 |
+-------+-----+
4 rows in set (0.00 sec)
按照从低到高排列
mysql> select name,math+physics+literature as zf from chengji order by zf desc
+-------+-----+
| name | zf |
+-------+-----+
| jim | 277 |
| harry | 166 |
| john | 142 |
| bill | 134 |
+-------+-----+
4 rows in set (0.00 sec)
按照从高到低的顺序排列
内建函数
Sum、avg、min、max、
mysql> select avg(math), avg(physics) from chengji;
+-----------+--------------+
| avg(math) | avg(physics) |
+-----------+--------------+
| 72.5000 | 55.7500 |
+-----------+--------------+
1 row in set (0.00 sec)
mysql> select min(math) from chengji;
+-----------+
| min(math) |
+-----------+
| 60 |
+-----------+
1 row in set (0.00 sec)
mysql> select max(math) from chegnji;
ERROR 1146 (42S02): Table 'toys.chegnji' doesn't exist
mysql> select max(math) from chengji;
+-----------+
| max(math) |
+-----------+
| 96 |
+-----------+
1 row in set (0.00 sec)
Limit后面跟的2个参数是从开始行的位置和要显示行的个数
mysql> select * from chengji order by math desc limit 0,4;
+----+-------+------+---------+------------+
| id | name | math | physics | literature |
+----+-------+------+---------+------------+
| 2 | jim | 96 | 89 | 92 |
| 4 | harry | 69 | 85 | 12 |
| 3 | bill | 65 | 12 | 57 |
| 1 | john | 60 | 37 | 45 |
+----+-------+------+---------+------------+
4 rows in set (0.00 sec)
mysql> select * from chengji order by math desc limit 2,4;
+----+------+------+---------+------------+
| id | name | math | physics | literature |
+----+------+------+---------+------------+
| 3 | bill | 65 | 12 | 57 |
| 1 | john | 60 | 37 | 45 |
+----+------+------+---------+------------+
2 rows in set (0.00 sec)
通配符模糊查询
mysql> select * from chengji where name like '%j%';
+----+------+------+---------+------------+
| id | name | math | physics | literature |
+----+------+------+---------+------------+
| 1 | john | 60 | 37 | 45 |
| 2 | jim | 96 | 89 | 92 |
+----+------+------+---------+------------+
2 rows in set (0.00 sec)
mysql> select math from chengji where name like '%j%';
+------+
| math |
+------+
| 60 |
| 96 |
+------+
2 rows in set (0.02 sec)
mysql> select name,math from chengji where math like '%9%';
+-------+------+
| name | math |
+-------+------+
| jim | 96 |
| harry | 69 |
+-------+------+
2 rows in set (0.00 sec)
mysql> select * from haha,chengji;
查询多个表里面所有字段,也可以查询出来需要的字段
mysql> select m.hyht,v.math from haha m,chengji v;
别名
SQL基础
User 库名——使用某个库
Show 表名——显示表的各个字段
Select count (*) from callslog ——显示一共有多少条记录
mysql> create database toys;——建立个数据库
Query OK, 1 row affected (0.02 sec)
mysql> use toys;
Database changed
mysql> show tables;
Empty set (0.01 sec)
mysql> create table user(id int(11) not null auto_increment,primary key(id));
建立个表
mysql> create table status(id int(11) not null,video_id tinyint(11) not null);
Query OK, 0 rows affected (0.08 sec)
mysql> alter table status add (hyht varchar(255));
Query OK, 0 rows affected (0.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
增加字段
mysql> alter table status rename haha;
Query OK, 0 rows affected (0.03 sec)
Alter的用法
mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));
重命名表,从t1到t2:
mysql> ALTER TABLE t1 RENAME t2;
为了改变列a,从INTEGER改为TINYINT NOT NULL(名字一样),并且改变列b,从CHAR(10)改为CHAR(20),同时重命名它,从b改为c:
mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
增加一个新TIMESTAMP列,名为d:
mysql> ALTER TABLE t2 ADD d TIMESTAMP;
在列d上增加一个索引,并且使列a为主键:
mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
删除列c:
mysql> ALTER TABLE t2 DROP COLUMN c;
增加一个新的AUTO_INCREMENT整数列,命名为c:
mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD INDEX (c);
注意,我们索引了c,因为AUTO_INCREMENT柱必须被索引,并且另外我们声明c为NOT NULL,因为索引了的列不能是NULL。 当你增加一个AUTO_INCREMENT列时,自动地用顺序数字填入列值。
mysql> insert into t2 (a,c)values(1,'hghg');
Query OK, 1 row affected (0.03 sec)
添加记录
mysql> alter table t2 drop column c;
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除列
mysql> alter table t2 add (c varchar(255));
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
增加列
注意:一个表中必须有主键才能在mysql插件中直接编辑,否则edit那项为灰色
mysql> delete from haha where video_id=2;
Query OK, 1 row affected (0.03 sec)
有条件的删除数据
mysql> update haha set hyht='tt'where hyht='1';
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0
更新数据
mysql> select * from haha;
+----+----------+------+
| id | video_id | hyht |
+----+----------+------+
| 1 | 3 | tt |
| 2 | 3 | erer |
| 3 | 3 | tt |
| 4 | 1 | tt |
| 5 | 2 | tt |
+----+----------+------+
5 rows in set (0.01 sec)
mysql> select hyht from haha
+------+
| hyht |
+------+
| tt |
| erer |
| tt |
| tt |
| tt |
+------+
5 rows in set (0.00 sec)
mysql> select distinct hyht
+------+
| hyht |
+------+
| tt |
| erer |
+------+
2 rows in set (0.01 sec)
三个select 查询语句
mysql> select * from haha where video_id>2;
+----+----------+------+
| id | video_id | hyht |
+----+----------+------+
| 1 | 3 | tt |
| 2 | 3 | erer |
| 3 | 3 | tt |
+----+----------+------+
3 rows in set (0.00 sec)
mysql> select * from chengji;
+----+-------+------+---------+------------+
| id | name | math | physics | literature |
+----+-------+------+---------+------------+
| 1 | john | 60 | 37 | 45 |
| 2 | jim | 96 | 89 | 92 |
| 3 | bill | 65 | 12 | 57 |
| 4 | harry | 69 | 85 | 12 |
+----+-------+------+---------+------------+
4 rows in set (0.00 sec)
查询chengji表
mysql> select * from chengji where math>90;
+----+------+------+---------+------------+
| id | name | math | physics | literature |
+----+------+------+---------+------------+
| 2 | jim | 96 | 89 | 92 |
+----+------+------+---------+------------+
1 row in set (0.00 sec)
查询math〉90 分的人
mysql> select name from chengji where math>85 and physics>85 and literature >85;
+------+
| name |
+------+
| jim |
+------+
1 row in set (0.00 sec)
查询出来的优秀学生
mysql> select * from chengji where math<25 or physics <25 or literature <25;
+----+-------+------+---------+------------+
| id | name | math | physics | literature |
+----+-------+------+---------+------------+
| 3 | bill | 65 | 12 | 57 |
| 4 | harry | 69 | 85 | 12 |
+----+-------+------+---------+------------+
2 rows in set (0.00 sec)
其中有一门没有过25分的学生
mysql> select name,math+physics+literature from chengji;
+-------+-------------------------+
| name | math+physics+literature |
+-------+-------------------------+
| john | 142 |
| jim | 277 |
| bill | 134 |
| harry | 166 |
+-------+-------------------------+
得出三门成绩的总分数
mysql> select name,math+physics+literature as zf from chengji order by zf;
+-------+-----+
| name | zf |
+-------+-----+
| bill | 134 |
| john | 142 |
| harry | 166 |
| jim | 277 |
+-------+-----+
4 rows in set (0.00 sec)
按照从低到高排列
mysql> select name,math+physics+literature as zf from chengji order by zf desc
+-------+-----+
| name | zf |
+-------+-----+
| jim | 277 |
| harry | 166 |
| john | 142 |
| bill | 134 |
+-------+-----+
4 rows in set (0.00 sec)
按照从高到低的顺序排列
内建函数
Sum、avg、min、max、
mysql> select avg(math), avg(physics) from chengji;
+-----------+--------------+
| avg(math) | avg(physics) |
+-----------+--------------+
| 72.5000 | 55.7500 |
+-----------+--------------+
1 row in set (0.00 sec)
mysql> select min(math) from chengji;
+-----------+
| min(math) |
+-----------+
| 60 |
+-----------+
1 row in set (0.00 sec)
mysql> select max(math) from chegnji;
ERROR 1146 (42S02): Table 'toys.chegnji' doesn't exist
mysql> select max(math) from chengji;
+-----------+
| max(math) |
+-----------+
| 96 |
+-----------+
1 row in set (0.00 sec)
Limit后面跟的2个参数是从开始行的位置和要显示行的个数
mysql> select * from chengji order by math desc limit 0,4;
+----+-------+------+---------+------------+
| id | name | math | physics | literature |
+----+-------+------+---------+------------+
| 2 | jim | 96 | 89 | 92 |
| 4 | harry | 69 | 85 | 12 |
| 3 | bill | 65 | 12 | 57 |
| 1 | john | 60 | 37 | 45 |
+----+-------+------+---------+------------+
4 rows in set (0.00 sec)
mysql> select * from chengji order by math desc limit 2,4;
+----+------+------+---------+------------+
| id | name | math | physics | literature |
+----+------+------+---------+------------+
| 3 | bill | 65 | 12 | 57 |
| 1 | john | 60 | 37 | 45 |
+----+------+------+---------+------------+
2 rows in set (0.00 sec)
通配符模糊查询
mysql> select * from chengji where name like '%j%';
+----+------+------+---------+------------+
| id | name | math | physics | literature |
+----+------+------+---------+------------+
| 1 | john | 60 | 37 | 45 |
| 2 | jim | 96 | 89 | 92 |
+----+------+------+---------+------------+
2 rows in set (0.00 sec)
mysql> select math from chengji where name like '%j%';
+------+
| math |
+------+
| 60 |
| 96 |
+------+
2 rows in set (0.02 sec)
mysql> select name,math from chengji where math like '%9%';
+-------+------+
| name | math |
+-------+------+
| jim | 96 |
| harry | 69 |
+-------+------+
2 rows in set (0.00 sec)
mysql> select * from haha,chengji;
查询多个表里面所有字段,也可以查询出来需要的字段
mysql> select m.hyht,v.math from haha m,chengji v;
别名
发表评论
-
MYSQL完全手册学习笔记(练习时候的数据库)
2008-10-23 11:14 2014MYSQL完全手册学习笔记(练习时候的数据库) 4-10章的 -
MYSQL完全手册学习笔记(第十章)
2008-10-23 11:05 1749连接 交叉连接 mysql> select * ... -
MYSQL完全手册学习笔记(第九章)
2008-10-21 17:51 1192使用数据 插入、更新 和删除记录 插入 Inser ... -
MYSQL完全手册学习笔记(第八章)
2008-10-20 11:30 2233使用数据库和表 创建数据库 mysql> crea ... -
MYSQL完全手册学习笔记(第七章)
2008-10-16 17:31 1451MYSQL函数 mysql> select round ... -
MySQL 5.1参考手册
2008-10-15 15:49 1158http://dev.mysql.com/doc/refman ... -
MYSQL完全手册学习笔记(第六章)
2008-10-15 15:47 1117运算符 算数运算符 mysql> select 10+ ... -
MYSQL完全手册学习笔记(第五章)
2008-10-15 14:41 1119数据类型 Float(5,2)规定显示的值不会超过5位数字 ... -
MySQL 5.1新特性之事件调度器(Event Scheduler)
2008-10-15 14:39 1323MySQL 5.1新特性之事件调度器(Event Schedu ... -
MySQL常用命令及基本操作总结
2008-10-15 10:46 1805MySQL常用操作基本操作,以下都是MySQL5.0下测试通过 ... -
Mysql命令集锦
2008-09-04 17:45 836Mysql命令集锦 MySQL常 ... -
mysql 驱动文件
2008-08-26 10:04 1488mysql 驱动文件 -
mysql帮助文档
2008-08-26 10:00 2386mysql 的帮助文档说明 -
Mysql命令集锦
2008-08-23 09:32 967Mysql命令集锦 MySQL常 ...
相关推荐
MYSQL完全手册MYSQL完全手册MYSQL完全手册MYSQL完全手册MYSQL完全手册
MySQL学习笔记
mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记
mysql手册mysql手册mysql手册mysql手册mysql手册mysql手册mysql手册mysql手册mysql手册mysql手册mysql手册mysql手册mysql手册mysql手册mysql手册mysql手册mysql手册mysql手册mysql手册mysql手册mysql手册mysql手册...
NULL 博文链接:https://qmug.iteye.com/blog/256867
美河提供.MYSQL完全手册.pdf ,易操作
MySQL DBA深度学习笔记
Mysql学习笔记1
MySQL中文完全手册,扫描版,PDF版,自己逐个加了目录,绝壁比市面上别的文档要好用!
MySQL笔记MySQL笔记MySQL笔记MySQL笔记MySQL笔记MySQL笔记
mysql学习笔记
mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql...
MySQL学习笔记.chmMySQL学习笔记.chmMySQL学习笔记.chmMySQL学习笔记.chm
MySQL数据库学习笔记.pdfMySQL数据库学习笔记.pdfMySQL数据库学习笔记.pdfMySQL数据库学习笔记.pdfMySQL数据库学习笔记.pdfMySQL数据库学习笔记.pdf
mysql的完全参考手册,电子书籍,共1846页,可以说完全囊括了mysql的全部奥秘。打包成了两部分,请下载后一起解压缩。这是第二部份
美河提供.MYSQL完全手册 美河提供.MYSQL完全手册 美河提供.MYSQL完全手册 美河提供.MYSQL完全手册
MySql学习笔记,MySql学习笔记,里面很详细的哈。
mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记
MySQL优化学习思维笔记.xmind