注:数据库是不区分大小写的,本文是边调试,边写文章的,所以大小写问题请不用纠结。
一。ubuntu16.04安装mysql数据库
sudo apt-get install mysql-server
sudo apt-get install mysql-client
sudo apt-get install libmysqlclient-dev
所有询问yes/no 都选择y,要你输入密码你可以输入一个原始密码。(需要记住你的原始密码)
二。unbutu16.04中开始使用mysql
1.登录:mysql -u root -p
输入密码:******
2.显示所有数据库:show databases;
3.选择一个数据库:use mysql;
4.查看本数据库中host,用户名,authentication_string:select host,user,authentication_string from mysql.user;
5.数据库中新加一个用户:CREATE USER "dawn02"@"host" IDENTIFIED BY '123456';
6.给用户赋值权限:GRANT SELECT,INSERT,UPDATE,DELETE,DROP,CREATE ON mysql.* TO 'dawn02'@'localhost' IDENTIFIED BY '123456';
7.显示该数据库中所有数据表:SHOW TABLES;
三。在mysql表中进行基本操作:
1.显示user表中的columns:show columns from user;
2.显示数据表的详细索引信息,包括PRIMARY KEY(主键):show index from user;
四。创建数据库
1.创建数据库: CREATE DATABASE kaishen;
2.删除数据库:drop database kaishen;
五。创建数据表:
1.创建学生表:
create table TbStudent
(
stuid integer not null,
stuname varchar(20) not null,
stusex bit default 1,
stubirth datetime not null,
stutel char(11),
stuaddr varchar(255),
stuphoto longblob,
primary key (stuid)
);
2.删除表中某一列:alter table TbStudent drop column stutel;
3.查看表结构:desc TbStudent;
4.新建一列:alter table TbStudent add column stutel varchar(11);
5.新建一列在某列后面:alter table TbStudent add column stutel varchar(11) after stubirth;
6.新建一列为第一列:alter table TbStudent add column stutel varchar(11) first;
六。表中插入数据:
1.插入一条数据:INSERT INTO TbStudent VALUES (1005, '丘处机', 1, '1983-5-5', '北京市海淀区宝盛北里西区28号', null);
2.插入多条数据:INSERT INTO TbStudent VALUES
(1001,'柯镇恶',1,'1966-8-12','舟山',null),
(1002,'令狐冲',1,'1972-4-5','陕西',null);
3.删除一条数据:DELETE FROM TbStudent WHERE stuname = '柯镇恶';
4.修改一条数据:UPDATE TbStudent SET stuaddr = '华山' WHERE stuname = '令狐冲';
5.查询一条数据:SELECT * FROM TbStudent WHERE stuid =1002;
6.模糊查询:SELECT * FROM TbStudent WHERE stuname LIKE "令%";
7.模糊查询2:SELECT * FROM TbStudent WHERE stuname LIKE "%令%";
8.查询一条:SELECT * FROM TbStudent LIMIT 1;
9.查询顺序:SELECT * FROM TbStudent ORDER BY stubirth LIMIT 2;
10.倒序查询:SELECT * FROM TbStudent ORDER BY stubirth DESC LIMIT 2;
七。学生表,老师表,课程表,成绩表等查询
学生表:student
老师表:teacher
课程表:cource
成绩表:score
1.查询姓“张”的老师的个数:SELECT COUNT(teaid) FROM teacher WHERE teaname like "张%";
2.查询课程编号为“2”的总成绩:SELECT SUM(stusource) FROM score WHERE courseid = 2;