bitsCN.com
MySql数据库入门讲解 一、数据库相关的概念Dbms: database manager system 数据库管理系统,即数据库的服务程序。 通常说的安装数据库就是安装dbms。 只有安装数据库的数据库系统才能真正的提供服务,所以安装数据库程序时,一般在安装管理服务程序时,会默认安装一个数据库实例。如SQL Server默认安装了相关的plus、northwind。而Mysql 默认安装test、mysql、information等,Oracle会安装时提示建议安装一个orcl数据库(当然名称也可以改)。以后在开发应用时有需求要新建数据库时,可以通过相关的客户端软件,连接服务程序创建相关的数据库实例。 具体关系如图: 二、MySQL数据库的安装下载地址:http://www.mysql.com/downloads/MySql的安装其实很简单,需要注意的是在安装进行到选择默认字符编码集时,请选择utf8。三、SQL简介强烈建议在学习SQL时,前期使用命令来学习不要用可视化的工具(包括创建修改表数据库等操作),这里用MySQL作为学习数据库。
Structured Query Language:SQL的缩写,是结构化查询语言。1. 进入数据库 mysql [-h 主机名或ip] -u[ ]用户名 -p[密码]在window命令行中,输入mysql -h 192.168.1.106 -u chen -p 回车换行后,会有提示要求输入密码即可进入。注意-h 若是本机地址时,也可以不用输入-h的。 2. 查看dbms管理着几个数据库,目前有几个数据库 show databases;四、SQL 语句的分类1. DDL:data definition language 数据库定义语言适用对象:数据库、表结构。 关键字:CREATE ALTER DROP
2. DML:data manipulate language 数据操作语言 适用对象:表中的记录。 关键字:INSERT UPDATE DELETE3. DQL:data query language 数据查询语言 适用对象:表记录等等。 关键字:SELECT DDL举例如下:0> 创建数据库:create database adtest;1> 创建表: (先要用use test; 数据库哦)create table if not exists employee(Id int primary key, Name varchar(100) unique not null, Gender varchar(5), Birthday date, salary float)2> 查看表结构: desc employee;3> 查看有哪些表:show tables;4> 增加一列图片列 alter table employee add image blob;5> 修改name列长度 alter table employ modify name varchar(40);6> 删除图片列 alter table employee drop image;7> 修改表名为user rename table employee to user;8> 修改表字符编码utf8 alter table employee character set utf8;9> 列名name改成username alter table employee change name username varchar(40);10> 删除表employee drop table employee; DML举例如下:1> 插入一条员工信息Insert into employ(id,name,gender,birthday,salary) values(‘1’,’xiaochen’,’2012-10-10’,12203.6);2> 客户端查看数据库的各种编码Show variables like ‘%char%’;有以下:character_set_client:通知服务器客户端使用的码表character_set_connection:链接数据库所使用的码表character_set_database:e是数据库服务器中某个库使用的字符集设定,如果建库时没有指明,将使用服务器安装时指定的字符集设置。 character_set_results:是数据库给客户端返回时使用的字符集设定,如果没有指明,使用服务器默认的字符集。 character_set_server:是服务器安装时指定的默认字符集设定。 character_set_system:是数据库系统使用的字符集设定注意:若在控制台中抛入中文,则必须输入set character_set_client=gbk;为了显示中文要输入set character_set_results=gbk;(控制台默认用本地gbk编码的)。这种set方式只对当前的命令控制台有效。 3> 更新操作 update tablename set columnname = value where ...或 Update t1(别名) set t1.columnname = value from tablename t1 where ...4. 删除操作 delete from tablename where ....DQL举例:1> 查询不同的英文成绩 同一英文成绩只显示一行Select distinct english from student2> 查询英文成绩大于90分,学号是1,3,8,9号学生的成绩Select id, name,english,chinese,math from student where english>90 and id in (1,3,8,9);3>对数学成绩排序后输出。 Asc 升序、Desc 降序SELECT name,math FROM student ORDER BY math ASC;
4> 查询购买了几类商品,并且每类总价大于100的商品 group by ... Having...SELECT id,product,SUM(price) FROM orders GROUP BY product HAVING SUM(price)>100;特别注意:在SQL Server与Oracle使用group by 中,select中的字符必须是group by中出现的或使用了聚合函数如sum(ItmNum)或Agv、Min等。而MySql没有这种要求,所以上面的select id也能出来。 4. 常用mysql函数Count(*) 计数个数 sum(math) 数学成绩汇总 avg(chinese)中文平均成绩Max(chinese)中文最高成绩 Min(chinese)中文最低成绩5. DCL: data control language 数据控制语言适用对象:相关的dba操作 如grant等操作
五、数据库约束1. 主外键约束 主键约束primary key 外键 foreign key业务主键 与业务相关 、 逻辑主键 与业务无关。 推荐有逻辑主键。2. 主键自动自增 MySql 主键要求int且用auto_increment自增。3. 定义唯一约束 unique4. 定义非空约束 not null 六、表与表之间的关系1. 一对多CREATE TABLE department( id int PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) UNIQUE NOT NULL);CREATE TABLE employee( id int PRIMARY KEY AUTO_INCREMENT,name VARCHAR(100) NOT NULL, salary FLOAT(8,2),dept_id int, CONSTRAINT dept_id_fk FOREIGN KEY(dept_id) REFERENCES department(id)); 2. 多对多关系CREATE TABLE teacher( id int PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, salary FLOAT(8,2));CREATE TABLE students( id int PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, grade VARCHAR(10));CREATE TABLE teacher_student( t_id int, s_id int, PRIMARY KEY(t_id,s_id), CONSTRAINT t_id_fk FOREIGN KEY(t_id) REFERENCES teacher(id), CONSTRAINT s_id_fk FOREIGN KEY(s_id) REFERENCES students(id)); 3. 一对一关系CREATE TABLE book( id int PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, price FLOAT(8,2));CREATE TABLE cartitem( id int PRIMARY KEY , num int, price FLOAT(8,2), CONSTRAINT b_id_fk FOREIGN KEY(id) REFERENCES book(id)); bitsCN.com

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

SublimeText3 English version
Recommended: Win version, supports code prompts!

SublimeText3 Linux new version
SublimeText3 Linux latest version

Notepad++7.3.1
Easy-to-use and free code editor
