Database-Initial MySQL-Introduction/Installation

First, Introduction:
MySQL database management software:
Socket: Server, client, client access to service-side data
1. Support concurrency, operation is to share data
2. Handling locks, data security, performance
3. Use other people's software, according to the norms of others, to organize their own grammatical rules

What this chapter learns:
1.mysql Basic Management
2.sql sentences to learn

Ii. Overview:
1. Database server: The computer running the database management software
2. Database management software: Mysql,oracle,db2,slqserver MySQL was acquired by Oracle
3. Libraries: Folders
4. Table: Documents
5, Record: A series of typical characteristics of things: Egon,male,18,oldgirl
6. Data: Symbols describing the characteristics of things

Three, MySQL introduction:
MySQL is a relational database management system developed by the Swedish MySQL AB company, currently owned by the Oracle company.
What is MySQL
MySQL is a software based on the C/s architecture of socket writing
Client software
MySQL comes with: such as MySQL command, mysqldump command, etc.
Python modules: such as Pymysql

IV. Classification of database management software:
There are two main types of:
Relationship type: As Sqllite,db2,oracle,access,sql server,mysql, NOTE: SQL statements are common
Non-relational: Mongodb,redis,memcache

Can be simply understood as:
Relational database requires a table structure, table structure = field + data type + constraints
The non-relational database is key-value stored, without a table structure

v. Download and install:
1.windows:
Windows-mysql Download and install
2.Linux:

http://www.cnblogs.com/linhaifeng/articles/7126847. html Centos7:sudo su Yum-Y Install mariadb-Server mariadb Centos6:yum-Y Install mysql-server MySQL boot: systemctl start mariadb systemctl status mariadb MySQL ...#it worked.CentOS: Set Password: ... mysqladmin-uroot-p Password"123"forgot password Skip authorization systemctl stop mariadb start: Mysqld_safe--skip-grant-tables sudo su mysql-uroot-p Log in directly to update mysql.user set password=password ("123") Where user="Root"  andhost="localhost";                Flush privileges; Exit restart MySQL service to kill the previous MySQL Pkill-9mysql restart: systemctl start mariadb#Normal sign- inMysql-uroot-p123---------------------------------Unified character: MySQL \s exit vim/etc/my.cnf INI#copy and paste the contents as above. ini to my.cnfwq! Systemctl restart mariadb mysql \s modified ... utf8---------------------------------Ubuntu installation Mysql:sudo apt-get Install Mysql-server mysql-client sudo netstat-tap |grep mysql mysql-uroot-P123linux

Linux

    3.mac:

***. DMG installation settings environment Variables/usr/local/MySQL vim/etc/Profile PATH=/usr/local/mysql/bin:/usr/local/mongodb/Bin: $PATH export path:wq! . SOURCE/etc/Profile immediate effect MySQL>>>: ...        A successful MySQL select User ();        Exit Mysqladmin-uroot-p Password"123"MySQL-uroot-p123 mysqladmin-uroot-p123 Password"456"MySQL-uroot-p456 turn off the already opened; Skip authorization start: Mysqld_safe--skip-grant--tables sudo su mysql-uroot-p#you can log in without a passwordChange Password: Update mysql.user set Password=password ("") Where user="Root"  andhost="localhost";            Flush privileges; Exit turn off and then normal start PS aux|grep MySQL Kill-9 6334Kill-9 6249Kill-9 6432Normal startup tool MySQL-uroot-P started normally, no password was set up just now.---------------------------------Unified character: MySQL \s exit vim/etc/MY.CNF the contents of a. ini before copying and pasting esc:wq! Restart Service MySQL \s this modifies UTF-8mac

mac

Vi. MySQL Startup and view:
View under 1.linux Platform
[Email protected] ~]# systemctl start mariadb #启动
[Email protected] ~]# Systemctl enable MARIADB #设置开机自启动
Created symlink From/etc/systemd/system/multi-user.target.wants/mariadb.service to/usr/lib/systemd/system/ Mariadb.service.
[[Email protected] ~]# PS aux |grep mysqld |grep-v grep #查看进程, mysqld_safe for startup MySQL script file, internal invoke mysqld command
MySQL 3329 0.0 0.0 113252 1592? Ss 16:19 0:00/bin/sh/usr/bin/mysqld_safe--basedir=/usr
MySQL 3488 0.0 2.3 839276 90380? Sl 16:19 0:00/usr/libexec/mysqld--basedir=/usr--datadir=/var/lib/mysql--plugin-dir=/usr/lib64/mysql/plugin--log- Error=/var/log/mariadb/mariadb.log--pid-file=/var/run/mariadb/mariadb.pid--socket=/var/lib/mysql/mysql.sock
[Email protected] ~]# Netstat-an |grep 3306 #查看端口
TCP 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
[[email protected] ~]# ll-d/var/lib/mysql #权限不对, boot unsuccessful, note user and group
Drwxr-xr-x 5 mysql mysql 4096 Jul 16:28/var/lib/mysql

You must reset your password using the ALTER USER statement before executing this statement.
After the installation of MySQL, after landing, regardless of running any command, always prompt this
Mac MySQL error must reset your password using ALTER USER statement before executing this statement.
Workaround:
Step 1:set PASSWORD = PASSWORD (' Your new PASSWORD ');
Step 2:alter USER ' root ' @ ' localhost ' PASSWORD EXPIRE never;
Step 3:flush privileges;

2.windows Platform to service view

Seven, login settings password:
Initial state, administrator root, password is empty, default to only allow log on from native localhost
Set Password
[[email protected] ~]# mysqladmin-uroot password "123" Set the initial password because the original password is empty, so-P can not
[[email protected] ~]# mysqladmin-uroot-p "123" password "456" to modify the MySQL password, because there is already a password, so you must enter the original password to set a new password

Command format:
[Email protected] ~]# mysql-h172.31.0.2-uroot-p456
[Email protected] ~]# mysql-uroot-p
[[email protected] ~]# MySQL log in to the root user, password is empty

Eight, crack password:
1.linux platform, two ways to crack the password
Method One: Delete the authorization library MySQL, re-initialize
[Email protected] ~]# rm-rf/var/lib/mysql/mysql #所有授权信息全部丢失!!!
[Email protected] ~]# systemctl restart MARIADB
[[email protected] ~]# MySQL
Method Two: Skip the authorization library when you start
[Email protected] ~]# vim/etc/my.cnf #mysql主配置文件
[Mysqld]
Skip-grant-table
[Email protected] ~]# systemctl restart MARIADB
[[email protected] ~]# MySQL
MariaDB [(none)]> update mysql.user set Password=password ("123") where user= "root" and host= "localhost";
MariaDB [(None)]> flush privileges;
MariaDB [(none)]> \q
[[email protected] ~]# #打开/etc/my.cnf remove skip-grant-table and restart
[Email protected] ~]# systemctl restart MARIADB
[Email protected] ~]# mysql-u root-p123 #以新密码登录

2.windows platform, 5.7 version of MySQL, two ways to crack passwords:
Way One
#1 turn off MySQL
#2 executed in cmd: mysqld--skip-grant-tables
#3 executed in cmd: MySQL
#4 execute the following sql:
Update Mysql.user set Authentication_string=password (") Where user = ' root ';
Flush privileges;
#5 Tskill mysqld #或taskkill-F/pid 7832
#6 Restart MySQL
Way Two
#1. Turn off MySQL and you can kill it with Tskill mysqld
#2. Under Unzip directory, create a new MySQL configuration file My.ini
#3. My.ini content, specifying
[Mysqld]
Skip-grant-tables
#4. Start mysqld
#5. Enter MySQL login directly in CMD and then operate
Update Mysql.user set Authentication_string=password (") where user= ' root and host= ' localhost ';
Flush privileges;
#6. Note The skip-grant-tables in My.ini, and then start MYQSLD, and then you can log in with the new password

Nine, unified character encoding:
Emphasis: Comments in the configuration file can have Chinese, but the configuration item cannot appear in Chinese
#在mysql的解压目录下, create a new My.ini, and then configure
#1. The following configuration takes effect when the mysqld command is executed, that is, when the MySQL service starts
[Mysqld]
; skip-grant-tables
port=3306
Character_set_server=utf8
Default-storage-engine=innodb
Innodb_file_per_table=1

#解压的目录
Basedir=e:\mysql-5.7.19-winx64
#data目录
Datadir=e:\my_data #在mysqld--initialize, the initial data is stored in the directory specified here, and after initialization, when MySQL is started, it will go to this directory for data

#2. For global configuration of client commands, the following configuration takes effect when the MySQL client command executes
[Client]
port=3306
Default-character-set=utf8
User=root
Password=123

#3. Only for MySQL this client configuration, 2 is the global configuration, and here is only for MySQL this command local configuration
[MySQL]
;p ort=3306
;d Efault-character-set=utf8
User=egon
password=4573

#！！！ If there is no [MySQL], the user's configuration when executing the MySQL command is based on [client]
Unified character encoding
#1. Modifying a configuration file
[Mysqld]
Default-character-set=utf8
[Client]
Default-character-set=utf8
[MySQL]
Default-character-set=utf8

#mysql5. More than 5: Modification method changed
[Mysqld]
Character-set-server=utf8
Collation-server=utf8_general_ci
[Client]
Default-character-set=utf8
[MySQL]
Default-character-set=utf8

#2. Restart Service
#3. To view the results of the modification:
\s
Show variables like '%char% '

10, the first knowledge of SQL statements:
SQL (structured query Language, structured queries language)
The SQL language is primarily used to access data, query data, update data, and manage relational database systems, which are developed by IBM.
There are 3 types of SQL languages:
1. DDL statement Database Definition Language: Database, table, view, index, stored procedure, such as Create DROP ALTER
2. DML statement Database manipulation language: Inserting data insert, deleting data Delete, updating data update, querying data Select
3. DCL Statement Database Control Language: for example, to control user access rights grant, REVOKE
folder (library), file (table), File contents (record);
1. Operation folder (library):
Add: Create Database db1 charset UTF8
Check: Show CREATE Database db1;
show databases;
Change: Alter DATABASE DB1 CharSet GBK;
Delete: Drop database db1;
2. Operation file (table):
To switch folders:
Use DB1;
Select Database ();
Add: CREATE TABLE T1 (id int,name char);
Check: show tables;
Desc T1;
Show CREATE table T1;
Show CREATE TABLE t1\g;
Change: ALTER TABLE t1 modify name char (6);
ALTER TABLE T1 change name name1 char (7);
Delete: drop table T1;
3. Operation file contents (record):
Add: INSERT INTO T1 (id,name) VALUES (1, ' Alice1 '), (2, ' Alice2 '), (3, ' alice3 ');
INSERT into T1 values (1, ' Egon1 '), (2, ' Egon2 '), (3, ' Egon3 ');
Check: Select Id,name from Db1.t1;
Select ID from Db1.t1;
SELECT * from Db1.t1; # Not recommended for use * low efficiency
Change: Update db1.t1 set name= ' Alex ';
Update T1 set name= ' Egon ' where id=2;
Delete: delete from T1;
Delete from T1 where id=2;

Database-Initial MySQL-Introduction/Installation