mysql> create database xs_kc;
Query OK, 1 row affected (0.05 sec)
mysql> use xs_kc;
Database changed
mysql> create table xs1( XS_id char(6) primary key not null,
-> XS_name varchar(10) null,
-> XS_major varchar(20) null,
-> XS_sex int(1) not null default 1,
-> XS_birth date null,
-> XS_credit tinyint null,
-> XS_photo blob null,
-> XS_remark text null);
Query OK, 0 rows affected (0.11 sec)
mysql> rename table xs1 to xs;
Query OK, 0 rows affected (0.06 sec)
mysql> insert into xs values('081101', '王林','计算机',1,'19930405', '60',null,n
ull);
Query OK, 1 row affected (0.05 sec)
mysql> insert into xs values('081102','张侃','数学系',1,'19940603','58',null,nul
l);
Query OK, 1 row affected (0.05 sec)
mysql> insert into xs set XS_id='081103',XS_name='李斯',
-> XS_major='音乐系',
-> XS_sex=0,XS_birth='19940807',
-> XS_credit=50;
Query OK, 1 row affected (0.05 sec)
mysql> insert into xs values('081101', '王林','计算机',1,'19930405', '60',null,n
ull);
ERROR 1062 (23000): Duplicate entry '081101' for key 'PRIMARY'
mysql> replace into xs values('081101', '刘华','通信工程',1,'19940505', '48',nul
l,null);
Query OK, 2 rows affected (0.05 sec)
mysql> insert into xs values('081104', '程明', '生物工程', 1, '19930508', 50,'f:
\m10006s.jpg', NULL);
Query OK, 1 row affected (0.03 sec)
mysql> insert into xs values('081105', '陈红', '计算机', 0, '19910907', 50, load
_file('f:\m10006s.jpg'),null);
Query OK, 1 row affected (0.09 sec)
mysql> delete from xs where xs_id='081105';
Query OK, 1 row affected (0.08 sec)
mysql> update xs set xs_id='081251',xs_remark='转专业学习' where xs_name='程明';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from xs;
+--------+---------+----------+--------+------------+-----------+---------------
+------------+
| XS_id | XS_name | XS_major | XS_sex | XS_birth | XS_credit | XS_photo
| XS_remark |
+--------+---------+----------+--------+------------+-----------+---------------
+------------+
| 081101 | 刘华 | 通信工程 | 1 | 1994-05-05 | 48 | NULL
| NULL |
| 081102 | 张侃 | 数学系 | 1 | 1994-06-03 | 58 | NULL
| NULL |
| 081103 | 李斯 | 音乐系 | 0 | 1994-08-07 | 50 | NULL
| NULL |
| 081251 | 程明 | 生物工程 | 1 | 1993-05-08 | 50 | f:m10006s.jpg
| 转专业学习 |
+--------+---------+----------+--------+------------+-----------+---------------
+------------+
4 rows in set (0.05 sec)
mysql> select xs_id,xs_name,
-> case when xs_credit is null then '尚未选课'
-> when xs_credit <50 then '不及格'
-> when xs_credit >=50 and xs_credit<=52 then '合格'
-> else'优秀'
-> end as level
-> from xs;
+--------+---------+--------+
| xs_id | xs_name | level |
+--------+---------+-
-------+
| 081101 | 刘华 | 不及格 |
| 081102 | 张侃 | 优秀 |
| 081103 | 李斯 | 合格 |
| 081251 | 程明 | 合格 |
+--------+---------+--------+
4 rows in set (0.00 sec)
mysql> create index xh_xs on xs(xs_id(5) asc);
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show grants for root@localhost;
+-------------------------------------------------------------------------------
---------------------------------------------------------+
| Grants for root@localhost
|
+-------------------------------------------------------------------------------
---------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*38D
F00F36027E4EA7B7B269C0ECD0616CCC1370A' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
|
+-------------------------------------------------------------------------------
---------------------------------------------------------+
2 rows in set (0.02 sec)
mysql> select * from xs order by xs_id limit 2;
+--------+---------+----------+--------+------------+-----------+----------+----
-------+
| XS_id | XS_name | XS_major | XS_sex | XS_birth | XS_credit | XS_photo | XS_
remark |
+--------+---------+----------+--------+------------+-----------+----------+----
-------+
| 081101 | 刘华 | 通信工程 | 1 | 1994-05-05 | 48 | NULL | NUL
L |
| 081102 | 张侃 | 数学系 | 1 | 1994-06-03 | 58 | NULL | NUL
L |
+--------+---------+----------+--------+------------+-----------+----------+----
-------+
2 rows in set (0.01 sec)
mysql> select * from xs order by xs_id limit 2,2;
+--------+---------+----------+--------+------------+-----------+---------------
+------------+
| XS_id | XS_name | XS_major | XS_sex | XS_birth | XS_credit | XS_photo
| XS_remark |
+--------+---------+----------+--------+------------+-----------+---------------
+------------+
| 081103 | 李斯 | 音乐系 | 0 | 1994-08-07 | 50 | NULL
| NULL |
| 081251 | 程明 | 生物工程 | 1 | 1993-05-08 | 50 | f:m10006s.jpg
| 转专业学习 |
+--------+---------+----------+--------+------------+-----------+---------------
+------------+
2 rows in set (0.00 sec)
mysql> ^A^A