mysql学习笔记(1)

最新推荐文章于 2023-11-04 12:26:54 发布
最新推荐文章于 2023-11-04 12:26:54 发布
阅读量715 收藏
点赞数
分类专栏： mysql MariaDB
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/windvally/article/details/50699631
版权
这篇博客记录了使用XAMPP套件安装MySQL的过程，并展示了如何创建数据库、授权用户、创建表、插入数据、修改表结构、进行事务处理、备份与恢复数据库以及查询分析。博主还通过示例介绍了基本的SQL操作，如`CREATE DATABASE`、`GRANT`、`ALTER TABLE`和`SELECT`等。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

我使用的是XAMPP套件。

WangWeis-MacBook-Air:bin wangwei$ pwd

/Applications/XAMPP/bin

WangWeis-MacBook-Air:bin wangwei$ ./mysql -u root

也可以

WangWeis-MacBook-Air:bin wangwei$ ./mysql -u root -p

Enter password: 

Welcome to the MariaDB monitor.  Commands end with ; or \g.



Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 30

Server version: 10.1.9-MariaDB Source distribution


Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


MariaDB [(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| phpmyadmin         |

| test               |

+--------------------+

5 rows in set (0.00 sec)


MariaDB [(none)]> 

MariaDB [(none)]> create database publications;

Query OK, 1 row affected (0.00 sec)


MariaDB [(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| phpmyadmin         |

| publications       |

| test               |

+--------------------+

6 rows in set (0.00 sec)


MariaDB [(none)]>

MariaDB [(none)]> use publications;

Database changed

MariaDB [publications]> 

MariaDB [publications]> grant all on publications.* to 'user1'@'localhost' identified by 'password1';

Query OK, 0 rows affected (0.00 sec)


MariaDB [publications]> 

MariaDB [publications]> exit

Bye

WangWeis-MacBook-Air:bin wangwei$ ./mysql -u user1 -p

Enter password: 

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 32

Server version: 10.1.9-MariaDB Source distribution


Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


MariaDB [(none)]> 

MariaDB [(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| publications       |

| test               |

+--------------------+

3 rows in set (0.00 sec)


MariaDB [(none)]> use test;

Database changed

MariaDB [test]> use publications;

Database changed

MariaDB [publications]> 

MariaDB [publications]> 

MariaDB [publications]> 

MariaDB [publications]> create table classics( author varchar(128), title varchar(128), type varchar(16), year char(4)) engine MyISAM;

Query OK, 0 rows affected (0.03 sec)


MariaDB [publications]> 

MariaDB [publications]> describe classics;

+--------+--------------+------+-----+---------+-------+

| Field  | Type         | Null | Key | Default | Extra |

+--------+--------------+------+-----+---------+-------+

| author | varchar(128) | YES  |     | NULL    |       |

| title  | varchar(128) | YES  |     | NULL    |       |

| type   | varchar(16)  | YES  |     | NULL    |       |

| year   | char(4)      | YES  |     | NULL    |       |

+--------+--------------+------+-----+---------+-------+

4 rows in set (0.00 sec)


MariaDB [publications]> 

MariaDB [publications]> alter table classics add id int unsigned not null auto_increment key;

Query OK, 0 rows affected (0.08 sec)               

Records: 0  Duplicates: 0  Warnings: 0


MariaDB [publications]> describe classics;

+--------+------------------+------+-----+---------+----------------+

| Field  | Type             | Null | Key | Default | Extra          |

+--------+------------------+------+-----+---------+----------------+

| author | varchar(128)     | YES  |     | NULL    |                |

| title  | varchar(128)     | YES  |     | NULL    |                |

| type   | varchar(16)      | YES  |     | NULL    |                |

| year   | char(4)          | YES  |     | NULL    |                |

| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |

+--------+------------------+------+-----+---------+----------------+

5 rows in set (0.00 sec)


MariaDB [publications]> 

MariaDB [publications]> INSERT INTO classics(author, title, type, year)

    -> VALUES('Mark Twain','The Adventures of Tom Sawyer','Fiction','1876');

Query OK, 1 row affected (0.00 sec)


MariaDB [publications]> INSERT INTO classics(author, title, type, year)

    -> VALUES('Jane Austen','Pride and Prejudice','Fiction','1811');

Query OK, 1 row affected (0.00 sec)


MariaDB [publications]> INSERT INTO classics(author, title, type, year)

    -> VALUES('Charles Darwin','The Origin of Species','Non-Fiction','1856');

Query OK, 1 row affected (0.00 sec)


MariaDB [publications]> INSERT INTO classics(author, title, type, year)

    -> VALUES('Charles Dickens','The Old Curiosity Shop','Fiction','1841');

Query OK, 1 row affected (0.00 sec)


MariaDB [publications]> INSERT INTO classics(author, title, type, year)

    -> VALUES('William Shakespeare','Romeo and Juliet','Play','1594');

Query OK, 1 row affected (0.00 sec)


MariaDB [publications]> select * from classics;

+---------------------+------------------------------+-------------+------+

| author              | title                        | type        | year |

+---------------------+------------------------------+-------------+------+

| Mark Twain          | The Adventures of Tom Sawyer | Fiction     | 1876 |

| Jane Austen         | Pride and Prejudice          | Fiction     | 1811 |

| Charles Darwin      | The Origin of Species        | Non-Fiction | 1856 |

| Charles Dickens     | The Old Curiosity Shop       | Fiction     | 1841 |

| William Shakespeare | Romeo and Juliet             | Play        | 1594 |

+---------------------+------------------------------+-------------+------+

5 rows in set (0.00 sec)


MariaDB [publications]> 

MariaDB [publications]> alter table classics rename pre1900;

Query OK, 0 rows affected (0.00 sec)


MariaDB [publications]> describe classics;

ERROR 1146 (42S02): Table 'publications.classics' doesn't exist

MariaDB [publications]> describe pre1900;

+--------+--------------+------+-----+---------+-------+

| Field  | Type         | Null | Key | Default | Extra |

+--------+--------------+------+-----+---------+-------+

| author | varchar(128) | YES  |     | NULL    |       |

| title  | varchar(128) | YES  |     | NULL    |       |

| type   | varchar(16)  | YES  |     | NULL    |       |

| year   | char(4)      | YES  |     | NULL    |       |

+--------+--------------+------+-----+---------+-------+

4 rows in set (0.00 sec)


MariaDB [publications]> alter table pre1900 rename classics;

Query OK, 0 rows affected (0.00 sec)


MariaDB [publications]> describe classics;

+--------+--------------+------+-----+---------+-------+

| Field  | Type         | Null | Key | Default | Extra |

+--------+--------------+------+-----+---------+-------+

| author | varchar(128) | YES  |     | NULL    |       |

| title  | varchar(128) | YES  |     | NULL    |       |

| type   | varchar(16)  | YES  |     | NULL    |       |

| year   | char(4)      | YES  |     | NULL    |       |

+--------+--------------+------+-----+---------+-------+

4 rows in set (0.01 sec)


MariaDB [publications]> 

MariaDB [publications]> 

MariaDB [publications]> alter table classics modify year smallint;

Query OK, 5 rows affected (0.03 sec)               

Records: 5  Duplicates: 0  Warnings: 0


MariaDB [publications]> describe classics;

+--------+--------------+------+-----+---------+-------+

| Field  | Type         | Null | Key | Default | Extra |

+--------+--------------+------+-----+---------+-------+

| author | varchar(128) | YES  |     | NULL    |       |

| title  | varchar(128) | YES  |     | NULL    |       |

| type   | varchar(16)  | YES  |     | NULL    |       |

| year   | smallint(6)  | YES  |     | NULL    |       |

+--------+--------------+------+-----+---------+-------+

4 rows in set (0.00 sec)


MariaDB [publications]> 

MariaDB [publications]> alter table classics add pages smallint unsigned;

Query OK, 5 rows affected (0.07 sec)               

Records: 5  Duplicates: 0  Warnings: 0


MariaDB [publications]> describe classics;

+--------+----------------------+------+-----+---------+-------+

| Field  | Type                 | Null | Key | Default | Extra |

+--------+----------------------+------+-----+---------+-------+

| author | varchar(128)         | YES  |     | NULL    |       |

| title  | varchar(128)         | YES  |     | NULL    |       |

| type   | varchar(16)          | YES  |     | NULL    |       |

| year   | smallint(6)          | YES  |     | NULL    |       |

| pages  | smallint(5) unsigned | YES  |     | NULL    |       |

+--------+----------------------+------+-----+---------+-------+

5 rows in set (0.00 sec)


MariaDB [publications]> alter table classics change type category varchar(16);

Query OK, 0 rows affected (0.04 sec)

Records: 0  Duplicates: 0  Warnings: 0


MariaDB [publications]> describe classics;

+----------+----------------------+------+-----+---------+-------+

| Field    | Type                 | Null | Key | Default | Extra |

+----------+----------------------+------+-----+---------+-------+

| author   | varchar(128)         | YES  |     | NULL    |       |

| title    | varchar(128)         | YES  |     | NULL    |       |

| category | varchar(16)          | YES  |     | NULL    |       |

| year     | smallint(6)          | YES  |     | NULL    |       |

| pages    | smallint(5) unsigned | YES  |     | NULL    |       |

+----------+----------------------+------+-----+---------+-------+

5 rows in set (0.00 sec)


MariaDB [publications]> 

MariaDB [publications]> alter table classics drop pages;

Query OK, 5 rows affected (0.03 sec)               

Records: 5  Duplicates: 0  Warnings: 0


MariaDB [publications]> describe classics;

+----------+--------------+------+-----+---------+-------+

| Field    | Type         | Null | Key | Default | Extra |

+----------+--------------+------+-----+---------+-------+

| author   | varchar(128) | YES  |     | NULL    |       |

| title    | varchar(128) | YES  |     | NULL    |       |

| category | varchar(16)  | YES  |     | NULL    |       |

| year     | smallint(6)  | YES  |     | NULL    |       |

+----------+--------------+------+-----+---------+-------+

4 rows in set (0.00 sec)


MariaDB [publications]> 

MariaDB [publications]> CREATE TABLE disposable(trash INT);

Query OK, 0 rows affected (0.04 sec)


MariaDB [publications]> DESCRIBE disposable;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| trash | int(11) | YES  |     | NULL    |       |

+-------+---------+------+-----+---------+-------+

1 row in set (0.00 sec)


MariaDB [publications]> DROP TABLE disposable;

Query OK, 0 rows affected (0.00 sec)


MariaDB [publications]> SHOW tables;

+------------------------+

| Tables_in_publications |

+------------------------+

| classics               |

+------------------------+

1 row in set (0.00 sec)


MariaDB [publications]> 

MariaDB [publications]> alter table classics add index(author(20));

Query OK, 5 rows affected (0.02 sec)               

Records: 5  Duplicates: 0  Warnings: 0


MariaDB [publications]> ALTER TABLE classics ADD INDEX(title(20));

Query OK, 5 rows affected (0.03 sec)               

Records: 5  Duplicates: 0  Warnings: 0


MariaDB [publications]> ALTER TABLE classics ADD INDEX(category(4));

Query OK, 5 rows affected (0.03 sec)               

Records: 5  Duplicates: 0  Warnings: 0


MariaDB [publications]> ALTER TABLE classics ADD INDEX(year);

Query OK, 5 rows affected (0.03 sec)               

Records: 5  Duplicates: 0  Warnings: 0


MariaDB [publications]> DESCRIBE classics;

+----------+--------------+------+-----+---------+-------+

| Field    | Type         | Null | Key | Default | Extra |

+----------+--------------+------+-----+---------+-------+

| author   | varchar(128) | YES  | MUL | NULL    |       |

| title    | varchar(128) | YES  | MUL | NULL    |       |

| category | varchar(16)  | YES  | MUL | NULL    |       |

| year     | smallint(6)  | YES  | MUL | NULL    |       |

+----------+--------------+------+-----+---------+-------+

4 rows in set (0.01 sec)


MariaDB [publications]> 


MariaDB [publications]> alter table classics add isbn char(13) primary key;

ERROR 1062 (23000): Duplicate entry '' for key 'PRIMARY'


MariaDB [publications]> ALTER TABLE classics ADD isbn CHAR(13);

Query OK, 5 rows affected (0.03 sec)               

Records: 5  Duplicates: 0  Warnings: 0


MariaDB [publications]> UPDATE classics SET isbn='9781598184891' WHERE year='1876';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0


MariaDB [publications]> UPDATE classics SET isbn='9780582506206' WHERE year='1811';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0


MariaDB [publications]> UPDATE classics SET isbn='9780517123201' WHERE year='1856';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0


MariaDB [publications]> UPDATE classics SET isbn='9780099533474' WHERE year='1841';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0


MariaDB [publications]> UPDATE classics SET isbn='9780192814968' WHERE year='1594';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0


MariaDB [publications]> ALTER TABLE classics ADD PRIMARY KEY(isbn);

Query OK, 5 rows affected (0.03 sec)               

Records: 5  Duplicates: 0  Warnings: 0


MariaDB [publications]> DESCRIBE classics;

+----------+--------------+------+-----+---------+-------+

| Field    | Type         | Null | Key | Default | Extra |

+----------+--------------+------+-----+---------+-------+

| author   | varchar(128) | YES  | MUL | NULL    |       |

| title    | varchar(128) | YES  | MUL | NULL    |       |

| category | varchar(16)  | YES  | MUL | NULL    |       |

| year     | smallint(6)  | YES  | MUL | NULL    |       |

| isbn     | char(13)     | NO   | PRI | NULL    |       |

+----------+--------------+------+-----+---------+-------+

5 rows in set (0.00 sec)


MariaDB [publications]> 

MariaDB [publications]> INSERT INTO classics(author, title, category, year, isbn)

    -> VALUES('Charles Dickens','Little Dorrit','Fiction','1857', '9780141439969');

Query OK, 1 row affected (0.00 sec)


MariaDB [publications]> select * from classics;                                 +---------------------+------------------------------+-------------+------+---------------+

| author              | title                        | category    | year | isbn          |

+---------------------+------------------------------+-------------+------+---------------+

| Mark Twain          | The Adventures of Tom Sawyer | Fiction     | 1876 | 9781598184891 |

| Jane Austen         | Pride and Prejudice          | Fiction     | 1811 | 9780582506206 |

| Charles Darwin      | The Origin of Species        | Non-Fiction | 1856 | 9780517123201 |

| Charles Dickens     | The Old Curiosity Shop       | Fiction     | 1841 | 9780099533474 |

| William Shakespeare | Romeo and Juliet             | Play        | 1594 | 9780192814968 |

| Charles Dickens     | Little Dorrit                | Fiction     | 1857 | 9780141439969 |

+---------------------+------------------------------+-------------+------+---------------+

6 rows in set (0.00 sec)


MariaDB [publications]> 

MariaDB [publications]> select author from classics;

+---------------------+

| author              |

+---------------------+

| Mark Twain          |

| Jane Austen         |

| Charles Darwin      |

| Charles Dickens     |

| William Shakespeare |

| Charles Dickens     |

+---------------------+

6 rows in set (0.00 sec)


MariaDB [publications]> select distinct author from classics;

+---------------------+

| author              |

+---------------------+

| Mark Twain          |

| Jane Austen         |

| Charles Darwin      |

| Charles Dickens     |

| William Shakespeare |

+---------------------+

5 rows in set (0.00 sec)


MariaDB [publications]> select author from classics limit 3;

+----------------+

| author         |

+----------------+

| Mark Twain     |

| Jane Austen    |

| Charles Darwin |

+----------------+

3 rows in set (0.00 sec)


MariaDB [publications]> select author from classics limit 3,1;

+-----------------+

| author          |

+-----------------+

| Charles Dickens |

+-----------------+

1 row in set (0.00 sec)


MariaDB [publications]> select author from classics limit 1,2;

+----------------+

| author         |

+----------------+

| Jane Austen    |

| Charles Darwin |

+----------------+

2 rows in set (0.00 sec)


MariaDB [publications]> 


MariaDB [publications]> select author,title from classics where match(author,title) against ('+charles -species');

+----------------+-----------------------+

| author         | title                 |

+----------------+-----------------------+

| Charles Darwin | The Origin of Species |

+----------------+-----------------------+

1 row in set (0.00 sec)


MariaDB [publications]> select author,title from classics where match(author,title) against ('+charles -species' in boolean mode);

+-----------------+------------------------+

| author          | title                  |

+-----------------+------------------------+

| Charles Dickens | The Old Curiosity Shop |

| Charles Dickens | Little Dorrit          |

+-----------------+------------------------+

2 rows in set (0.00 sec)

MariaDB [publications]> update classics set author='Mark Twain(Samuel Langhorne Clemens)' where author='Mark Twain';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0




MariaDB [publications]> update classics set category='Classic Fiction' where category='Fiction';

Query OK, 4 rows affected (0.00 sec)

Rows matched: 4  Changed: 4  Warnings: 0


MariaDB [publications]> select * from classics;

+--------------------------------------+------------------------------+-----------------+------+---------------+

| author                               | title                        | category        | year | isbn          |

+--------------------------------------+------------------------------+-----------------+------+---------------+

| Mark Twain(Samuel Langhorne Clemens) | The Adventures of Tom Sawyer | Classic Fiction | 1876 | 9781598184891 |

| Jane Austen                          | Pride and Prejudice          | Classic Fiction | 1811 | 9780582506206 |

| Charles Darwin                       | The Origin of Species        | Non-Fiction     | 1856 | 9780517123201 |

| Charles Dickens                      | The Old Curiosity Shop       | Classic Fiction | 1841 | 9780099533474 |

| William Shakespeare                  | Romeo and Juliet             | Play            | 1594 | 9780192814968 |

| Charles Dickens                      | Little Dorrit                | Classic Fiction | 1857 | 9780141439969 |

+--------------------------------------+------------------------------+-----------------+------+---------------+

6 rows in set (0.01 sec)


MariaDB [publications]> select category,count(author) from classics;

+-----------------+---------------+

| category        | count(author) |

+-----------------+---------------+

| Classic Fiction |             6 |

+-----------------+---------------+

1 row in set (0.00 sec)


MariaDB [publications]> select category,count(author) from classics group by category;

+-----------------+---------------+

| category        | count(author) |

+-----------------+---------------+

| Classic Fiction |             4 |

| Non-Fiction     |             1 |

| Play            |             1 |

+-----------------+---------------+

3 rows in set (0.00 sec)


MariaDB [publications]> 

MariaDB [publications]> CREATE TABLE customers (

    -> name VARCHAR(128),

    -> isbn VARCHAR(13),

    -> PRIMARY KEY (isbn)) ENGINE MyISAM;

Query OK, 0 rows affected (0.03 sec)


MariaDB [publications]> INSERT INTO customers(name,isbn)

    -> VALUES('Joe Bloggs','9780099533474');

Query OK, 1 row affected (0.00 sec)


MariaDB [publications]> INSERT INTO customers(name,isbn)

    -> VALUES('Mary Smith','9780582506206');

Query OK, 1 row affected (0.00 sec)


MariaDB [publications]> INSERT INTO customers(name,isbn)

    -> VALUES('Jack Wilson','9780517123201');

Query OK, 1 row affected (0.00 sec)


MariaDB [publications]> SELECT * FROM customers;

+-------------+---------------+

| name        | isbn          |

+-------------+---------------+

| Joe Bloggs  | 9780099533474 |

| Mary Smith  | 9780582506206 |

| Jack Wilson | 9780517123201 |

+-------------+---------------+

3 rows in set (0.00 sec)


MariaDB [publications]> 


MariaDB [publications]> select * from customers,classics where customers.isbn = classics.isbn; 

+-------------+---------------+-----------------+------------------------+-----------------+------+---------------+

| name        | isbn          | author          | title                  | category        | year | isbn          |

+-------------+---------------+-----------------+------------------------+-----------------+------+---------------+

| Joe Bloggs  | 9780099533474 | Charles Dickens | The Old Curiosity Shop | Classic Fiction | 1841 | 9780099533474 |

| Mary Smith  | 9780582506206 | Jane Austen     | Pride and Prejudice    | Classic Fiction | 1811 | 9780582506206 |

| Jack Wilson | 9780517123201 | Charles Darwin  | The Origin of Species  | Non-Fiction     | 1856 | 9780517123201 |

+-------------+---------------+-----------------+------------------------+-----------------+------+---------------+

3 rows in set (0.00 sec)


MariaDB [publications]> 


MariaDB [publications]> CREATE TABLE accounts (

    -> number INT, balance FLOAT, PRIMARY KEY(number)

    -> ) ENGINE InnoDB;

Query OK, 0 rows affected (0.03 sec)


MariaDB [publications]> DESCRIBE accounts;

+---------+---------+------+-----+---------+-------+

| Field   | Type    | Null | Key | Default | Extra |

+---------+---------+------+-----+---------+-------+

| number  | int(11) | NO   | PRI | NULL    |       |

| balance | float   | YES  |     | NULL    |       |

+---------+---------+------+-----+---------+-------+

2 rows in set (0.00 sec)


MariaDB [publications]> INSERT INTO accounts(number, balance) VALUES(12345, 1025.50);

Query OK, 1 row affected (0.00 sec)


MariaDB [publications]> INSERT INTO accounts(number, balance) VALUES(67890, 140.00);

Query OK, 1 row affected (0.01 sec)


MariaDB [publications]> SELECT * FROM accounts;

+--------+---------+

| number | balance |

+--------+---------+

|  12345 |  1025.5 |


|  67890 |     140 |

+--------+---------+

2 rows in set (0.00 sec)


MariaDB [publications]> 


MariaDB [publications]> BEGIN;

Query OK, 0 rows affected (0.00 sec)


MariaDB [publications]> UPDATE accounts SET balance=balance-250 WHERE number=12345;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0


MariaDB [publications]> UPDATE accounts SET balance=balance+250 WHERE number=67890;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0


MariaDB [publications]> SELECT * FROM accounts;

+--------+---------+

| number | balance |

+--------+---------+

|  12345 |  800.61 |

|  67890 |     390 |

+--------+---------+

2 rows in set (0.00 sec)


MariaDB [publications]> ROLLBACK;

Query OK, 0 rows affected (0.00 sec)


MariaDB [publications]> SELECT * FROM accounts;

+--------+---------+

| number | balance |

+--------+---------+

|  12345 | 1050.61 |

|  67890 |     140 |

+--------+---------+

2 rows in set (0.00 sec)


MariaDB [publications]> 

MariaDB [publications]> EXPLAIN SELECT * FROM accounts WHERE number='12345';

+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+

| id   | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |

+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+

|    1 | SIMPLE      | accounts | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |

+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+

1 row in set (0.00 sec)


MariaDB [publications]> 




WangWeis-MacBook-Air:bin wangwei$ ./mysqldump -u user1 -p password1 publications

Enter password: 

-- MySQL dump 10.16  Distrib 10.1.9-MariaDB, for osx10.6 (i386)

--

-- Host: localhost    Database: password1

-- ------------------------------------------------------

-- Server version 10.1.9-MariaDB


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

mysqldump: Got error: 1044: "Access denied for user 'user1'@'localhost' to database 'password1'" when selecting the database

WangWeis-MacBook-Air:bin wangwei$ ./mysqldump -u root  publications

-- MySQL dump 10.16  Distrib 10.1.9-MariaDB, for osx10.6 (i386)

--

-- Host: localhost    Database: publications

-- ------------------------------------------------------

-- Server version 10.1.9-MariaDB


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


--

-- Table structure for table `classics`

--


DROP TABLE IF EXISTS `classics`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `classics` (

  `author` varchar(128) DEFAULT NULL,

  `title` varchar(128) DEFAULT NULL,

  `category` varchar(16) DEFAULT NULL,

  `year` smallint(6) DEFAULT NULL,

  `isbn` char(13) NOT NULL,

  PRIMARY KEY (`isbn`),

  KEY `author` (`author`(20)),

  KEY `title` (`title`(20)),

  KEY `category` (`category`(4)),

  KEY `year` (`year`),

  FULLTEXT KEY `author_2` (`author`,`title`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;


--

-- Dumping data for table `classics`

--


LOCK TABLES `classics` WRITE;

/*!40000 ALTER TABLE `classics` DISABLE KEYS */;

INSERT INTO `classics` VALUES ('Mark Twain(Samuel Langhorne Clemens)','The Adventures of Tom Sawyer','Classic Fiction',1876,'9781598184891'),('Jane Austen','Pride and Prejudice','Classic Fiction',1811,'9780582506206'),('Charles Darwin','The Origin of Species','Non-Fiction',1856,'9780517123201'),('Charles Dickens','The Old Curiosity Shop','Classic Fiction',1841,'9780099533474'),('William Shakespeare','Romeo and Juliet','Play',1594,'9780192814968'),('Charles Dickens','Little Dorrit','Classic Fiction',1857,'9780141439969');

/*!40000 ALTER TABLE `classics` ENABLE KEYS */;

UNLOCK TABLES;


--

-- Table structure for table `customers`

--


DROP TABLE IF EXISTS `customers`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `customers` (

  `name` varchar(128) DEFAULT NULL,

  `isbn` varchar(13) NOT NULL,

  PRIMARY KEY (`isbn`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;


--

-- Dumping data for table `customers`

--


LOCK TABLES `customers` WRITE;

/*!40000 ALTER TABLE `customers` DISABLE KEYS */;

INSERT INTO `customers` VALUES ('Joe Bloggs','9780099533474'),('Mary Smith','9780582506206'),('Jack Wilson','9780517123201');

/*!40000 ALTER TABLE `customers` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;


/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;


-- Dump completed on 2016-02-21 21:05:36

WangWeis-MacBook-Air:bin wangwei$




mysql主从复制重做_Mysql主从复制（重置版）
weixin_32503361的博客
01-19 746
MySQL Replication是Mysql自带的一种功能，可以实现将数据从一台数据库服务器(master)复制到一台或多台数据库服务器(slave)，默认情况下属于异步复制，无需维持长连接。通过配置可以复制所有的库或者其中几个库，又或者库中的一部分表。(1).工作原理简单的说就是master服务器将数据库的改变写入二进制日志(binary log)，slave服务器同步这些二进制日志事件(bi...
【MySql安装出现的问题】
Mrchai521的博客
10-09 604
解决mysql 报1044错误的问题 这个错误一般是这个文件my-default.ini或者这个文件my.ini（对应你自己的版本）中sql_mode的配置可能有问题，你可以把它删除掉，然后按照下面的步骤做 1、cmd模式进入mysql的bin目录，输入D:\Program Files\MySQL\MySQL Server 5.6\bin>mysqld --defaults-file=...
mysql 模式的概念,理解MySQL——架构与概念
weixin_29671351的博客
03-17 417
第一章、MySQL架构与概念1、MySQL的逻辑架构最上面不是MySQL特有的，所有基于网络的C/S的网络应用程序都应该包括连接处理、认证、安全管理等。中间层是MySQL的核心，包括查询解析、分析、优化和缓存等。同时它还提供跨存储引擎的功能，包括存储过程、触发器和视图等。最下面是存储引擎，它负责存取数据。服务器通过storage engine API可以和各种存储引擎进行交互。1.1、查询优化和执...
Mysql库操作
phenix_417的博客
11-04 672
因此我们可以得出结论，创建数据库的字符集和校验集采用就近原则，如果不指定，就采用数据库默认的字符集和校验集。注意，备份数据库的时候不能在mysql中使用，因为这是一个操作系统的bash。实际上备份和恢复都是采用query的形式，也就是备份query语句，通过query再重新创建。校验规则：数据库进行字段比较所用的编码集，也就是读取数据库时采用的编码集。可以看到备份其实是把我们整个创建数据库，建表，导入数据的语句都装载这个文。可以看到test3采用的不区分大小写，查询出来的a大小写都有。
Mysql学习笔记1
07-25
### MySQL学习笔记1：深入理解内连接与外连接 #### 一、引言 在数据库管理与查询过程中，连接操作是十分常见且重要的一个环节。MySQL作为一种广泛使用的开源关系型数据库管理系统，在处理复杂的多表查询时，提供了...
MySQL学习笔记1：安装和登录(多种方法)
12-15
今天开始学习数据库，由于我对微软不怎么感冒，所以就不用他家的产品了本来想装ORACLE的，不过太大了，看着害怕对于我这种喜欢一切从简的人来说，MySQL是个不错的选择好了，关于数据库的大理论我就懒得写了，那些...
MySQL学习笔记 MySQL学习笔记
09-28
MySQL是世界上最受欢迎的关系型数据库管理系统之一，尤其在Web应用...学习MySQL不仅涉及理论知识，还需要大量的实践操作来熟悉其语法和功能。掌握MySQL能让你在数据管理、Web开发和系统集成等领域具备更强的竞争力。
MySQL学习笔记.zip
最新发布
02-23
这份“MySQL学习笔记”将引导我们深入理解其核心概念和实用技能。 一、MySQL简介 MySQL是一个开源、免费的数据库系统，由瑞典的MySQL AB公司开发，后被Oracle公司收购。它的设计目标是速度、可移植性和简洁性，支持...
SQL 重复记录查询
h819
11-27 185
mysql> create temporary table tmp_wrap select * from users_groups group by uid having count(1) >= 1;Query OK, 7 rows affected (0.11 sec)Records: 7  Duplicates: 0  Warnings: 0mysql> truncate t...
mysql-事务
学习，思考，记录
08-20 677
1、不是所有的表类型都支持事务，MyISAM类型表bu
mysql数据库操作一
zq4132的专栏
12-12 625
显示数据位数32还是64： mysql -V / mysql --version 登录数据库： mysql -u root -p 输入登录密码： 创建数据库： create DATABASE RUNOOB; 例子： mysql> create database tmysql; Query OK, 1 row affected (0.02 sec) 显示所有数据库： show databases; 例子： mysql> show databases; +--
mysql数据库技术基本操作_MySQL数据库基本操作（一）
weixin_39603604的博客
01-21 130
本地连接：mysql-u用户名 -p输入密码 qwe123mysql-uroot -pqwe123 #18sudo apt-get install mysql-server #p2 (没有装导入版的)创建用户#创建用户mysql> CREATE USER 'jianeng'@'%' IDENTIFIED BY 'qwe123';#给用户赋予权限mysql> GRANT...
修改mysql密码主页登录不了_mysql登录不了及修改密码
weixin_42522148的博客
01-19 176
安装mariadb，默认是无密码的，但一般是指要设置密码的。在设置密码时出现各种问题，可能还是不太明白其原理。一下我尝试了两种方法，但都失败了。下面这两个是我尝试的方法：一、网上最多的方法是1、输入下面的指令mysqladmin -u root -p password newpassword但是我已输入提示输入密码，我输入密码了但提示错误，见下[root@localhost sakila]# my...
mysql表分区6_MySQL 表分区（六）
weixin_29416781的博客
01-20 134
mysql>CREATE TABLE ts (id INT, purchased DATE)->PARTITION BY RANGE( YEAR(purchased) )->SUBPARTITION BY HASH( TO_DAYS(purchased) )-> SUBPARTITIONS 2-> ( PARTITION p0 VALUES LESS THAN (19...
数据库管理，范式，约束
HYJW01的博客
07-09 714
数据库管理 创建数据库语法：CREATE DATABASE [IF NOT EXISTS] <数据库名>[[DEFAULT] CHARACTER SET <字符集名>][[DEFAULT] COLLATE <校对规则名>]; # [ ] 可选项 <> 必选项 mysql> create database mydb default charset utf8mb4; 创建库 Query OK, 1 row affected...
商用密码的发展历程及当前应用情况
Mr_kuangrq的博客
09-15 1848
商用密码的发展历程及当前应用情况 一、商用密码发展历程 1、1999年，国务院颁布《商用密码管理条例》，规定国家密码管理委员会及其办公室主管全国的商用密码管理工作。 2、2002年，中央机构编制委员会批准国家密码管理委员会办公室下设商用密码管理办公室。 3、2005年，国家密码管理委员会办公室更名为国家密码管理局。 4、2008年，国家密码管理局列入部委管理的国家局序列。 5、2018年3月,《国务院关于部委管理的国家局设置的通知》(国发〔2018〕7号)明确规定,国家密码管理局与中央密码工作领
MySQL增删改查之DML语句详解
总结沉淀
04-02 2228
DML语句 主要指数据库对表记录的操作，包括表记录的插入（insert）、更新（update）、删除（delete）和查询（select）等 以下我们的操作都基于下表进行： mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field    | Type
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值