MYSQL基础操作与常用方法
一.常用Dos操作指令:
mysqld -install安装数据库,net stop/start mysql关闭/启动数据库,netstat -a 监听端口 (3306),mysql - uroot -p 登陆,选定默认数据库:use dbname,显示所有数据库:show databases,显示默认数据库中所有表:show tables,放弃正在输入的命令:\c,显示命令清单:\h,退出mysql程序:\q,查看MySQL服务器状态信息:\s
二.SQL语法组成:
1.DML(Data Manipulation Language数据操作语言)——SELECT、INSERT、UPDATE 、DELETE等;
2.DCL(Data Control Language数据控制语言)——GRANT、REVOKE等;
3.DDL(Data Definition Language数据定义语言)——CREATE TABLE、DROP TABLE、ALTER TABLE等;
4.功能函数。
三.常用数据类型:文本、数字、时间/日期
1.Text型:
CHAR(size) 固定长度不超过255字符,VARCHAR(size)可变不超过255字符,TINYTEXT最大长度为 255字符 ,TEXT最大长度为65535字符 ;
BLOB 最多 65535字节 ,MEDIUMTEXT存16777215个字符,MEDIUMBLOB存放最多16777215 字节,LONGTEXT /LONGBLOB存放4,294,967,295字符/字节
ENUM(x,y,z,etc.) 可以在 ENUM列表中列出最大 65535个值,SET与 ENUM类似,SET最多只能包含 64个列表项,不过 SET可存储一个以上的值。
2.Number型
TINYINT(size) -128 到 127 ,SMALLINT(size) -32768到 32767,MEDIUMINT(size) -8388608到 8388607,INT(size) -2147483648到 2147483647,BIGINT(size) -9223372036854775808到 9223372036854775807。
FLOAT(size,d) ,DOUBLE(size,d),DECIMAL(size,d)在括号中规定最大位数。在 d参数中规定小数点右侧的最大位数。
3.Date型:
DATE() 格式:YYYY-MM-DD;DATETIME()格式:YYYY-MM-DD HH:MM:SS;TIMESTAMP()(1970-01-01 00:00:01开始) 格式:YYYY-MM-DD HH:MM:SS;TIME()格式:HH:MM:SS;YEAR() 2位或 4位格式的年。
四.完整性约束
PRIMARY KEY 主键;UNIQUE唯一性约束;NOT NULL非空;AUTO_INCREMENT自增;DEFAULT默认值;CHARACTER SET‘name’指定字符集
五.运算符
比较运算符:<> , BETWEEN ,IN,<=>,BINARY(区分大小写)
位运算符:<< a<<b将a左移b个二进制位,即a*2的b次方;>> 与’<<’相反;
六.集函数
1.记数函数: count(列名)计算元素的个数
2.求和函数: sum(列名) 对某一列的值求和,但属性必须是整型
3.计算平均值:avg(列名)对某一列的值计算平均值
4.求最大值: max(列名)找出某一列的最大值
5.求最小值: min(列名)找出某一列的最小值
七.增删改查基本操作
创建数据库:CREATE DATABASE MySchool CHARACTER SET utf8 ;
删除数据库:DROP DATABASE MySchool;
创建学生表:CREATE TABLE student(sid INT NOT NULL AUTO_INCREMENT,NAME VARCHAR(20) NOT NULL,PRIMARY KEY(sid));
删除表:DROP TABLE student;
添加新字段:ALTER TABLE student ADD COLUMN sex BIT NULL AFTER NAME;
删除字段:ALTER TABLE student DROP (COLUMN )email;
修改字段:ALTER TABLE student CHANGE sex sex CHAR(2) NULL;ALTER TABLE student CHANGE email email VARCHAR(50) NULL;
数据库插入数据:INSERT INTO student (NAME, sex, age, email) VALUES ('宋仲基', '男', '25', '22@qq.com');
更新表数据:UPDATE student SET sex = '男' WHERE sid = '1' ;
表中数据删除:DELETE FROM student WHERE sid = '2';
查询所有信息:SELECT * FROM student;
外键关联:ALTER TABLE score ADD CONSTRAINT `FK_SCORE` FOREIGN KEY(sid) REFERENCES student(sid);
多表联查:SELECT * FROM student t1 LEFT JOIN score t2 ON t1.`sid`=t2.`sid`;
SELECT * FROM student t1 RIGHT JOIN score t2 ON t1.`sid`=t2.`sid`;
SELECT * FROM student t1, score t2 WHERE t1.`sid`=t2.`sid`;
复制表:CREATE TABLE grade SELECT * FROM score;
复制只有列名的空表:CREATE TABLE grade SELECT * FROM score WHERE 0=1;
更换表明:ALTER TABLE 表明RENAME 新表明;
设置默认值(之前字段内容不会改变):ALTER TABLE student ALTER phone SET DEFAULT‘110’;
八.经典查询语句
1.查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列
select * from student order by sdept,sage desc;
2.查询信息系、美术系和计算机系学生的姓名和性别
select sname,ssex from student where sdept in (‘信息系','美术','计算机系');
Select sname,ssex from student where sdept='信息系' or sdept='美术' or sdept='计算机系';
3.查询所有姓刘学生的姓名、学号和性别
select sname,sno,ssex from student where sname like '刘%';
4.某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号
select studentid,courseid from sc where grade is null;
5.查询年龄不在20~23岁之间的学生姓名、系别和年龄
select sname,sdept,sage from student where sage not between 20 and 23;
6.查询'信息系'、'美术系'和'计算机系'学生的姓名和性别
select sname,ssex from student where sdept in ('信息系','美术系','计算机系');
7.查询年龄最大的前3个学生的姓名和年龄,或第4、5个学生
select sname,sage from student order by sage desc limit 3;或(limit 3,2)
8.查询选修了课程的学生学号(去掉重复的记录)
Select distinct studentid from sc;
9.查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名
select sname, year(year(now())-sage), islower(sdept) from student;
10.查询入职最早的人的所有信息
Select * from staff where hiredate =(select min(hiredate)from staff);
11.查询学生的平均成绩在70分以上的
select studentid,avg(grade) as ‘平均成绩’ from sc group by studentid having avg(grade)>70;
12.查询用户名为初中生,密码为111111的用户的所有权限编码及权限
Select t1.id ,t1.menuname from menuinfo t1,userinfo t2,userrole t3,rolemenu t4 where t2.id=t3.userid and t3.roleid=t4.roleid and t4.menuid=t1.id and t2.username=’初中生’and t2.userpassword=’111111’;