SQL语言包含4个部分:
★ 数据定义语言(DDL),例如:CREATE、DROP、ALTER等语句。
★ 数据操作语言(DML),例如:INSERT(插入)、UPDATE(修改)、DELETE(删除)语句。
★ 数据查询语言(DQL),例如:SELECT语句。
★ 数据控制语言(DCL),例如:GRANT、REVOKE、COMMIT、ROLLBACK等语句。
★ 数据操作语言(DML),例如:INSERT(插入)、UPDATE(修改)、DELETE(删除)语句。
★ 数据查询语言(DQL),例如:SELECT语句。
★ 数据控制语言(DCL),例如:GRANT、REVOKE、COMMIT、ROLLBACK等语句。
关于数据库编码的设置:
在MySQL设置向导中可以设置编码格式,如果设置成了UTF-8在命令行操作时需要注意一下几点:
建表前设置编码:
set names utf8;
查询建表时用的字符集:
show create table tablename;
为了能在Windows命令行执行insert,update,则执行命令:
set character_set_client=gbk;
为了能在Windows命令下能正确查看中文,则执行命令:
set character_set_results=gbk;
创建数据库:
create database StudentManage;
使用数据库:
use StudentManage;
创建数据表:
//学生表(学号,学生姓名) create table Student( Sno char(9) primary key, Sname varchar(20) unique ); //课程表(课程代号,课程名称,先修课程代号) create table Course( Cno char(4) primary key, Cname char(40), Cpno char(4), foreign key (Cpno) references Course(Cno) ); //选课表(学生学号,课程代号) create table SC( Sno char(9), Cno char(4), Primary key(Sno,Cno), foreign key(Sno) references Student(Sno), foreign key(Cno) references Course(Cno) );
mysql> desc Student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sno | char(9) | NO | PRI | NULL | |
| Sname | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> desc Course;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| Cno | char(4) | NO | PRI | NULL | |
| Cname | char(40) | YES | | NULL | |
| Cpno | char(4) | YES | MUL | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.05 sec)
mysql> desc SC;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| Sno | char(9) | NO | PRI | NULL | |
| Cno | char(4) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
修改表结构:
添加一个表字段:
alter table student add column sex char(2);
删除一个表字段:
alter table student drop column sex restrict;
修改字段类型:
alter table Student modify Sname varchar(20); alter table student change Sname Sname varchar(20);
查看表的create语句:
show create table Student;
结果如下:
| Student | CREATE TABLE `student` (
`Sno` char(9) NOT NULL,
`Sname` varchar(20) default NULL,
PRIMARY KEY (`Sno`),
UNIQUE KEY `Sname` (`Sname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
其中的UNIQUE KEY `Sname` (`Sname`)就是约束索引了。
删除字段的约束索引:
alter table Student drop index Sname;
向表中插入数据:
插入全部字段:
insert into Student values ('080010011','arthinking');
插入部分字段:
insert into Course (Cno, Cname) values('0001','Java');
修改字段值:
update Student set Sno='080010013' where Sno='123456789';
删除全部数据:
delete from Student;
删除指定数据:
delete from Student where(Sno=’ 080010013’) or Sname is null;
查询:
查询所有列:
select * from student;
查询指定列:
select Sname from Student;
取消重复行:
select distinct Cname from Course;
模糊查询:
select * from student where Sname like '_a%';
排序:
select * from Student order by Sno asc; select * from Student order by Sno desc;
group by和having子句:
select avg(Cno),Cname from Course group by Cname having avg(Cno) >2;
Course表包含数据如下:
+------+-------+------+
| Cno | Cname | Cpno |
+------+-------+------+
| 0001 | Java | NULL |
| 0002 | C++ | NULL |
| 0003 | C++ | NULL |
+------+-------+------+
3 rows in set (0.00 sec)
查询结果如下:
+----------+-------+
| avg(Cno) | Cname |
+----------+-------+
| 2.5 | C++ |
+----------+-------+
1 row in set (0.06 sec)
多表查询:
连接查询:
查询有选课的学生:
select s.Sname from Student s,SC sc where s.Sno = sc.Sno;
多行子查询:
查询在选课表中有选课的学生的全部信息:
select * from Student s where s.Sno in (select sc.Sno from SC sc);
在from自句中使用子查询创建临时表:
查找除了0001号课程外已有人选修了的课程的名称:
mysql> select distinct c.Cname from Course c,(select Cno,Sno from SC where Cno not in('0001') ) temp where c.Cno=temp.Cno;
分页查询:
MySQL:select * from Student limit 0,2;