MYSQL基础知识与常用方法

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<将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’;