MySQL学习笔记——20170808

数据库DataBase

• Oracle
• DB2
• SQLServer
• MySQL

---

数据模型

• 层次模型
• 网状模型
• 关系模型

---

关系型数据库

1. row + col -> table
2. table -> database

---

MySQL安装

1. yum -y install mysql

2. yum -y install mysql-server

3. yum -y install mysql-devel

4. yum -y install mysql-connector

5. /etc/init.d/mysqld start 或者 service mysqld start -> 开启服务

6. mysqladmin -u root password ‘123456’ -> 初始化root密码为123456

7. mysqladmin -u root -p123456 passwrod ‘65431’ -> 修改密码

8. mysql -u root -p -> 登录mysql

9. chkconfig mysqld on 或者 vim /etc/rc.d/rc.local -> 设置开机启动shell命令

10. 开启3306端口

    1. vim /etc/sysconfig/iptables
    2. -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
    3. service iptables restart

11. 设置mysql字符集为utf-8

    1. vim /etc/my.cnf
    2. default-character-set=utf8
       character_set_server=utf8
       [client]
       default-character-set=utf8

    若想要其他主机能够访问本机mysql，则需：
    1.开启3306端口
    2.在mysql中设置允许远程连接
    3.若要root远程登录，需要在mysql的mysql(库名)的user(表名)中将root的Host值改为%

---

数据库基本操作

-- 练习1
CREATE DATABASE shcoolDB;

USE shcoolDB;

CREATE TABLE t_student (
	sNO INT,
	sname VARCHAR (20),
	age INT,
	gender VARCHAR (10)
);

INSERT INTO t_student (sNO, sname, age, gender)
VALUES
	(1, '张三', 23, '男'),
	(2, '李四', 22, '女'),
	(1, '王五', 22, '男'),
	(1, '赵六', 21, '男'),
	(1, '李欣', 23, '女');

CREATE TABLE t_course (
	cNO VARCHAR (10),
	cname VARCHAR (20),
	teacher VARCHAR (20)
);

INSERT INTO t_course (cNO, cname, teacher)
VALUES
	('K1', 'linux', '刘大华'),
	('K5', 'mysql', '张小友'),
	('K8', 'python', '李明');

CREATE TABLE t_score (
	sNO INT,
	cNO VARCHAR (10),
	sscore INT
);

INSERT INTO t_score (sNO, cNO, sscore)
VALUES
	(1, 'K1', 83),
	(2, 'K1', 85),
	(5, 'K1', 92),
	(2, 'K5', 90),
	(5, 'K5', 84),
	(5, 'K8', 80),
	(6, 'K1', 100),
	(7, 'K5', 80),
	(8, 'K1', 100);

-- 练习2
CREATE DATABASE hallDB;

USE hallDB;

CREATE TABLE t_user_info (
	uNO INT,
	NAME VARCHAR (20),
	gender VARCHAR (10),
	continfo VARCHAR (30)
);

CREATE TABLE t_phone (
	pNO INT,
	brand VARCHAR (20),
	model VARCHAR (20)
);

-- 练习3
CREATE DATABASE schoolDB2;

CREATE TABLE t_student (
	stu_id VARCHAR (6) PRIMARY KEY NOT NULL,
	stu_name VARCHAR (10) NOT NULL,
	stu_gender INT NOT NULL
);

CREATE TABLE t_course (
	class_id VARCHAR (6) PRIMARY KEY NOT NULL,
	class_name VARCHAR (20) NOT NULL,
	author VARCHAR (6) NOT NULL
);

CREATE TABLE t_score (
	stu_id VARCHAR (6) NOT NULL,
	class_id VARCHAR (6) NOT NULL,
	stu_score FLOAT (3)
);

ALTER TABLE t_score ADD FOREIGN KEY (stu_id) REFERENCES t_student (stu_id);

ALTER TABLE t_score ADD FOREIGN KEY (class_id) REFERENCES t_course (class_id);

ALTER TABLE t_student MODIFY COLUMN stu_gender bit (1);

ALTER TABLE t_student MODIFY COLUMN stu_gender bit (1) DEFAULT 0 COMMENT '性别，1为男0为女';

SHOW FULL COLUMNS FROM t_student;