数据库
文章平均质量分 66
风凌乱雨纷飞
戏如人生 人生如戏
展开
-
SQL命令类别
SQL命令分类:数据定义语言 DDL数据操作语言 DML数据查询语言 DQL数据控制语言 DCL数据管理命令事务控制命令DDL命令包括:CREATE TABLEALTER TABLEDROP TABLECREATE INDEXALTER INDEXDROP INDEXCREATE VIEWDROP VIEWDML命令包括:INSERTUPDATEDE原创 2015-01-14 09:58:04 · 746 阅读 · 0 评论 -
SQL基础(一二)--- 匹配
单字符匹配:查询以‘erry’结尾的名称的员工信息:mysql> select * from t_employee -> where fname like '_erry';+----------+-------+------+---------+| fnumber | fname | fage | fsalary |+----------+-------+-----原创 2015-02-05 14:31:38 · 400 阅读 · 0 评论 -
SQL基础(一六)--- 范围值检测
查询年龄在23~27岁的员工信息:mysql> select * from t_employee -> where fage>=23 and fage<=27;+----------+-------+------+---------+| fnumber | fname | fage | fsalary |+----------+-------+------+---------+原创 2015-02-05 15:08:06 · 513 阅读 · 0 评论 -
SQL基础(一七)--- 数据分组
数据表信息:mysql> select * from t_employee;+----------+-------+------+---------+-------------+---------------+| fnumber | fname | fage | fsalary | fsubcompany | fdepartment |+----------+-------+---原创 2015-02-05 15:29:42 · 404 阅读 · 0 评论 -
SQL基础(一四)--- 反义运算符
查询年龄不等于22,并且工资不小于2000的员工信息:方式一mysql> select * from t_employee -> where fage=2000;+----------+-------+------+---------+| fnumber | fname | fage | fsalary |+----------+-------+------+---------原创 2015-02-05 14:48:12 · 1510 阅读 · 0 评论 -
SQL基础(一一)--排序
默认排序:mysql> select * from t_employee;+----------+-------+------+---------+| fnumber | fname | fage | fsalary |+----------+-------+------+---------+| DEV001 | Tom | 25 | 8300 || DEV002原创 2015-02-05 14:05:48 · 516 阅读 · 0 评论 -
SQL基础(一三)--- 空值检测
查询姓名为NULL的员工信息:错误查询:mysql> select * from t_employee -> where fname=null;Empty set正确查询:mysql> select * from t_employee -> where fname is null;+---------+-------+------+---------+| fn原创 2015-02-05 14:39:27 · 664 阅读 · 0 评论 -
SQL基础(一五)--- 多值检测
查询年龄是23、25、28的员工信息:方式一mysql> select * from t_employee -> where fage=23 or fage=25 or fage=28;+----------+-------+------+---------+| fnumber | fname | fage | fsalary |+----------+-------+----原创 2015-02-05 14:55:44 · 611 阅读 · 0 评论 -
SQL基础(一九)--- HAVING与LIMIT
查询各个年龄段人数大于1的年龄段:mysql> select fage,count(*) from t_employee -> group by fage -> having count(*)>1;+------+----------+| fage | count(*) |+------+----------+| 23 | 2 || 25 |原创 2015-02-05 16:51:24 · 2168 阅读 · 1 评论 -
SQL基础(廿一)---UNION
UNION 规则:每个结果集必须有相同的列数;每个结果集的列必须类型相容。初始化条件:mysql> CREATE TABLE T_TempEmployee (FIdCardNumber VARCHAR(20),FName VARCHAR(20),FAgeINT ,PRIMARY KEY (FIdCardNumber)) -> ;Query OK, 0 row原创 2015-02-10 15:01:37 · 591 阅读 · 0 评论 -
SQL基础(廿三)--- 数学函数
求绝对值:mysql> select fweight-50, abs(fweight-50), abs(-3.6) from t_person;+------------+-----------------+-----------+| fweight-50 | abs(fweight-50) | abs(-3.6) |+------------+-----------------+---原创 2015-02-10 16:52:43 · 559 阅读 · 0 评论 -
SQL基础(一零)---Count
查询员工总数:mysql> select count(*), count(fnumber) from t_employee;+----------+----------------+| count(*) | count(fnumber) |+----------+----------------+| 8 | 8 |+----------+---原创 2015-02-05 13:55:24 · 1014 阅读 · 0 评论 -
SQL基础(廿)--- 抑制重复
查询每个员工的部门:mysql> select fdepartment from t_employee;+---------------+| fdepartment |+---------------+| Development || Development || HumanResource || HumanResource || InfoTech ||原创 2015-02-06 09:56:47 · 544 阅读 · 0 评论 -
SQL基础(二)
创建表:mysql> create table t_person( -> fname varchar(20), -> fage int, -> fremark varchar(20), -> primary key(fname) -> );Query OK, 0 rows affectedmysql> create table t_debt(原创 2015-02-05 09:15:21 · 504 阅读 · 0 评论 -
SQL基础(三)
非空约束对数据插入的影响:mysql> insert into t_debt(fnumber,fperson) values('1','Jim');1364 - Field 'famount' doesn't have a default valuemysql> insert into t_debt(fnumber,famount,fperson) values('1',200,'Jim'原创 2015-02-05 09:49:30 · 530 阅读 · 0 评论 -
SQL基础(四)--update
将表t_person中所有人员的FREMARK字段值更新为“SuperMan”mysql> update t_person -> set fremark='SuperMan';Query OK, 7 rows affectedRows matched: 7 Changed: 7 Warnings: 0mysql> select * from t_person;+-----原创 2015-02-05 10:06:55 · 515 阅读 · 0 评论 -
SQL基础(六)- 删除
清空表数据:mysql> select * from t_person;+----------+------+---------+| fname | fage | fremark |+----------+------+---------+| Iurenl | 25 | BlaBla || Jim | 22 | BlaBla || Kimi原创 2015-02-05 10:59:36 · 466 阅读 · 0 评论 -
SQL基础(五)-update2
非空约束对数据更新的影响:mysql> update t_debt -> set famount=NULL -> where fperson='Tom';1048 - Column 'famount' cannot be nullmysql> update t_debt -> set famount=199.99 -> where fperson='Tom'原创 2015-02-05 10:20:46 · 455 阅读 · 0 评论 -
基础SQL(一)
查看MySQL中的数据库:mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sakila ||原创 2015-02-05 09:09:43 · 392 阅读 · 0 评论 -
SQL基础(七)---SELECT
初始数据:mysql> create table t_employee( -> fnumber varchar(20), -> fname varchar(20), -> fage int, -> fsalary decimal(10,2), -> primary key(fnumber));Query OK, 0 rows affectedmys原创 2015-02-05 11:35:12 · 412 阅读 · 0 评论 -
SQL基础(八)--SELECT
查询所有工资少于5000的员工姓名和工资:mysql> select fname,fsalary from t_employee where fsalary<5000;+-------+---------+| fname | fsalary |+-------+---------+| Jerry | 2300.8 || Jane | 2200.88 || Smith | 390原创 2015-02-05 11:41:47 · 433 阅读 · 0 评论 -
SQL基础(九)---聚合函数
查询年龄大于25岁的员工最高工资:mysql> select max(fsalary) from t_employee where fage>25;+--------------+| max(fsalary) |+--------------+| 6200 |+--------------+1 row in set查询工资大于3800元的员工平均年龄:mys原创 2015-02-05 11:50:30 · 410 阅读 · 0 评论 -
SQL基础(一八)--- 数据分组与聚合函数
查询各个年龄段的人数:mysql> select fage, count(*) from t_employee -> group by fage;+------+----------+| fage | count(*) |+------+----------+| 22 | 1 || 23 | 2 || 25 | 2 |原创 2015-02-05 16:38:53 · 556 阅读 · 0 评论 -
SQL基础(廿二)---UNION ALL
UNION与UNION ALL的区别:mysql> select * from t_employee;+----------+-------+------+---------+-------------+---------------+| fnumber | fname | fage | fsalary | fsubcompany | fdepartment |+---------原创 2015-02-10 15:44:44 · 866 阅读 · 0 评论