MySQL Getting started in combat

The installation and connection tools for MySQL are explained earlier. This article is the beginning of MySQL in combat, mainly on the start and stop MySQL service, database management, table management and data deletion and modification;

Source code 1. Start of MySQL service, stop

As long as you are installed, the MySQL service starts automatically every time you start your computer. Of course MySQL also has his own start-stop command;

Start and stop MySQL at 1.1windows:

Run at the Windows command prompt:
Startup: Net start MySQL
Stop: net stop MySQL
Restart: NET Restart MySQL

Start and stop MySQL at 1.2Linux:

1. Start:

Start with service: service MySQL start
Start with mysqld script:/etc/inint.d/mysql start
Start:safe_mysql& with Safe_mysqld

1. Stop it:

Using service startup: Service MySQL Stop

Start with mysqld script:/etc/inint.d/mysql stop
Mysqladmin shutdown

1. Restart:

Using service startup: Service MySQL restart
Start with mysqld script:/etc/inint.d/mysql restart

2. Database management: 2.1 Login to MySQL:

When the MySQL service is already running, we can log in to the MySQL database using the MySQL client tool:

The command is as follows:

mysql -h 主机名 -u 用户名 -p-h : 该命令用于指定客户端所要登录的MySQL主机名, 登录当前机器该参数可以省略;-u : 所要登录的用户名;-p : 告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项。如登陆本地的：[email protected]:~$ mysql -u root -pEnter password: #键入你安装时用的密码Welcome to the MySQL monitor.  Commands end with ; or \g.

After logging in, you can use MySQL's SQL command.

SQL语句的分类：DDL： 数据定义语言:create / drop / alter DML：数据操作语句:insert / delete /update / truncate DQL： 数据查询语言：select / show 

Note : The MySQL statement is a semicolon (;) as the end of the statement, if you do not add a semicolon at the end of the statement, the command prompt prompts you to continue typing (there are individual exceptions, but the semicolon is definitely not wrong);

2.2 Querying all databases:

Command: show databases; notice the semicolon ends

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema |-- mysql元数据，基础数据| day01              |--这是我自己建立的数据库| mysql              |--mysql配置数据库，其中包含用户信息。（用户名和密码，权限管理）| performance_schema |--mysql数据库软件的运行数据，日志信息，性能数据| test               |     --测试数据库。空的+--------------------+5 rows in set (0.00 sec)mysql>

2.3 Create a database:

To use a database you must first create your own database:

1. Syntax format:

       create database 数据库名 [其他选项];

Other options are optional;

1. Practice:

Create a database day01 and specify a default character level of utf-8; Note that you need to write UTF8 in sql

create database day01 default character set utf8;##创建成功返回结果如下：Query OK, 1 row affected (0.00 sec)

You can view the default character set for the database you just created:

show create database day01;##输出：‘day01‘, ‘CREATE DATABASE `day01` /*!40100 DEFAULT CHARACTER SET utf8 */‘

2.4 Deleting a database:

This statement is not to be used in a disorderly way!!!!!!!

1. Syntax format:

      drop database  数据库名;

2. Practice:

Delete the database you just created;

drop0 rows affected (0.01 sec)

2.5 Modifying the database:

1. Syntax format:

      alter database 数据库名 default character set gbk;

2. Practice:

Modify the default character of the database you just created;

mysql>  defaultset1 row affected (0.00 sec)

2.6 Using the database

To operate on a database, you must first select the database, or you will be prompted with an error:
ERROR 1046 (3d000): No Database selected
Two options for using the database:
1. Specify when logging in to the database, command: mysql-d selected database name-h hostname-u user name-P
For example, select the database you just created when you log in: mysql-d day01-u root-p;
2. Specify with USE statement after login, command: using database name;
The USE statement can be executed without a semicolon, and a samp_db is used to select the database you just created, and you will be prompted after successful selection: Database changed

3. Table Management:

With the database you can manipulate the database, such as creating and modifying tables, adding data, and so on, and the following demonstrations are day01.

          use day01;--使用day01

3.1 View All tables:

1. Syntax format:

       show tables;

2. The demo is as follows: Because we haven't created a table, it's an empty table.

mysql> show tables;set (0.00sec)

3.2 Creating a Table

With a database you must have a table to store the data you need;

1. The types of characters the table can store are as follows:

1.数字类型整数: tinyint、smallint、mediumint、intfloat、double、real、decimal2.日期和时间: date、time、datetime、timestamp、year3.char、varchar文本: tinytext、text、mediumtext、longtext4.二进制(可用来存储图片、音乐等): tinyblob、blob、mediumblob、longblob

1. Syntax format

##简单格式create table 表名称(列声明);**注意**列声明由逗号隔开最后一个列声明没有逗号。##带条件创建，判断是否存在表：不存在则创建：create table if not exists 表名称(列声明);

1. Practice:

To create the students table, for example, the table will hold the number (SID), name (sname), score (Smark), age (Sage), and the content:

-- create tablecreate table student(            sid INT,-- 学号整型        sname varchar(20),-- 名字字符型        smark int,-- 分数整型         sage int-- 年龄);##输出：Query OK, 0 rows affected (0.01 sec)

1. To view the table structure you just created:

desc student;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| sid   | int(11)     | YES  |     | NULL    |       || sname | varchar(20) | YES  |     | NULL    |       || smark   | int(11)     | YES  |     | NULL    |       || sage  | int(11)     | YES  |     | NULL    |       |

3.3 Deleting a table

This statement is not to be used in a disorderly way!!!!!!!

1. Syntax format:

      drop table  表名;

2. Practice:

Delete the table just created;

drop0 rows affected (0.01 sec)

3.4 Modify the table:

Modify the statement with the ALTER command;

1. To add a field:

-- 添加字段sgender varchar(2);性别alter table student add column sgender varchar(2);

1. To delete a field:

-- 删除字段，先添加sbb字段，然后再删除alter table student add column sbb varchar(2);alter table student drop column sbb;

1. To modify a field type:

-- 修改字段数据类型。修改字段 smark int到 smark varchar(2);alter table student modify column smark varchar(2);

1. Modify Field Name:

-- 修改字段名称，将smark修改为markalter table student change column smark mark int;

1. Modify Table Name:

-- 修改表名称，将student表名修改为teacheralter table student rename to teacher;

4. Adding and deleting data

There is no doubt that the query statement is the most SQL used statements, but no additions and deletions, the existence of the query is meaningless. This part only explains: The use of insert,update,delete statement;

4.1 Inserting data: Insert

The INSERT statement is also a common statement used to insert a row of data into a table.

1. Syntax format:

##插入：全部字段必须给值INSERT INTO 表名 VALUES(值)；-- 此去必须给全值##插入部分字段：INSERT INTO 表名(字段1,字段2) VALUES(字段1的值,字段2的值)

1. Practice:

To insert data into student:

-- 1.增加数据INSERT INTO student VALUES(001,‘peace‘,1,22,‘男‘);-- 注意不能少或多字段值-- 插入部分字段INSERT INTO student(sid,sname) VALUES(002,‘rong‘);

4.2 Deleting data: Delete

Delete statements should be used with caution!!!

1. Syntax format:
   Delete from table where condition (optional)

2. The demo is as follows:

-- 删除所有数据(建议少用)---- delete from student;-- 带条件的删除--delete from student where sid=003;

1. Delete and TRUNCATE TABLE comparison

-- delete from: 可以全表删除      1)可以带条件删除  2）只能删除表的数据，不能删除表的约束     3)使用delete from删除的数据可以回滚（事务）-- truncate table: 可以全表删除   1）不能带条件删除 2）即可以删除表的数据，也可以删除表的约束 3）使用truncate table删除的数据不能回滚-- TRUNCATE TABLE student;

4.3 Modifying data: Update

This can be used to modify the contents of the specified line, such as QQ modified name, etc.

1. Syntax format:
   Update table name SET field name = modified value where condition (optional);

2. The demo is as follows:

-- 修改所有数据，建议少用--UPDATE student set mark=10;-- 带条件的修改（建议使用）--update student set mark=2 where sid=2;-- 修改多个字段，SET 字段名=值,字段名=值,....--update student set mark=1,sage=23 where sid=1; 

Okay, here's what this chapter is about.

From a little shark wpeace (rlovep.com)

MySQL Getting started in combat