mysql 笔记(一)
mysql 笔记 预留
mysql> use mysql;
mysql> grant all privileges on *.* to root@'%' identified by "password";
mysql> flush privileges;
0. win下安装mysql57
1.下载地址:https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.22-winx64.zip
2.解压设置配置文件
[mysqld] #basedir代表自己MySQL的安装根目录 basedir = D:\\Program Files\\mysql-5.7.22-winx64 #datadir代表自己MySQL的数据库保存的目录,如果没有在MySQL安装的根目录下新建一个data文件夹 datadir = D:\\Program Files\\mysql-5.7.22-winx64\\data #port代表端口号 port = 3306
3. 设置path
4. 安装服务 cmd管理员: mysqld --install
5.生成初始化文件data:mysqld --initialize
5.开启服务:net start mysql
6.寻找root用户初始化密码:手动在mysql目录下搜索*.err 关键字passwd
7.修改初始密码:
1.mysqladmin -u root password oldpass "newpass"
2.SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');
1、linux 下安装
#yum install mysql #yum install mysql-server #yum install mysql-devel
安装mysql-server失败
1、yum install mariadb-server mariadb
mariadb数据库的相关命令是:
systemctl start mariadb #启动MariaDB
systemctl stop mariadb #停止MariaDB
systemctl restart mariadb #重启MariaDB
systemctl enable mariadb #设置开机启动
所以先启动数据库
[root@yl-web yl]# systemctl start mariadb
然后就可以正常使用mysql了
2、
连接mysql 等一些操作
set password for 'root'@'localhost' =password('****');
show grants;
show databases;
use database;
show tables;
desc table;
SELECT DISTINCT User FROM mysql.user;
创建用户
create user name@localhost identified by '******';
授权
grant select, insert, delete, alter,update on *.* to name@localhost;
grant all privileges on *.* to name@localhost with grant option;
参照完整性
alter table `workers_sec`.`workers` add constraint FK_1 foreign key(Wsecno) REFERENCES `workers_sec`.`sec`(Sno);
增删改查 操作示例
创建一个表
CREATE TABLE `stu_course`.`student` (
`Sno` INTEGER UNSIGNED NOT NULL,
`Sname` CHAR(20) NOT NULL,
`Ssex` CHAR(2) NOT NULL,
`Sage` SMALLINT UNSIGNED NOT NULL,
`Sdept` CHAR(20) NOT NULL,
PRIMARY KEY (`Sno`)
)
ENGINE = InnoDB;
- 查询与项目组长“同姓且性别相同”的学生数据
select * from student where Sname like'杨%' and Ssex = '男';
2.计算选修KC01课程的学生平均成绩
select * from sc where Cno = 'KC01';
select avg(grade) from sc where Cno = 'KC01';
3.查询选修KC02课程且成绩在70分以上的所有学生的学号与姓名
select student.sname,sc.* from student,sc where student.sno=sc.sno and sc.cno = 'kc02' and sc.grade >= 70;
2 建立微电子“学生”视图,并将该视图中姓名为“张*”的学生的年龄增加1岁
create view Wtec_stu
as
select * from stu_course.student Where Sdept = "微电子";